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 {
      // Create table name safely
      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
        const values = batch.map(row => ({
          import_id: importId,
          ...row
        }));

        // Use Prisma's createMany for batch insertion
        // Note: This assumes the table has a corresponding Prisma model
        // For dynamic table names, we would need to use raw SQL
        const result = 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.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,
          import_id INTEGER NOT NULL,
          ${columns},
          created_at 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';
    }
  }
}