"""One-off seed: populate translation_keys/translation_values for the "help"
namespace — the pilot content behind PageInfoButton's "i" icon on 5 pages
(Language Master, Dashboard, Warranty & Insurance, Vendor Contracts, Assign
Custodian). Keys reuse the flat page id already used by Sidebar.tsx's
menuItems / routePermissions.ts, so no new naming scheme is introduced.

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

SOURCE_TEXT = {
    "master-iso-language.title": "Language Master",
    "master-iso-language.body.0": "This page controls every language the app can show, and how much of the app is translated into each one.",
    "master-iso-language.body.1": "Add a new language, then either translate word-by-word or download an Excel sheet, fill it in, and upload it back.",
    "master-iso-language.body.2": "A language only appears in the top menu once someone marks it Active — and it can only be activated after it's 100% translated.",

    "dashboard.title": "Enterprise Dashboard",
    "dashboard.body.0": "This is your starting screen — a quick summary of assets, alerts, and activity across the organization.",
    "dashboard.body.1": "Use the cards and charts here to spot problems early, then drill into the relevant module for details.",

    "warranty-tracking.title": "Warranty & Insurance",
    "warranty-tracking.body.0": "Track every asset's warranty and insurance coverage in one place, including when each one expires.",
    "warranty-tracking.body.1": "File and follow up on insurance claims here, and see which assets currently have no active coverage.",

    "contracts.title": "Vendor Contracts",
    "contracts.body.0": "Manage every vendor contract — AMC, service, and master agreements — from one screen.",
    "contracts.body.1": "Track service-level agreements, invoices, and renewal dates, and log correspondence with each vendor.",

    "custody-assign.title": "Assign Custodian",
    "custody-assign.body.0": "Use this page to hand an asset over to the person who will be responsible for it.",
    "custody-assign.body.1": "Once assigned, that person becomes accountable for the asset until it's transferred or returned.",
}

VALUES_BY_LANG = {
    "hi": {
        "master-iso-language.title": "भाषा मास्टर",
        "master-iso-language.body.0": "यह पेज तय करता है कि ऐप्प किन-किन भाषाओं में दिख सकता है और हर भाषा में ऐप्प का कितना हिस्सा अनुवादित है।",
        "master-iso-language.body.1": "नई भाषा जोड़ें, फिर शब्द-दर-शब्द अनुवाद करें या Excel शीट डाउनलोड करके भरें और वापस अपलोड करें।",
        "master-iso-language.body.2": "कोई भाषा टॉप मेनू में तभी दिखती है जब उसे Active किया जाए — और यह तभी संभव है जब वह 100% अनुवादित हो।",

        "dashboard.title": "एंटरप्राइज़ डैशबोर्ड",
        "dashboard.body.0": "यह आपकी शुरुआती स्क्रीन है — संगठन भर की एसेट्स, अलर्ट और गतिविधि का त्वरित सारांश।",
        "dashboard.body.1": "समस्याओं को जल्दी पहचानने के लिए यहां के कार्ड और चार्ट्स का उपयोग करें, फिर विवरण के लिए संबंधित मॉड्यूल में जाएं।",

        "warranty-tracking.title": "वारंटी और बीमा",
        "warranty-tracking.body.0": "हर एसेट की वारंटी और बीमा कवरेज को एक ही जगह ट्रैक करें, जिसमें यह भी शामिल है कि कब कौन-सी समाप्त हो रही है।",
        "warranty-tracking.body.1": "यहां से बीमा दावे दर्ज करें और उनकी प्रगति देखें, और यह भी देखें कि किन एसेट्स पर फिलहाल कोई सक्रिय कवरेज नहीं है।",

        "contracts.title": "वेंडर अनुबंध",
        "contracts.body.0": "एक ही स्क्रीन से हर वेंडर अनुबंध — AMC, सर्विस और मास्टर एग्रीमेंट — प्रबंधित करें।",
        "contracts.body.1": "सर्विस-लेवल एग्रीमेंट, इनवॉइस और नवीनीकरण तिथियों को ट्रैक करें, और हर वेंडर के साथ पत्राचार दर्ज करें।",

        "custody-assign.title": "कस्टोडियन असाइन करें",
        "custody-assign.body.0": "किसी एसेट को उस व्यक्ति को सौंपने के लिए इस पेज का उपयोग करें जो उसकी ज़िम्मेदारी लेगा।",
        "custody-assign.body.1": "एक बार असाइन होने के बाद, वह व्यक्ति एसेट के ट्रांसफर या वापस होने तक उसके लिए जवाबदेह होता है।",
    },
}


async def seed_translations_help(session: AsyncSession) -> None:
    now = datetime.now(timezone.utc)
    key_ids = {}
    for key, source_text in SOURCE_TEXT.items():
        existing = (await session.execute(
            text("SELECT id FROM translation_keys WHERE namespace = :ns AND key = :key"),
            {"ns": NAMESPACE, "key": key},
        )).scalar_one_or_none()
        if existing:
            key_ids[key] = existing
            await session.execute(
                text("UPDATE translation_keys SET source_text = :src WHERE id = :id"),
                {"src": source_text, "id": existing},
            )
        else:
            new_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": new_id, "ns": NAMESPACE, "key": key, "src": source_text, "now": now},
            )
            key_ids[key] = new_id

    for lang_code, values in VALUES_BY_LANG.items():
        for key, value in values.items():
            key_id = key_ids[key]
            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},
                )

    await session.commit()


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:
                await seed_translations_help(session)
                print(f"  seeded help translations: {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.")


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