const mysql = require('mysql2/promise');
require('dotenv').config();

const pool = mysql.createPool({
  host: process.env.DB_GGR_HOST || 'localhost',
  user: process.env.DB_GGR_USER || 'transubd_ggr',
  password: process.env.DB_GGR_PASSWORD || '',
  database: process.env.DB_GGR_NAME || 'transubd_ggr',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

async function logQuery(question, parsed, response, userId, success) {
  try {
    const sql = `
      INSERT INTO ia_query_logs (question, parsed_type, parsed_periode, parsed_gare, parsed_ligne, response_text, success, user_id, ip_address, user_agent)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `;
    
    await pool.execute(sql, [
      question,
      parsed.typeDonnees || null,
      parsed.periode?.type || null,
      parsed.gare || null,
      parsed.ligne || null,
      success ? 'SUCCESS' : 'ERROR',
      success ? 1 : 0,
      userId || null,
      null,
      null
    ]);
  } catch (error) {
    console.error('Erreur logging query:', error.message);
  }
}

async function logSuccessfulQuery(question, parsed, data, userId) {
  return logQuery(question, parsed, data, userId, true);
}

async function logFailedQuery(question, parsed, error, userId) {
  return logQuery(question, parsed, error, userId, false);
}

async function createTables() {
  const createTableSql = `
    CREATE TABLE IF NOT EXISTS ia_query_logs (
      id INT AUTO_INCREMENT PRIMARY KEY,
      question TEXT NOT NULL,
      parsed_type VARCHAR(50),
      parsed_periode VARCHAR(20),
      parsed_gare VARCHAR(100),
      parsed_ligne VARCHAR(20),
      response_text TEXT,
      success TINYINT(1) DEFAULT 0,
      user_id VARCHAR(50),
      ip_address VARCHAR(45),
      user_agent VARCHAR(255),
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      INDEX idx_question (question(100)),
      INDEX idx_parsed_type (parsed_type),
      INDEX idx_parsed_periode (parsed_periode),
      INDEX idx_success (success),
      INDEX idx_created_at (created_at)
    )
  `;
  
  const createDictionaryTable = `
    CREATE TABLE IF NOT EXISTS ia_dictionary (
      id INT AUTO_INCREMENT PRIMARY KEY,
      phrase VARCHAR(255) NOT NULL,
      normalized_phrase VARCHAR(255) NOT NULL,
      type_donnees VARCHAR(50) NOT NULL,
      periode VARCHAR(20),
      gare VARCHAR(100),
      ligne VARCHAR(20),
      aggregation VARCHAR(20),
      comparateur VARCHAR(20),
      frequency INT DEFAULT 1,
      last_used TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      approved TINYINT(1) DEFAULT 0,
      UNIQUE KEY unique_phrase (normalized_phrase, type_donnees),
      INDEX idx_phrase (phrase(100)),
      INDEX idx_type (type_donnees),
      INDEX idx_frequency (frequency DESC),
      INDEX idx_approved (approved)
    )
  `;

  try {
    await pool.execute(createTableSql);
    await pool.execute(createDictionaryTable);
    console.log('✅ Tables IA log et dictionary créées');
  } catch (error) {
    console.error('❌ Erreur création tables:', error.message);
  }
}

async function getTopQueries(limit = 20) {
  const [rows] = await pool.execute(`
    SELECT question, success, created_at 
    FROM ia_query_logs 
    ORDER BY created_at DESC 
    LIMIT ?
  `, [limit]);
  return rows;
}

async function getFailedQueries(limit = 20) {
  const [rows] = await pool.execute(`
    SELECT question, success, created_at 
    FROM ia_query_logs 
    WHERE success = 0
    ORDER BY created_at DESC 
    LIMIT ?
  `, [limit]);
  return rows;
}

async function updateDictionary(phrase, typeDonnees, parsed) {
  if (!phrase || !typeDonnees) return;
  
  const normalized = phrase.toLowerCase().trim();
  
  try {
    await pool.execute(`
      INSERT INTO ia_dictionary (phrase, normalized_phrase, type_donnees, periode, gare, ligne, aggregation, comparateur, frequency, last_used)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, 1, NOW())
      ON DUPLICATE KEY UPDATE 
        frequency = frequency + 1,
        last_used = NOW()
    `, [phrase, normalized, typeDonnees, parsed?.periode?.type || null, parsed?.gare || null, parsed?.ligne || null, parsed?.aggregation || null, parsed?.comparateur || null]);
  } catch (error) {
    console.error('Erreur update dictionary:', error.message);
  }
}

async function getDictionary(approved = false) {
  const sql = approved 
    ? 'SELECT * FROM ia_dictionary WHERE approved = 1 ORDER BY frequency DESC'
    : 'SELECT * FROM ia_dictionary ORDER BY frequency DESC';
  const [rows] = await pool.execute(sql);
  return rows;
}

async function approveDictionaryEntry(id) {
  await pool.execute('UPDATE ia_dictionary SET approved = 1 WHERE id = ?', [id]);
}

async function getStatistics() {
  const [totalQueries] = await pool.execute('SELECT COUNT(*) as total FROM ia_query_logs');
  const [successfulQueries] = await pool.execute('SELECT COUNT(*) as total FROM ia_query_logs WHERE success = 1');
  const [failedQueries] = await pool.execute('SELECT COUNT(*) as total FROM ia_query_logs WHERE success = 0');
  const [topTypes] = await pool.execute(`
    SELECT parsed_type, COUNT(*) as count 
    FROM ia_query_logs 
    GROUP BY parsed_type 
    ORDER BY count DESC 
    LIMIT 5
  `);
  const [topGares] = await pool.execute(`
    SELECT parsed_gare, COUNT(*) as count 
    FROM ia_query_logs 
    WHERE parsed_gare IS NOT NULL
    GROUP BY parsed_gare 
    ORDER BY count DESC 
    LIMIT 5
  `);
  
  return {
    total: totalQueries[0].total,
    successful: successfulQueries[0].total,
    failed: failedQueries[0].total,
    topTypes,
    topGares
  };
}

module.exports = {
  pool,
  createTables,
  logQuery,
  logSuccessfulQuery,
  logFailedQuery,
  updateDictionary,
  getDictionary,
  approveDictionaryEntry,
  getTopQueries,
  getFailedQueries,
  getStatistics
};