import ExcelJS from 'exceljs';

const resourceSpecs = {
  classes: {
    title: '班级台账', sheet: '班级',
    columns: [
      ['schoolCode', '学校代码*', 16, 'HZ01'], ['grade', '年级*', 14, '高三'],
      ['name', '班级名称*', 22, '高三（3）班'], ['status', '状态*', 12, '启用']
    ],
    validations: { status: ['启用', '停用'] }
  },
  class_admins: {
    title: '班级管理员台账', sheet: '班级管理员',
    columns: [
      ['schoolCode', '学校代码*', 16, 'HZ01'], ['className', '班级名称*', 22, '高三（1）班'],
      ['displayName', '管理员姓名*', 18, '张老师'], ['username', '登录账号*', 20, 'hz01_g301'],
      ['initialPassword', '初始密码（新建必填）', 24, 'ChangeMe123!'], ['status', '状态*', 12, '启用']
    ],
    validations: { status: ['启用', '停用'] }
  },
  account_quotas: {
    title: '批量报名号申领配额', sheet: '班级配额',
    columns: [['className', '班级名称*', 24, '高三（1）班'], ['count', '申领数量*', 16, 30]],
    numberColumns: ['count']
  },
  account_results: {
    title: '报名号下发清单', sheet: '账号结果',
    columns: [
      ['batchId', '批次编号', 30, ''], ['className', '班级', 22, ''],
      ['candidateNumber', '固定报名号', 26, ''], ['initialPassword', '初始密码', 22, '']
    ]
  },
  candidates: {
    title: '考生资料台账', sheet: '考生资料',
    columns: [
      ['candidateNumber', '报名号*', 26, '2026-HZ01-X-0001'], ['name', '姓名*', 16, '李明'],
      ['gender', '性别*', 10, '男'], ['idNumber', '证件号码*', 24, '320101200801011234'],
      ['phone', '手机号*', 18, '13800138000'], ['email', '邮箱', 24, 'student@example.com'],
      ['nativePlace', '籍贯', 18, '江苏海州'],
      ['provinceCode', '省级代码*', 14, '320000'], ['provinceName', '省份', 18, '江苏省'],
      ['cityCode', '市级代码*', 14, '320700'], ['cityName', '城市', 18, '连云港市'],
      ['districtCode', '区县代码*', 14, '320706'], ['districtName', '区县', 18, '海州区'],
      ['address', '详细住址*', 32, '示例路 1 号'],
      ['className', '班级*', 22, '高三（1）班'], ['ethnicity', '民族', 12, '汉族'],
      ['birthDate', '出生日期', 16, '2008-01-01'], ['postalCode', '邮编', 14, '222000'],
      ['guardianName', '监护人', 16, '李家长'], ['guardianPhone', '监护人电话', 18, '13900139000']
    ],
    validations: { gender: ['男', '女'] }
  },
  payments: {
    title: '考试缴费名单', sheet: '缴费名单',
    columns: [
      ['examCode', '考试代码', 18, ''], ['examName', '考试名称', 28, ''],
      ['schoolName', '学校', 24, ''], ['className', '班级', 20, ''],
      ['candidateNumber', '报名号', 26, ''], ['candidateName', '考生姓名', 16, ''],
      ['subjectNames', '报考科目', 34, ''], ['amountDue', '应缴金额（元）', 18, ''],
      ['paymentStatus', '缴费状态', 14, ''], ['paidAt', '确认时间', 24, ''],
      ['paidByName', '确认人', 16, '']
    ],
    numberColumns: ['amountDue'],
    numberFormats: { amountDue: '0.00' }
  },
  centers: {
    title: '考点考场档案', sheet: '考点考场',
    columns: [
      ['schoolCode', '学校代码*', 14, 'HZ01'], ['centerCode', '考点代码*', 18, 'HZ01-C02'],
      ['centerName', '考点名称*', 24, '第一中学东区考点'],
      ['provinceCode', '省级代码*', 14, '320000'], ['provinceName', '省份', 18, '江苏省'],
      ['cityCode', '市级代码*', 14, '320700'], ['cityName', '城市', 18, '连云港市'],
      ['districtCode', '区县代码*', 14, '320706'], ['districtName', '区县', 18, '海州区'],
      ['address', '详细地址*', 30, '示例路 8 号'],
      ['managerName', '负责人', 16, '王老师'], ['managerPhone', '负责人手机', 18, '13800138000'],
      ['contact', '值班电话', 18, '0518-86020000'], ['emergencyPhone', '应急电话', 18, '0518-120'],
      ['gateOpenTime', '开放时间', 14, '07:00'], ['transport', '交通提示', 30, '东门入场'],
      ['centerStatus', '考点状态*', 14, '启用'], ['centerNotes', '考点备注', 26, ''],
      ['roomCode', '考场代码*', 16, '001'], ['roomName', '考场名称*', 22, '第 001 考场'],
      ['building', '楼栋*', 18, '教学楼 A'], ['floor', '楼层', 12, '1 层'],
      ['capacity', '容量*', 12, 30], ['seatPlan', '座位编排说明', 28, '按教室现场座次表编排'],
      ['roomType', '考场类型*', 16, '标准考场'], ['roomStatus', '考场状态*', 14, '启用'],
      ['roomNotes', '考场备注', 26, '']
    ],
    validations: { centerStatus: ['启用', '停用'], roomStatus: ['启用', '停用'], roomType: ['标准考场', '机考考场', '无障碍考场', '备用考场'] },
    numberColumns: ['capacity']
  },
  results: {
    title: '考试成绩台账', sheet: '成绩',
    columns: [
      ['candidateNumber', '报名号*', 26, '2026-HZ01-X-0001'], ['candidateName', '姓名（只读参考）', 16, ''],
      ['examCode', '考试代码*', 20, 'EX-2026-AUT'], ['examName', '考试名称（只读参考）', 28, ''],
      ['subjectName', '科目*', 16, '语文'], ['fullScore', '科目满分（只读参考）', 18, ''],
      ['passRule', '单科及格规则（只读参考）', 26, ''], ['passScore', '实际及格分（只读参考）', 20, ''],
      ['score', '成绩*', 12, 120], ['rank', '本科排名（导出计算）', 18, ''],
      ['rankPercent', '排名百分位（导出计算）', 20, ''], ['qualified', '单科达线（导出计算）', 16, ''], ['grade', '排名等级（自动计算）', 18, ''],
      ['published', '发布状态*', 14, '发布'], ['updatedAt', '更新时间（只读参考）', 24, '']
    ],
    validations: { published: ['发布', '不发布'] },
    numberColumns: ['fullScore', 'passScore', 'score', 'rank', 'rankPercent'],
    numberFormats: { fullScore: '0.00', passScore: '0.00', score: '0.00', rank: '0', rankPercent: '0.00' }
  },
  admit_cards: {
    title: '准考证信息台账', sheet: '准考证信息',
    columns: [
      ['schoolName', '考生学校', 24, ''], ['className', '班级', 20, ''],
      ['candidateNumber', '报名号', 24, ''], ['candidateName', '姓名', 14, ''], ['idNumber', '证件号码', 22, ''],
      ['examCode', '考试代码', 18, ''], ['examName', '考试名称', 28, ''], ['cardNumber', '准考证号', 22, ''],
      ['centerCode', '考点代码', 16, ''], ['centerName', '考点名称', 26, ''], ['centerAddress', '考点详细地址', 38, ''],
      ['subjectName', '科目', 14, ''], ['subjectDate', '日期', 14, ''], ['subjectTime', '时间', 16, ''],
      ['examRoomCode', '考试考场序号', 16, ''], ['roomName', '考场通用名称', 20, ''], ['roomCode', '物理场地代码', 16, ''],
      ['building', '楼栋', 16, ''], ['floor', '楼层', 12, ''], ['seat', '座位号', 12, '']
    ]
  }
};

export function hasExcelResource(resource) {
  return Boolean(resourceSpecs[resource]);
}

function cellValue(cell) {
  const value = cell.value;
  if (value == null) return '';
  if (value instanceof Date) return value.toISOString().slice(0, 10);
  if (typeof value === 'object') {
    if ('text' in value) return String(value.text || '').trim();
    if ('result' in value) return String(value.result ?? '').trim();
  }
  return typeof value === 'number' ? value : String(value).trim();
}

export async function parseWorkbook(resource, buffer) {
  const spec = resourceSpecs[resource];
  if (!spec) throw Object.assign(new Error('不支持的 Excel 数据类型'), { status: 404 });
  const workbook = new ExcelJS.Workbook();
  try {
    await workbook.xlsx.load(buffer);
  } catch {
    throw Object.assign(new Error('无法读取 Excel 文件，请使用系统下载的 .xlsx 模板'), { status: 400 });
  }
  const sheet = workbook.getWorksheet(spec.sheet) || workbook.worksheets[0];
  if (!sheet) throw Object.assign(new Error('Excel 文件中没有可读取的工作表'), { status: 400 });
  const headerMap = new Map();
  sheet.getRow(2).eachCell((cell, col) => headerMap.set(String(cell.value || '').replace(/\*/g, '').trim(), col));
  const missing = spec.columns.filter(([, label]) => !headerMap.has(label.replace(/\*/g, '').trim()));
  if (missing.length) throw Object.assign(new Error(`模板列不完整：缺少 ${missing.map(([, label]) => label).join('、')}`), { status: 400 });
  const rows = [];
  for (let rowNumber = 3; rowNumber <= sheet.rowCount; rowNumber += 1) {
    const row = sheet.getRow(rowNumber);
    const item = { __row: rowNumber };
    let populated = false;
    for (const [key, label] of spec.columns) {
      const value = cellValue(row.getCell(headerMap.get(label.replace(/\*/g, '').trim())));
      item[key] = value;
      if (value !== '') populated = true;
    }
    if (populated) rows.push(item);
  }
  if (!rows.length) throw Object.assign(new Error('Excel 中没有可导入的数据行'), { status: 400 });
  return rows;
}

export async function buildWorkbook(resource, rows = [], { template = false, subtitle = '' } = {}) {
  const spec = resourceSpecs[resource];
  if (!spec) throw Object.assign(new Error('不支持的 Excel 数据类型'), { status: 404 });
  const workbook = new ExcelJS.Workbook();
  workbook.creator = '衡准考试信息管理系统';
  workbook.created = new Date();
  const sheet = workbook.addWorksheet(spec.sheet, { views: [{ state: 'frozen', ySplit: 2, showGridLines: false }] });
  const lastColumn = spec.columns.length;
  sheet.columns = spec.columns.map(([key, , width]) => ({ key, width }));
  spec.columns.forEach(([key], index) => {
    sheet.getColumn(index + 1).numFmt = spec.numberFormats?.[key] || (spec.numberColumns?.includes(key) ? '0' : '@');
  });
  sheet.mergeCells(1, 1, 1, lastColumn);
  const titleCell = sheet.getCell(1, 1);
  titleCell.value = subtitle ? `${spec.title}｜${subtitle}` : spec.title;
  titleCell.font = { name: '微软雅黑', size: 16, bold: true, color: { argb: 'FFFFFFFF' } };
  titleCell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF173F60' } };
  titleCell.alignment = { vertical: 'middle', horizontal: 'left' };
  sheet.getRow(1).height = 34;
  const header = sheet.getRow(2);
  header.values = spec.columns.map(([, label]) => label);
  header.height = 25;
  header.font = { name: '微软雅黑', bold: true, color: { argb: 'FFFFFFFF' } };
  header.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF2C7080' } };
  header.alignment = { vertical: 'middle', horizontal: 'center' };
  const outputRows = rows.length ? rows : template ? [Object.fromEntries(spec.columns.map(([key, , , example]) => [key, example]))] : [];
  for (const item of outputRows) {
    const row = sheet.addRow(Object.fromEntries(spec.columns.map(([key]) => [key, item[key] ?? ''])));
    row.height = 23;
    row.font = { name: '微软雅黑', size: 10, color: { argb: 'FF243B4A' } };
    row.alignment = { vertical: 'middle' };
    row.eachCell(cell => {
      cell.border = { bottom: { style: 'hair', color: { argb: 'FFD8E2E7' } } };
    });
  }
  sheet.autoFilter = { from: { row: 2, column: 1 }, to: { row: Math.max(2, sheet.rowCount), column: lastColumn } };
  for (const [key, values] of Object.entries(spec.validations || {})) {
    const col = spec.columns.findIndex(([columnKey]) => columnKey === key) + 1;
    for (let row = 3; row <= Math.max(202, sheet.rowCount); row += 1) {
      sheet.getCell(row, col).dataValidation = { type: 'list', allowBlank: false, formulae: [`"${values.join(',')}"`] };
    }
  }
  const requiredColumns = spec.columns.map(([, label], index) => label.includes('*') ? index + 1 : 0).filter(Boolean);
  for (const col of requiredColumns) header.getCell(col).font = { name: '微软雅黑', bold: true, color: { argb: 'FFFFE7A3' } };
  return workbook.xlsx.writeBuffer();
}

function addOperationalSheet(workbook, name, title, columns, rows, { landscape = true } = {}) {
  const sheet = workbook.addWorksheet(name, { views: [{ state: 'frozen', ySplit: 2, showGridLines: false }] });
  sheet.columns = columns.map(([key, , width]) => ({ key, width }));
  sheet.mergeCells(1, 1, 1, columns.length);
  const titleCell = sheet.getCell(1, 1);
  titleCell.value = title;
  titleCell.font = { name: '微软雅黑', size: 16, bold: true, color: { argb: 'FFFFFFFF' } };
  titleCell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF173F60' } };
  titleCell.alignment = { vertical: 'middle', horizontal: 'left' };
  sheet.getRow(1).height = 34;
  const header = sheet.getRow(2);
  header.values = columns.map(([, label]) => label);
  header.height = 28;
  header.font = { name: '微软雅黑', bold: true, color: { argb: 'FFFFFFFF' } };
  header.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF2C7080' } };
  header.alignment = { vertical: 'middle', horizontal: 'center', wrapText: true };
  rows.forEach((item, index) => {
    const row = sheet.addRow(Object.fromEntries(columns.map(([key]) => [key, item[key] ?? ''])));
    row.height = 28;
    row.font = { name: '微软雅黑', size: 10, color: { argb: 'FF243B4A' } };
    row.alignment = { vertical: 'middle', wrapText: true };
    if (index % 2 === 1) row.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FFF3F7F8' } };
    row.eachCell(cell => { cell.border = { bottom: { style: 'hair', color: { argb: 'FFD8E2E7' } } }; });
  });
  sheet.autoFilter = { from: { row: 2, column: 1 }, to: { row: Math.max(2, sheet.rowCount), column: columns.length } };
  sheet.pageSetup = { orientation: landscape ? 'landscape' : 'portrait', paperSize: 9, fitToPage: true, fitToWidth: 1, fitToHeight: 0, margins: { left: .25, right: .25, top: .4, bottom: .4, header: .2, footer: .2 } };
  sheet.printTitlesRow = '1:2';
  return sheet;
}

function addDeskStickerSheet(workbook, rows) {
  const sheet = workbook.addWorksheet('桌贴', { views: [{ showGridLines: false, zoomScale: 85 }] });
  const labelColumns = [14.5, 14.5, 14.5, 3, 14.5, 14.5, 14.5];
  labelColumns.forEach((width, index) => { sheet.getColumn(index + 1).width = width; });
  sheet.pageSetup = {
    orientation: 'portrait', paperSize: 9, fitToPage: true, fitToWidth: 1, fitToHeight: 0,
    margins: { left: .25, right: .25, top: .25, bottom: .25, header: 0, footer: 0 },
    horizontalCentered: true, verticalCentered: false
  };
  sheet.headerFooter = { oddFooter: '&C第 &P / &N 页' };
  const rowsPerLabel = 8;
  const labelsPerPage = 6;
  const rowsPerPage = rowsPerLabel * 3;
  const borderColor = { argb: 'FF173F60' };
  const paleBlue = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FFEAF1F5' } };
  const paleGold = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FFFFF1C7' } };
  const mergeLine = (row, startColumn) => {
    sheet.mergeCells(row, startColumn, row, startColumn + 2);
    return sheet.getCell(row, startColumn);
  };
  const styleLine = (cell, { size = 10, bold = false, color = 'FF243B4A', fill = null } = {}) => {
    cell.font = { name: '微软雅黑', size, bold, color: { argb: color } };
    cell.alignment = { vertical: 'middle', horizontal: 'center', wrapText: true, shrinkToFit: true };
    if (fill) cell.fill = fill;
  };

  rows.forEach((item, index) => {
    const page = Math.floor(index / labelsPerPage);
    const slot = index % labelsPerPage;
    const startRow = page * rowsPerPage + Math.floor(slot / 2) * rowsPerLabel + 1;
    const startColumn = slot % 2 === 0 ? 1 : 5;
    for (let row = startRow; row < startRow + rowsPerLabel; row += 1) sheet.getRow(row).height = 32.5;
    const cells = Array.from({ length: rowsPerLabel }, (_, offset) => mergeLine(startRow + offset, startColumn));
    cells[0].value = item.examName;
    styleLine(cells[0], { size: 11, bold: true, color: 'FFFFFFFF', fill: { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF173F60' } } });
    cells[1].value = `${item.subjectName}｜${item.subjectDate} ${item.subjectTime}`;
    styleLine(cells[1], { size: 9, bold: true, fill: paleBlue });
    cells[2].value = '考试考场序号';
    styleLine(cells[2], { size: 9, bold: true, color: 'FF2C7080' });
    cells[3].value = { richText: [{ text: String(item.examRoomCode || '') }] };
    cells[3].numFmt = '@';
    styleLine(cells[3], { size: 25, bold: true, color: 'FF173F60' });
    cells[4].value = `座位号  ${item.seat}`;
    styleLine(cells[4], { size: 20, bold: true, color: 'FF8A4B08', fill: paleGold });
    cells[5].value = `${item.roomName}（场地 ${item.roomCode}）｜${item.building} ${item.floor}`;
    styleLine(cells[5], { size: 9, bold: true });
    cells[6].value = `${item.candidateName}｜${item.schoolName} ${item.className}`;
    styleLine(cells[6], { size: 10, bold: true, fill: paleBlue });
    cells[7].value = `${item.centerName}｜准考证号 ${item.cardNumber}`;
    styleLine(cells[7], { size: 8, color: 'FF526576' });

    for (let row = startRow; row < startRow + rowsPerLabel; row += 1) {
      for (let column = startColumn; column < startColumn + 3; column += 1) {
        const cell = sheet.getCell(row, column);
        cell.border = {
          top: { style: row === startRow ? 'medium' : 'hair', color: borderColor },
          bottom: { style: row === startRow + rowsPerLabel - 1 ? 'medium' : 'hair', color: borderColor },
          left: { style: column === startColumn ? 'medium' : 'hair', color: borderColor },
          right: { style: column === startColumn + 2 ? 'medium' : 'hair', color: borderColor }
        };
      }
    }
    if ((index + 1) % labelsPerPage === 0 && index + 1 < rows.length) sheet.getRow(startRow + rowsPerLabel - 1).addPageBreak();
  });
  if (rows.length) sheet.pageSetup.printArea = `A1:G${Math.ceil(rows.length / labelsPerPage) * rowsPerPage}`;
  return sheet;
}

export async function buildCenterMaterialsWorkbook(rows, subtitle = '') {
  const workbook = new ExcelJS.Workbook();
  workbook.creator = '衡准考试信息管理系统';
  workbook.created = new Date();
  const sorted = [...rows].sort((a, b) => String(a.centerCode).localeCompare(String(b.centerCode))
    || String(a.subjectDate).localeCompare(String(b.subjectDate)) || String(a.subjectTime).localeCompare(String(b.subjectTime))
    || String(a.examRoomCode).localeCompare(String(b.examRoomCode)) || String(a.seat).localeCompare(String(b.seat)));
  const baseTitle = subtitle ? `｜${subtitle}` : '';
  addDeskStickerSheet(workbook, sorted);

  const doorMap = new Map();
  for (const item of sorted) {
    const key = [item.examCode, item.subjectName, item.centerCode, item.examRoomCode, item.roomCode].join('|');
    const current = doorMap.get(key) || { ...item, candidateCount: 0 };
    current.candidateCount += 1;
    doorMap.set(key, current);
  }
  addOperationalSheet(workbook, '门贴', `考场门贴${baseTitle}`, [
    ['examName', '考试', 28], ['subjectName', '科目', 14], ['subjectDate', '日期', 14], ['subjectTime', '时间', 16],
    ['centerName', '考点', 24], ['examRoomCode', '考试考场序号', 16], ['roomName', '考场通用名称', 20],
    ['roomCode', '场地代码', 14], ['building', '楼栋', 16], ['floor', '楼层', 10], ['candidateCount', '人数', 10]
  ], [...doorMap.values()]);

  addOperationalSheet(workbook, '考场签名单', `考场考生签名单${baseTitle}`, [
    ['examName', '考试', 26], ['subjectName', '科目', 14], ['subjectDate', '日期', 14], ['subjectTime', '时间', 16],
    ['examRoomCode', '考试考场序号', 16], ['roomName', '考场通用名称', 20], ['building', '楼栋', 16], ['floor', '楼层', 10],
    ['seat', '座位号', 10], ['cardNumber', '准考证号', 22], ['candidateName', '姓名', 14],
    ['schoolName', '考生学校', 24], ['className', '班级', 18], ['signature', '考生签名', 18]
  ], sorted.map(item => ({ ...item, signature: '' })));
  return workbook.xlsx.writeBuffer();
}