// IPI Index Calculation Service
const mysql = require('mysql2/promise');
const logger = require("./logger");

class IPICalculationService {


  constructor(dbConfig) {
    this.pool = mysql.createPool(dbConfig);
  }

  
  // STEP 1: Calculate and Store Base Year Average Production (Quarter-based)
  async calculateBaseYearProduction(baseYear = 2022, forceRecalculate = false) {

    const connection = await this.pool.getConnection();
    let logId = null;
    
    try {
      await connection.beginTransaction();
      
      logger.info(`calculateBaseYearProduction - Calculating Base Year Production for ${baseYear}...`);
      
      // If force recalculate, delete existing data
      if (forceRecalculate) {
        logger.info('calculateBaseYearProduction - Force recalculate enabled , clearing existing data...');
        await connection.query(
          `DELETE FROM base_year_production WHERE base_year = ?`,
          [baseYear]
        );
      }
      
      // Log calculation start
      logId = await this.logRecord( 'base_year', baseYear, null, 'Started', null, 0, null, connection);

      // Quarter-based base year production using submission_products.current_quantity
      const query = `
        SELECT
          p.id AS product_id,
          p.hs_code AS product_hs_code,
          u.uom_short_name AS unit,
          SUM(CASE WHEN s.quarter = 'Q1' THEN COALESCE(sp.current_quantity, 0) END) AS q1,
          SUM(CASE WHEN s.quarter = 'Q2' THEN COALESCE(sp.current_quantity, 0) END) AS q2,
          SUM(CASE WHEN s.quarter = 'Q3' THEN COALESCE(sp.current_quantity, 0) END) AS q3,
          SUM(CASE WHEN s.quarter = 'Q4' THEN COALESCE(sp.current_quantity, 0) END) AS q4,
          ROUND((
            COALESCE(SUM(CASE WHEN s.quarter='Q1' THEN sp.current_quantity END),0) +
            COALESCE(SUM(CASE WHEN s.quarter='Q2' THEN sp.current_quantity END),0) +
            COALESCE(SUM(CASE WHEN s.quarter='Q3' THEN sp.current_quantity END),0) +
            COALESCE(SUM(CASE WHEN s.quarter='Q4' THEN sp.current_quantity END),0)
          ) / 4, 2) AS avg_by_production
        FROM submission_products sp
        JOIN submission s ON s.id = sp.submission_id
        JOIN products p ON p.id = sp.product_id
        LEFT JOIN unit_master u ON u.id = p.unit_id
        WHERE s.year = ? AND sp.is_active = 1 AND s.status = 'Approved'
        GROUP BY p.id
      `;

      const [products] = await connection.query(query, [baseYear]);


      logger.info(`calculateBaseYearProduction - Found ${products.length} products to process...`);


      // Insert into base_year_production table
      let processedCount = 0;
      for (const product of products) {
        await connection.query(
          `INSERT INTO base_year_production 
           (product_id, product_hs_code, base_year, unit, q1, q2, q3, q4, avg_by_production)
           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
          [
            product.product_id, product.product_hs_code, baseYear, product.unit,
            product.q1 || 0, product.q2 || 0, product.q3 || 0, product.q4 || 0,
            product.avg_by_production
          ]
        );
        processedCount++;
        
        // Log progress every 50 products
        if (processedCount % 50 === 0) {
          logger.info(`calculateBaseYearProduction - Processed ${processedCount}/${products.length} products...`);
        }
      }

      // Update log
      await this.logRecord('base_year', baseYear, null, 'Completed', logId, products.length, null,connection);


      await connection.commit();
      logger.info(`calculateBaseYearProduction -  Base Year Production calculated: ${products.length} products`);
      return { success: true, productsProcessed: products.length };

    } catch (error) {
      await connection.rollback();
      console.error('Error calculating base year production:', error);
      logger.error(`calculateBaseYearProduction - Error calculating base year production: ${error} `);
      // Update log OUTSIDE the failed transaction
      if (logId) {
        await this.logRecord('base_year', baseYear, null, 'Failed', logId, 0, error.message);
      }

      throw error;
    } finally {
      connection.release();
    }
  }
 
  
  // STEP 2: Aggregate Quarterly Production
  async aggregateQuarterlyProduction(year, quarter) {
    const connection = await this.pool.getConnection();
    let logId = null;
    
    try {
      await connection.beginTransaction();
      
      logger.info(`aggregateQuarterlyProduction - Aggregating production for ${year}-${quarter}...`);

      // Log calculation start
      logId = await this.logRecord('monthly_production', year, quarter, 'Started', null, 0, null, connection);

      const validQuarters = new Set(['Q1', 'Q2', 'Q3', 'Q4']);
      if (!validQuarters.has(quarter)) {
        throw new Error('Invalid quarter. Accepted values: Q1, Q2, Q3, Q4');
      }

      // Aggregate production by product
      const query = `
        SELECT
          p.id AS product_id,
          p.hs_code AS product_hs_code,
          u.uom_short_name AS unit,
          SUM(COALESCE(sp.current_quantity, 0)) AS production_quantity
        FROM submission_products sp
        JOIN submission s ON s.id = sp.submission_id
        JOIN products p ON p.id = sp.product_id
        LEFT JOIN unit_master u ON u.id = p.unit_id
        WHERE s.year = ? AND s.quarter = ? 
          AND sp.is_active = 1 
          AND s.status = 'Approved'
        GROUP BY p.id
      `;

      const [products] = await connection.query(query, [year, quarter]);

      // Insert monthly production
      for (const product of products) {
        await connection.query(
          `INSERT INTO monthly_production 
           (product_id, product_hs_code, year, quarter, quarter_name, 
            production_quantity, unit)
           VALUES (?, ?, ?, ?, ?, ?, ?)
           ON DUPLICATE KEY UPDATE
           production_quantity=VALUES(production_quantity), 
           quarter_name=VALUES(quarter_name),
           unit=VALUES(unit)`,
          [
            product.product_id, product.product_hs_code, year, quarter,
            quarter, product.production_quantity || 0, product.unit
          ]
        );
      }


      // Update log
      await this.logRecord('monthly_production', year, quarter, 'Completed', logId, products.length, null, connection);

      await connection.commit();
      logger.info(`aggregateQuarterlyProduction - Quarterly production aggregated: ${products.length} products`);
      return { success: true, productsProcessed: products.length };

    } catch (error) {
      await connection.rollback();
      console.error('Error aggregating monthly production:', error);
      logger.error(`aggregateQuarterlyProduction - Error aggregating quarterly production: ${error}`);
      // Update log OUTSIDE the failed transaction
      if (logId) {
        await this.logRecord('monthly_production', year, quarter, 'Failed', logId, 0, error.message);
      }
      throw error;
    } finally {
      connection.release();
    }
  }

  
  // STEP 3: Calculate Item Level Indices
  async calculateItemLevelIndices(year, quarter) {
    const connection = await this.pool.getConnection();
    let logId = null;
    try {
      await connection.beginTransaction();
      
      logger.info(`calculateItemLevelIndices - Calculating item level indices for ${year}-${quarter}...`);

      // Log calculation start
      logId = await this.logRecord('item_level_indices', year, quarter, 'Started', null, 0, null, connection);

      // Calculate indices: Ri = Current Production / Base Year Avg Production
      // Ii = Ri × 100
      const query = `
        SELECT
          mp.product_id,
          mp.product_hs_code,
          mp.production_quantity AS current_production,
          byp.avg_by_production AS base_year_avg_production,
          (mp.production_quantity / byp.avg_by_production) AS production_relative,
          ((mp.production_quantity / byp.avg_by_production) * 100) AS item_index
        FROM monthly_production mp
        JOIN base_year_production byp ON byp.product_id = mp.product_id
        WHERE mp.year = ? AND mp.quarter = ?
          AND byp.avg_by_production > 0
      `;

      const [items] = await connection.query(query, [year, quarter]);

      // Insert item level indices
      for (const item of items) {
        await connection.query(
          `INSERT INTO item_level_indices 
           (product_id, product_hs_code, year, quarter, quarter_name,
            current_production, base_year_avg_production, 
            production_relative, item_index)
           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
           ON DUPLICATE KEY UPDATE
           current_production=VALUES(current_production),
           base_year_avg_production=VALUES(base_year_avg_production),
           production_relative=VALUES(production_relative),
           item_index=VALUES(item_index)`,
          [
            item.product_id, item.product_hs_code, year, quarter, quarter,
            item.current_production, item.base_year_avg_production,
            item.production_relative, item.item_index
          ]
        );
      }

      // Update log
      await this.logRecord('item_level_indices', year, quarter, 'Completed', logId, items.length, null, connection);

      await connection.commit();
      logger.info(`calculateItemLevelIndices - Item level indices calculated: ${items.length} items`);
      return { success: true, itemsProcessed: items.length };

    } catch (error) {
      await connection.rollback();
      console.error('Error calculating item indices:', error);
      logger.error(`calculateItemLevelIndices - Error calculating item indices: ${error}`);
      // Update log OUTSIDE the failed transaction
      if (logId) {
      await this.logRecord('item_level_indices', year, quarter, 'Failed', logId, 0, error.message);
      }
      throw error;
    } finally {
      connection.release();
    }
  }


  // STEP 4: Calculate ISIC 4-Digit Level Indices
  async calculateISIC4DigitIndices(year, quarter) {
    const connection = await this.pool.getConnection();
    let logId = null;
    try {
      await connection.beginTransaction();
      
      logger.info(`calculateISIC4DigitIndices - Calculating ISIC 4-digit indices for ${year}-${quarter}...`);

      // Log calculation start
      logId = await this.logRecord('isic_4digit_indices', year, quarter, 'Started', null, 0, null, connection);

      // ISIC 4-digit grouping uses establishments.isic_code (via approved submissions).
      // One establishment per product-period is chosen (highest current_quantity, then submission id)
      // so item_index and weight_in_ib are not multiplied when several establishments report the same product.
      const query = `
        WITH product_isic_from_establishment AS (
          SELECT
            ili.product_id,
            ili.item_index,
            LEFT(TRIM(CAST(e.isic_code AS CHAR)), 4) AS isic_4digit_code,
            ROW_NUMBER() OVER (
              PARTITION BY ili.product_id
              ORDER BY COALESCE(sp.current_quantity, 0) DESC, s.id ASC
            ) AS rn
          FROM item_level_indices ili
          JOIN submission_products sp ON sp.product_id = ili.product_id
          JOIN submission s ON s.id = sp.submission_id
            AND s.year = ?
            AND s.quarter = ?
            AND s.status = 'Approved'
          JOIN establishments e ON e.id = s.establishment_id
          WHERE ili.year = ?
            AND ili.quarter = ?
            AND sp.is_active = 1
            AND e.isic_code IS NOT NULL
            AND TRIM(CAST(e.isic_code AS CHAR)) <> ''
        )
        SELECT
          pie.isic_4digit_code,
          SUM(COALESCE(p.weight_in_ib, 0)) AS total_weight,
          SUM(COALESCE(p.weight_in_ib, 0) * pie.item_index) AS weighted_index_sum,
          (
            SUM(COALESCE(p.weight_in_ib, 0) * pie.item_index) /
            NULLIF(SUM(COALESCE(p.weight_in_ib, 0)), 0)
          ) AS isic_4digit_index
        FROM product_isic_from_establishment pie
        JOIN products p ON p.id = pie.product_id
        WHERE pie.rn = 1
          AND p.weight_in_ib IS NOT NULL
          AND p.weight_in_ib > 0
        GROUP BY pie.isic_4digit_code
      `;

      const [isic4Results] = await connection.query(query, [year, quarter, year, quarter]);

      for (const result of isic4Results) {
        await connection.query(
          `INSERT INTO isic_4digit_indices 
           (isic_4digit_code, year, quarter, quarter_name, total_weight, 
            weighted_index_sum, isic_4digit_index)
           VALUES (?, ?, ?, ?, ?, ?, ?)
           ON DUPLICATE KEY UPDATE
           total_weight=VALUES(total_weight),
           weighted_index_sum=VALUES(weighted_index_sum),
           isic_4digit_index=VALUES(isic_4digit_index)`,
          [
            result.isic_4digit_code, year, quarter, quarter,
            result.total_weight, 
            result.weighted_index_sum, 
            result.isic_4digit_index
          ]
        );
      }

      // Update log
      await this.logRecord('isic_4digit_indices', year, quarter, 'Completed', logId, isic4Results.length, null, connection);
	      
      await connection.commit();
      logger.info(`calculateISIC4DigitIndices - ISIC 4-digit indices calculated: ${isic4Results.length} codes`);
      return { success: true, codesProcessed: isic4Results.length };

    } catch (error) {
      await connection.rollback();
      console.error('Error calculating ISIC 4-digit indices:', error);
      logger.error(`calculateISIC4DigitIndices - Error calculating ISIC 4-digit indices: ${error} `);
      // Update log OUTSIDE the failed transaction
      if (logId) {
      await this.logRecord('isic_4digit_indices', year, quarter, 'Failed', logId, 0, error.message);
      }
      throw error;
    } finally {
      connection.release();
    }
  }

  
  // STEP 5: Calculate ISIC 3-Digit Level Indices
  async calculateISIC3DigitIndices(year, quarter) {
    const connection = await this.pool.getConnection();
    let logId = null;
    try {
      await connection.beginTransaction();

      logger.info(`calculateISIC3DigitIndices - Calculating ISIC 3-digit indices for ${year}-${quarter}...`);

      // Log calculation start
      logId = await this.logRecord('isic_3digit_indices', year, quarter, 'Started', null, 0, null, connection);

      const query = `
        SELECT
          LEFT(isic_4digit_code, 3) AS isic_3digit_code,
          SUM(total_weight) AS total_weight,
          SUM(total_weight * isic_4digit_index) AS weighted_index_sum,
          (SUM(total_weight * isic_4digit_index) / SUM(total_weight)) AS isic_3digit_index
        FROM isic_4digit_indices
        WHERE year = ? AND quarter = ?
        GROUP BY LEFT(isic_4digit_code, 3)
      `;

      const [isic3Results] = await connection.query(query, [year, quarter]);

      for (const result of isic3Results) {
        await connection.query(
          `INSERT INTO isic_3digit_indices 
           (isic_3digit_code, year, quarter, quarter_name, total_weight, 
            weighted_index_sum, isic_3digit_index)
           VALUES (?, ?, ?, ?, ?, ?, ?)
           ON DUPLICATE KEY UPDATE
           total_weight=VALUES(total_weight),
           weighted_index_sum=VALUES(weighted_index_sum),
           isic_3digit_index=VALUES(isic_3digit_index)`,
          [
            result.isic_3digit_code, year, quarter, quarter,
            result.total_weight, result.weighted_index_sum,
            result.isic_3digit_index
          ]
        );
      }

      // Update log
      await this.logRecord('isic_3digit_indices', year, quarter, 'Completed', logId, isic3Results.length, null, connection);

      await connection.commit();
      logger.info(`calculateISIC3DigitIndices - ISIC 3-digit indices calculated: ${isic3Results.length} codes`);
      return { success: true, codesProcessed: isic3Results.length };

    } catch (error) {
      await connection.rollback();
      console.error('Error calculating ISIC 3-digit indices:', error);
      logger.error(`calculateISIC3DigitIndices - Error calculating ISIC 3-digit indices: ${error} `);
      // Update log OUTSIDE the failed transaction
      if (logId) {
      await this.logRecord('isic_3digit_indices', year, quarter, 'Failed', logId, 0, error.message);
      }
      throw error;
    } finally {
      connection.release();
    }
  }

  
  // STEP 6: Calculate ISIC 2-Digit Level Indices
  async calculateISIC2DigitIndices(year, quarter) {
    const connection = await this.pool.getConnection();
    let logId = null;
    try {
      await connection.beginTransaction();

      logger.info(`calculateISIC2DigitIndices - Calculating ISIC 2-digit indices for ${year}-${quarter}...`);

      // Log calculation start
      logId = await this.logRecord('isic_2digit_indices', year, quarter, 'Started', null, 0, null, connection);

      const query = `
        SELECT
          LEFT(isic_3digit_code, 2) AS isic_2digit_code,
          SUM(total_weight) AS total_weight,
          SUM(total_weight * isic_3digit_index) AS weighted_index_sum,
          (SUM(total_weight * isic_3digit_index) / SUM(total_weight)) AS isic_2digit_index
        FROM isic_3digit_indices
        WHERE year = ? AND quarter = ?
        GROUP BY LEFT(isic_3digit_code, 2)
      `;

      const [isic2Results] = await connection.query(query, [year, quarter]);

      for (const result of isic2Results) {
        await connection.query(
          `INSERT INTO isic_2digit_indices 
           (isic_2digit_code, year, quarter, quarter_name, total_weight, 
            weighted_index_sum, isic_2digit_index)
           VALUES (?, ?, ?, ?, ?, ?, ?)
           ON DUPLICATE KEY UPDATE
           total_weight=VALUES(total_weight),
           weighted_index_sum=VALUES(weighted_index_sum),
           isic_2digit_index=VALUES(isic_2digit_index)`,
          [
            result.isic_2digit_code, year, quarter, quarter,
            result.total_weight, result.weighted_index_sum,
            result.isic_2digit_index
          ]
        );
      }

      // Update log
      await this.logRecord('isic_2digit_indices', year, quarter, 'Completed', logId, isic2Results.length, null, connection);

      await connection.commit();
      logger.info(`calculateISIC2DigitIndices - ISIC 2-digit indices calculated: ${isic2Results.length} codes`);
      return { success: true, codesProcessed: isic2Results.length };

    } catch (error) {
      await connection.rollback();
      console.error('Error calculating ISIC 2-digit indices:', error);
      logger.error(`calculateISIC2DigitIndices - Error calculating ISIC 2-digit indices: ${error}`);
      // Update log OUTSIDE the failed transaction
      if (logId) {
      await this.logRecord('isic_2digit_indices', year, quarter, 'Failed', logId, 0, error.message);
      }
      throw error;
    } finally {
      connection.release();
    }
  }

  
  // STEP 7: Calculate Manufacturing IPI (Headline Index)
  async calculateManufacturingIPI(year, quarter) {
    const connection = await this.pool.getConnection();
    let logId = null;
    try {
      await connection.beginTransaction();

      logger.info(`calculateManufacturingIPI - Calculating Manufacturing indices for ${year}-${quarter}...`);

      // Log calculation start
      logId = await this.logRecord('manufacturing_ipi', year, quarter, 'Started', null, 0, null, connection);

      // Calculate Manufacturing IPI
      const query = `
        SELECT
          SUM(total_weight) AS total_weight,
          SUM(total_weight * isic_2digit_index) AS weighted_index_sum,
          (SUM(total_weight * isic_2digit_index) / SUM(total_weight)) AS manufacturing_index
        FROM isic_2digit_indices
        WHERE year = ? AND quarter = ?
      `;

      const [result] = await connection.query(query, [year, quarter]);

      // If no data found (or aggregate returns NULLs), skip insert to avoid NOT NULL DB errors
      if (
        !result ||
        result.length === 0 ||
        result[0].total_weight == null ||
        result[0].weighted_index_sum == null ||
        result[0].manufacturing_index == null
      ) {
        await this.logRecord('manufacturing_ipi', year, quarter, 'Completed', logId, 0, null, connection);
        await connection.commit();

        logger.info(`calculateManufacturingIPI - No valid ISIC 2-digit aggregates found for ${year}-${quarter}`);
        return {
          success: true,
          index: null,
          qoqChange: null,
        momChange: null,
          yoyChange: null,
          message: "No IPI data found for the given year and quarter"
        };
      }

      const ipiData = result[0];

      // Calculate QoQ and YoY changes
      let qoqChange = null;
      let yoyChange = null;

      // Previous quarter (QoQ)
      const quarterOrder = ['Q1', 'Q2', 'Q3', 'Q4'];
      const quarterIdx = quarterOrder.indexOf(quarter);
      const prevQuarter = quarterIdx === 0 ? 'Q4' : quarterOrder[quarterIdx - 1];
      const prevYear = quarterIdx === 0 ? year - 1 : year;

      const [prevQuarterData] = await connection.query(
        `SELECT manufacturing_index FROM manufacturing_ipi 
         WHERE year = ? AND quarter = ?`,
        [prevYear, prevQuarter]
      );

      if (prevQuarterData.length > 0 && prevQuarterData[0].manufacturing_index) {
        const prevIndex = prevQuarterData[0].manufacturing_index;
        if (Number(prevIndex) !== 0) {
          qoqChange = ((ipiData.manufacturing_index - prevIndex) / prevIndex) * 100;
        }
      }

      // Get same quarter last year for YoY
      const [lastYearData] = await connection.query(
        `SELECT manufacturing_index FROM manufacturing_ipi 
         WHERE year = ? AND quarter = ?`,
        [year - 1, quarter]
      );

      if (lastYearData.length > 0 && lastYearData[0].manufacturing_index) {
        const lastYearIndex = lastYearData[0].manufacturing_index;
        if (Number(lastYearIndex) !== 0) {
          yoyChange = ((ipiData.manufacturing_index - lastYearIndex) / lastYearIndex) * 100;
        }
      }

      const quarterStartMonth = { Q1: '01', Q2: '04', Q3: '07', Q4: '10' };
      const referenceDate = `${year}-${quarterStartMonth[quarter]}-01`;

      // Insert Manufacturing IPI
      await connection.query(
        `INSERT INTO manufacturing_ipi 
         (year, quarter, quarter_name, reference_date, total_weight, 
          weighted_index_sum, manufacturing_index, mom_change, yoy_change, status, generated_on)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 'Completed',?)
         ON DUPLICATE KEY UPDATE
         total_weight=VALUES(total_weight),
         weighted_index_sum=VALUES(weighted_index_sum),
         manufacturing_index=VALUES(manufacturing_index),
         mom_change=VALUES(mom_change),
         yoy_change=VALUES(yoy_change),
         status=VALUES(status),
         generated_on = VALUES(generated_on)`,
        [
          year, quarter, quarter, referenceDate,
          ipiData.total_weight, ipiData.weighted_index_sum,
          ipiData.manufacturing_index, qoqChange, yoyChange , new Date()
        ]
      );


      // Update log
      await this.logRecord('manufacturing_ipi', year, quarter, 'Completed', logId, result.length, null, connection);

      await connection.commit();
      

      logger.info(`calculateManufacturingIPI - Manufacturing IPI calculated: ${ipiData.manufacturing_index}`);
      logger.info(`calculateManufacturingIPI - QoQ: ${qoqChange ? qoqChange.toFixed(2) + '%' : 'N/A'}`);
      logger.info(`calculateManufacturingIPI - YoY: ${yoyChange ? yoyChange.toFixed(2) + '%' : 'N/A'}`);

      return {
        success: true,
        index: ipiData.manufacturing_index,
        qoqChange,
        momChange: qoqChange,
        yoyChange
      };

    } catch (error) {
      await connection.rollback();
      console.error('Error calculating Manufacturing IPI:', error);
      logger.error(`calculateManufacturingIPI - Error calculating Manufacturing IPI: ${error}`);
      // Update log OUTSIDE the failed transaction
      if (logId) {
      await this.logRecord('manufacturing_ipi', year, quarter, 'Failed', logId, 0, error.message);
      }
      throw error;
    } finally {
      connection.release();
    }
  }


  // MASTER FUNCTION: Run Complete Calculation Pipeline
  async runCompleteCalculation(year, quarter) {

    logger.info(`**************************** Starting IPI Calculation for ${year}-${quarter} ****************************`);

    try {
      // Step 1: Aggregate quarterly production
      await this.aggregateQuarterlyProduction(year, quarter);

      // Step 2: Calculate item level indices
      await this.calculateItemLevelIndices(year, quarter);

      // Step 3: Calculate ISIC 4-digit indices
      await this.calculateISIC4DigitIndices(year, quarter);

      // Step 4: Calculate ISIC 3-digit indices
      await this.calculateISIC3DigitIndices(year, quarter);

      // Step 5: Calculate ISIC 2-digit indices
      await this.calculateISIC2DigitIndices(year, quarter);

      // Step 6: Calculate Manufacturing IPI
      const result = await this.calculateManufacturingIPI(year, quarter);

      logger.info(`**************************** IPI Calculation Completed Successfully ****************************`);


      return result;

    } catch (error) {
      console.error('Error in complete calculation:', error);
      logger.error(` Error in complete calculation: ${error}`);
      throw error;
    }
  }


// HELPER : calculation log
async logRecord(calculation_type, reference_year, reference_month = null, status = 'Started', logId = null, records_processed = 0, error_message = null, useConnection = null) {

  const connection = useConnection || await this.pool.getConnection();
  try {
    if (!logId) {
      const [result] = await connection.query(
        `INSERT INTO calculation_log (calculation_type, reference_year, reference_month, status)
         VALUES (?, ?, ?, ?)`,
        [calculation_type, reference_year, reference_month, status]
      );
      return result.insertId;
    }

    if(records_processed == 0)
    {
      status = 'Failed';
    }

    await connection.query(
      `UPDATE calculation_log
       SET status=?, records_processed=?, error_message=?, completed_at=NOW()
       WHERE id=?`,
      [status, records_processed, error_message?.substring(0,65535) || null, logId]
    );

  } catch (err) {
    console.error('[LOG ERROR]', err.message);
    logger.error(` Log record error: ${err.message} `);
  } finally {
    if (!useConnection) connection.release();
  }
}


  // HELPER: Verify Base Year Data
  async verifyBaseYearData(baseYear = 2022) {
    const connection = await this.pool.getConnection();
    
    try {

      logger.info(` Verifying Base Year ${baseYear} Data...`);
      
      // Count products
      const [countResult] = await connection.query(
        `SELECT COUNT(*) as count FROM base_year_production WHERE base_year = ?`,
        [baseYear]
      );
      
      // Get sample data
      const [sampleData] = await connection.query(
        `SELECT 
          product_id, 
          product_hs_code, 
          avg_by_production,
          q1, q2, q3, q4
         FROM base_year_production 
         WHERE base_year = ? 
         ORDER BY product_id 
         LIMIT 5`,
        [baseYear]
      );
      
      // Check for products with zero average
      const [zeroAvgResult] = await connection.query(
        `SELECT COUNT(*) as count 
         FROM base_year_production 
         WHERE base_year = ? AND (avg_by_production = 0 OR avg_by_production IS NULL)`,
        [baseYear]
      );

      logger.info(` Total products in base year: ${countResult[0].count}`);
      logger.info(` Products with zero/null average: ${zeroAvgResult[0].count}`);
      logger.info(` Sample data (first 5 products): ${sampleData} `);

      
      return {
        totalProducts: countResult[0].count,
        zeroAverageProducts: zeroAvgResult[0].count,
        sampleData: sampleData
      };
      
    } finally {
      connection.release();
    }
  }

  
  // HELPER: Clear All Calculated Data for a Period
  async clearCalculatedData(year, quarter) {
    const connection = await this.pool.getConnection();
    
    try {
      await connection.beginTransaction();
      
      logger.info(` Clearing calculated data for ${year}-${quarter} `);
      
      await connection.query(
        `DELETE FROM monthly_production WHERE year = ? AND quarter = ?`,
        [year, quarter]
      );
      
      await connection.query(
        `DELETE FROM item_level_indices WHERE year = ? AND quarter = ?`,
        [year, quarter]
      );
      
      await connection.query(
        `DELETE FROM isic_4digit_indices WHERE year = ? AND quarter = ?`,
        [year, quarter]
      );
      
      await connection.query(
        `DELETE FROM isic_3digit_indices WHERE year = ? AND quarter = ?`,
        [year, quarter]
      );
      
      await connection.query(
        `DELETE FROM isic_2digit_indices WHERE year = ? AND quarter = ?`,
        [year, quarter]
      );
      
      await connection.query(
        `DELETE FROM manufacturing_ipi WHERE year = ? AND quarter = ?`,
        [year, quarter]
      );
      
      await connection.commit();
      logger.info(` Data cleared for ${year}-${quarter} `);
      
      return { success: true };
      
    } catch (error) {
      await connection.rollback();
      console.error('Error clearing data:', error);
      logger.error(` Error clearing data: ${error} `);
      throw error;
    } finally {
      connection.release();
    }
  }

  // Close pool
  async close() {
    await this.pool.end();
  }
  
}




module.exports = IPICalculationService;