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

// Create multer storage
const storage = multer.diskStorage({
  destination: (req, file, cb) => {
    cb(null, 'Upload/images/courses');
  },
  filename: (req, file, cb) => {
    cb(null, file.fieldname + '_' + Date.now() + path.extname(file.originalname));
  }
});

// Upload image
const upload = multer({ storage: storage });

// Save external Degree data to database
router.post('/create', upload.single('cologo'), (req, res) => {
  const {
    university,
    universityType,
    faculty,
    department,
    courseName,
    category,
    level,
    duration,
    startDate,
    specification,
    careerOpportunities,
    industry,
    scholarships,
    courseFee,
    codescription,
    instituteAddress,
    institutePhone,
    instituteEmail,
    instituteLocation,
    institute_id
  } = req.body;

  // Log the request body to check the data being sent
  console.log("Request Body:", req.body);

  // Check for required fields
  if (!university || !universityType || !courseName || !level || !duration || !startDate || !institute_id) {
    return res.status(400).json({ message: "Please fill in all required fields" });
  }

  // Check if the external degree already exists
  const checkExternalDegreeQuery = 'SELECT * FROM external_degrees WHERE university = ? AND courseName = ? AND institute_id = ?';
  db.query(checkExternalDegreeQuery, [university, courseName, institute_id], (err, results) => {
    if (err) {
      console.error('Error checking external degree existence:', err);
      return res.status(500).json({ message: 'Server error while checking external degree existence' });
    }

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

    // Insert new external degree
    const cologo = req.file ? req.file.filename : null;
    const saveExternalDegree = `
      INSERT INTO external_degrees (
        university, universityType, faculty, department, courseName, category,
        level, duration, startDate, specification, careerOpportunities, industry,
        scholarships, courseFee, cologo, codescription, instituteAddress,
        institutePhone, instituteEmail, instituteLocation, institute_id
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `;
    const saveValues = [
      university, universityType, faculty, department, courseName, category,
      level, duration, startDate, specification, careerOpportunities, industry,
      scholarships, courseFee, cologo, codescription, instituteAddress,
      institutePhone, instituteEmail, instituteLocation, institute_id
    ];

    db.query(saveExternalDegree, saveValues, (err, result) => {
      if (err) {
        console.error('Error inserting external degree:', err);
        return res.status(500).json({ message: 'Server error while inserting external degree' });
      }
      res.json({ message: 'External degree created successfully' });
    });
  });
});

// Update external_degrees details
router.post('/update/:id', upload.single('cologo'), (req, res) => {
  const {
    university, universityType, faculty, department, courseName, category,
    level, duration, startDate, specification, careerOpportunities, industry,
    scholarships, courseFee, codescription, instituteAddress,
    institutePhone, instituteEmail, instituteLocation
  } = req.body;

  const cologo = req.file ? req.file.filename : null;

  const updateExternalDegree = `
    UPDATE external_degrees SET
    university = ?, universityType = ?, courseName = ?, level = ?, duration = ?, startDate = ?, category = ?, specification = ?, courseFee = ?, careerOpportunities = ?, industry = ?, cologo = ?, codescription = ?, instituteAddress = ?, institutePhone = ?, instituteEmail = ?, instituteLocation = ?
    WHERE id = ?
  `;
  const updateValues = [
    university, universityType, faculty, department, courseName, category,
    level, duration, startDate, specification, careerOpportunities, industry,
    scholarships, courseFee, cologo, codescription, instituteAddress,
    institutePhone, instituteEmail, instituteLocation, req.params.id
  ];

  db.query(updateExternalDegree, updateValues, (err, result) => {
    if (err) {
      console.error('Error updating data:', err);
      return res.status(500).send('Server error');
    }
    res.json({ message: 'External degree updated successfully' });
  });
});

// Get all approved and non-deleted external_degrees with optional filters
router.get('/all', (req, res) => {
  const { universityType, category, level, institute_id } = req.query;
  let query = 'SELECT * FROM external_degrees WHERE deleted = FALSE AND approved = TRUE';
  const values = [];

  if (universityType) {
    query += ' AND universityType = ?';
    values.push(universityType);
  }

  if (category) {  
    query += ' AND category = ?';
    values.push(category);
  }

  if (level) {
    query += ' AND level = ?';
    values.push(level);
  }

  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 external_degrees
router.get('/deleted', (req, res) => {
  const { institute_id } = req.query;
  let query = 'SELECT * FROM external_degrees 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 external degree to deleted state
router.post('/delete/:id', (req, res) => {
  const query = 'UPDATE external_degrees 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: 'External degree moved to deleted state' });
  });
});

// Get all non-deleted and non-approved external_degrees
router.get('/pending', (req, res) => {
  const { institute_id } = req.query;
  let query = 'SELECT * FROM external_degrees 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 external degree from created state
router.post('/approve/:id', (req, res) => {
  const query = 'UPDATE external_degrees 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: 'External degree approved' });
  });
});

// Restore external degree from deleted state
router.post('/restore/:id', (req, res) => {
  const query = 'UPDATE external_degrees 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: 'External degree restored successfully' });
  });
});

// Permanently delete external degree
router.delete('/delete/permanent/:id', (req, res) => {
  const query = 'DELETE FROM external_degrees 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: 'External degree permanently deleted' });
  });
});

// Get external degree by external degree ID
router.get('/:id', (req, res) => {
  const id = req.params.id;
  db.query('SELECT * FROM external_degrees 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;
