path: root/components/form-data/import-excel-form.tsx

// lib/excelUtils.ts (continued)
import ExcelJS from "exceljs";
import { saveAs } from "file-saver";
import { toast } from "sonner";
import { DataTableColumnJSON } from "./form-data-table-columns";
import { updateFormDataInDB } from "@/lib/forms/services";
// Assuming the previous types are defined above
export interface ImportExcelOptions {
  file: File;
  tableData: GenericData[];
  columnsJSON: DataTableColumnJSON[];
  formCode?: string;        // Optional - provide to enable direct DB save
  contractItemId?: number;  // Optional - provide to enable direct DB save
  onPendingChange?: (isPending: boolean) => void;
  onDataUpdate?: (updater: ((prev: GenericData[]) => GenericData[]) | GenericData[]) => void;
}

export interface ImportExcelResult {
  success: boolean;
  importedCount?: number;
  error?: any;
  message?: string;
}

export interface ExportExcelOptions {
  tableData: GenericData[];
  columnsJSON: DataTableColumnJSON[];
  formCode: string;
  onPendingChange?: (isPending: boolean) => void;
}

// For typing consistency
interface GenericData {
  [key: string]: any;
}

export async function importExcelData({
  file,
  tableData,
  columnsJSON,
  formCode,
  contractItemId,
  onPendingChange,
  onDataUpdate
}: ImportExcelOptions): Promise<ImportExcelResult> {
  if (!file) return { success: false, error: "No file provided" };

  try {
    if (onPendingChange) onPendingChange(true);

    // Get existing tag numbers
    const existingTagNumbers = new Set(tableData.map((d) => d.TAG_NO));

    const workbook = new ExcelJS.Workbook();
    const arrayBuffer = await file.arrayBuffer();
    await workbook.xlsx.load(arrayBuffer);

    const worksheet = workbook.worksheets[0];

    // Parse headers
    const headerRow = worksheet.getRow(1);
    const headerRowValues = headerRow.values as ExcelJS.CellValue[];

    console.log("Original headers:", headerRowValues);

    // Create mappings between Excel headers and column definitions
    const headerToIndexMap = new Map<string, number>();
    for (let i = 1; i < headerRowValues.length; i++) {
      const headerValue = String(headerRowValues[i] || "").trim();
      if (headerValue) {
        headerToIndexMap.set(headerValue, i);
      }
    }

    // Validate headers
    let headerErrorMessage = "";

    // Check for missing required columns
    columnsJSON.forEach((col) => {
      const label = col.label;
      if (!headerToIndexMap.has(label)) {
        headerErrorMessage += `Column "${label}" is missing. `;
      }
    });

    // Check for unexpected columns
    headerToIndexMap.forEach((index, headerLabel) => {
      const found = columnsJSON.some((col) => col.label === headerLabel);
      if (!found) {
        headerErrorMessage += `Unexpected column "${headerLabel}" found in Excel. `;
      }
    });

    // Add error column
    const lastColIndex = worksheet.columnCount + 1;
    worksheet.getRow(1).getCell(lastColIndex).value = "Error";

    // If header validation fails, download error report and exit
    if (headerErrorMessage) {
      headerRow.getCell(lastColIndex).value = headerErrorMessage.trim();

      const outBuffer = await workbook.xlsx.writeBuffer();
      saveAs(new Blob([outBuffer]), `import-check-result_${Date.now()}.xlsx`);

      toast.error(`Header mismatch found. Please check downloaded file.`);
      return { success: false, error: "Header mismatch" };
    }

    // Create column key to Excel index mapping
    const keyToIndexMap = new Map<string, number>();
    columnsJSON.forEach((col) => {
      const index = headerToIndexMap.get(col.label);
      if (index !== undefined) {
        keyToIndexMap.set(col.key, index);
      }
    });

    // Parse and validate data rows
    const importedData: GenericData[] = [];
    const lastRowNumber = worksheet.lastRow?.number || 1;
    let errorCount = 0;

    // Process each data row
    for (let rowNum = 2; rowNum <= lastRowNumber; rowNum++) {
      const row = worksheet.getRow(rowNum);
      const rowValues = row.values as ExcelJS.CellValue[];
      if (!rowValues || rowValues.length <= 1) continue; // Skip empty rows

      let errorMessage = "";
      const rowObj: Record<string, any> = {};

      // Process each column
      columnsJSON.forEach((col) => {
        const colIndex = keyToIndexMap.get(col.key);
        if (colIndex === undefined) return;

        const cellValue = rowValues[colIndex] ?? "";
        let stringVal = String(cellValue).trim();

        // Type-specific validation
        switch (col.type) {
          case "STRING":
            if (!stringVal && col.key === "TAG_NO") {
              errorMessage += `[${col.label}] is empty. `;
            }
            rowObj[col.key] = stringVal;
            break;

          case "NUMBER":
            if (stringVal) {
              const num = parseFloat(stringVal);
              if (isNaN(num)) {
                errorMessage += `[${col.label}] '${stringVal}' is not a valid number. `;
              } else {
                rowObj[col.key] = num;
              }
            } else {
              rowObj[col.key] = null;
            }
            break;

          case "LIST":
            if (
              stringVal &&
              col.options &&
              !col.options.includes(stringVal)
            ) {
              errorMessage += `[${
                col.label
              }] '${stringVal}' not in ${col.options.join(", ")}. `;
            }
            rowObj[col.key] = stringVal;
            break;

          default:
            rowObj[col.key] = stringVal;
            break;
        }
      });

      // Validate TAG_NO
      const tagNum = rowObj["TAG_NO"];
      if (!tagNum) {
        errorMessage += `No TAG_NO found. `;
      } else if (!existingTagNumbers.has(tagNum)) {
        errorMessage += `TagNumber '${tagNum}' is not in current data. `;
      }

      // Record errors or add to valid data
      if (errorMessage) {
        row.getCell(lastColIndex).value = errorMessage.trim();
        errorCount++;
      } else {
        importedData.push(rowObj);
      }
    }

    // If there are validation errors, download error report and exit
    if (errorCount > 0) {
      const outBuffer = await workbook.xlsx.writeBuffer();
      saveAs(new Blob([outBuffer]), `import-check-result_${Date.now()}.xlsx`);
      toast.error(
        `There are ${errorCount} error row(s). Please check downloaded file.`
      );
      return { success: false, error: "Data validation errors" };
    }

    // If we reached here, all data is valid
    // Create locally merged data for UI update
    const mergedData = [...tableData];
    const dataMap = new Map<string, GenericData>();
    
    // Map existing data by TAG_NO
    mergedData.forEach(item => {
      if (item.TAG_NO) {
        dataMap.set(item.TAG_NO, item);
      }
    });
    
    // Update with imported data
    importedData.forEach(item => {
      if (item.TAG_NO) {
        const existingItem = dataMap.get(item.TAG_NO);
        if (existingItem) {
          // Update existing item with imported values
          Object.assign(existingItem, item);
        }
      }
    });

    // If formCode and contractItemId are provided, save directly to DB
    if (formCode && contractItemId) {
      try {
        // Process each imported row individually
        let successCount = 0;
        let errorCount = 0;
        const errors = [];

        // Since updateFormDataInDB expects a single row at a time,
        // we need to process each imported row individually
        for (const importedRow of importedData) {
          try {
            const result = await updateFormDataInDB(
              formCode,
              contractItemId,
              importedRow
            );
            
            if (result.success) {
              successCount++;
            } else {
              errorCount++;
              errors.push(`Error updating tag ${importedRow.TAG_NO}: ${result.message}`);
            }
          } catch (rowError) {
            errorCount++;
            errors.push(`Exception updating tag ${importedRow.TAG_NO}: ${rowError instanceof Error ? rowError.message : 'Unknown error'}`);
          }
        }

        // If any errors occurred
        if (errorCount > 0) {
          console.error("Errors during import:", errors);
          
          if (successCount > 0) {
            toast.warning(`Partially successful: ${successCount} rows updated, ${errorCount} errors`);
          } else {
            toast.error(`Failed to update all ${errorCount} rows`);
          }
          
          // If some rows were updated successfully, update the local state
          if (successCount > 0) {
            if (onDataUpdate) {
              onDataUpdate(() => mergedData);
            }
            return { 
              success: true, 
              importedCount: successCount,
              message: `Partially successful: ${successCount} rows updated, ${errorCount} errors` 
            };
          } else {
            return { 
              success: false, 
              error: "All updates failed", 
              message: errors.join("\n")
            };
          }
        }
        
        // All rows were updated successfully
        if (onDataUpdate) {
          onDataUpdate(() => mergedData);
        }
        
        toast.success(`Successfully updated ${successCount} rows`);
        return { 
          success: true, 
          importedCount: successCount, 
          message: "All data imported and saved to database" 
        };
      } catch (saveError) {
        console.error("Failed to save imported data:", saveError);
        toast.error("Failed to save imported data to database");
        return { success: false, error: saveError };
      }
    } else {
      // Fall back to just updating local state if DB parameters aren't provided
      if (onDataUpdate) {
        onDataUpdate(() => mergedData);
      }
      
      toast.success(`Imported ${importedData.length} rows successfully (local only)`);
      return { success: true, importedCount: importedData.length };
    }
    
  } catch (err) {
    console.error("Excel import error:", err);
    toast.error("Excel import failed.");
    return { success: false, error: err };
  } finally {
    if (onPendingChange) onPendingChange(false);
  }
}