"""Backfill historical Feishu AI API/Account + BedRock approvals into Helix.

Source: the "AI API/Account Application(All)" Feishu export that IT ops
periodically hands us. Three sheets:
  - BedRock APply  → always api_key_assignments (AWS BedRock provider)
  - AI API/Account Application + AI Usage Application →
      * Scene starts with "Personal Advancement" → software_seat_allocation
      * Scene starts with "System Use"          → api_key_assignments

Only ``Approved`` rows are imported (Recalled/Rejected/Under Review are
skipped — they never became real grants).

Idempotency: every insert is keyed off the Feishu ``Request No.`` via
``employee_requests.request_number`` (UNIQUE). Re-running the script is a
no-op for rows that already landed.
"""
from __future__ import annotations

import argparse
import asyncio
import logging
from collections import Counter
from datetime import UTC, datetime
from decimal import Decimal, InvalidOperation
from pathlib import Path
from typing import Any

import openpyxl
from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession

# Register every model so SQLAlchemy mapper configuration works end-to-end.
import app.approval.models  # noqa: F401
import app.audit.models  # noqa: F401
import app.auth.models  # noqa: F401
import app.domains.apikey.models  # noqa: F401
import app.domains.asset.models  # noqa: F401
import app.domains.cmdb.models  # noqa: F401
import app.domains.email_group.models  # noqa: F401
import app.domains.hr.models  # noqa: F401
import app.domains.offboarding.models  # noqa: F401
import app.domains.permission.models  # noqa: F401
import app.domains.provisioning.models  # noqa: F401
import app.identity.person_models  # noqa: F401
from app.core.database import async_session_factory
from app.domains.apikey.models import ApiKeyAssignment, ApiKeyProvider
from app.domains.asset.models import (
    AssetSoftwareCatalog,
    EmployeeRequest,
    SoftwareLicensePlan,
    SoftwareSeatAllocation,
)
from app.identity.person_models import IdentityPerson

logger = logging.getLogger(__name__)


# ─── Excel → domain classification ────────────────────────────────────────

def _decimal(value: Any) -> Decimal:
    if value is None or value == "":
        return Decimal("0")
    if isinstance(value, (int, float, Decimal)):
        return Decimal(str(value))
    text = str(value).strip().replace(",", "").replace("USD", "").replace("$", "")
    try:
        return Decimal(text)
    except InvalidOperation:
        return Decimal("0")


def classify(row: dict[str, Any]) -> dict[str, Any] | None:
    if row.get("Status") != "Approved":
        return None
    title = row.get("Title")
    if title == "BedRock APply":
        return {"kind": "api_key", "provider_name": "AWS BedRock"}
    scene = (row.get("Scene:") or "").strip()
    source = (row.get("Source:") or "").strip()
    seat = (row.get("Seat") or "").strip()
    if scene.startswith("System Use"):
        prov = {
            "AWS Bedrock": "AWS BedRock",
            "Azure OpenAI": "Azure  OpenAI",
            "Qwen": "Qwen",
        }.get(source)
        if not prov:
            return None
        return {"kind": "api_key", "provider_name": prov}
    if scene.startswith("Personal Advancement"):
        if "Claude" in source:
            tier = "Premium" if "Premium" in seat else "Standard"
            return {"kind": "seat", "software_name": "Claude", "plan_name": tier}
        if "OpenAI" in source or "OPEN AI" in source:
            return {"kind": "seat", "software_name": "OPEN AI", "plan_name": "Team"}
    return None


def _reason_for_api_key(row: dict[str, Any]) -> str:
    scene = row.get("Scene:") or row.get("使用场景：")
    remarks = row.get("Remarks")
    parts = [p for p in (scene, remarks) if p]
    return "\n".join(str(p) for p in parts) or "(no description)"


def _reason_for_seat(row: dict[str, Any]) -> str:
    parts = []
    for k in ("Scene:", "project：", "Remarks"):
        v = row.get(k)
        if v:
            parts.append(f"{k} {v}")
    return "\n".join(parts) or "(no description)"


def _project_for_api_key(row: dict[str, Any]) -> str:
    # Prefer explicit project field, else fall back to Remarks, else "(backfill)".
    for k in ("project：", "Remarks"):
        v = row.get(k)
        if v:
            return str(v)[:250]
    scene = row.get("Scene:") or row.get("使用场景：")
    if scene:
        first_line = str(scene).splitlines()[0].strip()
        if first_line:
            return first_line[:250]
    return "(backfill)"


# ─── Person resolution ────────────────────────────────────────────────────

async def resolve_person(
    db: AsyncSession, *, employee_no: str | None, display_name: str | None,
) -> IdentityPerson | None:
    if employee_no:
        row = (await db.execute(
            select(IdentityPerson).where(IdentityPerson.employee_no == employee_no)
        )).scalar_one_or_none()
        if row is not None:
            return row
    if display_name:
        row = (await db.execute(
            select(IdentityPerson).where(IdentityPerson.display_name == display_name)
        )).scalar_one_or_none()
        if row is not None:
            return row
    return None


# ─── Catalog lookups ──────────────────────────────────────────────────────

async def _load_plan(
    db: AsyncSession, software_name: str, plan_name: str,
) -> SoftwareLicensePlan:
    sw = (await db.execute(
        select(AssetSoftwareCatalog).where(AssetSoftwareCatalog.name == software_name)
    )).scalar_one()
    # Plan name may have a trailing space in the catalog ("Team ").
    plans = (await db.execute(
        select(SoftwareLicensePlan).where(SoftwareLicensePlan.software_id == sw.id)
    )).scalars().all()
    for p in plans:
        if (p.name or "").strip() == plan_name:
            return p
    raise RuntimeError(f"No plan {plan_name!r} found under software {software_name!r}")


async def _load_provider(db: AsyncSession, name: str) -> ApiKeyProvider:
    prov = (await db.execute(
        select(ApiKeyProvider).where(ApiKeyProvider.name == name)
    )).scalar_one_or_none()
    if not prov:
        # Tolerate single/double space variation.
        prov = (await db.execute(
            select(ApiKeyProvider).where(
                ApiKeyProvider.name.ilike(name.replace("  ", "%"))
            )
        )).scalar_one()
    return prov


# ─── Row import ───────────────────────────────────────────────────────────

async def _import_api_key(
    db: AsyncSession,
    *,
    row: dict[str, Any],
    person: IdentityPerson,
    provider: ApiKeyProvider,
) -> str:
    request_number = str(row["Request No."])
    existing_er = (await db.execute(
        select(EmployeeRequest).where(EmployeeRequest.request_number == request_number)
    )).scalar_one_or_none()
    if existing_er:
        return "skip_existing"

    reason = _reason_for_api_key(row)
    project = _project_for_api_key(row)
    budget = _decimal(row.get("Monthly budget(USD)") or row.get("月度预算(USD)"))
    submitted_at = row.get("Submitted at")
    completed_at = row.get("Completed at")

    detail = {
        "source": "feishu_backfill",
        "feishu": {
            "request_no": request_number,
            "title": row.get("Title"),
            "submitted_at": submitted_at.isoformat() if isinstance(submitted_at, datetime) else submitted_at,
            "completed_at": completed_at.isoformat() if isinstance(completed_at, datetime) else completed_at,
            "scene": row.get("Scene:") or row.get("使用场景："),
            "source_value": row.get("Source:"),
            "remarks": row.get("Remarks"),
        },
        "api_key": {
            "provider_id": str(provider.id),
            "provider_name": provider.name,
            "project_name": project,
            "monthly_budget_usd": str(budget),
        },
    }

    er = EmployeeRequest(
        request_number=request_number,
        request_type="api_key_request",
        status="approved",
        requester_id=person.id,
        reason=reason,
        detail=detail,
        processed_at=completed_at if isinstance(completed_at, datetime) else None,
    )
    db.add(er)
    await db.flush()

    assignment = ApiKeyAssignment(
        provider_id=provider.id,
        assigned_to=person.id,
        status="approved",
        request_number=request_number,
        project_name=project,
        reason=reason,
        monthly_budget_usd=budget,
        employee_request_id=er.id,
        detail={
            "source": "feishu_backfill",
            "feishu": detail["feishu"],
        },
    )
    db.add(assignment)
    await db.flush()
    return "ok"


async def _import_seat(
    db: AsyncSession,
    *,
    row: dict[str, Any],
    person: IdentityPerson,
    plan: SoftwareLicensePlan,
) -> str:
    request_number = str(row["Request No."])
    existing_er = (await db.execute(
        select(EmployeeRequest).where(EmployeeRequest.request_number == request_number)
    )).scalar_one_or_none()
    if existing_er:
        return "skip_existing"

    reason = _reason_for_seat(row)
    submitted_at = row.get("Submitted at")
    completed_at = row.get("Completed at")

    software_name = plan.software.name if plan.software else None
    detail = {
        "source": "feishu_backfill",
        "feishu": {
            "request_no": request_number,
            "title": row.get("Title"),
            "submitted_at": submitted_at.isoformat() if isinstance(submitted_at, datetime) else submitted_at,
            "completed_at": completed_at.isoformat() if isinstance(completed_at, datetime) else completed_at,
            "scene": row.get("Scene:") or row.get("使用场景："),
            "source_value": row.get("Source:"),
            "seat_value": row.get("Seat"),
            "remarks": row.get("Remarks"),
            "project": row.get("project："),
        },
        "software_seat": {
            "software_id": str(plan.software_id),
            "software_name": software_name,
            "plan_id": str(plan.id),
            "plan_name": plan.name,
        },
    }

    er = EmployeeRequest(
        request_number=request_number,
        request_type="software_seat",
        status="approved",
        requester_id=person.id,
        reason=reason,
        detail=detail,
        processed_at=completed_at if isinstance(completed_at, datetime) else None,
    )
    db.add(er)
    await db.flush()

    allocation = SoftwareSeatAllocation(
        plan_id=plan.id,
        user_id=person.id,
        status="active",
        reason=reason,
        allocated_at=completed_at if isinstance(completed_at, datetime) else datetime.now(UTC),
    )
    db.add(allocation)
    await db.flush()
    return "ok"


# ─── Driver ───────────────────────────────────────────────────────────────

def load_rows(xlsx: Path) -> list[dict[str, Any]]:
    wb = openpyxl.load_workbook(xlsx, data_only=True)
    out: list[dict[str, Any]] = []
    for sn in wb.sheetnames:
        ws = wb[sn]
        headers = [c.value for c in ws[2]]
        for r in ws.iter_rows(min_row=3, values_only=True):
            if not any(r):
                continue
            d = dict(zip(headers, r))
            d["_sheet"] = sn
            out.append(d)
    return out


async def run(xlsx: Path, *, dry_run: bool) -> None:
    rows = load_rows(xlsx)
    approved = [r for r in rows if r.get("Status") == "Approved"]
    stats: Counter[str] = Counter()
    missing_people: list[str] = []

    async with async_session_factory() as session:
        # Preload catalog lookups so we hit the DB once each.
        plan_cache: dict[tuple[str, str], SoftwareLicensePlan] = {}
        provider_cache: dict[str, ApiKeyProvider] = {}

        for row in approved:
            cls = classify(row)
            if cls is None:
                stats["unclassified"] += 1
                continue
            person = await resolve_person(
                session,
                employee_no=row.get("Requester's Employee No.") or None,
                display_name=row.get("Requester's name") or None,
            )
            if person is None:
                stats["missing_person"] += 1
                nm = row.get("Requester's name")
                missing_people.append(f"{row.get('Request No.')} {nm}")
                continue

            if cls["kind"] == "api_key":
                name = cls["provider_name"]
                prov = provider_cache.get(name)
                if prov is None:
                    prov = await _load_provider(session, name)
                    provider_cache[name] = prov
                outcome = await _import_api_key(
                    session, row=row, person=person, provider=prov,
                )
                stats[f"api_key.{outcome}"] += 1
            elif cls["kind"] == "seat":
                key = (cls["software_name"], cls["plan_name"])
                plan = plan_cache.get(key)
                if plan is None:
                    plan = await _load_plan(session, *key)
                    plan_cache[key] = plan
                outcome = await _import_seat(
                    session, row=row, person=person, plan=plan,
                )
                stats[f"seat.{outcome}"] += 1

        if dry_run:
            await session.rollback()
            print("[DRY-RUN] Rolled back; no changes persisted.")
        else:
            await session.commit()
            print("[COMMIT] Changes persisted.")

    print("\n=== Summary ===")
    for k, v in sorted(stats.items()):
        print(f"  {k}: {v}")
    if missing_people:
        print("\n!!! Missing people (not backfilled):")
        for m in missing_people:
            print(f"  {m}")


def _main() -> None:
    logging.basicConfig(level=logging.INFO, format="%(asctime)s %(levelname)s %(message)s")
    ap = argparse.ArgumentParser()
    ap.add_argument(
        "--xlsx",
        default="/Users/ucjmh/Downloads/AI API_Account  Application(All)20260422154329.xlsx",
    )
    ap.add_argument("--dry-run", action="store_true")
    ap.add_argument("--commit", action="store_true", help="persist changes")
    args = ap.parse_args()
    dry = args.dry_run or not args.commit
    asyncio.run(run(Path(args.xlsx), dry_run=dry))


if __name__ == "__main__":
    _main()