"""SQLAlchemy ORM models for the Chrysopedia knowledge base.

Seven entities matching chrysopedia-spec.md §6.1:
  Creator, SourceVideo, TranscriptSegment, KeyMoment,
  TechniquePage, RelatedTechniqueLink, Tag
"""

from __future__ import annotations

import enum
import uuid
from datetime import datetime, timezone

from sqlalchemy import (
    BigInteger,
    Boolean,
    Enum,
    Float,
    ForeignKey,
    Index,
    Integer,
    String,
    Text,
    UniqueConstraint,
    func,
    text,
)
from sqlalchemy.dialects.postgresql import ARRAY, JSONB, UUID
from sqlalchemy.orm import Mapped, mapped_column
from sqlalchemy.orm import relationship as sa_relationship

from database import Base


# ── Enums ────────────────────────────────────────────────────────────────────

class ContentType(str, enum.Enum):
    """Source video content type."""
    tutorial = "tutorial"
    livestream = "livestream"
    breakdown = "breakdown"
    short_form = "short_form"


class ProcessingStatus(str, enum.Enum):
    """Pipeline processing status for a source video.

    User-facing lifecycle: not_started → queued → processing → complete
    Error branch: processing → error (retrigger resets to queued)
    """
    not_started = "not_started"
    queued = "queued"
    processing = "processing"
    error = "error"
    complete = "complete"


class KeyMomentContentType(str, enum.Enum):
    """Content classification for a key moment."""
    technique = "technique"
    settings = "settings"
    reasoning = "reasoning"
    workflow = "workflow"


class SourceQuality(str, enum.Enum):
    """Derived source quality for technique pages."""
    structured = "structured"
    mixed = "mixed"
    unstructured = "unstructured"


class RelationshipType(str, enum.Enum):
    """Types of links between technique pages."""
    same_technique_other_creator = "same_technique_other_creator"
    same_creator_adjacent = "same_creator_adjacent"
    general_cross_reference = "general_cross_reference"


class UserRole(str, enum.Enum):
    """Roles for authenticated users."""
    creator = "creator"
    admin = "admin"


class HighlightStatus(str, enum.Enum):
    """Triage status for highlight candidates."""
    candidate = "candidate"
    approved = "approved"
    rejected = "rejected"


class ChapterStatus(str, enum.Enum):
    """Review status for auto-detected chapters."""
    draft = "draft"
    approved = "approved"
    hidden = "hidden"


# ── Helpers ──────────────────────────────────────────────────────────────────

def _uuid_pk() -> Mapped[uuid.UUID]:
    return mapped_column(
        UUID(as_uuid=True),
        primary_key=True,
        default=uuid.uuid4,
        server_default=func.gen_random_uuid(),
    )


def _now() -> datetime:
    """Return current UTC time as a naive datetime (no tzinfo).

    PostgreSQL TIMESTAMP WITHOUT TIME ZONE columns require naive datetimes.
    asyncpg rejects timezone-aware datetimes for such columns.
    """
    return datetime.now(timezone.utc).replace(tzinfo=None)


# ── Models ───────────────────────────────────────────────────────────────────

class Creator(Base):
    __tablename__ = "creators"

    id: Mapped[uuid.UUID] = _uuid_pk()
    name: Mapped[str] = mapped_column(String(255), nullable=False)
    slug: Mapped[str] = mapped_column(String(255), unique=True, nullable=False)
    genres: Mapped[list[str] | None] = mapped_column(ARRAY(String), nullable=True)
    folder_name: Mapped[str] = mapped_column(String(255), nullable=False)
    avatar_url: Mapped[str | None] = mapped_column(String(1000), nullable=True)
    avatar_source: Mapped[str | None] = mapped_column(String(50), nullable=True)
    avatar_fetched_at: Mapped[datetime | None] = mapped_column(nullable=True)
    bio: Mapped[str | None] = mapped_column(Text, nullable=True)
    social_links: Mapped[dict | None] = mapped_column(JSONB, nullable=True)
    personality_profile: Mapped[dict | None] = mapped_column(JSONB, nullable=True)
    shorts_template: Mapped[dict | None] = mapped_column(JSONB, nullable=True)
    featured: Mapped[bool] = mapped_column(default=False, server_default="false")
    view_count: Mapped[int] = mapped_column(Integer, default=0, server_default="0")
    hidden: Mapped[bool] = mapped_column(default=False, server_default="false")
    created_at: Mapped[datetime] = mapped_column(
        default=_now, server_default=func.now()
    )
    updated_at: Mapped[datetime] = mapped_column(
        default=_now, server_default=func.now(), onupdate=_now
    )

    # relationships
    videos: Mapped[list[SourceVideo]] = sa_relationship(back_populates="creator")
    technique_pages: Mapped[list[TechniquePage]] = sa_relationship(back_populates="creator")
    posts: Mapped[list[Post]] = sa_relationship(back_populates="creator")


class User(Base):
    """Authenticated user account for the creator dashboard."""
    __tablename__ = "users"

    id: Mapped[uuid.UUID] = _uuid_pk()
    email: Mapped[str] = mapped_column(String(255), unique=True, nullable=False)
    hashed_password: Mapped[str] = mapped_column(String(255), nullable=False)
    display_name: Mapped[str] = mapped_column(String(255), nullable=False)
    role: Mapped[UserRole] = mapped_column(
        Enum(UserRole, name="user_role", create_constraint=True),
        default=UserRole.creator,
        server_default="creator",
    )
    creator_id: Mapped[uuid.UUID | None] = mapped_column(
        ForeignKey("creators.id", ondelete="SET NULL"), nullable=True
    )
    is_active: Mapped[bool] = mapped_column(
        Boolean, default=True, server_default="true"
    )
    onboarding_completed: Mapped[bool] = mapped_column(
        Boolean, default=False, server_default="false"
    )
    notification_preferences: Mapped[dict] = mapped_column(
        JSONB, nullable=False,
        server_default='{"email_digests": true, "digest_frequency": "daily"}',
    )
    created_at: Mapped[datetime] = mapped_column(
        default=_now, server_default=func.now()
    )
    updated_at: Mapped[datetime] = mapped_column(
        default=_now, server_default=func.now(), onupdate=_now
    )

    # relationships
    creator: Mapped[Creator | None] = sa_relationship()


class EmailDigestLog(Base):
    """Record of a digest email sent to a user."""
    __tablename__ = "email_digest_log"
    __table_args__ = (
        Index("ix_email_digest_log_user_sent", "user_id", "digest_sent_at"),
    )

    id: Mapped[uuid.UUID] = _uuid_pk()
    user_id: Mapped[uuid.UUID] = mapped_column(
        ForeignKey("users.id", ondelete="CASCADE"), nullable=False,
    )
    digest_sent_at: Mapped[datetime] = mapped_column(
        default=_now, server_default=func.now()
    )
    content_summary: Mapped[dict | None] = mapped_column(JSONB, nullable=True)

    # relationships
    user: Mapped[User] = sa_relationship()


class InviteCode(Base):
    """Single-use or limited-use invite codes for registration gating."""
    __tablename__ = "invite_codes"

    id: Mapped[uuid.UUID] = _uuid_pk()
    code: Mapped[str] = mapped_column(String(100), unique=True, nullable=False)
    uses_remaining: Mapped[int] = mapped_column(Integer, default=1, server_default="1")
    created_by: Mapped[uuid.UUID | None] = mapped_column(
        ForeignKey("users.id", ondelete="SET NULL"), nullable=True
    )
    expires_at: Mapped[datetime | None] = mapped_column(nullable=True)
    created_at: Mapped[datetime] = mapped_column(
        default=_now, server_default=func.now()
    )


class SourceVideo(Base):
    __tablename__ = "source_videos"

    id: Mapped[uuid.UUID] = _uuid_pk()
    creator_id: Mapped[uuid.UUID] = mapped_column(
        ForeignKey("creators.id", ondelete="CASCADE"), nullable=False
    )
    filename: Mapped[str] = mapped_column(String(500), nullable=False)
    file_path: Mapped[str] = mapped_column(String(1000), nullable=False)
    duration_seconds: Mapped[int] = mapped_column(Integer, nullable=True)
    content_type: Mapped[ContentType] = mapped_column(
        Enum(ContentType, name="content_type", create_constraint=True),
        nullable=False,
    )
    transcript_path: Mapped[str | None] = mapped_column(String(1000), nullable=True)
    content_hash: Mapped[str | None] = mapped_column(String(64), nullable=True, index=True)
    processing_status: Mapped[ProcessingStatus] = mapped_column(
        Enum(ProcessingStatus, name="processing_status", create_constraint=True),
        default=ProcessingStatus.not_started,
        server_default="not_started",
    )
    classification_data: Mapped[list | None] = mapped_column(JSONB, nullable=True)
    created_at: Mapped[datetime] = mapped_column(
        default=_now, server_default=func.now()
    )
    updated_at: Mapped[datetime] = mapped_column(
        default=_now, server_default=func.now(), onupdate=_now
    )

    # relationships
    creator: Mapped[Creator] = sa_relationship(back_populates="videos")
    segments: Mapped[list[TranscriptSegment]] = sa_relationship(back_populates="source_video")
    key_moments: Mapped[list[KeyMoment]] = sa_relationship(back_populates="source_video")


class TranscriptSegment(Base):
    __tablename__ = "transcript_segments"

    id: Mapped[uuid.UUID] = _uuid_pk()
    source_video_id: Mapped[uuid.UUID] = mapped_column(
        ForeignKey("source_videos.id", ondelete="CASCADE"), nullable=False
    )
    start_time: Mapped[float] = mapped_column(Float, nullable=False)
    end_time: Mapped[float] = mapped_column(Float, nullable=False)
    text: Mapped[str] = mapped_column(Text, nullable=False)
    segment_index: Mapped[int] = mapped_column(Integer, nullable=False)
    topic_label: Mapped[str | None] = mapped_column(String(255), nullable=True)

    # relationships
    source_video: Mapped[SourceVideo] = sa_relationship(back_populates="segments")


class KeyMoment(Base):
    __tablename__ = "key_moments"

    id: Mapped[uuid.UUID] = _uuid_pk()
    source_video_id: Mapped[uuid.UUID] = mapped_column(
        ForeignKey("source_videos.id", ondelete="CASCADE"), nullable=False
    )
    technique_page_id: Mapped[uuid.UUID | None] = mapped_column(
        ForeignKey("technique_pages.id", ondelete="SET NULL"), nullable=True
    )
    title: Mapped[str] = mapped_column(String(500), nullable=False)
    summary: Mapped[str] = mapped_column(Text, nullable=False)
    start_time: Mapped[float] = mapped_column(Float, nullable=False)
    end_time: Mapped[float] = mapped_column(Float, nullable=False)
    content_type: Mapped[KeyMomentContentType] = mapped_column(
        Enum(KeyMomentContentType, name="key_moment_content_type", create_constraint=True),
        nullable=False,
    )
    plugins: Mapped[list[str] | None] = mapped_column(ARRAY(String), nullable=True)
    raw_transcript: Mapped[str | None] = mapped_column(Text, nullable=True)
    chapter_status: Mapped[ChapterStatus] = mapped_column(
        Enum(ChapterStatus, name="chapter_status", create_constraint=True),
        nullable=False,
        server_default="draft",
        default=ChapterStatus.draft,
    )
    sort_order: Mapped[int] = mapped_column(Integer, nullable=False, server_default="0", default=0)
    created_at: Mapped[datetime] = mapped_column(
        default=_now, server_default=func.now()
    )
    updated_at: Mapped[datetime] = mapped_column(
        default=_now, server_default=func.now(), onupdate=_now
    )

    # relationships
    source_video: Mapped[SourceVideo] = sa_relationship(back_populates="key_moments")
    technique_page: Mapped[TechniquePage | None] = sa_relationship(
        back_populates="key_moments", foreign_keys=[technique_page_id]
    )


class TechniquePage(Base):
    __tablename__ = "technique_pages"

    id: Mapped[uuid.UUID] = _uuid_pk()
    creator_id: Mapped[uuid.UUID] = mapped_column(
        ForeignKey("creators.id", ondelete="CASCADE"), nullable=False
    )
    title: Mapped[str] = mapped_column(String(500), nullable=False)
    slug: Mapped[str] = mapped_column(String(500), unique=True, nullable=False)
    topic_category: Mapped[str] = mapped_column(String(255), nullable=False)
    topic_tags: Mapped[list[str] | None] = mapped_column(ARRAY(String), nullable=True)
    summary: Mapped[str | None] = mapped_column(Text, nullable=True)
    body_sections: Mapped[dict | None] = mapped_column(JSONB, nullable=True)
    body_sections_format: Mapped[str] = mapped_column(
        String(20), nullable=False, default="v1", server_default="v1"
    )
    signal_chains: Mapped[list | None] = mapped_column(JSONB, nullable=True)
    plugins: Mapped[list[str] | None] = mapped_column(ARRAY(String), nullable=True)
    source_quality: Mapped[SourceQuality | None] = mapped_column(
        Enum(SourceQuality, name="source_quality", create_constraint=True),
        nullable=True,
    )
    view_count: Mapped[int] = mapped_column(Integer, default=0, server_default="0")
    created_at: Mapped[datetime] = mapped_column(
        default=_now, server_default=func.now()
    )
    updated_at: Mapped[datetime] = mapped_column(
        default=_now, server_default=func.now(), onupdate=_now
    )

    # relationships
    creator: Mapped[Creator] = sa_relationship(back_populates="technique_pages")
    key_moments: Mapped[list[KeyMoment]] = sa_relationship(
        back_populates="technique_page", foreign_keys=[KeyMoment.technique_page_id]
    )
    versions: Mapped[list[TechniquePageVersion]] = sa_relationship(
        back_populates="technique_page", order_by="TechniquePageVersion.version_number"
    )
    outgoing_links: Mapped[list[RelatedTechniqueLink]] = sa_relationship(
        foreign_keys="RelatedTechniqueLink.source_page_id", back_populates="source_page"
    )
    incoming_links: Mapped[list[RelatedTechniqueLink]] = sa_relationship(
        foreign_keys="RelatedTechniqueLink.target_page_id", back_populates="target_page"
    )
    source_video_links: Mapped[list[TechniquePageVideo]] = sa_relationship(
        back_populates="technique_page"
    )


class RelatedTechniqueLink(Base):
    __tablename__ = "related_technique_links"
    __table_args__ = (
        UniqueConstraint("source_page_id", "target_page_id", "relationship", name="uq_technique_link"),
    )

    id: Mapped[uuid.UUID] = _uuid_pk()
    source_page_id: Mapped[uuid.UUID] = mapped_column(
        ForeignKey("technique_pages.id", ondelete="CASCADE"), nullable=False
    )
    target_page_id: Mapped[uuid.UUID] = mapped_column(
        ForeignKey("technique_pages.id", ondelete="CASCADE"), nullable=False
    )
    relationship: Mapped[RelationshipType] = mapped_column(
        Enum(RelationshipType, name="relationship_type", create_constraint=True),
        nullable=False,
    )

    # relationships
    source_page: Mapped[TechniquePage] = sa_relationship(
        foreign_keys=[source_page_id], back_populates="outgoing_links"
    )
    target_page: Mapped[TechniquePage] = sa_relationship(
        foreign_keys=[target_page_id], back_populates="incoming_links"
    )


class TechniquePageVersion(Base):
    """Snapshot of a TechniquePage before a pipeline re-synthesis overwrites it."""
    __tablename__ = "technique_page_versions"

    id: Mapped[uuid.UUID] = _uuid_pk()
    technique_page_id: Mapped[uuid.UUID] = mapped_column(
        ForeignKey("technique_pages.id", ondelete="CASCADE"), nullable=False
    )
    version_number: Mapped[int] = mapped_column(Integer, nullable=False)
    content_snapshot: Mapped[dict] = mapped_column(JSONB, nullable=False)
    pipeline_metadata: Mapped[dict | None] = mapped_column(JSONB, nullable=True)
    created_at: Mapped[datetime] = mapped_column(
        default=_now, server_default=func.now()
    )

    # relationships
    technique_page: Mapped[TechniquePage] = sa_relationship(
        back_populates="versions"
    )


class Tag(Base):
    __tablename__ = "tags"

    id: Mapped[uuid.UUID] = _uuid_pk()
    name: Mapped[str] = mapped_column(String(255), unique=True, nullable=False)
    category: Mapped[str] = mapped_column(String(255), nullable=False)
    aliases: Mapped[list[str] | None] = mapped_column(ARRAY(String), nullable=True)


class TechniquePageVideo(Base):
    """Association linking a technique page to its contributing source videos."""
    __tablename__ = "technique_page_videos"
    __table_args__ = (
        UniqueConstraint("technique_page_id", "source_video_id", name="uq_page_video"),
    )

    id: Mapped[uuid.UUID] = _uuid_pk()
    technique_page_id: Mapped[uuid.UUID] = mapped_column(
        ForeignKey("technique_pages.id", ondelete="CASCADE"), nullable=False
    )
    source_video_id: Mapped[uuid.UUID] = mapped_column(
        ForeignKey("source_videos.id", ondelete="CASCADE"), nullable=False
    )
    added_at: Mapped[datetime] = mapped_column(
        default=_now, server_default=func.now()
    )

    # relationships
    technique_page: Mapped[TechniquePage] = sa_relationship(
        back_populates="source_video_links"
    )
    source_video: Mapped[SourceVideo] = sa_relationship()


# ── Content Report Enums ─────────────────────────────────────────────────────

class ReportType(str, enum.Enum):
    """Classification of user-submitted content reports."""
    inaccurate = "inaccurate"
    missing_info = "missing_info"
    wrong_attribution = "wrong_attribution"
    formatting = "formatting"
    other = "other"


class ReportStatus(str, enum.Enum):
    """Triage status for content reports."""
    open = "open"
    acknowledged = "acknowledged"
    resolved = "resolved"
    dismissed = "dismissed"


# ── Content Report ───────────────────────────────────────────────────────────

class ContentReport(Base):
    """User-submitted report about a content issue.

    Generic: content_type + content_id can reference any entity
    (technique_page, key_moment, creator, or general).
    """
    __tablename__ = "content_reports"

    id: Mapped[uuid.UUID] = _uuid_pk()
    content_type: Mapped[str] = mapped_column(
        String(50), nullable=False, doc="Entity type: technique_page, key_moment, creator, general"
    )
    content_id: Mapped[uuid.UUID | None] = mapped_column(
        UUID(as_uuid=True), nullable=True, doc="FK to the reported entity (null for general reports)"
    )
    content_title: Mapped[str | None] = mapped_column(
        String(500), nullable=True, doc="Snapshot of entity title at report time"
    )
    report_type: Mapped[ReportType] = mapped_column(
        Enum(ReportType, name="report_type", create_constraint=True),
        nullable=False,
    )
    description: Mapped[str] = mapped_column(Text, nullable=False)
    status: Mapped[ReportStatus] = mapped_column(
        Enum(ReportStatus, name="report_status", create_constraint=True),
        default=ReportStatus.open,
        server_default="open",
    )
    admin_notes: Mapped[str | None] = mapped_column(Text, nullable=True)
    page_url: Mapped[str | None] = mapped_column(
        String(1000), nullable=True, doc="URL the user was on when reporting"
    )
    created_at: Mapped[datetime] = mapped_column(
        default=_now, server_default=func.now()
    )
    resolved_at: Mapped[datetime | None] = mapped_column(nullable=True)


# ── Pipeline Event ───────────────────────────────────────────────────────────

class SearchLog(Base):
    """Logged search query for analytics and popular searches."""
    __tablename__ = "search_log"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    query: Mapped[str] = mapped_column(String(500), nullable=False, index=True)
    scope: Mapped[str] = mapped_column(String(50), nullable=False)
    result_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    created_at: Mapped[datetime] = mapped_column(
        default=_now, server_default=func.now(), index=True
    )


class PipelineRunStatus(str, enum.Enum):
    """Status of a pipeline run."""
    running = "running"
    complete = "complete"
    error = "error"
    cancelled = "cancelled"


class PipelineRunTrigger(str, enum.Enum):
    """What initiated a pipeline run."""
    manual = "manual"
    clean_reprocess = "clean_reprocess"
    auto_ingest = "auto_ingest"
    bulk = "bulk"
    stage_rerun = "stage_rerun"


class PipelineRun(Base):
    """A single execution of the pipeline for a video.

    Each trigger/retrigger creates a new run. Events are scoped to a run
    via run_id, giving a clean audit trail per execution.
    """
    __tablename__ = "pipeline_runs"

    id: Mapped[uuid.UUID] = _uuid_pk()
    video_id: Mapped[uuid.UUID] = mapped_column(
        ForeignKey("source_videos.id", ondelete="CASCADE"), nullable=False, index=True,
    )
    run_number: Mapped[int] = mapped_column(
        Integer, nullable=False, doc="Auto-increment per video, 1-indexed"
    )
    trigger: Mapped[PipelineRunTrigger] = mapped_column(
        Enum(PipelineRunTrigger, name="pipeline_run_trigger", create_constraint=True),
        nullable=False,
    )
    status: Mapped[PipelineRunStatus] = mapped_column(
        Enum(PipelineRunStatus, name="pipeline_run_status", create_constraint=True),
        default=PipelineRunStatus.running,
        server_default="running",
    )
    started_at: Mapped[datetime] = mapped_column(
        default=_now, server_default=func.now()
    )
    finished_at: Mapped[datetime | None] = mapped_column(nullable=True)
    error_stage: Mapped[str | None] = mapped_column(String(50), nullable=True)
    total_tokens: Mapped[int] = mapped_column(Integer, default=0, server_default="0")

    # relationships
    video: Mapped[SourceVideo] = sa_relationship()
    events: Mapped[list[PipelineEvent]] = sa_relationship(
        back_populates="run", foreign_keys="PipelineEvent.run_id"
    )


# ── Pipeline Event ───────────────────────────────────────────────────────────

class PipelineEvent(Base):
    """Structured log entry for pipeline execution.

    Captures per-stage start/complete/error/llm_call events with
    token usage and optional response payloads for debugging.
    """
    __tablename__ = "pipeline_events"

    id: Mapped[uuid.UUID] = _uuid_pk()
    video_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True), nullable=False, index=True,
    )
    run_id: Mapped[uuid.UUID | None] = mapped_column(
        ForeignKey("pipeline_runs.id", ondelete="SET NULL"), nullable=True, index=True,
    )
    stage: Mapped[str] = mapped_column(
        String(50), nullable=False, doc="stage2_segmentation, stage3_extraction, etc."
    )
    event_type: Mapped[str] = mapped_column(
        String(30), nullable=False, doc="start, complete, error, llm_call"
    )
    prompt_tokens: Mapped[int | None] = mapped_column(Integer, nullable=True)
    completion_tokens: Mapped[int | None] = mapped_column(Integer, nullable=True)
    total_tokens: Mapped[int | None] = mapped_column(Integer, nullable=True)
    model: Mapped[str | None] = mapped_column(String(100), nullable=True)
    duration_ms: Mapped[int | None] = mapped_column(Integer, nullable=True)
    payload: Mapped[dict | None] = mapped_column(
        JSONB, nullable=True, doc="LLM response content, error details, stage metadata"
    )
    created_at: Mapped[datetime] = mapped_column(
        default=_now, server_default=func.now()
    )

    # Debug mode — full LLM I/O capture columns
    system_prompt_text: Mapped[str | None] = mapped_column(Text, nullable=True)
    user_prompt_text: Mapped[str | None] = mapped_column(Text, nullable=True)
    response_text: Mapped[str | None] = mapped_column(Text, nullable=True)

    # relationships
    run: Mapped[PipelineRun | None] = sa_relationship(
        back_populates="events", foreign_keys=[run_id]
    )


# ── Consent Enums ────────────────────────────────────────────────────────────

class ConsentField(str, enum.Enum):
    """Fields that can be individually consented to per video."""
    kb_inclusion = "kb_inclusion"
    training_usage = "training_usage"
    public_display = "public_display"


# ── Consent Models ───────────────────────────────────────────────────────────

class VideoConsent(Base):
    """Current consent state for a source video.

    One row per video. Mutable — updated when a creator toggles consent.
    The full change history lives in ConsentAuditLog.
    """
    __tablename__ = "video_consents"
    __table_args__ = (
        UniqueConstraint("source_video_id", name="uq_video_consent_video"),
    )

    id: Mapped[uuid.UUID] = _uuid_pk()
    source_video_id: Mapped[uuid.UUID] = mapped_column(
        ForeignKey("source_videos.id", ondelete="CASCADE"), nullable=False,
    )
    creator_id: Mapped[uuid.UUID] = mapped_column(
        ForeignKey("creators.id", ondelete="CASCADE"), nullable=False,
    )
    kb_inclusion: Mapped[bool] = mapped_column(
        Boolean, default=False, server_default="false",
    )
    training_usage: Mapped[bool] = mapped_column(
        Boolean, default=False, server_default="false",
    )
    public_display: Mapped[bool] = mapped_column(
        Boolean, default=True, server_default="true",
    )
    updated_by: Mapped[uuid.UUID] = mapped_column(
        ForeignKey("users.id", ondelete="RESTRICT"), nullable=False,
    )
    created_at: Mapped[datetime] = mapped_column(
        default=_now, server_default=func.now()
    )
    updated_at: Mapped[datetime] = mapped_column(
        default=_now, server_default=func.now(), onupdate=_now
    )

    # relationships
    source_video: Mapped[SourceVideo] = sa_relationship()
    creator: Mapped[Creator] = sa_relationship()
    audit_entries: Mapped[list[ConsentAuditLog]] = sa_relationship(
        back_populates="video_consent", order_by="ConsentAuditLog.version"
    )


class ConsentAuditLog(Base):
    """Append-only versioned record of per-field consent changes.

    Each row captures a single field change. Version is auto-assigned
    in application code (max(version) + 1 per video_consent_id).
    """
    __tablename__ = "consent_audit_log"

    id: Mapped[uuid.UUID] = _uuid_pk()
    video_consent_id: Mapped[uuid.UUID] = mapped_column(
        ForeignKey("video_consents.id", ondelete="CASCADE"), nullable=False, index=True,
    )
    version: Mapped[int] = mapped_column(Integer, nullable=False)
    field_name: Mapped[str] = mapped_column(
        String(50), nullable=False, doc="ConsentField value: kb_inclusion, training_usage, public_display"
    )
    old_value: Mapped[bool | None] = mapped_column(Boolean, nullable=True)
    new_value: Mapped[bool] = mapped_column(Boolean, nullable=False)
    changed_by: Mapped[uuid.UUID] = mapped_column(
        ForeignKey("users.id", ondelete="RESTRICT"), nullable=False,
    )
    ip_address: Mapped[str | None] = mapped_column(String(45), nullable=True)
    created_at: Mapped[datetime] = mapped_column(
        default=_now, server_default=func.now()
    )

    # relationships
    video_consent: Mapped[VideoConsent] = sa_relationship(
        back_populates="audit_entries"
    )


class ImpersonationLog(Base):
    """Audit trail for admin impersonation sessions."""
    __tablename__ = "impersonation_log"

    id: Mapped[uuid.UUID] = _uuid_pk()
    admin_user_id: Mapped[uuid.UUID] = mapped_column(
        ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True,
    )
    target_user_id: Mapped[uuid.UUID] = mapped_column(
        ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True,
    )
    action: Mapped[str] = mapped_column(
        String(10), nullable=False, doc="'start' or 'stop'"
    )
    write_mode: Mapped[bool] = mapped_column(
        default=False, server_default=text("false"),
    )
    ip_address: Mapped[str | None] = mapped_column(String(45), nullable=True)
    created_at: Mapped[datetime] = mapped_column(
        default=_now, server_default=func.now()
    )


# ── Highlight Detection ─────────────────────────────────────────────────────

class HighlightCandidate(Base):
    """Scored candidate for highlight detection, one per KeyMoment."""
    __tablename__ = "highlight_candidates"
    __table_args__ = (
        UniqueConstraint("key_moment_id", name="uq_highlight_candidate_moment"),
    )

    id: Mapped[uuid.UUID] = _uuid_pk()
    key_moment_id: Mapped[uuid.UUID] = mapped_column(
        ForeignKey("key_moments.id", ondelete="CASCADE"), nullable=False, unique=True,
    )
    source_video_id: Mapped[uuid.UUID] = mapped_column(
        ForeignKey("source_videos.id", ondelete="CASCADE"), nullable=False, index=True,
    )
    score: Mapped[float] = mapped_column(Float, nullable=False)
    score_breakdown: Mapped[dict | None] = mapped_column(JSONB, nullable=True)
    duration_secs: Mapped[float] = mapped_column(Float, nullable=False)
    status: Mapped[HighlightStatus] = mapped_column(
        Enum(HighlightStatus, name="highlight_status", create_constraint=True),
        default=HighlightStatus.candidate,
        server_default="candidate",
    )
    trim_start: Mapped[float | None] = mapped_column(Float, nullable=True, default=None)
    trim_end: Mapped[float | None] = mapped_column(Float, nullable=True, default=None)
    created_at: Mapped[datetime] = mapped_column(
        default=_now, server_default=func.now()
    )
    updated_at: Mapped[datetime] = mapped_column(
        default=_now, server_default=func.now(), onupdate=_now
    )

    # relationships
    key_moment: Mapped[KeyMoment] = sa_relationship()
    source_video: Mapped[SourceVideo] = sa_relationship()


# ── Follow System ────────────────────────────────────────────────────────────

class CreatorFollow(Base):
    """A user following a creator."""
    __tablename__ = "creator_follows"
    __table_args__ = (
        UniqueConstraint("user_id", "creator_id", name="uq_creator_follow_user_creator"),
    )

    id: Mapped[uuid.UUID] = _uuid_pk()
    user_id: Mapped[uuid.UUID] = mapped_column(
        ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True,
    )
    creator_id: Mapped[uuid.UUID] = mapped_column(
        ForeignKey("creators.id", ondelete="CASCADE"), nullable=False, index=True,
    )
    created_at: Mapped[datetime] = mapped_column(
        default=_now, server_default=func.now()
    )

    # relationships
    user: Mapped[User] = sa_relationship()
    creator: Mapped[Creator] = sa_relationship()


# ── Posts (Creator content feed) ─────────────────────────────────────────────

class Post(Base):
    """A rich text post by a creator, optionally with file attachments."""
    __tablename__ = "posts"

    id: Mapped[uuid.UUID] = _uuid_pk()
    creator_id: Mapped[uuid.UUID] = mapped_column(
        ForeignKey("creators.id", ondelete="CASCADE"), nullable=False, index=True,
    )
    title: Mapped[str] = mapped_column(String(500), nullable=False)
    body_json: Mapped[dict] = mapped_column(JSONB, nullable=False)
    is_published: Mapped[bool] = mapped_column(
        Boolean, default=False, server_default="false",
    )
    created_at: Mapped[datetime] = mapped_column(
        default=_now, server_default=func.now()
    )
    updated_at: Mapped[datetime] = mapped_column(
        default=_now, server_default=func.now(), onupdate=_now
    )

    # relationships
    creator: Mapped[Creator] = sa_relationship(back_populates="posts")
    attachments: Mapped[list[PostAttachment]] = sa_relationship(
        back_populates="post", cascade="all, delete-orphan"
    )


class PostAttachment(Base):
    """A file attachment on a post, stored in MinIO."""
    __tablename__ = "post_attachments"

    id: Mapped[uuid.UUID] = _uuid_pk()
    post_id: Mapped[uuid.UUID] = mapped_column(
        ForeignKey("posts.id", ondelete="CASCADE"), nullable=False, index=True,
    )
    filename: Mapped[str] = mapped_column(String(500), nullable=False)
    object_key: Mapped[str] = mapped_column(String(1000), nullable=False)
    content_type: Mapped[str] = mapped_column(String(255), nullable=False)
    size_bytes: Mapped[int] = mapped_column(BigInteger, nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        default=_now, server_default=func.now()
    )

    # relationships
    post: Mapped[Post] = sa_relationship(back_populates="attachments")


# ── Shorts Generation ────────────────────────────────────────────────────────

class FormatPreset(str, enum.Enum):
    """Output format presets for generated shorts."""
    vertical = "vertical"      # 9:16 (1080x1920)
    square = "square"          # 1:1  (1080x1080)
    horizontal = "horizontal"  # 16:9 (1920x1080)


class ShortStatus(str, enum.Enum):
    """Processing status for a generated short."""
    pending = "pending"
    processing = "processing"
    complete = "complete"
    failed = "failed"


class GeneratedShort(Base):
    """A video short generated from a highlight candidate in a specific format."""
    __tablename__ = "generated_shorts"

    id: Mapped[uuid.UUID] = _uuid_pk()
    highlight_candidate_id: Mapped[uuid.UUID] = mapped_column(
        ForeignKey("highlight_candidates.id", ondelete="CASCADE"),
        nullable=False, index=True,
    )
    format_preset: Mapped[FormatPreset] = mapped_column(
        Enum(FormatPreset, name="format_preset", create_constraint=True),
        nullable=False,
    )
    minio_object_key: Mapped[str | None] = mapped_column(String(1000), nullable=True)
    duration_secs: Mapped[float | None] = mapped_column(Float, nullable=True)
    width: Mapped[int] = mapped_column(Integer, nullable=False)
    height: Mapped[int] = mapped_column(Integer, nullable=False)
    file_size_bytes: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    status: Mapped[ShortStatus] = mapped_column(
        Enum(ShortStatus, name="short_status", create_constraint=True),
        default=ShortStatus.pending,
        server_default="pending",
    )
    error_message: Mapped[str | None] = mapped_column(Text, nullable=True)
    share_token: Mapped[str | None] = mapped_column(
        String(16), nullable=True, unique=True, index=True,
    )
    created_at: Mapped[datetime] = mapped_column(
        default=_now, server_default=func.now()
    )
    updated_at: Mapped[datetime] = mapped_column(
        default=_now, server_default=func.now(), onupdate=_now
    )

    captions_enabled: Mapped[bool] = mapped_column(
        Boolean, default=False, server_default=text("'false'"),
    )

    # relationships
    highlight_candidate: Mapped[HighlightCandidate] = sa_relationship()


# ── Chat Usage Tracking ──────────────────────────────────────────────────────

class ChatUsageLog(Base):
    """Per-request token usage log for chat completions.

    Append-only table — one row per chat request. Used for cost tracking,
    rate limit analytics, and the admin usage dashboard.
    """
    __tablename__ = "chat_usage_log"

    id: Mapped[uuid.UUID] = _uuid_pk()
    user_id: Mapped[uuid.UUID | None] = mapped_column(
        ForeignKey("users.id", ondelete="SET NULL"), nullable=True,
    )
    client_ip: Mapped[str | None] = mapped_column(String(45), nullable=True)
    creator_slug: Mapped[str | None] = mapped_column(String(255), nullable=True)
    query: Mapped[str] = mapped_column(Text, nullable=False)
    prompt_tokens: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    completion_tokens: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    total_tokens: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    cascade_tier: Mapped[str | None] = mapped_column(String(50), nullable=True)
    model: Mapped[str | None] = mapped_column(String(100), nullable=True)
    latency_ms: Mapped[float | None] = mapped_column(Float, nullable=True)
    created_at: Mapped[datetime] = mapped_column(
        default=_now, server_default=func.now(), index=True,
    )