vtorio/app/lib/excel-import/bulk-inserter.ts

import { PrismaClient } from '@prisma/client';
import { ReadSectionData } from './types';

export class BulkInserter {
  private prisma: PrismaClient;

  constructor() {
    this.prisma = new PrismaClient();
  }

  async insertSectionData(
    sectionData: ReadSectionData,
    importId: number,
    onProgress: (rows: number) => void
  ): Promise<number> {
    const batchSize = 5000;
    const totalRows = sectionData.data.length;
    let insertedRows = 0;

    try {
      // Handle specific table names with Prisma models
      const tableName = sectionData.tableName;

      for (let i = 0; i < totalRows; i += batchSize) {
        const batch = sectionData.data.slice(i, i + batchSize);

        if (batch.length === 0) continue;

        // Prepare data for insertion with proper field mapping
        const values = batch.map(row => {
          const mappedRow: any = {
            importId: importId,
            createdAt: new Date(),
            updatedAt: new Date()
          };

          // Map the row data to match Prisma model field names
          Object.keys(row).forEach(key => {
            // Convert snake_case to camelCase for Prisma model compatibility
            // Also handle digits (e.g., corp_id_2 -> corpId2)
            const camelKey = key.replace(/_([a-z0-9])/g, (g) => g[1].toUpperCase());
            mappedRow[camelKey] = row[key];
          });

          return mappedRow;
        });

        // Use appropriate Prisma model based on table name
        if (tableName === 'cintas_install_calendar') {
          await this.prisma.cintasInstallCalendar.createMany({
            data: values,
            skipDuplicates: false
          });
        } else if (tableName === 'cintas_install_calendar_summary' || tableName === 'cintas_intall_calendar_summary') {
          await this.prisma.cintasSummary.createMany({
            data: values,
            skipDuplicates: false
          });
        } else if (tableName === 'gow_data') {
          await this.prisma.gowData.createMany({
            data: values,
            skipDuplicates: false
          });
        } else if (tableName === 'gow_fac_id') {
          await this.prisma.gowFacId.createMany({
            data: values,
            skipDuplicates: false
          });
        } else if (tableName === 'gow_corp_ref') {
          await this.prisma.gowCorpRef.createMany({
            data: values,
            skipDuplicates: false
          });
        } else {
          // Fallback to raw SQL for other tables
          await this.prisma.$executeRawUnsafe(
            this.buildInsertQuery(tableName, values)
          );
        }

        insertedRows += batch.length;
        onProgress(insertedRows);
      }

      return insertedRows;

    } catch (error) {
      console.error('Error inserting section data:', error);
      throw error;
    }
  }

  private buildInsertQuery(tableName: string, values: any[]): string {
    if (values.length === 0) return '';

    const keys = Object.keys(values[0]);
    const columns = keys.map(key => `"${key}"`).join(', ');

    const placeholders = values.map(row => {
      const valuesList = keys.map(key => {
        const value = row[key];
        if (value === null || value === undefined) {
          return 'NULL';
        }
        if (typeof value === 'string') {
          return `'${value.replace(/'/g, "''")}'`;
        }
        return value;
      });
      return `(${valuesList.join(', ')})`;
    }).join(', ');

    return `INSERT INTO "${tableName}" (${columns}) VALUES ${placeholders}`;
  }

  async createImportTable(tableName: string, fields: any[]): Promise<void> {
    try {
      // Create table if it doesn't exist
      const columns = fields.map(field => {
        const dataType = this.mapDataType(field.dataType);
        return `"${field.importTableColumnName}" ${dataType}`;
      }).join(', ');

      const createTableQuery = `
        CREATE TABLE IF NOT EXISTS "${tableName}" (
          id SERIAL PRIMARY KEY,
          "importId" INTEGER NOT NULL,
          ${columns},
          "createdAt" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
      `;

      await this.prisma.$executeRawUnsafe(createTableQuery);
    } catch (error) {
      console.error('Error creating import table:', error);
      throw error;
    }
  }

  private mapDataType(dataType: string): string {
    switch (dataType?.toLowerCase()) {
      case 'string':
      case 'text':
        return 'TEXT';
      case 'number':
      case 'integer':
        return 'INTEGER';
      case 'decimal':
      case 'float':
        return 'DECIMAL';
      case 'boolean':
        return 'BOOLEAN';
      case 'date':
        return 'DATE';
      case 'datetime':
        return 'TIMESTAMP';
      default:
        return 'TEXT';
    }
  }
}