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

1 files changed, 134 insertions, 0 deletions

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
+}