"""
SQLAlchemy models for Pieria.
Phase 3: Many-to-Many relationship between Playlists and Artworks.
"""

import json
from datetime import UTC, datetime
from typing import List, Optional

from sqlalchemy import (
    Boolean,
    Column,
    DateTime,
    Float,
    ForeignKey,
    Integer,
    String,
    Table,
    Text,
    UniqueConstraint,
)
from sqlalchemy.orm import Mapped, mapped_column, relationship

from database import Base

# Association Table for Many-to-Many relationship
# Includes display_order to allow unique sequencing per playlist
playlist_artwork = Table(
    "playlist_artwork",
    Base.metadata,
    Column("playlist_id", Integer, ForeignKey("playlists.id"), primary_key=True),
    Column("artwork_id", Integer, ForeignKey("artworks.id"), primary_key=True),
    Column("display_order", Integer, default=0)
)

class ActiveDisplayModel(Base):
    """
    Tracks which displays are currently active across multiple Uvicorn workers.
    """
    __tablename__ = "active_displays"

    display_id: Mapped[str] = mapped_column(String, primary_key=True)
    last_seen_at: Mapped[datetime] = mapped_column(DateTime, default=lambda: datetime.now(UTC))
    # Now-playing: the artwork/collection currently on this display. Written by /next-image (the single
    # selection brain, used by both Canvas and e-ink); liveness (last_seen_at) stays heartbeat-owned.
    current_artwork_id: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
    current_playlist: Mapped[Optional[str]] = mapped_column(String, nullable=True)
    # 'canvas' | 'eink' — stamped by the path that owns the row (WS heartbeat / e-ink pull) so the public
    # API can report a display's kind. NULL until that display next checks in (reported as 'unknown').
    kind: Mapped[Optional[str]] = mapped_column(String, nullable=True)

class RemoteCommandModel(Base):
    """
    A persistent command queue to bridge remote commands to targeted displays.
    """
    __tablename__ = "remote_commands"

    id: Mapped[int] = mapped_column(primary_key=True, index=True)
    target_display: Mapped[str] = mapped_column(String, index=True)
    action: Mapped[str] = mapped_column(String)
    payload: Mapped[Optional[str]] = mapped_column(Text) # JSON string
    created_at: Mapped[datetime] = mapped_column(DateTime, default=lambda: datetime.now(UTC))

class DisplayPlaybackSessionModel(Base):
    """
    Tracks playback state per display to ensure variety and resume capability.
    """
    __tablename__ = "display_playback_sessions"
    # A8: one row per (display, playlist). Without this, two concurrent first-requests (4 workers) could
    # each insert a session, after which .first() picks nondeterministically and the bag state splits.
    __table_args__ = (UniqueConstraint("display_id", "playlist_id", name="uq_playback_display_playlist"),)

    id: Mapped[int] = mapped_column(primary_key=True, index=True)
    display_id: Mapped[str] = mapped_column(String, index=True)
    playlist_id: Mapped[int] = mapped_column(Integer, ForeignKey("playlists.id"), index=True)
    unplayed_artworks_json: Mapped[str] = mapped_column(Text, default="[]")
    last_sequential_index: Mapped[int] = mapped_column(Integer, default=-1)

class PlaylistModel(Base):
    """
    Table defining the artwork playlists.
    """
    __tablename__ = "playlists"

    id: Mapped[int] = mapped_column(primary_key=True, index=True)
    name: Mapped[str] = mapped_column(String, unique=True, index=True)
    display_time: Mapped[int] = mapped_column(Integer, default=30)
    default_mode: Mapped[str] = mapped_column(String, default="ken-burns")
    shuffle: Mapped[bool] = mapped_column(Boolean, default=False)
    # True for "My Photos" albums (created via the personal Studio). Keeps the personal world
    # (My Photos chips) separate from Museum collections, which stay is_personal=False.
    is_personal: Mapped[bool] = mapped_column(Boolean, default=False)

    # Placard Timers (stored in seconds)
    placard_initial_wait_sec: Mapped[int] = mapped_column(Integer, default=5)
    placard_initial_show_sec: Mapped[int] = mapped_column(Integer, default=15)
    placard_interaction_show_sec: Mapped[int] = mapped_column(Integer, default=10)

    # A Gallery (playlist) auto-minted from a downloaded Collection (pack/sub) links back to that
    # subscription — the explicit "from your <name> Collection" pairing (survives a gallery rename;
    # name-matching wouldn't). NULL for a user-built gallery. (ADR: Collections=own vs Galleries=show.)
    source_subscription_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("subscriptions.id"), nullable=True, index=True)

    # Many-to-Many relationship
    artworks: Mapped[List["ArtworkModel"]] = relationship(
        secondary=playlist_artwork,
        back_populates="playlists",
        lazy="selectin",
        order_by="playlist_artwork.c.display_order"
    )

    def __repr__(self) -> str:
        return f"<Playlist(name='{self.name}')>"

class ArtworkModel(Base):
    """
    Table defining individual artwork metadata.
    Decoupled from specific playlists to allow library-wide management.
    """
    __tablename__ = "artworks"

    id: Mapped[int] = mapped_column(primary_key=True, index=True)
    filename: Mapped[str] = mapped_column(String, index=True)

    # Original Dimensions
    original_width: Mapped[int] = mapped_column(Integer, default=0)
    original_height: Mapped[int] = mapped_column(Integer, default=0)

    # Many-to-Many relationship
    playlists: Mapped[List["PlaylistModel"]] = relationship(
        secondary=playlist_artwork,
        back_populates="artworks"
    )

    # Phase 6: Telemetry & Director Data
    affinity_score: Mapped[float] = mapped_column(Float, default=1.0)
    skip_count: Mapped[int] = mapped_column(Integer, default=0)
    total_display_time: Mapped[int] = mapped_column(Integer, default=0)

    # VRA Core Metadata
    title: Mapped[Optional[str]] = mapped_column(String, index=True)
    agent_name: Mapped[Optional[str]] = mapped_column(String, index=True)
    agent_role: Mapped[Optional[str]] = mapped_column(String, default="Artist")
    creation_date: Mapped[Optional[str]] = mapped_column(String)
    cultural_context: Mapped[Optional[str]] = mapped_column(String)
    medium: Mapped[Optional[str]] = mapped_column(String)
    date_display: Mapped[Optional[str]] = mapped_column(String)
    description_narrative: Mapped[Optional[str]] = mapped_column(Text)
    tags: Mapped[Optional[str]] = mapped_column(String)   # comma-separated
    # The series/set a print belongs to (e.g. a ukiyo-e "Famous Places…"), lifted from the source
    # title by tools/clean_titles.py. Additive placard subtitle; NULL for works with no series.
    series: Mapped[Optional[str]] = mapped_column(String)
    # Honest resolution tag ("HD"|"4K"|"8K") authored offline by tools/tag_resolution.py and carried
    # through the pack manifest (ADR resolution-tags §6). Surfaces the delivered quality on owned art.
    resolution_tier: Mapped[Optional[str]] = mapped_column(String)
    is_seed: Mapped[bool] = mapped_column(Boolean, default=False)
    # A personal photo (Studio → My Photos), not museum/catalog art: gates the jargon-free placard,
    # skips the museum AI pipeline, and keeps it out of Discover/publish.
    is_personal: Mapped[bool] = mapped_column(Boolean, default=False)

    # Provenance: where a seed/catalog/discovered work was fetched from (enables
    # re-download, dedup, and "already added" detection for the browseable catalog).
    source_url: Mapped[Optional[str]] = mapped_column(String, index=True)
    thumbnail_url: Mapped[Optional[str]] = mapped_column(String)

    # Attribution (INFRA spec_ccby_attribution.md Stage B; ADR-142). `license` is a
    # core/licensing.py PACK_ALLOWED id (e.g. "CC-BY-4.0"), never free text. `attribution` is the
    # credit line to display exactly as given (required for CC BY; PD/CC0 may carry a courtesy
    # credit). `origin_url` is the real web source (fixes the /art/{id} "View original source" link,
    # which previously fell back to a broken `pack:…` placeholder for pack installs). NULL means
    # "not known" — manual uploads leave these empty; installs never backfill (ADR-135/F8).
    license: Mapped[Optional[str]] = mapped_column(String)
    license_url: Mapped[Optional[str]] = mapped_column(String)
    attribution: Mapped[Optional[str]] = mapped_column(String)
    attribution_url: Mapped[Optional[str]] = mapped_column(String)
    origin_url: Mapped[Optional[str]] = mapped_column(String)

    status: Mapped[str] = mapped_column(String, default='pending_review', index=True)

    # ADR-148 F8: JSON array of column names the USER edited; a pack refresh never overwrites these.
    user_edited_fields: Mapped[str] = mapped_column(Text, default="[]", server_default="[]")

    # Crop Metadata (Stored in Original Pixels)
    crop_x: Mapped[Optional[float]] = mapped_column(Float, default=0.0)
    crop_y: Mapped[Optional[float]] = mapped_column(Float, default=0.0)
    crop_width: Mapped[Optional[float]] = mapped_column(Float, default=0.0)
    crop_height: Mapped[Optional[float]] = mapped_column(Float, default=0.0)

    # Focal point (normalized 0..1) — the visual subject the renderer keeps in frame when cropping
    # or panning to any aspect: the Ken Burns anchor + drift origin, and e-ink/Frame crop centering.
    # Default (0.5, 0.5) = image center = prior behavior (no regression for un-derived art).
    focal_x: Mapped[float] = mapped_column(Float, default=0.5)
    focal_y: Mapped[float] = mapped_column(Float, default=0.5)

    # Up to four per-shape crop boxes ({"16:9":[x0,y0,x1,y1], "9:16":[...], "4:3":[...], "3:4":[...]},
    # normalized 0..1), authored per work to answer "how do I best fill THIS screen shape with this
    # work" at render time (epaper.ASPECT_CROP_KEYS / pick_crop_for_aspect). Stored as JSON text, the
    # repo's JSON-in-Text convention (see unplayed_artworks_json/items_json). NULL means "not
    # derived" -> the renderer falls back to the focal cover. Not the Tier-1 crop_box/needs_frame_crop
    # (baked into the master at pack build) nor the LCD-only crop_x/crop_y/crop_width/crop_height.
    aspect_crops_json: Mapped[Optional[str]] = mapped_column(Text)

    @property
    def aspect_crops(self) -> Optional[dict]:
        """The parsed crop presets, or None. Every renderer reads them through here so the
        JSON-decode + tolerate-garbage behaviour can't drift between the e-ink, Frame and Canvas
        paths (a corrupt value must degrade to the focal cover, never break playback)."""
        if not self.aspect_crops_json:
            return None
        try:
            data = json.loads(self.aspect_crops_json)
        except (TypeError, ValueError):
            return None
        return data if isinstance(data, dict) and data else None

    def __repr__(self) -> str:
        return f"<Artwork(filename='{self.filename}', status='{self.status}')>"

class DiscoveryQueueModel(Base):
    """
    Table for new art recommendations found by Art Scouts.
    """
    __tablename__ = "discovery_queue"

    id: Mapped[int] = mapped_column(primary_key=True, index=True)
    source_url: Mapped[str] = mapped_column(String)
    thumbnail_url: Mapped[str] = mapped_column(String)
    proposed_title: Mapped[Optional[str]] = mapped_column(String)
    proposed_artist: Mapped[Optional[str]] = mapped_column(String)
    source_api: Mapped[str] = mapped_column(String)
    status: Mapped[str] = mapped_column(String, default='pending') # pending, approved, rejected
    context_hints: Mapped[Optional[str]] = mapped_column(Text)
    relevance_score: Mapped[Optional[float]] = mapped_column(Float, default=0.0)
    search_session_id: Mapped[Optional[str]] = mapped_column(String, default=None)

class SettingsModel(Base):
    """
    Table for dynamic configuration, secrets, and API keys.
    """
    __tablename__ = "settings"

    id: Mapped[int] = mapped_column(primary_key=True, index=True)
    setting_key: Mapped[str] = mapped_column(String, unique=True, index=True)
    setting_value: Mapped[str] = mapped_column(String)


class SubscriptionModel(Base):
    """A federated Manifest v2 collection the user subscribed to by URL.

    Provenance lives HERE (in our DB), established at subscribe-time — never trusted from the
    manifest body. Every browsed item inherits its origin/trust from this row, so a third-party
    feed can never masquerade as a bundled/official collection (those load from disk, not this path).
    """
    __tablename__ = "subscriptions"

    id: Mapped[int] = mapped_column(primary_key=True, index=True)
    url: Mapped[str] = mapped_column(String, unique=True, index=True)
    collection_id: Mapped[Optional[str]] = mapped_column(String)   # from the manifest
    title: Mapped[Optional[str]] = mapped_column(String)
    publisher_id: Mapped[Optional[str]] = mapped_column(String)
    publisher_name: Mapped[Optional[str]] = mapped_column(String)
    publisher_url: Mapped[Optional[str]] = mapped_column(String)
    # 'community' (added by raw URL, trust-on-first-use) vs 'verified' (registry + signature, later).
    trust: Mapped[str] = mapped_column(String, default="community")
    enabled: Mapped[bool] = mapped_column(Boolean, default=True)
    cached_manifest: Mapped[Optional[str]] = mapped_column(Text)    # last VALIDATED manifest JSON
    # sha256 of the manifest whose metadata was last applied to existing works (ADR-148 F8); a differing
    # hash on boot triggers a refresh. NULL = never recorded (refresh once).
    applied_manifest_hash: Mapped[Optional[str]] = mapped_column(String)
    # `generated_at` (signed, monotonic) of the manifest last applied; an OLDER fetched manifest is refused.
    applied_generated_at: Mapped[Optional[str]] = mapped_column(String)
    metadata_refreshed_at: Mapped[Optional[datetime]] = mapped_column(DateTime)   # last F8 refresh that ran
    # L5 key pinning (ADR-148): the publisher signing key pinned at subscribe / first signed sync. A changed
    # key blocks the feed ('changed', new key in pending_public_key) until re-trusted; 'rotated' = a
    # verified-registry rotation we followed (pending_public_key then holds the previous pin).
    pinned_public_key: Mapped[Optional[str]] = mapped_column(String)
    key_status: Mapped[str] = mapped_column(String, default="ok", server_default="ok")
    pending_public_key: Mapped[Optional[str]] = mapped_column(String)
    item_count: Mapped[int] = mapped_column(Integer, default=0)
    last_synced: Mapped[Optional[datetime]] = mapped_column(DateTime)
    last_status: Mapped[Optional[str]] = mapped_column(String)      # 'ok' | 'error: …'
    created_at: Mapped[datetime] = mapped_column(DateTime, default=lambda: datetime.now(UTC))


class PublisherCollectionModel(Base):
    """A draft collection being authored here for *publishing* as a Manifest v2 feed.

    The mirror image of SubscriptionModel: that consumes someone else's manifest; this AUTHORS one of
    our own. Items live as a JSON array (``items_json``) rather than normalized rows — the draft shape
    is 1:1 with the exported manifest, the field set is intentionally loose/forward-compatible (same
    stance as the validator), and there are no cross-collection item queries. Publisher *identity*
    (id/name/url + the Ed25519 keypair) is NOT here — it lives in SettingsModel, like every other
    secret, and is shared across all collections.
    """
    __tablename__ = "publisher_collections"

    id: Mapped[int] = mapped_column(primary_key=True, index=True)
    slug: Mapped[str] = mapped_column(String, unique=True, index=True)   # the manifest "id"
    title: Mapped[str] = mapped_column(String)
    description: Mapped[Optional[str]] = mapped_column(Text)
    default_license: Mapped[Optional[str]] = mapped_column(String)
    cover_image: Mapped[Optional[str]] = mapped_column(String)           # URL shown with the collection name
    items_json: Mapped[str] = mapped_column(Text, default="[]")          # JSON array of item dicts
    created_at: Mapped[datetime] = mapped_column(DateTime, default=lambda: datetime.now(UTC))
    updated_at: Mapped[datetime] = mapped_column(DateTime, default=lambda: datetime.now(UTC))


class ApiTokenModel(Base):
    """A bearer token for the public API (/api/v1). Only the sha256 of the token is stored — the
    plaintext exists once, in the mint response. `scopes` is a comma-separated subset of
    {read, control}. Revoked rows are kept (revoked_at) so the admin list can show history."""
    __tablename__ = "api_tokens"

    id: Mapped[int] = mapped_column(primary_key=True, index=True)
    name: Mapped[str] = mapped_column(String)
    token_hash: Mapped[str] = mapped_column(String, unique=True, index=True)
    scopes: Mapped[str] = mapped_column(String)
    created_at: Mapped[datetime] = mapped_column(DateTime, default=lambda: datetime.now(UTC))
    last_used_at: Mapped[Optional[datetime]] = mapped_column(DateTime, nullable=True)
    revoked_at: Mapped[Optional[datetime]] = mapped_column(DateTime, nullable=True)