lockstep-jukebox/schema/playlist_cache.sql

-- Schema derived from docs/playlists.md only.
-- Stores playlists, track list membership, and tracks (artist display name on track row).
-- Does not persist album objects or album.artists.

PRAGMA foreign_keys = ON;

-- SimplifiedPlaylistObject / PlaylistObject
CREATE TABLE playlists (
  id TEXT PRIMARY KEY,
  description TEXT,
  name TEXT NOT NULL,
  primary_color TEXT,
  snapshot_id TEXT NOT NULL,
  items_href TEXT,
  items_total INTEGER
);

CREATE TABLE playlist_images (
  playlist_id TEXT NOT NULL REFERENCES playlists (id) ON DELETE CASCADE,
  image_index INTEGER NOT NULL,
  url TEXT NOT NULL,
  height INTEGER,
  width INTEGER,
  PRIMARY KEY (playlist_id, image_index)
);

-- TrackObject + display of track.artists[].name (single column; if the API lists
-- multiple artists, join their names e.g. "A, B" in documented order).
CREATE TABLE tracks (
  id TEXT PRIMARY KEY,
  track_name TEXT NOT NULL,
  artist_name TEXT NOT NULL,
  duration_ms INTEGER NOT NULL
);

-- Order of tracks in a playlist (matches playlist.items.items[] order).
-- track_id NULL when the API returns a removed entry (wrapper with item: null).
CREATE TABLE playlist_tracks (
  playlist_id TEXT NOT NULL REFERENCES playlists (id) ON DELETE CASCADE,
  position INTEGER NOT NULL,
  track_id TEXT REFERENCES tracks (id) ON DELETE SET NULL,
  PRIMARY KEY (playlist_id, position)
);

CREATE INDEX idx_playlist_tracks_track ON playlist_tracks (track_id);