peworkshop/scripts/init-db.js

import sqlite3 from 'sqlite3';
import { open } from 'sqlite';
import bcrypt from 'bcryptjs';

async function initDb() {
    const db = await open({
        filename: './clue_hunt.db',
        driver: sqlite3.Database,
    });

    // Create tables
    await db.exec(`
    CREATE TABLE IF NOT EXISTS users (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      username TEXT NOT NULL UNIQUE,
      password TEXT NOT NULL,
      role TEXT NOT NULL DEFAULT 'user'
    );

    CREATE TABLE IF NOT EXISTS teams (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL UNIQUE,
      code TEXT NOT NULL UNIQUE
    );

    CREATE TABLE IF NOT EXISTS clues (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      description TEXT NOT NULL,
      found BOOLEAN NOT NULL DEFAULT FALSE,
      skipped BOOLEAN NOT NULL DEFAULT FALSE,
      team_id INTEGER,
      FOREIGN KEY (team_id) REFERENCES teams(id)
    );

    CREATE TABLE IF NOT EXISTS progress (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      team_id INTEGER,
      clue_id INTEGER,
      timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (team_id) REFERENCES teams(id),
      FOREIGN KEY (clue_id) REFERENCES clues(id)
    );
  `);

    // Seed initial data
    const hashedPassword = bcrypt.hashSync('admin', 10);
    await db.run(
        'INSERT OR IGNORE INTO users (username, password, role) VALUES (?, ?, ?)',
        ['admin', hashedPassword, 'admin']
    );

    await db.run(
        'INSERT OR IGNORE INTO teams (name, code) VALUES (?, ?)',
        ['Team Alpha', 'ALPHA123']
    );

    console.log('Database initialized successfully.');
    await db.close();
}

initDb().catch((err) => console.error('Error initializing database:', err));