-- M7 #352 slice 1: playlists CRUD foundation.
-- See docs/superpowers/specs/2026-05-03-m7-playlists-crud-design.md.

CREATE TABLE playlists (
    id           uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id      uuid        NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name         text        NOT NULL,
    description  text        NOT NULL DEFAULT '',
    is_public    boolean     NOT NULL DEFAULT false,
    cover_path   text,
    track_count  integer     NOT NULL DEFAULT 0,
    duration_sec integer     NOT NULL DEFAULT 0,
    created_at   timestamptz NOT NULL DEFAULT now(),
    updated_at   timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX playlists_user_idx   ON playlists (user_id, updated_at DESC);
CREATE INDEX playlists_public_idx ON playlists (is_public, updated_at DESC) WHERE is_public;

-- track_id is nullable + ON DELETE SET NULL so deleting a track from
-- the library doesn't silently drop entries from operators' playlists.
-- Denormalized title/artist/album/duration carry a snapshot so the row
-- is still legible after the upstream track row is gone.
CREATE TABLE playlist_tracks (
    playlist_id  uuid        NOT NULL REFERENCES playlists(id) ON DELETE CASCADE,
    position     integer     NOT NULL,
    track_id     uuid        REFERENCES tracks(id) ON DELETE SET NULL,
    title        text        NOT NULL,
    artist_name  text        NOT NULL,
    album_title  text        NOT NULL,
    duration_sec integer     NOT NULL,
    added_at     timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (playlist_id, position)
);

-- Partial index supports the FK lookup that fires when a track is
-- deleted (ON DELETE SET NULL) — without it, deleting a track triggers
-- a full scan of playlist_tracks.
CREATE INDEX playlist_tracks_track_idx ON playlist_tracks (track_id) WHERE track_id IS NOT NULL;