"""connectors — first-class integration catalog, connections, and permissions.

Revision ID: 054
Revises: 053
"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy.dialects.postgresql import UUID as PGUUID

revision = "054"
down_revision = "053"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.create_table(
        "connectors",
        sa.Column("id", PGUUID(as_uuid=True), primary_key=True),
        sa.Column("key", sa.String(length=64), nullable=False),
        sa.Column("name", sa.String(length=120), nullable=False),
        sa.Column("category", sa.String(length=64), nullable=False),
        sa.Column("auth_type", sa.String(length=32), nullable=False),
        sa.Column("status", sa.String(length=20), nullable=False, server_default="active"),
        sa.Column("icon", sa.String(length=120), nullable=True),
        sa.Column("description", sa.Text(), nullable=True),
        sa.Column("docs_url", sa.Text(), nullable=True),
        sa.Column("metadata_json", JSONB(), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
        sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
        sa.UniqueConstraint("key", name="uq_connectors_key"),
    )
    op.create_index("ix_connectors_key", "connectors", ["key"])

    op.create_table(
        "connector_actions",
        sa.Column("id", PGUUID(as_uuid=True), primary_key=True),
        sa.Column(
            "connector_id",
            PGUUID(as_uuid=True),
            sa.ForeignKey("connectors.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column("action_key", sa.String(length=80), nullable=False),
        sa.Column("slug", sa.String(length=160), nullable=False),
        sa.Column("name", sa.String(length=160), nullable=False),
        sa.Column("description", sa.Text(), nullable=True),
        sa.Column("input_schema", JSONB(), nullable=False, server_default=sa.text("'{}'::jsonb")),
        sa.Column("output_schema", JSONB(), nullable=False, server_default=sa.text("'{}'::jsonb")),
        sa.Column("execution_mode", sa.String(length=20), nullable=False, server_default="sync"),
        sa.Column("required_scopes", JSONB(), nullable=False, server_default=sa.text("'[]'::jsonb")),
        sa.Column("required_capabilities", JSONB(), nullable=False, server_default=sa.text("'[]'::jsonb")),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
        sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
        sa.UniqueConstraint("connector_id", "action_key", name="uq_connector_actions_connector_key"),
        sa.UniqueConstraint("slug", name="uq_connector_actions_slug"),
    )
    op.create_index("ix_connector_actions_connector_id", "connector_actions", ["connector_id"])
    op.create_index("ix_connector_actions_slug", "connector_actions", ["slug"])

    op.create_table(
        "connector_triggers",
        sa.Column("id", PGUUID(as_uuid=True), primary_key=True),
        sa.Column(
            "connector_id",
            PGUUID(as_uuid=True),
            sa.ForeignKey("connectors.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column("trigger_key", sa.String(length=100), nullable=False),
        sa.Column("event_type", sa.String(length=180), nullable=False),
        sa.Column("name", sa.String(length=160), nullable=False),
        sa.Column("webhook_required", sa.Boolean(), nullable=False, server_default=sa.text("true")),
        sa.Column("polling_supported", sa.Boolean(), nullable=False, server_default=sa.text("false")),
        sa.Column("schema", JSONB(), nullable=False, server_default=sa.text("'{}'::jsonb")),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
        sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
        sa.UniqueConstraint("connector_id", "trigger_key", name="uq_connector_triggers_connector_key"),
    )
    op.create_index("ix_connector_triggers_connector_id", "connector_triggers", ["connector_id"])
    op.create_index("ix_connector_triggers_event_type", "connector_triggers", ["event_type"])

    op.create_table(
        "connector_connections",
        sa.Column("id", PGUUID(as_uuid=True), primary_key=True),
        sa.Column(
            "connector_id",
            PGUUID(as_uuid=True),
            sa.ForeignKey("connectors.id", ondelete="RESTRICT"),
            nullable=False,
        ),
        sa.Column(
            "workspace_id",
            PGUUID(as_uuid=True),
            sa.ForeignKey("workspaces.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column(
            "project_id",
            PGUUID(as_uuid=True),
            sa.ForeignKey("projects.id", ondelete="CASCADE"),
            nullable=True,
        ),
        sa.Column("name", sa.String(length=80), nullable=False),
        sa.Column("status", sa.String(length=20), nullable=False, server_default="active"),
        sa.Column("auth_data_ref", sa.String(length=220), nullable=True),
        sa.Column("scopes", JSONB(), nullable=False, server_default=sa.text("'[]'::jsonb")),
        sa.Column("config_json", JSONB(), nullable=True),
        sa.Column("secrets_enc", JSONB(), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
        sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
        sa.UniqueConstraint("project_id", "name", name="uq_connector_connections_project_name"),
    )
    op.create_index("ix_connector_connections_connector_id", "connector_connections", ["connector_id"])
    op.create_index("ix_connector_connections_workspace_id", "connector_connections", ["workspace_id"])
    op.create_index("ix_connector_connections_project_id", "connector_connections", ["project_id"])

    op.create_table(
        "connector_permissions",
        sa.Column("id", PGUUID(as_uuid=True), primary_key=True),
        sa.Column(
            "connection_id",
            PGUUID(as_uuid=True),
            sa.ForeignKey("connector_connections.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column("action_slug", sa.String(length=160), nullable=False),
        sa.Column("mode", sa.String(length=12), nullable=False, server_default="deny"),
        sa.Column("policy_json", JSONB(), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
        sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
        sa.UniqueConstraint("connection_id", "action_slug", name="uq_connector_permissions_connection_action"),
    )
    op.create_index("ix_connector_permissions_connection_id", "connector_permissions", ["connection_id"])


def downgrade() -> None:
    op.drop_table("connector_permissions")
    op.drop_table("connector_connections")
    op.drop_table("connector_triggers")
    op.drop_table("connector_actions")
    op.drop_index("ix_connectors_key", table_name="connectors")
    op.drop_table("connectors")