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

/**
 * Universal bulk upload service for parsing Excel/CSV files
 * into entity records. Works for any entity type.
 */

export interface BulkUploadResult<T = Record<string, unknown>> {
  created: number;
  skipped: number;
  errors: { row: number; message: string; data?: Record<string, unknown> }[];
  records: T[];
  totalRows: number;
}

export interface ColumnMapping {
  /** Field name in the database */
  field: string;
  /** Regex patterns to match column headers */
  patterns: RegExp[];
  /** Transform the raw value */
  transform?: (val: string) => unknown;
  /** Is this field required? */
  required?: boolean;
  /** Default value if missing */
  defaultValue?: unknown;
}

export class BulkUploadService {
  private readonly logger = new Logger(BulkUploadService.name);

  /**
   * Parse an Excel/CSV buffer into records using column mappings.
   */
  parseFile(
    buffer: Buffer,
    fileName: string,
    mappings: ColumnMapping[],
  ): { rows: Record<string, unknown>[]; errors: { row: number; message: string }[]; headers: string[] } {
    const ext = fileName.toLowerCase();
    let workbook: XLSX.WorkBook;

    if (ext.endsWith('.csv')) {
      const text = buffer.toString('utf-8');
      workbook = XLSX.read(text, { type: 'string' });
    } else {
      workbook = XLSX.read(buffer, { type: 'buffer', cellDates: true });
    }

    const allRows: Record<string, unknown>[] = [];
    const allErrors: { row: number; message: string }[] = [];
    let allHeaders: string[] = [];

    for (const sheetName of workbook.SheetNames) {
      const ws = workbook.Sheets[sheetName];
      const rawRows: unknown[][] = XLSX.utils.sheet_to_json(ws, {
        header: 1,
        defval: '',
        blankrows: false,
      });

      if (rawRows.length < 2) continue;

      // Find header row
      let headerIdx = -1;
      let columnMap: Record<string, number> = {};

      for (let i = 0; i < Math.min(10, rawRows.length); i++) {
        const row = rawRows[i];
        if (!Array.isArray(row)) continue;

        const mapped = this.mapColumns(row, mappings);
        if (Object.keys(mapped).length >= 2 && Object.keys(mapped).length > Object.keys(columnMap).length) {
          headerIdx = i;
          columnMap = mapped;
          allHeaders = row.map(c => String(c || '').trim());
        }
      }

      if (headerIdx === -1) {
        this.logger.warn(`Sheet "${sheetName}": no header row found`);
        continue;
      }

      this.logger.log(`Sheet "${sheetName}": header at row ${headerIdx}, ${Object.keys(columnMap).length} columns mapped`);

      // Process data rows
      for (let i = headerIdx + 1; i < rawRows.length; i++) {
        const row = rawRows[i];
        if (!Array.isArray(row)) continue;

        // Skip empty rows
        const nonEmpty = row.filter(c => c !== '' && c !== null && c !== undefined);
        if (nonEmpty.length < 2) continue;

        const record: Record<string, unknown> = {};
        const rowErrors: string[] = [];

        for (const mapping of mappings) {
          const col = columnMap[mapping.field];
          let val: unknown = null;

          if (col !== undefined) {
            const rawVal = row[col];
            if (rawVal instanceof Date) {
              val = rawVal.toISOString().split('T')[0];
            } else if (rawVal !== '' && rawVal !== null && rawVal !== undefined) {
              val = String(rawVal).trim();
            }
          }

          if (val && mapping.transform) {
            try {
              val = mapping.transform(val as string);
            } catch {
              rowErrors.push(`Invalid value for ${mapping.field}: "${val}"`);
              val = null;
            }
          }

          if (!val && mapping.required) {
            rowErrors.push(`Missing required field: ${mapping.field}`);
          }

          record[mapping.field] = val ?? mapping.defaultValue ?? null;
        }

        if (rowErrors.length > 0) {
          allErrors.push({ row: i + 1, message: rowErrors.join('; ') });
        }

        allRows.push(record);
      }
    }

    return { rows: allRows, errors: allErrors, headers: allHeaders };
  }

  private mapColumns(headerRow: unknown[], mappings: ColumnMapping[]): Record<string, number> {
    const mapped: Record<string, number> = {};

    for (let col = 0; col < headerRow.length; col++) {
      const header = String(headerRow[col] || '').trim();
      if (!header) continue;

      for (const mapping of mappings) {
        if (mapped[mapping.field] !== undefined) continue;
        for (const pattern of mapping.patterns) {
          if (pattern.test(header)) {
            mapped[mapping.field] = col;
            break;
          }
        }
      }
    }

    return mapped;
  }
}

// ════════════════════════════════════════════════════════════
// ENTITY-SPECIFIC COLUMN MAPPINGS
// ════════════════════════════════════════════════════════════

export const VEHICLE_MAPPINGS: ColumnMapping[] = [
  { field: 'unitNumber', patterns: [/unit.*no|unit.*number|plate.*no|registration|reg.*no/i, /^plate$/i], required: true },
  { field: 'licensePlate', patterns: [/license.*plate|plate.*number|number.*plate|^plate$/i, /registration/i] },
  { field: 'type', patterns: [/type|vehicle.*type|truck.*type|category/i], defaultValue: 'DRY_VAN', transform: (v) => {
    const map: Record<string, string> = { 'dry van': 'DRY_VAN', 'refrigerated': 'REFRIGERATED', 'flatbed': 'FLATBED', 'tanker': 'TANKER', 'curtain': 'CURTAIN_SIDE', 'box': 'BOX_TRUCK', 'sprinter': 'SPRINTER' };
    const lower = v.toLowerCase();
    for (const [k, val] of Object.entries(map)) { if (lower.includes(k)) return val; }
    return v.toUpperCase().replace(/\s+/g, '_');
  }},
  { field: 'make', patterns: [/make|manufacturer|brand/i] },
  { field: 'model', patterns: [/^model$/i] },
  { field: 'year', patterns: [/year|model.*year/i], transform: (v) => parseInt(v) || null },
  { field: 'vin', patterns: [/vin|chassis/i] },
  { field: 'maxWeight', patterns: [/max.*weight|capacity|tonnage|gvw|payload/i], transform: (v) => parseFloat(v.replace(/[,\s]/g, '')) || null },
  { field: 'maxVolume', patterns: [/max.*volume|volume|cbm/i], transform: (v) => parseFloat(v.replace(/[,\s]/g, '')) || null },
  { field: 'fuelType', patterns: [/fuel.*type|fuel/i] },
  { field: 'transporterCode', patterns: [/transporter|carrier|company|fleet|owner/i] },
  { field: 'homeSite', patterns: [/home.*site|depot|base|station|trailer/i] },
  { field: 'status', patterns: [/^status$/i], defaultValue: 'AVAILABLE' },
];

export const DRIVER_MAPPINGS: ColumnMapping[] = [
  { field: 'firstName', patterns: [/first.*name|given.*name|^name$/i], required: true },
  { field: 'lastName', patterns: [/last.*name|surname|family.*name/i], required: true },
  { field: 'email', patterns: [/email|e-mail/i] },
  { field: 'phone', patterns: [/phone|mobile|tel|contact/i] },
  { field: 'licenseNumber', patterns: [/license.*no|license.*number|dl.*no|driving.*license/i] },
  { field: 'licenseExpiry', patterns: [/license.*expir|dl.*expir|expiry/i] },
];

export const CLIENT_MAPPINGS: ColumnMapping[] = [
  { field: 'name', patterns: [/^name$|company.*name|client.*name|customer/i], required: true },
  { field: 'code', patterns: [/^code$|client.*code|short.*code|abbreviation/i], required: true },
  { field: 'contactName', patterns: [/contact.*name|contact.*person|representative/i] },
  { field: 'contactEmail', patterns: [/contact.*email|email/i] },
  { field: 'contactPhone', patterns: [/contact.*phone|phone|mobile|tel/i] },
  { field: 'address', patterns: [/address|location/i] },
  { field: 'city', patterns: [/^city$|town/i] },
  { field: 'country', patterns: [/country/i], defaultValue: 'Kenya' },
];

export const TRANSPORTER_MAPPINGS: ColumnMapping[] = [
  { field: 'name', patterns: [/^name$|transporter.*name|carrier.*name|company/i], required: true },
  { field: 'code', patterns: [/^code$|short.*code|abbreviation/i], required: true },
  { field: 'contactName', patterns: [/contact.*name|contact.*person/i] },
  { field: 'contactEmail', patterns: [/contact.*email|email/i] },
  { field: 'contactPhone', patterns: [/contact.*phone|phone|mobile|tel/i] },
];

export const ZONE_MAPPINGS: ColumnMapping[] = [
  { field: 'name', patterns: [/^name$|zone.*name|area.*name|region.*name/i], required: true },
  { field: 'code', patterns: [/^code$|zone.*code|short.*code/i], required: true },
  { field: 'description', patterns: [/description|notes/i] },
  { field: 'region', patterns: [/region|area|territory/i] },
];

export const LANE_MAPPINGS: ColumnMapping[] = [
  { field: 'name', patterns: [/^name$|lane.*name|route.*name/i], required: true },
  { field: 'originName', patterns: [/origin|from|source|start/i], required: true },
  { field: 'destName', patterns: [/destination|to|end|dest/i], required: true },
  { field: 'distanceKm', patterns: [/distance|km|kilometers/i], required: true, transform: (v) => parseFloat(v.replace(/[,\s]/g, '')) || 0 },
  { field: 'estimatedHours', patterns: [/hours|duration|time|eta/i], transform: (v) => parseFloat(v.replace(/[,\s]/g, '')) || null },
];

export const TARIFF_MAPPINGS: ColumnMapping[] = [
  { field: 'laneName', patterns: [/lane|route/i], required: true },
  { field: 'vehicleType', patterns: [/vehicle.*type|truck.*type/i] },
  { field: 'ratePerTrip', patterns: [/rate.*trip|trip.*rate|flat.*rate/i], transform: (v) => parseFloat(v.replace(/[,\s]/g, '')) || null },
  { field: 'ratePerKm', patterns: [/rate.*km|per.*km|km.*rate/i], transform: (v) => parseFloat(v.replace(/[,\s]/g, '')) || null },
  { field: 'ratePerTon', patterns: [/rate.*ton|per.*ton|ton.*rate/i], transform: (v) => parseFloat(v.replace(/[,\s]/g, '')) || null },
  { field: 'minCharge', patterns: [/min.*charge|minimum/i], transform: (v) => parseFloat(v.replace(/[,\s]/g, '')) || null },
  { field: 'currency', patterns: [/currency/i], defaultValue: 'KES' },
];

export const LOADING_BAY_MAPPINGS: ColumnMapping[] = [
  { field: 'name', patterns: [/^name$|bay.*name/i], required: true },
  { field: 'code', patterns: [/^code$|bay.*code|bay.*no/i], required: true },
  { field: 'siteName', patterns: [/site|plant|location|facility/i] },
  { field: 'status', patterns: [/^status$/i], defaultValue: 'AVAILABLE' },
];