import { Injectable } from '@nestjs/common';
import ExcelJS from 'exceljs';
import { readXlsxSheetsFallback } from '../imports/imports.workbook-fallback';

export interface ExcelSheetInfo {
  name: string;
  rowCount: number;
  columns: string[];
}

export interface ExcelSheetRows {
  name: string;
  rows: string[][];
}

/**
 * Structured Excel reader used by the AI chat. ExcelJS handles standard
 * files; a direct OOXML fallback handles WPS-style files that prefix
 * every element with a namespace. The agent reads sheets on demand
 * (`list_excel_sheets` / `read_excel_rows`) instead of receiving one
 * fixed text dump.
 */
@Injectable()
export class AiExcelReaderService {
  async loadSheets(buffer: Buffer): Promise<ExcelSheetRows[]> {
    try {
      return await this.loadWithExcelJs(buffer);
    } catch {
      return this.loadWithFallback(buffer);
    }
  }

  async extractText(buffer: Buffer): Promise<string> {
    const sheets = await this.loadSheets(buffer);
    return sheets
      .map((sheet) => `# ${sheet.name}\n${sheet.rows.map((row) => row.join('\t')).join('\n')}`)
      .join('\n');
  }

  /** Sheet list + row counts + a short sample, small enough for prompts. */
  async overview(buffer: Buffer): Promise<{ sheets: ExcelSheetInfo[]; text: string }> {
    const sheets = await this.loadSheets(buffer);
    const info = sheets.map((sheet) => ({
      name: sheet.name,
      rowCount: sheet.rows.length,
      columns: sheet.rows[0] ?? [],
    }));
    const lines: string[] = [];
    for (const sheet of sheets) {
      lines.push(`# ${sheet.name}（共 ${sheet.rows.length} 行）`);
      for (const row of sheet.rows) lines.push(row.join('\t'));
    }
    return { sheets: info, text: lines.join('\n') };
  }

  async readRows(
    buffer: Buffer,
    sheetName: string | undefined,
    startRow: number,
    rowCount: number,
    maxColumns: number,
  ): Promise<{
    sheet: string;
    rowCount: number;
    startRow: number;
    rows: string[][];
    truncated: boolean;
  }> {
    const sheets = await this.loadSheets(buffer);
    const sheet = sheets.find((item) => item.name === sheetName) ?? sheets[0];
    if (!sheet) {
      return { sheet: sheetName ?? '', rowCount: 0, startRow, rows: [], truncated: false };
    }
    const from = Math.max(0, startRow - 1);
    const limit = rowCount;
    const slice = sheet.rows.slice(from, from + limit);
    const rows = slice.map((row) => row.slice(0, maxColumns));
    return {
      sheet: sheet.name,
      rowCount: sheet.rows.length,
      startRow: from + 1,
      rows,
      truncated: slice.length < limit,
    };
  }

  private async loadWithExcelJs(buffer: Buffer): Promise<ExcelSheetRows[]> {
    const workbook = new ExcelJS.Workbook();
    await workbook.xlsx.load(buffer.buffer as ArrayBuffer);
    const sheets: ExcelSheetRows[] = [];
    workbook.eachSheet((sheet) => {
      const rows: string[][] = [];
      sheet.eachRow((row) => {
        const values = Array.isArray(row.values) ? row.values.slice(1) : [];
        rows.push(values.map((value) => this.stringifyCellValue(value)));
      });
      sheets.push({ name: sheet.name, rows });
    });
    return sheets;
  }

  private async loadWithFallback(buffer: Buffer): Promise<ExcelSheetRows[]> {
    const sheets = await readXlsxSheetsFallback(buffer);
    return sheets.map((sheet) => ({ name: sheet.name, rows: sheet.rows }));
  }

  private stringifyCellValue(value: unknown): string {
    if (value === null || value === undefined) return '';
    if (value instanceof Date) {
      if (Number.isNaN(value.getTime())) return '';
      const year = value.getFullYear();
      const month = String(value.getMonth() + 1).padStart(2, '0');
      const day = String(value.getDate()).padStart(2, '0');
      return `${year}-${month}-${day}`;
    }
    if (typeof value === 'string' || typeof value === 'number' || typeof value === 'boolean') {
      return String(value);
    }
    try {
      return JSON.stringify(value) || '';
    } catch {
      return '';
    }
  }
}
