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

Usage: docker compose exec api python seed_translations_pageinfo_drill_down_reports.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)
DRILL_DOWN_REPORTS = {
    "drill-down-reports.title": (
        "Drill Down Reports",
        "ड्रिल-डाउन रिपोर्ट",
        "ड्रिल-डाउन अहवाल",
        "Drill Down Reports",
    ),
    "drill-down-reports.subtitle": (
        "How each KPI turns into a record-level report",
        "प्रत्येक KPI, record-level रिपोर्ट में कैसे बदलता है",
        "प्रत्येक KPI, record-level अहवालात कसे रूपांतरित होते",
        "Har KPI record-level report mein kaise convert hota hai",
    ),
    "drill-down-reports.body.0": (
        "Drill-down reports let you click through a KPI total (from the Executive Dashboard or the sidebar here) into the exact records behind it. Pick a KPI on the left and the table queries the live asset ledger in real time — nothing here is cached or pre-aggregated.",
        "Drill-down रिपोर्ट आपको किसी KPI टोटल पर (Executive Dashboard या यहाँ की sidebar से) क्लिक करके उसके पीछे के सटीक records तक ले जाती हैं। बाईं ओर कोई KPI चुनें और टेबल real time में live asset ledger को query करती है — यहाँ कुछ भी cache या pre-aggregate नहीं किया गया है।",
        "Drill-down अहवाल तुम्हाला एखाद्या KPI totalवर (Executive Dashboard किंवा येथील sidebar मधून) क्लिक करून त्यामागील नेमक्या records पर्यंत घेऊन जातात. डावीकडे एखादी KPI निवडा आणि टेबल real time मध्ये live asset ledger क्वेरी करते — येथे काहीही cache किंवा pre-aggregate केलेले नाही.",
        "Drill-down reports aapko kisi KPI total par (Executive Dashboard ya yahan ki sidebar se) click karke uske peeche ke exact records tak le jaate hain. Left side se koi KPI pick karein aur table real time mein live asset ledger query karta hai — yahan kuch bhi cached ya pre-aggregated nahi hai.",
    ),
    "drill-down-reports.body.1": (
        "There is no single shared report layout. Each KPI is its own backend query, so the columns shown are whatever fields that query returns — they will differ from one KPI to the next.",
        "कोई एक common report लेआउट नहीं है। हर KPI अपनी अलग backend query है, इसलिए दिखाए गए columns वही fields हैं जो वह query लौटाती है — ये हर KPI के साथ बदलते रहेंगे।",
        "एकच सामायिक report लेआउट नाही. प्रत्येक KPI ही स्वतःची स्वतंत्र backend query आहे, त्यामुळे दाखवलेले columns ती query जे fields परत करते तेच असतात — प्रत्येक KPI नुसार ते वेगळे असतील.",
        "Koi single shared report layout nahi hai. Har KPI apni alag backend query hai, isliye dikhaye gaye columns wahi fields hain jo wo query return karti hai — ye har KPI ke saath alag honge.",
    ),
    "drill-down-reports.stepsHeading": (
        "How a drill-down works",
        "Drill-down कैसे काम करता है",
        "Drill-down कसे कार्य करते",
        "Drill-down kaise kaam karta hai",
    ),
    "drill-down-reports.steps.0.label": ("Pick a KPI", "एक KPI चुनें", "एक KPI निवडा", "Ek KPI pick karein"),
    "drill-down-reports.steps.0.caption": (
        "Choose a metric from the sidebar list",
        "sidebar सूची से कोई metric चुनें",
        "sidebar यादीतून एखादे metric निवडा",
        "Sidebar list se koi metric choose karein",
    ),
    "drill-down-reports.steps.1.label": ("Query Runs", "Query चलती है", "Query चालते", "Query Runs"),
    "drill-down-reports.steps.1.caption": (
        "That KPI's dedicated backend query executes",
        "उस KPI की dedicated backend query execute होती है",
        "त्या KPI ची dedicated backend query execute होते",
        "Us KPI ki dedicated backend query execute hoti hai",
    ),
    "drill-down-reports.steps.2.label": ("Review Records", "Records की समीक्षा करें", "Records चा आढावा घ्या", "Records Review karein"),
    "drill-down-reports.steps.2.caption": (
        "Columns adapt to whatever fields came back",
        "Columns वापस आए fields के अनुसार बदल जाते हैं",
        "Columns परत आलेल्या fields नुसार बदलतात",
        "Columns jo bhi fields wapas aaye unke hisaab se adapt ho jaate hain",
    ),
    "drill-down-reports.steps.3.label": ("Export", "Export करें", "Export करा", "Export karein"),
    "drill-down-reports.steps.3.caption": (
        "Download the current KPI's rows to Excel or PDF",
        "वर्तमान KPI की rows को Excel या PDF में download करें",
        "सध्याच्या KPI च्या rows Excel किंवा PDF मध्ये डाउनलोड करा",
        "Current KPI ki rows ko Excel ya PDF mein download karein",
    ),
    "drill-down-reports.fieldRules.0.name": ("KPI", "KPI", "KPI", "KPI"),
    "drill-down-reports.fieldRules.0.description": (
        "Selects which fixed server-side query runs (Critical Assets, Custody Exceptions, Idle Assets, etc.) — there's no custom filter builder.",
        "यह तय करता है कि कौन-सी fixed server-side query चलेगी (Critical Assets, Custody Exceptions, Idle Assets, आदि) — इसमें कोई custom filter builder नहीं है।",
        "हे ठरवते की कोणती fixed server-side query चालते (Critical Assets, Custody Exceptions, Idle Assets, इ.) — यात कोणताही custom filter builder नाही.",
        "Ye decide karta hai ki kaunsi fixed server-side query chalegi (Critical Assets, Custody Exceptions, Idle Assets, etc.) — isme koi custom filter builder nahi hai.",
    ),
    "drill-down-reports.fieldRules.1.name": ("Total count", "कुल संख्या", "एकूण संख्या", "Total count"),
    "drill-down-reports.fieldRules.1.description": (
        "The number of records the server matched for this KPI, shown next to its name regardless of how many rows are currently visible.",
        "इस KPI के लिए server द्वारा matched records की संख्या, इसके नाम के बगल में दिखाई जाती है, चाहे अभी कितनी भी rows दिखाई दे रही हों।",
        "या KPI साठी server ने matched केलेल्या records ची संख्या, जी सध्या किती rows दिसत आहेत याची पर्वा न करता त्याच्या नावाशेजारी दाखवली जाते.",
        "Is KPI ke liye server ne jitne records match kiye hain unki count, jo iske naam ke pass dikhayi jaati hai, chahe abhi kitni bhi rows visible hon.",
    ),
    "drill-down-reports.fieldRules.2.name": ("Columns", "Columns", "Columns", "Columns"),
    "drill-down-reports.fieldRules.2.description": (
        "Not fixed — inferred from the fields present on the first returned record, so they vary by KPI.",
        "यह fixed नहीं है — पहले लौटाए गए record में मौजूद fields से तय होता है, इसलिए यह हर KPI के साथ बदलता है।",
        "हे fixed नाही — पहिल्या परत आलेल्या record मध्ये असलेल्या fields वरून ठरते, त्यामुळे ते प्रत्येक KPI नुसार बदलते.",
        "Ye fixed nahi hai — pehle wapas aaye record mein present fields se decide hota hai, isliye ye har KPI ke saath vary karta hai.",
    ),
    "drill-down-reports.fieldRules.3.name": ("Export file name", "Export फ़ाइल नाम", "Export फाइल नाव", "Export file name"),
    "drill-down-reports.fieldRules.3.description": (
        "Excel/PDF downloads are named drill-down-<kpi_key>, so exports from different KPIs never overwrite each other.",
        "Excel/PDF downloads का नाम drill-down-<kpi_key> रखा जाता है, इसलिए अलग-अलग KPIs के exports कभी एक-दूसरे को overwrite नहीं करते।",
        "Excel/PDF downloads चे नाव drill-down-<kpi_key> असे ठेवले जाते, त्यामुळे वेगवेगळ्या KPIs चे exports एकमेकांना कधीही overwrite करत नाहीत.",
        "Excel/PDF downloads ka naam drill-down-<kpi_key> rakha jaata hai, isliye alag-alag KPIs ke exports kabhi ek-dusre ko overwrite nahi karte.",
    ),
    "drill-down-reports.tip.title": (
        "An empty KPI shows no columns",
        "खाली KPI में कोई columns नहीं दिखते",
        "रिकाम्या KPI मध्ये कोणतेही columns दिसत नाहीत",
        "Empty KPI mein koi columns nahi dikhte",
    ),
    "drill-down-reports.tip.body": (
        "Columns come from the first record returned, not a fixed schema. If a KPI has zero matching rows, the table drops its column headers entirely and shows only the empty-state message — that's expected behavior, not a bug.",
        "Columns पहले लौटाए गए record से आते हैं, किसी fixed schema से नहीं। यदि किसी KPI के लिए zero matching rows हैं, तो टेबल अपने column headers पूरी तरह हटा देती है और केवल empty-state message दिखाती है — यह अपेक्षित व्यवहार है, कोई bug नहीं।",
        "Columns पहिल्या परत आलेल्या record मधून येतात, कोणत्याही fixed schema मधून नाही. जर एखाद्या KPI साठी zero matching rows असतील, तर टेबल आपले column headers पूर्णपणे काढून टाकते आणि फक्त empty-state message दाखवते — हे अपेक्षित वर्तन आहे, bug नाही.",
        "Columns pehle wapas aaye record se aate hain, kisi fixed schema se nahi. Agar kisi KPI ke liye zero matching rows hain, to table apne column headers poori tarah drop kar deta hai aur sirf empty-state message dikhata hai — ye expected behavior hai, bug nahi.",
    ),
}


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 DRILL_DOWN_REPORTS.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(DRILL_DOWN_REPORTS)} drill-down-reports keys.")


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