"""One-off seed: registers translation_keys English source_text and hi/mr/hinglish
translation_values for the "hrms-sync" PageInfoButton content.

Usage: docker compose exec api python seed_translations_pageinfo_hrms_sync.py
"""
import asyncio
import uuid
from datetime import datetime, timezone

from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession, async_sessionmaker, create_async_engine

from ams.core.config import settings

NAMESPACE = "help"

# key -> (english, hi, mr, hinglish)
HRMS_SYNC = {
    "hrms-sync.title": (
        "Directory Sync",
        "डायरेक्टरी सिंक",
        "डायरेक्टरी सिंक",
        "Directory Sync",
    ),
    "hrms-sync.subtitle": (
        "How each source syncs and what to check if it doesn't",
        "प्रत्येक स्रोत कैसे सिंक होता है और यदि सिंक न हो तो क्या जांचें",
        "प्रत्येक स्रोत कसा सिंक होतो आणि सिंक न झाल्यास काय तपासावे",
        "Har source kaise sync hota hai aur agar sync na ho to kya check karein",
    ),
    "hrms-sync.body.0": (
        "Bring your departments and people in automatically from your HR and identity systems, instead of adding them by hand.",
        "अपने departments और लोगों को मैन्युअल रूप से जोड़ने के बजाय, उन्हें अपने HR और identity systems से स्वचालित रूप से लाएं।",
        "आपले departments आणि लोकांना हाताने जोडण्याऐवजी, त्यांना आपल्या HR आणि identity systems मधून आपोआप आणा.",
        "Departments aur logon ko haath se add karne ke bajaay, unhe apne HR aur identity systems se automatically laayein.",
    ),
    "hrms-sync.body.1": (
        "Turn a source on and enter its details in Settings, then run a sync here.",
        "Settings में किसी source को चालू करें और उसकी details दर्ज करें, फिर यहां sync चलाएं।",
        "Settings मध्ये एखादा source चालू करा आणि त्याचे details टाका, नंतर इथे sync रन करा.",
        "Settings mein kisi source ko on karein aur uski details enter karein, phir yahan sync run karein.",
    ),
    "hrms-sync.stepsHeading": (
        "How a sync runs",
        "Sync कैसे चलता है",
        "Sync कसे चालते",
        "Sync kaise chalta hai",
    ),
    "hrms-sync.steps.0.label": ("Configure", "कॉन्फ़िगर करें", "कॉन्फिगर करा", "Configure"),
    "hrms-sync.steps.0.caption": (
        "Turn the source on and enter its connection details in Settings.",
        "Source को चालू करें और Settings में उसकी connection details दर्ज करें।",
        "Source चालू करा आणि Settings मध्ये त्याचे connection details टाका.",
        "Source ko on karein aur Settings mein uski connection details enter karein.",
    ),
    "hrms-sync.steps.1.label": ("Sync Now", "अभी सिंक करें", "आता सिंक करा", "Sync Now"),
    "hrms-sync.steps.1.caption": (
        "Kicks off a background job that pulls departments and people.",
        "यह एक background job शुरू करता है जो departments और लोगों को खींचता है।",
        "हे एक background job सुरू करते जे departments आणि लोकांना खेचते.",
        "Ye ek background job start karta hai jo departments aur logon ko pull karta hai.",
    ),
    "hrms-sync.steps.2.label": ("Review Status", "स्थिति जांचें", "स्थिती तपासा", "Review Status"),
    "hrms-sync.steps.2.caption": (
        "Watch the card's Last Run time to confirm the job completed.",
        "यह पुष्टि करने के लिए कि job पूरी हो गई है, card के Last Run time को देखें।",
        "job पूर्ण झाल्याची खात्री करण्यासाठी card च्या Last Run time कडे लक्ष द्या.",
        "Job complete hua ya nahi confirm karne ke liye card ka Last Run time dekhein.",
    ),
    "hrms-sync.fieldRules.0.name": ("Source", "स्रोत", "स्रोत", "Source"),
    "hrms-sync.fieldRules.0.description": (
        "HRMS (Oracle Fusion), Azure AD, or LDAP — each syncs independently of the others.",
        "HRMS (Oracle Fusion), Azure AD, या LDAP — प्रत्येक एक-दूसरे से स्वतंत्र रूप से सिंक होता है।",
        "HRMS (Oracle Fusion), Azure AD, किंवा LDAP — प्रत्येक एकमेकांपासून स्वतंत्रपणे सिंक होतो.",
        "HRMS (Oracle Fusion), Azure AD, ya LDAP — har ek doosre se independently sync hota hai.",
    ),
    "hrms-sync.fieldRules.1.name": ("On / Off", "चालू / बंद", "चालू / बंद", "On / Off"),
    "hrms-sync.fieldRules.1.description": (
        "Set in Settings → Directory Sync. A source stays disabled here until it's turned on and configured there.",
        "Settings → Directory Sync में सेट करें। जब तक source वहां चालू और configure नहीं किया जाता, तब तक यह यहां disabled रहता है।",
        "Settings → Directory Sync मध्ये सेट करा. जोपर्यंत source तिथे चालू आणि configure केला जात नाही, तोपर्यंत तो इथे disabled राहतो.",
        "Settings → Directory Sync mein set karein. Jab tak source wahan on aur configure nahi hota, tab tak ye yahan disabled rehta hai.",
    ),
    "hrms-sync.fieldRules.2.name": ("Last Run", "पिछला रन", "शेवटचा रन", "Last Run"),
    "hrms-sync.fieldRules.2.description": (
        "Timestamp of the most recent sync attempt for that source, or \"Not run yet\" if it's never been triggered.",
        "उस source के सबसे हाल के sync प्रयास का timestamp, या यदि यह कभी trigger नहीं हुआ है तो \"Not run yet\"।",
        "त्या source च्या अगदी अलीकडील sync प्रयत्नाचा timestamp, किंवा जर तो कधीच trigger झाला नसेल तर \"Not run yet\".",
        "Us source ke sabse recent sync attempt ka timestamp, ya agar wo kabhi trigger nahi hua to \"Not run yet\".",
    ),
    "hrms-sync.fieldRules.3.name": ("Sync Now", "अभी सिंक करें", "आता सिंक करा", "Sync Now"),
    "hrms-sync.fieldRules.3.description": (
        "Disabled while that source's own job is running; other sources can still be synced at the same time.",
        "जब तक उस source की अपनी job चल रही है, यह disabled रहता है; इसी दौरान अन्य sources को फिर भी sync किया जा सकता है।",
        "जोपर्यंत त्या source ची स्वतःची job चालू आहे, तोपर्यंत हे disabled राहते; त्याच वेळी इतर sources तरीही sync केले जाऊ शकतात.",
        "Jab tak us source ki apni job chal rahi hai, ye disabled rehta hai; usi time doosre sources phir bhi sync ho sakte hain.",
    ),
    "hrms-sync.tip.title": (
        "Sync Now starts the job — it doesn't wait for it",
        "Sync Now केवल job शुरू करता है — यह उसके पूरा होने का इंतज़ार नहीं करता",
        "Sync Now फक्त job सुरू करतो — तो तो पूर्ण होण्याची वाट पाहत नाही",
        "Sync Now sirf job start karta hai — ye uske complete hone ka wait nahi karta",
    ),
    "hrms-sync.tip.body": (
        "The button re-enables and Last Run updates as soon as the job is accepted, not once it finishes. For a large HRMS or AD import, give it a few minutes and refresh the page before assuming it stalled.",
        "job के accept होते ही button फिर से enable हो जाता है और Last Run अपडेट हो जाता है, न कि उसके पूरा होने पर। किसी बड़े HRMS या AD import के लिए, यह मान लेने से पहले कि यह अटक गया है, कुछ मिनट दें और page को refresh करें।",
        "job स्वीकारला जाताच button पुन्हा enable होतो आणि Last Run अपडेट होते, तो पूर्ण झाल्यावर नाही. मोठ्या HRMS किंवा AD import साठी, तो अडकला असे गृहीत धरण्यापूर्वी काही मिनिटे द्या आणि page रिफ्रेश करा.",
        "Job accept hote hi button dobara enable ho jaata hai aur Last Run update ho jaata hai, uske finish hone par nahi. Bade HRMS ya AD import ke liye, ye maan lene se pehle ki stall ho gaya, kuch minutes dein aur page refresh karein.",
    ),
}


async def seed_for_session(session: AsyncSession) -> tuple[int, int]:
    now = datetime.now(timezone.utc)
    keys_upserted = 0
    values_upserted = 0
    for key, (en, hi, mr, hinglish) in HRMS_SYNC.items():
        key_id = (await session.execute(
            text("SELECT id FROM translation_keys WHERE namespace = :ns AND key = :key"),
            {"ns": NAMESPACE, "key": key},
        )).scalar_one_or_none()
        if key_id:
            await session.execute(
                text("UPDATE translation_keys SET source_text = :src WHERE id = :id"),
                {"src": en, "id": key_id},
            )
        else:
            key_id = str(uuid.uuid4())
            await session.execute(
                text("""INSERT INTO translation_keys (id, namespace, key, source_text, created_at)
                        VALUES (:id, :ns, :key, :src, :now)"""),
                {"id": key_id, "ns": NAMESPACE, "key": key, "src": en, "now": now},
            )
            keys_upserted += 1

        for lang_code, value in (("hi", hi), ("mr", mr), ("hinglish", hinglish)):
            existing = (await session.execute(
                text("SELECT id FROM translation_values WHERE key_id = :kid AND language_code = :lc"),
                {"kid": key_id, "lc": lang_code},
            )).scalar_one_or_none()
            if existing:
                await session.execute(
                    text("UPDATE translation_values SET value = :val, updated_at = :now WHERE id = :id"),
                    {"val": value, "now": now, "id": existing},
                )
            else:
                await session.execute(
                    text("""INSERT INTO translation_values (id, key_id, language_code, value, updated_by, updated_at)
                            VALUES (:id, :kid, :lc, :val, NULL, :now)"""),
                    {"id": str(uuid.uuid4()), "kid": key_id, "lc": lang_code, "val": value, "now": now},
                )
                values_upserted += 1
    await session.commit()
    return keys_upserted, values_upserted


async def main() -> None:
    engine = create_async_engine(settings.DATABASE_URL, echo=False)
    Session = async_sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)

    async with Session() as session:
        tenants = (await session.execute(
            text("SELECT id::text AS id, slug FROM public.tenants WHERE status != 'archived'")
        )).fetchall()

    for tenant in tenants:
        schema = f"tenant_{tenant.id.replace('-', '_')}"
        async with Session() as session:
            await session.execute(text(f"SET search_path TO {schema}, public"))
            try:
                k, v = await seed_for_session(session)
                print(f"  seeded {k} new keys, {v} new values: {tenant.slug}")
            except Exception as exc:
                print(f"  SKIPPED {tenant.slug}: {exc}")
                await session.rollback()

    await engine.dispose()
    print(f"Done — {len(tenants)} tenant(s) processed, {len(HRMS_SYNC)} hrms-sync keys.")


if __name__ == "__main__":
    asyncio.run(main())