import * as XLSX from 'xlsx';
import { ExportFile } from '../common/tabular-file.util';
import {
  CalculatedCoatingRow,
  CalculatedMachineRow,
  CalculatedMaterialRow,
  CalculatedProcessRecord,
  ProcessCoatingRowInput,
  ProcessImportPreviewRow,
  ProcessMachineRowInput,
  ProcessMaterialRowInput,
  ProcessModuleKind,
  ProcessRecordWriteDto,
  ProcessSourceKind,
} from './process-shared.types';

const steelDensity = 7.8;
const holderHeaders = [
  'Id',
  'HOLDER NAME // HOLDER PROCESS',
  'MATERIAL  - code',
  'LENGTH // NUMBER',
  'WIDTH // DIA',
  'COMPONENTS Thickness',
  'No Of Strips',
  'No Of Pcs Per Strip',
  'No Of Sets Per Sheet',
  'One Sheet Weight',
  'PCS WEIGHT IN GRM',
  'NO OF PCS IN PER KG',
  'Std Price',
  'One Sheet Rate',
  'WEIGHT',
  'PCS IN ONE KG/ONE METER',
  'PCS IN ONE KG/ONE METER',
  'TOTAL',
  'PROCESS TOTAL',
  'TOTAL ALL',
  'Combination of plate ',
];

const wheelHeaders = [
  'code ',
  'WHEEL PROCESS',
  'MATERIAL ',
  'LENGTH // RAW MATERIAL UNIT COST',
  'WIDTH  // WEIGHT OF TREAD GRAMS',
  'COMPONENTS Thickness // NO OF CAVITY IN TOOL',
  'No Of Strips ',
  'No Of Pcs Per Strip',
  'One Sheet Weight',
  'One Sheet Rate',
  'TOTAL',
];

type WorkbookFile = {
  fileName: string;
  buffer: Buffer;
};

type MutableImportRecord = {
  rowNumber: number;
  payload: ProcessRecordWriteDto;
  inProcessSection: boolean;
  processSequence: number;
};

export function sourceKindFor(kind: ProcessModuleKind): ProcessSourceKind {
  return kind === 'holder' ? 'HOLDER_SUMMARY' : 'WHEEL_SUMMARY';
}

export function processTypeFor(kind: ProcessModuleKind) {
  return kind === 'holder' ? 'Holder Process' : 'Wheel Process';
}

export function clean(value: unknown) {
  return String(value ?? '').trim();
}

export function normalizeStatus(value: unknown, fallback = 'Active') {
  const status = clean(value).toLowerCase();
  if (status === 'inactive') return 'Inactive';
  if (status === 'draft') return 'Draft';
  return fallback;
}

export function parseNumber(value: unknown, fallback = 0) {
  if (value === null || value === undefined || value === '') return fallback;
  const parsed = Number.parseFloat(String(value).replace(/,/g, ''));
  return Number.isFinite(parsed) ? parsed : fallback;
}

export function optionalNumber(value: unknown) {
  const parsed = parseNumber(value, Number.NaN);
  return Number.isFinite(parsed) ? parsed : null;
}

export function round(value: number) {
  return Math.round((Number.isFinite(value) ? value : 0) * 1000000) / 1000000;
}

export function safeCode(value: unknown) {
  return clean(value).toUpperCase().replace(/[^A-Z0-9]+/g, '-').replace(/^-|-$/g, '');
}

export function stripQuotes(value: unknown) {
  return clean(value).replace(/^"|"$/g, '');
}

export function inferInch(value: unknown) {
  const match = clean(value).match(/(\d+(?:\.\d+)?)\s*INCH/i);
  return match ? `${match[1]} INCH` : '';
}

export function normalizeLookupKey(value: unknown) {
  return clean(value)
    .replace(/^"|"$/g, '')
    .replace(/\bCED COATING\b/gi, '')
    .replace(/\bPOWDER COAT(?:ING)?\b/gi, '')
    .replace(/\bEG COAT(?:ED|ING)?\b/gi, '')
    .replace(/\bZINC COAT(?:ED|ING)?\b/gi, '')
    .replace(/\bCED\b$/gi, '')
    .replace(/\bEG\b$/gi, '')
    .replace(/\bPC\b$/gi, '')
    .replace(/\s+/g, ' ')
    .trim()
    .toUpperCase();
}

export function detectFinishVariant(value: unknown, fallback = 'DEFAULT') {
  const text = clean(value).toUpperCase();
  if (text.includes('CED')) return 'CED';
  if (text.includes('POWDER') || /\bPC\b/.test(text)) return 'POWDER';
  if (text.includes('ZINC') || /\bEG\b/.test(text) || text.includes('GALV')) return 'EG';
  return fallback;
}

export function parsePlateCodes(value: unknown) {
  return stripQuotes(value)
    .split(',')
    .map((item) => item.trim())
    .filter(Boolean);
}

export function firstPositive(values: Array<number | null>) {
  return values.find((value) => Number.isFinite(value as number) && (value as number) > 0) ?? null;
}

export function classifyMaterialType(materialName: unknown, explicitType: unknown = '') {
  const text = `${explicitType} ${materialName}`.toUpperCase();
  if (text.includes('SHEET')) return 'SHEET';
  if (text.includes('SLEEVE')) return 'SLEEVE';
  if (text.includes('BRAKE')) return 'BRAKE';
  if (text.includes('ROD') || text.includes('RIVET')) return 'ROD';
  if (text.includes('GREASE')) return 'GREASE';
  if (text.includes('BALL') || text.includes('BEARING')) return 'BEARING';
  if (text.includes('BOLT') || text.includes('NUT') || text.includes('WASHER') || text.includes('PIN') || text.includes('RING') || text.includes('CLIP') || text.includes('SCREW')) return 'HARDWARE';
  if (text.includes('RUBBER') || text.includes('NYLON') || text.includes('TPU') || text.includes('PPCP') || text.includes('WHEEL')) return 'WHEEL';
  return 'RAW MATERIAL';
}

export function calculateMaterialRow(kind: ProcessModuleKind, row: ProcessMaterialRowInput): CalculatedMaterialRow {
  const materialName = stripQuotes(row.materialName || row.materialCode || 'Material');
  const materialType = classifyMaterialType(materialName, row.materialType);
  const quantity = parseNumber(row.quantity ?? row.length, 0);
  const length = parseNumber(row.length, 0);
  const width = parseNumber(row.width, 0);
  const thickness = parseNumber(row.thickness, 0);
  const noOfSetsPerSheet = parseNumber(row.noOfSetsPerSheet, 0);
  const oneSheetWeight = parseNumber(row.oneSheetWeight, 0);
  const weightGram = parseNumber(row.pcsWeightGram ?? row.weightGram, 0);
  const unitPrice = parseNumber(row.standardPrice, 0);
  const oneSheetRate = parseNumber(row.oneSheetRate, 0);
  const rawWeight = parseNumber(row.weight, 0);
  const piecesPerKgOrMeter = firstPositive([
    optionalNumber(row.piecesPerKgOrMeter),
    optionalNumber(row.noOfPcsPerKg),
  ]) || 0;
  const cavityCount = parseNumber(row.cavityCount, 0);
  const importedMaterialTotal = parseNumber(row.importedMaterialTotal ?? row.materialTotal, 0);
  let total = 0;

  if (kind === 'wheel') {
    total = unitPrice > 0 && weightGram > 0 ? (unitPrice * weightGram) / 1000 : importedMaterialTotal;
  } else if (materialType === 'SHEET') {
    const sheetRate = oneSheetWeight > 0 && unitPrice > 0 ? oneSheetWeight * unitPrice : oneSheetRate;
    total = noOfSetsPerSheet > 0 ? sheetRate / noOfSetsPerSheet : importedMaterialTotal || sheetRate;
  } else if (materialType === 'BRAKE') {
    total = length > 0 && width > 0 && thickness > 0 ? (length * width * thickness * steelDensity * unitPrice) / 1000000 : importedMaterialTotal;
  } else if (materialType === 'SLEEVE') {
    const outer = Math.PI * (thickness ** 2) / 4;
    const inner = Math.PI * (width ** 2) / 4;
    total = length > 0 && thickness > 0 ? Math.max(0, outer - inner) * length * steelDensity * unitPrice / 1000000 : importedMaterialTotal;
  } else if (materialType === 'ROD') {
    const area = Math.PI * (width ** 2) / 4;
    total = length > 0 && width > 0 ? area * length * steelDensity * unitPrice / 1000000 : importedMaterialTotal;
  } else if (materialType === 'GREASE') {
    total = unitPrice > 0 && (rawWeight > 0 || weightGram > 0) ? (unitPrice / 1000) * (rawWeight || weightGram) : importedMaterialTotal;
  } else if (materialType === 'WHEEL') {
    total = unitPrice > 0 && weightGram > 0 ? (unitPrice * weightGram) / 1000 : importedMaterialTotal;
  } else if (materialType === 'BEARING') {
    total = quantity > 0 && unitPrice > 0 ? quantity * unitPrice : importedMaterialTotal;
  } else if (materialType === 'HARDWARE') {
    if (quantity > 0 && unitPrice > 0 && piecesPerKgOrMeter > 0) total = (quantity * unitPrice) / piecesPerKgOrMeter;
    else if (weightGram > 0 && unitPrice > 0) total = (unitPrice * weightGram) / 1000;
    else if (importedMaterialTotal > 0) total = importedMaterialTotal;
    else total = quantity * unitPrice;
  } else {
    if (length > 0 && unitPrice > 0 && piecesPerKgOrMeter > 0) total = (length * unitPrice) / piecesPerKgOrMeter;
    else if (quantity > 0 && unitPrice > 0) total = quantity * unitPrice;
    else if (length > 0 && unitPrice > 0) total = length * unitPrice;
    else total = importedMaterialTotal;
  }

  return {
    ...row,
    lineCode: clean(row.lineCode || row.materialCode || safeCode(materialName) || 'MAT'),
    materialName,
    materialType,
    quantity: quantity || (length > 0 ? length : null),
    materialTotal: round(total),
  };
}

export function calculateMachineRow(row: ProcessMachineRowInput): CalculatedMachineRow {
  const processName = clean(row.processName);
  const quantity = parseNumber(row.quantity, 1) || 1;
  const explicitTotal = optionalNumber(row.total);
  const explicitPieceCost = optionalNumber(row.productionCostPerPiece);
  const runningCost = parseNumber(row.machineHourRate, 0);
  const labourCost = parseNumber(row.labourCostPerHour, 0);
  const productionPerHour = parseNumber(row.productionPerHour, 0);
  const productionCostPerPiece = explicitPieceCost && explicitPieceCost > 0
    ? explicitPieceCost
    : productionPerHour > 0
      ? (runningCost + labourCost) / productionPerHour
      : 0;
  const total = explicitTotal && explicitTotal > 0
    ? explicitTotal
    : productionCostPerPiece * quantity
      + parseNumber(row.oneStripCuttingCharge, 0)
      + parseNumber(row.onePieceCuttingCharge, 0) * quantity
      + parseNumber(row.galvanizingPerKg, 0)
      + parseNumber(row.galvanizingPerPiece, 0) * quantity;

  return {
    ...row,
    processName,
    lineCode: clean(row.lineCode || safeCode(processName) || 'PROC'),
    productionCostPerPiece: round(productionCostPerPiece),
    total: round(total),
  };
}

export function calculateCoatingRow(row: ProcessCoatingRowInput, materialRows: CalculatedMaterialRow[]): CalculatedCoatingRow {
  const coatingType = clean(row.coatingType || 'EG COATING').toUpperCase();
  const mode = clean(row.mode).toUpperCase() === 'PER PCS' ? 'Per PCS' : 'Per KG';
  const explicitTotal = optionalNumber(row.total);
  const unitPrice = parseNumber(row.unitPrice, 0);
  const quantity = parseNumber(row.quantity, 1) || 1;
  const weightGram = parseNumber(row.weightGram, 0) || materialRows.reduce((sum, item) => sum + parseNumber(item.pcsWeightGram ?? item.weightGram, 0), 0);
  const weightKg = parseNumber(row.weightKg, 0) || weightGram / 1000;
  const total = explicitTotal && explicitTotal > 0
    ? explicitTotal
    : mode === 'Per PCS'
      ? unitPrice * quantity
      : unitPrice * weightKg;

  return {
    ...row,
    coatingType,
    mode,
    total: round(total),
  };
}

export function calculateProcessRecord(kind: ProcessModuleKind, input: ProcessRecordWriteDto): CalculatedProcessRecord {
  const processCode = clean(input.processCode).toUpperCase();
  const processName = stripQuotes(input.processName || '');
  const processType = processTypeFor(kind);
  const finishVariant = detectFinishVariant(input.finishVariant || `${processCode} ${processName}`);
  const materialRows = (input.materialRows || []).map((row) => calculateMaterialRow(kind, row));
  const machineRows = (input.machineRows || []).map(calculateMachineRow);
  const coatingRows = (input.coatingRows || []).map((row) => calculateCoatingRow(row, materialRows));
  const materialTotal = round(materialRows.reduce((sum, row) => sum + row.materialTotal, 0));
  const processTotal = round(machineRows.reduce((sum, row) => sum + row.total, 0));
  const coatingTotal = round(coatingRows.reduce((sum, row) => sum + row.total, 0));

  return {
    kind,
    processCode,
    processName,
    processType,
    inch: clean(input.inch || inferInch(processName)),
    finishVariant,
    plateCodes: dedupeStrings(input.plateCodes || []),
    materialRows,
    machineRows,
    coatingRows,
    materialTotal,
    processTotal,
    coatingTotal,
    totalAll: round(materialTotal + processTotal + coatingTotal),
  };
}

export function validateProcessRecord(kind: ProcessModuleKind, input: ProcessRecordWriteDto) {
  const errors: string[] = [];
  if (!clean(input.processCode)) errors.push('Code is required.');
  if (!clean(input.processName)) errors.push('Process name is required.');
  if (kind === 'holder' && !(input.plateCodes || []).length) errors.push('Combination of plate is required for holder process.');
  if (!(input.materialRows || []).length) errors.push('Add at least one material row.');
  for (const [index, row] of (input.materialRows || []).entries()) {
    if (!clean(row.materialName || row.materialCode)) errors.push(`Material row ${index + 1}: material name is required.`);
  }
  for (const [index, row] of (input.machineRows || []).entries()) {
    if (!clean(row.processName)) errors.push(`Machine row ${index + 1}: process name is required.`);
  }
  for (const [index, row] of (input.coatingRows || []).entries()) {
    if (!clean(row.coatingType)) errors.push(`Coating row ${index + 1}: coating type is required.`);
  }
  return errors;
}

export function parseProcessImportPreview(kind: ProcessModuleKind, fileName: string, buffer: Buffer): ProcessImportPreviewRow[] {
  const matrix = readTabularMatrix({ fileName, buffer });
  const records = kind === 'holder' ? parseHolderMatrix(matrix, fileName) : parseWheelMatrix(matrix, fileName);
  return records.map((record) => {
    const errors = validateProcessRecord(kind, record.payload);
    return {
      rowNumber: record.rowNumber,
      code: clean(record.payload.processCode).toUpperCase(),
      name: clean(record.payload.processName),
      finishVariant: detectFinishVariant(record.payload.finishVariant || ''),
      plateCodes: record.payload.plateCodes || [],
      materialRows: (record.payload.materialRows || []).length,
      machineRows: (record.payload.machineRows || []).length,
      coatingRows: (record.payload.coatingRows || []).length,
      valid: errors.length === 0,
      errors,
      payload: record.payload,
    };
  });
}

export function exportProcessWorkbook(kind: ProcessModuleKind, records: ProcessRecordWriteDto[], format = 'xlsx'): ExportFile {
  const rows = kind === 'holder' ? buildHolderExportRows(records) : buildWheelExportRows(records);
  const workbook = XLSX.utils.book_new();
  const sheet = XLSX.utils.aoa_to_sheet(rows);
  XLSX.utils.book_append_sheet(workbook, sheet, kind === 'holder' ? 'Holder Process' : 'Wheel Process');
  const safeDate = new Date().toISOString().slice(0, 10);
  const safeName = `${kind}_process_${safeDate}`;
  if (format.toLowerCase() === 'csv') {
    return {
      fileName: `${safeName}.csv`,
      mime: 'text/csv',
      base64: Buffer.from(XLSX.utils.sheet_to_csv(sheet), 'utf8').toString('base64'),
    };
  }

  return {
    fileName: `${safeName}.xlsx`,
    mime: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
    base64: Buffer.from(XLSX.write(workbook, { bookType: 'xlsx', type: 'buffer' }) as Buffer).toString('base64'),
  };
}

export function holderProcessSample(format = 'xlsx') {
  return exportProcessWorkbook('holder', [
    {
      processCode: '1102.0101.0361 EG',
      processName: '2 INCH 37.5Y23.5H WOB HOLDER',
      inch: '2 INCH',
      plateCodes: ['P2001', 'P2002'],
      materialRows: [
        {
          materialName: 'CR SHEET 2 MM - R026',
          materialType: 'SHEET',
          length: 2500,
          width: 1250,
          thickness: 2,
          noOfStrips: '',
          noOfPcsPerStrip: 22,
          noOfSetsPerSheet: 506,
          oneSheetWeight: 49,
          pcsWeightGram: 96.8379,
          standardPrice: 75,
          oneSheetRate: 3920,
        },
      ],
      machineRows: [
        { processName: 'BLANKING', machineName: 'POWER PRESS 3', machineHourRate: 17, labourCostPerHour: 50, productionPerHour: 200, productionCostPerPiece: 0.334375, quantity: 1 },
      ],
      coatingRows: [{ coatingType: 'EG COATING', total: 1.936758 }],
    },
  ], format);
}

export function wheelProcessSample(format = 'xlsx') {
  return exportProcessWorkbook('wheel', [
    {
      processCode: 'WH2001',
      processName: '50DX18WX22H NYLON RP INSERT FOR PRESSED TREAD SLEEVE BEARING',
      materialRows: [
        { materialName: 'RP NYLON BLACK', standardPrice: 140, pcsWeightGram: 18, cavityCount: 8 },
        { materialName: 'BLACK RUBBER', standardPrice: 170, pcsWeightGram: 22, cavityCount: 16 },
      ],
      machineRows: [
        { processName: 'PLASTIC INJECTION MOULDING', machineName: 'AUTO INJECTION MOLDING MACHINE 150 TON', machineHourRate: 300, productionPerHour: 320, productionCostPerPiece: 0.9375, quantity: 1 },
        { processName: 'RUBBER MOULDING', machineHourRate: 5, productionPerHour: 50, productionCostPerPiece: 1.75, quantity: 1 },
      ],
    },
  ], format);
}

function readTabularMatrix(file: WorkbookFile) {
  const lowerName = clean(file.fileName).toLowerCase();
  const text = file.buffer.toString('utf8').replace(/^\uFEFF/, '');
  if (!lowerName.endsWith('.xls') && !lowerName.endsWith('.xlsx') && text.includes('\t')) {
    return text
      .split(/\r?\n/)
      .filter((line) => line.trim().length)
      .map((line) => line.split('\t').map((cell) => clean(cell)));
  }

  const workbook = XLSX.read(file.buffer, { type: 'buffer', cellDates: false });
  const firstSheet = workbook.Sheets[workbook.SheetNames[0] || ''];
  if (!firstSheet) return [];
  return (XLSX.utils.sheet_to_json(firstSheet, { header: 1, defval: '' }) as unknown[][]).map((row) => row.map((cell) => clean(cell)));
}

function parseHolderMatrix(matrix: string[][], fileName: string) {
  if (!matrix.length) return [] as MutableImportRecord[];
  const results: MutableImportRecord[] = [];
  let current: MutableImportRecord | null = null;

  for (let index = 1; index < matrix.length; index += 1) {
    const cells = padCells(matrix[index], 21);
    if (!cells.some(Boolean)) {
      continue;
    }
    if (cells[0] && cells[1] && cells[1].toUpperCase() !== 'PROCESS') {
      if (current) results.push(finalizeMutableImportRecord('holder', current));
      current = {
        rowNumber: index + 1,
        inProcessSection: false,
        processSequence: 0,
        payload: {
          processCode: clean(cells[0]).toUpperCase(),
          processName: stripQuotes(cells[1]),
          processType: 'Holder Process',
          inch: inferInch(cells[1]),
          finishVariant: detectFinishVariant(`${cells[0]} ${cells[1]}`),
          plateCodes: parsePlateCodes(cells[20]),
          materialRows: [createHolderMaterialRow(cells)],
          machineRows: [],
          coatingRows: [],
          status: 'Active',
          createdBy: 'Excel Import',
          sourceFile: fileName,
        },
      };
      continue;
    }
    if (!current) continue;
    if (cells[1].toUpperCase() === 'PROCESS') {
      current.inProcessSection = true;
      continue;
    }
    const coatingCellIndex = cells.findIndex((cell) => /COATING|ZINC/i.test(cell));
    if (coatingCellIndex >= 0) {
      current.payload.finishVariant = detectFinishVariant(cells[coatingCellIndex], clean(current.payload.finishVariant) || 'DEFAULT');
      current.payload.coatingRows = [
        ...(current.payload.coatingRows || []),
        {
          coatingType: clean(cells[coatingCellIndex]).toUpperCase(),
          total: parseNumber(cells[coatingCellIndex + 1], 0),
          sourceData: { cells },
        },
      ];
      continue;
    }
    if (!current.inProcessSection && cells[2]) {
      current.payload.materialRows = [...(current.payload.materialRows || []), createHolderMaterialRow(cells)];
      continue;
    }
    if (clean(cells[1])) {
      current.processSequence += 1;
      current.payload.machineRows = [
        ...(current.payload.machineRows || []),
        createMachineRow(cells, current.processSequence),
      ];
    }
  }

  if (current) results.push(finalizeMutableImportRecord('holder', current));
  return results;
}

function parseWheelMatrix(matrix: string[][], fileName: string) {
  if (!matrix.length) return [] as MutableImportRecord[];
  const results: MutableImportRecord[] = [];
  let current: MutableImportRecord | null = null;

  for (let index = 1; index < matrix.length; index += 1) {
    const cells = padCells(matrix[index], 11);
    if (!cells.some(Boolean)) {
      continue;
    }
    if (cells[0] && cells[1] && cells[1].toUpperCase() !== 'PROCESS') {
      if (current) results.push(finalizeMutableImportRecord('wheel', current));
      current = {
        rowNumber: index + 1,
        inProcessSection: false,
        processSequence: 0,
        payload: {
          processCode: clean(cells[0]).toUpperCase(),
          processName: stripQuotes(cells[1]),
          processType: 'Wheel Process',
          inch: inferInch(cells[1]),
          finishVariant: 'DEFAULT',
          plateCodes: [],
          materialRows: [createWheelMaterialRow(cells)],
          machineRows: [],
          coatingRows: [],
          status: 'Active',
          createdBy: 'Excel Import',
          sourceFile: fileName,
        },
      };
      continue;
    }
    if (!current) continue;
    if (cells[1].toUpperCase() === 'PROCESS') {
      current.inProcessSection = true;
      continue;
    }
    const coatingCellIndex = cells.findIndex((cell) => /COATING|ZINC/i.test(cell));
    if (coatingCellIndex >= 0) {
      current.payload.coatingRows = [
        ...(current.payload.coatingRows || []),
        {
          coatingType: clean(cells[coatingCellIndex]).toUpperCase(),
          total: parseNumber(cells[coatingCellIndex + 1], 0),
          sourceData: { cells },
        },
      ];
      continue;
    }
    if (!current.inProcessSection && cells[2]) {
      current.payload.materialRows = [...(current.payload.materialRows || []), createWheelMaterialRow(cells)];
      continue;
    }
    if (clean(cells[1])) {
      current.processSequence += 1;
      current.payload.machineRows = [
        ...(current.payload.machineRows || []),
        createWheelMachineRow(cells, current.processSequence),
      ];
    }
  }

  if (current) results.push(finalizeMutableImportRecord('wheel', current));
  return results;
}

function createHolderMaterialRow(cells: string[]): ProcessMaterialRowInput {
  const totals = extractHolderSummaryTotals(cells);
  return {
    materialCode: stripQuotes(cells[2]),
    materialName: stripQuotes(cells[2]),
    materialType: classifyMaterialType(cells[2], cells[1]),
    length: optionalNumber(cells[3]),
    quantity: optionalNumber(cells[3]),
    width: optionalNumber(cells[4]),
    thickness: optionalNumber(cells[5]),
    noOfStrips: optionalNumber(cells[6]),
    noOfPcsPerStrip: optionalNumber(cells[7]),
    noOfSetsPerSheet: optionalNumber(cells[8]),
    oneSheetWeight: optionalNumber(cells[9]),
    pcsWeightGram: optionalNumber(cells[10]),
    noOfPcsPerKg: optionalNumber(cells[11]),
    standardPrice: optionalNumber(cells[12]),
    oneSheetRate: optionalNumber(cells[13]),
    weight: optionalNumber(cells[14]),
    piecesPerKgOrMeter: firstPositive([optionalNumber(cells[15]), optionalNumber(cells[16]), optionalNumber(cells[11])]),
    importedMaterialTotal: totals.materialTotal,
    materialTotal: totals.materialTotal,
    sourceData: { cells, noOfStrips: optionalNumber(cells[6]) },
  };
}

function createWheelMaterialRow(cells: string[]): ProcessMaterialRowInput {
  return {
    materialCode: stripQuotes(cells[2]),
    materialName: stripQuotes(cells[2]),
    materialType: classifyMaterialType(cells[2]),
    standardPrice: optionalNumber(cells[3]),
    pcsWeightGram: optionalNumber(cells[4]),
    cavityCount: optionalNumber(cells[5]),
    importedMaterialTotal: optionalNumber(cells[10]),
    materialTotal: optionalNumber(cells[10]),
    sourceData: { cells },
  };
}

function createMachineRow(cells: string[], sequence: number): ProcessMachineRowInput {
  return {
    lineCode: `PROC-${String(sequence).padStart(2, '0')}`,
    processName: clean(cells[1]),
    machineName: clean(cells[2]) || null,
    machineMetric: clean(cells[2]) || null,
    machineHourRate: optionalNumber(cells[3]),
    labourCostPerHour: optionalNumber(cells[4]),
    productionPerHour: optionalNumber(cells[5]),
    productionCostPerPiece: optionalNumber(cells[6]),
    oneStripCuttingCharge: optionalNumber(cells[7]),
    onePieceCuttingCharge: optionalNumber(cells[8]),
    quantity: optionalNumber(cells[9]) || 1,
    galvanizingPerKg: optionalNumber(cells[10]),
    galvanizingPerPiece: optionalNumber(cells[11]),
    total: optionalNumber(cells[12]),
    sourceData: { cells },
  };
}

function createWheelMachineRow(cells: string[], sequence: number): ProcessMachineRowInput {
  return {
    lineCode: `PROC-${String(sequence).padStart(2, '0')}`,
    processName: clean(cells[1]),
    machineName: clean(cells[2]) || null,
    machineMetric: clean(cells[2]) || null,
    machineHourRate: optionalNumber(cells[3]),
    labourCostPerHour: firstPositive([optionalNumber(cells[7]), optionalNumber(cells[6])]),
    productionPerHour: firstPositive([optionalNumber(cells[5]), optionalNumber(cells[8]), optionalNumber(cells[7]), optionalNumber(cells[4])]),
    productionCostPerPiece: firstPositive([optionalNumber(cells[10]), optionalNumber(cells[9]), optionalNumber(cells[6])]),
    quantity: 1,
    total: firstPositive([optionalNumber(cells[10]), optionalNumber(cells[9])]),
    sourceData: { cells },
  };
}

function finalizeMutableImportRecord(kind: ProcessModuleKind, record: MutableImportRecord) {
  const calculated = calculateProcessRecord(kind, record.payload);
  return {
    ...record,
    payload: {
      ...record.payload,
      finishVariant: calculated.finishVariant,
      plateCodes: calculated.plateCodes,
      materialRows: calculated.materialRows,
      machineRows: calculated.machineRows,
      coatingRows: calculated.coatingRows,
    },
  };
}

function buildHolderExportRows(records: ProcessRecordWriteDto[]) {
  const rows: (string | number)[][] = [holderHeaders];
  for (const record of records) {
    const calculated = calculateProcessRecord('holder', record);
    const materialRows = calculated.materialRows.length ? calculated.materialRows : [calculateMaterialRow('holder', { materialName: '' })];
    materialRows.forEach((materialRow, index) => {
      rows.push([
        index === 0 ? calculated.processCode : '',
        index === 0 ? calculated.processName : '',
        materialRow.materialName,
        materialRow.length ?? materialRow.quantity ?? '',
        materialRow.width ?? '',
        materialRow.thickness ?? '',
        sourceDataNumber(materialRow.sourceData, 'noOfStrips') ?? materialRow.noOfStrips ?? '',
        materialRow.noOfPcsPerStrip ?? '',
        materialRow.noOfSetsPerSheet ?? '',
        materialRow.oneSheetWeight ?? '',
        materialRow.pcsWeightGram ?? '',
        materialRow.noOfPcsPerKg ?? '',
        materialRow.standardPrice ?? '',
        materialRow.oneSheetRate ?? '',
        materialRow.weight ?? '',
        materialRow.piecesPerKgOrMeter ?? '',
        materialRow.piecesPerKgOrMeter ?? '',
        materialRow.materialTotal,
        index === 0 ? calculated.processTotal : '',
        index === 0 ? calculated.totalAll : '',
        index === 0 ? calculated.plateCodes.join(',') : '',
      ]);
    });
    rows.push(['', 'PROCESS', 'PRESS CAPACITY', 'PRESS RUNNING COST/HR', 'LABOUR COST/HR', 'PRODUTION / HR', 'PRODUTION COST/PIECE', 'ONE STRIP CUTTING CHARGE', 'ONE PCS CUTTING CHARGE', 'QTY', 'GALVANIZING CR/PER KG', 'Galvanizing charge / per pcs', ' TOTAL ']);
    calculated.machineRows.forEach((machineRow) => {
      rows.push([
        '',
        machineRow.processName,
        machineRow.machineName ?? '',
        machineRow.machineHourRate ?? '',
        machineRow.labourCostPerHour ?? '',
        machineRow.productionPerHour ?? '',
        machineRow.productionCostPerPiece ?? '',
        machineRow.oneStripCuttingCharge ?? '',
        machineRow.onePieceCuttingCharge ?? '',
        machineRow.quantity ?? '',
        machineRow.galvanizingPerKg ?? '',
        machineRow.galvanizingPerPiece ?? '',
        machineRow.total,
      ]);
    });
    if (calculated.coatingRows.length) rows.push(['']);
    calculated.coatingRows.forEach((coatingRow) => {
      rows.push(['', '', coatingRow.coatingType, coatingRow.total]);
    });
  }
  return rows;
}

function buildWheelExportRows(records: ProcessRecordWriteDto[]) {
  const rows: (string | number)[][] = [wheelHeaders];
  for (const record of records) {
    const calculated = calculateProcessRecord('wheel', record);
    calculated.materialRows.forEach((materialRow, index) => {
      rows.push([
        index === 0 ? calculated.processCode : '',
        index === 0 ? calculated.processName : '',
        materialRow.materialName,
        materialRow.standardPrice ?? '',
        materialRow.pcsWeightGram ?? '',
        materialRow.cavityCount ?? '',
        '',
        '',
        '',
        '',
        materialRow.materialTotal,
      ]);
    });
    rows.push(['', 'PROCESS', 'MACHINE TONNAGE', ' MACHINE HOUR RATE ', ' NO OF SHOTS PER HOUR ', ' NO OF PCS PER HOUR ', ' LABOR COST PER PIECE ', ' LABOUR COST/HR ', ' PRODUTION / HR ', ' PRODUTION COST/PIECE ', ' TOTAL ']);
    calculated.machineRows.forEach((machineRow) => {
      rows.push([
        '',
        machineRow.processName,
        machineRow.machineName ?? machineRow.machineMetric ?? '',
        machineRow.machineHourRate ?? '',
        '',
        machineRow.productionPerHour ?? '',
        machineRow.productionCostPerPiece ?? '',
        machineRow.labourCostPerHour ?? '',
        machineRow.productionPerHour ?? '',
        machineRow.productionCostPerPiece ?? '',
        machineRow.total,
      ]);
    });
    if (calculated.coatingRows.length) rows.push(['']);
    calculated.coatingRows.forEach((coatingRow) => {
      rows.push(['', '', coatingRow.coatingType, coatingRow.unitPrice ?? '', '', '', '', '', '', '', coatingRow.total]);
    });
  }
  return rows;
}

function sourceDataNumber(sourceData: Record<string, unknown> | undefined, key: string) {
  if (!sourceData || !(key in sourceData)) return null;
  return optionalNumber(sourceData[key]);
}

function padCells(row: string[], size: number) {
  const cells = row.map((cell) => clean(cell));
  while (cells.length < size) cells.push('');
  return cells;
}

function dedupeStrings(values: string[]) {
  return [...new Set(values.map((value) => clean(value)).filter(Boolean))];
}

function extractHolderSummaryTotals(cells: string[]) {
  const materialTotal = firstPositive([optionalNumber(cells[17]), optionalNumber(cells[16])]) || 0;
  const processTotal = firstPositive([optionalNumber(cells[18])]) || 0;
  const overallTotal = firstPositive([optionalNumber(cells[19]), optionalNumber(cells[18]), optionalNumber(cells[17])]) || 0;
  return { materialTotal, processTotal, overallTotal };
}