"""v0.5 — dynamic domains table and managed_repos

Replaces the hardcoded PostgreSQL ENUM `domain` type on the `topics` table
with a first-class `domains` table (FK: topics.domain_id → domains.id).
Also introduces `managed_repos` for multi-repo support per domain.

Revision ID: b1c2d3e4f5a6
Revises: a3f1c2d4e5b6
Create Date: 2026-02-28 00:00:00.000000
"""
from typing import Sequence, Union

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

revision: str = "b1c2d3e4f5a6"
down_revision: Union[str, None] = "a3f1c2d4e5b6"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None

# Canonical domain slugs matching the old ENUM values
CANONICAL_DOMAINS = [
    ("custodian", "The Custodian"),
    ("railiance", "Railiance"),
    ("markitect", "Markitect"),
    ("coulomb_social", "Coulomb.social"),
    ("personhood", "Personhood"),
    ("foerster_capabilities", "Foerster Capabilities"),
]


def upgrade() -> None:
    # ── Step 1: Create domains table ─────────────────────────────────────────
    op.create_table(
        "domains",
        sa.Column("id", postgresql.UUID(as_uuid=True), primary_key=True,
                  server_default=sa.text("gen_random_uuid()")),
        sa.Column("slug", sa.String(50), nullable=False, unique=True),
        sa.Column("name", sa.String(200), nullable=False),
        sa.Column("description", sa.Text, nullable=True),
        sa.Column("status", sa.String(20), nullable=False, server_default="active"),
        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_domains_slug", "domains", ["slug"])

    # ── Step 2: Insert 6 canonical domain rows ────────────────────────────────
    for slug, name in CANONICAL_DOMAINS:
        op.execute(sa.text(
            "INSERT INTO domains (id, slug, name, status, created_at, updated_at) "
            "VALUES (gen_random_uuid(), :slug, :name, 'active', now(), now()) "
            "ON CONFLICT (slug) DO NOTHING"
        ).bindparams(slug=slug, name=name))

    # ── Step 3: Add domain_id FK column to topics (nullable initially) ────────
    op.add_column(
        "topics",
        sa.Column("domain_id", postgresql.UUID(as_uuid=True),
                  sa.ForeignKey("domains.id", ondelete="RESTRICT"), nullable=True),
    )

    # ── Step 4: Populate domain_id from existing enum values ──────────────────
    op.execute(sa.text(
        "UPDATE topics SET domain_id = ("
        "  SELECT id FROM domains WHERE domains.slug = topics.domain::text"
        ")"
    ))

    # ── Step 5: Make domain_id NOT NULL ────────────────────────────────────────
    op.alter_column("topics", "domain_id", nullable=False)

    # ── Step 6: Drop old domain enum column ───────────────────────────────────
    op.drop_column("topics", "domain")

    # ── Step 7: Drop PostgreSQL ENUM type ─────────────────────────────────────
    op.execute(sa.text("DROP TYPE IF EXISTS domain"))

    # ── Step 8: Create managed_repos table ────────────────────────────────────
    op.create_table(
        "managed_repos",
        sa.Column("id", postgresql.UUID(as_uuid=True), primary_key=True,
                  server_default=sa.text("gen_random_uuid()")),
        sa.Column("domain_id", postgresql.UUID(as_uuid=True),
                  sa.ForeignKey("domains.id", ondelete="RESTRICT"), nullable=False),
        sa.Column("slug", sa.String(100), nullable=False, unique=True),
        sa.Column("name", sa.String(200), nullable=False),
        sa.Column("local_path", sa.Text, nullable=True),
        sa.Column("remote_url", sa.Text, nullable=True),
        sa.Column("description", sa.Text, nullable=True),
        sa.Column("status", sa.String(20), nullable=False, server_default="active"),
        sa.Column("topic_id", postgresql.UUID(as_uuid=True),
                  sa.ForeignKey("topics.id", ondelete="SET NULL"), 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_managed_repos_slug", "managed_repos", ["slug"])
    op.create_index("ix_managed_repos_domain_id", "managed_repos", ["domain_id"])


def downgrade() -> None:
    # ── Drop managed_repos ────────────────────────────────────────────────────
    op.drop_table("managed_repos")

    # ── Recreate domain ENUM type ─────────────────────────────────────────────
    domain_enum = postgresql.ENUM(
        "custodian", "railiance", "markitect", "coulomb_social",
        "personhood", "foerster_capabilities",
        name="domain", create_type=True,
    )
    domain_enum.create(op.get_bind(), checkfirst=True)

    # ── Add domain column back (nullable initially) ───────────────────────────
    op.add_column(
        "topics",
        sa.Column("domain", sa.Enum(
            "custodian", "railiance", "markitect", "coulomb_social",
            "personhood", "foerster_capabilities",
            name="domain", create_type=False,
        ), nullable=True),
    )

    # ── Populate from domain_id ────────────────────────────────────────────────
    op.execute(sa.text(
        "UPDATE topics SET domain = ("
        "  SELECT slug FROM domains WHERE domains.id = topics.domain_id"
        ")::domain"
    ))

    # ── Make NOT NULL ──────────────────────────────────────────────────────────
    op.alter_column("topics", "domain", nullable=False)

    # ── Drop domain_id ─────────────────────────────────────────────────────────
    op.drop_column("topics", "domain_id")

    # ── Drop domains table ─────────────────────────────────────────────────────
    op.drop_table("domains")
