-- +goose Up
CREATE TABLE IF NOT EXISTS playlists (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS playlist_tracks (
    id SERIAL PRIMARY KEY,
    playlist_id INTEGER NOT NULL REFERENCES playlists(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    filename TEXT NOT NULL UNIQUE,
    position INTEGER NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

ALTER TABLE scenarios ADD COLUMN IF NOT EXISTS playlist_id INTEGER REFERENCES playlists(id) ON DELETE RESTRICT;
ALTER TABLE games ADD COLUMN IF NOT EXISTS playlist_id INTEGER REFERENCES playlists(id) ON DELETE RESTRICT;

CREATE TABLE IF NOT EXISTS game_music_states (
    game_id INTEGER PRIMARY KEY REFERENCES games(id) ON DELETE CASCADE,
    playlist_id INTEGER REFERENCES playlists(id) ON DELETE RESTRICT,
    track_index INTEGER NOT NULL DEFAULT 0,
    position_ms BIGINT NOT NULL DEFAULT 0,
    volume INTEGER NOT NULL DEFAULT 100,
    loop BOOLEAN NOT NULL DEFAULT FALSE,
    playback INTEGER NOT NULL DEFAULT 1,
    state_version BIGINT NOT NULL DEFAULT 1,
    started_at TIMESTAMPTZ,
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- +goose Down
DROP TABLE IF EXISTS game_music_states;
ALTER TABLE games DROP COLUMN IF EXISTS playlist_id;
ALTER TABLE scenarios DROP COLUMN IF EXISTS playlist_id;
DROP TABLE IF EXISTS playlist_tracks;
DROP TABLE IF EXISTS playlists;