"""Seed the complete managed ``asset_locations`` set the location
resolver depends on.

Context: ``asset_items.location`` is a free-text column. At claim
time the frontend defaults the value from
``identity_persons.work_location_name`` (a SuccessFactors HRIS field
that carries city-level values worldwide). When that raw value
doesn't match an ``asset_locations.name`` row, the asset list page's
location-filter dropdown can't surface the asset, AND the operations-
log ``Imported from 易盘点`` etc. backfill scripts keep recreating
city/state-level strings the dropdown doesn't list.

The accompanying code change introduces a write-time resolver
(``location_resolver.py``) that maps every raw value to a canonical
managed name OR ``None``. The normalization map only succeeds when
the **normalized result** also exists in the active master — so the
migration MUST ship every canonical name the resolver's map points
at, or fresh deploys (CI test DB, new envs) would silently degrade
``Santa Clara → 美国 → None`` because ``美国`` is missing.

Prod already carries the 16 historical rows (北京 / 上海 / 美国 / 日本
/ 新加坡 / 马来西亚 / 香港 / 印度 / 荷兰 / 德国 / IDC机房 / remote / 国内
/ ...) plus the 5 rows the 2026-05-30 SQL backfill added (南京 / 长沙
/ 郑州 / 沈阳 / 德国). The natural-key probe below makes every INSERT
a no-op for any row that already exists, so re-running on prod is
safe. The migration is the single source of truth for the master
set the resolver normalization map references; new envs come up with
the full set in one step.

No data backfill of existing ``asset_items.location`` rows happens
here — that was already done prior to this PR via two transactional
SQL passes (trace_ids ``f63a1c64-…`` and ``f5ebbdd4-…``).

Revision ID: 226
Revises: 225
Create Date: 2026-05-30
"""
from __future__ import annotations

import logging
import uuid

from sqlalchemy import text

from alembic import op

revision = "226"
down_revision = "225"
branch_labels = None
depends_on = None


_logger = logging.getLogger("alembic.runtime.migration")


# (code, name, sort_order)
# Codes follow the existing asset_locations convention:
#   * P-CNxx for China PingCAP main offices (sequential).
#   * P-XX for country-level rows that existed prior to 2026-05-12
#     (legacy 14-row seed shipped with the original M2 deploy).
#   * 2-char ISO-3166 alpha-2 code for everything added post 2026-05-12
#     (IN / NL / DE / and this PR's new countries).
# Names are the Chinese country / city names referenced by the
# resolver normalization map in ``location_resolver.py``. The
# migration is idempotent — every row uses a code-OR-name natural-key
# probe so prod's pre-existing 21 rows skip cleanly while a fresh DB
# picks up the complete set in one pass.
_NEW_LOCATIONS: list[tuple[str, str, int]] = [
    # ── Legacy 14-row seed (was created manually pre-migration-226).
    # Listed here so fresh deploys come up with the complete master
    # set out of the box. Prod skips these via the natural-key probe.
    ("P-CN03", "北京", 1),
    ("P-CN04", "成都", 2),
    ("P-CN06", "杭州", 3),
    ("P-CN05", "广州", 4),
    ("P-CN07", "上海", 5),
    ("P-CN08", "深圳", 6),
    ("P-US", "美国", 7),
    ("P-JP", "日本", 8),
    ("P-SG", "新加坡", 9),
    ("P-MY", "马来西亚", 10),
    ("P-CN01", "IDC机房", 11),
    ("P-CN02", "remote", 12),
    ("P-CN", "国内", 13),
    ("P-HK", "香港", 14),
    # ── Post-2026-05-12 additions (also created via manual SQL).
    ("IN", "印度", 15),
    ("NL", "荷兰", 16),
    # ── 2026-05-30 SQL backfill rows (data fix prior to this PR).
    ("P-CN09", "南京", 17),
    ("P-CN10", "长沙", 18),
    ("P-CN11", "郑州", 19),
    ("P-CN12", "沈阳", 20),
    ("DE", "德国", 21),
    # ── This PR's new rows: 3 PingCAP China cities + 13 countries
    # covering every value in ``identity_persons.work_location_name``
    # that the resolver normalization map points at.
    ("P-CN13", "武汉", 22),
    ("P-CN14", "大连", 23),
    ("P-CN15", "宁波", 24),
    ("AU", "澳大利亚", 25),
    ("CA", "加拿大", 26),
    ("ID", "印度尼西亚", 27),
    ("IE", "爱尔兰", 28),
    ("TW", "台湾", 29),
    ("TH", "泰国", 30),
    ("KR", "韩国", 31),
    ("AE", "阿联酋", 32),
    ("VN", "越南", 33),
    ("PL", "波兰", 34),
    ("GB", "英国", 35),
    ("IT", "意大利", 36),
    ("RO", "罗马尼亚", 37),
]


def upgrade() -> None:
    conn = op.get_bind()
    inserted = 0
    skipped = 0

    for code, name, sort_order in _NEW_LOCATIONS:
        # Natural-key probe: ``code`` is a UNIQUE column on
        # ``asset_locations`` (model definition), and ``name`` is
        # treated as a logical unique by the resolver (the master is
        # keyed off it). Skip when either is already taken so re-runs
        # are no-ops.
        already = conn.execute(
            text(
                "SELECT id FROM asset_locations "
                "WHERE code = :code OR name = :name LIMIT 1"
            ),
            {"code": code, "name": name},
        ).first()
        if already is not None:
            skipped += 1
            _logger.info(
                "asset_locations seed skip: %s/%s already exists",
                code, name,
            )
            continue

        conn.execute(
            text(
                "INSERT INTO asset_locations "
                "(id, code, name, sort_order, is_active, "
                " created_at, updated_at) "
                "VALUES (:id, :code, :name, :sort_order, 1, "
                "        NOW(6), NOW(6))"
            ),
            {
                "id": str(uuid.uuid4()),
                "code": code,
                "name": name,
                "sort_order": sort_order,
            },
        )
        inserted += 1

    _logger.info(
        "asset_locations seed complete: inserted=%d skipped=%d",
        inserted, skipped,
    )


def downgrade() -> None:
    # The new rows are purely additive metadata. Production may have
    # backfilled ``asset_items.location`` to point at some of these new
    # rows (e.g. ``德国`` for ``Würzburg``); dropping them via a
    # downgrade would orphan those rows. We refuse the downgrade
    # explicitly to make the irreversibility visible.
    #
    # ── ROLLBACK PROCEDURE (manual, requires DBA verification) ─────
    #
    # If a true rollback of this PR is required:
    #
    # 1. Identify which new master rows are referenced by asset_items:
    #
    #      SELECT al.code, al.name, COUNT(ai.id) AS asset_count
    #      FROM asset_locations al
    #      LEFT JOIN asset_items ai
    #        ON ai.location = al.name AND ai.deleted_at IS NULL
    #      WHERE al.code IN ('P-CN13','P-CN14','P-CN15',
    #                        'AU','CA','ID','IE','TW','TH','KR',
    #                        'AE','VN','PL','GB','IT','RO')
    #      GROUP BY al.code, al.name;
    #
    # 2. For each row with ``asset_count > 0``: pick a replacement
    #    canonical name from the surviving master, and UPDATE
    #    ``asset_items.location`` (write an ``audit_logs`` entry per
    #    asset for traceability) BEFORE deleting the master row.
    #
    # 3. For each row with ``asset_count = 0``: safe to delete
    #    directly. Use a single transaction:
    #
    #      START TRANSACTION;
    #      DELETE FROM asset_locations
    #      WHERE code IN ('P-CN13','P-CN14','P-CN15',
    #                     'AU','CA','ID','IE','TW','TH','KR',
    #                     'AE','VN','PL','GB','IT','RO')
    #        AND id NOT IN (
    #          SELECT DISTINCT al.id FROM asset_locations al
    #          JOIN asset_items ai ON ai.location = al.name
    #          WHERE ai.deleted_at IS NULL
    #        );
    #      COMMIT;
    #
    # 4. The 21 *legacy* rows (北京 / 美国 / IDC机房 / ... — sort_order
    #    1-21) MUST NOT be dropped on rollback. They predate this PR
    #    and are still required by every other location-aware flow.
    raise NotImplementedError(
        "Migration 226 (seed managed locations world) is irreversible "
        "via Alembic. See the docstring above for the manual rollback "
        "procedure: verify each new master row's reference count via "
        "asset_items.location JOIN, reassign references first, then "
        "DELETE the unreferenced rows in a single transaction. The "
        "legacy 21-row seed (sort_order 1-21) MUST NOT be dropped."
    )