const db = require('../models');
const { sendResponse, sendError } = require('../utils/response');
const asyncHandler = require('../middlewares/asyncHandler');
const { Op } = require('sequelize');
const PDFDocument = require('pdfkit');

const toPositiveIntOrNull = (value) => {
  if (value === undefined || value === null || value === '') return null;
  const parsed = Number(value);
  return Number.isInteger(parsed) && parsed > 0 ? parsed : null;
};

const validateHeaderReferences = async (payload, transaction, currentBomId = null) => {
  const checks = [
    { field: 'company_id', model: db.Company, required: true, label: 'Company' },
    { field: 'plant_id', model: db.Plant, required: true, label: 'Plant' },
    { field: 'material_id', model: db.Material, required: true, label: 'Material' },
    { field: 'base_uom_id', model: db.UOM, required: true, label: 'Base UOM' },
  ];

  for (const check of checks) {
    const rawValue = payload[check.field];
    const normalized = toPositiveIntOrNull(rawValue);
    if (check.required && !normalized) {
      return `${check.label} is required`;
    }
    const exists = await check.model.findByPk(normalized, { attributes: ['id'], transaction });
    if (!exists) {
      return `${check.label} referenced record does not exist`;
    }
    payload[check.field] = normalized;
  }

  if (Object.prototype.hasOwnProperty.call(payload, 'parent_bom_id')) {
    const parentId = toPositiveIntOrNull(payload.parent_bom_id);
    if (!parentId) {
      payload.parent_bom_id = null;
    } else if (currentBomId && Number(parentId) === Number(currentBomId)) {
      return 'Parent BOM cannot be the same BOM';
    } else {
      const parent = await db.BOMHeader.findByPk(parentId, { attributes: ['id'], transaction });
      if (!parent) {
        return 'Parent BOM referenced record does not exist';
      }
      payload.parent_bom_id = parentId;
    }
  }

  return null;
};

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

const getBestMaterialCost = async (materialId, plantId, transaction) => {
  const material = await db.Material.findByPk(materialId, {
    attributes: ['id', 'unit_cost', 'last_purchase_rate'],
    transaction,
  });

  if (!material) return null;

  const directMaterialCost = toPositiveNumberOrNull(material.unit_cost);
  if (directMaterialCost !== null) return directMaterialCost;

  const valuationWhere = {
    material_id: materialId,
    status: 'ACTIVE',
  };

  let valuation = null;
  if (plantId) {
    valuation = await db.MaterialValuation.findOne({
      attributes: ['standard_price', 'moving_average_price'],
      where: { ...valuationWhere, plant_id: plantId },
      order: [['updated_at', 'DESC'], ['id', 'DESC']],
      transaction,
    });
  }
  if (!valuation) {
    valuation = await db.MaterialValuation.findOne({
      attributes: ['standard_price', 'moving_average_price'],
      where: valuationWhere,
      order: [['updated_at', 'DESC'], ['id', 'DESC']],
      transaction,
    });
  }
  if (valuation) {
    const standardPrice = toPositiveNumberOrNull(valuation.standard_price);
    if (standardPrice !== null) return standardPrice;
    const movingAvgPrice = toPositiveNumberOrNull(valuation.moving_average_price);
    if (movingAvgPrice !== null) return movingAvgPrice;
  }

  const plantWhere = {
    material_id: materialId,
    status: 'ACTIVE',
  };
  let plantData = null;
  if (plantId) {
    plantData = await db.MaterialPlant.findOne({
      attributes: ['opening_balance_unit_cost'],
      where: { ...plantWhere, plant_id: plantId },
      order: [['updated_at', 'DESC'], ['id', 'DESC']],
      transaction,
    });
  }
  if (!plantData) {
    plantData = await db.MaterialPlant.findOne({
      attributes: ['opening_balance_unit_cost'],
      where: plantWhere,
      order: [['updated_at', 'DESC'], ['id', 'DESC']],
      transaction,
    });
  }
  if (plantData) {
    const openingBalanceUnitCost = toPositiveNumberOrNull(plantData.opening_balance_unit_cost);
    if (openingBalanceUnitCost !== null) return openingBalanceUnitCost;
  }

  const preferredSupplier = await db.MaterialSupplier.findOne({
    attributes: ['standard_price'],
    where: {
      material_id: materialId,
      status: 'ACTIVE',
      is_preferred: true,
    },
    order: [['updated_at', 'DESC'], ['id', 'DESC']],
    transaction,
  });
  const preferredSupplierCost = toPositiveNumberOrNull(preferredSupplier?.standard_price);
  if (preferredSupplierCost !== null) return preferredSupplierCost;

  const anySupplier = await db.MaterialSupplier.findOne({
    attributes: ['standard_price'],
    where: {
      material_id: materialId,
      status: 'ACTIVE',
    },
    order: [['is_preferred', 'DESC'], ['updated_at', 'DESC'], ['id', 'DESC']],
    transaction,
  });
  const supplierCost = toPositiveNumberOrNull(anySupplier?.standard_price);
  if (supplierCost !== null) return supplierCost;

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

  return null;
};

const buildMaterialUnitCostMap = async (components = [], plantId, transaction) => {
  const materialIds = [...new Set(
    components
      .map((component) => Number(component?.material_id))
      .filter((id) => Number.isInteger(id) && id > 0)
  )];

  if (materialIds.length === 0) {
    return new Map();
  }

  const materialCostMap = new Map();
  for (const materialId of materialIds) {
    const bestCost = await getBestMaterialCost(materialId, plantId, transaction);
    if (bestCost !== null) {
      materialCostMap.set(Number(materialId), bestCost);
    }
  }

  return materialCostMap;
};

const resolveComponentRate = (component, materialCostMap) => {
  if (component?.rate !== undefined && component?.rate !== null && component?.rate !== '') {
    const explicitRate = parseFloat(component.rate);
    if (Number.isFinite(explicitRate) && explicitRate > 0) {
      return explicitRate;
    }
  }

  const materialId = Number(component?.material_id);
  if (Number.isInteger(materialId) && materialId > 0 && materialCostMap.has(materialId)) {
    return materialCostMap.get(materialId);
  }

  return 0;
};

exports.getAll = asyncHandler(async (req, res) => {
  const { page = 1, limit = 10, search, status, companyId, plantId, material_id, include_child_bom } = req.query;
  const offset = (page - 1) * 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 (material_id) {
    where.material_id = material_id;
  }

  if (status) {
    where.status = status;
  } else {
    where.status = { [Op.ne]: 'DELETED' };
  }

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

  // Show only parent/main BOM headers in list by default.
  // Child/sub-assembly BOMs (materials used as BOM components) are hidden unless explicitly requested.
  const includeChildBOM = String(include_child_bom || '').toLowerCase() === 'true';
  if (!includeChildBOM && !material_id) {
    // Hide explicit child BOMs from top-level BOM list.
    where.parent_bom_id = { [Op.is]: null };

    const childComponents = await db.BOMComponent.findAll({
      attributes: ['material_id'],
      include: [{
        model: db.BOMHeader,
        as: 'bom',
        attributes: [],
        required: true,
        where: {
          ...(where.company_id ? { company_id: where.company_id } : {}),
          ...(where.plant_id ? { plant_id: where.plant_id } : {}),
          status: { [Op.in]: ['ACTIVE', 'LOCKED'] }
        }
      }],
      group: ['BOMComponent.material_id'],
      raw: true
    });

    const childMaterialIds = childComponents
      .map((r) => Number(r.material_id))
      .filter((id) => Number.isFinite(id) && id > 0);

    if (childMaterialIds.length > 0) {
      where.material_id = {
        ...(where.material_id ? { [Op.eq]: where.material_id } : {}),
        [Op.notIn]: childMaterialIds
      };
    }
  }

  const includeOptions = [
    { model: db.Company, as: 'company', attributes: ['id', 'code', 'name'] },
    { model: db.Plant, as: 'plant', attributes: ['id', 'code', 'name'] },
    { model: db.BOMHeader, as: 'parentBOM', attributes: ['id', 'bom_number', 'alternative'], required: false },
    { 
      model: db.Material, 
      as: 'material', 
      attributes: [
        'id',
        'material_number',
        'description',
        'material_type',
        'material_type_id',
        'material_group',
        'material_group_id',
        'drawing_number',
        'drawing_file_name',
        'drawing_file_path',
        'drawing_file_type',
      ],
      required: true, // Keep required to ensure material exists, but no type filter
      include: [
        { model: db.MaterialType, as: 'materialType', attributes: ['id', 'type_code', 'type_name'] },
        { model: db.MaterialGroup, as: 'materialGroup', attributes: ['id', 'group_code', 'group_name'] },
      ]
    },
    { model: db.UOM, as: 'baseUOM', attributes: ['id', 'uom_code', 'uom_name'] },
  ];

  const { count, rows } = await db.BOMHeader.findAndCountAll({
    where,
    limit: parseInt(limit),
    offset: parseInt(offset),
    order: [['id', 'DESC']],
    include: includeOptions,
  });

  // Recursive function to count all components including nested BOMs
  const countAllComponents = async (bomId, processedBOMs = new Set()) => {
    // Prevent infinite recursion
    if (processedBOMs.has(bomId)) {
      return 0;
    }
    processedBOMs.add(bomId);

    // Get direct components for this BOM
    const components = await db.BOMComponent.findAll({
      where: {
        bom_id: bomId,
        status: 'ACTIVE'
      },
      include: [
        {
          model: db.Material,
          as: 'material',
          attributes: ['id', 'material_type', 'material_type_id']
        }
      ]
    });

    let totalCount = components.length;

    // For each component, check if it has a nested BOM (semi-finished or finished good)
    for (const component of components) {
      const material = component.material;
      if (material) {
        // Check if this material has a BOM (could be semi-finished or finished good)
        const nestedBOM = await db.BOMHeader.findOne({
          where: {
            material_id: material.id,
            status: 'ACTIVE',
            ...(where.company_id && { company_id: where.company_id })
          },
          order: [['id', 'DESC']],
          limit: 1
        });

        if (nestedBOM) {
          // Recursively count components in the nested BOM
          const nestedCount = await countAllComponents(nestedBOM.id, processedBOMs);
          totalCount += nestedCount;
        }
      }
    }

    return totalCount;
  };

  // Get components count for each BOM header (including nested BOMs)
  let items = rows;
  if (rows.length > 0) {
    // Create a map to store counts (to avoid recalculating for same BOM)
    const componentsCountMap = {};
    
    // Calculate counts for each BOM
    for (const bom of rows) {
      const bomId = bom.id;
      if (!componentsCountMap[bomId]) {
        componentsCountMap[bomId] = await countAllComponents(bomId);
      }
    }

    // Add components count to each record
    items = rows.map(bom => {
      const bomData = bom.toJSON();
      bomData.components_count = componentsCountMap[bom.id] || 0;
      return bomData;
    });
  }

  sendResponse(res, 200, true, 'BOM Headers retrieved successfully', {
    items: items,
    totalItems: count,
    totalPages: Math.ceil(count / limit),
    currentPage: parseInt(page),
  });
});

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

  const bomHeader = await db.BOMHeader.findByPk(id, {
    include: [
      { model: db.Company, as: 'company', attributes: ['id', 'code', 'name'] },
      { model: db.Plant, as: 'plant', attributes: ['id', 'code', 'name'] },
      { 
        model: db.Material, 
        as: 'material', 
        attributes: ['id', 'material_number', 'description', 'material_type', 'material_type_id', 'material_group', 'material_group_id'],
        include: [
          { model: db.MaterialType, as: 'materialType', attributes: ['id', 'type_code', 'type_name'] },
          { model: db.MaterialGroup, as: 'materialGroup', attributes: ['id', 'group_code', 'group_name'] },
        ]
      },
      { model: db.UOM, as: 'baseUOM', attributes: ['id', 'uom_code', 'uom_name'] },
      {
        model: db.BOMComponent,
        as: 'components',
        where: { status: 'ACTIVE' },
        required: false,
        include: [
          { 
            model: db.Material, 
            as: 'material', 
            attributes: ['id', 'material_number', 'description', 'material_type', 'material_type_id', 'drawing_number', 'drawing_file_path', 'drawing_file_name', 'drawing_file_type', 'gross_weight', 'net_weight', 'weight_uom_id', 'thickness', 'thickness_id', 'grade_id', 'unit_cost', 'last_purchase_rate'],
            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: 'componentUOM', attributes: ['id', 'uom_code', 'uom_name'] },
        ],
        order: [['item_number', 'ASC']],
      },
      { model: db.BOMHeader, as: 'parentBOM', attributes: ['id', 'bom_number', 'alternative'], required: false },
    ],
  });

  if (!bomHeader) {
    return sendError(res, 'BOM Header not found', 404);
  }

  sendResponse(res, 200, true, 'BOM Header retrieved successfully', bomHeader);
});

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

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

  try {
    bomHeaderData.created_by = userId;
    bomHeaderData.updated_by = userId;

    if (bomHeaderData.company_id === undefined && req.user) {
      bomHeaderData.company_id = req.user.companyId;
    }

    // Support child BOM creation flow from source BOM link.
    if (!bomHeaderData.parent_bom_id && data.source_bom_id) {
      bomHeaderData.parent_bom_id = data.source_bom_id;
    }

    const createRefError = await validateHeaderReferences(bomHeaderData, transaction, null);
    if (createRefError) {
      await transaction.rollback();
      return sendError(res, createRefError, 400);
    }

    const bomHeader = await db.BOMHeader.create(bomHeaderData, { transaction });

    if (components && Array.isArray(components) && components.length > 0) {
      const materialCostMap = await buildMaterialUnitCostMap(
        components,
        Number(bomHeaderData.plant_id || null),
        transaction
      );
      const bomComponents = components.map((component, index) => ({
        bom_id: bomHeader.id,
        item_number: component.item_number || (index + 1) * 10,
        material_id: component.material_id,
        component_quantity: parseFloat(component.component_quantity) || 0,
        component_uom_id: component.component_uom_id,
        rate: resolveComponentRate(component, materialCostMap),
        component_weight: component.component_weight !== undefined && component.component_weight !== null && component.component_weight !== ''
          ? parseFloat(component.component_weight)
          : null,
        component_height: component.component_height !== undefined && component.component_height !== null && component.component_height !== ''
          ? parseFloat(component.component_height)
          : null,
        component_length: component.component_length !== undefined && component.component_length !== null && component.component_length !== ''
          ? parseFloat(component.component_length)
          : null,
        component_thickness: component.component_thickness !== undefined && component.component_thickness !== null && component.component_thickness !== ''
          ? parseFloat(component.component_thickness)
          : null,
        component_material_grade: component.component_material_grade || null,
        scrap_percentage: parseFloat(component.scrap_percentage) || 0,
        item_category: component.item_category || 'L',
        operation_number: component.operation_number || null,
        fixed_quantity: component.fixed_quantity || false,
        status: component.status || 'ACTIVE',
        created_by: userId,
        updated_by: userId,
      }));

      await db.BOMComponent.bulkCreate(bomComponents, { transaction });
    }

    await transaction.commit();

    const createdBOM = await db.BOMHeader.findByPk(bomHeader.id, {
      include: [
        { model: db.Company, as: 'company', attributes: ['id', 'code', 'name'] },
        { model: db.Plant, as: 'plant', attributes: ['id', 'code', 'name'] },
        { model: db.BOMHeader, as: 'parentBOM', attributes: ['id', 'bom_number', 'alternative'], required: false },
        { model: db.Material, as: 'material', attributes: ['id', 'material_number', 'description'] },
        { model: db.UOM, as: 'baseUOM', attributes: ['id', 'uom_code', 'uom_name'] },
        {
          model: db.BOMComponent,
          as: 'components',
          where: { status: 'ACTIVE' },
          required: false,
          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', 'unit_cost', 'last_purchase_rate'],
              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: 'componentUOM', attributes: ['id', 'uom_code', 'uom_name'] },
          ],
          order: [['item_number', 'ASC']],
        },
      ],
    });

    sendResponse(res, 201, true, 'BOM Header created successfully', createdBOM);
  } 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 { components, ...bomHeaderData } = data;

  const bomHeader = await db.BOMHeader.findByPk(id);
  if (!bomHeader) {
    return sendError(res, 'BOM Header not found', 404);
  }

  if (bomHeader.status === 'LOCKED') {
    return sendError(res, 'Locked BOM Header cannot be updated', 400);
  }

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

  try {
    bomHeaderData.updated_by = userId;

    const updateRefError = await validateHeaderReferences(bomHeaderData, transaction, id);
    if (updateRefError) {
      await transaction.rollback();
      return sendError(res, updateRefError, 400);
    }

    await bomHeader.update(bomHeaderData, { transaction });

    if (components !== undefined) {
      const existingComponents = await db.BOMComponent.findAll({
        where: { bom_id: id },
        transaction
      });

      const existingById = new Map(existingComponents.map((c) => [Number(c.id), c]));
      const existingByItemNo = new Map(existingComponents.map((c) => [Number(c.item_number), c]));
      const retainedComponentIds = new Set();

      if (Array.isArray(components) && components.length > 0) {
        const materialCostMap = await buildMaterialUnitCostMap(
          components,
          Number(bomHeaderData.plant_id || bomHeader.plant_id || null),
          transaction
        );
        // Prevent duplicate item numbers in incoming payload.
        const normalizedItemNumbers = components.map((component, index) => (
          component.item_number || (index + 1) * 10
        ));
        const hasDuplicateItemNumbers = new Set(normalizedItemNumbers).size !== normalizedItemNumbers.length;
        if (hasDuplicateItemNumbers) {
          await transaction.rollback();
          return sendError(res, 'Duplicate item number found in BOM components', 400);
        }

        // Temporarily move existing item numbers away to avoid unique key collisions
        // while reordering/swapping rows (unique key: bom_id + item_number).
        if (existingComponents.length > 0) {
          for (const existingRow of existingComponents) {
            await existingRow.update(
              { item_number: -1000000 - Number(existingRow.id), updated_by: userId },
              { transaction }
            );
          }
        }

        for (let index = 0; index < components.length; index += 1) {
          const component = components[index];
          const normalizedItemNumber = component.item_number || (index + 1) * 10;
          const incomingId = component.id ? Number(component.id) : null;

          // Reuse existing row by id (preferred), else by item number.
          const existingRow = (incomingId && existingById.get(incomingId))
            || existingByItemNo.get(Number(normalizedItemNumber))
            || null;

          const payload = {
            bom_id: id,
            item_number: normalizedItemNumber,
            material_id: component.material_id,
            component_quantity: parseFloat(component.component_quantity) || 0,
            component_uom_id: component.component_uom_id,
            rate: resolveComponentRate(component, materialCostMap),
            component_weight: component.component_weight !== undefined && component.component_weight !== null && component.component_weight !== ''
              ? parseFloat(component.component_weight)
              : null,
            component_height: component.component_height !== undefined && component.component_height !== null && component.component_height !== ''
              ? parseFloat(component.component_height)
              : null,
            component_length: component.component_length !== undefined && component.component_length !== null && component.component_length !== ''
              ? parseFloat(component.component_length)
              : null,
            component_thickness: component.component_thickness !== undefined && component.component_thickness !== null && component.component_thickness !== ''
              ? parseFloat(component.component_thickness)
              : null,
            component_material_grade: component.component_material_grade || null,
            scrap_percentage: parseFloat(component.scrap_percentage) || 0,
            item_category: component.item_category || 'L',
            operation_number: component.operation_number || null,
            fixed_quantity: Boolean(component.fixed_quantity),
            status: component.status || 'ACTIVE',
            updated_by: userId
          };

          if (existingRow) {
            await existingRow.update(payload, { transaction });
            retainedComponentIds.add(Number(existingRow.id));
          } else {
            const created = await db.BOMComponent.create(
              {
                ...payload,
                created_by: userId
              },
              { transaction }
            );
            retainedComponentIds.add(Number(created.id));
          }
        }
      }

      // Instead of hard delete (FK failure in live), softly inactivate removed components.
      for (const existingRow of existingComponents) {
        if (!retainedComponentIds.has(Number(existingRow.id))) {
          await existingRow.update(
            { status: 'INACTIVE', updated_by: userId },
            { transaction }
          );
        }
      }
    }

    await transaction.commit();

    const updatedBOM = await db.BOMHeader.findByPk(id, {
      include: [
        { model: db.Company, as: 'company', attributes: ['id', 'code', 'name'] },
        { model: db.Plant, as: 'plant', attributes: ['id', 'code', 'name'] },
        { model: db.BOMHeader, as: 'parentBOM', attributes: ['id', 'bom_number', 'alternative'], required: false },
        { model: db.Material, as: 'material', attributes: ['id', 'material_number', 'description'] },
        { model: db.UOM, as: 'baseUOM', attributes: ['id', 'uom_code', 'uom_name'] },
        {
          model: db.BOMComponent,
          as: 'components',
          where: { status: 'ACTIVE' },
          required: false,
          include: [
            { model: db.Material, as: 'material', attributes: ['id', 'material_number', 'description', 'unit_cost', 'last_purchase_rate'] },
            { model: db.UOM, as: 'componentUOM', attributes: ['id', 'uom_code', 'uom_name'] },
          ],
          order: [['item_number', 'ASC']],
        },
      ],
    });

    sendResponse(res, 200, true, 'BOM Header updated successfully', updatedBOM);
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
});

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

  const bomHeader = await db.BOMHeader.findByPk(id);
  if (!bomHeader) {
    return sendError(res, 'BOM Header not found', 404);
  }

  if (bomHeader.status === 'LOCKED') {
    return sendError(res, 'Locked BOM Header cannot be deleted', 400);
  }

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

  try {
    bomHeader.status = 'INACTIVE';
    bomHeader.updated_by = userId;
    await bomHeader.save({ transaction });

    await transaction.commit();

    sendResponse(res, 200, true, 'BOM Header deleted successfully', null);
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
});

/**
 * Generate BOM Header PDF (assembly + sub-assembly)
 * Columns: S.No, Description, Drawing No, Qty, UOM, Weight, Height, Length, Width, Thickness, Material Grade
 */
exports.generatePDF = asyncHandler(async (req, res) => {
  const { id } = req.params;

  const bomHeader = await db.BOMHeader.findByPk(id, {
    include: [
      { model: db.Company, as: 'company', attributes: ['id', 'code', 'name'] },
      { model: db.Plant, as: 'plant', attributes: ['id', 'code', 'name'] },
      { model: db.Material, as: 'material', attributes: ['id', 'material_number', 'description', 'drawing_number'] },
    ],
  });

  if (!bomHeader) {
    return sendError(res, 'BOM Header not found', 404);
  }

  const collectRows = async (bomId, level = 0, lineage = new Set()) => {
    // Prevent only circular path loops; allow same sub-BOM under different branches.
    if (lineage.has(Number(bomId))) return [];
    const nextLineage = new Set(lineage);
    nextLineage.add(Number(bomId));

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

    const rows = [];
    for (const comp of components) {
      const material = comp.material;
      if (!material) continue;
      const supplierRows = Array.isArray(material.suppliers) ? material.suppliers : [];
      const preferredSupplier = supplierRows.find((supplier) => supplier?.is_preferred && supplier?.vendor?.bp_number);
      const fallbackSupplier = supplierRows.find((supplier) => supplier?.vendor?.bp_number);
      const vendorCode = preferredSupplier?.vendor?.bp_number || fallbackSupplier?.vendor?.bp_number || '-';

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

      const childBOMs = await db.BOMHeader.findAll({
        where: {
          material_id: material.id,
          company_id: bomHeader.company_id,
          ...(bomHeader.plant_id ? { plant_id: bomHeader.plant_id } : {}),
          status: 'ACTIVE',
          id: { [Op.ne]: bomId },
        },
        attributes: ['id'],
        order: [['id', 'DESC']],
      });

      for (const childBOM of childBOMs) {
        const nested = await collectRows(childBOM.id, level + 1, nextLineage);
        rows.push(...nested);
      }
    }
    return rows;
  };

  const rows = await collectRows(bomHeader.id, 0);

  const doc = new PDFDocument({ size: 'A4', margin: 36 });
  res.setHeader('Content-Type', 'application/pdf');
  res.setHeader('Content-Disposition', `attachment; filename="BOM_${bomHeader.bom_number || id}.pdf"`);
  doc.pipe(res);

  const margin = 36;
  const contentWidth = doc.page.width - margin * 2;
  const bottomY = () => doc.page.height - 40;
  let currentPage = 1;

  // A4 portrait content width ~= 523 with 36 margins. Keep exact fit for boxed grid.
  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) => {
    // Keep footer inside printable area; below bottom margin causes auto blank pages.
    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 = bomHeader.bom_number || '-';
  const productNameValue = `${bomHeader.material?.material_number || '-'} - ${bomHeader.material?.description || '-'}`;
  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);
  doc.font('Helvetica');
  y += 6;

  const drawCell = (x, cellY, w, h, text, opts = {}) => {
    const {
      align = 'left',
      bold = false,
      fontSize = 8,
      lineBreak = false,
      paddingX = 3,
      paddingY = 3,
      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);
      doc.text(printable, x + paddingX, cellY + paddingY, {
        width: textWidth,
        height: textHeight,
        align,
        lineBreak,
        lineGap,
      });
      return;
    }

    // Manual bounded wrapping avoids PDFKit auto-pagination creating blank pages.
    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;
        continue;
      }
      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 lineHeightPx = doc.currentLineHeight();
    const step = lineHeightPx + lineGap;
    const maxLines = Math.max(1, Math.floor((textHeight + lineGap) / step));
    const renderLines = lines.slice(0, maxLines);

    renderLines.forEach((line, idx) => {
      const lineY = cellY + paddingY + (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 headerH = 18;
    drawCell(x, y, cols.sno, headerH, 'S.No', { bold: true, fontSize: 8.5, align: 'left' }); x += cols.sno;
    drawCell(x, y, cols.vendor, headerH, 'Vendor Code', { bold: true, fontSize: 8.5, align: 'left' }); x += cols.vendor;
    drawCell(x, y, cols.drawing, headerH, 'Drawing No', { bold: true, fontSize: 8.5, align: 'left' }); x += cols.drawing;
    drawCell(x, y, cols.partName, headerH, 'Part Name', { bold: true, fontSize: 8.5, align: 'left' }); x += cols.partName;
    drawCell(x, y, cols.description, headerH, 'Description', { bold: true, fontSize: 8.5, align: 'left' }); x += cols.description;
    drawCell(x, y, cols.qty, headerH, 'Qty', { bold: true, fontSize: 8.5, align: 'right' }); x += cols.qty;
    drawCell(x, y, cols.uom, headerH, 'UOM', { bold: true, fontSize: 8.5, align: 'center' }); x += cols.uom;
    y += headerH;
  };

  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((row) => {
    groupedRows[getTypeBucket(row)].push(row);
  });

  const drawSectionHeader = (title) => {
    const sectionH = 16;
    if (y + sectionH > bottomY()) {
      drawPageNumber(currentPage);
      doc.addPage();
      currentPage += 1;
      y = 42;
      drawHeader();
    }
    drawCell(tableX, y, tableWidth, sectionH, title, {
      bold: true,
      fontSize: 8.5,
      align: 'left',
      noWrap: true,
      paddingY: 4,
    });
    y += sectionH;
  };

  drawHeader();

  let serialNo = 1;
  bucketOrder.forEach((bucketKey) => {
    const bucketRows = groupedRows[bucketKey];
    if (!bucketRows || bucketRows.length === 0) return;
    drawSectionHeader(bucketTitles[bucketKey]);

    bucketRows.forEach((row) => {
    doc.font('Helvetica').fontSize(7.2);
    const descriptionLine = getDescriptionLine(row);
    const drawingTextH = doc.heightOfString(row.drawing_no || '-', {
      width: cols.drawing - 6,
      align: 'left',
    });
    const partNameTextH = doc.heightOfString(row.description || '-', {
      width: cols.partName - 6,
      align: 'left',
      lineGap: 1,
    });
    const descriptionTextH = doc.heightOfString(descriptionLine, {
      width: cols.description - 6,
      align: 'left',
      lineGap: 1,
    });
    const rowH = Math.min(84, Math.max(44, Math.ceil(drawingTextH + 12), Math.ceil(partNameTextH + 12), Math.ceil(descriptionTextH + 12)));

    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), { align: 'left', bold: false, paddingY: 8 }); x += cols.sno;
    drawCell(x, y, cols.vendor, rowH, row.vendor_code || '-', {
      align: 'left',
      bold: false,
      paddingY: 6,
      noWrap: false,
      fontSize: 7.2,
      lineGap: 1,
    });
    x += cols.vendor;
    drawCell(x, y, cols.drawing, rowH, row.drawing_no || '-', {
      align: 'left',
      bold: false,
      paddingY: 6,
      noWrap: false,
      fontSize: 7.2,
      lineGap: 1,
    });
    x += cols.drawing;
    drawCell(x, y, cols.partName, rowH, row.description || '-', {
      align: 'left',
      bold: false,
      paddingY: 6,
      noWrap: false,
      fontSize: 7.2,
      lineGap: 1,
    });
    x += cols.partName;
    drawCell(x, y, cols.description, rowH, descriptionLine, {
      align: 'left',
      bold: false,
      paddingY: 6,
      noWrap: false,
      fontSize: 7.2,
      lineGap: 1,
    });
    x += cols.description;

    drawCell(x, y, cols.qty, rowH, fmt(row.qty), { align: 'right', bold: false, paddingY: 8 }); x += cols.qty;
    drawCell(x, y, cols.uom, rowH, row.uom || '-', { align: 'center', bold: false, paddingY: 8 }); x += cols.uom;

    y += rowH;
    serialNo += 1;
    });
  });
  drawPageNumber(currentPage);

  doc.end();
});

