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

Usage: docker compose exec api python seed_translations_pageinfo_custody_history.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)
CUSTODY_HISTORY = {
    "custody-history.title": (
        "Custody History",
        "कस्टडी इतिहास",
        "कस्टडी इतिहास",
        "Custody History",
    ),
    "custody-history.subtitle": (
        "The full chain-of-custody trail for one asset",
        "एक asset के लिए पूरा chain-of-custody ट्रेल",
        "एका asset साठी संपूर्ण chain-of-custody ट्रेल",
        "Ek asset ki poori chain-of-custody trail.",
    ),
    "custody-history.body.0": (
        "Pick an asset above to see every custody record ever created for it, newest first — who held it, when custody started, and whether it was accepted.",
        "उस asset से जुड़ा हर custody record देखने के लिए ऊपर एक asset चुनें, सबसे नए रिकॉर्ड सबसे पहले — किसके पास वह था, custody कब शुरू हुई, और क्या इसे स्वीकार किया गया।",
        "त्या asset साठी तयार झालेला प्रत्येक custody record पाहण्यासाठी वर एक asset निवडा, सर्वात नवीन रेकॉर्ड सर्वप्रथम — तो कोणाकडे होता, custody कधी सुरू झाली, आणि तो स्वीकारला गेला की नाही.",
        "Upar ek asset select karein taaki uske liye ab tak banaya gaya har custody record dikhe, newest sabse pehle — kiske paas tha, custody kab start hui, aur accept hua tha ya nahi.",
    ),
    "custody-history.body.1": (
        "Records are append-only: assigning or accepting custody never edits an old row, it adds a new one. A single asset can show several records over time as it's reassigned.",
        "रिकॉर्ड्स append-only होते हैं: custody assign या accept करने से पुरानी row कभी edit नहीं होती, बल्कि एक नई row जुड़ती है। समय के साथ reassign होने पर एक ही asset के कई records दिख सकते हैं।",
        "रेकॉर्ड्स append-only असतात: custody assign किंवा accept केल्याने जुनी row कधीही edit होत नाही, त्याऐवजी नवीन row जोडली जाते. वेळोवेळी reassign झाल्यामुळे एकाच asset चे अनेक records दिसू शकतात.",
        "Records append-only hote hain: custody assign ya accept karne se purani row kabhi edit nahi hoti, ek nayi row add hoti hai. Time ke saath reassign hone par ek hi asset ke kai records dikh sakte hain.",
    ),
    "custody-history.stepsHeading": (
        "How a record's status changes",
        "किसी रिकॉर्ड की स्थिति कैसे बदलती है",
        "एखाद्या रेकॉर्डची स्थिती कशी बदलते",
        "Record ka status kaise change hota hai",
    ),
    "custody-history.steps.0.label": ("Assigned", "असाइन किया गया", "असाइन केले", "Assigned"),
    "custody-history.steps.0.caption": (
        "Custodian appointed; record is pending_acceptance.",
        "Custodian नियुक्त किया गया; रिकॉर्ड pending_acceptance में है।",
        "Custodian नियुक्त केला; रेकॉर्ड pending_acceptance स्थितीत आहे.",
        "Custodian appoint hota hai; record pending_acceptance mein hota hai.",
    ),
    "custody-history.steps.1.label": ("Accepted", "स्वीकृत", "स्वीकृत", "Accepted"),
    "custody-history.steps.1.caption": (
        "Custodian accepts — record becomes active.",
        "Custodian स्वीकार करता है — रिकॉर्ड active हो जाता है।",
        "Custodian स्वीकारतो — रेकॉर्ड active होते.",
        "Custodian accept karta hai — record active ho jaata hai.",
    ),
    "custody-history.steps.2.label": ("Superseded", "प्रतिस्थापित", "प्रतिस्थापित", "Superseded"),
    "custody-history.steps.2.caption": (
        "Reassigned or replaced by a newer record; stays visible in the trail.",
        "Reassign किया गया या किसी नए रिकॉर्ड द्वारा replace किया गया; फिर भी trail में दिखाई देता रहता है।",
        "Reassign केले किंवा नवीन रेकॉर्डने replace केले; तरीही trail मध्ये दिसत राहते.",
        "Reassign ho jaata hai ya kisi naye record se replace ho jaata hai; phir bhi trail mein visible rehta hai.",
    ),
    "custody-history.fieldRules.0.name": ("Asset", "Asset", "Asset", "Asset"),
    "custody-history.fieldRules.0.description": (
        "Search and select the asset to inspect — the timeline stays empty until one is chosen.",
        "जिस asset की जांच करनी है उसे खोजें और चुनें — जब तक कोई asset नहीं चुना जाता, timeline खाली रहती है।",
        "तपासायच्या asset ला शोधा आणि निवडा — जोपर्यंत एखादी asset निवडली जात नाही तोपर्यंत timeline रिकामी राहते.",
        "Inspect karne wali asset ko search karke select karein — jab tak koi asset choose nahi hoti, timeline empty rehti hai.",
    ),
    "custody-history.fieldRules.1.name": ("Status", "स्थिति", "स्थिती", "Status"),
    "custody-history.fieldRules.1.description": (
        "pending_acceptance (amber), active (teal), or superseded (grey) — where the record sits in its lifecycle.",
        "pending_acceptance (amber), active (teal), या superseded (grey) — रिकॉर्ड अपने lifecycle में कहाँ है, यह दर्शाता है।",
        "pending_acceptance (amber), active (teal), किंवा superseded (grey) — रेकॉर्ड त्याच्या lifecycle मध्ये कुठे आहे हे दर्शवते.",
        "pending_acceptance (amber), active (teal), ya superseded (grey) — record apni lifecycle mein kahan hai, ye dikhata hai.",
    ),
    "custody-history.fieldRules.2.name": ("Effective / Accepted", "प्रभावी / स्वीकृत", "प्रभावी / स्वीकृत", "Effective / Accepted"),
    "custody-history.fieldRules.2.description": (
        "Effective is when custody started; Accepted is when the custodian confirmed it — blank while still pending.",
        "Effective वह समय है जब custody शुरू हुई; Accepted वह समय है जब custodian ने इसकी पुष्टि की — जब तक pending है, यह खाली रहता है।",
        "Effective म्हणजे custody कधी सुरू झाली; Accepted म्हणजे custodian ने त्याची पुष्टी कधी केली — जोपर्यंत pending आहे तोपर्यंत रिकामे राहते.",
        "Effective wo time hai jab custody start hui; Accepted wo time hai jab custodian ne confirm kiya — jab tak pending hai, ye blank rehta hai.",
    ),
    "custody-history.fieldRules.3.name": ("Cost centre override", "कॉस्ट सेंटर ओवरराइड", "कॉस्ट सेंटर ओव्हरराइड", "Cost centre override"),
    "custody-history.fieldRules.3.description": (
        "Only shown when this assignment charges a different cost centre than the asset's default.",
        "यह तभी दिखता है जब यह assignment asset के default से अलग किसी cost centre पर charge करता है।",
        "हे तेव्हाच दिसते जेव्हा हे assignment asset च्या default पेक्षा वेगळ्या cost centre वर charge करते.",
        "Ye sirf tab dikhta hai jab ye assignment asset ke default se alag kisi cost centre par charge karta hai.",
    ),
    "custody-history.tip.title": (
        "Deep-linked asset can look blank",
        "Deep-linked asset खाली दिख सकता है",
        "Deep-linked asset रिकामी दिसू शकते",
        "Deep-linked asset blank dikh sakta hai",
    ),
    "custody-history.tip.body": (
        "Opening this page with ?asset_id=... in the URL fetches that asset's label separately from the list. If the id is stale or invalid, the fetch fails silently and the picker shows empty instead of an error — reselect the asset manually if that happens.",
        "URL में ?asset_id=... के साथ यह पेज खोलने पर उस asset का label list से अलग fetch किया जाता है। अगर id पुरानी या invalid है, तो fetch silently fail हो जाता है और picker error के बजाय खाली दिखाता है — ऐसा होने पर asset को मैन्युअल रूप से फिर से चुनें।",
        "URL मध्ये ?asset_id=... सह हे पेज उघडल्यास त्या asset चे label list पासून वेगळे fetch केले जाते. जर id जुनी किंवा invalid असेल, तर fetch silently fail होते आणि picker error ऐवजी रिकामे दाखवते — असे झाल्यास asset पुन्हा manually निवडा.",
        "URL mein ?asset_id=... ke saath ye page open karne par us asset ka label list se alag fetch hota hai. Agar id stale ya invalid hai, to fetch silently fail ho jaata hai aur picker error ke bajaye empty dikhata hai — aisa ho to asset ko manually reselect 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 CUSTODY_HISTORY.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(CUSTODY_HISTORY)} custody-history keys.")


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