import * as XLSX from 'xlsx';
import { BadRequestException } from '@nestjs/common';

export type UploadedImportFile = {
  fileName?: string;
  content?: string;
};

export type CostingDetailInput = {
  lineType: string;
  sequence: number;
  lineCode?: string | null;
  lineName: string;
  machineName?: string | null;
  quantity?: number | null;
  unitRate?: number | null;
  length?: number | null;
  width?: number | null;
  thickness?: number | null;
  weightGram?: number | null;
  cavityCount?: number | null;
  noOfSetsPerSheet?: number | null;
  noOfPcsPerStrip?: number | null;
  oneSheetWeight?: number | null;
  stdPrice?: number | null;
  oneSheetRate?: number | null;
  piecesPerKgOrMeter?: number | null;
  runningCostPerHour?: number | null;
  labourCostPerHour?: number | null;
  productionPerHour?: number | null;
  productionCostPerPiece?: number | null;
  oneStripCuttingCharge?: number | null;
  onePieceCuttingCharge?: number | null;
  galvanizingPerKg?: number | null;
  galvanizingPerPiece?: number | null;
  totalCost: number;
  sourceData: Record<string, unknown>;
};

export type CostingRecordInput = {
  itemCode: string;
  itemName: string;
  lookupKey: string;
  sfgType: 'HOLDER' | 'WHEEL';
  inch: string | null;
  finishVariant: string;
  sourceKind: 'HOLDER_SUMMARY' | 'WHEEL_SUMMARY';
  plateCodes: string[];
  materialCost: number;
  machineCost: number;
  coatingCost: number;
  totalCost: number;
  sourceFile: string;
  sourceData: Record<string, unknown>;
  details: CostingDetailInput[];
};

export function readWorkbookFromContent(file: UploadedImportFile) {
  const buffer = decodeImportBuffer(file);
  return XLSX.read(buffer, { type: 'buffer', cellDates: false });
}

export function parseHolderSummary(file: UploadedImportFile) {
  const matrix = readTabularMatrix(file);
  if (!matrix.length) return [];
  const entries: CostingRecordInput[] = [];
  let current: CostingRecordInput | null = null;
  let processSequence = 0;

  for (const rawRow of matrix.slice(1)) {
    const cells = padCells(rawRow, 21).map(clean);
    if (!cells.some(Boolean)) continue;
    if (cells[0] && cells[1] && cells[1].toUpperCase() !== 'PROCESS') {
      if (current) entries.push(finalizeRecord(current));
      current = createHolderRecord(cells, file.fileName || 'holder-summary');
      processSequence = 0;
      continue;
    }
    if (!current) continue;
    if (cells[1].toUpperCase() === 'PROCESS') continue;
    const coatingName = cells.find((cell) => /COATING|ZINC/i.test(cell));
    if (coatingName) {
      const coatingIndex = cells.findIndex((cell) => cell === coatingName);
      current.finishVariant = detectFinishVariant(coatingName, current.finishVariant);
      const coatingCost = parseNumber(cells[coatingIndex + 1], 0);
      current.coatingCost = round(coatingCost);
      current.details.push({
        lineType: 'COATING',
        sequence: current.details.length + 1,
        lineName: coatingName,
        totalCost: round(coatingCost),
        sourceData: { cells },
      });
      continue;
    }
    const processName = cells[1];
    if (!processName) continue;
    processSequence += 1;
    const detail = {
      lineType: 'PROCESS',
      sequence: current.details.length + 1,
      lineCode: `${safeCode(current.itemCode || current.itemName)}-P${String(processSequence).padStart(2, '0')}`,
      lineName: processName,
      machineName: cells[2] || null,
      runningCostPerHour: nullableNumber(cells[3]),
      labourCostPerHour: nullableNumber(cells[4]),
      productionPerHour: nullableNumber(cells[5]),
      productionCostPerPiece: nullableNumber(cells[6]),
      oneStripCuttingCharge: nullableNumber(cells[7]),
      onePieceCuttingCharge: nullableNumber(cells[8]),
      quantity: nullableNumber(cells[9]),
      galvanizingPerKg: nullableNumber(cells[10]),
      galvanizingPerPiece: nullableNumber(cells[11]),
      totalCost: round(calculateHolderProcessCost(cells)),
      sourceData: { cells },
    } satisfies CostingDetailInput;
    current.machineCost = round(current.machineCost + detail.totalCost);
    current.details.push(detail);
  }

  if (current) entries.push(finalizeRecord(current));
  return entries;
}

export function parseWheelSummary(file: UploadedImportFile) {
  const matrix = readTabularMatrix(file);
  if (!matrix.length) return [];
  const entries: CostingRecordInput[] = [];
  let current: CostingRecordInput | null = null;
  let processSequence = 0;

  for (const rawRow of matrix.slice(1)) {
    const cells = padCells(rawRow, 11).map(clean);
    if (!cells.some(Boolean)) continue;
    if (cells[0] && cells[1] && cells[1].toUpperCase() !== 'PROCESS') {
      if (current) entries.push(finalizeRecord(current));
      current = createWheelRecord(cells, file.fileName || 'wheel-summary');
      processSequence = 0;
      continue;
    }
    if (!current) continue;
    if (cells[1].toUpperCase() === 'PROCESS') continue;
    const processName = cells[1];
    if (processName) {
      processSequence += 1;
      const totalCost = calculateWheelProcessCost(cells);
      const detail = {
        lineType: 'PROCESS',
        sequence: current.details.length + 1,
        lineCode: `${safeCode(current.itemCode || current.itemName)}-P${String(processSequence).padStart(2, '0')}`,
        lineName: processName,
        machineName: cells[2] || null,
        runningCostPerHour: nullableNumber(cells[3]),
        productionPerHour: firstPositive([nullableNumber(cells[5]), nullableNumber(cells[7]), nullableNumber(cells[8]), nullableNumber(cells[4])]),
        productionCostPerPiece: firstPositive([nullableNumber(cells[10]), nullableNumber(cells[9]), nullableNumber(cells[6])]),
        totalCost: round(totalCost),
        sourceData: { cells },
      } satisfies CostingDetailInput;
      current.machineCost = round(current.machineCost + detail.totalCost);
      current.details.push(detail);
      continue;
    }
    if (cells[2]) {
      const detail = createWheelMaterialDetail(current, cells, current.details.length + 1);
      current.materialCost = round(current.materialCost + detail.totalCost);
      current.details.push(detail);
    }
  }

  if (current) entries.push(finalizeRecord(current));
  return entries;
}

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 inferInch(value: unknown) {
  const match = clean(value).match(/(\d+(?:\.\d+)?)\s*INCH/i);
  return match ? `${match[1]} INCH` : null;
}

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

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

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

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

function decodeImportBuffer(file: UploadedImportFile) {
  if (!file.content) throw new BadRequestException(['File content is required.']);
  const base64 = file.content.includes(',') ? file.content.split(',').pop() || '' : file.content;
  return Buffer.from(base64, 'base64');
}

function readTabularMatrix(file: UploadedImportFile) {
  const buffer = decodeImportBuffer(file);
  const asText = buffer.toString('utf8').replace(/^\uFEFF/, '');
  if (looksLikeDelimitedText(asText)) return parseDelimitedMatrix(asText);
  const workbook = XLSX.read(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 looksLikeDelimitedText(value: string) {
  const sample = value.slice(0, 300);
  if (!sample.includes('\t')) return false;
  const nonPrintable = sample.split('').filter((char) => {
    const code = char.charCodeAt(0);
    return code !== 9 && code !== 10 && code !== 13 && (code < 32 || code > 126);
  }).length;
  return sample.length > 0 && nonPrintable / sample.length < 0.1;
}

function parseDelimitedMatrix(text: string) {
  return text
    .split(/\r?\n/)
    .filter((line) => line.trim().length)
    .map((line) => line.split('\t').map((cell) => cell.replace(/^\uFEFF/, '').trim()));
}

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

function createHolderRecord(cells: string[], sourceFile: string): CostingRecordInput {
  const itemCode = cells[0];
  const itemName = stripQuotes(cells[1]);
  const materialDetail = createHolderMaterialDetail(itemCode, itemName, cells);
  return {
    itemCode,
    itemName,
    lookupKey: normalizeLookupKey(itemName),
    sfgType: 'HOLDER',
    inch: inferInch(itemName),
    finishVariant: detectFinishVariant(`${itemCode} ${itemName}`),
    sourceKind: 'HOLDER_SUMMARY',
    plateCodes: parsePlateCodes(cells[20]),
    materialCost: round(materialDetail.totalCost),
    machineCost: 0,
    coatingCost: 0,
    totalCost: 0,
    sourceFile,
    sourceData: { cells },
    details: [materialDetail],
  };
}

function createWheelRecord(cells: string[], sourceFile: string): CostingRecordInput {
  const itemCode = cells[0];
  const itemName = stripQuotes(cells[1]);
  const materialDetail = createWheelMaterialDetail({ itemCode, itemName }, cells);
  return {
    itemCode,
    itemName,
    lookupKey: normalizeLookupKey(itemName),
    sfgType: 'WHEEL',
    inch: inferInch(itemName),
    finishVariant: 'DEFAULT',
    sourceKind: 'WHEEL_SUMMARY',
    plateCodes: [],
    materialCost: round(materialDetail.totalCost),
    machineCost: 0,
    coatingCost: 0,
    totalCost: 0,
    sourceFile,
    sourceData: { cells },
    details: [materialDetail],
  };
}

function createHolderMaterialDetail(itemCode: string, itemName: string, cells: string[]) {
  const totalCost = calculateHolderMaterialCost(itemName, cells);
  return {
    lineType: 'MATERIAL',
    sequence: 1,
    lineCode: itemCode || safeCode(itemName),
    lineName: stripQuotes(cells[2]) || itemName,
    quantity: nullableNumber(cells[3]),
    length: nullableNumber(cells[3]),
    width: nullableNumber(cells[4]),
    thickness: nullableNumber(cells[5]),
    noOfPcsPerStrip: nullableNumber(cells[7]),
    noOfSetsPerSheet: nullableNumber(cells[8]),
    oneSheetWeight: nullableNumber(cells[9]),
    weightGram: nullableNumber(cells[10]),
    piecesPerKgOrMeter: firstPositive([nullableNumber(cells[15]), nullableNumber(cells[11])]),
    stdPrice: nullableNumber(cells[12]),
    oneSheetRate: nullableNumber(cells[13]),
    totalCost: round(totalCost),
    sourceData: { cells },
  } satisfies CostingDetailInput;
}

function createWheelMaterialDetail(record: { itemCode?: string; itemName?: string }, cells: string[], sequence = 1) {
  const totalCost = calculateWheelMaterialCost(cells);
  return {
    lineType: 'MATERIAL',
    sequence,
    lineCode: record.itemCode || safeCode(record.itemName),
    lineName: stripQuotes(cells[2]) || stripQuotes(record.itemName),
    unitRate: nullableNumber(cells[3]),
    weightGram: nullableNumber(cells[4]),
    cavityCount: nullableNumber(cells[5]),
    totalCost: round(totalCost),
    sourceData: { cells },
  } satisfies CostingDetailInput;
}

function calculateHolderMaterialCost(itemName: string, cells: string[]) {
  const materialName = `${cells[2]} ${itemName}`.toUpperCase();
  const length = parseNumber(cells[3], 0);
  const width = parseNumber(cells[4], 0);
  const thickness = parseNumber(cells[5], 0);
  const noOfSetsPerSheet = parseNumber(cells[8], 0);
  const oneSheetWeight = parseNumber(cells[9], 0);
  const weightGram = parseNumber(cells[10], 0);
  const stdPrice = parseNumber(cells[12], 0);
  const piecesPerUnit = firstPositive([nullableNumber(cells[15]), nullableNumber(cells[11])]) || 0;
  const rawWeight = parseNumber(cells[14], 0);
  const summaryTotals = extractHolderSummaryTotals(cells);

  if (noOfSetsPerSheet > 0 && oneSheetWeight > 0 && stdPrice > 0) return (oneSheetWeight * stdPrice) / noOfSetsPerSheet;
  if ((materialName.includes('BALL') || materialName.includes('BEARING')) && length > 0 && stdPrice > 0) return length * stdPrice;
  if (materialName.includes('SLEEVE') && length > 0 && width > 0 && thickness > 0) {
    const outer = Math.PI * Math.pow(thickness / 2, 2);
    const inner = Math.PI * Math.pow(width / 2, 2);
    return Math.max(0, outer - inner) * length * 0.0078 * (stdPrice / 1000);
  }
  if ((materialName.includes('ROD') || materialName.includes('HEX ROD') || materialName.includes('RIVET')) && length > 0 && width > 0) {
    const area = Math.PI * Math.pow(width / 2, 2);
    return area * length * 0.0078 * (stdPrice / 1000);
  }
  if (materialName.includes('BRAKE') && length > 0 && width > 0 && thickness > 0) {
    return length * width * thickness * 7.8 / 1000 / 1000 * stdPrice;
  }
  if (materialName.includes('GREASE') && stdPrice > 0) {
    const greaseWeight = firstPositive([nullableNumber(cells[14]), nullableNumber(cells[10])]) || 0;
    if (greaseWeight > 0) return (stdPrice / 1000) * greaseWeight;
  }
  if (materialName.includes('WHEEL') && stdPrice > 0 && rawWeight > 0) return stdPrice * rawWeight;
  if (weightGram > 0 && stdPrice > 0) return stdPrice * weightGram / 1000;
  if (piecesPerUnit > 0 && stdPrice > 0) return length * stdPrice / piecesPerUnit;
  if (summaryTotals.materialTotal > 0 && materialName.match(/\b(BOLT|NUT|WASHER|PIN|RING|CLIP|SCREW)\b/)) return summaryTotals.materialTotal;
  if (summaryTotals.materialTotal > 0 && width === 0 && thickness === 0 && weightGram === 0 && noOfSetsPerSheet === 0) return summaryTotals.materialTotal;
  return length * stdPrice;
}

function calculateHolderProcessCost(cells: string[]) {
  const total = parseNumber(cells[12], NaN);
  if (Number.isFinite(total)) return total;
  const productionCostPerPiece = parseNumber(cells[6], 0);
  const oneStrip = parseNumber(cells[7], 0);
  const onePiece = parseNumber(cells[8], 0);
  const quantity = parseNumber(cells[9], 1);
  const galvanizingPerPiece = parseNumber(cells[11], 0);
  const runningCost = parseNumber(cells[3], 0);
  const labourCost = parseNumber(cells[4], 0);
  const productionPerHour = parseNumber(cells[5], 0);
  const computedProduction = productionCostPerPiece || (productionPerHour > 0 ? (runningCost + labourCost) / productionPerHour : 0);
  return computedProduction + (oneStrip || onePiece || galvanizingPerPiece ? (oneStrip + onePiece + galvanizingPerPiece) * Math.max(quantity, 1) : 0);
}

function calculateWheelMaterialCost(cells: string[]) {
  const rate = parseNumber(cells[3], 0);
  const weightGram = parseNumber(cells[4], 0);
  const cavityCount = parseNumber(cells[5], 0);
  if (rate > 0 && weightGram > 0 && cavityCount > 0) return (rate * weightGram) / (1000 * cavityCount);
  return parseNumber(cells[10], 0);
}

function calculateWheelProcessCost(cells: string[]) {
  const total = parseNumber(cells[10], NaN);
  if (Number.isFinite(total)) return total;
  const productionCost = parseNumber(cells[9], NaN);
  if (Number.isFinite(productionCost) && productionCost > 0) return productionCost;
  const laborCostPerPiece = parseNumber(cells[6], NaN);
  if (Number.isFinite(laborCostPerPiece) && laborCostPerPiece > 0) return laborCostPerPiece;
  const machineHourRate = parseNumber(cells[3], 0);
  const labourCostPerHour = parseNumber(cells[6], 0);
  const piecesPerHour = firstPositive([nullableNumber(cells[5]), nullableNumber(cells[7]), nullableNumber(cells[4])]) || 0;
  return piecesPerHour > 0 ? (machineHourRate + labourCostPerHour) / piecesPerHour : 0;
}

function finalizeRecord(record: CostingRecordInput) {
  record.materialCost = round(record.materialCost);
  record.machineCost = round(record.machineCost);
  record.coatingCost = round(record.coatingCost);
  record.totalCost = round(record.materialCost + record.machineCost + record.coatingCost);
  return record;
}

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

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

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

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

function extractHolderSummaryTotals(cells: string[]) {
  const values = [16, 17, 18, 19]
    .map((index) => nullableNumber(cells[index]))
    .filter((value): value is number => Number.isFinite(value as number) && (value as number) > 0);
  if (!values.length) return { materialTotal: 0, processTotal: 0, overallTotal: 0 };
  if (values.length === 1) return { materialTotal: values[0], processTotal: 0, overallTotal: values[0] };
  if (values.length === 2) return { materialTotal: values[0], processTotal: round(Math.max(values[1] - values[0], 0)), overallTotal: values[1] };
  return { materialTotal: values[0], processTotal: values[1], overallTotal: values[values.length - 1] };
}