NotAShelf
56d44e120a
Signed-off-by: NotAShelf <raf@notashelf.dev> Change-Id: I8567eec6980b2b5453687bcbd07a61206a6a6964
131 lines
4.2 KiB
SQL
131 lines
4.2 KiB
SQL
-- Ratings
|
|
CREATE TABLE IF NOT EXISTS ratings (
|
|
id UUID PRIMARY KEY,
|
|
user_id UUID NOT NULL,
|
|
media_id UUID NOT NULL REFERENCES media_items(id) ON DELETE CASCADE,
|
|
stars INTEGER NOT NULL CHECK (stars >= 1 AND stars <= 5),
|
|
review_text TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
UNIQUE(user_id, media_id)
|
|
);
|
|
|
|
-- Comments
|
|
CREATE TABLE IF NOT EXISTS comments (
|
|
id UUID PRIMARY KEY,
|
|
user_id UUID NOT NULL,
|
|
media_id UUID NOT NULL REFERENCES media_items(id) ON DELETE CASCADE,
|
|
parent_comment_id UUID REFERENCES comments(id) ON DELETE CASCADE,
|
|
text TEXT NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- Favorites
|
|
CREATE TABLE IF NOT EXISTS favorites (
|
|
user_id UUID NOT NULL,
|
|
media_id UUID NOT NULL REFERENCES media_items(id) ON DELETE CASCADE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
PRIMARY KEY (user_id, media_id)
|
|
);
|
|
|
|
-- Share links
|
|
CREATE TABLE IF NOT EXISTS share_links (
|
|
id UUID PRIMARY KEY,
|
|
media_id UUID NOT NULL REFERENCES media_items(id) ON DELETE CASCADE,
|
|
created_by UUID NOT NULL,
|
|
token TEXT NOT NULL UNIQUE,
|
|
password_hash TEXT,
|
|
expires_at TIMESTAMPTZ,
|
|
view_count INTEGER NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- Playlists
|
|
CREATE TABLE IF NOT EXISTS playlists (
|
|
id UUID PRIMARY KEY,
|
|
owner_id UUID NOT NULL,
|
|
name TEXT NOT NULL,
|
|
description TEXT,
|
|
is_public BOOLEAN NOT NULL DEFAULT FALSE,
|
|
is_smart BOOLEAN NOT NULL DEFAULT FALSE,
|
|
filter_query TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- Playlist items
|
|
CREATE TABLE IF NOT EXISTS playlist_items (
|
|
playlist_id UUID NOT NULL REFERENCES playlists(id) ON DELETE CASCADE,
|
|
media_id UUID NOT NULL REFERENCES media_items(id) ON DELETE CASCADE,
|
|
position INTEGER NOT NULL DEFAULT 0,
|
|
added_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
PRIMARY KEY (playlist_id, media_id)
|
|
);
|
|
|
|
-- Usage events
|
|
CREATE TABLE IF NOT EXISTS usage_events (
|
|
id UUID PRIMARY KEY,
|
|
media_id UUID REFERENCES media_items(id) ON DELETE SET NULL,
|
|
user_id UUID,
|
|
event_type TEXT NOT NULL,
|
|
timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
duration_secs DOUBLE PRECISION,
|
|
context_json JSONB
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_usage_events_media ON usage_events(media_id);
|
|
CREATE INDEX IF NOT EXISTS idx_usage_events_user ON usage_events(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_usage_events_timestamp ON usage_events(timestamp);
|
|
|
|
-- Watch history / progress
|
|
CREATE TABLE IF NOT EXISTS watch_history (
|
|
id UUID PRIMARY KEY,
|
|
user_id UUID NOT NULL,
|
|
media_id UUID NOT NULL REFERENCES media_items(id) ON DELETE CASCADE,
|
|
progress_secs DOUBLE PRECISION NOT NULL DEFAULT 0,
|
|
last_watched TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
UNIQUE(user_id, media_id)
|
|
);
|
|
|
|
-- Subtitles
|
|
CREATE TABLE IF NOT EXISTS subtitles (
|
|
id UUID PRIMARY KEY,
|
|
media_id UUID NOT NULL REFERENCES media_items(id) ON DELETE CASCADE,
|
|
language TEXT,
|
|
format TEXT NOT NULL,
|
|
file_path TEXT,
|
|
is_embedded BOOLEAN NOT NULL DEFAULT FALSE,
|
|
track_index INTEGER,
|
|
offset_ms INTEGER NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_subtitles_media ON subtitles(media_id);
|
|
|
|
-- External metadata (enrichment)
|
|
CREATE TABLE IF NOT EXISTS external_metadata (
|
|
id UUID PRIMARY KEY,
|
|
media_id UUID NOT NULL REFERENCES media_items(id) ON DELETE CASCADE,
|
|
source TEXT NOT NULL,
|
|
external_id TEXT,
|
|
metadata_json JSONB NOT NULL DEFAULT '{}',
|
|
confidence DOUBLE PRECISION NOT NULL DEFAULT 0.0,
|
|
last_updated TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_external_metadata_media ON external_metadata(media_id);
|
|
|
|
-- Transcode sessions
|
|
CREATE TABLE IF NOT EXISTS transcode_sessions (
|
|
id UUID PRIMARY KEY,
|
|
media_id UUID NOT NULL REFERENCES media_items(id) ON DELETE CASCADE,
|
|
user_id UUID,
|
|
profile TEXT NOT NULL,
|
|
cache_path TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'pending',
|
|
progress DOUBLE PRECISION NOT NULL DEFAULT 0.0,
|
|
error_message TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
expires_at TIMESTAMPTZ
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_transcode_sessions_media ON transcode_sessions(media_id);
|