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

// État de la flotte (bus en marche, en réparation, immobilisés)
router.get('/etat', async (req, res) => {
  try {
    const sql = `
      SELECT 
        Etat,
        COUNT(*) as nombre,
        GROUP_CONCAT(Num_Parc) as bus_list
      FROM Bus 
      GROUP BY Etat
    `;
    
    const results = await query('parc', sql);
    
    const total = results.reduce((sum, r) => sum + r.nombre, 0);
    const enMarche = results.find(r => r.Etat === 'En Marche')?.nombre || 0;
    const enReparation = results.find(r => r.Etat === 'En Réparation')?.nombre || 0;
    const immobilises = results.find(r => r.Etat === 'Immobilisé')?.nombre || 0;
    
    res.json({
      success: true,
      data: {
        total,
        en_marche: enMarche,
        en_reparation: enReparation,
        immobilises,
        taux_disponibilite: total > 0 ? ((enMarche / total) * 100).toFixed(2) : 0,
        details: results
      }
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      message: error.message
    });
  }
});

// Bus par type
router.get('/par-type', async (req, res) => {
  try {
    const sql = `
      SELECT 
        type_bus as type,
        COUNT(*) as nombre,
        GROUP_CONCAT(DISTINCT Marque) as marques
      FROM Bus 
      GROUP BY type_bus
    `;
    
    const results = await query('parc', sql);
    
    res.json({
      success: true,
      data: results
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      message: error.message
    });
  }
});

// Consommations (huile, liquides) par période
router.get('/consommations', async (req, res) => {
  try {
    const { periode = 'month' } = req.query;
    let dateStart, dateEnd;
    
    const now = moment();
    switch(periode) {
      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('month');
        dateEnd = now.endOf('month');
    }
    
    const sql = `
      SELECT 
        Type_Lub as type_lubrifiant,
        SUM(Qte_Cons) as quantite_totale,
        COUNT(*) as nombre_interventions,
        AVG(Qte_Cons) as quantite_moyenne
      FROM Consommations 
      WHERE Date_Cons BETWEEN ? AND ?
      GROUP BY Type_Lub
      ORDER BY quantite_totale DESC
    `;
    
    const results = await query('parc', 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
    });
  }
});

// Évolution des consommations
router.get('/consommations-evolution', async (req, res) => {
  try {
    const { periode = 'month' } = req.query;
    let months;
    
    switch(periode) {
      case '3months':
        months = 3;
        break;
      case '6months':
        months = 6;
        break;
      default:
        months = 6;
    }
    
    const sql = `
      SELECT 
        DATE_FORMAT(Date_Cons, '%Y-%m') as mois,
        SUM(Qte_Cons) as quantite_totale,
        COUNT(*) as nombre_interventions
      FROM Consommations 
      WHERE Date_Cons >= DATE_SUB(NOW(), INTERVAL ? MONTH)
      GROUP BY DATE_FORMAT(Date_Cons, '%Y-%m')
      ORDER BY mois ASC
    `;
    
    const results = await query('parc', sql, [months]);
    
    res.json({
      success: true,
      data: results
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      message: error.message
    });
  }
});

// Contrôles techniques à venir
router.get('/controles-techniques', async (req, res) => {
  try {
    const { jours = 30 } = req.query;
    
    const sql = `
      SELECT 
        NumParc,
        DateExp as date_expiration,
        DATEDIFF(DateExp, CURDATE()) as jours_restant,
        CASE 
          WHEN DATEDIFF(DateExp, CURDATE()) < 0 THEN 'Expiré'
          WHEN DATEDIFF(DateExp, CURDATE()) <= 7 THEN 'Urgent'
          WHEN DATEDIFF(DateExp, CURDATE()) <= 30 THEN 'Bientôt'
          ELSE 'OK'
        END as statut
      FROM Controles_Techniques 
      WHERE DateExp IS NOT NULL 
        AND DateExp <= DATE_ADD(CURDATE(), INTERVAL ? DAY)
      ORDER BY DateExp ASC
    `;
    
    const results = await query('parc', sql, [parseInt(jours)]);
    
    const stats = {
      expire: results.filter(r => r.statut === 'Expiré').length,
      urgent: results.filter(r => r.statut === 'Urgent').length,
      bientot: results.filter(r => r.statut === 'Bientôt').length,
      total: results.length
    };
    
    res.json({
      success: true,
      data: {
        controles_a_venir: results,
        stats
      }
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      message: error.message
    });
  }
});

// Top bus les plus consommateur
router.get('/top-consommateurs', async (req, res) => {
  try {
    const { limit = 10, periode = 'month' } = req.query;
    let months;
    
    switch(periode) {
      case '3months':
        months = 3;
        break;
      default:
        months = 1;
    }
    
    const sql = `
      SELECT 
        c.Bus as bus,
        SUM(c.Qte_Cons) as quantite_totale,
        COUNT(*) as nombre_interventions,
        AVG(c.Qte_Cons) as quantite_moyenne,
        GROUP_CONCAT(DISTINCT c.Cause) as causes
      FROM Consommations c
      WHERE c.Date_Cons >= DATE_SUB(NOW(), INTERVAL ? MONTH)
      GROUP BY c.Bus
      ORDER BY quantite_totale DESC
      LIMIT ?
    `;
    
    const results = await query('parc', sql, [months, parseInt(limit)]);
    
    res.json({
      success: true,
      data: results
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      message: error.message
    });
  }
});

// Bus par affectation
router.get('/par-affectation', async (req, res) => {
  try {
    const sql = `
      SELECT 
        a.Libelle as affectation,
        COUNT(*) as nombre_bus,
        GROUP_CONCAT(b.Num_Parc) as bus_list
      FROM Bus b
      JOIN Affectations a ON b.Num_Parc = a.Libelle
      GROUP BY a.Code, a.Libelle
      ORDER BY nombre_bus DESC
    `;
    
    const results = await query('parc', sql);
    
    res.json({
      success: true,
      data: results
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      message: error.message
    });
  }
});

module.exports = router;