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

// Multer configuration for file uploads
const storage = multer.diskStorage({
  destination: (req, file, cb) => {
    cb(null, 'Upload/images/profExams');
  },
  filename: (req, file, cb) => {
    cb(null, Date.now() + '-' + file.originalname);
  },
});

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

// Create a new professional exam
router.post('/create', upload.single('logo'), (req, res) => {
  const {
    examName,
    shortName,
    description,
    tableRows,
    university,
    instituteAddress,
    institutePhone,
    instituteEmail,
    instituteWebsite,
    institute_id,
  } = req.body;

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

  // Validate required fields
  if (!examName || !shortName || !description || !institute_id) {
    return res.status(400).send('Please fill in all required fields.');
  }

  let parsedTableRows;

  try {
    parsedTableRows = JSON.parse(tableRows); // Parse the tableRows field
  } catch (error) {
    console.error('Error parsing tableRows:', error);
    return res.status(400).send('Invalid format for table rows.');
  }

  const examData = {
    institute_id,
    logo,
    examName,
    shortName,
    description,
    tableRows: JSON.stringify(parsedTableRows), 
    university,
    instituteAddress,
    institutePhone,
    instituteEmail,
    instituteWebsite,
  };

  const sql = 'INSERT INTO professional_exams SET ?';

  db.query(sql, examData, (err, result) => {
    if (err) {
      console.error('Error inserting data:', err);
      return res.status(500).send('An error occurred while adding the exam.');
    }

    res.status(200).send('Professional Exam added successfully.');
  });
});

// Update professional exam details
router.post('/update/:id', upload.single('logo'), (req, res) => {
  const {
    examName,
    shortName,
    description,
    tableRows,
    university,
    instituteAddress,
    institutePhone,
    instituteEmail,
    instituteWebsite,
    institute_id,
  } = req.body;

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

  let parsedTableRows;

  try {
    parsedTableRows = JSON.parse(tableRows); // Parse the tableRows field
  } catch (error) {
    console.error('Error parsing tableRows:', error);
    return res.status(400).send('Invalid format for table rows.');
  }

  const examData = {
    examName,
    shortName,
    description,
    tableRows: JSON.stringify(parsedTableRows),
    university,
    instituteAddress,
    institutePhone,
    instituteEmail,
    instituteWebsite,
    institute_id,
    logo: logo ? logo : undefined, 
  };

  const sql = 'UPDATE professional_exams SET ? WHERE id = ?';

  db.query(sql, [examData, examId], (err, result) => {
    if (err) {
      console.error('Error updating data:', err);
      return res.status(500).send('An error occurred while updating the exam.');
    }

    res.status(200).send('Professional Exam updated successfully.');
  });
});

// Get all approved and non-deleted professional exams
router.get('/all', (req, res) => {
  const { category, level, institute_id } = req.query;
  let query = 'SELECT * FROM professional_exams 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');
    }

    // Parse tableRows for each result
    results.forEach(exam => {
      if (exam.tableRows) {
        try {
          exam.tableRows = JSON.parse(exam.tableRows);
        } catch (err) {
          console.error('Error parsing tableRows:', err);
        }
      }
    });

    res.json(results);
  });
});


// Get all deleted professional exams
router.get('/deleted', (req, res) => {
  const { institute_id } = req.query;
  let query = 'SELECT * FROM professional_exams 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 professional exams to deleted state
router.post('/delete/:id', (req, res) => {
  const query = 'UPDATE professional_exams 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: 'Professional Exam moved to deleted state' });
  });
});

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

// Restore professional exams from deleted state
router.post('/restore/:id', (req, res) => {
  const query = 'UPDATE professional_exams 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: 'Professional Exam restored successfully' });
  });
});

// Permanently delete professional exams
router.delete('/delete/permanent/:id', (req, res) => {
  const query = 'DELETE FROM professional_exams 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: 'Professional Exam permanently deleted' });
  });
});

// Get Professional Exam by ID
router.get('/:id', (req, res) => {
  const id = req.params.id;
  db.query('SELECT * FROM professional_exams WHERE id = ?', [id], (error, result) => {
    if (error) {
      console.error('Error retrieving data:', error);
      return res.status(500).send('Server error');
    }

    // Parse tableRows if it exists
    const exam = result[0];
    if (exam && exam.tableRows) {
      try {
        exam.tableRows = JSON.parse(exam.tableRows);
      } catch (err) {
        console.error('Error parsing tableRows:', err);
        return res.status(500).send('Error parsing tableRows.');
      }
    }

    res.json(exam);
  });
});

module.exports = router;
