navidrome/db/migrations/20251027000000_create_schema.sql

-- +goose Up
-- PostgreSQL Schema for Navidrome
-- Converted from SQLite schema

-- Enable extensions
CREATE EXTENSION IF NOT EXISTS citext;

-- Property table
CREATE TABLE property (
                          id VARCHAR NOT NULL PRIMARY KEY,
                          value VARCHAR DEFAULT '' NOT NULL
);

-- Transcoding table
CREATE TABLE transcoding (
                             id VARCHAR NOT NULL PRIMARY KEY,
                             name VARCHAR NOT NULL UNIQUE,
                             target_format VARCHAR NOT NULL UNIQUE,
                             command VARCHAR DEFAULT '' NOT NULL,
                             default_bit_rate SMALLINT DEFAULT 192
);

-- User table
CREATE TABLE "user" (
                        id VARCHAR NOT NULL PRIMARY KEY,
                        user_name CITEXT NOT NULL UNIQUE,  -- CITEXT for case-insensitive username
                        name VARCHAR DEFAULT '' NOT NULL,
                        email VARCHAR DEFAULT '' NOT NULL,
                        password VARCHAR DEFAULT '' NOT NULL,
                        is_admin BOOLEAN DEFAULT FALSE NOT NULL,
                        last_login_at TIMESTAMP,
                        last_access_at TIMESTAMP,
                        created_at TIMESTAMP NOT NULL,
                        updated_at TIMESTAMP NOT NULL
);

CREATE INDEX user_username_password ON "user"(user_name, password);

-- Library table
CREATE TABLE library (
                         id SERIAL PRIMARY KEY,
                         name VARCHAR NOT NULL UNIQUE,
                         path VARCHAR NOT NULL UNIQUE,
                         remote_path VARCHAR DEFAULT '',
                         last_scan_at TIMESTAMP NOT NULL DEFAULT '1970-01-01 00:00:00',
                         last_scan_started_at TIMESTAMP DEFAULT '1970-01-01 00:00:00' NOT NULL,
                         full_scan_in_progress BOOLEAN DEFAULT FALSE NOT NULL,
                         total_songs BIGINT DEFAULT 0 NOT NULL,
                         total_albums BIGINT DEFAULT 0 NOT NULL,
                         total_artists BIGINT DEFAULT 0 NOT NULL,
                         total_folders BIGINT DEFAULT 0 NOT NULL,
                         total_files BIGINT DEFAULT 0 NOT NULL,
                         total_missing_files BIGINT DEFAULT 0 NOT NULL,
                         total_size BIGINT DEFAULT 0 NOT NULL,
                         total_duration REAL DEFAULT 0,
                         default_new_users BOOLEAN DEFAULT FALSE,
                         updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                         created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Artist table
CREATE TABLE artist (
                        id VARCHAR NOT NULL PRIMARY KEY,
                        name VARCHAR DEFAULT '' NOT NULL,
                        full_text VARCHAR DEFAULT '',
                        biography VARCHAR DEFAULT '' NOT NULL,  -- VARCHAR: biographies can be long
                        small_image_url VARCHAR DEFAULT '' NOT NULL,
                        medium_image_url VARCHAR DEFAULT '' NOT NULL,
                        large_image_url VARCHAR DEFAULT '' NOT NULL,
                        external_url VARCHAR DEFAULT '' NOT NULL,
                        external_info_updated_at TIMESTAMP,
                        order_artist_name VARCHAR DEFAULT '' NOT NULL,
                        sort_artist_name VARCHAR DEFAULT '' NOT NULL,
                        mbz_artist_id VARCHAR DEFAULT '' NOT NULL,
                        missing BOOLEAN DEFAULT FALSE NOT NULL,
                        similar_artists JSONB DEFAULT '[]' NOT NULL,
                        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE INDEX artist_name ON artist(name);
CREATE INDEX artist_order_artist_name ON artist(order_artist_name);
CREATE INDEX artist_sort_name ON artist(COALESCE(NULLIF(sort_artist_name, ''), order_artist_name));
CREATE INDEX artist_updated_at ON artist(updated_at);
CREATE INDEX artist_mbz_artist_id ON artist(mbz_artist_id);

-- Library-Artist association table
CREATE TABLE library_artist (
                                library_id INT NOT NULL DEFAULT 1 REFERENCES library(id) ON DELETE CASCADE,
                                artist_id VARCHAR NOT NULL REFERENCES artist(id) ON DELETE CASCADE,
                                stats VARCHAR DEFAULT '{}',
                                CONSTRAINT library_artist_ux UNIQUE (library_id, artist_id)
);

-- Album table
CREATE TABLE album (
                       id VARCHAR NOT NULL PRIMARY KEY,
                       name VARCHAR DEFAULT '' NOT NULL,
                       embed_art_path VARCHAR DEFAULT '' NOT NULL,
                       album_artist VARCHAR DEFAULT '' NOT NULL,
                       min_year SMALLINT DEFAULT 0 NOT NULL,
                       max_year SMALLINT DEFAULT 0 NOT NULL,
                       compilation BOOLEAN DEFAULT FALSE NOT NULL,
                       song_count INT DEFAULT 0 NOT NULL,
                       duration REAL DEFAULT 0 NOT NULL,
                       genre VARCHAR DEFAULT '' NOT NULL,
                       created_at TIMESTAMP,
                       updated_at TIMESTAMP,
                       full_text VARCHAR DEFAULT '',
                       album_artist_id VARCHAR DEFAULT '',
                       size BIGINT DEFAULT 0 NOT NULL,
                       description VARCHAR DEFAULT '' NOT NULL,
                       small_image_url VARCHAR DEFAULT '' NOT NULL,
                       medium_image_url VARCHAR DEFAULT '' NOT NULL,
                       large_image_url VARCHAR DEFAULT '' NOT NULL,
                       external_url VARCHAR DEFAULT '' NOT NULL,
                       external_info_updated_at TIMESTAMP,
                       date VARCHAR DEFAULT '' NOT NULL,
                       min_original_year SMALLINT DEFAULT 0 NOT NULL,
                       max_original_year SMALLINT DEFAULT 0 NOT NULL,
                       original_date VARCHAR DEFAULT '' NOT NULL,
                       release_date VARCHAR DEFAULT '' NOT NULL,
                       order_album_name VARCHAR DEFAULT '' NOT NULL,
                       order_album_artist_name VARCHAR DEFAULT '' NOT NULL,
                       sort_album_name VARCHAR DEFAULT '' NOT NULL,
                       sort_album_artist_name VARCHAR DEFAULT '' NOT NULL,
                       catalog_num VARCHAR DEFAULT '' NOT NULL,
                       comment VARCHAR DEFAULT '' NOT NULL,
                       mbz_album_id VARCHAR DEFAULT '' NOT NULL,
                       mbz_album_artist_id VARCHAR DEFAULT '' NOT NULL,
                       mbz_album_type VARCHAR DEFAULT '' NOT NULL,
                       mbz_album_comment VARCHAR DEFAULT '' NOT NULL,
                       discs JSONB DEFAULT '{}' NOT NULL,
                       library_id INT DEFAULT 1 NOT NULL REFERENCES library ON DELETE CASCADE,
                       imported_at TIMESTAMP DEFAULT '1970-01-01 00:00:00' NOT NULL,
                       missing BOOLEAN DEFAULT FALSE NOT NULL,
                       mbz_release_group_id VARCHAR DEFAULT '' NOT NULL,
                       tags JSONB DEFAULT '{}' NOT NULL,
                       participants JSONB DEFAULT '{}' NOT NULL,
                       folder_ids JSONB DEFAULT '[]' NOT NULL,
                       explicit_status VARCHAR DEFAULT '' NOT NULL
);

CREATE INDEX album_alphabetical_by_artist ON album(compilation, order_album_artist_name, order_album_name);
CREATE INDEX album_artist_album_id ON album(album_artist_id);
CREATE INDEX album_created_at ON album(created_at);
CREATE INDEX album_genre ON album(genre);
CREATE INDEX album_max_year ON album(max_year);
CREATE INDEX album_mbz_album_type ON album(mbz_album_type);
CREATE INDEX album_min_year ON album(min_year);
CREATE INDEX album_name ON album(name);
CREATE INDEX album_order_album_artist_name ON album(order_album_artist_name);
CREATE INDEX album_order_album_name ON album(order_album_name);
CREATE INDEX album_size ON album(size);
CREATE INDEX album_sort_name ON album(COALESCE(NULLIF(sort_album_name, ''), order_album_name));
CREATE INDEX album_sort_album_artist_name ON album(COALESCE(NULLIF(sort_album_artist_name, ''), order_album_artist_name));
CREATE INDEX album_updated_at ON album(updated_at);
CREATE INDEX album_imported_at ON album(imported_at);
CREATE INDEX album_mbz_album_id ON album(mbz_album_id);
CREATE INDEX album_mbz_release_group_id ON album(mbz_release_group_id);

-- Folder table
CREATE TABLE folder (
                        id VARCHAR NOT NULL PRIMARY KEY,
                        library_id INT NOT NULL REFERENCES library(id) ON DELETE CASCADE,
                        path VARCHAR DEFAULT '' NOT NULL,
                        name VARCHAR DEFAULT '' NOT NULL,
                        missing BOOLEAN DEFAULT FALSE NOT NULL,
                        parent_id VARCHAR DEFAULT '' NOT NULL,
                        num_audio_files INT DEFAULT 0 NOT NULL,
                        num_playlists INT DEFAULT 0 NOT NULL,
                        image_files JSONB DEFAULT '[]' NOT NULL,
                        images_updated_at TIMESTAMP DEFAULT '1970-01-01 00:00:00' NOT NULL,
                        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
                        hash VARCHAR DEFAULT '' NOT NULL
);

CREATE INDEX folder_parent_id ON folder(parent_id);

-- Media File table
CREATE TABLE media_file (
                            id VARCHAR NOT NULL PRIMARY KEY,
                            path VARCHAR DEFAULT '' NOT NULL,
                            title VARCHAR DEFAULT '' NOT NULL,
                            album VARCHAR DEFAULT '' NOT NULL,
                            artist VARCHAR DEFAULT '' NOT NULL,
                            artist_id VARCHAR DEFAULT '' NOT NULL,
                            album_artist VARCHAR DEFAULT '' NOT NULL,
                            album_id VARCHAR DEFAULT '' NOT NULL,
                            has_cover_art BOOLEAN DEFAULT FALSE NOT NULL,
                            track_number SMALLINT DEFAULT 0 NOT NULL,
                            disc_number SMALLINT DEFAULT 0 NOT NULL,
                            year SMALLINT DEFAULT 0 NOT NULL,
                            size BIGINT DEFAULT 0 NOT NULL,
                            suffix VARCHAR DEFAULT '' NOT NULL,
                            duration REAL DEFAULT 0 NOT NULL,
                            bit_rate SMALLINT DEFAULT 0 NOT NULL,
                            genre VARCHAR DEFAULT '' NOT NULL,
                            compilation BOOLEAN DEFAULT FALSE NOT NULL,
                            created_at TIMESTAMP,
                            updated_at TIMESTAMP,
                            full_text VARCHAR DEFAULT '',
                            album_artist_id VARCHAR DEFAULT '',
                            date VARCHAR DEFAULT '' NOT NULL,
                            original_year SMALLINT DEFAULT 0 NOT NULL,
                            original_date VARCHAR DEFAULT '' NOT NULL,
                            release_year SMALLINT DEFAULT 0 NOT NULL,
                            release_date VARCHAR DEFAULT '' NOT NULL,
                            order_album_name VARCHAR DEFAULT '' NOT NULL,
                            order_album_artist_name VARCHAR DEFAULT '' NOT NULL,
                            order_artist_name VARCHAR DEFAULT '' NOT NULL,
                            sort_album_name VARCHAR DEFAULT '' NOT NULL,
                            sort_artist_name VARCHAR DEFAULT '' NOT NULL,
                            sort_album_artist_name VARCHAR DEFAULT '' NOT NULL,
                            sort_title VARCHAR DEFAULT '' NOT NULL,
                            disc_subtitle VARCHAR DEFAULT '' NOT NULL,
                            catalog_num VARCHAR DEFAULT '' NOT NULL,
                            comment VARCHAR DEFAULT '' NOT NULL,
                            order_title VARCHAR DEFAULT '' NOT NULL,
                            mbz_recording_id VARCHAR DEFAULT '' NOT NULL,
                            mbz_album_id VARCHAR DEFAULT '' NOT NULL,
                            mbz_artist_id VARCHAR DEFAULT '' NOT NULL,
                            mbz_album_artist_id VARCHAR DEFAULT '' NOT NULL,
                            mbz_album_type VARCHAR DEFAULT '' NOT NULL,
                            mbz_album_comment VARCHAR DEFAULT '' NOT NULL,
                            mbz_release_track_id VARCHAR DEFAULT '' NOT NULL,
                            bpm SMALLINT DEFAULT 0 NOT NULL,
                            channels SMALLINT DEFAULT 0 NOT NULL,
                            lyrics JSONB DEFAULT '[]' NOT NULL,
                            sample_rate INT DEFAULT 0 NOT NULL,
                            library_id INT DEFAULT 1 NOT NULL REFERENCES library ON DELETE CASCADE,
                            folder_id VARCHAR DEFAULT '' NOT NULL,
                            pid VARCHAR DEFAULT '' NOT NULL,
                            missing BOOLEAN DEFAULT FALSE NOT NULL,
                            mbz_release_group_id VARCHAR DEFAULT '' NOT NULL,
                            tags JSONB DEFAULT '{}' NOT NULL,
                            participants JSONB DEFAULT '{}' NOT NULL,
                            bit_depth SMALLINT DEFAULT 0 NOT NULL,
                            explicit_status VARCHAR DEFAULT '' NOT NULL,
                            birth_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
                            rg_album_gain REAL,
                            rg_album_peak REAL,
                            rg_track_gain REAL,
                            rg_track_peak REAL
);

CREATE INDEX media_file_album_artist ON media_file(album_artist);
CREATE INDEX media_file_album_id ON media_file(album_id);
CREATE INDEX media_file_artist ON media_file(artist);
CREATE INDEX media_file_artist_album_id ON media_file(album_artist_id);
CREATE INDEX media_file_artist_id ON media_file(artist_id);
CREATE INDEX media_file_bpm ON media_file(bpm);
CREATE INDEX media_file_channels ON media_file(channels);
CREATE INDEX media_file_created_at ON media_file(created_at);
CREATE INDEX media_file_duration ON media_file(duration);
CREATE INDEX media_file_genre ON media_file(genre);
CREATE INDEX media_file_mbz_track_id ON media_file(mbz_recording_id);
CREATE INDEX media_file_order_album_name ON media_file(order_album_name);
CREATE INDEX media_file_order_artist_name ON media_file(order_artist_name);
CREATE INDEX media_file_order_title ON media_file(order_title);
CREATE INDEX media_file_path ON media_file(path);
CREATE INDEX media_file_sample_rate ON media_file(sample_rate);
CREATE INDEX media_file_sort_title ON media_file(COALESCE(NULLIF(sort_title, ''), order_title));
CREATE INDEX media_file_sort_artist_name ON media_file(COALESCE(NULLIF(sort_artist_name, ''), order_artist_name));
CREATE INDEX media_file_sort_album_name ON media_file(COALESCE(NULLIF(sort_album_name, ''), order_album_name));
CREATE INDEX media_file_title ON media_file(title);
CREATE INDEX media_file_track_number ON media_file(disc_number, track_number);
CREATE INDEX media_file_updated_at ON media_file(updated_at);
CREATE INDEX media_file_year ON media_file(year);
CREATE INDEX media_file_birth_time ON media_file(birth_time);
CREATE INDEX media_file_folder_id ON media_file(folder_id);
CREATE INDEX media_file_pid ON media_file(pid);
CREATE INDEX media_file_missing ON media_file(missing);
CREATE INDEX media_file_mbz_release_track_id ON media_file(mbz_release_track_id);

-- Media File Artists (many-to-many with roles)
CREATE TABLE media_file_artists (
                                    media_file_id VARCHAR NOT NULL REFERENCES media_file(id) ON DELETE CASCADE,
                                    artist_id VARCHAR NOT NULL REFERENCES artist(id) ON DELETE CASCADE,
                                    role VARCHAR DEFAULT '' NOT NULL,
                                    sub_role VARCHAR DEFAULT '' NOT NULL,
                                    CONSTRAINT artist_tracks UNIQUE (artist_id, media_file_id, role, sub_role)
);

CREATE INDEX media_file_artists_media_file_id ON media_file_artists(media_file_id);
CREATE INDEX media_file_artists_role ON media_file_artists(role);

-- Album Artists (many-to-many with roles)
CREATE TABLE album_artists (
                               album_id VARCHAR NOT NULL REFERENCES album(id) ON DELETE CASCADE,
                               artist_id VARCHAR NOT NULL REFERENCES artist(id) ON DELETE CASCADE,
                               role VARCHAR DEFAULT '' NOT NULL,
                               sub_role VARCHAR DEFAULT '' NOT NULL,
                               CONSTRAINT album_artists_ux UNIQUE (album_id, artist_id, role, sub_role)
);

CREATE INDEX album_artists_album_id ON album_artists(album_id);
CREATE INDEX album_artists_role ON album_artists(role);

-- Playlist table
CREATE TABLE playlist (
                          id VARCHAR NOT NULL PRIMARY KEY,
                          name VARCHAR DEFAULT '' NOT NULL,
                          comment VARCHAR DEFAULT '' NOT NULL,
                          duration REAL DEFAULT 0 NOT NULL,
                          song_count INT DEFAULT 0 NOT NULL,
                          public BOOLEAN DEFAULT FALSE NOT NULL,
                          created_at TIMESTAMP,
                          updated_at TIMESTAMP,
                          path VARCHAR DEFAULT '' NOT NULL,
                          sync BOOLEAN DEFAULT FALSE NOT NULL,
                          size BIGINT DEFAULT 0 NOT NULL,
                          rules VARCHAR,
                          evaluated_at TIMESTAMP,
                          owner_id VARCHAR NOT NULL REFERENCES "user"(id) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE INDEX playlist_created_at ON playlist(created_at);
CREATE INDEX playlist_evaluated_at ON playlist(evaluated_at);
CREATE INDEX playlist_name ON playlist(name);
CREATE INDEX playlist_size ON playlist(size);
CREATE INDEX playlist_updated_at ON playlist(updated_at);

-- Playlist Tracks table
CREATE TABLE playlist_tracks (
                                 id INT DEFAULT 0 NOT NULL,
                                 playlist_id VARCHAR NOT NULL REFERENCES playlist(id) ON UPDATE CASCADE ON DELETE CASCADE,
                                 media_file_id VARCHAR NOT NULL
);

CREATE UNIQUE INDEX playlist_tracks_pos ON playlist_tracks(playlist_id, id);

-- Playlist Fields table (for smart playlists)
CREATE TABLE playlist_fields (
                                 field VARCHAR NOT NULL,
                                 playlist_id VARCHAR NOT NULL REFERENCES playlist(id) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE UNIQUE INDEX playlist_fields_idx ON playlist_fields(field, playlist_id);

-- Radio table
CREATE TABLE radio (
                       id VARCHAR NOT NULL PRIMARY KEY,
                       name VARCHAR NOT NULL UNIQUE,
                       stream_url VARCHAR NOT NULL,
                       home_page_url VARCHAR DEFAULT '' NOT NULL,
                       created_at TIMESTAMP,
                       updated_at TIMESTAMP
);

CREATE INDEX radio_name ON radio(name);

-- Player table
CREATE TABLE player (
                        id VARCHAR NOT NULL PRIMARY KEY,
                        name VARCHAR NOT NULL,
                        user_agent VARCHAR,
                        user_id VARCHAR NOT NULL REFERENCES "user"(id) ON UPDATE CASCADE ON DELETE CASCADE,
                        client VARCHAR NOT NULL,
                        ip VARCHAR,
                        last_seen TIMESTAMP,
                        max_bit_rate SMALLINT DEFAULT 0,
                        transcoding_id VARCHAR,
                        report_real_path BOOLEAN DEFAULT FALSE NOT NULL,
                        scrobble_enabled BOOLEAN DEFAULT TRUE
);

CREATE INDEX player_match ON player(client, user_agent, user_id);
CREATE INDEX player_name ON player(name);

-- Annotation table (play counts, ratings, stars)
CREATE TABLE annotation (
                            user_id VARCHAR NOT NULL REFERENCES "user"(id) ON DELETE CASCADE ON UPDATE CASCADE,
                            item_id VARCHAR DEFAULT '' NOT NULL,
                            item_type VARCHAR DEFAULT '' NOT NULL,
                            play_count BIGINT DEFAULT 0,
                            play_date TIMESTAMP,
                            rating SMALLINT DEFAULT 0,
                            starred BOOLEAN DEFAULT FALSE NOT NULL,
                            starred_at TIMESTAMP,
                            UNIQUE (user_id, item_id, item_type)
);

CREATE INDEX annotation_play_count ON annotation(play_count);
CREATE INDEX annotation_play_date ON annotation(play_date);
CREATE INDEX annotation_rating ON annotation(rating);
CREATE INDEX annotation_starred ON annotation(starred);
CREATE INDEX annotation_starred_at ON annotation(starred_at);

-- Bookmark table
CREATE TABLE bookmark (
                          user_id VARCHAR NOT NULL REFERENCES "user"(id) ON UPDATE CASCADE ON DELETE CASCADE,
                          item_id VARCHAR NOT NULL,
                          item_type VARCHAR NOT NULL,
                          comment VARCHAR,
                          position BIGINT,
                          changed_by VARCHAR,
                          created_at TIMESTAMP,
                          updated_at TIMESTAMP,
                          CONSTRAINT bookmark_pk UNIQUE (user_id, item_id, item_type)
);

-- User Properties table
CREATE TABLE user_props (
                            user_id VARCHAR NOT NULL REFERENCES "user"(id) ON UPDATE CASCADE ON DELETE CASCADE,
                            key VARCHAR NOT NULL,
                            value VARCHAR,
                            CONSTRAINT user_props_pk PRIMARY KEY (user_id, key)
);

-- Scrobble Buffer table
CREATE TABLE scrobble_buffer (
                                 id VARCHAR NOT NULL DEFAULT '' PRIMARY KEY,
                                 user_id VARCHAR NOT NULL REFERENCES "user"(id) ON UPDATE CASCADE ON DELETE CASCADE,
                                 service VARCHAR NOT NULL,
                                 media_file_id VARCHAR NOT NULL REFERENCES media_file(id) ON UPDATE CASCADE ON DELETE CASCADE,
                                 play_time TIMESTAMP NOT NULL,
                                 enqueue_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE UNIQUE INDEX scrobble_buffer_unique ON scrobble_buffer(user_id, service, media_file_id, play_time);

-- Share table
CREATE TABLE share (
                       id VARCHAR NOT NULL PRIMARY KEY,
                       expires_at TIMESTAMP,
                       last_visited_at TIMESTAMP,
                       resource_ids VARCHAR NOT NULL,
                       created_at TIMESTAMP,
                       updated_at TIMESTAMP,
                       user_id VARCHAR NOT NULL REFERENCES "user"(id) ON UPDATE CASCADE ON DELETE CASCADE,
                       downloadable BOOLEAN NOT NULL DEFAULT FALSE,
                       description VARCHAR NOT NULL DEFAULT '',
                       resource_type VARCHAR NOT NULL DEFAULT '',
                       contents VARCHAR NOT NULL DEFAULT '',
                       format VARCHAR NOT NULL DEFAULT '',
                       max_bit_rate SMALLINT NOT NULL DEFAULT 0,
                       visit_count BIGINT NOT NULL DEFAULT 0
);

-- Playqueue table
CREATE TABLE playqueue (
                           id VARCHAR NOT NULL PRIMARY KEY,
                           user_id VARCHAR NOT NULL REFERENCES "user"(id) ON UPDATE CASCADE ON DELETE CASCADE,
                           current BIGINT NOT NULL DEFAULT 0,
                           position REAL,
                           changed_by VARCHAR,
                           items VARCHAR,
                           created_at TIMESTAMP,
                           updated_at TIMESTAMP
);

-- Tag table
CREATE TABLE tag (
                     id VARCHAR NOT NULL PRIMARY KEY,
                     tag_name VARCHAR DEFAULT '' NOT NULL,
                     tag_value VARCHAR DEFAULT '' NOT NULL,
                     CONSTRAINT tags_name_value UNIQUE (tag_name, tag_value)
);

-- Library Tag table (tag statistics per library)
CREATE TABLE library_tag (
                             tag_id VARCHAR NOT NULL REFERENCES tag(id) ON DELETE CASCADE,
                             library_id INT NOT NULL REFERENCES library(id) ON DELETE CASCADE,
                             album_count INT DEFAULT 0 NOT NULL,
                             media_file_count BIGINT DEFAULT 0 NOT NULL,
                             PRIMARY KEY (tag_id, library_id)
);

CREATE INDEX idx_library_tag_tag_id ON library_tag(tag_id);
CREATE INDEX idx_library_tag_library_id ON library_tag(library_id);

-- User Library association table (many-to-many)
CREATE TABLE user_library (
                              user_id VARCHAR NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
                              library_id INT NOT NULL REFERENCES library(id) ON DELETE CASCADE,
                              PRIMARY KEY (user_id, library_id)
);

CREATE INDEX idx_user_library_user_id ON user_library(user_id);
CREATE INDEX idx_user_library_library_id ON user_library(library_id);

-- Insert default library
INSERT INTO library (id, name, path, default_new_users)
VALUES (1, 'Music Library', './music', true);

-- +goose Down
SELECT 1;