import { BadRequestException, Injectable, NotFoundException } from '@nestjs/common';
import { BomHeader, Prisma, SfgItem } from '../generated/prisma/index';
import * as XLSX from 'xlsx';
import { resolveProductImageUrl } from '../common/file-manager-image.util';
import { exportRows } from '../common/tabular-file.util';
import { PrismaService } from '../prisma/prisma.service';
import { SfgItemWriteDto, SfgQuery, UploadedImportFile, WorkbookImportDto } from './sfg-bom.dto';
import {
  clean,
  detectFinishVariant,
  inferInch,
  normalizeLookupKey,
  parseHolderSummary,
  parseNumber,
  parseWheelSummary,
  readWorkbookFromContent,
  round,
  safeCode,
} from './sfg-bom-costing.util';

const itemStatuses = ['Active', 'Inactive'];
const sfgTypes = ['HOLDER', 'WHEEL'];
const bomTypes = ['FG_BOM', 'SFG_SUB_PARTS'];
const processTypes = ['Holder Process', 'Wheel Process'];

type CostSummary = {
  materialCost: number;
  machineCost: number;
  coatingCost: number;
  totalCost: number;
};

type ResolvedBomItem = {
  childCode: string;
  childName: string;
  childItemType: string;
  quantity: number;
  branchType: string;
  finishVariant: string;
  plateCodes: string[];
  materialCost: number;
  machineCost: number;
  coatingCost: number;
  totalCost: number;
  detailLines: any[];
  nestedBom: ResolvedBomHeader | null;
};

type ResolvedBomHeader = {
  id: string;
  bomType: string;
  parentCode: string;
  parentName: string;
  parentItemType: string;
  items: ResolvedBomItem[];
  summary: CostSummary;
};

type CostingMaps = {
  headersByParentCode: Map<string, Array<BomHeader & { items: any[] }>>;
  costByItemCode: Map<string, any[]>;
  costByLookupKey: Map<string, any[]>;
};

const finalProductCostSelect: Prisma.SetProductSelect = {
  id: true,
  productCode: true,
  productName: true,
  wheelName: true,
  productInch: true,
  holderCode: true,
  plateCode: true,
  wheelCode: true,
  finishType: true,
  finishLabel: true,
};

@Injectable()
export class SfgBomService {
  constructor(private readonly prisma: PrismaService) {}

  async options() {
    const [sfgItems, machines, bomParents, sfgInchRows, finalProductInchRows] = await Promise.all([
      this.prisma.sfgItem.findMany({ orderBy: { itemName: 'asc' }, take: 600 }),
      this.prisma.machinery.findMany({ orderBy: { machineryName: 'asc' }, take: 600 }),
      this.prisma.bomHeader.findMany({ orderBy: { parentCode: 'asc' }, take: 600 }),
      this.prisma.sfgItem.findMany({
        where: { inch: { not: null } },
        distinct: ['inch'],
        select: { inch: true },
        orderBy: { inch: 'asc' },
      }),
      this.prisma.setProduct.findMany({
        distinct: ['productInch'],
        select: { productInch: true },
        orderBy: { productInch: 'asc' },
      }),
    ]);

    return {
      sfgTypes,
      bomTypes,
      processTypes,
      statuses: itemStatuses,
      inches: sfgInchRows.map((item) => clean(item.inch)).filter(Boolean).sort(compareInchText),
      finalProductInches: finalProductInchRows
        .map((item) => formatInchValue(item.productInch))
        .filter(Boolean)
        .sort(compareInchText),
      sfgItems,
      machines,
      fgProducts: [],
      bomParents,
    };
  }

  async listItems(query: SfgQuery) {
    const page = positiveInt(query.page, 1);
    const pageSize = Math.min(positiveInt(query.pageSize, 10), 100);
    const where = this.itemWhere(query);
    const [total, items] = await Promise.all([
      this.prisma.sfgItem.count({ where }),
      this.prisma.sfgItem.findMany({ where, orderBy: { itemCode: 'asc' }, skip: (page - 1) * pageSize, take: pageSize }),
    ]);
    return { items, meta: meta(total, page, pageSize), filterOptions: await this.options() };
  }

  async getItem(id: string) {
    const item = await this.prisma.sfgItem.findUnique({ where: { id } });
    if (!item) throw new NotFoundException('SFG item not found');
    const lookupKey = normalizeLookupKey(item.itemName);
    const [processes, parentBoms, childBoms, costRecords] = await Promise.all([
      this.prisma.sfgItemProcess.findMany({ where: { itemCode: item.itemCode }, orderBy: { processCode: 'asc' } }),
      this.prisma.bomHeader.findMany({ where: { parentCode: item.itemCode }, include: { items: true } }),
      this.prisma.bomItem.findMany({ where: { childCode: item.itemCode }, include: { bom: true } }),
      (this.prisma as any).sfgCostingRecord.findMany({
        where: { OR: [{ itemCode: item.itemCode }, { lookupKey }] },
        include: { details: { orderBy: { sequence: 'asc' } } },
        orderBy: [{ sourceKind: 'asc' }, { finishVariant: 'asc' }],
      }),
    ]);
    return { ...item, processes, parentBoms, childBoms, costRecords };
  }

  async createItem(dto: SfgItemWriteDto) {
    return this.prisma.sfgItem.create({ data: this.prepareItem(dto) });
  }

  async updateItem(id: string, dto: SfgItemWriteDto) {
    await this.getItem(id);
    return this.prisma.sfgItem.update({ where: { id }, data: this.prepareItem(dto) as Prisma.SfgItemUpdateInput });
  }

  async deleteItem(id: string) {
    await this.getItem(id);
    await this.prisma.sfgItem.delete({ where: { id } });
    return { deleted: true };
  }

  async listBoms(query: SfgQuery) {
    if (clean(query.bomType).toUpperCase() === 'FG_BOM') {
      return this.listFinalProductBoms(query);
    }

    const page = positiveInt(query.page, 1);
    const pageSize = Math.min(positiveInt(query.pageSize, 10), 100);
    const where = await this.bomWhere(query);
    const [total, headers, maps] = await Promise.all([
      this.prisma.bomHeader.count({ where }),
      this.prisma.bomHeader.findMany({
        where,
        include: { items: true },
        orderBy: [{ bomType: 'asc' }, { parentCode: 'asc' }],
        skip: (page - 1) * pageSize,
        take: pageSize,
      }),
      this.buildResolutionMaps(),
    ]);

    const items = headers.map((header) => {
      const resolved = this.resolveBomHeader(header, maps);
      return {
        ...header,
        itemCount: header.items.length,
        materialCost: resolved.summary.materialCost,
        machineCost: resolved.summary.machineCost,
        coatingCost: resolved.summary.coatingCost,
        totalCost: resolved.summary.totalCost,
      };
    });

    return { items, meta: meta(total, page, pageSize), filterOptions: await this.options() };
  }

  async getBom(id: string) {
    const finalProductCode = syntheticFinalProductCode(id);
    if (finalProductCode) {
      return this.buildFinalProductBomByCode(finalProductCode);
    }

    const bom = await this.prisma.bomHeader.findUnique({ where: { id }, include: { items: { orderBy: { childCode: 'asc' } } } });
    if (!bom) throw new NotFoundException('BOM not found');
    const maps = await this.buildResolutionMaps();
    const resolved = this.resolveBomHeader(bom, maps);
    const finalProductCosting =
      bom.bomType === 'FG_BOM' || bom.parentItemType === 'FG_PRODUCT'
        ? await this.buildFinalProductCostByCode(bom.parentCode)
        : null;
    return {
      ...bom,
      summary: resolved.summary,
      items: resolved.items,
      extendedRows: this.flattenResolvedItems(resolved.items),
      finalProductCosting,
    };
  }

  async listProcesses(query: SfgQuery) {
    const page = positiveInt(query.page, 1);
    const pageSize = Math.min(positiveInt(query.pageSize, 10), 100);
    const where = this.processWhere(query);
    const [total, items] = await Promise.all([
      this.prisma.sfgItemProcess.count({ where }),
      this.prisma.sfgItemProcess.findMany({ where, orderBy: { processCode: 'asc' }, skip: (page - 1) * pageSize, take: pageSize }),
    ]);
    return { items, meta: meta(total, page, pageSize), filterOptions: await this.options() };
  }

  async finalProductOptions(query: SfgQuery) {
    const page = positiveInt(query.page, 1);
    const pageSize = Math.min(positiveInt(query.pageSize, 50), 100);
    const where = this.finalProductWhere(query);
    const [total, items, inches] = await Promise.all([
      this.prisma.setProduct.count({ where }),
      this.prisma.setProduct.findMany({
        where,
        select: finalProductCostSelect,
        orderBy: { productCode: 'asc' },
        skip: (page - 1) * pageSize,
        take: pageSize,
      }),
      this.listFinalProductInches(),
    ]);

    return {
      items: items.map((item) => ({
        ...item,
        image: resolveProductImageUrl(item.productCode, item.productInch),
      })),
      meta: meta(total, page, pageSize),
      inches,
    };
  }

  async finalCost(query: SfgQuery) {
    const code = clean(query.code);
    const wantsPagedList = clean(query.page) || clean(query.pageSize) || clean(query.search) || clean(query.inch) || !code;
    if (wantsPagedList) {
      return this.listFinalProductCosts(query);
    }

    const product = await this.findFinalProductForCosting(code);
    if (!product) throw new NotFoundException('Final product was not found.');
    return this.buildFinalProductCost(product);
  }

  private async listFinalProductCosts(query: SfgQuery) {
    const page = positiveInt(query.page, 1);
    const pageSize = Math.min(positiveInt(query.pageSize, 10), 100);
    const where = this.finalProductWhere(query);
    const [total, products, inches] = await Promise.all([
      this.prisma.setProduct.count({ where }),
      this.prisma.setProduct.findMany({
        where,
        select: finalProductCostSelect,
        orderBy: { productCode: 'asc' },
        skip: (page - 1) * pageSize,
        take: pageSize,
      }),
      this.listFinalProductInches(),
    ]);
    const items = await Promise.all(products.map((product) => this.buildFinalProductCost(product)));
    return { items, meta: meta(total, page, pageSize), inches };
  }

  private async listFinalProductBoms(query: SfgQuery) {
    const page = positiveInt(query.page, 1);
    const pageSize = Math.min(positiveInt(query.pageSize, 10), 100);
    const where = this.finalProductWhere(query);
    const [total, products] = await Promise.all([
      this.prisma.setProduct.count({ where }),
      this.prisma.setProduct.findMany({
        where,
        select: finalProductCostSelect,
        orderBy: { productCode: 'asc' },
        skip: (page - 1) * pageSize,
        take: pageSize,
      }),
    ]);

    const costs = await Promise.all(products.map((product) => this.buildFinalProductCost(product)));
    const items = costs.map((cost, index) => this.mapFinalProductCostToBomListItem(products[index], cost));
    return { items, meta: meta(total, page, pageSize), filterOptions: await this.options() };
  }

  private async buildFinalProductBomByCode(code: string) {
    const product = await this.findFinalProductForCosting(code);
    if (!product) throw new NotFoundException('Final product was not found.');
    const finalProductCosting = await this.buildFinalProductCost(product);
    const summary = finalCostSummary(finalProductCosting);

    return {
      id: syntheticFinalProductBomId(product.productCode),
      bomType: 'FG_BOM',
      parentCode: product.productCode,
      parentName: finalProductCosting.fgName,
      parentItemType: 'FG_PRODUCT',
      parentSfgItemId: null,
      parentSetProductId: product.id || null,
      sourceSheet: 'Final Product Costing',
      status: 'Active',
      image: finalProductCosting.image,
      itemCount: finalProductCosting.rows.length,
      summary,
      materialCost: summary.materialCost,
      machineCost: summary.machineCost,
      coatingCost: summary.coatingCost,
      totalCost: summary.totalCost,
      items: finalProductCosting.rows.map((row: any) => ({
        childCode: row.code,
        childName: row.name,
        childItemType: `SFG_${row.type}`,
        quantity: 1,
        branchType: row.type,
        finishVariant: row.finishVariant,
        plateCodes: row.plateCodes,
        materialCost: row.materialCost,
        machineCost: row.machineCost,
        coatingCost: row.coatingCost,
        totalCost: row.totalCost,
        detailLines: row.detailLines,
        nestedBom: null,
      })),
      extendedRows: finalProductCosting.extendedRows,
      finalProductCosting,
    };
  }

  private mapFinalProductCostToBomListItem(product: any, cost: any) {
    const summary = finalCostSummary(cost);
    return {
      id: syntheticFinalProductBomId(product.productCode),
      bomType: 'FG_BOM',
      parentCode: product.productCode,
      parentName: cost.fgName,
      parentItemType: 'FG_PRODUCT',
      parentSfgItemId: null,
      parentSetProductId: product.id || null,
      sourceSheet: 'Final Product Costing',
      status: 'Active',
      itemCount: cost.rows.length,
      image: cost.image,
      holderCode: product.holderCode,
      plateCode: product.plateCode,
      wheelCode: product.wheelCode,
      inch: product.productInch,
      finishVariant: cost.finishVariant,
      materialCost: summary.materialCost,
      machineCost: summary.machineCost,
      coatingCost: summary.coatingCost,
      totalCost: summary.totalCost,
    };
  }

  private async buildFinalProductCostByCode(code: string) {
    const product = await this.findFinalProductForCosting(code);
    return product ? this.buildFinalProductCost(product) : null;
  }

  private async findFinalProductForCosting(code = '') {
    const normalizedCode = syntheticFinalProductCode(code) || clean(code);
    if (normalizedCode) {
      return this.prisma.setProduct.findFirst({
        where: { productCode: normalizedCode },
        select: finalProductCostSelect,
      });
    }

    return this.prisma.setProduct.findFirst({
      orderBy: { productCode: 'asc' },
      select: finalProductCostSelect,
    });
  }

  private async buildFinalProductCost(product: any) {
    const [holder, wheel] = await Promise.all([
      this.prisma.holderMaster.findFirst({
        where: { holderCode: product.holderCode },
        select: { holderCode: true, holderName: true },
      }),
      this.prisma.wheelMaster.findFirst({
        where: { wheelCode: product.wheelCode },
        select: { wheelCode: true, wheelName: true },
      }),
    ]);

    const finishVariant = detectFinishVariant(`${product.finishType} ${product.finishLabel}`);
    const [holderCandidates, wheelCandidates] = await Promise.all([
      this.findFinalCostCandidates('HOLDER_SUMMARY', product.holderCode, holder?.holderName || ''),
      this.findFinalCostCandidates(
        'WHEEL_SUMMARY',
        product.wheelCode,
        wheel?.wheelName || product.wheelName || '',
      ),
    ]);
    const holderRecord = this.pickFinalCostRecord(
      holderCandidates,
      finishVariant,
      product.plateCode,
    );
    const wheelRecord = this.pickFinalCostRecord(wheelCandidates, finishVariant);
    const holderRow = this.mapFinalCostRow(
      'HOLDER',
      product.holderCode,
      holder?.holderName || product.holderCode,
      holderRecord,
      product.plateCode,
    );
    const wheelRow = this.mapFinalCostRow(
      'WHEEL',
      product.wheelCode,
      wheel?.wheelName || product.wheelName || product.wheelCode,
      wheelRecord,
    );
    const rows = [holderRow, wheelRow];
    const extendedRows = rows.map((row) => ({
      path: `${product.productCode} > ${row.code}`,
      code: row.code,
      name: row.name,
      type: row.type,
      quantity: 1,
      finishVariant: row.finishVariant,
      plateCodes: row.plateCodes,
      materialCost: row.materialCost,
      machineCost: row.machineCost,
      coatingCost: row.coatingCost,
      totalCost: row.totalCost,
      detailLines: row.detailLines,
    }));
    const breakdown = {
      fgCode: product.productCode,
      fgName: product.productName || product.wheelName || product.productCode,
      inch: product.productInch,
      image: resolveProductImageUrl(product.productCode, product.productInch),
      finishVariant,
      holderCode: product.holderCode,
      plateCode: product.plateCode,
      wheelCode: product.wheelCode,
      holderSfgMaterialCost: 0,
      holderSfgMachineCost: 0,
      wheelSfgMaterialCost: 0,
      wheelSfgMachineCost: 0,
      subPartsMaterialCost: 0,
      coatingOtherCost: 0,
      totalSfgCost: 0,
      finalFgProductCost: 0,
      rows: [] as Record<string, unknown>[],
      extendedRows,
    };

    for (const item of rows) {
      const row = {
        code: item.code,
        name: item.name,
        type: item.type,
        materialCost: item.materialCost,
        machineCost: item.machineCost,
        coatingCost: item.coatingCost,
        totalCost: item.totalCost,
        hasChildren: false,
        detailLines: item.detailLines,
        finishVariant: item.finishVariant,
        plateCodes: item.plateCodes,
      };
      breakdown.rows.push(row);
      if (item.type === 'WHEEL') {
        breakdown.wheelSfgMaterialCost += item.materialCost;
        breakdown.wheelSfgMachineCost += item.machineCost;
        breakdown.coatingOtherCost += item.coatingCost;
      } else if (item.type === 'HOLDER') {
        breakdown.holderSfgMaterialCost += item.materialCost;
        breakdown.holderSfgMachineCost += item.machineCost;
        breakdown.coatingOtherCost += item.coatingCost;
      } else {
        breakdown.subPartsMaterialCost += item.totalCost;
      }
    }

    breakdown.holderSfgMaterialCost = round(breakdown.holderSfgMaterialCost);
    breakdown.holderSfgMachineCost = round(breakdown.holderSfgMachineCost);
    breakdown.wheelSfgMaterialCost = round(breakdown.wheelSfgMaterialCost);
    breakdown.wheelSfgMachineCost = round(breakdown.wheelSfgMachineCost);
    breakdown.subPartsMaterialCost = round(breakdown.subPartsMaterialCost);
    breakdown.coatingOtherCost = round(breakdown.coatingOtherCost);
    breakdown.totalSfgCost = round(
      breakdown.holderSfgMaterialCost +
        breakdown.holderSfgMachineCost +
        breakdown.wheelSfgMaterialCost +
        breakdown.wheelSfgMachineCost,
    );
    breakdown.finalFgProductCost = round(breakdown.totalSfgCost + breakdown.subPartsMaterialCost + breakdown.coatingOtherCost);
    return breakdown;
  }

  private async findFinalCostCandidates(
    sourceKind: 'HOLDER_SUMMARY' | 'WHEEL_SUMMARY',
    itemCode: string,
    itemName = '',
  ) {
    const conditions: any[] = [];
    const normalizedCode = clean(itemCode).toUpperCase();
    const lookupKey = normalizeLookupKey(itemName);

    if (normalizedCode) {
      conditions.push({ itemCode: normalizedCode });
    }

    if (lookupKey) {
      conditions.push({ lookupKey });
    }

    if (!conditions.length) {
      return [];
    }

    return (this.prisma as any).sfgCostingRecord.findMany({
      where: {
        sourceKind,
        OR: conditions,
      },
      include: { details: { orderBy: { sequence: 'asc' } } },
      orderBy: [{ finishVariant: 'asc' }, { updatedAt: 'desc' }],
    });
  }

  private pickFinalCostRecord(records: any[], finishVariant: string, plateCode = '') {
    if (!records.length) {
      return null;
    }

    const normalizedFinishVariant = clean(finishVariant).toUpperCase() || 'DEFAULT';
    const normalizedPlateCode = clean(plateCode).toUpperCase();

    const ranked = [...records].sort((left, right) => {
      const leftScore = this.scoreFinalCostRecord(
        left,
        normalizedFinishVariant,
        normalizedPlateCode,
      );
      const rightScore = this.scoreFinalCostRecord(
        right,
        normalizedFinishVariant,
        normalizedPlateCode,
      );

      if (leftScore !== rightScore) {
        return rightScore - leftScore;
      }

      return (right.details?.length || 0) - (left.details?.length || 0);
    });

    return ranked[0] || null;
  }

  private scoreFinalCostRecord(record: any, finishVariant: string, plateCode = '') {
    let score = 0;

    if (clean(record.finishVariant).toUpperCase() === finishVariant) {
      score += 4;
    } else if (clean(record.finishVariant).toUpperCase() === 'DEFAULT') {
      score += 2;
    }

    if (
      plateCode
      && Array.isArray(record.plateCodes)
      && record.plateCodes.some((value: string) => clean(value).toUpperCase() === plateCode)
    ) {
      score += 3;
    }

    if (record.itemCode) {
      score += 1;
    }

    return score;
  }

  private mapFinalCostRow(
    type: 'HOLDER' | 'WHEEL',
    code: string,
    name: string,
    record: any,
    plateCode = '',
  ) {
    const summary = record ? this.costRecordSummary(record) : emptySummary();

    return {
      code,
      name,
      type,
      finishVariant: record?.finishVariant || 'DEFAULT',
      plateCodes:
        record?.plateCodes?.length
          ? record.plateCodes
          : plateCode && type === 'HOLDER'
            ? [plateCode]
            : [],
      materialCost: summary.materialCost,
      machineCost: summary.machineCost,
      coatingCost: summary.coatingCost,
      totalCost: summary.totalCost,
      detailLines: record?.details || [],
    };
  }

  async previewWorkbook(dto: WorkbookImportDto) {
    const bundle = this.normalizeImportBundle(dto);
    const preview = {
      fileSheets: [] as string[],
      rows: {
        sfgItems: 0,
        fgBom: 0,
        itemProcesses: 0,
        subPartsBom: 0,
        holderSummary: 0,
        wheelSummary: 0,
      },
    };

    if (bundle.workbook) {
      const workbook = readWorkbookFromContent(bundle.workbook);
      const workbookPreview = this.readWorkbookCounts(workbook);
      preview.fileSheets = workbookPreview.fileSheets;
      preview.rows.sfgItems = workbookPreview.rows.sfgItems;
      preview.rows.fgBom = workbookPreview.rows.fgBom;
      preview.rows.itemProcesses = workbookPreview.rows.itemProcesses;
      preview.rows.subPartsBom = workbookPreview.rows.subPartsBom;
    }
    if (bundle.holderSummary) preview.rows.holderSummary = dedupeCostRecords(parseHolderSummary(bundle.holderSummary)).length;
    if (bundle.wheelSummary) preview.rows.wheelSummary = dedupeCostRecords(parseWheelSummary(bundle.wheelSummary)).length;
    return preview;
  }

  async importWorkbook(dto: WorkbookImportDto) {
    const bundle = this.normalizeImportBundle(dto);
    const result = {
      fileSheets: [] as string[],
      rows: {
        sfgItems: 0,
        fgBom: 0,
        itemProcesses: 0,
        subPartsBom: 0,
        holderSummary: 0,
        wheelSummary: 0,
      },
      imported: {
        sfgItems: 0,
        fgBomHeaders: 0,
        fgBomItems: 0,
        subPartBomHeaders: 0,
        subPartBomItems: 0,
        itemProcesses: 0,
        machinery: 0,
        holderCostRecords: 0,
        holderCostLines: 0,
        wheelCostRecords: 0,
        wheelCostLines: 0,
      },
      errors: [] as string[],
    };

    if (bundle.workbook) {
      const workbook = readWorkbookFromContent(bundle.workbook);
      const workbookPreview = this.readWorkbookCounts(workbook);
      result.fileSheets = workbookPreview.fileSheets;
      result.rows = { ...result.rows, ...workbookPreview.rows };
      await this.importSfgItems(workbook, result);
      await this.importBomSheet(workbook, 'BOM SFG AND FG', 'FG_BOM', result);
      await this.importBomSheet(workbook, 'SUB PARTS BOM', 'SFG_SUB_PARTS', result);
      await this.importItemProcesses(workbook, result);
    }

    if (bundle.holderSummary) {
      const records = dedupeCostRecords(parseHolderSummary(bundle.holderSummary));
      result.rows.holderSummary = records.length;
      await this.importCostingRecords(records, 'HOLDER_SUMMARY', result);
    }

    if (bundle.wheelSummary) {
      const records = dedupeCostRecords(parseWheelSummary(bundle.wheelSummary));
      result.rows.wheelSummary = records.length;
      await this.importCostingRecords(records, 'WHEEL_SUMMARY', result);
    }

    return result;
  }

  async exportItems(format = 'xlsx') {
    const items = await this.prisma.sfgItem.findMany({ orderBy: { itemCode: 'asc' } });
    return exportRows(
      'sfg_items',
      items.map((item) => ({
        'Item Code': item.itemCode,
        'Item Name': item.itemName,
        'Item Group': item.itemGroup || '',
        'SFG Type': item.sfgType,
        'Purchase UOM': item.purchaseUom || '',
        'Stock UOM': item.stockUom || '',
        'Min Stock Level': item.minStockLevel,
        Rate: item.rate,
        'Cost Price': item.costPrice,
        Status: item.status,
      })),
      format,
    );
  }

  async exportProcesses(format = 'xlsx') {
    const items = await this.prisma.sfgItemProcess.findMany({ orderBy: { processCode: 'asc' } });
    return exportRows(
      'sfg_item_processes',
      items.map((item) => ({
        'Process Code': item.processCode,
        'Item Code': item.itemCode,
        'Item Name': item.itemName,
        'Process Name': item.processName,
        'Process Type': item.processType,
        Inch: item.inch || '',
        Machine: item.machineName || '',
        'Process Time': item.processTime,
        'Hour Rate': item.hourRate,
        'Machine Cost': item.machineCost,
        Status: item.status,
      })),
      format,
    );
  }

  async exportBoms(format = 'xlsx', bomType = '') {
    const headers = await this.prisma.bomHeader.findMany({
      where: clean(bomType) ? { bomType: clean(bomType) } : undefined,
      include: { items: true },
      orderBy: [{ bomType: 'asc' }, { parentCode: 'asc' }],
    });
    const rows = headers.flatMap((header) =>
      header.items.map((item) => ({
        'BOM Type': header.bomType,
        'Parent Code': header.parentCode,
        'Parent Name': header.parentName,
        'Parent Type': header.parentItemType,
        'Child Code': item.childCode,
        'Child Name': item.childName,
        'Child Type': item.childItemType,
        Quantity: item.quantity,
        UOM: item.uom || '',
        'Wastage %': item.wastagePercent,
        'Cost Per Unit': item.costPerUnit,
        'Total Material Cost': item.totalMaterialCost,
      })),
    );
    return exportRows('product_bom', rows, format);
  }

  private prepareItem(dto: SfgItemWriteDto): Prisma.SfgItemCreateInput {
    const itemCode = clean(dto.itemCode).toUpperCase();
    const itemName = clean(dto.itemName);
    const errors: string[] = [];
    if (!itemCode) errors.push('Item code is required.');
    if (!itemName) errors.push('Item name is required.');
    if (errors.length) throw new BadRequestException(errors);
    return {
      itemCode,
      itemName,
      itemGroup: clean(dto.itemGroup) || null,
      sfgType: normalizeSfgType(dto.sfgType || dto.itemGroup || itemName),
      purchaseUom: clean(dto.purchaseUom) || null,
      stockUom: clean(dto.stockUom) || null,
      conversionRatio: parseNumber(dto.conversionRatio, 1),
      billingItem: parseBool(dto.billingItem),
      bom: parseBool(dto.bom),
      stockMaintain: parseBool(dto.stockMaintain),
      minStockLevel: parseNumber(dto.minStockLevel, 0),
      maxStockLevel: parseNumber(dto.maxStockLevel, 0),
      rate: parseNumber(dto.rate, 0),
      costPrice: parseNumber(dto.costPrice, 0),
      inch: clean(dto.inch) || inferInch(itemName),
      status: normalizeStatus(dto.status),
      sourceData: {},
    };
  }

  private readWorkbookCounts(workbook: any) {
    return {
      fileSheets: workbook.SheetNames,
      rows: {
        sfgItems: tableRows(workbook, 'SFG ITEM MASTER', ['ITEM CODE']).length,
        fgBom: tableRows(workbook, 'BOM SFG AND FG', ['FG Code', 'RM Code']).length,
        itemProcesses: tableRows(workbook, 'ITEM PROCESS', ['item code', 'process name']).length,
        subPartsBom: tableRows(workbook, 'SUB PARTS BOM', ['SFG Code', 'RM Code']).length,
      },
    };
  }

  private normalizeImportBundle(dto: WorkbookImportDto) {
    const workbook = dto.workbook || (dto.content ? { fileName: dto.fileName, content: dto.content } : undefined);
    const holderSummary = this.normalizeImportFile(dto.holderSummary || (dto.holderSummaryContent ? { fileName: dto.holderSummaryFileName, content: dto.holderSummaryContent } : undefined));
    const wheelSummary = this.normalizeImportFile(dto.wheelSummary || (dto.wheelSummaryContent ? { fileName: dto.wheelSummaryFileName, content: dto.wheelSummaryContent } : undefined));
    if (!workbook && !holderSummary && !wheelSummary) throw new BadRequestException(['Please upload at least one file.']);
    return { workbook: this.normalizeImportFile(workbook), holderSummary, wheelSummary };
  }

  private normalizeImportFile(file?: UploadedImportFile) {
    if (!file?.content) return undefined;
    return file;
  }

  private async importSfgItems(workbook: any, result: any) {
    const rows = tableRows(workbook, 'SFG ITEM MASTER', ['ITEM CODE', 'ITEM NAME']);
    for (const row of rows) {
      const itemCode = clean(row['ITEM CODE']).toUpperCase();
      const itemName = clean(row['ITEM NAME']);
      if (!itemCode || !itemName) continue;
      const data = {
        itemCode,
        itemName,
        itemGroup: clean(row['ITEM GROUP']) || null,
        sfgType: normalizeSfgType(`${row['ITEM GROUP']} ${itemCode} ${itemName}`),
        purchaseUom: clean(row['PURCHASE UOM']) || null,
        stockUom: clean(row['STOCK UOM']) || null,
        conversionRatio: parseNumber(row['CONVERSION RATIO'], 1),
        billingItem: parseBool(row['BILLING ITEM']),
        bom: parseBool(row['BOM']),
        stockMaintain: parseBool(row['STOCK MAINTAIN']),
        minStockLevel: parseNumber(row['MIN STOCK LEVEL'], 0),
        maxStockLevel: parseNumber(row['MAX STOCK LEVEL'], 0),
        rate: parseNumber(row['RATE'], 0),
        costPrice: parseNumber(row['COST PRICE'], 0),
        inch: inferInch(itemName),
        status: 'Active',
        sourceData: row as Prisma.InputJsonValue,
      };
      await this.prisma.sfgItem.upsert({ where: { itemCode }, update: data, create: data });
      result.imported.sfgItems += 1;
    }
  }

  private async importBomSheet(workbook: any, sheetName: string, bomType: string, result: any) {
    const rows = tableRows(workbook, sheetName, sheetName === 'SUB PARTS BOM' ? ['SFG Code', 'RM Code'] : ['FG Code', 'RM Code']);
    const [sfgItems, rawMaterials, setProducts] = await Promise.all([
      this.prisma.sfgItem.findMany(),
      this.prisma.rawMaterial.findMany(),
      this.prisma.setProduct.findMany(),
    ]);
    const sfgMap = new Map(sfgItems.map((item) => [item.itemCode.toUpperCase(), item]));
    const rawMap = new Map(rawMaterials.map((item) => [item.materialCode.toUpperCase(), item]));
    const setMap = new Map(setProducts.map((item) => [item.productCode.toUpperCase(), item]));
    const cleared = new Set<string>();
    let parentCode = '';
    let parentName = '';
    for (const row of rows) {
      parentCode = clean(row['FG Code'] || row['SFG Code']) || parentCode;
      parentName = clean(row['FG Name'] || row['SFG Name']) || parentName;
      const childCode = clean(row['RM Code']).toUpperCase();
      const childName = clean(row['RM Name']);
      if (!parentCode || !childCode) continue;
      const parentSfg = sfgMap.get(parentCode.toUpperCase());
      const parentSet = setMap.get(parentCode.toUpperCase());
      const header = await this.prisma.bomHeader.upsert({
        where: { bomType_parentCode: { bomType, parentCode } },
        update: {
          parentName,
          parentItemType: parentSfg ? `SFG_${parentSfg.sfgType}` : 'FG_PRODUCT',
          parentSfgItemId: parentSfg?.id || null,
          parentSetProductId: parentSet?.id || null,
          sourceSheet: sheetName,
          status: 'Active',
        },
        create: {
          bomType,
          parentCode,
          parentName,
          parentItemType: parentSfg ? `SFG_${parentSfg.sfgType}` : 'FG_PRODUCT',
          parentSfgItemId: parentSfg?.id || null,
          parentSetProductId: parentSet?.id || null,
          sourceSheet: sheetName,
          status: 'Active',
        },
      });
      if (!cleared.has(header.id)) {
        await this.prisma.bomItem.deleteMany({ where: { bomId: header.id } });
        cleared.add(header.id);
        if (bomType === 'FG_BOM') result.imported.fgBomHeaders += 1;
        else result.imported.subPartBomHeaders += 1;
      }
      const childSfg = sfgMap.get(childCode);
      const raw = rawMap.get(childCode);
      const quantity = parseNumber(row['NEW QTY'], Number.NaN) || parseNumber(row['Req Qty'], 0);
      const wastagePercent = parseNumber(row['Wastage %'] || row['WASTAGE %'], 0);
      const costPerUnit = childSfg ? childSfg.costPrice || childSfg.rate : raw?.costPerUnit || parseNumber(row['Cost Per Unit'], 0);
      const totalMaterialCost = round(quantity * costPerUnit * (1 + wastagePercent / 100));
      const itemData = {
        bomId: header.id,
        childCode,
        childName,
        childItemType: childSfg ? `SFG_${childSfg.sfgType}` : raw ? 'RAW_MATERIAL' : 'SUB_PART',
        childSfgItemId: childSfg?.id || null,
        rawMaterialId: raw?.id || null,
        quantity,
        newQuantity: clean(row['NEW QTY']) ? parseNumber(row['NEW QTY'], 0) : null,
        uom: raw?.uomId || null,
        wastagePercent,
        costPerUnit,
        totalMaterialCost,
        sourceSheet: sheetName,
        sourceData: row as Prisma.InputJsonValue,
      };
      await this.prisma.bomItem.upsert({
        where: { bomId_childCode: { bomId: header.id, childCode } },
        update: {
          childName,
          childItemType: itemData.childItemType,
          childSfgItemId: itemData.childSfgItemId,
          rawMaterialId: itemData.rawMaterialId,
          quantity: { increment: quantity },
          totalMaterialCost: { increment: totalMaterialCost },
          wastagePercent,
          costPerUnit,
          sourceData: row as Prisma.InputJsonValue,
        },
        create: itemData,
      });
      if (bomType === 'FG_BOM') result.imported.fgBomItems += 1;
      else result.imported.subPartBomItems += 1;
    }
  }

  private async importItemProcesses(workbook: any, result: any) {
    const rows = tableRows(workbook, 'ITEM PROCESS', ['item code', 'process name']);
    const sfgItems = await this.prisma.sfgItem.findMany();
    const sfgMap = new Map(sfgItems.map((item) => [item.itemCode.toUpperCase(), item]));
    let itemCode = '';
    let itemName = '';
    const sequenceByItem = new Map<string, number>();
    for (const row of rows) {
      itemCode = clean(row['item code']).toUpperCase() || itemCode;
      itemName = clean(row['item name']) || itemName;
      const processName = clean(row['process name']);
      if (!itemCode || !processName) continue;
      const sfg = sfgMap.get(itemCode);
      const machineName = clean(row['machines']);
      const machinery = machineName ? await this.findOrCreateMachine(machineName, result) : null;
      const processTime = parseNumber(row['process time'], 0);
      const hourRate = machinery?.hourRate || 0;
      const next = (sequenceByItem.get(itemCode) || 0) + 1;
      sequenceByItem.set(itemCode, next);
      const processCode = `IP-${safeCode(itemCode)}-${String(next).padStart(3, '0')}`;
      const processType = sfg?.sfgType === 'WHEEL' ? 'Wheel Process' : 'Holder Process';
      const data = {
        processCode,
        sfgItemId: sfg?.id || null,
        itemCode,
        itemName,
        processName,
        processType,
        inch: sfg?.inch || inferInch(itemName),
        setupTime: parseNumber(row['setup time'], 0),
        processTime,
        inspectionTime: parseNumber(row['insp time'], 0),
        inspectionRequired: parseBool(row['insp req']),
        toolsDie: clean(row['tools / die']) || null,
        machineryId: machinery?.id || null,
        machineName: machineName || null,
        description: clean(row['description']) || null,
        reqMaterialCode: clean(row['req material code']) || null,
        reqMaterialQty: parseNumber(row['req material qty'], 0),
        hourRate,
        machineCost: round(hourRate * processTime),
        status: 'Active',
        sourceData: row as Prisma.InputJsonValue,
      };
      const costing = await this.prisma.costingProcess.upsert({
        where: { processCode },
        update: {
          processName,
          processType,
          inch: data.inch,
          machineCost: data.machineCost,
          totalCost: data.machineCost,
          machineInput: { machineId: machinery?.id || '', processTime, hourRate } as Prisma.InputJsonValue,
        },
        create: {
          processCode,
          processName,
          processType,
          inch: data.inch,
          selectedItems: [{ code: itemCode, name: itemName }] as Prisma.InputJsonValue,
          materialInput: {} as Prisma.InputJsonValue,
          machineInput: { machineId: machinery?.id || '', processTime, hourRate } as Prisma.InputJsonValue,
          coatingInput: {} as Prisma.InputJsonValue,
          materialCost: 0,
          machineCost: data.machineCost,
          coatingCost: 0,
          totalCost: data.machineCost,
          status: 'Active',
          createdBy: 'Excel Import',
        },
      });
      await this.prisma.sfgItemProcess.upsert({
        where: { processCode },
        update: { ...data, costingProcessId: costing.id },
        create: { ...data, costingProcessId: costing.id },
      });
      result.imported.itemProcesses += 1;
    }
  }

  private async importCostingRecords(records: any[], sourceKind: 'HOLDER_SUMMARY' | 'WHEEL_SUMMARY', result: any) {
    const [sfgItems] = await Promise.all([this.prisma.sfgItem.findMany()]);
    const byCode = new Map(sfgItems.map((item) => [item.itemCode.toUpperCase(), item]));
    const byLookupKey = groupBy(sfgItems, (item) => normalizeLookupKey(item.itemName));
    await (this.prisma as any).sfgCostingRecord.deleteMany({ where: { sourceKind } });

    for (const record of records) {
      const matched = this.findMatchingSfgItem(record, byCode, byLookupKey);
      await (this.prisma as any).sfgCostingRecord.create({
        data: {
          sfgItemId: matched?.id || null,
          itemCode: record.itemCode || null,
          itemName: record.itemName,
          lookupKey: record.lookupKey,
          sfgType: record.sfgType,
          inch: record.inch || matched?.inch || inferInch(record.itemName),
          finishVariant: record.finishVariant,
          sourceKind,
          plateCodes: record.plateCodes,
          materialCost: record.materialCost,
          machineCost: record.machineCost,
          coatingCost: record.coatingCost,
          totalCost: record.totalCost,
          sourceFile: record.sourceFile,
          sourceData: record.sourceData as Prisma.InputJsonValue,
          details: {
            create: record.details.map((detail: any) => ({
              lineType: detail.lineType,
              sequence: detail.sequence,
              lineCode: detail.lineCode || null,
              lineName: detail.lineName,
              machineName: detail.machineName || null,
              quantity: detail.quantity ?? null,
              unitRate: detail.unitRate ?? null,
              length: detail.length ?? null,
              width: detail.width ?? null,
              thickness: detail.thickness ?? null,
              weightGram: detail.weightGram ?? null,
              cavityCount: detail.cavityCount ?? null,
              noOfSetsPerSheet: detail.noOfSetsPerSheet ?? null,
              noOfPcsPerStrip: detail.noOfPcsPerStrip ?? null,
              oneSheetWeight: detail.oneSheetWeight ?? null,
              stdPrice: detail.stdPrice ?? null,
              oneSheetRate: detail.oneSheetRate ?? null,
              piecesPerKgOrMeter: detail.piecesPerKgOrMeter ?? null,
              runningCostPerHour: detail.runningCostPerHour ?? null,
              labourCostPerHour: detail.labourCostPerHour ?? null,
              productionPerHour: detail.productionPerHour ?? null,
              productionCostPerPiece: detail.productionCostPerPiece ?? null,
              oneStripCuttingCharge: detail.oneStripCuttingCharge ?? null,
              onePieceCuttingCharge: detail.onePieceCuttingCharge ?? null,
              galvanizingPerKg: detail.galvanizingPerKg ?? null,
              galvanizingPerPiece: detail.galvanizingPerPiece ?? null,
              totalCost: detail.totalCost,
              sourceData: detail.sourceData as Prisma.InputJsonValue,
            })),
          },
        },
      });
      if (sourceKind === 'HOLDER_SUMMARY') {
        result.imported.holderCostRecords += 1;
        result.imported.holderCostLines += record.details.length;
      } else {
        result.imported.wheelCostRecords += 1;
        result.imported.wheelCostLines += record.details.length;
      }
    }
  }

  private findMatchingSfgItem(record: any, byCode: Map<string, SfgItem>, byLookupKey: Map<string, SfgItem[]>) {
    const code = clean(record.itemCode).toUpperCase();
    if (code && byCode.has(code)) return byCode.get(code);
    const lookupItems = byLookupKey.get(record.lookupKey) || [];
    return lookupItems.find((item) => item.sfgType === record.sfgType) || lookupItems[0] || null;
  }

  private async findOrCreateMachine(machineName: string, result: any) {
    const existing = await this.prisma.machinery.findFirst({
      where: { OR: [{ machineryName: { equals: machineName, mode: 'insensitive' } }, { name: { equals: machineName, mode: 'insensitive' } }] },
    });
    if (existing) return existing;
    const machineryNumber = await this.nextMachineryNumber(machineName);
    result.imported.machinery += 1;
    return this.prisma.machinery.create({
      data: {
        moduleType: machineName.toUpperCase().includes('PRESS') ? 'Press' : machineName.toUpperCase().includes('HYDRAULIC') ? 'Press' : 'General',
        machineryName: machineName,
        machineryNumber,
        eachHp: 0,
        hourRate: 0,
        name: machineName,
        status: 'Active',
      },
    });
  }

  private async nextMachineryNumber(machineName: string) {
    const base = `MCH-${safeCode(machineName).slice(0, 22) || 'MACHINE'}`;
    let value = base;
    let index = 1;
    while (await this.prisma.machinery.findUnique({ where: { machineryNumber: value } })) {
      value = `${base}-${index}`;
      index += 1;
    }
    return value;
  }

  private itemWhere(query: SfgQuery): Prisma.SfgItemWhereInput {
    const and: Prisma.SfgItemWhereInput[] = [];
    const search = clean(query.search);
    if (search) {
      and.push({
        OR: [
          { itemCode: { contains: search, mode: 'insensitive' } },
          { itemName: { contains: search, mode: 'insensitive' } },
          { itemGroup: { contains: search, mode: 'insensitive' } },
        ],
      });
    }
    if (clean(query.sfgType)) and.push({ sfgType: normalizeSfgType(query.sfgType) });
    if (clean(query.status)) and.push({ status: normalizeStatus(query.status) });
    if (clean(query.inch)) and.push({ inch: clean(query.inch) });
    return and.length ? { AND: and } : {};
  }

  private async bomWhere(query: SfgQuery): Promise<Prisma.BomHeaderWhereInput> {
    const and: Prisma.BomHeaderWhereInput[] = [];
    const search = clean(query.search);
    if (search) {
      and.push({
        OR: [
          { parentCode: { contains: search, mode: 'insensitive' } },
          { parentName: { contains: search, mode: 'insensitive' } },
          { items: { some: { childCode: { contains: search, mode: 'insensitive' } } } },
          { items: { some: { childName: { contains: search, mode: 'insensitive' } } } },
        ],
      });
    }
    if (clean(query.bomType)) and.push({ bomType: clean(query.bomType) });
    if (clean(query.parentItemType)) and.push({ parentItemType: clean(query.parentItemType) });
    if (clean(query.inch)) and.push(await this.bomInchWhere(query.inch || '', query.bomType || ''));
    return and.length ? { AND: and } : {};
  }

  private processWhere(query: SfgQuery): Prisma.SfgItemProcessWhereInput {
    const and: Prisma.SfgItemProcessWhereInput[] = [];
    const search = clean(query.search);
    if (search) {
      and.push({
        OR: [
          { processCode: { contains: search, mode: 'insensitive' } },
          { itemCode: { contains: search, mode: 'insensitive' } },
          { itemName: { contains: search, mode: 'insensitive' } },
          { processName: { contains: search, mode: 'insensitive' } },
          { machineName: { contains: search, mode: 'insensitive' } },
        ],
      });
    }
    if (clean(query.processType)) and.push({ processType: clean(query.processType) });
    if (clean(query.machineryId)) and.push({ machineryId: clean(query.machineryId) });
    if (clean(query.inch)) and.push({ inch: clean(query.inch) });
    return and.length ? { AND: and } : {};
  }

  private async bomInchWhere(inchText: string, bomType = ''): Promise<Prisma.BomHeaderWhereInput> {
    const normalizedBomType = clean(bomType).toUpperCase();
    const inch = clean(inchText);
    const productInch = parseNumber(inch, Number.NaN);
    const branches: Prisma.BomHeaderWhereInput[] = [];

    if (!normalizedBomType || normalizedBomType === 'FG_BOM') {
      const fgPrefix = formatFinalProductInchPrefix(productInch);
      if (fgPrefix) {
        branches.push({
          AND: [
            { bomType: 'FG_BOM' },
            { parentCode: { startsWith: fgPrefix } },
          ],
        });
      } else if (normalizedBomType === 'FG_BOM') {
        return { parentCode: { in: ['__NO_MATCH__'] } };
      }
    }

    if (!normalizedBomType || normalizedBomType === 'SFG_SUB_PARTS') {
      const items = await this.prisma.sfgItem.findMany({
        where: { inch },
        select: { id: true, itemCode: true },
      });
      if (items.length) {
        branches.push({
          AND: [
            { bomType: 'SFG_SUB_PARTS' },
            {
              OR: [
                { parentSfgItemId: { in: items.map((item) => item.id) } },
                { parentCode: { in: items.map((item) => item.itemCode) } },
              ],
            },
          ],
        });
      } else if (normalizedBomType === 'SFG_SUB_PARTS') {
        return { parentCode: { in: ['__NO_MATCH__'] } };
      }
    }

    if (!branches.length) {
      return { parentCode: { in: ['__NO_MATCH__'] } };
    }

    return branches.length === 1 ? branches[0] : { OR: branches };
  }

  private finalProductWhere(query: SfgQuery): Prisma.SetProductWhereInput {
    const and: Prisma.SetProductWhereInput[] = [];
    const search = clean(query.search || query.code);
    if (search) {
      and.push({
        OR: [
          { productCode: { contains: search, mode: 'insensitive' } },
          { productName: { contains: search, mode: 'insensitive' } },
          { wheelName: { contains: search, mode: 'insensitive' } },
          { holderCode: { contains: search, mode: 'insensitive' } },
          { plateCode: { contains: search, mode: 'insensitive' } },
          { wheelCode: { contains: search, mode: 'insensitive' } },
        ],
      });
    }

    const inchText = clean(query.inch);
    if (inchText) {
      const inch = parseNumber(inchText, Number.NaN);
      if (Number.isFinite(inch)) and.push({ productInch: inch });
    }

    return and.length ? { AND: and } : {};
  }

  private async listFinalProductInches() {
    const rows = await this.prisma.setProduct.findMany({
      distinct: ['productInch'],
      select: { productInch: true },
      orderBy: { productInch: 'asc' },
    });
    return rows.map((item) => formatInchValue(item.productInch)).filter(Boolean).sort(compareInchText);
  }

  private async buildResolutionMaps(): Promise<CostingMaps> {
    const [headers, costRecords] = await Promise.all([
      this.prisma.bomHeader.findMany({ include: { items: { orderBy: { childCode: 'asc' } } } }),
      (this.prisma as any).sfgCostingRecord.findMany({ include: { details: { orderBy: { sequence: 'asc' } } } }),
    ]);

    return {
      headersByParentCode: groupBy(headers, (header) => header.parentCode.toUpperCase()),
      costByItemCode: groupBy(costRecords.filter((record) => clean(record.itemCode)), (record) => clean(record.itemCode).toUpperCase()),
      costByLookupKey: groupBy(costRecords, (record) => record.lookupKey),
    };
  }

  private resolveBomHeader(header: BomHeader & { items: any[] }, maps: CostingMaps, branchType = 'UNKNOWN', visited = new Set<string>()): ResolvedBomHeader {
    const visitKey = `${header.bomType}:${header.parentCode}`;
    if (visited.has(visitKey)) {
      return {
        id: header.id,
        bomType: header.bomType,
        parentCode: header.parentCode,
        parentName: header.parentName,
        parentItemType: header.parentItemType,
        items: [],
        summary: emptySummary(),
      };
    }
    visited.add(visitKey);
    const items = header.items.map((item) => this.resolveBomItem(item, maps, branchType, visited));
    visited.delete(visitKey);
    return {
      id: header.id,
      bomType: header.bomType,
      parentCode: header.parentCode,
      parentName: header.parentName,
      parentItemType: header.parentItemType,
      items,
      summary: sumSummaries(items),
    };
  }

  private resolveBomItem(item: any, maps: CostingMaps, parentBranchType: string, visited: Set<string>): ResolvedBomItem {
    const quantity = item.quantity > 0 ? item.quantity : 1;
    const branchType = parentBranchType === 'UNKNOWN' ? branchFromText(`${item.childItemType} ${item.childCode} ${item.childName}`) : parentBranchType;
    const costing = this.findCostRecord(item.childCode, item.childName, branchType, maps);
    const nestedHeader = this.findNestedHeader(item.childCode, maps.headersByParentCode);
    if (nestedHeader) {
      const nestedBom = this.resolveBomHeader(nestedHeader, maps, branchType, visited);
      const directSummary = costing ? this.costRecordSummary(costing) : null;
      const summary = directSummary || scaleSummary(nestedBom.summary, quantity);
      const detailLines = costing ? this.scaleDetailLines(costing.details || [], 1) : [];
      return {
        childCode: item.childCode,
        childName: item.childName,
        childItemType: item.childItemType,
        quantity,
        branchType,
        finishVariant: costing?.finishVariant || detectFinishVariant(`${item.childCode} ${item.childName}`),
        plateCodes: costing?.plateCodes || [],
        materialCost: summary.materialCost,
        machineCost: summary.machineCost,
        coatingCost: summary.coatingCost,
        totalCost: summary.totalCost,
        detailLines,
        nestedBom,
      };
    }

    const directSummary = costing ? this.costRecordSummary(costing) : null;
    const unitSummary = directSummary || {
      materialCost: item.totalMaterialCost || 0,
      machineCost: 0,
      coatingCost: 0,
      totalCost: item.totalMaterialCost || 0,
    };
    const summary = directSummary ? unitSummary : scaleSummary(unitSummary, quantity);
    const detailLines = costing ? this.scaleDetailLines(costing.details || [], 1) : [];
    if (!detailLines.length && summary.materialCost > 0) {
      detailLines.push({
        lineType: 'MATERIAL',
        sequence: 1,
        lineCode: item.childCode,
        lineName: item.childName,
        totalCost: summary.materialCost,
        unitTotalCost: directSummary ? summary.materialCost : unitSummary.materialCost,
      });
    }
    return {
      childCode: item.childCode,
      childName: item.childName,
      childItemType: item.childItemType,
      quantity,
      branchType,
      finishVariant: costing?.finishVariant || detectFinishVariant(`${item.childCode} ${item.childName}`),
      plateCodes: costing?.plateCodes || [],
      materialCost: summary.materialCost,
      machineCost: summary.machineCost,
      coatingCost: summary.coatingCost,
      totalCost: summary.totalCost,
      detailLines,
      nestedBom: null,
    };
  }

  private findNestedHeader(childCode: string, headersByParentCode: Map<string, Array<BomHeader & { items: any[] }>>) {
    const headers = headersByParentCode.get(clean(childCode).toUpperCase()) || [];
    return headers.sort((left, right) => bomPriority(left.bomType) - bomPriority(right.bomType))[0] || null;
  }

  private findCostRecord(childCode: string, childName: string, branchType: string, maps: CostingMaps) {
    const sourceKind = branchType === 'WHEEL' ? 'WHEEL_SUMMARY' : 'HOLDER_SUMMARY';
    const variant = detectFinishVariant(`${childCode} ${childName}`);
    const lookupKey = normalizeLookupKey(childName);
    const candidates = [
      ...(maps.costByItemCode.get(clean(childCode).toUpperCase()) || []),
      ...(maps.costByLookupKey.get(lookupKey) || []),
    ].filter((record) => record.sourceKind === sourceKind);
    return (
      candidates.find((record) => record.finishVariant === variant) ||
      candidates.find((record) => record.finishVariant === 'DEFAULT') ||
      candidates[0] ||
      null
    );
  }

  private flattenResolvedItems(items: ResolvedBomItem[], path: string[] = []) {
    const rows: any[] = [];
    for (const item of items) {
      const nextPath = [...path, item.childCode];
      if (item.detailLines.length || (!item.nestedBom && item.totalCost > 0)) {
        rows.push({
          path: nextPath.join(' > '),
          code: item.childCode,
          name: item.childName,
          type: item.branchType,
          quantity: item.quantity,
          finishVariant: item.finishVariant,
          plateCodes: item.plateCodes,
          materialCost: item.materialCost,
          machineCost: item.machineCost,
          coatingCost: item.coatingCost,
          totalCost: item.totalCost,
          detailLines: item.detailLines,
        });
      }
      if (item.nestedBom) {
        rows.push(...this.flattenResolvedItems(item.nestedBom.items, nextPath));
        continue;
      }
    }
    return rows;
  }

  private costRecordSummary(costing: any): CostSummary {
    return {
      materialCost: round(costing?.materialCost || 0),
      machineCost: round(costing?.machineCost || 0),
      coatingCost: round(costing?.coatingCost || 0),
      totalCost: round(costing?.totalCost || 0),
    };
  }

  private scaleDetailLines(details: any[], multiplier: number) {
    return details.map((detail) => ({
      ...detail,
      unitTotalCost: detail.totalCost,
      totalCost: round((detail.totalCost || 0) * multiplier),
    }));
  }
}

function tableRows(workbook: any, sheetName: string, headerNeedles: string[]) {
  const sheet = workbook.Sheets[sheetName];
  if (!sheet) return [];
  const rows = XLSX.utils.sheet_to_json(sheet, { header: 1, defval: '' }) as unknown[][];
  const headerIndex = rows.findIndex((row) => headerNeedles.every((needle) => row.map((cell) => clean(cell).toLowerCase()).includes(needle.toLowerCase())));
  if (headerIndex < 0) return [];
  const headers = rows[headerIndex].map((cell: unknown) => clean(cell));
  return rows
    .slice(headerIndex + 1)
    .map((row) => {
      const item: Record<string, string> = {};
      headers.forEach((header: string, index: number) => {
        if (header) item[header] = clean(row[index]);
      });
      return item;
    })
    .filter((row) => Object.values(row).some(Boolean));
}

function normalizeSfgType(value: unknown) {
  const text = clean(value).toUpperCase();
  return text.includes('WHEEL') || text.startsWith('WH') ? 'WHEEL' : 'HOLDER';
}

function normalizeStatus(value: unknown) {
  return clean(value).toLowerCase() === 'inactive' ? 'Inactive' : 'Active';
}

function parseBool(value: unknown) {
  const text = clean(value).toLowerCase();
  return ['yes', 'y', 'true', '1', 'active'].includes(text);
}

function positiveInt(value: unknown, fallback: number) {
  const parsed = Number.parseInt(clean(value), 10);
  return Number.isFinite(parsed) && parsed > 0 ? parsed : fallback;
}

function meta(total: number, page: number, pageSize: number) {
  return { total, page, pageSize, totalPages: Math.max(1, Math.ceil(total / pageSize)) };
}

function groupBy<T>(items: T[], getKey: (item: T) => string) {
  const map = new Map<string, T[]>();
  for (const item of items) {
    const key = getKey(item);
    map.set(key, [...(map.get(key) || []), item]);
  }
  return map;
}

function emptySummary(): CostSummary {
  return { materialCost: 0, machineCost: 0, coatingCost: 0, totalCost: 0 };
}

function finalCostSummary(cost: any): CostSummary {
  return {
    materialCost: round((cost.holderSfgMaterialCost || 0) + (cost.wheelSfgMaterialCost || 0) + (cost.subPartsMaterialCost || 0)),
    machineCost: round((cost.holderSfgMachineCost || 0) + (cost.wheelSfgMachineCost || 0)),
    coatingCost: round(cost.coatingOtherCost || 0),
    totalCost: round(cost.finalFgProductCost || 0),
  };
}

function sumSummaries(items: Array<{ materialCost: number; machineCost: number; coatingCost: number; totalCost: number }>): CostSummary {
  return {
    materialCost: round(items.reduce((sum, item) => sum + item.materialCost, 0)),
    machineCost: round(items.reduce((sum, item) => sum + item.machineCost, 0)),
    coatingCost: round(items.reduce((sum, item) => sum + item.coatingCost, 0)),
    totalCost: round(items.reduce((sum, item) => sum + item.totalCost, 0)),
  };
}

function scaleSummary(summary: CostSummary, multiplier: number): CostSummary {
  return {
    materialCost: round(summary.materialCost * multiplier),
    machineCost: round(summary.machineCost * multiplier),
    coatingCost: round(summary.coatingCost * multiplier),
    totalCost: round(summary.totalCost * multiplier),
  };
}

function branchFromText(value: string) {
  const text = clean(value).toUpperCase();
  if (text.includes('WHEEL') || text.startsWith('WH')) return 'WHEEL';
  if (/(HOLDER|PLATE|BRAKE|RIVET|BOLT|NUT|GREASE|BALL|SLEEVE|ROD)/.test(text)) return 'HOLDER';
  return 'UNKNOWN';
}

function bomPriority(value: string) {
  return value === 'FG_BOM' ? 0 : 1;
}

function syntheticFinalProductBomId(code: string) {
  return `final-product:${clean(code)}`;
}

function syntheticFinalProductCode(idOrCode: string) {
  const text = clean(idOrCode);
  if (!text.startsWith('final-product:')) return '';
  return clean(text.slice('final-product:'.length));
}

function formatInchValue(value: number | string | null | undefined) {
  const numeric = Number(value);
  if (Number.isFinite(numeric)) {
    return Number.isInteger(numeric) ? String(numeric) : String(numeric);
  }
  return clean(value);
}

function compareInchText(left: string, right: string) {
  const leftNumeric = Number(left);
  const rightNumeric = Number(right);
  if (Number.isFinite(leftNumeric) && Number.isFinite(rightNumeric) && leftNumeric !== rightNumeric) {
    return leftNumeric - rightNumeric;
  }
  return left.localeCompare(right, undefined, { numeric: true, sensitivity: 'base' });
}

function formatFinalProductInchPrefix(value: number) {
  if (!Number.isFinite(value) || !Number.isInteger(value) || value < 0) {
    return '';
  }
  return String(value);
}

function dedupeCostRecords(records: any[]) {
  const map = new Map<string, any>();
  for (const record of records) {
    const key = `${record.sourceKind}::${record.lookupKey}::${record.finishVariant}`;
    const existing = map.get(key);
    if (!existing || (existing.details?.length || 0) < (record.details?.length || 0)) map.set(key, record);
  }
  return [...map.values()];
}