path: root/components/form-data/spreadJS-dialog.tsx

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

// SpreadSheets를 동적으로 import (SSR 비활성화)
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; // SPREAD_ITEM용
  tableData?: GenericData[]; // SPREAD_LIST용
  formCode: string;
  columnsJSON: DataTableColumnJSON[]
  contractItemId: number;
  editableFieldsMap?: Map<string, string[]>;
  onUpdateSuccess?: (updatedValues: Record<string, any> | GenericData[]) => void;
}

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[]>([]);

  // 클라이언트 사이드에서만 렌더링되도록 보장
  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(template => {
      const hasSpreadListContent = template.SPR_LST_SETUP?.CONTENT;
      const hasSpreadItemContent = template.SPR_ITM_LST_SETUP?.CONTENT;
      const hasGrdListSetup = template.GRD_LST_SETUP && columnsJSON.length > 0; // GRD_LIST 조건: GRD_LST_SETUP 존재 + columnsJSON 있음
      
      const isValidType = template.TMPL_TYPE === "SPREAD_LIST" || 
                         template.TMPL_TYPE === "SPREAD_ITEM" || 
                         template.TMPL_TYPE === "GRD_LIST"; // GRD_LIST 타입 추가

      return isValidType && (hasSpreadListContent || hasSpreadItemContent || hasGrdListSetup);
    });

    setAvailableTemplates(validTemplates);

    // 첫 번째 유효한 템플릿을 기본으로 선택
    if (validTemplates.length > 0 && !selectedTemplateId) {
      const firstTemplate = validTemplates[0];
      setSelectedTemplateId(firstTemplate.TMPL_ID);
      
      // 템플릿 타입 결정
      let templateTypeToSet: 'SPREAD_LIST' | 'SPREAD_ITEM' | 'GRD_LIST';
      if (firstTemplate.GRD_LST_SETUP && columnsJSON.length > 0) {
        templateTypeToSet = 'GRD_LIST';
      } else {
        templateTypeToSet = firstTemplate.TMPL_TYPE as 'SPREAD_LIST' | 'SPREAD_ITEM';
      }
      
      setTemplateType(templateTypeToSet);
    }
  }, [templateData, selectedTemplateId]);

  // 선택된 템플릿 변경 처리
  const handleTemplateChange = (templateId: string) => {
    const template = availableTemplates.find(t => t.TMPL_ID === templateId);
    if (template) {
      setSelectedTemplateId(templateId);
      
      // 템플릿 타입 결정
      let templateTypeToSet: 'SPREAD_LIST' | 'SPREAD_ITEM' | 'GRD_LIST';
      if (template.GRD_LST_SETUP && columnsJSON.length > 0) {
        templateTypeToSet = 'GRD_LIST';
      } else {
        templateTypeToSet = template.TMPL_TYPE as 'SPREAD_LIST' | 'SPREAD_ITEM';
      }
      
      setTemplateType(templateTypeToSet);
      setHasChanges(false);
      setValidationErrors([]);

      // SpreadSheets 재초기화
      if (currentSpread) {
        const template = availableTemplates.find(t => t.TMPL_ID === templateId);
        if (template) {
          initSpread(currentSpread, template);
        }
      }
    }
  };

  // 현재 선택된 템플릿 가져오기
  const selectedTemplate = React.useMemo(() => {
    return availableTemplates.find(t => t.TMPL_ID === selectedTemplateId);
  }, [availableTemplates, selectedTemplateId]);

  // 편집 가능한 필드 목록 계산
  const editableFields = React.useMemo(() => {
    // SPREAD_ITEM인 경우: selectedRow의 TAG_NO로 확인
    if (templateType === 'SPREAD_ITEM' && selectedRow?.TAG_NO) {
      if (!editableFieldsMap.has(selectedRow.TAG_NO)) {
        return [];
      }
      return editableFieldsMap.get(selectedRow.TAG_NO) || [];
    }
    
    // SPREAD_LIST 또는 GRD_LIST인 경우: 첫 번째 행의 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) => {
    // columnsJSON에서 해당 attId의 shi 값 확인
    const columnConfig = columnsJSON.find(col => col.key === attId);
    if (columnConfig?.shi === true) {
      return false; // columnsJSON에서 shi가 true이면 편집 불가
    }

    // TAG_NO와 TAG_DESC는 기본적으로 편집 가능 (columnsJSON의 shi가 false인 경우)
    if (attId === "TAG_NO" || attId === "TAG_DESC") {
      return true;
    }

    // SPREAD_ITEM인 경우: editableFields 체크
    if (templateType === 'SPREAD_ITEM') {
      return editableFields.includes(attId);
    }
    
    // SPREAD_LIST 또는 GRD_LIST인 경우: 개별 행의 편집 가능성도 고려
    if (templateType === 'SPREAD_LIST' || templateType === 'GRD_LIST') {
      // 기본적으로 editableFields에 포함되어야 함
      if (!editableFields.includes(attId)) {
        return false;
      }
      
      // rowData가 제공된 경우 해당 행의 shi 상태도 확인
      if (rowData && rowData.shi === true) {
        return false;
      }
      
      return true;
    }
    
    return true;
  }, [templateType, columnsJSON, editableFields]);

  // 편집 가능한 필드 개수 계산
  const editableFieldsCount = React.useMemo(() => {
    return cellMappings.filter(m => m.isEditable).length;
  }, [cellMappings]);

  // 셀 주소를 행과 열로 변환하는 함수
  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 };
  };

  // 행과 열을 셀 주소로 변환하는 함수 (GRD_LIST용)
  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; // 빈 값은 별도 required 검증에서 처리
    }

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

      if (templateType === 'SPREAD_ITEM') {
        // 단일 행 검증
        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
          });
        }
      } else if (templateType === 'SPREAD_LIST' || templateType === 'GRD_LIST') {
        // 복수 행 검증 - 각 매핑은 이미 개별 행을 가리킴
        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, templateType]);

  // ═══════════════════════════════════════════════════════════════════════════════
  // 🛠️ 헬퍼 함수들
  // ═══════════════════════════════════════════════════════════════════════════════

  // 🎨 셀 스타일 생성
  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);

      // ✅ 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;
      }

      console.log(`📋 Safe options:`, safeOptions);

      // ✅ DataValidation용 문자열 준비
      const optionsString = safeOptions.join(',');

      // 🔑 핵심 수정: 각 셀마다 개별 ComboBox 인스턴스 생성!
      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);
          
          // ComboBox + DataValidation 둘 다 적용
          activeSheet.setCellType(targetRow, cellPos.col, comboBoxCellType);
          activeSheet.setDataValidator(targetRow, cellPos.col, cellValidator);
          
          // 셀 잠금 해제
          const cell = activeSheet.getCell(targetRow, cellPos.col);
          cell.locked(false);
          
          console.log(`✅ Individual dropdown applied to [${targetRow}, ${cellPos.col}]`);
          
        } catch (cellError) {
          console.warn(`⚠️ Failed to apply to row ${cellPos.row + i}:`, cellError);
        }
      }

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

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

  // 🚀 행 용량 확보
  const ensureRowCapacity = React.useCallback((activeSheet: any, requiredRowCount: number) => {
    const currentRowCount = activeSheet.getRowCount();
    if (requiredRowCount > currentRowCount) {
      activeSheet.setRowCount(requiredRowCount + 10); // 여유분 추가
      console.log(`📈 Expanded sheet to ${requiredRowCount + 10} rows`);
    }
  }, []);

  // 🆕 GRD_LIST용 동적 테이블 생성 함수
  const createGrdListTable = React.useCallback((activeSheet: any, template: TemplateItem) => {
    console.log('🏗️ Creating GRD_LIST table');
    
    // columnsJSON의 visible 컬럼들을 seq 순서로 정렬하여 사용
    const visibleColumns = columnsJSON
      .filter(col => col.hidden !== true) // hidden이 true가 아닌 것들만
      .sort((a, b) => {
        // seq가 없는 경우 999999로 처리하여 맨 뒤로 보냄
        const seqA = a.seq !== undefined ? a.seq : 999999;
        const seqB = b.seq !== undefined ? b.seq : 999999;
        return seqA - seqB;
      });

    console.log('📊 Using columns:', visibleColumns.map(c => `${c.key}(seq:${c.seq})`));

    if (visibleColumns.length === 0) {
      console.warn('❌ No visible columns found in columnsJSON');
      return [];
    }

    // 테이블 생성 시작
    const mappings: CellMapping[] = [];
    const startCol = 1; // A열 제외하고 B열부터 시작
    
    // 🔍 그룹 헤더 분석
    const groupInfo = analyzeColumnGroups(visibleColumns);
    const hasGroups = groupInfo.groups.length > 0;
    
    // 헤더 행 계산: 그룹이 있으면 2행, 없으면 1행
    const groupHeaderRow = 0;
    const columnHeaderRow = hasGroups ? 1 : 0;
    const dataStartRow = hasGroups ? 2 : 1;

    // 🎨 헤더 스타일 생성
    const groupHeaderStyle = new GC.Spread.Sheets.Style();
    groupHeaderStyle.backColor = "#1e40af"; // 더 진한 파란색
    groupHeaderStyle.foreColor = "#ffffff";
    groupHeaderStyle.font = "bold 13px Arial";
    groupHeaderStyle.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
    groupHeaderStyle.vAlign = GC.Spread.Sheets.VerticalAlign.center;

    const columnHeaderStyle = new GC.Spread.Sheets.Style();
    columnHeaderStyle.backColor = "#3b82f6"; // 기본 파란색
    columnHeaderStyle.foreColor = "#ffffff";
    columnHeaderStyle.font = "bold 12px Arial";
    columnHeaderStyle.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
    columnHeaderStyle.vAlign = GC.Spread.Sheets.VerticalAlign.center;

    let currentCol = startCol;

    // 🏗️ 그룹 헤더 및 컬럼 헤더 생성
    if (hasGroups) {
      // 그룹 헤더가 있는 경우
      groupInfo.groups.forEach(group => {
        if (group.isGroup) {
          // 그룹 헤더 생성 및 병합
          const groupStartCol = currentCol;
          const groupEndCol = currentCol + group.columns.length - 1;
          
          // 그룹 헤더 셀 설정
          const groupHeaderCell = activeSheet.getCell(groupHeaderRow, groupStartCol);
          groupHeaderCell.value(group.head);
          
          // 그룹 헤더 병합
          if (group.columns.length > 1) {
            activeSheet.addSpan(groupHeaderRow, groupStartCol, 1, group.columns.length);
          }
          
          // 그룹 헤더 스타일 적용
          for (let col = groupStartCol; col <= groupEndCol; col++) {
            activeSheet.setStyle(groupHeaderRow, col, groupHeaderStyle);
            activeSheet.getCell(groupHeaderRow, col).locked(true);
          }
          
          console.log(`📝 Group Header [${groupHeaderRow}, ${groupStartCol}-${groupEndCol}]: ${group.head}`);
          
          // 그룹 내 개별 컬럼 헤더 생성
          group.columns.forEach((column, index) => {
            const colIndex = groupStartCol + index;
            const columnHeaderCell = activeSheet.getCell(columnHeaderRow, colIndex);
            columnHeaderCell.value(column.label);
            activeSheet.setStyle(columnHeaderRow, colIndex, columnHeaderStyle);
            columnHeaderCell.locked(true);
            
            console.log(`📝 Column Header [${columnHeaderRow}, ${colIndex}]: ${column.label}`);
          });
          
          currentCol += group.columns.length;
        } else {
          // 그룹이 아닌 단일 컬럼
          const column = group.columns[0];
          
          // 그룹 헤더 행에는 빈 셀 (개별 컬럼이므로)
          const groupHeaderCell = activeSheet.getCell(groupHeaderRow, currentCol);
          groupHeaderCell.value("");
          activeSheet.setStyle(groupHeaderRow, currentCol, groupHeaderStyle);
          groupHeaderCell.locked(true);
          
          // 컬럼 헤더 생성
          const columnHeaderCell = activeSheet.getCell(columnHeaderRow, currentCol);
          columnHeaderCell.value(column.label);
          activeSheet.setStyle(columnHeaderRow, currentCol, columnHeaderStyle);
          columnHeaderCell.locked(true);
          
          console.log(`📝 Single Column [${columnHeaderRow}, ${currentCol}]: ${column.label}`);
          currentCol++;
        }
      });
    } else {
      // 그룹이 없는 경우 - 기존 로직
      visibleColumns.forEach((column, colIndex) => {
        const headerCol = startCol + colIndex;
        const headerCell = activeSheet.getCell(columnHeaderRow, headerCol);
        headerCell.value(column.label);
        activeSheet.setStyle(columnHeaderRow, headerCol, columnHeaderStyle);
        headerCell.locked(true);
        
        console.log(`📝 Header [${columnHeaderRow}, ${headerCol}]: ${column.label}`);
      });
    }

    // 데이터 행 생성
    const dataRowCount = tableData.length;
    ensureRowCapacity(activeSheet, dataStartRow + dataRowCount);

    tableData.forEach((rowData, rowIndex) => {
      const targetRow = dataStartRow + rowIndex;
      
      visibleColumns.forEach((column, colIndex) => {
        const targetCol = startCol + colIndex;
        const cellAddress = getCellAddress(targetRow, targetCol);
        const isEditable = isFieldEditable(column.key, rowData);
        
        // 매핑 정보 추가
        mappings.push({
          attId: column.key,
          cellAddress: cellAddress,
          isEditable: isEditable,
          dataRowIndex: rowIndex
        });

        // 셀 값 설정
        const cell = activeSheet.getCell(targetRow, targetCol);
        const value = rowData[column.key];
        cell.value(value ?? null);

        // 스타일 적용
        const style = createCellStyle(isEditable);
        activeSheet.setStyle(targetRow, targetCol, style);
        cell.locked(!isEditable);

        console.log(`📝 Data [${targetRow}, ${targetCol}]: ${column.key} = "${value}" (${isEditable ? 'Editable' : 'ReadOnly'})`);

        // LIST 타입 드롭다운 설정
        if (column.type === "LIST" && column.options && isEditable) {
          setupOptimizedListValidation(activeSheet, { row: targetRow, col: targetCol }, column.options, 1);
        }
      });
    });

    console.log(`🏗️ GRD_LIST table created with ${mappings.length} mappings, hasGroups: ${hasGroups}`);
    return mappings;
  }, [tableData, columnsJSON, isFieldEditable, createCellStyle, ensureRowCapacity, setupOptimizedListValidation]);

  // 🔍 컬럼 그룹 분석 함수
  const analyzeColumnGroups = React.useCallback((columns: DataTableColumnJSON[]) => {
    const groups: Array<{
      head: string;
      isGroup: boolean;
      columns: DataTableColumnJSON[];
    }> = [];

    let i = 0;
    while (i < columns.length) {
      const currentCol = columns[i];
      
      // head가 없거나 빈 문자열인 경우 단일 컬럼으로 처리
      if (!currentCol.head || !currentCol.head.trim()) {
        groups.push({
          head: '',
          isGroup: false,
          columns: [currentCol]
        });
        i++;
        continue;
      }

      // 같은 head를 가진 연속된 컬럼들을 찾기
      const groupHead = currentCol.head.trim();
      const groupColumns: DataTableColumnJSON[] = [currentCol];
      let j = i + 1;

      while (j < columns.length && columns[j].head && columns[j].head.trim() === groupHead) {
        groupColumns.push(columns[j]);
        j++;
      }

      // 그룹 추가
      groups.push({
        head: groupHead,
        isGroup: groupColumns.length > 1,
        columns: groupColumns
      });

      i = j; // 다음 그룹으로 이동
    }

    return { groups };
  }, []);

  // 🛡️ 시트 보호 및 이벤트 설정
  const setupSheetProtectionAndEvents = React.useCallback((activeSheet: any, mappings: CellMapping[]) => {
    console.log(`🛡️ Setting up protection and events for ${mappings.length} mappings`);
    
    // 시트 보호 설정
    activeSheet.options.isProtected = true;
    activeSheet.options.protectionOptions = {
      allowSelectLockedCells: true,
      allowSelectUnlockedCells: true,
      allowSort: false,
      allowFilter: false,
      allowEditObjects: false,
      allowResizeRows: false,
      allowResizeColumns: 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} at ${exactMapping.cellAddress}`);

      // 기본 편집 권한 확인
      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;
        
        console.log(`🔍 Checking SHI for data row ${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;
          }
        } else {
          console.warn(`⚠️ Invalid dataRowIndex: ${dataRowIndex} (tableData.length: ${tableData.length})`);
        }
      }

      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}`);
      
      // ✅ 정확한 매핑 찾기
      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}] - skipping validation`);
        return;
      }

      const columnConfig = columnsJSON.find(col => col.key === exactMapping.attId);
      if (columnConfig) {
        const cellValue = activeSheet.getValue(info.row, info.col);
        console.log(`🔍 Validating ${exactMapping.attId}: "${cellValue}"`);
        
        const errorMessage = validateCellValue(cellValue, columnConfig.type, columnConfig.options);
        const cell = activeSheet.getCell(info.row, info.col);

        if (errorMessage) {
          console.log(`❌ Validation failed: ${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}. Please correct the value.`, { duration: 5000 });
        } else {
          console.log(`✅ Validation passed`);
          
          // ✅ 정상 스타일 복원
          const normalStyle = createCellStyle(exactMapping.isEditable);
          activeSheet.setStyle(info.row, info.col, normalStyle);
          cell.locked(!exactMapping.isEditable);
        }
      }
    });

    console.log(`🛡️ Protection and events configured for ${mappings.length} mappings`);
  }, [templateType, tableData, createCellStyle, validateCellValue, columnsJSON]);

  // ═══════════════════════════════════════════════════════════════════════════════
  // 🏗️ 메인 SpreadSheets 초기화 함수
  // ═══════════════════════════════════════════════════════════════════════════════

  const initSpread = React.useCallback((spread: any, template?: TemplateItem) => {
    const workingTemplate = template || selectedTemplate;
    if (!spread || !workingTemplate) return;

    try {
      // 🔄 초기 설정
      setCurrentSpread(spread);
      setHasChanges(false);
      setValidationErrors([]);

      // 성능을 위한 렌더링 일시 중단
      spread.suspendPaint();

      try {
        const activeSheet = spread.getActiveSheet();
        
        // 시트 보호 해제 (편집을 위해)
        activeSheet.options.isProtected = false;

        let mappings: CellMapping[] = [];

        // 🆕 GRD_LIST 처리
        if (templateType === 'GRD_LIST' && workingTemplate.GRD_LST_SETUP) {
          console.log('🏗️ Processing GRD_LIST template');
          
          // 기본 워크북 설정
          spread.clearSheets();
          spread.addSheet(0);
          const sheet = spread.getSheet(0);
          sheet.name('Data');
          spread.setActiveSheet('Data');
          
          // 동적 테이블 생성
          mappings = createGrdListTable(sheet, workingTemplate);
          
        } else {
          // 기존 SPREAD_LIST 및 SPREAD_ITEM 처리
          let contentJson = null;
          let dataSheets = null;

          // SPR_LST_SETUP.CONTENT 우선 사용
          if (workingTemplate.SPR_LST_SETUP?.CONTENT) {
            contentJson = workingTemplate.SPR_LST_SETUP.CONTENT;
            dataSheets = workingTemplate.SPR_LST_SETUP.DATA_SHEETS;
            console.log('✅ Using SPR_LST_SETUP.CONTENT for template:', workingTemplate.NAME);
          }
          // SPR_ITM_LST_SETUP.CONTENT 대안 사용
          else if (workingTemplate.SPR_ITM_LST_SETUP?.CONTENT) {
            contentJson = workingTemplate.SPR_ITM_LST_SETUP.CONTENT;
            dataSheets = workingTemplate.SPR_ITM_LST_SETUP.DATA_SHEETS;
            console.log('✅ Using SPR_ITM_LST_SETUP.CONTENT for template:', workingTemplate.NAME);
          }

          if (!contentJson) {
            console.warn('❌ No CONTENT found in template:', workingTemplate.NAME);
            return;
          }

          // 🏗️ SpreadSheets 초기화
          const jsonData = typeof contentJson === 'string' ? JSON.parse(contentJson) : contentJson;
          
          // 템플릿 구조 로드
          spread.fromJSON(jsonData);

          // 📊 셀 매핑 및 데이터 처리
          if (dataSheets && dataSheets.length > 0) {
            
            // 🔄 각 데이터 시트의 매핑 정보 처리
            dataSheets.forEach(dataSheet => {
              if (dataSheet.MAP_CELL_ATT) {
                dataSheet.MAP_CELL_ATT.forEach(mapping => {
                  const { ATT_ID, IN } = mapping;

                  if (IN && IN.trim() !== "") {
                    const cellPos = parseCellAddress(IN);
                    if (cellPos) {
                      const columnConfig = columnsJSON.find(col => col.key === ATT_ID);

                      // 🎯 템플릿 타입별 데이터 처리
                      if (templateType === 'SPREAD_ITEM' && selectedRow) {
                        // 📝 단일 행 처리 (SPREAD_ITEM)
                        const isEditable = isFieldEditable(ATT_ID);
                        
                        // 매핑 정보 저장
                        mappings.push({
                          attId: ATT_ID,
                          cellAddress: IN,
                          isEditable: isEditable,
                          dataRowIndex: 0
                        });

                        const cell = activeSheet.getCell(cellPos.row, cellPos.col);
                        const value = selectedRow[ATT_ID];
                        
                        // 값 설정
                        cell.value(value ?? null);

                        // 🎨 스타일 및 편집 권한 설정
                        cell.locked(!isEditable);
                        const style = createCellStyle(isEditable);
                        activeSheet.setStyle(cellPos.row, cellPos.col, style);

                        // 📋 LIST 타입 드롭다운 설정
                        if (columnConfig?.type === "LIST" && columnConfig.options && isEditable) {
                          setupOptimizedListValidation(activeSheet, cellPos, columnConfig.options, 1);
                        }

                      } else if (templateType === 'SPREAD_LIST' && tableData.length > 0) {
                        // 📊 복수 행 처리 (SPREAD_LIST) - 성능 최적화됨
                        console.log(`🔄 Processing SPREAD_LIST for ${ATT_ID} with ${tableData.length} rows`);
                        
                        // 🚀 행 확장 (필요시)
                        ensureRowCapacity(activeSheet, cellPos.row + tableData.length);

                        // 📋 각 행마다 개별 매핑 생성
                        tableData.forEach((rowData, index) => {
                          const targetRow = cellPos.row + index;
                          const targetCellAddress = `${String.fromCharCode(65 + cellPos.col)}${targetRow + 1}`;
                          const cellEditable = isFieldEditable(ATT_ID, rowData);
                          
                          // 개별 매핑 추가
                          mappings.push({
                            attId: ATT_ID,
                            cellAddress: targetCellAddress, // 각 행마다 다른 주소
                            isEditable: cellEditable,
                            dataRowIndex: index // 원본 데이터 인덱스
                          });
                          
                          console.log(`📝 Mapping ${ATT_ID} Row ${index}: ${targetCellAddress} (${cellEditable ? 'Editable' : 'ReadOnly'})`);
                        });

                        // 📋 LIST 타입 드롭다운 설정 (조건부)
                        if (columnConfig?.type === "LIST" && columnConfig.options) {
                          // 편집 가능한 행이 하나라도 있으면 드롭다운 설정
                          const hasEditableRows = tableData.some((rowData) => isFieldEditable(ATT_ID, rowData));
                          if (hasEditableRows) {
                            setupOptimizedListValidation(activeSheet, cellPos, columnConfig.options, tableData.length);
                          }
                        }

                        // 🎨 개별 셀 데이터 및 스타일 설정
                        tableData.forEach((rowData, index) => {
                          const targetRow = cellPos.row + index;
                          const cell = activeSheet.getCell(targetRow, cellPos.col);
                          const value = rowData[ATT_ID];
                          
                          // 값 설정
                          cell.value(value ?? null);
                          // console.log(`📝 Row ${targetRow}: ${ATT_ID} = "${value}"`);
                          
                          // 편집 권한 및 스타일 설정
                          const cellEditable = isFieldEditable(ATT_ID, rowData);
                          cell.locked(!cellEditable);
                          const style = createCellStyle(cellEditable);
                          activeSheet.setStyle(targetRow, cellPos.col, style);
                        });
                      }

                      // console.log(`📌 Mapped ${ATT_ID} → ${IN} (${templateType})`);
                    }
                  }
                });
              }
            });
          }
        }

        // 💾 매핑 정보 저장 및 이벤트 설정
        setCellMappings(mappings);
        setupSheetProtectionAndEvents(activeSheet, mappings);

      } finally {
        // 렌더링 재개
        spread.resumePaint();
      }

    } catch (error) {
      console.error('❌ Error initializing spread:', error);
      toast.error('Failed to load template');
      if (spread?.resumePaint) {
        spread.resumePaint();
      }
    }
  }, [selectedTemplate, templateType, selectedRow, tableData, isFieldEditable, columnsJSON, createCellStyle, setupOptimizedListValidation, ensureRowCapacity, setupSheetProtectionAndEvents, createGrdListTable]);

  // 변경사항 저장 함수
  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) {
        // 복수 행 저장 (SPREAD_LIST와 GRD_LIST 동일 처리)
        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; // 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-[80%] max-w-none h-[80vh] flex flex-col"
        style={{ maxWidth: "80vw" }}
      >
        <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.GRD_LST_SETUP && columnsJSON.length > 0
                              ? 'GRD_LIST' 
                              : 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>

        {/* SpreadSheets 컴포넌트 영역 */}
        <div className="flex-1 overflow-hidden">
          {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>
  );
}