"""프로젝트 범위 모델 — PostgreSQL 단일 데이터베이스에 저장."""

from sqlalchemy import Boolean, CheckConstraint, Column, ForeignKey, Integer, String, Text, UniqueConstraint
from sqlalchemy.orm import deferred
from sqlalchemy.sql import expression
from app.core.database import Base
from app.core.file_paths import ImagePathType


class Episode(Base):
    __tablename__ = "episode"

    id = Column(Text, primary_key=True)
    project_id = Column(Text, ForeignKey("project_registry.id"), nullable=False)
    episode_number = Column(Integer, nullable=False)
    title = Column(Text, nullable=False)
    source_filename = Column(Text, nullable=False)
    source_path = Column(Text, nullable=False)
    fulltext = deferred(Column(Text))
    language = Column(Text, default="ko")
    page_count = Column(Integer)
    status = Column(Text, default="uploaded")  # uploaded|analyzing|analyzed|error
    analysis_error = Column(Text)
    summary = Column(Text)               # 에피소드 상세 요약 (분석 시 생성)
    created_at = Column(Text, nullable=False)
    updated_at = Column(Text, nullable=False)


class EntityCanon(Base):
    __tablename__ = "entity_canon"

    id = Column(Text, primary_key=True)
    project_id = Column(Text, ForeignKey("project_registry.id"), nullable=False)
    short_id = Column(Text)  # C01, L03, P05, O01 — LLM 통신용 짧은 ID
    entity_type = Column(Text, nullable=False)  # character|location|prop|outlook
    name = Column(Text, nullable=False)
    description = Column(Text)
    stable_traits = Column(Text, default="{}")
    # D6: location entity 의 space_profile 등 free-form metadata.
    # stable_traits 의 list 계약 (visual_traits) 보존을 위한 별도 column.
    metadata_json = Column(Text, nullable=False, server_default="{}", default="{}")
    t2i_prompt = Column(Text)  # 시각적 보통명사 기반 T2I 프롬프트
    status = Column(Text, default="active")
    created_at = Column(Text, nullable=False)
    updated_at = Column(Text, nullable=False)


class CharacterOutlook(Base):
    __tablename__ = "character_outlook"

    id = Column(Text, primary_key=True)
    character_id = Column(Text, ForeignKey("entity_canon.id"), nullable=False)
    outlook_id = Column(Text, ForeignKey("entity_canon.id"), nullable=False)
    project_id = Column(Text, ForeignKey("project_registry.id"), nullable=False)
    created_at = Column(Text, nullable=False)


class EntityAlias(Base):
    __tablename__ = "entity_alias"

    id = Column(Text, primary_key=True)
    canon_id = Column(Text, ForeignKey("entity_canon.id"), nullable=False)
    alias = Column(Text, nullable=False)

    __table_args__ = (UniqueConstraint("canon_id", "alias"),)


class RelationFact(Base):
    __tablename__ = "relation_fact"

    id = Column(Text, primary_key=True)
    project_id = Column(Text, ForeignKey("project_registry.id"), nullable=False)
    relation_family = Column(Text, nullable=False)
    relation_type = Column(Text, nullable=False)
    directionality = Column(Text, nullable=False)
    temporal_scope = Column(Text, nullable=False)
    continuity_priority = Column(Text, nullable=False)
    continuity_reason = Column(Text)
    created_at = Column(Text, nullable=False)


class RelationParticipant(Base):
    __tablename__ = "relation_participant"

    id = Column(Text, primary_key=True)
    relation_id = Column(Text, ForeignKey("relation_fact.id"), nullable=False)
    canon_id = Column(Text, ForeignKey("entity_canon.id"), nullable=False)
    participant_role = Column(Text, nullable=False)
    participant_order = Column(Integer, default=1)


class SceneStill(Base):
    __tablename__ = "scene_still"

    id = Column(Text, primary_key=True)
    project_id = Column(Text, ForeignKey("project_registry.id"), nullable=False)
    episode_id = Column(Text, ForeignKey("episode.id"), nullable=False)
    still_index = Column(Integer, nullable=False)
    screenplay_scene_heading = Column(Text)
    beat_title = Column(Text)
    still_frame_prompt = Column(Text)
    camera_json = Column(Text, default="{}")
    lighting_json = Column(Text, default="{}")
    visible_entities_json = Column(Text, default="[]")
    t2i_prompt_cinematic = Column(Text)    # A) wide/establishing 버전 (대표 T2I)
    t2i_prompt_closeup = Column(Text)      # B) 인물 클로즈업 버전
    t2i_variations_json = Column(Text)     # N개 T2I 변형 JSON: [{theme, theme_label, t2i_prompt}]
    t2i_composer_version = Column(Text)    # 사용된 composer 프롬프트 버전
    segment_start_char = Column(Integer)   # 시나리오 원문 세그먼트 시작 위치
    segment_end_char = Column(Integer)     # 시나리오 원문 세그먼트 끝 위치
    variation_a_type = Column(Text)        # "angle" | "color" | "angle+color" | "none"
    variation_a_angle = Column(Text)       # JSON: {"horizontal": 45, "vertical": 0, "zoom": 1.0}
    variation_a_color = Column(Text)       # color edit prompt
    variation_a_reason = Column(Text)      # LLM recommendation reason
    variation_b_type = Column(Text)
    variation_b_angle = Column(Text)
    variation_b_color = Column(Text)
    variation_b_reason = Column(Text)
    recommended_variant = Column(Text)     # "original" | "A" | "B"
    selected_variant = Column(Text)        # user override (null = use recommended)
    dependent_scene_id = Column(Text)      # 앞쪽 의존 씬 ID (같은 장소 등)
    shot_type_1 = Column(Text)             # 촬영 기법 1 (scene_cinematography)
    shot_type_2 = Column(Text)             # 촬영 기법 2 (scene_cinematography)
    scene_index = Column(Integer)           # v4: 원본 씬 번호 (그룹핑용)
    shot_index = Column(Integer)           # v4: shot 인덱스 (씬 내)
    shot_description = Column(Text)        # v4: shot 설명
    based_on_beat = Column(Integer)        # v4: 근거 beat 인덱스
    scene_type = Column(Text, default="normal")  # normal|montage|flashback|dream|voiceover|transition
    scene_summary = Column(Text)                    # 씬 요약 (200자)
    audio_entity_ids = Column(Text, default="[]")   # V/A/H: 소리만 들리는 엔티티 [short_id]
    hallucination_entity_ids = Column(Text, default="[]")  # V/A/H: 비물리적 표현 [short_id]
    is_selected = Column(Boolean, default=True)     # shot-more: shot_selection 결과, True=이미지 생성 대상
    image_generated = Column(Boolean, default=False)  # shot-more: 이미지 파이프라인 완료 여부
    status = Column(Text, default="pending")        # pending|completed|stale (force 재실행 시 stale)
    created_at = Column(Text, nullable=False)


class EntityEpisodeLink(Base):
    __tablename__ = "entity_episode_link"

    id = Column(Text, primary_key=True)
    project_id = Column(Text, ForeignKey("project_registry.id"), nullable=False)
    canon_id = Column(Text, ForeignKey("entity_canon.id"), nullable=False)
    episode_id = Column(Text, ForeignKey("episode.id"), nullable=False)
    source = Column(Text, default="extracted")
    t2i_appearance_count = Column(Integer, default=0)

    __table_args__ = (UniqueConstraint("canon_id", "episode_id"),)


class ImageAsset(Base):
    __tablename__ = "image_asset"
    __table_args__ = (
        # 절대 경로 차단 — ImagePathType bind 에서 자동 상대화 하지만 raw SQL/Core
        # insert 우회 시 절대 경로가 들어가는 사고를 DB 차원에서 마지막 차단.
        # alembic 005 가 production 에 동일 constraint 추가; 모델 정의는 fresh
        # ``Base.metadata.create_all`` (test/dev) 환경에 동등 보장.
        # 표현식은 alembic 005 CHECK_EXPR + database.py `_migrations` DO block 과 동일 — 동기화 필수.
        CheckConstraint(
            "file_path = '' OR file_path NOT LIKE '/%'",
            name="ck_image_asset_file_path_relative",
        ),
    )

    id = Column(Text, primary_key=True)
    project_id = Column(Text, ForeignKey("project_registry.id"), nullable=False)
    asset_type = Column(Text, nullable=False)  # 'reference' | 'scene'
    entity_id = Column(Text)  # for reference images
    still_id = Column(Text)   # for scene images
    episode_id = Column(Text)
    file_path = Column(ImagePathType(), nullable=False)  # bind=상대 / result=절대 (Phase 2 architectural fix)
    prompt_used = Column(Text)
    generation_model = Column(Text)
    width = Column(Integer)
    height = Column(Integer)
    status = Column(Text, default="generated")  # generated|approved|needs_fix|regenerating
    review_notes = Column(Text, default="")
    validation_score = Column(Integer)       # LVM validation score 0-100
    validation_result = Column(Text)         # JSON string of full validation result
    sanitization_strategy = Column(Text)     # null=원본, "film_previs"|"movie_poster"|"aftermath"
    original_prompt = Column(Text)           # 수정 전 원본 프롬프트 (수정된 경우에만)
    sanitization_note = Column(Text)         # GPT가 어떻게 수정했는지 설명
    variant_type = Column(Text)              # "original" | "variant_a" | "variant_b"
    angle_applied = Column(Text)             # JSON of angle params applied
    color_applied = Column(Text)             # color prompt applied
    source_image_id = Column(Text)           # parent image ID for i2i chain
    is_primary = Column(Integer, default=0)  # 1 = representative image for PDF/display
    prompt_type = Column(Text)               # "cinematic" | "closeup" | "original" | None
    code_version = Column(Text)              # from version_registry at generation time
    prompt_file_version = Column(Text)       # "t2i_composer/v1" or "prompt_sanitizer/v1" etc.
    # Task 5 (single-vs-batch reference contract §4.4): lineage refs (visible_entities
    # character/prop UUID list, NOT actual LLM-attached refs).
    # For actual refs see llm_call_log.reference_image_ids.
    reference_image_ids = Column(Text, default="[]")  # (lineage) JSON array of visible_entities character/prop UUIDs
    parent_image_id = Column(Text, ForeignKey("image_asset.id"))
    shot_index = Column(Integer)              # v4: shot 인덱스
    theme_label = Column(Text)               # v5: 변형 주제 라벨 (예: "인물 중심", "상황 중심")
    # Phase 5 — outlook 패턴 backgrounds 확장
    variant_index = Column(Integer, nullable=False, default=0, server_default="0")  # 0=floor_plan, 1+=chain_bg
    variant_label = Column(String(255), nullable=False, default="v00", server_default="v00")  # "v00"|"v01"|... (chain_bg group_id 등 LLM 생성 라벨 수용 — alembic 004)
    t2i_guide = Column(Text)                 # shot t2i 주입용 가이드 (Phase 6에서 scene_detail consumer)
    # ── 전 과정 생성 이미지 영속화 (alembic 008) ──────────────────────────────
    # 중간 생성물(가이드/스케치/항공뷰/블로킹/거부 후보 등)을 image_asset 행으로
    # 영속화하기 위한 신규 메타. 모두 nullable — 기존 최종물 행에는 부재(NULL).
    # ★ reference_image_ids 는 절대 재사용 금지(char/prop lineage 유지). 중간물의
    #   전체 입력 lineage 는 신규 input_image_ids 에 저장한다.
    input_image_ids = Column(Text)           # (lineage) JSON array of input image_asset UUIDs (i2i/ref 입력)
    stage = Column(Text)                     # 파이프라인 단계명(step): "pose_guide" | "background_render" | ...
    pipeline_role = Column(Text)             # 용도: aerial_base|shot_blocking|pose_guide|candidate|...
    is_intermediate = Column(Boolean, default=False, nullable=False, server_default=expression.false())  # 중간물 여부(캔버스 필터/접기)
    generation_call_id = Column(Text)        # → llm_call_log 조인(프롬프트/검증 SOT)
    candidate_index = Column(Integer)        # 같은 호출 내 후보 번호(거부 후보 구분)
    pipeline_metadata_json = Column(Text)    # group_id/checkpoint key/rejection reason/model params 등 확장 JSON
    # ── true-intermediate 생애 메타 (alembic 009) ──────────────────────────────
    disposition = Column(Text)               # accepted|rejected|diagnostic|cache_hit_source (NULL=미상/최종물)
    attempt_index = Column(Integer)          # 같은 논리 생성의 재시도 회차(0-base, candidate_index 와 직교)
    created_at = Column(Text, nullable=False)


class ProjectSettings(Base):
    __tablename__ = "project_settings"

    id = Column(Text, primary_key=True)
    project_id = Column(Text, ForeignKey("project_registry.id"), nullable=False, unique=True)
    composer_system_prompt = Column(Text)  # Override for T2I composer system prompt
    composer_user_prompt = Column(Text)    # Override for T2I composer user prompt
    style_rules_json = Column(Text)        # 프로젝트 단위 스타일 규칙 JSON
    world_summary = Column(Text)           # 프로젝트 단위 세계관 요약
    scene_split_threshold = Column(Integer, default=600)  # 씬 분할 글자수 임계값
    llm_config_json = Column(Text, default="{}")  # 단계별 LLM 모델 설정 JSON
    updated_at = Column(Text, nullable=False)


class WorldGuide(Base):
    __tablename__ = "world_guide"

    id = Column(Text, primary_key=True)
    project_id = Column(Text, ForeignKey("project_registry.id"), nullable=False)
    episode_id = Column(Text, ForeignKey("episode.id"))
    guide_json = Column(Text, nullable=False)
    source_hash = Column(Text)  # MD5 hash of inputs (fulltext + entities + stills count)
    created_at = Column(Text, nullable=False)


class WebbookPackage(Base):
    __tablename__ = "webbook_package"

    id = Column(Text, primary_key=True)
    project_id = Column(Text, ForeignKey("project_registry.id"), nullable=False)
    episode_id = Column(Text, ForeignKey("episode.id"))
    package_json = Column(Text, nullable=False)
    prompt_version = Column(Text)
    created_at = Column(Text, nullable=False)


class OperationLog(Base):
    """모든 파이프라인 작업의 프로비저닝 기록 — Opik 호환 구조."""
    __tablename__ = "operation_log"

    id = Column(Text, primary_key=True)
    project_id = Column(Text, ForeignKey("project_registry.id"), nullable=False)
    operation_type = Column(Text, nullable=False)  # entity_extraction / scene_still_extraction / image_generation / webbook_generation / pdf_rendering / validation
    episode_id = Column(Text)
    module_name = Column(Text, nullable=False)      # from version_registry
    module_version = Column(Text, nullable=False)
    prompt_name = Column(Text)                       # e.g., "entity_extraction/v5"
    prompt_version = Column(Text)                    # e.g., "v5"
    prompt_hash = Column(Text)                       # SHA256 of actual prompt content
    input_summary = Column(Text)                     # JSON: key inputs (truncated)
    output_summary = Column(Text)                    # JSON: key outputs (truncated)
    status = Column(Text, nullable=False)            # success / error / partial
    error_message = Column(Text)
    duration_ms = Column(Integer)
    token_usage = Column(Text, default="{}")         # JSON: {input_tokens, output_tokens, cost_usd}
    metadata_json = Column(Text, default="{}")       # JSON: extra context
    created_at = Column(Text, nullable=False)


class PipelineProgress(Base):
    """파이프라인 진행률 추적 — UI에서 폴링하여 표시."""
    __tablename__ = "pipeline_progress"

    id = Column(Text, primary_key=True)
    project_id = Column(Text, ForeignKey("project_registry.id"), nullable=False)
    episode_id = Column(Text, nullable=False)
    operation = Column(Text, nullable=False)  # analysis / image_generation / webbook / pdf_render
    status = Column(Text, default="running")  # running / completed / error
    current_step = Column(Text, default="")   # e.g., "엔티티 추출 중", "참조 이미지 생성 (5/26)"
    total_steps = Column(Integer, default=0)
    completed_steps = Column(Integer, default=0)
    error_message = Column(Text)
    started_at = Column(Text, nullable=False)
    updated_at = Column(Text, nullable=False)
    completed_at = Column(Text)


class LLMCallLog(Base):
    """모든 LLM 호출의 입력/출력 기록 — 추적 및 재활용."""
    __tablename__ = "llm_call_log"

    id = Column(Text, primary_key=True)
    project_id = Column(Text)
    episode_id = Column(Text)
    operation_type = Column(Text)       # entity_extraction / scene_analysis / outlook / image_gen / ...
    step_name = Column(Text)            # turn0 / scene_10 / translate / select_best / ...
    model_name = Column(Text, nullable=False)
    system_prompt = Column(Text)
    user_prompt = Column(Text, nullable=False)
    output_text = Column(Text)
    # Task 5 (single-vs-batch reference contract §4.4): actual ref labels attached
    # to LLM call (e.g. "character C01O02 in outfit", "BACKGROUND chain reference").
    # image_asset.reference_image_ids 는 lineage — 두 컬럼 의미가 다름.
    reference_image_ids = Column(Text, default="[]")  # (actual) JSON array of attached ref labels
    duration_ms = Column(Integer)
    input_tokens = Column(Integer)
    output_tokens = Column(Integer)
    status = Column(Text, nullable=False)  # success / error
    error_message = Column(Text)
    # Phase 4 iter 7 W3+I1 — free-form trace metadata (JSON serialized).
    # scene_image_gen / ref_image_gen 호출 시 scene_index / shot_index /
    # still_id / entity_id 같은 fan-out 단위 추적 정보를 보존. alembic 006.
    metadata_json = Column(Text)
    created_at = Column(Text, nullable=False)


class ScenePlan(Base):
    """씬 분할 계획 — 사용자 승인 전 미리보기용."""
    __tablename__ = "scene_plan"

    id = Column(Text, primary_key=True)
    project_id = Column(Text, ForeignKey("project_registry.id"), nullable=False)
    episode_id = Column(Text, ForeignKey("episode.id"), nullable=False)
    split_threshold = Column(Integer, default=600)
    segments_json = Column(Text, nullable=False)  # JSON array of segment dicts
    total_scenes = Column(Integer, nullable=False)
    status = Column(Text, default="pending")  # pending|approved|rejected
    created_at = Column(Text, nullable=False)


class GenerationTrace(Base):
    __tablename__ = "generation_trace"

    id = Column(Text, primary_key=True)
    project_id = Column(Text, ForeignKey("project_registry.id"), nullable=False)
    image_asset_id = Column(Text)  # nullable — may not have asset yet
    still_id = Column(Text)
    entity_id = Column(Text)
    attempt_number = Column(Integer, nullable=False, default=1)
    prompt_used = Column(Text, nullable=False)
    prompt_version = Column(Text, nullable=False, default="original")  # original/sanitized_v1/sanitized_v2/alternative
    model_name = Column(Text)
    status = Column(Text, nullable=False)  # success/moderation_blocked/error/timeout
    block_reason = Column(Text)  # SAFETY, HARM, etc.
    block_categories = Column(Text, default="[]")  # JSON array
    response_time_ms = Column(Integer)
    sanitizer_feedback = Column(Text)  # GPT's explanation of changes
    created_at = Column(Text, nullable=False)