"""Promote cmdb created_by / triggered_by_user_id to real FKs.

Revision ID: 044
Revises: 043
Create Date: 2026-04-21

Two columns that have been storing ``identity_persons.id`` as a bare
``VARCHAR(36)`` since migration 006:

* ``cmdb_provider_accounts.created_by``
* ``cmdb_sync_jobs.triggered_by_user_id``

With no FK, a person deletion leaves orphan UUIDs behind, and
``JOIN identity_persons`` can't be proven sound by the schema. PR-F
follow-up ships the missing constraint.

Steps:

1. Null out any value that no longer matches an existing
   ``identity_persons`` row. Solo-dev environment; production has at
   most a handful of provider accounts and a few sync jobs, so the
   orphan count is expected to be zero — this guard just keeps the
   ``ADD CONSTRAINT`` step from failing if someone's row was deleted.
2. Normalize the column's charset/collation to match the parent's
   (needed for TiDB where ``identity_persons.id`` is
   ``utf8mb4_unicode_ci`` but newer tables default to ``0900_ai_ci``).
3. Add the FK with ``ON DELETE SET NULL`` so dropping a person doesn't
   cascade into provider/job history.

Idempotent — constraint creation is guarded by
``information_schema.KEY_COLUMN_USAGE``.
"""
from __future__ import annotations

import sqlalchemy as sa
from alembic import op

from app.core.migration_helpers import normalize_fk_column_collation

revision = "044"
down_revision = "043"
branch_labels = None
depends_on = None


FK_TARGETS = [
    ("cmdb_provider_accounts", "created_by", "fk_cmdb_provider_accounts_created_by"),
    ("cmdb_sync_jobs", "triggered_by_user_id", "fk_cmdb_sync_jobs_triggered_by_user_id"),
]


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


def _table_exists(bind, name: str) -> bool:
    return bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.TABLES "
            "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :n",
        ),
        {"n": name},
    ).scalar() is not None


def upgrade() -> None:
    bind = op.get_bind()

    if not _table_exists(bind, "identity_persons"):
        # Defensive — migration order should guarantee this, but if the
        # parent table isn't there yet we have nothing to FK into.
        return

    for table, column, constraint in FK_TARGETS:
        if not _table_exists(bind, table):
            continue

        # Null orphan references so the FK can be added.
        bind.execute(sa.text(
            f"UPDATE {table} SET {column} = NULL "
            f"WHERE {column} IS NOT NULL "
            f"AND {column} NOT IN (SELECT id FROM identity_persons)"
        ))

        normalize_fk_column_collation(
            table=table,
            column=column,
            parent_table="identity_persons",
            nullable=True,
        )

        if not _fk_exists(bind, table, constraint):
            op.create_foreign_key(
                constraint,
                source_table=table,
                referent_table="identity_persons",
                local_cols=[column],
                remote_cols=["id"],
                ondelete="SET NULL",
            )


def downgrade() -> None:
    bind = op.get_bind()

    for table, _column, constraint in FK_TARGETS:
        if not _table_exists(bind, table):
            continue
        if _fk_exists(bind, table, constraint):
            op.drop_constraint(constraint, table, type_="foreignkey")