"""Data-access semantic bindings.

Revision ID: 331
Revises: 330
Create Date: 2026-06-10

DAX-009 adds ``data_access_bindings`` for approved semantic desired
state. A binding is keyed by:

``data_access_resources.id`` + ``email_groups.id`` + ``data_access_entitlements.id``.

The table preserves the Google group id and primary email snapshots so
later rename/revoke compilation can intentionally emit an absent effect
for an old provider member and a present effect for the new primary
email. Those snapshots are not the uniqueness key, and aliases are not
stored.

MySQL/TiDB do not support partial unique indexes, so current-binding
uniqueness uses a generated hash key. ``active`` and ``revoked_pending``
rows produce ``active_semantic_key``; historical ``revoked`` rows produce
NULL and remain queryable.
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op
from app.core.migration_helpers import (
    id_type_for,
    normalize_fk_column_collation,
    table_kw_for,
)

revision = "334"
down_revision = "333"
branch_labels = None
depends_on = None

TABLE = "data_access_bindings"
ACTIVE_UNIQUE = "uq_data_access_bindings_active_semantic"


def _table_exists(bind, table: str) -> bool:
    return bool(bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.tables "
            "WHERE table_schema = DATABASE() AND table_name = :t LIMIT 1",
        ),
        {"t": table},
    ).scalar())


def upgrade() -> None:
    bind = op.get_bind()
    if _table_exists(bind, TABLE):
        return

    row_id = id_type_for("identity_persons")
    resource_id = id_type_for("data_access_resources")
    group_id = id_type_for("email_groups")
    entitlement_id = id_type_for("data_access_entitlements")
    request_id = id_type_for("data_access_requests")
    request_item_id = id_type_for("data_access_request_items")
    kw = table_kw_for("identity_persons")

    op.create_table(
        TABLE,
        sa.Column("id", row_id, primary_key=True),
        sa.Column("resource_id", resource_id, nullable=False),
        sa.Column("principal_email_group_id", group_id, nullable=False),
        sa.Column(
            "principal_google_group_id_snapshot",
            sa.String(128),
            nullable=True,
        ),
        sa.Column("principal_email_snapshot", sa.String(255), nullable=False),
        sa.Column("entitlement_id", entitlement_id, nullable=False),
        sa.Column(
            "entitlement_snapshot", sa.String(32), nullable=False,
            server_default="read",
        ),
        sa.Column(
            "entitlement_version_snapshot", sa.String(16), nullable=False,
            server_default="v1",
        ),
        sa.Column("source_request_id", request_id, nullable=True),
        sa.Column("source_request_item_id", request_item_id, nullable=True),
        sa.Column(
            "status", sa.String(32), nullable=False,
            server_default="active",
        ),
        sa.Column(
            "active_semantic_key",
            sa.String(64),
            sa.Computed(
                "CASE WHEN status IN ('active', 'revoked_pending') THEN "
                "SHA2(CONCAT_WS('|', resource_id, principal_email_group_id, "
                "entitlement_id), 256) ELSE NULL END",
            ),
            nullable=True,
        ),
        sa.Column(
            "granted_at", sa.DateTime(timezone=True), nullable=False,
            server_default=sa.func.now(),
        ),
        sa.Column("revoked_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("revoke_reason", sa.String(64), nullable=True),
        sa.Column(
            "created_at", sa.DateTime(timezone=True), nullable=False,
            server_default=sa.func.now(),
        ),
        sa.Column(
            "updated_at", sa.DateTime(timezone=True), nullable=False,
            server_default=sa.func.now(),
        ),
        **kw,
    )
    for column, parent, nullable in (
        ("resource_id", "data_access_resources", False),
        ("principal_email_group_id", "email_groups", False),
        ("entitlement_id", "data_access_entitlements", False),
        ("source_request_id", "data_access_requests", True),
        ("source_request_item_id", "data_access_request_items", True),
    ):
        normalize_fk_column_collation(
            TABLE, column, parent, nullable=nullable,
        )
    op.create_foreign_key(
        "fk_data_access_bindings_resource",
        TABLE,
        "data_access_resources",
        ["resource_id"],
        ["id"],
    )
    op.create_foreign_key(
        "fk_data_access_bindings_principal_group",
        TABLE,
        "email_groups",
        ["principal_email_group_id"],
        ["id"],
    )
    op.create_foreign_key(
        "fk_data_access_bindings_entitlement",
        TABLE,
        "data_access_entitlements",
        ["entitlement_id"],
        ["id"],
    )
    op.create_foreign_key(
        "fk_data_access_bindings_source_request",
        TABLE,
        "data_access_requests",
        ["source_request_id"],
        ["id"],
        ondelete="SET NULL",
    )
    op.create_foreign_key(
        "fk_data_access_bindings_source_request_item",
        TABLE,
        "data_access_request_items",
        ["source_request_item_id"],
        ["id"],
        ondelete="SET NULL",
    )
    op.create_index(ACTIVE_UNIQUE, TABLE, ["active_semantic_key"], unique=True)
    op.create_index("ix_data_access_bindings_resource", TABLE, ["resource_id"])
    op.create_index(
        "ix_data_access_bindings_principal",
        TABLE,
        ["principal_email_group_id"],
    )
    op.create_index(
        "ix_data_access_bindings_entitlement", TABLE, ["entitlement_id"],
    )
    op.create_index("ix_data_access_bindings_status", TABLE, ["status"])
    op.create_index(
        "ix_data_access_bindings_request_item",
        TABLE,
        ["source_request_item_id"],
    )


def downgrade() -> None:
    bind = op.get_bind()
    if not _table_exists(bind, TABLE):
        return
    op.drop_table(TABLE)