"""Backfill employee_requests rows for existing api_key_assignments.

Step 2 of the request unification: after ApiKeyService starts dual-writing
the shared ``employee_requests`` row on submit, any pre-existing
``api_key_assignments`` created before the deploy still have
``employee_request_id IS NULL``. This migration creates matching
``employee_requests`` rows for those and links them.

Idempotent: scoped strictly to rows where the FK is still null, so
re-running is a no-op.

Revision ID: 031
Revises: 030
Create Date: 2026-04-19

"""
import json
import uuid
from collections.abc import Sequence
from datetime import UTC, datetime

import sqlalchemy as sa
from alembic import op

revision: str = "031"
down_revision: str | Sequence[str] | None = "030"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    bind = op.get_bind()

    # Pull assignments that still need a mirror row.
    rows = bind.execute(
        sa.text(
            """
            SELECT id, request_number, assigned_to, status, reason, detail,
                   provider_id, project_name, monthly_budget_usd,
                   valid_from, valid_until, created_at
              FROM api_key_assignments
             WHERE employee_request_id IS NULL
            """
        )
    ).fetchall()

    now = datetime.now(UTC)

    for row in rows:
        # Map assignment lifecycle status → EmployeeRequest status vocabulary.
        # api_key_assignments uses pending_approval / approved / active /
        # rejected / revoked / expired; employee_requests uses pending /
        # approved / rejected / etc. Collapse active → approved because the
        # underlying approval decision is the same.
        asgn_status = (row.status or "").lower()
        if asgn_status in ("pending_approval", "pending"):
            er_status = "pending"
        elif asgn_status in ("approved", "active"):
            er_status = "approved"
        elif asgn_status == "rejected":
            er_status = "rejected"
        else:
            er_status = asgn_status or "pending"

        # Preserve feishu sub-detail (instance_code + approval_code) so the
        # unified listing shows the Feishu approval badge.
        detail_src = row.detail
        if isinstance(detail_src, (bytes, str)):
            try:
                detail_src = json.loads(detail_src)
            except (TypeError, ValueError):
                detail_src = {}
        if not isinstance(detail_src, dict):
            detail_src = {}
        feishu = detail_src.get("feishu") or {}

        er_detail = {
            "submitted_answers": detail_src.get("submitted_answers") or {},
            "feishu": feishu,
            "api_key": {
                "provider_id": str(row.provider_id) if row.provider_id else None,
                "project_name": row.project_name,
                "monthly_budget_usd": (
                    str(row.monthly_budget_usd)
                    if row.monthly_budget_usd is not None
                    else None
                ),
                "valid_from": row.valid_from.isoformat() if row.valid_from else None,
                "valid_until": row.valid_until.isoformat() if row.valid_until else None,
            },
        }

        new_id = str(uuid.uuid4())
        bind.execute(
            sa.text(
                """
                INSERT INTO employee_requests
                  (id, request_number, request_type, status, requester_id,
                   reason, detail, created_at, processed_at)
                VALUES
                  (:id, :req_no, 'api_key_request', :status, :requester_id,
                   :reason, :detail, :created_at,
                   CASE WHEN :status IN ('approved','rejected') THEN :now ELSE NULL END)
                """
            ),
            {
                "id": new_id,
                "req_no": row.request_number,
                "status": er_status,
                "requester_id": str(row.assigned_to),
                "reason": row.reason,
                "detail": json.dumps(er_detail, ensure_ascii=False),
                "created_at": row.created_at,
                "now": now,
            },
        )
        bind.execute(
            sa.text(
                "UPDATE api_key_assignments SET employee_request_id = :erid WHERE id = :aid"
            ),
            {"erid": new_id, "aid": row.id},
        )


def downgrade() -> None:
    # Remove the backfill-created mirror rows. We identify them by the
    # pointer we wrote on the assignment side, so the downgrade is exact.
    op.execute(
        """
        DELETE er FROM employee_requests er
        JOIN api_key_assignments aka ON aka.employee_request_id = er.id
        WHERE er.request_type = 'api_key_request'
        """
    )
    op.execute(
        "UPDATE api_key_assignments SET employee_request_id = NULL "
        "WHERE employee_request_id IS NOT NULL"
    )