"""Project Integrations: project_integrations table.

Revision ID: 037
Revises: 036
"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

revision = "037"
down_revision = "036"
branch_labels = None
depends_on = None


def upgrade() -> None:
    # project_integrations — one row per installed provider per project
    # config_json  = non-secret config (publishable keys, account IDs, from email, etc.)
    # secrets_enc  = JSONB of { secret_name: fernet_encrypted_value }
    #                Never exposed outside Core; Cloud receives opaque secret_ref only
    op.create_table(
        "project_integrations",
        sa.Column("id", postgresql.UUID(as_uuid=True), primary_key=True),
        sa.Column(
            "project_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("projects.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column("provider", sa.String(40), nullable=False),
        # active | disconnected | error
        sa.Column("status", sa.String(20), nullable=False, server_default="active"),
        sa.Column("config_json", postgresql.JSONB, nullable=True),
        # { secret_name: fernet_encrypted_value } — never leaves Core raw
        sa.Column("secrets_enc", postgresql.JSONB, nullable=True),
        sa.Column("webhook_auto_registered", sa.Boolean, nullable=False, server_default="false"),
        sa.Column("installed_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", "provider", name="uq_project_integration_project_provider"),
    )
    op.create_index("ix_project_integrations_project_id", "project_integrations", ["project_id"])
    op.create_index(
        "ix_project_integrations_project_provider",
        "project_integrations",
        ["project_id", "provider"],
        unique=True,
    )


def downgrade() -> None:
    op.drop_table("project_integrations")