import {
  Injectable,
  Logger,
  NotFoundException,
  BadRequestException,
} from '@nestjs/common';
import { PrismaService } from '../../prisma/prisma.service';
import { ExcelParser } from './parsers/excel.parser';
import { CsvParser } from './parsers/csv.parser';
import { SmartParser, SmartParsedOrder } from './parsers/smart.parser';
import {
  ClaudeExtractor,
  ExtractionResult,
} from './ai-extractor/claude.extractor';
import { OrderValidator, ValidationResult } from './validators/order.validator';
import { AutoAllocator } from './allocation/auto-allocator';

/**
 * Order ingestion pipeline service.
 *
 * Orchestrates file upload, parsing (SmartParser + AI fallback), validation,
 * approval with order creation, auto-allocation with smart bay assignment,
 * and client portal self-service uploads.
 *
 * @dependencies
 *   - {@link PrismaService} — tenant-aware database access
 *   - {@link SmartParser} — heuristic Excel column mapping
 *   - {@link CsvParser} — CSV text extraction
 *   - {@link ExcelParser} — Excel text extraction (for AI fallback)
 *   - {@link ClaudeExtractor} — AI-powered order extraction from raw text
 *   - {@link OrderValidator} — extracted order validation
 *   - {@link AutoAllocator} — vehicle-to-order matching and trip creation
 */
@Injectable()
export class IngestionService {
  private readonly logger = new Logger(IngestionService.name);
  private readonly excelParser = new ExcelParser();
  private readonly csvParser = new CsvParser();
  private readonly smartParser = new SmartParser();
  private readonly claudeExtractor = new ClaudeExtractor();
  private readonly orderValidator = new OrderValidator();
  private readonly autoAllocator: AutoAllocator;

  constructor(private readonly prisma: PrismaService) {
    this.autoAllocator = new AutoAllocator(prisma);
  }

  /**
   * Process an uploaded file through the ingestion pipeline.
   *
   * Steps: create ingestion record -> parse file (SmartParser or AI fallback) ->
   * validate extracted orders -> compute missing-data report -> determine
   * review status -> store parsed data as JSON on the ingestion record.
   *
   * The file's original content is stored base64-encoded in `rawPayload`
   * for audit replay. Parsed orders are stored in `parsedData` as structured
   * JSON for the review UI.
   *
   * @param file - The uploaded file (Multer format with buffer and metadata).
   * @param clientId - Optional client ID to associate orders with.
   * @param orgId - The tenant's organisation ID.
   * @param userId - ID of the uploading user.
   * @param ingestionSource - Source channel (UPLOAD, CLIENT_PORTAL, EMAIL, API).
   * @returns Object with `ingestion` record, `extraction` summary, `validation` result, and `missingData` report.
   * @throws {BadRequestException} Unsupported file type.
   */
  async processUpload(
    file: Express.Multer.File,
    clientId: string | null,
    orgId: string,
    userId: string,
    ingestionSource: 'UPLOAD' | 'CLIENT_PORTAL' | 'EMAIL' | 'API' = 'UPLOAD',
  ) {
    // 1. Create Ingestion record
    const ingestion = await this.prisma.ingestion.create({
      data: {
        organizationId: orgId,
        source: ingestionSource as any,
        status: 'RECEIVED',
        fileName: file.originalname,
        rawPayload: {
          originalName: file.originalname,
          mimeType: file.mimetype,
          size: file.size,
          uploadedBy: userId,
          clientId: clientId || undefined,
          uploadedAt: new Date().toISOString(),
          fileBase64: file.buffer.toString('base64'),
        },
      },
    });

    try {
      await this.prisma.ingestion.update({
        where: { id: ingestion.id },
        data: { status: 'PARSING' },
      });

      // 2. Parse and extract ALL columns
      const ext = file.originalname.toLowerCase();
      let smartOrders: SmartParsedOrder[] = [];
      let extractionSummary: Record<string, unknown> = {};

      if (ext.endsWith('.xlsx') || ext.endsWith('.xls')) {
        const smartResult = this.smartParser.parse(file.buffer);
        smartOrders = smartResult.orders;
        extractionSummary = smartResult.summary;

        this.logger.log(
          `SmartParser: ${smartOrders.length} orders, ` +
          `${(extractionSummary as any).orders_with_missing_data || 0} with missing data`,
        );

        // Fallback to Claude AI if SmartParser finds nothing
        if (smartOrders.length === 0) {
          this.logger.warn('SmartParser found 0 orders, falling back to Claude AI');
          const textResult = this.excelParser.parse(file.buffer);
          let clientName: string | undefined;
          if (clientId) {
            const client = await this.prisma.client.findUnique({
              where: { id: clientId },
              select: { name: true },
            });
            clientName = client?.name;
          }
          const aiResult = await this.claudeExtractor.extractOrders(textResult.rawText, clientName);
          // Convert AI result to our format (no SmartParsedOrder fields)
          const updated = await this.prisma.ingestion.update({
            where: { id: ingestion.id },
            data: {
              status: 'REVIEW_REQUIRED',
              parsedData: JSON.parse(JSON.stringify({
                extraction: { orders: aiResult.orders, summary: aiResult.summary },
                validation: { valid: true, errors: [], warnings: ['AI extraction used — verify results'], validOrderCount: aiResult.orders.length, rejectedCount: 0 },
              })),
            },
          });
          return { ingestion: updated, extraction: aiResult.summary, validation: { valid: true, totalExtracted: aiResult.orders.length, validOrders: aiResult.orders.length, rejectedOrders: 0, errors: [], warnings: [] } };
        }
      } else if (ext.endsWith('.csv')) {
        const result = this.csvParser.parse(file.buffer);
        let clientName: string | undefined;
        if (clientId) {
          const client = await this.prisma.client.findUnique({
            where: { id: clientId },
            select: { name: true },
          });
          clientName = client?.name;
        }
        const aiResult = await this.claudeExtractor.extractOrders(result.rawText, clientName);
        const updated = await this.prisma.ingestion.update({
          where: { id: ingestion.id },
          data: {
            status: 'REVIEW_REQUIRED',
            parsedData: JSON.parse(JSON.stringify({
              extraction: { orders: aiResult.orders, summary: aiResult.summary },
              validation: { valid: true, errors: [], warnings: [], validOrderCount: aiResult.orders.length, rejectedCount: 0 },
            })),
          },
        });
        return { ingestion: updated, extraction: aiResult.summary, validation: { valid: true, totalExtracted: aiResult.orders.length, validOrders: aiResult.orders.length, rejectedOrders: 0, errors: [], warnings: [] } };
      } else {
        throw new BadRequestException(
          `Unsupported file type: ${file.originalname}. Supported: .xlsx, .xls, .csv`,
        );
      }

      // 3. Validate
      const extractedForValidation = smartOrders.map(o => ({
        origin_address: o.origin_address,
        destination_address: o.destination_address,
        origin_contact_name: o.origin_contact_name,
        origin_contact_phone: o.origin_contact_phone,
        dest_contact_name: o.dest_contact_name,
        dest_contact_phone: o.dest_contact_phone,
        weight_kg: o.weight_kg,
        volume_cbm: o.volume_cbm,
        pieces: o.pieces,
        commodity: o.commodity,
        vehicle_type: o.vehicle_type,
        pickup_date: o.pickup_date,
        delivery_date: o.delivery_date,
        special_instructions: o.special_instructions,
        priority: o.priority,
        reference_number: o.reference_number,
      }));

      const validation: ValidationResult = this.orderValidator.validate(extractedForValidation);

      // 4. Build missing data report
      const missingDataReport = smartOrders
        .filter(o => o.missing_fields.length > 0)
        .map(o => ({
          row: o.row_number,
          sheet: o.sheet_date,
          distributor: o.distributor,
          missing: o.missing_fields,
          warnings: o.data_warnings,
        }));

      // 5. Determine status
      const hasErrors = validation.errors.length > 0;
      const hasWarnings = validation.warnings.length > 0 || missingDataReport.length > 0;

      let status: 'PARSED' | 'VALIDATION_FAILED' | 'REVIEW_REQUIRED';
      if (validation.validOrders.length === 0 && smartOrders.length > 0) {
        status = 'VALIDATION_FAILED';
      } else if (hasErrors || hasWarnings) {
        status = 'REVIEW_REQUIRED';
      } else {
        status = 'PARSED';
      }

      // 6. Store complete extraction with ALL columns preserved
      const updated = await this.prisma.ingestion.update({
        where: { id: ingestion.id },
        data: {
          status,
          parsedData: JSON.parse(JSON.stringify({
            extraction: {
              orders: smartOrders.map(o => {
                // eslint-disable-next-line @typescript-eslint/no-unused-vars
                const { raw_row, ...rest } = o;
                return rest;
              }),
              summary: extractionSummary,
            },
            validation: {
              valid: validation.valid,
              errors: validation.errors,
              warnings: validation.warnings,
              validOrderCount: validation.validOrders.length,
              rejectedCount: validation.rejectedCount,
            },
            missingData: {
              totalOrdersWithMissingData: missingDataReport.length,
              details: missingDataReport.slice(0, 100),
            },
          })),
          errorMessage: hasErrors
            ? `${validation.errors.length} validation error(s) found`
            : missingDataReport.length > 0
              ? `${missingDataReport.length} orders have missing data fields`
              : null,
        },
      });

      return {
        ingestion: updated,
        extraction: extractionSummary,
        validation: {
          valid: validation.valid,
          totalExtracted: smartOrders.length,
          validOrders: validation.validOrders.length,
          rejectedOrders: validation.rejectedCount,
          errors: validation.errors,
          warnings: validation.warnings,
        },
        missingData: {
          count: missingDataReport.length,
          details: missingDataReport.slice(0, 20),
        },
      };
    } catch (error: any) {
      await this.prisma.ingestion.update({
        where: { id: ingestion.id },
        data: {
          status: 'VALIDATION_FAILED',
          errorMessage: error?.message || 'Unknown error during processing',
        },
      });

      this.logger.error(`Ingestion failed for "${file.originalname}": ${error?.message}`);
      throw error;
    }
  }

  /**
   * Approve a parsed ingestion and create Order records from the extracted data.
   *
   * Creates one Order per extracted row with full field mapping from the
   * Excel/CSV source. Orders enter as `IMPORTED` with their missing-fields
   * list preserved. Also auto-registers any truck plates found in the
   * Excel data as new Vehicle records.
   *
   * Client resolution priority: 1) explicit clientId from upload form,
   * 2) filename pattern matching (e.g., "BEER DASHBOARD" -> EABL/KBL),
   * 3) first active client in the organisation.
   *
   * @param ingestionId - The ingestion record's database ID.
   * @param userId - ID of the approving user (empty string for portal/API sources).
   * @param overrideSource - Override the order source label (e.g., 'PORTAL_UPLOAD').
   * @returns Object with created `orders` array, `count`, and `trucks` registration summary.
   * @throws {NotFoundException} Ingestion record not found.
   * @throws {BadRequestException} Already approved, wrong status, no valid orders, or no active client.
   */
  async approveIngestion(ingestionId: string, userId: string, overrideSource?: 'UPLOAD' | 'PORTAL_UPLOAD' | 'EMAIL' | 'API') {
    // Look up user name for tracking. Portal uploads pass an empty userId
    // because the customer isn't an authenticated user — fall back to the
    // descriptive source label so /orders shows "Client Portal upload" as
    // the creator.
    let approverName = 'System';
    if (userId && userId.length > 0) {
      const approver = await this.prisma.user.findUnique({
        where: { id: userId },
        select: { firstName: true, lastName: true, email: true },
      });
      if (approver) {
        approverName = `${approver.firstName || ''} ${approver.lastName || ''}`.trim() || approver.email || 'System';
      }
    } else if (overrideSource === 'PORTAL_UPLOAD') {
      approverName = 'Client Portal upload';
    } else if (overrideSource === 'API') {
      approverName = 'Client API';
    } else if (overrideSource === 'EMAIL') {
      approverName = 'Email inbound';
    }

    const ingestion = await this.prisma.ingestion.findUnique({
      where: { id: ingestionId },
    });

    if (!ingestion) {
      throw new NotFoundException('Ingestion not found');
    }

    if (ingestion.status === 'APPROVED') {
      throw new BadRequestException('Ingestion already approved');
    }

    if (
      ingestion.status !== 'PARSED' &&
      ingestion.status !== 'REVIEW_REQUIRED'
    ) {
      throw new BadRequestException(
        `Cannot approve ingestion with status "${ingestion.status}". Must be PARSED or REVIEW_REQUIRED.`,
      );
    }

    const parsedData = ingestion.parsedData as any;
    if (!parsedData?.extraction?.orders?.length) {
      throw new BadRequestException('No valid orders to approve');
    }

    const extractedOrders = parsedData.extraction.orders;

    // Find client — prioritize: 1) client from upload form, 2) match by filename, 3) first active
    let clientId: string | null = null;
    const rawPayload = ingestion.rawPayload as any;
    const uploadClientId = rawPayload?.clientId;

    if (uploadClientId) {
      clientId = uploadClientId;
    } else {
      // Try matching by filename (e.g., "BEER DASHBOARD" → EABL/KBL)
      const fileName = (ingestion.fileName || '').toUpperCase();
      if (fileName.includes('BEER') || fileName.includes('EABL') || fileName.includes('KBL')) {
        const eabl = await this.prisma.client.findFirst({
          where: {
            organizationId: ingestion.organizationId,
            isActive: true,
            OR: [
              { code: { in: ['EABL', 'KBL'] } },
              { name: { contains: 'Breweries', mode: 'insensitive' } },
              { name: { contains: 'EABL', mode: 'insensitive' } },
            ],
          },
          select: { id: true },
        });
        if (eabl) clientId = eabl.id;
      }
    }

    // Fallback to first active client
    if (!clientId) {
      const fallback = await this.prisma.client.findFirst({
        where: { organizationId: ingestion.organizationId, isActive: true },
        select: { id: true },
        orderBy: { createdAt: 'asc' },
      });
      if (fallback) clientId = fallback.id;
    }

    if (!clientId) {
      throw new BadRequestException(
        'No active client found in organization. Create a client first.',
      );
    }

    // Create ALL Order records — complete ones go to DRAFT (ready for Jobs),
    // incomplete ones go to DRAFT with jobStatus INCOMPLETE (editable but won't flow to Jobs)
    const createdOrders = [];
    let completeCount = 0;
    let incompleteCount = 0;
    for (const extracted of extractedOrders) {
      const isComplete = !!(
        (extracted.lpo_number || extracted.beer_order_number || extracted.udv_order_number) &&
        (extracted.qty_beer || extracted.qty_udv) &&
        extracted.distributor
      );
      const orderNumber = await this.generateOrderNumber(
        ingestion.organizationId,
      );

      const order = await this.prisma.order.create({
        data: {
          organizationId: ingestion.organizationId,
          clientId,
          orderNumber,
          referenceNumber: extracted.reference_number || extracted.lpo_number,
          status: 'DRAFT',
          priority: (extracted.priority as any) || 'NORMAL',
          vehicleType: this.isValidVehicleType(extracted.vehicle_type) ? (extracted.vehicle_type as any) : undefined,

          // Origin
          originAddress: extracted.origin_address || extracted.plant,
          originName: extracted.plant,

          // Destination
          destAddress: extracted.destination_address || extracted.distributor,
          destName: extracted.distributor,

          // Scheduling
          pickupDate: this.safeParseDate(extracted.pickup_date || extracted.order_creation_date),
          deliveryDate: this.safeParseDate(extracted.delivery_date || extracted.circulation_date),
          circulationDate: this.safeParseDate(extracted.circulation_date),

          // Cargo
          commodity: extracted.commodity,
          weight: extracted.weight_kg,
          pieces: extracted.pieces ? Math.round(extracted.pieces) : undefined,
          pallets: extracted.pallets ? Math.round(extracted.pallets) : undefined,
          specialInstructions: extracted.special_instructions,

          // Full Excel fields
          lpoNumber: extracted.lpo_number,
          plantName: extracted.plant,
          distributorName: extracted.distributor,
          beerOrderNumber: extracted.beer_order_number,
          udvOrderNumber: extracted.udv_order_number,
          deliveryNumberBeer: extracted.delivery_number_beer,
          deliveryNumberUdv: extracted.delivery_number_udv,
          qtyBeer: extracted.qty_beer ? Math.round(extracted.qty_beer) : undefined,
          qtyUdv: extracted.qty_udv ? Math.round(extracted.qty_udv) : undefined,
          truckPlate: extracted.truck_plate,
          palletization: extracted.palletization,
          region: extracted.region,
          sheetDate: extracted.sheet_date,
          excelRowNumber: extracted.row_number,
          missingFields: extracted.missing_fields || [],
          rawExcelData: extracted.raw_row || undefined,
          // ALL orders stay in Orders tab as IMPORTED — user reviews, fills/waives missing data,
          // then manually sends to Jobs. No auto-move.
          jobStatus: 'IMPORTED',

          ingestionId: ingestion.id,
          addedByName: approverName,
          source: (overrideSource as any) || 'UPLOAD',
        },
      });

      if (isComplete) completeCount++;
      else incompleteCount++;

      await this.prisma.orderEvent.create({
        data: {
          orderId: order.id,
          toStatus: 'DRAFT',
          userId: userId && userId.length > 0 ? userId : null,
          reason: `Created from ${ingestion.fileName || 'upload'} (row ${extracted.row_number || '?'}, sheet ${extracted.sheet_date || '?'})${!isComplete ? ' — INCOMPLETE, needs editing' : ''}`,
        },
      });

      createdOrders.push(order);
    }

    this.logger.log(
      `Created ${createdOrders.length} orders (${completeCount} complete, ${incompleteCount} incomplete) from ingestion ${ingestionId}`,
    );

    // === AUTO-REGISTER TRUCKS FROM EXCEL (register vehicles, but don't allocate) ===
    const truckPlatesFromExcel = extractedOrders
      .map((e: any) => e.truck_plate)
      .filter(Boolean);

    let truckRegistration = { registered: 0, existing: 0, details: [] as any[] };
    if (truckPlatesFromExcel.length > 0) {
      truckRegistration = await this.autoAllocator.autoRegisterTrucks(
        ingestion.organizationId,
        truckPlatesFromExcel,
      );
      this.logger.log(
        `Truck registration: ${truckRegistration.registered} new trucks added, ${truckRegistration.existing} already existed`,
      );
    }

    // === ORDERS GO TO JOBS BOARD (PENDING) — Planner allocates from there ===
    // NO auto-allocation here. The planner reviews orders in Jobs,
    // confirms truck sizes, assigns loading bays, and converts to trips.
    // This is the correct enterprise workflow.

    // Update ingestion status
    await this.prisma.ingestion.update({
      where: { id: ingestionId },
      data: { status: 'APPROVED' },
    });

    this.logger.log(
      `Approved ingestion ${ingestionId}: ${createdOrders.length} orders created → visible in Jobs board (PENDING)`,
    );

    return {
      orders: createdOrders,
      count: createdOrders.length,
      trucks: {
        newlyRegistered: truckRegistration.registered,
        alreadyExisted: truckRegistration.existing,
        totalUniqueTrucks: truckRegistration.details.length,
      },
    };
  }

  /**
   * Re-run auto-allocation on orders that don't have trucks yet.
   * Called from the "Auto-Allocate" button in the UI.
   */
  async autoAllocateOrders(organizationId: string) {
    // Only allocate orders that are in the Jobs board (PENDING) — NOT imported/incomplete orders
    const pendingOrders = await this.prisma.order.findMany({
      where: {
        organizationId,
        tripId: null,
        jobStatus: 'PENDING',
      },
      select: {
        id: true,
        distributorName: true,
        destAddress: true,
        plantName: true,
        originAddress: true,
        truckPlate: true,
        region: true,
        qtyBeer: true,
        qtyUdv: true,
        pieces: true,
        weight: true,
      },
    });

    if (pendingOrders.length === 0) {
      return {
        message: 'No unallocated orders found',
        allocated: 0,
        tripsCreated: 0,
      };
    }

    this.logger.log(`Auto-allocating ${pendingOrders.length} pending orders`);

    // Auto-register any new truck plates
    const plates = pendingOrders
      .map(o => o.truckPlate)
      .filter(Boolean) as string[];
    if (plates.length > 0) {
      await this.autoAllocator.autoRegisterTrucks(organizationId, plates);
    }

    // Run allocation
    const allocationInput = pendingOrders.map(order => ({
      orderId: order.id,
      distributor: order.distributorName || order.destAddress || '',
      region: order.region || null,
      plant: order.plantName || order.originAddress || null,
      truckPlateFromExcel: order.truckPlate || null,
      qtyBeer: order.qtyBeer,
      qtyUdv: order.qtyUdv,
      pieces: order.pieces,
      weightKg: order.weight,
    }));

    const allocation = await this.autoAllocator.allocateOrders(
      organizationId,
      allocationInput,
    );

    // Create trips for newly allocated orders
    const tripsByVehicle = new Map<string, string[]>();
    let stillUnallocated = 0;

    for (const result of allocation.results) {
      if (result.vehicleId) {
        const orderIds = tripsByVehicle.get(result.vehicleId) || [];
        orderIds.push(result.orderId);
        tripsByVehicle.set(result.vehicleId, orderIds);
      } else {
        stillUnallocated++;
      }
    }

    let tripsCreated = 0;
    for (const [vehicleId, orderIds] of tripsByVehicle) {
      const tripNumber = await this.generateTripNumber(organizationId);

      // Try to auto-assign the vehicle's default driver
      const vehicle = await this.prisma.vehicle.findUnique({
        where: { id: vehicleId },
        select: { driverId: true },
      });

      // Get first order's dates and bay-matching fields
      const firstOrder = await this.prisma.order.findFirst({
        where: { id: { in: orderIds } },
        select: { pickupDate: true, deliveryDate: true, plantName: true, qtyBeer: true, qtyUdv: true, commodity: true, laneId: true },
      });

      // ── Smart Bay Auto-Assignment ─────────────────────────────────────────
      // Determine desired bay category from order content
      // Determine desired bay category from commodity
      let desiredCategory: string | null = null;
      if (firstOrder?.commodity) {
        const c = firstOrder.commodity.toLowerCase();
        if (c.includes('keg')) desiredCategory = 'KEG';
      }

      // Fetch all available active bays for the org
      const availableBays = await this.prisma.loadingBay.findMany({
        where: { organizationId, status: 'AVAILABLE', isActive: true },
      });

      let selectedBayId: string | null = null;

      if (availableBays.length > 0) {
        // Count how many trips are assigned to each bay today (shift capacity check)
        const todayStart = new Date();
        todayStart.setHours(0, 0, 0, 0);
        const todayEnd = new Date();
        todayEnd.setHours(23, 59, 59, 999);

        const todayTripCounts = await this.prisma.trip.groupBy({
          by: ['loadingBayId'],
          where: {
            organizationId,
            loadingBayId: { in: availableBays.map(b => b.id), not: null },
            createdAt: { gte: todayStart, lte: todayEnd },
          },
          _count: { id: true },
        });
        const bayTripCountMap = new Map(
          todayTripCounts.map(r => [r.loadingBayId as string, r._count.id])
        );

        // Score each bay
        const scoredBays = availableBays
          .filter(bay => {
            // Exclude bays at shift capacity
            const tripCount = bayTripCountMap.get(bay.id) ?? 0;
            if (bay.shiftCapacity && tripCount >= bay.shiftCapacity) return false;
            return true;
          })
          .map(bay => {
            let score = 0;
            // Site match: prefer bays at the same plant as the order
            if (firstOrder?.plantName && bay.siteName &&
                bay.siteName.toLowerCase().includes(firstOrder.plantName.toLowerCase())) {
              score += 40;
            }
            // Category match
            if (desiredCategory) {
              if (bay.category === desiredCategory) score += 30;
              else if (bay.category === 'MIXED') score += 10; // MIXED bays are universal fallback
            } else {
              score += 5; // no preference, any bay is fine
            }
            // Load balancing: prefer bays with fewer trips today
            const tripCount = bayTripCountMap.get(bay.id) ?? 0;
            score -= tripCount * 5;
            // Prefer bays with higher shift capacity (more headroom)
            if (bay.shiftCapacity) score += Math.min(bay.shiftCapacity, 10);

            return { bay, score };
          })
          .sort((a, b) => b.score - a.score);

        if (scoredBays.length > 0) {
          selectedBayId = scoredBays[0].bay.id;
        }
      }
      // ─────────────────────────────────────────────────────────────────────

      const trip = await this.prisma.trip.create({
        data: {
          organizationId,
          tripNumber,
          vehicleId,
          driverId: vehicle?.driverId || undefined,
          startDate: firstOrder?.pickupDate || new Date(),
          endDate: firstOrder?.deliveryDate || undefined,
          status: 'PLANNED',
          loadingBayId: selectedBayId || undefined,
          laneId: firstOrder?.laneId || undefined,
        },
      });

      // Bay is reserved (linked via loadingBayId on trip) but NOT marked OCCUPIED yet.
      // The bay becomes OCCUPIED only when the trip is dispatched (trips.service.ts dispatch()).
      // This prevents bays from being locked during the PLANNED phase.

      await this.prisma.order.updateMany({
        where: { id: { in: orderIds } },
        data: {
          tripId: trip.id,
          status: 'ASSIGNED',
          jobStatus: 'ALLOCATED',
        },
      });

      // Do NOT mark vehicle as ASSIGNED here — vehicle stays AVAILABLE until dispatched.
      // The vehicle only becomes IN_TRANSIT on dispatch (trips.service.ts dispatch()).
      // The allocator's internal vehicleAllocations map prevents double-booking within a batch.

      tripsCreated++;
    }

    // Resolve old allocation alerts if everything is now allocated
    if (stillUnallocated === 0) {
      await this.prisma.alert.updateMany({
        where: {
          organizationId,
          entityType: 'INGESTION',
          title: { contains: 'need truck allocation' },
          status: 'ACTIVE',
        },
        data: {
          status: 'RESOLVED',
          resolvedAt: new Date(),
        },
      });
    }

    // Update route data
    const regionMap = new Map<string, string | null>();
    for (const order of pendingOrders) {
      regionMap.set(order.id, order.region || null);
    }
    const commonPlant = pendingOrders[0]?.plantName || null;
    await this.autoAllocator.updateVehicleRouteData(
      allocation.results,
      commonPlant,
      regionMap,
    );

    return {
      message: `Allocated ${allocation.summary.autoAllocated + allocation.summary.excelPreAssigned} of ${pendingOrders.length} orders`,
      totalPending: pendingOrders.length,
      allocated: allocation.summary.autoAllocated + allocation.summary.excelPreAssigned,
      stillUnallocated,
      tripsCreated,
      allocation: allocation.summary,
    };
  }

  /**
   * Reject a parsed ingestion with a reason. No orders are created.
   *
   * @param ingestionId - The ingestion record's database ID.
   * @param reason - Human-readable rejection reason.
   * @returns The updated ingestion record.
   * @throws {NotFoundException} Ingestion not found.
   * @throws {BadRequestException} Already rejected or already approved.
   */
  async rejectIngestion(ingestionId: string, reason: string) {
    const ingestion = await this.prisma.ingestion.findUnique({
      where: { id: ingestionId },
    });

    if (!ingestion) {
      throw new NotFoundException('Ingestion not found');
    }

    if (ingestion.status === 'REJECTED') {
      throw new BadRequestException('Ingestion already rejected');
    }

    if (ingestion.status === 'APPROVED') {
      throw new BadRequestException('Cannot reject an already approved ingestion');
    }

    return this.prisma.ingestion.update({
      where: { id: ingestionId },
      data: {
        status: 'REJECTED',
        errorMessage: reason || 'Rejected by user',
      },
    });
  }

  /**
   * List ingestion records with pagination and optional status filter.
   *
   * @param orgId - The tenant's organisation ID.
   * @param query - Optional status filter and pagination params.
   * @returns Paginated result with ingestion summaries (no raw payload).
   */
  async findAll(orgId: string, query: { status?: string; page?: string; limit?: string }) {
    const page = parseInt(query.page || '1', 10);
    const limit = parseInt(query.limit || '20', 10);
    const skip = (page - 1) * limit;

    const where: any = { organizationId: orgId };
    if (query.status) {
      where.status = query.status;
    }

    const [items, total] = await Promise.all([
      this.prisma.ingestion.findMany({
        where,
        skip,
        take: limit,
        orderBy: { createdAt: 'desc' },
        select: {
          id: true,
          source: true,
          status: true,
          fileName: true,
          errorMessage: true,
          createdAt: true,
          updatedAt: true,
          _count: { select: { orders: true } },
        },
      }),
      this.prisma.ingestion.count({ where }),
    ]);

    return {
      data: items,
      meta: {
        total,
        page,
        limit,
        totalPages: Math.ceil(total / limit),
      },
    };
  }

  /**
   * Retrieve a single ingestion record with its created orders.
   *
   * The raw file payload is excluded from the response (replaced with
   * a `fileInfo` summary) to keep the response size manageable.
   *
   * @param id - The ingestion record's database ID.
   * @returns Ingestion record with orders and file metadata.
   * @throws {NotFoundException} Ingestion not found.
   */
  async findOne(id: string) {
    const ingestion = await this.prisma.ingestion.findUnique({
      where: { id },
      include: {
        orders: {
          select: {
            id: true,
            orderNumber: true,
            status: true,
            originAddress: true,
            destAddress: true,
            weight: true,
            commodity: true,
            priority: true,
            lpoNumber: true,
            plantName: true,
            distributorName: true,
            truckPlate: true,
            region: true,
            qtyBeer: true,
            qtyUdv: true,
            missingFields: true,
          },
        },
      },
    });

    if (!ingestion) {
      throw new NotFoundException('Ingestion not found');
    }

    const { rawPayload, ...rest } = ingestion;
    const payload = rawPayload as any;

    return {
      ...rest,
      fileInfo: payload
        ? {
            originalName: payload.originalName,
            mimeType: payload.mimeType,
            size: payload.size,
            uploadedBy: payload.uploadedBy,
            uploadedAt: payload.uploadedAt,
          }
        : null,
    };
  }

  /**
   * Check if a vehicle type string is a valid enum value.
   *
   * @param type - The vehicle type string to validate.
   * @returns True if the type is in the allowed vehicle type enum.
   */
  private isValidVehicleType(type: string | null | undefined): boolean {
    if (!type) return false;
    const valid = ['DRY_VAN', 'REFRIGERATED', 'FLATBED', 'TANKER', 'CURTAIN_SIDE', 'BOX_TRUCK', 'SPRINTER', 'HAZMAT'];
    return valid.includes(type.toUpperCase());
  }

  /**
   * Safely parse a date string from Excel data, handling DD.MM.YYYY,
   * DD/MM/YYYY, and standard ISO formats. Rejects dates outside the
   * 2020-2030 range to filter out Excel serial numbers and garbage values.
   *
   * @param dateStr - The date string to parse (nullable).
   * @returns A valid Date object or undefined if parsing fails.
   */
  private safeParseDate(dateStr: string | null | undefined): Date | undefined {
    if (!dateStr) return undefined;
    try {
      // Handle DD.MM.YYYY format
      const dotMatch = dateStr.match(/^(\d{1,2})\.(\d{1,2})\.(\d{4})$/);
      if (dotMatch) {
        const d = new Date(parseInt(dotMatch[3]), parseInt(dotMatch[2]) - 1, parseInt(dotMatch[1]));
        if (!isNaN(d.getTime()) && d.getFullYear() >= 2020 && d.getFullYear() <= 2030) return d;
      }

      // Handle DD/MM/YYYY format
      const slashMatch = dateStr.match(/^(\d{1,2})\/(\d{1,2})\/(\d{4})$/);
      if (slashMatch) {
        const d = new Date(parseInt(slashMatch[3]), parseInt(slashMatch[2]) - 1, parseInt(slashMatch[1]));
        if (!isNaN(d.getTime()) && d.getFullYear() >= 2020 && d.getFullYear() <= 2030) return d;
      }

      // Standard ISO / Date parse
      const d = new Date(dateStr);
      if (isNaN(d.getTime())) return undefined;
      if (d.getFullYear() < 2020 || d.getFullYear() > 2030) return undefined;
      return d;
    } catch {
      return undefined;
    }
  }

  private async generateOrderNumber(organizationId: string): Promise<string> {
    const count = await this.prisma.order.count({
      where: { organizationId },
    });
    const num = count + 1;
    return `ORD-${String(num).padStart(6, '0')}`;
  }

  private async generateTripNumber(organizationId: string): Promise<string> {
    const count = await this.prisma.trip.count({
      where: { organizationId },
    });
    const num = count + 1;
    return `TRP-${String(num).padStart(6, '0')}`;
  }

  /**
   * Upload + parse + auto-approve in a single call — used by the
   * Client Portal when a customer drops an Excel/CSV file. The customer
   * isn't a real user, so we pass `null` as the user id; approveIngestion
   * gracefully handles that (falls back to "System" as the name, and
   * OrderEvent.userId is nullable).
   *
   * All orders created this way are stamped `source: 'PORTAL_UPLOAD'`.
   */
  async processPortalFileUpload(
    file: Express.Multer.File,
    clientId: string,
    orgId: string,
  ): Promise<{ ingestionId: string; orderCount: number; orders: any[] }> {
    // Step 1 — parse the file and create a pending ingestion record
    const uploadResult = await this.processUpload(
      file,
      clientId,
      orgId,
      '', // no real user — portal submission
      'CLIENT_PORTAL',
    );
    const ingestionId = (uploadResult.ingestion as any).id;

    // Step 2 — auto-approve to create all orders tagged as PORTAL_UPLOAD
    await this.approveIngestion(ingestionId, '', 'PORTAL_UPLOAD');

    // Step 3 — fetch the created orders so the portal can echo count + refs
    const orders = await this.prisma.order.findMany({
      where: { ingestionId },
      select: {
        id: true,
        orderNumber: true,
        status: true,
        destAddress: true,
        originAddress: true,
      },
      orderBy: { createdAt: 'asc' },
    });

    return {
      ingestionId,
      orderCount: orders.length,
      orders,
    };
  }
}