import { BadRequestException } from '@nestjs/common';
import * as ExcelJS from 'exceljs';
import JSZip from 'jszip';
import { parseDateValue } from './imports.helpers';
import { parseSheets } from './imports.workbook';

async function xlsxBuffer(rows: Array<Array<unknown>>): Promise<Buffer> {
  const workbook = new ExcelJS.Workbook();
  const worksheet = workbook.addWorksheet('名单');
  rows.forEach((row, index) => worksheet.addRow(row));
  return (await workbook.xlsx.writeBuffer()) as Buffer;
}

async function wpsNamespaceXlsxBuffer(): Promise<Buffer> {
  const zip = new JSZip();
  zip.file('[Content_Types].xml', '<?xml version="1.0" encoding="UTF-8"?><Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types"/>');
  zip.file(
    'xl/workbook.xml',
    `<x:workbook xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<x:sheets><x:sheet name="名单" sheetId="1" state="visible" r:id="rId1"/></x:sheets>
</x:workbook>`,
  );
  zip.file(
    'xl/_rels/workbook.xml.rels',
    `<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/>
</Relationships>`,
  );
  zip.file(
    'xl/sharedStrings.xml',
    `<x:sst xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><x:si><x:t>张三</x:t></x:si></x:sst>`,
  );
  zip.file(
    'xl/worksheets/sheet1.xml',
    `<x:worksheet xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<x:sheetData>
<x:row r="1"><x:c r="A1" t="inlineStr"><x:is><x:t>姓名</x:t></x:is></x:c><x:c r="B1" t="inlineStr"><x:is><x:t>手机号</x:t></x:is></x:c></x:row>
<x:row r="2"><x:c r="A2" t="s"><x:v>0</x:v></x:c><x:c r="B2"><x:v>13800138000</x:v></x:c></x:row>
</x:sheetData>
</x:worksheet>`,
  );
  return Buffer.from(await zip.generateAsync({ type: 'nodebuffer' }));
}

describe('parseSheets', () => {
  it('从 headerRow 取表头，并保留空行后的物理行号', async () => {
    const buffer = await xlsxBuffer(
      [
        ['标题行', null],
        ['姓名', '学号'],
        ['', ''],
        ['张三', '2024001'],
        ['李四', '2024002'],
      ],
    );
    const sheets = await parseSheets(
      buffer,
      'students.xlsx',
      'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
      2,
    );
    expect(sheets).toHaveLength(1);
    expect(sheets[0].headers).toEqual(['姓名', '学号']);
    expect(sheets[0].rows).toEqual([
      ['张三', '2024001'],
      ['李四', '2024002'],
    ]);
    expect(sheets[0].rowNumbers).toEqual([4, 5]);
  });

  it('解析 CSV 并默认使用第一行作为表头', async () => {
    const buffer = Buffer.from('姓名,学号\n张三,2024001\n', 'utf8');
    const sheets = await parseSheets(buffer, 'students.csv', 'text/csv');
    expect(sheets).toHaveLength(1);
    expect(sheets[0].headers).toEqual(['姓名', '学号']);
    expect(sheets[0].rows[0]).toEqual(['张三', 2024001]);
  });

  it('ExcelJS 失败时回退到 WPS 命名空间解析', async () => {
    const buffer = await wpsNamespaceXlsxBuffer();
    const sheets = await parseSheets(
      buffer,
      'students.xlsx',
      'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
    );
    expect(sheets).toHaveLength(1);
    expect(sheets[0].name).toBe('名单');
    expect(sheets[0].headers).toEqual(['姓名', '手机号']);
    expect(sheets[0].rows).toEqual([['张三', '13800138000']]);
  });

  it('拒绝 .xls 文件', async () => {
    await expect(
      parseSheets(Buffer.from('not excel'), 'a.xls', 'application/vnd.ms-excel'),
    ).rejects.toBeInstanceOf(BadRequestException);
  });
});

describe('parseDateValue', () => {
  it('按进程本地时区格式化为 YYYY-MM-DD（CST 午夜不偏移到前一天）', () => {
    expect(parseDateValue(new Date(2026, 0, 1))).toBe('2026-01-01');
    expect(parseDateValue(new Date(2026, 11, 31))).toBe('2026-12-31');
  });
});
