"""Drop the ``cmdb_ai_resources`` table.

Revision ID: 152
Revises: 151
Create Date: 2026-05-12

Why
---
``cmdb_ai_resources`` was a denormalized registry of "AI-capable cloud
resources" (Foundry deployments) — one row per Azure Cognitive
Services resource that lived inside an ``is_ai_dedicated`` Azure
subscription. PR #253 (Phase 2) retires both the ``is_ai_dedicated``
concept (migration 152) and the per-resource registry: AI spend is
now identified by ``cmdb_cost_facts.service`` matching the
configurable whitelist (``ai_spending.service_whitelist`` platform
setting, seeded in migration 149), and per-AK attribution flows
through the AK's ``cloud_scope_id`` → resource_group string column on
facts. No second table needed.

Order
-----
Runs AFTER 150 because the child table ``cmdb_ai_resource_cost_daily``
holds the only FK pointing at ``cmdb_ai_resources.id``; 150 drops the
child + FK, then this drops the parent cleanly.

Downgrade
---------
Re-creates the table with the same shape as migration 079 declared.
The three FKs to ``integrations`` / ``cmdb_cloud_scopes`` /
``identity_persons`` go back. Data is not restored — see 150 docstring
for the rationale.
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "152"
down_revision = "151"
branch_labels = None
depends_on = None


TABLE = "cmdb_ai_resources"


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 _fk_exists(bind, table: str, 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": name},
    ).scalar() is not None


def upgrade() -> None:
    bind = op.get_bind()
    if not _table_exists(bind, TABLE):
        return
    # Drop FKs first — explicit even though drop_table cascades on most
    # engines, because TiDB occasionally surfaces FK-still-referenced
    # errors when the drop happens inside a single migration run that
    # also dropped the child table. Same defensive posture as migration
    # 141's downgrade.
    for fk_name in (
        "fk_cmdb_ai_resources_integration",
        "fk_cmdb_ai_resources_scope",
        "fk_cmdb_ai_resources_owner",
    ):
        if _fk_exists(bind, TABLE, fk_name):
            op.drop_constraint(fk_name, TABLE, type_="foreignkey")
    op.drop_table(TABLE)


def downgrade() -> None:
    bind = op.get_bind()
    if _table_exists(bind, TABLE):
        return
    op.create_table(
        TABLE,
        sa.Column("id", sa.CHAR(36), primary_key=True),
        sa.Column("integration_id", sa.CHAR(36), nullable=False),
        sa.Column("scope_id", sa.String(36), nullable=True),
        sa.Column("provider", sa.String(32), nullable=False),
        sa.Column("resource_id", sa.String(512), nullable=False),
        sa.Column("resource_name", sa.String(256), nullable=False),
        sa.Column("resource_kind", sa.String(64), nullable=True),
        sa.Column("resource_location", sa.String(64), nullable=True),
        sa.Column("owner_person_id", sa.CHAR(36), nullable=True),
        sa.Column("owner_department", sa.String(256), nullable=True),
        sa.Column("auto_owner_source", sa.String(32), nullable=True),
        sa.Column("tags", sa.JSON, nullable=True),
        sa.Column("raw_data", sa.JSON, nullable=True),
        sa.Column(
            "first_seen_at", sa.DateTime(timezone=True),
            server_default=sa.func.now(), nullable=False,
        ),
        sa.Column(
            "last_seen_at", sa.DateTime(timezone=True),
            server_default=sa.func.now(), nullable=False,
        ),
        sa.Column(
            "deleted_at", sa.DateTime(timezone=True), nullable=True,
        ),
        sa.Column(
            "created_at", sa.DateTime(timezone=True),
            server_default=sa.func.now(), nullable=False,
        ),
        sa.Column(
            "updated_at", sa.DateTime(timezone=True),
            server_default=sa.func.now(), onupdate=sa.func.now(),
            nullable=False,
        ),
        sa.UniqueConstraint(
            "integration_id", "resource_id",
            name="uq_cmdb_ai_resources_integration_resource",
        ),
        sa.Index("ix_cmdb_ai_resources_owner", "owner_person_id"),
        sa.Index("ix_cmdb_ai_resources_scope", "scope_id"),
    )
    # FKs re-created best-effort — collation mismatch on TiDB would
    # otherwise abort the downgrade. Same pattern as migration 079
    # upgrade.
    for fk_name, refs, cols, ondelete in [
        (
            "fk_cmdb_ai_resources_integration", "integrations",
            ["integration_id"], "CASCADE",
        ),
        (
            "fk_cmdb_ai_resources_scope", "cmdb_cloud_scopes",
            ["scope_id"], "SET NULL",
        ),
        (
            "fk_cmdb_ai_resources_owner", "identity_persons",
            ["owner_person_id"], "SET NULL",
        ),
    ]:
        try:
            op.create_foreign_key(
                fk_name, TABLE, cols, refs, ["id"], ondelete=ondelete,
            )
        except Exception:  # noqa: BLE001
            # TiDB collation mismatch on downgrade is non-fatal — the
            # table exists, the data path is what matters; the FK is
            # belt+braces. Match migration 079's _try_swallow posture.
            continue