import { getConnection } from '../db/connection.js';

export const getReportsByCompany = async (req, res) => {
  let connection;
  const { table_prefix, company_nit } = req.body;

  if (!table_prefix || !company_nit) {
    console.warn("Falta table_prefix o company_nit");
    return res.status(400).json({ error: "Debes enviar table_prefix y company_nit" });
  }

  try {
    connection = await getConnection();

    const regionTable = `${table_prefix}_region_reports`;
    const countryTable = `${table_prefix}_country_reports`;

    //  Consultar reportes regionales
    const [regionReports] = await connection.query(
      `SELECT 'region' AS report_type, id, year, month, category_id, subcategory_id, company_id, region, produccion, ventas_pesos, ventas_volumen
       FROM \`${regionTable}\`
       WHERE company_id = ?`,
      [company_nit]
    );

    //  Consultar reportes nacionales
    const [countryReports] = await connection.query(
      `SELECT 'country' AS report_type, id, year, month, category_id, subcategory_id, company_id, NULL AS region, produccion, NULL AS ventas_pesos, NULL AS ventas_volumen
       FROM \`${countryTable}\`
       WHERE company_id = ?`,
      [company_nit]
    );

    //  Combinar ambos
    const allReports = [...regionReports, ...countryReports];

    res.status(200).json({
      message: 'Reportes obtenidos correctamente',
      total: allReports.length,
      region_reports: regionReports.length,
      country_reports: countryReports.length,
      data: allReports
    });

  } catch (error) {
    console.error(" Error obteniendo reports por company_nit:", error);
    res.status(500).json({ error: "Error obteniendo reports" });
  } finally {
    if (connection) {
      connection.release();
    }
  }
};

export const getReportsGroupedByMonthYear = async (req, res) => {
  let connection;
  const { table_prefix } = req.body;

  console.log("[getReportsGroupedByMonthYear] Body recibido:", req.body);

  if (!table_prefix) {
    console.warn("Falta table_prefix en el body");
    return res.status(400).json({ error: "Debes enviar table_prefix" });
  }

  try {
    connection = await getConnection();
    console.log("Conexión establecida a la BD");

    const regionTable = `${table_prefix}_region_reports`;
    const countryTable = `${table_prefix}_country_reports`;

    console.log(`Consultando tablas: ${regionTable} y ${countryTable}`);

    //  Sumar producción, ventas, etc. combinando ambas tablas
    const [rows] = await connection.query(`
      SELECT 
        year,
        month,
        SUM(produccion) AS total_produccion,
        SUM(ventas_pesos) AS total_ventas_pesos,
        SUM(ventas_volumen) AS total_ventas_volumen
      FROM (
        SELECT year, month, produccion, ventas_pesos, ventas_volumen
        FROM \`${regionTable}\`
        UNION ALL
        SELECT year, month, produccion, NULL AS ventas_pesos, NULL AS ventas_volumen
        FROM \`${countryTable}\`
      ) AS combined
      GROUP BY year, month
      ORDER BY year DESC, month DESC
    `);

    console.log(`Periodos encontrados: ${rows.length}`);

    res.status(200).json({
      message: 'Reportes agrupados correctamente',
      total_periods: rows.length,
      data: rows
    });

  } catch (error) {
    console.error(" Error obteniendo reports agrupados:", error);
    res.status(500).json({ error: "Error obteniendo reports agrupados" });
  } finally {
    if (connection) {
      connection.release();
    }
  }
};
