const express = require('express');
const router = express.Router();
const employeeController = require('../controllers/employeeController');
const vacationController = require('../controllers/vacationController');
const functionController = require('../controllers/functionController');
const authentification = require('../middlewares/authentification');
const db = require('../models');
const multer = require('multer');
const fileController = require('../controllers/fileController');
const { body } = require('express-validator');
const isSuperAdmin = require('../middlewares/isSuperAdmin');
const path = require('path');
const excel = require('exceljs');
const moment = require('moment');
const fs = require('fs')

const storage = multer.diskStorage({
  destination: function (req, file, cb) {
    cb(null, 'public/files/');
  },
  filename: function (req, file, cb) {
    cb(null, file.originalname);
  }
});

const upload = multer({ storage: storage, preservePath: true });
const uploads = multer({ storage: storage });
  


router.get( '/', authentification,async ( req, res, next ) => {
    let msg = null;
    var data = {
        employees: [],
        fonctions: [],
        vacation: []
    };      
    
    // Fonction de l'employee
    data.fonctions = await functionController.findAll();
    let employees = await employeeController.getAllEmployee();  
    for (let i = 0; i < employees.length; i++) {
        let employee = employees[i];             
        let employeeFunct = await db.employeeFunction.findOne({
            where: { employeeId: employee.id },
            order: [['date', 'DESC'],['createdAt','DESC']]
        });
        
        if (employeeFunct) {
            let fonction = await db.function.findOne({
                where: { id: employeeFunct.functionId }
            });
            if(fonction) {
                employee.fonction = fonction.id;
            }
            employee.dateFunct = employeeFunct.date;
        } else {
            // Pas de fonction
            employee.fonction = null;
        }            
        data.employees.push(employee);
    }
    //  Fin fonction 

    //  Congee 
    for (let i = 0; i < data.employees.length; i++) {
        let employee = data.employees[i];
        let vacation = await vacationController.findLatestVacation(employee.id);                
        data.employees[i].vacation = vacation;        
    }
    //  Fin congee        
            
    res.render('pages/liste/liste.employee.ejs',{
        data: data,
        req,
        msg
    })
})

router.get('/newEmpl', authentification, async ( req, res ) => {
    // Récupérer les données des fonctions depuis la base de donne
    const fonctions = await functionController.findAll();  
    
    const admins = await db.admin.findAll();
    res.render('pages/forms/employee', { admins ,fonctions , req });
})


router.post(
    '/save',
    authentification,
    employeeController.dataToValidateRegistration,
    uploads.fields([
        { name: 'photo', maxCount: 1 },
        { name: 'cv', maxCount: 1 },
    ]),
    async (req, res ) => {
        let employee = await employeeController.createEmployee(req, res);        
        res.redirect('/employee/')
    }
);


//  Importer les conges employee par excel
router.post('/import-excel', 
    authentification,
    upload.single('file'),
    body('file')
    .custom((value, { req }) => {
      if (!req.file) {
        throw new Error('Veuillez sélectionner un fichier.');
      }
      // Vérifier si le fichier est un fichier Excel (XLSX)
      if (req.file.mimetype !== 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') {
        throw new Error('Veuillez sélectionner un fichier Excel (XLSX).');
      }
      return true
    }),
    employeeController.importEmployeeExcel
);

router.post('/export', authentification, async (req, res) => {    
    try {
      const employees = await db.employee.findAll(); // Replace 'Employee' with your Sequelize model
      
        if (!req.body.startDate) {
            return res.redirect("/employee/");
        }

        const DATE = new Date(req.body.startDate);
        // const MONTH = DATE.getMonth();

        // Create a new Excel workbook and worksheet
        const workbook = new excel.Workbook();
        const worksheet = workbook.addWorksheet('Employees');

        // Add header row
        worksheet.addRow([
            'ID',
            'Matricule', 
            'Nom et prenoms', 
            'Fonction', 
            'Date d\'embauche', 
            'Congé M-1',
            'Congé M',
            'Récuperation',
            'Repos maladie',
            'Assistance maternelle',
            'Permission',
            'Congé pris',
            'Reste'
        ]);

        // Add data rows
        for(let i = 0; i < employees.length; i++){
            let fonction = await functionController.getFunctionEmployee(employees[i].id);
            let vacM = await vacationController.findAllVacationMonth(employees[i].id, DATE);
            // return res.send(vacM);
            let rest = await vacationController.findLatestVacationOnDate(employees[i].id, DATE);
            let tab;
            let congePris;
            let recup;
            if(vacM[0] && rest && vacM[0].totalVacation !== rest.totalVacation ) {
                if(vacM[0].totalVacation - rest.totalVacation >= 0) {
                    // recup = rest.totalVacation - vacM[0].totalVacation;
                    congePris = Math.round(vacM[0].totalVacation - rest.totalVacation);            
                }
                // else {
                // }
            }
            tab = [
                employees[i].id, 
                employees[i].matricule,
                employees[i].name_employee + ' ' + employees[i].last_name_employee,
                fonction ? fonction.nameFunction : '',
                employees[i].hiring_date,
                vacM[0] ? vacM[0].totalVacation - 2.5 : '',
                vacM[0] ? vacM[0].totalVacation : '',
                recup ?? '',
                '',
                '',
                '',
                congePris ?? '', 
                rest ? rest.totalVacation : ''
            ];        
            console.log(tab);
            worksheet.addRow(tab);
        };        
        // Set column widths (optional)
        worksheet.getColumn(1).width = 5;
        worksheet.getColumn(2).width = 5;
        worksheet.getColumn(3).width = 50;
        worksheet.getColumn(4).width = 40;
        worksheet.getColumn(5).width = 15;
        worksheet.getColumn(6).width = 10;
        worksheet.getColumn(7).width = 10;
        worksheet.getColumn(8).width = 10;    
        
        // Centrer les données des colonnes restantes
        worksheet.columns.forEach((column, index) => {
            if (index !== 2) {
                column.alignment = { horizontal: 'center' };
            } else {
                column.alignment = { horizontal: 'left' };
            }
        });

        // Aligner la première ligne à gauche
        // const firstRow = worksheet.getRow(1);
        //     firstRow.eachCell({ includeEmpty: true }, cell => {
        //     cell.alignment = { horizontal: 'left' };
        // });

        // Generate a unique filename for the Excel file
        const filename = `employees_${Date.now()}.xlsx`;

        // Save the Excel file to a local directory
        const filePath = path.join(__dirname, '../public/export/', filename);
        await workbook.xlsx.writeFile(filePath);     

        // Stream the file to the response
        res.download(filePath, () => {
            // After the download is complete, delete the file from the server
            console.log('Telechargement...')
            fs.unlinkSync(filePath);        
        });

    } catch (error) {
        console.error(error);
        res.status(500).send('An error occurred');
    }
});

//  Importer employee par excel
router.post('/information-excel', 
    authentification,
    upload.single('file2'),
    body('file2')
    .custom((value, { req }) => {
      if (!req.file) {
        throw new Error('Veuillez sélectionner un fichier.');
      }
      // Vérifier si le fichier est un fichier Excel (XLSX)
      if (req.file.mimetype !== 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') {
        throw new Error('Veuillez sélectionner un fichier Excel (XLSX).');
      }
      return true
    }),
    employeeController.informationImport
);
//  PREND UN SEUL EMPLOYEE PAR ID
router.get('/:id(\\d+)', authentification, async (req, res) => {
    let employee = await employeeController.getOneEmployee(req.params.id);    
    const fonctions = await functionController.findAll();
    if(employee) {

        //  Enfant employee
        if( employee.nbr_child && employee.nbr_child > 0) {
            var childs = await db.childEmployee.findAll({
                where : { employeeId: employee.id },
                order: [
                    ['createdAt', 'DESC']
                ]
            });
            employee.childs = childs;
        }
        else {
            employee.childs = [];
        }
        //  Fin enfant
        
        //  Fonction employee        
        let employeeFunct = await db.employeeFunction.findOne({
            // limit: 1,
            where: { employeeId: employee.id },
            order: [['date', 'DESC'],['createdAt', 'DESC']]
        });        

        if(employeeFunct) {
            let fonction = await db.function.findOne({
                where: { id: employeeFunct.functionId}
            })  
            console.log(employeeFunct.functionId);
            if(fonction) {
                employee.fonction = fonction.id;
            }      
            employee.dateFunct = employeeFunct.date
        }
        else {
            //Pas de fonction
            employee.fonction = null
        }
        //  Fin fonction
        
        //  CV employee 
        if(employee) {
            var cv = await fileController.getCvByEmployeeId(employee.id);        
            var photo = await fileController.getPhotoByEmployeeId(employee.id);
            if(cv) {            
                cv.path = cv.path.replace('public/','../../');                
            }
            else {
                cv = null
            }            
            if(photo) {            
                photo.path = photo.path.replace('public/','/');                
            }
            else {
                photo = null
            }
        }
        //  Fin CV

        // TROUVER LEUR ROLE         
        let post = await db.admin.findOne({ where: { matriculeEmployee: employee.matricule }});        
        if(!post) {                  
            employee.post = {
                role: ''   
            }
        }                
        else {
            employee.post = post;
        }        
        console.log(employee.AdmnistratorId);
        if(employee.AdmnistratorId) {
            let admin = await db.admin.findOne({ where: { id: employee.AdmnistratorId }});
            if(admin) {
                employee.admin = admin.id;
            }
            else {
                employee.admin = null;
            }
        }

        let employees = await db.employee.findAll();

        //  l'interimaire
        if( employee.nbr_interims && employee.nbr_interims > 0) {
            var interims = await db.interim.findAll({
                where : { employeeId: employee.id },                
            });
            for( let i = 0; i < interims.length; i++) {             
                let tmp = employees.filter((employee) => employee.id === interims[i].interimId );
                console.log(tmp);
                interims[i].nom = tmp[0].name_employee + ' ' + tmp[0].last_name_employee;
            };
            employee.interims = interims;
        }
        else {
            employee.interims = [];            
        }
                
        //  Trouver les admins
        let admins = await db.admin.findAll();
        res.render('pages/forms/employeeEdit', {
            req,
            admins,
            fonctions,
            cv,
            photo,
            employee,        
            employees, 
        })
    }
    else {
        res.redirect('/employee/')
    }
});


//  MISE A JOURS
router.post('/update', 
    authentification,
    uploads.fields([
        { name: 'photo', maxCount: 1 },
        { name: 'cv', maxCount:1 },
    ]),
    employeeController.dataToValidateRegistration, 
    async (req, res ) => {
        let employee = await employeeController.updateEmployee(req, res);        
        res.redirect('/employee/')
    }    
);

//  AFFICHER LE CV 
router.get('/cv/:id(\\d+)', authentification, async (req, res) => {
    let employee = await employeeController.getOneEmployee(req.params.id);    
    if(employee) {
        const cv = await fileController.getCvByEmployeeId(employee.id);        
        if(cv) {            
            cv.path = cv.path.replace('public/','../../');
            res.send(`<iframe src="${cv.path}" width="100%" height="100%" border="none" alt="CV" ></iframe>`)
        }
        else {
            res.redirect('/employee/');
        }
    }
    else {
        res.redirect('/employee/')
    }
})

//  SUPPRIMER UN ENFANT
router.delete('/child/:id(\\d+)', async (req, res) => {
    const childId = req.params.id;

    //  Verifier que l'enfant est dans la base de données
    const child = await db.childEmployee.findOne({where: {
        id: childId
    }});            
    // S'il n'est pas enregistrer
    if(!child) {        
        return res.status(404).json({message: 'Enfant introuvable'});
    }

    let parent = child.employeeId;        
    try {          
        //  Supprimer l'enfant
        await db.childEmployee.destroy({ where: { id: child.id }});

        // Trouver le parent à modifier
        var employee = await db.employee.findOne({ where: { id:  parent }});
        if(employee) {
            //  Réduire leur nombre d'enfant            
            employee.nbr_child -= 1;
            //  Appliquer la modification            
            await db.employee.update({ nbr_child: employee.nbr_child }, 
                { where:{id: employee.id }
            });                             
            res.json({ success: true });
        }
    }
    catch(err) { 
        console.log("msg"+err);
    }    
})

router.delete('/:id(\\d+)', authentification, isSuperAdmin, employeeController.deleteEmployee);
router.delete('/interim/:id(\\d+)', authentification, isSuperAdmin, employeeController.deleteInterim);



module.exports = router;