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

          // Map the row data to match Prisma model field names
          Object.keys(row).forEach(key => {
            // Convert snake_case to camelCase for Prisma model compatibility
            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') {
          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}`;
  }
}