"""Backfill ``detail.buyback_amount`` on historical buyback rows.

The hardware-request page now renders a 回购金额 column and a by-currency
buyback total, both reading the first-class ``detail.buyback_amount``
key that submits stamp as of 2026-07-03. Historical
``offboarding_buyback`` / ``laptop_replacement`` rows carry the figure
only inside widget snapshots (``detail.feishu.form_snapshot`` /
``submitted_form``) — unreachable from SQL aggregation. Stamp them once
here (pure Python extraction, few hundred rows) so the summary numbers
are complete from day one.

Idempotent: rows that already have the key are skipped; rows with no
amount widget (no-buyback laptop replacements) are left untouched.

Revision ID: 379
Revises: 378
"""
import json

import sqlalchemy as sa

from alembic import op

revision = "379"
down_revision = "378"
branch_labels = None
depends_on = None

_BUYBACK_TYPES = ("offboarding_buyback", "laptop_replacement")


def _parse_maybe_json(value):
    if isinstance(value, str):
        text = value.strip()
        if text.startswith("{") or text.startswith("["):
            try:
                return json.loads(text)
            except (TypeError, ValueError):
                return value
    return value


def _norm_amount(value):
    value = _parse_maybe_json(value)
    if not isinstance(value, dict):
        return None
    amount = value.get("amount")
    if amount in (None, ""):
        return None
    return {"amount": str(amount), "currency": str(value.get("currency") or "")}


def _extract(detail: dict):
    """Mirror of ``ApprovalService.extract_buyback_amount`` fallbacks."""
    found = _norm_amount(detail.get("buyback_amount"))
    if found:
        return found
    feishu = detail.get("feishu") or {}
    snapshot = feishu.get("form_snapshot")
    if isinstance(snapshot, list):
        for widget in snapshot:
            if isinstance(widget, dict) and widget.get("type") == "amount":
                found = _norm_amount(widget.get("answer"))
                if found:
                    return found
    submitted = _parse_maybe_json(feishu.get("submitted_form"))
    if isinstance(submitted, list):
        for widget in submitted:
            if isinstance(widget, dict) and widget.get("type") == "amount":
                found = _norm_amount(widget.get("value"))
                if found:
                    return found
    # Last resort: the live-instance snapshot cached by detail opens /
    # callbacks. Feishu ships ``form`` as a JSON string.
    instance = feishu.get("latest_instance_detail")
    if isinstance(instance, dict):
        form = _parse_maybe_json(instance.get("form"))
        if isinstance(form, list):
            for widget in form:
                if isinstance(widget, dict) and widget.get("type") == "amount":
                    found = _norm_amount(widget.get("value"))
                    if found:
                        return found
    return None


def upgrade() -> None:
    bind = op.get_bind()
    rows = bind.execute(sa.text(
        """
        SELECT id, detail FROM employee_requests
        WHERE request_type IN :types
          AND JSON_EXTRACT(detail, '$.buyback_amount.amount') IS NULL
        """
    ).bindparams(sa.bindparam("types", expanding=True)), {"types": list(_BUYBACK_TYPES)}).fetchall()

    stamped = 0
    for row_id, detail_raw in rows:
        try:
            detail = json.loads(detail_raw) if isinstance(detail_raw, (str, bytes)) else (detail_raw or {})
        except (TypeError, ValueError):
            continue
        if not isinstance(detail, dict):
            continue
        found = _extract(detail)
        if not found:
            continue
        bind.execute(sa.text(
            """
            UPDATE employee_requests
            SET detail = JSON_SET(detail, '$.buyback_amount',
                                  CAST(:blob AS JSON))
            WHERE id = :id
            """
        ), {"blob": json.dumps(found, ensure_ascii=True), "id": row_id})
        stamped += 1
    print(f"[379] buyback_amount backfilled on {stamped}/{len(rows)} candidate rows")


def downgrade() -> None:
    # The stamped key is derived data; removing it is safe.
    op.get_bind().execute(sa.text(
        """
        UPDATE employee_requests
        SET detail = JSON_REMOVE(detail, '$.buyback_amount')
        WHERE request_type IN ('offboarding_buyback', 'laptop_replacement')
        """
    ))