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

// 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[] }[]; // 건너뛴 필드 정보
}

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

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

      let errorMessage = "";
      let warningMessage = "";
      const rowObj: Record<string, any> = {};
      const skippedFields: string[] = []; // 현재 행에서 건너뛴 필드들
      
      // 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);

      // 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":
            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;
        }
      });

      // Log skipped fields for this TAG
      if (skippedFields.length > 0) {
        skippedFieldsLog.push({
          tagNo: tagNo,
          fields: skippedFields
        });
        warningMessage += `Skipped ${skippedFields.length} SHI-only fields. `;
      }

      // 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 {
        // Add warning message to Excel if there are skipped fields
        if (warningMessage) {
          row.getCell(lastColIndex).value = `WARNING: ${warningMessage.trim()}`;
        }
        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, 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",
        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`,
              skippedFields: skippedFieldsLog
            };
          } else {
            return { 
              success: false, 
              error: "All updates failed", 
              message: errors.join("\n"),
              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",
          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,
          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,
        skippedFields: skippedFieldsLog
      };
    }
    
  } catch (err) {
    console.error("Excel import error:", err);
    toast.error("Excel import failed.");
    return { success: false, error: err };
  } finally {
    if (onPendingChange) onPendingChange(false);
  }
}