"""Add owner_person_id to cmdb_cdn_domains — align with domains/dns pattern.

Revision ID: 160
Revises: 159
Create Date: 2026-05-13

Prior art: migration 042 dropped the free-text ``owner_raw`` column from
cmdb_servers and cmdb_domains and replaced it with an FK to
``identity_persons``.  CDN domains were missed in that sweep; they still
carried ``owner_raw VARCHAR(255)``.

This migration:
1. Adds ``owner_person_id VARCHAR(36) NULL`` with a hard FK (ondelete
   SET NULL) and index to ``identity_persons`` — consistent with the
   pattern used for servers / domains in migration 042.
2. Backfills existing ``owner_raw`` values by resolving them via
   ``work_email`` lookup, exactly like the migration 042 backfill did.
   Unresolvable strings are left as ``owner_person_id = NULL``; the raw
   text stays in the ``owner_raw`` column as a fallback audit trail.
3. Does NOT drop ``owner_raw`` — keeping it lets the CDN sync driver
   continue to write a raw display string without needing a person
   resolution round-trip.  The application layer now reads
   ``owner_person_id`` for display (resolved via batch label query)
   and writes ``owner_raw`` for updates (resolved at annotation time).
"""

from alembic import op
import sqlalchemy as sa
from sqlalchemy import text


revision = "160"
down_revision = "159"
branch_labels = None
depends_on = None


def _fk_source_col_type(bind, target_table: str, target_column: str, *, length: int = 36):
    """Mirror the FK target's DATA_TYPE + COLLATION so TiDB / MySQL 8 don't
    reject the constraint with error 3780 (CHAR vs VARCHAR incompatibility)."""
    row = bind.execute(
        sa.text(
            "SELECT DATA_TYPE, COLLATION_NAME FROM "
            "information_schema.COLUMNS "
            "WHERE TABLE_SCHEMA = DATABASE() "
            "  AND TABLE_NAME = :t AND COLUMN_NAME = :c"
        ),
        {"t": target_table, "c": target_column},
    ).fetchone()
    data_type = (row[0] or "char").lower() if row else "char"
    collation = row[1] if row else None
    type_cls = sa.CHAR if data_type == "char" else sa.String
    return type_cls(length, collation=collation) if collation else type_cls(length)


def _column_exists(bind, table: str, column: str) -> bool:
    row = bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.COLUMNS "
            "WHERE TABLE_SCHEMA = DATABASE() "
            "  AND TABLE_NAME = :t AND COLUMN_NAME = :c"
        ),
        {"t": table, "c": column},
    ).fetchone()
    return row is not None


def _index_exists(bind, table: str, index: str) -> bool:
    row = bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.STATISTICS "
            "WHERE TABLE_SCHEMA = DATABASE() "
            "  AND TABLE_NAME = :t AND INDEX_NAME = :i"
        ),
        {"t": table, "i": index},
    ).fetchone()
    return row is not None


def _fk_exists(bind, table: str, constraint: str) -> bool:
    row = bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.TABLE_CONSTRAINTS "
            "WHERE TABLE_SCHEMA = DATABASE() "
            "  AND TABLE_NAME = :t AND CONSTRAINT_NAME = :c "
            "  AND CONSTRAINT_TYPE = 'FOREIGN KEY'"
        ),
        {"t": table, "c": constraint},
    ).fetchone()
    return row is not None


def upgrade() -> None:
    bind = op.get_bind()
    persons_id_type = _fk_source_col_type(bind, "identity_persons", "id")

    # Guard: first deploy partially applied (column added as VARCHAR, FK failed
    # with 3780). Column must match identity_persons.id type exactly (CHAR on
    # TiDB) before FK creation. Add if missing; ALTER to correct type if present.
    if not _column_exists(bind, "cmdb_cdn_domains", "owner_person_id"):
        op.add_column(
            "cmdb_cdn_domains",
            sa.Column("owner_person_id", persons_id_type, nullable=True),
        )
    else:
        # Column was added with wrong type by the first partial apply — retype it.
        op.alter_column(
            "cmdb_cdn_domains",
            "owner_person_id",
            type_=persons_id_type,
            existing_nullable=True,
        )

    # Backfill: resolve existing owner_raw strings via work_email.
    op.execute(text("""
        UPDATE cmdb_cdn_domains c
        JOIN identity_persons p
            ON LOWER(p.work_email) = LOWER(c.owner_raw)
        SET c.owner_person_id = CAST(p.id AS CHAR(36))
        WHERE c.owner_raw IS NOT NULL
          AND c.owner_person_id IS NULL
          AND c.deleted_at IS NULL
    """))

    # Index for owner-filter queries + FK for referential integrity.
    # Consistent with cmdb_servers.owner_person_id and cmdb_domains.owner_person_id
    # (both added FK + index in migration 042).
    if not _index_exists(bind, "cmdb_cdn_domains", "ix_cmdb_cdn_domains_owner_person_id"):
        op.create_index(
            "ix_cmdb_cdn_domains_owner_person_id",
            "cmdb_cdn_domains",
            ["owner_person_id"],
        )
    if not _fk_exists(bind, "cmdb_cdn_domains", "fk_cmdb_cdn_domains_owner_person_id"):
        op.create_foreign_key(
            "fk_cmdb_cdn_domains_owner_person_id",
            "cmdb_cdn_domains",
            "identity_persons",
            ["owner_person_id"],
            ["id"],
            ondelete="SET NULL",
        )


def downgrade() -> None:
    op.drop_constraint("fk_cmdb_cdn_domains_owner_person_id", "cmdb_cdn_domains", type_="foreignkey")
    op.drop_index("ix_cmdb_cdn_domains_owner_person_id", "cmdb_cdn_domains")
    op.drop_column("cmdb_cdn_domains", "owner_person_id")