"""Backfill the historical Approved AI Tools list into ai_tool_applications.

Source: ``Approved AI Tools.xlsx`` exported from the Feishu approval
center (the manually-maintained pre-Helix log of every AI function /
product introduction request). Sidecar JSON
``106_seed_approved_ai_tools.json`` next to this file holds the rows.

The seed is idempotent: it skips rows whose ``source_id`` already
exists. Both local dev and TiDB Cloud run the same alembic upgrade
chain, so both environments end up with the same baseline once this
migration applies (the user explicitly asked for "生产和线下都补上").

Person attribution is best-effort: we look up ``identity_persons`` by
display_name; if no match, ``person_id`` stays NULL and the public
listing falls back to ``legacy_applicant_name`` /
``legacy_applicant_department``. New live submissions populate
``person_id`` directly and leave the legacy columns NULL.

Revision ID: 108
"""
from __future__ import annotations

import json
import logging
import uuid
from datetime import datetime
from pathlib import Path

import sqlalchemy as sa
from alembic import op


revision = "108"
down_revision = "107"
branch_labels = None
depends_on = None


logger = logging.getLogger(__name__)


_SEED_FILE = Path(__file__).with_suffix(".json")


def _parse_dt(value: str | None) -> datetime | None:
    if not value:
        return None
    return datetime.fromisoformat(value)


def _resolve_person_id(bind, display_name: str | None) -> str | None:
    """Best-effort lookup: if exactly one identity_persons row matches
    the Chinese display_name, link the seed row to it. Multiple matches
    or no match → leave NULL and rely on legacy_applicant_name."""
    if not display_name:
        return None
    rows = bind.execute(
        sa.text(
            "SELECT id FROM identity_persons "
            "WHERE display_name = :n "
            "LIMIT 2"
        ),
        {"n": display_name.strip()},
    ).fetchall()
    if len(rows) != 1:
        return None
    return str(rows[0][0])


def upgrade() -> None:
    if not _SEED_FILE.exists():
        logger.warning(
            "AI tools seed file missing at %s — skipping backfill",
            _SEED_FILE,
        )
        return

    raw = _SEED_FILE.read_text(encoding="utf-8")
    rows = json.loads(raw)
    bind = op.get_bind()

    # source_id is the dedup key for re-runnable seed; new rows get a
    # generated UUID, existing rows are left untouched.
    existing = {
        r[0]
        for r in bind.execute(
            sa.text(
                "SELECT source_id FROM ai_tool_applications "
                "WHERE source_id IS NOT NULL"
            )
        ).fetchall()
    }

    inserted = 0
    skipped = 0
    table = sa.table(
        "ai_tool_applications",
        sa.column("id", sa.String(36)),
        sa.column("person_id", sa.String(36)),
        sa.column("ai_name", sa.String(255)),
        sa.column("how_to_use", sa.String(255)),
        sa.column("data_level", sa.String(64)),
        sa.column("purpose", sa.Text),
        sa.column("data_type", sa.Text),
        sa.column("privacy_policy", sa.Text),
        sa.column("attachment_note", sa.String(255)),
        sa.column("status", sa.String(32)),
        sa.column("granted_at", sa.DateTime(timezone=True)),
        sa.column("legacy_request_no", sa.String(32)),
        sa.column("legacy_applicant_name", sa.String(128)),
        sa.column("legacy_applicant_department", sa.String(128)),
        sa.column("source_id", sa.String(255)),
        sa.column("created_at", sa.DateTime(timezone=True)),
        sa.column("updated_at", sa.DateTime(timezone=True)),
    )

    for row in rows:
        source_id = row.get("source_id")
        if source_id and source_id in existing:
            skipped += 1
            continue
        person_id = _resolve_person_id(bind, row.get("legacy_applicant_name"))
        op.bulk_insert(
            table,
            [
                {
                    "id": str(uuid.uuid4()),
                    "person_id": person_id,
                    "ai_name": row["ai_name"],
                    "how_to_use": row["how_to_use"],
                    "data_level": row["data_level"],
                    "purpose": row["purpose"],
                    "data_type": row["data_type"],
                    "privacy_policy": row["privacy_policy"],
                    "attachment_note": row.get("attachment_note"),
                    "status": row["status"],
                    "granted_at": _parse_dt(row.get("granted_at")),
                    "legacy_request_no": row.get("legacy_request_no"),
                    "legacy_applicant_name": row.get("legacy_applicant_name"),
                    "legacy_applicant_department":
                        row.get("legacy_applicant_department"),
                    "source_id": source_id,
                    "created_at":
                        _parse_dt(row.get("created_at")) or datetime.utcnow(),
                    "updated_at":
                        _parse_dt(row.get("updated_at")) or datetime.utcnow(),
                }
            ],
        )
        inserted += 1

    logger.info(
        "Approved AI Tools backfill: inserted=%d skipped=%d total_seed=%d",
        inserted, skipped, len(rows),
    )


def downgrade() -> None:
    # Remove only the rows we own (those with a source_id from the
    # seed file). Anything submitted live afterwards has source_id
    # NULL and survives.
    if not _SEED_FILE.exists():
        return
    rows = json.loads(_SEED_FILE.read_text(encoding="utf-8"))
    source_ids = [r["source_id"] for r in rows if r.get("source_id")]
    if not source_ids:
        return
    op.execute(
        sa.text(
            "DELETE FROM ai_tool_applications WHERE source_id IN :ids"
        ).bindparams(sa.bindparam("ids", expanding=True)),
        {"ids": source_ids},
    )