"""CF Account Pool, Cloud Instances, and Workspace Cloud Assignments.

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

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


def upgrade() -> None:
    # Enums
    op.execute(
        """
        DO $$ BEGIN
            CREATE TYPE p2hs_cf_account_tier AS ENUM ('basic', 'pro', 'enterprise');
        EXCEPTION WHEN duplicate_object THEN NULL;
        END $$;
        """
    )
    op.execute(
        """
        DO $$ BEGIN
            CREATE TYPE p2hs_cf_account_status AS ENUM ('active', 'suspended', 'full');
        EXCEPTION WHEN duplicate_object THEN NULL;
        END $$;
        """
    )
    op.execute(
        """
        DO $$ BEGIN
            CREATE TYPE p2hs_cloud_instance_status AS ENUM ('active', 'unhealthy', 'draining', 'offline');
        EXCEPTION WHEN duplicate_object THEN NULL;
        END $$;
        """
    )
    op.execute(
        """
        DO $$ BEGIN
            CREATE TYPE p2hs_workspace_cloud_assignment_status AS ENUM ('active', 'migrating', 'released');
        EXCEPTION WHEN duplicate_object THEN NULL;
        END $$;
        """
    )

    cf_account_tier = postgresql.ENUM("basic", "pro", "enterprise", name="p2hs_cf_account_tier", create_type=False)
    cf_account_status = postgresql.ENUM("active", "suspended", "full", name="p2hs_cf_account_status", create_type=False)
    cloud_instance_status = postgresql.ENUM(
        "active", "unhealthy", "draining", "offline", name="p2hs_cloud_instance_status", create_type=False
    )
    workspace_cloud_assignment_status = postgresql.ENUM(
        "active", "migrating", "released", name="p2hs_workspace_cloud_assignment_status", create_type=False
    )

    # cf_accounts — one row per Cloudflare account managed by ops
    op.create_table(
        "cf_accounts",
        sa.Column("id", postgresql.UUID(as_uuid=True), primary_key=True),
        sa.Column("account_id", sa.String(64), nullable=False),        # CF account ID
        sa.Column("account_name", sa.String(120), nullable=False),
        sa.Column("tier", cf_account_tier, nullable=False, server_default="basic"),
        sa.Column("status", cf_account_status, nullable=False, server_default="active"),
        sa.Column("max_workspaces", sa.Integer, nullable=False, server_default="500"),
        sa.Column("workspace_count", sa.Integer, nullable=False, server_default="0"),
        sa.Column("api_token_enc", sa.Text, nullable=False),           # Fernet-encrypted CF API token
        sa.Column("metadata", postgresql.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()),
    )
    op.create_index("ix_cf_accounts_account_id", "cf_accounts", ["account_id"], unique=True)
    op.create_index("ix_cf_accounts_tier_status", "cf_accounts", ["tier", "status"])

    # cloud_instances — one row per running P2HS Cloud gateway instance
    op.create_table(
        "cloud_instances",
        sa.Column("id", postgresql.UUID(as_uuid=True), primary_key=True),
        sa.Column(
            "instance_id", sa.String(128), nullable=False
        ),  # matches INSTANCE_ID env var in Cloud
        sa.Column(
            "cf_account_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("cf_accounts.id", ondelete="RESTRICT"),
            nullable=False,
        ),
        sa.Column("tier", cf_account_tier, nullable=False, server_default="basic"),
        sa.Column("gateway_url", sa.Text, nullable=False),             # e.g. https://cloudgate.p2hs.com
        sa.Column("internal_token_enc", sa.Text, nullable=False),      # Fernet-encrypted CLOUD_INTERNAL_TOKEN
        sa.Column("status", cloud_instance_status, nullable=False, server_default="active"),
        sa.Column("last_heartbeat_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("registered_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
        sa.Column("metadata", postgresql.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()),
    )
    op.create_index("ix_cloud_instances_instance_id", "cloud_instances", ["instance_id"], unique=True)
    op.create_index("ix_cloud_instances_cf_account_id", "cloud_instances", ["cf_account_id"])
    op.create_index("ix_cloud_instances_tier_status", "cloud_instances", ["tier", "status"])

    # workspace_cloud_assignment — which Cloud instance serves a workspace
    op.create_table(
        "workspace_cloud_assignment",
        sa.Column("id", postgresql.UUID(as_uuid=True), primary_key=True),
        sa.Column(
            "workspace_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("workspaces.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column(
            "cloud_instance_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("cloud_instances.id", ondelete="RESTRICT"),
            nullable=False,
        ),
        sa.Column(
            "status",
            workspace_cloud_assignment_status,
            nullable=False,
            server_default="active",
        ),
        sa.Column("assigned_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
        sa.Column("released_at", sa.DateTime(timezone=True), nullable=True),
        sa.UniqueConstraint("workspace_id", name="uq_workspace_cloud_assignment_workspace"),
    )
    op.create_index(
        "ix_workspace_cloud_assignment_workspace_id",
        "workspace_cloud_assignment",
        ["workspace_id"],
    )
    op.create_index(
        "ix_workspace_cloud_assignment_instance_id",
        "workspace_cloud_assignment",
        ["cloud_instance_id"],
    )


def downgrade() -> None:
    op.drop_table("workspace_cloud_assignment")
    op.drop_table("cloud_instances")
    op.drop_table("cf_accounts")
    op.execute("DROP TYPE IF EXISTS p2hs_workspace_cloud_assignment_status")
    op.execute("DROP TYPE IF EXISTS p2hs_cloud_instance_status")
    op.execute("DROP TYPE IF EXISTS p2hs_cf_account_status")
    op.execute("DROP TYPE IF EXISTS p2hs_cf_account_tier")