/**
 * Production Order Service
 * Business logic for production order management
 */

// Import ProductionOrder and related models (variants removed - using fg_product_id)
const { ProductionOrder, BOM, BOMItem, Product, Inventory, InventoryItem, InventoryMovement, RMCuttingOperation, RMInventoryPiece } = require('../../../models');
// Import inventory service
const inventoryService = require('../../inventory/services');
const inventoryItemsService = require('../../inventory/services/items');
// Import BOM service
const bomService = require('./boms');
// Import dimension-based services
const materialAllocationService = require('../../../services/materialAllocationService');
const bomDimensionService = require('../../../services/bomDimensionService');
const cuttingOperationsService = require('../../../services/cuttingOperationsService');
const dimensionService = require('../../../services/dimensionValidationService');
// Import UID generator utility
const { generateUID, generateBarcode } = require('../../../utils/uidGenerator');
// Import custom error classes
const { NotFoundError, ValidationError, InventoryError } = require('../../../utils/errors');
// Import logger for logging
const logger = require('../../../utils/logger');
// Import Sequelize operators and transaction
const { Op } = require('sequelize');
const { sequelize } = require('../../../models');

/**
 * Create production order
 * Creates a new production order (variants removed - using fg_product_id)
 * @param {Object} poData - Production order data (fg_product_id, quantity, status)
 * @returns {Promise<Object>} Created production order
 */
const createProductionOrder = async (poData) => {
  // Extract production order data
  const { fg_product_id, quantity, status = 'DRAFT' } = poData; // Extract data
  
  // Validate required fields
  if (!fg_product_id || !quantity) {
    throw new ValidationError('Finished good product ID and quantity are required'); // Throw error if missing fields
  }
  
  // Validate quantity
  if (parseFloat(quantity) <= 0) {
    throw new ValidationError('Quantity must be greater than 0'); // Throw error if invalid quantity
  }
  
  // Validate status
  const validStatuses = ['DRAFT', 'CONFIRMED']; // Valid statuses
  if (!validStatuses.includes(status)) {
    throw new ValidationError(`Status must be one of: ${validStatuses.join(', ')}`); // Throw error if invalid status
  }
  
  // Validate finished good product exists and is FG type
  const fgProduct = await Product.findByPk(fg_product_id); // Find product
  if (!fgProduct) {
    throw new NotFoundError(`Product with ID ${fg_product_id} not found`); // Throw error if product not found
  }
  
  if (fgProduct.product_type !== 'FG') {
    throw new ValidationError('Finished good product must be of type FG (Finished Goods)'); // Throw error if not FG
  }
  
  // Ensure FG product is marked as sellable on POS (auto-fix if not set)
  if (fgProduct.sell_on_pos !== true) {
    logger.warn(`FG product ${fgProduct.id} (${fgProduct.name}) is not marked as sell_on_pos=true. Auto-fixing...`);
    await fgProduct.update({ sell_on_pos: true }); // Auto-fix: set sell_on_pos to true
    logger.info(`FG product ${fgProduct.id} updated: sell_on_pos set to true`);
  }
  
  // Validate BOM exists for this product (variants removed - using fg_product_id)
  try {
    await bomService.getBOMByProduct(fg_product_id); // Check if BOM exists
  } catch (error) {
    if (error instanceof NotFoundError) {
      throw new ValidationError(`BOM not found for product ${fg_product_id}. Please create a BOM first.`); // Throw error if BOM not found
    }
    throw error; // Re-throw other errors
  }
  
  // Create production order
  const productionOrder = await ProductionOrder.create({
    fg_product_id, // Finished good product ID
    quantity: parseFloat(quantity), // Quantity to produce
    status, // Status
    confirmed_at: status === 'CONFIRMED' ? new Date() : null, // Set confirmed_at if CONFIRMED
  });
  
  logger.info(`Production order created: ${productionOrder.id} for product ${fg_product_id}, quantity: ${quantity}`); // Log PO creation
  
  // Return created production order with associations
  return await getProductionOrder(productionOrder.id); // Return PO with associations
};

/**
 * Get production order by ID
 * Retrieves a production order by ID
 * @param {number} poId - Production order ID
 * @returns {Promise<Object>} Production order
 */
const getProductionOrder = async (poId) => {
  // Validate production order ID
  if (!poId) {
    throw new ValidationError('Production order ID is required'); // Throw error if ID missing
  }
  
  // Find production order with associations (variants removed - using fg_product_id)
  const productionOrder = await ProductionOrder.findByPk(poId, {
    include: [
      {
        model: Product, // Include finished good product
        as: 'finishedGoodProduct', // Use finishedGoodProduct alias
      },
    ],
  });
  
  // Check if production order exists
  if (!productionOrder) {
    throw new NotFoundError(`Production order with ID ${poId} not found`); // Throw error if not found
  }

  // Get BOM for this product if it exists (variants removed - using fg_product_id)
  try {
    const bom = await bomService.getBOMByProduct(productionOrder.fg_product_id);
    productionOrder.bom = bom; // Add BOM to production order
  } catch (error) {
    // BOM might not exist, that's okay for display purposes
    productionOrder.bom = null;
  }
  
  // Return production order
  return productionOrder;
};

/**
 * List production orders
 * Lists production orders with optional filters (variants removed - using fg_product_id)
 * @param {Object} filters - Filter options (fg_product_id, status)
 * @param {Object} pagination - Pagination options (page, limit)
 * @returns {Promise<Object>} Paginated list of production orders
 */
const listProductionOrders = async (filters = {}, pagination = {}) => {
  // Extract filters
  const {
    fg_product_id, // Finished good product ID filter (variants removed)
    status, // Status filter
  } = filters; // Extract filters
  
  // Extract pagination options
  const page = parseInt(pagination.page, 10) || 1; // Current page (default 1)
  const limit = parseInt(pagination.limit, 10) || 50; // Items per page (default 50)
  const offset = (page - 1) * limit; // Calculate offset
  
  // Build where clause
  const where = {}; // Initialize where clause
  
  // Add fg_product_id filter (variants removed)
  if (fg_product_id) {
    where.fg_product_id = fg_product_id; // Filter by product ID
  }
  
  // Add status filter
  if (status) {
    where.status = status; // Filter by status
  }
  
  // Find production orders with pagination
  const { count, rows } = await ProductionOrder.findAndCountAll({
    where, // Where clause
    include: [
      {
        model: Product, // Include finished good product
        as: 'finishedGoodProduct', // Use finishedGoodProduct alias
      },
    ],
    limit, // Limit results
    offset, // Offset results
    order: [['created_at', 'DESC']], // Order by creation date descending
  });
  
  // Calculate pagination metadata
  const totalPages = Math.ceil(count / limit); // Total pages
  const hasNextPage = page < totalPages; // Has next page
  const hasPrevPage = page > 1; // Has previous page
  
  // Return paginated results
  return {
    productionOrders: rows, // Production orders array
    pagination: {
      page, // Current page
      limit, // Items per page
      total: count, // Total count
      totalPages, // Total pages
      hasNextPage, // Has next page
      hasPrevPage, // Has previous page
    },
  };
};

/**
 * Check raw material availability
 * Checks if sufficient raw materials are available for production
 * @param {number} poId - Production order ID
 * @returns {Promise<Object>} Availability check result
 */
const checkAvailability = async (poId) => {
  // Validate production order ID
  if (!poId) {
    throw new ValidationError('Production order ID is required'); // Throw error if ID missing
  }
  
  // Get production order
  const productionOrder = await ProductionOrder.findByPk(poId); // Find by ID
  
  // Check if production order exists
  if (!productionOrder) {
    throw new NotFoundError(`Production order with ID ${poId} not found`); // Throw error if not found
  }
  
  // Get BOM for this variant
  const bom = await bomService.getBOMByProduct(productionOrder.fg_product_id); // Get BOM
  
  // Check availability for each raw material
  const availabilityCheck = []; // Array to store availability results
  let allAvailable = true; // Flag to track if all materials are available
  
  for (const bomItem of bom.items) {
    // Determine if this is dimension-based by checking for dimension fields or product property
    const isDimensionBased = !!(
      (bomItem.required_length && bomItem.required_width && bomItem.dimension_unit) ||
      bomItem.rawMaterial?.track_by_dimensions === true
    );
    
    let itemResult = {
      rm_product_id: bomItem.rm_product_id, // Raw material product ID
      rm_product_name: bomItem.rawMaterial.name, // Raw material name
      use_dimensions: isDimensionBased // Whether this item uses dimensions
    };

    if (isDimensionBased) {
      // Dimension-based availability check
      try {
        const dimensionRequirements = bomDimensionService.calculateDimensionRequirements(
          bomItem, 
          productionOrder.quantity
        );

        if (!dimensionRequirements.isValid) {
          itemResult.available = false;
          itemResult.error = dimensionRequirements.error;
          allAvailable = false;
        } else {
          // Use material allocation service to check feasibility
          const feasibilityResult = await bomDimensionService.validateBOMFeasibility(
            { id: bom.id, items: [bomItem] },
            productionOrder.quantity
          );

          if (feasibilityResult.isValid && feasibilityResult.itemResults.length > 0) {
            const itemFeasibility = feasibilityResult.itemResults[0];
            const areaPerUnit = dimensionRequirements.areaPerUnit;
            const totalAreaRequired = dimensionRequirements.totalAreaRequired;
            const availableArea = itemFeasibility.availableArea || 0;
            const shortfall = totalAreaRequired - availableArea;
            
            // Calculate maximum producible units with available area
            const maxProducibleUnits = availableArea > 0 && areaPerUnit > 0 
              ? Math.floor(availableArea / areaPerUnit) 
              : 0;
            
            itemResult.available = itemFeasibility.feasible;
            itemResult.area_per_unit = parseFloat(areaPerUnit.toFixed(3));
            itemResult.total_area_required = parseFloat(totalAreaRequired.toFixed(3));
            itemResult.available_area = parseFloat(availableArea.toFixed(3));
            itemResult.shortfall = shortfall > 0 ? parseFloat(shortfall.toFixed(3)) : 0;
            itemResult.max_producible_units = maxProducibleUnits;
            itemResult.requested_quantity = productionOrder.quantity;
            itemResult.dimensions = {
              length: bomItem.required_length,
              width: bomItem.required_width,
              unit: bomItem.dimension_unit
            };

            if (!itemFeasibility.feasible) {
              allAvailable = false;
            }
          } else {
            itemResult.available = false;
            itemResult.error = 'Feasibility check failed';
            allAvailable = false;
          }
        }
      } catch (error) {
        itemResult.available = false;
        itemResult.error = `Dimension availability check failed: ${error.message}`;
        allAvailable = false;
      }
    } else {
      // Quantity-based availability check (special items RM: zippers, buttons, threads, etc.)
      // These use standard inventory.quantity tracking, not dimension-based tracking
      const quantityPerUnit = parseFloat(bomItem.quantity_per_unit);
      const requiredQuantity = quantityPerUnit * parseFloat(productionOrder.quantity); // Calculate required quantity
      
      // Check availability
      const available = await inventoryService.checkAvailability(
        bomItem.rm_product_id, // Raw material product ID
        requiredQuantity, // Required quantity
        null // No variant for raw materials (they're products, not variants)
      ); // Check availability
      
      // Get current quantity
      const currentQuantity = await inventoryService.getQuantity(
        bomItem.rm_product_id, // Raw material product ID
        null // No variant
      ); // Get current quantity
      
      // Calculate shortfall and max producible units
      const shortfall = requiredQuantity - currentQuantity;
      const maxProducibleUnits = quantityPerUnit > 0 
        ? Math.floor(currentQuantity / quantityPerUnit) 
        : 0;
      
      itemResult.quantity_per_unit = parseFloat(quantityPerUnit.toFixed(3));
      itemResult.required_quantity = parseFloat(requiredQuantity.toFixed(3));
      itemResult.current_quantity = parseFloat(currentQuantity);
      itemResult.available = available;
      itemResult.shortfall = shortfall > 0 ? parseFloat(shortfall.toFixed(3)) : 0;
      itemResult.max_producible_units = maxProducibleUnits;
      itemResult.requested_quantity = productionOrder.quantity;

      // Update allAvailable flag
      if (!available) {
        allAvailable = false; // Set to false if any material unavailable
      }
    }

    availabilityCheck.push(itemResult);
  }
  
  // Calculate overall maximum producible units (limited by the most constrained material)
  let maxProducibleUnits = null;
  if (availabilityCheck.length > 0) {
    const producibleUnits = availabilityCheck
      .map(item => item.max_producible_units)
      .filter(units => units !== undefined && units !== null);
    
    if (producibleUnits.length > 0) {
      // Maximum producible is limited by the material with the least availability
      maxProducibleUnits = Math.min(...producibleUnits);
    }
  }
  
  // Return availability check result
  return {
    production_order_id: poId, // Production order ID
    requested_quantity: productionOrder.quantity, // Requested production quantity
    all_available: allAvailable, // All materials available flag
    max_producible_units: maxProducibleUnits, // Maximum units that can be produced with available materials
    items: availabilityCheck, // Availability check items
  };
};

/**
 * Get production quantity optimization suggestions
 * Analyzes available materials and suggests optimal production quantities
 * @param {number} poId - Production order ID
 * @param {Object} options - Optimization options
 * @returns {Promise<Object>} Optimization suggestions
 */
const getProductionOptimizationSuggestions = async (poId, options = {}) => {
  const { maxSuggestions = 5, includeWasteAnalysis = true } = options;

  // Validate production order ID
  if (!poId) {
    throw new ValidationError('Production order ID is required');
  }
  
  // Get production order
  const productionOrder = await ProductionOrder.findByPk(poId);
  
  // Check if production order exists
  if (!productionOrder) {
    throw new NotFoundError(`Production order with ID ${poId} not found`);
  }
  
  // Get BOM for this variant
  const bom = await bomService.getBOMByProduct(productionOrder.fg_product_id);
  
  const suggestions = [];
  const currentQuantity = productionOrder.quantity;
  
  // Test different production quantities to find optimal ones
  const testQuantities = [
    Math.floor(currentQuantity * 0.5),
    Math.floor(currentQuantity * 0.75),
    currentQuantity,
    Math.floor(currentQuantity * 1.25),
    Math.floor(currentQuantity * 1.5),
    Math.floor(currentQuantity * 2)
  ].filter(q => q > 0);

  for (const testQuantity of testQuantities) {
    try {
      // Check feasibility for this quantity
      const feasibilityResult = await bomDimensionService.validateBOMFeasibility(bom, testQuantity);
      
      if (feasibilityResult.isValid) {
        let totalWasteArea = 0;
        let totalRequiredArea = 0;
        let feasibleItems = 0;
        let totalItems = 0;

        for (const itemResult of feasibilityResult.itemResults) {
          totalItems++;
          if (itemResult.feasible) {
            feasibleItems++;
          }
          
          if (itemResult.totalAreaRequired) {
            totalRequiredArea += itemResult.totalAreaRequired;
          }
        }

        // Calculate waste if requested
        if (includeWasteAnalysis) {
          try {
            const allocationResult = await bomDimensionService.allocateMaterialsForBOM(
              bom, 
              testQuantity, 
              materialAllocationService.ALLOCATION_STRATEGIES.BEST_FIT
            );
            
            if (allocationResult.isValid) {
              for (const itemResult of allocationResult.itemResults) {
                if (itemResult.wasteGenerated && Array.isArray(itemResult.wasteGenerated)) {
                  totalWasteArea += itemResult.wasteGenerated.reduce((sum, waste) => 
                    sum + (waste.length * waste.width), 0);
                }
              }
            }
          } catch (error) {
            // Waste analysis failed, continue without it
          }
        }

        const utilizationRatio = totalRequiredArea > 0 ? 
          (totalRequiredArea - totalWasteArea) / totalRequiredArea : 0;
        
        const feasibilityRatio = totalItems > 0 ? feasibleItems / totalItems : 0;

        suggestions.push({
          quantity: testQuantity,
          feasible: feasibilityResult.overallFeasible,
          feasibilityRatio: feasibilityRatio,
          totalRequiredArea: totalRequiredArea,
          totalWasteArea: totalWasteArea,
          utilizationRatio: utilizationRatio,
          efficiencyScore: feasibilityRatio * 50 + utilizationRatio * 50,
          isCurrentQuantity: testQuantity === currentQuantity,
          recommendation: generateQuantityRecommendation(testQuantity, currentQuantity, feasibilityResult.overallFeasible, utilizationRatio)
        });
      }
    } catch (error) {
      // Skip this quantity if analysis fails
      continue;
    }
  }

  // Sort by efficiency score (highest first)
  suggestions.sort((a, b) => b.efficiencyScore - a.efficiencyScore);

  return {
    production_order_id: poId,
    current_quantity: currentQuantity,
    suggestions: suggestions.slice(0, maxSuggestions),
    analysis: {
      totalQuantitiesTested: testQuantities.length,
      feasibleQuantities: suggestions.filter(s => s.feasible).length,
      bestEfficiencyScore: suggestions.length > 0 ? suggestions[0].efficiencyScore : 0,
      recommendedQuantity: suggestions.length > 0 ? suggestions[0].quantity : currentQuantity
    }
  };
};

/**
 * Generate recommendation text for a quantity suggestion
 * @param {number} testQuantity - Test quantity
 * @param {number} currentQuantity - Current production quantity
 * @param {boolean} feasible - Whether quantity is feasible
 * @param {number} utilizationRatio - Material utilization ratio
 * @returns {string} Recommendation text
 */
function generateQuantityRecommendation(testQuantity, currentQuantity, feasible, utilizationRatio) {
  if (!feasible) {
    return 'Not feasible - insufficient materials';
  }
  
  if (testQuantity === currentQuantity) {
    return 'Current planned quantity';
  }
  
  if (testQuantity > currentQuantity) {
    if (utilizationRatio > 0.8) {
      return 'Higher quantity with excellent material utilization';
    } else if (utilizationRatio > 0.6) {
      return 'Higher quantity with good material utilization';
    } else {
      return 'Higher quantity but with increased waste';
    }
  } else {
    if (utilizationRatio > 0.8) {
      return 'Lower quantity but with excellent material utilization';
    } else {
      return 'Lower quantity with reduced material requirements';
    }
  }
}

/**
 * Get cutting operations for a production order
 * Retrieves all cutting operations performed for a production order
 * @param {number} poId - Production order ID
 * @returns {Promise<Array>} Array of cutting operations with details
 */
const getCuttingOperations = async (poId) => {
  // Validate production order ID
  if (!poId) {
    throw new ValidationError('Production order ID is required');
  }
  
  // Get cutting operations for this production order
  const cuttingOperations = await RMCuttingOperation.findAll({
    where: {
      production_order_id: poId
    },
    include: [
      {
        model: RMInventoryPiece,
        as: 'originalPiece',
        attributes: ['id', 'piece_number', 'length', 'width', 'unit', 'status']
      },
      {
        model: RMInventoryPiece,
        as: 'remainingPiece',
        attributes: ['id', 'piece_number', 'length', 'width', 'unit', 'status'],
        required: false
      },
      {
        model: BOMItem,
        as: 'bomItem',
        include: [
          {
            model: Product,
            as: 'rawMaterial',
            attributes: ['id', 'name', 'sku']
          }
        ]
      }
    ],
    order: [['cut_at', 'ASC']]
  });
  
  // Calculate summary statistics
  const summary = {
    total_operations: cuttingOperations.length,
    total_cut_area: 0,
    total_waste_area: 0,
    total_scrap_area: 0,
    waste_pieces_created: 0,
    scrap_pieces_created: 0,
    pieces_with_remaining: 0
  };
  
  cuttingOperations.forEach(operation => {
    summary.total_cut_area += operation.getCutArea();
    summary.total_waste_area += operation.getTotalWasteArea();
    summary.total_scrap_area += operation.getScrapArea();
    
    if (operation.waste_pieces && Array.isArray(operation.waste_pieces)) {
      const wastePieces = operation.getWastePiecesByStatus('WASTE');
      const scrapPieces = operation.getWastePiecesByStatus('SCRAP');
      summary.waste_pieces_created += wastePieces.length;
      summary.scrap_pieces_created += scrapPieces.length;
    }
    
    if (operation.hasRemainingPiece()) {
      summary.pieces_with_remaining++;
    }
  });
  
  return {
    production_order_id: poId,
    cutting_operations: cuttingOperations,
    summary: summary
  };
};

/**
 * Get waste piece suggestions for production planning
 * Suggests available waste pieces that match BOM requirements
 * @param {number} poId - Production order ID
 * @returns {Promise<Object>} Waste piece suggestions
 */
const getWastePieceSuggestions = async (poId) => {
  // Validate production order ID
  if (!poId) {
    throw new ValidationError('Production order ID is required');
  }
  
  // Get production order
  const productionOrder = await ProductionOrder.findByPk(poId);
  
  if (!productionOrder) {
    throw new NotFoundError(`Production order with ID ${poId} not found`);
  }
  
  // Get BOM for this variant
  const bom = await bomService.getBOMByProduct(productionOrder.fg_product_id);
  
  const suggestions = [];
  
  for (const bomItem of bom.items) {
    // Determine if dimension-based
    const isDimensionBased = !!(
      (bomItem.required_length && bomItem.required_width && bomItem.dimension_unit) ||
      bomItem.rawMaterial?.track_by_dimensions === true
    );
    
    if (isDimensionBased) {
      // Find available waste pieces for this RM product
      const availableWastePieces = await RMInventoryPiece.findAll({
        where: {
          product_id: bomItem.rm_product_id,
          status: 'WASTE'
        },
        include: [
          {
            model: Product,
            as: 'product',
            attributes: ['id', 'name', 'sku']
          }
        ],
        order: [['created_at', 'DESC']] // Newest waste first
      });
      
      if (availableWastePieces.length > 0) {
        // Check which waste pieces can satisfy the BOM requirements
        const suitableWastePieces = [];
        
        for (const wastePiece of availableWastePieces) {
          // Check if waste piece dimensions are sufficient
          const wasteDimensions = {
            length: wastePiece.usable_length || wastePiece.length,
            width: wastePiece.usable_width || wastePiece.width,
            unit: wastePiece.unit
          };
          
          const requiredDimensions = {
            length: bomItem.required_length,
            width: bomItem.required_width,
            unit: bomItem.dimension_unit
          };
          
          const sufficiencyResult = dimensionService.checkDimensionSufficiency(
            wasteDimensions,
            requiredDimensions
          );
          
          if (sufficiencyResult.isValid && sufficiencyResult.canFit) {
            // Calculate how many units can be cut from this waste piece
            const unitsFromWaste = Math.floor(Math.min(
              wasteDimensions.length / requiredDimensions.length,
              wasteDimensions.width / requiredDimensions.width
            ));
            
            if (unitsFromWaste > 0) {
              suitableWastePieces.push({
                piece: wastePiece,
                unitsAvailable: unitsFromWaste,
                wasteArea: wasteDimensions.length * wasteDimensions.width,
                requiredArea: requiredDimensions.length * requiredDimensions.width,
                utilizationRatio: (requiredDimensions.length * requiredDimensions.width * unitsFromWaste) / 
                                 (wasteDimensions.length * wasteDimensions.width),
                costSavings: wastePiece.cost_per_area * requiredDimensions.length * requiredDimensions.width * unitsFromWaste
              });
            }
          }
        }
        
        if (suitableWastePieces.length > 0) {
          // Sort by utilization ratio (highest first) to prioritize efficient use
          suitableWastePieces.sort((a, b) => b.utilizationRatio - a.utilizationRatio);
          
          suggestions.push({
            bom_item_id: bomItem.id,
            rm_product: {
              id: bomItem.rm_product_id,
              name: bomItem.rawMaterial.name,
              sku: bomItem.rawMaterial.sku
            },
            required_per_unit: {
              length: bomItem.required_length,
              width: bomItem.required_width,
              unit: bomItem.dimension_unit
            },
            total_required_units: productionOrder.quantity,
            suitable_waste_pieces: suitableWastePieces.slice(0, 5), // Top 5 suggestions
            total_waste_pieces_available: availableWastePieces.length,
            potential_cost_savings: suitableWastePieces.reduce((sum, piece) => sum + piece.costSavings, 0)
          });
        }
      }
    }
  }
  
  // Calculate overall statistics
  const totalWastePieces = suggestions.reduce((sum, s) => sum + s.total_waste_pieces_available, 0);
  const totalPotentialSavings = suggestions.reduce((sum, s) => sum + s.potential_cost_savings, 0);
  const bomItemsWithWasteSuggestions = suggestions.length;
  const totalBomItems = bom.items.filter(item => 
    (item.required_length && item.required_width && item.dimension_unit) ||
    item.rawMaterial?.track_by_dimensions === true
  ).length;
  
  return {
    production_order_id: poId,
    production_quantity: productionOrder.quantity,
    suggestions: suggestions,
    summary: {
      bom_items_with_waste_suggestions: bomItemsWithWasteSuggestions,
      total_bom_items: totalBomItems,
      waste_coverage_ratio: totalBomItems > 0 ? bomItemsWithWasteSuggestions / totalBomItems : 0,
      total_waste_pieces_available: totalWastePieces,
      total_potential_cost_savings: totalPotentialSavings,
      recommendation: generateWasteRecommendation(bomItemsWithWasteSuggestions, totalBomItems, totalPotentialSavings)
    }
  };
};

/**
 * Generate waste reuse recommendation text
 * @param {number} itemsWithWaste - Number of BOM items with waste suggestions
 * @param {number} totalItems - Total number of BOM items
 * @param {number} potentialSavings - Total potential cost savings
 * @returns {string} Recommendation text
 */
function generateWasteRecommendation(itemsWithWaste, totalItems, potentialSavings) {
  if (itemsWithWaste === 0) {
    return 'No suitable waste pieces found for this production order';
  }
  
  const coverageRatio = itemsWithWaste / totalItems;
  
  if (coverageRatio >= 0.8) {
    return `Excellent waste reuse opportunity! ${itemsWithWaste}/${totalItems} materials can use waste pieces. Potential savings: $${potentialSavings.toFixed(2)}`;
  } else if (coverageRatio >= 0.5) {
    return `Good waste reuse opportunity! ${itemsWithWaste}/${totalItems} materials can use waste pieces. Potential savings: $${potentialSavings.toFixed(2)}`;
  } else if (coverageRatio >= 0.25) {
    return `Moderate waste reuse opportunity. ${itemsWithWaste}/${totalItems} materials can use waste pieces. Potential savings: $${potentialSavings.toFixed(2)}`;
  } else {
    return `Limited waste reuse opportunity. ${itemsWithWaste}/${totalItems} materials can use waste pieces. Potential savings: $${potentialSavings.toFixed(2)}`;
  }
}

/**
 * Get material traceability for a production order
 * Provides complete traceability from source pieces to final products
 * @param {number} poId - Production order ID
 * @returns {Promise<Object>} Traceability information
 */
const getMaterialTraceability = async (poId) => {
  // Validate production order ID
  if (!poId) {
    throw new ValidationError('Production order ID is required');
  }
  
  // Get production order
  const productionOrder = await ProductionOrder.findByPk(poId, {
    include: [
      {
        model: Product, // Variants removed - using Product directly
        as: 'finishedGoodProduct',
        include: [
          {
            model: Product,
            as: 'product',
            attributes: ['id', 'name', 'sku']
          }
        ]
      }
    ]
  });
  
  if (!productionOrder) {
    throw new NotFoundError(`Production order with ID ${poId} not found`);
  }
  
  // Get cutting operations with full traceability
  const cuttingOperations = await RMCuttingOperation.findAll({
    where: {
      production_order_id: poId
    },
    include: [
      {
        model: RMInventoryPiece,
        as: 'originalPiece',
        include: [
          {
            model: Product,
            as: 'product',
            attributes: ['id', 'name', 'sku', 'product_type']
          }
        ]
      },
      {
        model: RMInventoryPiece,
        as: 'remainingPiece',
        required: false
      },
      {
        model: BOMItem,
        as: 'bomItem',
        include: [
          {
            model: Product,
            as: 'rawMaterial',
            attributes: ['id', 'name', 'sku']
          }
        ]
      }
    ],
    order: [['cut_at', 'ASC']]
  });
  
  // Build traceability tree
  const traceabilityTree = {
    production_order: {
      id: productionOrder.id,
      quantity: productionOrder.quantity,
      status: productionOrder.status,
      finished_good: {
        id: productionOrder.finishedGoodProduct.id,
        name: productionOrder.finishedGoodProduct.name,
        sku: productionOrder.finishedGoodProduct.sku
      }
    },
    material_usage: []
  };
  
  // Group operations by original piece
  const pieceGroups = {};
  cuttingOperations.forEach(operation => {
    const pieceId = operation.originalPiece.id;
    if (!pieceGroups[pieceId]) {
      pieceGroups[pieceId] = {
        original_piece: operation.originalPiece,
        operations: [],
        total_cut_area: 0,
        total_waste_area: 0,
        waste_pieces: [],
        remaining_pieces: []
      };
    }
    
    pieceGroups[pieceId].operations.push(operation);
    pieceGroups[pieceId].total_cut_area += operation.getCutArea();
    pieceGroups[pieceId].total_waste_area += operation.getTotalWasteArea();
    
    if (operation.waste_pieces && Array.isArray(operation.waste_pieces)) {
      pieceGroups[pieceId].waste_pieces.push(...operation.waste_pieces);
    }
    
    if (operation.remainingPiece) {
      pieceGroups[pieceId].remaining_pieces.push(operation.remainingPiece);
    }
  });
  
  traceabilityTree.material_usage = Object.values(pieceGroups);
  
  return traceabilityTree;
};

/**
 * Confirm production
 * Confirms production by consuming raw materials and creating finished goods
 * @param {number} poId - Production order ID
 * @param {number} userId - User ID confirming production
 * @param {boolean} generateUIDs - Whether to generate UIDs for finished goods (default: true)
 * @returns {Promise<Object>} Confirmed production order
 */
const confirmProduction = async (poId, userId, generateUIDs = true, confirmQuantity = null) => {
  // Validate production order ID
  if (!poId) {
    throw new ValidationError('Production order ID is required'); // Throw error if ID missing
  }
  
  // Get production order with product (variants removed - using Product directly)
  const productionOrder = await ProductionOrder.findByPk(poId, {
    include: [
      {
        model: Product, // Include finished good product
        as: 'finishedGoodProduct', // Use finishedGoodProduct alias
        attributes: ['id', 'name', 'sku', 'product_type', 'track_inventory', 'sell_on_pos', 'track_by_dimensions'], // Ensure all needed fields are loaded
      },
    ],
  }); // Find production order
  
  // Check if production order exists
  if (!productionOrder) {
    throw new NotFoundError(`Production order with ID ${poId} not found`); // Throw error if not found
  }
  
  // Determine the quantity to confirm
  const totalQuantity = Math.floor(parseFloat(productionOrder.quantity));
  const alreadyConfirmed = Math.floor(parseFloat(productionOrder.confirmed_quantity || 0));

  // Derive remaining quantity from total - confirmed to avoid stale/inconsistent remaining_quantity
  const remainingQty = Math.max(0, totalQuantity - alreadyConfirmed);
  
  // If confirmQuantity is not provided, default to remaining quantity
  const qtyToConfirm = confirmQuantity !== null ? Math.floor(parseFloat(confirmQuantity)) : remainingQty;
  
  // Validate confirm quantity - must be whole number
  if (qtyToConfirm <= 0 || !Number.isInteger(qtyToConfirm)) {
    throw new ValidationError('Production quantity must be a positive whole number');
  }
  
  if (qtyToConfirm > remainingQty) {
    throw new ValidationError(`Cannot confirm ${qtyToConfirm} units. Only ${remainingQty} units remaining.`);
  }
  
  // Check if already fully confirmed
  if (productionOrder.status === 'CONFIRMED' && remainingQty === 0) {
    throw new ValidationError('Production order is already fully confirmed'); // Throw error if already fully confirmed
  }
  
  // Check availability for the quantity to confirm
  // We need to temporarily modify the production order quantity for the check
  const originalQuantity = productionOrder.quantity;
  const originalConfirmedQty = productionOrder.confirmed_quantity || 0;
  const originalRemainingQty = productionOrder.remaining_quantity;
  
  // Temporarily set quantity to qtyToConfirm for availability check
  productionOrder.quantity = qtyToConfirm;
  
  const availability = await checkAvailability(poId); // Check availability for qtyToConfirm
  if (!availability.all_available) {
    // Restore original values before throwing error
    productionOrder.quantity = originalQuantity;
    productionOrder.confirmed_quantity = originalConfirmedQty;
    productionOrder.remaining_quantity = originalRemainingQty;
    
    const error = new InventoryError(`Insufficient raw materials for production of ${qtyToConfirm} units`, 400); // Create error with status code
    error.items = availability.items; // Attach availability items to error
    throw error; // Throw error if unavailable
  }
  
  // Restore original quantity (we'll use qtyToConfirm for allocation)
  productionOrder.quantity = originalQuantity;
  
  // Get BOM for this variant
  const bom = await bomService.getBOMByProduct(productionOrder.fg_product_id); // Get BOM
  
  // Start database transaction
  const transaction = await sequelize.transaction(); // Start transaction
  
  try {
    // Perform dimension-based material allocation for dimension-based BOM items
    const allocationResults = [];
    
    for (const bomItem of bom.items) {
      // Determine if dimension-based
      const isDimensionBased = !!(
        (bomItem.required_length && bomItem.required_width && bomItem.dimension_unit) ||
        bomItem.rawMaterial?.track_by_dimensions === true
      );
      
      if (isDimensionBased) {
        // Dimension-based material allocation
        try {
          const allocationResult = await bomDimensionService.allocateMaterialsForBOM(
            { id: bom.id, items: [bomItem] },
            productionOrder.quantity,
            materialAllocationService.ALLOCATION_STRATEGIES.BEST_FIT
          );

          if (!allocationResult.isValid || !allocationResult.overallSuccess) {
            throw new InventoryError(`Material allocation failed for ${bomItem.rawMaterial.name}: ${allocationResult.error || 'Unknown error'}`);
          }

          allocationResults.push({
            bomItem: bomItem,
            allocationResult: allocationResult
          });

          // Process cutting operations for allocated pieces
          if (allocationResult.itemResults.length > 0) {
            const itemResult = allocationResult.itemResults[0];
            
            if (itemResult.allocatedPieces && Array.isArray(itemResult.allocatedPieces)) {
              for (const allocatedPiece of itemResult.allocatedPieces) {
                // Create cutting operation for each allocated piece
                const cuttingData = {
                  production_order_id: poId,
                  bom_item_id: bomItem.id,
                  cut_length: bomItem.required_length,
                  cut_width: bomItem.required_width,
                  unit: bomItem.dimension_unit,
                  operator_id: userId,
                  notes: `Production cutting for PO ${poId}`
                };

                const cuttingResult = cuttingOperationsService.processCuttingOperation(
                  cuttingData,
                  allocatedPiece.piece,
                  { generateWastePieces: true, autoClassifyWaste: true }
                );

                if (!cuttingResult.isValid) {
                  throw new InventoryError(`Cutting operation failed for piece ${allocatedPiece.piece.id}: ${cuttingResult.error || 'Unknown error'}`);
                }

                // Save cutting operation to database
                const cuttingOperationRecord = await RMCuttingOperation.create({
                  production_order_id: poId,
                  rm_piece_id: allocatedPiece.piece.id,
                  bom_item_id: bomItem.id,
                  cut_length: cuttingData.cut_length,
                  cut_width: cuttingData.cut_width,
                  unit: cuttingData.unit,
                  remaining_piece_id: cuttingResult.remainingPiece ? cuttingResult.remainingPiece.id : null,
                  waste_pieces: cuttingResult.newWastePieces.length > 0 ? cuttingResult.newWastePieces : null,
                  scrap_dimensions: cuttingResult.cutCalculations.scrapDimensions,
                  cut_by_user_id: userId,
                  cut_at: new Date(),
                  notes: cuttingData.notes
                }, { transaction });

                // Update source piece status
                const sourcePiece = await RMInventoryPiece.findByPk(allocatedPiece.piece.id, { transaction });
                if (sourcePiece) {
                  await sourcePiece.update({
                    status: cuttingResult.sourcePieceUpdates.newStatus,
                    usable_length: cuttingResult.sourcePieceUpdates.newUsableLength,
                    usable_width: cuttingResult.sourcePieceUpdates.newUsableWidth,
                    last_cut_at: new Date()
                  }, { transaction });
                }

                // Create remaining piece if exists
                if (cuttingResult.remainingPiece) {
                  const remainingPieceRecord = await RMInventoryPiece.create({
                    product_id: sourcePiece.product_id,
                    grn_item_id: sourcePiece.grn_item_id,
                    piece_number: cuttingResult.remainingPiece.piece_number,
                    length: cuttingResult.remainingPiece.length,
                    width: cuttingResult.remainingPiece.width,
                    unit: cuttingResult.remainingPiece.unit,
                    status: cuttingResult.remainingPiece.status,
                    usable_length: cuttingResult.remainingPiece.usable_length,
                    usable_width: cuttingResult.remainingPiece.usable_width,
                    parent_piece_id: sourcePiece.id,
                    created_from_cutting: true,
                    cost_per_area: sourcePiece.cost_per_area,
                    supplier_batch: sourcePiece.supplier_batch,
                    quality_grade: sourcePiece.quality_grade
                  }, { transaction });

                  // Update cutting operation with actual remaining piece ID
                  await cuttingOperationRecord.update({
                    remaining_piece_id: remainingPieceRecord.id
                  }, { transaction });
                }

                // Create waste pieces as separate inventory pieces
                if (cuttingResult.newWastePieces.length > 0) {
                  for (const wastePiece of cuttingResult.newWastePieces) {
                    if (wastePiece.status === 'WASTE') {
                      // Only create inventory pieces for reusable waste
                      await RMInventoryPiece.create({
                        product_id: sourcePiece.product_id,
                        grn_item_id: sourcePiece.grn_item_id,
                        piece_number: sourcePiece.piece_number + Math.floor(Math.random() * 1000), // Generate unique piece number
                        length: wastePiece.length,
                        width: wastePiece.width,
                        unit: wastePiece.unit,
                        status: wastePiece.status,
                        usable_length: wastePiece.length,
                        usable_width: wastePiece.width,
                        parent_piece_id: sourcePiece.id,
                        created_from_cutting: true,
                        cost_per_area: sourcePiece.cost_per_area,
                        supplier_batch: sourcePiece.supplier_batch,
                        quality_grade: sourcePiece.quality_grade
                      }, { transaction });
                    }
                    // SCRAP pieces are tracked in the cutting operation but not created as inventory pieces
                  }
                }

                logger.info(`Cutting operation saved: ${cuttingOperationRecord.id} for piece ${allocatedPiece.piece.id}, waste pieces: ${cuttingResult.newWastePieces.length}`);
              }
            }
          }
        } catch (error) {
          throw new InventoryError(`Dimension-based allocation failed for ${bomItem.rawMaterial.name}: ${error.message}`);
        }
      }
    }

    // Consume raw materials for quantity-based items (special items RM: zippers, buttons, etc.)
    for (const bomItem of bom.items) {
      // Determine if dimension-based
      const isDimensionBased = !!(
        (bomItem.required_length && bomItem.required_width && bomItem.dimension_unit) ||
        bomItem.rawMaterial?.track_by_dimensions === true
      );
      
      if (!isDimensionBased) {
        // Quantity-based material consumption (special items RM: zippers, buttons, threads, etc.)
        // These use standard inventory.quantity tracking, not dimension-based tracking
        const requiredQuantity = parseFloat(bomItem.quantity_per_unit) * qtyToConfirm; // Calculate required quantity for qtyToConfirm
        
        // Get or create quantity-based inventory record
        const [inventory] = await Inventory.findOrCreate({
          where: {
            product_id: bomItem.rm_product_id, // Match product ID
            // Variants removed - raw materials tracked at product level only
          },
          defaults: {
            quantity: 0, // Default quantity
            reorder_level: 0, // Default reorder level
          },
          transaction, // Use transaction
        }); // Get or create inventory
        
        // Calculate new quantity (decrease)
        const newQuantity = parseFloat(inventory.quantity) - requiredQuantity; // Calculate new quantity
        
        // Check if sufficient stock
        if (newQuantity < 0) {
          throw new InventoryError(`Insufficient raw material stock for product ${bomItem.rm_product_id}. Current: ${inventory.quantity}, Required: ${requiredQuantity}`); // Throw error if insufficient stock
        }
        
        // Update inventory quantity
        inventory.quantity = newQuantity; // Set new quantity
        await inventory.save({ transaction }); // Save inventory in transaction
        
        // Create inventory movement for raw material consumption
        await InventoryMovement.create({
          product_id: bomItem.rm_product_id, // Product ID
          variant_id: null, // No variant for raw materials
          inventory_item_id: null, // No inventory item for quantity-based
          quantity_change: -requiredQuantity, // Negative quantity change (consume)
          reason: 'PRODUCTION_CONSUME', // Reason: PRODUCTION_CONSUME
          reference_id: poId, // Reference ID: production order ID
        }, { transaction }); // Create movement in transaction
      }
    }
    
    // Create finished goods
    // Fetch product fresh from database (matches procurement behavior) (variants removed)
    const fgProductId = productionOrder.fg_product_id; // Get product ID directly (variants removed)
    const fgProduct = await Product.findByPk(fgProductId, { transaction }); // Fetch product fresh (like procurement)
    
    // Ensure product exists
    if (!fgProduct) {
      throw new NotFoundError(`Product not found for finished good product ${productionOrder.fg_product_id}`); // Throw error if product not found
    }
    
    const fgQuantity = Math.floor(qtyToConfirm); // Get integer quantity for qtyToConfirm (for UID-based items)
    
    // **IMPORTANT**: UUID generation is ONLY for FG (Finished Goods) products
    // RM products should NOT get UUIDs - they are tracked by quantity/dimensions only
    // Generate UIDs if requested, product tracks inventory, AND product is FG type
    if (generateUIDs && fgProduct.track_inventory && fgProduct.product_type === 'FG') {
      // Generate UIDs for finished goods
      for (let i = 0; i < fgQuantity; i++) {
        // Generate UID and barcode
        let itemUID = generateUID(); // Generate UID
        const itemBarcode = generateBarcode(); // Generate barcode
        
        // Check if UID already exists (unlikely but possible)
        let existingItem = await InventoryItem.findOne({
          where: { uid: itemUID }, // Match UID
          transaction, // Use transaction
        });
        
        // Retry UID generation if it exists
        while (existingItem) {
          itemUID = generateUID(); // Generate new UID
          existingItem = await InventoryItem.findOne({
            where: { uid: itemUID }, // Match UID
            transaction, // Use transaction
          });
        }
        
        // Get or create quantity-based inventory record at BASE PRODUCT LEVEL (variants removed)
        // This matches procurement behavior - inventory is tracked at product level, not variant level
        const [inventory] = await Inventory.findOrCreate({
          where: {
            product_id: fgProduct.id, // Match product ID
            // Variants removed - base product level (like procurement)
          },
          defaults: {
            quantity: 0, // Default quantity
            reorder_level: 0, // Default reorder level
          },
          transaction, // Use transaction
        }); // Get or create inventory
        
        // Create inventory item with UID (within transaction)
        // Variants removed - tracking at product level only
        const inventoryItem = await InventoryItem.create({
          product_id: fgProduct.id, // Product ID
          // Variants removed - tracking at product level only
          uid: itemUID, // UID
          barcode: itemBarcode, // Barcode
          status: 'IN_STOCK', // Status: IN_STOCK
          source: 'PRODUCTION', // Source: PRODUCTION
          source_reference_id: poId, // Track which production order created this item
        }, { transaction }); // Create inventory item in transaction
        
        // Increment quantity in quantity-based inventory (sync with UID-based)
        await inventory.increment('quantity', { by: 1, transaction }); // Increment quantity
        await inventory.reload({ transaction }); // Reload to get updated quantity
        
        // Create inventory movement for finished good output
        // Variants removed - track at product level only
        await InventoryMovement.create({
          product_id: fgProduct.id, // Product ID
          variant_id: null, // Variants removed - no variant_id
          inventory_item_id: inventoryItem.id, // Inventory item ID
          quantity_change: 1, // Quantity change (1 item)
          reason: 'PRODUCTION_OUTPUT', // Reason: PRODUCTION_OUTPUT
          reference_id: poId, // Reference ID: production order ID
        }, { transaction }); // Create movement in transaction
      }
    } else {
      // If not generating UIDs, add to quantity-based inventory at BASE PRODUCT LEVEL
      // This matches procurement behavior - inventory is tracked at product level, not variant level
      const [inventory] = await Inventory.findOrCreate({
        where: {
          product_id: fgProduct.id, // Match product ID
          variant_id: null, // Base product level (like procurement)
        },
        defaults: {
          quantity: 0, // Default quantity
          reorder_level: 0, // Default reorder level
        },
        transaction, // Use transaction
      }); // Get or create inventory
      
      // Increment quantity by qtyToConfirm
      await inventory.increment('quantity', { by: qtyToConfirm, transaction }); // Increment quantity
      await inventory.reload({ transaction }); // Reload to get updated quantity
      
      // Create inventory movement for finished good output
      // Variants removed - track at product level only
      await InventoryMovement.create({
        product_id: fgProduct.id, // Product ID
        variant_id: null, // Variants removed - no variant_id
        inventory_item_id: null, // No inventory item for quantity-based
        quantity_change: qtyToConfirm, // Quantity change
        reason: 'PRODUCTION_OUTPUT', // Reason: PRODUCTION_OUTPUT
        reference_id: poId, // Reference ID: production order ID
      }, { transaction }); // Create movement in transaction
    }
    
    // Update production order quantities and status
    const newConfirmedQty = alreadyConfirmed + qtyToConfirm;
    // Recalculate remaining based on total - confirmed to keep data consistent
    const newRemainingQty = Math.max(0, totalQuantity - newConfirmedQty);
    
    productionOrder.confirmed_quantity = newConfirmedQty;
    productionOrder.remaining_quantity = newRemainingQty;
    
    // Determine status: CONFIRMED if fully confirmed, PARTIALLY_CONFIRMED if partial
    if (newRemainingQty <= 0) {
      productionOrder.status = 'CONFIRMED'; // Fully confirmed
    } else {
      productionOrder.status = 'PARTIALLY_CONFIRMED'; // Partially confirmed
    }
    
    // Set confirmed_at on first confirmation (don't overwrite if already set)
    if (!productionOrder.confirmed_at) {
      productionOrder.confirmed_at = new Date(); // Set confirmed_at timestamp on first confirmation
    }
    
    await productionOrder.save({ transaction }); // Save production order in transaction
    
    // Commit transaction
    await transaction.commit(); // Commit transaction
    
    logger.info(`Production order ${newRemainingQty > 0 ? 'partially ' : ''}confirmed: ${poId} - Confirmed ${qtyToConfirm}/${totalQuantity} units, Consumed RM (${allocationResults.length} dimension-based allocations), created ${fgQuantity} FG items`); // Log production confirmation
    
    // Return confirmed production order with allocation details
    const result = await getProductionOrder(poId);
    result.allocationResults = allocationResults.map(ar => ({
      bomItemId: ar.bomItem.id,
      productName: ar.bomItem.rawMaterial.name,
      success: ar.allocationResult.overallSuccess,
      piecesAllocated: ar.allocationResult.itemResults[0]?.allocatedPieces?.length || 0,
      wasteGenerated: ar.allocationResult.itemResults[0]?.wasteGenerated?.length || 0
    }));
    
    return result;
  } catch (error) {
    // Rollback transaction on error
    await transaction.rollback(); // Rollback transaction
    throw error; // Re-throw error
  }
};

// Export production order service functions
module.exports = {
  createProductionOrder,
  getProductionOrder,
  listProductionOrders,
  checkAvailability,
  getProductionOptimizationSuggestions,
  getCuttingOperations,
  getMaterialTraceability,
  getWastePieceSuggestions,
  confirmProduction,
};

