"""Add tenant_id FK CASCADE to remaining tables not covered by migration 0091.

Tables missing from migration 0091:
- contact_merge_history (has tenant_id from TenantMixin but no FK)
- tenant_plugin_activation (plugin table, may not exist yet)
- user_groups (has FK already but verify CASCADE)
- guest_users (has FK already but verify CASCADE)
- user_tenants (has FK already but verify CASCADE)

Also adds FK to calendar_entry_links.tenant_id if not already present
(migration 0091 includes it but the model definition lacks the FK).

Revision ID: 0103
"""

from alembic import op
import sqlalchemy as sa

revision = "0103"
down_revision = "0102"
branch_labels = None
depends_on = None

# Tables that need tenant_id FK with CASCADE but were not in migration 0091
TABLES_NEEDING_FK = [
    "contact_merge_history",
    "tenant_plugin_activation",
]

# Tables that should already have FK but we verify CASCADE is set
TABLES_VERIFY_CASCADE = [
    "user_groups",
    "guest_users",
    "user_tenants",
]


def _table_exists(conn, table_name: str) -> bool:
    result = conn.execute(
        sa.text("SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = :name)"),
        {"name": table_name},
    )
    return result.scalar()


def _fk_exists(conn, table_name: str, constraint_name: str) -> bool:
    result = conn.execute(
        sa.text(
            "SELECT EXISTS (SELECT 1 FROM information_schema.table_constraints "
            "WHERE constraint_name = :name AND constraint_type = 'FOREIGN KEY')"
        ),
        {"name": constraint_name},
    )
    return result.scalar()


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

    # Add FK CASCADE to tables that are missing it
    for table_name in TABLES_NEEDING_FK:
        if not _table_exists(conn, table_name):
            print(f"[0103] Skipping {table_name} — table does not exist")
            continue
        constraint_name = f"fk_{table_name}_tenant_id"
        if _fk_exists(conn, table_name, constraint_name):
            print(f"[0103] Skipping {table_name} — FK already exists")
            continue
        # Check if tenant_id column exists
        col_exists = conn.execute(
            sa.text(
                "SELECT EXISTS (SELECT 1 FROM information_schema.columns "
                "WHERE table_name = :name AND column_name = 'tenant_id')"
            ),
            {"name": table_name},
        ).scalar()
        if not col_exists:
            print(f"[0103] Skipping {table_name} — no tenant_id column")
            continue
        op.execute(
            f"ALTER TABLE {table_name} ADD CONSTRAINT {constraint_name} "
            f"FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE;"
        )
        print(f"[0103] Added FK CASCADE to {table_name}")

    # Verify CASCADE on existing FKs (drop and recreate if missing CASCADE)
    for table_name in TABLES_VERIFY_CASCADE:
        if not _table_exists(conn, table_name):
            continue
        constraint_name = f"fk_{table_name}_tenant_id"
        if not _fk_exists(conn, table_name, constraint_name):
            # Check if any FK exists on tenant_id
            existing_fk = conn.execute(
                sa.text(
                    "SELECT conname FROM pg_constraint con "
                    "JOIN pg_class cls ON con.conrelid = cls.oid "
                    "WHERE cls.relname = :table AND con.contype = 'f' "
                    "AND EXISTS (SELECT 1 FROM pg_attribute att "
                    "WHERE att.attrelid = con.conrelid AND att.attname = 'tenant_id' "
                    "AND att.attnum = ANY(con.conkey))"
                ),
                {"table": table_name},
            ).scalar_one_or_none()
            if existing_fk:
                # Drop existing FK and recreate with CASCADE
                op.execute(f"ALTER TABLE {table_name} DROP CONSTRAINT {existing_fk};")
                op.execute(
                    f"ALTER TABLE {table_name} ADD CONSTRAINT {constraint_name} "
                    f"FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE;"
                )
                print(f"[0103] Replaced FK on {table_name} with CASCADE (was: {existing_fk})")
            else:
                op.execute(
                    f"ALTER TABLE {table_name} ADD CONSTRAINT {constraint_name} "
                    f"FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE;"
                )
                print(f"[0103] Added FK CASCADE to {table_name}")


def downgrade() -> None:
    conn = op.get_bind()
    for table_name in TABLES_NEEDING_FK + TABLES_VERIFY_CASCADE:
        if not _table_exists(conn, table_name):
            continue
        constraint_name = f"fk_{table_name}_tenant_id"
        if _fk_exists(conn, table_name, constraint_name):
            op.execute(f"ALTER TABLE {table_name} DROP CONSTRAINT {constraint_name};")