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

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

// Create a new student loan
router.post('/create', upload.single('logo'), (req, res) => {
    const {
        bankName,
        stdLoanName,
        description,
        phone,
        address,
        email,
        location,
    } = req.body;

    if (!bankName || !stdLoanName || !description || !phone || !address || !email || !location) {
        return res.status(400).json({ message: 'Please fill in all required fields' });
    }

    // Check if a loan with the same bankName and stdLoanName already exists
    const checkQuery = `
        SELECT * FROM bankStdLoan WHERE bankName = ? AND stdLoanName = ?
    `;
    db.query(checkQuery, [bankName, stdLoanName], (err, results) => {
        if (err) {
            console.error('Error checking existing loan:', err);
            return res.status(500).json({ message: 'Server error' });
        }

        if (results.length > 0) {
            return res.status(400).json({ message: 'A loan with this name already exists for this bank' });
        }

        // Proceed with the insertion if no existing record is found
        const logo = req.file ? req.file.filename : null;

        const query = `
            INSERT INTO bankStdLoan (
                bankName, stdLoanName, description, phone, address, email,
                location, logo, deleted, approved
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, FALSE, FALSE)
        `;
        const values = [bankName, stdLoanName, description, phone, address, email, location, logo];

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

// Update loan details
router.post('/update/:id', upload.single('logo'), (req, res) => {
    const {
        bankName, stdLoanName, description, phone, address, email, location
    } = req.body;

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

    const updateLoan = `
        UPDATE bankStdLoan SET
        bankName = ?, stdLoanName = ?, description = ?, phone = ?, address = ?,
        email = ?, location = ?, logo = ?
        WHERE id = ?
    `;
    const updateValues = [
        bankName, stdLoanName, description, phone, address,
        email, location, logo, req.params.id
    ];

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

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

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

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

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

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

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

// Move loan to deleted state
router.post('/delete/:id', (req, res) => {
    const query = 'UPDATE bankStdLoan 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).json({ message: 'Server error' });
        }
        res.json({ message: 'Loan moved to deleted state' });
    });
});

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

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

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

// Approve loan
router.post('/approve/:id', (req, res) => {
    const query = 'UPDATE bankStdLoan 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).json({ message: 'Server error' });
        }
        res.json({ message: 'Loan approved' });
    });
});

// Restore loan from deleted state
router.post('/restore/:id', (req, res) => {
    const query = 'UPDATE bankStdLoan 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).json({ message: 'Server error' });
        }
        res.json({ message: 'Loan restored successfully' });
    });
});

// Permanently delete loan
router.delete('/delete/permanent/:id', (req, res) => {
    const query = 'DELETE FROM bankStdLoan 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).json({ message: 'Server error' });
        }
        res.json({ message: 'Loan permanently deleted' });
    });
});

// Get loan by ID
router.get('/:id', (req, res) => {
    const id = req.params.id;
    db.query('SELECT * FROM bankStdLoan 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: 'Loan not found' });
        }
        res.json(results[0]);
    });
});

module.exports = router;
