"""Add the governed project-scoped L3 capability registry.

Revision ID: 167
Revises: 166
"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects.postgresql import JSONB, UUID

revision = "167"
down_revision = "166"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.create_table(
        "l3_candidates",
        sa.Column("id", UUID(as_uuid=True), primary_key=True),
        sa.Column("candidate_id", sa.String(160), nullable=False, unique=True),
        sa.Column("semantic", sa.String(160), nullable=False),
        sa.Column("scope", sa.String(20), nullable=False, server_default="project"),
        sa.Column("status", sa.String(40), nullable=False, server_default="discovered"),
        sa.Column("project_id", UUID(as_uuid=True), sa.ForeignKey("projects.id", ondelete="CASCADE"), nullable=False),
        sa.Column("generation_run_id", UUID(as_uuid=True), sa.ForeignKey("codegen_runs.id", ondelete="SET NULL")),
        sa.Column("requirement_summary", sa.Text(), nullable=False),
        sa.Column("gap_reason", sa.Text(), nullable=False),
        sa.Column("architecture_manifest_sha", sa.String(64)),
        sa.Column("proposed_contract", JSONB, nullable=False, server_default=sa.text("'{}'::jsonb")),
        sa.Column("dependencies", JSONB, nullable=False, server_default=sa.text("'[]'::jsonb")),
        sa.Column("evidence", JSONB, nullable=False, server_default=sa.text("'{}'::jsonb")),
        sa.Column("source_locations", JSONB, nullable=False, server_default=sa.text("'[]'::jsonb")),
        sa.Column("related_candidate_ids", JSONB, nullable=False, server_default=sa.text("'[]'::jsonb")),
        sa.Column("commit_sha", sa.String(64)),
        sa.Column("version", sa.String(32), nullable=False, server_default="0.1.0"),
        sa.Column("promotion_candidate", sa.Boolean(), nullable=False, server_default=sa.true()),
        sa.Column("review_notes", sa.Text()),
        sa.Column("reviewed_by", UUID(as_uuid=True), sa.ForeignKey("users.id", ondelete="SET NULL")),
        sa.Column("reviewed_at", sa.DateTime(timezone=True)),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
        sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
    )
    op.create_index("ix_l3_candidates_semantic", "l3_candidates", ["semantic"])
    op.create_index("ix_l3_candidates_status", "l3_candidates", ["status"])
    op.create_index("ix_l3_candidates_project_id", "l3_candidates", ["project_id"])
    op.create_index("ix_l3_candidates_generation_run_id", "l3_candidates", ["generation_run_id"])
    op.execute("ALTER TYPE p2hs_codegen_run_status ADD VALUE IF NOT EXISTS 'completed_with_l3'")


def downgrade() -> None:
    op.drop_index("ix_l3_candidates_generation_run_id", table_name="l3_candidates")
    op.drop_index("ix_l3_candidates_project_id", table_name="l3_candidates")
    op.drop_index("ix_l3_candidates_status", table_name="l3_candidates")
    op.drop_index("ix_l3_candidates_semantic", table_name="l3_candidates")
    op.drop_table("l3_candidates")