"""Templates: add templates, template_plan_access tables and projects.template_id column.

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

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


def upgrade() -> None:
    # Enum type (idempotent — survives partial failed runs). Use postgresql.ENUM(..., create_type=False)
    # on columns so Alembic does not emit a second CREATE TYPE (sa.Enum can still create the type).
    op.execute(
        """
        DO $$ BEGIN
            CREATE TYPE p2hs_template_deploy_status AS ENUM ('pending', 'deploying', 'live', 'failed');
        EXCEPTION WHEN duplicate_object THEN NULL;
        END $$;
        """
    )

    p2hs_template_deploy_status = postgresql.ENUM(
        "pending",
        "deploying",
        "live",
        "failed",
        name="p2hs_template_deploy_status",
        create_type=False,
    )

    op.create_table(
        "templates",
        sa.Column("id", postgresql.UUID(as_uuid=True), primary_key=True),
        sa.Column("name", sa.String(120), nullable=False),
        sa.Column("ref", sa.String(63), nullable=False),
        sa.Column("description", sa.Text, nullable=True),
        sa.Column("thumbnail_url", sa.Text, nullable=True),
        sa.Column("tags", postgresql.JSONB, nullable=True),
        sa.Column("github_repo", sa.String(255), nullable=False),
        sa.Column("github_installation_id", sa.BigInteger, nullable=False, server_default="0"),
        sa.Column("framework", sa.String(40), nullable=False, server_default="react_app"),
        sa.Column("main_branch", sa.String(100), nullable=False, server_default="main"),
        sa.Column("dev_branch", sa.String(100), nullable=False, server_default="development"),
        sa.Column("starter_branch", sa.String(100), nullable=False, server_default="starter"),
        sa.Column("starter_sha", sa.String(40), nullable=True),
        sa.Column("demo_subdomain", sa.String(63), nullable=False),
        sa.Column("dev_subdomain", sa.String(63), nullable=False),
        sa.Column("cf_pages_project_name", sa.String(100), nullable=True),
        sa.Column("demo_url", sa.Text, nullable=True),
        sa.Column("dev_url", sa.Text, nullable=True),
        sa.Column(
            "deploy_status",
            p2hs_template_deploy_status,
            nullable=False,
            server_default="pending",
        ),
        sa.Column("is_active", sa.Boolean, nullable=False, server_default="false"),
        sa.Column("created_by", postgresql.UUID(as_uuid=True),
                  sa.ForeignKey("users.id", ondelete="SET NULL"), 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.Column("deleted_at", sa.DateTime(timezone=True), nullable=True),
    )
    op.create_index("ix_templates_ref", "templates", ["ref"], unique=True)
    op.create_index("ix_templates_github_repo", "templates", ["github_repo"])

    op.create_table(
        "template_plan_access",
        sa.Column("id", postgresql.UUID(as_uuid=True), primary_key=True),
        sa.Column("template_id", postgresql.UUID(as_uuid=True),
                  sa.ForeignKey("templates.id", ondelete="CASCADE"), nullable=False),
        sa.Column("plan_slug", sa.String(60), nullable=False),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
        sa.UniqueConstraint("template_id", "plan_slug", name="uq_template_plan_access"),
    )
    op.create_index("ix_template_plan_access_template_id", "template_plan_access", ["template_id"])

    # Add template_id to projects (soft reference — no FK to avoid circular/cascade concerns)
    op.add_column(
        "projects",
        sa.Column("template_id", postgresql.UUID(as_uuid=True), nullable=True),
    )


def downgrade() -> None:
    op.drop_column("projects", "template_id")
    op.drop_table("template_plan_access")
    op.drop_table("templates")
    op.execute("DROP TYPE IF EXISTS p2hs_template_deploy_status")