const db = require('../models');
const { Op } = require('sequelize');
const { generateNextNumber } = require('./numberFormatService');

// Default rates (can be moved to configuration or WorkCenter master)
const DEFAULT_LABOR_RATE_PER_HOUR = 100; // Default labor rate per hour
const DEFAULT_OVERHEAD_RATE_PER_HOUR = 50; // Default overhead rate per hour
const DEFAULT_SETUP_COST_PER_SETUP = 200; // Default setup cost

const toPositiveNumberOrNull = (value) => {
  if (value === null || value === undefined || value === '') return null;
  const parsed = parseFloat(value);
  if (!Number.isFinite(parsed) || parsed <= 0) return null;
  return parsed;
};

const toRateNumberOrNull = (value) => {
  if (value === null || value === undefined || value === '') return null;
  const parsed = parseFloat(value);
  if (!Number.isFinite(parsed) || parsed < 0) return null;
  return parsed;
};

const RATE_COMPARE_EPSILON = 0.0001;
const hasRateChanged = (bomRate, latestRate) => {
  if (latestRate === null) return false;
  return Math.abs(bomRate - latestRate) > RATE_COMPARE_EPSILON;
};

const getBestMaterialRate = (material, valuation, materialPlant, preferredSupplier, anySupplier) => {
  const directUnitCost = toPositiveNumberOrNull(material?.unit_cost);
  if (directUnitCost !== null) return directUnitCost;

  const valuationStandard = toPositiveNumberOrNull(valuation?.standard_price);
  if (valuationStandard !== null) return valuationStandard;

  const valuationMovingAvg = toPositiveNumberOrNull(valuation?.moving_average_price);
  if (valuationMovingAvg !== null) return valuationMovingAvg;

  const openingBalanceUnitCost = toPositiveNumberOrNull(materialPlant?.opening_balance_unit_cost);
  if (openingBalanceUnitCost !== null) return openingBalanceUnitCost;

  const preferredSupplierRate = toPositiveNumberOrNull(preferredSupplier?.standard_price);
  if (preferredSupplierRate !== null) return preferredSupplierRate;

  const anySupplierRate = toPositiveNumberOrNull(anySupplier?.standard_price);
  if (anySupplierRate !== null) return anySupplierRate;

  const lastPurchaseRate = toPositiveNumberOrNull(material?.last_purchase_rate);
  if (lastPurchaseRate !== null) return lastPurchaseRate;

  return 0;
};

const getBOMCacheKey = (companyId, materialId, plantId) => `${companyId}::${materialId}::${plantId || 'ALL'}`;

const getActiveBomByMaterial = async ({ companyId, materialId, plantId = null, bomCache }) => {
  const cacheKey = getBOMCacheKey(companyId, materialId, plantId);
  if (bomCache.has(cacheKey)) return bomCache.get(cacheKey);

  const where = {
    company_id: companyId,
    material_id: materialId,
    status: 'ACTIVE'
  };
  if (plantId) where.plant_id = plantId;

  const bomHeader = await db.BOMHeader.findOne({
    where,
    include: [
      {
        model: db.BOMComponent,
        as: 'components',
        where: { status: 'ACTIVE' },
        required: false,
        include: [
          {
            model: db.Material,
            as: 'material',
            attributes: ['id', 'material_number', 'description', 'drawing_number', 'unit_cost', 'last_purchase_rate']
          },
          {
            model: db.UOM,
            as: 'componentUOM',
            attributes: ['id', 'uom_code', 'uom_name']
          }
        ]
      }
    ],
    order: [[{ model: db.BOMComponent, as: 'components' }, 'item_number', 'ASC']]
  });

  bomCache.set(cacheKey, bomHeader || null);
  return bomHeader || null;
};

const explodeBomComponents = async ({
  companyId,
  plantId = null,
  materialId,
  requiredQty,
  level = 0,
  parentMaterialId = null,
  bomCache,
  recursionStack
}) => {
  const bomHeader = await getActiveBomByMaterial({ companyId, materialId, plantId, bomCache });
  if (!bomHeader) return [];

  if (recursionStack.has(bomHeader.id)) {
    return [];
  }

  recursionStack.add(bomHeader.id);
  const components = Array.isArray(bomHeader.components) ? bomHeader.components : [];
  const baseQty = parseFloat(bomHeader.base_quantity || 1) || 1;
  const multiplier = (parseFloat(requiredQty || 0) || 0) / baseQty;
  const explodedLines = [];

  for (const component of components) {
    const componentQty = parseFloat(component.component_quantity || 0) || 0;
    const lineRequiredQty = componentQty * multiplier;

    const childBom = await getActiveBomByMaterial({
      companyId,
      materialId: component.material_id,
      plantId,
      bomCache
    });
    const hasChildBom = Boolean(childBom);

    explodedLines.push({
      bom_id: bomHeader.id,
      bom_number: bomHeader.bom_number,
      bom_component_id: component.id,
      item_number: component.item_number,
      material_id: component.material_id,
      quantity: lineRequiredQty,
      level,
      parent_material_id: parentMaterialId,
      has_child_bom: hasChildBom,
      material: component.material || null,
      componentUOM: component.componentUOM || null,
      bom_rate: component.rate
    });

    if (hasChildBom) {
      const childLines = await explodeBomComponents({
        companyId,
        plantId,
        materialId: component.material_id,
        requiredQty: lineRequiredQty,
        level: level + 1,
        parentMaterialId: materialId,
        bomCache,
        recursionStack
      });
      explodedLines.push(...childLines);
    }
  }

  recursionStack.delete(bomHeader.id);
  return explodedLines;
};

/**
 * Calculate standard cost for a Production Order
 * Based on BOM material costs and routing labor/overhead
 * 
 * @param {Object} productionOrder - Production Order object
 * @param {Integer} userId - User ID
 * @param {Object} transaction - Sequelize transaction (optional)
 * @returns {Promise<Object>} ProductionCost record
 */
exports.calculateStandardCost = async (productionOrder, userId, transaction = null) => {
  const options = transaction ? { transaction } : {};
  
  // Get Production Order with all related data
  const po = await db.ProductionOrder.findByPk(productionOrder.id, {
    include: [
      {
        model: db.ProductionOrderComponent,
        as: 'components',
        include: [
          {
            model: db.Material,
            as: 'material',
            include: [
              {
                model: db.MaterialValuation,
                as: 'valuations',
                where: {
                  plant_id: productionOrder.plant_id,
                  status: 'ACTIVE'
                },
                required: false
              }
            ]
          }
        ]
      },
      {
        model: db.Routing,
        as: 'routing',
        include: [
          {
            model: db.RoutingOperation,
            as: 'operations',
            where: { status: 'ACTIVE' },
            required: false,
            include: [
              {
                model: db.WorkCenter,
                as: 'workCenter'
              }
            ]
          }
        ],
        required: false
      },
      {
        model: db.Material,
        as: 'finishedMaterial'
      }
    ],
    ...options
  });

  if (!po) {
    throw new Error('Production Order not found');
  }

  const orderQuantity = parseFloat(po.order_quantity || 0);
  let materialCost = 0;
  let laborCost = 0;
  let overheadCost = 0;
  let setupCost = 0;

  // Calculate material cost from BOM components
  if (po.components && po.components.length > 0) {
    for (const component of po.components) {
      const requiredQty = parseFloat(component.required_quantity || 0);
      
      // Get material valuation (standard price)
      let unitPrice = 0;
      if (component.material && component.material.valuations && component.material.valuations.length > 0) {
        const valuation = component.material.valuations[0];
        unitPrice = parseFloat(valuation.standard_price || valuation.moving_average_price || 0);
      }

      const componentCost = requiredQty * unitPrice;
      materialCost += componentCost;
    }
  }

  // Calculate labor and overhead cost from routing
  if (po.routing && po.routing.operations && po.routing.operations.length > 0) {
    const baseQuantity = parseFloat(po.routing.base_quantity || 1);
    const quantityRatio = orderQuantity / baseQuantity;

    for (const operation of po.routing.operations) {
      const setupTime = parseInt(operation.setup_time || 0);
      const laborTime = parseFloat(operation.labor_time || 0);
      const machineTime = parseFloat(operation.machine_time || 0);

      // Calculate labor cost
      const totalLaborTime = (laborTime * quantityRatio) + (setupTime / 60); // Convert setup minutes to hours
      const laborRate = DEFAULT_LABOR_RATE_PER_HOUR; // TODO: Get from WorkCenter or configuration
      laborCost += totalLaborTime * laborRate;

      // Calculate overhead cost (based on machine time or labor time)
      const totalMachineTime = machineTime * quantityRatio;
      const overheadRate = DEFAULT_OVERHEAD_RATE_PER_HOUR; // TODO: Get from WorkCenter or configuration
      overheadCost += Math.max(totalLaborTime, totalMachineTime) * overheadRate;

      // Calculate setup cost
      if (setupTime > 0) {
        setupCost += DEFAULT_SETUP_COST_PER_SETUP; // TODO: Get from WorkCenter or configuration
      }
    }
  }

  const totalCost = materialCost + laborCost + overheadCost + setupCost;
  const unitCost = orderQuantity > 0 ? totalCost / orderQuantity : 0;

  // Get currency from finished material valuation or default
  let currencyId = null;
  if (po.finishedMaterial) {
    const finishedMaterialValuation = await db.MaterialValuation.findOne({
      where: {
        material_id: po.finished_material_id,
        plant_id: po.plant_id,
        status: 'ACTIVE'
      },
      ...options
    });
    if (finishedMaterialValuation) {
      currencyId = finishedMaterialValuation.currency_id;
    }
  }

  // Get cost center from work center or production order
  let costCenterId = null;
  if (po.routing && po.routing.operations && po.routing.operations.length > 0) {
    const firstOperation = po.routing.operations[0];
    if (firstOperation.workCenter && firstOperation.workCenter.cost_center_id) {
      costCenterId = firstOperation.workCenter.cost_center_id;
    }
  }

  // Create or update standard cost record
  const [costRecord, created] = await db.ProductionCost.findOrCreate({
    where: {
      production_order_id: po.id,
      cost_type: 'STANDARD'
    },
    defaults: {
      company_id: po.company_id,
      plant_id: po.plant_id,
      production_order_id: po.id,
      cost_type: 'STANDARD',
      material_cost: materialCost,
      labor_cost: laborCost,
      overhead_cost: overheadCost,
      setup_cost: setupCost,
      total_cost: totalCost,
      quantity: orderQuantity,
      unit_cost: unitCost,
      currency_id: currencyId,
      cost_center_id: costCenterId,
      calculated_by: userId
    },
    ...options
  });

  if (!created) {
    await costRecord.update({
      material_cost: materialCost,
      labor_cost: laborCost,
      overhead_cost: overheadCost,
      setup_cost: setupCost,
      total_cost: totalCost,
      quantity: orderQuantity,
      unit_cost: unitCost,
      currency_id: currencyId,
      cost_center_id: costCenterId,
      calculated_by: userId,
      calculated_at: new Date()
    }, options);
  }

  return costRecord;
};

/**
 * Calculate actual cost for a Production Order
 * Based on actual material issued and actual labor from routing executions
 * 
 * @param {Object} productionOrder - Production Order object
 * @param {Integer} userId - User ID
 * @param {Object} transaction - Sequelize transaction (optional)
 * @returns {Promise<Object>} ProductionCost record
 */
exports.calculateActualCost = async (productionOrder, userId, transaction = null) => {
  const options = transaction ? { transaction } : {};
  
  // Get Production Order with all related data
  const po = await db.ProductionOrder.findByPk(productionOrder.id, {
    include: [
      {
        model: db.ProductionOrderComponent,
        as: 'components',
        include: [
          {
            model: db.Material,
            as: 'material'
          }
        ]
      }
    ],
    ...options
  });

  if (!po) {
    throw new Error('Production Order not found');
  }

  const confirmedQuantity = parseFloat(po.confirmed_quantity || 0);
  let materialCost = 0;
  let laborCost = 0;
  let overheadCost = 0;
  let setupCost = 0;

  // Calculate actual material cost from issued materials
  if (po.components && po.components.length > 0) {
    for (const component of po.components) {
      const issuedQty = parseFloat(component.issued_quantity || 0);
      
      // Get actual material cost from Material Issue
      const materialIssues = await db.MaterialIssue.findAll({
        where: {
          production_order_id: po.id,
          status: 'POSTED'
        },
        include: [
          {
            model: db.MaterialIssueItem,
            as: 'items',
            where: {
              material_id: component.material_id
            },
            required: false,
            include: [
              {
                model: db.Material,
                as: 'material',
                include: [
                  {
                    model: db.MaterialValuation,
                    as: 'valuations',
                    where: {
                      plant_id: po.plant_id,
                      status: 'ACTIVE'
                    },
                    required: false
                  }
                ]
              }
            ]
          }
        ],
        ...options
      });

      for (const materialIssue of materialIssues) {
        if (materialIssue.items && materialIssue.items.length > 0) {
          for (const item of materialIssue.items) {
            // Use total_amount if available, otherwise calculate from unit_price and quantity
            const itemCost = parseFloat(item.total_amount || 0);
            if (itemCost > 0) {
              materialCost += itemCost;
            } else {
              const quantity = parseFloat(item.quantity || 0);
              const unitPrice = parseFloat(item.unit_price || 0);
              materialCost += quantity * unitPrice;
            }
          }
        }
      }
    }
  }

  // Calculate actual labor and overhead cost from routing executions
  const routingExecutions = await db.ProductionOrderRoutingExecution.findAll({
    where: {
      production_order_id: po.id,
      status: { [Op.in]: ['COMPLETED', 'IN_PROGRESS'] }
    },
    include: [
      {
        model: db.RoutingOperation,
        as: 'routingOperation',
        include: [
          {
            model: db.WorkCenter,
            as: 'workCenter'
          }
        ]
      }
    ],
    ...options
  });

  for (const execution of routingExecutions) {
    const actualLaborTime = parseFloat(execution.actual_labor_time || 0);
    const actualMachineTime = parseFloat(execution.actual_machine_time || 0);
    const actualSetupTime = parseFloat(execution.actual_setup_time || 0);

    // Calculate labor cost
    const laborRate = DEFAULT_LABOR_RATE_PER_HOUR;
    laborCost += (actualLaborTime + (actualSetupTime / 60)) * laborRate;

    // Calculate overhead cost
    const overheadRate = DEFAULT_OVERHEAD_RATE_PER_HOUR;
    overheadCost += Math.max(actualLaborTime, actualMachineTime) * overheadRate;

    // Calculate setup cost
    if (actualSetupTime > 0) {
      setupCost += DEFAULT_SETUP_COST_PER_SETUP;
    }
  }

  const totalCost = materialCost + laborCost + overheadCost + setupCost;
  const unitCost = confirmedQuantity > 0 ? totalCost / confirmedQuantity : 0;

  // Get currency from finished material valuation
  let currencyId = null;
  if (po.finished_material_id) {
    const finishedMaterialValuation = await db.MaterialValuation.findOne({
      where: {
        material_id: po.finished_material_id,
        plant_id: po.plant_id,
        status: 'ACTIVE'
      },
      ...options
    });
    if (finishedMaterialValuation) {
      currencyId = finishedMaterialValuation.currency_id;
    }
  }

  // Get cost center
  let costCenterId = null;
  if (routingExecutions.length > 0 && routingExecutions[0].routingOperation) {
    const workCenter = routingExecutions[0].routingOperation.workCenter;
    if (workCenter && workCenter.cost_center_id) {
      costCenterId = workCenter.cost_center_id;
    }
  }

  // Create or update actual cost record
  const [costRecord, created] = await db.ProductionCost.findOrCreate({
    where: {
      production_order_id: po.id,
      cost_type: 'ACTUAL'
    },
    defaults: {
      company_id: po.company_id,
      plant_id: po.plant_id,
      production_order_id: po.id,
      cost_type: 'ACTUAL',
      material_cost: materialCost,
      labor_cost: laborCost,
      overhead_cost: overheadCost,
      setup_cost: setupCost,
      total_cost: totalCost,
      quantity: confirmedQuantity,
      unit_cost: unitCost,
      currency_id: currencyId,
      cost_center_id: costCenterId,
      calculated_by: userId
    },
    ...options
  });

  if (!created) {
    await costRecord.update({
      material_cost: materialCost,
      labor_cost: laborCost,
      overhead_cost: overheadCost,
      setup_cost: setupCost,
      total_cost: totalCost,
      quantity: confirmedQuantity,
      unit_cost: unitCost,
      currency_id: currencyId,
      cost_center_id: costCenterId,
      calculated_by: userId,
      calculated_at: new Date()
    }, options);
  }

  return costRecord;
};

/**
 * Calculate cost variance between standard and actual costs
 * 
 * @param {Object} productionOrder - Production Order object
 * @param {Integer} userId - User ID
 * @param {Object} transaction - Sequelize transaction (optional)
 * @returns {Promise<Object>} ProductionCostVariance record
 */
exports.calculateCostVariance = async (productionOrder, userId, transaction = null) => {
  const options = transaction ? { transaction } : {};
  
  // Get standard and actual costs
  const standardCost = await db.ProductionCost.findOne({
    where: {
      production_order_id: productionOrder.id,
      cost_type: 'STANDARD'
    },
    ...options
  });

  const actualCost = await db.ProductionCost.findOne({
    where: {
      production_order_id: productionOrder.id,
      cost_type: 'ACTUAL'
    },
    ...options
  });

  if (!standardCost) {
    throw new Error('Standard cost not found. Please calculate standard cost first.');
  }

  if (!actualCost) {
    throw new Error('Actual cost not found. Please calculate actual cost first.');
  }

  // Calculate variances
  const materialVariance = parseFloat(actualCost.material_cost) - parseFloat(standardCost.material_cost);
  const laborVariance = parseFloat(actualCost.labor_cost) - parseFloat(standardCost.labor_cost);
  const overheadVariance = parseFloat(actualCost.overhead_cost) - parseFloat(standardCost.overhead_cost);
  const setupVariance = parseFloat(actualCost.setup_cost) - parseFloat(standardCost.setup_cost);
  const totalVariance = parseFloat(actualCost.total_cost) - parseFloat(standardCost.total_cost);

  // Calculate variance percentage
  const variancePercentage = parseFloat(standardCost.total_cost) > 0
    ? (totalVariance / parseFloat(standardCost.total_cost)) * 100
    : 0;

  // Determine variance type
  let varianceType = 'NEUTRAL';
  if (totalVariance < 0) {
    varianceType = 'FAVORABLE'; // Actual cost is less than standard (good)
  } else if (totalVariance > 0) {
    varianceType = 'UNFAVORABLE'; // Actual cost is more than standard (bad)
  }

  // Use confirmed quantity for variance calculation
  const quantity = parseFloat(actualCost.quantity || standardCost.quantity || 0);
  const unitVariance = quantity > 0 ? totalVariance / quantity : 0;

  // Create or update variance record
  const [varianceRecord, created] = await db.ProductionCostVariance.findOrCreate({
    where: {
      production_order_id: productionOrder.id
    },
    defaults: {
      company_id: productionOrder.company_id,
      plant_id: productionOrder.plant_id,
      production_order_id: productionOrder.id,
      standard_cost_id: standardCost.id,
      actual_cost_id: actualCost.id,
      material_variance: materialVariance,
      labor_variance: laborVariance,
      overhead_variance: overheadVariance,
      setup_variance: setupVariance,
      total_variance: totalVariance,
      variance_percentage: variancePercentage,
      variance_type: varianceType,
      quantity: quantity,
      unit_variance: unitVariance,
      currency_id: standardCost.currency_id,
      calculated_by: userId
    },
    ...options
  });

  if (!created) {
    await varianceRecord.update({
      standard_cost_id: standardCost.id,
      actual_cost_id: actualCost.id,
      material_variance: materialVariance,
      labor_variance: laborVariance,
      overhead_variance: overheadVariance,
      setup_variance: setupVariance,
      total_variance: totalVariance,
      variance_percentage: variancePercentage,
      variance_type: varianceType,
      quantity: quantity,
      unit_variance: unitVariance,
      currency_id: standardCost.currency_id,
      calculated_by: userId,
      calculated_at: new Date()
    }, options);
  }

  return varianceRecord;
};

/**
 * Get production cost for a Production Order
 * 
 * @param {Integer} productionOrderId - Production Order ID
 * @param {String} costType - Cost type (STANDARD, ACTUAL, or null for all)
 * @param {Object} transaction - Sequelize transaction (optional)
 * @returns {Promise<Array|Object>} ProductionCost record(s)
 */
exports.getProductionCost = async (productionOrderId, costType = null, transaction = null) => {
  const options = transaction ? { transaction } : {};
  
  const where = {
    production_order_id: productionOrderId
  };

  if (costType) {
    where.cost_type = costType;
  }

  const costs = await db.ProductionCost.findAll({
    where,
    include: [
      { model: db.ProductionOrder, as: 'productionOrder' },
      { model: db.Currency, as: 'currency' },
      { model: db.CostCenter, as: 'costCenter' }
    ],
    order: [['cost_type', 'ASC']],
    ...options
  });

  return costType ? costs[0] : costs;
};

/**
 * Get cost variance for a Production Order
 * 
 * @param {Integer} productionOrderId - Production Order ID
 * @param {Object} transaction - Sequelize transaction (optional)
 * @returns {Promise<Object>} ProductionCostVariance record
 */
exports.getCostVariance = async (productionOrderId, transaction = null) => {
  const options = transaction ? { transaction } : {};
  
  return await db.ProductionCostVariance.findOne({
    where: {
      production_order_id: productionOrderId
    },
    include: [
      { model: db.ProductionOrder, as: 'productionOrder' },
      { model: db.ProductionCost, as: 'standardCost' },
      { model: db.ProductionCost, as: 'actualCost' },
      { model: db.Currency, as: 'currency' }
    ],
    ...options
  });
};

/**
 * Estimate production cost from Finished Good.
 * - Material cost from BOM components
 * - Operation cost from Cost Center (mapped via Routing operation)
 *
 * @param {Object} params
 * @param {Number} params.companyId
 * @param {Number} params.materialId
 * @param {Number|null} params.plantId
 * @returns {Promise<Object>}
 */
exports.estimateCostByFinishedGood = async ({ companyId, materialId, plantId = null }) => {
  const material = await db.Material.findOne({
    where: {
      id: materialId,
      company_id: companyId,
      status: { [Op.ne]: 'DELETED' }
    },
    attributes: ['id', 'material_number', 'description', 'drawing_number', 'base_uom_id']
  });

  if (!material) {
    throw new Error('Finished good material not found');
  }

  const bomCache = new Map();
  const bomHeader = await getActiveBomByMaterial({ companyId, materialId, plantId, bomCache });

  const routingWhere = {
    company_id: companyId,
    material_id: materialId,
    status: 'ACTIVE'
  };
  if (plantId) routingWhere.plant_id = plantId;

  const routing = await db.Routing.findOne({
    where: routingWhere,
    include: [
      {
        model: db.RoutingOperation,
        as: 'operations',
        where: { status: 'ACTIVE' },
        required: false,
        attributes: ['id', 'operation_number', 'operation_code', 'operation_description', 'work_center_id']
      }
    ],
    order: [[{ model: db.RoutingOperation, as: 'operations' }, 'operation_number', 'ASC']]
  });

  const explodedBOMLines = bomHeader
    ? await explodeBomComponents({
        companyId,
        plantId,
        materialId,
        requiredQty: parseFloat(bomHeader.base_quantity || 1) || 1,
        level: 0,
        parentMaterialId: null,
        bomCache,
        recursionStack: new Set()
      })
    : [];

  const componentMaterialIds = [...new Set(
    explodedBOMLines
      .map((line) => Number(line.material_id))
      .filter((id) => Number.isInteger(id) && id > 0)
  )];

  const valuations = componentMaterialIds.length > 0
    ? await db.MaterialValuation.findAll({
        where: {
          material_id: { [Op.in]: componentMaterialIds },
          ...(plantId ? { plant_id: plantId } : {}),
          status: 'ACTIVE'
        },
        attributes: ['material_id', 'standard_price', 'moving_average_price', 'plant_id'],
        order: [['id', 'DESC']]
      })
    : [];

  const materialPlants = componentMaterialIds.length > 0
    ? await db.MaterialPlant.findAll({
        where: {
          material_id: { [Op.in]: componentMaterialIds },
          ...(plantId ? { plant_id: plantId } : {}),
          status: 'ACTIVE'
        },
        attributes: ['material_id', 'opening_balance_unit_cost', 'plant_id'],
        order: [['id', 'DESC']]
      })
    : [];

  const suppliers = componentMaterialIds.length > 0
    ? await db.MaterialSupplier.findAll({
        where: {
          material_id: { [Op.in]: componentMaterialIds },
          status: 'ACTIVE'
        },
        attributes: ['material_id', 'standard_price', 'is_preferred'],
        order: [['is_preferred', 'DESC'], ['id', 'DESC']]
      })
    : [];

  const valuationMap = new Map();
  valuations.forEach((row) => {
    if (!valuationMap.has(Number(row.material_id))) valuationMap.set(Number(row.material_id), row);
  });

  const materialPlantMap = new Map();
  materialPlants.forEach((row) => {
    if (!materialPlantMap.has(Number(row.material_id))) materialPlantMap.set(Number(row.material_id), row);
  });

  const preferredSupplierMap = new Map();
  const anySupplierMap = new Map();
  suppliers.forEach((row) => {
    const materialKey = Number(row.material_id);
    if (!anySupplierMap.has(materialKey)) anySupplierMap.set(materialKey, row);
    if (row.is_preferred && !preferredSupplierMap.has(materialKey)) {
      preferredSupplierMap.set(materialKey, row);
    }
  });

  let totalMaterialCost = 0;
  let changedRateCount = 0;
  const materialCostLines = explodedBOMLines.map((line, index) => {
    const quantity = parseFloat(line.quantity || 0) || 0;
    const bomRateRaw = toRateNumberOrNull(line.bom_rate);
    const bomRate = bomRateRaw !== null ? bomRateRaw : 0;
    const materialInfo = line.material || null;

    const latestRate = getBestMaterialRate(
      materialInfo,
      valuationMap.get(Number(line.material_id)),
      materialPlantMap.get(Number(line.material_id)),
      preferredSupplierMap.get(Number(line.material_id)),
      anySupplierMap.get(Number(line.material_id))
    );
    const hasRateDifference = hasRateChanged(bomRate, latestRate);
    if (hasRateDifference) changedRateCount += 1;

    // Always keep BOM rate as costing baseline until user explicitly updates.
    const rate = bomRate;

    // If component is a sub-assembly with its own BOM, cost comes from exploded child lines.
    const amount = line.has_child_bom ? 0 : quantity * rate;
    totalMaterialCost += amount;

    return {
      row_key: `${line.bom_component_id}-${index}`,
      bom_id: line.bom_id,
      bom_number: line.bom_number,
      bom_component_id: line.bom_component_id,
      item_number: line.item_number,
      material_id: line.material_id,
      material_number: materialInfo?.material_number || '',
      material_description: materialInfo?.description || '',
      drawing_number: materialInfo?.drawing_number || '',
      level: line.level,
      has_child_bom: line.has_child_bom,
      quantity,
      uom: line.componentUOM?.uom_code || '',
      bom_rate: bomRate,
      latest_rate: latestRate,
      has_rate_change: hasRateDifference,
      rate,
      amount
    };
  });

  const routingOperations = Array.isArray(routing?.operations) ? routing.operations : [];
  const operationCodes = [...new Set(
    routingOperations
      .map((operation) => String(operation.operation_code || '').trim())
      .filter(Boolean)
  )];

  const operationMasters = operationCodes.length > 0
    ? await db.OperationName.findAll({
        where: {
          company_id: companyId,
          operation_code: { [Op.in]: operationCodes },
          status: 'ACTIVE'
        },
        attributes: ['id', 'operation_code', 'description']
      })
    : [];

  const operationCodeMap = new Map();
  operationMasters.forEach((operationMaster) => {
    operationCodeMap.set(String(operationMaster.operation_code).trim(), operationMaster);
  });

  const operationMasterIds = operationMasters.map((operationMaster) => operationMaster.id);
  const costCenters = operationMasterIds.length > 0
    ? await db.CostCenter.findAll({
        where: {
          company_id: companyId,
          operation_name_id: { [Op.in]: operationMasterIds },
          status: 'ACTIVE'
        },
        attributes: ['id', 'cost_center_code', 'description', 'operation_name_id', 'cost_per_hour', 'standard_time']
      })
    : [];

  const operationCostCenterMap = new Map();
  costCenters.forEach((costCenter) => {
    const operationKey = Number(costCenter.operation_name_id);
    if (!operationCostCenterMap.has(operationKey)) operationCostCenterMap.set(operationKey, costCenter);
  });

  let totalOperationCost = 0;
  const operationCostLines = routingOperations.map((operation) => {
    const operationCode = String(operation.operation_code || '').trim();
    const operationMaster = operationCodeMap.get(operationCode) || null;
    const linkedCostCenter = operationMaster
      ? operationCostCenterMap.get(Number(operationMaster.id)) || null
      : null;

    const costPerHour = parseFloat(linkedCostCenter?.cost_per_hour || 0) || 0;
    const standardTime = parseFloat(linkedCostCenter?.standard_time || 0) || 0;
    const amount = costPerHour * standardTime;
    totalOperationCost += amount;

    return {
      routing_operation_id: operation.id,
      operation_number: operation.operation_number,
      operation_code: operationCode,
      operation_description: operation.operation_description || operationMaster?.description || '',
      cost_center_id: linkedCostCenter?.id || null,
      cost_center_code: linkedCostCenter?.cost_center_code || '',
      cost_center_description: linkedCostCenter?.description || '',
      cost_per_hour: costPerHour,
      standard_time: standardTime,
      amount
    };
  });

  const totalProductionCost = totalMaterialCost + totalOperationCost;

  return {
    finished_good: {
      id: material.id,
      material_number: material.material_number,
      description: material.description,
      drawing_number: material.drawing_number || ''
    },
    bom: bomHeader
      ? {
          id: bomHeader.id,
          bom_number: bomHeader.bom_number,
          alternative: bomHeader.alternative,
          base_quantity: parseFloat(bomHeader.base_quantity || 0) || 0
        }
      : null,
    routing: routing
      ? {
          id: routing.id,
          routing_number: routing.routing_number,
          alternative: routing.alternative,
          base_quantity: parseFloat(routing.base_quantity || 0) || 0
        }
      : null,
    material_cost_lines: materialCostLines,
    rate_comparison: {
      has_changes: changedRateCount > 0,
      changed_components: changedRateCount,
      total_components: materialCostLines.length
    },
    operation_cost_lines: operationCostLines,
    totals: {
      material_cost: totalMaterialCost,
      operation_cost: totalOperationCost,
      total_production_cost: totalProductionCost
    }
  };
};

/**
 * Save estimated production costing in dedicated table.
 *
 * @param {Object} params
 * @param {Number} params.companyId
 * @param {Number} params.materialId
 * @param {Number|null} params.plantId
 * @param {Number|null} params.userId
 * @param {String|null} params.remarks
 * @returns {Promise<Object>}
 */
exports.saveEstimatedCostByFinishedGood = async ({
  companyId,
  materialId,
  plantId = null,
  userId = null,
  remarks = null
}) => {
  const estimation = await exports.estimateCostByFinishedGood({ companyId, materialId, plantId });
  let productionCostingNumber = null;

  try {
    productionCostingNumber = await generateNextNumber(companyId, 'PRODUCTION_COSTING');
  } catch (error) {
    const year = new Date().getFullYear();
    const prefix = `PC-${year}`;
    const latest = await db.ProductionCosting.findOne({
      where: {
        company_id: companyId,
        production_costing_number: { [Op.like]: `${prefix}-%` }
      },
      order: [['id', 'DESC']],
      attributes: ['production_costing_number']
    });

    const latestSeq = latest?.production_costing_number
      ? parseInt(String(latest.production_costing_number).split('-').pop(), 10) || 0
      : 0;
    productionCostingNumber = `${prefix}-${String(latestSeq + 1).padStart(5, '0')}`;
  }

  const currencyId = await (async () => {
    if (plantId) {
      const valuation = await db.MaterialValuation.findOne({
        where: {
          material_id: materialId,
          plant_id: plantId,
          status: 'ACTIVE'
        },
        attributes: ['currency_id'],
        order: [['id', 'DESC']]
      });
      if (valuation?.currency_id) return valuation.currency_id;
    }
    return null;
  })();

  const saved = await db.ProductionCosting.create({
    company_id: companyId,
    plant_id: plantId || null,
    finished_material_id: materialId,
    production_costing_number: productionCostingNumber,
    bom_id: estimation?.bom?.id || null,
    routing_id: estimation?.routing?.id || null,
    material_cost: estimation?.totals?.material_cost || 0,
    operation_cost: estimation?.totals?.operation_cost || 0,
    total_production_cost: estimation?.totals?.total_production_cost || 0,
    material_cost_lines_json: JSON.stringify(estimation?.material_cost_lines || []),
    operation_cost_lines_json: JSON.stringify(estimation?.operation_cost_lines || []),
    currency_id: currencyId,
    remarks: remarks || null,
    estimated_at: new Date(),
    estimated_by: userId || null
  });

  return {
    id: saved.id,
    production_costing_number: saved.production_costing_number,
    estimated_at: saved.estimated_at,
    totals: estimation?.totals || {
      material_cost: 0,
      operation_cost: 0,
      total_production_cost: 0
    }
  };
};

exports.updateBomRatesWithLatestByFinishedGood = async ({
  companyId,
  materialId,
  plantId = null,
  userId = null
}) => {
  const estimationBeforeUpdate = await exports.estimateCostByFinishedGood({ companyId, materialId, plantId });
  const changedLines = Array.isArray(estimationBeforeUpdate?.material_cost_lines)
    ? estimationBeforeUpdate.material_cost_lines.filter((line) => (
        line?.has_rate_change
        && Number.isInteger(Number(line?.bom_component_id))
        && Number(line?.bom_component_id) > 0
        && toRateNumberOrNull(line?.latest_rate) !== null
      ))
    : [];

  if (changedLines.length === 0) {
    return {
      updated_components: 0,
      estimation: estimationBeforeUpdate
    };
  }

  const uniqueByComponent = new Map();
  changedLines.forEach((line) => {
    uniqueByComponent.set(Number(line.bom_component_id), toRateNumberOrNull(line.latest_rate));
  });

  await db.sequelize.transaction(async (transaction) => {
    for (const [componentId, latestRate] of uniqueByComponent.entries()) {
      if (latestRate === null) continue;
      await db.BOMComponent.update(
        {
          rate: latestRate,
          ...(userId ? { updated_by: userId } : {})
        },
        {
          where: {
            id: componentId,
            status: 'ACTIVE'
          },
          transaction
        }
      );
    }
  });

  const refreshedEstimation = await exports.estimateCostByFinishedGood({ companyId, materialId, plantId });
  return {
    updated_components: uniqueByComponent.size,
    estimation: refreshedEstimation
  };
};

exports.getSavedEstimatedCostings = async ({
  companyId,
  plantId = null,
  userId = null,
  page = 1,
  limit = 25,
  search = ''
}) => {
  const pageNum = Math.max(parseInt(page, 10) || 1, 1);
  const limitNum = Math.max(parseInt(limit, 10) || 25, 1);
  const offset = (pageNum - 1) * limitNum;

  const where = { company_id: companyId };
  if (plantId) where.plant_id = plantId;

  if (search && String(search).trim()) {
    where[Op.or] = [
      { production_costing_number: { [Op.like]: `%${search}%` } },
      { '$finishedMaterial.material_number$': { [Op.like]: `%${search}%` } },
      { '$finishedMaterial.description$': { [Op.like]: `%${search}%` } }
    ];
  }

  const { count, rows } = await db.ProductionCosting.findAndCountAll({
    where,
    include: [
      {
        model: db.Material,
        as: 'finishedMaterial',
        attributes: ['id', 'material_number', 'description', 'drawing_number']
      },
      {
        model: db.Plant,
        as: 'plant',
        attributes: ['id', 'code', 'name'],
        required: false
      }
    ],
    order: [['estimated_at', 'DESC'], ['id', 'DESC']],
    limit: limitNum,
    offset,
    distinct: true,
    subQuery: false
  });

  const ensureRateChangeNotification = async (item, changedLines) => {
    if (!userId || !item?.finished_material_id || !Array.isArray(changedLines) || changedLines.length === 0) return;

    const materialId = Number(item.finished_material_id);
    if (!Number.isInteger(materialId) || materialId <= 0) return;

    const existingUnread = await db.Notification.findOne({
      where: {
        company_id: companyId,
        user_id: userId,
        module_name: 'ProductionCostingRateChange',
        transaction_id: materialId,
        type: 'WARNING',
        is_read: false
      },
      attributes: ['id']
    });
    if (existingUnread) return;

    const firstChanged = changedLines[0];
    const oldRate = parseFloat(firstChanged?.bom_rate || 0) || 0;
    const newRate = parseFloat(firstChanged?.latest_rate || 0) || 0;
    const materialNumber = String(item?.finishedMaterial?.material_number || '').trim();
    const costingNumber = String(item?.production_costing_number || '').trim();

    await db.Notification.create({
      company_id: companyId,
      user_id: userId,
      type: 'WARNING',
      module_name: 'ProductionCostingRateChange',
      transaction_id: materialId,
      transaction_number: costingNumber || null,
      title: 'Production Costing Rate Change',
      message: `${materialNumber || 'Finished Good'} has ${changedLines.length} BOM material rate change(s). Example: Old Rs ${oldRate.toFixed(2)} -> New Rs ${newRate.toFixed(2)}.`,
      link: `/production/costing/new?material_id=${materialId}&source=notification`,
      priority: 'HIGH',
      created_by: userId,
      updated_by: userId
    });
  };

  const enrichedItems = await Promise.all((rows || []).map(async (row) => {
    const item = row.toJSON ? row.toJSON() : row;
    try {
      const currentEstimation = await exports.estimateCostByFinishedGood({
        companyId,
        materialId: item.finished_material_id,
        plantId: item.plant_id || null
      });
      const changedLines = Array.isArray(currentEstimation?.material_cost_lines)
        ? currentEstimation.material_cost_lines.filter((line) => line?.has_rate_change)
        : [];
      item.current_rate_change_count = changedLines.length;
      item.has_current_rate_changes = changedLines.length > 0;
      item.current_rate_change_details = changedLines.slice(0, 5).map((line) => {
        const oldRate = parseFloat(line?.bom_rate || 0) || 0;
        const newRate = parseFloat(line?.latest_rate || 0) || 0;
        return {
          material_number: line?.material_number || '',
          material_description: line?.material_description || '',
          old_rate: oldRate,
          new_rate: newRate
        };
      });

      await ensureRateChangeNotification(item, changedLines);
    } catch (error) {
      item.current_rate_change_count = 0;
      item.has_current_rate_changes = false;
      item.current_rate_change_details = [];
    }
    return item;
  }));

  return {
    items: enrichedItems,
    totalItems: count,
    totalPages: Math.ceil(count / limitNum),
    currentPage: pageNum
  };
};

