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

// Create new language qualification
router.post('/create', upload.none(), (req, res) => {
    console.log('Request body:', req.body); 

    const {
        langQualName,
        university,
        teachingMethod,
        duration,
        courseFee,
        payMethod,
        description,
        instituteAddress,
        institutePhone,
        instituteEmail,
        instituteWebsite,
        instituteLocation,
        institute_id
    } = req.body;

    // Ensure all required fields are provided
    if (!langQualName || !university || !teachingMethod || !duration || !courseFee || !payMethod 
        || !instituteAddress || !institutePhone || !instituteEmail || !institute_id) {
        return res.status(400).json({ message: "Please fill in all required fields" });
    }

    // Check for duplicate language qualification
    const checkDuplicate = `
        SELECT * FROM langqualifications
        WHERE langQualName = ? AND university = ? AND teachingMethod = ? AND duration = ? AND courseFee = ? AND payMethod = ? AND institute_id = ?
    `;
    const checkValues = [
        langQualName, university, teachingMethod, duration, courseFee, payMethod, institute_id
    ];

    db.query(checkDuplicate, checkValues, (err, results) => {
        if (err) {
            console.error('Error checking for duplicates:', err);
            return res.status(500).json({ message: 'Server error' });
        }

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

        // SQL query to insert a new language qualification
        const saveLangQual = `
            INSERT INTO langqualifications (
                langQualName, university, teachingMethod, duration, courseFee, payMethod, description,
                instituteAddress, institutePhone, instituteEmail, instituteWebsite, instituteLocation, institute_id, approved, deleted
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, FALSE, FALSE)
        `;

        const saveValues = [
            langQualName, university, teachingMethod, duration, courseFee, payMethod, description,
            instituteAddress, institutePhone, instituteEmail, instituteWebsite, instituteLocation, institute_id
        ];

        db.query(saveLangQual, saveValues, (err, result) => {
            if (err) {
                console.error('Error saving language qualification:', err);
                return res.status(500).json({ message: 'Server error' });
            }
            res.status(200).json({ message: 'Language qualification added successfully' });
        });
    });
});

// Get all approved and non-deleted langqualifications with optional filters
router.get('/all', (req, res) => {
    const {university, langQualName, institute_id } = req.query;
    let query = 'SELECT * FROM langqualifications WHERE deleted = FALSE AND approved = TRUE';
    const values = [];
  
    if (university) {  
      query += ' AND university = ?';
      values.push(university);
    }

    if (langQualName) {  
        query += ' AND langQualName = ?';
        values.push(langQualName);
      }
  
    if (institute_id) {
      query += ' AND institute_id = ?';
      values.push(institute_id);
    }
  
    db.query(query, values, (err, results) => {
      if (err) {
        console.error('Error getting data:', err);
        return res.status(500).send('Server error');
      }
      res.json(results);
    });
  });
  
  // Get all deleted langqualifications
  router.get('/deleted', (req, res) => {
    const { institute_id } = req.query;
    let query = 'SELECT * FROM langqualifications WHERE deleted = TRUE';
    const values = [];
  
    if (institute_id) {
      query += ' AND institute_id = ?';
      values.push(institute_id);
    }
  
    db.query(query, values, (err, results) => {
      if (err) {
        console.error('Error getting data:', err);
        return res.status(500).send('Server error');
      }
      res.json(results);
    });
  });
  
  // Move langqualifications to deleted state
  router.post('/delete/:id', (req, res) => {
    const query = 'UPDATE langqualifications SET deleted = TRUE WHERE id = ?';
    const values = [req.params.id];
    db.query(query, values, (err, result) => {
      if (err) {
        console.error('Error updating data:', err);
        return res.status(500).send('Server error');
      }
      res.json({ message: ' Language Qualification moved to deleted state' });
    });
  });
  
  // Get all non-deleted and non-approved scholarships
  router.get('/pending', (req, res) => {
    const { institute_id } = req.query;
    let query = 'SELECT * FROM langqualifications WHERE deleted = FALSE AND approved = FALSE';
    const values = [];
  
    if (institute_id) {
      query += ' AND institute_id = ?';
      values.push(institute_id);
    }
  
    db.query(query, values, (err, results) => {
      if (err) {
        console.error('Error getting data:', err);
        return res.status(500).send('Server error');
      }
      res.json(results);
    });
  });
  
  // Approve langqualifications from created state
  router.post('/approve/:id', (req, res) => {
    const query = 'UPDATE langqualifications SET approved = TRUE WHERE id = ?';
    const values = [req.params.id];
    db.query(query, values, (err, result) => {
      if (err) {
        console.error('Error updating data:', err);
        return res.status(500).send('Server error');
      }
      res.json({ message: 'Language Qualification approved' });
    });
  });
  
  // Restore langqualifications from deleted state
  router.post('/restore/:id', (req, res) => {
    const query = 'UPDATE langqualifications SET deleted = FALSE WHERE id = ?';
    const values = [req.params.id];
    db.query(query, values, (err, result) => {
      if (err) {
        console.error('Error updating data:', err);
        return res.status(500).send('Server error');
      }
      res.json({ message: 'Language Qualification restored successfully' });
    });
  });
  
  // Permanently delete langqualifications
  router.delete('/delete/permanent/:id', (req, res) => {
    const query = 'DELETE FROM langqualifications WHERE id = ?';
    const values = [req.params.id];
    db.query(query, values, (err, result) => {
      if (err) {
        console.error('Error deleting data:', err);
        return res.status(500).send('Server error');
      }
      res.json({ message: 'Language Qualification permanently deleted' });
    });
  });
  
  // Get stdLoans by langqualifications ID
  router.get('/:id', (req, res) => {
    const id = req.params.id;
    db.query('SELECT * FROM langqualifications WHERE id = ?', [id], (error, result) => {
      if (error) {
        console.error('Error retrieving data:', error);
        return res.status(500).send('Server error');
      }
      res.json(result[0]);
    });
  });
module.exports = router;
