"""One-off seed: registers translation_keys English source_text and hi/mr/hinglish
translation_values for the shared DataTable/M3Modal UI chrome strings
(search box, export buttons, pagination footer, close button) — these are
rendered on virtually every list/table page in the app via the `app`
namespace's "dataTable.*" key prefix.

Usage: docker compose exec api python seed_translations_datatable_chrome.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 = "app"

# key -> (english, hi, mr, hinglish)
DATATABLE_CHROME = {
    "dataTable.close": ("Close", "बंद करें", "बंद करा", "Close"),
    "dataTable.search": ("Search...", "खोजें...", "शोधा...", "Search..."),
    "dataTable.searchAll": ("Search all records...", "सभी रिकॉर्ड खोजें...", "सर्व रेकॉर्ड्स शोधा...", "Sabhi records search karein..."),
    "dataTable.copy": ("Copy", "कॉपी", "कॉपी", "Copy"),
    "dataTable.copyAllTitle": (
        "Copy all matching rows to clipboard",
        "सभी मिलान वाली rows को क्लिपबोर्ड में कॉपी करें",
        "सर्व जुळणाऱ्या rows क्लिपबोर्डमध्ये कॉपी करा",
        "Sabhi matching rows ko clipboard mein copy karein",
    ),
    "dataTable.copyFilteredTitle": (
        "Copy visible/filtered rows to clipboard",
        "दिखाई देने वाली/फ़िल्टर की गई rows को क्लिपबोर्ड में कॉपी करें",
        "दृश्यमान/फिल्टर केलेल्या rows क्लिपबोर्डमध्ये कॉपी करा",
        "Visible/filtered rows ko clipboard mein copy karein",
    ),
    "dataTable.copySuccess": ("Table copied to clipboard", "टेबल क्लिपबोर्ड में कॉपी हो गई", "टेबल क्लिपबोर्डमध्ये कॉपी झाली", "Table clipboard mein copy ho gayi"),
    "dataTable.copyFailure": ("Failed to copy table", "टेबल कॉपी करने में विफल", "टेबल कॉपी करण्यात अयशस्वी", "Table copy karne mein fail ho gaya"),
    "dataTable.excel": ("Excel", "एक्सेल", "एक्सेल", "Excel"),
    "dataTable.excelTitle": (
        "Download as Excel-compatible CSV",
        "Excel-compatible CSV के रूप में डाउनलोड करें",
        "Excel-compatible CSV म्हणून डाउनलोड करा",
        "Excel-compatible CSV ke roop mein download karein",
    ),
    "dataTable.pdf": ("PDF", "पीडीएफ", "पीडीएफ", "PDF"),
    "dataTable.pdfTitle": ("Download as PDF", "PDF के रूप में डाउनलोड करें", "PDF म्हणून डाउनलोड करा", "PDF ke roop mein download karein"),
    "dataTable.filterByColumn": (
        "Filter by {{column}}",
        "{{column}} के अनुसार फ़िल्टर करें",
        "{{column}} नुसार फिल्टर करा",
        "{{column}} ke hisaab se filter karein",
    ),
    "dataTable.filterPlaceholder": ("Filter...", "फ़िल्टर करें...", "फिल्टर करा...", "Filter..."),
    "dataTable.loading": ("Loading...", "लोड हो रहा है...", "लोड होत आहे...", "Loading ho raha hai..."),
    "dataTable.noRecords": ("No records found.", "कोई रिकॉर्ड नहीं मिला।", "कोणतेही रेकॉर्ड सापडले नाही.", "Koi record nahi mila."),
    "dataTable.show": ("Show", "दिखाएं", "दाखवा", "Show"),
    "dataTable.entries": ("entries", "प्रविष्टियाँ", "नोंदी", "entries"),
    "dataTable.max100": ("Max (100)", "अधिकतम (100)", "कमाल (100)", "Max (100)"),
    "dataTable.all": ("All", "सभी", "सर्व", "All"),
    "dataTable.showing": ("Showing", "दिखा रहे हैं", "दाखवत आहे", "Showing"),
    "dataTable.of": ("of", "में से", "पैकी", "mein se"),
    "dataTable.filteredFrom": (
        "(filtered from {{count}})",
        "({{count}} में से फ़िल्टर किया गया)",
        "({{count}} पैकी फिल्टर केलेले)",
        "({{count}} mein se filter kiya gaya)",
    ),
    "dataTable.first": ("First", "पहला", "पहिले", "First"),
    "dataTable.prev": ("Prev", "पिछला", "मागील", "Prev"),
    "dataTable.next": ("Next", "अगला", "पुढील", "Next"),
    "dataTable.last": ("Last", "आखिरी", "शेवटचे", "Last"),
}


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 DATATABLE_CHROME.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(DATATABLE_CHROME)} dataTable-chrome keys.")


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