import * as XLSX from 'xlsx';

export function generateTruckAssignmentTemplate(filename = 'truck-assignments.xlsx'): void {
  const data = [
    {
      'Truck Plate Number': '3-12345/67890',
      'Driver Name': 'John Doe',
      'Truck Type': 'Flatbed',
      'Container 1': 'MAEU1234567',
      'Container 2': 'HLXU7654321',
    },
    {
      'Truck Plate Number': '3-98765/43210',
      'Driver Name': 'Jane Smith',
      'Truck Type': 'Flatbed',
      'Container 1': 'COSCO1111111',
      'Container 2': '',
    },
  ];

  const instructions = [
    ['TRUCK ASSIGNMENT BULK UPLOAD - INSTRUCTIONS'],
    [],
    ['Column', 'Required', 'Notes'],
    ['Truck Plate Number', 'Yes', 'Format: 3-XXXXX/XXXXX (Ethiopian plate format)'],
    ['Driver Name', 'Yes', 'Full name of truck driver'],
    ['Truck Type', 'Yes', 'e.g., Flatbed, Lowbed, Tanker, Trailer, etc.'],
    ['Container 1', 'Yes*', '*Required for EXPORT. Leave empty for IMPORT bulk cargo.'],
    ['Container 2', 'No', 'Optional. ISO format: e.g., MAEU1234567. Max 2 containers per truck.'],
    [],
    ['CONTAINER RULES'],
    ['- A 40ft container fills one truck (max 1 per truck)'],
    ['- Two 20ft containers fit on one truck (max 2 per truck)'],
    ['- No size mixing on same truck'],
    ['- Containers must be from the booking'],
    [],
    ['Example Data Below →'],
  ];

  const wb = XLSX.utils.book_new();

  // Instructions sheet
  const wsInstructions = XLSX.utils.aoa_to_sheet(instructions);
  wsInstructions['!cols'] = [{ wch: 30 }, { wch: 12 }, { wch: 50 }];
  XLSX.utils.book_append_sheet(wb, wsInstructions, 'Instructions');

  // Data template sheet
  const wsData = XLSX.utils.json_to_sheet(data, {
    header: ['Truck Plate Number', 'Driver Name', 'Truck Type', 'Container 1', 'Container 2'],
  });
  wsData['!cols'] = [{ wch: 20 }, { wch: 20 }, { wch: 15 }, { wch: 18 }, { wch: 18 }];
  XLSX.utils.book_append_sheet(wb, wsData, 'Trucks');

  XLSX.writeFile(wb, filename);
}

export function parseTruckAssignmentFile(
  file: File,
): Promise<
  Array<{
    truckPlateNumber: string;
    driverName: string;
    truckType: string;
    containerNumbers?: string[];
  }>
> {
  return new Promise((resolve, reject) => {
    const reader = new FileReader();

    reader.onload = (e) => {
      try {
        const data = e.target?.result as ArrayBuffer;
        const wb = XLSX.read(data, { type: 'array' });
        const wsData = wb.Sheets['Trucks'] || Object.values(wb.Sheets)[0];

        if (!wsData) {
          reject(new Error('No data sheet found in Excel file'));
          return;
        }

        const jsonData = XLSX.utils.sheet_to_json(wsData) as Array<Record<string, any>>;

        const trucks = jsonData.map((row) => {
          const containers = [
            row['Container 1'],
            row['Container 2'],
          ]
            .filter((c) => c && c.trim())
            .map((c) => c.trim().toUpperCase());

          return {
            truckPlateNumber: row['Truck Plate Number']?.trim() || '',
            driverName: row['Driver Name']?.trim() || '',
            truckType: row['Truck Type']?.trim() || '',
            containerNumbers: containers.length > 0 ? containers : undefined,
          };
        });

        resolve(trucks);
      } catch (error) {
        reject(error);
      }
    };

    reader.onerror = () => reject(new Error('Failed to read file'));
    reader.readAsArrayBuffer(file);
  });
}
