"""사람이 대상마다 **쓸 사진 한 장**을 고른 기록 (append-only).

판정(012)은 후보 한 장마다 「그 시대·그 곳 것인가」이고, 어느 장을 쓸지는 다른 물음이다
(Codex BLOCK 2026-09-02 밤). 새 선택은 앞 선택을 `supersedes_id` 로 대신한다.
★additive — 기존 표·칸을 안 건드린다. id 는 32자 안.

Revision ID: 013_grounding_ref_selection
Revises: 012_grounding_fidelity_review
"""
from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op

revision: str = "013_grounding_ref_selection"
down_revision: Union[str, None] = "012_grounding_fidelity_review"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    op.create_table(
        "grounding_reference_selection",
        sa.Column("id", sa.Text(), primary_key=True),
        sa.Column("project_id", sa.Text(), sa.ForeignKey("project_registry.id"), nullable=False),
        sa.Column("episode_id", sa.Text(), nullable=False),
        sa.Column("research_subject_id", sa.Text(), nullable=False),
        sa.Column("review_input_hash", sa.Text(), nullable=False),
        sa.Column("image_sha256", sa.Text(), nullable=False, server_default=""),
        sa.Column("selected_by", sa.Text(), nullable=False),
        sa.Column("selected_at", sa.Text(), nullable=False),
        sa.Column("supersedes_id", sa.Text(), nullable=True),
        sa.Column("idempotency_key", sa.Text(), nullable=False),
        sa.Column("created_at", sa.Text(), nullable=False),
        sa.UniqueConstraint("project_id", "episode_id", "idempotency_key",
                            name="uq_grounding_reference_selection_idem"),
    )
    op.create_index("ix_grounding_reference_selection_scope",
                    "grounding_reference_selection",
                    ["project_id", "episode_id", "research_subject_id"])


def downgrade() -> None:
    op.drop_index("ix_grounding_reference_selection_scope",
                  table_name="grounding_reference_selection")
    op.drop_table("grounding_reference_selection")