"""Core-owned provider authorization kernel.

Revision ID: 139
Revises: 138
"""

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects.postgresql import JSONB, UUID

revision = "139"
down_revision = "138"
branch_labels = None
depends_on = None


def tenant_columns():
    return [
        sa.Column(
            "workspace_id",
            UUID(),
            sa.ForeignKey("workspaces.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column(
            "project_id", UUID(), sa.ForeignKey("projects.id", ondelete="CASCADE"), nullable=False
        ),
    ]


def upgrade():
    op.create_table(
        "provider_connections",
        sa.Column("id", UUID(), primary_key=True),
        *tenant_columns(),
        sa.Column("provider", sa.String(32), nullable=False),
        sa.Column("actor_type", sa.String(16), nullable=False),
        sa.Column("actor_id", sa.String(160), nullable=False),
        sa.Column("subject", sa.String(255), nullable=False),
        sa.Column("status", sa.String(40), nullable=False),
        sa.Column("effective_scopes", JSONB(), nullable=False),
        sa.Column("secrets_enc", JSONB(), nullable=False),
        sa.Column("expires_at", sa.DateTime(timezone=True)),
        sa.UniqueConstraint(
            "project_id",
            "provider",
            "actor_type",
            "actor_id",
            "subject",
            name="uq_provider_actor_subject",
        ),
        sa.CheckConstraint("actor_type IN ('user', 'project')", name="ck_provider_actor"),
    )
    op.create_table(
        "provider_grants",
        sa.Column("id", UUID(), primary_key=True),
        sa.Column(
            "connection_id",
            UUID(),
            sa.ForeignKey("provider_connections.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column("family", sa.String(32), nullable=False),
        sa.Column("scopes", JSONB(), nullable=False),
        sa.Column("status", sa.String(40), nullable=False),
        sa.Column("granted_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("revoked_at", sa.DateTime(timezone=True)),
        sa.UniqueConstraint("connection_id", "family", name="uq_provider_grant_family"),
    )
    op.create_table(
        "provider_authorization_sessions",
        sa.Column("context_enc", sa.Text()),
        sa.Column("completion_hash", sa.String(64), unique=True),
        sa.Column("id", UUID(), primary_key=True),
        *tenant_columns(),
        sa.Column("actor_type", sa.String(16), nullable=False),
        sa.Column("actor_id", sa.String(160), nullable=False),
        sa.Column("initiated_by", sa.String(160), nullable=False),
        sa.Column("purpose", sa.String(32), nullable=False),
        sa.Column("family", sa.String(32), nullable=False),
        sa.Column(
            "connection_id", UUID(), sa.ForeignKey("provider_connections.id", ondelete="SET NULL")
        ),
        sa.Column("state_hash", sa.String(64), nullable=False, unique=True),
        sa.Column("browser_binding_hash", sa.String(64), nullable=False),
        sa.Column("verifier_enc", sa.Text(), nullable=False),
        sa.Column("redirect_uri", sa.Text(), nullable=False),
        sa.Column("return_to", sa.Text(), nullable=False),
        sa.Column("status", sa.String(40), nullable=False),
        sa.Column("expires_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
    )
    for table in ("provider_connections", "provider_authorization_sessions"):
        for column in ("workspace_id", "project_id"):
            op.create_index(f"ix_{table}_{column}", table, [column])
    op.create_index("ix_provider_grants_connection_id", "provider_grants", ["connection_id"])
    op.add_column(
        "connector_connections", sa.Column("provider_connection_id", UUID(), nullable=True)
    )
    op.create_foreign_key(
        "fk_connector_provider_connection",
        "connector_connections",
        "provider_connections",
        ["provider_connection_id"],
        ["id"],
        ondelete="RESTRICT",
    )
    op.create_index(
        "ix_connector_connections_provider_connection_id",
        "connector_connections",
        ["provider_connection_id"],
    )


def downgrade():
    op.drop_index("ix_connector_connections_provider_connection_id", "connector_connections")
    op.drop_constraint(
        "fk_connector_provider_connection", "connector_connections", type_="foreignkey"
    )
    op.drop_column("connector_connections", "provider_connection_id")
    op.drop_table("provider_authorization_sessions")
    op.drop_table("provider_grants")
    op.drop_table("provider_connections")