Vellum/internal/catalog/catalog.go

// Package catalog is the one place that reads items and files out of the
// database, and the one place that enforces "may this user read this item".
//
// It exists because that check had been reimplemented five times — in media,
// comics, games, jellyfin and subsonic — each with its own SQL and its own idea
// of what to do when access is denied. Five copies of one authorization rule
// means four of them can drift, and the failure mode is not a crash: it is
// silently serving somebody a library they were not granted. One implementation
// is one thing to review.
//
// Consumers depend on this package rather than on *sql.DB, which is also what
// makes the storage engine swappable: a Postgres implementation would satisfy
// this package's surface instead of editing 186 statements scattered across the
// tree.
//
// # What the Store covers, and what it deliberately does not
//
// Store is the seam, and it holds the per-item load path only: Item, ItemFor,
// Files, FilesFor, FileByID, FirstFile, FileCount. Those are the queries whose
// correctness is a security property (ItemFor is the authorization chokepoint)
// and whose shape is identical for every media kind, so one implementation is
// worth having.
//
// Aggregate and list queries — browse lists, home shelves, Subsonic albums —
// deliberately stay OUT of it. Each is a presentation concern of the package
// that owns the surface, with its own columns, ordering and JSON contract, and
// none of them is shared. Pulling them in would mean rewriting all 186
// statements to match one interface, which is the exact cost this seam exists to
// avoid, and the result would be an interface nobody could implement without
// writing the whole application's query layer twice. The seam buys a swappable
// per-item load; it is not a repository.
//
// The television tree is the one exception, and for the same reason ItemFor is
// here rather than in a caller: Seasons, Episodes and NextEpisode are not
// presentation, they are a grant-enforcing read of a parent the caller has not
// yet proved it may read. Implementing them per surface would mean implementing
// "may this user read this series" three more times.
//
// This package holds no HTTP knowledge. It returns typed errors and lets the
// caller choose a status code.
package catalog

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"path/filepath"
	"time"

	"github.com/mithral/vellum/internal/auth"
)

// ErrNotFound means the item does not exist, OR exists but the caller may not
// read it.
//
// The two are deliberately indistinguishable. A caller that could tell them
// apart would leak the existence of other people's libraries through a 403, so
// every handler turns this into a plain 404.
var ErrNotFound = errors.New("catalog: item not found")

// Item is one media item plus the root of its library.
type Item struct {
	ID        int64
	LibraryID int64
	Kind      string
	Path      string // path relative to the library root
	Title     string
	Year      int
	Missing   bool

	// SeasonNumber and EpisodeNumber are the television tree (migration 0013).
	// Both are zero for anything that is not part of it, which includes every
	// film: the columns are nullable and a film leaves both NULL. An EPISODE
	// stays kind='video' (see Kind), so an episode is recognised by its kind
	// together with a non-zero EpisodeNumber, never by kind alone.
	SeasonNumber  int
	EpisodeNumber int

	// RootPath is the absolute library root, so AbsPath can be computed.
	RootPath string

	// ModTime is the backing file's modification time, used for conditional
	// GETs. Zero when unknown.
	ModTime time.Time
}

// AbsPath returns the absolute filesystem path of the item's file.
//
// item.path is stored relative to the library root and the root is stored
// resolved, so this join cannot escape the library.
func (i Item) AbsPath() string {
	return filepath.Join(i.RootPath, filepath.FromSlash(i.Path))
}

// File is one physical file backing an item, including the ffprobe facts.
//
// The JSON tags are the frozen MediaFile contract in web/src/lib/api.ts. The
// codec fields are what let a CLIENT decide whether it can direct-play, since
// Vellum never transcodes and playability depends on the client, not the file.
type File struct {
	ID         int64  `json:"id"`
	Path       string `json:"path"`
	Size       int64  `json:"size"`
	Container  string `json:"container"`
	Missing    bool   `json:"missing"`
	DurationMS int64  `json:"duration_ms"`
	VCodec     string `json:"vcodec"`
	ACodec     string `json:"acodec"`
	Width      int    `json:"width"`
	Height     int    `json:"height"`
}

// Store is the storage seam. Implement it to run this package's per-item load
// path against something other than SQLite.
//
// The contract is the one the SQL implementation has always had: absent item and
// forbidden item both come back as ErrNotFound, and every other failure is a
// wrapped error. A caller that can distinguish the two has a bug.
type Store interface {
	Item(ctx context.Context, id int64) (Item, error)
	ItemFor(ctx context.Context, u auth.User, id int64) (Item, error)
	Files(ctx context.Context, itemID int64) ([]File, error)
	FilesFor(ctx context.Context, u auth.User, itemID int64) ([]File, error)
	FileByID(ctx context.Context, itemID, fileID int64) (File, error)
	FirstFile(ctx context.Context, itemID int64) (File, error)
	FileCount(ctx context.Context, itemID int64) (int, error)

	// Seasons returns a series' seasons in season order, Episodes a season's
	// episodes in episode order, and NextEpisode the series' first episode the
	// caller has not finished.
	//
	// All three take the caller rather than an already-authorized item because
	// the grant is the whole point of them: they read a parent the caller has
	// not named yet, so the parent has to be checked before its children are
	// listed. As everywhere else in this package, a parent the caller may not
	// read and a parent that does not exist are the same ErrNotFound.
	Seasons(ctx context.Context, u auth.User, seriesID int64) ([]Item, error)
	Episodes(ctx context.Context, u auth.User, seasonID int64) ([]Item, error)
	NextEpisode(ctx context.Context, u auth.User, seriesID int64) (Item, error)
}

const itemColumns = `
	SELECT i.id, i.library_id, i.kind, i.path, i.title, COALESCE(i.year, 0), i.missing,
	       l.root_path,
	       COALESCE((SELECT f.mtime FROM media_files f WHERE f.item_id = i.id ORDER BY f.id LIMIT 1), ''),
	       COALESCE(i.season_number, 0), COALESCE(i.episode_number, 0)
	FROM media_items i
	JOIN libraries l ON l.id = i.library_id`

const itemSelect = itemColumns + ` WHERE i.id = ?`

// scanner is one row, so the single-item and list paths share the same scan.
type scanner interface{ Scan(dest ...any) error }

// scanItem reads one row of itemColumns. A missing row is ErrNotFound, so every
// caller inherits the "absent and forbidden are the same" rule rather than
// re-deciding it.
func scanItem(row scanner) (Item, error) {
	var (
		it    Item
		mtime string
	)
	if err := row.Scan(&it.ID, &it.LibraryID, &it.Kind, &it.Path, &it.Title,
		&it.Year, &it.Missing, &it.RootPath, &mtime,
		&it.SeasonNumber, &it.EpisodeNumber); err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return Item{}, ErrNotFound
		}
		return Item{}, err
	}
	if nanos := parseUnixNano(mtime); nanos > 0 {
		it.ModTime = time.Unix(0, nanos).UTC()
	}
	return it, nil
}

// SQL is the SQLite implementation of Store, and the only thing in this package
// that knows the query text.
type SQL struct{ db *sql.DB }

var _ Store = (*SQL)(nil)

// New returns a Store backed by db. The db must already be migrated; this
// package never creates schema.
func New(db *sql.DB) *SQL { return &SQL{db: db} }

// Item reads an item WITHOUT checking access. Prefer ItemFor in any handler that
// serves a request: this exists for the scanner, jobs and tests, which have
// already established the right to touch the row.
func (s *SQL) Item(ctx context.Context, id int64) (Item, error) {
	it, err := scanItem(s.db.QueryRowContext(ctx, itemSelect, id))
	switch {
	case errors.Is(err, ErrNotFound):
		// Returned unwrapped on purpose: this message is part of the "absent
		// and forbidden are indistinguishable" contract and must stay the same
		// one every caller gets.
		return Item{}, ErrNotFound
	case err != nil:
		return Item{}, fmt.Errorf("catalog: load item: %w", err)
	}
	return it, nil
}

// ItemFor reads an item and confirms the user may read its library.
//
// This is the authorization chokepoint. Every HTTP surface — the web app, the
// modules, Jellyfin, Subsonic — is expected to go through it, so there is one
// implementation to audit rather than five to keep in sync.
func (s *SQL) ItemFor(ctx context.Context, u auth.User, id int64) (Item, error) {
	it, err := s.Item(ctx, id)
	if err != nil {
		return Item{}, err
	}
	allowed, err := auth.CanAccessLibrary(ctx, s.db, u, it.LibraryID)
	if err != nil {
		return Item{}, fmt.Errorf("catalog: check access: %w", err)
	}
	if !allowed {
		// Deliberately the same error as a missing row: see ErrNotFound.
		return Item{}, ErrNotFound
	}
	return it, nil
}

// Files returns an item's files in order, with codec facts.
func (s *SQL) Files(ctx context.Context, itemID int64) ([]File, error) {
	rows, err := s.db.QueryContext(ctx, `
		SELECT id, path, size, container, missing,
		       duration_ms, vcodec, acodec, width, height
		FROM media_files WHERE item_id = ? ORDER BY id`, itemID)
	if err != nil {
		return nil, fmt.Errorf("catalog: list files: %w", err)
	}
	defer rows.Close()

	out := []File{}
	for rows.Next() {
		var f File
		if err := rows.Scan(&f.ID, &f.Path, &f.Size, &f.Container, &f.Missing,
			&f.DurationMS, &f.VCodec, &f.ACodec, &f.Width, &f.Height); err != nil {
			return nil, fmt.Errorf("catalog: scan file: %w", err)
		}
		out = append(out, f)
	}
	return out, rows.Err()
}

// FilesFor returns an item's files after confirming access. Callers that
// already hold an authorized Item should use Files to avoid a second check.
func (s *SQL) FilesFor(ctx context.Context, u auth.User, itemID int64) ([]File, error) {
	if _, err := s.ItemFor(ctx, u, itemID); err != nil {
		return nil, err
	}
	return s.Files(ctx, itemID)
}

// FileByID returns one file of an item, scoped to that item so a file id from
// another item cannot be fetched through it.
func (s *SQL) FileByID(ctx context.Context, itemID, fileID int64) (File, error) {
	var f File
	err := s.db.QueryRowContext(ctx, `
		SELECT id, path, size, container, missing,
		       duration_ms, vcodec, acodec, width, height
		FROM media_files WHERE item_id = ? AND id = ?`, itemID, fileID).Scan(
		&f.ID, &f.Path, &f.Size, &f.Container, &f.Missing,
		&f.DurationMS, &f.VCodec, &f.ACodec, &f.Width, &f.Height)
	if errors.Is(err, sql.ErrNoRows) {
		return File{}, ErrNotFound
	}
	if err != nil {
		return File{}, fmt.Errorf("catalog: load file: %w", err)
	}
	return f, nil
}

// FirstFile returns the item's first file, which is what single-file playback
// and cover extraction both want.
func (s *SQL) FirstFile(ctx context.Context, itemID int64) (File, error) {
	var f File
	err := s.db.QueryRowContext(ctx, `
		SELECT id, path, size, container, missing,
		       duration_ms, vcodec, acodec, width, height
		FROM media_files WHERE item_id = ? ORDER BY id LIMIT 1`, itemID).Scan(
		&f.ID, &f.Path, &f.Size, &f.Container, &f.Missing,
		&f.DurationMS, &f.VCodec, &f.ACodec, &f.Width, &f.Height)
	if errors.Is(err, sql.ErrNoRows) {
		return File{}, ErrNotFound
	}
	if err != nil {
		return File{}, fmt.Errorf("catalog: first file: %w", err)
	}
	return f, nil
}

// FileCount reports how many files back an item.
func (s *SQL) FileCount(ctx context.Context, itemID int64) (int, error) {
	var n int
	err := s.db.QueryRowContext(ctx,
		`SELECT count(*) FROM media_files WHERE item_id = ?`, itemID).Scan(&n)
	if err != nil {
		return 0, fmt.Errorf("catalog: file count: %w", err)
	}
	return n, nil
}

// ── the television tree ─────────────────────────────────────────────────
//
// library -> series -> season -> episode, by parent_id (migration 0013). Each
// read below loads the parent through ItemFor first, so a caller with no grant
// on the series' library gets the same ErrNotFound an absent series would give,
// and only then walks its children — which are additionally scoped to the
// parent's own library, so a corrupt parent_id can never reach across it.

// Seasons returns a series' seasons, ordered by season number.
//
// The number is the order, not the title and not the insertion order: a rescan
// upserts by path, so "Season 10" and "Season 2" arrive in whatever order the
// filesystem walk produced, and a title sort would interleave them
// lexicographically ("Season 10" before "Season 2"). id breaks a tie, so two
// rows claiming the same number still come back deterministically.
func (s *SQL) Seasons(ctx context.Context, u auth.User, seriesID int64) ([]Item, error) {
	series, err := s.ItemFor(ctx, u, seriesID)
	if err != nil {
		return nil, err
	}
	rows, err := s.db.QueryContext(ctx, itemColumns+`
		WHERE i.parent_id = ? AND i.library_id = ? AND i.kind = 'season' AND i.missing = 0
		ORDER BY COALESCE(i.season_number, 0), i.id`, series.ID, series.LibraryID)
	if err != nil {
		return nil, fmt.Errorf("catalog: list seasons: %w", err)
	}
	defer rows.Close()
	return scanItems(rows)
}

// Episodes returns a season's episodes, ordered by episode number, for the same
// reason Seasons orders by season number.
func (s *SQL) Episodes(ctx context.Context, u auth.User, seasonID int64) ([]Item, error) {
	season, err := s.ItemFor(ctx, u, seasonID)
	if err != nil {
		return nil, err
	}
	rows, err := s.db.QueryContext(ctx, itemColumns+`
		WHERE i.parent_id = ? AND i.library_id = ? AND i.kind = 'video' AND i.missing = 0
		ORDER BY COALESCE(i.episode_number, 0), i.id`, season.ID, season.LibraryID)
	if err != nil {
		return nil, fmt.Errorf("catalog: list episodes: %w", err)
	}
	defer rows.Close()
	return scanItems(rows)
}

// NextEpisode returns the first episode of a series the caller has not finished,
// in season-then-episode order, so a finished season 1 rolls into season 2 rather
// than reporting the show as caught up. A half-watched episode is the next one:
// it is what the caller is already in the middle of.
//
// It returns ErrNotFound when every episode is finished, and — like every other
// read here — when the series is missing or the caller may not read it.
func (s *SQL) NextEpisode(ctx context.Context, u auth.User, seriesID int64) (Item, error) {
	series, err := s.ItemFor(ctx, u, seriesID)
	if err != nil {
		return Item{}, err
	}
	// The join reaches one level down (series -> season -> episode) because the
	// parent of an episode is a season, and a series holds no episodes of its
	// own. episode_number IS NOT NULL is what makes the order total: a video row
	// with no number cannot be placed in a series at all.
	it, err := scanItem(s.db.QueryRowContext(ctx, itemColumns+`
		WHERE i.library_id = ? AND i.kind = 'video' AND i.missing = 0
		  AND i.episode_number IS NOT NULL
		  AND i.parent_id IN (SELECT c.id FROM media_items c
		                       WHERE c.parent_id = ? AND c.kind = 'season')
		  AND NOT EXISTS (SELECT 1 FROM progress p
		                  WHERE p.user_id = ? AND p.item_id = i.id AND p.finished = 1)
		ORDER BY COALESCE((SELECT c.season_number FROM media_items c WHERE c.id = i.parent_id), 0),
		         i.episode_number, i.id
		LIMIT 1`, series.LibraryID, series.ID, u.ID))
	switch {
	case errors.Is(err, ErrNotFound):
		return Item{}, ErrNotFound
	case err != nil:
		return Item{}, fmt.Errorf("catalog: next episode: %w", err)
	}
	return it, nil
}

// scanItems drains a query of itemColumns.
func scanItems(rows *sql.Rows) ([]Item, error) {
	out := []Item{}
	for rows.Next() {
		it, err := scanItem(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, it)
	}
	return out, rows.Err()
}

// The functions below are the original package API, kept so that existing
// callers compile and behave exactly as before. Each one is a one-line
// delegation to the SQL implementation above.
//
// NEW CODE SHOULD TAKE A Store, NOT A *sql.DB. A caller that wants to be
// swappable off SQLite takes the Store interface and receives New(db) at the
// wiring point; reaching for these functions bakes the engine back in.

func LoadUnchecked(ctx context.Context, db *sql.DB, id int64) (Item, error) {
	return New(db).Item(ctx, id)
}

func LoadFor(ctx context.Context, db *sql.DB, u auth.User, id int64) (Item, error) {
	return New(db).ItemFor(ctx, u, id)
}

func Files(ctx context.Context, db *sql.DB, itemID int64) ([]File, error) {
	return New(db).Files(ctx, itemID)
}

func FilesFor(ctx context.Context, db *sql.DB, u auth.User, itemID int64) ([]File, error) {
	return New(db).FilesFor(ctx, u, itemID)
}

func FileByID(ctx context.Context, db *sql.DB, itemID, fileID int64) (File, error) {
	return New(db).FileByID(ctx, itemID, fileID)
}

func FirstFile(ctx context.Context, db *sql.DB, itemID int64) (File, error) {
	return New(db).FirstFile(ctx, itemID)
}

func FileCount(ctx context.Context, db *sql.DB, itemID int64) (int, error) {
	return New(db).FileCount(ctx, itemID)
}

// parseUnixNano parses a base-10 nanosecond timestamp, returning 0 on anything
// that is not all digits.
func parseUnixNano(s string) int64 {
	if s == "" {
		return 0
	}
	var n int64
	for i := 0; i < len(s); i++ {
		if s[i] < '0' || s[i] > '9' {
			return 0
		}
		n = n*10 + int64(s[i]-'0')
	}
	return n
}