import ExcelJS from "exceljs";
import { saveAs } from "file-saver";
import { toast } from "sonner";
import { DataTableColumnJSON } from "./form-data-table-columns";
import { updateFormDataBatchInDB } from "@/lib/forms-plant/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;
}

// Updated options interface with editableFieldsMap
export interface ImportExcelOptions {
  file: File;
  tableData: GenericData[];
  columnsJSON: DataTableColumnJSON[];
  formCode?: string;
  contractItemId?: number;
  editableFieldsMap?: Map<string, string[]>; // 새로 추가
  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;
  notFoundTags?: string[];
}

export interface ExportExcelOptions {
  tableData: GenericData[];
  columnsJSON: DataTableColumnJSON[];
  formCode: string;
  editableFieldsMap?: Map<string, string[]>; // 새로 추가
  onPendingChange?: (isPending: boolean) => void;
}

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

/**
 * Check if a field is editable for a specific TAG_NO
 */
function isFieldEditable(
  column: DataTableColumnJSON,
  tagNo: string,
  editableFieldsMap: Map<string, string[]>
): boolean {
  // SHI-only fields (shi === "OUT" or shi === null) are never editable
  if (column.shi === "OUT" || column.shi === null) return false;

  // System fields are never editable
  if (column.key === "TAG_NO" || column.key === "TAG_DESC" || column.key === "status") return false;

  // If no editableFieldsMap provided, assume all non-SHI fields are editable
  if (!editableFieldsMap || editableFieldsMap.size === 0) return true;

  // If TAG_NO not in map, no fields are editable
  if (!editableFieldsMap.has(tagNo)) return false;

  // Check if this field is in the editable fields list for this TAG_NO
  const editableFields = editableFieldsMap.get(tagNo) || [];
  return editableFields.includes(column.key);
}

/**
 * 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;
          case "READ_ONLY_FIELD":
            bgColor = "FFF0F0F0"; // Light gray
            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,
  editableFieldsMap = new Map(), // 새로 추가
  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[];
      // 실제 값이 있는지 확인 (빈 문자열이 아닌 실제 내용)
      const hasAnyValue = rowValues && rowValues.slice(1).some(val =>
        val !== undefined &&
        val !== null &&
        String(val).trim() !== ""
      );

      if (!hasAnyValue) {
        console.log(`Row ${rowNum} is empty, skipping...`);
        continue; // 완전히 빈 행은 건너뛰기
      }

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

      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 field is editable for this TAG_NO
        const fieldEditable = isFieldEditable(col, tagNo, editableFieldsMap);

        if (!fieldEditable) {
          // If field is not editable, preserve existing value
          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;
            }
          }

          // Determine skip reason
          let skipReason = "";
          if (col.shi === "OUT" || col.shi === null) {
            skipReason = "SHI-only field";
          } else if (col.key === "TAG_NO" || col.key === "TAG_DESC" || col.key === "status") {
            skipReason = "System field";
          } else {
            skipReason = "Not editable for this TAG";
          }

          // Log skipped field
          skippedFields.push(`${col.label} (${skipReason})`);

          // Check if Excel contains a value for a read-only field and warn
          const cellValue = rowValues[colIndex] ?? "";
          const stringVal = String(cellValue).trim();
          if (stringVal && existingRowData && String(existingRowData[col.key] || "").trim() !== stringVal) {
            validationErrors.push({
              tagNo: tagNo || `Row-${rowNum}`,
              rowIndex: rowNum,
              columnKey: col.key,
              columnLabel: col.label,
              errorType: "READ_ONLY_FIELD",
              errorMessage: `Attempting to modify read-only field. ${skipReason}.`,
              currentValue: stringVal,
              expectedFormat: `Field is read-only. Current value: ${existingRowData[col.key] || "empty"}`,
            });
            hasErrors = true;
          }

          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} read-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
    // importExcelData 함수에서 DB 저장 부분
    if (formCode && contractItemId) {
      try {
        // 배치 업데이트 함수 호출
        const result = await updateFormDataBatchInDB(
          formCode,
          contractItemId,
          importedData // 모든 imported rows를 한번에 전달
        );

        if (result.success) {
          // 로컬 상태 업데이트
          if (onDataUpdate) {
            onDataUpdate(() => mergedData);
          }

          // 성공 메시지 구성
          const { updatedCount, notFoundTags } = result.data || {};

          let message = `Successfully updated ${updatedCount || importedData.length} rows`;

          // 건너뛴 필드가 있는 경우
          if (skippedFieldsLog.length > 0) {
            const totalSkippedFields = skippedFieldsLog.reduce((sum, log) => sum + log.fields.length, 0);
            message += ` (${totalSkippedFields} read-only fields preserved)`;
          }

          // 찾을 수 없는 TAG가 있는 경우
          if (notFoundTags && notFoundTags.length > 0) {
            console.warn("Tags not found in database:", notFoundTags);
            message += `. Warning: ${notFoundTags.length} tags not found in database`;
          }

          toast.success(message);

          return {
            success: true,
            importedCount: updatedCount || importedData.length,
            message: message,
            errorCount: 0,
            hasErrors: false,
            skippedFields: skippedFieldsLog,
            notFoundTags: notFoundTags
          };

        } else {
          // 배치 업데이트 실패
          console.error("Batch update failed:", result.message);

          // 부분 성공인 경우
          if (result.data?.updatedCount > 0) {
            // 부분적으로라도 업데이트된 경우 로컬 상태 업데이트
            if (onDataUpdate) {
              onDataUpdate(() => mergedData);
            }

            toast.warning(
              `Partially updated: ${result.data.updatedCount} of ${importedData.length} rows updated. ` +
              `${result.data.failedCount || 0} failed.`
            );

            return {
              success: true, // 부분 성공도 success로 처리
              importedCount: result.data.updatedCount,
              message: result.message,
              errorCount: result.data.failedCount || 0,
              hasErrors: true,
              skippedFields: skippedFieldsLog
            };

          } else {
            // 완전 실패
            toast.error(result.message || "Failed to update data to database");

            return {
              success: false,
              error: result.message,
              errorCount: importedData.length,
              hasErrors: true,
              skippedFields: skippedFieldsLog
            };
          }
        }

      } catch (saveError) {
        // 예외 발생 처리
        console.error("Failed to save imported data:", saveError);

        const errorMessage = saveError instanceof Error
          ? saveError.message
          : "Unknown error occurred";

        toast.error(`Database update failed: ${errorMessage}`);

        return {
          success: false,
          error: saveError,
          message: errorMessage,
          errorCount: importedData.length,
          hasErrors: true,
          skippedFields: skippedFieldsLog
        };
      }

    } else {
      // formCode나 contractItemId가 없는 경우 - 로컬 업데이트만
      if (onDataUpdate) {
        onDataUpdate(() => mergedData);
      }

      const successMessage = skippedFieldsLog.length > 0
        ? `Imported ${importedData.length} rows successfully (read-only fields preserved)`
        : `Imported ${importedData.length} rows successfully`;

      toast.success(`${successMessage} (local only - no database connection)`);

      return {
        success: true,
        importedCount: importedData.length,
        message: "Data imported locally only",
        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);
  }
}