const express = require('express');
const router = express.Router();
const db = require('../database/db');

// Register a new student
router.post('/stdregister', (req, res) => {
  const {
    name, address, district, province, phone, mobile, email, school, course, category, message
  } = req.body;

  // Check if student is already registered based on email or phone
  const checkSql = 'SELECT * FROM stdregistrations WHERE email = ? OR phone = ?';
  const checkValues = [email, phone];

  db.query(checkSql, checkValues, (err, results) => {
    if (err) {
      console.error('Error checking existing data:', err);
      return res.status(500).send('Server error');
    }

    if (results.length > 0) {
      return res.status(400).json({ message: 'Student already registered' });
    }

    // Insert new student registration
    const insertSql = `
      INSERT INTO stdregistrations
      (name, address, district, province, phone, mobile, email, school, course, category, message, approved)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, FALSE)
    `;
    const insertValues = [name, address, district, province, phone, mobile, email, school, course, category, message];

    db.query(insertSql, insertValues, (err, result) => {
      if (err) {
        console.error('Error inserting data:', err);
        return res.status(500).send('Server error');
      }
      res.json({ message: 'Student registered successfully' });
    });
  });
});

// Fetch all students (approved)
router.get('/allstudents', (req, res) => {
  const fetchSql = 'SELECT * FROM stdregistrations WHERE approved = TRUE AND deleted = FALSE';

  db.query(fetchSql, (err, results) => {
    if (err) {
      console.error('Error fetching data:', err);
      return res.status(500).send('Server error');
    }
    res.json(results);
  });
});

// Fetch all pending students
router.get('/pending', (req, res) => {
  const fetchSql = 'SELECT * FROM stdregistrations WHERE approved = FALSE AND deleted = FALSE';

  db.query(fetchSql, (err, results) => {
    if (err) {
      console.error('Error fetching data:', err);
      return res.status(500).send('Server error');
    }
    res.json(results);
  });
});

// Fetch all deleted students
router.get('/deleted', (req, res) => {
  const fetchSql = 'SELECT * FROM stdregistrations WHERE deleted = TRUE';

  db.query(fetchSql, (err, results) => {
    if (err) {
      console.error('Error fetching deleted students:', err);
      return res.status(500).send('Server error');
    }
    res.json(results);
  });
});

// Soft delete a student
router.delete('/delete/:id', (req, res) => {
  const { id } = req.params;
  const updateSql = 'UPDATE stdregistrations SET deleted = TRUE WHERE id = ?';

  db.query(updateSql, [id], (err, result) => {
    if (err) {
      console.error('Error deleting data:', err);
      return res.status(500).send('Server error');
    }
    res.sendStatus(204);
  });
});

// Restore a student
router.post('/restore/:id', (req, res) => {
  const { id } = req.params;
  const updateSql = 'UPDATE stdregistrations SET deleted = FALSE WHERE id = ?';

  db.query(updateSql, [id], (err, result) => {
    if (err) {
      console.error('Error restoring data:', err);
      return res.status(500).send('Server error');
    }
    res.sendStatus(204);
  });
});

// Permanently delete a student
router.delete('/delete/permanent/:id', (req, res) => {
  const { id } = req.params;
  const deleteSql = 'DELETE FROM stdregistrations WHERE id = ?';

  db.query(deleteSql, [id], (err, result) => {
    if (err) {
      console.error('Error deleting data:', err);
      return res.status(500).send('Server error');
    }
    res.sendStatus(204);
  });
});

// Approve a student
router.post('/approve/:id', (req, res) => {
  const { id } = req.params;
  const updateSql = 'UPDATE stdregistrations SET approved = TRUE WHERE id = ?';

  db.query(updateSql, [id], (err, result) => {
    if (err) {
      console.error('Error approving student:', err);
      return res.status(500).send('Server error');
    }
    res.sendStatus(204);
  });
});

module.exports = router;
