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

Usage: docker compose exec api python seed_translations_pageinfo_master_records.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)
MASTER_RECORDS = {
    "master-records.title": (
        "Master Data Records",
        "मास्टर डेटा रिकॉर्ड्स",
        "मास्टर डेटा रेकॉर्ड्स",
        "Master Data Records",
    ),
    "master-records.subtitle": (
        "A read-only, consolidated view across all governed master registers",
        "सभी गवर्न्ड मास्टर रजिस्टरों में एक read-only, समेकित दृश्य",
        "सर्व गव्हर्न्ड मास्टर रजिस्टर्समधील एक read-only, एकत्रित दृश्य",
        "Sabhi governed master registers ka ek read-only, consolidated view",
    ),
    "master-records.body.0": (
        "Consolidated repository of core classification categories used across operational ledger sheets.",
        "ऑपरेशनल लेजर शीट्स में उपयोग की जाने वाली मुख्य classification categories का समेकित भंडार।",
        "ऑपरेशनल लेजर शीट्समध्ये वापरल्या जाणाऱ्या मुख्य classification categories चा एकत्रित संग्रह.",
        "Operational ledger sheets mein use hone wali core classification categories ka consolidated repository.",
    ),
    "master-records.body.1": (
        "It pulls rows from five separate registers — Asset Classes, Locations, Vendors, Document Types, and Cost Centres — into one table so you can see everything in one place. The two cards above switch which dataset the table shows: every record, or only the ones still awaiting a second-user approval.",
        "यह पाँच अलग-अलग रजिस्टरों — Asset Classes, Locations, Vendors, Document Types, और Cost Centres — से rows को खींचकर एक ही table में लाता है, ताकि आप सब कुछ एक जगह देख सकें। ऊपर दिए गए दोनों cards यह तय करते हैं कि table किस dataset को दिखाए: हर record, या केवल वे जो अभी भी second-user approval की प्रतीक्षा में हैं।",
        "हे पाच वेगवेगळ्या रजिस्टर्समधून — Asset Classes, Locations, Vendors, Document Types, आणि Cost Centres — rows एका table मध्ये आणते, जेणेकरून तुम्हाला सर्व काही एकाच ठिकाणी दिसेल. वरील दोन cards table कोणता dataset दाखवेल हे बदलतात: प्रत्येक record, किंवा फक्त तेच जे अजूनही second-user approval ची वाट पाहत आहेत.",
        "Ye paanch alag-alag registers — Asset Classes, Locations, Vendors, Document Types, aur Cost Centres — se rows khinch kar ek hi table mein le aata hai, taaki aap sab kuch ek jagah dekh sako. Upar diye gaye dono cards decide karte hain ki table kaunsa dataset dikhaye: har record, ya sirf wo jo abhi bhi second-user approval ka wait kar rahe hain.",
    ),
    "master-records.stepsHeading": (
        "Life of a master record",
        "एक मास्टर रिकॉर्ड का जीवनचक्र",
        "मास्टर रेकॉर्डचे जीवनचक्र",
        "Master record ki life",
    ),
    "master-records.steps.0.label": ("Create", "बनाएँ", "तयार करा", "Create"),
    "master-records.steps.0.caption": (
        "Added in its own register (e.g. Asset Classes, Vendor Masters)",
        "इसके अपने रजिस्टर में जोड़ा गया (जैसे Asset Classes, Vendor Masters)",
        "त्याच्या स्वतःच्या रजिस्टरमध्ये जोडले जाते (उदा. Asset Classes, Vendor Masters)",
        "Iske apne register mein add hota hai (jaise Asset Classes, Vendor Masters)",
    ),
    "master-records.steps.1.label": ("Pending", "लंबित", "प्रलंबित", "Pending"),
    "master-records.steps.1.caption": (
        'Listed under "Awaiting Dual Validation" here',
        'यहाँ "Awaiting Dual Validation" के अंतर्गत सूचीबद्ध',
        'येथे "Awaiting Dual Validation" अंतर्गत सूचीबद्ध',
        'Yahan "Awaiting Dual Validation" ke andar list hota hai',
    ),
    "master-records.steps.2.label": ("Approve", "स्वीकृत करें", "मंजूर करा", "Approve"),
    "master-records.steps.2.caption": (
        "A different user approves it in the Approval Queue",
        "Approval Queue में कोई दूसरा user इसे स्वीकृत करता है",
        "Approval Queue मध्ये दुसरा user ते मंजूर करतो",
        "Approval Queue mein koi doosra user ise approve karta hai",
    ),
    "master-records.steps.3.label": ("Active", "सक्रिय", "सक्रिय", "Active"),
    "master-records.steps.3.caption": (
        "Appears as Active in Total Master Records, usable elsewhere",
        "Total Master Records में Active के रूप में दिखाई देता है, कहीं और भी उपयोग योग्य",
        "Total Master Records मध्ये Active म्हणून दिसते, इतरत्रही वापरण्यायोग्य",
        "Total Master Records mein Active dikhta hai, aur kahin bhi use ho sakta hai",
    ),
    "master-records.fieldRules.0.name": ("Master Code", "मास्टर कोड", "मास्टर कोड", "Master Code"),
    "master-records.fieldRules.0.description": (
        "Code from the record's own register — unique within that register, not across all five.",
        "रिकॉर्ड के अपने रजिस्टर से कोड — उस रजिस्टर के भीतर अद्वितीय, सभी पाँचों में नहीं।",
        "रेकॉर्डच्या स्वतःच्या रजिस्टरमधील कोड — त्या रजिस्टरमध्ये अद्वितीय, सर्व पाचांमध्ये नाही.",
        "Record ke apne register se code — us register ke andar unique hota hai, sabhi paanch mein nahi.",
    ),
    "master-records.fieldRules.1.name": ("Master Type", "मास्टर प्रकार", "मास्टर प्रकार", "Master Type"),
    "master-records.fieldRules.1.description": (
        "Which of the five registers the row came from (asset_classes, locations, vendors, document_types, cost_centres).",
        "row पाँच रजिस्टरों में से किससे आई है (asset_classes, locations, vendors, document_types, cost_centres)।",
        "row पाच रजिस्टर्सपैकी कोणत्या रजिस्टरमधून आली आहे (asset_classes, locations, vendors, document_types, cost_centres).",
        "Row paanch registers mein se kaunse se aayi hai (asset_classes, locations, vendors, document_types, cost_centres).",
    ),
    "master-records.fieldRules.2.name": ("Status", "स्थिति", "स्थिती", "Status"),
    "master-records.fieldRules.2.description": (
        "Active/Inactive per that register's own flag. Records still awaiting approval only show under the Pending card, not as a status here.",
        "उस रजिस्टर के अपने flag के अनुसार Active/Inactive। जो records अभी भी approval की प्रतीक्षा में हैं, वे केवल Pending card के अंतर्गत दिखते हैं, यहाँ status के रूप में नहीं।",
        "त्या रजिस्टरच्या स्वतःच्या flag नुसार Active/Inactive. जे records अजूनही approval ची वाट पाहत आहेत ते फक्त Pending card अंतर्गत दिसतात, येथे status म्हणून नाही.",
        "Us register ke apne flag ke hisaab se Active/Inactive. Jo records abhi bhi approval ka wait kar rahe hain wo sirf Pending card ke andar dikhte hain, yahan status ke roop mein nahi.",
    ),
    "master-records.fieldRules.3.name": ("Last Updated", "अंतिम अपडेट", "शेवटचे अद्यतन", "Last Updated"),
    "master-records.fieldRules.3.description": (
        "Falls back from updated_at to last_updated to created_at, whichever the source register populates.",
        "updated_at से last_updated से created_at तक fallback करता है, जो भी source register भरता है।",
        "updated_at पासून last_updated पासून created_at पर्यंत fallback होते, जे काही source register भरते.",
        "updated_at se last_updated se created_at tak fallback karta hai, jo bhi source register populate karta hai.",
    ),
    "master-records.tip.title": (
        "The View/Edit icons here don't open anything",
        "यहाँ के View/Edit icons कुछ भी नहीं खोलते",
        "येथील View/Edit icons काहीही उघडत नाहीत",
        "Yahan ke View/Edit icons kuch bhi open nahi karte",
    ),
    "master-records.tip.body": (
        "This table is a rollup for reporting, not an editor. To actually change a record, go to its own register — Asset Classes, Location Masters, Vendor Masters, Document Masters, or Cost Centres — and edit it there.",
        "यह table reporting के लिए एक rollup है, editor नहीं। किसी record को वास्तव में बदलने के लिए, उसके अपने रजिस्टर — Asset Classes, Location Masters, Vendor Masters, Document Masters, या Cost Centres — पर जाएँ और वहाँ edit करें।",
        "हे table reporting साठी एक rollup आहे, editor नाही. एखादा record प्रत्यक्षात बदलण्यासाठी, त्याच्या स्वतःच्या रजिस्टरवर — Asset Classes, Location Masters, Vendor Masters, Document Masters, किंवा Cost Centres — जा आणि तिथे edit करा.",
        "Ye table reporting ke liye ek rollup hai, editor nahi. Kisi record ko actually change karne ke liye, uske apne register — Asset Classes, Location Masters, Vendor Masters, Document Masters, ya Cost Centres — par jaayein aur wahan edit 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 MASTER_RECORDS.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(MASTER_RECORDS)} master-records keys.")


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