tps-dashboard/backend/app/models/episode.py

"""
节目期次模型 — SQLModel

业务规则（.clinerules 5.3 软引用 + 快照模式）:
  - editor_id 可空（离职编导）
  - editor_name_snapshot 必填（历史导入也走快照）
  - 查询历史作品：先 editor_id，回退 editor_name_snapshot 文本匹配
"""

from datetime import date, datetime

from typing import Any

from sqlalchemy import Column
from sqlalchemy import DateTime as SADateTime
from sqlalchemy import Text
from sqlalchemy import Boolean as SABoolean
from sqlalchemy import Integer as SAInteger
from sqlalchemy import Numeric as SANumeric
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy.sql import func as sa_func
from sqlmodel import Field, SQLModel


class Episode(SQLModel, table=True):
    __tablename__ = "episodes"

    id: int | None = Field(default=None, primary_key=True)
    episode_number: int = Field(unique=True, index=True)
    program_name: str = Field(max_length=200)
    air_date: date

    # 编导（软引用 + 快照双字段）
    # 在职编导：editor_id 和 editor_name_snapshot 都填
    # 离职编导：editor_id 留空，editor_name_snapshot 填姓名
    editor_id: int | None = Field(default=None, foreign_key="users.id")
    editor_name_snapshot: str = Field(max_length=100)

    # 收视数据
    # 注意：.clinerules 5.1 颜色只对 audience_share 上色
    # audience_rating 只展示数字，不参与颜色判定
    audience_share: float | None = Field(default=None)
    audience_rating: float | None = Field(default=None)

    # 重播相关
    is_rerun: bool = Field(default=False)
    original_episode_id: int | None = Field(default=None, foreign_key="episodes.id")
    is_time_sensitive: bool = Field(default=False)

    # 外拍信息
    is_outdoor_shoot: bool = Field(default=False)
    outdoor_contact_person: str | None = Field(default=None, max_length=100)
    outdoor_contact_info: str | None = Field(default=None, max_length=200)

    # 线索
    lead_source: str | None = Field(default=None, max_length=300)

    # 责编备注
    notes: str | None = Field(default=None)

    # ── AI 打标字段（看板升级 L2→L3）──
    # 全部可空：旧数据不填，AI 打标 + 制片人审核后写入
    # 枚举值在应用层校验，不加数据库 CHECK 约束
    program_format: str | None = Field(default=None, max_length=20)          # 主题材(单选)
    equipment_domain: Any | None = Field(default=None, sa_column=Column(JSONB))  # 装备域(多选)
    scene_tags: Any | None = Field(default=None, sa_column=Column(JSONB))        # 场景标签(多选)
    tech_tags: Any | None = Field(default=None, sa_column=Column(JSONB))         # 技术线标签(多选)
    narrative_structure: str | None = Field(default=None, max_length=20)     # 叙事结构(单选)
    opening_hook: str | None = Field(default=None, max_length=10)           # 开篇钩子(单选)
    ai_label_confidence: str | None = Field(default=None, max_length=10)    # draft/reviewed/inferred

    # ── 内容摘要卡（AI 生成，供诊断报告输入）──
    content_digest: Any | None = Field(default=None, sa_column=Column(JSONB))

    created_at: datetime | None = Field(
        default=None,
        sa_column=Column(SADateTime(timezone=True), nullable=False, server_default=sa_func.now()),
    )
    updated_at: datetime | None = Field(
        default=None,
        sa_column=Column(SADateTime(timezone=True), nullable=False, server_default=sa_func.now()),
    )