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

// Recettes totales par période
router.get('/total', async (req, res) => {
  try {
    const { periode = 'week', gare = null } = 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;
      case 'year':
        dateStart = now.startOf('year');
        dateEnd = now.endOf('year');
        break;
      default:
        dateStart = now.startOf('week');
        dateEnd = now.endOf('week');
    }
    
    let sql = `
      SELECT 
        SUM(montant) as total_recettes,
        COUNT(*) as nombre_transactions
      FROM recettes 
      WHERE date_recette BETWEEN ? AND ?
    `;
    
    const params = [dateStart.format('YYYY-MM-DD'), dateEnd.format('YYYY-MM-DD')];
    
    if (gare) {
      sql += ' AND code_gare = ?';
      params.push(gare);
    }
    
    const result = await query('ggr', sql, params);
    
    res.json({
      success: true,
      data: {
        total: result[0].total_recettes || 0,
        transactions: result[0].nombre_transactions || 0,
        periode: periode,
        date_start: dateStart.format('YYYY-MM-DD'),
        date_end: dateEnd.format('YYYY-MM-DD')
      }
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      message: error.message
    });
  }
});

// Recettes par gare
router.get('/par-gare', async (req, res) => {
  try {
    const { periode = 'week' } = 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('week');
        dateEnd = now.endOf('week');
    }
    
    const sql = `
      SELECT 
        g.libelle as gare,
        SUM(r.montant) as total_recettes,
        COUNT(*) as nombre_transactions
      FROM recettes r
      JOIN gares g ON r.code_gare = g.code
      WHERE r.date_recette BETWEEN ? AND ?
      GROUP BY r.code_gare, g.libelle
      ORDER BY total_recettes DESC
      LIMIT 20
    `;
    
    const results = await query('ggr', 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
    });
  }
});

// Recettes par ligne
router.get('/par-ligne', async (req, res) => {
  try {
    const { periode = 'week' } = 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('week');
        dateEnd = now.endOf('week');
    }
    
    const sql = `
      SELECT 
        l.libelle as ligne,
        SUM(r.montant) as total_recettes,
        COUNT(*) as nombre_transactions
      FROM recettes r
      JOIN lignes l ON r.code_ligne = l.code
      WHERE r.date_recette BETWEEN ? AND ?
      GROUP BY r.code_ligne, l.libelle
      ORDER BY total_recettes DESC
      LIMIT 20
    `;
    
    const results = await query('ggr', 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 recettes (pour graphiques)
router.get('/evolution', async (req, res) => {
  try {
    const { periode = 'week', intervalle = 'day' } = req.query;
    let dateStart, dateEnd;
    
    const now = moment();
    switch(periode) {
      case 'week':
        dateStart = now.subtract(7, 'days');
        dateEnd = now;
        break;
      case 'month':
        dateStart = now.subtract(30, 'days');
        dateEnd = now;
        break;
      case 'year':
        dateStart = now.subtract(12, 'months');
        dateEnd = now;
        break;
      default:
        dateStart = now.subtract(7, 'days');
        dateEnd = now;
    }
    
    const sql = `
      SELECT 
        DATE(date_recette) as date,
        SUM(montant) as total_recettes,
        COUNT(*) as nombre_transactions
      FROM recettes 
      WHERE date_recette BETWEEN ? AND ?
      GROUP BY DATE(date_recette)
      ORDER BY date ASC
    `;
    
    const results = await query('ggr', 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
    });
  }
});

// Top gares (les plus rentables)
router.get('/top-gares', async (req, res) => {
  try {
    const { limit = 10, 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 
        g.libelle as gare,
        SUM(r.montant) as total_recettes,
        COUNT(*) as nombre_transactions,
        ROUND(SUM(r.montant) / COUNT(*), 2) as moyenne_par_transaction
      FROM recettes r
      JOIN gares g ON r.code_gare = g.code
      WHERE r.date_recette BETWEEN ? AND ?
      GROUP BY r.code_gare, g.libelle
      ORDER BY total_recettes DESC
      LIMIT ?
    `;
    
    const results = await query('ggr', sql, [dateStart.format('YYYY-MM-DD'), dateEnd.format('YYYY-MM-DD'), parseInt(limit)]);
    
    res.json({
      success: true,
      data: results
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      message: error.message
    });
  }
});

module.exports = router;