const db = require('../models');
const { sendResponse, sendError } = require('../utils/response');
const asyncHandler = require('../middlewares/asyncHandler');
const { getPagination, getPagingData } = require('../utils/pagination');
const { Op } = require('sequelize');
const PDFDocument = require('pdfkit');
const path = require('path');
const fs = require('fs');
const atpService = require('../services/atpService');
const notificationService = require('../services/notificationService');
const statusValidationService = require('../services/statusValidationService');
const businessRuleValidationService = require('../services/businessRuleValidationService');
const { generateNextNumber } = require('../services/numberFormatService');
const customerPOProcessFlowService = require('../services/customerPOProcessFlowService2');

const toNum = (value) => {
  const n = parseFloat(value);
  return Number.isFinite(n) ? n : 0;
};

const normalizedCode = (value) => (value ? String(value).trim().toUpperCase() : '');

const getTaxTypeByStateCode = async (companyId, customerId, transaction = null) => {
  const company = await db.Company.findByPk(companyId, {
    include: [{ model: db.State, as: 'state', attributes: ['id', 'gst_state_code', 'state_code'] }],
    transaction: transaction || undefined
  });
  const customerGSTIN = await db.BPGSTIN.findOne({
    where: { bp_id: customerId, status: 'ACTIVE' },
    include: [{ model: db.State, as: 'state', attributes: ['id', 'gst_state_code', 'state_code'] }],
    order: [['is_default', 'DESC'], ['id', 'ASC']],
    transaction: transaction || undefined
  });

  const companyCode = normalizedCode(company?.state?.gst_state_code || company?.state?.state_code || company?.state_id);
  const customerCode = normalizedCode(customerGSTIN?.state?.gst_state_code || customerGSTIN?.state?.state_code || customerGSTIN?.state_id);
  const isInterState = Boolean(companyCode && customerCode && companyCode !== customerCode);

  return { isInterState };
};

const generatePONumber = async (companyId) => {
  const today = new Date();
  const year = today.getFullYear();
  const prefix = `CPO${year}`;
  
  const lastPO = await db.CustomerPO.findOne({
    where: {
      company_id: companyId,
      po_number: { [Op.like]: `${prefix}%` }
    },
    order: [['po_number', 'DESC']]
  });

  let sequence = 1;
  if (lastPO) {
    const lastSequence = parseInt(lastPO.po_number.slice(-4)) || 0;
    sequence = lastSequence + 1;
  }

  return `${prefix}${sequence.toString().padStart(4, '0')}`;
};

const generateOrderNumber = async (companyId, fiscalYearId, transaction = null) => {
  try {
    // Use existing Number Format module key. `po_number` remains manual; `order_number` is internal.
    return await generateNextNumber(companyId, 'CUSTOMER_PO', fiscalYearId, transaction);
  } catch (error) {
    console.warn('Order number from format master failed, using fallback:', error.message);
    const today = new Date();
    const year = today.getFullYear();
    const prefix = `CPO${year}`;
    const last = await db.CustomerPO.findOne({
      where: {
        company_id: companyId,
        order_number: { [Op.like]: `${prefix}%` }
      },
      order: [['order_number', 'DESC']],
      ...(transaction ? { transaction } : {})
    });

    let sequence = 1;
    if (last?.order_number) {
      const lastSequence = parseInt(String(last.order_number).slice(-4), 10) || 0;
      sequence = lastSequence + 1;
    }

    return `${prefix}${String(sequence).padStart(4, '0')}`;
  }
};

const generateProductionOrderNumber = async (companyId, fiscalYearId, transaction = null) => {
  try {
    return await generateNextNumber(companyId, 'PRODUCTION_ORDER', fiscalYearId, transaction);
  } catch (error) {
    console.warn('Production order number from format master failed, using fallback:', error.message);
    const today = new Date();
    const year = today.getFullYear();
    const prefix = `PRO${year}`;
    const lastPO = await db.ProductionOrder.findOne({
      where: {
        company_id: companyId,
        production_order_number: { [Op.like]: `${prefix}%` }
      },
      order: [['production_order_number', 'DESC']],
      ...(transaction ? { transaction } : {})
    });
    let sequence = 1;
    if (lastPO) {
      const lastSequence = parseInt(lastPO.production_order_number.slice(-4)) || 0;
      sequence = lastSequence + 1;
    }
    return `${prefix}${sequence.toString().padStart(4, '0')}`;
  }
};

exports.getAll = asyncHandler(async (req, res) => {
  const { page = 1, limit = 25, search, status, companyId, plantId, fiscalYearId, customerId, customer_id, date_from, date_to } = req.query;
  const { limit: limitNum, offset } = getPagination(page, limit);

  const where = {};
  
  if (companyId) {
    where.company_id = companyId;
  } else if (req.user && !req.user.isSuperAdmin) {
    where.company_id = req.user.companyId;
  }

  if (plantId) {
    where.plant_id = plantId;
  }

  if (fiscalYearId) {
    where.fiscal_year_id = fiscalYearId;
  }

  const customerIdValue = customerId || customer_id;
  if (customerIdValue) {
    where.customer_id = customerIdValue;
  }

  if (status) {
    // Handle comma-separated status values (e.g., "APPROVED,RELEASED")
    if (typeof status === 'string' && status.includes(',')) {
      const statusArray = status.split(',').map(s => s.trim()).filter(s => s);
      if (statusArray.length > 0) {
        where.status = { [Op.in]: statusArray };
      }
    } else {
      where.status = status;
    }
  }

  // Date range filtering
  if (date_from || date_to) {
    where.po_date = {};
    if (date_from) {
      where.po_date[Op.gte] = date_from;
    }
    if (date_to) {
      where.po_date[Op.lte] = date_to;
    }
  }

  if (search) {
    where[Op.or] = [
      { po_number: { [Op.like]: `%${search}%` } },
      { '$customer.name$': { [Op.like]: `%${search}%` } },
      { '$customer.bp_number$': { [Op.like]: `%${search}%` } },
      { '$customer.legal_name$': { [Op.like]: `%${search}%` } },
    ];
  }

  const records = await db.CustomerPO.findAndCountAll({
    where,
    include: [
      { model: db.Company, as: 'company', attributes: ['id', 'code', 'name'] },
      { model: db.Plant, as: 'plant', attributes: ['id', 'code', 'name'] },
      { model: db.BusinessPartner, as: 'customer', attributes: ['id', 'bp_number', 'name'] }
    ],
    limit: limitNum,
    offset,
    order: [['po_date', 'DESC'], ['id', 'DESC']]
  });

  // Get items count and conversion statistics for each PO efficiently
  const poIds = records.rows.map(po => po.id);
  
  // Get items count
  const itemsCounts = await db.CustomerPOItem.findAll({
    attributes: [
      'po_id',
      [db.sequelize.fn('COUNT', db.sequelize.col('id')), 'count']
    ],
    where: {
      po_id: { [Op.in]: poIds }
    },
    group: ['po_id'],
    raw: true
  });

  // Get conversion statistics (count of distinct items that have production orders)
  const convertedItemsData = await db.ProductionOrder.findAll({
    attributes: [
      'customer_po_id',
      'customer_po_item_id'
    ],
    where: {
      customer_po_id: { [Op.in]: poIds },
      customer_po_item_id: { [Op.ne]: null }
    },
    raw: true
  });

  // Count distinct items per PO
  const convertedCountMap = {};
  const itemSetMap = {};
  convertedItemsData.forEach(item => {
    if (!itemSetMap[item.customer_po_id]) {
      itemSetMap[item.customer_po_id] = new Set();
    }
    itemSetMap[item.customer_po_id].add(item.customer_po_item_id);
  });
  
  Object.keys(itemSetMap).forEach(poId => {
    convertedCountMap[parseInt(poId)] = itemSetMap[poId].size;
  });

  // Create items count map
  const itemsCountMap = {};
  itemsCounts.forEach(item => {
    itemsCountMap[item.po_id] = parseInt(item.count, 10);
  });

  // Add items count and conversion stats to each record
  const items = records.rows.map(po => {
    const poData = po.toJSON();
    const totalItems = itemsCountMap[po.id] || 0;
    const convertedItems = convertedCountMap[po.id] || 0;
    const pendingItems = totalItems - convertedItems;
    
    poData.items_count = totalItems;
    poData.converted_items_count = convertedItems;
    poData.pending_items_count = pendingItems;
    poData.conversion_status = totalItems > 0 
      ? (convertedItems === totalItems ? 'FULLY_CONVERTED' : convertedItems > 0 ? 'PARTIALLY_CONVERTED' : 'PENDING')
      : 'NO_ITEMS';
    
    return poData;
  });

  const response = getPagingData({ ...records, rows: items }, page, limitNum);
  sendResponse(res, 200, true, 'Customer POs retrieved successfully', response);
});

exports.getById = asyncHandler(async (req, res) => {
  const { id } = req.params;

  const po = await db.CustomerPO.findByPk(id, {
    include: [
      { 
        model: db.Company, 
        as: 'company',
        include: [
          { model: db.State, as: 'state', attributes: ['id', 'state_code', 'state_name', 'gst_state_code'] }
        ]
      },
      { model: db.Plant, as: 'plant' },
      { model: db.FiscalYear, as: 'fiscalYear' },
      { model: db.BusinessPartner, as: 'customer' },
      { 
        model: db.BPAddress, 
        as: 'customerAddress',
        include: [
          { model: db.State, as: 'state', attributes: ['id', 'state_code', 'state_name', 'gst_state_code'] }
        ]
      },
      { model: db.CustomerQuotation, as: 'customerQuotation' },
      { model: db.Currency, as: 'currency' },
      {
        model: db.CustomerPOItem,
        as: 'items',
        include: [
          { 
            model: db.Material, 
            as: 'material',
            attributes: ['id', 'material_number', 'description', 'material_type', 'material_type_id', 'gross_weight', 'net_weight', 'weight_uom_id', 'thickness', 'thickness_id', 'grade_id'],
            include: [
              { model: db.MaterialThickness, as: 'materialThickness', attributes: ['id', 'thickness_code', 'thickness_value', 'thickness_unit', 'description'] },
              { model: db.MaterialGrade, as: 'materialGrade', attributes: ['id', 'grade_code', 'grade_name', 'description'] },
              { model: db.MaterialType, as: 'materialType', attributes: ['id', 'type_code', 'type_name'] },
              { model: db.UOM, as: 'weightUOM', attributes: ['id', 'uom_code', 'uom_name'] },
            ]
          },
          { model: db.UOM, as: 'uom' },
          { model: db.StorageLocation, as: 'storageLocation' }
        ],
        order: [['item_number', 'ASC']]
      }
    ]
  });

  if (!po) {
    return sendError(res, 'Customer PO not found', 404);
  }

  // Convert to JSON first
  const poData = po.toJSON ? po.toJSON() : po;

  // Check if production orders exist for each item
  // Note: isConverted is computed dynamically, not stored in customer_po_items table
  if (poData.items && poData.items.length > 0) {
    const itemIds = poData.items.map(item => item.id);
    const plantId = poData.plant_id || poData.plant?.id || po.plant_id;
    const effectiveCompanyId = poData.company_id || poData.company?.id || po.company_id;
    const materialIds = poData.items
      .map(it => it?.material?.id || it?.material_id)
      .filter(Boolean);
    
    // Query ProductionOrder table to find which items have been converted
    // Note: isConverted is computed dynamically by checking if ProductionOrder exists
    // with customer_po_item_id matching the item.id - no database column needed in customer_po_items
    // We check the production_order table for records where customer_po_item_id matches the item ID
    const productionOrders = await db.ProductionOrder.findAll({
      where: {
        customer_po_id: id,
        customer_po_item_id: { [Op.in]: itemIds }
      },
      attributes: ['id', 'customer_po_item_id', 'production_order_number', 'status'],
      raw: true
    });
    
    // Debug: Log the query results
    if (productionOrders.length > 0) {
      console.log(`[Customer PO ${id}] Found ${productionOrders.length} production order(s) for items:`, 
        productionOrders.map(po => `Item ${po.customer_po_item_id} -> PO ${po.production_order_number}`).join(', '));
    } else {
      console.log(`[Customer PO ${id}] No production orders found for items:`, itemIds);
    }

    // Create a map of item_id -> production order
    const poMap = {};
    productionOrders.forEach(prodOrder => {
      if (prodOrder.customer_po_item_id) {
        poMap[prodOrder.customer_po_item_id] = {
          id: prodOrder.id,
          production_order_number: prodOrder.production_order_number,
          status: prodOrder.status
        };
      }
    });

    // Precompute active BOM availability for each material (so UI can block conversion without trial-and-error)
    const activeBOMs = await db.BOMHeader.findAll({
      where: {
        company_id: effectiveCompanyId,
        plant_id: plantId,
        status: 'ACTIVE',
        material_id: { [Op.in]: materialIds }
      },
      attributes: ['id', 'material_id'],
      raw: true
    });
    const activeBOMMaterialSet = new Set((activeBOMs || []).map(b => b.material_id));

    // Add production order info to each item
    // isConverted is true if a ProductionOrder exists with customer_po_item_id = item.id
    poData.items = poData.items.map(item => {
      const itemData = item.toJSON ? item.toJSON() : item;
      const hasProductionOrder = !!poMap[item.id];
      const material = itemData.material || {};
      const materialTypeCode = material?.materialType?.type_code || null;
      const isFinishedGood = material?.material_type === 'FINISHED_GOOD' || materialTypeCode === 'FIN_GOOD';
      const isSemiFinished = material?.material_type === 'SEMI_FINISHED' || materialTypeCode === 'SEMI_FIN';
      const hasActiveBOM = material?.id ? activeBOMMaterialSet.has(material.id) : false;
      itemData.productionOrder = poMap[item.id] || null;
      itemData.isConverted = hasProductionOrder; // Computed dynamically, not a DB column
      itemData.hasActiveBOM = hasActiveBOM;
      itemData.isConvertibleToProduction = Boolean(!hasProductionOrder && (isFinishedGood || isSemiFinished) && hasActiveBOM);
      return itemData;
    });
  }

  sendResponse(res, 200, true, 'Customer PO retrieved successfully', poData);
});

/** GET /customer/po/:id/process-flow — derived workflow from linked documents (no duplicate business data). */
exports.getProcessFlow = asyncHandler(async (req, res) => {
  const id = parseInt(req.params.id, 10);
  if (!(id > 0)) return sendError(res, 'Invalid Customer PO id', 400);
  const companyId = req.user?.companyId ?? req.user?.company_id;
  const isSuperAdmin = Boolean(req.user?.isSuperAdmin || req.user?.is_super_admin);
  const data = await customerPOProcessFlowService.buildProcessFlow(id, { companyId, isSuperAdmin });
  if (!data) return sendError(res, 'Customer PO not found', 404);
  if (data.forbidden) return sendError(res, 'Forbidden', 403);
  sendResponse(res, 200, true, 'Customer PO process flow loaded', data);
});

// Get all released PO items with pagination, filters, and sorting
exports.getReleasedPOItems = asyncHandler(async (req, res) => {
  const { page = 1, limit = 25, search, companyId, plantId, customerId, materialType, status, poNumber, dateFrom, dateTo, sortBy = 'po_date', sortOrder = 'DESC' } = req.query;
  const { limit: limitNum, offset } = getPagination(page, limit);

  const where = {
    status: 'RELEASED'
  };
  
  if (companyId) {
    where.company_id = companyId;
  } else if (req.user && !req.user.isSuperAdmin) {
    where.company_id = req.user.companyId;
  }

  if (plantId) {
    where.plant_id = plantId;
  }

  if (customerId) {
    where.customer_id = customerId;
  }

  if (search) {
    where[Op.or] = [
      { po_number: { [Op.like]: `%${search}%` } }
    ];
  }

  if (poNumber) {
    where.po_number = { [Op.like]: `%${poNumber}%` };
  }

  if (dateFrom || dateTo) {
    where.po_date = {};
    if (dateFrom) {
      where.po_date[Op.gte] = new Date(dateFrom);
    }
    if (dateTo) {
      where.po_date[Op.lte] = new Date(dateTo + 'T23:59:59');
    }
  }

  // Get released POs
  const pos = await db.CustomerPO.findAll({
    where,
    include: [
      { model: db.Company, as: 'company', attributes: ['id', 'code', 'name'] },
      { model: db.Plant, as: 'plant', attributes: ['id', 'code', 'name'] },
      { model: db.BusinessPartner, as: 'customer', attributes: ['id', 'bp_number', 'name'] }
    ],
    order: [[sortBy, sortOrder]]
  });

  const poIds = pos.map(po => po.id);

  if (poIds.length === 0) {
    return sendResponse(res, 200, true, 'Released PO items retrieved successfully', {
      items: [],
      totalItems: 0,
      totalPages: 0,
      currentPage: parseInt(page),
      pageSize: limitNum
    });
  }

  // Build where clause for items
  const itemWhere = {
    po_id: { [Op.in]: poIds }
  };

  if (materialType) {
    itemWhere[Op.or] = [
      { '$material.materialType.type_code$': materialType },
      { '$material.materialType.type_name$': { [Op.like]: `%${materialType}%` } }
    ];
  }

  if (status === 'converted') {
    // Items that have production orders
    const productionOrders = await db.ProductionOrder.findAll({
      attributes: ['customer_po_item_id'],
      where: {
        customer_po_id: { [Op.in]: poIds }
      },
      raw: true
    });
    const convertedItemIds = productionOrders.map(po => po.customer_po_item_id).filter(Boolean);
    if (convertedItemIds.length > 0) {
      itemWhere.id = { [Op.in]: convertedItemIds };
    } else {
      // No converted items, return empty
      return sendResponse(res, 200, true, 'Released PO items retrieved successfully', {
        items: [],
        totalItems: 0,
        totalPages: 0,
        currentPage: parseInt(page),
        pageSize: limitNum
      });
    }
  } else if (status === 'pending') {
    // Items that don't have production orders
    const productionOrders = await db.ProductionOrder.findAll({
      attributes: ['customer_po_item_id'],
      where: {
        customer_po_id: { [Op.in]: poIds }
      },
      raw: true
    });
    const convertedItemIds = productionOrders.map(po => po.customer_po_item_id).filter(Boolean);
    if (convertedItemIds.length > 0) {
      itemWhere.id = { [Op.notIn]: convertedItemIds };
    }
  }

  if (search) {
    if (!itemWhere[Op.or]) {
      itemWhere[Op.or] = [];
    }
    itemWhere[Op.or].push(
      { '$material.material_number$': { [Op.like]: `%${search}%` } },
      { '$material.description$': { [Op.like]: `%${search}%` } },
      { '$customerPO.po_number$': { [Op.like]: `%${search}%` } }
    );
  }

  // Build order clause
  let orderClause = [];
  if (sortBy === 'po_date' || sortBy === 'po_number') {
    orderClause = [[{ model: db.CustomerPO, as: 'customerPO' }, sortBy, sortOrder]];
  } else if (sortBy === 'material_number' || sortBy === 'description') {
    orderClause = [[{ model: db.Material, as: 'material' }, sortBy, sortOrder]];
  } else {
    orderClause = [[sortBy, sortOrder]];
  }

  // Get items with pagination
  const { count, rows: items } = await db.CustomerPOItem.findAndCountAll({
    where: itemWhere,
    include: [
      {
        model: db.CustomerPO,
        as: 'customerPO',
        attributes: ['id', 'po_number', 'po_date', 'delivery_date', 'status'],
        include: [
          { model: db.Company, as: 'company', attributes: ['id', 'code', 'name'] },
          { model: db.Plant, as: 'plant', attributes: ['id', 'code', 'name'] },
          { model: db.BusinessPartner, as: 'customer', attributes: ['id', 'bp_number', 'name'] }
        ]
      },
      {
        model: db.Material,
        as: 'material',
        attributes: ['id', 'material_number', 'description', 'material_type_id'],
        include: [
          {
            model: db.MaterialType,
            as: 'materialType',
            attributes: ['id', 'type_code', 'type_name']
          }
        ]
      },
      {
        model: db.UOM,
        as: 'uom',
        attributes: ['id', 'uom_code', 'uom_name']
      }
    ],
    limit: limitNum,
    offset,
    order: orderClause,
    distinct: true
  });

  // Get production orders for these items to determine conversion status
  const itemIds = items.map(item => item.id);
  const productionOrders = await db.ProductionOrder.findAll({
    where: {
      customer_po_item_id: { [Op.in]: itemIds }
    },
    attributes: ['id', 'customer_po_item_id', 'production_order_number', 'status'],
    raw: true
  });

  const poMap = {};
  productionOrders.forEach(prodOrder => {
    if (prodOrder.customer_po_item_id) {
      poMap[prodOrder.customer_po_item_id] = {
        id: prodOrder.id,
        production_order_number: prodOrder.production_order_number,
        status: prodOrder.status
      };
    }
  });

  // Add conversion status to items
  const itemsWithStatus = items.map(item => {
    const itemData = item.toJSON ? item.toJSON() : item;
    const hasProductionOrder = !!poMap[item.id];
    itemData.productionOrder = poMap[item.id] || null;
    itemData.isConverted = hasProductionOrder;
    return itemData;
  });

  // Calculate statistics
  const allItems = await db.CustomerPOItem.findAll({
    where: {
      po_id: { [Op.in]: poIds }
    },
    attributes: ['id']
  });

  const allItemIds = allItems.map(item => item.id);
  const allProductionOrders = await db.ProductionOrder.findAll({
    where: {
      customer_po_item_id: { [Op.in]: allItemIds }
    },
    attributes: ['customer_po_item_id'],
    raw: true
  });

  const convertedItemIds = new Set(allProductionOrders.map(po => po.customer_po_item_id).filter(Boolean));
  const totalItems = allItems.length;
  const convertedItems = convertedItemIds.size;
  const pendingItems = totalItems - convertedItems;

  const pagingData = getPagingData({ count, rows: itemsWithStatus }, page, limitNum);

  sendResponse(res, 200, true, 'Released PO items retrieved successfully', {
    items: itemsWithStatus,
    totalItems: count,
    statistics: {
      total: totalItems,
      converted: convertedItems,
      pending: pendingItems
    },
    ...pagingData
  });
});

exports.create = asyncHandler(async (req, res) => {
  const data = req.body;
  const userId = req.user?.userId || null;
  const companyId = req.user?.companyId;

  if (!companyId) {
    return sendError(res, 'Company ID is required', 400);
  }

    if (!data.plant_id || !data.fiscal_year_id || !data.customer_id) {
      return sendError(res, 'Plant ID, Fiscal Year ID, and Customer ID are required', 400);
    }

    if (!data.po_number || data.po_number.trim() === '') {
      return sendError(res, 'PO Number is required', 400);
    }

    if (!data.po_date) {
      return sendError(res, 'PO Date is required', 400);
    }

    if (!data.items || !Array.isArray(data.items) || data.items.length === 0) {
      return sendError(res, 'At least one item is required', 400);
    }

  const transaction = await db.sequelize.transaction();

  try {
    // Validate that referenced records exist
    const plant = await db.Plant.findByPk(data.plant_id);
    if (!plant) {
      await transaction.rollback();
      return sendError(res, 'Plant not found', 404);
    }

    const fiscalYear = await db.FiscalYear.findByPk(data.fiscal_year_id);
    if (!fiscalYear) {
      await transaction.rollback();
      return sendError(res, 'Fiscal Year not found', 404);
    }

    const customer = await db.BusinessPartner.findByPk(data.customer_id);
    if (!customer) {
      await transaction.rollback();
      return sendError(res, 'Customer not found', 404);
    }

    // Validate optional foreign keys if provided
    if (data.customer_address_id) {
      const customerAddress = await db.BPAddress.findByPk(data.customer_address_id);
      if (!customerAddress) {
        await transaction.rollback();
        return sendError(res, 'Customer Address not found', 404);
      }
    }

    if (data.cq_id) {
      const customerQuotation = await db.CustomerQuotation.findByPk(data.cq_id);
      if (!customerQuotation) {
        await transaction.rollback();
        return sendError(res, 'Customer Quotation not found', 404);
      }
    }

    if (data.currency_id) {
      const currency = await db.Currency.findByPk(data.currency_id);
      if (!currency) {
        await transaction.rollback();
        return sendError(res, 'Currency not found', 404);
      }
    }

    // Check if PO number already exists for this company, plant, and fiscal year
    const existingPO = await db.CustomerPO.findOne({
      where: {
        company_id: companyId,
        plant_id: data.plant_id,
        fiscal_year_id: data.fiscal_year_id,
        po_number: data.po_number.trim(),
      },
      transaction,
    });

    if (existingPO) {
      await transaction.rollback();
      return sendError(res, `PO Number "${data.po_number}" already exists for this company, plant, and fiscal year`, 400);
    }

    data.company_id = companyId;
    data.created_by = userId;
    data.updated_by = userId;

    // Auto-generate internal order number (separate from customer po_number)
    if (!data.order_number || String(data.order_number).trim() === '') {
      data.order_number = await generateOrderNumber(companyId, data.fiscal_year_id, transaction);
    } else {
      data.order_number = String(data.order_number).trim();
    }

    // Sanitize data: convert empty strings to null for optional fields
    if (data.customer_address_id === '' || data.customer_address_id === null || data.customer_address_id === undefined) {
      data.customer_address_id = null;
    }
    if (data.cq_id === '' || data.cq_id === null || data.cq_id === undefined) {
      data.cq_id = null;
    }
    if (data.currency_id === '' || data.currency_id === null || data.currency_id === undefined) {
      data.currency_id = null;
    }
    if (data.payment_terms === '') {
      data.payment_terms = null;
    }
    if (data.delivery_terms === '') {
      data.delivery_terms = null;
    }
    if (data.delivery_date === '' || data.delivery_date === null || data.delivery_date === undefined) {
      data.delivery_date = null;
    }
    if (data.remarks === '') {
      data.remarks = null;
    }

    const { items, ...poData } = data;
    const { isInterState } = await getTaxTypeByStateCode(companyId, data.customer_id, transaction);

    poData.sub_total = 0;
    poData.discount_amount = 0;
    poData.tax_amount = 0;
    poData.total_amount = 0;

    const po = await db.CustomerPO.create(poData, { transaction });

    let subTotal = 0;
    let discountAmount = 0;
    let taxAmount = 0;

    if (items && Array.isArray(items)) {
      for (let i = 0; i < items.length; i++) {
        const item = items[i];
        
        // Validate item foreign keys
        if (!item.material_id) {
          await transaction.rollback();
          return sendError(res, `Material ID is required for item ${i + 1}`, 400);
        }
        
        const material = await db.Material.findByPk(item.material_id);
        if (!material) {
          await transaction.rollback();
          return sendError(res, `Material not found for item ${i + 1}`, 404);
        }

        if (!item.uom_id) {
          await transaction.rollback();
          return sendError(res, `UOM ID is required for item ${i + 1}`, 400);
        }

        const uom = await db.UOM.findByPk(item.uom_id);
        if (!uom) {
          await transaction.rollback();
          return sendError(res, `UOM not found for item ${i + 1}`, 404);
        }

        if (item.storage_location_id) {
          const storageLocation = await db.StorageLocation.findByPk(item.storage_location_id);
          if (!storageLocation) {
            await transaction.rollback();
            return sendError(res, `Storage Location not found for item ${i + 1}`, 404);
          }
        }

        const quantity = toNum(item.quantity);
        const unitPrice = toNum(item.unit_price);
        const discountPercent = toNum(item.discount_percentage);
        const taxRateInput = toNum(item.tax_rate);
        let cgstRate = toNum(item.cgst_rate);
        let sgstRate = toNum(item.sgst_rate);
        let igstRate = toNum(item.igst_rate);

        const lineTotal = quantity * unitPrice;
        const lineDiscount = (lineTotal * discountPercent) / 100;
        const taxableAmount = lineTotal - lineDiscount;
        if (isInterState) {
          if (igstRate <= 0) igstRate = taxRateInput;
          cgstRate = 0;
          sgstRate = 0;
        } else {
          if (cgstRate <= 0 && sgstRate <= 0 && taxRateInput > 0) {
            cgstRate = taxRateInput / 2;
            sgstRate = taxRateInput / 2;
          }
          igstRate = 0;
        }
        const cgstAmount = (taxableAmount * cgstRate) / 100;
        const sgstAmount = (taxableAmount * sgstRate) / 100;
        const igstAmount = (taxableAmount * igstRate) / 100;
        const lineTax = cgstAmount + sgstAmount + igstAmount;
        const taxRate = cgstRate + sgstRate + igstRate;

        // Determine tax_type: IGST if igst_rate > 0, otherwise GST (CGST+SGST)
        const taxType = (igstRate > 0 || igstAmount > 0) ? 'IGST' : 'GST';

        subTotal += lineTotal;
        discountAmount += lineDiscount;
        taxAmount += lineTax;

        await db.CustomerPOItem.create({
          po_id: po.id,
          item_number: i + 1,
          material_id: item.material_id,
          description: item.description,
          quantity: quantity,
          delivered_quantity: 0,
          uom_id: item.uom_id,
          unit_price: unitPrice,
          discount_percentage: discountPercent,
          discount_amount: lineDiscount,
          net_amount: taxableAmount,
          tax_type: taxType,
          tax_rate: taxRate,
          tax_amount: lineTax,
          cgst_rate: cgstRate,
          cgst_amount: cgstAmount,
          sgst_rate: sgstRate,
          sgst_amount: sgstAmount,
          igst_rate: igstRate,
          igst_amount: igstAmount,
          total_amount: taxableAmount + lineTax,
          hsn_code: item.hsn_code,
          storage_location_id: item.storage_location_id,
          delivery_date: item.delivery_date || null
        }, { transaction });
      }
    }

    await po.update(
      {
        sub_total: subTotal,
        discount_amount: discountAmount,
        tax_amount: taxAmount,
        total_amount: subTotal - discountAmount + taxAmount
      },
      { transaction }
    );

    await transaction.commit();

    const createdPO = await db.CustomerPO.findByPk(po.id, {
      include: [
        { model: db.Company, as: 'company' },
        { model: db.Plant, as: 'plant' },
        { model: db.FiscalYear, as: 'fiscalYear' },
        { model: db.BusinessPartner, as: 'customer' },
        {
          model: db.CustomerPOItem,
          as: 'items',
          include: [
            { model: db.Material, as: 'material' },
            { model: db.UOM, as: 'uom' }
          ]
        }
      ]
    });

    sendResponse(res, 201, true, 'Customer PO created successfully', createdPO);
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
});

exports.update = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const data = req.body;
  const userId = req.user?.userId || null;

  const po = await db.CustomerPO.findByPk(id);

  if (!po) {
    return sendError(res, 'Customer PO not found', 404);
  }

  // Validate business rules
  const businessRuleValidation = await businessRuleValidationService.customerPO.canUpdate(po);
  if (!businessRuleValidation.valid) {
    return sendError(res, businessRuleValidation.errors.join(', '), 400);
  }

  const transaction = await db.sequelize.transaction();

  try {
    data.updated_by = userId;

    const { items, ...poData } = data;
    const effectiveCustomerId = poData.customer_id || po.customer_id;
    const { isInterState } = await getTaxTypeByStateCode(po.company_id, effectiveCustomerId, transaction);

    poData.sub_total = 0;
    poData.discount_amount = 0;
    poData.tax_amount = 0;
    poData.total_amount = 0;

    await po.update(poData, { transaction });

    await db.CustomerPOItem.destroy({ where: { po_id: po.id }, transaction });

    let subTotal = 0;
    let discountAmount = 0;
    let taxAmount = 0;

    if (items && Array.isArray(items)) {
      for (let i = 0; i < items.length; i++) {
        const item = items[i];
        const quantity = toNum(item.quantity);
        const unitPrice = toNum(item.unit_price);
        const discountPercent = toNum(item.discount_percentage);
        const taxRateInput = toNum(item.tax_rate);
        let cgstRate = toNum(item.cgst_rate);
        let sgstRate = toNum(item.sgst_rate);
        let igstRate = toNum(item.igst_rate);

        const lineTotal = quantity * unitPrice;
        const lineDiscount = (lineTotal * discountPercent) / 100;
        const taxableAmount = lineTotal - lineDiscount;
        if (isInterState) {
          if (igstRate <= 0) igstRate = taxRateInput;
          cgstRate = 0;
          sgstRate = 0;
        } else {
          if (cgstRate <= 0 && sgstRate <= 0 && taxRateInput > 0) {
            cgstRate = taxRateInput / 2;
            sgstRate = taxRateInput / 2;
          }
          igstRate = 0;
        }
        const cgstAmount = (taxableAmount * cgstRate) / 100;
        const sgstAmount = (taxableAmount * sgstRate) / 100;
        const igstAmount = (taxableAmount * igstRate) / 100;
        const lineTax = cgstAmount + sgstAmount + igstAmount;
        const taxRate = cgstRate + sgstRate + igstRate;

        // Determine tax_type: IGST if igst_rate > 0, otherwise GST (CGST+SGST)
        const taxType = (igstRate > 0 || igstAmount > 0) ? 'IGST' : 'GST';

        subTotal += lineTotal;
        discountAmount += lineDiscount;
        taxAmount += lineTax;

        await db.CustomerPOItem.create({
          po_id: po.id,
          item_number: i + 1,
          material_id: item.material_id,
          description: item.description,
          quantity: quantity,
          delivered_quantity: item.delivered_quantity || 0,
          uom_id: item.uom_id,
          unit_price: unitPrice,
          discount_percentage: discountPercent,
          discount_amount: lineDiscount,
          net_amount: taxableAmount,
          tax_type: taxType,
          tax_rate: taxRate,
          tax_amount: lineTax,
          cgst_rate: cgstRate,
          cgst_amount: cgstAmount,
          sgst_rate: sgstRate,
          sgst_amount: sgstAmount,
          igst_rate: igstRate,
          igst_amount: igstAmount,
          total_amount: taxableAmount + lineTax,
          hsn_code: item.hsn_code,
          storage_location_id: item.storage_location_id,
          delivery_date: item.delivery_date || null
        }, { transaction });
      }
    }

    await po.update(
      {
        sub_total: subTotal,
        discount_amount: discountAmount,
        tax_amount: taxAmount,
        total_amount: subTotal - discountAmount + taxAmount
      },
      { transaction }
    );

    await transaction.commit();

    const updatedPO = await db.CustomerPO.findByPk(po.id, {
      include: [
        { model: db.Company, as: 'company' },
        { model: db.Plant, as: 'plant' },
        { model: db.FiscalYear, as: 'fiscalYear' },
        { model: db.BusinessPartner, as: 'customer' },
        {
          model: db.CustomerPOItem,
          as: 'items',
          include: [
            { model: db.Material, as: 'material' },
            { model: db.UOM, as: 'uom' }
          ]
        }
      ]
    });

    sendResponse(res, 200, true, 'Customer PO updated successfully', updatedPO);
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
});

exports.delete = asyncHandler(async (req, res) => {
  const { id } = req.params;

  const po = await db.CustomerPO.findByPk(id);

  if (!po) {
    return sendError(res, 'Customer PO not found', 404);
  }

  // Validate business rules
  const businessRuleValidation = await businessRuleValidationService.customerPO.canDelete(po);
  if (!businessRuleValidation.valid) {
    return sendError(res, businessRuleValidation.errors.join(', '), 400);
  }

  await db.CustomerPOItem.destroy({ where: { po_id: po.id } });
  await po.destroy();

  sendResponse(res, 200, true, 'Customer PO deleted successfully', null);
});

exports.submit = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const userId = req.user?.userId || null;

  const po = await db.CustomerPO.findByPk(id, {
    include: [{ model: db.CustomerPOItem, as: 'items' }]
  });

  if (!po) {
    return sendError(res, 'Customer PO not found', 404);
  }

  // Validate status transition
  const statusValidation = statusValidationService.validateStatusTransition('customerPO', po.status, 'SUBMITTED');
  if (!statusValidation.valid) {
    return sendError(res, statusValidation.message, 400);
  }

  // Validate business rules
  const businessRuleValidation = await businessRuleValidationService.customerPO.canSubmit(po);
  if (!businessRuleValidation.valid) {
    return sendError(res, businessRuleValidation.errors.join(', '), 400);
  }

  await po.update({
    status: 'SUBMITTED',
    updated_by: userId
  });

  const updatedPO = await db.CustomerPO.findByPk(po.id, {
    include: [
      { model: db.Company, as: 'company' },
      { model: db.Plant, as: 'plant' },
      { model: db.BusinessPartner, as: 'customer' }
    ]
  });

  sendResponse(res, 200, true, 'Customer PO submitted successfully', updatedPO);
});

exports.approve = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const userId = req.user?.userId || null;

  const po = await db.CustomerPO.findByPk(id, {
    include: [
      { model: db.CustomerPOItem, as: 'items', include: [{ model: db.Material, as: 'material' }] }
    ]
  });

  if (!po) {
    return sendError(res, 'Customer PO not found', 404);
  }

  // Validate status transition
  const statusValidation = statusValidationService.validateStatusTransition('customerPO', po.status, 'APPROVED');
  if (!statusValidation.valid) {
    return sendError(res, statusValidation.message, 400);
  }

  // Validate business rules
  const businessRuleValidation = await businessRuleValidationService.customerPO.canApprove(po);
  if (!businessRuleValidation.valid) {
    return sendError(res, businessRuleValidation.errors.join(', '), 400);
  }

  await po.update({
    status: 'APPROVED',
    updated_by: userId
  });

  const updatedPO = await db.CustomerPO.findByPk(po.id, {
    include: [
      { model: db.Company, as: 'company' },
      { model: db.Plant, as: 'plant' },
      { model: db.BusinessPartner, as: 'customer' }
    ]
  });

  sendResponse(res, 200, true, 'Customer PO approved successfully', updatedPO);
});

exports.release = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const userId = req.user?.userId || null;

  const po = await db.CustomerPO.findByPk(id, {
    include: [
      { model: db.CustomerPOItem, as: 'items', include: [{ model: db.Material, as: 'material' }] }
    ]
  });

  if (!po) {
    return sendError(res, 'Customer PO not found', 404);
  }

  // Validate status transition
  const statusValidation = statusValidationService.validateStatusTransition('customerPO', po.status, 'RELEASED');
  if (!statusValidation.valid) {
    return sendError(res, statusValidation.message, 400);
  }

  // Validate business rules
  const businessRuleValidation = await businessRuleValidationService.customerPO.canRelease(po);
  if (!businessRuleValidation.valid) {
    return sendError(res, businessRuleValidation.errors.join(', '), 400);
  }

  await po.update({
    status: 'RELEASED',
    updated_by: userId
  });

  const updatedPO = await db.CustomerPO.findByPk(po.id, {
    include: [
      { model: db.Company, as: 'company' },
      { model: db.Plant, as: 'plant' },
      { model: db.BusinessPartner, as: 'customer' }
    ]
  });

  sendResponse(res, 200, true, 'Customer PO released successfully', updatedPO);
});

/**
 * Convert Customer PO to Production Orders
 * Creates Production Orders for each finished good item in the Customer PO
 */
exports.convertToProductionOrders = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const userId = req.user?.userId || null;
  const companyId = req.user?.companyId;

  if (!companyId) {
    return sendError(res, 'Company ID is required', 400);
  }

  const customerPO = await db.CustomerPO.findByPk(id, {
    include: [
      {
        model: db.CustomerPOItem,
        as: 'items',
        include: [
          {
            model: db.Material,
            as: 'material',
            include: [
              {
                model: db.MaterialType,
                as: 'materialType',
                attributes: ['id', 'type_code', 'type_name']
              }
            ]
          },
          {
            model: db.UOM,
            as: 'uom',
            attributes: ['id', 'uom_code', 'uom_name']
          }
        ]
      },
      {
        model: db.Plant,
        as: 'plant',
        attributes: ['id', 'code', 'name']
      }
    ]
  });

  if (!customerPO) {
    return sendError(res, 'Customer PO not found', 404);
  }

  // Only APPROVED or RELEASED Customer POs can be converted
  if (customerPO.status !== 'APPROVED' && customerPO.status !== 'RELEASED') {
    return sendError(res, 'Only APPROVED or RELEASED Customer POs can be converted to Production Orders', 400);
  }

  if (!customerPO.items || customerPO.items.length === 0) {
    return sendError(res, 'Customer PO has no items to convert', 400);
  }

  const transaction = await db.sequelize.transaction();

  try {
    const createdProductionOrders = [];
    const errors = [];

    // BOM explosion helper
    // Recursive BOM explosion function to handle multi-level BOMs with sub-assemblies
    const explodeBOM = async (bomId, orderQuantity, baseQuantity, processedBOMs = new Set(), level = 0) => {
      // Prevent infinite loops
      if (processedBOMs.has(bomId)) {
        return [];
      }
      processedBOMs.add(bomId);

      const bom = await db.BOMHeader.findByPk(bomId, {
        include: [
          {
            model: db.BOMComponent,
            as: 'components',
            where: { status: 'ACTIVE' },
            required: false,
            include: [
              { 
                model: db.Material, 
                as: 'material',
                attributes: ['id', 'material_number', 'description', 'material_type', 'base_uom_id']
              },
              { 
                model: db.UOM, 
                as: 'componentUOM',
                attributes: ['id', 'uom_code', 'uom_name']
              }
            ]
          }
        ],
        transaction
      });

      if (!bom || !bom.components || bom.components.length === 0) {
        return [];
      }

      const multiplier = parseFloat(orderQuantity) / parseFloat(baseQuantity || 1);
      const components = [];

      for (const comp of bom.components) {
        const componentMaterial = comp.material;
        if (!componentMaterial) continue;

        const requiredQty = parseFloat(comp.component_quantity || comp.quantity || 0) * multiplier;
        const componentUOMId = comp.component_uom_id || comp.uom_id;

        // Add the component
        components.push({
          bom_component_id: comp.id,
          material_id: comp.material_id,
          required_quantity: requiredQty,
          uom_id: componentUOMId,
          issue_type: comp.issue_type || 'MANUAL',
          storage_location: comp.storage_location || null,
          level: level,
          parent_material_id: level > 0 ? bom.material_id : null
        });

        // Check if this component material has its own BOM (it's a sub-assembly)
        const isSubAssembly = componentMaterial.material_type === 'SEMI_FINISHED' || 
                              componentMaterial.material_type === 'FINISHED_GOOD';
        
        if (isSubAssembly) {
          // Find active BOM for this sub-assembly material
          const subBOM = await db.BOMHeader.findOne({
            where: {
              material_id: componentMaterial.id,
              company_id: bom.company_id,
              status: 'ACTIVE'
            },
            order: [['id', 'DESC']],
            limit: 1,
            transaction
          });

          if (subBOM) {
            // Recursively explode the sub-BOM
            const subComponents = await explodeBOM(
              subBOM.id,
              requiredQty,
              subBOM.base_quantity || 1,
              processedBOMs,
              level + 1
            );
            
            // Add all sub-components to the result
            components.push(...subComponents);
          }
        }
      }

      return components;
    };

    // Process each Customer PO item
    for (const item of customerPO.items) {
      const material = item.material;
      
      if (!material) {
        errors.push(`Item ${item.item_number}: Material not found`);
        continue;
      }

      // Check if material is a finished good - comprehensive check
      let isFinishedGood = false;
      
      // Method 1: Check material_type ENUM field (case-insensitive)
      if (material.material_type) {
        const matTypeUpper = String(material.material_type).toUpperCase();
        isFinishedGood = matTypeUpper === 'FINISHED_GOOD' || matTypeUpper === 'FINISHEDGOOD';
      }
      
      // Method 2: Check materialType relationship type_code
      if (!isFinishedGood && material.materialType && material.materialType.type_code) {
        const typeCodeUpper = String(material.materialType.type_code).toUpperCase();
        isFinishedGood = typeCodeUpper === 'FIN_GOOD' || typeCodeUpper === 'FINGOOD';
      }
      
      // Method 3: Query MaterialType directly by material_type_id
      if (!isFinishedGood && material.material_type_id) {
        const materialType = await db.MaterialType.findByPk(material.material_type_id, {
          attributes: ['type_code'],
          transaction
        });
        if (materialType && materialType.type_code) {
          const typeCodeUpper = String(materialType.type_code).toUpperCase();
          isFinishedGood = typeCodeUpper === 'FIN_GOOD' || typeCodeUpper === 'FINGOOD';
        }
      }
      
      // Final fallback: Reload material from database with all relationships if still not determined
      if (!isFinishedGood && material.id) {
        const freshMaterial = await db.Material.findByPk(material.id, {
          include: [
            {
              model: db.MaterialType,
              as: 'materialType',
              attributes: ['id', 'type_code', 'type_name']
            }
          ],
          attributes: ['id', 'material_number', 'material_type', 'material_type_id', 'company_id'],
          transaction
        });
        
        if (freshMaterial) {
          // Check ENUM (case-insensitive)
          if (freshMaterial.material_type) {
            const matTypeUpper = String(freshMaterial.material_type).toUpperCase();
            if (matTypeUpper === 'FINISHED_GOOD' || matTypeUpper === 'FINISHEDGOOD') {
              isFinishedGood = true;
            }
          }
          
          // Check materialType relationship - both type_code and type_name
          if (!isFinishedGood && freshMaterial.materialType) {
            if (freshMaterial.materialType.type_code) {
              const typeCodeUpper = String(freshMaterial.materialType.type_code).toUpperCase();
              if (typeCodeUpper === 'FIN_GOOD' || typeCodeUpper === 'FINGOOD') {
                isFinishedGood = true;
              }
            }
            // Also check type_name as fallback
            if (!isFinishedGood && freshMaterial.materialType.type_name) {
              const typeNameUpper = String(freshMaterial.materialType.type_name).toUpperCase();
              if (typeNameUpper.includes('FINISHED') && typeNameUpper.includes('GOOD')) {
                isFinishedGood = true;
              }
            }
          }
          
          // Check material_type_id directly
          if (!isFinishedGood && freshMaterial.material_type_id) {
            const materialType = await db.MaterialType.findByPk(freshMaterial.material_type_id, {
              attributes: ['type_code', 'type_name'],
              transaction
            });
            if (materialType) {
              if (materialType.type_code) {
                const typeCodeUpper = String(materialType.type_code).toUpperCase();
                if (typeCodeUpper === 'FIN_GOOD' || typeCodeUpper === 'FINGOOD') {
                  isFinishedGood = true;
                }
              }
              // Also check type_name
              if (!isFinishedGood && materialType.type_name) {
                const typeNameUpper = String(materialType.type_name).toUpperCase();
                if (typeNameUpper.includes('FINISHED') && typeNameUpper.includes('GOOD')) {
                  isFinishedGood = true;
                }
              }
            }
          }
        }
      }
      
      // Ultimate fallback: Query material by material_number with company context and check pattern
      if (!isFinishedGood && material.material_number && companyId) {
        const materialByNumber = await db.Material.findOne({
          where: {
            material_number: material.material_number,
            company_id: companyId
          },
          include: [
            {
              model: db.MaterialType,
              as: 'materialType',
              attributes: ['id', 'type_code', 'type_name']
            }
          ],
          attributes: ['id', 'material_number', 'material_type', 'material_type_id'],
          transaction
        });
        
        if (materialByNumber) {
          // Check ENUM (case-insensitive)
          if (materialByNumber.material_type) {
            const matTypeUpper = String(materialByNumber.material_type).toUpperCase();
            if (matTypeUpper === 'FINISHED_GOOD' || matTypeUpper === 'FINISHEDGOOD') {
              isFinishedGood = true;
            }
          }
          
          // Check materialType relationship - both type_code and type_name
          if (!isFinishedGood && materialByNumber.materialType) {
            if (materialByNumber.materialType.type_code) {
              const typeCodeUpper = String(materialByNumber.materialType.type_code).toUpperCase();
              if (typeCodeUpper === 'FIN_GOOD' || typeCodeUpper === 'FINGOOD') {
                isFinishedGood = true;
              }
            }
            // Also check type_name
            if (!isFinishedGood && materialByNumber.materialType.type_name) {
              const typeNameUpper = String(materialByNumber.materialType.type_name).toUpperCase();
              if (typeNameUpper.includes('FINISHED') && typeNameUpper.includes('GOOD')) {
                isFinishedGood = true;
              }
            }
          }
          
          // Pattern-based fallback: Check material_number for common patterns
          if (!isFinishedGood) {
            const matNumber = (materialByNumber.material_number || '').toUpperCase();
            if (matNumber.includes('FINGD') || matNumber.includes('FINISHED') || matNumber.startsWith('FG-')) {
              // Material number suggests finished good - try to find and set the type
              const finGoodType = await db.MaterialType.findOne({
                where: {
                  company_id: companyId,
                  type_code: 'FIN_GOOD'
                },
                attributes: ['id'],
                transaction
              });
              if (finGoodType) {
                // Update the material to be a finished good
                await db.Material.update(
                  {
                    material_type: 'FINISHED_GOOD',
                    material_type_id: finGoodType.id
                  },
                  {
                    where: { id: materialByNumber.id },
                    transaction
                  }
                );
                isFinishedGood = true;
              }
            }
          }
        }
      }

      if (!isFinishedGood) {
        errors.push(`Item ${item.item_number} (${material.material_number || material.description}): Not a finished good, skipping`);
        continue;
      }

      // Find active BOM for this material
      const bom = await db.BOMHeader.findOne({
        where: {
          material_id: material.id,
          company_id: companyId,
          plant_id: customerPO.plant_id,
          status: 'ACTIVE'
        },
        order: [['id', 'DESC']],
        limit: 1,
        transaction
      });

      if (!bom) {
        errors.push(`Item ${item.item_number} (${material.material_number || material.description}): No active BOM found`);
        continue;
      }

      // Check if Production Order already exists for this Customer PO item
      const existingPO = await db.ProductionOrder.findOne({
        where: {
          customer_po_id: customerPO.id,
          customer_po_item_id: item.id
        },
        transaction
      });

      if (existingPO) {
        errors.push(`Item ${item.item_number} (${material.material_number || material.description}): Production Order already exists (${existingPO.production_order_number})`);
        continue;
      }

      // Get routing if available (from material or BOM)
      let routingId = null;
      if (material.routing_id) {
        routingId = material.routing_id;
      } else if (bom.routing_id) {
        routingId = bom.routing_id;
      }

      // Calculate start and finish dates based on delivery date
      let startDate = null;
      let finishDate = null;
      if (item.delivery_date) {
        finishDate = item.delivery_date;
        // If routing exists, calculate start date (for now, set start date as 7 days before finish date)
        if (routingId) {
          const finishDateObj = new Date(item.delivery_date);
          finishDateObj.setDate(finishDateObj.getDate() - 7); // Default 7 days lead time
          startDate = finishDateObj.toISOString().split('T')[0];
        }
      }

      // Generate Production Order number
      const poNumber = await generateProductionOrderNumber(companyId, customerPO.fiscal_year_id, transaction);

      // Create Production Order
      const productionOrder = await db.ProductionOrder.create({
        company_id: companyId,
        plant_id: customerPO.plant_id,
        fiscal_year_id: customerPO.fiscal_year_id,
        production_order_number: poNumber,
        production_order_date: new Date().toISOString().split('T')[0],
        finished_material_id: material.id,
        bom_id: bom.id,
        routing_id: routingId,
        order_quantity: parseFloat(item.quantity || 0),
        uom_id: item.uom_id,
        start_date: startDate,
        finish_date: finishDate,
        customer_po_id: customerPO.id,
        customer_po_item_id: item.id,
        status: 'DRAFT',
        remarks: `Created from Customer PO: ${customerPO.po_number}, Item: ${item.item_number}`,
        created_by: userId,
        updated_by: userId
      }, { transaction });

      // Explode BOM recursively and create components (including all sub-assemblies)
      const components = await explodeBOM(bom.id, item.quantity, bom.base_quantity || 1, new Set(), 0);
      
      if (components && components.length > 0) {
        for (const comp of components) {
          await db.ProductionOrderComponent.create({
            production_order_id: productionOrder.id,
            bom_component_id: comp.bom_component_id || null,
            material_id: comp.material_id,
            required_quantity: comp.required_quantity,
            uom_id: comp.uom_id,
            issue_type: comp.issue_type || 'MANUAL',
            storage_location: comp.storage_location || null
          }, { transaction });
        }
      }

      // Fetch created Production Order with associations
      const createdPO = await db.ProductionOrder.findByPk(productionOrder.id, {
        include: [
          { model: db.Company, as: 'company' },
          { model: db.Plant, as: 'plant' },
          { model: db.Material, as: 'finishedMaterial' },
          { model: db.BOMHeader, as: 'bom' },
          {
            model: db.ProductionOrderComponent,
            as: 'components',
            include: [
              { model: db.Material, as: 'material' },
              { model: db.UOM, as: 'uom' }
            ]
          },
          { model: db.CustomerPO, as: 'customerPO', attributes: ['id', 'po_number'] },
          { model: db.CustomerPOItem, as: 'customerPOItem', attributes: ['id', 'item_number'] }
        ],
        transaction
      });

      createdProductionOrders.push(createdPO);
    }

    await transaction.commit();

    sendResponse(res, 201, true, 'Production Orders created successfully', {
      created: createdProductionOrders,
      errors: errors.length > 0 ? errors : undefined,
      message: `Created ${createdProductionOrders.length} Production Order(s)${errors.length > 0 ? `, ${errors.length} error(s)` : ''}`
    });
  } catch (error) {
    await transaction.rollback();
    console.error('Error converting Customer PO to Production Orders:', error);
    sendError(res, error.message || 'Failed to convert Customer PO to Production Orders', 500);
  }
});

/**
 * Convert specific Customer PO items to Production Orders
 */
exports.convertItemsToProductionOrders = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const { itemIds } = req.body; // Array of Customer PO Item IDs to convert
  const userId = req.user?.userId || null;
  const companyId = req.user?.companyId;

  if (!companyId) {
    return sendError(res, 'Company ID is required', 400);
  }

  if (!itemIds || !Array.isArray(itemIds) || itemIds.length === 0) {
    return sendError(res, 'Item IDs array is required', 400);
  }

  const customerPO = await db.CustomerPO.findByPk(id, {
    include: [
      {
        model: db.CustomerPOItem,
        as: 'items',
        where: { id: { [Op.in]: itemIds } },
        required: false,
        include: [
          {
            model: db.Material,
            as: 'material',
            include: [
              {
                model: db.MaterialType,
                as: 'materialType',
                attributes: ['id', 'type_code', 'type_name']
              }
            ]
          },
          {
            model: db.UOM,
            as: 'uom',
            attributes: ['id', 'uom_code', 'uom_name']
          }
        ]
      },
      {
        model: db.Plant,
        as: 'plant',
        attributes: ['id', 'code', 'name']
      }
    ]
  });

  if (!customerPO) {
    return sendError(res, 'Customer PO not found', 404);
  }

  // Only APPROVED or RELEASED Customer POs can be converted (same as full PO convert)
  if (customerPO.status !== 'APPROVED' && customerPO.status !== 'RELEASED') {
    return sendError(res, 'Only APPROVED or RELEASED Customer POs can be converted to Production Orders', 400);
  }

  if (!customerPO.items || customerPO.items.length === 0) {
    return sendError(res, 'No items found to convert', 400);
  }

  const transaction = await db.sequelize.transaction();

  try {
    const createdProductionOrders = [];
    const errors = [];

    // BOM explosion helper (same as convertToProductionOrders)
    const explodeBOM = async (bomId, orderQuantity, baseQuantity, processedBOMs = new Set(), level = 0) => {
      if (processedBOMs.has(bomId)) {
        return [];
      }
      processedBOMs.add(bomId);

      const bom = await db.BOMHeader.findByPk(bomId, {
        include: [
          {
            model: db.BOMComponent,
            as: 'components',
            where: { status: 'ACTIVE' },
            required: false,
            include: [
              { 
                model: db.Material, 
                as: 'material',
                attributes: ['id', 'material_number', 'description', 'material_type', 'base_uom_id']
              },
              { 
                model: db.UOM, 
                as: 'componentUOM',
                attributes: ['id', 'uom_code', 'uom_name']
              }
            ]
          }
        ],
        transaction
      });

      if (!bom || !bom.components || bom.components.length === 0) {
        return [];
      }

      const multiplier = parseFloat(orderQuantity) / parseFloat(baseQuantity || 1);
      const components = [];

      for (const comp of bom.components) {
        const componentMaterial = comp.material;
        if (!componentMaterial) continue;

        const requiredQty = parseFloat(comp.component_quantity || comp.quantity || 0) * multiplier;
        const componentUOMId = comp.component_uom_id || comp.uom_id;

        components.push({
          bom_component_id: comp.id,
          material_id: comp.material_id,
          required_quantity: requiredQty,
          uom_id: componentUOMId,
          issue_type: comp.issue_type || 'MANUAL',
          storage_location: comp.storage_location || null,
          level: level,
          parent_material_id: level > 0 ? bom.material_id : null
        });

        const isSubAssembly = componentMaterial.material_type === 'SEMI_FINISHED' || 
                              componentMaterial.material_type === 'FINISHED_GOOD';
        
        if (isSubAssembly) {
          const subBOM = await db.BOMHeader.findOne({
            where: {
              material_id: componentMaterial.id,
              company_id: bom.company_id,
              status: 'ACTIVE'
            },
            order: [['id', 'DESC']],
            limit: 1,
            transaction
          });

          if (subBOM) {
            const subComponents = await explodeBOM(
              subBOM.id,
              requiredQty,
              subBOM.base_quantity || 1,
              processedBOMs,
              level + 1
            );
            components.push(...subComponents);
          }
        }
      }

      return components;
    };

    // Process each selected Customer PO item
    for (const item of customerPO.items) {
      const material = item.material;
      
      if (!material) {
        errors.push(`Item ${item.item_number}: Material not found`);
        continue;
      }

      // Check if material is a finished good or semi-finished - comprehensive check
      let isFinishedGood = false;
      let isSemiFinished = false;
      
      // Method 1: Check material_type ENUM field (case-insensitive)
      if (material.material_type) {
        const matTypeUpper = String(material.material_type).toUpperCase();
        isFinishedGood = matTypeUpper === 'FINISHED_GOOD' || matTypeUpper === 'FINISHEDGOOD';
        isSemiFinished = matTypeUpper === 'SEMI_FINISHED' || matTypeUpper === 'SEMIFINISHED';
      }
      
      // Method 2: Check materialType relationship type_code
      if (!isFinishedGood && !isSemiFinished && material.materialType && material.materialType.type_code) {
        const typeCodeUpper = String(material.materialType.type_code).toUpperCase();
        isFinishedGood = typeCodeUpper === 'FIN_GOOD' || typeCodeUpper === 'FINGOOD';
        isSemiFinished = typeCodeUpper === 'SEMI_FIN' || typeCodeUpper === 'SEMIFIN';
      }
      
      // Method 3: Check materialType relationship type_name
      if (!isFinishedGood && !isSemiFinished && material.materialType && material.materialType.type_name) {
        const typeNameUpper = String(material.materialType.type_name).toUpperCase();
        if (typeNameUpper.includes('FINISHED') && typeNameUpper.includes('GOOD')) {
          isFinishedGood = true;
        }
        if (typeNameUpper.includes('SEMI') && typeNameUpper.includes('FINISHED')) {
          isSemiFinished = true;
        }
      }
      
      // Method 4: Query MaterialType directly by material_type_id
      if (!isFinishedGood && !isSemiFinished && material.material_type_id) {
        const materialType = await db.MaterialType.findByPk(material.material_type_id, {
          attributes: ['type_code', 'type_name'],
          transaction
        });
        if (materialType) {
          if (materialType.type_code) {
            const typeCodeUpper = String(materialType.type_code).toUpperCase();
            isFinishedGood = typeCodeUpper === 'FIN_GOOD' || typeCodeUpper === 'FINGOOD';
            isSemiFinished = typeCodeUpper === 'SEMI_FIN' || typeCodeUpper === 'SEMIFIN';
          }
          // Also check type_name
          if (!isFinishedGood && !isSemiFinished && materialType.type_name) {
            const typeNameUpper = String(materialType.type_name).toUpperCase();
            if (typeNameUpper.includes('FINISHED') && typeNameUpper.includes('GOOD')) {
              isFinishedGood = true;
            }
            if (typeNameUpper.includes('SEMI') && typeNameUpper.includes('FINISHED')) {
              isSemiFinished = true;
            }
          }
        }
      }
      
      // Final fallback: Reload material from database with all relationships if still not determined
      if (!isFinishedGood && !isSemiFinished && material.id) {
        const freshMaterial = await db.Material.findByPk(material.id, {
          include: [
            {
              model: db.MaterialType,
              as: 'materialType',
              attributes: ['id', 'type_code', 'type_name']
            }
          ],
          attributes: ['id', 'material_number', 'material_type', 'material_type_id'],
          transaction
        });
        
        if (freshMaterial) {
          // Check ENUM (case-insensitive)
          if (freshMaterial.material_type) {
            const matTypeUpper = String(freshMaterial.material_type).toUpperCase();
            if (matTypeUpper === 'FINISHED_GOOD' || matTypeUpper === 'FINISHEDGOOD') {
              isFinishedGood = true;
            } else if (matTypeUpper === 'SEMI_FINISHED' || matTypeUpper === 'SEMIFINISHED') {
              isSemiFinished = true;
            }
          }
          
          // Check materialType relationship - both type_code and type_name
          if (!isFinishedGood && !isSemiFinished && freshMaterial.materialType) {
            if (freshMaterial.materialType.type_code) {
              const typeCodeUpper = String(freshMaterial.materialType.type_code).toUpperCase();
              if (typeCodeUpper === 'FIN_GOOD' || typeCodeUpper === 'FINGOOD') {
                isFinishedGood = true;
              } else if (typeCodeUpper === 'SEMI_FIN' || typeCodeUpper === 'SEMIFIN') {
                isSemiFinished = true;
              }
            }
            // Also check type_name
            if (!isFinishedGood && !isSemiFinished && freshMaterial.materialType.type_name) {
              const typeNameUpper = String(freshMaterial.materialType.type_name).toUpperCase();
              if (typeNameUpper.includes('FINISHED') && typeNameUpper.includes('GOOD')) {
                isFinishedGood = true;
              }
              if (typeNameUpper.includes('SEMI') && typeNameUpper.includes('FINISHED')) {
                isSemiFinished = true;
              }
            }
          }
          
          // Check material_type_id directly
          if (!isFinishedGood && !isSemiFinished && freshMaterial.material_type_id) {
            const materialType = await db.MaterialType.findByPk(freshMaterial.material_type_id, {
              attributes: ['type_code', 'type_name'],
              transaction
            });
            if (materialType) {
              if (materialType.type_code) {
                const typeCodeUpper = String(materialType.type_code).toUpperCase();
                if (typeCodeUpper === 'FIN_GOOD' || typeCodeUpper === 'FINGOOD') {
                  isFinishedGood = true;
                } else if (typeCodeUpper === 'SEMI_FIN' || typeCodeUpper === 'SEMIFIN') {
                  isSemiFinished = true;
                }
              }
              // Also check type_name
              if (!isFinishedGood && !isSemiFinished && materialType.type_name) {
                const typeNameUpper = String(materialType.type_name).toUpperCase();
                if (typeNameUpper.includes('FINISHED') && typeNameUpper.includes('GOOD')) {
                  isFinishedGood = true;
                }
                if (typeNameUpper.includes('SEMI') && typeNameUpper.includes('FINISHED')) {
                  isSemiFinished = true;
                }
              }
            }
          }
        }
      }
      
      // Ultimate fallback: Query material by material_number with company context and check pattern
      if (!isFinishedGood && !isSemiFinished && material.material_number && companyId) {
        const materialByNumber = await db.Material.findOne({
          where: {
            material_number: material.material_number,
            company_id: companyId
          },
          include: [
            {
              model: db.MaterialType,
              as: 'materialType',
              attributes: ['id', 'type_code', 'type_name']
            }
          ],
          attributes: ['id', 'material_number', 'material_type', 'material_type_id'],
          transaction
        });
        
        if (materialByNumber) {
          // Check ENUM (case-insensitive)
          if (materialByNumber.material_type) {
            const matTypeUpper = String(materialByNumber.material_type).toUpperCase();
            if (matTypeUpper === 'FINISHED_GOOD' || matTypeUpper === 'FINISHEDGOOD') {
              isFinishedGood = true;
            } else if (matTypeUpper === 'SEMI_FINISHED' || matTypeUpper === 'SEMIFINISHED') {
              isSemiFinished = true;
            }
          }
          
          // Check materialType relationship - both type_code and type_name
          if (!isFinishedGood && !isSemiFinished && materialByNumber.materialType) {
            if (materialByNumber.materialType.type_code) {
              const typeCodeUpper = String(materialByNumber.materialType.type_code).toUpperCase();
              if (typeCodeUpper === 'FIN_GOOD' || typeCodeUpper === 'FINGOOD') {
                isFinishedGood = true;
              } else if (typeCodeUpper === 'SEMI_FIN' || typeCodeUpper === 'SEMIFIN') {
                isSemiFinished = true;
              }
            }
            // Also check type_name
            if (!isFinishedGood && !isSemiFinished && materialByNumber.materialType.type_name) {
              const typeNameUpper = String(materialByNumber.materialType.type_name).toUpperCase();
              if (typeNameUpper.includes('FINISHED') && typeNameUpper.includes('GOOD')) {
                isFinishedGood = true;
              }
              if (typeNameUpper.includes('SEMI') && typeNameUpper.includes('FINISHED')) {
                isSemiFinished = true;
              }
            }
          }
          
          // Pattern-based fallback: Check material_number for common patterns
          if (!isFinishedGood && !isSemiFinished) {
            const matNumber = (materialByNumber.material_number || '').toUpperCase();
            if (matNumber.includes('FINGD') || matNumber.includes('FINISHED') || matNumber.startsWith('FG-')) {
              // Material number suggests finished good - try to find and set the type
              const finGoodType = await db.MaterialType.findOne({
                where: {
                  company_id: companyId,
                  type_code: 'FIN_GOOD'
                },
                attributes: ['id'],
                transaction
              });
              if (finGoodType) {
                // Update the material to be a finished good
                await db.Material.update(
                  {
                    material_type: 'FINISHED_GOOD',
                    material_type_id: finGoodType.id
                  },
                  {
                    where: { id: materialByNumber.id },
                    transaction
                  }
                );
                isFinishedGood = true;
              }
            } else if (matNumber.includes('SEMIFIN') || matNumber.includes('SEMI-FIN') || matNumber.startsWith('SF-')) {
              // Material number suggests semi-finished - try to find and set the type
              const semiFinType = await db.MaterialType.findOne({
                where: {
                  company_id: companyId,
                  type_code: 'SEMI_FIN'
                },
                attributes: ['id'],
                transaction
              });
              if (semiFinType) {
                // Update the material to be a semi-finished good
                await db.Material.update(
                  {
                    material_type: 'SEMI_FINISHED',
                    material_type_id: semiFinType.id
                  },
                  {
                    where: { id: materialByNumber.id },
                    transaction
                  }
                );
                isSemiFinished = true;
              }
            }
          }
        }
      }

      if (!isFinishedGood && !isSemiFinished) {
        errors.push(`Item ${item.item_number} (${material.material_number || material.description}): Not a finished good or semi-finished, skipping`);
        continue;
      }

      // Find active BOM for this material
      const bom = await db.BOMHeader.findOne({
        where: {
          material_id: material.id,
          company_id: companyId,
          plant_id: customerPO.plant_id,
          status: 'ACTIVE'
        },
        order: [['id', 'DESC']],
        limit: 1,
        transaction
      });

      if (!bom) {
        errors.push(`Item ${item.item_number} (${material.material_number || material.description}): No active BOM found`);
        continue;
      }

      // Check if Production Order already exists for this Customer PO item
      const existingPO = await db.ProductionOrder.findOne({
        where: {
          customer_po_id: customerPO.id,
          customer_po_item_id: item.id
        },
        transaction
      });

      if (existingPO) {
        errors.push(`Item ${item.item_number} (${material.material_number || material.description}): Production Order already exists (${existingPO.production_order_number})`);
        continue;
      }

      // Get routing if available
      let routingId = null;
      if (material.routing_id) {
        routingId = material.routing_id;
      } else if (bom.routing_id) {
        routingId = bom.routing_id;
      }

      // Calculate start and finish dates
      let startDate = null;
      let finishDate = null;
      if (item.delivery_date) {
        finishDate = item.delivery_date;
        if (routingId) {
          const finishDateObj = new Date(item.delivery_date);
          finishDateObj.setDate(finishDateObj.getDate() - 7);
          startDate = finishDateObj.toISOString().split('T')[0];
        }
      }

      // Generate Production Order number
      const poNumber = await generateProductionOrderNumber(companyId, customerPO.fiscal_year_id, transaction);

      // Create Production Order
      const productionOrder = await db.ProductionOrder.create({
        company_id: companyId,
        plant_id: customerPO.plant_id,
        fiscal_year_id: customerPO.fiscal_year_id,
        production_order_number: poNumber,
        production_order_date: new Date().toISOString().split('T')[0],
        finished_material_id: material.id,
        bom_id: bom.id,
        routing_id: routingId,
        order_quantity: parseFloat(item.quantity || 0),
        uom_id: item.uom_id,
        start_date: startDate,
        finish_date: finishDate,
        customer_po_id: customerPO.id,
        customer_po_item_id: item.id,
        status: 'DRAFT',
        remarks: `Created from Customer PO: ${customerPO.po_number}, Item: ${item.item_number}`,
        created_by: userId,
        updated_by: userId
      }, { transaction });

      // Explode BOM recursively and create components
      const components = await explodeBOM(bom.id, item.quantity, bom.base_quantity || 1, new Set(), 0);
      
      if (components && components.length > 0) {
        for (const comp of components) {
          await db.ProductionOrderComponent.create({
            production_order_id: productionOrder.id,
            bom_component_id: comp.bom_component_id || null,
            material_id: comp.material_id,
            required_quantity: comp.required_quantity,
            uom_id: comp.uom_id,
            issue_type: comp.issue_type || 'MANUAL',
            storage_location: comp.storage_location || null
          }, { transaction });
        }
      }

      // Fetch created Production Order with associations
      const createdPO = await db.ProductionOrder.findByPk(productionOrder.id, {
        include: [
          { model: db.Company, as: 'company' },
          { model: db.Plant, as: 'plant' },
          { model: db.Material, as: 'finishedMaterial' },
          { model: db.BOMHeader, as: 'bom' },
          {
            model: db.ProductionOrderComponent,
            as: 'components',
            include: [
              { model: db.Material, as: 'material' },
              { model: db.UOM, as: 'uom' }
            ]
          },
          { model: db.CustomerPO, as: 'customerPO', attributes: ['id', 'po_number'] },
          { model: db.CustomerPOItem, as: 'customerPOItem', attributes: ['id', 'item_number'] }
        ],
        transaction
      });

      createdProductionOrders.push(createdPO);
    }

    await transaction.commit();

    // Send notifications to Production Department users
    if (createdProductionOrders.length > 0) {
      try {
        // Find Production Department
        const productionDept = await db.Department.findOne({
          where: {
            company_id: companyId,
            department_code: 'PROD',
            status: 'ACTIVE'
          }
        });

        if (productionDept) {
          // Get all employees in Production Department
          const productionEmployees = await db.Employee.findAll({
            where: {
              company_id: companyId,
              department_id: productionDept.id,
              status: 'ACTIVE'
            },
            attributes: ['id']
          });

          if (productionEmployees.length > 0) {
            const employeeIds = productionEmployees.map(emp => emp.id);

            // Get all users linked to these employees
            const productionUsers = await db.User.findAll({
              where: {
                company_id: companyId,
                employee_id: { [Op.in]: employeeIds },
                status: 'ACTIVE',
                is_active: true
              },
              attributes: ['id']
            });

            // Create notifications for each production user
            const poNumbers = createdProductionOrders.map(po => po.production_order_number).join(', ');
            const itemCount = createdProductionOrders.length;

            for (const user of productionUsers) {
              await notificationService.createNotification({
                companyId,
                userId: user.id,
                type: 'INFO',
                moduleName: 'ProductionOrder',
                transactionId: createdProductionOrders[0].id,
                transactionNumber: createdProductionOrders[0].production_order_number,
                title: 'New Production Orders Created',
                message: `${itemCount} Production Order(s) (${poNumbers}) have been created from Customer PO ${customerPO.po_number}. Please review and proceed with production planning.`,
                link: `/production/production-order`,
                priority: 'HIGH',
                createdBy: userId
              });
            }
          }
        }
      } catch (notificationError) {
        // Log error but don't fail the conversion
        console.error('Error sending notifications to Production Department:', notificationError);
      }
    }

    sendResponse(res, 201, true, 'Production Orders created successfully', {
      created: createdProductionOrders,
      errors: errors.length > 0 ? errors : undefined,
      message: `Created ${createdProductionOrders.length} Production Order(s)${errors.length > 0 ? `, ${errors.length} error(s)` : ''}`
    });
  } catch (error) {
    await transaction.rollback();
    console.error('Error converting Customer PO items to Production Orders:', error);
    sendError(res, error.message || 'Failed to convert Customer PO items to Production Orders', 500);
  }
});

/**
 * Generate PDF for BOM List
 */
exports.generateBOMListPDF = asyncHandler(async (req, res) => {
  const { material, bomList, printHeader, company, plant, date, deliveryDate, customer, customerAddress, customerQuotation, poNumber, materialItem } = req.body;
  const companyId = req.user?.companyId || company?.id;

  if (!material || !bomList || !Array.isArray(bomList)) {
    return sendError(res, 'Material and BOM list are required', 400);
  }

  try {
    // Get print header if not provided
    let headerData = printHeader;
    if (!headerData && companyId) {
      const header = await db.PrintHeader.findOne({
        where: { company_id: companyId, status: 'ACTIVE' },
        order: [['id', 'DESC']]
      });
      headerData = header;
    }

    // Get company data if not provided
    let companyData = company;
    if (!companyData && companyId) {
      companyData = await db.Company.findByPk(companyId, {
        include: [
          { model: db.State, as: 'state', attributes: ['id', 'state_code', 'state_name', 'gst_state_code'] }
        ]
      });
    }

    // Create PDF document
    const doc = new PDFDocument({ margin: 50, size: 'A4' });
    
    // Set response headers
    res.setHeader('Content-Type', 'application/pdf');
    res.setHeader('Content-Disposition', `attachment; filename="BOM_List_${material.material_number}_${date || new Date().toISOString().split('T')[0]}.pdf"`);

    // Pipe PDF to response
    doc.pipe(res);

    // Helper function to add logo if available
    const addLogo = (logoPath, x, y, maxWidth = 100) => {
      if (logoPath && headerData) {
        try {
          const logoFullPath = path.join(__dirname, '../uploads', logoPath);
          if (fs.existsSync(logoFullPath)) {
            doc.image(logoFullPath, x, y, { width: maxWidth, fit: [maxWidth, 100] });
            return true;
          }
        } catch (err) {
          console.error('Error loading logo:', err);
        }
      }
      return false;
    };

    // Header Section
    let yPosition = 50;
    const pageWidth = doc.page.width;
    const margin = 50;
    const contentWidth = pageWidth - (margin * 2);

    // Logo (left side)
    const logoAdded = addLogo(headerData?.logo_path, margin, yPosition, 80);

    // Company Name (centered)
    doc.fontSize(16).font('Helvetica-Bold');
    doc.text(headerData?.company_name || companyData?.name || 'Company Name', margin, yPosition, { align: 'center', width: contentWidth });
    yPosition += 20;

    if (headerData?.former_name) {
      doc.fontSize(10).font('Helvetica');
      doc.text(`(Formerly Known as ${headerData.former_name})`, margin, yPosition, { align: 'center', width: contentWidth });
      yPosition += 15;
    }

    // Address (centered)
    doc.fontSize(9).font('Helvetica');
    const address = headerData?.address || companyData?.address || '';
    if (address) {
      const addressLines = address.split('\n').filter(line => line.trim());
      addressLines.forEach(line => {
        doc.text(line.trim(), margin, yPosition, { align: 'center', width: contentWidth });
        yPosition += 12;
      });
    }

    // Add GSTIN to address if available
    if (headerData?.gstin) {
      doc.fontSize(9).font('Helvetica');
      doc.text(`GSTIN: ${headerData.gstin}`, margin, yPosition, { align: 'center', width: contentWidth });
      yPosition += 12;
    }

    // Contact Information (centered)
    const contactInfo = [];
    if (headerData?.phone) contactInfo.push(`Phone: ${headerData.phone}`);
    if (headerData?.fax) contactInfo.push(`Fax: ${headerData.fax}`);
    if (headerData?.email) contactInfo.push(`E-mail: ${headerData.email}`);
    if (headerData?.website) contactInfo.push(`Webpage: ${headerData.website}`);

    if (contactInfo.length > 0) {
      yPosition += 5;
      contactInfo.forEach(info => {
        doc.text(info, margin, yPosition, { align: 'center', width: contentWidth });
        yPosition += 12;
      });
    }

    // Quality Certification Logo (top right)
    if (headerData?.quality_certification_logo_path) {
      try {
        const certLogoPath = path.join(__dirname, '../uploads', headerData.quality_certification_logo_path);
        if (fs.existsSync(certLogoPath)) {
          doc.image(certLogoPath, pageWidth - margin - 80, 50, { width: 80, fit: [80, 60] });
        }
      } catch (err) {
        console.error('Error loading certification logo:', err);
      }
    }

    // Add "CERTIFIED" text if certification text exists (below certification logo)
    if (headerData?.quality_certification_text || headerData?.quality_certification_body) {
      const certText = headerData.quality_certification_text || headerData.quality_certification_body;
      doc.fontSize(8).font('Helvetica-Bold');
      doc.text('CERTIFIED', pageWidth - margin - 80, 115, { align: 'center', width: 80 });
    }

    // Title: PURCHASE ORDER (centered box)
    yPosition += 20;
    const titleBoxHeight = 30;
    const titleBoxWidth = 200;
    const titleBoxX = (pageWidth - titleBoxWidth) / 2;
    
    // Draw box for title
    doc.rect(titleBoxX, yPosition - 5, titleBoxWidth, titleBoxHeight).stroke();
    
    doc.fontSize(18).font('Helvetica-Bold');
    doc.text('PURCHASE ORDER', titleBoxX, yPosition, { align: 'center', width: titleBoxWidth });
    yPosition += titleBoxHeight + 10;

    // Customer Information Section (Left side)
    const customerYStart = yPosition;
    doc.fontSize(10).font('Helvetica-Bold');
    doc.text('To', margin, customerYStart);
    
    if (customer || customerAddress) {
      doc.fontSize(10).font('Helvetica');
      let customerY = customerYStart + 15;
      
      if (customer?.name) {
        doc.font('Helvetica-Bold');
        doc.text(customer.name, margin, customerY);
        customerY += 15;
      }
      
      if (customerAddress) {
        doc.font('Helvetica');
        const addressLines = [];
        if (customerAddress.address_line1) addressLines.push(customerAddress.address_line1);
        if (customerAddress.address_line2) addressLines.push(customerAddress.address_line2);
        
        // Handle city (could be object or string)
        let cityName = '';
        if (customerAddress.city) {
          cityName = typeof customerAddress.city === 'object' 
            ? customerAddress.city.city_name || customerAddress.city.name || ''
            : customerAddress.city;
        }
        if (cityName) {
          let cityLine = cityName;
          if (customerAddress.pincode) cityLine += `-${customerAddress.pincode}`;
          addressLines.push(cityLine);
        } else if (customerAddress.pincode) {
          addressLines.push(`-${customerAddress.pincode}`);
        }
        
        // Handle state (could be object or string)
        if (customerAddress.state) {
          const stateName = typeof customerAddress.state === 'object' 
            ? customerAddress.state.state_name || customerAddress.state.name || ''
            : customerAddress.state;
          if (stateName) addressLines.push(stateName);
        }
        
        addressLines.forEach(line => {
          if (line.trim()) {
            doc.text(line.trim(), margin, customerY);
            customerY += 12;
          }
        });
      } else if (customer?.address) {
        const addressLines = customer.address.split('\n').filter(line => line.trim());
        addressLines.forEach(line => {
          doc.text(line.trim(), margin, customerY);
          customerY += 12;
        });
      }
    }

    // Order Details Section (Right side)
    const orderDetailsX = pageWidth - margin - 150;
    let orderDetailsY = customerYStart;
    
    doc.fontSize(9).font('Helvetica');
    doc.text('Our Ref :', orderDetailsX, orderDetailsY);
    orderDetailsY += 15;
    doc.text('Your Ref:', orderDetailsX, orderDetailsY);
    orderDetailsY += 15;
    
    // Customer Quotation Number (only if available)
    if (customerQuotation?.cq_number) {
      doc.text(`CQ No. : ${customerQuotation.cq_number}`, orderDetailsX, orderDetailsY);
      orderDetailsY += 15;
    }
    
    // PO Number
    if (poNumber) {
      doc.text(`PO No. : ${poNumber}`, orderDetailsX, orderDetailsY);
      orderDetailsY += 15;
    } else {
      doc.text('PO No. :', orderDetailsX, orderDetailsY);
      orderDetailsY += 15;
    }
    
    // Date
    const reportDate = date ? new Date(date).toLocaleDateString('en-GB').replace(/\//g, '.') : new Date().toLocaleDateString('en-GB').replace(/\//g, '.');
    doc.text(`Date : ${reportDate}`, orderDetailsX, orderDetailsY);
    orderDetailsY += 15;
    doc.text('Handled By :', orderDetailsX, orderDetailsY);

    // Vendor Code and Value Section (Left side, below customer info, same line)
    let vendorY = customerYStart + 80;
    if (customer?.bp_number) {
      doc.fontSize(10).font('Helvetica-Bold');
      doc.text('Vendor Code:', margin, vendorY);
      doc.font('Helvetica');
      doc.text(customer.bp_number, margin + 80, vendorY);
      
      // Value on the same line (will be calculated after fetching components)
      // Placeholder for now, will be updated after table
    }

    // Update yPosition to below customer/order details section
    yPosition = Math.max(vendorY, orderDetailsY + 20);

    // Table Header
    yPosition += 10;
    const tableTop = yPosition;
    const availableWidth = pageWidth - (margin * 2); // 495 points for A4
    const colWidths = {
      slNo: 35,
      description: 200,
      um: 40,
      quantity: 60,
      rate: 70,
      delivery: 90
    };

    // Table Headers
    doc.fontSize(9).font('Helvetica-Bold');
    let xPos = margin;
    doc.text('Sl. No.', xPos, yPosition);
    xPos += colWidths.slNo;
    doc.text('Description', xPos, yPosition, { width: colWidths.description });
    xPos += colWidths.description;
    doc.text('UOM', xPos, yPosition);
    xPos += colWidths.um;
    doc.text('Quantity', xPos, yPosition, { align: 'right', width: colWidths.quantity });
    xPos += colWidths.quantity;
    doc.text('Rate INR', xPos, yPosition, { align: 'right', width: colWidths.rate });
    xPos += colWidths.rate;
    doc.text('Delivery', xPos, yPosition, { align: 'center', width: colWidths.delivery });

    // Draw header line
    yPosition += 15;
    doc.moveTo(margin, yPosition).lineTo(pageWidth - margin, yPosition).stroke();

    // Recursive function to fetch components and sub-components for Purchase Order
    const fetchComponentsRecursivelyPO = async (bomId, materialItemRate, processedMaterials = new Set(), level = 0) => {
      const components = [];
      
      if (processedMaterials.has(bomId)) {
        return components; // Avoid infinite loops
      }
      processedMaterials.add(bomId);
      
      // Format delivery date as DD.MM.YYYY (use deliveryDate from request, fallback to date)
      let formattedDeliveryDate = '-';
      const dateToUse = deliveryDate || date;
      if (dateToUse) {
        try {
          const dateObj = new Date(dateToUse);
          const day = String(dateObj.getDate()).padStart(2, '0');
          const month = String(dateObj.getMonth() + 1).padStart(2, '0');
          const year = dateObj.getFullYear();
          formattedDeliveryDate = `${day}.${month}.${year}`;
        } catch (e) {
          formattedDeliveryDate = dateToUse;
        }
      }
      
      const bomHeader = await db.BOMHeader.findByPk(bomId, {
        include: [
          {
            model: db.BOMComponent,
            as: 'components',
            where: { status: 'ACTIVE' },
            required: false,
            include: [
              {
                model: db.Material,
                as: 'material',
                attributes: ['id', 'material_number', 'description']
              },
              {
                model: db.UOM,
                as: 'componentUOM',
                attributes: ['id', 'uom_code', 'uom_name']
              }
            ],
            order: [['item_number', 'ASC']]
          }
        ]
      });

      if (bomHeader && bomHeader.components) {
        for (const comp of bomHeader.components) {
          const quantity = parseFloat(comp.component_quantity || 0);
          const material = comp.material;
          
          if (material) {
            // Get rate from materialItem for main components (level 0), otherwise 0
            let componentRate = '0.00';
            if (level === 0 && materialItemRate) {
              componentRate = parseFloat(materialItemRate || 0).toFixed(2);
            }
            
            components.push({
              material_number: material.material_number || '',
              description: material.description || '',
              uom: comp.componentUOM?.uom_code || 'NOS',
              quantity: Math.round(quantity).toString(),
              rate: componentRate,
              delivery: formattedDeliveryDate,
              level: level
            });
            
            // Check if this material has a BOM (sub-component)
            const subBOM = await db.BOMHeader.findOne({
              where: {
                material_id: material.id,
                company_id: companyId,
                status: 'ACTIVE'
              },
              order: [['id', 'DESC']],
              limit: 1
            });
            
            if (subBOM) {
              // Recursively fetch sub-components (rate will be 0.00 for sub-components)
              const subComponents = await fetchComponentsRecursivelyPO(
                subBOM.id,
                null, // No rate for sub-components
                processedMaterials,
                level + 1
              );
              components.push(...subComponents);
            }
          }
        }
      }
      
      return components;
    };

    // Fetch all components from all BOMs (including sub-components)
    const allComponents = [];
    const materialItemRate = materialItem?.unit_price || null;
    
    for (const bom of bomList) {
      if (bom.id) {
        const components = await fetchComponentsRecursivelyPO(bom.id, materialItemRate);
        allComponents.push(...components);
      }
    }

    // Table Rows
    yPosition += 10;
    doc.fontSize(9).font('Helvetica');
    
    // Calculate total value from materialItem (unit_price * quantity from Customer PO item)
    let totalValue = 0;
    if (materialItem?.unit_price && materialItem?.quantity) {
      totalValue = parseFloat(materialItem.unit_price || 0) * parseFloat(materialItem.quantity || 0);
    }
    
    allComponents.forEach((comp, index) => {
      // Check if we need a new page
      if (yPosition > doc.page.height - 100) {
        doc.addPage();
        yPosition = 50;
      }

      const slNo = index + 1;
      // Add indentation for sub-components
      const indent = comp.level > 0 ? '  '.repeat(comp.level) : '';
      const description = comp.material_number ? `${indent}${comp.material_number} ${comp.description}` : `${indent}${comp.description || '-'}`;

      xPos = margin;
      doc.text(slNo.toString(), xPos, yPosition, { width: colWidths.slNo });
      xPos += colWidths.slNo;
      doc.text(description, xPos, yPosition, { width: colWidths.description - 2 });
      xPos += colWidths.description;
      doc.text(comp.uom, xPos, yPosition, { width: colWidths.um });
      xPos += colWidths.um;
      doc.text(comp.quantity, xPos, yPosition, { align: 'right', width: colWidths.quantity });
      xPos += colWidths.quantity;
      doc.text(comp.rate, xPos, yPosition, { align: 'right', width: colWidths.rate });
      xPos += colWidths.rate;
      doc.text(comp.delivery, xPos, yPosition, { align: 'center', width: colWidths.delivery });

      yPosition += 20;
    });

    // Draw bottom line
    doc.moveTo(margin, yPosition).lineTo(pageWidth - margin, yPosition).stroke();
    yPosition += 20;

    // Add Value field (total value in INR) - on same line as vendor code
    if (customer?.bp_number) {
      const valueY = customerYStart + 80;
      doc.fontSize(10).font('Helvetica-Bold');
      // Value on same line, positioned after vendor code
      doc.text('Value:', margin + 200, valueY);
      doc.font('Helvetica');
      doc.text(`₹${totalValue.toFixed(2)}`, margin + 250, valueY);
    }

    // Footer Section
    const footerY = doc.page.height - 100;
    doc.fontSize(9).font('Helvetica');
    
    // Left column - Only GSTIN (removed ARN Code, HSN Code, SAC No. as per requirement)
    if (headerData?.gstin) {
      doc.text(`Our GSTIN No : ${headerData.gstin}`, margin, footerY);
    }

    // Right column - Company name and signature
    doc.text(`For ${headerData?.company_name || companyData?.name || 'Company Name'}`, pageWidth - margin - 200, footerY, { align: 'right' });
    doc.text('Authorised Signatories', pageWidth - margin - 200, footerY + 15, { align: 'right' });

    // Finalize PDF
    doc.end();
  } catch (error) {
    console.error('Error generating PDF:', error);
    if (!res.headersSent) {
      return sendError(res, 'Failed to generate PDF', 500);
    }
  }
});

/**
 * Generate Simple BOM List PDF (BOM LIST format)
 */
exports.generateBOMListSimplePDF = asyncHandler(async (req, res) => {
  const { material, bomList, materialItem, poItemQty } = req.body;
  const companyId = req.user?.companyId || null;

  if (!material || !bomList || !Array.isArray(bomList)) {
    return sendError(res, 'Material and BOM list are required', 400);
  }

  try {
    const collectRows = async (bomId, multiplier = 1, processed = new Set(), level = 0) => {
      const rows = [];
      if (!bomId || processed.has(bomId)) return rows;
      processed.add(bomId);

      const bomHeader = await db.BOMHeader.findByPk(bomId, {
        include: [
          { model: db.UOM, as: 'baseUOM', attributes: ['id', 'uom_code'] },
          {
            model: db.BOMComponent,
            as: 'components',
            where: { status: 'ACTIVE' },
            required: false,
            include: [
              {
                model: db.Material,
                as: 'material',
                attributes: ['id', 'material_number', 'description', 'drawing_number', 'length', 'width', 'height', 'thickness', 'net_weight', 'gross_weight', 'material_type'],
                include: [
                  { model: db.MaterialType, as: 'materialType', attributes: ['id', 'type_code', 'type_name'], required: false },
                  { model: db.MaterialGrade, as: 'materialGrade', attributes: ['id', 'grade_code', 'grade_name'], required: false },
                  {
                    model: db.MaterialSupplier,
                    as: 'suppliers',
                    required: false,
                    where: { status: 'ACTIVE' },
                    include: [{ model: db.BusinessPartner, as: 'vendor', attributes: ['id', 'bp_number'], required: false }]
                  }
                ]
              },
              { model: db.UOM, as: 'componentUOM', attributes: ['id', 'uom_code'], required: false }
            ],
            order: [['item_number', 'ASC']]
          }
        ]
      });

      if (!bomHeader || !Array.isArray(bomHeader.components)) return rows;
      const baseUOM = bomHeader.baseUOM?.uom_code || 'NOS';

      for (const comp of bomHeader.components) {
        const m = comp.material;
        if (!m) continue;

        const preferredSupplier = (m.suppliers || []).find((s) => s?.is_preferred && s?.vendor?.bp_number);
        const fallbackSupplier = (m.suppliers || []).find((s) => s?.vendor?.bp_number);
        const vendorCode = preferredSupplier?.vendor?.bp_number || fallbackSupplier?.vendor?.bp_number || '-';

        const compQty = parseFloat(comp.component_quantity || 0) || 0;
        const requiredQty = compQty * (parseFloat(multiplier) || 1);

        rows.push({
          level,
          vendor_code: vendorCode,
          drawing_no: m.drawing_number || '-',
          part_name: m.description || m.material_number || '-',
          length: m.length ?? null,
          width: m.width ?? null,
          height: m.height ?? null,
          thickness: comp.component_thickness ?? m.thickness ?? null,
          weight: comp.component_weight ?? m.net_weight ?? m.gross_weight ?? null,
          material_grade: comp.component_material_grade || m.materialGrade?.grade_code || m.materialGrade?.grade_name || '-',
          qty: requiredQty,
          uom: comp.componentUOM?.uom_code || baseUOM || 'NOS',
          material_type_code: m.materialType?.type_code || m.material_type || '',
          material_type_name: m.materialType?.type_name || m.material_type || '',
        });

        const child = await db.BOMHeader.findOne({
          where: {
            material_id: m.id,
            ...(companyId ? { company_id: companyId } : {}),
            status: 'ACTIVE',
            id: { [Op.ne]: bomId }
          },
          attributes: ['id'],
          order: [['id', 'DESC']]
        });
        if (child?.id) {
          const nested = await collectRows(child.id, requiredQty, new Set(processed), level + 1);
          rows.push(...nested);
        }
      }
      return rows;
    };

    const multiplier = parseFloat(poItemQty || materialItem?.quantity || 1) || 1;
    let rows = [];
    for (const b of bomList) {
      if (!b?.id) continue;
      const partial = await collectRows(b.id, multiplier, new Set(), 0);
      rows.push(...partial);
    }

    const doc = new PDFDocument({ size: 'A4', margin: 36 });
    res.setHeader('Content-Type', 'application/pdf');
    res.setHeader('Content-Disposition', `attachment; filename="BOM_List_${material.material_number || 'ITEM'}_${new Date().toISOString().slice(0, 10)}.pdf"`);
    doc.pipe(res);

    const margin = 36;
    const contentWidth = doc.page.width - margin * 2;
    const bottomY = () => doc.page.height - 40;
    let currentPage = 1;
    const cols = { sno: 30, vendor: 72, drawing: 88, partName: 130, description: 135, qty: 34, uom: 34 };
    const tableWidth = Object.values(cols).reduce((sum, w) => sum + w, 0);
    const tableX = margin + Math.max(0, (contentWidth - tableWidth) / 2);

    const fitText = (text, maxWidth) => {
      const value = String(text ?? '-').replace(/\s+/g, ' ').trim() || '-';
      if (doc.widthOfString(value) <= maxWidth) return value;
      const ell = '...';
      let out = value;
      while (out.length > 0 && doc.widthOfString(`${out}${ell}`) > maxWidth) out = out.slice(0, -1);
      return out ? `${out}${ell}` : ell;
    };
    const fmt = (v) => {
      const n = parseFloat(v);
      if (v === null || v === undefined || v === '' || Number.isNaN(n)) return '-';
      return n.toFixed(3).replace(/\.?0+$/, '');
    };
    const fmtMM = (v) => {
      const n = fmt(v);
      return n === '-' ? '-' : `${n}mm`;
    };
    const getDescriptionLine = (row) => {
      const length = fmtMM(row.length);
      const width = fmtMM(row.width);
      const height = fmtMM(row.height);
      const thickness = fmtMM(row.thickness);
      const weight = fmt(row.weight);
      const grade = row.material_grade || '-';
      return `${length} x ${width} x ${height} x ${thickness} x ${weight}${grade !== '-' ? ` (${grade})` : ''}`;
    };
    const drawPageNumber = (pageNo) => {
      const footerY = doc.page.height - margin - 12;
      doc.font('Helvetica').fontSize(9).fillColor('#000000');
      doc.text(String(pageNo), margin, footerY, { width: contentWidth, align: 'center', lineBreak: false });
    };

    let y = 42;
    doc.font('Helvetica-Bold').fontSize(15).text('Bill Of Materials', margin, y, { width: contentWidth, align: 'center' });
    y += 20;
    doc.fontSize(10);
    const bomNoValue = bomList?.[0]?.bom_number || bomList?.[0]?.bom_number_display || '-';
    const productNameValue = material?.description || material?.material_number || '-';
    const labelWidth = Math.ceil(Math.max(doc.widthOfString('BOM No'), doc.widthOfString('Product Name')));
    const metaGap = 6;
    const drawMetaRow = (label, value) => {
      doc.font('Helvetica-Bold').text(label, margin, y, { lineBreak: false });
      doc.font('Helvetica').text(':', margin + labelWidth + metaGap, y, { lineBreak: false });
      doc.text(` ${value}`, margin + labelWidth + metaGap + 4, y, { width: contentWidth - (labelWidth + metaGap + 4), align: 'left' });
      y += doc.heightOfString(` ${value}`, { width: contentWidth - (labelWidth + metaGap + 4), align: 'left' }) + 2;
    };
    drawMetaRow('BOM No', bomNoValue);
    drawMetaRow('Product Name', productNameValue);
    y += 6;

    const drawCell = (x, cellY, w, h, text, opts = {}) => {
      const { align = 'left', bold = false, fontSize = 8, lineBreak = false, paddingX = 3, paddingY = 2, noWrap = true, lineGap = 0 } = opts;
      doc.rect(x, cellY, w, h).stroke();
      doc.font(bold ? 'Helvetica-Bold' : 'Helvetica').fontSize(fontSize);
      const value = text == null || text === '' ? '-' : String(text);
      const textWidth = w - (paddingX * 2);
      const textHeight = h - (paddingY * 2);
      if (noWrap) {
        const printable = fitText(value, textWidth);
        const lineH = doc.currentLineHeight();
        const textY = cellY + Math.max(paddingY, (h - lineH) / 2);
        doc.text(printable, x + paddingX, textY, { width: textWidth, height: textHeight, align, lineBreak, lineGap });
        return;
      }
      const normalized = value.replace(/\s+/g, ' ').trim() || '-';
      const words = normalized.split(' ');
      const lines = [];
      let current = '';
      for (const word of words) {
        const candidate = current ? `${current} ${word}` : word;
        if (doc.widthOfString(candidate) <= textWidth) current = candidate;
        else if (current) {
          lines.push(current);
          current = word;
        } else {
          let clipped = word;
          while (clipped.length > 1 && doc.widthOfString(clipped) > textWidth) clipped = clipped.slice(0, -1);
          lines.push(clipped);
          current = word.slice(clipped.length).trim();
        }
      }
      if (current) lines.push(current);
      const step = doc.currentLineHeight() + lineGap;
      const maxLines = Math.max(1, Math.floor((textHeight + lineGap) / step));
      const renderLines = lines.slice(0, maxLines);
      const blockH = renderLines.length * step - (renderLines.length > 0 ? lineGap : 0);
      const startY = cellY + Math.max(paddingY, (h - blockH) / 2);
      renderLines.forEach((line, idx) => {
        const lineY = startY + (idx * step);
        let lineX = x + paddingX;
        const lineW = doc.widthOfString(line);
        if (align === 'center') lineX = x + paddingX + Math.max(0, (textWidth - lineW) / 2);
        if (align === 'right') lineX = x + paddingX + Math.max(0, textWidth - lineW);
        doc.text(line, lineX, lineY, { lineBreak: false });
      });
    };

    const drawHeader = () => {
      let x = tableX;
      const h = 14;
      drawCell(x, y, cols.sno, h, 'S.No', { bold: true, fontSize: 8 }); x += cols.sno;
      drawCell(x, y, cols.vendor, h, 'Vendor Code', { bold: true, fontSize: 8 }); x += cols.vendor;
      drawCell(x, y, cols.drawing, h, 'Drawing No', { bold: true, fontSize: 8 }); x += cols.drawing;
      drawCell(x, y, cols.partName, h, 'Part Name', { bold: true, fontSize: 8 }); x += cols.partName;
      drawCell(x, y, cols.description, h, 'Description', { bold: true, fontSize: 8 }); x += cols.description;
      drawCell(x, y, cols.qty, h, 'Qty', { bold: true, fontSize: 8, align: 'right' }); x += cols.qty;
      drawCell(x, y, cols.uom, h, 'UOM', { bold: true, fontSize: 8, align: 'center' });
      y += h;
    };

    const getTypeBucket = (row) => {
      const code = String(row.material_type_code || '').toUpperCase();
      const name = String(row.material_type_name || '').toUpperCase();
      const combined = `${code} ${name}`;
      if (combined.includes('SEMI_FIN') || combined.includes('SEMI FIN')) return 'semi_finished';
      if (combined.includes('RAW_MAT') || combined.includes('RAW MATERIAL') || combined.includes('RAW_MATERIAL')) return 'raw_material';
      if (combined.includes('FASTENER') || combined.includes('FASTENERS')) return 'fasteners';
      return 'others';
    };
    const bucketOrder = ['semi_finished', 'raw_material', 'fasteners', 'others'];
    const bucketTitles = { semi_finished: 'SEMI FINISHED GOOD', raw_material: 'RAW MATERIAL', fasteners: 'FASTENERS', others: 'OTHERS' };
    const groupedRows = { semi_finished: [], raw_material: [], fasteners: [], others: [] };
    rows.forEach((r) => groupedRows[getTypeBucket(r)].push(r));

    const drawSectionHeader = (title) => {
      const sectionH = 13;
      if (y + sectionH > bottomY()) {
        drawPageNumber(currentPage);
        doc.addPage(); currentPage += 1; y = 42;
        drawHeader();
      }
      drawCell(tableX, y, tableWidth, sectionH, title, { bold: true, fontSize: 8, align: 'center', paddingY: 2 });
      y += sectionH;
    };

    drawHeader();
    let serialNo = 1;
    bucketOrder.forEach((bucketKey) => {
      const bucketRows = groupedRows[bucketKey];
      if (!bucketRows?.length) return;
      drawSectionHeader(bucketTitles[bucketKey]);
      bucketRows.forEach((row) => {
        doc.font('Helvetica').fontSize(7);
        const descriptionLine = getDescriptionLine(row);
        const drawingTextH = doc.heightOfString(row.drawing_no || '-', { width: cols.drawing - 6 });
        const partNameTextH = doc.heightOfString(row.part_name || '-', { width: cols.partName - 6, lineGap: 0.5 });
        const descriptionTextH = doc.heightOfString(descriptionLine, { width: cols.description - 6, lineGap: 0.5 });
        const rowH = Math.min(56, Math.max(18, Math.ceil(drawingTextH + 6), Math.ceil(partNameTextH + 6), Math.ceil(descriptionTextH + 6)));
        if (y + rowH > bottomY()) {
          drawPageNumber(currentPage);
          doc.addPage(); currentPage += 1; y = 42;
          drawHeader();
          drawSectionHeader(bucketTitles[bucketKey]);
        }
        let x = tableX;
        drawCell(x, y, cols.sno, rowH, String(serialNo), { fontSize: 7 }); x += cols.sno;
        drawCell(x, y, cols.vendor, rowH, row.vendor_code || '-', { fontSize: 7, align: 'center' }); x += cols.vendor;
        drawCell(x, y, cols.drawing, rowH, row.drawing_no || '-', { noWrap: false, lineGap: 0.5, fontSize: 7 }); x += cols.drawing;
        drawCell(x, y, cols.partName, rowH, row.part_name || '-', { noWrap: false, lineGap: 0.5, fontSize: 7 }); x += cols.partName;
        drawCell(x, y, cols.description, rowH, descriptionLine, { noWrap: false, lineGap: 0.5, fontSize: 7 }); x += cols.description;
        drawCell(x, y, cols.qty, rowH, fmt(row.qty), { align: 'right', fontSize: 7 }); x += cols.qty;
        drawCell(x, y, cols.uom, rowH, row.uom || '-', { align: 'center', fontSize: 7 });
        y += rowH;
        serialNo += 1;
      });
    });

    drawPageNumber(currentPage);
    doc.end();
  } catch (error) {
    console.error('Error generating BOM List PDF:', error);
    if (!res.headersSent) {
      return sendError(res, 'Failed to generate PDF', 500);
    }
  }
});

// Get BOM list with required qty, stock qty, and purchase qty
exports.getBOMList = asyncHandler(async (req, res) => {
  const { customerPOId, companyId, plantId, itemId } = req.body;
  const companyIdFromUser = req.user?.companyId || companyId;
  const plantIdFromUser = req.user?.plantId || plantId;

  if (!customerPOId) {
    return sendError(res, 'Customer PO ID is required', 400);
  }

  // Get Customer PO with items
  const customerPO = await db.CustomerPO.findByPk(customerPOId, {
    include: [
      {
        model: db.CustomerPOItem,
        as: 'items',
        include: [
          {
            model: db.Material,
            as: 'material',
            attributes: ['id', 'material_number', 'description', 'drawing_number']
          }
        ]
      }
    ]
  });

  if (!customerPO) {
    return sendError(res, 'Customer PO not found', 404);
  }

  const finalPlantId = customerPO.plant_id || plantIdFromUser;
  const finalCompanyId = customerPO.company_id || companyIdFromUser;

  // Map to store aggregated quantities by material (all components from BOM only)
  const materialMap = new Map();

  // Recursive function to fetch components and calculate quantities (all components from BOM)
  const fetchComponentsRecursively = async (bomId, parentRequiredQty, processedMaterials = new Set(), level = 0) => {
    if (processedMaterials.has(bomId)) {
      return; // Avoid infinite loops
    }
    processedMaterials.add(bomId);

    const bomHeader = await db.BOMHeader.findByPk(bomId, {
      include: [
        {
          model: db.BOMComponent,
          as: 'components',
          where: { status: 'ACTIVE' },
          required: false,
          include: [
            {
              model: db.Material,
              as: 'material',
              attributes: ['id', 'material_number', 'description', 'drawing_number', 'material_type', 'material_type_id', 'net_weight', 'gross_weight', 'weight_uom_id', 'thickness_id', 'grade_id', 'thickness'],
              include: [
                {
                  model: db.MaterialType,
                  as: 'materialType',
                  attributes: ['id', 'type_code', 'type_name']
                },
                {
                  model: db.UOM,
                  as: 'weightUOM',
                  attributes: ['id', 'uom_code', 'uom_name']
                },
                {
                  model: db.MaterialThickness,
                  as: 'materialThickness',
                  attributes: ['id', 'thickness_value', 'thickness_unit']
                },
                {
                  model: db.MaterialGrade,
                  as: 'materialGrade',
                  attributes: ['id', 'grade_code', 'grade_name']
                }
              ]
            }
          ],
          order: [['item_number', 'ASC']]
        }
      ]
    });

    if (bomHeader && bomHeader.components) {
      for (const comp of bomHeader.components) {
        const componentQuantity = parseFloat(comp.component_quantity || 0);
        const material = comp.material;

        if (material) {
          // Calculate required quantity: BOM component qty * parent required qty
          // For top level: parentRequiredQty = item quantity
          // For sub-levels: parentRequiredQty = required qty of the parent component
          const requiredQty = componentQuantity * parentRequiredQty;

          // Get stock quantity
          const stockInfo = await atpService.getAvailableStock(
            material.id,
            finalPlantId
          );
          const stockQty = stockInfo.available || 0;

          // Calculate purchase qty = required qty - stock qty
          const purchaseQty = Math.max(0, requiredQty - stockQty);

          // Check if material is raw material
          const isRawMaterial = material.material_type === 'RAW_MATERIAL' || 
                                (material.materialType && material.materialType.type_code === 'RAW_MAT');

          // Get weight, thickness, and grade (for raw materials)
          let unitWeight = null;
          let weightUOM = null;
          let totalWeight = null;
          let thickness = null;
          let materialGrade = null;

          if (isRawMaterial) {
            // Get unit weight (per unit)
            unitWeight = material.net_weight || material.gross_weight || null;
            weightUOM = material.weightUOM?.uom_code || '';
            
            // Calculate total weight = unit weight * required quantity
            if (unitWeight && requiredQty > 0) {
              totalWeight = parseFloat(unitWeight) * requiredQty;
            }

            // Get thickness (property, not multiplied)
            if (material.materialThickness) {
              thickness = `${parseFloat(material.materialThickness.thickness_value || 0).toFixed(3)} ${material.materialThickness.thickness_unit || 'mm'}`;
            } else if (material.thickness) {
              thickness = `${parseFloat(material.thickness).toFixed(3)} mm`;
            }

            // Get grade (property, not multiplied)
            if (material.materialGrade) {
              materialGrade = material.materialGrade.grade_code || material.materialGrade.grade_name || null;
            }
          }

          // Aggregate by material ID (sum up quantities if same material appears multiple times)
          const materialId = material.id;
          if (materialMap.has(materialId)) {
            const existing = materialMap.get(materialId);
            existing.required_qty += requiredQty;
            // Stock is the same for same material, so keep the max (or could recalculate)
            existing.stock_qty = stockQty; // Use current stock
            existing.purchase_qty = Math.max(0, existing.required_qty - existing.stock_qty);
            // Update attributes if not already set (for raw materials)
            if (isRawMaterial) {
              if (!existing.unit_weight && unitWeight) {
                existing.unit_weight = unitWeight;
                existing.weight_uom = weightUOM;
              }
              // Recalculate total weight based on updated required_qty
              if (existing.unit_weight && existing.required_qty > 0) {
                existing.total_weight = parseFloat(existing.unit_weight) * existing.required_qty;
              }
              if (!existing.thickness && thickness) {
                existing.thickness = thickness;
              }
              if (!existing.material_grade && materialGrade) {
                existing.material_grade = materialGrade;
              }
            }
          } else {
            materialMap.set(materialId, {
              material_id: materialId,
              material_number: material.material_number || '',
              description: material.description || '',
              drawing_number: material.drawing_number || '-',
              required_qty: requiredQty,
              stock_qty: stockQty,
              purchase_qty: purchaseQty,
              level: level,
              material_type: material.material_type || material.materialType?.type_code || '',
              materialType: material.materialType ? {
                id: material.materialType.id,
                type_code: material.materialType.type_code,
                type_name: material.materialType.type_name
              } : null,
              // Add raw material attributes
              unit_weight: isRawMaterial ? unitWeight : null,
              total_weight: isRawMaterial ? totalWeight : null,
              weight_uom: isRawMaterial ? weightUOM : null,
              thickness: isRawMaterial ? thickness : null,
              material_grade: isRawMaterial ? materialGrade : null
            });
          }

          // Check if this material has a BOM (sub-component)
          const subBOM = await db.BOMHeader.findOne({
            where: {
              material_id: material.id,
              company_id: finalCompanyId,
              status: 'ACTIVE'
            },
            order: [['id', 'DESC']],
            limit: 1
          });

          if (subBOM) {
            // Recursively fetch sub-components with the calculated required qty for this component
            await fetchComponentsRecursively(
              subBOM.id,
              requiredQty, // Pass the required qty for this component as parent qty
              processedMaterials,
              level + 1
            );
          }
        }
      }
    }
  };

  // Process each item in the Customer PO
  // If itemId is provided, only process that specific item
  const itemsToProcess = itemId 
    ? customerPO.items.filter(item => item.id === parseInt(itemId))
    : customerPO.items;

  if (itemsToProcess && itemsToProcess.length > 0) {
    for (const item of itemsToProcess) {
      if (!item.material_id) continue;

      const itemQuantity = parseFloat(item.quantity || 0);
      if (itemQuantity <= 0) continue;

      // Get BOMs where this material is the main material
      const mainBOMs = await db.BOMHeader.findAll({
        where: {
          material_id: item.material_id,
          company_id: finalCompanyId,
          status: 'ACTIVE'
        },
        order: [['id', 'DESC']],
        limit: 1
      });

      // Process each BOM
      for (const bom of mainBOMs) {
        await fetchComponentsRecursively(bom.id, itemQuantity, new Set(), 0);
      }
    }
  }

  // Convert map to array and sort
  const bomList = Array.from(materialMap.values()).map(item => ({
    material_number: item.material_number,
    description: item.description,
    drawing_number: item.drawing_number,
    required_qty: Math.round(item.required_qty * 100) / 100, // Round to 2 decimals
    stock_qty: Math.round(item.stock_qty * 100) / 100,
    purchase_qty: Math.round(item.purchase_qty * 100) / 100,
    material_type: item.material_type || '',
    materialType: item.materialType,
    // Include raw material attributes
    unit_weight: item.unit_weight || null,
    total_weight: item.total_weight ? Math.round(item.total_weight * 1000) / 1000 : null, // Round to 3 decimals
    weight_uom: item.weight_uom || null,
    thickness: item.thickness || null,
    material_grade: item.material_grade || null
  }));

  // Sort by material number
  bomList.sort((a, b) => {
    if (a.material_number < b.material_number) return -1;
    if (a.material_number > b.material_number) return 1;
    return 0;
  });

  sendResponse(res, 200, true, 'BOM list retrieved successfully', bomList);
});

// Generate BOM Requirement List PDF
exports.generateBOMRequirementListPDF = asyncHandler(async (req, res) => {
  const { customerPOId, companyId, plantId, itemId } = req.body;
  const companyIdFromUser = req.user?.companyId || companyId;
  const plantIdFromUser = req.user?.plantId || plantId;

  if (!customerPOId) {
    return sendError(res, 'Customer PO ID is required', 400);
  }

  // Get Customer PO with items
  const customerPO = await db.CustomerPO.findByPk(customerPOId, {
    include: [
      {
        model: db.CustomerPOItem,
        as: 'items',
        include: [
          {
            model: db.Material,
            as: 'material',
            attributes: ['id', 'material_number', 'description', 'drawing_number']
          }
        ]
      },
      {
        model: db.BusinessPartner,
        as: 'customer',
        attributes: ['id', 'name', 'bp_number', 'legal_name']
      },
      {
        model: db.BPAddress,
        as: 'customerAddress',
        attributes: ['id', 'address_line1', 'address_line2', 'pincode'],
        include: [
          {
            model: db.City,
            as: 'city',
            attributes: ['id', 'city_name']
          },
          {
            model: db.State,
            as: 'state',
            attributes: ['id', 'state_name']
          }
        ]
      }
    ]
  });

  if (!customerPO) {
    return sendError(res, 'Customer PO not found', 404);
  }

  const finalPlantId = customerPO.plant_id || plantIdFromUser;
  const finalCompanyId = customerPO.company_id || companyIdFromUser;

  // Get print header
  const printHeader = await db.PrintHeader.findOne({
    where: { company_id: finalCompanyId, status: 'ACTIVE' },
    order: [['id', 'DESC']]
  });

  // Get company data
  const company = await db.Company.findByPk(finalCompanyId, {
    include: [
      { model: db.State, as: 'state', attributes: ['id', 'state_code', 'state_name', 'gst_state_code'] }
    ]
  });

  // Map to store aggregated quantities by material
  const materialMap = new Map();

  // Recursive function to fetch components and calculate quantities (same as getBOMList)
  const fetchComponentsRecursively = async (bomId, parentRequiredQty, processedMaterials = new Set(), level = 0) => {
    if (processedMaterials.has(bomId)) {
      return;
    }
    processedMaterials.add(bomId);

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

    if (bomHeader && bomHeader.components) {
      for (const comp of bomHeader.components) {
        const componentQuantity = parseFloat(comp.component_quantity || 0);
        const material = comp.material;

        if (material) {
          const requiredQty = componentQuantity * parentRequiredQty;
          const stockInfo = await atpService.getAvailableStock(material.id, finalPlantId);
          const stockQty = stockInfo.available || 0;
          const purchaseQty = Math.max(0, requiredQty - stockQty);

          const materialId = material.id;
          if (materialMap.has(materialId)) {
            const existing = materialMap.get(materialId);
            existing.required_qty += requiredQty;
            existing.stock_qty = stockQty;
            existing.purchase_qty = Math.max(0, existing.required_qty - existing.stock_qty);
          } else {
            materialMap.set(materialId, {
              material_number: material.material_number || '',
              description: material.description || '',
              drawing_number: material.drawing_number || '-',
              required_qty: requiredQty,
              stock_qty: stockQty,
              purchase_qty: purchaseQty
            });
          }

          // Check for sub-BOM
          const subBOM = await db.BOMHeader.findOne({
            where: {
              material_id: material.id,
              company_id: finalCompanyId,
              status: 'ACTIVE'
            },
            order: [['id', 'DESC']],
            limit: 1
          });

          if (subBOM) {
            await fetchComponentsRecursively(subBOM.id, requiredQty, processedMaterials, level + 1);
          }
        }
      }
    }
  };

  // Process each item in the Customer PO
  // If itemId is provided, only process that specific item
  const itemsToProcess = itemId 
    ? customerPO.items.filter(item => item.id === parseInt(itemId))
    : customerPO.items;

  if (itemsToProcess && itemsToProcess.length > 0) {
    for (const item of itemsToProcess) {
      if (!item.material_id) continue;
      const itemQuantity = parseFloat(item.quantity || 0);
      if (itemQuantity <= 0) continue;

      const mainBOMs = await db.BOMHeader.findAll({
        where: {
          material_id: item.material_id,
          company_id: finalCompanyId,
          status: 'ACTIVE'
        },
        order: [['id', 'DESC']],
        limit: 1
      });

      for (const bom of mainBOMs) {
        await fetchComponentsRecursively(bom.id, itemQuantity, new Set(), 0);
      }
    }
  }

  // Convert map to array and sort
  const bomList = Array.from(materialMap.values()).map(item => ({
    material_number: item.material_number,
    description: item.description,
    drawing_number: item.drawing_number,
    required_qty: Math.round(item.required_qty * 100) / 100,
    stock_qty: Math.round(item.stock_qty * 100) / 100,
    purchase_qty: Math.round(item.purchase_qty * 100) / 100
  }));

  bomList.sort((a, b) => {
    if (a.material_number < b.material_number) return -1;
    if (a.material_number > b.material_number) return 1;
    return 0;
  });

  try {
    // Create PDF document
    const doc = new PDFDocument({ margin: 50, size: 'A4' });
    
    // Set response headers
    res.setHeader('Content-Type', 'application/pdf');
    res.setHeader('Content-Disposition', `attachment; filename="BOM_Requirement_List_${customerPO.po_number || customerPOId}_${new Date().toISOString().split('T')[0]}.pdf"`);

    // Pipe PDF to response
    doc.pipe(res);

    // Constants
    const pageWidth = doc.page.width;
    const pageHeight = doc.page.height;
    const margin = 50;
    const contentWidth = pageWidth - (margin * 2);
    let pageNumber = 1;
    let isFirstPage = true;

    // Helper function to add logo
    const addLogo = (logoPath, x, y, maxWidth = 100) => {
      if (logoPath && printHeader) {
        try {
          const logoFullPath = path.join(__dirname, '../uploads', logoPath);
          if (fs.existsSync(logoFullPath)) {
            doc.image(logoFullPath, x, y, { width: maxWidth, fit: [maxWidth, 100] });
            return true;
          }
        } catch (err) {
          console.error('Error loading logo:', err);
        }
      }
      return false;
    };

    // Function to add header on each page
    const addPageHeader = (yStart = 50) => {
      let yPos = yStart;
      
      // Logo (left side)
      const logoAdded = addLogo(printHeader?.logo_path, margin, yPos, 80);

      // Company Name (centered)
      doc.fontSize(16).font('Helvetica-Bold');
      const companyName = printHeader?.company_name || company?.name || 'Company Name';
      doc.text(companyName, margin, yPos, { align: 'center', width: contentWidth });
      yPos += 20;

      if (printHeader?.former_name) {
        doc.fontSize(10).font('Helvetica');
        doc.text(`(Formerly Known as ${printHeader.former_name})`, margin, yPos, { align: 'center', width: contentWidth });
        yPos += 15;
      }

      // Address (centered)
      doc.fontSize(9).font('Helvetica');
      const address = printHeader?.address || company?.address || '';
      if (address) {
        const addressLines = address.split('\n').filter(line => line.trim());
        addressLines.forEach(line => {
          doc.text(line.trim(), margin, yPos, { align: 'center', width: contentWidth });
          yPos += 12;
        });
      }

      // Add GSTIN to address if available
      if (printHeader?.gstin) {
        doc.fontSize(9).font('Helvetica');
        doc.text(`GSTIN: ${printHeader.gstin}`, margin, yPos, { align: 'center', width: contentWidth });
        yPos += 12;
      }

      // Contact Information (centered)
      const contactInfo = [];
      if (printHeader?.phone) contactInfo.push(`Phone: ${printHeader.phone}`);
      if (printHeader?.fax) contactInfo.push(`Fax: ${printHeader.fax}`);
      if (printHeader?.email) contactInfo.push(`E-mail: ${printHeader.email}`);
      if (printHeader?.website) contactInfo.push(`Webpage: ${printHeader.website}`);

      if (contactInfo.length > 0) {
        yPos += 5;
        contactInfo.forEach(info => {
          doc.text(info, margin, yPos, { align: 'center', width: contentWidth });
          yPos += 12;
        });
      }

      // Draw line after header
      yPos += 10;
      doc.moveTo(margin, yPos).lineTo(pageWidth - margin, yPos).stroke();
      yPos += 20;

      // Title: BOM Requirement List
      doc.fontSize(14).font('Helvetica-Bold');
      doc.text('BOM REQUIREMENT LIST', margin, yPos, { align: 'center', width: contentWidth });
      yPos += 20;

      return yPos;
    };

    // Function to add customer and order details (only on first page)
    const addCustomerAndOrderDetails = (yStart) => {
      let yPos = yStart;

      // Two-column layout: Customer details (left) and Order Items (right)
      const leftColumnX = margin;
      const rightColumnX = pageWidth / 2 + 20;
      const columnWidth = (pageWidth - margin * 2) / 2 - 20;
      let leftY = yPos;
      let rightY = yPos;
      
      // Left Column: Customer Information
      doc.fontSize(10).font('Helvetica');
      doc.text(`Customer: ${customerPO.customer?.name || '-'}`, leftColumnX, leftY);
      leftY += 15;
      
      // Customer Code
      if (customerPO.customer?.bp_number) {
        doc.text(`Customer Code: ${customerPO.customer.bp_number}`, leftColumnX, leftY);
        leftY += 15;
      }
      
      // Customer PO Number
      if (customerPO.po_number) {
        doc.font('Helvetica-Bold');
        doc.text(`PO Number: ${customerPO.po_number}`, leftColumnX, leftY);
        doc.font('Helvetica');
        leftY += 15;
      }
      
      // Customer Address
      if (customerPO.customerAddress) {
        const addr = customerPO.customerAddress;
        const addressParts = [];
        if (addr.address_line1) addressParts.push(addr.address_line1);
        if (addr.address_line2) addressParts.push(addr.address_line2);
        if (addr.city?.city_name) addressParts.push(addr.city.city_name);
        if (addr.state?.state_name) addressParts.push(addr.state.state_name);
        if (addr.pincode) addressParts.push(addr.pincode);
        
        if (addressParts.length > 0) {
          doc.text(`Address: ${addressParts.join(', ')}`, leftColumnX, leftY, { width: columnWidth });
          leftY += 15;
        }
      }
      
      // Right Column: Order Items
      if (customerPO.items && customerPO.items.length > 0) {
        doc.fontSize(10).font('Helvetica-Bold');
        doc.text('Order Items:', rightColumnX, rightY);
        rightY += 15;
        
        doc.fontSize(10).font('Helvetica');
        
        // Format date as DD/MM/YYYY
        const formatDate = (dateString) => {
          if (!dateString) return '-';
          try {
            const date = new Date(dateString);
            const day = String(date.getDate()).padStart(2, '0');
            const month = String(date.getMonth() + 1).padStart(2, '0');
            const year = date.getFullYear();
            return `${day}/${month}/${year}`;
          } catch (e) {
            return dateString;
          }
        };
        
        customerPO.items.forEach((item, idx) => {
          if (item.material) {
            const code = item.material.material_number || '-';
            const name = item.material.description || '-';
            const qty = Math.round(parseFloat(item.quantity || 0)).toString();
            const orderDate = formatDate(customerPO.po_date);
            
            doc.text(`Code: ${code}`, rightColumnX, rightY);
            rightY += 15;
            doc.text(`Name: ${name}`, rightColumnX, rightY, { width: columnWidth });
            rightY += 15;
            doc.text(`Qty: ${qty}`, rightColumnX, rightY);
            rightY += 15;
            doc.text(`Order Date: ${orderDate}`, rightColumnX, rightY);
            rightY += 20;
          }
        });
      }
      
      // Use the maximum Y position from both columns
      return Math.max(leftY, rightY) + 15;
    };

    // Function to add table headers
    const addTableHeaders = (yStart) => {
      let yPos = yStart;
      doc.fontSize(9).font('Helvetica-Bold');
      
      // Column widths
      const colWidths = {
        sno: 30,
        partNo: 80,
        description: 140,
        drawingNo: 70,
        requiredQty: 60,
        stockQty: 60,
        purchaseQty: 75
      };

      let xPos = margin;
      doc.text('S.No.', xPos, yPos);
      xPos += colWidths.sno;
      doc.text('Part No.', xPos, yPos, { width: colWidths.partNo });
      xPos += colWidths.partNo;
      doc.text('Description', xPos, yPos, { width: colWidths.description });
      xPos += colWidths.description;
      doc.text('Drawing No.', xPos, yPos, { width: colWidths.drawingNo });
      xPos += colWidths.drawingNo;
      doc.text('Required Qty', xPos, yPos, { width: colWidths.requiredQty, align: 'right' });
      xPos += colWidths.requiredQty;
      doc.text('Stock Qty', xPos, yPos, { width: colWidths.stockQty, align: 'right' });
      xPos += colWidths.stockQty;
      doc.text('Purchase Qty', xPos, yPos, { width: colWidths.purchaseQty, align: 'right' });

      // Draw header line - extend exactly to the last column end
      yPos += 15;
      const lastColumnEnd = margin + colWidths.sno + colWidths.partNo + colWidths.description + colWidths.drawingNo + colWidths.requiredQty + colWidths.stockQty + colWidths.purchaseQty;
      doc.moveTo(margin, yPos).lineTo(lastColumnEnd, yPos).stroke();
      yPos += 10;

      return { yPos, colWidths };
    };

    // Function to add page number and footer
    const addPageFooter = () => {
      const footerY = pageHeight - 30;
      doc.fontSize(8).font('Helvetica');
      doc.text(`Page ${pageNumber}`, margin, footerY, { align: 'right', width: contentWidth });
    };

    // Header Section (First Page)
    let yPosition = addPageHeader(50);
    
    // Customer and Order Details (First Page Only)
    yPosition = addCustomerAndOrderDetails(yPosition);

    // Table Header (First Page)
    const { yPos: tableYPos, colWidths } = addTableHeaders(yPosition);
    yPosition = tableYPos;

    // Add page footer for first page
    addPageFooter();

    // Table Data - Start items immediately after headers on first page
    doc.fontSize(9).font('Helvetica');
    const formatQty = (qty) => {
      return Math.round(parseFloat(qty || 0)).toString();
    };

    // Start adding items immediately - ensure first item is ALWAYS on same page as headers
    // No page break check for first item - it must be on the same page
    const rowHeight = 20;

    bomList.forEach((item, index) => {
      // For the first item, NEVER create a new page - always add it on the current page
      // For subsequent items, check if we need a new page
      if (index > 0) {
        // Check if we need a new page BEFORE adding the item
        if (yPosition + rowHeight > pageHeight - 100) {
          // Create new page
          doc.addPage();
          pageNumber++;
          isFirstPage = false;
          
          // Add header on new page
          yPosition = addPageHeader(50);
          
          // Add table headers on new page
          const { yPos: newTableYPos } = addTableHeaders(yPosition);
          yPosition = newTableYPos;
          
          // Add page footer
          addPageFooter();
        }
      }

      let xPos = margin;
      doc.text((index + 1).toString(), xPos, yPosition);
      xPos += colWidths.sno;
      
      // Part No. (from material_number)
      doc.text(item.material_number || '-', xPos, yPosition, { width: colWidths.partNo });
      xPos += colWidths.partNo;
      
      // Description
      doc.text(item.description || '-', xPos, yPosition, { width: colWidths.description });
      xPos += colWidths.description;
      
      doc.text(item.drawing_number || '-', xPos, yPosition, { width: colWidths.drawingNo });
      xPos += colWidths.drawingNo;
      
      doc.text(formatQty(item.required_qty), xPos, yPosition, { width: colWidths.requiredQty, align: 'right' });
      xPos += colWidths.requiredQty;
      
      doc.text(formatQty(item.stock_qty), xPos, yPosition, { width: colWidths.stockQty, align: 'right' });
      xPos += colWidths.stockQty;
      
      doc.font('Helvetica-Bold');
      doc.text(formatQty(item.purchase_qty), xPos, yPosition, { width: colWidths.purchaseQty, align: 'right' });
      doc.font('Helvetica');
      
      yPosition += 20;
    });

    // Finalize PDF
    doc.end();
  } catch (error) {
    console.error('Error generating BOM Requirement List PDF:', error);
    if (!res.headersSent) {
      return sendError(res, 'Failed to generate PDF', 500);
    }
  }
});

// Generate Raw Materials Requirements PDF (only raw materials with weight, thickness, grade)
exports.generateRawMaterialsRequirementsPDF = asyncHandler(async (req, res) => {
  const { customerPOId, companyId, plantId, itemId } = req.body;
  const companyIdFromUser = req.user?.companyId || companyId;
  const plantIdFromUser = req.user?.plantId || plantId;

  if (!customerPOId || !itemId) {
    return sendError(res, 'Customer PO ID and Item ID are required', 400);
  }

  // Get Customer PO with items
  const customerPO = await db.CustomerPO.findByPk(customerPOId, {
    include: [
      {
        model: db.CustomerPOItem,
        as: 'items',
        include: [
          {
            model: db.Material,
            as: 'material',
            attributes: ['id', 'material_number', 'description', 'drawing_number'],
            include: [
              { model: db.MaterialType, as: 'materialType', attributes: ['id', 'type_code', 'type_name'] }
            ]
          }
        ]
      },
      {
        model: db.BusinessPartner,
        as: 'customer',
        attributes: ['id', 'name', 'bp_number', 'legal_name']
      },
      {
        model: db.BPAddress,
        as: 'customerAddress',
        attributes: ['id', 'address_line1', 'address_line2', 'pincode'],
        include: [
          {
            model: db.City,
            as: 'city',
            attributes: ['id', 'city_name']
          },
          {
            model: db.State,
            as: 'state',
            attributes: ['id', 'state_name']
          }
        ]
      }
    ]
  });

  if (!customerPO) {
    return sendError(res, 'Customer PO not found', 404);
  }

  const selectedItem = customerPO.items.find(item => item.id === parseInt(itemId));
  if (!selectedItem) {
    return sendError(res, 'PO Item not found', 404);
  }

  const finalPlantId = customerPO.plant_id || plantIdFromUser;
  const finalCompanyId = customerPO.company_id || companyIdFromUser;

  // Get print header
  const printHeader = await db.PrintHeader.findOne({
    where: { company_id: finalCompanyId, status: 'ACTIVE' },
    order: [['id', 'DESC']]
  });

  // Get company data
  const company = await db.Company.findByPk(finalCompanyId, {
    include: [
      { model: db.State, as: 'state', attributes: ['id', 'state_code', 'state_name', 'gst_state_code'] }
    ]
  });

  // Get raw material type ID
  const rawMaterialType = await db.MaterialType.findOne({
    where: {
      company_id: finalCompanyId,
      type_code: 'RAW_MAT',
      is_active: true
    }
  });

  const rawMaterialTypeId = rawMaterialType?.id;

  // Map to store aggregated quantities by material (only raw materials)
  const materialMap = new Map();

  // Recursive function to fetch components and calculate quantities (filter for raw materials only)
  const fetchComponentsRecursively = async (bomId, parentRequiredQty, processedMaterials = new Set(), level = 0) => {
    if (processedMaterials.has(bomId)) {
      return;
    }
    processedMaterials.add(bomId);

    const bomHeader = await db.BOMHeader.findByPk(bomId, {
      include: [
        {
          model: db.BOMComponent,
          as: 'components',
          where: { status: 'ACTIVE' },
          required: false,
          include: [
            {
              model: db.Material,
              as: 'material',
              attributes: ['id', 'material_number', 'description', 'drawing_number', 'material_type', 'material_type_id', 'gross_weight', 'net_weight', 'weight_uom_id', 'thickness', 'thickness_id', 'grade_id'],
              include: [
                { model: db.MaterialThickness, as: 'materialThickness', attributes: ['id', 'thickness_code', 'thickness_value', 'thickness_unit', 'description'] },
                { model: db.MaterialGrade, as: 'materialGrade', attributes: ['id', 'grade_code', 'grade_name', 'description'] },
                { model: db.MaterialType, as: 'materialType', attributes: ['id', 'type_code', 'type_name'] },
                { model: db.UOM, as: 'weightUOM', attributes: ['id', 'uom_code', 'uom_name'] },
              ]
            }
          ],
          order: [['item_number', 'ASC']]
        }
      ]
    });

    if (bomHeader && bomHeader.components) {
      for (const comp of bomHeader.components) {
        const componentQuantity = parseFloat(comp.component_quantity || 0);
        const material = comp.material;

        if (material) {
          // Check if material is raw material
          const isRawMaterial = material.material_type === 'RAW_MATERIAL' || 
                                (rawMaterialTypeId && material.material_type_id === rawMaterialTypeId) ||
                                (material.materialType && material.materialType.type_code === 'RAW_MAT');

          // Only process raw materials
          if (isRawMaterial) {
            const requiredQty = componentQuantity * parentRequiredQty;
            const stockQty = await atpService.getAvailableStock(material.id, finalPlantId);
            const purchaseQty = Math.max(0, requiredQty - stockQty);

            // Get weight, thickness, and grade
            const weight = material.net_weight || material.gross_weight || null;
            const weightUOM = material.weightUOM?.uom_code || '';
            const thickness = material.materialThickness 
              ? `${parseFloat(material.materialThickness.thickness_value || 0).toFixed(3)} ${material.materialThickness.thickness_unit || 'mm'}`
              : (material.thickness ? `${parseFloat(material.thickness).toFixed(3)} mm` : null);
            const materialGrade = material.materialGrade 
              ? (material.materialGrade.grade_code || material.materialGrade.grade_name || null)
              : null;

            const materialId = material.id;
            if (materialMap.has(materialId)) {
              const existing = materialMap.get(materialId);
              existing.required_qty += requiredQty;
              existing.stock_qty = stockQty;
              existing.purchase_qty = Math.max(0, existing.required_qty - existing.stock_qty);
            } else {
              materialMap.set(materialId, {
                material_number: material.material_number || '',
                description: material.description || '',
                drawing_number: material.drawing_number || '-',
                required_qty: requiredQty,
                stock_qty: stockQty,
                purchase_qty: purchaseQty,
                weight: weight,
                weight_uom: weightUOM,
                thickness: thickness,
                material_grade: materialGrade
              });
            }
          }

          // Check for sub-BOM (even if not raw material, we need to traverse to find raw materials)
          const subBOM = await db.BOMHeader.findOne({
            where: {
              material_id: material.id,
              company_id: finalCompanyId,
              status: 'ACTIVE'
            },
            order: [['id', 'DESC']],
            limit: 1
          });

          if (subBOM) {
            await fetchComponentsRecursively(subBOM.id, componentQuantity * parentRequiredQty, processedMaterials, level + 1);
          }
        }
      }
    }
  };

  // Process the selected item
  if (selectedItem && selectedItem.material_id) {
    const itemQuantity = parseFloat(selectedItem.quantity || 0);
    if (itemQuantity > 0) {
      const mainBOMs = await db.BOMHeader.findAll({
        where: {
          material_id: selectedItem.material_id,
          company_id: finalCompanyId,
          status: 'ACTIVE'
        },
        order: [['id', 'DESC']],
        limit: 1
      });

      for (const bom of mainBOMs) {
        await fetchComponentsRecursively(bom.id, itemQuantity, new Set(), 0);
      }
    }
  }

  // Convert map to array and sort
  const rawMaterialsList = Array.from(materialMap.values()).map(item => ({
    material_number: item.material_number,
    description: item.description,
    required_qty: Math.round(item.required_qty * 100) / 100,
    stock_qty: Math.round(item.stock_qty * 100) / 100,
    purchase_qty: Math.round(item.purchase_qty * 100) / 100,
    weight: item.weight ? `${parseFloat(item.weight).toFixed(3)} ${item.weight_uom}`.trim() : '-',
    thickness: item.thickness || '-',
    material_grade: item.material_grade || '-'
  }));

  rawMaterialsList.sort((a, b) => {
    if (a.material_number < b.material_number) return -1;
    if (a.material_number > b.material_number) return 1;
    return 0;
  });

  try {
    // Create PDF document
    const doc = new PDFDocument({ margin: 50, size: 'A4' });
    
    // Set response headers
    res.setHeader('Content-Type', 'application/pdf');
    const materialNumber = selectedItem.material?.material_number || 'Item';
    res.setHeader('Content-Disposition', `attachment; filename="Raw_Materials_Requirements_${materialNumber}_${customerPO.po_number || customerPOId}_${new Date().toISOString().split('T')[0]}.pdf"`);

    // Pipe PDF to response
    doc.pipe(res);

    // Constants
    const pageWidth = doc.page.width;
    const pageHeight = doc.page.height;
    const margin = 50;
    const contentWidth = pageWidth - (margin * 2);
    let pageNumber = 1;
    let isFirstPage = true;

    // Helper function to add logo
    const addLogo = (logoPath, x, y, maxWidth = 100) => {
      if (logoPath && printHeader) {
        try {
          const logoFullPath = path.join(__dirname, '../uploads', logoPath);
          if (fs.existsSync(logoFullPath)) {
            doc.image(logoFullPath, x, y, { width: maxWidth, fit: [maxWidth, 100] });
            return true;
          }
        } catch (err) {
          console.error('Error loading logo:', err);
        }
      }
      return false;
    };

    // Function to add header on each page
    const addPageHeader = (yStart = 50) => {
      let yPos = yStart;
      
      // Logo (left side)
      const logoAdded = addLogo(printHeader?.logo_path, margin, yPos, 80);

      // Company Name (centered)
      doc.fontSize(16).font('Helvetica-Bold');
      const companyName = printHeader?.company_name || company?.name || 'Company Name';
      doc.text(companyName, margin, yPos, { align: 'center', width: contentWidth });
      yPos += 20;

      if (printHeader?.former_name) {
        doc.fontSize(10).font('Helvetica');
        doc.text(`(Formerly Known as ${printHeader.former_name})`, margin, yPos, { align: 'center', width: contentWidth });
        yPos += 15;
      }

      // Address (centered)
      doc.fontSize(9).font('Helvetica');
      const address = printHeader?.address || company?.address || '';
      if (address) {
        const addressLines = address.split('\n').filter(line => line.trim());
        addressLines.forEach(line => {
          doc.text(line.trim(), margin, yPos, { align: 'center', width: contentWidth });
          yPos += 12;
        });
      }

      // Add GSTIN to address if available
      if (printHeader?.gstin) {
        doc.fontSize(9).font('Helvetica');
        doc.text(`GSTIN: ${printHeader.gstin}`, margin, yPos, { align: 'center', width: contentWidth });
        yPos += 12;
      }

      // Contact Information (centered)
      const contactInfo = [];
      if (printHeader?.phone) contactInfo.push(`Phone: ${printHeader.phone}`);
      if (printHeader?.fax) contactInfo.push(`Fax: ${printHeader.fax}`);
      if (printHeader?.email) contactInfo.push(`E-mail: ${printHeader.email}`);
      if (printHeader?.website) contactInfo.push(`Webpage: ${printHeader.website}`);

      if (contactInfo.length > 0) {
        yPos += 5;
        contactInfo.forEach(info => {
          doc.text(info, margin, yPos, { align: 'center', width: contentWidth });
          yPos += 12;
        });
      }

      // Draw line after header
      yPos += 10;
      doc.moveTo(margin, yPos).lineTo(pageWidth - margin, yPos).stroke();
      yPos += 20;

      // Title: Raw Materials Requirements
      doc.fontSize(14).font('Helvetica-Bold');
      doc.text('RAW MATERIALS REQUIREMENTS', margin, yPos, { align: 'center', width: contentWidth });
      yPos += 20;

      return yPos;
    };

    // Add first page header
    let yPosition = addPageHeader(50);

    // PO Details
    doc.fontSize(10).font('Helvetica-Bold');
    doc.text('PO Details:', margin, yPosition);
    yPosition += 15;

    doc.fontSize(9).font('Helvetica');
    doc.text(`PO Number: ${customerPO.po_number || '-'}`, margin, yPosition);
    yPosition += 12;
    doc.text(`PO Date: ${customerPO.po_date ? new Date(customerPO.po_date).toLocaleDateString('en-IN') : '-'}`, margin, yPosition);
    yPosition += 12;
    doc.text(`Customer: ${customerPO.customer?.name || customerPO.customer?.legal_name || '-'}`, margin, yPosition);
    yPosition += 12;
    
    // Selected Item Details
    doc.fontSize(10).font('Helvetica-Bold');
    doc.text('Item Details:', margin, yPosition);
    yPosition += 15;

    doc.fontSize(9).font('Helvetica');
    doc.text(`Material: ${selectedItem.material?.material_number || '-'} - ${selectedItem.material?.description || selectedItem.description || '-'}`, margin, yPosition);
    yPosition += 12;
    doc.text(`Quantity: ${parseFloat(selectedItem.quantity || 0).toFixed(2)} ${selectedItem.uom?.uom_code || ''}`, margin, yPosition);
    yPosition += 20;

    // Table headers - adjusted widths without Drawing No column
    const totalTableWidth = contentWidth;
    const colWidths = {
      sno: 35,
      materialNumber: 110,
      description: 180,
      weight: 75,
      thickness: 75,
      grade: 70,
      requiredQty: 75,
      stockQty: 75,
      purchaseQty: 75
    };

    yPosition += 10;
    doc.fontSize(8).font('Helvetica-Bold');
    let xPos = margin;
    
    doc.text('S.No', xPos, yPosition, { width: colWidths.sno });
    xPos += colWidths.sno;
    doc.text('Material No.', xPos, yPosition, { width: colWidths.materialNumber });
    xPos += colWidths.materialNumber;
    doc.text('Description', xPos, yPosition, { width: colWidths.description });
    xPos += colWidths.description;
    doc.text('Weight', xPos, yPosition, { width: colWidths.weight });
    xPos += colWidths.weight;
    doc.text('Thickness', xPos, yPosition, { width: colWidths.thickness });
    xPos += colWidths.thickness;
    doc.text('Grade', xPos, yPosition, { width: colWidths.grade });
    xPos += colWidths.grade;
    doc.text('Required', xPos, yPosition, { width: colWidths.requiredQty, align: 'right' });
    xPos += colWidths.requiredQty;
    doc.text('Stock', xPos, yPosition, { width: colWidths.stockQty, align: 'right' });
    xPos += colWidths.stockQty;
    doc.text('Purchase', xPos, yPosition, { width: colWidths.purchaseQty, align: 'right' });
    
    // Draw line under headers
    yPosition += 15;
    doc.moveTo(margin, yPosition).lineTo(pageWidth - margin, yPosition).stroke();
    yPosition += 10;

    // Helper function to format quantity
    const formatQty = (qty) => {
      return parseFloat(qty || 0).toFixed(2);
    };

    // Add data rows
    doc.fontSize(8).font('Helvetica');
    rawMaterialsList.forEach((item, index) => {
      // Check if we need a new page
      if (yPosition > pageHeight - 100) {
        doc.addPage();
        pageNumber++;
        yPosition = addPageHeader(50);
        
        // Redraw table headers
        doc.fontSize(8).font('Helvetica-Bold');
        xPos = margin;
        doc.text('S.No', xPos, yPosition, { width: colWidths.sno });
        xPos += colWidths.sno;
        doc.text('Material No.', xPos, yPosition, { width: colWidths.materialNumber });
        xPos += colWidths.materialNumber;
        doc.text('Description', xPos, yPosition, { width: colWidths.description });
        xPos += colWidths.description;
        doc.text('Weight', xPos, yPosition, { width: colWidths.weight });
        xPos += colWidths.weight;
        doc.text('Thickness', xPos, yPosition, { width: colWidths.thickness });
        xPos += colWidths.thickness;
        doc.text('Grade', xPos, yPosition, { width: colWidths.grade });
        xPos += colWidths.grade;
        doc.text('Required', xPos, yPosition, { width: colWidths.requiredQty, align: 'right' });
        xPos += colWidths.requiredQty;
        doc.text('Stock', xPos, yPosition, { width: colWidths.stockQty, align: 'right' });
        xPos += colWidths.stockQty;
        doc.text('Purchase', xPos, yPosition, { width: colWidths.purchaseQty, align: 'right' });
        yPosition += 15;
        doc.moveTo(margin, yPosition).lineTo(pageWidth - margin, yPosition).stroke();
        yPosition += 10;
        doc.fontSize(8).font('Helvetica');
      }

      xPos = margin;
      doc.text(String(index + 1), xPos, yPosition, { width: colWidths.sno });
      xPos += colWidths.sno;
      
      doc.text(item.material_number || '-', xPos, yPosition, { width: colWidths.materialNumber });
      xPos += colWidths.materialNumber;
      
      doc.text(item.description || '-', xPos, yPosition, { width: colWidths.description });
      xPos += colWidths.description;
      
      doc.text(item.weight || '-', xPos, yPosition, { width: colWidths.weight });
      xPos += colWidths.weight;
      
      doc.text(item.thickness || '-', xPos, yPosition, { width: colWidths.thickness });
      xPos += colWidths.thickness;
      
      doc.text(item.material_grade || '-', xPos, yPosition, { width: colWidths.grade });
      xPos += colWidths.grade;
      
      doc.text(formatQty(item.required_qty), xPos, yPosition, { width: colWidths.requiredQty, align: 'right' });
      xPos += colWidths.requiredQty;
      
      doc.text(formatQty(item.stock_qty), xPos, yPosition, { width: colWidths.stockQty, align: 'right' });
      xPos += colWidths.stockQty;
      
      doc.font('Helvetica-Bold');
      doc.text(formatQty(item.purchase_qty), xPos, yPosition, { width: colWidths.purchaseQty, align: 'right' });
      doc.font('Helvetica');
      
      yPosition += 20;
    });

    // Finalize PDF
    doc.end();
  } catch (error) {
    console.error('Error generating Raw Materials Requirements PDF:', error);
    if (!res.headersSent) {
      return sendError(res, 'Failed to generate PDF', 500);
    }
  }
});

