"""Tenant/project public refs, preview deploy environment, hosting metadata on deploys.

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

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

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


def upgrade() -> None:
    # ADD VALUE may require autocommit on some PostgreSQL versions
    with op.get_context().autocommit_block():
        op.execute(
            text("ALTER TYPE p2hs_deploy_environment ADD VALUE IF NOT EXISTS 'preview'")
        )

    op.add_column("workspaces", sa.Column("tenant_ref", sa.String(length=16), nullable=True))
    op.create_index("uq_workspaces_tenant_ref", "workspaces", ["tenant_ref"], unique=True)

    op.add_column("projects", sa.Column("project_ref", sa.String(length=16), nullable=True))
    op.add_column("projects", sa.Column("primary_hosting_provider", sa.String(length=40), nullable=True))
    op.add_column("projects", sa.Column("live_hostname", sa.Text(), nullable=True))
    op.add_column("projects", sa.Column("preview_hostname", sa.Text(), nullable=True))
    op.create_index("uq_projects_project_ref", "projects", ["project_ref"], unique=True)

    op.execute(
        text(
            """
            UPDATE workspaces
            SET tenant_ref = 'w' || substring(md5(id::text) from 1 for 8)
            WHERE tenant_ref IS NULL
            """
        )
    )
    op.execute(
        text(
            """
            UPDATE projects
            SET project_ref = 'p' || substring(md5(id::text || 'proj') from 1 for 8)
            WHERE project_ref IS NULL
            """
        )
    )

    op.add_column(
        "project_deploys",
        sa.Column("hosting_provider", sa.String(length=40), nullable=True),
    )
    op.add_column(
        "project_deploys",
        sa.Column("provider_namespace", sa.String(length=100), nullable=True),
    )
    op.add_column(
        "project_deploys",
        sa.Column("provider_version_id", sa.String(length=255), nullable=True),
    )
    op.add_column(
        "project_deploys",
        sa.Column("source_git_sha", sa.String(length=40), nullable=True),
    )
    op.add_column(
        "project_deploys",
        sa.Column("activation_status", sa.String(length=40), nullable=True),
    )
    op.add_column(
        "project_deploys",
        sa.Column("last_completed_step", sa.String(length=100), nullable=True),
    )
    op.add_column(
        "project_deploys",
        sa.Column("deployment_metadata", JSONB(), nullable=False, server_default=sa.text("'{}'::jsonb")),
    )


def downgrade() -> None:
    op.drop_column("project_deploys", "deployment_metadata")
    op.drop_column("project_deploys", "last_completed_step")
    op.drop_column("project_deploys", "activation_status")
    op.drop_column("project_deploys", "source_git_sha")
    op.drop_column("project_deploys", "provider_version_id")
    op.drop_column("project_deploys", "provider_namespace")
    op.drop_column("project_deploys", "hosting_provider")

    op.drop_index("uq_projects_project_ref", table_name="projects")
    op.drop_column("projects", "preview_hostname")
    op.drop_column("projects", "live_hostname")
    op.drop_column("projects", "primary_hosting_provider")
    op.drop_column("projects", "project_ref")

    op.drop_index("uq_workspaces_tenant_ref", table_name="workspaces")
    op.drop_column("workspaces", "tenant_ref")

    # Note: ``preview`` remains on p2hs_deploy_environment; PostgreSQL cannot drop enum labels safely here.