import {
  BadRequestException,
  Injectable,
  NotFoundException,
} from '@nestjs/common';
import {
  exportRows,
  normalizeImportRow,
  parseImportRows,
} from '../common/tabular-file.util';
import { resolveProductImageUrl } from '../common/file-manager-image.util';
import { PrismaService } from '../prisma/prisma.service';

type FinalProductQuery = {
  page?: string;
  pageSize?: string;
  search?: string;
  status?: string;
  inch?: string;
  codeStart?: string;
  codeEnd?: string;
  sortBy?: string;
  sortOrder?: string;
  format?: string;
};

type FinalProductWriteData = {
  code: string;
  wheelName: string;
  inch: number | null;
  productId: string | null;
  uniqueCode: string | null;
  stockAvailability: string | null;
  productDescription: string | null;
  application: string | null;
  loadCarryingCapacity: string | null;
  wheelSize: string | null;
  fittingType: string | null;
  bodyType: string | null;
  mountingType: string | null;
  finishType: string | null;
  bearingType: string | null;
  wheelType: string | null;
  headerType: string | null;
  dustCoverOnWheel: string | null;
  bearingInFrame: string | null;
  brakingSystem: string | null;
  frameColor: string | null;
  wheelColor: string | null;
  pageTitle: string | null;
  keywords: string | null;
  metaDescription: string | null;
  status: string;
  wheelTypeSub: string | null;
  yorkeCode: string | null;
  yorkeLoad: string | null;
  sourceFileName: string | null;
};

type FinalProductExportCodeMaps = {
  application: Map<string, string>;
  applicationCodes: string[];
  loadCarryingCapacity: Map<string, string>;
  wheelSize: Map<string, string>;
  fittingType: Map<string, string>;
  bodyType: Map<string, string>;
  mountingType: Map<string, string>;
  finishType: Map<string, string>;
  bearingType: Map<string, string>;
  wheelType: Map<string, string>;
  headerType: Map<string, string>;
  dustCoverOnWheel: Map<string, string>;
  bearingInFrame: Map<string, string>;
  brakingSystem: Map<string, string>;
  frameColor: Map<string, string>;
  wheelColor: Map<string, string>;
  wheelTypeSub: Map<string, string>;
};

type FinalProductImportCodeMaps = {
  application: Map<string, string>;
  loadCarryingCapacity: Map<string, string>;
  wheelSize: Map<string, string>;
  fittingType: Map<string, string>;
  bodyType: Map<string, string>;
  mountingType: Map<string, string>;
  finishType: Map<string, string>;
  bearingType: Map<string, string>;
  wheelType: Map<string, string>;
  headerType: Map<string, string>;
  dustCoverOnWheel: Map<string, string>;
  bearingInFrame: Map<string, string>;
  brakingSystem: Map<string, string>;
  frameColor: Map<string, string>;
  wheelColor: Map<string, string>;
  wheelTypeSub: Map<string, string>;
};

type ExportCodeAlias = {
  alias: string;
  targets: string[];
};

type FinalProductExportRow = {
  code: string;
  wheelName: string;
  productId: string;
  application: string;
  status: string;
  updatedAt: number;
  searchIndex: string;
  values: Record<string, string>;
};

type FinalProductLinkedProductTagContext = {
  setProductByCode: Map<
    string,
    {
      productCode: string;
      holderCode: string;
      plateCode: string;
      wheelCode: string;
      finishType: string;
      productInch: number;
    }
  >;
  holderByCode: Map<
    string,
    {
      holderCode: string;
      bodyCode: string;
    }
  >;
  plateByCode: Map<
    string,
    {
      plateCode: string;
      loadCarryingCode: string;
      mountingCode: string;
      fittingSizeCode: string;
      fittingCode: string;
      bearingCode: string;
      brakeTypeCode: string;
    }
  >;
  wheelByCode: Map<
    string,
    {
      wheelCode: string;
      wheelSizeCode: string;
      wheelTypeCode: string;
      bearingCode: string;
      dustCoverCode: string;
      wheelColorCode: string;
      dynamicLoadCarryCapacityCode: string;
      wheelTypeSubNameCode: string;
    }
  >;
};

const sortColumns = [
  'code',
  'wheelName',
  'productId',
  'application',
  'status',
  'updatedAt',
] as const;

const finishTypeExportAliases: ExportCodeAlias[] = [
  {
    alias: 'EG_COATED',
    targets: ['ELECTRO GALVANISED ZINC COATING'],
  },
  {
    alias: 'CED_COATED',
    targets: ['CED COATING'],
  },
  {
    alias: 'POWDER_COATED',
    targets: ['POWDER COATING'],
  },
];

const frameColorExportAliases: ExportCodeAlias[] = [
  {
    alias: 'EG_COATED',
    targets: ['Zinc Blue'],
  },
  {
    alias: 'ELECTRO GALVANISED ZINC COATING',
    targets: ['Zinc Blue'],
  },
  {
    alias: 'CED_COATED',
    targets: ['CED COATED BLACK'],
  },
  {
    alias: 'CED COATING',
    targets: ['CED COATED BLACK'],
  },
  {
    alias: 'POWDER_COATED',
    targets: ['Powder Coated Black', 'Powder Black Coating'],
  },
  {
    alias: 'POWDER COATING',
    targets: ['Powder Coated Black', 'Powder Black Coating'],
  },
];

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

  async list(query: FinalProductQuery) {
    const model = this.getModel();
    const page = this.toPositiveNumber(query.page, 1);
    const pageSize = Math.min(this.toPositiveNumber(query.pageSize, 10), 100);
    const sortBy = this.resolveSortBy(query.sortBy);
    const sortOrder = query.sortOrder === 'desc' ? 'desc' : 'asc';
    const allowedCodes = await this.resolveSetProductCodes(query, false);
    const where = this.buildWhere(query, allowedCodes);

    const [total, items, statuses] = await Promise.all([
      model.count({ where }),
      model.findMany({
        where,
        orderBy: this.buildOrderBy(sortBy, sortOrder),
        skip: (page - 1) * pageSize,
        take: pageSize,
      }),
      model.findMany({
        distinct: ['status'],
        orderBy: { status: 'asc' },
        select: { status: true },
      }),
    ]);

    return {
      items: items.map((item: any) => this.withProductImage(item)),
      meta: {
        total,
        page,
        pageSize,
        totalPages: Math.max(1, Math.ceil(total / pageSize)),
      },
      filterOptions: {
        statuses: statuses.map((item: { status: string }) => item.status),
      },
    };
  }

  private withProductImage(item: any) {
    return {
      ...item,
      image: resolveProductImageUrl(item.code, item.inch),
    };
  }

  async importFile(fileName: string, buffer: Buffer, mimeType?: string) {
    const model = this.getModel();
    const codeMaps = await this.buildImportCodeMaps();
    const rows = parseImportRows(
      fileName,
      `data:${mimeType || 'application/octet-stream'};base64,${buffer.toString('base64')}`,
    ).map(normalizeImportRow);
    const setProductCodes = await this.fetchExistingSetProductCodes(
      rows.map((row) => normalizeProductCode(readText(row, 'code'))),
    );
    const linkedProductTagContext =
      await this.buildLinkedProductTagValidationContext(setProductCodes);
    const errors: string[] = [];
    let created = 0;
    let updated = 0;
    let skipped = 0;

    for (const [index, row] of rows.entries()) {
      try {
        const data = this.validateRow(
          row,
          fileName,
          codeMaps,
          setProductCodes,
          linkedProductTagContext,
        );
        const existing = await model.findUnique({
          where: { code: data.code },
        });

        await model.upsert({
          where: { code: data.code },
          create: data,
          update: data,
        });

        if (existing) {
          updated += 1;
        } else {
          created += 1;
        }
      } catch (error: any) {
        skipped += 1;
        const message = Array.isArray(error?.response?.message)
          ? error.response.message.join(', ')
          : error?.response?.message || error?.message || 'Invalid row.';
        errors.push(`Row ${index + 2}: ${message}`);
      }
    }

    return {
      totalRows: rows.length,
      created,
      updated,
      skipped,
      errors: errors.slice(0, 25),
    };
  }

  async exportFile(query: FinalProductQuery) {
    const setProducts = await this.prisma.setProduct.findMany({
      where: this.buildSetProductWhere(query, true),
      orderBy: [{ updatedAt: 'desc' }, { productCode: 'asc' }],
      select: {
        holderCode: true,
        plateCode: true,
        finishType: true,
        finishLabel: true,
        wheelCode: true,
        wheelName: true,
        productName: true,
        productCode: true,
        productInch: true,
        updatedAt: true,
      },
    });

    if (!setProducts.length) {
      return exportRows('final_products', [], query.format || 'xlsx');
    }

    const codeMaps = await this.buildExportCodeMaps();
    const productCodes = setProducts.map((product) => product.productCode);
    const holderCodes = uniqueValues(setProducts.map((product) => product.holderCode));
    const plateCodes = uniqueValues(setProducts.map((product) => product.plateCode));
    const wheelCodes = uniqueValues(setProducts.map((product) => product.wheelCode));

    const [finalRows, holders, plates, wheels] = await Promise.all([
      this.getModel().findMany({
        where: {
          code: {
            in: productCodes,
          },
        },
      }),
      this.prisma.holderMaster.findMany({
        where: {
          holderCode: {
            in: holderCodes,
          },
        },
        select: {
          holderCode: true,
          bodyCode: true,
          yorkeCode: true,
          yorkeLoad: true,
        },
      }),
      this.prisma.plateMaster.findMany({
        where: {
          plateCode: {
            in: plateCodes,
          },
        },
        select: {
          plateCode: true,
          loadCarryingCode: true,
          mountingCode: true,
          fittingSizeCode: true,
          fittingSize: true,
          fittingCode: true,
          fittingType: true,
          bearingCode: true,
          brakeTypeCode: true,
          yokeCode: true,
        },
      }),
      this.prisma.wheelMaster.findMany({
        where: {
          wheelCode: {
            in: wheelCodes,
          },
        },
        select: {
          wheelCode: true,
          wheelSizeCode: true,
          wheelTypeCode: true,
          bearingCode: true,
          dustCoverCode: true,
          wheelColorCode: true,
          dynamicLoadCarryCapacityCode: true,
          wheelTypeSubNameCode: true,
        },
      }),
    ]);

    const finalByCode = new Map(
      finalRows.map((row: any) => [normalizeProductCode(row.code), row]),
    );
    const holderByCode = new Map(
      holders.map((holder) => [holder.holderCode, holder]),
    );
    const plateByCode = new Map(plates.map((plate) => [plate.plateCode, plate]));
    const wheelByCode = new Map(wheels.map((wheel) => [wheel.wheelCode, wheel]));
    const exportRowsData = setProducts.map((product) =>
      this.mapDerivedExportRow(
        product,
        finalByCode.get(normalizeProductCode(product.productCode)),
        holderByCode.get(product.holderCode),
        plateByCode.get(product.plateCode),
        wheelByCode.get(product.wheelCode),
        codeMaps,
      ),
    );
    const filteredRows = this.filterDerivedExportRows(exportRowsData, query);
    const sortedRows = this.sortDerivedExportRows(filteredRows, query);

    return exportRows(
      'final_products',
      sortedRows.map((row) => row.values),
      query.format || 'xlsx',
    );
  }

  async delete(id: string) {
    const model = this.getModel();
    const existing = await model.findUnique({ where: { id } });

    if (!existing) {
      throw new NotFoundException('Final product record not found.');
    }

    await model.delete({ where: { id } });
    return { deleted: true };
  }

  private getModel(): any {
    return (this.prisma as any).finalProduct;
  }

  private async fetchExistingSetProductCodes(codes: string[]) {
    const filteredCodes = [...new Set(codes.filter(Boolean))];

    if (!filteredCodes.length) {
      return new Set<string>();
    }

    const products = await this.prisma.setProduct.findMany({
      where: {
        productCode: {
          in: filteredCodes,
        },
      },
      select: {
        productCode: true,
      },
    });

    return new Set(products.map((product) => normalizeProductCode(product.productCode)));
  }

  private async resolveSetProductCodes(
    query: FinalProductQuery,
    includeCodeRange: boolean,
  ) {
    const where = this.buildSetProductWhere(query, includeCodeRange);

    if (!where) {
      return null;
    }

    const products = await this.prisma.setProduct.findMany({
      where,
      select: {
        productCode: true,
      },
    });

    return uniqueValues(
      products.map((product) => normalizeProductCode(product.productCode)),
    );
  }

  private buildSetProductWhere(
    query: FinalProductQuery,
    includeCodeRange: boolean,
  ) {
    const and: any[] = [];
    const inch = Number.parseFloat(query.inch || '');
    const codeRange = includeCodeRange
      ? this.buildProductCodeRange(query.codeStart, query.codeEnd)
      : null;

    if (Number.isFinite(inch) && inch > 0) {
      and.push({
        productInch: inch,
      });
    }

    if (codeRange) {
      and.push({
        productCode: codeRange,
      });
    }

    return and.length ? ({ AND: and } as any) : undefined;
  }

  private buildProductCodeRange(start?: string, end?: string) {
    let startCode = normalizeProductCode(start);
    let endCode = normalizeProductCode(end);

    if (!startCode && !endCode) {
      return null;
    }

    if (startCode && endCode && compareText(startCode, endCode) > 0) {
      [startCode, endCode] = [endCode, startCode];
    }

    return {
      ...(startCode ? { gte: startCode } : {}),
      ...(endCode ? { lte: endCode } : {}),
    };
  }

  private buildWhere(query: FinalProductQuery, allowedCodes?: string[] | null) {
    const and: any[] = [];
    const search = clean(query.search);

    if (allowedCodes) {
      and.push({
        code: {
          in: allowedCodes.length ? allowedCodes : ['__NO_MATCHING_SET_PRODUCT__'],
        },
      } as any);
    }

    if (search) {
      and.push({
        OR: [
          { code: { contains: search, mode: 'insensitive' } },
          { wheelName: { contains: search, mode: 'insensitive' } },
          { productId: { contains: search, mode: 'insensitive' } },
          { uniqueCode: { contains: search, mode: 'insensitive' } },
          { application: { contains: search, mode: 'insensitive' } },
          { productDescription: { contains: search, mode: 'insensitive' } },
          { wheelType: { contains: search, mode: 'insensitive' } },
          { frameColor: { contains: search, mode: 'insensitive' } },
          { wheelColor: { contains: search, mode: 'insensitive' } },
          { status: { contains: search, mode: 'insensitive' } },
        ],
      } as any);
    }

    if (clean(query.status)) {
      and.push({
        status: normalizeStatusFilter(query.status),
      } as any);
    }

    return and.length ? ({ AND: and } as any) : {};
  }

  private buildOrderBy(sortBy: string, sortOrder: 'asc' | 'desc') {
    return [{ [sortBy]: sortOrder }, { code: 'asc' }];
  }

  private resolveSortBy(value?: string) {
    return sortColumns.includes(clean(value) as (typeof sortColumns)[number])
      ? clean(value)
      : 'updatedAt';
  }

  private validateRow(
    row: Record<string, string>,
    sourceFileName: string,
    codeMaps: FinalProductImportCodeMaps,
    setProductCodes: Set<string>,
    linkedProductTagContext: FinalProductLinkedProductTagContext,
  ): FinalProductWriteData {
    const code = normalizeProductCode(readText(row, 'code'));
    const wheelName = readText(row, 'wheelName', 'wheel');
    const status = normalizeStatus(readText(row, 'status'));
    const errors: string[] = [];

    if (!code) {
      errors.push('CODE is required.');
    }

    if (!wheelName) {
      errors.push('WHEEL NAME is required.');
    }

    if (code && !setProductCodes.has(code)) {
      errors.push(`CODE ${code} was not found in Set Creator products.`);
    }

    if (code) {
      errors.push(
        ...this.validateLinkedProductTagCodes(
          code,
          codeMaps,
          linkedProductTagContext,
        ),
      );
    }

    if (errors.length) {
      throw new BadRequestException(errors);
    }

    return {
      code,
      wheelName,
      inch: this.validateInchValue(
        readText(row, 'inch'),
        linkedProductTagContext.setProductByCode.get(code)?.productInch ?? null,
      ),
      productId: asNullable(readText(row, 'productId', 'productID')),
      uniqueCode: asNullable(readText(row, 'uniqueCode')),
      stockAvailability: asNullable(
        readText(row, 'stockAvailability', 'stockAvailable'),
      ),
      productDescription: asNullable(readText(row, 'productDescription')),
      application: this.validateLookupCodes(
        'Application',
        readText(row, 'application'),
        codeMaps.application,
      ),
      loadCarryingCapacity: this.validateLookupCodes(
        'Load Carrying Capacity',
        readText(
          row,
          'loadCarryingCapacity',
          'loadCarryCapacity',
          'loadCarryCapacityTag',
        ),
        codeMaps.loadCarryingCapacity,
      ),
      wheelSize: this.validateLookupCodes(
        'Wheel Size',
        readText(row, 'wheelSize'),
        codeMaps.wheelSize,
      ),
      fittingType: this.validateLookupCodes(
        'Fitting Type',
        readText(row, 'fittingType', 'fittingSize'),
        codeMaps.fittingType,
      ),
      bodyType: this.validateLookupCodes(
        'Body Type',
        readText(row, 'bodyType'),
        codeMaps.bodyType,
      ),
      mountingType: this.validateLookupCodes(
        'Mounting Type',
        readText(row, 'mountingType'),
        codeMaps.mountingType,
      ),
      finishType: this.validateLookupCodes(
        'Finish Type',
        readText(row, 'finishType'),
        codeMaps.finishType,
      ),
      bearingType: this.validateLookupCodes(
        'Bearing Type',
        readText(row, 'bearingType'),
        codeMaps.bearingType,
      ),
      wheelType: this.validateLookupCodes(
        'Wheel Type',
        readText(row, 'wheelType'),
        codeMaps.wheelType,
      ),
      headerType: this.validateLookupCodes(
        'Header Type',
        readText(row, 'headerType'),
        codeMaps.headerType,
      ),
      dustCoverOnWheel: this.validateLookupCodes(
        'Dust Cover On Wheel',
        readText(row, 'dustCoverOnWheel', 'dustCoverType'),
        codeMaps.dustCoverOnWheel,
      ),
      bearingInFrame: this.validateLookupCodes(
        'Bearing In Frame',
        readText(row, 'bearingInFrame', 'bearingInFrameType'),
        codeMaps.bearingInFrame,
      ),
      brakingSystem: this.validateLookupCodes(
        'Braking System',
        readText(row, 'brakingSystem', 'brakingSystems'),
        codeMaps.brakingSystem,
      ),
      frameColor: this.validateLookupCodes(
        'Frame Color',
        readText(row, 'frameColor'),
        codeMaps.frameColor,
      ),
      wheelColor: this.validateLookupCodes(
        'Wheel Color',
        readText(row, 'wheelColor'),
        codeMaps.wheelColor,
      ),
      pageTitle: asNullable(readText(row, 'pageTitle')),
      keywords: asNullable(readText(row, 'keywords')),
      metaDescription: asNullable(readText(row, 'metaDescription')),
      status,
      wheelTypeSub: this.validateLookupCodes(
        'Wheel Type Sub',
        readText(row, 'wheelTypeSub', 'wheelTypeTag'),
        codeMaps.wheelTypeSub,
      ),
      yorkeCode: asNullable(readText(row, 'yorkeCode')),
      yorkeLoad: asNullable(readText(row, 'yorkeLoad')),
      sourceFileName: asNullable(sourceFileName),
    };
  }

  private async buildLinkedProductTagValidationContext(
    setProductCodes: Set<string>,
  ): Promise<FinalProductLinkedProductTagContext> {
    const codes = uniqueValues([...setProductCodes]);

    if (!codes.length) {
      return {
        setProductByCode: new Map(),
        holderByCode: new Map(),
        plateByCode: new Map(),
        wheelByCode: new Map(),
      };
    }

    const setProducts = await this.prisma.setProduct.findMany({
      where: {
        productCode: {
          in: codes,
        },
      },
      select: {
        productCode: true,
        holderCode: true,
        plateCode: true,
        wheelCode: true,
        finishType: true,
        productInch: true,
      },
    });

    const holderCodes = uniqueValues(setProducts.map((product) => product.holderCode));
    const plateCodes = uniqueValues(setProducts.map((product) => product.plateCode));
    const wheelCodes = uniqueValues(setProducts.map((product) => product.wheelCode));

    const [holders, plates, wheels] = await Promise.all([
      this.prisma.holderMaster.findMany({
        where: {
          holderCode: {
            in: holderCodes,
          },
        },
        select: {
          holderCode: true,
          bodyCode: true,
        },
      }),
      this.prisma.plateMaster.findMany({
        where: {
          plateCode: {
            in: plateCodes,
          },
        },
        select: {
          plateCode: true,
          loadCarryingCode: true,
          mountingCode: true,
          fittingSizeCode: true,
          fittingCode: true,
          bearingCode: true,
          brakeTypeCode: true,
        },
      }),
      this.prisma.wheelMaster.findMany({
        where: {
          wheelCode: {
            in: wheelCodes,
          },
        },
        select: {
          wheelCode: true,
          wheelSizeCode: true,
          wheelTypeCode: true,
          bearingCode: true,
          dustCoverCode: true,
          wheelColorCode: true,
          dynamicLoadCarryCapacityCode: true,
          wheelTypeSubNameCode: true,
        },
      }),
    ]);

    return {
      setProductByCode: new Map(
        setProducts.map((product) => [
          normalizeProductCode(product.productCode),
          product,
        ]),
      ),
      holderByCode: new Map(holders.map((holder) => [holder.holderCode, holder])),
      plateByCode: new Map(plates.map((plate) => [plate.plateCode, plate])),
      wheelByCode: new Map(wheels.map((wheel) => [wheel.wheelCode, wheel])),
    };
  }

  private validateLinkedProductTagCodes(
    code: string,
    codeMaps: FinalProductImportCodeMaps,
    linkedProductTagContext: FinalProductLinkedProductTagContext,
  ) {
    const errors: string[] = [];
    const setProduct = linkedProductTagContext.setProductByCode.get(code);

    if (!setProduct) {
      return errors;
    }

    const holder = linkedProductTagContext.holderByCode.get(setProduct.holderCode);
    const plate = linkedProductTagContext.plateByCode.get(setProduct.plateCode);
    const wheel = linkedProductTagContext.wheelByCode.get(setProduct.wheelCode);

    if (!holder) {
      errors.push(
        `Holder code "${setProduct.holderCode}" linked to CODE ${code} was not found in Holder Master.`,
      );
    }

    if (!plate) {
      errors.push(
        `Plate code "${setProduct.plateCode}" linked to CODE ${code} was not found in Plate Master.`,
      );
    }

    if (!wheel) {
      errors.push(
        `Wheel code "${setProduct.wheelCode}" linked to CODE ${code} was not found in Wheel Master.`,
      );
    }

    this.appendProductTagValidationError(
      errors,
      'Finish Type',
      setProduct.finishType,
      codeMaps.finishType,
      `Set Creator product ${code}`,
    );

    if (holder) {
      this.appendProductTagValidationError(
        errors,
        'Body Type',
        holder.bodyCode,
        codeMaps.bodyType,
        `Holder ${setProduct.holderCode}`,
      );
    }

    if (plate) {
      this.appendProductTagValidationError(
        errors,
        'Load Carrying Capacity',
        plate.loadCarryingCode,
        codeMaps.loadCarryingCapacity,
        `Plate ${setProduct.plateCode}`,
      );
      this.appendProductTagValidationError(
        errors,
        'Mounting Type',
        plate.mountingCode,
        codeMaps.mountingType,
        `Plate ${setProduct.plateCode}`,
      );
      this.appendProductTagValidationError(
        errors,
        'Fitting Type',
        plate.fittingCode,
        codeMaps.fittingType,
        `Plate ${setProduct.plateCode}`,
      );
      this.appendProductTagValidationError(
        errors,
        'Header Type',
        plate.fittingSizeCode,
        codeMaps.headerType,
        `Plate ${setProduct.plateCode}`,
      );
      this.appendProductTagValidationError(
        errors,
        'Bearing In Frame',
        plate.bearingCode,
        codeMaps.bearingInFrame,
        `Plate ${setProduct.plateCode}`,
      );
      this.appendProductTagValidationError(
        errors,
        'Braking System',
        plate.brakeTypeCode,
        codeMaps.brakingSystem,
        `Plate ${setProduct.plateCode}`,
      );
    }

    if (wheel) {
      this.appendProductTagValidationError(
        errors,
        'Load Carrying Capacity',
        wheel.dynamicLoadCarryCapacityCode,
        codeMaps.loadCarryingCapacity,
        `Wheel ${setProduct.wheelCode}`,
      );
      this.appendProductTagValidationError(
        errors,
        'Wheel Size',
        wheel.wheelSizeCode,
        codeMaps.wheelSize,
        `Wheel ${setProduct.wheelCode}`,
      );
      this.appendProductTagValidationError(
        errors,
        'Bearing Type',
        wheel.bearingCode,
        codeMaps.bearingType,
        `Wheel ${setProduct.wheelCode}`,
      );
      this.appendProductTagValidationError(
        errors,
        'Wheel Type',
        wheel.wheelTypeCode,
        codeMaps.wheelType,
        `Wheel ${setProduct.wheelCode}`,
      );
      this.appendProductTagValidationError(
        errors,
        'Dust Cover On Wheel',
        wheel.dustCoverCode,
        codeMaps.dustCoverOnWheel,
        `Wheel ${setProduct.wheelCode}`,
      );
      this.appendProductTagValidationError(
        errors,
        'Wheel Color',
        wheel.wheelColorCode,
        codeMaps.wheelColor,
        `Wheel ${setProduct.wheelCode}`,
      );
      this.appendProductTagValidationError(
        errors,
        'Wheel Type Sub',
        wheel.wheelTypeSubNameCode,
        codeMaps.wheelTypeSub,
        `Wheel ${setProduct.wheelCode}`,
      );
    }

    return errors;
  }

  private appendProductTagValidationError(
    errors: string[],
    label: string,
    value: string,
    codeMap: Map<string, string>,
    source: string,
  ) {
    const code = clean(value);

    if (!code) {
      return;
    }

    if (!codeMap.get(normalizeLookup(code))) {
      errors.push(`${label} code "${code}" from ${source} was not found in Product Tags.`);
    }
  }

  private async buildExportCodeMaps(): Promise<FinalProductExportCodeMaps> {
    const [
      applications,
      loadCarryCapacities,
      wheelSizes,
      fittingSizes,
      bodyTypes,
      mountingTypes,
      finishTypes,
      bearingTypes,
      wheelTypes,
      headerTypes,
      dustCoverTypes,
      bearingInFrameTypes,
      brakingSystems,
      frameColors,
      wheelColors,
      wheelTypeTags,
    ] = await Promise.all([
      this.prisma.application.findMany({ select: { name: true, code: true } }),
      this.prisma.loadCarryCapacity.findMany({ select: { name: true, code: true } }),
      this.prisma.wheelSize.findMany({ select: { name: true, code: true } }),
      this.prisma.fittingSize.findMany({ select: { name: true, code: true } }),
      this.prisma.bodyType.findMany({ select: { name: true, code: true } }),
      this.prisma.mountingType.findMany({ select: { name: true, code: true } }),
      this.prisma.finishType.findMany({ select: { name: true, code: true } }),
      this.prisma.bearingType.findMany({ select: { name: true, code: true } }),
      this.prisma.wheelType.findMany({ select: { name: true, code: true } }),
      this.prisma.headerType.findMany({ select: { name: true, code: true } }),
      this.prisma.dustCoverType.findMany({ select: { name: true, code: true } }),
      this.prisma.bearingInFrameType.findMany({ select: { name: true, code: true } }),
      this.prisma.brakingSystem.findMany({ select: { name: true, code: true } }),
      this.prisma.frameColor.findMany({ select: { name: true, code: true } }),
      this.prisma.wheelColor.findMany({ select: { name: true, code: true } }),
      this.prisma.wheelTypeTag.findMany({ select: { name: true, code: true } }),
    ]);

    const finishTypeMap = this.buildCodeMap(finishTypes);
    const frameColorMap = this.buildCodeMap(frameColors);

    this.assignExportCodeAliases(finishTypeMap, finishTypeExportAliases);
    this.assignExportCodeAliases(frameColorMap, frameColorExportAliases);

    return {
      application: this.buildCodeMap(applications),
      applicationCodes: uniqueValues(applications.map((item) => item.code)),
      loadCarryingCapacity: this.buildCodeMap(loadCarryCapacities),
      wheelSize: this.buildCodeMap(wheelSizes),
      fittingType: this.buildCodeMap(fittingSizes),
      bodyType: this.buildCodeMap(bodyTypes),
      mountingType: this.buildCodeMap(mountingTypes),
      finishType: finishTypeMap,
      bearingType: this.buildCodeMap(bearingTypes),
      wheelType: this.buildCodeMap(wheelTypes),
      headerType: this.buildCodeMap(headerTypes),
      dustCoverOnWheel: this.buildCodeMap(dustCoverTypes),
      bearingInFrame: this.buildCodeMap(bearingInFrameTypes),
      brakingSystem: this.buildCodeMap(brakingSystems),
      frameColor: frameColorMap,
      wheelColor: this.buildCodeMap(wheelColors),
      wheelTypeSub: this.buildCodeMap(wheelTypeTags),
    };
  }

  private buildCodeMap(rows: Array<{ name: string; code: string }>) {
    const map = new Map<string, string>();

    for (const row of rows) {
      const nameKey = normalizeLookup(row.name);
      const codeKey = normalizeLookup(row.code);

      if (nameKey) {
        map.set(nameKey, row.code);
      }

      if (codeKey) {
        map.set(codeKey, row.code);
      }
    }

    return map;
  }

  private buildStrictCodeMap(rows: Array<{ code: string }>) {
    const map = new Map<string, string>();

    for (const row of rows) {
      const codeKey = normalizeLookup(row.code);

      if (codeKey) {
        map.set(codeKey, row.code);
      }
    }

    return map;
  }

  private assignExportCodeAliases(
    map: Map<string, string>,
    aliases: ExportCodeAlias[],
  ) {
    for (const alias of aliases) {
      for (const target of alias.targets) {
        const code = map.get(normalizeLookup(target));

        if (code) {
          map.set(normalizeLookup(alias.alias), code);
          break;
        }
      }
    }
  }

  private async buildImportCodeMaps(): Promise<FinalProductImportCodeMaps> {
    const [
      applications,
      loadCarryCapacities,
      wheelSizes,
      fittingSizes,
      bodyTypes,
      mountingTypes,
      finishTypes,
      bearingTypes,
      wheelTypes,
      headerTypes,
      dustCoverTypes,
      bearingInFrameTypes,
      brakingSystems,
      frameColors,
      wheelColors,
      wheelTypeTags,
    ] = await Promise.all([
      this.prisma.application.findMany({ select: { code: true } }),
      this.prisma.loadCarryCapacity.findMany({ select: { code: true } }),
      this.prisma.wheelSize.findMany({ select: { code: true } }),
      this.prisma.fittingSize.findMany({ select: { code: true } }),
      this.prisma.bodyType.findMany({ select: { code: true } }),
      this.prisma.mountingType.findMany({ select: { code: true } }),
      this.prisma.finishType.findMany({ select: { name: true, code: true } }),
      this.prisma.bearingType.findMany({ select: { code: true } }),
      this.prisma.wheelType.findMany({ select: { code: true } }),
      this.prisma.headerType.findMany({ select: { code: true } }),
      this.prisma.dustCoverType.findMany({ select: { code: true } }),
      this.prisma.bearingInFrameType.findMany({ select: { code: true } }),
      this.prisma.brakingSystem.findMany({ select: { code: true } }),
      this.prisma.frameColor.findMany({ select: { code: true } }),
      this.prisma.wheelColor.findMany({ select: { code: true } }),
      this.prisma.wheelTypeTag.findMany({ select: { code: true } }),
    ]);

    const finishTypeMap = this.buildCodeMap(finishTypes);
    this.assignExportCodeAliases(finishTypeMap, finishTypeExportAliases);

    return {
      application: this.buildStrictCodeMap(applications),
      loadCarryingCapacity: this.buildStrictCodeMap(loadCarryCapacities),
      wheelSize: this.buildStrictCodeMap(wheelSizes),
      fittingType: this.buildStrictCodeMap(fittingSizes),
      bodyType: this.buildStrictCodeMap(bodyTypes),
      mountingType: this.buildStrictCodeMap(mountingTypes),
      finishType: finishTypeMap,
      bearingType: this.buildStrictCodeMap(bearingTypes),
      wheelType: this.buildStrictCodeMap(wheelTypes),
      headerType: this.buildStrictCodeMap(headerTypes),
      dustCoverOnWheel: this.buildStrictCodeMap(dustCoverTypes),
      bearingInFrame: this.buildStrictCodeMap(bearingInFrameTypes),
      brakingSystem: this.buildStrictCodeMap(brakingSystems),
      frameColor: this.buildStrictCodeMap(frameColors),
      wheelColor: this.buildStrictCodeMap(wheelColors),
      wheelTypeSub: this.buildStrictCodeMap(wheelTypeTags),
    };
  }

  private validateLookupCodes(
    label: string,
    value: string,
    codeMap: Map<string, string>,
  ) {
    const parts = splitValues(value);

    if (!parts.length) {
      return null;
    }

    return parts
      .map((part) => {
        const code = codeMap.get(normalizeLookup(part));

        if (!code) {
          throw new BadRequestException(`${label} code "${part}" was not found.`);
        }

        return code;
      })
      .join(', ');
  }

  private validateInchValue(value: string, fallback: number | null = null) {
    const text = clean(value);

    if (!text) {
      return Number.isFinite(fallback as number) ? Number(fallback) : null;
    }

    const match = text.match(/-?\d+(?:\.\d+)?/);
    const parsed = Number.parseFloat(match?.[0] || '');

    if (!Number.isFinite(parsed)) {
      throw new BadRequestException(`Inch value "${text}" is invalid.`);
    }

    return parsed;
  }

  private resolveExportCode(map: Map<string, string>, value?: string | null) {
    const text = clean(value);

    if (!text) {
      return '';
    }

    return splitValues(text)
      .map((item) => map.get(normalizeLookup(item)) || item)
      .join(', ');
  }

  private resolveStrictExportCode(map: Map<string, string>, value?: string | null) {
    const text = clean(value);

    if (!text) {
      return '';
    }

    const parts = splitValues(text);
    const resolved = parts.map((item) => map.get(normalizeLookup(item)) || '');

    return resolved.every(Boolean) ? resolved.join(', ') : '';
  }

  private mapDerivedExportRow(
    product: any,
    finalRow: any,
    holder: any,
    plate: any,
    wheel: any,
    codeMaps: FinalProductExportCodeMaps,
  ): FinalProductExportRow {
    const code = normalizeProductCode(product.productCode);
    const wheelName = this.pickText(product.wheelName, finalRow?.wheelName);
    const productId = this.pickText(finalRow?.productId, code);
    const application = this.pickCodeValue(
      codeMaps.application,
      finalRow?.application,
    );
    const status = normalizeStatus(finalRow?.status);
    const values = {
      CODE: code,
      'WHEEL NAME': wheelName,
      Inch: this.pickText(finalRow?.inch, product.productInch),
      'Product Id': productId,
      'Unique Code': this.pickText(finalRow?.uniqueCode),
      'Stock Availability': this.pickText(finalRow?.stockAvailability, 'YES'),
      'Product Description': this.pickText(
        finalRow?.productDescription,
        product.productName,
      ),
      'Load Carrying Capacity': this.pickCodeValue(
        codeMaps.loadCarryingCapacity,
        finalRow?.loadCarryingCapacity,
        plate?.loadCarryingCode,
        wheel?.dynamicLoadCarryCapacityCode,
      ),
      'Wheel Size': this.pickCodeValue(
        codeMaps.wheelSize,
        wheel?.wheelSizeCode,
        finalRow?.wheelSize,
      ),
      'Fitting Type': this.pickCodeValue(
        codeMaps.fittingType,
        finalRow?.fittingType,
        plate?.fittingCode,
        plate?.fittingType,
      ),
      'Body Type': this.pickCodeValue(
        codeMaps.bodyType,
        holder?.bodyCode,
        finalRow?.bodyType,
      ),
      'Mounting Type': this.pickCodeValue(
        codeMaps.mountingType,
        plate?.mountingCode,
        finalRow?.mountingType,
      ),
      'Finish Type': this.pickStrictCodeValue(
        codeMaps.finishType,
        finalRow?.finishType,
        product.finishType,
        product.finishLabel,
      ),
      'Bearing Type': this.pickCodeValue(
        codeMaps.bearingType,
        wheel?.bearingCode,
        finalRow?.bearingType,
      ),
      'Wheel Type': this.pickCodeValue(
        codeMaps.wheelType,
        wheel?.wheelTypeCode,
        finalRow?.wheelType,
      ),
      'Header Type': this.pickCodeValue(
        codeMaps.headerType,
        finalRow?.headerType,
        plate?.fittingSizeCode,
        plate?.fittingSize,
      ),
      'Dust Cover On Wheel': this.pickCodeValue(
        codeMaps.dustCoverOnWheel,
        wheel?.dustCoverCode,
        finalRow?.dustCoverOnWheel,
      ),
      'Bearing In Frame': this.pickCodeValue(
        codeMaps.bearingInFrame,
        plate?.bearingCode,
        finalRow?.bearingInFrame,
      ),
      'Braking System': this.pickCodeValue(
        codeMaps.brakingSystem,
        plate?.brakeTypeCode,
        finalRow?.brakingSystem,
      ),
      'Frame Color': this.pickStrictCodeValue(
        codeMaps.frameColor,
        finalRow?.frameColor,
        product.finishLabel,
        product.finishType,
      ),
      'Wheel Color': this.pickCodeValue(
        codeMaps.wheelColor,
        wheel?.wheelColorCode,
        finalRow?.wheelColor,
      ),
      'Page Title': this.pickText(finalRow?.pageTitle, `Product${code}`),
      Keywords: this.pickText(
        finalRow?.keywords,
        'castor wheel, caster wheel, trolley wheel',
      ),
      'Meta Description': this.pickText(
        finalRow?.metaDescription,
        'castor wheel, caster wheel, trolley wheel',
      ),
      Status: status,
      'Wheel Type Sub': this.pickCodeValue(
        codeMaps.wheelTypeSub,
        wheel?.wheelTypeSubNameCode,
        finalRow?.wheelTypeSub,
      ),
      'Yorke Code': this.pickText(
        finalRow?.yorkeCode,
        holder?.yorkeCode,
        plate?.yokeCode,
      ),
      'Yorke Load': this.pickText(
        finalRow?.yorkeLoad,
        holder?.yorkeLoad,
      ),
    };

    return {
      code,
      wheelName,
      productId,
      application,
      status,
      updatedAt: new Date(finalRow?.updatedAt || product.updatedAt).getTime(),
      searchIndex: Object.values(values)
        .map((value) => clean(value).toLowerCase())
        .join(' '),
      values,
    };
  }

  private filterDerivedExportRows(
    rows: FinalProductExportRow[],
    query: FinalProductQuery,
  ) {
    const search = clean(query.search).toLowerCase();
    const status = normalizeStatusFilter(query.status);

    return rows.filter((row) => {
      if (status && row.status !== status) {
        return false;
      }

      if (search && !row.searchIndex.includes(search)) {
        return false;
      }

      return true;
    });
  }

  private sortDerivedExportRows(
    rows: FinalProductExportRow[],
    query: FinalProductQuery,
  ) {
    const sortBy = this.resolveSortBy(query.sortBy);
    const sortOrder = query.sortOrder === 'desc' ? 'desc' : 'asc';
    const direction = sortOrder === 'desc' ? -1 : 1;

    return [...rows].sort((left, right) => {
      let result = 0;

      switch (sortBy) {
        case 'code':
          result = compareText(left.code, right.code);
          break;
        case 'wheelName':
          result = compareText(left.wheelName, right.wheelName);
          break;
        case 'productId':
          result = compareText(left.productId, right.productId);
          break;
        case 'application':
          result = compareText(left.application, right.application);
          break;
        case 'status':
          result = compareText(left.status, right.status);
          break;
        default:
          result = left.updatedAt - right.updatedAt;
          break;
      }

      if (result !== 0) {
        return result * direction;
      }

      return compareText(left.code, right.code);
    });
  }

  private pickText(...values: Array<string | number | null | undefined>) {
    for (const value of values) {
      const text = clean(value);

      if (text) {
        return text;
      }
    }

    return '';
  }

  private pickCodeValue(
    map: Map<string, string>,
    ...values: Array<string | number | null | undefined>
  ) {
    let fallback = '';

    for (const value of values) {
      const normalized = asNullable(value);
      const resolved = this.resolveStrictExportCode(map, normalized);

      if (clean(resolved)) {
        return resolved;
      }

      if (!fallback) {
        fallback = this.resolveExportCode(map, normalized);
      }
    }

    return fallback;
  }

  private pickStrictCodeValue(
    map: Map<string, string>,
    ...values: Array<string | number | null | undefined>
  ) {
    for (const value of values) {
      const normalized = asNullable(value);
      const resolved = this.resolveStrictExportCode(map, normalized);

      if (clean(resolved)) {
        return resolved;
      }
    }

    return '';
  }

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

function clean(value?: string | number | null) {
  return String(value ?? '').trim();
}

function asNullable(value?: string | number | null) {
  const text = clean(value);
  return text || null;
}

function readText(row: Record<string, string>, ...keys: string[]) {
  for (const key of keys) {
    const value = clean(row[key]);
    if (value) {
      return value;
    }
  }

  return '';
}

function normalizeStatus(value?: string | number | null) {
  const text = clean(value);

  if (!text) {
    return 'Active';
  }

  if (text.toLowerCase() === 'inactive') {
    return 'Inactive';
  }

  if (text.toLowerCase() === 'active') {
    return 'Active';
  }

  return text;
}

function normalizeStatusFilter(value?: string | number | null) {
  const text = clean(value);

  if (!text) {
    return '';
  }

  if (text.toLowerCase() === 'inactive') {
    return 'Inactive';
  }

  if (text.toLowerCase() === 'active') {
    return 'Active';
  }

  return text;
}

function normalizeLookup(value?: string | number | null) {
  return clean(value)
    .toLowerCase()
    .replace(/[^a-z0-9]+/g, ' ')
    .trim();
}

function splitValues(value?: string | number | null) {
  return clean(value)
    .split(/[;,]/)
    .map((item) => item.trim())
    .filter(Boolean);
}

function normalizeProductCode(value?: string | number | null) {
  return clean(value).toUpperCase();
}

function compareText(left?: string | null, right?: string | null) {
  return clean(left).localeCompare(clean(right), undefined, {
    sensitivity: 'base',
    numeric: true,
  });
}

function uniqueValues(values: Array<string | null | undefined>) {
  return [...new Set(values.map((value) => clean(value)).filter(Boolean))];
}