import Database from 'better-sqlite3';
import path from 'path';
import fs from 'fs';
import bcrypt from 'bcryptjs';

const dbPath = process.env.DATABASE_PATH || path.join(__dirname, '../data/gallery.db');

const dbDir = path.dirname(dbPath);
if (!fs.existsSync(dbDir)) {
  fs.mkdirSync(dbDir, { recursive: true });
}

export const db = new Database(dbPath);
db.pragma('journal_mode = WAL');

export function initDatabase() {
  db.exec(`
    CREATE TABLE IF NOT EXISTS categories (
      cid INTEGER PRIMARY KEY AUTOINCREMENT,
      owner_id INTEGER DEFAULT 0,
      name TEXT NOT NULL,
      description TEXT DEFAULT '',
      pos INTEGER DEFAULT 0,
      parent INTEGER DEFAULT 0,
      thumb INTEGER DEFAULT 0
    );

    CREATE TABLE IF NOT EXISTS albums (
      aid INTEGER PRIMARY KEY AUTOINCREMENT,
      title TEXT NOT NULL,
      description TEXT DEFAULT '',
      visibility INTEGER DEFAULT 0,
      uploads TEXT DEFAULT 'NO',
      comments TEXT DEFAULT 'YES',
      votes TEXT DEFAULT 'YES',
      pos INTEGER DEFAULT 0,
      category INTEGER DEFAULT 0,
      owner INTEGER DEFAULT 1,
      thumb INTEGER DEFAULT 0,
      keyword TEXT DEFAULT '',
      alb_hits INTEGER DEFAULT 0
    );

    CREATE TABLE IF NOT EXISTS pictures (
      pid INTEGER PRIMARY KEY AUTOINCREMENT,
      aid INTEGER NOT NULL,
      filepath TEXT NOT NULL,
      filename TEXT NOT NULL,
      filesize INTEGER DEFAULT 0,
      pwidth INTEGER DEFAULT 0,
      pheight INTEGER DEFAULT 0,
      hits INTEGER DEFAULT 0,
      mtime DATETIME DEFAULT CURRENT_TIMESTAMP,
      ctime INTEGER DEFAULT 0,
      owner_id INTEGER DEFAULT 1,
      pic_rating INTEGER DEFAULT 0,
      votes INTEGER DEFAULT 0,
      title TEXT DEFAULT '',
      caption TEXT DEFAULT '',
      keywords TEXT DEFAULT '',
      approved TEXT DEFAULT 'YES',
      position INTEGER DEFAULT 0
    );

    CREATE TABLE IF NOT EXISTS comments (
      msg_id INTEGER PRIMARY KEY AUTOINCREMENT,
      pid INTEGER NOT NULL,
      msg_author TEXT NOT NULL,
      msg_body TEXT NOT NULL,
      msg_date DATETIME DEFAULT CURRENT_TIMESTAMP,
      author_id INTEGER DEFAULT 0
    );

    CREATE TABLE IF NOT EXISTS usergroups (
      group_id INTEGER PRIMARY KEY AUTOINCREMENT,
      group_name TEXT NOT NULL,
      has_admin_access INTEGER DEFAULT 0,
      can_rate_pictures INTEGER DEFAULT 1,
      can_send_ecards INTEGER DEFAULT 1,
      can_post_comments INTEGER DEFAULT 1,
      can_upload_pictures INTEGER DEFAULT 1,
      can_create_albums INTEGER DEFAULT 1
    );

    CREATE TABLE IF NOT EXISTS users (
      user_id INTEGER PRIMARY KEY AUTOINCREMENT,
      user_group INTEGER DEFAULT 2,
      user_active TEXT DEFAULT 'YES',
      user_name TEXT NOT NULL UNIQUE,
      user_password TEXT NOT NULL,
      user_email TEXT NOT NULL,
      user_regdate DATETIME DEFAULT CURRENT_TIMESTAMP
    );

    CREATE TABLE IF NOT EXISTS config (
      name TEXT PRIMARY KEY,
      value TEXT NOT NULL
    );
  `);

  const groupCount = (db.prepare('SELECT COUNT(*) as count FROM usergroups').get() as any).count;
  if (groupCount === 0) {
    db.prepare('INSERT INTO usergroups (group_id, group_name, has_admin_access) VALUES (?, ?, ?)').run(1, 'Administrators', 1);
    db.prepare('INSERT INTO usergroups (group_id, group_name, has_admin_access) VALUES (?, ?, ?)').run(2, 'Registered', 0);
    db.prepare('INSERT INTO usergroups (group_id, group_name, has_admin_access) VALUES (?, ?, ?)').run(3, 'Guests', 0);
  }

  const userCount = (db.prepare('SELECT COUNT(*) as count FROM users').get() as any).count;
  if (userCount === 0) {
    const adminPasswordHash = bcrypt.hashSync('admin', 10);
    db.prepare('INSERT INTO users (user_id, user_group, user_name, user_password, user_email) VALUES (?, ?, ?, ?, ?)').run(
      1,
      1,
      'admin',
      adminPasswordHash,
      'admin@example.com'
    );
  }

  const configCount = (db.prepare('SELECT COUNT(*) as count FROM config').get() as any).count;
  if (configCount === 0) {
    const defaultConfig = [
      ['gallery_name', 'Coppermine Photo Gallery'],
      ['gallery_description', 'Your online photo album'],
      ['theme', 'classic'],
      ['thumb_pfx', 'thumb_'],
      ['normal_pfx', 'normal_'],
      ['max_upl_width', '2048'],
      ['max_upl_height', '2048'],
      ['max_upl_size', '10485760'],
      ['thumb_width', '200'],
      ['albums_per_page', '12'],
      ['pics_per_page', '12']
    ];
    const stmt = db.prepare('INSERT INTO config (name, value) VALUES (?, ?)');
    for (const [name, val] of defaultConfig) {
      stmt.run(name, val);
    }
  }

  const catCount = (db.prepare('SELECT COUNT(*) as count FROM categories').get() as any).count;
  if (catCount === 0) {
    db.prepare('INSERT INTO categories (cid, name, description) VALUES (?, ?, ?)').run(1, 'General Photography', 'Default category for general photos');
  }

  const albCount = (db.prepare('SELECT COUNT(*) as count FROM albums').get() as any).count;
  if (albCount === 0) {
    db.prepare('INSERT INTO albums (aid, title, description, category, uploads) VALUES (?, ?, ?, ?, ?)').run(1, 'Sample Album', 'Welcome to your photo gallery!', 1, 'YES');
  }
}

export function getConfig(): Record<string, string> {
  const rows = db.prepare('SELECT name, value FROM config').all() as { name: string; value: string }[];
  const config: Record<string, string> = {};
  for (const row of rows) {
    config[row.name] = row.value;
  }
  return config;
}