path: root/lib/esg-check-list/table/excel-utils.tsx

// @/lib/esg-check-list/excel-utils.ts
import * as ExcelJS from 'exceljs';
import { saveAs } from 'file-saver';

// ====================================================================
// 타입 정의
// ====================================================================

export interface ExcelEvaluation {
  serialNumber: string;
  category: string;
  inspectionItem: string;
}

export interface ExcelEvaluationItem {
  serialNumber: string;
  evaluationItem: string;
  evaluationItemDescription?: string;
  orderIndex: number;
}

export interface ExcelAnswerOption {
  serialNumber: string;
  evaluationItem: string;
  answerText: string;
  score: number;
  orderIndex: number;
}

export interface ParsedExcelData {
  evaluations: ExcelEvaluation[];
  evaluationItems: ExcelEvaluationItem[];
  answerOptions: ExcelAnswerOption[];
}

// ====================================================================
// 템플릿 다운로드
// ====================================================================

export async function downloadEsgTemplate() {
  const workbook = new ExcelJS.Workbook();
  
  // 시트 1: 평가표 기본 정보
  const evaluationsSheet = workbook.addWorksheet('평가표');
  evaluationsSheet.columns = [
    { header: '시리얼번호*', key: 'serialNumber', width: 15 },
    { header: '분류*', key: 'category', width: 20 },
    { header: '점검항목*', key: 'inspectionItem', width: 50 },
  ];

  // 예시 데이터
  evaluationsSheet.addRows([
    { serialNumber: 'P-1', category: '정보공시', inspectionItem: 'ESG 정보공시 형식' },
    { serialNumber: 'E-1', category: '환경 (Environmental)', inspectionItem: '환경경영 체계	' },
  ]);

  // 헤더 스타일링
  const evaluationsHeaderRow = evaluationsSheet.getRow(1);
  evaluationsHeaderRow.font = { bold: true };
  evaluationsHeaderRow.fill = {
    type: 'pattern',
    pattern: 'solid',
    fgColor: { argb: 'FFE6F3FF' }
  };

  // 시트 2: 평가항목
  const itemsSheet = workbook.addWorksheet('평가항목');
  itemsSheet.columns = [
    { header: '시리얼번호*', key: 'serialNumber', width: 15 },
    { header: '평가항목*', key: 'evaluationItem', width: 40 },
    { header: '평가항목설명', key: 'evaluationItemDescription', width: 50 },
    { header: '순서*', key: 'orderIndex', width: 10 },
  ];

  // 예시 데이터
  itemsSheet.addRows([
    { 
      serialNumber: 'P-1', 
      evaluationItem: 'ESG 보고서 작성 여부', 
      evaluationItemDescription: '연간 ESG 보고서를 작성하고 공시하는지 확인',
      orderIndex: 1 
    },
    { 
      serialNumber: 'P-1', 
      evaluationItem: '지속가능경영 전략 수립', 
      evaluationItemDescription: '장기적인 지속가능경영 전략이 수립되어 있는지 확인',
      orderIndex: 2 
    },
    { 
      serialNumber: 'P-1', 
      evaluationItem: '환경경영시스템 인증', 
      evaluationItemDescription: 'ISO 14001 등 환경경영시스템 인증 보유 여부',
      orderIndex: 1 
    },
  ]);

  // 헤더 스타일링
  const itemsHeaderRow = itemsSheet.getRow(1);
  itemsHeaderRow.font = { bold: true };
  itemsHeaderRow.fill = {
    type: 'pattern',
    pattern: 'solid',
    fgColor: { argb: 'FFFFEEE6' }
  };

  // 시트 3: 답변옵션
  const optionsSheet = workbook.addWorksheet('답변옵션');
  optionsSheet.columns = [
    { header: '시리얼번호*', key: 'serialNumber', width: 15 },
    { header: '평가항목*', key: 'evaluationItem', width: 40 },
    { header: '답변내용*', key: 'answerText', width: 30 },
    { header: '점수*', key: 'score', width: 10 },
    { header: '순서*', key: 'orderIndex', width: 10 },
  ];

  // 예시 데이터
  optionsSheet.addRows([
    { serialNumber: 'P-1', evaluationItem: 'ESG 보고서 작성 여부', answerText: '매년 정기적으로 작성', score: 5, orderIndex: 1 },
    { serialNumber: 'P-1', evaluationItem: 'ESG 보고서 작성 여부', answerText: '비정기적으로 작성', score: 3, orderIndex: 2 },
    { serialNumber: 'P-1', evaluationItem: 'ESG 보고서 작성 여부', answerText: '작성하지 않음', score: 0, orderIndex: 3 },
    { serialNumber: 'P-1', evaluationItem: '지속가능경영 전략 수립', answerText: '체계적인 전략 보유', score: 5, orderIndex: 1 },
    { serialNumber: 'P-1', evaluationItem: '지속가능경영 전략 수립', answerText: '기본적인 계획 보유', score: 3, orderIndex: 2 },
    { serialNumber: 'P-1', evaluationItem: '지속가능경영 전략 수립', answerText: '전략 없음', score: 0, orderIndex: 3 },
  ]);

  // 헤더 스타일링
  const optionsHeaderRow = optionsSheet.getRow(1);
  optionsHeaderRow.font = { bold: true };
  optionsHeaderRow.fill = {
    type: 'pattern',
    pattern: 'solid',
    fgColor: { argb: 'FFE6F7E6' }
  };

  // 안내 시트 추가
  const guideSheet = workbook.addWorksheet('사용안내', { state: 'visible' });
  guideSheet.columns = [
    { header: '항목', key: 'item', width: 20 },
    { header: '설명', key: 'description', width: 60 },
  ];

  guideSheet.addRows([
    { item: '사용 방법', description: '1. 각 시트의 예시 데이터를 참고하여 데이터를 입력해주세요.' },
    { item: '', description: '2. "*" 표시된 필드는 필수 입력 항목입니다.' },
    { item: '', description: '3. 시리얼번호는 모든 시트에서 일관성 있게 사용해야 합니다.' },
    { item: '', description: '4. 순서는 1부터 시작하는 숫자로 입력해주세요.' },
    { item: '주의사항', description: '• 시리얼번호는 고유해야 합니다 (중복 불가)' },
    { item: '', description: '• 평가항목과 답변옵션의 시리얼번호는 평가표 시트에 있어야 합니다' },
    { item: '', description: '• 점수는 숫자만 입력 가능합니다' },
    { item: '', description: '• 순서는 각 그룹 내에서 연속된 숫자여야 합니다' },
  ]);

  // 안내 시트 스타일링
  const guideHeaderRow = guideSheet.getRow(1);
  guideHeaderRow.font = { bold: true, size: 12 };
  guideHeaderRow.fill = {
    type: 'pattern',
    pattern: 'solid',
    fgColor: { argb: 'FFF0F0F0' }
  };

  // 파일 다운로드
  const buffer = await workbook.xlsx.writeBuffer();
  const blob = new Blob([buffer], { 
    type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' 
  });
  
  const fileName = `ESG_평가표_템플릿_${new Date().toISOString().split('T')[0]}.xlsx`;
  saveAs(blob, fileName);
}

// ====================================================================
// Excel 파일 파싱
// ====================================================================

export async function parseEsgExcelFile(file: File): Promise<ParsedExcelData> {
  const workbook = new ExcelJS.Workbook();
  const arrayBuffer = await file.arrayBuffer();
  await workbook.xlsx.load(arrayBuffer);

  const result: ParsedExcelData = {
    evaluations: [],
    evaluationItems: [],
    answerOptions: [],
  };

  try {
    // 시트 1: 평가표 파싱
    const evaluationsSheet = workbook.getWorksheet('평가표') || workbook.getWorksheet(1);
    if (evaluationsSheet) {
      evaluationsSheet.eachRow((row, rowNumber) => {
        if (rowNumber === 1) return; // 헤더 스킵
        
        const serialNumber = row.getCell(1).value?.toString()?.trim();
        const category = row.getCell(2).value?.toString()?.trim();
        const inspectionItem = row.getCell(3).value?.toString()?.trim();

        if (serialNumber && category && inspectionItem) {
          result.evaluations.push({
            serialNumber,
            category,
            inspectionItem,
          });
        }
      });
    }

    // 시트 2: 평가항목 파싱
    const itemsSheet = workbook.getWorksheet('평가항목') || workbook.getWorksheet(2);
    if (itemsSheet) {
      itemsSheet.eachRow((row, rowNumber) => {
        if (rowNumber === 1) return; // 헤더 스킵
        
        const serialNumber = row.getCell(1).value?.toString()?.trim();
        const evaluationItem = row.getCell(2).value?.toString()?.trim();
        const evaluationItemDescription = row.getCell(3).value?.toString()?.trim();
        const orderIndex = parseInt(row.getCell(4).value?.toString() || '0');

        if (serialNumber && evaluationItem && !isNaN(orderIndex)) {
          result.evaluationItems.push({
            serialNumber,
            evaluationItem,
            evaluationItemDescription,
            orderIndex,
          });
        }
      });
    }

    // 시트 3: 답변옵션 파싱
    const optionsSheet = workbook.getWorksheet('답변옵션') || workbook.getWorksheet(3);
    if (optionsSheet) {
      optionsSheet.eachRow((row, rowNumber) => {
        if (rowNumber === 1) return; // 헤더 스킵
        
        const serialNumber = row.getCell(1).value?.toString()?.trim();
        const evaluationItem = row.getCell(2).value?.toString()?.trim();
        const answerText = row.getCell(3).value?.toString()?.trim();
        const score = parseFloat(row.getCell(4).value?.toString() || '0');
        const orderIndex = parseInt(row.getCell(5).value?.toString() || '0');

        if (serialNumber && evaluationItem && answerText && !isNaN(score) && !isNaN(orderIndex)) {
          result.answerOptions.push({
            serialNumber,
            evaluationItem,
            answerText,
            score,
            orderIndex,
          });
        }
      });
    }

    return result;
  } catch (error) {
    console.error('Excel parsing error:', error);
    throw new Error('Excel 파일을 파싱하는 중 오류가 발생했습니다.');
  }
}

// ====================================================================
// 데이터 검증
// ====================================================================

export function validateExcelData(data: ParsedExcelData): string[] {
  const errors: string[] = [];

  // 평가표 검증
  if (data.evaluations.length === 0) {
    errors.push('평가표 데이터가 없습니다.');
  }

  // 시리얼번호 중복 확인
  const serialNumbers = data.evaluations.map(e => e.serialNumber);
  const duplicateSerials = serialNumbers.filter((item, index) => serialNumbers.indexOf(item) !== index);
  if (duplicateSerials.length > 0) {
    errors.push(`중복된 시리얼번호가 있습니다: ${duplicateSerials.join(', ')}`);
  }

  // 평가항목 검증
  for (const item of data.evaluationItems) {
    if (!serialNumbers.includes(item.serialNumber)) {
      errors.push(`평가항목의 시리얼번호 '${item.serialNumber}'이 평가표에 없습니다.`);
    }
  }

  // 답변옵션 검증
  for (const option of data.answerOptions) {
    if (!serialNumbers.includes(option.serialNumber)) {
      errors.push(`답변옵션의 시리얼번호 '${option.serialNumber}'이 평가표에 없습니다.`);
    }
    
    const hasMatchingItem = data.evaluationItems.some(
      item => item.serialNumber === option.serialNumber && 
               item.evaluationItem === option.evaluationItem
    );
    
    if (!hasMatchingItem) {
      errors.push(`답변옵션의 평가항목 '${option.evaluationItem}'이 평가항목 시트에 없습니다.`);
    }
  }

  return errors;
}