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

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";
import { decryptWithServerAction } from "../drm/drmUtils";

// Define error structure for import
export interface ImportError {
  tagNo: string;
  rowIndex: number;
  columnKey: string;
  columnLabel: string;
  errorType: string;
  errorMessage: string;
  currentValue?: any;
  expectedFormat?: string;
}

// Simplified options interface without editableFieldsMap
export interface ImportExcelOptions {
  file: File;
  tableData: GenericData[];
  columnsJSON: DataTableColumnJSON[];
  formCode?: string;
  contractItemId?: number;
  onPendingChange?: (isPending: boolean) => void;
  onDataUpdate?: (updater: ((prev: GenericData[]) => GenericData[]) | GenericData[]) => void;
}

export interface ImportExcelResult {
  success: boolean;
  importedCount?: number;
  error?: any;
  message?: string;
  skippedFields?: { tagNo: string, fields: string[] }[]; // 건너뛴 필드 정보
  errorCount?: number;
  hasErrors?: boolean;
}

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

interface GenericData {
  [key: string]: any;
}

/**
 * Create error sheet with import validation results
 */
function createImportErrorSheet(workbook: ExcelJS.Workbook, errors: ImportError[], headerErrors?: string[]) {

  const existingErrorSheet = workbook.getWorksheet("Import_Errors");
  if (existingErrorSheet) {
    workbook.removeWorksheet("Import_Errors");
  }
  
  const errorSheet = workbook.addWorksheet("Import_Errors");

  // Add header error section if exists
  if (headerErrors && headerErrors.length > 0) {
    errorSheet.addRow(["HEADER VALIDATION ERRORS"]);
    const headerErrorTitleRow = errorSheet.getRow(1);
    headerErrorTitleRow.font = { bold: true, size: 14, color: { argb: "FFFFFFFF" } };
    headerErrorTitleRow.getCell(1).fill = {
      type: "pattern",
      pattern: "solid",
      fgColor: { argb: "FFDC143C" },
    };

    headerErrors.forEach((error, index) => {
      const errorRow = errorSheet.addRow([`${index + 1}. ${error}`]);
      errorRow.getCell(1).fill = {
        type: "pattern",
        pattern: "solid",
        fgColor: { argb: "FFFFCCCC" },
      };
    });

    errorSheet.addRow([]); // Empty row for separation
  }

  // Data validation errors section
  const startRow = errorSheet.rowCount + 1;
  
  // Summary row
  errorSheet.addRow([`DATA VALIDATION ERRORS: ${errors.length} errors found`]);
  const summaryRow = errorSheet.getRow(startRow);
  summaryRow.font = { bold: true, size: 12 };
  if (errors.length > 0) {
    summaryRow.getCell(1).fill = {
      type: "pattern",
      pattern: "solid",
      fgColor: { argb: "FFFFC0C0" },
    };
  }

  if (errors.length > 0) {
    // Error data headers
    const errorHeaders = [
      "TAG NO",
      "Row Number",
      "Column",
      "Error Type",
      "Error Message",
      "Current Value",
      "Expected Format",
    ];

    errorSheet.addRow(errorHeaders);
    const headerRow = errorSheet.getRow(errorSheet.rowCount);
    headerRow.font = { bold: true, color: { argb: "FFFFFFFF" } };
    headerRow.alignment = { horizontal: "center" };
    
    headerRow.eachCell((cell) => {
      cell.fill = {
        type: "pattern",
        pattern: "solid",
        fgColor: { argb: "FFDC143C" },
      };
    });

    // Add error data
    errors.forEach((error) => {
      const errorRow = errorSheet.addRow([
        error.tagNo,
        error.rowIndex,
        error.columnLabel,
        error.errorType,
        error.errorMessage,
        error.currentValue || "",
        error.expectedFormat || "",
      ]);

      // Color code by error type
      errorRow.eachCell((cell) => {
        let bgColor = "FFFFFFFF"; // Default white
        
        switch (error.errorType) {
          case "MISSING_TAG_NO":
            bgColor = "FFFFCCCC"; // Light red
            break;
          case "TAG_NOT_FOUND":
            bgColor = "FFFFDDDD"; // Very light red
            break;
          case "TYPE_MISMATCH":
            bgColor = "FFFFEECC"; // Light orange
            break;
          case "INVALID_OPTION":
            bgColor = "FFFFFFE0"; // Light yellow
            break;
          case "HEADER_MISMATCH":
            bgColor = "FFFFE0E0"; // Very light red
            break;
        }

        cell.fill = {
          type: "pattern",
          pattern: "solid",
          fgColor: { argb: bgColor },
        };
      });
    });
  }

  // Auto-fit columns
  errorSheet.columns.forEach((column) => {
    let maxLength = 0;
    column.eachCell({ includeEmpty: false }, (cell) => {
      const columnLength = String(cell.value).length;
      if (columnLength > maxLength) {
        maxLength = columnLength;
      }
    });
    column.width = Math.min(Math.max(maxLength + 2, 10), 50);
  });

  return errorSheet;
}

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 and create a map for quick lookup
    const existingTagNumbers = new Set(tableData.map((d) => d.TAG_NO));
    const existingDataMap = new Map<string, GenericData>();
    tableData.forEach(item => {
      if (item.TAG_NO) {
        existingDataMap.set(item.TAG_NO, item);
      }
    });

    const workbook = new ExcelJS.Workbook();
    const arrayBuffer = await decryptWithServerAction(file);
    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
    const headerErrors: string[] = [];

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

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

    // If header validation fails, create error report and exit
    if (headerErrors.length > 0) {
      createImportErrorSheet(workbook, [], headerErrors);

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

      toast.error(`Header validation failed. ${headerErrors.length} errors found. Check downloaded error report.`);
      return { 
        success: false, 
        error: "Header validation errors",
        errorCount: headerErrors.length,
        hasErrors: true
      };
    }

    // 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 validationErrors: ImportError[] = [];
    const lastRowNumber = worksheet.lastRow?.number || 1;
    const skippedFieldsLog: { tagNo: string, fields: string[] }[] = []; // 건너뛴 필드 로그

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

      const rowObj: Record<string, any> = {};
      const skippedFields: string[] = []; // 현재 행에서 건너뛴 필드들
      let hasErrors = false;
      
      // Get the TAG_NO first to identify existing data
      const tagNoColIndex = keyToIndexMap.get("TAG_NO");
      const tagNo = tagNoColIndex ? String(rowValues[tagNoColIndex] ?? "").trim() : "";
      const existingRowData = existingDataMap.get(tagNo);

      // Validate TAG_NO first
      if (!tagNo) {
        validationErrors.push({
          tagNo: `Row-${rowNum}`,
          rowIndex: rowNum,
          columnKey: "TAG_NO",
          columnLabel: "TAG NO",
          errorType: "MISSING_TAG_NO",
          errorMessage: "TAG_NO is empty or missing",
          currentValue: tagNo,
        });
        hasErrors = true;
      } else if (!existingTagNumbers.has(tagNo)) {
        validationErrors.push({
          tagNo: tagNo,
          rowIndex: rowNum,
          columnKey: "TAG_NO",
          columnLabel: "TAG NO",
          errorType: "TAG_NOT_FOUND",
          errorMessage: "TAG_NO not found in current data",
          currentValue: tagNo,
        });
        hasErrors = true;
      }

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

        // Check if this is a SHI-only field (skip processing but preserve existing value)
        if (col.shi === true) {
          if (existingRowData && existingRowData[col.key] !== undefined) {
            rowObj[col.key] = existingRowData[col.key];
          } else {
            // If no existing data, use appropriate default
            switch (col.type) {
              case "NUMBER":
                rowObj[col.key] = null;
                break;
              case "STRING":
              case "LIST":
              default:
                rowObj[col.key] = "";
                break;
            }
          }
          
          // Log skipped field
          skippedFields.push(`${col.label} (SHI-only field)`);
          return; // Skip processing Excel value for this column
        }

        // Process Excel value for editable fields
        const cellValue = rowValues[colIndex] ?? "";
        let stringVal = String(cellValue).trim();

        // Type-specific validation
        switch (col.type) {
          case "STRING":
            rowObj[col.key] = stringVal;
            break;

          case "NUMBER":
            if (stringVal) {
              const num = parseFloat(stringVal);
              if (isNaN(num)) {
                validationErrors.push({
                  tagNo: tagNo || `Row-${rowNum}`,
                  rowIndex: rowNum,
                  columnKey: col.key,
                  columnLabel: col.label,
                  errorType: "TYPE_MISMATCH",
                  errorMessage: "Value is not a valid number",
                  currentValue: stringVal,
                  expectedFormat: "Number",
                });
                hasErrors = true;
              } else {
                rowObj[col.key] = num;
              }
            } else {
              rowObj[col.key] = null;
            }
            break;

          case "LIST":
            if (stringVal && col.options && !col.options.includes(stringVal)) {
              validationErrors.push({
                tagNo: tagNo || `Row-${rowNum}`,
                rowIndex: rowNum,
                columnKey: col.key,
                columnLabel: col.label,
                errorType: "INVALID_OPTION",
                errorMessage: "Value is not in the allowed options list",
                currentValue: stringVal,
                expectedFormat: col.options.join(", "),
              });
              hasErrors = true;
            }
            rowObj[col.key] = stringVal;
            break;

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

      // Log skipped fields for this TAG
      if (skippedFields.length > 0) {
        skippedFieldsLog.push({
          tagNo: tagNo,
          fields: skippedFields
        });
      }

      // Add to valid data only if no errors
      if (!hasErrors) {
        importedData.push(rowObj);
      }
    }

    // Show summary of skipped fields
    if (skippedFieldsLog.length > 0) {
      const totalSkippedFields = skippedFieldsLog.reduce((sum, log) => sum + log.fields.length, 0);
      console.log("Skipped fields summary:", skippedFieldsLog);
      toast.info(
        `${totalSkippedFields} SHI-only fields were skipped across ${skippedFieldsLog.length} rows. Check console for details.`
      );
    }

    // If there are validation errors, create error report and exit
    if (validationErrors.length > 0) {
      createImportErrorSheet(workbook, validationErrors);

      const outBuffer = await workbook.xlsx.writeBuffer();
      saveAs(new Blob([outBuffer]), `import-error-report_${Date.now()}.xlsx`);
      
      toast.error(
        `Data validation failed. ${validationErrors.length} errors found across ${new Set(validationErrors.map(e => e.tagNo)).size} TAG(s). Check downloaded error report.`
      );
      
      return { 
        success: false, 
        error: "Data validation errors",
        errorCount: validationErrors.length,
        hasErrors: true,
        skippedFields: skippedFieldsLog
      };
    }

    // 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`,
              errorCount: errorCount,
              hasErrors: errorCount > 0,
              skippedFields: skippedFieldsLog
            };
          } else {
            return { 
              success: false, 
              error: "All updates failed", 
              message: errors.join("\n"),
              errorCount: errorCount,
              hasErrors: true,
              skippedFields: skippedFieldsLog
            };
          }
        }
        
        // All rows were updated successfully
        if (onDataUpdate) {
          onDataUpdate(() => mergedData);
        }
        
        const successMessage = skippedFieldsLog.length > 0 
          ? `Successfully updated ${successCount} rows (SHI-only fields were preserved)`
          : `Successfully updated ${successCount} rows`;
        
        toast.success(successMessage);
        return { 
          success: true, 
          importedCount: successCount, 
          message: "All data imported and saved to database",
          errorCount: 0,
          hasErrors: false,
          skippedFields: skippedFieldsLog
        };
      } catch (saveError) {
        console.error("Failed to save imported data:", saveError);
        toast.error("Failed to save imported data to database");
        return { 
          success: false, 
          error: saveError,
          errorCount: 1,
          hasErrors: true,
          skippedFields: skippedFieldsLog
        };
      }
    } else {
      // Fall back to just updating local state if DB parameters aren't provided
      if (onDataUpdate) {
        onDataUpdate(() => mergedData);
      }
      
      const successMessage = skippedFieldsLog.length > 0 
        ? `Imported ${importedData.length} rows successfully (SHI-only fields preserved)`
        : `Imported ${importedData.length} rows successfully`;
      
      toast.success(`${successMessage} (local only)`);
      return { 
        success: true, 
        importedCount: importedData.length,
        errorCount: 0,
        hasErrors: false,
        skippedFields: skippedFieldsLog
      };
    }
    
  } catch (err) {
    console.error("Excel import error:", err);
    toast.error("Excel import failed.");
    return { 
      success: false, 
      error: err,
      errorCount: 1,
      hasErrors: true
    };
  } finally {
    if (onPendingChange) onPendingChange(false);
  }
}