"""External Connections: project_external_connections table.

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

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


def upgrade() -> None:
    # Drop any partially-created un-prefixed types from a previous failed run (no table references them).
    op.execute("DROP TYPE IF EXISTS connection_type_enum")
    op.execute("DROP TYPE IF EXISTS connection_status_enum")

    # Create this migration's enum types with the p2hs_ prefix.
    op.execute("""
        DO $$ BEGIN
            CREATE TYPE p2hs_connection_type AS ENUM ('postgres', 'mysql', 'http_api');
        EXCEPTION WHEN duplicate_object THEN NULL;
        END $$
    """)
    op.execute("""
        DO $$ BEGIN
            CREATE TYPE p2hs_connection_status AS ENUM ('pending', 'testing', 'active', 'error');
        EXCEPTION WHEN duplicate_object THEN NULL;
        END $$
    """)

    op.create_table(
        "project_external_connections",
        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(
            "workspace_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("workspaces.id", ondelete="CASCADE"),
            nullable=False,
        ),
        # Slug identifier — unique per project
        sa.Column("name", sa.String(64), nullable=False),
        sa.Column(
            "type",
            PgEnum(name="p2hs_connection_type", create_type=False),
            nullable=False,
        ),
        sa.Column(
            "status",
            PgEnum(name="p2hs_connection_status", create_type=False),
            nullable=False,
            server_default="pending",
        ),
        sa.Column("readonly", sa.Boolean, nullable=False, server_default="false"),

        # SQL-only fields (postgres / mysql)
        sa.Column("host", sa.String(255), nullable=True),
        sa.Column("port", sa.Integer, nullable=True),
        sa.Column("database", sa.String(128), nullable=True),
        sa.Column("username", sa.String(128), nullable=True),
        # Fernet-encrypted password — never returned, never sent to Cloud
        sa.Column("password_enc", sa.Text, nullable=True),
        # require | prefer | disable
        sa.Column("ssl_mode", sa.String(16), nullable=True, server_default="require"),
        sa.Column("max_rows_per_query", sa.Integer, nullable=True, server_default="1000"),
        # Opaque Cloudflare Hyperdrive config ID — safe to store / send to Cloud
        sa.Column("hyperdrive_id", sa.String(64), nullable=True),

        # HTTP API-only fields
        sa.Column("http_base_url", sa.Text, nullable=True),
        # api_key | bearer | basic | oauth2_client
        sa.Column("http_auth_type", sa.String(20), nullable=True),
        # Fernet-encrypted auth secret (API key, bearer token, or base64 user:pass)
        sa.Column("http_auth_secret_enc", sa.Text, nullable=True),
        sa.Column("http_default_headers_json", postgresql.JSONB, nullable=True),
        sa.Column("max_response_size_bytes", sa.Integer, nullable=True),

        # Shared
        # Full information_schema snapshot: tables, columns, indexes, foreign keys
        sa.Column("schema_snapshot_json", postgresql.JSONB, nullable=True),
        sa.Column("schema_snapshot_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("last_tested_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("last_test_error", sa.Text, 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_external_connection_project_name"),
    )
    op.create_index("ix_ext_conn_project_id", "project_external_connections", ["project_id"])
    op.create_index("ix_ext_conn_workspace_id", "project_external_connections", ["workspace_id"])


def downgrade() -> None:
    op.drop_table("project_external_connections")
    op.execute("DROP TYPE IF EXISTS p2hs_connection_status")
    op.execute("DROP TYPE IF EXISTS p2hs_connection_type")