import * as XLSX from 'xlsx';
import type { SelfOperatedLedgerRow } from '../types';
import { isPlateInVehicleSystem, resolveSystemVehicleType } from './vehicle-ref';
import { applyImportCalculations } from './calc-row';

/** 物流业务明细导入模板列（不含系统车型、金额、总成本、盈亏） */
export const SELF_IMPORT_TEMPLATE_HEADERS = [
  '年份（必填）',
  '月份（必填）',
  '出车日期（必填）',
  '业务名称（必填）',
  '司机（必填）',
  '电话（必填）',
  '车牌号码（必填）',
  '单价（必填）',
  '数量（必填）',
  '氢费',
  'ETC费用',
  '薪资（必填）',
  '电费',
  '人工报销费用',
  '日社保服务费',
  '日挂车服务费',
  '日挂车费用',
  '日停车费用',
  '日轮胎费用',
  '车辆费用',
  '是否多趟（必填）',
  '线路计价（必填）',
  '备注',
] as const;

export const SELF_IMPORT_REQUIRED_HEADERS = [
  '年份',
  '月份',
  '出车日期',
  '业务名称',
  '司机',
  '电话',
  '车牌号码',
  '单价',
  '数量',
  '薪资',
  '是否多趟',
  '线路计价',
] as const;

export interface SelfImportErrorRow {
  reason: string;
  rawCells: (string | number)[];
}

export interface SelfImportParseResult {
  success: SelfOperatedLedgerRow[];
  errors: SelfImportErrorRow[];
  fileHeaders: string[];
}

const TEMPLATE_SAMPLE: Record<string, string | number> = {
  '年份（必填）': 2026,
  '月份（必填）': 1,
  '出车日期（必填）': '2026-01-01',
  '业务名称（必填）': '嘉兴金小悦贸易有限公司',
  '司机（必填）': '王召善',
  '电话（必填）': '15112321670',
  '车牌号码（必填）': '粤AGQ8393',
  '单价（必填）': 590,
  '数量（必填）': 1,
  氢费: 303.8,
  ETC费用: 0,
  '薪资（必填）': 258.06,
  电费: 0,
  人工报销费用: 0,
  日社保服务费: 40,
  日挂车服务费: 0,
  日挂车费用: 0,
  日停车费用: 0,
  日轮胎费用: 20,
  车辆费用: 80,
  '是否多趟（必填）': '否',
  '线路计价（必填）': '',
  备注: '',
};

const HEADER_TO_KEY: Record<string, keyof SelfOperatedLedgerRow> = {
  年份: 'year',
  月份: 'month',
  出车日期: 'dispatchDate',
  业务名称: 'businessName',
  司机: 'driver',
  电话: 'phone',
  车牌号码: 'plateNo',
  单价: 'unitPrice',
  数量: 'quantity',
  氢费: 'hydrogenFee',
  ETC费用: 'etcFee',
  薪资: 'salary',
  电费: 'electricityFee',
  人工报销费用: 'manualReimbursement',
  日社保服务费: 'dailySocialSecurityFee',
  日挂车服务费: 'dailyTrailerServiceFee',
  日挂车费用: 'dailyTrailerFee',
  日停车费用: 'dailyParkingFee',
  日轮胎费用: 'dailyTireFee',
  车辆费用: 'vehicleFee',
  是否多趟: 'multiTrip',
  线路计价: 'routePricing',
  备注: 'remark',
};

const IMPORT_NUMERIC_KEYS = new Set<keyof SelfOperatedLedgerRow>([
  'unitPrice',
  'quantity',
  'hydrogenFee',
  'etcFee',
  'salary',
  'electricityFee',
  'manualReimbursement',
  'dailySocialSecurityFee',
  'dailyTrailerServiceFee',
  'dailyTrailerFee',
  'dailyParkingFee',
  'dailyTireFee',
  'vehicleFee',
]);

const IMPORT_TEXT_KEYS = new Set<keyof SelfOperatedLedgerRow>([
  'year',
  'month',
  'dispatchDate',
  'businessName',
  'driver',
  'phone',
  'plateNo',
  'multiTrip',
  'routePricing',
  'remark',
]);

const REQUIRED_NUMERIC_HEADERS = new Set(['年份', '月份', '单价', '数量', '薪资']);

function isCellEmpty(value: unknown): boolean {
  if (value === null || value === undefined) return true;
  return String(value).trim() === '';
}

function parseNumber(value: unknown): number {
  if (value === null || value === undefined || value === '') return 0;
  const n = Number(String(value).replace(/,/g, ''));
  return Number.isFinite(n) ? n : 0;
}

function parseDateCell(value: unknown): string {
  if (value === null || value === undefined || value === '') return '';
  if (typeof value === 'number' && Number.isFinite(value)) {
    const base = new Date(Date.UTC(1899, 11, 30));
    base.setUTCDate(base.getUTCDate() + Math.floor(value));
    return base.toISOString().slice(0, 10);
  }
  const raw = String(value).trim();
  if (/^\d{5}(\.\d+)?$/.test(raw)) {
    return parseDateCell(Number(raw));
  }
  return raw.slice(0, 10);
}

function normalizeHeader(header: string): string {
  return header.replace(/（必填）/g, '').trim();
}

function resolveHeaderKey(header: string): keyof SelfOperatedLedgerRow | undefined {
  return HEADER_TO_KEY[normalizeHeader(header)];
}

function validateRequiredFields(headers: string[], cells: (string | number)[]): string[] {
  const reasons: string[] = [];
  const headerIndex = new Map(headers.map((h, i) => [normalizeHeader(h), i]));

  SELF_IMPORT_REQUIRED_HEADERS.forEach((required) => {
    const idx = headerIndex.get(required);
    const raw = idx === undefined ? undefined : cells[idx];
    if (REQUIRED_NUMERIC_HEADERS.has(required)) {
      if (isCellEmpty(raw)) reasons.push(`${required}不能为空`);
      return;
    }
    if (isCellEmpty(raw)) reasons.push(`${required}不能为空`);
  });

  return reasons;
}

function enrichDraftFromSystem(draft: Partial<SelfOperatedLedgerRow>): void {
  if (!draft.plateNo) return;
  draft.systemVehicleType = resolveSystemVehicleType(draft.plateNo);
}

function parseRowDraft(
  headers: string[],
  cells: (string | number)[],
  index: number,
): Partial<SelfOperatedLedgerRow> {
  const draft: Partial<SelfOperatedLedgerRow> = {
    id: `import-${Date.now()}-${index}`,
  };

  headers.forEach((header, colIndex) => {
    const key = resolveHeaderKey(header);
    if (!key) return;
    const raw = cells[colIndex];
    if (key === 'dispatchDate') {
      (draft as Record<string, unknown>)[key] = parseDateCell(raw);
    } else if (IMPORT_NUMERIC_KEYS.has(key)) {
      (draft as Record<string, unknown>)[key] = parseNumber(raw);
    } else if (IMPORT_TEXT_KEYS.has(key)) {
      (draft as Record<string, unknown>)[key] = String(raw ?? '').trim();
    }
  });

  enrichDraftFromSystem(draft);
  return draft;
}

export function downloadSelfImportTemplate(): void {
  const sampleRow = SELF_IMPORT_TEMPLATE_HEADERS.map((header) => TEMPLATE_SAMPLE[header] ?? '');
  const sheet = XLSX.utils.aoa_to_sheet([Array.from(SELF_IMPORT_TEMPLATE_HEADERS), sampleRow]);
  const book = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(book, sheet, '物流业务明细');
  XLSX.writeFile(book, '物流业务明细导入模板.xlsx');
}

export function downloadSelfImportErrorLog(
  errors: SelfImportErrorRow[],
  fileHeaders: string[],
): void {
  if (!errors.length) return;
  const headers = ['导入失败原因', ...(fileHeaders.length ? fileHeaders : Array.from(SELF_IMPORT_TEMPLATE_HEADERS))];
  const body = errors.map((err) => [err.reason, ...err.rawCells]);
  const sheet = XLSX.utils.aoa_to_sheet([headers, ...body]);
  const book = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(book, sheet, '导入失败记录');
  XLSX.writeFile(book, `物流业务明细导入失败记录_${Date.now()}.xlsx`);
}

export async function parseSelfImportFile(file: File): Promise<SelfImportParseResult> {
  const buffer = await file.arrayBuffer();
  const book = XLSX.read(buffer, { type: 'array' });
  const sheet = book.Sheets[book.SheetNames[0]];
  const matrix = XLSX.utils.sheet_to_json<(string | number)[]>(sheet, { header: 1, defval: '' }) as (string | number)[][];
  if (matrix.length < 2) {
    return { success: [], errors: [], fileHeaders: [] };
  }

  const fileHeaders = matrix[0].map((cell) => String(cell).trim());
  const success: SelfOperatedLedgerRow[] = [];
  const errors: SelfImportErrorRow[] = [];

  matrix.slice(1).forEach((cells, index) => {
    if (!cells.some((cell) => String(cell).trim())) return;

    const rawCells = fileHeaders.map((_, colIndex) => cells[colIndex] ?? '');
    const requiredReasons = validateRequiredFields(fileHeaders, cells);
    if (requiredReasons.length > 0) {
      errors.push({ reason: requiredReasons.join('；'), rawCells });
      return;
    }

    const draft = parseRowDraft(fileHeaders, cells, index);
    const plateNo = String(draft.plateNo ?? '').trim();
    if (!isPlateInVehicleSystem(plateNo)) {
      errors.push({ reason: '车牌号不存在', rawCells });
      return;
    }

    success.push(applyImportCalculations(draft));
  });

  return { success, errors, fileHeaders };
}
