"""Capture's tables: the downloaded files (`MediaAsset`), each user's jobs over them and the folders
they sit in, the shares and public links that hand them on, a user's quota, cookies and format
profiles, and where they stopped playing one. Only this unit writes them; another unit reads them
along a declared edge, or asks this unit's service."""

from datetime import date, datetime, timezone

from sqlalchemy import (
    BigInteger,
    Boolean,
    CheckConstraint,
    Date,
    DateTime,
    ForeignKey,
    Index,
    Integer,
    JSON,
    String,
    Text,
    UniqueConstraint,
    func,
    text,
)
from sqlalchemy.orm import Mapped, mapped_column, validates

from app.db import Base
from app.models import DISPLAY_TEXT_MAX, TimestampMixin, UpdatedAtMixin


class LibraryState(Base):
    """Where a user stopped in a library item (their own download, or one shared with them) — the
    library player's resume point. Keyed by the JOB, not the file: a job is the user's library entry,
    a re-download keeps it, and deleting the job deletes the row; the shared file under it may outlive
    both. A revoked share leaves its recipient's row behind, unread — only a job the user can still
    reach is ever read or written — and it is back if the item is shared again. Rows exist only for a
    position worth keeping (see `routes._common.kept_position`); no row means "from the start"."""

    __tablename__ = "library_states"
    __table_args__ = (UniqueConstraint("user_id", "job_id", name="uq_library_user_job"),)

    id: Mapped[int] = mapped_column(primary_key=True)
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"))
    job_id: Mapped[int] = mapped_column(
        ForeignKey("download_jobs.id", ondelete="CASCADE"), index=True
    )
    # int4: a checkpoint body must stay under POSITION_MAX_S, or the flush is a DataError (a 500).
    position_seconds: Mapped[int] = mapped_column(Integer, nullable=False)
    progress_updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)


class MediaAsset(Base, TimestampMixin):
    """A physical downloaded media file — the SHARED cache layer of the download center.

    Mirrors the app's "shared catalog, private per-user state" model: one row per unique
    ``(source_kind, source_ref, format_sig)`` is the global cache. When a second user requests
    the same video with a format that hashes to the same `format_sig`, their `download_job`
    links to this ready asset instantly — no re-download, no cost. Only the SOURCE download is
    shared; per-user edited derivatives (phase 2) are never cached here.

    `rel_path` is relative to `settings.files_root` (the mount), so the physical filename is
    system-decided and bound to the source id — never renamed (the user's custom name is display
    metadata on `download_job`, applied only when downloading to their own machine). The naming
    fields (`title`/`uploader`/`upload_date`) are denormalized so the Plex-tree path can be built
    uniformly for both catalog videos and ad-hoc URLs. `ref_count` (active jobs pointing here) frees
    the file with its last holder, and `last_access_at` orders the GC's LRU eviction. How long a file
    stays is not the asset's: each holder's job carries its own `expires_at` (its library folder's)."""

    __tablename__ = "media_assets"
    __table_args__ = (
        UniqueConstraint(
            "source_kind", "source_ref", "format_sig", name="uq_media_asset_cache_key"
        ),
    )

    id: Mapped[int] = mapped_column(primary_key=True)
    source_kind: Mapped[str] = mapped_column(String(16))  # "youtube" | "url"
    source_ref: Mapped[str] = mapped_column(String(512))  # video id, or full URL for ad-hoc
    format_sig: Mapped[str] = mapped_column(String(64))  # hash of the output-affecting spec
    status: Mapped[str] = mapped_column(
        String(16), default="pending", server_default="pending", index=True
    )  # "pending" | "ready" | "error"
    rel_path: Mapped[str | None] = mapped_column(Text)  # relative to files_root
    storyboard_path: Mapped[str | None] = mapped_column(Text)  # phase-2 filmstrip mosaic
    # A locally-generated poster frame (ffmpeg), set only when the source had NO thumbnail so the
    # card / watch page / link preview can still show an image. Relative to files_root.
    poster_path: Mapped[str | None] = mapped_column(Text)
    size_bytes: Mapped[int | None] = mapped_column(BigInteger)
    duration_s: Mapped[int | None] = mapped_column(Integer)
    width: Mapped[int | None] = mapped_column(Integer)
    height: Mapped[int | None] = mapped_column(Integer)
    container: Mapped[str | None] = mapped_column(String(16))
    vcodec: Mapped[str | None] = mapped_column(String(32))
    acodec: Mapped[str | None] = mapped_column(String(32))
    # "video" | "audio" | "file" — the media-kind discriminator that drives which library actions
    # apply (play/edit for media; download-only for a generic uploaded file). Null while an asset is
    # still pending (kind is known once the file is probed at finalize). It is the single stored home
    # of the invariant `downloads.mediakind.derive` used to compute — never re-derived in prose (an
    # earlier ad-hoc `vcodec is None and acodec` check lived only in links.py; consolidated here).
    media_kind: Mapped[str | None] = mapped_column(String(16))
    nfo_written: Mapped[bool] = mapped_column(Boolean, default=False, server_default="false")
    # Denormalized naming/metadata for the Plex tree + ad-hoc display.
    title: Mapped[str | None] = mapped_column(Text)
    uploader: Mapped[str | None] = mapped_column(String(255))
    # yt-dlp's channel/uploader page URL (channel_url|uploader_url) — lets an auto-filled channel
    # render as a clickable link. Null for sources without one (e.g. reddit) or older rows.
    uploader_url: Mapped[str | None] = mapped_column(Text)
    upload_date: Mapped[date | None] = mapped_column(Date)
    thumbnail_url: Mapped[str | None] = mapped_column(Text)
    # yt-dlp's canonical page URL for the source (webpage_url) — the clean "downloaded from"
    # reference shown in the Files library. Null until the worker extracts it (older/queued rows
    # fall back to a URL derived from source_kind+source_ref).
    source_webpage_url: Mapped[str | None] = mapped_column(Text)
    error: Mapped[str | None] = mapped_column(Text)
    ref_count: Mapped[int] = mapped_column(Integer, default=0, server_default="0")
    last_access_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    # The file's SHA-256 (hex), so a machine fetching it can check what it got. Hashed while an upload
    # streams in, otherwise on the first ask (`routes.files`); NULL = not hashed yet.
    sha256: Mapped[str | None] = mapped_column(String(64))

    @validates("rel_path")
    def _forget_hash(self, _key: str, rel_path: str | None) -> str | None:
        # A hash names the bytes at `rel_path`: a new file there (a conversion, a retry) is not them.
        self.sha256 = None
        return rel_path


class DownloadProfile(Base, TimestampMixin):
    """A named download preset — a reusable format profile.

    `user_id` NULL + `is_builtin` marks the shipped presets (Best MP4, 1080p, audio-only, …);
    a user's saved custom profiles carry their `user_id`. `spec` is the format definition
    (mode av/v/a, max_height, container, audio_format, codec prefs, embed subs/chapters/
    thumbnail, sponsorblock) that `downloads.formats` turns into a yt-dlp selector + a cache
    `format_sig`."""

    __tablename__ = "download_profiles"

    id: Mapped[int] = mapped_column(primary_key=True)
    user_id: Mapped[int | None] = mapped_column(
        ForeignKey("users.id", ondelete="CASCADE"), index=True
    )  # NULL for a builtin preset
    name: Mapped[str] = mapped_column(String(80))
    spec: Mapped[dict] = mapped_column(JSON)
    is_builtin: Mapped[bool] = mapped_column(Boolean, default=False, server_default="false")
    sort_order: Mapped[int] = mapped_column(Integer, default=0, server_default="0")


class FileFolder(Base, TimestampMixin):
    """A folder of the Files library (`app.downloads.folders`): a row, never a directory.

    A SOURCE folder (`source_key` set: youtube / plex / upload / site:<host>) is made the first time a
    user gets a file from that origin; an OWN folder (`name` set) is one the user made; the HUB folder
    (neither set, one per user) is where they swap files with a machine of theirs. Every file —
    a `DownloadJob` — sits in exactly one (`DownloadJob.folder_id`). `retention_days` is how long a
    file stays once done (NULL = until deleted); a file's own `expires_at` is stamped from it."""

    __tablename__ = "file_folders"
    __table_args__ = (
        UniqueConstraint("user_id", "source_key", name="uq_file_folder_source"),
        Index(
            "uq_file_folder_own_name",
            "user_id",
            func.lower(text("name")),
            unique=True,
            postgresql_where=text("name IS NOT NULL"),
        ),
        # One Hub folder per user.
        Index("uq_file_folder_hub", "user_id", unique=True, postgresql_where=text("kind = 'hub'")),
        CheckConstraint(
            "(kind = 'source' AND source_key IS NOT NULL AND name IS NULL)"
            " OR (kind = 'own' AND name IS NOT NULL AND source_key IS NULL)"
            " OR (kind = 'hub' AND name IS NULL AND source_key IS NULL)",
            name="ck_file_folder_kind",
        ),
    )

    id: Mapped[int] = mapped_column(primary_key=True)
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"), index=True)
    kind: Mapped[str] = mapped_column(String(16))  # folders.FolderKind
    # "site:" + a host name (DNS caps one at 253 characters).
    source_key: Mapped[str | None] = mapped_column(String(260))
    name: Mapped[str | None] = mapped_column(String(DISPLAY_TEXT_MAX))
    retention_days: Mapped[int | None] = mapped_column(Integer)


def _ready_on_insert(context) -> datetime | None:
    """A job inserted already done (an upload kept as it came) was ready the moment it was made; one
    that becomes done later is stamped then (`folders.stamp_done`, `service.finalize_if_running`)."""
    done = context.get_current_parameters().get("status") == "done"
    return datetime.now(timezone.utc) if done else None


class DownloadJob(Base, TimestampMixin, UpdatedAtMixin):
    """A user's request to download one source — the per-user layer over `MediaAsset`.

    The worker claims `queued` jobs with ``FOR UPDATE SKIP LOCKED`` and writes live
    `progress`/`speed_bps`/`eta_s`/`phase` here (the frontend polls via useLiveQuery: there is
    no server push channel, and the worker is a separate process anyway). `profile_snapshot`
    freezes the spec at enqueue so later edits/deletion of the profile don't change this job.
    `display_name` is the user's own name for the file (display + the Content-Disposition on
    local download); the on-disk asset is never renamed. `asset_id` links to the shared file
    (SET NULL if the asset is GC'd — the job then shows as expired).

    Phase 2 (editor): `job_kind='edit'` jobs derive a new per-user clip from an already-downloaded
    ready job. They still hang off a `MediaAsset` (with `source_kind='edit'`, a per-user cache key)
    so all the file-serve/ref-count/GC/quota machinery is reused; the worker branches on the
    asset's `source_kind` and runs ffmpeg instead of yt-dlp. `source_job_id`/`source_asset_id`
    point at the parent download the clip was cut from; `edit_spec` is the (trim/crop) recipe."""

    __tablename__ = "download_jobs"
    __table_args__ = (
        Index("ix_download_jobs_claim", "status", "queue_pos"),
        # Every way a job becomes done stamps when (`folders.stamp_done`, `finalize_if_running`).
        CheckConstraint(
            "status <> 'done' OR ready_at IS NOT NULL", name="ck_download_jobs_ready_at"
        ),
    )

    id: Mapped[int] = mapped_column(primary_key=True)
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"), index=True)
    asset_id: Mapped[int | None] = mapped_column(
        ForeignKey("media_assets.id", ondelete="SET NULL"), index=True
    )
    source_kind: Mapped[str] = mapped_column(String(16))  # "youtube" | "url"
    source_ref: Mapped[str] = mapped_column(String(512))
    # "download" (default), "edit" (a per-user trim/crop derivative of another job), "upload" (a
    # user's own file, converted by the worker when it is not phone-safe — see `downloads.upload`) or
    # "clip" (a range cut straight from a YouTube video or a Plex item — see `downloads.clip`).
    job_kind: Mapped[str] = mapped_column(String(16), default="download", server_default="download")
    # For edit jobs: the parent download this clip is cut from (SET NULL if the parent is deleted).
    source_job_id: Mapped[int | None] = mapped_column(
        ForeignKey("download_jobs.id", ondelete="SET NULL")
    )
    source_asset_id: Mapped[int | None] = mapped_column(
        ForeignKey("media_assets.id", ondelete="SET NULL")
    )
    # For edit jobs: the trim/crop recipe {trim?:{start_s,end_s}, crop?:{x,y,w,h}} in pixels. For clip
    # jobs: the clip spec {source, ref, start_s, end_s, audio_track?, crop?:{x,y,w,h,aspect}}, its crop
    # in fractions of the box it was drawn in (`downloads.clip.normalize`).
    edit_spec: Mapped[dict | None] = mapped_column(JSON)
    profile_id: Mapped[int | None] = mapped_column(
        ForeignKey("download_profiles.id", ondelete="SET NULL")
    )
    profile_snapshot: Mapped[dict] = mapped_column(JSON)
    display_name: Mapped[str | None] = mapped_column(String(DISPLAY_TEXT_MAX))
    # Per-user display overrides + extras (like display_name — metadata only, never the file).
    # `display_uploader`/`display_uploader_url` override the asset's auto channel name/link;
    # `extra_links` is an optional list of extra reference URLs shown on the card + watch page.
    display_uploader: Mapped[str | None] = mapped_column(String(DISPLAY_TEXT_MAX))
    display_uploader_url: Mapped[str | None] = mapped_column(Text)
    extra_links: Mapped[list | None] = mapped_column(JSON)
    status: Mapped[str] = mapped_column(
        String(16), default="queued", server_default="queued", index=True
    )  # queued | running | paused | done | error | canceled
    progress: Mapped[int] = mapped_column(Integer, default=0, server_default="0")
    speed_bps: Mapped[int | None] = mapped_column(BigInteger)
    eta_s: Mapped[int | None] = mapped_column(Integer)
    phase: Mapped[str | None] = mapped_column(String(32))
    error: Mapped[str | None] = mapped_column(Text)  # raw yt-dlp/ffmpeg text (technical detail)
    error_code: Mapped[str | None] = mapped_column(
        String(24)
    )  # category, see downloads.errors.classify
    # The host-specific failure-catalog entry on top of the category (`facebook.login_wall`), or NULL.
    error_detail: Mapped[str | None] = mapped_column(String(48))  # see downloads.errors.diagnose
    queue_pos: Mapped[int] = mapped_column(Integer, default=0, server_default="0")
    # The Files library: the folder this file sits in (`downloads.folders.place` at creation; an own
    # folder is emptied back into the source folders before it is deleted, hence RESTRICT), whether
    # the user keeps it past its folder's retention, and when it goes (stamped once done; NULL =
    # never). `expiry_warned` marks the one warning the GC sends before it deletes the file.
    folder_id: Mapped[int | None] = mapped_column(
        ForeignKey("file_folders.id", ondelete="RESTRICT"), index=True
    )
    # The origin the file came from (the source folder's key), recorded where it is placed: an edit
    # traces its origin through its parent, and that parent may be deleted or expire later — the
    # file must still know which source folder it may go back to. NULL only for a file placed before
    # the column existed outside a source folder; `folders.source_key_for` works those out.
    source_key: Mapped[str | None] = mapped_column(String(260))
    kept: Mapped[bool] = mapped_column(Boolean, default=False, server_default="false")
    expires_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), index=True)
    expiry_warned: Mapped[bool] = mapped_column(Boolean, default=False, server_default="false")
    # When the file became done — what the library's "Added" means: a download that failed for days
    # and then went through is new to its user, so it sorts as new. `created_at` stays when it was
    # asked for (the queue's order). Set again when a file is made anew (a retry, a re-download).
    ready_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True), default=_ready_on_insert
    )


class DownloadShare(Base, TimestampMixin):
    """A user→user grant: `to_user` may access `from_user`'s downloaded file (private by
    default). Since assets are already deduped/shared on disk, a share is just an ACL row."""

    __tablename__ = "download_shares"
    __table_args__ = (UniqueConstraint("job_id", "to_user_id", name="uq_download_share"),)

    id: Mapped[int] = mapped_column(primary_key=True)
    job_id: Mapped[int] = mapped_column(ForeignKey("download_jobs.id", ondelete="CASCADE"))
    from_user_id: Mapped[int] = mapped_column(
        ForeignKey("users.id", ondelete="CASCADE"), index=True
    )
    to_user_id: Mapped[int] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"), index=True)


class DownloadQuota(Base, UpdatedAtMixin):
    """Per-user download limits (admin-managed). A row exists only when an admin customizes a
    user; otherwise the sysconfig `download_default_*` values apply. `unlimited` bypasses the
    byte cap entirely (e.g. for the admin's own account)."""

    __tablename__ = "download_quotas"

    user_id: Mapped[int] = mapped_column(
        ForeignKey("users.id", ondelete="CASCADE"), primary_key=True
    )
    max_bytes: Mapped[int] = mapped_column(BigInteger)
    max_concurrent: Mapped[int] = mapped_column(Integer)
    max_jobs: Mapped[int] = mapped_column(Integer)
    unlimited: Mapped[bool] = mapped_column(Boolean, default=False, server_default="false")


class DownloadLink(Base, TimestampMixin):
    """A public capability link to one download, or to one of its owner's own folders — the "share
    by link" surface.

    Anyone holding the unguessable token can watch the file on the login-free `/watch/{token}`
    player page (Google-Drive style); a folder link opens the folder's list there, with what is in
    the folder at the time it is opened. The token itself is never stored as written: a link is
    found by its SHA-256 (`token_hash`), and `token_enc` is the Fernet-encrypted copy its owner
    copies the link from again — absent without a key (`downloads/links.py`). Optional per-link
    controls: `expires_at`, `allow_download` (stream-only vs downloadable), and a `password_hash`
    (argon2, like a user password). Revoke = delete the row. Distinct from `DownloadShare`, which
    grants a *registered* user access via the in-app "Shared with me" list."""

    __tablename__ = "download_links"
    __table_args__ = (
        # A link opens exactly one thing: a file or a folder.
        CheckConstraint("(job_id IS NULL) <> (folder_id IS NULL)", name="ck_download_link_target"),
    )

    id: Mapped[int] = mapped_column(primary_key=True)
    job_id: Mapped[int | None] = mapped_column(
        ForeignKey("download_jobs.id", ondelete="CASCADE"), index=True
    )
    folder_id: Mapped[int | None] = mapped_column(
        ForeignKey("file_folders.id", ondelete="CASCADE"), index=True
    )
    token_hash: Mapped[str] = mapped_column(String(64), unique=True)
    token_enc: Mapped[str | None] = mapped_column(Text)  # Fernet ciphertext — never returned/logged
    created_by: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"))
    allow_download: Mapped[bool] = mapped_column(Boolean, default=False, server_default="false")
    password_hash: Mapped[str | None] = mapped_column(Text)
    # Bumped whenever the password changes; folded into the signed watch grant so rotating the
    # password immediately invalidates any grants issued under the old one (instead of them living
    # out the 6h grant TTL).
    password_version: Mapped[int] = mapped_column(Integer, default=0, server_default="0")
    expires_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    view_count: Mapped[int] = mapped_column(Integer, default=0, server_default="0")


class DownloadCredential(Base, TimestampMixin, UpdatedAtMixin):
    """A user's stored login cookies for ONE auth-walled download host (Facebook, Instagram, …).

    Per-user, per-host: each user pastes their OWN browser cookies.txt (Netscape format) for a host
    where they're logged in (e.g. a closed Facebook group they belong to), and a download of a
    yt-dlp-supported, auth-walled link on that host is retried with the ACTING user's cookie. The
    blob is Fernet-encrypted at rest (`cookies_enc`) and is NEVER returned by any API/log/audit —
    the routes are write-only (host + updated_at only). See `downloads.credentials`.

    Two load-bearing security controls live around this row, NOT on it:
      * **cache privacy** — a cookie-authed download SALTS its MediaAsset `format_sig` with the
        user id, so the authed asset is private to that user and can never be cache-served to
        another user (`downloads.credentials.cookie_salt`).
      * **host scope (SEC-12)** — a cookie is attached ONLY when the download URL's netloc matches
        this row's `host_key` (`downloads.credentials.host_key_for_url`), so an arbitrary `?url=`
        can't exfiltrate the user's session to a look-alike host.

    `host_key` is a short canonical key from the supported-host registry (e.g. "facebook"), not the
    raw netloc — one credential can cover several domains of the same site (facebook.com/fb.watch)."""

    __tablename__ = "download_credentials"
    __table_args__ = (UniqueConstraint("user_id", "host_key", name="uq_download_credential"),)

    id: Mapped[int] = mapped_column(primary_key=True)
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"), index=True)
    host_key: Mapped[str] = mapped_column(String(64))
    cookies_enc: Mapped[str] = mapped_column(Text)  # Fernet ciphertext — never returned/logged