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

const dbPath = path.resolve(__dirname, 'database.sqlite');
// Use persistent database

const db = new sqlite3.Database(dbPath, (err) => {
    if (err) {
        console.error('Error opening database', err.message);
    } else {
        console.log('Connected to the SQLite database.');
        db.serialize(() => {
            db.run(`CREATE TABLE IF NOT EXISTS districts (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT UNIQUE NOT NULL
            )`);
            
            db.run(`CREATE TABLE IF NOT EXISTS students (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                district_id INTEGER,
                roll_number TEXT UNIQUE NOT NULL,
                name TEXT NOT NULL,
                dob TEXT,
                father_name TEXT,
                mother_name TEXT,
                contact_number TEXT,
                FOREIGN KEY(district_id) REFERENCES districts(id)
            )`);

            db.run(`CREATE TABLE IF NOT EXISTS test_scores (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                student_id INTEGER,
                test_name TEXT NOT NULL,
                physics_score INTEGER NOT NULL,
                chemistry_score INTEGER NOT NULL,
                biology_score INTEGER NOT NULL,
                total_score INTEGER NOT NULL,
                FOREIGN KEY(student_id) REFERENCES students(id)
            )`);
            
            // Seed initial data
            db.run(`INSERT OR IGNORE INTO districts (name) VALUES ('Chennai'), ('Coimbatore'), ('Madurai')`);
        });
    }
});

module.exports = db;
