kanpeki/db/migrations/20250316175245_create_initial_tables.sql

-- +goose Up
CREATE TABLE libraries (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT UNIQUE NOT NULL,
    path TEXT NOT NULL,
    type TEXT NOT NULL, -- ('tv' or 'movies')
    num_files INTEGER,
    size INTEGER
);

CREATE TABLE tv_shows (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    library_id INTEGER NOT NULL,
    name TEXT NOT NULL,
    num_files INTEGER,
    size INTEGER,
    FOREIGN KEY (library_id) REFERENCES libraries(id)
);

CREATE TABLE seasons (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    tv_show_id INTEGER NOT NULL,
    season_number INTEGER NOT NULL,
    num_files INTEGER,
    size INTEGER,
    FOREIGN KEY (tv_show_id) REFERENCES tv_shows(id)
);

CREATE TABLE movies (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    library_id INTEGER NOT NULL,
    title TEXT NOT NULL,
    last_scanned INTEGER,
    FOREIGN KEY (library_id) REFERENCES libraries(id)
);

CREATE TABLE files (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    library_id INTEGER NOT NULL,
    tv_show_id INTEGER,  -- Nullable for movies
    season_id INTEGER,   -- Nullable for movies
    movie_id INTEGER,    -- Nullable for TV episodes
    filename TEXT NOT NULL,
    last_scanned INTEGER,
    FOREIGN KEY (library_id) REFERENCES libraries(id),
    FOREIGN KEY (tv_show_id) REFERENCES tv_shows(id),
    FOREIGN KEY (season_id) REFERENCES seasons(id),
    FOREIGN KEY (movie_id) REFERENCES movies(id)
);

CREATE TABLE ffprobe_metadata (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    file_id INTEGER NOT NULL,
    data JSON NOT NULL,
    FOREIGN KEY (file_id) REFERENCES files(id)
);

-- +goose Down
DROP TABLE ffprobe_metadata;
DROP TABLE files;
DROP TABLE libraries;
DROP TABLE tv_shows;
DROP TABLE movies;
DROP TABLE seasons;