"""image_asset 전 과정 생성 이미지 영속화 컬럼 + 인덱스.

전 과정 생성 이미지 DB 영속화(Phase A) — 파이프라인이 생성하는 모든 중간
이미지(가이드/스케치/항공뷰/블로킹/거부 후보 등)를 image_asset 행으로 남기기
위한 nullable add-column 마이그레이션.

설계 SOT: docs/w21b-all-image-db-persistence-20260630/design.html §10.3
계획 SOT: docs/w21b-all-image-db-persistence-20260630/plan.html Task A1

- add-column 7개 (전부 nullable; is_intermediate 만 NOT NULL server_default false).
- 인덱스 3개: (project_id,episode_id,is_intermediate) / generation_call_id /
  (project_id,episode_id,pipeline_role).
- ★ reference_image_ids 는 절대 재사용/의미 변경 금지(char/prop lineage 유지).
  중간물의 전체 입력 lineage 는 신규 input_image_ids 에 저장한다.
- idempotent: inspector 로 기존 컬럼/인덱스 존재 시 skip (007 패턴 정합 +
  startup `_migrations` IF NOT EXISTS path 와의 회귀 가드).
- downgrade = drop (인덱스 → 컬럼 순).

Revision ID: 008_image_asset_gen_cols
Revises: 007_d6_entity_metadata_json
Create Date: 2026-06-30
"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


revision: str = "008_image_asset_gen_cols"
down_revision: Union[str, None] = "007_d6_entity_metadata_json"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


_TABLE = "image_asset"

# (name, column factory) — server_default 는 마이그레이션 시점에 평가.
_NEW_COLUMNS = (
    ("input_image_ids", lambda: sa.Column("input_image_ids", sa.Text(), nullable=True)),
    ("stage", lambda: sa.Column("stage", sa.Text(), nullable=True)),
    ("pipeline_role", lambda: sa.Column("pipeline_role", sa.Text(), nullable=True)),
    (
        "is_intermediate",
        lambda: sa.Column(
            "is_intermediate",
            sa.Boolean(),
            nullable=False,
            server_default=sa.false(),
        ),
    ),
    ("generation_call_id", lambda: sa.Column("generation_call_id", sa.Text(), nullable=True)),
    ("candidate_index", lambda: sa.Column("candidate_index", sa.Integer(), nullable=True)),
    ("pipeline_metadata_json", lambda: sa.Column("pipeline_metadata_json", sa.Text(), nullable=True)),
)

# (index_name, columns)
_NEW_INDEXES = (
    ("idx_image_asset_intermediate", ["project_id", "episode_id", "is_intermediate"]),
    ("idx_image_asset_generation_call", ["generation_call_id"]),
    ("idx_image_asset_pipeline_role", ["project_id", "episode_id", "pipeline_role"]),
)


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

    existing_cols = {col["name"] for col in inspector.get_columns(_TABLE)}
    for name, factory in _NEW_COLUMNS:
        if name not in existing_cols:
            op.add_column(_TABLE, factory())

    existing_idx = {ix["name"] for ix in inspector.get_indexes(_TABLE)}
    for idx_name, cols in _NEW_INDEXES:
        if idx_name not in existing_idx:
            op.create_index(idx_name, _TABLE, cols, if_not_exists=True)


def downgrade() -> None:
    bind = op.get_bind()
    inspector = sa.inspect(bind)

    existing_idx = {ix["name"] for ix in inspector.get_indexes(_TABLE)}
    for idx_name, _cols in _NEW_INDEXES:
        if idx_name in existing_idx:
            op.drop_index(idx_name, table_name=_TABLE)

    existing_cols = {col["name"] for col in inspector.get_columns(_TABLE)}
    for name, _factory in reversed(_NEW_COLUMNS):
        if name in existing_cols:
            op.drop_column(_TABLE, name)