"""Preview port + align codegen_runs ORM with legacy 001 columns.

Revision ID: 016
Revises: 015

- ``preview_environments``: add ``port`` (ORM expects it; 001 had ``build_id`` / ``adapter_type`` / ``started_at`` only).
- ``codegen_runs``: add MVP result columns; migrate ``tokens_used`` jsonb→int; copy revision from legacy refs; drop unused 001 columns.
"""
from typing import Sequence, Union

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

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


def _column_map(bind, table: str) -> dict[str, str]:
    r = bind.execute(
        text(
            """
            SELECT column_name, data_type
            FROM information_schema.columns
            WHERE table_schema = 'public' AND table_name = :t
            """
        ),
        {"t": table},
    )
    return {row[0]: row[1] for row in r}


def upgrade() -> None:
    bind = op.get_bind()

    # --- preview_environments.port ---
    prev = _column_map(bind, "preview_environments")
    if "port" not in prev:
        op.add_column(
            "preview_environments",
            sa.Column("port", sa.Integer(), nullable=True),
        )

    # --- codegen_runs: new columns from current ORM ---
    cg = _column_map(bind, "codegen_runs")

    if "result_commit_sha" not in cg:
        op.add_column(
            "codegen_runs",
            sa.Column("result_commit_sha", sa.String(length=40), nullable=True),
        )
    if "result_pr_url" not in cg:
        op.add_column(
            "codegen_runs",
            sa.Column("result_pr_url", sa.Text(), nullable=True),
        )
    if "result_summary" not in cg:
        op.add_column(
            "codegen_runs",
            sa.Column("result_summary", sa.Text(), nullable=True),
        )
    if "revision_id" not in cg:
        op.add_column(
            "codegen_runs",
            sa.Column("revision_id", postgresql.UUID(as_uuid=True), nullable=True),
        )
        op.create_foreign_key(
            "fk_codegen_runs_revision_id",
            "codegen_runs",
            "project_revisions",
            ["revision_id"],
            ["id"],
        )
    if "created_by" not in cg:
        op.add_column(
            "codegen_runs",
            sa.Column("created_by", postgresql.UUID(as_uuid=True), nullable=True),
        )
        op.create_foreign_key(
            "fk_codegen_runs_created_by",
            "codegen_runs",
            "users",
            ["created_by"],
            ["id"],
        )

    cg = _column_map(bind, "codegen_runs")
    if "input_revision_id" in cg or "output_revision_id" in cg:
        bind.execute(
            text(
                """
                UPDATE codegen_runs
                SET revision_id = COALESCE(
                    revision_id, output_revision_id, input_revision_id
                )
                WHERE revision_id IS NULL
                  AND (output_revision_id IS NOT NULL OR input_revision_id IS NOT NULL)
                """
            )
        )

    cg = _column_map(bind, "codegen_runs")
    if cg.get("tokens_used") == "jsonb":
        op.add_column(
            "codegen_runs",
            sa.Column("tokens_used_int", sa.Integer(), nullable=True),
        )
        bind.execute(
            text(
                """
                UPDATE codegen_runs
                SET tokens_used_int = CASE
                    WHEN tokens_used IS NULL THEN NULL
                    WHEN jsonb_typeof(tokens_used) = 'number'
                        THEN (tokens_used #>> '{}')::integer
                    WHEN tokens_used ? 'total' AND NULLIF(trim(tokens_used->>'total'), '') IS NOT NULL
                        THEN (tokens_used->>'total')::integer
                    ELSE NULL
                END
                """
            )
        )
        op.drop_column("codegen_runs", "tokens_used")
        op.execute(text("ALTER TABLE codegen_runs RENAME COLUMN tokens_used_int TO tokens_used"))

    for col in (
        "input_revision_id",
        "output_revision_id",
        "apply_policy",
        "pr_number",
        "ai_model",
    ):
        cg = _column_map(bind, "codegen_runs")
        if col in cg:
            op.drop_column("codegen_runs", col)


def downgrade() -> None:
    raise NotImplementedError(
        "016 reshapes codegen_runs (drops legacy columns); downgrade is not supported without a DB backup."
    )