var express = require('express');
var router = express.Router();
var db = require('../views/db')
const multer = require('multer');
const xlsx = require('xlsx');


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

router.get('/upload', upload.single('excelFile') ,async (req, res, next) => {
    try {

        const fileBuffer = req.file.buffer;
        const workbook = xlsx.read(fileBuffer, { type: 'buffer' });
        const sheetName = workbook.SheetNames[0];
        const sheet = workbook.Sheets[sheetName];
        const data = xlsx.utils.sheet_to_json(sheet, { header: 1 });

        const columns = data[0];

        data.shift();

        const tableName = 'test';
        const columnsString = columns.join(', ');
        const placeholders = columns.map(() => '?').join(', ');
        const query = `INSERT INTO ${tableName} (${columnsString}) VALUES (${placeholders})`;

         data.forEach(row => {
            db.query(query, row, (err, results) => {
                console.log(row);
            if (err) {
                console.error('Error inserting data: ' + err);
            } else {
                console.log('Data inserted successfully.');
            }
            });
        });

        res.json({ message: 'Data insertion process initiated.' });

    } catch (error) {
        if(error) throw error;
    }
});



router.post('/LogIn',async function(req, res, next) {

    try {
        const {
            Email,
            password
        } = req.body
        const getUserData = "select * from test where Email = '"+Email+"'";
        db.query(getUserData,function(err, result){
            if(err) throw err;
            console.log(result);
            if(result[0]?.Email === Email){
                if(result[0]?.password === password){
                    res.status(200).json({
                        status:"Login successful"
                    })
                }else{
                    res.status(200).json({
                        status:"Invalid password"
                    })
                }
            }else{
                res.status(200).json({
                    status:"Invalid email id"
                })
            }
        })
        
    } catch (error) {
        if(error) throw error;
    }
});

module.exports = router;