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 study abroad course data to database
router.post('/create', upload.single('cologo'), (req, res) => {
    const {
      country,
      university,
      courseName,
      level,
      duration,
      startDate,
      category,
      specification,
      courseFee, 
      careerOpportunities,
      industry,
      codescription,
      instituteName,
      instituteAddress,
      institutePhone,
      instituteEmail,
      instituteLocation,
      institute_id
    } = req.body;
  
    // Check for required fields
    if (!country || !university || !courseName || !level || !duration || !startDate) {
      return res.status(400).json({ message: "Please fill in all required fields" });
    }
  
    // Check if the course already exists
    const checkCourseQuery = 'SELECT * FROM studyabroad WHERE university = ? AND courseName = ?';
    db.query(checkCourseQuery, [university, courseName], (err, results) => {
      if (err) {
        console.error('Error checking data:', err);
        return res.status(500).send('Server error');
      }
  
      if (results.length > 0) {
        return res.status(400).json({ message: 'Course already exists' });
      }
  
      // Insert new course
      const cologo = req.file ? req.file.filename : null;
      const saveCourse = `
        INSERT INTO studyabroad (
          country, university, courseName, level, duration, startDate, category, specification, courseFee,
          careerOpportunities, industry, cologo, codescription, instituteName, instituteAddress, institutePhone,
          instituteEmail, instituteLocation, institute_id, deleted, approved
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, FALSE, FALSE)
      `;
      const saveValues = [
        country, university, courseName, level, duration, startDate, category, specification, courseFee, 
        careerOpportunities, industry, cologo, codescription, instituteName, instituteAddress, institutePhone,
        instituteEmail, instituteLocation, institute_id
      ];
  
      db.query(saveCourse, saveValues, (err, result) => {
        if (err) {
          console.error('Error inserting data:', err);
          return res.status(500).send('Server error');
        }
        res.json({ message: 'Study abroad course created successfully' });
      });
    });
  });
  

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

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

  const updateCourse = `
    UPDATE studyabroad SET
    country = ?, university = ?, courseName = ?, level = ?, duration = ?, startDate = ?, category = ?, specification = ?, courseFee = ?, careerOpportunities = ?, industry = ?, cologo = ?, codescription = ?, instituteName = ?, instituteAddress = ?, institutePhone = ?, instituteEmail = ?, instituteLocation = ?
    WHERE id = ?
  `;
  const updateValues = [
    country, university, courseName, level, duration, startDate,
    category, specification, courseFee, careerOpportunities, industry, cologo,
    codescription, instituteName, instituteAddress, institutePhone, instituteEmail, instituteLocation, req.params.id
  ];

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

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

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

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

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

  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 study abroad courses
router.get('/deleted', (req, res) => {
  const query = 'SELECT * FROM studyabroad WHERE deleted = TRUE';
  db.query(query, (err, results) => {
    if (err) {
      console.error('Error getting data:', err);
      return res.status(500).send('Server error');
    }
    res.json(results);
  });
});

// Move study abroad course to deleted state
router.post('/delete/:id', (req, res) => {
  const query = 'UPDATE studyabroad 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: 'Study abroad course moved to deleted state' });
  });
});

// Get all non-deleted and non-approved study abroad courses
router.get('/pending', (req, res) => {
  const query = 'SELECT * FROM studyabroad WHERE deleted = FALSE AND approved = FALSE';
  db.query(query, (err, results) => {
    if (err) {
      console.error('Error getting data:', err);
      return res.status(500).send('Server error');
    }
    res.json(results);
  });
});

// Approve study abroad course
router.post('/approve/:id', (req, res) => {
  const query = 'UPDATE studyabroad 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: 'Study abroad course approved' });
  });
});

// Restore study abroad course from deleted state
router.post('/restore/:id', (req, res) => {
  const query = 'UPDATE studyabroad 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: 'Study abroad course restored successfully' });
  });
});

// Permanently delete study abroad course
router.delete('/delete/permanent/:id', (req, res) => {
  const query = 'DELETE FROM studyabroad 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: 'Study abroad course permanently deleted' });
  });
});

// Get study abroad course by ID
router.get('/:id', (req, res) => {
  const id = req.params.id;
  db.query('SELECT * FROM studyabroad WHERE id = ?', [id], (error, results) => {
    if (error) {
      return res.status(500).json({ error: error.message });
    }
    if (results.length === 0) {
      return res.status(404).json({ message: 'Study abroad course not found' });
    }
    res.json(results[0]);
  });
});

module.exports = router;
