// lib/excelUtils.ts
import ExcelJS from "exceljs";
import { saveAs } from "file-saver";
import { toast } from "sonner";

// Define the column type enum
export type ColumnType = "STRING" | "NUMBER" | "LIST" | string;

// Define the column structure
export interface DataTableColumnJSON {
  key: string;
  label: string;
  type: ColumnType;
  options?: string[];
  shi?: boolean; // SHI-only field indicator
  required?: boolean; // Required field indicator
  // Add any other properties that might be in columnsJSON
}

// Define a generic data interface
export interface GenericData {
  [key: string]: any;
  TAG_NO?: string; // Since TAG_NO seems important in the code
}

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

// Define the options interface for the export function
export interface ExportExcelOptions {
  tableData: GenericData[];
  columnsJSON: DataTableColumnJSON[];
  formCode: string;
  onPendingChange?: (isPending: boolean) => void;
  validateData?: boolean; // Option to enable/disable data validation
}

// Define the return type
export interface ExportExcelResult {
  success: boolean;
  error?: any;
  errorCount?: number;
  hasErrors?: boolean;
}

/**
 * Validate data and collect errors
 */
function validateTableData(
  tableData: GenericData[],
  columnsJSON: DataTableColumnJSON[]
): DataError[] {
  const errors: DataError[] = [];
  const tagNoSet = new Set<string>();

  tableData.forEach((rowData, index) => {
    const rowIndex = index + 2; // Excel row number (header is row 1)
    const tagNo = rowData.TAG_NO || `Row-${rowIndex}`;

    // Check for duplicate TAG_NO
    if (rowData.TAG_NO) {
      if (tagNoSet.has(rowData.TAG_NO)) {
        errors.push({
          tagNo,
          rowIndex,
          columnKey: "TAG_NO",
          columnLabel: "TAG NO",
          errorType: "DUPLICATE",
          errorMessage: "Duplicate TAG_NO found",
          currentValue: rowData.TAG_NO,
        });
      } else {
        tagNoSet.add(rowData.TAG_NO);
      }
    }

    // Validate each column
    columnsJSON.forEach((column) => {
      const value = rowData[column.key];
      const isEmpty = value === undefined || value === null || value === "";

      // Required field validation
      if (column.required && isEmpty) {
        errors.push({
          tagNo,
          rowIndex,
          columnKey: column.key,
          columnLabel: column.label,
          errorType: "REQUIRED",
          errorMessage: "Required field is empty",
          currentValue: value,
        });
      }

      if (!isEmpty) {
        // Type validation
        switch (column.type) {
          case "NUMBER":
            if (isNaN(Number(value))) {
              errors.push({
                tagNo,
                rowIndex,
                columnKey: column.key,
                columnLabel: column.label,
                errorType: "TYPE_MISMATCH",
                errorMessage: "Value is not a valid number",
                currentValue: value,
                expectedFormat: "Number",
              });
            }
            break;

          case "LIST":
            if (column.options && !column.options.includes(String(value))) {
              errors.push({
                tagNo,
                rowIndex,
                columnKey: column.key,
                columnLabel: column.label,
                errorType: "INVALID_OPTION",
                errorMessage: "Value is not in the allowed options list",
                currentValue: value,
                expectedFormat: column.options.join(", "),
              });
            }
            break;

          case "STRING":
            // Additional string validations can be added here
            if (typeof value !== "string" && typeof value !== "number") {
              errors.push({
                tagNo,
                rowIndex,
                columnKey: column.key,
                columnLabel: column.label,
                errorType: "TYPE_MISMATCH",
                errorMessage: "Value is not a valid string",
                currentValue: value,
                expectedFormat: "String",
              });
            }
            break;
        }
      }
    });
  });

  return errors;
}

/**
 * Create error sheet with validation results
 */
function createErrorSheet(workbook: ExcelJS.Workbook, errors: DataError[]) {
  const errorSheet = workbook.addWorksheet("Errors");

  // Error sheet headers
  const errorHeaders = [
    "TAG NO",
    "Row Number",
    "Column",
    "Error Type",
    "Error Message",
    "Current Value",
    "Expected Format",
  ];

  errorSheet.addRow(errorHeaders);

  // Style error sheet header
  const errorHeaderRow = errorSheet.getRow(1);
  errorHeaderRow.font = { bold: true, color: { argb: "FFFFFFFF" } };
  errorHeaderRow.alignment = { horizontal: "center" };
  
  errorHeaderRow.eachCell((cell) => {
    cell.fill = {
      type: "pattern",
      pattern: "solid",
      fgColor: { argb: "FFDC143C" }, // Crimson background
    };
  });

  // 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, colNumber) => {
      let bgColor = "FFFFFFFF"; // Default white
      
      switch (error.errorType) {
        case "REQUIRED":
          bgColor = "FFFFCCCC"; // Light red
          break;
        case "TYPE_MISMATCH":
          bgColor = "FFFFEECC"; // Light orange
          break;
        case "INVALID_OPTION":
          bgColor = "FFFFFFE0"; // Light yellow
          break;
        case "DUPLICATE":
          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);
  });

  // Add summary at the top
  errorSheet.insertRow(1, [`Total Errors Found: ${errors.length}`]);
  const summaryRow = errorSheet.getRow(1);
  summaryRow.font = { bold: true, size: 14 };
  if (errors.length > 0) {
    summaryRow.getCell(1).fill = {
      type: "pattern",
      pattern: "solid",
      fgColor: { argb: "FFFFC0C0" }, // Light red background
    };
  }

  // Adjust header row number
  const newHeaderRow = errorSheet.getRow(2);
  newHeaderRow.font = { bold: true, color: { argb: "FFFFFFFF" } };
  newHeaderRow.alignment = { horizontal: "center" };
  
  newHeaderRow.eachCell((cell) => {
    cell.fill = {
      type: "pattern",
      pattern: "solid",
      fgColor: { argb: "FFDC143C" },
    };
  });

  return errorSheet;
}

/**
 * Export table data to Excel with data validation for select columns
 * @param options Configuration options for Excel export
 * @returns Promise with success/error information
 */
export async function exportExcelData({
  tableData,
  columnsJSON,
  formCode,
  onPendingChange,
  validateData = true
}: ExportExcelOptions): Promise<ExportExcelResult> {
  try {
    if (onPendingChange) onPendingChange(true);

    // Validate data first if validation is enabled
    const errors = validateData ? validateTableData(tableData, columnsJSON) : [];

    // Create a new workbook
    const workbook = new ExcelJS.Workbook();

    // 데이터 시트 생성
    const worksheet = workbook.addWorksheet("Data");

    // 유효성 검사용 숨김 시트 생성
    const validationSheet = workbook.addWorksheet("ValidationData");
    validationSheet.state = "hidden"; // 시트 숨김 처리

    // 1. 유효성 검사 시트에 select 옵션 추가
    const selectColumns = columnsJSON.filter(
      (col) => col.type === "LIST" && col.options && col.options.length > 0
    );

    // 유효성 검사 범위 저장 맵 (컬럼 키 -> 유효성 검사 범위)
    const validationRanges = new Map<string, string>();

    selectColumns.forEach((col, idx) => {
      const colIndex = idx + 1;
      const colLetter = validationSheet.getColumn(colIndex).letter;

      // 헤더 추가 (컬럼 레이블)
      validationSheet.getCell(`${colLetter}1`).value = col.label;

      // 옵션 추가
      if (col.options) {
        col.options.forEach((option, optIdx) => {
          validationSheet.getCell(`${colLetter}${optIdx + 2}`).value = option;
        });

        // 유효성 검사 범위 저장 (ValidationData!$A$2:$A$4 형식)
        validationRanges.set(
          col.key,
          `ValidationData!${colLetter}$2:${colLetter}${
            col.options.length + 1
          }`
        );
      }
    });

    // 2. 데이터 시트에 헤더 추가
    const headers = columnsJSON.map((col) => {
      let headerLabel = col.label;
      if (col.required) {
        headerLabel += " *"; // Required fields marked with asterisk
      }
      return headerLabel;
    });
    worksheet.addRow(headers);

    // 헤더 스타일 적용
    const headerRow = worksheet.getRow(1);
    headerRow.font = { bold: true };
    headerRow.alignment = { horizontal: "center" };
    
    // 각 헤더 셀에 스타일 적용
    headerRow.eachCell((cell, colNumber) => {
      const columnIndex = colNumber - 1;
      const column = columnsJSON[columnIndex];
      
      if (column?.shi === true) {
        // SHI-only 필드는 더 진한 음영으로 표시
        cell.fill = {
          type: "pattern",
          pattern: "solid",
          fgColor: { argb: "FFFF9999" }, // 연한 빨간색 배경
        };
        cell.font = { bold: true, color: { argb: "FF800000" } }; // 진한 빨간색 글자
      } else if (column?.required) {
        // Required 필드는 파란색 배경
        cell.fill = {
          type: "pattern",
          pattern: "solid",
          fgColor: { argb: "FFCCE5FF" }, // 연한 파란색 배경
        };
        cell.font = { bold: true, color: { argb: "FF000080" } }; // 진한 파란색 글자
      } else {
        // 일반 필드는 기존 스타일
        cell.fill = {
          type: "pattern",
          pattern: "solid",
          fgColor: { argb: "FFCCCCCC" }, // 연한 회색 배경
        };
      }
    });

    // 3. 데이터 행 추가
    tableData.forEach((rowData, rowIndex) => {
      const rowValues = columnsJSON.map((col) => {
        const value = rowData[col.key];
        return value !== undefined && value !== null ? value : "";
      });
      const dataRow = worksheet.addRow(rowValues);
      
      // Get errors for this row
      const rowErrors = errors.filter(err => err.rowIndex === rowIndex + 2);
      const hasErrors = rowErrors.length > 0;
      
      // SHI-only 컬럼의 데이터 셀에도 음영 적용
      dataRow.eachCell((cell, colNumber) => {
        const columnIndex = colNumber - 1;
        const column = columnsJSON[columnIndex];
        
        // Check if this cell has errors
        const cellHasError = rowErrors.some(err => err.columnKey === column.key);
        
        if (column?.shi === true) {
          // SHI-only 필드의 데이터 셀에 연한 음영 적용
          cell.fill = {
            type: "pattern",
            pattern: "solid",
            fgColor: { argb: cellHasError ? "FFFF6666" : "FFFFCCCC" }, // 에러가 있으면 더 진한 빨간색
          };
          cell.font = { italic: true, color: { argb: "FF666666" } };
        } else if (cellHasError) {
          // 에러가 있는 셀은 연한 빨간색 배경
          cell.fill = {
            type: "pattern",
            pattern: "solid",
            fgColor: { argb: "FFFFDDDD" },
          };
          cell.font = { color: { argb: "FFCC0000" } };
        }
      });
    });

    // 4. 데이터 유효성 검사 적용
    const maxRows = 5000; // 데이터 유효성 검사를 적용할 최대 행 수

    columnsJSON.forEach((col, idx) => {
      const colLetter = worksheet.getColumn(idx + 1).letter;
      
      // SHI-only 필드가 아닌 LIST 타입에만 유효성 검사 적용
      if (col.type === "LIST" && validationRanges.has(col.key) && col.shi !== true) {
        const validationRange = validationRanges.get(col.key)!;

        // 유효성 검사 정의
        const validation = {
          type: "list" as const,
          allowBlank: !col.required,
          formulae: [validationRange],
          showErrorMessage: true,
          errorStyle: "warning" as const,
          errorTitle: "유효하지 않은 값",
          error: "목록에서 값을 선택해주세요.",
        };

        // 모든 데이터 행에 유효성 검사 적용 (최대 maxRows까지)
        for (
          let rowIdx = 2;
          rowIdx <= Math.min(tableData.length + 1, maxRows);
          rowIdx++
        ) {
          worksheet.getCell(`${colLetter}${rowIdx}`).dataValidation =
            validation;
        }

        // 빈 행에도 적용 (최대 maxRows까지)
        if (tableData.length + 1 < maxRows) {
          for (
            let rowIdx = tableData.length + 2;
            rowIdx <= maxRows;
            rowIdx++
          ) {
            worksheet.getCell(`${colLetter}${rowIdx}`).dataValidation =
              validation;
          }
        }
      }
      
      // SHI-only 필드의 빈 행들에도 음영 처리 적용
      if (col.shi === true) {
        for (let rowIdx = tableData.length + 2; rowIdx <= maxRows; rowIdx++) {
          const cell = worksheet.getCell(`${colLetter}${rowIdx}`);
          cell.fill = {
            type: "pattern",
            pattern: "solid",
            fgColor: { argb: "FFFFCCCC" },
          };
          cell.font = { italic: true, color: { argb: "FF666666" } };
        }
      }
    });

    // 5. 컬럼 너비 자동 조정
    columnsJSON.forEach((col, idx) => {
      const column = worksheet.getColumn(idx + 1);

      // 최적 너비 계산
      let maxLength = col.label.length;
      tableData.forEach((row) => {
        const value = row[col.key];
        if (value !== undefined && value !== null) {
          const valueLength = String(value).length;
          if (valueLength > maxLength) {
            maxLength = valueLength;
          }
        }
      });

      // 너비 설정 (최소 10, 최대 50)
      column.width = Math.min(Math.max(maxLength + 2, 10), 50);
    });

    // 6. 에러 시트 생성 (에러가 있을 경우에만)
    if (errors.length > 0) {
      createErrorSheet(workbook, errors);
    }

    // 7. 범례 추가 (별도 시트)
    const legendSheet = workbook.addWorksheet("Legend");
    legendSheet.addRow(["Excel Template Legend"]);
    legendSheet.addRow([]);
    legendSheet.addRow(["Symbol", "Description"]);
    legendSheet.addRow(["Red background header", "SHI-only fields that cannot be edited"]);
    legendSheet.addRow(["Blue background header", "Required fields (marked with *)"]);
    legendSheet.addRow(["Gray background header", "Regular optional fields"]);
    legendSheet.addRow(["Light red background cells", "Cells with validation errors"]);
    legendSheet.addRow(["Light red data cells", "Data in SHI-only fields (read-only)"]);
    
    if (errors.length > 0) {
      legendSheet.addRow([]);
      legendSheet.addRow([`Note: ${errors.length} validation errors found in the 'Errors' sheet`]);
      const errorNoteRow = legendSheet.getRow(legendSheet.rowCount);
      errorNoteRow.font = { bold: true, color: { argb: "FFCC0000" } };
    }
    
    // 범례 스타일 적용
    const legendHeaderRow = legendSheet.getRow(1);
    legendHeaderRow.font = { bold: true, size: 14 };
    
    const legendTableHeader = legendSheet.getRow(3);
    legendTableHeader.font = { bold: true };
    legendTableHeader.eachCell((cell) => {
      cell.fill = {
        type: "pattern",
        pattern: "solid",
        fgColor: { argb: "FFCCCCCC" },
      };
    });

    // 8. 파일 다운로드
    const buffer = await workbook.xlsx.writeBuffer();
    const fileName = errors.length > 0 
      ? `${formCode}_data_with_errors_${new Date().toISOString().slice(0, 10)}.xlsx`
      : `${formCode}_data_${new Date().toISOString().slice(0, 10)}.xlsx`;
    
    saveAs(new Blob([buffer]), fileName);

    const message = errors.length > 0 
      ? `Excel 내보내기 완료! (${errors.length}개의 검증 오류 발견)`
      : "Excel 내보내기 완료!";
    
    toast.success(message);
    
    return { 
      success: true, 
      errorCount: errors.length, 
      hasErrors: errors.length > 0 
    };
  } catch (err) {
    console.error("Excel export error:", err);
    toast.error("Excel 내보내기 실패.");
    return { success: false, error: err };
  } finally {
    if (onPendingChange) onPendingChange(false);
  }
}