
import Database from 'better-sqlite3';
import path from 'path';

const dbPath = path.join(process.cwd(), 'media.db');
const db = new Database(dbPath);

db.exec(`
  CREATE TABLE IF NOT EXISTS libraries (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    path TEXT NOT NULL UNIQUE
  );
`);

db.exec(`
  CREATE TABLE IF NOT EXISTS media (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    library_id INTEGER,
    path TEXT NOT NULL UNIQUE,
    type TEXT NOT NULL,
    title TEXT,
    size INTEGER,
    thumbnail TEXT,
    bookmark_count INTEGER DEFAULT 0,
    star_count INTEGER DEFAULT 0,
    avg_rating REAL DEFAULT 0.0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (library_id) REFERENCES libraries (id)
  );
`);

db.exec(`
  CREATE TABLE IF NOT EXISTS bookmarks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    media_id INTEGER NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (media_id) REFERENCES media(id) ON DELETE CASCADE
  );
`);

db.exec(`
  CREATE TABLE IF NOT EXISTS stars (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    media_id INTEGER NOT NULL,
    rating INTEGER NOT NULL CHECK (rating >= 1 AND rating <= 5),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (media_id) REFERENCES media(id) ON DELETE CASCADE
  );
`);

// Create indexes for performance
db.exec(`CREATE INDEX IF NOT EXISTS idx_bookmarks_media_id ON bookmarks(media_id);`);
db.exec(`CREATE INDEX IF NOT EXISTS idx_stars_media_id ON stars(media_id);`);
db.exec(`CREATE INDEX IF NOT EXISTS idx_media_bookmark_count ON media(bookmark_count);`);
db.exec(`CREATE INDEX IF NOT EXISTS idx_media_star_count ON media(star_count);`);

// Pagination and filtering indexes
db.exec(`CREATE INDEX IF NOT EXISTS idx_media_type_created_at ON media(type, created_at);`);
db.exec(`CREATE INDEX IF NOT EXISTS idx_media_path ON media(path);`);
db.exec(`CREATE INDEX IF NOT EXISTS idx_media_library_id ON media(library_id);`);

// Full-text search indexes
db.exec(`CREATE INDEX IF NOT EXISTS idx_media_title ON media(title);`);
db.exec(`CREATE INDEX IF NOT EXISTS idx_media_type_path ON media(type, path);`);

export default db;
