peworkshop/lib/db.js

import sqlite3 from "sqlite3";

// Create a singleton database connection
let db = null;

export function getDb() {
  if (!db) {
    db = new sqlite3.Database("./clue_hunt.db", (err) => {
      if (err) {
        console.error("Error connecting to database:", err);
      } else {
        console.log("Connected to the SQLite database.");
      }
    });
  }
  return db;
}

// Execute a query that returns a single row
export function queryOne(sql, params = []) {
  return new Promise((resolve, reject) => {
    const db = getDb();
    if (!db) {
      reject(Error("Database connection not available"));
      return;
    }

    db.get(sql, params, function (err, row) {
      if (err) {
        console.error("SQL Error in queryOne():", err.message);
        console.error("SQL Statement:", sql);
        console.error("Parameters:", JSON.stringify(params));
        reject(err);
        return;
      }
      resolve(row);
    });
  });
}

// Execute a query that returns multiple rows
export function query(sql, params = []) {
  return new Promise((resolve, reject) => {
    const db = getDb();
    if (!db) {
      reject(Error("Database connection not available"));
      return;
    }

    db.all(sql, params, function (err, rows) {
      if (err) {
        console.error("SQL Error in query():", err.message);
        console.error("SQL Statement:", sql);
        console.error("Parameters:", JSON.stringify(params));
        reject(err);
        return;
      }
      resolve(rows || []);
    });
  });
}

// Execute a query that doesn't return results (INSERT, UPDATE, DELETE)
export function run(sql, params = []) {
  return new Promise((resolve, reject) => {
    const db = getDb();
    if (!db) {
      const error = Error("Database connection not available");
      console.error(error);
      reject(error);
      return;
    }

    // Log transaction operations for debugging
    if (
      sql.toLowerCase().includes("begin") ||
      sql.toLowerCase().includes("commit") ||
      sql.toLowerCase().includes("rollback")
    ) {
      console.log(`Transaction operation: ${sql}`);
    }

    db.run(sql, params, function (err) {
      if (err) {
        console.error("SQL Error in run():", err.message);
        console.error("SQL Statement:", sql);
        console.error("Parameters:", JSON.stringify(params));
        reject(err);
        return;
      }
      resolve({ lastID: this.lastID, changes: this.changes });
    });
  });
}

// Close database connection - useful for tests and clean shutdown
export function closeDb() {
  return new Promise((resolve, reject) => {
    if (db) {
      db.close((err) => {
        if (err) {
          console.error("Error closing database:", err.message);
          reject(err);
        } else {
          console.log("Database connection closed");
          db = null;
          resolve();
        }
      });
    } else {
      resolve();
    }
  });
}

// New function to explicitly enable foreign keys
export function enableForeignKeys() {
  return run("PRAGMA foreign_keys = ON;")
    .then(() => console.log("Foreign key constraints enabled"))
    .catch((err) => console.error("Error enabling foreign keys:", err));
}

// New function to run multiple statements as a transaction
export async function runTransaction(statements) {
  try {
    // Begin transaction
    await run("BEGIN TRANSACTION");

    // Execute all statements in order
    for (const [sql, params] of statements) {
      await run(sql, params);
    }

    // Commit on success
    await run("COMMIT");
    return { success: true };
  } catch (error) {
    // Rollback on any error
    try {
      await run("ROLLBACK");
      console.log("Transaction rolled back due to error");
    } catch (rollbackError) {
      console.error("Error during rollback:", rollbackError);
    }
    throw error;
  }
}