"""project_deployment_targets table, preview branch tracking, provider URLs.

Revision ID: 018
Revises: 017
"""
from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op
from sqlalchemy.dialects.postgresql import JSONB

revision: str = "018"
down_revision: Union[str, None] = "017"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # --- project_deployment_targets -------------------------------------------
    op.create_table(
        "project_deployment_targets",
        sa.Column("id", sa.UUID(as_uuid=True), primary_key=True, server_default=sa.text("gen_random_uuid()")),
        sa.Column("project_id", sa.UUID(as_uuid=True), sa.ForeignKey("projects.id", ondelete="CASCADE"), nullable=False),
        sa.Column("environment", sa.String(20), nullable=False),          # production | preview
        sa.Column("target_kind", sa.String(40), nullable=False),          # cloudflare_pages | external_origin | ...
        sa.Column("routing_provider_kind", sa.String(40), nullable=False, server_default="cloudflare_w4p"),
        sa.Column("deployment_provider_kind", sa.String(40), nullable=False, server_default="cloudflare_pages"),
        # CF Pages / provider identifiers
        sa.Column("provider_project_name", sa.String(100), nullable=True),   # e.g. "p2hs-abc123ef"
        sa.Column("provider_project_id", sa.String(255), nullable=True),
        sa.Column("provider_deployment_id", sa.String(255), nullable=True),  # last known CF deployment id
        # Stable public URLs
        sa.Column("provider_preview_url", sa.Text(), nullable=True),     # CF Pages branch preview URL
        sa.Column("provider_production_url", sa.Text(), nullable=True),  # CF Pages production URL
        # Routing
        sa.Column("routing_target_descriptor", JSONB(), nullable=False, server_default=sa.text("'{}'::jsonb")),
        # State tracking
        sa.Column("latest_commit_sha", sa.String(40), nullable=True),
        sa.Column("latest_revision_id", sa.UUID(as_uuid=True), sa.ForeignKey("project_revisions.id", ondelete="SET NULL"), nullable=True),
        sa.Column("repo_linkage_status", sa.String(30), nullable=False, server_default="pending"),
        # pending | linked | github_connection_required | failed
        sa.Column("domain_status", sa.String(20), nullable=False, server_default="pending"),
        # pending | active | error
        sa.Column("custom_domain_status", sa.String(20), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
    )
    op.create_index("ix_pdt_project_env", "project_deployment_targets", ["project_id", "environment"], unique=True)

    # --- project_deploys: deployment_target_kind column -----------------------
    op.add_column(
        "project_deploys",
        sa.Column("deployment_target_kind", sa.String(40), nullable=True),
    )

    # --- project_repos: preview_branch column ---------------------------------
    op.add_column(
        "project_repos",
        sa.Column("preview_branch", sa.String(100), nullable=False, server_default="preview"),
    )

    # --- preview_environments: provider_preview_url, drop port ---------------
    op.add_column(
        "preview_environments",
        sa.Column("provider_preview_url", sa.Text(), nullable=True),
    )
    op.drop_column("preview_environments", "port")


def downgrade() -> None:
    op.add_column("preview_environments", sa.Column("port", sa.Integer(), nullable=True))
    op.drop_column("preview_environments", "provider_preview_url")
    op.drop_column("project_repos", "preview_branch")
    op.drop_column("project_deploys", "deployment_target_kind")
    op.drop_index("ix_pdt_project_env", table_name="project_deployment_targets")
    op.drop_table("project_deployment_targets")