"""Link verified CMDB saving events to an explicitly handled AI recommendation.

Revision ID: 510
Revises: 509
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op
from app.core.migration_helpers import table_kw_for

revision = "510"
down_revision = "509"
branch_labels = None
depends_on = None

_TABLE = "intelligence_recommendation_savings_links"


def upgrade() -> None:
    # TiDB requires the child table's character set/collation to match the
    # existing recommendation UUID column exactly.  Leaving this implicit
    # inherits the server default instead, which rejects the FK in production
    # even when local MySQL accepts it (error 3780).
    table_kw = table_kw_for("intelligence_recommendations")
    op.create_table(
        _TABLE,
        sa.Column("id", sa.CHAR(length=36), nullable=False),
        sa.Column("recommendation_id", sa.CHAR(length=36), nullable=False),
        sa.Column("savings_event_id", sa.CHAR(length=36), nullable=False),
        sa.Column("server_id", sa.String(length=36), nullable=False),
        sa.Column("linked_at", sa.DateTime(timezone=True), server_default=sa.text("CURRENT_TIMESTAMP"), nullable=False),
        sa.ForeignKeyConstraint(["recommendation_id"], ["intelligence_recommendations.id"], ondelete="RESTRICT"),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("savings_event_id", name="uq_intel_rec_saving_event"),
        sa.UniqueConstraint("recommendation_id", "savings_event_id", name="uq_intel_rec_saving_pair"),
        **table_kw,
    )
    op.create_index("ix_intel_rec_saving_recommendation", _TABLE, ["recommendation_id"])


def downgrade() -> None:
    op.drop_index("ix_intel_rec_saving_recommendation", table_name=_TABLE)
    op.drop_table(_TABLE)