feat: SQLite schema migrations, repository interfaces, and concrete SQLite implementations with :memory: table-driven tests (task l9)

18 files changed, 2716 insertions, 1 deletions

diff --git a/go.mod b/go.mod
index 931c9a4..93d2a61 100644
--- a/go.mod
+++ b/go.mod
@@ -1,3 +1,17 @@
 module github.com/paul/kiss-media-player
 
-go 1.24
+go 1.25.0
+
+require modernc.org/sqlite v1.50.0
+
+require (
+	github.com/dustin/go-humanize v1.0.1 // indirect
+	github.com/google/uuid v1.6.0 // indirect
+	github.com/mattn/go-isatty v0.0.20 // indirect
+	github.com/ncruces/go-strftime v1.0.0 // indirect
+	github.com/remyoudompheng/bigfft v0.0.0-20230129092748-24d4a6f8daec // indirect
+	golang.org/x/sys v0.42.0 // indirect
+	modernc.org/libc v1.72.0 // indirect
+	modernc.org/mathutil v1.7.1 // indirect
+	modernc.org/memory v1.11.0 // indirect
+)
diff --git a/go.sum b/go.sum
new file mode 100644
index 0000000..2dc5885
--- /dev/null
+++ b/go.sum
@@ -0,0 +1,51 @@
+github.com/dustin/go-humanize v1.0.1 h1:GzkhY7T5VNhEkwH0PVJgjz+fX1rhBrR7pRT3mDkpeCY=
+github.com/dustin/go-humanize v1.0.1/go.mod h1:Mu1zIs6XwVuF/gI1OepvI0qD18qycQx+mFykh5fBlto=
+github.com/google/pprof v0.0.0-20250317173921-a4b03ec1a45e h1:ijClszYn+mADRFY17kjQEVQ1XRhq2/JR1M3sGqeJoxs=
+github.com/google/pprof v0.0.0-20250317173921-a4b03ec1a45e/go.mod h1:boTsfXsheKC2y+lKOCMpSfarhxDeIzfZG1jqGcPl3cA=
+github.com/google/uuid v1.6.0 h1:NIvaJDMOsjHA8n1jAhLSgzrAzy1Hgr+hNrb57e+94F0=
+github.com/google/uuid v1.6.0/go.mod h1:TIyPZe4MgqvfeYDBFedMoGGpEw/LqOeaOT+nhxU+yHo=
+github.com/hashicorp/golang-lru/v2 v2.0.7 h1:a+bsQ5rvGLjzHuww6tVxozPZFVghXaHOwFs4luLUK2k=
+github.com/hashicorp/golang-lru/v2 v2.0.7/go.mod h1:QeFd9opnmA6QUJc5vARoKUSoFhyfM2/ZepoAG6RGpeM=
+github.com/mattn/go-isatty v0.0.20 h1:xfD0iDuEKnDkl03q4limB+vH+GxLEtL/jb4xVJSWWEY=
+github.com/mattn/go-isatty v0.0.20/go.mod h1:W+V8PltTTMOvKvAeJH7IuucS94S2C6jfK/D7dTCTo3Y=
+github.com/ncruces/go-strftime v1.0.0 h1:HMFp8mLCTPp341M/ZnA4qaf7ZlsbTc+miZjCLOFAw7w=
+github.com/ncruces/go-strftime v1.0.0/go.mod h1:Fwc5htZGVVkseilnfgOVb9mKy6w1naJmn9CehxcKcls=
+github.com/remyoudompheng/bigfft v0.0.0-20230129092748-24d4a6f8daec h1:W09IVJc94icq4NjY3clb7Lk8O1qJ8BdBEF8z0ibU0rE=
+github.com/remyoudompheng/bigfft v0.0.0-20230129092748-24d4a6f8daec/go.mod h1:qqbHyh8v60DhA7CoWK5oRCqLrMHRGoxYCSS9EjAz6Eo=
+golang.org/x/mod v0.33.0 h1:tHFzIWbBifEmbwtGz65eaWyGiGZatSrT9prnU8DbVL8=
+golang.org/x/mod v0.33.0/go.mod h1:swjeQEj+6r7fODbD2cqrnje9PnziFuw4bmLbBZFrQ5w=
+golang.org/x/sync v0.20.0 h1:e0PTpb7pjO8GAtTs2dQ6jYa5BWYlMuX047Dco/pItO4=
+golang.org/x/sync v0.20.0/go.mod h1:9xrNwdLfx4jkKbNva9FpL6vEN7evnE43NNNJQ2LF3+0=
+golang.org/x/sys v0.6.0/go.mod h1:oPkhp1MJrh7nUepCBck5+mAzfO9JrbApNNgaTdGDITg=
+golang.org/x/sys v0.42.0 h1:omrd2nAlyT5ESRdCLYdm3+fMfNFE/+Rf4bDIQImRJeo=
+golang.org/x/sys v0.42.0/go.mod h1:4GL1E5IUh+htKOUEOaiffhrAeqysfVGipDYzABqnCmw=
+golang.org/x/tools v0.42.0 h1:uNgphsn75Tdz5Ji2q36v/nsFSfR/9BRFvqhGBaJGd5k=
+golang.org/x/tools v0.42.0/go.mod h1:Ma6lCIwGZvHK6XtgbswSoWroEkhugApmsXyrUmBhfr0=
+modernc.org/cc/v4 v4.27.3 h1:uNCgn37E5U09mTv1XgskEVUJ8ADKpmFMPxzGJ0TSo+U=
+modernc.org/cc/v4 v4.27.3/go.mod h1:3YjcbCqhoTTHPycJDRl2WZKKFj0nwcOIPBfEZK0Hdk8=
+modernc.org/ccgo/v4 v4.32.4 h1:L5OB8rpEX4ZsXEQwGozRfJyJSFHbbNVOoQ59DU9/KuU=
+modernc.org/ccgo/v4 v4.32.4/go.mod h1:lY7f+fiTDHfcv6YlRgSkxYfhs+UvOEEzj49jAn2TOx0=
+modernc.org/fileutil v1.4.0 h1:j6ZzNTftVS054gi281TyLjHPp6CPHr2KCxEXjEbD6SM=
+modernc.org/fileutil v1.4.0/go.mod h1:EqdKFDxiByqxLk8ozOxObDSfcVOv/54xDs/DUHdvCUU=
+modernc.org/gc/v2 v2.6.5 h1:nyqdV8q46KvTpZlsw66kWqwXRHdjIlJOhG6kxiV/9xI=
+modernc.org/gc/v2 v2.6.5/go.mod h1:YgIahr1ypgfe7chRuJi2gD7DBQiKSLMPgBQe9oIiito=
+modernc.org/gc/v3 v3.1.2 h1:ZtDCnhonXSZexk/AYsegNRV1lJGgaNZJuKjJSWKyEqo=
+modernc.org/gc/v3 v3.1.2/go.mod h1:HFK/6AGESC7Ex+EZJhJ2Gni6cTaYpSMmU/cT9RmlfYY=
+modernc.org/goabi0 v0.2.0 h1:HvEowk7LxcPd0eq6mVOAEMai46V+i7Jrj13t4AzuNks=
+modernc.org/goabi0 v0.2.0/go.mod h1:CEFRnnJhKvWT1c1JTI3Avm+tgOWbkOu5oPA8eH8LnMI=
+modernc.org/libc v1.72.0 h1:IEu559v9a0XWjw0DPoVKtXpO2qt5NVLAnFaBbjq+n8c=
+modernc.org/libc v1.72.0/go.mod h1:tTU8DL8A+XLVkEY3x5E/tO7s2Q/q42EtnNWda/L5QhQ=
+modernc.org/mathutil v1.7.1 h1:GCZVGXdaN8gTqB1Mf/usp1Y/hSqgI2vAGGP4jZMCxOU=
+modernc.org/mathutil v1.7.1/go.mod h1:4p5IwJITfppl0G4sUEDtCr4DthTaT47/N3aT6MhfgJg=
+modernc.org/memory v1.11.0 h1:o4QC8aMQzmcwCK3t3Ux/ZHmwFPzE6hf2Y5LbkRs+hbI=
+modernc.org/memory v1.11.0/go.mod h1:/JP4VbVC+K5sU2wZi9bHoq2MAkCnrt2r98UGeSK7Mjw=
+modernc.org/opt v0.1.4 h1:2kNGMRiUjrp4LcaPuLY2PzUfqM/w9N23quVwhKt5Qm8=
+modernc.org/opt v0.1.4/go.mod h1:03fq9lsNfvkYSfxrfUhZCWPk1lm4cq4N+Bh//bEtgns=
+modernc.org/sortutil v1.2.1 h1:+xyoGf15mM3NMlPDnFqrteY07klSFxLElE2PVuWIJ7w=
+modernc.org/sortutil v1.2.1/go.mod h1:7ZI3a3REbai7gzCLcotuw9AC4VZVpYMjDzETGsSMqJE=
+modernc.org/sqlite v1.50.0 h1:eMowQSWLK0MeiQTdmz3lqoF5dqclujdlIKeJA11+7oM=
+modernc.org/sqlite v1.50.0/go.mod h1:m0w8xhwYUVY3H6pSDwc3gkJ/irZT/0YEXwBlhaxQEew=
+modernc.org/strutil v1.2.1 h1:UneZBkQA+DX2Rp35KcM69cSsNES9ly8mQWD71HKlOA0=
+modernc.org/strutil v1.2.1/go.mod h1:EHkiggD70koQxjVdSBM3JKM7k6L0FbGE5eymy9i3B9A=
+modernc.org/token v1.1.0 h1:Xl7Ap9dKaEs5kLoOQeQmPWevfnk/DM5qcLcYlA8ys6Y=
+modernc.org/token v1.1.0/go.mod h1:UGzOrNV1mAFSEB63lOFHIpNRUVMvYTc6yu1SMY/XTDM=
diff --git a/internal/repository/favorite.go b/internal/repository/favorite.go
new file mode 100644
index 0000000..4d3e0f5
--- /dev/null
+++ b/internal/repository/favorite.go
@@ -0,0 +1,62 @@
+package repository
+
+import (
+	"context"
+	"fmt"
+
+	"github.com/paul/kiss-media-player/internal/model"
+)
+
+// ToggleFavorite inserts or deletes a favorite row, returning whether it is now favorited.
+func (s *SQLite) ToggleFavorite(ctx context.Context, userID, mediaID int64) (bool, error) {
+	row := s.db.QueryRowContext(ctx,
+		`SELECT 1 FROM favorites WHERE user_id = ? AND media_id = ?`, userID, mediaID)
+	var dummy int
+	err := row.Scan(&dummy)
+	if err != nil {
+		// Insert new favorite
+		_, err = s.db.ExecContext(ctx,
+			`INSERT INTO favorites (user_id, media_id) VALUES (?, ?)`, userID, mediaID)
+		if err != nil {
+			return false, fmt.Errorf("insert favorite: %w", err)
+		}
+		return true, nil
+	}
+	// Delete existing favorite
+	_, err = s.db.ExecContext(ctx,
+		`DELETE FROM favorites WHERE user_id = ? AND media_id = ?`, userID, mediaID)
+	if err != nil {
+		return false, fmt.Errorf("delete favorite: %w", err)
+	}
+	return false, nil
+}
+
+// IsFavorite returns true if the user has favorited the media.
+func (s *SQLite) IsFavorite(ctx context.Context, userID, mediaID int64) (bool, error) {
+	row := s.db.QueryRowContext(ctx,
+		`SELECT 1 FROM favorites WHERE user_id = ? AND media_id = ?`, userID, mediaID)
+	var dummy int
+	if err := row.Scan(&dummy); err != nil {
+		return false, nil
+	}
+	return true, nil
+}
+
+// ListFavoritesByUser returns all favorites for a user.
+func (s *SQLite) ListFavoritesByUser(ctx context.Context, userID int64) ([]model.Favorite, error) {
+	rows, err := s.db.QueryContext(ctx,
+		`SELECT user_id, media_id, created_at FROM favorites WHERE user_id = ? ORDER BY created_at DESC`, userID)
+	if err != nil {
+		return nil, fmt.Errorf("list favorites: %w", err)
+	}
+	defer rows.Close()
+	var favs []model.Favorite
+	for rows.Next() {
+		var f model.Favorite
+		if err := rows.Scan(&f.UserID, &f.MediaID, &f.CreatedAt); err != nil {
+			return nil, err
+		}
+		favs = append(favs, f)
+	}
+	return favs, rows.Err()
+}
diff --git a/internal/repository/media.go b/internal/repository/media.go
new file mode 100644
index 0000000..1f1b485
--- /dev/null
+++ b/internal/repository/media.go
@@ -0,0 +1,244 @@
+package repository
+
+import (
+	"context"
+	"database/sql"
+	"fmt"
+	"strings"
+
+	"github.com/paul/kiss-media-player/internal/model"
+)
+
+// CreateMedia inserts a new media and returns the generated ID.
+func (s *SQLite) CreateMedia(ctx context.Context, media *model.Media) (int64, error) {
+	res, err := s.db.ExecContext(ctx,
+		`INSERT INTO media (set_id, rel_path, file_name, abs_path, type, duration, codec, resolution, bitrate, file_size_bytes, thumbnail_path, play_count, deleted_at, created_at)
+		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
+		media.SetID, media.RelPath, media.FileName, media.AbsPath, string(media.Type),
+		media.Duration, media.Codec, media.Resolution, media.Bitrate, media.FileSizeBytes,
+		sqlNullString(media.ThumbnailPath), media.PlayCount, sqlNullTime(media.DeletedAt), media.CreatedAt,
+	)
+	if err != nil {
+		return 0, fmt.Errorf("insert media: %w", err)
+	}
+	return res.LastInsertId()
+}
+
+func scanMedia(row sqlScanner) (*model.Media, error) {
+	var m model.Media
+	var deleted sql.NullTime
+	var mediaType string
+	var thumbnail sql.NullString
+	var codec sql.NullString
+	var resolution sql.NullString
+	var duration sql.NullFloat64
+	var bitrate sql.NullInt64
+	var fileSize sql.NullInt64
+	err := row.Scan(
+		&m.ID, &m.SetID, &m.RelPath, &m.FileName, &m.AbsPath, &mediaType,
+		&duration, &codec, &resolution, &bitrate, &fileSize,
+		&thumbnail, &m.PlayCount, &deleted, &m.CreatedAt,
+	)
+	if err != nil {
+		return nil, err
+	}
+	m.Type = model.MediaType(mediaType)
+	if deleted.Valid {
+		m.DeletedAt = &deleted.Time
+	}
+	if thumbnail.Valid {
+		m.ThumbnailPath = thumbnail.String
+	}
+	if codec.Valid {
+		m.Codec = codec.String
+	}
+	if resolution.Valid {
+		m.Resolution = resolution.String
+	}
+	if duration.Valid {
+		m.Duration = duration.Float64
+	}
+	if bitrate.Valid {
+		m.Bitrate = int(bitrate.Int64)
+	}
+	if fileSize.Valid {
+		m.FileSizeBytes = fileSize.Int64
+	}
+	return &m, nil
+}
+
+// GetMediaByID retrieves a media by ID.
+func (s *SQLite) GetMediaByID(ctx context.Context, id int64) (*model.Media, error) {
+	row := s.db.QueryRowContext(ctx,
+		`SELECT id, set_id, rel_path, file_name, abs_path, type, duration, codec, resolution, bitrate, file_size_bytes, thumbnail_path, play_count, deleted_at, created_at FROM media WHERE id = ?`, id)
+	return scanMedia(row)
+}
+
+// UpdateMedia updates all mutable fields of a media record.
+func (s *SQLite) UpdateMedia(ctx context.Context, media *model.Media) error {
+	_, err := s.db.ExecContext(ctx,
+		`UPDATE media SET set_id = ?, rel_path = ?, file_name = ?, abs_path = ?, type = ?, duration = ?, codec = ?, resolution = ?, bitrate = ?, file_size_bytes = ?, thumbnail_path = ?, play_count = ?, deleted_at = ? WHERE id = ?`,
+		media.SetID, media.RelPath, media.FileName, media.AbsPath, string(media.Type), media.Duration,
+		media.Codec, media.Resolution, media.Bitrate, media.FileSizeBytes, sqlNullString(media.ThumbnailPath),
+		media.PlayCount, sqlNullTime(media.DeletedAt), media.ID,
+	)
+	if err != nil {
+		return fmt.Errorf("update media: %w", err)
+	}
+	return nil
+}
+
+// SoftDeleteMedia sets deleted_at to NOW().
+func (s *SQLite) SoftDeleteMedia(ctx context.Context, id int64) error {
+	_, err := s.db.ExecContext(ctx, `UPDATE media SET deleted_at = CURRENT_TIMESTAMP WHERE id = ?`, id)
+	if err != nil {
+		return fmt.Errorf("soft delete media: %w", err)
+	}
+	return nil
+}
+
+// RestoreMedia clears deleted_at.
+func (s *SQLite) RestoreMedia(ctx context.Context, id int64) error {
+	_, err := s.db.ExecContext(ctx, `UPDATE media SET deleted_at = NULL WHERE id = ?`, id)
+	if err != nil {
+		return fmt.Errorf("restore media: %w", err)
+	}
+	return nil
+}
+
+// HardDeleteMedia permanently deletes a media record.
+func (s *SQLite) HardDeleteMedia(ctx context.Context, id int64) error {
+	_, err := s.db.ExecContext(ctx, `DELETE FROM media WHERE id = ?`, id)
+	if err != nil {
+		return fmt.Errorf("hard delete media: %w", err)
+	}
+	return nil
+}
+
+// ListMedia returns media matching the filter.
+func (s *SQLite) ListMedia(ctx context.Context, filter MediaFilter) ([]model.Media, error) {
+	var args []any
+	var conds []string
+	var joins string
+	query := `SELECT DISTINCT media.id, media.set_id, media.rel_path, media.file_name, media.abs_path, media.type, media.duration, media.codec, media.resolution, media.bitrate, media.file_size_bytes, media.thumbnail_path, media.play_count, media.deleted_at, media.created_at FROM media`
+
+	if filter.Search != "" {
+		conds = append(conds, `(media.file_name LIKE ? OR media.rel_path LIKE ?)`)
+		like := "%" + strings.ReplaceAll(filter.Search, "%", "\\%") + "%"
+		args = append(args, like, like)
+	}
+	if filter.Favorites != nil {
+		joins += ` INNER JOIN favorites f ON f.media_id = media.id AND f.user_id = ?`
+		args = append(args, *filter.Favorites)
+	}
+	if len(filter.Tags) > 0 {
+		joins += ` INNER JOIN media_tags mt ON mt.media_id = media.id INNER JOIN tags t ON t.id = mt.tag_id`
+		conds = append(conds, `t.name IN (`+placeholders(len(filter.Tags))+`)`)
+		for _, t := range filter.Tags {
+			args = append(args, t)
+		}
+		// Require all tags by grouping and checking count
+		// This is handled below via HAVING
+	}
+
+	if filter.SetID != nil {
+		conds = append(conds, `media.set_id = ?`)
+		args = append(args, *filter.SetID)
+	}
+	if filter.Type != nil {
+		conds = append(conds, `media.type = ?`)
+		args = append(args, string(*filter.Type))
+	}
+	if filter.MinDuration != nil {
+		conds = append(conds, `media.duration >= ?`)
+		args = append(args, *filter.MinDuration)
+	}
+	if filter.MaxDuration != nil {
+		conds = append(conds, `media.duration <= ?`)
+		args = append(args, *filter.MaxDuration)
+	}
+	conds = append(conds, `media.deleted_at IS NULL`)
+
+	query += joins
+	if len(conds) > 0 {
+		query += " WHERE " + strings.Join(conds, " AND ")
+	}
+	if len(filter.Tags) > 0 {
+		query += ` GROUP BY media.id HAVING COUNT(DISTINCT t.name) = ` + fmt.Sprintf("%d", len(filter.Tags))
+	}
+
+	switch filter.Sort {
+	case "duration":
+		query += " ORDER BY media.duration"
+	case "play_count":
+		query += " ORDER BY media.play_count DESC"
+	case "date":
+		query += " ORDER BY media.created_at DESC"
+	case "random":
+		query += " ORDER BY RANDOM()"
+	default:
+		query += " ORDER BY media.file_name"
+	}
+
+	if filter.Limit > 0 {
+		query += fmt.Sprintf(" LIMIT %d", filter.Limit)
+	}
+	if filter.Offset > 0 {
+		query += fmt.Sprintf(" OFFSET %d", filter.Offset)
+	}
+
+	rows, err := s.db.QueryContext(ctx, query, args...)
+	if err != nil {
+		return nil, fmt.Errorf("list media: %w", err)
+	}
+	defer rows.Close()
+
+	var media []model.Media
+	for rows.Next() {
+		m, err := scanMedia(rows)
+		if err != nil {
+			return nil, err
+		}
+		media = append(media, *m)
+	}
+	return media, rows.Err()
+}
+
+// ListDeletedMedia returns all soft-deleted media.
+func (s *SQLite) ListDeletedMedia(ctx context.Context) ([]model.Media, error) {
+	rows, err := s.db.QueryContext(ctx,
+		`SELECT id, set_id, rel_path, file_name, abs_path, type, duration, codec, resolution, bitrate, file_size_bytes, thumbnail_path, play_count, deleted_at, created_at FROM media WHERE deleted_at IS NOT NULL ORDER BY deleted_at DESC`)
+	if err != nil {
+		return nil, fmt.Errorf("list deleted media: %w", err)
+	}
+	defer rows.Close()
+	var media []model.Media
+	for rows.Next() {
+		m, err := scanMedia(rows)
+		if err != nil {
+			return nil, err
+		}
+		media = append(media, *m)
+	}
+	return media, rows.Err()
+}
+
+// IncrementPlayCount increments the play_count of a media by 1.
+func (s *SQLite) IncrementPlayCount(ctx context.Context, id int64) error {
+	_, err := s.db.ExecContext(ctx, `UPDATE media SET play_count = play_count + 1 WHERE id = ?`, id)
+	if err != nil {
+		return fmt.Errorf("increment play count: %w", err)
+	}
+	return nil
+}
+
+func placeholders(n int) string {
+	if n <= 0 {
+		return ""
+	}
+	parts := make([]string, n)
+	for i := range parts {
+		parts[i] = "?"
+	}
+	return strings.Join(parts, ",")
+}
diff --git a/internal/repository/migrate.go b/internal/repository/migrate.go
new file mode 100644
index 0000000..60cea03
--- /dev/null
+++ b/internal/repository/migrate.go
@@ -0,0 +1,134 @@
+package repository
+
+import (
+	"database/sql"
+	"fmt"
+)
+
+// Migrate creates the database schema if it does not exist.
+func Migrate(db *sql.DB) error {
+	if _, err := db.Exec(`PRAGMA foreign_keys = ON;`); err != nil {
+		return fmt.Errorf("enable foreign keys: %w", err)
+	}
+
+	schema := `
+CREATE TABLE IF NOT EXISTS users (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    username TEXT UNIQUE NOT NULL,
+    password_hash TEXT NOT NULL,
+    is_admin INTEGER NOT NULL DEFAULT 0,
+    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
+);
+
+CREATE TABLE IF NOT EXISTS sets (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    name TEXT NOT NULL,
+    root_path TEXT UNIQUE NOT NULL,
+    cover_thumbnail_path TEXT,
+    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
+);
+
+CREATE TABLE IF NOT EXISTS set_permissions (
+    set_id INTEGER NOT NULL REFERENCES sets(id) ON DELETE CASCADE,
+    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
+    role TEXT CHECK(role IN ('owner','viewer')) NOT NULL DEFAULT 'viewer',
+    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
+    PRIMARY KEY (set_id, user_id)
+);
+
+CREATE TABLE IF NOT EXISTS media (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    set_id INTEGER NOT NULL REFERENCES sets(id) ON DELETE CASCADE,
+    rel_path TEXT NOT NULL,
+    file_name TEXT NOT NULL,
+    abs_path TEXT NOT NULL,
+    type TEXT CHECK(type IN ('video','audio')) NOT NULL,
+    duration REAL,
+    codec TEXT,
+    resolution TEXT,
+    bitrate INTEGER,
+    file_size_bytes INTEGER,
+    thumbnail_path TEXT,
+    play_count INTEGER NOT NULL DEFAULT 0,
+    deleted_at DATETIME,
+    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
+    UNIQUE(set_id, rel_path)
+);
+
+CREATE TABLE IF NOT EXISTS tags (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    name TEXT UNIQUE NOT NULL
+);
+
+CREATE TABLE IF NOT EXISTS media_tags (
+    media_id INTEGER NOT NULL REFERENCES media(id) ON DELETE CASCADE,
+    tag_id INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
+    PRIMARY KEY (media_id, tag_id)
+);
+
+CREATE TABLE IF NOT EXISTS favorites (
+    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
+    media_id INTEGER NOT NULL REFERENCES media(id) ON DELETE CASCADE,
+    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
+    PRIMARY KEY (user_id, media_id)
+);
+
+CREATE TABLE IF NOT EXISTS playback_progress (
+    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
+    media_id INTEGER NOT NULL REFERENCES media(id) ON DELETE CASCADE,
+    position_seconds REAL NOT NULL,
+    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
+    PRIMARY KEY (user_id, media_id)
+);
+
+CREATE TABLE IF NOT EXISTS sessions (
+    id TEXT PRIMARY KEY,
+    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
+    expires_at DATETIME NOT NULL,
+    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
+);
+
+CREATE TABLE IF NOT EXISTS playback_accumulator (
+    session_id TEXT NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
+    media_id INTEGER NOT NULL REFERENCES media(id) ON DELETE CASCADE,
+    last_position REAL NOT NULL DEFAULT 0,
+    accumulated_seconds REAL NOT NULL DEFAULT 0,
+    counted INTEGER NOT NULL DEFAULT 0,
+    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
+    PRIMARY KEY (session_id, media_id)
+);
+
+CREATE TABLE IF NOT EXISTS shares (
+    token TEXT PRIMARY KEY,
+    media_id INTEGER NOT NULL REFERENCES media(id) ON DELETE CASCADE,
+    created_by INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
+    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
+    expires_at DATETIME NOT NULL,
+    max_uses INTEGER,
+    used_count INTEGER NOT NULL DEFAULT 0
+);
+
+CREATE TABLE IF NOT EXISTS media_notes (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    media_id INTEGER NOT NULL REFERENCES media(id) ON DELETE CASCADE,
+    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
+    content TEXT NOT NULL DEFAULT '',
+    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
+    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
+    UNIQUE(media_id, user_id)
+);
+
+CREATE INDEX IF NOT EXISTS idx_media_set_id ON media(set_id);
+CREATE INDEX IF NOT EXISTS idx_media_rel_path ON media(set_id, rel_path);
+CREATE INDEX IF NOT EXISTS idx_media_deleted_at ON media(deleted_at);
+CREATE INDEX IF NOT EXISTS idx_media_type ON media(type);
+CREATE INDEX IF NOT EXISTS idx_media_filename ON media(file_name);
+CREATE INDEX IF NOT EXISTS idx_permissions_user ON set_permissions(user_id);
+CREATE INDEX IF NOT EXISTS idx_permissions_set ON set_permissions(set_id);
+CREATE INDEX IF NOT EXISTS idx_shares_expires ON shares(expires_at);
+`
+	if _, err := db.Exec(schema); err != nil {
+		return fmt.Errorf("execute schema: %w", err)
+	}
+	return nil
+}
diff --git a/internal/repository/mock.go b/internal/repository/mock.go
new file mode 100644
index 0000000..7c54e5c
--- /dev/null
+++ b/internal/repository/mock.go
@@ -0,0 +1,635 @@
+// Package repository provides data access abstractions.
+package repository
+
+import (
+	"context"
+	"time"
+
+	"github.com/paul/kiss-media-player/internal/model"
+)
+
+// compile-time checks.
+var (
+	_ Store = (*MockStore)(nil)
+)
+
+// MockStore is a hand-written fake for all repository interfaces.
+// Each embedded struct provides default no-op / zero-value behavior;
+// callers override individual func fields to inject test behavior.
+type MockStore struct {
+	UserRepo                MockUserRepo
+	SetRepo                 MockSetRepo
+	SetPermissionRepo       MockSetPermissionRepo
+	MediaRepo               MockMediaRepo
+	TagRepo                 MockTagRepo
+	FavoriteRepo            MockFavoriteRepo
+	PlaybackProgressRepo    MockPlaybackProgressRepo
+	PlaybackAccumulatorRepo MockPlaybackAccumulatorRepo
+	SessionRepo             MockSessionRepo
+	ShareRepo               MockShareRepo
+	NoteRepo                MockNoteRepo
+}
+
+func (m *MockStore) CreateUser(ctx context.Context, user *model.User) (int64, error) {
+	return m.UserRepo.CreateUser(ctx, user)
+}
+func (m *MockStore) GetUserByID(ctx context.Context, id int64) (*model.User, error) {
+	return m.UserRepo.GetUserByID(ctx, id)
+}
+func (m *MockStore) GetUserByUsername(ctx context.Context, username string) (*model.User, error) {
+	return m.UserRepo.GetUserByUsername(ctx, username)
+}
+func (m *MockStore) ListUsers(ctx context.Context) ([]model.User, error) {
+	return m.UserRepo.ListUsers(ctx)
+}
+func (m *MockStore) DeleteUser(ctx context.Context, id int64) error {
+	return m.UserRepo.DeleteUser(ctx, id)
+}
+func (m *MockStore) CountUsers(ctx context.Context) (int, error) { return m.UserRepo.CountUsers(ctx) }
+
+func (m *MockStore) CreateSet(ctx context.Context, set *model.Set) (int64, error) {
+	return m.SetRepo.CreateSet(ctx, set)
+}
+func (m *MockStore) GetSetByID(ctx context.Context, id int64) (*model.Set, error) {
+	return m.SetRepo.GetSetByID(ctx, id)
+}
+func (m *MockStore) ListSets(ctx context.Context) ([]model.Set, error) {
+	return m.SetRepo.ListSets(ctx)
+}
+func (m *MockStore) UpdateSet(ctx context.Context, set *model.Set) error {
+	return m.SetRepo.UpdateSet(ctx, set)
+}
+func (m *MockStore) DeleteSet(ctx context.Context, id int64) error {
+	return m.SetRepo.DeleteSet(ctx, id)
+}
+
+func (m *MockStore) GrantPermission(ctx context.Context, perm *model.SetPermission) error {
+	return m.SetPermissionRepo.GrantPermission(ctx, perm)
+}
+func (m *MockStore) RevokePermission(ctx context.Context, setID, userID int64) error {
+	return m.SetPermissionRepo.RevokePermission(ctx, setID, userID)
+}
+func (m *MockStore) GetPermission(ctx context.Context, setID, userID int64) (*model.SetPermission, error) {
+	return m.SetPermissionRepo.GetPermission(ctx, setID, userID)
+}
+func (m *MockStore) ListPermissionsBySet(ctx context.Context, setID int64) ([]model.SetPermission, error) {
+	return m.SetPermissionRepo.ListPermissionsBySet(ctx, setID)
+}
+func (m *MockStore) ListPermissionsByUser(ctx context.Context, userID int64) ([]model.SetPermission, error) {
+	return m.SetPermissionRepo.ListPermissionsByUser(ctx, userID)
+}
+
+func (m *MockStore) CreateMedia(ctx context.Context, media *model.Media) (int64, error) {
+	return m.MediaRepo.CreateMedia(ctx, media)
+}
+func (m *MockStore) GetMediaByID(ctx context.Context, id int64) (*model.Media, error) {
+	return m.MediaRepo.GetMediaByID(ctx, id)
+}
+func (m *MockStore) UpdateMedia(ctx context.Context, media *model.Media) error {
+	return m.MediaRepo.UpdateMedia(ctx, media)
+}
+func (m *MockStore) SoftDeleteMedia(ctx context.Context, id int64) error {
+	return m.MediaRepo.SoftDeleteMedia(ctx, id)
+}
+func (m *MockStore) RestoreMedia(ctx context.Context, id int64) error {
+	return m.MediaRepo.RestoreMedia(ctx, id)
+}
+func (m *MockStore) HardDeleteMedia(ctx context.Context, id int64) error {
+	return m.MediaRepo.HardDeleteMedia(ctx, id)
+}
+func (m *MockStore) ListMedia(ctx context.Context, filter MediaFilter) ([]model.Media, error) {
+	return m.MediaRepo.ListMedia(ctx, filter)
+}
+func (m *MockStore) ListDeletedMedia(ctx context.Context) ([]model.Media, error) {
+	return m.MediaRepo.ListDeletedMedia(ctx)
+}
+func (m *MockStore) IncrementPlayCount(ctx context.Context, id int64) error {
+	return m.MediaRepo.IncrementPlayCount(ctx, id)
+}
+
+func (m *MockStore) CreateTag(ctx context.Context, name string) (int64, error) {
+	return m.TagRepo.CreateTag(ctx, name)
+}
+func (m *MockStore) GetTagByID(ctx context.Context, id int64) (*model.Tag, error) {
+	return m.TagRepo.GetTagByID(ctx, id)
+}
+func (m *MockStore) GetTagByName(ctx context.Context, name string) (*model.Tag, error) {
+	return m.TagRepo.GetTagByName(ctx, name)
+}
+func (m *MockStore) ListTags(ctx context.Context) ([]model.Tag, error) {
+	return m.TagRepo.ListTags(ctx)
+}
+func (m *MockStore) DeleteTag(ctx context.Context, id int64) error {
+	return m.TagRepo.DeleteTag(ctx, id)
+}
+func (m *MockStore) AssignTag(ctx context.Context, mediaID, tagID int64) error {
+	return m.TagRepo.AssignTag(ctx, mediaID, tagID)
+}
+func (m *MockStore) RemoveTag(ctx context.Context, mediaID, tagID int64) error {
+	return m.TagRepo.RemoveTag(ctx, mediaID, tagID)
+}
+func (m *MockStore) ListTagsByMedia(ctx context.Context, mediaID int64) ([]model.Tag, error) {
+	return m.TagRepo.ListTagsByMedia(ctx, mediaID)
+}
+
+func (m *MockStore) ToggleFavorite(ctx context.Context, userID, mediaID int64) (bool, error) {
+	return m.FavoriteRepo.ToggleFavorite(ctx, userID, mediaID)
+}
+func (m *MockStore) IsFavorite(ctx context.Context, userID, mediaID int64) (bool, error) {
+	return m.FavoriteRepo.IsFavorite(ctx, userID, mediaID)
+}
+func (m *MockStore) ListFavoritesByUser(ctx context.Context, userID int64) ([]model.Favorite, error) {
+	return m.FavoriteRepo.ListFavoritesByUser(ctx, userID)
+}
+
+func (m *MockStore) UpsertProgress(ctx context.Context, progress *model.PlaybackProgress) error {
+	return m.PlaybackProgressRepo.UpsertProgress(ctx, progress)
+}
+func (m *MockStore) GetProgress(ctx context.Context, userID, mediaID int64) (*model.PlaybackProgress, error) {
+	return m.PlaybackProgressRepo.GetProgress(ctx, userID, mediaID)
+}
+func (m *MockStore) ListProgressByUser(ctx context.Context, userID int64) ([]model.PlaybackProgress, error) {
+	return m.PlaybackProgressRepo.ListProgressByUser(ctx, userID)
+}
+
+func (m *MockStore) UpsertAccumulator(ctx context.Context, acc *model.PlaybackAccumulator) error {
+	return m.PlaybackAccumulatorRepo.UpsertAccumulator(ctx, acc)
+}
+func (m *MockStore) GetAccumulator(ctx context.Context, sessionID string, mediaID int64) (*model.PlaybackAccumulator, error) {
+	return m.PlaybackAccumulatorRepo.GetAccumulator(ctx, sessionID, mediaID)
+}
+
+func (m *MockStore) CreateSession(ctx context.Context, session *model.Session) error {
+	return m.SessionRepo.CreateSession(ctx, session)
+}
+func (m *MockStore) GetSessionByID(ctx context.Context, id string) (*model.Session, error) {
+	return m.SessionRepo.GetSessionByID(ctx, id)
+}
+func (m *MockStore) DeleteSession(ctx context.Context, id string) error {
+	return m.SessionRepo.DeleteSession(ctx, id)
+}
+func (m *MockStore) DeleteExpiredSessions(ctx context.Context, now time.Time) error {
+	return m.SessionRepo.DeleteExpiredSessions(ctx, now)
+}
+
+func (m *MockStore) CreateShare(ctx context.Context, share *model.Share) error {
+	return m.ShareRepo.CreateShare(ctx, share)
+}
+func (m *MockStore) GetShareByToken(ctx context.Context, token string) (*model.Share, error) {
+	return m.ShareRepo.GetShareByToken(ctx, token)
+}
+func (m *MockStore) ListSharesByMedia(ctx context.Context, mediaID int64) ([]model.Share, error) {
+	return m.ShareRepo.ListSharesByMedia(ctx, mediaID)
+}
+func (m *MockStore) UseShare(ctx context.Context, token string) error {
+	return m.ShareRepo.UseShare(ctx, token)
+}
+func (m *MockStore) DeleteShare(ctx context.Context, token string) error {
+	return m.ShareRepo.DeleteShare(ctx, token)
+}
+func (m *MockStore) DeleteExpiredShares(ctx context.Context, now time.Time) error {
+	return m.ShareRepo.DeleteExpiredShares(ctx, now)
+}
+
+func (m *MockStore) UpsertNote(ctx context.Context, note *model.Note) error {
+	return m.NoteRepo.UpsertNote(ctx, note)
+}
+func (m *MockStore) GetNote(ctx context.Context, mediaID, userID int64) (*model.Note, error) {
+	return m.NoteRepo.GetNote(ctx, mediaID, userID)
+}
+func (m *MockStore) DeleteNote(ctx context.Context, mediaID, userID int64) error {
+	return m.NoteRepo.DeleteNote(ctx, mediaID, userID)
+}
+
+// MockUserRepo is a fake UserRepo.
+type MockUserRepo struct {
+	CreateUserFunc        func(ctx context.Context, user *model.User) (int64, error)
+	GetUserByIDFunc       func(ctx context.Context, id int64) (*model.User, error)
+	GetUserByUsernameFunc func(ctx context.Context, username string) (*model.User, error)
+	ListUsersFunc         func(ctx context.Context) ([]model.User, error)
+	DeleteUserFunc        func(ctx context.Context, id int64) error
+	CountUsersFunc        func(ctx context.Context) (int, error)
+}
+
+func (m *MockUserRepo) CreateUser(ctx context.Context, user *model.User) (int64, error) {
+	if m.CreateUserFunc != nil {
+		return m.CreateUserFunc(ctx, user)
+	}
+	return 1, nil
+}
+func (m *MockUserRepo) GetUserByID(ctx context.Context, id int64) (*model.User, error) {
+	if m.GetUserByIDFunc != nil {
+		return m.GetUserByIDFunc(ctx, id)
+	}
+	return nil, nil
+}
+func (m *MockUserRepo) GetUserByUsername(ctx context.Context, username string) (*model.User, error) {
+	if m.GetUserByUsernameFunc != nil {
+		return m.GetUserByUsernameFunc(ctx, username)
+	}
+	return nil, nil
+}
+func (m *MockUserRepo) ListUsers(ctx context.Context) ([]model.User, error) {
+	if m.ListUsersFunc != nil {
+		return m.ListUsersFunc(ctx)
+	}
+	return nil, nil
+}
+func (m *MockUserRepo) DeleteUser(ctx context.Context, id int64) error {
+	if m.DeleteUserFunc != nil {
+		return m.DeleteUserFunc(ctx, id)
+	}
+	return nil
+}
+func (m *MockUserRepo) CountUsers(ctx context.Context) (int, error) {
+	if m.CountUsersFunc != nil {
+		return m.CountUsersFunc(ctx)
+	}
+	return 0, nil
+}
+
+// MockSetRepo is a fake SetRepo.
+type MockSetRepo struct {
+	CreateSetFunc  func(ctx context.Context, set *model.Set) (int64, error)
+	GetSetByIDFunc func(ctx context.Context, id int64) (*model.Set, error)
+	ListSetsFunc   func(ctx context.Context) ([]model.Set, error)
+	UpdateSetFunc  func(ctx context.Context, set *model.Set) error
+	DeleteSetFunc  func(ctx context.Context, id int64) error
+}
+
+func (m *MockSetRepo) CreateSet(ctx context.Context, set *model.Set) (int64, error) {
+	if m.CreateSetFunc != nil {
+		return m.CreateSetFunc(ctx, set)
+	}
+	return 1, nil
+}
+func (m *MockSetRepo) GetSetByID(ctx context.Context, id int64) (*model.Set, error) {
+	if m.GetSetByIDFunc != nil {
+		return m.GetSetByIDFunc(ctx, id)
+	}
+	return nil, nil
+}
+func (m *MockSetRepo) ListSets(ctx context.Context) ([]model.Set, error) {
+	if m.ListSetsFunc != nil {
+		return m.ListSetsFunc(ctx)
+	}
+	return nil, nil
+}
+func (m *MockSetRepo) UpdateSet(ctx context.Context, set *model.Set) error {
+	if m.UpdateSetFunc != nil {
+		return m.UpdateSetFunc(ctx, set)
+	}
+	return nil
+}
+func (m *MockSetRepo) DeleteSet(ctx context.Context, id int64) error {
+	if m.DeleteSetFunc != nil {
+		return m.DeleteSetFunc(ctx, id)
+	}
+	return nil
+}
+
+// MockSetPermissionRepo is a fake SetPermissionRepo.
+type MockSetPermissionRepo struct {
+	GrantPermissionFunc       func(ctx context.Context, perm *model.SetPermission) error
+	RevokePermissionFunc      func(ctx context.Context, setID, userID int64) error
+	GetPermissionFunc         func(ctx context.Context, setID, userID int64) (*model.SetPermission, error)
+	ListPermissionsBySetFunc  func(ctx context.Context, setID int64) ([]model.SetPermission, error)
+	ListPermissionsByUserFunc func(ctx context.Context, userID int64) ([]model.SetPermission, error)
+}
+
+func (m *MockSetPermissionRepo) GrantPermission(ctx context.Context, perm *model.SetPermission) error {
+	if m.GrantPermissionFunc != nil {
+		return m.GrantPermissionFunc(ctx, perm)
+	}
+	return nil
+}
+func (m *MockSetPermissionRepo) RevokePermission(ctx context.Context, setID, userID int64) error {
+	if m.RevokePermissionFunc != nil {
+		return m.RevokePermissionFunc(ctx, setID, userID)
+	}
+	return nil
+}
+func (m *MockSetPermissionRepo) GetPermission(ctx context.Context, setID, userID int64) (*model.SetPermission, error) {
+	if m.GetPermissionFunc != nil {
+		return m.GetPermissionFunc(ctx, setID, userID)
+	}
+	return nil, nil
+}
+func (m *MockSetPermissionRepo) ListPermissionsBySet(ctx context.Context, setID int64) ([]model.SetPermission, error) {
+	if m.ListPermissionsBySetFunc != nil {