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

import * as XLSX from 'xlsx';
import { ReadSectionData, LayoutSectionField, SectionTypeEnum, FieldTypeEnum, ImportProgress } from './types';
import { prisma } from '@/lib/prisma';

export class DatabaseExcelReaderService {
  async readExcelFromDatabase(
    fileId: string,
    layoutConfig: any,
    onProgress: (progress: ImportProgress) => void
  ): Promise<ReadSectionData[]> {
    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`);
      }

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

      const workbook = XLSX.read(arrayBuffer, { type: 'array' });

      const results: ReadSectionData[] = [];
      const totalSections = layoutConfig.sections?.length || 0;

      // 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];

        const worksheet = workbook.Sheets[section.sheetName];

        if (!worksheet) {
          const error = `Worksheet '${section.sheetName}' not found`;

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

      return results;
    } catch (error) {
      throw error;
    }
  }

  async readExcelFromBuffer(
    buffer: Buffer,
    layoutConfig: any,
    onProgress: (progress: ImportProgress) => void
  ): Promise<ReadSectionData[]> {

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

      const workbook = XLSX.read(arrayBuffer, { type: 'array' });

      const results: ReadSectionData[] = [];
      const totalSections = layoutConfig.sections?.length || 0;

      // 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];

        const worksheet = workbook.Sheets[section.sheetName];

        if (!worksheet) {
          const error = `Worksheet '${section.sheetName}' not found`;

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

      return results;
    } catch (error) {
      throw error;
    }
  }

  private async processSectionFromWorksheet(
    worksheet: XLSX.WorkSheet,
    section: any,
    sectionIndex: number,
    totalSections: number,
    onProgress: (progress: ImportProgress) => void
  ): Promise<ReadSectionData> {
    const startingRow = section.startingRow || 2; // Default to 2 to skip header
    const endingRow = section.endingRow || Infinity;

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

    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 === '')) {
        continue;
      }

      const rowData: Record<string, any> = {};

      // 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

          if (cellValue !== null && cellValue !== undefined && cellValue !== '') {
            const value = this.convertCellValue(
              field.dataType,
              field.dataTypeFormat,
              cellValue,
              field.parsedType || FieldTypeEnum.String
            );

            // Map to the correct column name for Prisma model
            const columnName = field.importTableColumnName;
            rowData[columnName] = value;
          }
        } catch (error) {
          console.log(`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);
      }

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

    return result;
  }

  private parseCellAddress(cellPosition: string, rowNumber: number): { row: number; col: number } {

    let match = cellPosition.match(/([A-Z]+)(\d+)/);
    if (!match) {
      const appendedCellPosition = `${cellPosition}${rowNumber}`;
      match = appendedCellPosition.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 {
    const mappedType = (() => {
      switch (type?.toLowerCase()) {
        case 'grid':
          return SectionTypeEnum.Grid;
        case 'properties':
          return SectionTypeEnum.Properties;
        default:
          return SectionTypeEnum.Unknown;
      }
    })();

    return mappedType;
  }

  private mapFields(fields: any[]): LayoutSectionField[] {
    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)
      };

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

    return mappedType;
  }

  private convertCellValue(dataType: string, dataTypeFormat: string | undefined, value: any, fieldType: FieldTypeEnum): any {
    if (value === null || value === undefined) {
      return null;
    }

    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
            return result;
          }
          return value;

        case FieldTypeEnum.Decimal:
          const decimalResult = parseFloat(value.toString()) || 0;
          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);
            return dateResult;
          }
          const dateResult = new Date(value);
          return dateResult;

        case FieldTypeEnum.Numeric:
          const numericResult = parseInt(value.toString()) || 0;
          return numericResult;

        case FieldTypeEnum.String:
        default:
          const stringResult = value.toString();
          return stringResult;
      }
    })();

    return convertedValue;
  }
}