"use client";

import * as React from "react";
import dynamic from "next/dynamic";
import { Dialog, DialogContent, DialogHeader, DialogTitle, DialogFooter, DialogDescription } from "@/components/ui/dialog";
import { Button } from "@/components/ui/button";
import { Select, SelectContent, SelectItem, SelectTrigger, SelectValue } from "@/components/ui/select";
import { GenericData } from "./export-excel-form";
import * as GC from "@mescius/spread-sheets";
import { toast } from "sonner";
import { updateFormDataInDB } from "@/lib/forms/services";
import { Loader, Save, AlertTriangle } from "lucide-react";
import '@mescius/spread-sheets/styles/gc.spread.sheets.excel2016colorful.css';
import { DataTableColumnJSON, ColumnType } from "./form-data-table-columns";

const SpreadSheets = dynamic(
  () => import("@mescius/spread-sheets-react").then(mod => mod.SpreadSheets),
  {
    ssr: false,
    loading: () => (
      <div className="flex items-center justify-center h-full">
        <Loader className="mr-2 h-4 w-4 animate-spin" />
        Loading SpreadSheets...
      </div>
    )
  }
);

if (typeof window !== 'undefined' && process.env.NEXT_PUBLIC_SPREAD_LICENSE) {
  GC.Spread.Sheets.LicenseKey = process.env.NEXT_PUBLIC_SPREAD_LICENSE;
}

interface TemplateItem {
  TMPL_ID: string;
  NAME: string;
  TMPL_TYPE: string;
  SPR_LST_SETUP: {
    ACT_SHEET: string;
    HIDN_SHEETS: Array<string>;
    CONTENT?: string;
    DATA_SHEETS: Array<{
      SHEET_NAME: string;
      REG_TYPE_ID: string;
      MAP_CELL_ATT: Array<{
        ATT_ID: string;
        IN: string;
      }>;
    }>;
  };
  GRD_LST_SETUP: {
    REG_TYPE_ID: string;
    SPR_ITM_IDS: Array<string>;
    ATTS: Array<{}>;
  };
  SPR_ITM_LST_SETUP: {
    ACT_SHEET: string;
    HIDN_SHEETS: Array<string>;
    CONTENT?: string;
    DATA_SHEETS: Array<{
      SHEET_NAME: string;
      REG_TYPE_ID: string;
      MAP_CELL_ATT: Array<{
        ATT_ID: string;
        IN: string;
      }>;
    }>;
  };
}

interface ValidationError {
  cellAddress: string;
  attId: string;
  value: any;
  expectedType: ColumnType;
  message: string;
}

interface CellMapping {
  attId: string;
  cellAddress: string;
  isEditable: boolean;
  dataRowIndex?: number;
}

interface TemplateViewDialogProps {
  isOpen: boolean;
  onClose: () => void;
  templateData: TemplateItem[] | any;
  selectedRow?: GenericData;
  tableData?: GenericData[];
  formCode: string;
  columnsJSON: DataTableColumnJSON[]
  contractItemId: number;
  editableFieldsMap?: Map<string, string[]>;
  onUpdateSuccess?: (updatedValues: Record<string, any> | GenericData[]) => void;
}

// 🚀 로딩 프로그레스 컴포넌트
interface LoadingProgressProps {
  phase: string;
  progress: number;
  total: number;
  isVisible: boolean;
}

const LoadingProgress: React.FC<LoadingProgressProps> = ({ phase, progress, total, isVisible }) => {
  const percentage = total > 0 ? Math.round((progress / total) * 100) : 0;
  
  if (!isVisible) return null;
  
  return (
    <div className="absolute inset-0 bg-white/90 flex items-center justify-center z-50">
      <div className="bg-white rounded-lg shadow-lg border p-6 min-w-[300px]">
        <div className="flex items-center space-x-3 mb-4">
          <Loader className="h-5 w-5 animate-spin text-blue-600" />
          <span className="font-medium text-gray-900">Loading Template</span>
        </div>
        
        <div className="space-y-2">
          <div className="text-sm text-gray-600">{phase}</div>
          <div className="w-full bg-gray-200 rounded-full h-2">
            <div 
              className="bg-blue-600 h-2 rounded-full transition-all duration-300 ease-out"
              style={{ width: `${percentage}%` }}
            />
          </div>
          <div className="text-xs text-gray-500 text-right">
            {progress.toLocaleString()} / {total.toLocaleString()} ({percentage}%)
          </div>
        </div>
      </div>
    </div>
  );
};

export function TemplateViewDialog({
  isOpen,
  onClose,
  templateData,
  selectedRow,
  tableData = [],
  formCode,
  contractItemId,
  columnsJSON,
  editableFieldsMap = new Map(),
  onUpdateSuccess
}: TemplateViewDialogProps) {
  const [hostStyle, setHostStyle] = React.useState({
    width: '100%',
    height: '100%'
  });

  const [isPending, setIsPending] = React.useState(false);
  const [hasChanges, setHasChanges] = React.useState(false);
  const [currentSpread, setCurrentSpread] = React.useState<any>(null);
  const [cellMappings, setCellMappings] = React.useState<CellMapping[]>([]);
  const [isClient, setIsClient] = React.useState(false);
  const [templateType, setTemplateType] = React.useState<'SPREAD_LIST' | 'SPREAD_ITEM' | 'GRD_LIST' | null>(null);
  const [validationErrors, setValidationErrors] = React.useState<ValidationError[]>([]);
  const [selectedTemplateId, setSelectedTemplateId] = React.useState<string>("");
  const [availableTemplates, setAvailableTemplates] = React.useState<TemplateItem[]>([]);
  
  // 🆕 로딩 상태 추가
  const [loadingProgress, setLoadingProgress] = React.useState<{
    phase: string;
    progress: number;
    total: number;
  } | null>(null);
  const [isInitializing, setIsInitializing] = React.useState(false);

  // 🔄 진행상황 업데이트 함수
  const updateProgress = React.useCallback((phase: string, progress: number, total: number) => {
    setLoadingProgress({ phase, progress, total });
  }, []);

  const determineTemplateType = React.useCallback((template: TemplateItem): 'SPREAD_LIST' | 'SPREAD_ITEM' | 'GRD_LIST' | null => {
    if (template.TMPL_TYPE === "SPREAD_LIST" && (template.SPR_LST_SETUP?.CONTENT || template.SPR_ITM_LST_SETUP?.CONTENT)) {
      return 'SPREAD_LIST';
    }
    if (template.TMPL_TYPE === "SPREAD_ITEM" && (template.SPR_LST_SETUP?.CONTENT || template.SPR_ITM_LST_SETUP?.CONTENT)) {
      return 'SPREAD_ITEM';
    }
    if (template.GRD_LST_SETUP && columnsJSON.length > 0) {
      return 'GRD_LIST';
    }
    return null;
  }, [columnsJSON]);

  const isValidTemplate = React.useCallback((template: TemplateItem): boolean => {
    return determineTemplateType(template) !== null;
  }, [determineTemplateType]);

  React.useEffect(() => {
    setIsClient(true);
  }, []);

  React.useEffect(() => {
    if (!templateData) return;

    let templates: TemplateItem[];
    if (Array.isArray(templateData)) {
      templates = templateData as TemplateItem[];
    } else {
      templates = [templateData as TemplateItem];
    }

    const validTemplates = templates.filter(isValidTemplate);
    setAvailableTemplates(validTemplates);

    if (validTemplates.length > 0 && !selectedTemplateId) {
      const firstTemplate = validTemplates[0];
      const templateTypeToSet = determineTemplateType(firstTemplate);
      setSelectedTemplateId(firstTemplate.TMPL_ID);
      setTemplateType(templateTypeToSet);
    }
  }, [templateData, selectedTemplateId, isValidTemplate, determineTemplateType]);

  const handleTemplateChange = (templateId: string) => {
    const template = availableTemplates.find(t => t.TMPL_ID === templateId);
    if (template) {
      const templateTypeToSet = determineTemplateType(template);
      setSelectedTemplateId(templateId);
      setTemplateType(templateTypeToSet);
      setHasChanges(false);
      setValidationErrors([]);

      if (currentSpread && template) {
        initSpread(currentSpread, template);
      }
    }
  };

  const selectedTemplate = React.useMemo(() => {
    return availableTemplates.find(t => t.TMPL_ID === selectedTemplateId);
  }, [availableTemplates, selectedTemplateId]);

  const editableFields = React.useMemo(() => {
    if (templateType === 'SPREAD_ITEM' && selectedRow?.TAG_NO) {
      if (!editableFieldsMap.has(selectedRow.TAG_NO)) {
        return [];
      }
      return editableFieldsMap.get(selectedRow.TAG_NO) || [];
    }

    if ((templateType === 'SPREAD_LIST' || templateType === 'GRD_LIST') && tableData.length > 0) {
      const firstRowTagNo = tableData[0]?.TAG_NO;
      if (firstRowTagNo && editableFieldsMap.has(firstRowTagNo)) {
        return editableFieldsMap.get(firstRowTagNo) || [];
      }
    }

    return [];
  }, [templateType, selectedRow?.TAG_NO, tableData, editableFieldsMap]);

  const isFieldEditable = React.useCallback((attId: string, rowData?: GenericData) => {
    const columnConfig = columnsJSON.find(col => col.key === attId);
    if (columnConfig?.shi === true) {
      return false;
    }

    if (attId === "TAG_NO" || attId === "TAG_DESC" || attId === "status") {
      return false;
    }

    if (templateType === 'SPREAD_LIST' || templateType === 'GRD_LIST') {
      if (rowData && rowData.shi === true) {
        return false;
      }
      return true;
    }

    return true;
  }, [templateType, columnsJSON]);

  const editableFieldsCount = React.useMemo(() => {
    return cellMappings.filter(m => m.isEditable).length;
  }, [cellMappings]);

  // 🚀 배치 처리 함수들
  const setBatchValues = React.useCallback((
    activeSheet: any, 
    valuesToSet: Array<{row: number, col: number, value: any}>
  ) => {
    console.log(`🚀 Setting ${valuesToSet.length} values in batch`);
    
    const columnGroups = new Map<number, Array<{row: number, value: any}>>();
    
    valuesToSet.forEach(({row, col, value}) => {
      if (!columnGroups.has(col)) {
        columnGroups.set(col, []);
      }
      columnGroups.get(col)!.push({row, value});
    });
    
    columnGroups.forEach((values, col) => {
      values.sort((a, b) => a.row - b.row);
      
      let start = 0;
      while (start < values.length) {
        let end = start;
        while (end + 1 < values.length && values[end + 1].row === values[end].row + 1) {
          end++;
        }
        
        const rangeValues = values.slice(start, end + 1).map(v => v.value);
        const startRow = values[start].row;
        
        try {
          if (rangeValues.length === 1) {
            activeSheet.setValue(startRow, col, rangeValues[0]);
          } else {
            const dataArray = rangeValues.map(v => [v]);
            activeSheet.setArray(startRow, col, dataArray);
          }
        } catch (error) {
          for (let i = start; i <= end; i++) {
            try {
              activeSheet.setValue(values[i].row, col, values[i].value);
            } catch (cellError) {
              console.warn(`⚠️ Individual value setting failed [${values[i].row}, ${col}]:`, cellError);
            }
          }
        }
        
        start = end + 1;
      }
    });
  }, []);

  const setBatchStyles = React.useCallback((
    activeSheet: any, 
    stylesToSet: Array<{row: number, col: number, isEditable: boolean}>
  ) => {
    console.log(`🎨 Setting ${stylesToSet.length} styles in batch`);
    
    const editableStyle = createCellStyle(true);
    const readonlyStyle = createCellStyle(false);
    
    // 🔧 개별 셀별로 스타일과 잠금 상태 설정 (편집 권한 보장)
    stylesToSet.forEach(({row, col, isEditable}) => {
      try {
        const cell = activeSheet.getCell(row, col);
        const style = isEditable ? editableStyle : readonlyStyle;
        
        activeSheet.setStyle(row, col, style);
        cell.locked(!isEditable); // 편집 가능하면 잠금 해제
        
        // 🆕 편집 가능한 셀에 기본 텍스트 에디터 설정
        if (isEditable) {
          const textCellType = new GC.Spread.Sheets.CellTypes.Text();
          activeSheet.setCellType(row, col, textCellType);
        }
      } catch (error) {
        console.warn(`⚠️ Failed to set style for cell [${row}, ${col}]:`, error);
      }
    });
  }, [createCellStyle]);

  const parseCellAddress = (address: string): { row: number, col: number } | null => {
    if (!address || address.trim() === "") return null;

    const match = address.match(/^([A-Z]+)(\d+)$/);
    if (!match) return null;

    const [, colStr, rowStr] = match;

    let col = 0;
    for (let i = 0; i < colStr.length; i++) {
      col = col * 26 + (colStr.charCodeAt(i) - 65 + 1);
    }
    col -= 1;

    const row = parseInt(rowStr) - 1;
    return { row, col };
  };

  const getCellAddress = (row: number, col: number): string => {
    let colStr = '';
    let colNum = col;
    while (colNum >= 0) {
      colStr = String.fromCharCode((colNum % 26) + 65) + colStr;
      colNum = Math.floor(colNum / 26) - 1;
    }
    return colStr + (row + 1);
  };

  const validateCellValue = (value: any, columnType: ColumnType, options?: string[]): string | null => {
    if (value === undefined || value === null || value === "") {
      return null;
    }

    switch (columnType) {
      case "NUMBER":
        if (isNaN(Number(value))) {
          return "Value must be a valid number";
        }
        break;
      case "LIST":
        if (options && !options.includes(String(value))) {
          return `Value must be one of: ${options.join(", ")}`;
        }
        break;
      case "STRING":
        break;
      default:
        break;
    }

    return null;
  };

  const validateAllData = React.useCallback(() => {
    if (!currentSpread || !selectedTemplate) return [];

    const activeSheet = currentSpread.getActiveSheet();
    const errors: ValidationError[] = [];

    cellMappings.forEach(mapping => {
      const columnConfig = columnsJSON.find(col => col.key === mapping.attId);
      if (!columnConfig) return;

      const cellPos = parseCellAddress(mapping.cellAddress);
      if (!cellPos) return;

      const cellValue = activeSheet.getValue(cellPos.row, cellPos.col);
      const errorMessage = validateCellValue(cellValue, columnConfig.type, columnConfig.options);

      if (errorMessage) {
        errors.push({
          cellAddress: mapping.cellAddress,
          attId: mapping.attId,
          value: cellValue,
          expectedType: columnConfig.type,
          message: errorMessage
        });
      }
    });

    setValidationErrors(errors);
    return errors;
  }, [currentSpread, selectedTemplate, cellMappings, columnsJSON]);

  const createCellStyle = React.useCallback((isEditable: boolean) => {
    const style = new GC.Spread.Sheets.Style();
    if (isEditable) {
      style.backColor = "#f0fdf4";
    } else {
      style.backColor = "#f9fafb";
      style.foreColor = "#6b7280";
    }
    return style;
  }, []);

  const setupOptimizedListValidation = React.useCallback((activeSheet: any, cellPos: { row: number, col: number }, options: string[], rowCount: number) => {
    try {
      console.log(`🎯 Setting up dropdown for ${rowCount} rows with options:`, options);

      const safeOptions = options
        .filter(opt => opt !== null && opt !== undefined && opt !== '')
        .map(opt => String(opt).trim())
        .filter(opt => opt.length > 0)
        .filter((opt, index, arr) => arr.indexOf(opt) === index)
        .slice(0, 20);

      if (safeOptions.length === 0) {
        console.warn(`⚠️ No valid options found, skipping`);
        return;
      }

      const optionsString = safeOptions.join(',');

      for (let i = 0; i < rowCount; i++) {
        try {
          const targetRow = cellPos.row + i;
          
          // 🔧 각 셀마다 새로운 ComboBox 인스턴스 생성
          const comboBoxCellType = new GC.Spread.Sheets.CellTypes.ComboBox();
          comboBoxCellType.items(safeOptions);
          comboBoxCellType.editorValueType(GC.Spread.Sheets.CellTypes.EditorValueType.text);

          // 🔧 DataValidation 설정
          const cellValidator = GC.Spread.Sheets.DataValidation.createListValidator(optionsString);
          cellValidator.showInputMessage(false);
          cellValidator.showErrorMessage(false);

          // ComboBox와 Validator 적용
          activeSheet.setCellType(targetRow, cellPos.col, comboBoxCellType);
          activeSheet.setDataValidator(targetRow, cellPos.col, cellValidator);
          
          // 🚀 중요: 셀 잠금 해제 및 편집 가능 설정
          const cell = activeSheet.getCell(targetRow, cellPos.col);
          cell.locked(false);

          console.log(`✅ Dropdown applied to [${targetRow}, ${cellPos.col}] with ${safeOptions.length} options`);

        } catch (cellError) {
          console.warn(`⚠️ Failed to apply dropdown to row ${cellPos.row + i}:`, cellError);
        }
      }

      console.log(`✅ Dropdown setup completed for ${rowCount} cells`);

    } catch (error) {
      console.error('❌ Dropdown setup failed:', error);
    }
  }, []);

  const getSafeActiveSheet = React.useCallback((spread: any, functionName: string = 'unknown') => {
    if (!spread) return null;
    
    try {
      let activeSheet = spread.getActiveSheet();
      if (!activeSheet) {
        const sheetCount = spread.getSheetCount();
        if (sheetCount > 0) {
          activeSheet = spread.getSheet(0);
          if (activeSheet) {
            spread.setActiveSheetIndex(0);
          }
        }
      }
      return activeSheet;
    } catch (error) {
      console.error(`❌ Error getting activeSheet in ${functionName}:`, error);
      return null;
    }
  }, []);

  const ensureRowCapacity = React.useCallback((activeSheet: any, requiredRowCount: number) => {
    try {
      if (!activeSheet) return false;
      
      const currentRowCount = activeSheet.getRowCount();
      if (requiredRowCount > currentRowCount) {
        const newRowCount = requiredRowCount + 10;
        activeSheet.setRowCount(newRowCount);
      }
      return true;
    } catch (error) {
      console.error('❌ Error in ensureRowCapacity:', error);
      return false;
    }
  }, []);

  const ensureColumnCapacity = React.useCallback((activeSheet: any, requiredColumnCount: number) => {
    try {
      if (!activeSheet) return false;
      
      const currentColumnCount = activeSheet.getColumnCount();
      if (requiredColumnCount > currentColumnCount) {
        const newColumnCount = requiredColumnCount + 10;
        activeSheet.setColumnCount(newColumnCount);
      }
      return true;
    } catch (error) {
      console.error('❌ Error in ensureColumnCapacity:', error);
      return false;
    }
  }, []);

  const setOptimalColumnWidths = React.useCallback((activeSheet: any, columns: any[], startCol: number, tableData: any[]) => {
    columns.forEach((column, colIndex) => {
      const targetCol = startCol + colIndex;
      const optimalWidth = column.type === 'NUMBER' ? 100 : column.type === 'STRING' ? 150 : 120;
      activeSheet.setColumnWidth(targetCol, optimalWidth);
    });
  }, []);

  // 🚀 최적화된 GRD_LIST 생성
  const createGrdListTableOptimized = React.useCallback((activeSheet: any, template: TemplateItem) => {
    console.log('🚀 Creating optimized GRD_LIST table');

    const visibleColumns = columnsJSON
      .filter(col => col.hidden !== true)
      .sort((a, b) => (a.seq ?? 999999) - (b.seq ?? 999999));

    if (visibleColumns.length === 0) return [];

    const startCol = 1;
    const dataStartRow = 1;
    const mappings: CellMapping[] = [];

    ensureColumnCapacity(activeSheet, startCol + visibleColumns.length);
    ensureRowCapacity(activeSheet, dataStartRow + tableData.length);

    // 헤더 생성
    const headerStyle = new GC.Spread.Sheets.Style();
    headerStyle.backColor = "#3b82f6";
    headerStyle.foreColor = "#ffffff";
    headerStyle.font = "bold 12px Arial";
    headerStyle.hAlign = GC.Spread.Sheets.HorizontalAlign.center;

    visibleColumns.forEach((column, colIndex) => {
      const targetCol = startCol + colIndex;
      const cell = activeSheet.getCell(0, targetCol);
      cell.value(column.label);
      cell.locked(true);
      activeSheet.setStyle(0, targetCol, headerStyle);
    });

    // 🚀 데이터 배치 처리 준비
    const allValues: Array<{row: number, col: number, value: any}> = [];
    const allStyles: Array<{row: number, col: number, isEditable: boolean}> = [];

    // 🔧 편집 가능한 셀 정보 수집 (드롭다운용)
    const dropdownConfigs: Array<{
      startRow: number;
      col: number;
      rowCount: number;
      options: string[];
      editableRows: number[]; // 편집 가능한 행만 추적
    }> = [];

    visibleColumns.forEach((column, colIndex) => {
      const targetCol = startCol + colIndex;
      
      // 드롭다운 설정을 위한 편집 가능한 행 찾기
      if (column.type === "LIST" && column.options) {
        const editableRows: number[] = [];
        tableData.forEach((rowData, rowIndex) => {
          if (isFieldEditable(column.key, rowData)) {
            editableRows.push(dataStartRow + rowIndex);
          }
        });
        
        if (editableRows.length > 0) {
          dropdownConfigs.push({
            startRow: dataStartRow,
            col: targetCol,
            rowCount: tableData.length,
            options: column.options,
            editableRows: editableRows
          });
        }
      }
      
      tableData.forEach((rowData, rowIndex) => {
        const targetRow = dataStartRow + rowIndex;
        const cellEditable = isFieldEditable(column.key, rowData);
        const value = rowData[column.key];

        mappings.push({
          attId: column.key,
          cellAddress: getCellAddress(targetRow, targetCol),
          isEditable: cellEditable,
          dataRowIndex: rowIndex
        });

        allValues.push({
          row: targetRow,
          col: targetCol,
          value: value ?? null
        });

        allStyles.push({
          row: targetRow,
          col: targetCol,
          isEditable: cellEditable
        });
      });
    });

    // 🚀 배치로 값과 스타일 설정
    setBatchValues(activeSheet, allValues);
    setBatchStyles(activeSheet, allStyles);

    // 🎯 개선된 드롭다운 설정 (편집 가능한 셀에만)
    dropdownConfigs.forEach(({ col, options, editableRows }) => {
      try {
        console.log(`🎯 Setting dropdown for column ${col}, editable rows: ${editableRows.length}`);
        
        const safeOptions = options
          .filter(opt => opt !== null && opt !== undefined && opt !== '')
          .map(opt => String(opt).trim())
          .filter(opt => opt.length > 0)
          .slice(0, 20);

        if (safeOptions.length === 0) return;

        // 편집 가능한 행에만 드롭다운 적용
        editableRows.forEach(targetRow => {
          try {
            const comboBoxCellType = new GC.Spread.Sheets.CellTypes.ComboBox();
            comboBoxCellType.items(safeOptions);
            comboBoxCellType.editorValueType(GC.Spread.Sheets.CellTypes.EditorValueType.text);

            const cellValidator = GC.Spread.Sheets.DataValidation.createListValidator(safeOptions.join(','));
            cellValidator.showInputMessage(false);
            cellValidator.showErrorMessage(false);

            activeSheet.setCellType(targetRow, col, comboBoxCellType);
            activeSheet.setDataValidator(targetRow, col, cellValidator);
            
            // 🚀 편집 권한 명시적 설정
            const cell = activeSheet.getCell(targetRow, col);
            cell.locked(false);

            console.log(`✅ Dropdown applied to editable cell [${targetRow}, ${col}]`);
          } catch (cellError) {
            console.warn(`⚠️ Failed to apply dropdown to [${targetRow}, ${col}]:`, cellError);
          }
        });
      } catch (error) {
        console.error(`❌ Dropdown config failed for column ${col}:`, error);
      }
    });

    setOptimalColumnWidths(activeSheet, visibleColumns, startCol, tableData);
    
    console.log(`✅ Optimized GRD_LIST created:`);
    console.log(`   - Total mappings: ${mappings.length}`);
    console.log(`   - Editable cells: ${mappings.filter(m => m.isEditable).length}`);
    console.log(`   - Dropdown configs: ${dropdownConfigs.length}`);
    
    return mappings;
  }, [tableData, columnsJSON, isFieldEditable, setBatchValues, setBatchStyles, ensureColumnCapacity, ensureRowCapacity, getCellAddress, setOptimalColumnWidths]);

  const setupSheetProtectionAndEvents = React.useCallback((activeSheet: any, mappings: CellMapping[]) => {
    console.log(`🛡️ Setting up protection and events for ${mappings.length} mappings`);

    // 🔧 시트 보호 완전 해제 후 편집 권한 설정
    activeSheet.options.isProtected = false;

    // 🔧 편집 가능한 셀들을 위한 강화된 설정
    mappings.forEach((mapping) => {
      const cellPos = parseCellAddress(mapping.cellAddress);
      if (!cellPos) return;
      
      try {
        const cell = activeSheet.getCell(cellPos.row, cellPos.col);
        const columnConfig = columnsJSON.find(col => col.key === mapping.attId);
        
        if (mapping.isEditable) {
          // 🚀 편집 가능한 셀 설정 강화
          cell.locked(false);
          
          if (columnConfig?.type === "LIST" && columnConfig.options) {
            // LIST 타입: 새 ComboBox 인스턴스 생성
            const comboBox = new GC.Spread.Sheets.CellTypes.ComboBox();
            comboBox.items(columnConfig.options);
            comboBox.editorValueType(GC.Spread.Sheets.CellTypes.EditorValueType.text);
            activeSheet.setCellType(cellPos.row, cellPos.col, comboBox);
            
            // DataValidation도 추가
            const validator = GC.Spread.Sheets.DataValidation.createListValidator(columnConfig.options.join(','));
            activeSheet.setDataValidator(cellPos.row, cellPos.col, validator);
          } else if (columnConfig?.type === "NUMBER") {
            // NUMBER 타입: 숫자 입력 허용
            const textCellType = new GC.Spread.Sheets.CellTypes.Text();
            activeSheet.setCellType(cellPos.row, cellPos.col, textCellType);
            
            // 숫자 validation 추가 (에러 메시지 없이)
            const numberValidator = GC.Spread.Sheets.DataValidation.createNumberValidator(
              GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.between,
              -999999999, 999999999, true
            );
            numberValidator.showInputMessage(false);
            numberValidator.showErrorMessage(false);
            activeSheet.setDataValidator(cellPos.row, cellPos.col, numberValidator);
          } else {
            // 기본 TEXT 타입: 자유 텍스트 입력
            const textCellType = new GC.Spread.Sheets.CellTypes.Text();
            activeSheet.setCellType(cellPos.row, cellPos.col, textCellType);
          }
          
          // 편집 가능 스타일 재적용
          const editableStyle = createCellStyle(true);
          activeSheet.setStyle(cellPos.row, cellPos.col, editableStyle);
          
          console.log(`🔓 Cell [${cellPos.row}, ${cellPos.col}] ${mapping.attId} set as EDITABLE`);
        } else {
          // 읽기 전용 셀
          cell.locked(true);
          const readonlyStyle = createCellStyle(false);
          activeSheet.setStyle(cellPos.row, cellPos.col, readonlyStyle);
        }
      } catch (error) {
        console.error(`❌ Error processing cell ${mapping.cellAddress}:`, error);
      }
    });

    // 🛡️ 시트 보호 재설정 (편집 허용 모드로)
    activeSheet.options.isProtected = true;
    activeSheet.options.protectionOptions = {
      allowSelectLockedCells: true,
      allowSelectUnlockedCells: true,
      allowSort: false,
      allowFilter: false,
      allowEditObjects: true,        // ✅ 편집 객체 허용
      allowResizeRows: false,
      allowResizeColumns: false,
      allowFormatCells: false,
      allowInsertRows: false,
      allowInsertColumns: false,
      allowDeleteRows: false,
      allowDeleteColumns: false
    };

    // 🎯 변경 감지 이벤트
    const changeEvents = [
      GC.Spread.Sheets.Events.CellChanged,
      GC.Spread.Sheets.Events.ValueChanged,
      GC.Spread.Sheets.Events.ClipboardPasted
    ];

    changeEvents.forEach(eventType => {
      activeSheet.bind(eventType, () => {
        console.log(`📝 ${eventType} detected`);
        setHasChanges(true);
      });
    });

    // 🚫 편집 시작 권한 확인
    activeSheet.bind(GC.Spread.Sheets.Events.EditStarting, (event: any, info: any) => {
      console.log(`🎯 EditStarting: Row ${info.row}, Col ${info.col}`);

      const exactMapping = mappings.find(m => {
        const cellPos = parseCellAddress(m.cellAddress);
        return cellPos && cellPos.row === info.row && cellPos.col === info.col;
      });

      if (!exactMapping) {
        console.log(`ℹ️ No mapping found for [${info.row}, ${info.col}] - allowing edit`);
        return; // 매핑이 없으면 허용
      }

      console.log(`📋 Found mapping: ${exactMapping.attId}, isEditable: ${exactMapping.isEditable}`);

      if (!exactMapping.isEditable) {
        console.log(`🚫 Field ${exactMapping.attId} is not editable`);
        toast.warning(`${exactMapping.attId} field is read-only`);
        info.cancel = true;
        return;
      }

      // SPREAD_LIST/GRD_LIST 개별 행 SHI 확인
      if ((templateType === 'SPREAD_LIST' || templateType === 'GRD_LIST') && exactMapping.dataRowIndex !== undefined) {
        const dataRowIndex = exactMapping.dataRowIndex;
        if (dataRowIndex >= 0 && dataRowIndex < tableData.length) {
          const rowData = tableData[dataRowIndex];
          if (rowData?.shi === true) {
            console.log(`🚫 Row ${dataRowIndex} is in SHI mode`);
            toast.warning(`Row ${dataRowIndex + 1}: ${exactMapping.attId} field is read-only (SHI mode)`);
            info.cancel = true;
            return;
          }
        }
      }

      console.log(`✅ Edit allowed for ${exactMapping.attId}`);
    });

    // ✅ 편집 완료 검증
    activeSheet.bind(GC.Spread.Sheets.Events.EditEnded, (event: any, info: any) => {
      console.log(`🏁 EditEnded: Row ${info.row}, Col ${info.col}, New value: ${activeSheet.getValue(info.row, info.col)}`);

      const exactMapping = mappings.find(m => {
        const cellPos = parseCellAddress(m.cellAddress);
        return cellPos && cellPos.row === info.row && cellPos.col === info.col;
      });

      if (!exactMapping) return;

      const columnConfig = columnsJSON.find(col => col.key === exactMapping.attId);
      if (columnConfig) {
        const cellValue = activeSheet.getValue(info.row, info.col);
        const errorMessage = validateCellValue(cellValue, columnConfig.type, columnConfig.options);
        const cell = activeSheet.getCell(info.row, info.col);

        if (errorMessage) {
          // 🚨 에러 스타일 적용
          const errorStyle = new GC.Spread.Sheets.Style();
          errorStyle.backColor = "#fef2f2";
          errorStyle.foreColor = "#dc2626";
          errorStyle.borderLeft = new GC.Spread.Sheets.LineBorder("#dc2626", GC.Spread.Sheets.LineStyle.thick);
          errorStyle.borderRight = new GC.Spread.Sheets.LineBorder("#dc2626", GC.Spread.Sheets.LineStyle.thick);
          errorStyle.borderTop = new GC.Spread.Sheets.LineBorder("#dc2626", GC.Spread.Sheets.LineStyle.thick);
          errorStyle.borderBottom = new GC.Spread.Sheets.LineBorder("#dc2626", GC.Spread.Sheets.LineStyle.thick);

          activeSheet.setStyle(info.row, info.col, errorStyle);
          cell.locked(!exactMapping.isEditable); // 편집 가능 상태 유지
          toast.warning(`Invalid value in ${exactMapping.attId}: ${errorMessage}`, { duration: 5000 });
        } else {
          // ✅ 정상 스타일 복원
          const normalStyle = createCellStyle(exactMapping.isEditable);
          activeSheet.setStyle(info.row, info.col, normalStyle);
          cell.locked(!exactMapping.isEditable);
        }
      }

      setHasChanges(true);
    });

    console.log(`🛡️ Protection configured. Editable cells: ${mappings.filter(m => m.isEditable).length}`);
  }, [templateType, tableData, createCellStyle, validateCellValue, columnsJSON]);

  // 🚀 최적화된 initSpread
  const initSpread = React.useCallback(async (spread: any, template?: TemplateItem) => {
    const workingTemplate = template || selectedTemplate;
    if (!spread || !workingTemplate) {
      console.error('❌ Invalid spread or template');
      return;
    }

    try {
      console.log('🚀 Starting optimized spread initialization...');
      setIsInitializing(true);
      updateProgress('Initializing...', 0, 100);
      
      setCurrentSpread(spread);
      setHasChanges(false);
      setValidationErrors([]);

      // 🚀 핵심 최적화: 모든 렌더링과 이벤트 중단
      spread.suspendPaint();
      spread.suspendEvent();
      spread.suspendCalcService();
      
      updateProgress('Setting up workspace...', 10, 100);

      try {
        let activeSheet = getSafeActiveSheet(spread, 'initSpread');
        if (!activeSheet) {
          throw new Error('Failed to get initial activeSheet');
        }

        activeSheet.options.isProtected = false;
        let mappings: CellMapping[] = [];

        if (templateType === 'GRD_LIST') {
          updateProgress('Creating dynamic table...', 20, 100);
          
          spread.clearSheets();
          spread.addSheet(0);
          const sheet = spread.getSheet(0);
          sheet.name('Data');
          spread.setActiveSheet('Data');
          
          updateProgress('Processing table data...', 50, 100);
          mappings = createGrdListTableOptimized(sheet, workingTemplate);
          
        } else {
          updateProgress('Loading template structure...', 20, 100);
          
          let contentJson = workingTemplate.SPR_LST_SETUP?.CONTENT || workingTemplate.SPR_ITM_LST_SETUP?.CONTENT;
          let dataSheets = workingTemplate.SPR_LST_SETUP?.DATA_SHEETS || workingTemplate.SPR_ITM_LST_SETUP?.DATA_SHEETS;

          if (!contentJson || !dataSheets) {
            throw new Error(`No template content found for ${workingTemplate.NAME}`);
          }

          const jsonData = typeof contentJson === 'string' ? JSON.parse(contentJson) : contentJson;
          
          updateProgress('Loading template layout...', 40, 100);
          spread.fromJSON(jsonData);
          
          activeSheet = getSafeActiveSheet(spread, 'after-fromJSON');
          if (!activeSheet) {
            throw new Error('ActiveSheet became null after loading template');
          }

          activeSheet.options.isProtected = false;

          if (templateType === 'SPREAD_LIST' && tableData.length > 0) {
            updateProgress('Processing data rows...', 60, 100);
            
            dataSheets.forEach(dataSheet => {
              if (dataSheet.MAP_CELL_ATT && dataSheet.MAP_CELL_ATT.length > 0) {
                dataSheet.MAP_CELL_ATT.forEach((mapping: any) => {
                  const { ATT_ID, IN } = mapping;
                  if (!ATT_ID || !IN || IN.trim() === "") return;

                  const cellPos = parseCellAddress(IN);
                  if (!cellPos) return;

                  const requiredRows = cellPos.row + tableData.length;
                  if (!ensureRowCapacity(activeSheet, requiredRows)) return;

                  // 🚀 배치 데이터 준비
                  const valuesToSet: Array<{row: number, col: number, value: any}> = [];
                  const stylesToSet: Array<{row: number, col: number, isEditable: boolean}> = [];

                  tableData.forEach((rowData, index) => {
                    const targetRow = cellPos.row + index;
                    const cellEditable = isFieldEditable(ATT_ID, rowData);
                    const value = rowData[ATT_ID];

                    mappings.push({
                      attId: ATT_ID,
                      cellAddress: getCellAddress(targetRow, cellPos.col),
                      isEditable: cellEditable,
                      dataRowIndex: index
                    });

                    valuesToSet.push({
                      row: targetRow,
                      col: cellPos.col,
                      value: value ?? null
                    });

                    stylesToSet.push({
                      row: targetRow,
                      col: cellPos.col,
                      isEditable: cellEditable
                    });
                  });

                  // 🚀 배치 처리
                  setBatchValues(activeSheet, valuesToSet);
                  setBatchStyles(activeSheet, stylesToSet);

                  // 드롭다운 설정
                  const columnConfig = columnsJSON.find(col => col.key === ATT_ID);
                  if (columnConfig?.type === "LIST" && columnConfig.options) {
                    const hasEditableRows = tableData.some((rowData) => isFieldEditable(ATT_ID, rowData));
                    if (hasEditableRows) {
                      setupOptimizedListValidation(activeSheet, cellPos, columnConfig.options, tableData.length);
                    }
                  }
                });
              }
            });
            
          } else if (templateType === 'SPREAD_ITEM' && selectedRow) {
            updateProgress('Setting up form fields...', 60, 100);
            
            dataSheets.forEach(dataSheet => {
              dataSheet.MAP_CELL_ATT?.forEach((mapping: any) => {
                const { ATT_ID, IN } = mapping;
                const cellPos = parseCellAddress(IN);
                if (cellPos) {
                  const isEditable = isFieldEditable(ATT_ID);
                  const value = selectedRow[ATT_ID];
                  
                  mappings.push({
                    attId: ATT_ID,
                    cellAddress: IN,
                    isEditable: isEditable,
                    dataRowIndex: 0
                  });
                  
                  const cell = activeSheet.getCell(cellPos.row, cellPos.col);
                  cell.value(value ?? null);
                  
                  const style = createCellStyle(isEditable);
                  activeSheet.setStyle(cellPos.row, cellPos.col, style);
                  
                  const columnConfig = columnsJSON.find(col => col.key === ATT_ID);
                  if (columnConfig?.type === "LIST" && columnConfig.options && isEditable) {
                    setupOptimizedListValidation(activeSheet, cellPos, columnConfig.options, 1);
                  }
                }
              });
            });
          }
        }

        updateProgress('Configuring interactions...', 90, 100);
        setCellMappings(mappings);
        
        const finalActiveSheet = getSafeActiveSheet(spread, 'setupEvents');
        if (finalActiveSheet) {
          setupSheetProtectionAndEvents(finalActiveSheet, mappings);
        }

        updateProgress('Finalizing...', 100, 100);
        console.log(`✅ Optimized initialization completed with ${mappings.length} mappings`);

      } finally {
        // 🚀 올바른 순서로 재개
        spread.resumeCalcService();
        spread.resumeEvent();
        spread.resumePaint();
      }

    } catch (error) {
      console.error('❌ Error in optimized spread initialization:', error);
      if (spread?.resumeCalcService) spread.resumeCalcService();
      if (spread?.resumeEvent) spread.resumeEvent();
      if (spread?.resumePaint) spread.resumePaint();
      toast.error(`Template loading failed: ${error.message}`);
    } finally {
      setIsInitializing(false);
      setLoadingProgress(null);
    }
  }, [selectedTemplate, templateType, selectedRow, tableData, updateProgress, getSafeActiveSheet, createGrdListTableOptimized, setBatchValues, setBatchStyles, setupSheetProtectionAndEvents, setCellMappings]);

  const handleSaveChanges = React.useCallback(async () => {
    if (!currentSpread || !hasChanges) {
      toast.info("No changes to save");
      return;
    }

    const errors = validateAllData();
    if (errors.length > 0) {
      toast.error(`Cannot save: ${errors.length} validation errors found. Please fix them first.`);
      return;
    }

    try {
      setIsPending(true);
      const activeSheet = currentSpread.getActiveSheet();

      if (templateType === 'SPREAD_ITEM' && selectedRow) {
        const dataToSave = { ...selectedRow };

        cellMappings.forEach(mapping => {
          if (mapping.isEditable) {
            const cellPos = parseCellAddress(mapping.cellAddress);
            if (cellPos) {
              const cellValue = activeSheet.getValue(cellPos.row, cellPos.col);
              dataToSave[mapping.attId] = cellValue;
            }
          }
        });

        dataToSave.TAG_NO = selectedRow.TAG_NO;

        const { success, message } = await updateFormDataInDB(
          formCode,
          contractItemId,
          dataToSave
        );

        if (!success) {
          toast.error(message);
          return;
        }

        toast.success("Changes saved successfully!");
        onUpdateSuccess?.(dataToSave);

      } else if ((templateType === 'SPREAD_LIST' || templateType === 'GRD_LIST') && tableData.length > 0) {
        const updatedRows: GenericData[] = [];
        let saveCount = 0;

        for (let i = 0; i < tableData.length; i++) {
          const originalRow = tableData[i];
          const dataToSave = { ...originalRow };
          let hasRowChanges = false;

          cellMappings.forEach(mapping => {
            if (mapping.dataRowIndex === i && mapping.isEditable) {
              const columnConfig = columnsJSON.find(col => col.key === mapping.attId);
              const isColumnEditable = columnConfig?.shi !== true;
              const isRowEditable = originalRow.shi !== true;

              if (isColumnEditable && isRowEditable) {
                const cellPos = parseCellAddress(mapping.cellAddress);
                if (cellPos) {
                  const cellValue = activeSheet.getValue(cellPos.row, cellPos.col);
                  if (cellValue !== originalRow[mapping.attId]) {
                    dataToSave[mapping.attId] = cellValue;
                    hasRowChanges = true;
                  }
                }
              }
            }
          });

          if (hasRowChanges) {
            dataToSave.TAG_NO = originalRow.TAG_NO;
            const { success } = await updateFormDataInDB(
              formCode,
              contractItemId,
              dataToSave
            );

            if (success) {
              updatedRows.push(dataToSave);
              saveCount++;
            }
          } else {
            updatedRows.push(originalRow);
          }
        }

        if (saveCount > 0) {
          toast.success(`${saveCount} rows saved successfully!`);
          onUpdateSuccess?.(updatedRows);
        } else {
          toast.info("No changes to save");
        }
      }

      setHasChanges(false);
      setValidationErrors([]);

    } catch (error) {
      console.error("Error saving changes:", error);
      toast.error("An unexpected error occurred while saving");
    } finally {
      setIsPending(false);
    }
  }, [currentSpread, hasChanges, templateType, selectedRow, tableData, formCode, contractItemId, onUpdateSuccess, cellMappings, columnsJSON, validateAllData]);

  if (!isOpen) return null;

  const isDataValid = templateType === 'SPREAD_ITEM' ? !!selectedRow : tableData.length > 0;
  const dataCount = templateType === 'SPREAD_ITEM' ? 1 : tableData.length;
  
  return (
    <Dialog open={isOpen} onOpenChange={onClose}>
      <DialogContent
        className="w-[90vw] max-w-[1400px] h-[85vh] flex flex-col fixed top-[50%] left-[50%] translate-x-[-50%] translate-y-[-50%] z-50"
      >
        <DialogHeader className="flex-shrink-0">
          <DialogTitle>SEDP Template - {formCode}</DialogTitle>
          <DialogDescription>
            <div className="space-y-3">
              {availableTemplates.length > 1 && (
                <div className="flex items-center gap-4">
                  <span className="text-sm font-medium">Template:</span>
                  <Select value={selectedTemplateId} onValueChange={handleTemplateChange}>
                    <SelectTrigger className="w-64">
                      <SelectValue placeholder="Select a template" />
                    </SelectTrigger>
                    <SelectContent>
                      {availableTemplates.map(template => (
                        <SelectItem key={template.TMPL_ID} value={template.TMPL_ID}>
                          {template.NAME} ({template.TMPL_TYPE})
                        </SelectItem>
                      ))}
                    </SelectContent>
                  </Select>
                </div>
              )}

              {selectedTemplate && (
                <div className="flex items-center gap-4 text-sm">
                  <span className="font-medium text-blue-600">
                    Template Type: {
                      templateType === 'SPREAD_LIST' ? 'List View (SPREAD_LIST)' :
                        templateType === 'SPREAD_ITEM' ? 'Item View (SPREAD_ITEM)' :
                          'Grid List View (GRD_LIST)'
                    }
                  </span>
                  {templateType === 'SPREAD_ITEM' && selectedRow && (
                    <span>• Selected TAG_NO: {selectedRow.TAG_NO || 'N/A'}</span>
                  )}
                  {(templateType === 'SPREAD_LIST' || templateType === 'GRD_LIST') && (
                    <span>• {dataCount} rows</span>
                  )}
                  {hasChanges && (
                    <span className="text-orange-600 font-medium">
                      • Unsaved changes
                    </span>
                  )}
                  {validationErrors.length > 0 && (
                    <span className="text-red-600 font-medium flex items-center">
                      <AlertTriangle className="w-4 h-4 mr-1" />
                      {validationErrors.length} validation errors
                    </span>
                  )}
                </div>
              )}

              <div className="flex items-center gap-4 text-xs">
                <span className="text-muted-foreground">
                  <span className="inline-block w-3 h-3 bg-green-100 border border-green-400 mr-1"></span>
                  Editable fields
                </span>
                <span className="text-muted-foreground">
                  <span className="inline-block w-3 h-3 bg-gray-100 border border-gray-300 mr-1"></span>
                  Read-only fields
                </span>
                <span className="text-muted-foreground">
                  <span className="inline-block w-3 h-3 bg-red-100 border border-red-300 mr-1"></span>
                  Validation errors
                </span>
                {cellMappings.length > 0 && (
                  <span className="text-blue-600">
                    {editableFieldsCount} of {cellMappings.length} fields editable
                  </span>
                )}
              </div>
            </div>
          </DialogDescription>
        </DialogHeader>

        <div className="flex-1 overflow-hidden relative">
          {/* 🆕 로딩 프로그레스 오버레이 */}
          <LoadingProgress 
            phase={loadingProgress?.phase || ''}
            progress={loadingProgress?.progress || 0}
            total={loadingProgress?.total || 100}
            isVisible={isInitializing && !!loadingProgress}
          />
          
          {selectedTemplate && isClient && isDataValid ? (
            <SpreadSheets
              key={`${templateType}-${selectedTemplate.TMPL_ID}-${selectedTemplateId}`}
              workbookInitialized={initSpread}
              hostStyle={hostStyle}
            />
          ) : (
            <div className="flex items-center justify-center h-full text-muted-foreground">
              {!isClient ? (
                <>
                  <Loader className="mr-2 h-4 w-4 animate-spin" />
                  Loading...
                </>
              ) : !selectedTemplate ? (
                "No template available"
              ) : !isDataValid ? (
                `No ${templateType === 'SPREAD_ITEM' ? 'selected row' : 'data'} available`
              ) : (
                "Template not ready"
              )}
            </div>
          )}
        </div>

        <DialogFooter className="flex-shrink-0">
          <div className="flex items-center gap-2">
            <Button variant="outline" onClick={onClose}>
              Close
            </Button>

            {hasChanges && (
              <Button
                variant="default"
                onClick={handleSaveChanges}
                disabled={isPending || validationErrors.length > 0}
              >
                {isPending ? (
                  <>
                    <Loader className="mr-2 h-4 w-4 animate-spin" />
                    Saving...
                  </>
                ) : (
                  <>
                    <Save className="mr-2 h-4 w-4" />
                    Save Changes
                  </>
                )}
              </Button>
            )}

            {validationErrors.length > 0 && (
              <Button
                variant="outline"
                onClick={validateAllData}
                className="text-red-600 border-red-300 hover:bg-red-50"
              >
                <AlertTriangle className="mr-2 h-4 w-4" />
                Check Errors ({validationErrors.length})
              </Button>
            )}
          </div>
        </DialogFooter>
      </DialogContent>
    </Dialog>
  );
}