vtorio/app/lib/excel-import/excel-reader.ts

import * as ExcelJS from 'exceljs';
import { ReadSectionData, LayoutSectionField, SectionTypeEnum, FieldTypeEnum, ImportProgress } from './types';

export class ExcelReaderService {
  async readExcelFile(
    fileBuffer: Buffer,
    layoutConfig: any,
    onProgress: (progress: ImportProgress) => void
  ): Promise<ReadSectionData[]> {
    const workbook = new ExcelJS.Workbook();
    await workbook.xlsx.load(fileBuffer);
    
    const results: ReadSectionData[] = [];
    const totalSections = layoutConfig.sections?.length || 0;
    
    // Initialize progress
    onProgress({
      importId: 0, // Will be set by caller
      status: 'processing',
      currentSection: '',
      currentRow: 0,
      totalRows: 0,
      errors: [],
      processedSections: 0,
      totalSections
    });

    for (let sectionIndex = 0; sectionIndex < totalSections; sectionIndex++) {
      const section = layoutConfig.sections[sectionIndex];
      const worksheet = workbook.getWorksheet(section.sheetName);
      
      if (!worksheet) {
        onProgress({
          importId: 0,
          status: 'processing',
          currentSection: section.name,
          currentRow: 0,
          totalRows: 0,
          errors: [`Worksheet '${section.sheetName}' not found`],
          processedSections: sectionIndex + 1,
          totalSections
        });
        continue;
      }

      const sectionData = await this.processSection(worksheet, section, sectionIndex, totalSections, onProgress);
      results.push(sectionData);
    }

    return results;
  }

  private async processSection(
    worksheet: ExcelJS.Worksheet,
    section: any,
    sectionIndex: number,
    totalSections: number,
    onProgress: (progress: ImportProgress) => void
  ): Promise<ReadSectionData> {
    const startingRow = section.startingRow || 1;
    const endingRow = section.endingRow || worksheet.rowCount;
    
    // Get headers from the first row (assuming row 1 has headers)
    const headers: string[] = [];
    const headerRow = worksheet.getRow(1);
    headerRow.eachCell((cell) => {
      headers.push(cell.text || '');
    });

    // Process data rows
    const data: Record<string, any>[] = [];
    const totalRows = endingRow - startingRow + 1;
    
    for (let rowNum = startingRow; rowNum <= endingRow; rowNum++) {
      const row = worksheet.getRow(rowNum);
      if (!row.hasValues) continue;

      const rowData: Record<string, any> = {};
      
      // Map cell values based on field configuration
      for (const field of section.fields || []) {
        const cellAddress = this.parseCellAddress(field.cellPosition);
        const cell = row.getCell(cellAddress.col);
        
        if (cell && cell.value !== null && cell.value !== undefined) {
          rowData[field.importTableColumnName] = this.convertCellValue(
            cell.value,
            field.parsedType || FieldTypeEnum.String
          );
        }
      }
      
      data.push(rowData);
      
      // Update progress every 100 rows
      if (rowNum % 100 === 0 || rowNum === endingRow) {
        onProgress({
          importId: 0,
          status: 'processing',
          currentSection: section.name,
          currentRow: rowNum - startingRow + 1,
          totalRows,
          errors: [],
          processedSections: sectionIndex,
          totalSections
        });
      }
    }

    return {
      id: section.id || 0,
      name: section.name || '',
      tableName: section.tableName || '',
      sheet: section.sheetName || '',
      type: section.type || '',
      startingRow,
      endingRow,
      parsedType: this.mapSectionType(section.type),
      fields: this.mapFields(section.fields || []),
      data
    };
  }

  private parseCellAddress(cellPosition: string): { row: number; col: number } {
    const match = cellPosition.match(/([A-Z]+)(\d+)/);
    if (!match) return { row: 1, col: 1 };
    
    const col = match[1].charCodeAt(0) - 'A'.charCodeAt(0) + 1;
    const row = parseInt(match[2]);
    
    return { row, col };
  }

  private mapSectionType(type: string): SectionTypeEnum {
    switch (type?.toLowerCase()) {
      case 'grid':
        return SectionTypeEnum.Grid;
      case 'properties':
        return SectionTypeEnum.Properties;
      default:
        return SectionTypeEnum.Unknown;
    }
  }

  private mapFields(fields: any[]): LayoutSectionField[] {
    return fields.map((field, index) => ({
      id: field.id || index,
      cellPosition: field.cellPosition || '',
      name: field.name || '',
      dataType: field.dataType || 'string',
      dataTypeFormat: field.dataTypeFormat,
      importTableColumnName: field.importTableColumnName || field.name || `column_${index}`,
      importColumnOrderNumber: field.importColumnOrderNumber || index,
      parsedType: this.mapFieldType(field.dataType)
    }));
  }

  private mapFieldType(dataType: string): FieldTypeEnum {
    const type = dataType?.toLowerCase();
    
    switch (type) {
      case 'time':
        return FieldTypeEnum.Time;
      case 'decimal':
      case 'number':
      case 'float':
        return FieldTypeEnum.Decimal;
      case 'date':
        return FieldTypeEnum.Date;
      case 'int':
      case 'integer':
      case 'numeric':
        return FieldTypeEnum.Numeric;
      default:
        return FieldTypeEnum.String;
    }
  }

  private convertCellValue(value: any, fieldType: FieldTypeEnum): any {
    if (value === null || value === undefined) return null;
    
    switch (fieldType) {
      case FieldTypeEnum.Time:
        if (typeof value === 'number') {
          // Excel time is fraction of a day
          return value * 24 * 60 * 60 * 1000; // Convert to milliseconds
        }
        return value;
      
      case FieldTypeEnum.Decimal:
        return parseFloat(value.toString()) || 0;
      
      case FieldTypeEnum.Date:
        if (typeof value === 'number') {
          // Excel date is days since 1900-01-01
          const excelEpoch = new Date(1900, 0, 1);
          return new Date(excelEpoch.getTime() + (value - 1) * 24 * 60 * 60 * 1000);
        }
        return new Date(value);
      
      case FieldTypeEnum.Numeric:
        return parseInt(value.toString()) || 0;
      
      case FieldTypeEnum.String:
      default:
        return value.toString();
    }
  }
}