const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database('./database.sqlite');

db.serialize(() => {
  // Create Tables
  db.run(`CREATE TABLE IF NOT EXISTS sampleStudent (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    subject1 INTEGER,
    subject2 INTEGER,
    subject3 INTEGER
  )`);

  db.run(`CREATE TABLE IF NOT EXISTS grade_rules (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    min_avg INTEGER,
    max_avg INTEGER,
    grade TEXT
  )`);

  db.run(`CREATE TABLE IF NOT EXISTS personality_traits (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    grade TEXT,
    trait TEXT
  )`);

  // Seed Data (Check if exists first to avoid duplicates on re-run, or just delete file)
  // For simplicity in this script, we'll clear and re-insert logic tables
  db.run(`DELETE FROM grade_rules`);
  const gradeStmt = db.prepare("INSERT INTO grade_rules (min_avg, max_avg, grade) VALUES (?, ?, ?)");
  gradeStmt.run(90, 100, 'A');
  gradeStmt.run(75, 89, 'B');
  gradeStmt.run(60, 74, 'C');
  gradeStmt.run(40, 59, 'D');
  gradeStmt.run(0, 39, 'E');
  gradeStmt.finalize();

  db.run(`DELETE FROM personality_traits`);
  const traitStmt = db.prepare("INSERT INTO personality_traits (grade, trait) VALUES (?, ?)");
  traitStmt.run('A', 'Highly Disciplined & Leader');
  traitStmt.run('B', 'Confident & Responsible');
  traitStmt.run('C', 'Hardworking & Consistent');
  traitStmt.run('D', 'Needs Motivation');
  traitStmt.run('E', 'Requires Improvement');
  traitStmt.finalize();

  console.log("SQLite Database initialized with tables and seed data.");
});

db.close();