const Database = require('better-sqlite3');
const path = require('path');
const fs = require('fs');

const DB_PATH = process.env.DB_PATH || path.join(__dirname, 'bills.db');
const SCHEMA_PATH = path.join(__dirname, 'schema.sql');
const DEFAULT_CATEGORIES = ['Housing', 'Utilities', 'Subscriptions', 'Insurance', 'Loans', 'Other'];

fs.mkdirSync(path.dirname(DB_PATH), { recursive: true });

let db = null;
let initializing = false;

function assertWritableDbPath() {
  const dir = path.dirname(DB_PATH);
  const probe = path.join(dir, `.write-test-${process.pid}-${Date.now()}`);

  try {
    fs.mkdirSync(dir, { recursive: true });
    fs.writeFileSync(probe, 'ok');
    fs.unlinkSync(probe);

    if (fs.existsSync(DB_PATH)) {
      fs.accessSync(DB_PATH, fs.constants.R_OK | fs.constants.W_OK);
    }
  } catch (err) {
    const message = [
      `Database path is not writable: ${DB_PATH}`,
      `Ensure the DB directory is writable by the app user. In Docker, rebuild the image and recreate the container so the entrypoint can chown /data.`,
      `Original error: ${err.message}`,
    ].join('\n');
    const wrapped = new Error(message);
    wrapped.code = err.code;
    throw wrapped;
  } finally {
    try {
      if (fs.existsSync(probe)) fs.unlinkSync(probe);
    } catch {}
  }
}

function sleep(ms) {
  Atomics.wait(new Int32Array(new SharedArrayBuffer(4)), 0, 0, ms);
}

function getDb() {
  // already ready
  if (db) return db;

  // wait if another init is happening
  while (initializing) {
    sleep(50);
  }

  // check again after wait
  if (db) return db;

  initializing = true;

  try {
    console.log('Opening DB at:', DB_PATH);
    assertWritableDbPath();

    db = new Database(DB_PATH, {
      timeout: 5000
    });

    db.pragma('busy_timeout = 5000');

    try {
      db.pragma('journal_mode = WAL');
    } catch (e) {
      console.warn('WAL failed:', e.message);
    }

    db.pragma('foreign_keys = ON');

    initSchema();
    seedDefaults();

    console.log('DB initialized successfully');

    return db;
  } catch (err) {
    console.error('DB init failed:', err);
    throw err;
  } finally {
    initializing = false;
  }
}

function initSchema() {
  const schema = fs.readFileSync(SCHEMA_PATH, 'utf8');
  db.exec(schema);
  runMigrations();
}

function runMigrations() {
  // ── users: notification columns ───────────────────────────────────────────
  const userCols = db.prepare('PRAGMA table_info(users)').all().map(c => c.name);
  const newUserCols = [
    ['notification_email',    'TEXT'],
    ['notifications_enabled', 'INTEGER NOT NULL DEFAULT 0'],
    ['notify_3d',             'INTEGER NOT NULL DEFAULT 1'],
    ['notify_1d',             'INTEGER NOT NULL DEFAULT 1'],
    ['notify_due',            'INTEGER NOT NULL DEFAULT 1'],
    ['notify_overdue',        'INTEGER NOT NULL DEFAULT 1'],
  ];
  for (const [col, def] of newUserCols) {
    if (!userCols.includes(col)) db.exec(`ALTER TABLE users ADD COLUMN ${col} ${def}`);
  }

  // ── payments: soft-delete column (v0.2) ──────────────────────────────────
  const paymentCols = db.prepare('PRAGMA table_info(payments)').all().map(c => c.name);
  if (!paymentCols.includes('deleted_at')) {
    db.exec('ALTER TABLE payments ADD COLUMN deleted_at TEXT');
    // Index for fast filtering of live payments
    db.exec('CREATE INDEX IF NOT EXISTS idx_payments_deleted ON payments(deleted_at)');
    console.log('[migration] payments.deleted_at column added');
  }

  // ── payments: compound index for tracker query (v0.3) ─────────────────────
  // Supports: WHERE bill_id = ? AND paid_date BETWEEN ? AND ? AND deleted_at IS NULL
  db.exec('CREATE INDEX IF NOT EXISTS idx_payments_bill_date_del ON payments(bill_id, paid_date, deleted_at)');

  // ── monthly_bill_state: per-bill per-month overrides (v0.4) ───────────────
  db.exec(`
    CREATE TABLE IF NOT EXISTS monthly_bill_state (
      id            INTEGER PRIMARY KEY AUTOINCREMENT,
      bill_id       INTEGER NOT NULL REFERENCES bills(id) ON DELETE CASCADE,
      year          INTEGER NOT NULL CHECK(year BETWEEN 2000 AND 2100),
      month         INTEGER NOT NULL CHECK(month BETWEEN 1 AND 12),
      actual_amount REAL,
      notes         TEXT,
      is_skipped    INTEGER NOT NULL DEFAULT 0,
      created_at    TEXT DEFAULT (datetime('now')),
      updated_at    TEXT DEFAULT (datetime('now')),
      UNIQUE(bill_id, year, month)
    )
  `);
  db.exec('CREATE INDEX IF NOT EXISTS idx_monthly_bill_state_lookup ON monthly_bill_state(bill_id, year, month)');
  console.log('[migration] monthly_bill_state table ensured');

  // ── import_sessions: temporary preview state (v0.38) ─────────────────────
  db.exec(`
    CREATE TABLE IF NOT EXISTS import_sessions (
      id           TEXT    PRIMARY KEY,
      user_id      INTEGER NOT NULL,
      created_at   TEXT    NOT NULL,
      expires_at   TEXT    NOT NULL,
      preview_json TEXT    NOT NULL
    )
  `);
  db.exec('CREATE INDEX IF NOT EXISTS idx_import_sessions_user ON import_sessions(user_id)');
  db.exec('CREATE INDEX IF NOT EXISTS idx_import_sessions_expires ON import_sessions(expires_at)');

  // ── import_history: per-user audit log (v0.38) ────────────────────────────
  db.exec(`
    CREATE TABLE IF NOT EXISTS import_history (
      id               INTEGER PRIMARY KEY AUTOINCREMENT,
      user_id          INTEGER NOT NULL,
      imported_at      TEXT    NOT NULL,
      source_filename  TEXT,
      file_type        TEXT    DEFAULT 'xlsx',
      sheet_name       TEXT,
      rows_parsed      INTEGER DEFAULT 0,
      rows_created     INTEGER DEFAULT 0,
      rows_updated     INTEGER DEFAULT 0,
      rows_skipped     INTEGER DEFAULT 0,
      rows_ambiguous   INTEGER DEFAULT 0,
      rows_errored     INTEGER DEFAULT 0,
      options_json     TEXT,
      summary_json     TEXT
    )
  `);
  db.exec('CREATE INDEX IF NOT EXISTS idx_import_history_user ON import_history(user_id)');

  // ── users: profile columns (v0.13) ───────────────────────────────────────
  const userColsNow = db.prepare('PRAGMA table_info(users)').all().map(c => c.name);
  const profileCols = [
    ['display_name',           'TEXT'],
    ['last_password_change_at','TEXT'],
  ];
  for (const [col, def] of profileCols) {
    if (!userColsNow.includes(col)) db.exec(`ALTER TABLE users ADD COLUMN ${col} ${def}`);
  }

  // ── ownership: user-scoped bills/categories (v0.40) ──────────────────────
  const billCols = db.prepare('PRAGMA table_info(bills)').all().map(c => c.name);
  if (!billCols.includes('user_id')) {
    db.exec('ALTER TABLE bills ADD COLUMN user_id INTEGER REFERENCES users(id) ON DELETE CASCADE');
  }
  const categoryCols = db.prepare('PRAGMA table_info(categories)').all().map(c => c.name);
  if (!categoryCols.includes('user_id')) {
    db.exec('ALTER TABLE categories ADD COLUMN user_id INTEGER REFERENCES users(id) ON DELETE CASCADE');
  }
  const categorySql = db.prepare("SELECT sql FROM sqlite_master WHERE type='table' AND name='categories'").get()?.sql || '';
  if (/name\s+TEXT\s+NOT\s+NULL\s+UNIQUE/i.test(categorySql)) {
    db.exec('PRAGMA foreign_keys = OFF');
    db.exec(`
      CREATE TABLE IF NOT EXISTS categories_v040 (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
        name TEXT NOT NULL,
        created_at TEXT DEFAULT (datetime('now')),
        updated_at TEXT DEFAULT (datetime('now'))
      )
    `);
    db.exec('INSERT INTO categories_v040 (id, user_id, name, created_at, updated_at) SELECT id, user_id, name, created_at, updated_at FROM categories');
    db.exec('DROP TABLE categories');
    db.exec('ALTER TABLE categories_v040 RENAME TO categories');
    db.exec('PRAGMA foreign_keys = ON');
  }

  const firstUser = db.prepare("SELECT id FROM users WHERE role = 'user' ORDER BY id LIMIT 1").get();
  if (firstUser) {
    db.prepare('UPDATE bills SET user_id = ? WHERE user_id IS NULL').run(firstUser.id);
    // Drop any NULL-owner categories whose name already exists for this user (case-insensitive)
    // to prevent a UNIQUE(user_id, name) violation when we assign them below.
    db.prepare(`
      DELETE FROM categories
      WHERE user_id IS NULL
        AND LOWER(name) IN (
          SELECT LOWER(name) FROM categories WHERE user_id = ?
        )
    `).run(firstUser.id);
    db.prepare('UPDATE categories SET user_id = ? WHERE user_id IS NULL').run(firstUser.id);
  }
  db.exec('CREATE INDEX IF NOT EXISTS idx_bills_user_active ON bills(user_id, active)');
  db.exec('CREATE INDEX IF NOT EXISTS idx_categories_user_name ON categories(user_id, name)');
  db.exec('CREATE UNIQUE INDEX IF NOT EXISTS idx_categories_user_name_unique ON categories(user_id, name COLLATE NOCASE)');

  // ── bills: history visibility mode (v0.14) ───────────────────────────────
  const billColsHist = db.prepare('PRAGMA table_info(bills)').all().map(c => c.name);
  if (!billColsHist.includes('history_visibility')) {
    db.exec("ALTER TABLE bills ADD COLUMN history_visibility TEXT NOT NULL DEFAULT 'default'");
    console.log('[migration] bills.history_visibility column added');
  }

  // ── bills: optional credit-card APR / interest rate (v0.14.4) ───────────
  const billColsInterest = db.prepare('PRAGMA table_info(bills)').all().map(c => c.name);
  if (!billColsInterest.includes('interest_rate')) {
    db.exec('ALTER TABLE bills ADD COLUMN interest_rate REAL');
    console.log('[migration] bills.interest_rate column added');
  }

  // ── bill_history_ranges: per-bill date ranges for history visibility (v0.14)
  db.exec(`
    CREATE TABLE IF NOT EXISTS bill_history_ranges (
      id          INTEGER PRIMARY KEY AUTOINCREMENT,
      bill_id     INTEGER NOT NULL REFERENCES bills(id) ON DELETE CASCADE,
      start_year  INTEGER NOT NULL,
      start_month INTEGER NOT NULL,
      end_year    INTEGER,
      end_month   INTEGER,
      label       TEXT,
      created_at  TEXT DEFAULT (datetime('now')),
      updated_at  TEXT DEFAULT (datetime('now'))
    )
  `);
  db.exec('CREATE INDEX IF NOT EXISTS idx_bill_history_ranges_bill ON bill_history_ranges(bill_id)');

  // ── users: external identity / OIDC columns (v0.17) ──────────────────────
  const userColsOidc = db.prepare('PRAGMA table_info(users)').all().map(c => c.name);
  const oidcUserCols = [
    ['auth_provider',    "TEXT NOT NULL DEFAULT 'local'"],
    ['external_subject', 'TEXT'],
    ['email',            'TEXT'],
    ['last_login_at',    'TEXT'],
  ];
  for (const [col, def] of oidcUserCols) {
    if (!userColsOidc.includes(col)) {
      db.exec(`ALTER TABLE users ADD COLUMN ${col} ${def}`);
    }
  }

  // ── oidc_states: short-lived PKCE + nonce state for OIDC login (v0.17) ───
  db.exec(`
    CREATE TABLE IF NOT EXISTS oidc_states (
      id            TEXT PRIMARY KEY,
      nonce         TEXT NOT NULL,
      code_verifier TEXT NOT NULL,
      redirect_to   TEXT,
      created_at    TEXT NOT NULL,
      expires_at    TEXT NOT NULL
    )
  `);
  db.exec('CREATE INDEX IF NOT EXISTS idx_oidc_states_expires ON oidc_states(expires_at)');
}

function seedDefaults() {
  const defaults = [
    ['currency', 'USD'],
    ['date_format', 'MM/DD/YYYY'],
    ['grace_period_days', '5'],
    ['notify_days_before', '3'],
    ['backup_enabled', 'false'],
    ['backup_frequency_days', '1'],
    ['backup_keep_count', '14'],
    ['backup_path', process.env.BACKUP_PATH || path.join(__dirname, '..', 'backups')],
    ['backup_schedule_enabled', 'false'],
    ['backup_schedule_frequency', 'daily'],
    ['backup_schedule_time', '02:00'],
    ['backup_schedule_retention_count', '14'],
    ['backup_schedule_last_run_at', ''],
    ['backup_schedule_last_error', ''],
    ['auth_mode', 'multi'],
    ['default_user_id', ''],
    ['notify_smtp_enabled', 'false'],
    ['notify_sender_name', 'Bill Tracker'],
    ['notify_sender_address', ''],
    ['notify_smtp_host', ''],
    ['notify_smtp_port', '587'],
    ['notify_smtp_encryption', 'starttls'],
    ['notify_smtp_self_signed', 'false'],
    ['notify_smtp_username', ''],
    ['notify_smtp_password', ''],
    ['notify_allow_user_config', 'false'],
    ['notify_global_recipient', ''],
    // Cleanup worker settings (v0.15)
    ['cleanup_import_sessions_enabled',     'true'],
    ['cleanup_temp_exports_enabled',        'true'],
    ['cleanup_temp_export_max_age_hours',   '2'],
    ['cleanup_backup_partials_enabled',     'true'],
    ['cleanup_import_history_enabled',      'false'],
    ['cleanup_import_history_max_age_days', '365'],
    ['cleanup_last_run_at',  ''],
    ['cleanup_last_result',  ''],
    // Auth method settings (v0.18)
    ['local_login_enabled',  'true'],
    ['oidc_login_enabled',   'false'],
    ['oidc_provider_name',   'authentik'],
    ['oidc_issuer_url',      ''],
    ['oidc_client_id',       ''],
    ['oidc_client_secret',   ''],
    ['oidc_token_auth_method', 'client_secret_basic'],
    ['oidc_redirect_uri',    ''],
    ['oidc_scopes',          'openid email profile groups'],
    ['oidc_auto_provision',  'true'],
    ['oidc_admin_group',     ''],
    ['oidc_default_role',    'user'],
  ];

  const insert = db.prepare(
    'INSERT OR IGNORE INTO settings (key, value) VALUES (?, ?)'
  );

  for (const [key, value] of defaults) {
    insert.run(key, value);
  }

  const insertCat = db.prepare(
    'INSERT INTO categories (name) VALUES (?)'
  );

  for (const name of DEFAULT_CATEGORIES) {
    const existing = db.prepare('SELECT id FROM categories WHERE user_id IS NULL AND name = ? COLLATE NOCASE').get(name);
    if (!existing) insertCat.run(name);
  }
}

function ensureUserDefaultCategories(userId) {
  const db = getDb();
  const insert = db.prepare('INSERT INTO categories (user_id, name) VALUES (?, ?)');
  for (const name of DEFAULT_CATEGORIES) {
    const existing = db.prepare('SELECT id FROM categories WHERE user_id = ? AND name = ? COLLATE NOCASE')
      .get(userId, name);
    if (!existing) insert.run(userId, name);
  }
}

function getSetting(key) {
  const db = getDb();
  const row = db.prepare('SELECT value FROM settings WHERE key = ?').get(key);
  return row ? row.value : null;
}

function setSetting(key, value) {
  const db = getDb();
  db.prepare(
    "INSERT OR REPLACE INTO settings (key, value, updated_at) VALUES (?, ?, datetime('now'))"
  ).run(key, String(value));
}

function closeDb() {
  if (!db) return;
  db.close();
  db = null;
}

function getDbPath() {
  return DB_PATH;
}

module.exports = { getDb, getSetting, setSetting, closeDb, getDbPath, ensureUserDefaultCategories };