"""Hotfix migration 232 — TiDB strict-FK collation rejection on
``auditor_privileged_role_holders.assigned_to_person_id`` → ``identity_persons.id``.

Revision ID: 233
Revises: 232
Create Date: 2026-06-01

Background
==========

Migration 232 (PR-5 of symmetric-onboarding) tried to ADD COLUMN +
CREATE FOREIGN KEY in the same step. Local CI MySQL accepted it; prod
TiDB rejected the CREATE FOREIGN KEY with:

    (3780, "Referencing column 'assigned_to_person_id' and referenced
    column 'id' in foreign key constraint 'fk_apriv_holder_assigned'
    are incompatible.")

Root cause is the documented ``feedback_tidb_fk_strictness`` issue:
TiDB's per-row collation strictness requires the FK column to share
DATA_TYPE AND collation with the referenced column. ``id_type_for``
gives the right DATA_TYPE but the new column inherits the OWNING
table's default collation (``utf8mb4_0900_ai_ci`` on the newer
``auditor_*`` tables) which differs from ``identity_persons.id``'s
``utf8mb4_unicode_ci`` on prod TiDB.

The standard helper for this is
``app.core.migration_helpers.normalize_fk_column_collation`` — emits
an ALTER TABLE MODIFY COLUMN to align the column's charset/collation
with the parent's, then ``create_foreign_key`` succeeds.

What this migration does
========================

* Idempotent. Probes information_schema for current state.
* If column missing → add it with the correct type.
* Normalize the column collation to match ``identity_persons.id``.
* If FK missing → create it.
* Companion guards on the 2 indexes that migration 232 also adds.

Migration 232's own column / index / FK / cron seed guards remain in
place, so prod that partially applied 232 (column added, FK failed)
can re-run 232 idempotently after this hotfix.
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op
from app.core.migration_helpers import (
    id_type_for,
    normalize_fk_column_collation,
)

revision = "233"
down_revision = "232"
branch_labels = None
depends_on = None

_TABLE = "auditor_privileged_role_holders"


def _column_exists(table: str, column: str) -> bool:
    row = (
        op.get_bind()
        .execute(
            sa.text(
                "SELECT 1 FROM information_schema.COLUMNS WHERE "
                "TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t "
                "AND COLUMN_NAME = :c LIMIT 1"
            ),
            {"t": table, "c": column},
        )
        .scalar()
    )
    return bool(row)


def _fk_exists(table: str, fk_name: str) -> bool:
    row = (
        op.get_bind()
        .execute(
            sa.text(
                "SELECT 1 FROM information_schema.REFERENTIAL_CONSTRAINTS "
                "WHERE CONSTRAINT_SCHEMA = DATABASE() AND TABLE_NAME = :t "
                "AND CONSTRAINT_NAME = :c LIMIT 1"
            ),
            {"t": table, "c": fk_name},
        )
        .scalar()
    )
    return bool(row)


def _index_exists(table: str, index: str) -> bool:
    row = (
        op.get_bind()
        .execute(
            sa.text(
                "SELECT 1 FROM information_schema.STATISTICS WHERE "
                "TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t "
                "AND INDEX_NAME = :i LIMIT 1"
            ),
            {"t": table, "i": index},
        )
        .scalar()
    )
    return bool(row)


def upgrade() -> None:
    persons_id = id_type_for("identity_persons")

    # 1. Add column if 232 never got that far (transactional rollback path).
    if not _column_exists(_TABLE, "assigned_to_person_id"):
        op.add_column(
            _TABLE,
            sa.Column("assigned_to_person_id", persons_id, nullable=True),
        )

    # 1.5. TiDB rejects ALTER COLUMN ... COLLATE when an index is defined
    # on the column — error 8200 ("Unsupported modifying collation of
    # column 'X' from 'utf8mb4_bin' to 'utf8mb4_unicode_ci' when index
    # is defined on it"). Prod TiDB hit exactly this on the 2026-06-01
    # auto-deploy: migration 232's partial run had added the column AND
    # created ``idx_apriv_holder_assigned`` on it, but its FK step had
    # failed — so step 2 below (``normalize_fk_column_collation`` →
    # MODIFY COLUMN COLLATE) crashed.
    #
    # Drop the index BEFORE collation normalisation, ONLY when the
    # partial-232 footprint is present: index exists AND the FK does
    # NOT yet exist. In clean-deploy environments (CI MySQL) migration
    # 232 ran end-to-end, so the FK exists and the index is needed by
    # it — MySQL would refuse the drop with error 1553 ("Cannot drop
    # index 'X': needed in a foreign key constraint"). When the FK is
    # already in place the column collation must already be compatible
    # with ``identity_persons.id`` (the FK wouldn't have been created
    # otherwise), so step 2 below is a no-op and we don't need to
    # touch the index at all.
    if (
        _index_exists(_TABLE, "idx_apriv_holder_assigned")
        and not _fk_exists(_TABLE, "fk_apriv_holder_assigned")
    ):
        op.drop_index("idx_apriv_holder_assigned", _TABLE)

    # 2. Align column's charset+collation with identity_persons.id.
    #    No-op when collations already match (helper checks).
    normalize_fk_column_collation(
        table=_TABLE,
        column="assigned_to_person_id",
        parent_table="identity_persons",
        nullable=True,
    )

    # 3. Create the FK if it doesn't exist yet.
    if not _fk_exists(_TABLE, "fk_apriv_holder_assigned"):
        op.create_foreign_key(
            "fk_apriv_holder_assigned",
            _TABLE,
            "identity_persons",
            ["assigned_to_person_id"],
            ["id"],
            ondelete="SET NULL",
        )

    # 4. Companion columns from migration 232 (idempotent).
    if not _column_exists(_TABLE, "decided_at_deadline"):
        op.add_column(
            _TABLE,
            sa.Column(
                "decided_at_deadline",
                sa.DateTime(timezone=True),
                nullable=True,
            ),
        )
    if not _column_exists(_TABLE, "escalated_at"):
        op.add_column(
            _TABLE,
            sa.Column(
                "escalated_at",
                sa.DateTime(timezone=True),
                nullable=True,
            ),
        )

    # 5. Indexes — same shape as migration 232.
    if not _index_exists(_TABLE, "idx_apriv_holder_pending_deadline"):
        op.create_index(
            "idx_apriv_holder_pending_deadline",
            _TABLE,
            ["decision", "decided_at_deadline"],
        )
    if not _index_exists(_TABLE, "idx_apriv_holder_assigned"):
        op.create_index(
            "idx_apriv_holder_assigned",
            _TABLE,
            ["assigned_to_person_id"],
        )

    # 6. Cron seed — idempotent on (name).
    exists = (
        op.get_bind()
        .execute(
            sa.text(
                "SELECT 1 FROM scheduled_jobs "
                "WHERE name = 'auditor.privileged_review_escalate' LIMIT 1"
            )
        )
        .scalar()
    )
    if not exists:
        op.get_bind().execute(
            sa.text(
                """
                INSERT INTO scheduled_jobs
                    (id, name, display_name, description, handler_key,
                     trigger_type, interval_seconds, cron_expr, timezone,
                     enabled, config, run_count, failure_count,
                     created_at, updated_at)
                VALUES
                    (UUID(),
                     'auditor.privileged_review_escalate',
                     '特权审阅升级扫描',
                     '每天 03:30 (Asia/Shanghai) 扫描所有 pending 状态、且 '
                     'decided_at_deadline 已经过去 14 天的 '
                     'AuditorPrivilegedRoleHolder, 设置 escalated_at + '
                     '写 compliance_incident (type=access_review_overdue, '
                     'severity=warning) + 推送 Feishu 卡到 assigned_to '
                     '的经理。idempotent — 一次写入 escalated_at 就不会 '
                     '重复。',
                     'auditor.privileged_review_escalate', 'cron', NULL,
                     '30 3 * * *', 'Asia/Shanghai',
                     1, JSON_OBJECT(), 0, 0, NOW(), NOW())
                """
            )
        )


def downgrade() -> None:
    # No-op — undoing this hotfix would re-introduce the broken state
    # from migration 232. To roll back the access-review assignment
    # feature, downgrade past migration 232 instead.
    pass