import { BadRequestException, Injectable, NotFoundException } from '@nestjs/common';
import { Prisma } from '../generated/prisma/index';
import { PrismaService } from '../prisma/prisma.service';
import { ProcessImportCommitDto, ProcessListQuery } from './process-shared.dto';
import {
  CalculatedProcessRecord,
  ProcessModuleKind,
  ProcessRecordWriteDto,
} from './process-shared.types';
import {
  calculateProcessRecord,
  clean,
  detectFinishVariant,
  exportProcessWorkbook,
  holderProcessSample,
  inferInch,
  normalizeLookupKey,
  normalizeStatus,
  parseProcessImportPreview,
  processTypeFor,
  round,
  sourceKindFor,
  validateProcessRecord,
  wheelProcessSample,
} from './process-shared.util';

const sortColumns = new Set(['itemCode', 'itemName', 'inch', 'finishVariant', 'materialCost', 'machineCost', 'coatingCost', 'totalCost', 'updatedAt']);

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

  async list(kind: ProcessModuleKind, query: ProcessListQuery) {
    const page = positiveInt(query.page, 1);
    const pageSize = Math.min(positiveInt(query.pageSize, 10), 100);
    const sortBy = sortColumns.has(query.sortBy || '') ? query.sortBy || 'itemCode' : 'itemCode';
    const sortOrder = query.sortOrder === 'desc' ? 'desc' : 'asc';
    const where = this.buildWhere(kind, query);
    const [total, items, filterOptions] = await Promise.all([
      (this.prisma as any).sfgCostingRecord.count({ where }),
      (this.prisma as any).sfgCostingRecord.findMany({
        where,
        orderBy: { [sortBy]: sortOrder },
        skip: (page - 1) * pageSize,
        take: pageSize,
      }),
      this.options(kind),
    ]);

    return {
      items: items.map((item: any) => ({
        id: item.id,
        processCode: item.itemCode,
        processName: item.itemName,
        processType: processTypeFor(kind),
        inch: item.inch || '',
        finishVariant: item.finishVariant,
        plateCodes: item.plateCodes || [],
        materialCost: item.materialCost,
        machineCost: item.machineCost,
        coatingCost: item.coatingCost,
        totalCost: item.totalCost,
        status: item.sourceData?.status || 'Active',
        updatedAt: item.updatedAt,
      })),
      meta: { total, page, pageSize, totalPages: Math.max(1, Math.ceil(total / pageSize)) },
      filterOptions,
    };
  }

  async detail(kind: ProcessModuleKind, id: string) {
    const record = await (this.prisma as any).sfgCostingRecord.findFirst({
      where: { id, sourceKind: sourceKindFor(kind) },
      include: { details: { orderBy: { sequence: 'asc' } } },
    });
    if (!record) throw new NotFoundException(`${titleFor(kind)} process not found.`);
    return this.mapRecordToDto(kind, record);
  }

  async create(kind: ProcessModuleKind, dto: ProcessRecordWriteDto) {
    const existing = await this.findExistingRecord(kind, dto);
    return this.save(kind, dto, existing?.id || '');
  }

  async update(kind: ProcessModuleKind, id: string, dto: ProcessRecordWriteDto) {
    await this.detail(kind, id);
    return this.save(kind, dto, id);
  }

  async delete(kind: ProcessModuleKind, id: string) {
    await this.detail(kind, id);
    await (this.prisma as any).sfgCostingRecord.delete({ where: { id } });
    return { deleted: true };
  }

  async calculate(kind: ProcessModuleKind, dto: ProcessRecordWriteDto) {
    const normalizedDto = await this.applyRawMaterialRates(dto);
    return calculateProcessRecord(kind, normalizedDto);
  }

  async options(kind: ProcessModuleKind) {
    const [materials, machines, holders, wheels, plates, records] = await Promise.all([
      this.prisma.rawMaterial.findMany({ include: { category: true, uom: true }, orderBy: { materialName: 'asc' }, take: 1000 }),
      this.prisma.machinery.findMany({ orderBy: { machineryName: 'asc' }, take: 500 }),
      kind === 'holder' ? this.prisma.holderMaster.findMany({ orderBy: { holderName: 'asc' }, take: 600 }) : Promise.resolve([]),
      kind === 'wheel' ? this.prisma.wheelMaster.findMany({ orderBy: { wheelName: 'asc' }, take: 600 }) : Promise.resolve([]),
      kind === 'holder' ? this.prisma.plateMaster.findMany({ orderBy: { plateName: 'asc' }, take: 1000 }) : Promise.resolve([]),
      (this.prisma as any).sfgCostingRecord.findMany({ where: { sourceKind: sourceKindFor(kind) }, select: { finishVariant: true, inch: true, plateCodes: true } }),
    ]);

    return {
      processTypes: [processTypeFor(kind)],
      statuses: ['Active', 'Inactive', 'Draft'],
      finishVariants: dedupe(records.map((record: any) => clean(record.finishVariant)).filter(Boolean)),
      inches: dedupe(records.map((record: any) => clean(record.inch)).filter(Boolean)).sort(),
      holders,
      wheels,
      plates,
      materials: materials.map((material) => ({
        id: material.id,
        materialCode: material.materialCode,
        materialName: material.materialName,
        categoryName: material.category?.categoryName || '',
        uomCode: material.uom?.uomCode || '',
        costPerUnit: material.costPerUnit,
      })),
      machines,
    };
  }

  previewImport(kind: ProcessModuleKind, fileName: string, buffer: Buffer) {
    const rows = parseProcessImportPreview(kind, fileName, buffer);
    return {
      fileName,
      rows,
      total: rows.length,
      valid: rows.filter((row) => row.valid).length,
      invalid: rows.filter((row) => !row.valid).length,
    };
  }

  async importFile(kind: ProcessModuleKind, fileName: string, buffer: Buffer, mode: 'validOnly' | 'allOrNothing' = 'validOnly') {
    const preview = this.previewImport(kind, fileName, buffer);
    return this.commitImport(kind, { rows: preview.rows.map((row) => row.payload), mode });
  }

  async commitImport(kind: ProcessModuleKind, dto: ProcessImportCommitDto) {
    const mode = dto.mode === 'allOrNothing' ? 'allOrNothing' : 'validOnly';
    const errors: string[] = [];
    let created = 0;
    let updated = 0;

    const rows = await Promise.all(
      (dto.rows || []).map(async (row, index) => {
        const normalizedRow = await this.applyRawMaterialRates(row);

        return {
          index,
          row: normalizedRow,
          errors: validateProcessRecord(kind, normalizedRow),
        };
      }),
    );

    if (mode === 'allOrNothing') {
      const invalid = rows.filter((row) => row.errors.length);
      if (invalid.length) throw new BadRequestException(invalid.flatMap((row) => row.errors.map((error) => `Row ${row.index + 1}: ${error}`)));
      await this.prisma.$transaction(async (tx) => {
        for (const row of rows) {
          const result = await this.persistRecord(tx, kind, row.row);
          if (result === 'created') created += 1;
          else updated += 1;
        }
      });
      return { created, updated, failed: 0, errors: [] };
    }

    for (const row of rows) {
      try {
        const result = await this.prisma.$transaction(async (tx) => {
          return this.persistRecord(tx, kind, row.row);
        });

        if (result === 'created') created++;
        if (result === 'recreated') created++;

      } catch (error) {
        errors.push(`Row ${row.index + 1}: ${extractError(error)}`);
      }
    }

    return { created, updated, failed: errors.length, errors };
  }

  async export(kind: ProcessModuleKind, query: ProcessListQuery & { format?: string }) {
    const records = await (this.prisma as any).sfgCostingRecord.findMany({
      where: this.buildWhere(kind, query),
      include: { details: { orderBy: { sequence: 'asc' } } },
      orderBy: { itemCode: 'asc' },
    });
    return exportProcessWorkbook(kind, records.map((record: any) => this.mapRecordToDto(kind, record)), query.format || 'xlsx');
  }

  sample(kind: ProcessModuleKind, format = 'xlsx') {
    if (kind === 'holder') return holderProcessSample(format);
    return wheelProcessSample(format);
  }

  private async save(kind: ProcessModuleKind, dto: ProcessRecordWriteDto, id = '') {
    const normalizedDto = await this.applyRawMaterialRates(dto);
    const errors = validateProcessRecord(kind, normalizedDto);
    if (errors.length) throw new BadRequestException(errors);
    let savedId = '';

    await this.prisma.$transaction(async (tx) => {
      const recordId = id || (await this.findExistingRecord(kind, normalizedDto, tx))?.id || '';
      const action = await this.persistRecord(tx, kind, { ...normalizedDto, id: recordId });
      const record = await this.findExistingRecord(kind, normalizedDto, tx);
      savedId = record?.id || recordId;
      if (!savedId) throw new BadRequestException([`Unable to ${action} ${titleFor(kind)} process.`]);
    });

    return this.detail(kind, savedId);
  }

  private async applyRawMaterialRates(dto: ProcessRecordWriteDto, client: any = this.prisma) {
    const materialRows = dto.materialRows || [];

    if (!materialRows.length) {
      return {
        ...dto,
        materialRows: [],
      };
    }

    const byId = new Map<string, any>();
    const byCode = new Map<string, any>();
    const byName = new Map<string, any>();
    const where: any[] = [];
    const rawMaterialIds = dedupe(materialRows.map((row) => clean(row.rawMaterialId)).filter(Boolean));
    const materialCodes = dedupe(materialRows.map((row) => clean(row.materialCode)).filter(Boolean));
    const materialNames = dedupe(materialRows.map((row) => clean(row.materialName)).filter(Boolean));

    if (rawMaterialIds.length) {
      where.push({ id: { in: rawMaterialIds } });
    }

    for (const materialCode of materialCodes) {
      where.push({
        materialCode: {
          equals: materialCode,
          mode: 'insensitive',
        },
      });
    }

    for (const materialName of materialNames) {
      where.push({
        materialName: {
          equals: materialName,
          mode: 'insensitive',
        },
      });
    }

    if (!where.length) {
      return {
        ...dto,
        materialRows: materialRows.map((row) => ({ ...row })),
      };
    }

    const materials = await client.rawMaterial.findMany({
      where: { OR: where },
      include: { category: true },
    });

    for (const material of materials) {
      byId.set(material.id, material);
      byCode.set(clean(material.materialCode).toLowerCase(), material);
      byName.set(clean(material.materialName).toLowerCase(), material);
    }

    return {
      ...dto,
      materialRows: materialRows.map((row) => {
        const material =
          byId.get(clean(row.rawMaterialId))
          || byCode.get(clean(row.materialCode).toLowerCase())
          || byName.get(clean(row.materialName).toLowerCase());

        if (!material) {
          return { ...row };
        }

        return {
          ...row,
          rawMaterialId: material.id,
          materialCode: material.materialCode,
          materialName: material.materialName,
          materialType: material.category?.categoryName || clean(row.materialType),
          standardPrice: material.costPerUnit,
        };
      }),
    };
  }

  private async persistRecord(tx: any, kind: ProcessModuleKind, dto: ProcessRecordWriteDto) {
    const calculated = calculateProcessRecord(kind, dto);
    const existing = dto.id
      ? await tx.sfgCostingRecord.findFirst({
          where: { id: dto.id, sourceKind: sourceKindFor(kind) },
        })
      : await this.findExistingRecord(kind, dto, tx);

    // if (!dto.id && existing) {
    //   return 'skipped';
    // }

    const sourceData = {
      status: normalizeStatus(dto.status, 'Active'),
      processType: processTypeFor(kind),
      sourceFile: clean(dto.sourceFile),
      createdBy: clean(dto.createdBy) || 'Starline Admin',
    };

    const data = {
      sfgItemId: existing?.sfgItemId || null,
      itemCode: calculated.processCode,
      itemName: calculated.processName,
      lookupKey: normalizeLookupKey(calculated.processName),
      sfgType: kind === 'holder' ? 'HOLDER' : 'WHEEL',
      inch: calculated.inch || inferInch(calculated.processName) || null,
      finishVariant: calculated.finishVariant,
      sourceKind: sourceKindFor(kind),
      plateCodes: calculated.plateCodes,
      materialCost: calculated.materialTotal,
      machineCost: calculated.processTotal,
      coatingCost: calculated.coatingTotal,
      totalCost: calculated.totalAll,
      sourceFile: clean(dto.sourceFile) || existing?.sourceFile || null,
      sourceData: sourceData as Prisma.InputJsonValue,
      details: {
        create: this.mapDetails(calculated),
      },
    };
const duplicate = await tx.sfgCostingRecord.findFirst({
  where: {
    lookupKey: data.lookupKey,
    finishVariant: data.finishVariant,
    sourceKind: data.sourceKind,
  },
});

if (duplicate) {
  // 🔥 Delete using SAME UNIQUE CONDITION (VERY IMPORTANT)
  await tx.sfgCostingRecord.deleteMany({
    where: {
      lookupKey: data.lookupKey,
      finishVariant: data.finishVariant,
      sourceKind: data.sourceKind,
    },
  });
}

    const linkedItem = await tx.sfgItem.findFirst({
      where: {
        OR: [
          { itemCode: calculated.processCode },
          { itemName: { equals: calculated.processName, mode: 'insensitive' } },
        ],
      },
    });

    await tx.sfgCostingRecord.create({
      data: {
        ...data,
        sfgItemId: linkedItem?.id || null,
      },
    });
    return 'created';
  }

  private mapRecordToDto(kind: ProcessModuleKind, record: any): ProcessRecordWriteDto {
    const details = record.details || [];
    const materialRows = details
      .filter((detail: any) => detail.lineType === 'MATERIAL')
      .map((detail: any) => ({
        id: detail.id,
        lineCode: detail.lineCode,
        rawMaterialId: detail.sourceData?.rawMaterialId || '',
        materialCode: detail.sourceData?.materialCode || detail.lineCode,
        materialName: detail.lineName,
        materialType: detail.sourceData?.materialType || detail.sourceData?.materialCategory || '',
        length: detail.length,
        quantity: detail.quantity,
        width: detail.width,
        thickness: detail.thickness,
        noOfStrips: detail.sourceData?.noOfStrips ?? null,
        noOfPcsPerStrip: detail.noOfPcsPerStrip,
        noOfSetsPerSheet: detail.noOfSetsPerSheet,
        oneSheetWeight: detail.oneSheetWeight,
        pcsWeightGram: detail.weightGram,
        noOfPcsPerKg: detail.sourceData?.noOfPcsPerKg ?? null,
        standardPrice: detail.stdPrice ?? detail.unitRate,
        oneSheetRate: detail.oneSheetRate,
        weight: detail.sourceData?.weight ?? null,
        piecesPerKgOrMeter: detail.piecesPerKgOrMeter,
        cavityCount: detail.cavityCount,
        materialTotal: detail.totalCost,
        importedMaterialTotal: detail.sourceData?.importedMaterialTotal ?? null,
        sourceData: detail.sourceData || {},
      }));
    const machineRows = details
      .filter((detail: any) => detail.lineType === 'PROCESS')
      .map((detail: any) => ({
        id: detail.id,
        lineCode: detail.lineCode,
        processName: detail.lineName,
        machineId: detail.sourceData?.machineId || '',
        machineName: detail.machineName,
        machineMetric: detail.sourceData?.machineMetric || detail.machineName || '',
        machineHourRate: detail.runningCostPerHour ?? detail.unitRate,
        labourCostPerHour: detail.labourCostPerHour,
        productionPerHour: detail.productionPerHour,
        productionCostPerPiece: detail.productionCostPerPiece,
        quantity: detail.quantity,
        oneStripCuttingCharge: detail.oneStripCuttingCharge,
        onePieceCuttingCharge: detail.onePieceCuttingCharge,
        galvanizingPerKg: detail.galvanizingPerKg,
        galvanizingPerPiece: detail.galvanizingPerPiece,
        total: detail.totalCost,
        sourceData: detail.sourceData || {},
      }));
    const coatingRows = details
      .filter((detail: any) => detail.lineType === 'COATING')
      .map((detail: any) => ({
        id: detail.id,
        lineCode: detail.lineCode,
        coatingType: detail.lineName,
        mode: detail.sourceData?.mode || 'Per KG',
        unitPrice: detail.unitRate,
        quantity: detail.quantity,
        weightKg: detail.sourceData?.weightKg ?? null,
        weightGram: detail.weightGram,
        total: detail.totalCost,
        sourceData: detail.sourceData || {},
      }));

    return {
      id: record.id,
      processCode: record.itemCode,
      processName: record.itemName,
      processType: processTypeFor(kind),
      inch: record.inch || '',
      finishVariant: record.finishVariant,
      plateCodes: record.plateCodes || [],
      materialRows,
      machineRows,
      coatingRows,
      status: record.sourceData?.status || 'Active',
      createdBy: record.sourceData?.createdBy || 'Starline Admin',
      sourceFile: record.sourceFile || '',
      materialTotal: record.materialCost,
      processTotal: record.machineCost,
      coatingTotal: record.coatingCost,
      totalAll: record.totalCost,
    };
  }

  private mapDetails(calculated: CalculatedProcessRecord) {
    let sequence = 0;
    const details: Prisma.SfgCostingDetailCreateWithoutCostingRecordInput[] = [];

    for (const row of calculated.materialRows) {
      sequence += 1;
      details.push({
        lineType: 'MATERIAL',
        sequence,
        lineCode: row.lineCode,
        lineName: row.materialName,
        quantity: parseNullableNumber(row.quantity ?? row.length),
        unitRate: parseNullableNumber(row.standardPrice),
        length: parseNullableNumber(row.length),
        width: parseNullableNumber(row.width),
        thickness: parseNullableNumber(row.thickness),
        weightGram: parseNullableNumber(row.pcsWeightGram),
        cavityCount: parseNullableNumber(row.cavityCount),
        noOfSetsPerSheet: parseNullableNumber(row.noOfSetsPerSheet),
        noOfPcsPerStrip: parseNullableNumber(row.noOfPcsPerStrip),
        oneSheetWeight: parseNullableNumber(row.oneSheetWeight),
        stdPrice: parseNullableNumber(row.standardPrice),
        oneSheetRate: parseNullableNumber(row.oneSheetRate),
        piecesPerKgOrMeter: parseNullableNumber(row.piecesPerKgOrMeter ?? row.noOfPcsPerKg),
        totalCost: row.materialTotal,
        sourceData: {
          ...(row.sourceData || {}),
          rawMaterialId: clean(row.rawMaterialId || ''),
          materialCode: clean(row.materialCode || row.lineCode || row.materialName || ''),
          materialType: row.materialType,
          noOfStrips: parseNullableNumber(row.noOfStrips),
          noOfPcsPerKg: parseNullableNumber(row.noOfPcsPerKg),
          weight: parseNullableNumber(row.weight),
          importedMaterialTotal: parseNullableNumber(row.importedMaterialTotal),
        } as Prisma.InputJsonValue,
      });
    }

    for (const row of calculated.machineRows) {
      sequence += 1;
      details.push({
        lineType: 'PROCESS',
        sequence,
        lineCode: row.lineCode,
        lineName: row.processName,
        machineName: row.machineName || null,
        quantity: parseNullableNumber(row.quantity),
        unitRate: parseNullableNumber(row.machineHourRate),
        runningCostPerHour: parseNullableNumber(row.machineHourRate),
        labourCostPerHour: parseNullableNumber(row.labourCostPerHour),
        productionPerHour: parseNullableNumber(row.productionPerHour),
        productionCostPerPiece: parseNullableNumber(row.productionCostPerPiece),
        oneStripCuttingCharge: parseNullableNumber(row.oneStripCuttingCharge),
        onePieceCuttingCharge: parseNullableNumber(row.onePieceCuttingCharge),
        galvanizingPerKg: parseNullableNumber(row.galvanizingPerKg),
        galvanizingPerPiece: parseNullableNumber(row.galvanizingPerPiece),
        totalCost: row.total,
        sourceData: {
          ...(row.sourceData || {}),
          machineId: clean(row.machineId || ''),
          machineMetric: row.machineMetric || '',
        } as Prisma.InputJsonValue,
      });
    }

    for (const row of calculated.coatingRows) {
      sequence += 1;
      details.push({
        lineType: 'COATING',
        sequence,
        lineCode: row.lineCode || null,
        lineName: row.coatingType,
        quantity: parseNullableNumber(row.quantity),
        unitRate: parseNullableNumber(row.unitPrice),
        weightGram: parseNullableNumber(row.weightGram),
        totalCost: row.total,
        sourceData: {
          ...(row.sourceData || {}),
          mode: row.mode,
          weightKg: parseNullableNumber(row.weightKg),
        } as Prisma.InputJsonValue,
      });
    }

    return details;
  }

  private buildWhere(kind: ProcessModuleKind, query: ProcessListQuery): Prisma.SfgCostingRecordWhereInput {
    const and: Prisma.SfgCostingRecordWhereInput[] = [{ sourceKind: sourceKindFor(kind) }];
    const search = clean(query.search);
    if (search) {
      and.push({
        OR: [
          { itemCode: { contains: search, mode: 'insensitive' } },
          { itemName: { contains: search, mode: 'insensitive' } },
          { details: { some: { lineName: { contains: search, mode: 'insensitive' } } } },
        ],
      });
    }
    if (clean(query.inch)) and.push({ inch: clean(query.inch) });
    if (clean(query.finishVariant)) and.push({ finishVariant: detectFinishVariant(query.finishVariant, clean(query.finishVariant)) });
    if (clean(query.plateCode)) and.push({ plateCodes: { has: clean(query.plateCode) } });
    if (clean(query.status)) and.push({ sourceData: { path: ['status'], equals: normalizeStatus(query.status, 'Active') } });
    return { AND: and };
  }

  private async findExistingRecord(kind: ProcessModuleKind, dto: ProcessRecordWriteDto, tx: any = this.prisma) {
    const code = clean(dto.processCode).toUpperCase();
    const name = clean(dto.processName);
    const finishVariant = detectFinishVariant(dto.finishVariant || `${code} ${name}`);
    if (dto.id) {
      return tx.sfgCostingRecord.findFirst({ where: { id: dto.id, sourceKind: sourceKindFor(kind) } });
    }
    if (code) {
      const byCode = await tx.sfgCostingRecord.findFirst({
        where: { itemCode: code, finishVariant, sourceKind: sourceKindFor(kind) },
      });
      if (byCode) return byCode;
    }
    if (!name) return null;
    return tx.sfgCostingRecord.findFirst({
      where: {
        lookupKey: normalizeLookupKey(name),
        finishVariant,
        sourceKind: sourceKindFor(kind),
      },
    });
  }
}

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

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

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

function parseNullableNumber(value: unknown) {
  const parsed = Number.parseFloat(String(value ?? ''));
  return Number.isFinite(parsed) ? parsed : null;
}

function extractError(error: unknown) {
  if (error instanceof BadRequestException) {
    const response = error.getResponse() as any;
    return Array.isArray(response.message) ? response.message.join(', ') : response.message || error.message;
  }
  if (error instanceof Error) return error.message;
  return 'Import failed.';
}