/**
 * ─────────────────────────────────────────────
 *  SPB Drive — Personal Cloud Drive
 * ─────────────────────────────────────────────
 *  Author  : Simon-Pierre Boucher
 *  Contact : contact@spboucher.ai
 *  File    : src/db/db.mjs
 *  Purpose : SQLite bootstrap (better-sqlite3, WAL), migrations, root node seed
 *  License : MIT © Simon-Pierre Boucher
 * ─────────────────────────────────────────────
 */

import { readFileSync } from 'node:fs';
import path from 'node:path';
import { fileURLToPath } from 'node:url';
import Database from 'better-sqlite3';
import { config } from '../config.mjs';

const HERE = path.dirname(fileURLToPath(import.meta.url));

/** @type {import('better-sqlite3').Database | null} */
let db = null;

/**
 * Open (or return the already-open) database, applying schema migrations
 * and seeding the root folder node on first run.
 * @param {string} [file] Override DB path (tests).
 * @returns {import('better-sqlite3').Database}
 */
export function getDb(file) {
  if (db) return db;
  db = new Database(file ?? config.dbFile);
  db.pragma('journal_mode = WAL');
  db.pragma('foreign_keys = ON');
  db.pragma('synchronous = NORMAL');

  db.exec(readFileSync(path.join(HERE, 'schema.sql'), 'utf8'));
  migrate(db);

  const root = db.prepare('SELECT id FROM nodes WHERE id = 1').get();
  if (!root) {
    db.prepare(
      `INSERT INTO nodes (id, parent_id, name, type, created, modified)
       VALUES (1, NULL, '', 'folder', ?, ?)`,
    ).run(Date.now(), Date.now());
  }
  return db;
}

/**
 * Column-level migrations for tables that predate v2 (CREATE IF NOT EXISTS
 * won't touch existing tables, so new columns are added here).
 */
function migrate(database) {
  const addColumn = (table, column, ddl) => {
    const present = database
      .prepare(`SELECT 1 FROM pragma_table_info(?) WHERE name = ?`)
      .get(table, column);
    if (!present) database.exec(`ALTER TABLE ${table} ADD COLUMN ${column} ${ddl}`);
  };
  // v2: public file-request uploads are tagged with their request id.
  addColumn('uploads', 'source', 'TEXT');
}

/** Close the database (tests / graceful shutdown). */
export function closeDb() {
  if (db) {
    db.close();
    db = null;
  }
}

/**
 * Record an activity-log entry.
 * @param {string} kind e.g. 'login', 'upload', 'rename', 'share.create'
 * @param {{nodeId?: number|null, detail?: string, ip?: string}} [extra]
 */
export function logActivity(kind, { nodeId = null, detail = '', ip = '' } = {}) {
  getDb()
    .prepare('INSERT INTO activity (ts, kind, node_id, detail, ip) VALUES (?, ?, ?, ?, ?)')
    .run(Date.now(), kind, nodeId, detail, ip);
}