"""Project Auth: project_auth_config, project_auth_keys, project_auth_roles tables.

Revision ID: 034
Revises: 033
"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

revision = "034"
down_revision = "033"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.execute(
        """
        DO $$ BEGIN
            CREATE TYPE p2hs_project_auth_key_status AS ENUM ('active', 'rotated', 'revoked');
        EXCEPTION WHEN duplicate_object THEN NULL;
        END $$;
        """
    )
    op.execute(
        """
        DO $$ BEGIN
            CREATE TYPE p2hs_project_auth_role_creator AS ENUM ('codegen', 'user');
        EXCEPTION WHEN duplicate_object THEN NULL;
        END $$;
        """
    )

    project_auth_key_status = postgresql.ENUM(
        "active", "rotated", "revoked", name="p2hs_project_auth_key_status", create_type=False
    )
    project_auth_role_creator = postgresql.ENUM(
        "codegen", "user", name="p2hs_project_auth_role_creator", create_type=False
    )

    # project_auth_config — one row per project that has auth enabled
    op.create_table(
        "project_auth_config",
        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("enabled", sa.Boolean, nullable=False, server_default="false"),
        # JSON list of active providers e.g. ["email", "magic_link", "google"]
        sa.Column("providers", postgresql.JSONB, nullable=False, server_default="[]"),
        sa.Column("allow_signup", sa.Boolean, nullable=False, server_default="true"),
        sa.Column("require_email_verify", sa.Boolean, nullable=False, server_default="false"),
        # "platform" = P2HS shared Google app, "custom" = BYOC
        sa.Column("google_oauth_mode", sa.String(16), nullable=True),
        sa.Column("google_client_id_enc", sa.Text, nullable=True),   # Fernet-encrypted
        sa.Column("google_client_secret_enc", sa.Text, nullable=True),  # Fernet-encrypted
        sa.Column("access_token_ttl_seconds", sa.Integer, nullable=False, server_default="3600"),
        sa.Column("refresh_token_ttl_days", sa.Integer, nullable=False, server_default="30"),
        sa.Column("password_min_length", sa.Integer, nullable=False, server_default="8"),
        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="uq_project_auth_config_project"),
    )
    op.create_index("ix_project_auth_config_project_id", "project_auth_config", ["project_id"])

    # project_auth_keys — RS256 key pairs per project
    op.create_table(
        "project_auth_keys",
        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("key_id", sa.String(64), nullable=False),          # "kid" in JWT header
        sa.Column("status", project_auth_key_status, nullable=False, server_default="active"),
        sa.Column("public_key_pem", sa.Text, nullable=False),         # Plain PEM — pushed to Cloud KV
        sa.Column("private_key_enc", sa.Text, nullable=False),        # Fernet-encrypted private PEM
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
        sa.Column("rotated_at", sa.DateTime(timezone=True), nullable=True),
        sa.UniqueConstraint("project_id", "key_id", name="uq_project_auth_key_project_kid"),
    )
    op.create_index("ix_project_auth_keys_project_id", "project_auth_keys", ["project_id"])
    op.create_index(
        "ix_project_auth_keys_project_status",
        "project_auth_keys",
        ["project_id", "status"],
    )

    # project_auth_roles — custom roles per project (codegen-definable)
    op.create_table(
        "project_auth_roles",
        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("name", sa.String(64), nullable=False),
        sa.Column("description", sa.Text, nullable=True),
        sa.Column("is_default", sa.Boolean, nullable=False, server_default="false"),
        sa.Column("permissions_json", postgresql.JSONB, nullable=False, server_default="[]"),
        sa.Column(
            "created_by", project_auth_role_creator, nullable=False, server_default="user"
        ),
        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_project_auth_role_project_name"),
    )
    op.create_index("ix_project_auth_roles_project_id", "project_auth_roles", ["project_id"])


def downgrade() -> None:
    op.drop_table("project_auth_roles")
    op.drop_table("project_auth_keys")
    op.drop_table("project_auth_config")
    op.execute("DROP TYPE IF EXISTS p2hs_project_auth_role_creator")
    op.execute("DROP TYPE IF EXISTS p2hs_project_auth_key_status")