db.js

const fs = require('fs');
const path = require('path');
const sqlite3 = require('sqlite3').verbose();
const bcrypt = require('bcrypt');

const dataDir = path.join(__dirname, 'data');
if (!fs.existsSync(dataDir)) fs.mkdirSync(dataDir);

const DB_PATH = path.join(dataDir, 'database.sqlite');
const db = new sqlite3.Database(DB_PATH);

db.serialize(() => {
  db.run(`CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE,
    password TEXT
  )`);

  db.run(`CREATE TABLE IF NOT EXISTS password_resets (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER,
    token TEXT UNIQUE,
    expires_at INTEGER,
    FOREIGN KEY(user_id) REFERENCES users(id)
  )`);

  db.run(`CREATE TABLE IF NOT EXISTS students (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    email TEXT UNIQUE,
    course TEXT,
    year INTEGER
  )`);

  // seed admin user if not exists
  db.get('SELECT COUNT(*) as cnt FROM users', (err, row) => {
    if (err) return console.error(err);
    if (row && row.cnt === 0) {
      const pass = bcrypt.hashSync('admin123', 10);
      db.run('INSERT INTO users (username, password) VALUES (?, ?)', ['admin', pass]);
    }
  });

  // seed sample students if none
  db.get('SELECT COUNT(*) as cnt FROM students', (err, row) => {
    if (err) return console.error(err);
    if (row && row.cnt === 0) {
      const stmt = db.prepare('INSERT INTO students (name, email, course, year) VALUES (?, ?, ?, ?)');
      stmt.run('Alice Johnson', 'alice@example.com', 'Computer Science', 2);
      stmt.run('Bob Smith', 'bob@example.com', 'Mathematics', 3);
      stmt.run('Carol Lee', 'carol@example.com', 'Physics', 1);
      stmt.finalize();
    }
  });
});

module.exports = db;