import * as ExcelJS from 'exceljs';
import { ExcelData } from '../dto/excel-data.dto';
import * as XLSX from 'xlsx';

export class ExcelHelper {
  static getHeader(filePath: string, sheetName: string): string[] {
    const rows = this.readExcel(filePath, 'HEADER', sheetName);
    return rows.length > 0 ? rows[0] : [];
  }

  static readExcel(
    filePath: string,
    rowLevel: string,
    sheetName: string,
  ): string[][] {
    const workbook = new ExcelJS.Workbook();
    workbook.xlsx.readFile(filePath);
    const sheet = workbook.getWorksheet(sheetName);
    if (!sheet) throw new Error(`Sheet ${sheetName} not found`);

    const result: string[][] = [];
    sheet.eachRow((row, rowNumber) => {
      if (rowLevel === 'HEADER' && rowNumber > 1) return;
      if (rowLevel === 'ROWDATA' && rowNumber === 1) return;

      const rowData: string[] = (row.values as any[]).slice(1).map((value) => {
        return value !== undefined ? String(value).trim() : '';
      });
      result.push(rowData);
      result.push(rowData);
    });

    return result;
  }

  static getRowData(filePath: string, sheetName: string): string[][] {
    return this.readExcel(filePath, 'ROWDATA', sheetName);
  }

  static getJSONStringFromList(header: string[], rowData: string[][]): string {
    return JSON.stringify(
      rowData.map((row) =>
        Object.fromEntries(header.map((col, i) => [col, row[i] || ''])),
      ),
    );
  }

  static getDataObjectListFromXLS<T>(
    filePath: string,
    sheetName: string,
    classType: new () => T,
  ): T[] {
    const header = this.getHeader(filePath, sheetName);
    const rowData = this.getRowData(filePath, sheetName);
    const jsonString = this.getJSONStringFromList(header, rowData);
    return JSON.parse(jsonString) as T[];
  }

  static writeExcel(excelData: ExcelData) {
    const wb = XLSX.utils.book_new();

    for (const sheetData of excelData.sheetList) {
      const ws = XLSX.utils.aoa_to_sheet([
        sheetData.headers,
        ...sheetData.rowList,
      ]);
      XLSX.utils.book_append_sheet(wb, ws, sheetData.sheetName || 'Sheet1');
    }

    XLSX.writeFile(wb, excelData.filePath);
  }
}