"""One-off backfill: copy each tenant's old public.branding_settings row into
their own schema (new per-tenant branding_settings table + system_parameters
DEFAULT_CURRENCY/DATE_FORMAT/TIMEZONE), before public.branding_settings is
dropped by migration 0101.

Run once, after `alembic upgrade head` applies migration 0100 (which creates
the target tables) and before migration 0101 (which drops the source table).

Usage: docker compose exec api python backfill_branding_to_tenant_schemas.py
"""
import asyncio
import uuid

from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession, async_sessionmaker, create_async_engine

from ams.core.config import settings

# Fallback values when a tenant never customized branding (mirrors
# ams/services/branding.py's DEFAULTS at the time this script was written —
# not imported, so this script keeps working even after that module drops
# these 3 keys).
FALLBACK_CURRENCY = "INR"
FALLBACK_DATE_FORMAT = "DD/MM/YYYY"
FALLBACK_TIMEZONE = "Asia/Kolkata"

DISPLAY_KEYS = {
    "currency_code": ("DEFAULT_CURRENCY", FALLBACK_CURRENCY),
    "date_format": ("DATE_FORMAT", FALLBACK_DATE_FORMAT),
    "timezone": ("TIMEZONE", FALLBACK_TIMEZONE),
}

BRANDING_COLUMNS = (
    "primary_color", "secondary_color", "button_color", "logo_url", "favicon_url",
    "login_title", "app_title", "app_subtitle", "version_label", "powered_by_text",
)


async def backfill_tenant(session: AsyncSession, schema: str, branding_row: dict | None) -> None:
    await session.execute(text(f"SET search_path TO {schema}, public"))

    for old_col, (param_key, fallback) in DISPLAY_KEYS.items():
        value = (branding_row or {}).get(old_col) or fallback
        await session.execute(
            text("""
                INSERT INTO system_parameters (id, key, value, description, is_editable)
                VALUES (:id, :key, :val, :desc, true)
                ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value
            """),
            {
                "id": str(uuid.uuid4()), "key": param_key, "val": value,
                "desc": f"Backfilled from legacy branding_settings.{old_col}",
            },
        )

    if branding_row:
        existing = (await session.execute(text("SELECT id FROM branding_settings LIMIT 1"))).fetchone()
        if not existing:
            params = {col: branding_row.get(col) for col in BRANDING_COLUMNS}
            params["id"] = str(uuid.uuid4())
            await session.execute(
                text(f"""
                    INSERT INTO branding_settings
                        (id, {", ".join(BRANDING_COLUMNS)}, created_at, updated_at)
                    VALUES
                        (:id, {", ".join(f":{c}" for c in BRANDING_COLUMNS)}, NOW(), NOW())
                """),
                params,
            )

    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()

        branding_rows = {
            row.tenant_id: dict(row._mapping)
            for row in (await session.execute(text(f"""
                SELECT tenant_id::text AS tenant_id, {", ".join(BRANDING_COLUMNS)},
                       currency_code, date_format, timezone
                FROM public.branding_settings
            """))).fetchall()
        }

    for tenant in tenants:
        schema = f"tenant_{tenant.id.replace('-', '_')}"
        branding_row = branding_rows.get(tenant.id)
        async with Session() as session:
            await backfill_tenant(session, schema, branding_row)
        print(f"  backfilled: {tenant.slug} ({'customized branding' if branding_row else 'defaults'})")

    await engine.dispose()
    print(f"Done — {len(tenants)} tenant(s) processed.")


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