"""add intakes + intake_notes tables (CUST-WP-0061-T01, work-record stage 3)

Revision ID: a7c3e9f1b4d2
Revises: f0a1b2c3d4e5
Create Date: 2026-07-21

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

revision = "a7c3e9f1b4d2"
down_revision = "f0a1b2c3d4e5"
branch_labels = None
depends_on = None

intakelane = postgresql.ENUM(
    "green", "blue", "yellow", "orange", "red",
    name="intakelane",
    create_type=False,
)
intakestatus = postgresql.ENUM(
    "open", "vetted", "routed", "closed",
    name="intakestatus",
    create_type=False,
)
intakeoutcome = postgresql.ENUM(
    "promoted", "declined", "absorbed",
    name="intakeoutcome",
    create_type=False,
)


def upgrade() -> None:
    intakelane.create(op.get_bind(), checkfirst=True)
    intakestatus.create(op.get_bind(), checkfirst=True)
    intakeoutcome.create(op.get_bind(), checkfirst=True)

    op.create_table(
        "intakes",
        sa.Column("id", UUID(as_uuid=True), primary_key=True),
        sa.Column("topic_id", UUID(as_uuid=True), sa.ForeignKey("topics.id", ondelete="SET NULL"), nullable=True),
        sa.Column("workplan_id", UUID(as_uuid=True), sa.ForeignKey("workplans.id", ondelete="SET NULL"), nullable=True),
        sa.Column("repo_id", UUID(as_uuid=True), sa.ForeignKey("managed_repos.id", ondelete="SET NULL"), nullable=True),
        sa.Column("title", sa.String(length=500), nullable=False),
        sa.Column("description", sa.Text(), nullable=True),
        sa.Column("lane", intakelane, nullable=False, server_default="green"),
        sa.Column("status", intakestatus, nullable=False, server_default="open"),
        sa.Column("outcome", intakeoutcome, nullable=True),
        sa.Column("origin", sa.String(length=200), nullable=True),
        sa.Column("origin_ref", sa.String(length=200), nullable=True),
        sa.Column("promoted_to", sa.String(length=200), nullable=True),
        sa.Column("source_repo_path", sa.Text(), nullable=True),
        sa.Column("routed_note", sa.Text(), nullable=True),
        sa.Column("closed_at", sa.DateTime(timezone=True), 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),
        sa.CheckConstraint(
            "topic_id IS NOT NULL OR workplan_id IS NOT NULL OR repo_id IS NOT NULL",
            name="ck_intakes_topic_or_workplan_or_repo",
        ),
        sa.CheckConstraint(
            "(status != 'closed') OR (outcome IS NOT NULL)",
            name="ck_intakes_closed_requires_outcome",
        ),
        sa.CheckConstraint(
            "(outcome != 'promoted') OR (promoted_to IS NOT NULL)",
            name="ck_intakes_promoted_requires_promoted_to",
        ),
    )
    op.create_index("ix_intakes_topic_id", "intakes", ["topic_id"])
    op.create_index("ix_intakes_workplan_id", "intakes", ["workplan_id"])
    op.create_index("ix_intakes_repo_id", "intakes", ["repo_id"])
    op.create_index("ix_intakes_status", "intakes", ["status"])
    op.create_index("ix_intakes_origin_ref", "intakes", ["origin_ref"])

    op.create_table(
        "intake_notes",
        sa.Column("id", UUID(as_uuid=True), primary_key=True),
        sa.Column("intake_id", UUID(as_uuid=True), sa.ForeignKey("intakes.id", ondelete="CASCADE"), nullable=False),
        sa.Column("author", sa.String(length=100), nullable=True),
        sa.Column("content", sa.Text(), nullable=False),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
    )
    op.create_index("ix_intake_notes_intake_id", "intake_notes", ["intake_id"])


def downgrade() -> None:
    op.drop_index("ix_intake_notes_intake_id", table_name="intake_notes")
    op.drop_table("intake_notes")
    op.drop_index("ix_intakes_origin_ref", table_name="intakes")
    op.drop_index("ix_intakes_status", table_name="intakes")
    op.drop_index("ix_intakes_repo_id", table_name="intakes")
    op.drop_index("ix_intakes_workplan_id", table_name="intakes")
    op.drop_index("ix_intakes_topic_id", table_name="intakes")
    op.drop_table("intakes")
    intakeoutcome.drop(op.get_bind(), checkfirst=True)
    intakestatus.drop(op.get_bind(), checkfirst=True)
    intakelane.drop(op.get_bind(), checkfirst=True)
