const database = require('../config/database');

class QueryBuilder {
  constructor() {
    this.dbMapping = {
      recettes: 'ggr',
      frequentation: 'ggr',
      retards: 'ggr',
      flotte: 'parc',
      personnel: 'grh'
    };

    this.sqlTemplates = {
      recettes: {
        byGare: {
          query: `
            SELECT 
              COALESCE(SUM(montant), 0) as total,
              COUNT(*) as nb_transactions,
              DATE(date_operation) as date
            FROM transubd_GGR.gare_recettes
            WHERE date_operation BETWEEN ? AND ?
              AND LOWER(gare) LIKE LOWER(?)
            GROUP BY DATE(date_operation)
            ORDER BY date DESC
          `,
          params: (parsed) => [parsed.periode.start, parsed.periode.end, `%${parsed.gare}%`]
        },
        global: {
          query: `
            SELECT 
              COALESCE(SUM(montant), 0) as total,
              DATE(date_operation) as date
            FROM transubd_GGR.gare_recettes
            WHERE date_operation BETWEEN ? AND ?
            GROUP BY DATE(date_operation)
            ORDER BY date DESC
          `,
          params: (parsed) => [parsed.periode.start, parsed.periode.end]
        },
        topGares: {
          query: `
            SELECT 
              gare,
              SUM(montant) as total
            FROM transubd_GGR.gare_recettes
            WHERE date_operation BETWEEN ? AND ?
            GROUP BY gare
            ORDER BY total DESC
            LIMIT ?
          `,
          params: (parsed) => [parsed.periode.start, parsed.periode.end, parsed.limite]
        }
      },
      frequentation: {
        byGare: {
          query: `
            SELECT 
              COUNT(*) as total_passagers,
              DATE(date_operation) as date
            FROM transubd_GGR.gare_recettes
            WHERE date_operation BETWEEN ? AND ?
              AND LOWER(gare) LIKE LOWER(?)
            GROUP BY DATE(date_operation)
            ORDER BY date DESC
          `,
          params: (parsed) => [parsed.periode.start, parsed.periode.end, `%${parsed.gare}%`]
        },
        global: {
          query: `
            SELECT 
              COUNT(*) as total_passagers,
              DATE(date_operation) as date
            FROM transubd_GGR.gare_recettes
            WHERE date_operation BETWEEN ? AND ?
            GROUP BY DATE(date_operation)
            ORDER BY date DESC
          `,
          params: (parsed) => [parsed.periode.start, parsed.periode.end]
        }
      },
      retards: {
        yesterday: {
          query: `
            SELECT 
              ligne,
              COUNT(*) as nb_retards,
              AVG(temps_retard) as avg_retard,
              MIN(temps_retard) as min_retard,
              MAX(temps_retard) as max_retard
            FROM transubd_GGR.incidents
            WHERE DATE(date_incident) = ?
              AND type_incident = 'retard'
            GROUP BY ligne
            ORDER BY nb_retards DESC
          `,
          params: (parsed) => [parsed.periode.start]
        },
        byPeriode: {
          query: `
            SELECT 
              ligne,
              COUNT(*) as nb_retards,
              AVG(temps_retard) as avg_retard,
              DATE(date_incident) as date
            FROM transubd_GGR.incidents
            WHERE date_incident BETWEEN ? AND ?
              AND type_incident = 'retard'
            GROUP BY ligne, DATE(date_incident)
            ORDER BY date DESC, nb_retards DESC
          `,
          params: (parsed) => [parsed.periode.start, parsed.periode.end]
        },
        byLigne: {
          query: `
            SELECT 
              ligne,
              COUNT(*) as nb_retards,
              AVG(temps_retard) as avg_retard,
              DATE(date_incident) as date
            FROM transubd_GGR.incidents
            WHERE date_incident BETWEEN ? AND ?
              AND ligne = ?
            GROUP BY DATE(date_incident)
            ORDER BY date DESC
          `,
          params: (parsed) => [parsed.periode.start, parsed.periode.end, parsed.ligne]
        }
      },
      flotte: {
        etat: {
          query: `
            SELECT 
              etat,
              COUNT(*) as nombre,
              marque,
              modele
            FROM transubd_GestionParc.vehicules
            GROUP BY etat, marque, modele
            ORDER BY etat, nombre DESC
          `,
          params: () => []
        },
        disponibilite: {
          query: `
            SELECT 
              etat,
              COUNT(*) as nombre
            FROM transubd_GestionParc.vehicules
            GROUP BY etat
            ORDER BY nombre DESC
          `,
          params: () => []
        },
        byType: {
          query: `
            SELECT 
              type_vehicule,
              COUNT(*) as nombre,
              etat
            FROM transubd_GestionParc.vehicules
            WHERE etat = 'actif' OR etat = 'disponible'
            GROUP BY type_vehicule, etat
            ORDER BY nombre DESC
          `,
          params: () => []
        }
      },
      personnel: {
        effectif: {
          query: `
            SELECT 
              service,
              COUNT(*) as effectif,
              SUM(CASE WHEN statut = 'actif' THEN 1 ELSE 0 END) as actifs
            FROM transubd_GRH.employes
            GROUP BY service
            ORDER BY effectif DESC
          `,
          params: () => []
        },
        presence: {
          query: `
            SELECT 
              DATE(date_pointage) as date,
              COUNT(DISTINCT employe_id) as presents,
              SUM(CASE WHEN present = 0 THEN 1 ELSE 0 END) as absents
            FROM transubd_gestion_pointage.pointages
            WHERE date_pointage BETWEEN ? AND ?
            GROUP BY DATE(date_pointage)
            ORDER BY date DESC
          `,
          params: (parsed) => [parsed.periode.start, parsed.periode.end]
        },
        absenteisme: {
          query: `
            SELECT 
              service,
              COUNT(*) as total_employes,
              SUM(CASE WHEN present = 0 THEN 1 ELSE 0 END) as nb_absences,
              ROUND((SUM(CASE WHEN present = 0 THEN 1 ELSE 0 END) / COUNT(*)) * 100, 2) as taux_absence
            FROM transubd_GRH.employes e
            LEFT JOIN transubd_gestion_pointage.pointages p ON e.id = p.employe_id
            WHERE p.date_pointage BETWEEN ? AND ?
            GROUP BY service
            ORDER BY taux_absence DESC
          `,
          params: (parsed) => [parsed.periode.start, parsed.periode.end]
        }
      }
    };
  }

  buildQuery(parsed) {
    if (!parsed.typeDonnees) {
      throw new Error('Type de données non identifié. Veuillez préciser votre question (recettes, fréquentations, retards, flotte, personnel).');
    }

    const templates = this.sqlTemplates[parsed.typeDonnees];
    if (!templates) {
      throw new Error(`Type de données "${parsed.typeDonnees}" non supporté.`);
    }

    let template;

    switch (parsed.typeDonnees) {
      case 'recettes':
        if (parsed.gare) {
          template = templates.byGare;
        } else if (parsed.aggregation === 'COUNT' || parsed.comparateur) {
          template = templates.topGares;
        } else {
          template = templates.global;
        }
        break;

      case 'frequentation':
        template = parsed.gare ? templates.byGare : templates.global;
        break;

      case 'retards':
        if (parsed.periode.type === 'yesterday') {
          template = templates.yesterday;
        } else if (parsed.ligne) {
          template = templates.byLigne;
        } else {
          template = templates.byPeriode;
        }
        break;

      case 'flotte':
        if (parsed.gare || parsed.ligne) {
          template = templates.byType;
        } else if (parsed.comparateur === 'disponibilite') {
          template = templates.disponibilite;
        } else {
          template = templates.etat;
        }
        break;

      case 'personnel':
        if (parsed.aggregation === 'COUNT' || parsed.comparateur === 'absenteisme') {
          template = templates.absenteisme;
        } else if (parsed.comparateur === 'presence') {
          template = templates.presence;
        } else {
          template = templates.effectif;
        }
        break;

      default:
        throw new Error(`Type de données "${parsed.typeDonnees}" non implémenté.`);
    }

    return {
      sql: template.query,
      params: template.params(parsed),
      dbKey: this.dbMapping[parsed.typeDonnees] || 'ggr'
    };
  }

  async executeQuery(queryObj) {
    try {
      const dbKey = queryObj.dbKey || 'ggr';
      const pool = database.pools[dbKey];
      
      if (!pool) {
        throw new Error(`Base de données ${dbKey} non configurée`);
      }
      
      const connection = await pool.getConnection();
      
      const [results] = await connection.execute(
        queryObj.sql,
        queryObj.params
      );
      
      connection.release();
      
      if (!results || results.length === 0) {
        return {
          empty: true,
          message: 'Aucune donnée trouvée pour cette période.',
          data: []
        };
      }

      return {
        empty: false,
        data: results
      };
    } catch (error) {
      console.error('Erreur lors de l\'exécution de la requête:', error);
      return {
        empty: true,
        message: `Erreur lors de la récupération des données: ${error.message}`,
        data: [],
        error: error.message
      };
    }
  }

  validateAndSanitize(input) {
    if (typeof input !== 'string') return input;
    
    const dangerousPatterns = [
      /;/g,
      /--/g,
      /\/\*/g,
      /\*\//g,
      /xp_/gi,
      /sp_/gi,
      /exec\s*\(/gi,
      /execute\s*\(/gi,
      /union\s+select/gi,
      /drop\s+table/gi,
      /drop\s+database/gi,
      /insert\s+into/gi,
      /update\s+.*\s+set/gi,
      /delete\s+from/gi
    ];

    for (const pattern of dangerousPatterns) {
      if (pattern.test(input)) {
        throw new Error('Entrée invalide détectée. Veuillez reformuler votre question.');
      }
    }

    return input.trim();
  }

  getAvailableTypes() {
    return Object.keys(this.sqlTemplates);
  }

  getAvailableOperations(typeDonnees) {
    const template = this.sqlTemplates[typeDonnees];
    if (!template) return [];
    return Object.keys(template);
  }
}

module.exports = new QueryBuilder();