import {
  BadRequestException,
  Injectable,
  NotFoundException,
} from '@nestjs/common';
import {
  Prisma,
  PurchaseOrder,
  PurchaseOrderItem,
  RawMaterial,
  RawMaterialCategory,
  RawMaterialPriceHistory,
  RawMaterialSettings,
  RawMaterialVendor,
  UnitOfMeasure,
} from '../generated/prisma/index';
import { createHash } from 'crypto';
import { existsSync, mkdirSync, writeFileSync } from 'fs';
import { join } from 'path';
import { connect } from 'tls';
import { inflateRawSync } from 'zlib';
import { PrismaService } from '../prisma/prisma.service';
import {
  ImportCommitDto,
  ImportPreviewDto,
  PurchaseOrderEmailDto,
  PurchaseOrderItemWriteDto,
  PurchaseOrderWriteDto,
  RawMaterialCategoryWriteDto,
  RawMaterialQuery,
  RawMaterialSettingsWriteDto,
  RawMaterialVendorWriteDto,
  RawMaterialWriteDto,
  UnitOfMeasureWriteDto,
} from './raw-material.dto';

const materialSortColumns = new Set([
  'materialCode',
  'materialName',
  'costPerUnit',
  'currentStock',
  'reorderLevel',
  'status',
  'updatedAt',
]);
const categorySortColumns = new Set(['categoryCode', 'categoryName', 'status', 'updatedAt']);
const uomSortColumns = new Set(['uomName', 'uomCode', 'measureType', 'status', 'updatedAt']);
const vendorSortColumns = new Set(['vendorCode', 'vendorName', 'status', 'updatedAt']);
const priceSortColumns = new Set(['effectiveDate', 'oldPrice', 'newPrice', 'currency']);
const purchaseSortColumns = new Set(['poNumber', 'orderDate', 'status', 'totalAmount', 'updatedAt']);

const measureTypes = ['Weight', 'Volume', 'Quantity', 'Length', 'Energy'];
const stockTypes = ['Central Store', 'Local Store'];
const statuses = ['Active', 'Inactive'];
const purchaseStatuses = ['Draft', 'Generated', 'Approved', 'Sent', 'Cancelled'];
const importModules = ['materials', 'categories', 'uoms', 'vendors', 'purchase-orders'];

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

  async listMaterials(query: RawMaterialQuery) {
    const page = this.toPositiveNumber(query.page, 1);
    const pageSize = Math.min(this.toPositiveNumber(query.pageSize, 10), 100);
    const where = this.buildMaterialWhere(query);
    const sortBy = materialSortColumns.has(query.sortBy || '')
      ? (query.sortBy as keyof RawMaterial)
      : 'materialCode';
    const sortOrder = query.sortOrder === 'desc' ? 'desc' : 'asc';

    const [total, items, filterOptions] = await Promise.all([
      this.prisma.rawMaterial.count({ where }),
      this.prisma.rawMaterial.findMany({
        where,
        orderBy: { [sortBy]: sortOrder } as Prisma.RawMaterialOrderByWithRelationInput,
        include: this.materialIncludes(),
        skip: (page - 1) * pageSize,
        take: pageSize,
      }),
      this.getFilterOptions(),
    ]);

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

  async suggestMaterials(search = '') {
    const value = clean(search);
    const items = await this.prisma.rawMaterial.findMany({
      where: value
        ? {
            OR: [
              { materialCode: { contains: value, mode: 'insensitive' } },
              { materialName: { contains: value, mode: 'insensitive' } },
            ],
          }
        : {},
      include: this.materialIncludes(),
      orderBy: { materialName: 'asc' },
      take: 20,
    });

    return items.map(toMaterialView);
  }

  async getMaterial(id: string) {
    const material = await this.prisma.rawMaterial.findUnique({
      where: { id },
      include: {
        ...this.materialIncludes(),
        priceHistory: {
          orderBy: { effectiveDate: 'desc' },
          include: { vendor: true },
        },
      },
    });

    if (!material) {
      throw new NotFoundException('Raw material not found');
    }

    return {
      ...toMaterialView(material),
      priceHistory: material.priceHistory.map(toPriceHistoryView),
    };
  }

  async createMaterial(dto: RawMaterialWriteDto) {
    const data = await this.validateMaterial(dto);
    data.imagePath = this.resolveUploadPath(dto.imageData, dto.imageName, 'raw-materials') || data.imagePath;

    const material = await this.prisma.rawMaterial.create({
      data,
      include: this.materialIncludes(),
    });

    if (material.costPerUnit > 0) {
      await this.createPriceHistory(material, 0, material.costPerUnit, dto, 'Initial price');
    }

    return toMaterialView(material);
  }

  async updateMaterial(id: string, dto: RawMaterialWriteDto) {
    const existing = await this.prisma.rawMaterial.findUnique({ where: { id } });

    if (!existing) {
      throw new NotFoundException('Raw material not found');
    }

    const data = await this.validateMaterial(dto);
    const uploadPath = this.resolveUploadPath(dto.imageData, dto.imageName, 'raw-materials');
    data.imagePath = uploadPath || clean(dto.imagePath) || existing.imagePath || null;
    data.currentStock = existing.currentStock;

    const material = await this.prisma.rawMaterial.update({
      where: { id },
      data,
      include: this.materialIncludes(),
    });

    if (existing.costPerUnit !== material.costPerUnit) {
      await this.createPriceHistory(
        material,
        existing.costPerUnit,
        material.costPerUnit,
        dto,
        'Price updated from raw material edit',
      );
    }

    return toMaterialView(material);
  }

  async deleteMaterial(id: string) {
    await this.ensureMaterial(id);
    await this.prisma.rawMaterial.delete({ where: { id } });
    return { deleted: true };
  }

  async listCategories(query: RawMaterialQuery) {
    const page = this.toPositiveNumber(query.page, 1);
    const pageSize = Math.min(this.toPositiveNumber(query.pageSize, 10), 100);
    const search = clean(query.search);
    const where: Prisma.RawMaterialCategoryWhereInput = {
      ...(search
        ? {
            OR: [
              { categoryCode: { contains: search, mode: 'insensitive' } },
              { categoryName: { contains: search, mode: 'insensitive' } },
              { description: { contains: search, mode: 'insensitive' } },
            ],
          }
        : {}),
      ...(clean(query.status) ? { status: normalizeStatus(query.status) } : {}),
    };
    const sortBy = categorySortColumns.has(query.sortBy || '')
      ? (query.sortBy as keyof RawMaterialCategory)
      : 'categoryCode';
    const sortOrder = query.sortOrder === 'desc' ? 'desc' : 'asc';

    const [total, items] = await Promise.all([
      this.prisma.rawMaterialCategory.count({ where }),
      this.prisma.rawMaterialCategory.findMany({
        where,
        orderBy: { [sortBy]: sortOrder } as Prisma.RawMaterialCategoryOrderByWithRelationInput,
        skip: (page - 1) * pageSize,
        take: pageSize,
      }),
    ]);

    return this.listResponse(items, total, page, pageSize);
  }

  async createCategory(dto: RawMaterialCategoryWriteDto) {
    return this.prisma.rawMaterialCategory.create({ data: this.validateCategory(dto) });
  }

  async updateCategory(id: string, dto: RawMaterialCategoryWriteDto) {
    await this.ensureCategory(id);
    return this.prisma.rawMaterialCategory.update({ where: { id }, data: this.validateCategory(dto) });
  }

  async deleteCategory(id: string) {
    await this.ensureCategory(id);
    await this.prisma.rawMaterialCategory.delete({ where: { id } });
    return { deleted: true };
  }

  async listUoms(query: RawMaterialQuery) {
    const page = this.toPositiveNumber(query.page, 1);
    const pageSize = Math.min(this.toPositiveNumber(query.pageSize, 10), 100);
    const search = clean(query.search);
    const where: Prisma.UnitOfMeasureWhereInput = {
      ...(search
        ? {
            OR: [
              { uomName: { contains: search, mode: 'insensitive' } },
              { uomCode: { contains: search, mode: 'insensitive' } },
              { measureType: { contains: search, mode: 'insensitive' } },
            ],
          }
        : {}),
      ...(clean(query.status) ? { status: normalizeStatus(query.status) } : {}),
    };
    const sortBy = uomSortColumns.has(query.sortBy || '')
      ? (query.sortBy as keyof UnitOfMeasure)
      : 'uomCode';
    const sortOrder = query.sortOrder === 'desc' ? 'desc' : 'asc';

    const [total, items] = await Promise.all([
      this.prisma.unitOfMeasure.count({ where }),
      this.prisma.unitOfMeasure.findMany({
        where,
        orderBy: { [sortBy]: sortOrder } as Prisma.UnitOfMeasureOrderByWithRelationInput,
        skip: (page - 1) * pageSize,
        take: pageSize,
      }),
    ]);

    return this.listResponse(items, total, page, pageSize);
  }

  async createUom(dto: UnitOfMeasureWriteDto) {
    return this.prisma.unitOfMeasure.create({ data: this.validateUom(dto) });
  }

  async updateUom(id: string, dto: UnitOfMeasureWriteDto) {
    await this.ensureUom(id);
    return this.prisma.unitOfMeasure.update({ where: { id }, data: this.validateUom(dto) });
  }

  async deleteUom(id: string) {
    await this.ensureUom(id);
    await this.prisma.unitOfMeasure.delete({ where: { id } });
    return { deleted: true };
  }

  async listVendors(query: RawMaterialQuery) {
    const page = this.toPositiveNumber(query.page, 1);
    const pageSize = Math.min(this.toPositiveNumber(query.pageSize, 10), 100);
    const search = clean(query.search);
    const where: Prisma.RawMaterialVendorWhereInput = {
      ...(search
        ? {
            OR: [
              { vendorCode: { contains: search, mode: 'insensitive' } },
              { vendorName: { contains: search, mode: 'insensitive' } },
              { phoneNumber: { contains: search, mode: 'insensitive' } },
              { whatsappNumber: { contains: search, mode: 'insensitive' } },
              { email: { contains: search, mode: 'insensitive' } },
              { gstNumber: { contains: search, mode: 'insensitive' } },
              { locationPlace: { contains: search, mode: 'insensitive' } },
              { locationPincode: { contains: search, mode: 'insensitive' } },
            ],
          }
        : {}),
      ...(clean(query.status) ? { status: normalizeStatus(query.status) } : {}),
    };
    const sortBy = vendorSortColumns.has(query.sortBy || '')
      ? (query.sortBy as keyof RawMaterialVendor)
      : 'vendorCode';
    const sortOrder = query.sortOrder === 'desc' ? 'desc' : 'asc';

    const [total, items] = await Promise.all([
      this.prisma.rawMaterialVendor.count({ where }),
      this.prisma.rawMaterialVendor.findMany({
        where,
        orderBy: { [sortBy]: sortOrder } as Prisma.RawMaterialVendorOrderByWithRelationInput,
        skip: (page - 1) * pageSize,
        take: pageSize,
      }),
    ]);

    return this.listResponse(items.map(toVendorView), total, page, pageSize);
  }

  async createVendor(dto: RawMaterialVendorWriteDto) {
    return toVendorView(await this.prisma.rawMaterialVendor.create({ data: this.validateVendor(dto) }));
  }

  async updateVendor(id: string, dto: RawMaterialVendorWriteDto) {
    await this.ensureVendor(id);
    return toVendorView(await this.prisma.rawMaterialVendor.update({ where: { id }, data: this.validateVendor(dto) }));
  }

  async deleteVendor(id: string) {
    await this.ensureVendor(id);
    await this.prisma.rawMaterialVendor.delete({ where: { id } });
    return { deleted: true };
  }

  async listPriceHistory(query: RawMaterialQuery & { rawMaterialId?: string }) {
    const page = this.toPositiveNumber(query.page, 1);
    const pageSize = Math.min(this.toPositiveNumber(query.pageSize, 10), 100);
    const search = clean(query.search);
    const where: Prisma.RawMaterialPriceHistoryWhereInput = {
      ...(clean(query.rawMaterialId) ? { rawMaterialId: clean(query.rawMaterialId) } : {}),
      ...(clean(query.vendorId) ? { vendorId: clean(query.vendorId) } : {}),
      ...(search
        ? {
            OR: [
              { rawMaterial: { materialCode: { contains: search, mode: 'insensitive' } } },
              { rawMaterial: { materialName: { contains: search, mode: 'insensitive' } } },
              { vendor: { vendorName: { contains: search, mode: 'insensitive' } } },
              { remarks: { contains: search, mode: 'insensitive' } },
            ],
          }
        : {}),
    };
    const sortBy = priceSortColumns.has(query.sortBy || '') ? query.sortBy || 'effectiveDate' : 'effectiveDate';
    const sortOrder = query.sortOrder === 'asc' ? 'asc' : 'desc';

    const [total, items] = await Promise.all([
      this.prisma.rawMaterialPriceHistory.count({ where }),
      this.prisma.rawMaterialPriceHistory.findMany({
        where,
        include: { rawMaterial: true, vendor: true },
        orderBy: { [sortBy]: sortOrder } as Prisma.RawMaterialPriceHistoryOrderByWithRelationInput,
        skip: (page - 1) * pageSize,
        take: pageSize,
      }),
    ]);

    return this.listResponse(items.map(toPriceHistoryView), total, page, pageSize);
  }

  async listPurchaseOrders(query: RawMaterialQuery) {
    const page = this.toPositiveNumber(query.page, 1);
    const pageSize = Math.min(this.toPositiveNumber(query.pageSize, 10), 100);
    const where = this.buildPurchaseOrderWhere(query);
    const sortBy = purchaseSortColumns.has(query.sortBy || '')
      ? (query.sortBy as keyof PurchaseOrder)
      : 'orderDate';
    const sortOrder = query.sortOrder === 'asc' ? 'asc' : 'desc';

    const [total, items] = await Promise.all([
      this.prisma.purchaseOrder.count({ where }),
      this.prisma.purchaseOrder.findMany({
        where,
        include: { vendor: true, items: { include: { rawMaterial: true, uom: true } } },
        orderBy: { [sortBy]: sortOrder } as Prisma.PurchaseOrderOrderByWithRelationInput,
        skip: (page - 1) * pageSize,
        take: pageSize,
      }),
    ]);

    return this.listResponse(items.map(toPurchaseOrderView), total, page, pageSize);
  }

  async getPurchaseOrder(id: string) {
    const order = await this.prisma.purchaseOrder.findUnique({
      where: { id },
      include: { vendor: true, items: { include: { rawMaterial: true, uom: true } } },
    });

    if (!order) {
      throw new NotFoundException('Purchase order not found');
    }

    return { ...toPurchaseOrderView(order), settings: await this.getSettings() };
  }

  async createPurchaseOrder(dto: PurchaseOrderWriteDto) {
    const prepared = await this.validatePurchaseOrder(dto);
    const order = await this.prisma.purchaseOrder.create({
      data: prepared.data,
      include: { vendor: true, items: { include: { rawMaterial: true, uom: true } } },
    });

    await this.refreshMaterialStocks(prepared.materialIds);
    return toPurchaseOrderView(order);
  }

  async updatePurchaseOrder(id: string, dto: PurchaseOrderWriteDto) {
    const existing = await this.prisma.purchaseOrder.findUnique({
      where: { id },
      include: { items: true },
    });

    if (!existing) {
      throw new NotFoundException('Purchase order not found');
    }

    const prepared = await this.validatePurchaseOrder(dto, existing.poNumber);
    const oldMaterialIds = existing.items.map((item) => item.rawMaterialId).filter(Boolean) as string[];
    const order = await this.prisma.purchaseOrder.update({
      where: { id },
      data: {
        ...prepared.data,
        poNumber: existing.poNumber,
        items: {
          deleteMany: {},
          create: prepared.itemCreates,
        },
      },
      include: { vendor: true, items: { include: { rawMaterial: true, uom: true } } },
    });

    await this.refreshMaterialStocks([...oldMaterialIds, ...prepared.materialIds]);
    return toPurchaseOrderView(order);
  }

  async deletePurchaseOrder(id: string) {
    const existing = await this.prisma.purchaseOrder.findUnique({ where: { id }, include: { items: true } });
    if (!existing) {
      throw new NotFoundException('Purchase order not found');
    }
    await this.prisma.purchaseOrder.delete({ where: { id } });
    await this.refreshMaterialStocks(existing.items.map((item) => item.rawMaterialId).filter(Boolean) as string[]);
    return { deleted: true };
  }

  async generatePurchaseOrder(id: string) {
    const order = await this.prisma.purchaseOrder.update({
      where: { id },
      data: { status: 'Generated', generatedAt: new Date() },
      include: { vendor: true, items: { include: { rawMaterial: true, uom: true } } },
    });
    return toPurchaseOrderView(order);
  }

  async convertPurchaseOrderToStock(id: string, dto: { convertedBy?: string }) {
    const existing = await this.prisma.purchaseOrder.findUnique({
      where: { id },
      include: { items: true },
    });

    if (!existing) {
      throw new NotFoundException('Purchase order not found');
    }
    if (existing.stockConvertedAt) {
      throw new BadRequestException(['This purchase order is already converted to stock.']);
    }
    if (!['Generated', 'Approved', 'Sent'].includes(existing.status)) {
      throw new BadRequestException(['Only generated, approved, or sent purchase orders can be converted to stock.']);
    }

    const order = await this.prisma.purchaseOrder.update({
      where: { id },
      data: {
        status: existing.status === 'Generated' ? 'Approved' : existing.status,
        stockConvertedAt: new Date(),
        stockConvertedBy: clean(dto?.convertedBy) || 'Starline Admin',
      },
      include: { vendor: true, items: { include: { rawMaterial: true, uom: true } } },
    });
    await this.refreshMaterialStocks(existing.items.map((item) => item.rawMaterialId).filter(Boolean) as string[]);
    return toPurchaseOrderView(order);
  }

  async sendPurchaseOrderEmail(id: string, dto: PurchaseOrderEmailDto) {
    const order = await this.getPurchaseOrder(id);
    const settings = await this.getSettings();
    const to = clean(dto.to) || order.vendor?.email || '';
    const subject = clean(dto.subject) || `Purchase Order ${order.poNumber}`;
    const cc = clean(dto.cc) || settings.defaultCc || '';
    const message = clean(dto.message) || `Please find purchase order ${order.poNumber}.`;

    if (!to) {
      throw new BadRequestException(['Recipient email is required.']);
    }

    await sendSmtpMail(settings, {
      to,
      cc,
      subject,
      text: `${message}\n\nTotal: ${order.currencyLabel}`,
      html: buildPurchaseOrderEmailHtml(order, message, settings),
    });

    await this.prisma.purchaseOrder.update({
      where: { id },
      data: {
        status: order.status === 'Draft' ? 'Sent' : order.status,
        emailSubject: subject,
        emailTo: to,
        emailCc: cc,
        emailMessage: message,
        emailSentAt: new Date(),
      },
    });

    return { sent: true };
  }

  async getSettings() {
    return this.prisma.rawMaterialSettings.upsert({
      where: { key: 'raw_material' },
      update: {},
      create: {
        key: 'raw_material',
        smtpHost: 'mail.castorwheel.co',
        smtpPort: 465,
        smtpSecure: true,
        smtpUsername: 'castorwheel@castorwheel.co',
        smtpPassword: 'nadathara#123#wheel',
        defaultSenderEmail: 'castorwheel@castorwheel.co',
        defaultSenderName: 'Castorwheel Support',
        defaultCc: 'accounts@starlinecastors.com',
        billHeader: 'STARLINE CASTORS - PURCHASE ORDER',
        billFooter: 'Thank you for supporting Starline purchase operations.',
        processCodeAutoGenerate: true,
        processCodePrefix: 'PRC',
      },
    });
  }

  async updateSettings(dto: RawMaterialSettingsWriteDto) {
    const logoPath = this.resolveUploadPath(dto.logoData, dto.logoName, 'raw-material-settings');
    return this.prisma.rawMaterialSettings.upsert({
      where: { key: 'raw_material' },
      update: {
        smtpHost: clean(dto.smtpHost) || null,
        smtpPort: this.toPositiveNumber(String(dto.smtpPort ?? ''), 465),
        smtpSecure: parseBoolean(dto.smtpSecure, true),
        smtpUsername: clean(dto.smtpUsername) || null,
        smtpPassword: clean(dto.smtpPassword) || null,
        defaultSenderEmail: clean(dto.defaultSenderEmail).toLowerCase() || null,
        defaultSenderName: clean(dto.defaultSenderName) || 'Castorwheel Support',
        defaultCc: clean(dto.defaultCc) || null,
        billHeader: clean(dto.billHeader) || null,
        billFooter: clean(dto.billFooter) || null,
        logoPath: logoPath || clean(dto.logoPath) || null,
        processCodeAutoGenerate: parseBoolean(dto.processCodeAutoGenerate, true),
        processCodePrefix: clean(dto.processCodePrefix).toUpperCase() || 'PRC',
      },
      create: {
        key: 'raw_material',
        smtpHost: clean(dto.smtpHost) || null,
        smtpPort: this.toPositiveNumber(String(dto.smtpPort ?? ''), 465),
        smtpSecure: parseBoolean(dto.smtpSecure, true),
        smtpUsername: clean(dto.smtpUsername) || null,
        smtpPassword: clean(dto.smtpPassword) || null,
        defaultSenderEmail: clean(dto.defaultSenderEmail).toLowerCase() || null,
        defaultSenderName: clean(dto.defaultSenderName) || 'Castorwheel Support',
        defaultCc: clean(dto.defaultCc) || null,
        billHeader: clean(dto.billHeader) || null,
        billFooter: clean(dto.billFooter) || null,
        logoPath: logoPath || clean(dto.logoPath) || null,
        processCodeAutoGenerate: parseBoolean(dto.processCodeAutoGenerate, true),
        processCodePrefix: clean(dto.processCodePrefix).toUpperCase() || 'PRC',
      },
    });
  }

  async getOptions() {
    return this.getFilterOptions();
  }

  async exportModule(moduleName: string, query: RawMaterialQuery & { format?: string }) {
    this.ensureImportModule(moduleName);
    const rows = await this.getExportRows(moduleName, query);
    const format = clean(query.format).toLowerCase() === 'csv' ? 'csv' : 'xlsx';
    const date = new Date().toISOString().slice(0, 10);
    const safeName = moduleName.replace(/-/g, '_');

    if (format === 'csv') {
      const content = toCsv(rows);
      return {
        fileName: `${safeName}_${date}.csv`,
        mime: 'text/csv',
        base64: Buffer.from(content, 'utf8').toString('base64'),
      };
    }

    return {
      fileName: `${safeName}_${date}.xlsx`,
      mime: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
      base64: createXlsx(rows, safeName).toString('base64'),
    };
  }

  async previewImport(moduleName: string, dto: ImportPreviewDto) {
    this.ensureImportModule(moduleName);
    const parsedRows = parseImportRows(dto.fileName || '', dto.content || '');
    const rows = parsedRows.map((row, index) => this.validateImportRow(moduleName, row, index));

    return {
      fileName: dto.fileName || '',
      rows,
      total: rows.length,
      valid: rows.filter((row) => row.valid).length,
      invalid: rows.filter((row) => !row.valid).length,
    };
  }

  async commitImport(moduleName: string, dto: ImportCommitDto) {
    this.ensureImportModule(moduleName);
    const rows = (dto.rows || []).filter(Boolean);
    let created = 0;
    const errors: string[] = [];

    for (const [index, row] of rows.entries()) {
      try {
        await this.createFromImport(moduleName, row);
        created += 1;
      } catch (error) {
        errors.push(`Row ${index + 1}: ${extractErrorMessage(error)}`);
      }
    }

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

  private async getFilterOptions() {
    const [categories, uoms, vendors, settings] = await Promise.all([
      this.prisma.rawMaterialCategory.findMany({
        where: { status: 'Active' },
        orderBy: { categoryName: 'asc' },
      }),
      this.prisma.unitOfMeasure.findMany({
        where: { status: 'Active' },
        orderBy: { uomCode: 'asc' },
      }),
      this.prisma.rawMaterialVendor.findMany({
        where: { status: 'Active' },
        orderBy: { vendorName: 'asc' },
      }),
      this.getSettings(),
    ]);

    return {
      categories,
      uoms,
      vendors: vendors.map(toVendorView),
      statuses,
      stockTypes,
      measureTypes,
      currencies: ['INR', 'USD', 'EUR'],
      purchaseStatuses,
      settings,
    };
  }

  private buildMaterialWhere(query: RawMaterialQuery): Prisma.RawMaterialWhereInput {
    const and: Prisma.RawMaterialWhereInput[] = [];
    const search = clean(query.search);

    if (search) {
      and.push({
        OR: [
          { materialCode: { contains: search, mode: 'insensitive' } },
          { materialName: { contains: search, mode: 'insensitive' } },
          { subType: { contains: search, mode: 'insensitive' } },
          { hsnCode: { contains: search, mode: 'insensitive' } },
          { taxCategory: { contains: search, mode: 'insensitive' } },
          { batchNumber: { contains: search, mode: 'insensitive' } },
          { qualityGrade: { contains: search, mode: 'insensitive' } },
          { accountLevelLink: { contains: search, mode: 'insensitive' } },
          { category: { categoryName: { contains: search, mode: 'insensitive' } } },
          { vendor: { vendorName: { contains: search, mode: 'insensitive' } } },
          { uom: { uomCode: { contains: search, mode: 'insensitive' } } },
        ],
      });
    }

    if (clean(query.categoryId)) and.push({ categoryId: clean(query.categoryId) });
    if (clean(query.vendorId)) and.push({ vendorId: clean(query.vendorId) });
    if (clean(query.uomId)) and.push({ uomId: clean(query.uomId) });
    if (clean(query.stockType)) and.push({ stockType: clean(query.stockType) });
    if (clean(query.status)) and.push({ status: normalizeStatus(query.status) });

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

  private buildPurchaseOrderWhere(query: RawMaterialQuery): Prisma.PurchaseOrderWhereInput {
    const and: Prisma.PurchaseOrderWhereInput[] = [];
    const search = clean(query.search);

    if (search) {
      and.push({
        OR: [
          { poNumber: { contains: search, mode: 'insensitive' } },
          { status: { contains: search, mode: 'insensitive' } },
          { vendor: { vendorName: { contains: search, mode: 'insensitive' } } },
          { items: { some: { rawMaterial: { materialName: { contains: search, mode: 'insensitive' } } } } },
        ],
      });
    }

    if (clean(query.vendorId)) and.push({ vendorId: clean(query.vendorId) });
    if (clean(query.status)) and.push({ status: clean(query.status) });
    if (clean(query.fromDate) || clean(query.toDate)) {
      and.push({
        orderDate: {
          ...(clean(query.fromDate) ? { gte: new Date(clean(query.fromDate)) } : {}),
          ...(clean(query.toDate) ? { lte: endOfDay(clean(query.toDate)) } : {}),
        },
      });
    }

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

  private async validateMaterial(dto: RawMaterialWriteDto): Promise<Prisma.RawMaterialUncheckedCreateInput> {
    const gstPercent = parseNumber(dto.gstPercent, 0);
    const costPerUnit = parseNumber(dto.costPerUnit, 0);
    const reorderLevel = parseNumber(dto.reorderLevel, 0);
    const reorderQuantity = parseNumber(dto.reorderQuantity, 0);
    const lossWastePercent = parseNumber(dto.lossWastePercent, 0);
    const data: Prisma.RawMaterialUncheckedCreateInput = {
      materialCode: clean(dto.materialCode).toUpperCase(),
      materialName: clean(dto.materialName),
      categoryId: clean(dto.categoryId) || null,
      subType: clean(dto.subType) || null,
      description: clean(dto.description) || null,
      uomId: clean(dto.uomId) || null,
      hsnCode: clean(dto.hsnCode) || null,
      taxCategory: clean(dto.taxCategory) || null,
      gstPercent,
      vendorId: clean(dto.vendorId) || null,
      costPerUnit,
      currency: clean(dto.currency).toUpperCase() || 'INR',
      batchNumber: clean(dto.batchNumber) || null,
      qualityGrade: clean(dto.qualityGrade) || null,
      specificationStandard: clean(dto.specificationStandard) || null,
      storageConditions: clean(dto.storageConditions) || null,
      currentStock: 0,
      reorderLevel,
      reorderQuantity,
      stockType: stockTypes.includes(clean(dto.stockType)) ? clean(dto.stockType) : 'Central Store',
      lossWastePercent,
      accountLevelLink: clean(dto.accountLevelLink) || null,
      imagePath: clean(dto.imagePath) || null,
      status: normalizeStatus(dto.status),
    };

    const errors: string[] = [];
    if (!data.materialCode) errors.push('Material code is required.');
    if (!data.materialName) errors.push('Material name is required.');
    if (costPerUnit < 0) errors.push('Cost per unit cannot be negative.');
    if (gstPercent < 0) errors.push('GST percent cannot be negative.');
    if (reorderLevel < 0 || reorderQuantity < 0) errors.push('Reorder level and reorder quantity cannot be negative.');
    if (lossWastePercent < 0 || lossWastePercent > 100) errors.push('Loss / Waste % must be between 0 and 100.');

    if (data.categoryId) await this.ensureCategory(data.categoryId);
    if (data.uomId) await this.ensureUom(data.uomId);
    if (data.vendorId) await this.ensureVendor(data.vendorId);

    if (errors.length) throw new BadRequestException(errors);
    return data;
  }

  private validateCategory(dto: RawMaterialCategoryWriteDto): Prisma.RawMaterialCategoryCreateInput {
    const data = {
      categoryCode: clean(dto.categoryCode).toUpperCase() || null,
      categoryName: clean(dto.categoryName).toUpperCase(),
      description: clean(dto.description) || null,
      status: normalizeStatus(dto.status),
    };

    const errors: string[] = [];
    if (!data.categoryCode) errors.push('Category code is required.');
    if (!data.categoryName) errors.push('Category name is required.');
    if (errors.length) throw new BadRequestException(errors);

    return data;
  }

  private validateUom(dto: UnitOfMeasureWriteDto): Prisma.UnitOfMeasureCreateInput {
    const data = {
      uomName: clean(dto.uomName),
      uomCode: clean(dto.uomCode).toUpperCase(),
      measureType: measureTypes.includes(clean(dto.measureType)) ? clean(dto.measureType) : 'Quantity',
      conversionFactor: parseNumber(dto.conversionFactor, 1),
      status: normalizeStatus(dto.status),
    };

    const errors: string[] = [];
    if (!data.uomName) errors.push('UOM name is required.');
    if (!data.uomCode) errors.push('UOM code is required.');
    if (data.conversionFactor <= 0) errors.push('Conversion factor must be greater than zero.');
    if (errors.length) throw new BadRequestException(errors);

    return data;
  }

  private validateVendor(dto: RawMaterialVendorWriteDto): Prisma.RawMaterialVendorCreateInput {
    const latitude = parseOptionalNumber(dto.latitude);
    const longitude = parseOptionalNumber(dto.longitude);
    const mapLocationUrl =
      clean(dto.mapLocationUrl) ||
      (latitude !== null && longitude !== null ? `https://www.openstreetmap.org/?mlat=${latitude}&mlon=${longitude}#map=16/${latitude}/${longitude}` : null);
    const data = {
      vendorCode: clean(dto.vendorCode).toUpperCase(),
      vendorName: clean(dto.vendorName),
      address: clean(dto.address) || null,
      phoneNumber: clean(dto.phoneNumber) || null,
      whatsappNumber: clean(dto.whatsappNumber) || clean(dto.phoneNumber) || null,
      email: clean(dto.email).toLowerCase() || null,
      gstNumber: clean(dto.gstNumber).toUpperCase() || null,
      mapLocationUrl,
      locationPlace: clean(dto.locationPlace) || null,
      locationPincode: clean(dto.locationPincode) || null,
      latitude,
      longitude,
      status: normalizeStatus(dto.status),
    };

    const errors: string[] = [];
    if (!data.vendorCode) errors.push('Vendor code is required.');
    if (!data.vendorName) errors.push('Vendor name is required.');
    if (data.email && !/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(data.email)) errors.push('Vendor email is invalid.');
    if (errors.length) throw new BadRequestException(errors);

    return data;
  }

  private async validatePurchaseOrder(dto: PurchaseOrderWriteDto, existingPoNumber?: string) {
    if (clean(dto.vendorId)) await this.ensureVendor(clean(dto.vendorId));
    const items = await this.preparePurchaseItems(dto.items || []);
    const subtotal = items.reduce((sum, item) => sum + item.lineSubtotal, 0);
    const gstTotal = items.reduce((sum, item) => sum + item.lineGst, 0);
    const transportationCost = parseNumber(dto.transportationCost, 0);
    const totalAmount = subtotal + gstTotal + transportationCost;
    const epassRequired = totalAmount > 50000;
    const epassFilePath = this.resolveUploadPath(dto.epassData, dto.epassName, 'purchase-epass') || clean(dto.epassFilePath) || null;
    const status = purchaseStatuses.includes(clean(dto.status)) ? clean(dto.status) : 'Draft';

    const errors: string[] = [];
    if (!clean(dto.vendorId)) errors.push('Vendor is required.');
    if (!items.length) errors.push('At least one purchase item is required.');
    if (transportationCost < 0) errors.push('Transportation cost cannot be negative.');
    if (epassRequired && !epassFilePath) errors.push('E-Pass upload is required when total amount is above 50,000.');
    if (errors.length) throw new BadRequestException(errors);

    const poNumber = existingPoNumber || clean(dto.poNumber).toUpperCase() || (await this.nextPurchaseOrderNumber());
    const itemCreates = items.map(({ rawMaterialId, uomId, description, quantity, rate, gstPercent, lineSubtotal, lineGst, lineTotal }) => ({
      rawMaterialId,
      uomId,
      description,
      quantity,
      rate,
      gstPercent,
      lineSubtotal,
      lineGst,
      lineTotal,
    }));

    return {
      materialIds: items.map((item) => item.rawMaterialId).filter(Boolean) as string[],
      itemCreates,
      data: {
        poNumber,
        vendorId: clean(dto.vendorId),
        orderDate: clean(dto.orderDate) ? new Date(clean(dto.orderDate)) : new Date(),
        expectedDate: clean(dto.expectedDate) ? new Date(clean(dto.expectedDate)) : null,
        status,
        subtotal,
        gstTotal,
        transportationCost,
        totalAmount,
        transportDetails: clean(dto.transportDetails) || null,
        epassRequired,
        epassFilePath,
        notes: clean(dto.notes) || null,
        createdBy: clean(dto.createdBy) || 'Starline Admin',
        items: { create: itemCreates },
      },
    };
  }

  private async preparePurchaseItems(items: PurchaseOrderItemWriteDto[]) {
    const prepared: Array<{
      rawMaterialId: string | null;
      uomId: string | null;
      description: string | null;
      quantity: number;
      rate: number;
      gstPercent: number;
      lineSubtotal: number;
      lineGst: number;
      lineTotal: number;
    }> = [];

    for (const [index, item] of items.entries()) {
      const rawMaterialId = clean(item.rawMaterialId);
      const quantity = parseNumber(item.quantity, 0);
      const material = rawMaterialId
        ? await this.prisma.rawMaterial.findUnique({ where: { id: rawMaterialId } })
        : null;
      if (!material) throw new BadRequestException([`Item ${index + 1}: Raw material is required.`]);
      const rate = parseNumber(item.rate, material.costPerUnit);
      const gstPercent = parseNumber(item.gstPercent, material.gstPercent);
      if (quantity <= 0) throw new BadRequestException([`Item ${index + 1}: Quantity must be greater than zero.`]);
      if (rate < 0) throw new BadRequestException([`Item ${index + 1}: Rate cannot be negative.`]);
      const lineSubtotal = quantity * rate;
      const lineGst = (lineSubtotal * gstPercent) / 100;
      prepared.push({
        rawMaterialId,
        uomId: clean(item.uomId) || material.uomId || null,
        description: clean(item.description) || material.materialName,
        quantity,
        rate,
        gstPercent,
        lineSubtotal,
        lineGst,
        lineTotal: lineSubtotal + lineGst,
      });
    }

    return prepared;
  }

  private async nextPurchaseOrderNumber() {
    const count = await this.prisma.purchaseOrder.count();
    return `PO-${String(count + 1).padStart(5, '0')}`;
  }

  private async refreshMaterialStocks(materialIds: string[]) {
    const uniqueIds = [...new Set(materialIds.filter(Boolean))];
    for (const id of uniqueIds) {
      const aggregate = await this.prisma.purchaseOrderItem.aggregate({
        where: {
          rawMaterialId: id,
          purchaseOrder: {
            status: { not: 'Cancelled' },
            stockConvertedAt: { not: null },
          },
        },
        _sum: { quantity: true },
      });

      await this.prisma.rawMaterial.update({
        where: { id },
        data: { currentStock: aggregate._sum.quantity || 0 },
      });
    }
  }

  private async createPriceHistory(
    material: RawMaterial,
    oldPrice: number,
    newPrice: number,
    dto: RawMaterialWriteDto,
    fallbackRemark: string,
  ) {
    await this.prisma.rawMaterialPriceHistory.create({
      data: {
        rawMaterialId: material.id,
        vendorId: material.vendorId,
        oldPrice,
        newPrice,
        currency: material.currency,
        updatedBy: clean(dto.updatedBy) || 'System',
        remarks: clean(dto.remarks) || fallbackRemark,
      },
    });
  }

  private async getExportRows(moduleName: string, query: RawMaterialQuery) {
    if (moduleName === 'materials') {
      const response = await this.listMaterials({ ...query, page: '1', pageSize: '10000' });
      return response.items.map((item) => ({
        'Material Code': item.materialCode,
        'Material Name': item.materialName,
        Category: item.categoryName,
        'Sub-Type': item.subType || '',
        UOM: item.uomCode,
        'HSN Code': item.hsnCode || '',
        'GST %': item.gstPercent,
        Vendor: item.vendorName,
        'Cost per Unit': item.costPerUnit,
        Currency: item.currency,
        'Current Stock': item.currentStock,
        'Reorder Level': item.reorderLevel,
        'Stock Type': item.stockType,
        Status: item.status,
      }));
    }
    if (moduleName === 'categories') {
      const response = await this.listCategories({ ...query, page: '1', pageSize: '10000' });
      return response.items as Record<string, unknown>[];
    }
    if (moduleName === 'uoms') {
      const response = await this.listUoms({ ...query, page: '1', pageSize: '10000' });
      return response.items as Record<string, unknown>[];
    }
    if (moduleName === 'vendors') {
      const response = await this.listVendors({ ...query, page: '1', pageSize: '10000' });
      return response.items as Record<string, unknown>[];
    }

    const response = await this.listPurchaseOrders({ ...query, page: '1', pageSize: '10000' });
    return response.items.map((item: any) => ({
      'PO Number': item.poNumber,
      Vendor: item.vendorName,
      Date: item.orderDate,
      Status: item.status,
      Subtotal: item.subtotal,
      GST: item.gstTotal,
      Transportation: item.transportationCost,
      Total: item.totalAmount,
      'E-Pass Required': item.epassRequired ? 'Yes' : 'No',
    }));
  }

  private validateImportRow(moduleName: string, row: Record<string, string>, index: number) {
    const errors: string[] = [];
    const normalized = normalizeRow(row);

    if (moduleName === 'materials') {
      if (!normalized.materialCode) errors.push('Material Code is required');
      if (!normalized.materialName) errors.push('Material Name is required');
    }
    if (moduleName === 'categories') {
      if (!normalized.categoryCode) errors.push('Category Code is required');
      if (!normalized.categoryName) errors.push('Category Name is required');
    }
    if (moduleName === 'uoms') {
      if (!normalized.uomCode) errors.push('UOM Code is required');
      if (!normalized.uomName) errors.push('UOM Name is required');
    }
    if (moduleName === 'vendors') {
      if (!normalized.vendorCode) errors.push('Vendor Code is required');
      if (!normalized.vendorName) errors.push('Vendor Name is required');
    }
    if (moduleName === 'purchase-orders') {
      if (!normalized.vendorCode && !normalized.vendorName) errors.push('Vendor Code or Vendor Name is required');
      if (!normalized.materialCode) errors.push('Material Code is required');
      if (!normalized.quantity) errors.push('Quantity is required');
    }

    return { rowNumber: index + 1, data: normalized, valid: errors.length === 0, errors };
  }

  private async createFromImport(moduleName: string, row: Record<string, unknown>) {
    const data = normalizeRow(row as Record<string, string>);
    if (moduleName === 'categories') {
      if (await this.prisma.rawMaterialCategory.findFirst({ where: { OR: [{ categoryCode: data.categoryCode }, { categoryName: data.categoryName }] } })) {
        throw new BadRequestException(['Category already exists.']);
      }
      return this.createCategory(data);
    }
    if (moduleName === 'uoms') {
      if (await this.prisma.unitOfMeasure.findFirst({ where: { OR: [{ uomCode: data.uomCode }, { uomName: data.uomName }] } })) {
        throw new BadRequestException(['UOM already exists.']);
      }
      return this.createUom(data);
    }
    if (moduleName === 'vendors') {
      if (await this.prisma.rawMaterialVendor.findUnique({ where: { vendorCode: data.vendorCode } })) {
        throw new BadRequestException(['Vendor already exists.']);
      }
      return this.createVendor(data);
    }
    if (moduleName === 'purchase-orders') {
      const vendor = await this.findVendorForImport(data);
      const material = await this.prisma.rawMaterial.findUnique({ where: { materialCode: data.materialCode } });
      if (!vendor || !material) throw new BadRequestException(['Vendor or material not found.']);
      return this.createPurchaseOrder({
        vendorId: vendor.id,
        transportationCost: data.transportationCost,
        items: [{ rawMaterialId: material.id, quantity: data.quantity, rate: data.rate || material.costPerUnit }],
      });
    }

    if (await this.prisma.rawMaterial.findUnique({ where: { materialCode: data.materialCode } })) {
      throw new BadRequestException(['Material code already exists.']);
    }
    const category = await this.findOrCreateImportCategory(data);
    const uom = await this.findOrCreateImportUom(data);
    const vendor = await this.findVendorForImport(data);
    return this.createMaterial({
      ...data,
      categoryId: category?.id,
      uomId: uom?.id,
      vendorId: vendor?.id,
      costPerUnit: data.costPerUnit || data.rate || '0',
      gstPercent: data.gstPercent || '0',
    });
  }

  private async findOrCreateImportCategory(data: Record<string, string>) {
    if (!data.categoryCode && !data.categoryName) return null;
    const existing = await this.prisma.rawMaterialCategory.findFirst({
      where: {
        OR: [
          ...(data.categoryCode ? [{ categoryCode: data.categoryCode }] : []),
          ...(data.categoryName ? [{ categoryName: data.categoryName.toUpperCase() }] : []),
        ],
      },
    });
    if (existing) return existing;
    return this.prisma.rawMaterialCategory.create({
      data: {
        categoryCode: data.categoryCode || `CAT-${Date.now()}`,
        categoryName: (data.categoryName || data.categoryCode || 'RAW MATERIAL').toUpperCase(),
        status: 'Active',
      },
    });
  }

  private async findOrCreateImportUom(data: Record<string, string>) {
    const code = (data.uomCode || data.uom || 'UNIT').toUpperCase();
    const existing = await this.prisma.unitOfMeasure.findUnique({ where: { uomCode: code } });
    if (existing) return existing;
    return this.prisma.unitOfMeasure.create({
      data: { uomCode: code, uomName: data.uomName || code, measureType: data.measureType || 'Quantity', conversionFactor: 1, status: 'Active' },
    });
  }

  private async findVendorForImport(data: Record<string, string>) {
    if (!data.vendorCode && !data.vendorName) return null;
    return this.prisma.rawMaterialVendor.findFirst({
      where: {
        OR: [
          ...(data.vendorCode ? [{ vendorCode: data.vendorCode }] : []),
          ...(data.vendorName ? [{ vendorName: { equals: data.vendorName, mode: 'insensitive' as const } }] : []),
        ],
      },
    });
  }

  private resolveUploadPath(data?: string, fileName?: string, folder = 'files') {
    const content = clean(data);
    if (!content) return '';
    const match = content.match(/^data:([^;]+);base64,(.+)$/);
    const rawBase64 = match ? match[2] : content;
    const ext = extensionFromFileName(fileName) || extensionFromMime(match?.[1]) || '.bin';
    const uploadRoot = join(process.cwd(), 'uploads', folder);
    if (!existsSync(uploadRoot)) mkdirSync(uploadRoot, { recursive: true });
    const digest = createHash('sha1').update(rawBase64.slice(0, 500)).update(String(Date.now())).digest('hex').slice(0, 14);
    const safeFile = `${digest}${ext}`;
    writeFileSync(join(uploadRoot, safeFile), Buffer.from(rawBase64, 'base64'));
    return `/uploads/${folder}/${safeFile}`;
  }

  private materialIncludes() {
    return { category: true, uom: true, vendor: true };
  }

  private async ensureMaterial(id: string) {
    const existing = await this.prisma.rawMaterial.findUnique({ where: { id } });
    if (!existing) throw new NotFoundException('Raw material not found');
  }

  private async ensureCategory(id: string) {
    const existing = await this.prisma.rawMaterialCategory.findUnique({ where: { id } });
    if (!existing) throw new BadRequestException(['Selected category was not found.']);
  }

  private async ensureUom(id: string) {
    const existing = await this.prisma.unitOfMeasure.findUnique({ where: { id } });
    if (!existing) throw new BadRequestException(['Selected UOM was not found.']);
  }

  private async ensureVendor(id: string) {
    const existing = await this.prisma.rawMaterialVendor.findUnique({ where: { id } });
    if (!existing) throw new BadRequestException(['Selected vendor was not found.']);
  }

  private ensureImportModule(moduleName: string) {
    if (!importModules.includes(moduleName)) {
      throw new BadRequestException(['Invalid import/export module.']);
    }
  }

  private listResponse(items: unknown[], total: number, page: number, pageSize: number) {
    return { items, meta: this.meta(total, page, pageSize) };
  }

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

  private toPositiveNumber(value: string | undefined, fallback: number) {
    const parsed = Number.parseInt(value || '', 10);
    return Number.isFinite(parsed) && parsed > 0 ? parsed : fallback;
  }
}

function toMaterialView(
  material: RawMaterial & {
    category?: RawMaterialCategory | null;
    uom?: UnitOfMeasure | null;
    vendor?: RawMaterialVendor | null;
  },
) {
  return {
    ...material,
    categoryName: material.category?.categoryName || '',
    categoryCode: material.category?.categoryCode || '',
    uomName: material.uom?.uomName || '',
    uomCode: material.uom?.uomCode || '',
    vendorName: material.vendor?.vendorName || '',
    vendorCode: material.vendor?.vendorCode || '',
    reorderWarning: material.reorderLevel > 0 && material.currentStock <= material.reorderLevel,
  };
}

function toVendorView(vendor: RawMaterialVendor) {
  const query = [vendor.locationPlace, vendor.locationPincode].filter(Boolean).join(' ');
  const mapEmbedUrl =
    vendor.latitude !== null && vendor.longitude !== null && vendor.latitude !== undefined && vendor.longitude !== undefined
      ? `https://www.openstreetmap.org/export/embed.html?marker=${vendor.latitude},${vendor.longitude}&layer=mapnik`
      : query
        ? `https://www.openstreetmap.org/export/embed.html?bbox=68,6,98,37&layer=mapnik&query=${encodeURIComponent(query)}`
        : '';

  return { ...vendor, mapEmbedUrl };
}

function toPriceHistoryView(
  item: RawMaterialPriceHistory & {
    rawMaterial?: RawMaterial;
    vendor?: RawMaterialVendor | null;
  },
) {
  return {
    id: item.id,
    rawMaterialId: item.rawMaterialId,
    materialCode: item.rawMaterial?.materialCode || '',
    materialName: item.rawMaterial?.materialName || '',
    vendorId: item.vendorId,
    vendorName: item.vendor?.vendorName || '',
    oldPrice: item.oldPrice,
    newPrice: item.newPrice,
    currency: item.currency,
    effectiveDate: item.effectiveDate,
    updatedBy: item.updatedBy || '',
    remarks: item.remarks || '',
    movement: item.newPrice > item.oldPrice ? 'up' : item.newPrice < item.oldPrice ? 'down' : 'same',
  };
}

function toPurchaseOrderView(
  order: PurchaseOrder & {
    vendor?: RawMaterialVendor | null;
    items?: Array<PurchaseOrderItem & { rawMaterial?: RawMaterial | null; uom?: UnitOfMeasure | null }>;
  },
) {
  return {
    ...order,
    vendorName: order.vendor?.vendorName || '',
    vendorEmail: order.vendor?.email || '',
    vendor: order.vendor || null,
    currencyLabel: new Intl.NumberFormat('en-IN', { style: 'currency', currency: 'INR' }).format(order.totalAmount),
    items: (order.items || []).map((item) => ({
      ...item,
      materialCode: item.rawMaterial?.materialCode || '',
      materialName: item.rawMaterial?.materialName || item.description || '',
      uomCode: item.uom?.uomCode || '',
    })),
  };
}

function buildPurchaseOrderEmailHtml(order: any, message: string, settings: RawMaterialSettings) {
  const rows = (order.items || [])
    .map(
      (item: any) =>
        `<tr><td>${escapeHtml(item.materialCode)}</td><td>${escapeHtml(item.materialName)}</td><td>${item.quantity}</td><td>${item.rate}</td><td>${item.lineTotal}</td></tr>`,
    )
    .join('');
  return `<h2>${escapeHtml(settings.billHeader || 'Purchase Order')}</h2><p>${escapeHtml(message)}</p><p><strong>PO:</strong> ${escapeHtml(order.poNumber)}</p><table border="1" cellspacing="0" cellpadding="6"><thead><tr><th>Code</th><th>Material</th><th>Qty</th><th>Rate</th><th>Total</th></tr></thead><tbody>${rows}</tbody></table><h3>Total: ${escapeHtml(order.currencyLabel)}</h3><p>${escapeHtml(settings.billFooter || '')}</p>`;
}

async function sendSmtpMail(settings: RawMaterialSettings, mail: { to: string; cc?: string; subject: string; text: string; html: string }) {
  if (!settings.smtpHost || !settings.defaultSenderEmail) {
    throw new BadRequestException(['SMTP host and sender email must be configured in settings.']);
  }
  const port = settings.smtpPort || 465;
  const recipients = [mail.to, ...(mail.cc ? mail.cc.split(',') : [])].map((item) => item.trim()).filter(Boolean);
  const from = settings.defaultSenderEmail;
  const headers = [
    `From: "${settings.defaultSenderName || 'Starline Purchase'}" <${from}>`,
    `To: ${mail.to}`,
    mail.cc ? `Cc: ${mail.cc}` : '',
    `Subject: ${mail.subject}`,
    'MIME-Version: 1.0',
    'Content-Type: text/html; charset=utf-8',
  ].filter(Boolean).join('\r\n');
  const payload = `${headers}\r\n\r\n${mail.html}`;

  await smtpConversation(settings.smtpHost, port, [
    `EHLO localhost`,
    ...(settings.smtpUsername && settings.smtpPassword
      ? [`AUTH LOGIN`, Buffer.from(settings.smtpUsername).toString('base64'), Buffer.from(settings.smtpPassword).toString('base64')]
      : []),
    `MAIL FROM:<${from}>`,
    ...recipients.map((recipient) => `RCPT TO:<${recipient}>`),
    'DATA',
    `${payload}\r\n.`,
    'QUIT',
  ]);
}

function smtpConversation(host: string, port: number, commands: string[]) {
  return new Promise<void>((resolve, reject) => {
    const socket = connect(port, host, { rejectUnauthorized: false });
    let commandIndex = 0;
    let buffer = '';

    socket.setTimeout(15000);
    socket.on('data', (chunk) => {
      buffer += chunk.toString();
      const lines = buffer.split(/\r?\n/).filter(Boolean);
      const last = lines[lines.length - 1] || '';
      if (!/^\d{3} /.test(last)) return;
      const code = Number(last.slice(0, 3));
      if (code >= 400) {
        socket.destroy();
        reject(new BadRequestException([`SMTP error: ${last}`]));
        return;
      }
      if (commandIndex < commands.length) {
        socket.write(`${commands[commandIndex]}\r\n`);
        commandIndex += 1;
        buffer = '';
      } else {
        socket.end();
        resolve();
      }
    });
    socket.on('timeout', () => {
      socket.destroy();
      reject(new BadRequestException(['SMTP connection timed out.']));
    });
    socket.on('error', (error) => reject(error));
  });
}

function parseDelimitedRows(text: string) {
  const rows = rawDelimitedRows(text.replace(/^\uFEFF/, '')).filter((row) => row.some((cell) => clean(cell)));
  if (rows.length < 2) return [];
  const headers = rows[0].map((header) => clean(header));
  return rows.slice(1).map((row) =>
    headers.reduce<Record<string, string>>((result, header, index) => {
      result[header] = clean(row[index]);
      return result;
    }, {}),
  );
}

function rawDelimitedRows(text: string) {
  const firstLine = text.split(/\r?\n/, 1)[0] || '';
  const delimiter = firstLine.includes('\t') ? '\t' : ',';
  const rows: string[][] = [];
  let row: string[] = [];
  let cell = '';
  let inQuotes = false;

  for (let index = 0; index < text.length; index += 1) {
    const char = text[index];
    const nextChar = text[index + 1];
    if (char === '"') {
      if (inQuotes && nextChar === '"') {
        cell += '"';
        index += 1;
      } else {
        inQuotes = !inQuotes;
      }
      continue;
    }
    if (!inQuotes && char === delimiter) {
      row.push(cell);
      cell = '';
      continue;
    }
    if (!inQuotes && (char === '\n' || char === '\r')) {
      if (char === '\r' && nextChar === '\n') index += 1;
      row.push(cell);
      rows.push(row);
      row = [];
      cell = '';
      continue;
    }
    cell += char;
  }
  row.push(cell);
  rows.push(row);
  return rows;
}

function parseImportRows(fileName: string, content: string) {
  if (fileName.toLowerCase().endsWith('.xlsx')) {
    return parseXlsxRows(decodeImportBuffer(content));
  }
  return parseDelimitedRows(decodeImportContent(content));
}

function decodeImportBuffer(content: string) {
  const value = clean(content);
  const base64Match = value.match(/^data:[^;]+;base64,(.+)$/);
  if (base64Match) return Buffer.from(base64Match[1], 'base64');
  return Buffer.from(value, 'utf8');
}

function decodeImportContent(content: string) {
  const value = clean(content);
  const base64Match = value.match(/^data:[^;]+;base64,(.+)$/);
  if (base64Match) return Buffer.from(base64Match[1], 'base64').toString('utf8');
  return value;
}

function parseXlsxRows(buffer: Buffer) {
  const files = readZipFiles(buffer);
  const sharedStrings = parseSharedStrings(files['xl/sharedStrings.xml'] || '');
  const sheet = files['xl/worksheets/sheet1.xml'];
  if (!sheet) return [];

  const rows: string[][] = [];
  for (const rowMatch of sheet.matchAll(/<row[^>]*>([\s\S]*?)<\/row>/g)) {
    const cells: string[] = [];
    for (const cellMatch of rowMatch[1].matchAll(/<c([^>]*)>([\s\S]*?)<\/c>/g)) {
      const attrs = cellMatch[1];
      const body = cellMatch[2];
      const ref = attrs.match(/\sr="([A-Z]+)\d+"/)?.[1] || columnName(cells.length + 1);
      const columnIndex = columnIndexFromName(ref) - 1;
      const type = attrs.match(/\st="([^"]+)"/)?.[1] || '';
      const valueMatch = body.match(/<v>([\s\S]*?)<\/v>/);
      const inlineMatch = body.match(/<t[^>]*>([\s\S]*?)<\/t>/);
      let value = '';
      if (type === 's' && valueMatch) {
        value = sharedStrings[Number(valueMatch[1])] || '';
      } else if (inlineMatch) {
        value = decodeXml(inlineMatch[1]);
      } else if (valueMatch) {
        value = decodeXml(valueMatch[1]);
      }
      cells[columnIndex] = value;
    }
    rows.push(cells.map((cell) => cell || ''));
  }

  if (rows.length < 2) return [];
  const headers = rows[0].map((header) => clean(header));
  return rows.slice(1).filter((row) => row.some((cell) => clean(cell))).map((row) =>
    headers.reduce<Record<string, string>>((result, header, index) => {
      result[header || `Column ${index + 1}`] = clean(row[index]);
      return result;
    }, {}),
  );
}

function readZipFiles(buffer: Buffer) {
  const files: Record<string, string> = {};
  const eocdOffset = buffer.lastIndexOf(Buffer.from([0x50, 0x4b, 0x05, 0x06]));
  if (eocdOffset < 0) return files;
  const totalEntries = buffer.readUInt16LE(eocdOffset + 10);
  let centralOffset = buffer.readUInt32LE(eocdOffset + 16);

  for (let index = 0; index < totalEntries; index += 1) {
    if (buffer.readUInt32LE(centralOffset) !== 0x02014b50) break;
    const compression = buffer.readUInt16LE(centralOffset + 10);
    const compressedSize = buffer.readUInt32LE(centralOffset + 20);
    const fileNameLength = buffer.readUInt16LE(centralOffset + 28);
    const extraLength = buffer.readUInt16LE(centralOffset + 30);
    const commentLength = buffer.readUInt16LE(centralOffset + 32);
    const localOffset = buffer.readUInt32LE(centralOffset + 42);
    const name = buffer.slice(centralOffset + 46, centralOffset + 46 + fileNameLength).toString('utf8');

    const localNameLength = buffer.readUInt16LE(localOffset + 26);
    const localExtraLength = buffer.readUInt16LE(localOffset + 28);
    const dataStart = localOffset + 30 + localNameLength + localExtraLength;
    const compressed = buffer.slice(dataStart, dataStart + compressedSize);
    const data = compression === 8 ? inflateRawSync(compressed) : compressed;
    files[name] = data.toString('utf8');

    centralOffset += 46 + fileNameLength + extraLength + commentLength;
  }
  return files;
}

function parseSharedStrings(xml: string) {
  const strings: string[] = [];
  for (const match of xml.matchAll(/<si[^>]*>([\s\S]*?)<\/si>/g)) {
    const text = [...match[1].matchAll(/<t[^>]*>([\s\S]*?)<\/t>/g)].map((item) => decodeXml(item[1])).join('');
    strings.push(text);
  }
  return strings;
}

function normalizeRow(row: Record<string, string>) {
  const normalized: Record<string, string> = {};
  for (const [key, value] of Object.entries(row)) {
    const cleanKey = clean(key).toLowerCase().replace(/[^a-z0-9]+/g, ' ').trim();
    const mapped = keyMap[cleanKey] || cleanKey.replace(/\s+([a-z0-9])/g, (_, char) => String(char).toUpperCase());
    normalized[mapped] = clean(value);
  }
  return normalized;
}

const keyMap: Record<string, string> = {
  'material code': 'materialCode',
  'item code': 'materialCode',
  'material name': 'materialName',
  'item name': 'materialName',
  category: 'categoryName',
  'category code': 'categoryCode',
  catergory: 'categoryName',
  'catergory code': 'categoryCode',
  'sub type': 'subType',
  uom: 'uomCode',
  'uom code': 'uomCode',
  'uom name': 'uomName',
  'measure type': 'measureType',
  'hsn code': 'hsnCode',
  'gst': 'gstPercent',
  'gst percent': 'gstPercent',
  'gst %': 'gstPercent',
  vendor: 'vendorName',
  'vendor name': 'vendorName',
  'vendor code': 'vendorCode',
  'cost per unit': 'costPerUnit',
  rate: 'rate',
  quantity: 'quantity',
  status: 'status',
  'transportation cost': 'transportationCost',
};

function toCsv(rows: Record<string, unknown>[]) {
  if (!rows.length) return '';
  const headers = Object.keys(rows[0]);
  return [headers, ...rows.map((row) => headers.map((header) => formatCsvCell(row[header])))]
    .map((row) => row.join(','))
    .join('\n');
}

function formatCsvCell(value: unknown) {
  const text = value instanceof Date ? value.toISOString() : String(value ?? '');
  return /[",\n\r]/.test(text) ? `"${text.replace(/"/g, '""')}"` : text;
}

function createXlsx(rows: Record<string, unknown>[], sheetName: string) {
  const headers = rows.length ? Object.keys(rows[0]) : ['No Data'];
  const sheetRows = [headers, ...rows.map((row) => headers.map((header) => row[header]))];
  const sheetXml = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?><worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><sheetData>${sheetRows
    .map(
      (row, rowIndex) =>
        `<row r="${rowIndex + 1}">${row
          .map((cell, cellIndex) => {
            const ref = `${columnName(cellIndex + 1)}${rowIndex + 1}`;
            if (typeof cell === 'number') return `<c r="${ref}"><v>${cell}</v></c>`;
            return `<c r="${ref}" t="inlineStr"><is><t>${escapeXml(String(cell ?? ''))}</t></is></c>`;
          })
          .join('')}</row>`,
    )
    .join('')}</sheetData></worksheet>`;
  const files: Record<string, string> = {
    '[Content_Types].xml':
      '<?xml version="1.0" encoding="UTF-8" standalone="yes"?><Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types"><Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/><Default Extension="xml" ContentType="application/xml"/><Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/><Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/></Types>',
    '_rels/.rels':
      '<?xml version="1.0" encoding="UTF-8" standalone="yes"?><Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/></Relationships>',
    'xl/workbook.xml': `<?xml version="1.0" encoding="UTF-8" standalone="yes"?><workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"><sheets><sheet name="${escapeXml(sheetName.slice(0, 31))}" sheetId="1" r:id="rId1"/></sheets></workbook>`,
    'xl/_rels/workbook.xml.rels':
      '<?xml version="1.0" encoding="UTF-8" standalone="yes"?><Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/></Relationships>',
    'xl/worksheets/sheet1.xml': sheetXml,
  };
  return createZip(files);
}

function createZip(files: Record<string, string>) {
  const localParts: Buffer[] = [];
  const centralParts: Buffer[] = [];
  let offset = 0;
  for (const [name, content] of Object.entries(files)) {
    const nameBuffer = Buffer.from(name);
    const data = Buffer.from(content, 'utf8');
    const crc = crc32(data);
    const local = Buffer.alloc(30);
    local.writeUInt32LE(0x04034b50, 0);
    local.writeUInt16LE(20, 4);
    local.writeUInt16LE(0, 6);
    local.writeUInt16LE(0, 8);
    local.writeUInt16LE(0, 10);
    local.writeUInt16LE(0, 12);
    local.writeUInt32LE(crc, 14);
    local.writeUInt32LE(data.length, 18);
    local.writeUInt32LE(data.length, 22);
    local.writeUInt16LE(nameBuffer.length, 26);
    local.writeUInt16LE(0, 28);
    localParts.push(local, nameBuffer, data);

    const central = Buffer.alloc(46);
    central.writeUInt32LE(0x02014b50, 0);
    central.writeUInt16LE(20, 4);
    central.writeUInt16LE(20, 6);
    central.writeUInt16LE(0, 8);
    central.writeUInt16LE(0, 10);
    central.writeUInt16LE(0, 12);
    central.writeUInt16LE(0, 14);
    central.writeUInt32LE(crc, 16);
    central.writeUInt32LE(data.length, 20);
    central.writeUInt32LE(data.length, 24);
    central.writeUInt16LE(nameBuffer.length, 28);
    central.writeUInt16LE(0, 30);
    central.writeUInt16LE(0, 32);
    central.writeUInt16LE(0, 34);
    central.writeUInt16LE(0, 36);
    central.writeUInt32LE(0, 38);
    central.writeUInt32LE(offset, 42);
    centralParts.push(central, nameBuffer);
    offset += local.length + nameBuffer.length + data.length;
  }

  const centralSize = centralParts.reduce((sum, part) => sum + part.length, 0);
  const end = Buffer.alloc(22);
  end.writeUInt32LE(0x06054b50, 0);
  end.writeUInt16LE(0, 4);
  end.writeUInt16LE(0, 6);
  end.writeUInt16LE(Object.keys(files).length, 8);
  end.writeUInt16LE(Object.keys(files).length, 10);
  end.writeUInt32LE(centralSize, 12);
  end.writeUInt32LE(offset, 16);
  end.writeUInt16LE(0, 20);
  return Buffer.concat([...localParts, ...centralParts, end]);
}

function crc32(buffer: Buffer) {
  let crc = -1;
  for (const byte of buffer) {
    crc = (crc >>> 8) ^ crcTable[(crc ^ byte) & 0xff];
  }
  return (crc ^ -1) >>> 0;
}

const crcTable = Array.from({ length: 256 }, (_, index) => {
  let value = index;
  for (let bit = 0; bit < 8; bit += 1) {
    value = value & 1 ? 0xedb88320 ^ (value >>> 1) : value >>> 1;
  }
  return value >>> 0;
});

function columnName(index: number) {
  let name = '';
  while (index > 0) {
    const mod = (index - 1) % 26;
    name = String.fromCharCode(65 + mod) + name;
    index = Math.floor((index - mod) / 26);
  }
  return name;
}

function columnIndexFromName(name: string) {
  return name.split('').reduce((sum, char) => sum * 26 + char.charCodeAt(0) - 64, 0);
}

function extensionFromFileName(name?: string) {
  const match = clean(name).match(/\.[a-z0-9]+$/i);
  return match ? match[0].toLowerCase() : '';
}

function extensionFromMime(mime?: string) {
  if (!mime) return '';
  if (mime.includes('png')) return '.png';
  if (mime.includes('jpeg') || mime.includes('jpg')) return '.jpg';
  if (mime.includes('pdf')) return '.pdf';
  return '';
}

function endOfDay(value: string) {
  const date = new Date(value);
  date.setHours(23, 59, 59, 999);
  return date;
}

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

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

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

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

function parseBoolean(value: unknown, fallback: boolean) {
  if (value === null || value === undefined || value === '') return fallback;
  if (typeof value === 'boolean') return value;
  return ['1', 'true', 'yes', 'on'].includes(String(value).toLowerCase());
}

function escapeXml(value: string) {
  return value.replace(/&/g, '&amp;').replace(/</g, '&lt;').replace(/>/g, '&gt;').replace(/"/g, '&quot;');
}

function decodeXml(value: string) {
  return value
    .replace(/&quot;/g, '"')
    .replace(/&apos;/g, "'")
    .replace(/&lt;/g, '<')
    .replace(/&gt;/g, '>')
    .replace(/&amp;/g, '&');
}

function escapeHtml(value: string) {
  return escapeXml(value);
}

function extractErrorMessage(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.';
}