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

/* eslint-disable @typescript-eslint/no-explicit-any */
import * as XLSX from 'xlsx';
import { ReadSectionData, LayoutSectionField, SectionTypeEnum, FieldTypeEnum, ImportProgress } from './types';

// Simple logger utility for debugging
const logger = {
  debug: (message: string, ...args: any[]) => {
    console.debug(`[ExcelReaderService] ${new Date().toISOString()} - ${message}`, ...args);
  },
  info: (message: string, ...args: any[]) => {
    console.info(`[ExcelReaderService] ${new Date().toISOString()} - ${message}`, ...args);
  },
  warn: (message: string, ...args: any[]) => {
    console.warn(`[ExcelReaderService] ${new Date().toISOString()} - ${message}`, ...args);
  },
  error: (message: string, ...args: any[]) => {
    console.error(`[ExcelReaderService] ${new Date().toISOString()} - ${message}`, ...args);
  }
};

export class ExcelReaderService {
  async readExcelFile(
    filePath: string,
    layoutConfig: any,
    onProgress: (progress: ImportProgress) => void
  ): Promise<ReadSectionData[]> {
    logger.info('Starting Excel file import', {
      filePath,
      fileSize: require('fs').statSync(filePath).size,
      layoutConfigSections: layoutConfig.sections?.length || 0
    });

    const startTime = Date.now();
    
    try {
      logger.debug('Loading Excel workbook from file path...');
      const workbook = XLSX.readFile(filePath);
      logger.info('Excel workbook loaded successfully', {
        worksheets: workbook.SheetNames.map(name => ({ name, rowCount: XLSX.utils.sheet_to_json(workbook.Sheets[name]).length }))
      });
        
      const results: ReadSectionData[] = [];
      const totalSections = layoutConfig.sections?.length || 0;
      
      logger.info('Processing Excel import', { totalSections });
      
      // 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];
        logger.debug(`Processing section ${sectionIndex + 1}/${totalSections}`, {
          sectionName: section.name,
          sheetName: section.sheetName,
          startingRow: section.startingRow,
          endingRow: section.endingRow
        });
        
        const worksheet = workbook.Sheets[section.sheetName];
        
        if (!worksheet) {
          const error = `Worksheet '${section.sheetName}' not found`;
          logger.warn(error, { availableWorksheets: workbook.SheetNames });
          
          onProgress({
            importId: 0,
            status: 'processing',
            currentSection: section.name,
            currentRow: 0,
            totalRows: 0,
            errors: [error],
            processedSections: sectionIndex + 1,
            totalSections
          });
          continue;
        }

        const sectionData = await this.processSection(worksheet, section, sectionIndex, totalSections, onProgress);
        results.push(sectionData);
        
        logger.info(`Section ${section.name} processed successfully`, {
          rowsProcessed: sectionData.data.length,
          fields: sectionData.fields.length
        });
      }

      const totalTime = Date.now() - startTime;
      logger.info('Excel file import completed', {
        totalSections: results.length,
        totalRows: results.reduce((sum, section) => sum + section.data.length, 0),
        totalTimeMs: totalTime
      });

      return results;
    } catch (error) {
      logger.error('Error reading Excel file', {
        error: error instanceof Error ? error.message : String(error),
        stack: error instanceof Error ? error.stack : undefined
      });
      throw error;
    }
  }

  private async processSection(
    worksheet: XLSX.WorkSheet,
    section: any,
    sectionIndex: number,
    totalSections: number,
    onProgress: (progress: ImportProgress) => void
  ): Promise<ReadSectionData> {
    const sectionStartTime = Date.now();
    logger.info(`Starting section processing`, {
      sectionName: section.name,
      sheetName: section.sheetName,
      sectionIndex: sectionIndex + 1,
      totalSections
    });

    const startingRow = section.startingRow || 2; // Default to 2 to skip header
    const endingRow = section.endingRow || Infinity;
    
    logger.debug('Section configuration', {
      sectionName: section.name,
      startingRow,
      endingRow,
      fieldsCount: section.fields?.length || 0
    });
    
    // Convert worksheet to JSON array
    const worksheetData = XLSX.utils.sheet_to_json(worksheet, { header: 1 }) as any[][];
    
    // Process data rows
    const data: Record<string, any>[] = [];
    const totalRows = Math.min(endingRow, worksheetData.length) - startingRow + 1;
    
    let processedRows = 0;
    let skippedRows = 0;
    
    for (let rowNum = startingRow; rowNum <= Math.min(endingRow, worksheetData.length); rowNum++) {
      const row = worksheetData[rowNum - 1]; // Convert to 0-based index
      
      if (!row || row.every(cell => cell === null || cell === undefined || cell === '')) {
        skippedRows++;
        logger.debug(`Skipping empty row ${rowNum}`);
        continue;
      }

      const rowData: Record<string, any> = {};
      let fieldsProcessed = 0;
      
      // Map cell values based on field configuration
      for (const field of section.fields || []) {
        try {
          const cellAddress = this.parseCellAddress(field.cellPosition);
          const cellValue = row[cellAddress.col - 1]; // Convert to 0-based index
          
          logger.debug(`Processing field`, {
            fieldName: field.name,
            cellPosition: field.cellPosition,
            cellAddress,
            rawValue: cellValue,
            rowNum
          });
          
          if (cellValue !== null && cellValue !== undefined && cellValue !== '') {
            const value = this.convertCellValue(
              cellValue,
              field.parsedType || FieldTypeEnum.String
            );
            
            logger.debug(`Value converted`, {
              fieldName: field.name,
              originalValue: cellValue,
              convertedValue: value,
              fieldType: field.parsedType || FieldTypeEnum.String
            });
            
            // Map to the correct column name for Prisma model
            const columnName = field.importTableColumnName;
            rowData[columnName] = value;
            fieldsProcessed++;
          }
        } catch (error) {
          logger.error(`Error processing field ${field.name} at row ${rowNum}`, {
            error: error instanceof Error ? error.message : String(error),
            field,
            rowNum
          });
        }
      }
      
      // Only add non-empty rows
      if (Object.keys(rowData).length > 0) {
        data.push(rowData);
        processedRows++;
        
        if (processedRows <= 5 || processedRows % 100 === 0) {
          logger.debug(`Row processed`, {
            rowNum,
            fieldsProcessed,
            rowDataKeys: Object.keys(rowData),
            dataLength: data.length
          });
        }
      } else {
        logger.debug(`Skipping row with no valid data`, { rowNum });
      }
      
      // Update progress every 100 rows
      if (rowNum % 100 === 0 || rowNum === Math.min(endingRow, worksheetData.length)) {
        onProgress({
          importId: 0,
          status: 'processing',
          currentSection: section.name,
          currentRow: rowNum - startingRow + 1,
          totalRows,
          errors: [],
          processedSections: sectionIndex,
          totalSections
        });
      }
    }

    const sectionTime = Date.now() - sectionStartTime;
    logger.info(`Section processing completed`, {
      sectionName: section.name,
      processedRows,
      skippedRows,
      totalRows,
      dataRows: data.length,
      processingTimeMs: sectionTime
    });

    const result = {
      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
    };

    logger.debug('Section result', {
      sectionName: section.name,
      resultSummary: {
        id: result.id,
        name: result.name,
        tableName: result.tableName,
        dataRows: result.data.length,
        fields: result.fields.length
      }
    });

    return result;
  }

  private parseCellAddress(cellPosition: string): { row: number; col: number } {
    logger.debug(`Parsing cell address: ${cellPosition}`);
    
    const match = cellPosition.match(/([A-Z]+)(\d+)/);
    if (!match) {
      logger.warn(`Invalid cell position format: ${cellPosition}, using default 1,1`);
      return { row: 1, col: 1 };
    }
    
    const col = match[1].charCodeAt(0) - 'A'.charCodeAt(0) + 1;
    const row = parseInt(match[2]);
    
    logger.debug(`Parsed cell address`, {
      original: cellPosition,
      row,
      col
    });
    
    return { row, col };
  }

  private mapSectionType(type: string): SectionTypeEnum {
    logger.debug(`Mapping section type: ${type}`);
    
    const mappedType = (() => {
      switch (type?.toLowerCase()) {
        case 'grid':
          return SectionTypeEnum.Grid;
        case 'properties':
          return SectionTypeEnum.Properties;
        default:
          return SectionTypeEnum.Unknown;
      }
    })();
    
    logger.debug(`Section type mapped`, {
      originalType: type,
      mappedType: SectionTypeEnum[mappedType]
    });
    
    return mappedType;
  }

  private mapFields(fields: any[]): LayoutSectionField[] {
    logger.debug(`Mapping ${fields.length} fields`);
    
    const mappedFields = fields.map((field, index) => {
      const mappedField = {
        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)
      };
      
      logger.debug(`Field mapped`, {
        index,
        originalName: field.name,
        mappedName: mappedField.name,
        cellPosition: mappedField.cellPosition,
        parsedType: FieldTypeEnum[mappedField.parsedType]
      });
      
      return mappedField;
    });
    
    return mappedFields;
  }

  private mapFieldType(dataType: string): FieldTypeEnum {
    const type = dataType?.toLowerCase();
    
    const mappedType = (() => {
      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;
      }
    })();
    
    logger.debug(`Field type mapped`, {
      originalDataType: dataType,
      mappedType: FieldTypeEnum[mappedType]
    });
    
    return mappedType;
  }

  private convertCellValue(value: any, fieldType: FieldTypeEnum): any {
    if (value === null || value === undefined) {
      logger.debug(`Converting null/undefined value to null`, { fieldType: FieldTypeEnum[fieldType] });
      return null;
    }
    
    logger.debug(`Converting cell value`, {
      originalValue: value,
      originalType: typeof value,
      targetFieldType: FieldTypeEnum[fieldType]
    });
    
    const convertedValue = (() => {
      switch (fieldType) {
        case FieldTypeEnum.Time:
          if (typeof value === 'number') {
            // Excel time is fraction of a day
            const result = value * 24 * 60 * 60 * 1000; // Convert to milliseconds
            logger.debug(`Time conversion`, { original: value, converted: result });
            return result;
          }
          return value;
        
        case FieldTypeEnum.Decimal:
          const decimalResult = parseFloat(value.toString()) || 0;
          logger.debug(`Decimal conversion`, { original: value, converted: decimalResult });
          return decimalResult;
        
        case FieldTypeEnum.Date:
          if (typeof value === 'number') {
            // Excel date is days since 1900-01-01
            const excelEpoch = new Date(1900, 0, 1);
            const dateResult = new Date(excelEpoch.getTime() + (value - 1) * 24 * 60 * 60 * 1000);
            logger.debug(`Date conversion`, { original: value, converted: dateResult });
            return dateResult;
          }
          const dateResult = new Date(value);
          logger.debug(`Date conversion from string`, { original: value, converted: dateResult });
          return dateResult;
        
        case FieldTypeEnum.Numeric:
          const numericResult = parseInt(value.toString()) || 0;
          logger.debug(`Numeric conversion`, { original: value, converted: numericResult });
          return numericResult;
        
        case FieldTypeEnum.String:
        default:
          const stringResult = value.toString();
          logger.debug(`String conversion`, { original: value, converted: stringResult });
          return stringResult;
      }
    })();
    
    return convertedValue;
  }
}