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 uploads
const storage = multer.diskStorage({
    destination: (req, file, cb) => {
      cb(null, 'Upload/pdf'); 
    },
    filename: (req, file, cb) => {
      cb(null, file.fieldname + '_' + Date.now() + path.extname(file.originalname));
    }
  });
  

const upload = multer({ 
  storage: storage,
  fileFilter: (req, file, cb) => {
    // Validate file type
    if (file.mimetype !== 'application/pdf') {
      return cb(new Error('Only PDF files are allowed'), false);
    }
    cb(null, true);
  }
});

// Create a new application
router.post('/create', upload.single('application'), (req, res) => {
  const {
    id,
    appName,
    ownerName,
    ownerEmail,
    contact,
    link,
    description,
    expire_time
  } = req.body;

  if (!id || !appName || !ownerName || !ownerEmail || !contact || !description) {
    return res.status(400).json({ message: "Please fill in all required fields" });
  }

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

  const saveApplication = `
    INSERT INTO applications (
      id, appName, ownerName, ownerEmail, contact, link, application, description, created_time, expire_time, deleted, approved
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, NOW(), ?, FALSE, FALSE)
  `;
  const saveValues = [
    id, appName, ownerName, ownerEmail, contact, link, application, description, expire_time
  ];

  db.query(saveApplication, saveValues, (err, result) => {
    if (err) {
      console.error('Error inserting application:', err);
      return res.status(500).json({ message: 'Server error while inserting application' });
    }
    res.status(201).json({ message: 'Application created successfully' });
  });
});

// Update application details
router.post('/update/:id', upload.single('application'), (req, res) => {
  const {
    appName,
    ownerName,
    ownerEmail,
    contact,
    link,
    description,
    expire_time
  } = req.body;

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

  const updateApplication = `
    UPDATE applications SET
      appName = ?, ownerName = ?, ownerEmail = ?, contact = ?, link = ?, description = ?, application = ?, expire_time = ?
    WHERE id = ?
  `;
  const updateValues = [
    appName, ownerName, ownerEmail, contact, link, description, application, expire_time, req.params.id
  ];

  db.query(updateApplication, updateValues, (err, result) => {
    if (err) {
      console.error('Error updating application:', err);
      return res.status(500).json({ message: 'Server error while updating application' });
    }
    res.json({ message: 'Application updated successfully' });
  });
});

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

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

  db.query(query, values, (err, results) => {
    if (err) {
      console.error('Error fetching applications:', err);
      return res.status(500).json({ message: 'Server error while fetching applications' });
    }
    res.json(results);
  });
});

// Get all deleted applications
router.get('/deleted', (req, res) => {
  const { id } = req.query;
  let query = 'SELECT * FROM applications WHERE deleted = TRUE';
  const values = [];

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

  db.query(query, values, (err, results) => {
    if (err) {
      console.error('Error fetching deleted applications:', err);
      return res.status(500).json({ message: 'Server error while fetching deleted applications' });
    }
    res.json(results);
  });
});

// Move application to deleted state
router.post('/delete/:id', (req, res) => {
  const query = 'UPDATE applications SET deleted = TRUE WHERE id = ?';
  const values = [req.params.id];

  db.query(query, values, (err, result) => {
    if (err) {
      console.error('Error deleting application:', err);
      return res.status(500).json({ message: 'Server error while deleting application' });
    }
    res.json({ message: 'Application moved to deleted state' });
  });
});

// Get all non-deleted and non-approved applications
router.get('/pending', (req, res) => {
  const { id } = req.query;
  let query = 'SELECT * FROM applications WHERE deleted = FALSE AND approved = FALSE';
  const values = [];

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

  db.query(query, values, (err, results) => {
    if (err) {
      console.error('Error fetching pending applications:', err);
      return res.status(500).json({ message: 'Server error while fetching pending applications' });
    }
    res.json(results);
  });
});

// Approve application
router.post('/approve/:id', (req, res) => {
  const query = 'UPDATE applications SET approved = TRUE WHERE id = ?';
  const values = [req.params.id];

  db.query(query, values, (err, result) => {
    if (err) {
      console.error('Error approving application:', err);
      return res.status(500).json({ message: 'Server error while approving application' });
    }
    res.json({ message: 'Application approved' });
  });
});

// Restore application from deleted state
router.post('/restore/:id', (req, res) => {
  const query = 'UPDATE applications SET deleted = FALSE WHERE id = ?';
  const values = [req.params.id];

  db.query(query, values, (err, result) => {
    if (err) {
      console.error('Error restoring application:', err);
      return res.status(500).json({ message: 'Server error while restoring application' });
    }
    res.json({ message: 'Application restored successfully' });
  });
});

// Permanently delete application
router.delete('/delete/permanent/:id', (req, res) => {
  const query = 'DELETE FROM applications WHERE id = ?';
  const values = [req.params.id];

  db.query(query, values, (err, result) => {
    if (err) {
      console.error('Error permanently deleting application:', err);
      return res.status(500).json({ message: 'Server error while permanently deleting application' });
    }
    res.json({ message: 'Application permanently deleted' });
  });
});

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

module.exports = router;
