miniaudiolib

package musicdb

import (
	"database/sql"
	"fmt"
	"os"
	"path/filepath"

	_ "modernc.org/sqlite"
)

func Open(path string) (*sql.DB, error) {
	if err := os.MkdirAll(filepath.Dir(path), 0o755); err != nil {
		return nil, err
	}
	db, err := sql.Open("sqlite", path)
	if err != nil {
		return nil, err
	}
	db.SetMaxOpenConns(1)
	if _, err = db.Exec(`PRAGMA journal_mode=WAL; PRAGMA foreign_keys=ON; PRAGMA busy_timeout=5000;`); err != nil {
		db.Close()
		return nil, err
	}
	if _, err = db.Exec(schema); err != nil {
		db.Close()
		return nil, fmt.Errorf("migrate music database: %w", err)
	}
	return db, nil
}

const schema = `
CREATE TABLE IF NOT EXISTS tracks (
 id INTEGER PRIMARY KEY, path TEXT NOT NULL UNIQUE, filename TEXT NOT NULL,
 title TEXT NOT NULL DEFAULT '', artist TEXT NOT NULL DEFAULT '', album_artist TEXT NOT NULL DEFAULT '',
 album TEXT NOT NULL DEFAULT '', genre TEXT NOT NULL DEFAULT '', year INTEGER NOT NULL DEFAULT 0,
 track_number INTEGER NOT NULL DEFAULT 0, disc_number INTEGER NOT NULL DEFAULT 0,
 duration REAL NOT NULL DEFAULT 0, codec TEXT NOT NULL DEFAULT '', bitrate INTEGER NOT NULL DEFAULT 0,
 sample_rate INTEGER NOT NULL DEFAULT 0, channels INTEGER NOT NULL DEFAULT 0,
 container TEXT NOT NULL DEFAULT '', size INTEGER NOT NULL, hash TEXT NOT NULL DEFAULT '',
 modified_at INTEGER NOT NULL, added_at INTEGER NOT NULL, cover TEXT NOT NULL DEFAULT '',
 favorite INTEGER NOT NULL DEFAULT 0, play_count INTEGER NOT NULL DEFAULT 0,
 last_played INTEGER, playback_position REAL NOT NULL DEFAULT 0
);
CREATE INDEX IF NOT EXISTS idx_tracks_artist ON tracks(artist COLLATE NOCASE);
CREATE INDEX IF NOT EXISTS idx_tracks_album ON tracks(album COLLATE NOCASE);
CREATE INDEX IF NOT EXISTS idx_tracks_added ON tracks(added_at DESC);
CREATE TABLE IF NOT EXISTS scan_errors(path TEXT PRIMARY KEY, error TEXT NOT NULL, failed_at INTEGER NOT NULL);
CREATE TABLE IF NOT EXISTS audiobook_chapters (
 id INTEGER PRIMARY KEY, path TEXT NOT NULL UNIQUE, filename TEXT NOT NULL,
 book_path TEXT NOT NULL, book_title TEXT NOT NULL, title TEXT NOT NULL,
 duration REAL NOT NULL DEFAULT 0, container TEXT NOT NULL DEFAULT '', size INTEGER NOT NULL,
 modified_at INTEGER NOT NULL, cover TEXT NOT NULL DEFAULT '', playback_position REAL NOT NULL DEFAULT 0,
 completed INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX IF NOT EXISTS idx_audiobook_book ON audiobook_chapters(book_path);
CREATE TABLE IF NOT EXISTS music_metadata_cache (
 cache_key TEXT PRIMARY KEY, payload TEXT NOT NULL, fetched_at INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS playlists (
 id INTEGER PRIMARY KEY, name TEXT NOT NULL, created_at INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS playlist_tracks (
 playlist_id INTEGER NOT NULL REFERENCES playlists(id) ON DELETE CASCADE,
 track_id INTEGER NOT NULL REFERENCES tracks(id) ON DELETE CASCADE,
 position INTEGER NOT NULL,
 PRIMARY KEY(playlist_id,track_id)
);
CREATE INDEX IF NOT EXISTS idx_playlist_tracks_order ON playlist_tracks(playlist_id,position);
`