const express = require('express');
const router = express.Router();
const db = require('../database/db');
const multer = require('multer');
const path = require('path');
const { Console } = require('console');
const bcrypt = require('bcrypt');


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

});

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


//save data to database
router.post('/register', upload.single('img'), (req, res) => {

    let img = req.file;
    const { id, in_name, website, email, tel, address, desc, approve, password, token, deleted, date, ireg, pname, pconatct, country, type  } = req.body;


    //check values are null
    if (!id || !in_name || !website || !email || !tel || !address || !img || !desc || !approve || !password || !token || !deleted || !date || !ireg || !pname || !pconatct || !country || !type) {
        return res.status(400).send('Please fill all fields');
    }

    //hash password
    const salt = bcrypt.genSaltSync(10);
    const hash = bcrypt.hashSync(password, salt);

    console.log(hash);

    // Check if the institute already exists
    const checkQuary = 'SELECT * FROM institute WHERE email = ? OR in_name = ?';
    const checkValues = [email, in_name];

    db.query(checkQuary, checkValues, (err, results) => {
        if (err) {
            console.error('Error checking existing data:', err);
            return res.status(500).send('Server error');
        }

        if (results.length > 0) {
            return res.status(400).send('Institute already registered');
        }

        // Insert new institute if not already registered
        img = req.file.filename;
        const saveData = 'INSERT INTO institute (id, in_name, website, email, tel, address, img, desc_, approve, password, token, deleted, date, ireg, pname, pconatct, country, type , password_on	) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? , ? , ?, ?)';
        const saveValues = [id, in_name, website, email, tel, address, img, desc, approve, hash, token, deleted, date, ireg, pname, pconatct, country, type, password];

        db.query(saveData, saveValues, (err, result) => {
            if (err) {
                console.error('Error inserting data:', err);
                return res.status(500).send('Server error');
            }
            res.json({ message: 'Institute registered successfully' });
        });
    });


});


//get all institutes
router.get('/all', (req, res) => {
    const query = 'SELECT * FROM institute';
    db.query(query, (err, results) => {
        if (err) {
            console.error('Error getting data:', err);
            return res.status(500).send('Server error');
        }
        res.json(results);
    });
})

//get institute by id
router.get('/one/:id', (req, res) => {
    const query = 'SELECT * FROM institute WHERE id = ?';
    const values = [req.params.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);
    });
})


//update institute by id
router.put('/update/:id', upload.single('img'), (req, res) => {
    const { in_name, website, email, tel, address, desc, token } = req.body;

    let img = req.file;

    //check values are null
    if (!in_name || !website || !email || !tel || !address || !desc || !token) {
        return res.status(400).send('Please fill all fields');
    }

    if (img == undefined) {
       
        const isaveData = 'UPDATE institute SET in_name = ?, website = ?, email = ?, tel = ?, address = ?, desc_ = ?, token = ?  WHERE id = ?';
        const isaveValues = [in_name, website, email, tel, address, desc, token, req.params.id];

        db.query(isaveData, isaveValues, (err, result) => {
            if (err) {
                console.error('Error inserting data:', err);
                return res.status(500).send('Server error');
            }
            res.json({ message: 'Institute updated successfully' });
        })


    } else {
        img = req.file.filename;

        const saveData = 'UPDATE institute SET in_name = ?, website = ?, email = ?, tel = ?, address = ?, img = ?, desc_ = ? , token = ?  WHERE id = ?';
        const saveValues = [in_name, website, email, tel, address, img, desc, token, req.params.id];

        db.query(saveData, saveValues, (err, result) => {
            if (err) {
                console.error('Error inserting data:', err);
                return res.status(500).send('Server error');
            }
            res.json({ message: 'Institute updated successfully' });
        });
    }
    // 


});



//update approve
router.put('/approve/:id', (req, res) => {
    const query = 'UPDATE institute SET approve = ? WHERE id = ?';
    const values = [req.body.approve, req.params.id];
    db.query(query, values, (err, results) => {
        if (err) {
            console.error('Error updating data:', err);
            return res.status(500).send('Server error');
        }
        res.json({ message: 'Institute approved successfully' });
    });
})

//update deleted
router.put('/delete/:id', (req, res) => {
    const query = 'UPDATE institute SET deleted = ? , approve = ? WHERE id = ?';
    const values = [req.body.deleted, "pending", req.params.id];
    db.query(query, values, (err, results) => {
        if (err) {
            console.error('Error updating data:', err);
            return res.status(500).send('Server error');
        }
        res.json({ message: 'Institute deleted successfully' });
    });
})


//get only approve data
router.get('/approve', (req, res) => {
    const query = 'SELECT * FROM institute WHERE approve = "approve" and deleted = "false"';
    db.query(query, (err, results) => {
        if (err) {
            console.error('Error getting data:', err);
            return res.status(500).send('Server error');
        }
        res.json(results);
    });
})

//get not deleted and pending data data
router.get('/delete', (req, res) => {
    const query = 'SELECT * FROM institute WHERE deleted = "false" and approve = "pending"';
    db.query(query, (err, results) => {
        if (err) {
            console.error('Error getting data:', err);
            return res.status(500).send('Server error');
        }
        res.json(results);
    });
})


//get deleted and pending data
router.get('/deleted', (req, res) => {
    const query = 'SELECT * FROM institute WHERE deleted = "true" and approve = "pending"';
    db.query(query, (err, results) => {
        if (err) {
            console.error('Error getting data:', err);
            return res.status(500).send('Server error');
        }
        res.json(results);
    });
})

//get course data for institute pages
router.get('/course/:id', (req, res) => {
    const query = 'SELECT * FROM courses WHERE institute_id = ?';
    const values = [req.params.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 only private institute data in sri lanka
router.get('/privatesl', (req, res) => {
    const query = 'SELECT * FROM institute WHERE type = "private" and country = "Sri Lanka (ශ්‍රී ලංකාව)" and deleted = "false" and approve = "approve"';
    db.query(query, (err, results) => {
        if (err) {
            console.error('Error getting data:', err);
            return res.status(500).send('Server error');
        }
        res.json(results);
    });
})


//get only private goverment data in sri lanka
router.get('/govermentsl', (req, res) => {
    const query = 'SELECT * FROM institute WHERE type = "government" and country = "Sri Lanka (ශ්‍රී ලංකාව)" and deleted = "false" and approve = "approve"';
    db.query(query, (err, results) => {
        if (err) {
            console.error('Error getting data:', err);
            return res.status(500).send('Server error');
        }
        res.json(results);
    });
})


//getAll forgin institute
router.get('/foreign', (req, res) => {
    const query = 'SELECT * FROM institute WHERE country != "Sri Lanka (ශ්‍රී ලංකාව)" and deleted = "false" and approve = "approve"';
    db.query(query, (err, results) => {
        if (err) {
            console.error('Error getting data:', err);
            return res.status(500).send('Server error');
        }
        res.json(results);
    });
})



//update firstlog 
router.post('/firstLogUpdate/:id', (req, res) => {

    const { firstlog } = req.body;

    const query = 'UPDATE institute SET firstlog = ? WHERE id = ?';
    const values = [firstlog, req.params.id];

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



});

router.post('/deletenow/:id', (req, res) => {

    const id = req.params.id;
    const query = 'DELETE FROM institute WHERE id = ?';
    db.query(query, id, (err, result) => {
        if (err) {
            res.status(500).send({ message: err.message });
        } else {
            res.json({ message: 'Institute deleted successfully' });
        }
    });

});


module.exports = router;