const express = require('express');
const router = express.Router();
const { query } = require('../config/database');
const moment = require('moment');

// Effectif total par état
router.get('/effectif', async (req, res) => {
  try {
    const sql = `
      SELECT 
        e.lib_etat as etat,
        COUNT(*) as nombre
      FROM etat_sal e
      JOIN salaries s ON e.num_etat = s.etat
      GROUP BY e.num_etat, e.lib_etat
    `;
    
    const results = await query('grh', sql);
    
    const total = results.reduce((sum, r) => sum + r.nombre, 0);
    const actifs = results.find(r => r.etat === 'Actif')?.nombre || 0;
    
    res.json({
      success: true,
      data: {
        total,
        actifs,
        details: results
      }
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      message: error.message
    });
  }
});

// Effectif par direction
router.get('/par-direction', async (req, res) => {
  try {
    const sql = `
      SELECT 
        d.Lib_Dir as direction,
        COUNT(*) as nombre_employes
      FROM directions d
      LEFT JOIN salaries s ON d.Num_Dir = s.direction
      GROUP BY d.Num_Dir, d.Lib_Dir
      ORDER BY nombre_employes DESC
    `;
    
    const results = await query('grh', sql);
    
    res.json({
      success: true,
      data: results
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      message: error.message
    });
  }
});

// Effectif par catégorie
router.get('/par-categorie', async (req, res) => {
  try {
    const sql = `
      SELECT 
        c.lib_cat as categorie,
        COUNT(*) as nombre
      FROM categories c
      LEFT JOIN salaries s ON c.code_cat = s.categorie
      GROUP BY c.code_cat, c.lib_cat
      ORDER BY nombre DESC
    `;
    
    const results = await query('grh', sql);
    
    res.json({
      success: true,
      data: results
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      message: error.message
    });
  }
});

// Pointages du jour
router.get('/pointages-jour', async (req, res) => {
  try {
    const date = req.query.date || moment().format('YYYY-MM-DD');
    
    const sql = `
      SELECT 
        p.matricule,
        p.user_name as nom_prenom,
        MIN(p.time) as heure_arrivee,
        MAX(p.time) as heure_depart,
        COUNT(*) as nombre_pointages
      FROM pointage p
      WHERE p.date = ?
      GROUP BY p.matricule, p.user_name
      ORDER BY heure_arrivee ASC
    `;
    
    const results = await query('grh', sql, [date]);
    
    res.json({
      success: true,
      data: {
        date,
        total_employes: results.length,
        pointages: results
      }
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      message: error.message
    });
  }
});

// Absences du jour
router.get('/absences-jour', async (req, res) => {
  try {
    const date = req.query.date || moment().format('YYYY-MM-DD');
    
    const sql = `
      SELECT 
        a.Lib_Absence as type_absence,
        COUNT(*) as nombre
      FROM absences a
      WHERE a.Type_Absence = 'D'
      GROUP BY a.Lib_Absence
    `;
    
    const results = await query('grh', sql);
    
    res.json({
      success: true,
      data: {
        date,
        absences: results
      }
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      message: error.message
    });
  }
});

// Taux de présence
router.get('/taux-presence', async (req, res) => {
  try {
    const { periode = 'week' } = req.query;
    let dateStart, dateEnd;
    
    const now = moment();
    switch(periode) {
      case 'day':
        dateStart = now.startOf('day');
        dateEnd = now.endOf('day');
        break;
      case 'week':
        dateStart = now.startOf('week');
        dateEnd = now.endOf('week');
        break;
      case 'month':
        dateStart = now.startOf('month');
        dateEnd = now.endOf('month');
        break;
      default:
        dateStart = now.startOf('week');
        dateEnd = now.endOf('week');
    }
    
    const sql = `
      SELECT 
        p.date,
        COUNT(DISTINCT p.matricule) as employes_points,
        (SELECT COUNT(*) FROM salaries WHERE etat = 1) as effectif_total,
        ROUND((COUNT(DISTINCT p.matricule) / (SELECT COUNT(*) FROM salaries WHERE etat = 1)) * 100, 2) as taux_presence
      FROM pointage p
      WHERE p.date BETWEEN ? AND ?
      GROUP BY p.date
      ORDER BY p.date ASC
    `;
    
    const results = await query('grh', sql, [dateStart.format('YYYY-MM-DD'), dateEnd.format('YYYY-MM-DD')]);
    
    res.json({
      success: true,
      data: results
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      message: error.message
    });
  }
});

// Congés en cours
router.get('/conges-en-cours', async (req, res) => {
  try {
    const date = moment().format('YYYY-MM-DD');
    
    const sql = `
      SELECT 
        c.nomprenom_conge as nom_prenom,
        c.type_conge as type,
        c.nbre_jours_conge as nombre_jours,
        c.exercice
      FROM conge c
      WHERE c.date_recrutement_conge <= ? 
        AND c.date_fin_contrat_conge >= ?
      ORDER BY c.nomprenom_conge ASC
    `;
    
    const results = await query('grh', sql, [date, date]);
    
    res.json({
      success: true,
      data: {
        total: results.length,
        conges: results
      }
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      message: error.message
    });
  }
});

// Répartition par tranche d'âge
router.get('/repartition-age', async (req, res) => {
  try {
    const sql = `
      SELECT 
        CASE 
          WHEN TIMESTAMPDIFF(YEAR, date_naissance, CURDATE()) < 30 THEN 'Moins de 30 ans'
          WHEN TIMESTAMPDIFF(YEAR, date_naissance, CURDATE()) < 40 THEN '30-40 ans'
          WHEN TIMESTAMPDIFF(YEAR, date_naissance, CURDATE()) < 50 THEN '40-50 ans'
          ELSE 'Plus de 50 ans'
        END as tranche_age,
        COUNT(*) as nombre
      FROM salaries 
      WHERE date_naissance IS NOT NULL AND etat = 1
      GROUP BY tranche_age
      ORDER BY tranche_age
    `;
    
    const results = await query('grh', sql);
    
    res.json({
      success: true,
      data: results
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      message: error.message
    });
  }
});

// Ancienneté moyenne
router.get('/anciennete', async (req, res) => {
  try {
    const sql = `
      SELECT 
        AVG(TIMESTAMPDIFF(YEAR, date_recrutement, CURDATE())) as anciennete_moyenne,
        MIN(TIMESTAMPDIFF(YEAR, date_recrutement, CURDATE())) as anciennete_min,
        MAX(TIMESTAMPDIFF(YEAR, date_recrutement, CURDATE())) as anciennete_max
      FROM salaries 
      WHERE date_recrutement IS NOT NULL AND etat = 1
    `;
    
    const results = await query('grh', sql);
    
    res.json({
      success: true,
      data: results[0]
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      message: error.message
    });
  }
});

module.exports = router;