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

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

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

export class DatabaseExcelReaderService {
  async readExcelFromDatabase(
    fileId: string,
    layoutConfig: any,
    onProgress: (progress: ImportProgress) => void
  ): Promise<ReadSectionData[]> {
    logger.info('Starting Excel file import from database', {
      fileId,
      layoutConfigSections: layoutConfig.sections?.length || 0
    });

    const startTime = Date.now();

    try {
      // Fetch file from database
      const fileRecord = await prisma.file.findUnique({
        where: { id: fileId }
      });

      if (!fileRecord) {
        throw new Error(`File with ID ${fileId} not found in database`);
      }

      logger.info('File retrieved from database', {
        filename: fileRecord.filename,
        size: fileRecord.size,
        mimetype: fileRecord.mimetype
      });

      // Convert Buffer to ArrayBuffer for xlsx library
      const arrayBuffer = fileRecord.data.buffer.slice(
        fileRecord.data.byteOffset,
        fileRecord.data.byteOffset + fileRecord.data.byteLength
      );

      logger.debug('Loading Excel workbook from buffer...');
      const workbook = XLSX.read(arrayBuffer, { type: 'array' });

      logger.info('Excel workbook loaded successfully from database', {
        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 from database', { totalSections });

      // Initialize progress
      onProgress({
        importId: 0,
        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.processSectionFromWorksheet(
          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 from database 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 from database', {
        error: error instanceof Error ? error.message : String(error),
        stack: error instanceof Error ? error.stack : undefined
      });
      throw error;
    }
  }

  async readExcelFromBuffer(
    buffer: Buffer,
    layoutConfig: any,
    onProgress: (progress: ImportProgress) => void
  ): Promise<ReadSectionData[]> {
    logger.info('Starting Excel file import from buffer', {
      bufferSize: buffer.length,
      layoutConfigSections: layoutConfig.sections?.length || 0
    });

    const startTime = Date.now();

    try {
      // Convert Buffer to ArrayBuffer for xlsx library
      const arrayBuffer = buffer.buffer.slice(
        buffer.byteOffset,
        buffer.byteOffset + buffer.byteLength
      );

      logger.debug('Loading Excel workbook from buffer...');
      const workbook = XLSX.read(arrayBuffer, { type: 'array' });

      logger.info('Excel workbook loaded successfully from buffer', {
        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 from buffer', { totalSections });

      // Initialize progress
      onProgress({
        importId: 0,
        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.processSectionFromWorksheet(
          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 from buffer 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 from buffer', {
        error: error instanceof Error ? error.message : String(error),
        stack: error instanceof Error ? error.stack : undefined
      });
      throw error;
    }
  }

  private async processSectionFromWorksheet(
    worksheet: XLSX.WorkSheet,
    section: any,
    sectionIndex: number,
    totalSections: number,
    onProgress: (progress: ImportProgress) => void
  ): Promise<ReadSectionData> {
    const sectionStartTime = Date.now();
    logger.info(`Starting section processing from worksheet`, {
      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, rowNum);
          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(
              field.dataType,
              field.dataTypeFormat,
              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, rowNumber: number): { row: number; col: number } {
    logger.debug(`Parsing cell address: ${cellPosition}`);

    let match = cellPosition.match(/([A-Z]+)(\d+)/);
    if (!match) {
      logger.warn(`Invalid cell position format: ${cellPosition}, trying to add row number ${rowNumber}`);
      const appendedCellPosition = `${cellPosition}${rowNumber}`;
      match = appendedCellPosition.match(/([A-Z]+)(\d+)/);
      if (!match) {
        logger.warn(`Invalid cell position format: ${appendedCellPosition}, 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(dataType: string, dataTypeFormat: string | undefined, 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 = (() => {
      if (dataType === "DATE")
        return XLSX.SSF.format(dataTypeFormat || 'yyyy-mm-dd', value);

      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;
  }
}