-- 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);