import { Buffer } from 'node:buffer';
import readXlsxFile, { readSheet } from 'read-excel-file/node';
import { HttpError } from '../../core/http.js';

type JsonObject = Record<string, unknown>;
type SpreadsheetImportType = 'questions' | 'vocabulary' | 'handbook' | 'scoreline' | 'videos';
type SupportedSpreadsheetFormat = 'json' | 'csv' | 'excel';
type FieldMappingOverrides = Map<string, string>;

interface ParsedSpreadsheet {
  rows: JsonObject[];
  sheets: Record<string, JsonObject[]>;
  metadata: JsonObject;
}

const MAX_SPREADSHEET_ROWS = 5000;
const MAX_SPREADSHEET_COLUMNS = 160;
const MAX_SPREADSHEET_CELL_CHARS = 100_000;
const MAX_EXCEL_FILE_BYTES = 8 * 1024 * 1024;

const QUESTION_OPTION_KEYS = ['optionA', 'optionB', 'optionC', 'optionD', 'optionE', 'optionF', 'optionG', 'optionH'];
const DANGEROUS_OBJECT_KEYS = new Set(['__proto__', 'prototype', 'constructor']);
const SAFE_DYNAMIC_FIELD = /^[A-Za-z][A-Za-z0-9_]{0,63}$/;

const OVERRIDE_TARGET_FIELDS: Record<SpreadsheetImportType, Set<string>> = {
  questions: new Set([
    'legacyId',
    'type',
    'typeLabel',
    'content',
    'options',
    ...QUESTION_OPTION_KEYS,
    'answer',
    'correctOptionIndex',
    'correctOptionIndices',
    'answerText',
    'explanation',
    'difficulty',
    'tags',
    'mediaUrl',
    'subQuestions',
    'examMarkers',
    'codeLang',
    'codeTemplate',
    'metadata',
  ]),
  vocabulary: new Set([
    'legacyId',
    'unitLegacyId',
    'unitId',
    'unitName',
    'unitDescription',
    'unitOrder',
    'wordLegacyId',
    'word',
    'phonetic',
    'meaning',
    'example',
    'exampleTranslation',
    'difficulty',
    'tags',
    'order',
    'wordCount',
    'isActive',
    'metadata',
  ]),
  handbook: new Set([
    'legacyId',
    'subjectLegacyId',
    'subjectId',
    'subjectName',
    'subjectType',
    'chapterLegacyId',
    'chapterId',
    'chapterName',
    'sectionLegacyId',
    'sectionId',
    'sectionName',
    'entryLegacyId',
    'entryId',
    'title',
    'content',
    'summary',
    'tags',
    'description',
    'type',
    'icon',
    'color',
    'order',
    'isActive',
    'metadata',
  ]),
  scoreline: new Set([
    'legacyId',
    'kind',
    'regionId',
    'schoolId',
    'schoolLegacyId',
    'schoolName',
    'schoolShortName',
    'schoolType',
    'shortName',
    'majorId',
    'majorLegacyId',
    'majorName',
    'year',
    'fieldKey',
    'fieldName',
    'fieldType',
    'unit',
    'isFilter',
    'isRequired',
    'isVisible',
    'isTrend',
    'options',
    'placeholder',
    'description',
    'sortOrder',
    'order',
    'fieldValues',
    'hasRestriction',
    'restrictionDesc',
    'isHot',
    'metadata',
  ]),
  videos: new Set([
    'legacyId',
    'title',
    'description',
    'videoUrl',
    'thumbnailUrl',
    'duration',
    'durationSeconds',
    'knowledgeTags',
    'isGeneral',
    'subjectId',
    'difficulty',
    'order',
    'isActive',
    'assetId',
    'accessMode',
    'freePreviewSeconds',
    'questionId',
    'legacyQuestionId',
    'videoType',
    'bindings',
    'metadata',
  ]),
};

function objectValue(value: unknown): JsonObject {
  return value && typeof value === 'object' && !Array.isArray(value) ? value as JsonObject : {};
}

function stringValue(value: unknown) {
  return typeof value === 'string' && value.trim() ? value.trim() : '';
}

function normalizedKey(value: string) {
  return value
    .trim()
    .toLowerCase()
    .replace(/[\s_\-./\\:：()（）[\]【】]/g, '');
}

function assertSafeObjectKey(key: string) {
  if (DANGEROUS_OBJECT_KEYS.has(key)) {
    throw new HttpError(400, `Unsupported spreadsheet header: ${key}`, 'IMPORT_HEADER_UNSAFE');
  }
}

function isAllowedOverrideTarget(importType: SpreadsheetImportType, field: string) {
  if (OVERRIDE_TARGET_FIELDS[importType].has(field)) return true;
  return importType === 'scoreline' && SAFE_DYNAMIC_FIELD.test(field) && !DANGEROUS_OBJECT_KEYS.has(field);
}

function sourceFormatValue(value: unknown): SupportedSpreadsheetFormat {
  const raw = stringValue(value).toLowerCase();
  if (!raw || raw === 'json') return 'json';
  if (raw === 'csv') return 'csv';
  if (['excel', 'xlsx'].includes(raw)) return 'excel';
  throw new HttpError(400, 'sourceFormat must be json, csv, or excel', 'IMPORT_FORMAT_NOT_SUPPORTED');
}

function assertRowLimit(count: number) {
  if (count > MAX_SPREADSHEET_ROWS) {
    throw new HttpError(400, `Spreadsheet import can contain at most ${MAX_SPREADSHEET_ROWS} rows`, 'IMPORT_TOO_LARGE');
  }
}

function decodeBase64File(body: JsonObject) {
  const raw = stringValue(body.fileBase64 ?? body.excelBase64 ?? body.csvBase64);
  if (!raw) return null;
  const base64 = raw.includes(',') ? raw.slice(raw.indexOf(',') + 1) : raw;
  let buffer: Buffer;
  try {
    buffer = Buffer.from(base64, 'base64');
  } catch {
    throw new HttpError(400, 'fileBase64 must be a valid base64 string', 'IMPORT_FILE_BASE64_INVALID');
  }
  if (!buffer.length) {
    throw new HttpError(400, 'fileBase64 is empty', 'IMPORT_FILE_EMPTY');
  }
  if (buffer.length > MAX_EXCEL_FILE_BYTES) {
    throw new HttpError(400, `Import file can be at most ${MAX_EXCEL_FILE_BYTES} bytes`, 'IMPORT_FILE_TOO_LARGE');
  }
  return buffer;
}

function primitiveCellValue(value: unknown): unknown {
  if (value === undefined || value === null) return undefined;
  if (value instanceof Date) return value.toISOString();
  if (typeof value === 'number' || typeof value === 'boolean') return value;
  if (typeof value === 'string') {
    const trimmed = value.trim();
    return trimmed.length > MAX_SPREADSHEET_CELL_CHARS ? trimmed.slice(0, MAX_SPREADSHEET_CELL_CHARS) : trimmed;
  }
  if (typeof value === 'object') {
    const cell = value as {
      text?: string;
      result?: unknown;
      formula?: string;
      richText?: Array<{ text?: string }>;
      hyperlink?: string;
    };
    if (cell.result !== undefined) return primitiveCellValue(cell.result);
    if (Array.isArray(cell.richText)) return cell.richText.map(part => part.text || '').join('');
    if (cell.text !== undefined) return primitiveCellValue(cell.text);
    if (cell.hyperlink !== undefined) return primitiveCellValue(cell.hyperlink);
  }
  return String(value);
}

function parseJsonLike(value: unknown): unknown {
  if (typeof value !== 'string') return value;
  const trimmed = value.trim();
  if (!trimmed) return undefined;
  if (
    (trimmed.startsWith('[') && trimmed.endsWith(']')) ||
    (trimmed.startsWith('{') && trimmed.endsWith('}'))
  ) {
    try {
      return JSON.parse(trimmed);
    } catch {
      return trimmed;
    }
  }
  return trimmed;
}

function splitList(value: unknown) {
  const parsed = parseJsonLike(value);
  if (Array.isArray(parsed)) return parsed.map(item => String(item).trim()).filter(Boolean);
  if (parsed === undefined || parsed === null) return [];
  return String(parsed)
    .split(/[|,，;；\n]/)
    .map(item => item.trim())
    .filter(Boolean);
}

function parseBooleanLike(value: unknown): unknown {
  if (typeof value === 'boolean') return value;
  if (typeof value !== 'string') return value;
  const normalized = value.trim().toLowerCase();
  if (['true', '1', 'yes', 'y', '是', '启用', '有效'].includes(normalized)) return true;
  if (['false', '0', 'no', 'n', '否', '禁用', '无效'].includes(normalized)) return false;
  return value;
}

function parseNumericLike(value: unknown): unknown {
  if (typeof value !== 'string') return value;
  const trimmed = value.trim();
  if (!trimmed) return undefined;
  if (!/^-?\d+(\.\d+)?$/.test(trimmed)) return value;
  const parsed = Number(trimmed);
  return Number.isFinite(parsed) ? parsed : value;
}

function coerceValue(key: string, value: unknown) {
  if (value === undefined || value === null || value === '') return undefined;
  if ([
    'options',
    'tags',
    'correctOptionIndices',
    'knowledgeTags',
  ].includes(key)) {
    return splitList(value);
  }
  if (['subQuestions', 'examMarkers', 'metadata', 'fieldValues', 'bindings'].includes(key)) {
    return parseJsonLike(value);
  }
  if (['isActive', 'isHot', 'isFilter', 'isRequired', 'isVisible', 'isTrend', 'hasRestriction', 'isGeneral'].includes(key)) {
    return parseBooleanLike(value);
  }
  if ([
    'difficulty',
    'order',
    'sortOrder',
    'duration',
    'durationSeconds',
    'freePreviewSeconds',
    'year',
    'wordCount',
  ].includes(key)) {
    return parseNumericLike(value);
  }
  return parseJsonLike(value);
}

function commonHeaderAlias(header: string) {
  const key = normalizedKey(header);
  const aliases: Record<string, string> = {
    id: 'legacyId',
    legacyid: 'legacyId',
    legacy_id: 'legacyId',
    externalid: 'legacyId',
    external_id: 'legacyId',
    旧id: 'legacyId',
    原id: 'legacyId',
    外部id: 'legacyId',
    排序: 'order',
    sort: 'order',
    sortorder: 'order',
    sort_order: 'order',
    是否启用: 'isActive',
    状态: 'isActive',
    标签: 'tags',
    tags: 'tags',
    metadata: 'metadata',
    元数据: 'metadata',
  };
  return aliases[key] || null;
}

function fieldMappingOverrides(body: JsonObject, importType: SpreadsheetImportType): FieldMappingOverrides {
  const raw = objectValue(body.fieldMappingOverrides ?? body.field_mapping_overrides);
  const mapping = new Map<string, string>();
  for (const [targetField, aliases] of Object.entries(raw)) {
    const canonicalField = stringValue(targetField);
    if (!canonicalField) continue;
    assertSafeObjectKey(canonicalField);
    if (!isAllowedOverrideTarget(importType, canonicalField)) {
      throw new HttpError(400, `Unsupported fieldMappingOverrides target field: ${canonicalField}`, 'IMPORT_FIELD_MAPPING_TARGET_INVALID');
    }
    const values = Array.isArray(aliases) ? aliases : String(aliases || '').split(/[,\n，]/);
    for (const alias of values) {
      const key = normalizedKey(String(alias || ''));
      if (key) mapping.set(key, canonicalField);
    }
  }
  return mapping;
}

function importHeaderAlias(header: string, importType: SpreadsheetImportType, overrides: FieldMappingOverrides) {
  const key = normalizedKey(header);
  const override = overrides.get(key);
  if (override) return override;
  const common = commonHeaderAlias(header);
  const aliases: Record<SpreadsheetImportType, Record<string, string>> = {
    questions: {
      type: 'type',
      questiontype: 'type',
      题型: 'type',
      typelabel: 'typeLabel',
      题型名称: 'typeLabel',
      content: 'content',
      title: 'content',
      题目: 'content',
      题干: 'content',
      问题: 'content',
      选项: 'options',
      options: 'options',
      a: 'optionA',
      选项a: 'optionA',
      optiona: 'optionA',
      b: 'optionB',
      选项b: 'optionB',
      optionb: 'optionB',
      c: 'optionC',
      选项c: 'optionC',
      optionc: 'optionC',
      d: 'optionD',
      选项d: 'optionD',
      optiond: 'optionD',
      e: 'optionE',
      选项e: 'optionE',
      optione: 'optionE',
      f: 'optionF',
      选项f: 'optionF',
      optionf: 'optionF',
      answer: 'answer',
      correctanswer: 'answer',
      correct: 'answer',
      答案: 'answer',
      正确答案: 'answer',
      正确选项: 'answer',
      correctoptionindices: 'correctOptionIndices',
      correct_option_indices: 'correctOptionIndices',
      explanation: 'explanation',
      解析: 'explanation',
      答案解析: 'explanation',
      difficulty: 'difficulty',
      难度: 'difficulty',
      mediaurl: 'mediaUrl',
      图片: 'mediaUrl',
      subquestions: 'subQuestions',
      子题: 'subQuestions',
      exammarkers: 'examMarkers',
      考试标记: 'examMarkers',
    },
    vocabulary: {
      unitlegacyid: 'unitLegacyId',
      unitid: 'unitLegacyId',
      单元id: 'unitLegacyId',
      unitname: 'unitName',
      unit: 'unitName',
      单元: 'unitName',
      单元名称: 'unitName',
      unitdescription: 'unitDescription',
      单元描述: 'unitDescription',
      unitorder: 'unitOrder',
      单元排序: 'unitOrder',
      wordlegacyid: 'wordLegacyId',
      wordid: 'wordLegacyId',
      单词id: 'wordLegacyId',
      word: 'word',
      单词: 'word',
      phonetic: 'phonetic',
      pronunciation: 'phonetic',
      音标: 'phonetic',
      meaning: 'meaning',
      translation: 'meaning',
      definition: 'meaning',
      释义: 'meaning',
      中文: 'meaning',
      意思: 'meaning',
      example: 'example',
      例句: 'example',
      exampletranslation: 'exampleTranslation',
      例句翻译: 'exampleTranslation',
      difficulty: 'difficulty',
      难度: 'difficulty',
    },
    handbook: {
      subjectlegacyid: 'subjectLegacyId',
      subjectid: 'subjectLegacyId',
      科目id: 'subjectLegacyId',
      subjectname: 'subjectName',
      subject: 'subjectName',
      科目: 'subjectName',
      手册: 'subjectName',
      chapterlegacyid: 'chapterLegacyId',
      chapterid: 'chapterLegacyId',
      章节id: 'chapterLegacyId',
      chaptername: 'chapterName',
      chapter: 'chapterName',
      章节: 'chapterName',
      sectionlegacyid: 'sectionLegacyId',
      sectionid: 'sectionLegacyId',
      小节id: 'sectionLegacyId',
      sectionname: 'sectionName',
      section: 'sectionName',
      小节: 'sectionName',
      entrylegacyid: 'entryLegacyId',
      entryid: 'entryLegacyId',
      知识点id: 'entryLegacyId',
      title: 'title',
      标题: 'title',
      知识点: 'title',
      content: 'content',
      正文: 'content',
      内容: 'content',
      summary: 'summary',
      摘要: 'summary',
    },
    scoreline: {
      kind: 'kind',
      itemtype: 'kind',
      类型: 'kind',
      fieldkey: 'fieldKey',
      字段key: 'fieldKey',
      fieldname: 'fieldName',
      字段名: 'fieldName',
      fieldtype: 'fieldType',
      字段类型: 'fieldType',
      unit: 'unit',
      单位: 'unit',
      schoolid: 'schoolId',
      院校id: 'schoolId',
      schoollegacyid: 'schoolLegacyId',
      schoolname: 'schoolName',
      school: 'schoolName',
      学校: 'schoolName',
      院校: 'schoolName',
      majorid: 'majorId',
      专业id: 'majorId',
      majorlegacyid: 'majorLegacyId',
      majorname: 'majorName',
      major: 'majorName',
      专业: 'majorName',
      year: 'year',
      年份: 'year',
      fieldvalues: 'fieldValues',
      动态字段: 'fieldValues',
      最低分: 'minScore',
      录取分: 'minScore',
      计划数: 'planCount',
      招生计划: 'planCount',
    },
    videos: {
      title: 'title',
      name: 'title',
      视频标题: 'title',
      标题: 'title',
      description: 'description',
      描述: 'description',
      videourl: 'videoUrl',
      url: 'videoUrl',
      视频地址: 'videoUrl',
      thumbnailurl: 'thumbnailUrl',
      封面: 'thumbnailUrl',
      duration: 'durationSeconds',
      durationseconds: 'durationSeconds',
      时长: 'durationSeconds',
      subjectid: 'subjectId',
      科目id: 'subjectId',
      questionid: 'questionId',
      题目id: 'questionId',
      legacyquestionid: 'legacyQuestionId',
      旧题目id: 'legacyQuestionId',
      assetid: 'assetId',
      资源id: 'assetId',
      accessmode: 'accessMode',
      访问模式: 'accessMode',
      knowledgetags: 'knowledgeTags',
      知识点标签: 'knowledgeTags',
      bindings: 'bindings',
      绑定题目: 'bindings',
    },
  };
  return aliases[importType][key] || common || header.trim();
}

function normalizeSpreadsheetRow(raw: JsonObject, importType: SpreadsheetImportType, overrides: FieldMappingOverrides) {
  const row: JsonObject = {};
  for (const [header, value] of Object.entries(raw)) {
    assertSafeObjectKey(header.trim());
    const key = importHeaderAlias(header, importType, overrides);
    assertSafeObjectKey(key);
    let coerced = coerceValue(key, value);
    if (importType === 'scoreline' && key === header.trim()) {
      coerced = parseNumericLike(coerced);
    }
    if (coerced !== undefined && coerced !== '') row[key] = coerced;
  }

  if (importType === 'questions') {
    const options = QUESTION_OPTION_KEYS.map(key => row[key]).filter(value => value !== undefined && value !== '');
    if (!row.options && options.length) row.options = options;
    if (!row.correctOptionIndices && row.answer) {
      row.correctOptionIndices = parseAnswerIndices(row.answer, Array.isArray(row.options) ? row.options.length : 0);
    }
  }

  if (importType === 'vocabulary') {
    if (row.wordLegacyId && !row.legacyId) row.legacyId = row.wordLegacyId;
    if (row.unitOrder && !row.order && !row.word) row.order = row.unitOrder;
  }

  if (importType === 'handbook') {
    if (row.entryLegacyId && !row.legacyId) row.legacyId = row.entryLegacyId;
  }

  return row;
}

function parseAnswerIndices(value: unknown, optionCount: number) {
  const parts = splitList(value);
  const indices: number[] = [];
  for (const part of parts) {
    const upper = String(part).trim().toUpperCase();
    if (/^[A-H]$/.test(upper)) {
      indices.push(upper.charCodeAt(0) - 65);
      continue;
    }
    const parsed = Number(upper);
    if (Number.isFinite(parsed)) {
      const integer = Math.trunc(parsed);
      indices.push(integer === 0 ? 0 : optionCount > 0 && integer <= optionCount ? integer - 1 : integer);
    }
  }
  return [...new Set(indices)];
}

function parseCsv(text: string, delimiter: string) {
  const rows: string[][] = [];
  let row: string[] = [];
  let current = '';
  let quoted = false;
  for (let index = 0; index < text.length; index += 1) {
    const char = text[index];
    const next = text[index + 1];
    if (quoted) {
      if (char === '"' && next === '"') {
        current += '"';
        index += 1;
      } else if (char === '"') {
        quoted = false;
      } else {
        current += char;
      }
      continue;
    }
    if (char === '"') {
      quoted = true;
    } else if (char === delimiter) {
      row.push(current);
      current = '';
    } else if (char === '\n') {
      row.push(current);
      rows.push(row);
      row = [];
      current = '';
    } else if (char !== '\r') {
      current += char;
    }
  }
  row.push(current);
  rows.push(row);
  return rows.filter(item => item.some(cell => cell.trim()));
}

function autoDelimiter(text: string) {
  const sample = text.slice(0, 4096);
  const candidates = [',', '\t', ';'];
  return candidates
    .map(delimiter => ({ delimiter, count: (sample.match(new RegExp(delimiter === '\t' ? '\\t' : delimiter, 'g')) || []).length }))
    .sort((left, right) => right.count - left.count)[0]?.delimiter || ',';
}

function rowsFromMatrix(matrix: string[][], importType: SpreadsheetImportType, overrides: FieldMappingOverrides) {
  if (matrix.length < 2) return [];
  const headers = matrix[0].map((header, index) => stringValue(header) || `column${index + 1}`);
  if (headers.length > MAX_SPREADSHEET_COLUMNS) {
    throw new HttpError(400, `Spreadsheet can contain at most ${MAX_SPREADSHEET_COLUMNS} columns`, 'IMPORT_TOO_MANY_COLUMNS');
  }
  const rows = matrix.slice(1).map(line => {
    const row: JsonObject = {};
    headers.forEach((header, index) => {
      const value = stringValue(line[index]);
      assertSafeObjectKey(header.trim());
      if (value) row[header] = value;
    });
    return normalizeSpreadsheetRow(row, importType, overrides);
  }).filter(row => Object.keys(row).length > 0);
  assertRowLimit(rows.length);
  return rows;
}

function parseCsvBody(body: JsonObject, importType: SpreadsheetImportType): ParsedSpreadsheet {
  const buffer = decodeBase64File(body);
  const text = stringValue(body.csvText ?? body.text ?? body.fileContent ?? body.payload) || buffer?.toString('utf8') || '';
  if (!text.trim()) {
    throw new HttpError(400, 'CSV import requires csvText, fileContent, payload, or fileBase64', 'CSV_CONTENT_REQUIRED');
  }
  const delimiter = stringValue(body.delimiter) || autoDelimiter(text);
  const overrides = fieldMappingOverrides(body, importType);
  const rows = rowsFromMatrix(parseCsv(text, delimiter), importType, overrides);
  return {
    rows,
    sheets: { csv: rows },
    metadata: {
      parser: 'csv',
      delimiter: delimiter === '\t' ? 'tab' : delimiter,
      rowCount: rows.length,
      fieldMappingOverrides: overrides.size,
    },
  };
}

function worksheetRows(matrix: unknown[][], importType: SpreadsheetImportType, overrides: FieldMappingOverrides) {
  const rows = matrix.map(line => {
    const values = line
      .slice(0, MAX_SPREADSHEET_COLUMNS)
      .map(value => {
        const primitive = primitiveCellValue(value);
        return primitive === undefined || primitive === null ? '' : String(primitive);
      });
    return values;
  }).filter(values => values.some(value => value.trim()));
  return rowsFromMatrix(rows, importType, overrides);
}

async function parseExcelBody(body: JsonObject, importType: SpreadsheetImportType): Promise<ParsedSpreadsheet> {
  const buffer = decodeBase64File(body);
  if (!buffer) {
    throw new HttpError(400, 'Excel import requires fileBase64', 'EXCEL_FILE_REQUIRED');
  }

  let workbookSheets: Array<{ sheet: string; data: unknown[][] }>;
  try {
    workbookSheets = await readXlsxFile(buffer);
  } catch {
    throw new HttpError(400, 'Excel file could not be parsed as .xlsx', 'EXCEL_PARSE_FAILED');
  }

  const sheetName = stringValue(body.sheetName);
  const sheetIndex = Number(body.sheetIndex ?? 1);
  let selected = sheetName
    ? workbookSheets.find(sheet => sheet.sheet === sheetName)
    : workbookSheets[Math.max(0, Math.trunc(Number.isFinite(sheetIndex) && sheetIndex > 0 ? sheetIndex : 1) - 1)];
  selected = selected || workbookSheets.find(sheet => sheet.data.some(row => row.some(value => value !== null && value !== undefined && String(value).trim())));
  if (!selected) {
    throw new HttpError(400, 'Excel file has no non-empty worksheet', 'EXCEL_SHEET_EMPTY');
  }

  const sheets: Record<string, JsonObject[]> = {};
  let totalRows = 0;
  const overrides = fieldMappingOverrides(body, importType);
  for (const sheet of workbookSheets) {
    if (!sheet.data.some(row => row.some(value => value !== null && value !== undefined && String(value).trim()))) continue;
    const rows = worksheetRows(sheet.data, importType, overrides);
    sheets[normalizedKey(sheet.sheet)] = rows;
    totalRows += rows.length;
  }
  assertRowLimit(totalRows);

  if (!sheets[normalizedKey(selected.sheet)]) {
    try {
      const selectedData = await readSheet(buffer, selected.sheet);
      sheets[normalizedKey(selected.sheet)] = worksheetRows(selectedData, importType, overrides);
    } catch {
      throw new HttpError(400, 'Excel file could not be parsed as .xlsx', 'EXCEL_PARSE_FAILED');
    }
  }

  const selectedRows = sheets[normalizedKey(selected.sheet)] || [];
  return {
    rows: selectedRows,
    sheets,
    metadata: {
      parser: 'read-excel-file',
      workbookSheetCount: workbookSheets.length,
      selectedSheet: selected.sheet,
      rowCount: selectedRows.length,
      totalParsedRows: totalRows,
      fieldMappingOverrides: overrides.size,
    },
  };
}

function sheetRows(parsed: ParsedSpreadsheet, names: string[]) {
  for (const name of names) {
    const rows = parsed.sheets[normalizedKey(name)];
    if (rows?.length) return rows;
  }
  return [];
}

function stableLegacyId(value: unknown, fallback: string) {
  return stringValue(value) || fallback;
}

function buildVocabularyPayload(parsed: ParsedSpreadsheet) {
  const explicitUnitRows = sheetRows(parsed, ['units', 'vocabulary_units', '单元', '单词单元']);
  const explicitWordRows = sheetRows(parsed, ['words', 'vocabulary', 'vocabulary_words', '单词', '词汇']);
  const rows = explicitWordRows.length || explicitUnitRows.length ? [...explicitUnitRows, ...explicitWordRows] : parsed.rows;
  const units = new Map<string, JsonObject & { words: JsonObject[] }>();

  for (const row of rows) {
    const rowType = String(row.rowType ?? row.kind ?? row.type ?? '').toLowerCase();
    const hasWord = Boolean(row.word);
    const unitName = stringValue(row.unitName ?? row.unit) || '默认单词单元';
    const unitKey = stableLegacyId(row.unitLegacyId ?? row.unitId, unitName);
    const unit = units.get(unitKey) || {
      legacyId: unitKey,
      name: unitName,
      description: row.unitDescription ?? row.description ?? null,
      order: row.unitOrder ?? row.order ?? units.size + 1,
      isActive: row.isActive ?? true,
      words: [],
    };
    if (rowType.includes('unit') && !hasWord) {
      unit.name = unitName;
      unit.description = row.unitDescription ?? row.description ?? unit.description;
      unit.order = row.unitOrder ?? row.order ?? unit.order;
      unit.isActive = row.isActive ?? unit.isActive;
    }
    if (hasWord) {
      const word = { ...row };
      word.legacyId = row.wordLegacyId ?? row.legacyId ?? null;
      delete word.unitLegacyId;
      delete word.unitId;
      delete word.unitName;
      delete word.unitDescription;
      delete word.unitOrder;
      delete word.wordLegacyId;
      unit.words.push(word);
    }
    units.set(unitKey, unit);
  }

  return { units: Array.from(units.values()) };
}

function getOrCreateNested<T extends JsonObject>(
  map: Map<string, T>,
  key: string,
  create: () => T,
) {
  const existing = map.get(key);
  if (existing) return existing;
  const created = create();
  map.set(key, created);
  return created;
}

function buildHandbookPayload(parsed: ParsedSpreadsheet) {
  const rows = sheetRows(parsed, ['entries', 'handbook', '知识点', '手册']) || parsed.rows;
  const subjectMap = new Map<string, JsonObject & { chapters: Array<JsonObject & { sections: Array<JsonObject & { entries: JsonObject[] }> }> }>();

  for (const row of rows.length ? rows : parsed.rows) {
    const subjectName = stringValue(row.subjectName ?? row.subject) || '默认知识手册';
    const subjectKey = stableLegacyId(row.subjectLegacyId ?? row.subjectId, subjectName);
    const subject = getOrCreateNested(subjectMap, subjectKey, () => ({
      legacyId: subjectKey,
      name: subjectName,
      type: row.subjectType ?? row.type ?? null,
      icon: row.icon ?? null,
      color: row.color ?? null,
      order: subjectMap.size + 1,
      chapters: [],
    }));

    const chapterName = stringValue(row.chapterName ?? row.chapter) || '默认章节';
    const chapterKey = stableLegacyId(row.chapterLegacyId ?? row.chapterId, `${subjectKey}:${chapterName}`);
    let chapter = subject.chapters.find(item => item.legacyId === chapterKey);
    if (!chapter) {
      chapter = { legacyId: chapterKey, name: chapterName, order: subject.chapters.length + 1, sections: [] };
      subject.chapters.push(chapter);
    }

    const sectionName = stringValue(row.sectionName ?? row.section) || '';
    const sectionKey = stableLegacyId(row.sectionLegacyId ?? row.sectionId, `${chapterKey}:${sectionName || 'default'}`);
    let section = chapter.sections.find(item => item.legacyId === sectionKey);
    if (!section) {
      section = { legacyId: sectionKey, name: sectionName || null, order: chapter.sections.length + 1, entries: [] };
      chapter.sections.push(section);
    }

    if (row.title || row.content) {
      section.entries.push({
        legacyId: row.entryLegacyId ?? row.legacyId ?? null,
        title: row.title ?? '未命名知识点',
        summary: row.summary ?? null,
        content: row.content ?? '',
        tags: row.tags ?? [],
        order: row.order ?? section.entries.length + 1,
        isActive: row.isActive ?? true,
        metadata: row.metadata ?? {},
      });
    }
  }

  return { subjects: Array.from(subjectMap.values()) };
}

function buildScorelinePayload(parsed: ParsedSpreadsheet) {
  const buckets = {
    fields: sheetRows(parsed, ['fields', 'scoreline_fields', '字段']),
    schools: sheetRows(parsed, ['schools', 'scoreline_schools', '院校', '学校']),
    majors: sheetRows(parsed, ['majors', 'scoreline_majors', '专业']),
    records: sheetRows(parsed, ['records', 'scoreline_records', '分数线', '记录']),
  };
  if (Object.values(buckets).some(rows => rows.length > 0)) return buckets;
  return { items: parsed.rows };
}

function buildVideosPayload(parsed: ParsedSpreadsheet) {
  const rows = sheetRows(parsed, ['videos', 'video_explanations', '视频']) || parsed.rows;
  return { videos: rows.length ? rows : parsed.rows };
}

export async function expandSpreadsheetImportBody(body: JsonObject, importType: SpreadsheetImportType): Promise<JsonObject> {
  const sourceFormat = sourceFormatValue(body.sourceFormat);
  if (sourceFormat === 'json') return body;

  const parsed = sourceFormat === 'csv'
    ? parseCsvBody(body, importType)
    : await parseExcelBody(body, importType);

  if (!parsed.rows.length && !Object.values(parsed.sheets).some(rows => rows.length > 0)) {
    throw new HttpError(400, 'Spreadsheet import payload must contain at least one data row', 'EMPTY_IMPORT_PAYLOAD');
  }

  const next: JsonObject = {
    ...body,
    sourceFormat,
    spreadsheet: parsed.metadata,
  };
  delete next.csvText;
  delete next.text;
  delete next.fileContent;
  delete next.fileBase64;
  delete next.excelBase64;
  delete next.csvBase64;
  delete next.payload;

  if (importType === 'questions') next.items = parsed.rows;
  if (importType === 'vocabulary') Object.assign(next, buildVocabularyPayload(parsed));
  if (importType === 'handbook') Object.assign(next, buildHandbookPayload(parsed));
  if (importType === 'scoreline') Object.assign(next, buildScorelinePayload(parsed));
  if (importType === 'videos') Object.assign(next, buildVideosPayload(parsed));

  return next;
}
