"""Inject travelerApprover rule into existing kind='navan' targets.

Revision ID: 203
Revises: 202
Create Date: 2026-05-26

Background:
PingCAP's Navan tenant approves T&E by subsidiary. US-entity employees
route to nancy.wu@pingcap.com; everyone else falls back to
qiufang@pingcap.com (default approver). The mapping rule lives in
``NAVAN_MAPPING_RULES`` (preset) — new Navan targets created after
migration 202 pick it up automatically. Targets created BEFORE this
PR (the live prod target ``navan-prod`` is the only one) need the
rule injected via this migration.

Idempotent — skip if a rule with ``target_field='...travelerApprover'``
already exists (admin may have hand-rolled their own).

Important data note:
The US-entity value_map key carries U+2004 (THREE-PER-EM SPACE), NOT
ASCII space — PingCAP's SAP HRIS records ``FOCompany.name`` for the
US entity as ``01_PingCAP (US), Inc.``. Without the   match,
every US employee would silently fall through to the qiufang default.
The migration writes the EXACT same Unicode shape so the rule matches
real data — DO NOT collapse the   to a regular space.
"""
from __future__ import annotations

import json

import sqlalchemy as sa

from alembic import op


revision = "203"
down_revision = "202"
branch_labels = None
depends_on = None


_TRAVELER_APPROVER_TARGET = (
    "urn:ietf:params:scim:schemas:extension:navan:2.0:User.travelerApprover"
)

# U+2004 = THREE-PER-EM SPACE. Encoded as the raw character below; do
# not refactor to ``"PingCAP (US), Inc."`` unless SAP HRIS first
# updates FOCompany.name to use ASCII space.
_US_ENTITY_KEY = "PingCAP (US), Inc."

_TRAVELER_APPROVER_RULE = {
    "source_template": "{company_name}",
    "transform": "strip_company_prefix",
    "value_map": {
        _US_ENTITY_KEY: "nancy.wu@pingcap.com",
    },
    "default": "qiufang@pingcap.com",
    "target_field": _TRAVELER_APPROVER_TARGET,
}


def upgrade() -> None:
    bind = op.get_bind()
    rows = bind.execute(
        sa.text(
            "SELECT id, mapping_config FROM identity_sync_targets "
            "WHERE kind = 'navan' AND deleted_at IS NULL"
        )
    ).fetchall()
    for row in rows:
        target_id = row[0]
        mc = row[1]
        if isinstance(mc, str):
            try:
                mc = json.loads(mc)
            except (TypeError, ValueError):
                continue
        if not isinstance(mc, dict):
            continue
        rules = mc.get("rules") or []
        if not isinstance(rules, list):
            continue
        # Idempotency: skip if any rule already targets travelerApprover.
        if any(
            isinstance(r, dict) and r.get("target_field") == _TRAVELER_APPROVER_TARGET
            for r in rules
        ):
            continue
        new_rules = [*rules, dict(_TRAVELER_APPROVER_RULE)]
        new_mc = dict(mc)
        new_mc["rules"] = new_rules
        bind.execute(
            sa.text(
                "UPDATE identity_sync_targets SET mapping_config = :mc "
                "WHERE id = :id"
            ),
            {"mc": json.dumps(new_mc, ensure_ascii=False), "id": target_id},
        )
        # Bust per-person payload hash cache so the next sweep re-pushes
        # with the new field — matches the pattern in migration 202.
        bind.execute(
            sa.text(
                "UPDATE identity_sync_state SET last_payload_hash = NULL "
                "WHERE target_id = :id"
            ),
            {"id": target_id},
        )


def downgrade() -> None:
    """Remove the injected travelerApprover rule (only the one whose
    target_field matches — admin custom rules are preserved)."""
    bind = op.get_bind()
    rows = bind.execute(
        sa.text(
            "SELECT id, mapping_config FROM identity_sync_targets "
            "WHERE kind = 'navan' AND deleted_at IS NULL"
        )
    ).fetchall()
    for row in rows:
        target_id = row[0]
        mc = row[1]
        if isinstance(mc, str):
            try:
                mc = json.loads(mc)
            except (TypeError, ValueError):
                continue
        if not isinstance(mc, dict):
            continue
        rules = mc.get("rules") or []
        if not isinstance(rules, list):
            continue
        new_rules = [
            r for r in rules
            if not (
                isinstance(r, dict)
                and r.get("target_field") == _TRAVELER_APPROVER_TARGET
            )
        ]
        if len(new_rules) == len(rules):
            continue
        new_mc = dict(mc)
        new_mc["rules"] = new_rules
        bind.execute(
            sa.text(
                "UPDATE identity_sync_targets SET mapping_config = :mc "
                "WHERE id = :id"
            ),
            {"mc": json.dumps(new_mc, ensure_ascii=False), "id": target_id},
        )