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

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

const upload = multer({ storage: storage });

// Create new student loan
router.post('/create', upload.single('logo'), (req, res) => {
    const {
        university,
        faculty,
        department,
        degreeName,
        category,
        level,
        duration,
        startDate,
        specification,
        careerOpportunities,
        industry,
        description,
        instituteAddress,
        institutePhone,
        instituteEmail,
        instituteLocation,
        institute_id
    } = req.body;

    // Ensure all required fields are provided
    if (!university || !faculty || !department || !degreeName || !category || !level || !duration ||
        !startDate || !specification || !careerOpportunities || !industry || !description ||
        !instituteAddress || !institutePhone || !instituteEmail || !instituteLocation || !institute_id) {
        return res.status(400).json({ message: "Please fill in all required fields" });
    }

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

    // SQL query to insert a new student loan
    const saveLoan = `
        INSERT INTO stdLoans (
            university, faculty, department, degreeName, category, level, duration, startDate, specification,
            careerOpportunities, industry, description, instituteAddress, institutePhone, instituteEmail,
            instituteLocation, logo, institute_id, deleted, approved
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, FALSE, FALSE)
    `;

    const saveValues = [
        university, faculty, department, degreeName, category, level, duration, startDate, specification,
        careerOpportunities, industry, description, instituteAddress, institutePhone, instituteEmail,
        instituteLocation, logo, institute_id
    ];

    db.query(saveLoan, saveValues, (err, result) => {
        if (err) {
            console.error('Error saving student loan:', err);
            return res.status(500).json({ message: 'Server error' });
        }
        res.status(200).json({ message: 'Student loan added successfully' });
    });
});

// Update student loan details
router.post('/update/:id', upload.single('logo'), (req, res) => {
    const {
        university, faculty, department, degreeName, category, level, duration, startDate, specification,
        careerOpportunities, industry, description, instituteAddress, institutePhone, instituteEmail,
        instituteLocation, institute_id
    } = req.body;

    const logo = req.file ? req.file.filename : null;
    const loanId = req.params.id;

    // SQL query to update student loan details
    const updateLoan = `
        UPDATE stdLoans SET
            university = ?, faculty = ?, department = ?, degreeName = ?, category = ?, level = ?, duration = ?,
            startDate = ?, specification = ?, careerOpportunities = ?, industry = ?, description = ?,
            instituteAddress = ?, institutePhone = ?, instituteEmail = ?, instituteLocation = ?, 
            logo = COALESCE(?, logo), institute_id = ?
        WHERE id = ?
    `;

    const updateValues = [
        university, faculty, department, degreeName, category, level, duration, startDate, specification,
        careerOpportunities, industry, description, instituteAddress, institutePhone, instituteEmail,
        instituteLocation, logo, institute_id, loanId
    ];

    db.query(updateLoan, updateValues, (err, result) => {
        if (err) {
            console.error('Error updating student loan:', err);
            return res.status(500).json({ message: 'Server error' });
        }
        res.status(200).json({ message: 'Student loan updated successfully' });
    });
});

// Get all approved and non-deleted stdLoans with optional filters
router.get('/all', (req, res) => {
    const {category, level, institute_id } = req.query;
    let query = 'SELECT * FROM stdLoans WHERE deleted = FALSE AND approved = TRUE';
    const values = [];
  
    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 stdLoans
  router.get('/deleted', (req, res) => {
    const { institute_id } = req.query;
    let query = 'SELECT * FROM stdLoans 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 stdLoans to deleted state
  router.post('/delete/:id', (req, res) => {
    const query = 'UPDATE stdLoans 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: ' Student Loan moved to deleted state' });
    });
  });
  
  // Get all non-deleted and non-approved stdLoans
  router.get('/pending', (req, res) => {
    const { institute_id } = req.query;
    let query = 'SELECT * FROM stdLoans 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 stdLoans from created state
  router.post('/approve/:id', (req, res) => {
    const query = 'UPDATE stdLoans 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: 'Student Loan approved' });
    });
  });
  
  // Restore stdLoans from deleted state
  router.post('/restore/:id', (req, res) => {
    const query = 'UPDATE stdLoans 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: 'Student Loan restored successfully' });
    });
  });
  
  // Permanently delete stdLoans
  router.delete('/delete/permanent/:id', (req, res) => {
    const query = 'DELETE FROM stdLoans 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: 'Student Loan permanently deleted' });
    });
  });
  
  // Get stdLoans by stdLoans ID
  router.get('/:id', (req, res) => {
    const id = req.params.id;
    db.query('SELECT * FROM stdLoans 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;
