const express = require("express");
const bodyParser = require("body-parser");
const sqlite3 = require("sqlite3").verbose(); // Use SQLite
const path = require("path");

const app = express();
const PORT = process.env.PORT || 7860;
app.set("view engine", "ejs");
app.use(bodyParser.urlencoded({ extended: true }));
app.use(express.json()); // Support JSON bodies for API
app.use(express.static(path.join(__dirname, 'public')));

// Connect to SQLite Database (File-based)
const db = new sqlite3.Database('./database.sqlite', (err) => {
  if (err) {
    console.error(err.message);
  }
  console.log('Connected to the SQLite database.');
});

// Helper to get common data (rules, traits, history)
const getCommonData = (callback) => {
    db.all("SELECT * FROM grade_rules ORDER BY min_avg DESC", [], (err, rules) => {
        if (err) rules = [];
        db.all("SELECT * FROM personality_traits", [], (err, traits) => {
            if (err) traits = [];
            db.all("SELECT * FROM sampleStudent ORDER BY id DESC LIMIT 5", [], (err, history) => {
                if (err) history = [];
                callback({ rules, traits, history });
            });
        });
    });
};

app.get("/", (req, res) => {
  getCommonData((data) => {
      res.render("index", { result: null, ...data });
  });
});

// --- API Endpoints ---

// Get recent students
app.get("/api/students", (req, res) => {
    db.all("SELECT * FROM sampleStudent ORDER BY id DESC LIMIT 10", [], (err, rows) => {
        if (err) {
            res.status(500).json({ error: err.message });
            return;
        }
        res.json(rows);
    });
});

// Get grading rules
app.get("/api/rules", (req, res) => {
    db.all("SELECT * FROM grade_rules ORDER BY min_avg DESC", [], (err, rows) => {
        if (err) {
             res.status(500).json({ error: err.message });
             return;
        }
        res.json(rows);
    });
});

// Get personality traits
app.get("/api/traits", (req, res) => {
    db.all("SELECT * FROM personality_traits", [], (err, rows) => {
        if (err) {
            res.status(500).json({ error: err.message });
            return;
        }
        res.json(rows);
    });
});

// Analyze student and save result
app.post("/api/analyze", (req, res) => {
    const { name, sub1, sub2, sub3 } = req.body;
    
    if (!name || sub1 === undefined || sub2 === undefined || sub3 === undefined) {
        return res.status(400).json({ error: "Missing required fields: name, sub1, sub2, sub3" });
    }

    const s1 = parseInt(sub1);
    const s2 = parseInt(sub2);
    const s3 = parseInt(sub3);
    const avg = Math.round((s1 + s2 + s3) / 3);

    // 1. Insert Student
    const insertQuery = `INSERT INTO sampleStudent (name, subject1, subject2, subject3) VALUES (?, ?, ?, ?)`;
    
    db.run(insertQuery, [name, s1, s2, s3], function(err) {
      if (err) {
        return res.status(500).json({ error: "Error saving student data: " + err.message });
      }
  
      // 2. Find Grade
      const gradeQuery = `SELECT grade FROM grade_rules WHERE ? >= min_avg AND ? <= max_avg LIMIT 1`;
  
      db.get(gradeQuery, [avg, avg], (err, gradeRow) => {
        if (err || !gradeRow) {
            return res.status(404).json({ error: "Grade not found for average: " + avg });
        }
        
        const grade = gradeRow.grade;
  
        // 3. Find Trait
        const traitQuery = `SELECT trait FROM personality_traits WHERE grade = ?`;
        db.get(traitQuery, [grade], (err, traitRow) => {
           if (err || !traitRow) {
             return res.status(404).json({ error: "Trait not found for grade: " + grade });
           }
  
           res.json({
               success: true,
               data: {
                   name,
                   average: avg,
                   grade,
                   trait: traitRow.trait
               }
           });
        });
      });
    });
});

// --- End API Endpoints ---

app.post("/result", (req, res) => {
  const { name, sub1, sub2, sub3 } = req.body;

  const s1 = parseInt(sub1);
  const s2 = parseInt(sub2);
  const s3 = parseInt(sub3);
  const avg = Math.round((s1 + s2 + s3) / 3);

  // 1. Insert Student
  const insertQuery = `INSERT INTO sampleStudent (name, subject1, subject2, subject3) VALUES (?, ?, ?, ?)`;
  
  db.run(insertQuery, [name, s1, s2, s3], function(err) {
    if (err) {
      return res.send("Error saving student data: " + err.message);
    }

    // 2. Find Grade
    const gradeQuery = `SELECT grade FROM grade_rules WHERE ? >= min_avg AND ? <= max_avg LIMIT 1`;

    db.get(gradeQuery, [avg, avg], (err, gradeRow) => {
      if (err || !gradeRow) {
          console.log("No grade found for avg:", avg);
          // Only rendering if valid, else simplistic error for now
          return res.send("Grade not found for average: " + avg);
      }
      
      const grade = gradeRow.grade;

      // 3. Find Trait
      const traitQuery = `SELECT trait FROM personality_traits WHERE grade = ?`;
      db.get(traitQuery, [grade], (err, traitRow) => {
         if (err || !traitRow) {
           return res.send("Trait not found for grade: " + grade);
         }

         // Fetch updated history and rules for display
         getCommonData((data) => {
            res.render("index", {
                result: {
                    name,
                    avg,
                    grade,
                    trait: traitRow.trait
                },
                ...data
            });
         });
      });
    });
  });
});

app.listen(PORT, () => {
  console.log(`Server running on port ${PORT}`);
});