"""Data-access semantic entitlement catalog.

Revision ID: 329
Revises: 328
Create Date: 2026-06-10

DAX-007 adds ``data_access_entitlements`` as the V1 semantic entitlement
catalog. The seed is intentionally one row: ``read`` version ``v1`` for
BigQuery dataset access by Google Groups, compiling to
``roles/bigquery.dataViewer`` with ``group:`` provider members.

This table is not a per-resource policy override surface. It has no
resource, integration, project, or dataset locator columns. Request and
callback code can later reference the canonical semantic entitlement,
but only the reconciler may turn compiled effects into BigQuery IAM
mutation.
"""
from __future__ import annotations

import sqlalchemy as sa

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

revision = "332"
down_revision = "331"
branch_labels = None
depends_on = None

TABLE = "data_access_entitlements"
READ_V1_ID = "0b8c586a-0b8f-5a4b-9e56-0f2976225cb0"
READ_V1_SEED = {
    "id": READ_V1_ID,
    "domain": "data_access",
    "provider": "bigquery",
    "resource_type": "dataset",
    "principal_type": "google_group",
    "entitlement": "read",
    "version": "v1",
    "provider_member_prefix": "group:",
    "provider_role": "roles/bigquery.dataViewer",
    "requestable": True,
    "status": "active",
    "description": (
        "DAX V1 read access: Google Group gains "
        "roles/bigquery.dataViewer on a BigQuery dataset."
    ),
}


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 _seed_read_v1(bind) -> None:
    bind.execute(
        sa.text(
            f"INSERT INTO {TABLE} ("
            "id, domain, provider, resource_type, principal_type, "
            "entitlement, version, provider_member_prefix, provider_role, "
            "requestable, status, description, created_at, updated_at"
            ") "
            "SELECT "
            ":id, :domain, :provider, :resource_type, :principal_type, "
            ":entitlement, :version, :provider_member_prefix, :provider_role, "
            ":requestable, :status, :description, NOW(), NOW() "
            "FROM DUAL WHERE NOT EXISTS ("
            f"SELECT 1 FROM {TABLE} "
            "WHERE entitlement = :entitlement AND version = :version"
            ")",
        ),
        READ_V1_SEED,
    )


def upgrade() -> None:
    bind = op.get_bind()
    if not _table_exists(bind, TABLE):
        row_id = id_type_for("identity_persons")
        kw = table_kw_for("identity_persons")
        op.create_table(
            TABLE,
            sa.Column("id", row_id, primary_key=True),
            sa.Column(
                "domain", sa.String(32), nullable=False,
                server_default="data_access",
            ),
            sa.Column(
                "provider", sa.String(32), nullable=False,
                server_default="bigquery",
            ),
            sa.Column(
                "resource_type", sa.String(32), nullable=False,
                server_default="dataset",
            ),
            sa.Column(
                "principal_type", sa.String(32), nullable=False,
                server_default="google_group",
            ),
            sa.Column("entitlement", sa.String(32), nullable=False),
            sa.Column(
                "version", sa.String(16), nullable=False,
                server_default="v1",
            ),
            sa.Column(
                "provider_member_prefix", sa.String(32), nullable=False,
                server_default="group:",
            ),
            sa.Column(
                "provider_role", sa.String(128), nullable=False,
                server_default="roles/bigquery.dataViewer",
            ),
            sa.Column(
                "requestable", sa.Boolean(), nullable=False,
                server_default="1",
            ),
            sa.Column(
                "status", sa.String(24), nullable=False,
                server_default="active",
            ),
            sa.Column("description", sa.Text(), 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(),
            ),
            sa.UniqueConstraint(
                "entitlement",
                "version",
                name="uq_data_access_entitlements_entitlement_version",
            ),
            **kw,
        )
        op.create_index(
            "ix_data_access_entitlements_status", TABLE, ["status"],
        )
        op.create_index(
            "ix_data_access_entitlements_requestable", TABLE, ["requestable"],
        )
        op.create_index(
            "ix_data_access_entitlements_scope",
            TABLE,
            ["provider", "resource_type", "principal_type"],
        )

    _seed_read_v1(bind)


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