path: root/lib/evaluation-criteria/excel/reg-eval-criteria-excel-import.ts

/* eslint-disable @typescript-eslint/no-explicit-any */

'use server';

/* IMPORT */
import * as ExcelJS from 'exceljs';
import { filterColumns } from '@/lib/filter-columns';
import {
  regEvalCriteriaColumnsConfig,
} from '@/config/regEvalCriteriaColumnsConfig';
import {
  REG_EVAL_CRITERIA_CATEGORY2_ENUM,
  REG_EVAL_CRITERIA_CATEGORY_ENUM,
  REG_EVAL_CRITERIA_ITEM_ENUM,
  REG_EVAL_CRITERIA_CATEGORY,
  REG_EVAL_CRITERIA_CATEGORY2,
  REG_EVAL_CRITERIA_ITEM,
  type NewRegEvalCriteria,
  type NewRegEvalCriteriaDetails,
  type RegEvalCriteria,
  type RegEvalCriteriaDetails,
  type RegEvalCriteriaView,
} from '@/db/schema';
import {
  createRegEvalCriteriaWithDetails,
  modifyRegEvalCriteriaWithDetails,
  removeRegEvalCriteria,
  getRegEvalCriteria,
} from '../service';
import db from '@/db/db';
import { selectRegEvalCriteria } from '../repository';

// ----------------------------------------------------------------------------------------------------

/* TYPES */
interface ImportResult {
  errorFile: File | null,
  errorMessage: string | null,
  successMessage?: string,
}
type ExcelRowData = {
  criteriaData: NewRegEvalCriteria,
  detailList: (NewRegEvalCriteriaDetails & { rowIndex: number, toDelete: boolean })[],
}

// ----------------------------------------------------------------------------------------------------

/* CONSTANTS */
const HEADER_ROW_INDEX = 1;
const DATA_START_ROW_INDEX = 2;

// 영문과 한글 헤더 매핑
const HEADER_MAPPING: Record<string, string> = {
  // 영문 헤더
  'Category': 'category',
  'Score Category': 'category2',
  'Item': 'item',
  'Classification': 'classification',
  'Range': 'range',
  'Detail': 'detail',
  'Remarks': 'remarks',
  'ID': 'id',
  'Criteria ID': 'criteriaId',
  'Order Index': 'orderIndex',
  'Equipment-Shipbuilding Score': 'scoreEquipShip',
  'Equipment-Marine Engineering Score': 'scoreEquipMarine',
  'Bulk-Shipbuilding Score': 'scoreBulkShip',
  'Bulk-Marine Engineering Score': 'scoreBulkMarine',
  
  // 한글 헤더
  '평가부문': 'category',
  '점수구분': 'category2', 
  '항목': 'item',
  '구분': 'classification',
  '범위': 'range',
  '평가내용': 'detail',
  '비고': 'remarks',
  '기준 ID': 'criteriaId',
  '정렬 순서': 'orderIndex',
  '장비-조선 점수': 'scoreEquipShip',
  '장비-해양 점수': 'scoreEquipMarine',
  '벌크-조선 점수': 'scoreBulkShip',
  '벌크-해양 점수': 'scoreBulkMarine',
};

// ----------------------------------------------------------------------------------------------------

/* FUNCTION FOR IMPORTING EXCEL FILES */
export async function importRegEvalCriteriaExcel(file: File): Promise<ImportResult> {
  try {
    const buffer = await file.arrayBuffer();
    const workbook = new ExcelJS.Workbook();
    try {
      await workbook.xlsx.load(buffer);
    } catch {
      throw new Error('유효한 Excel 파일이 아닙니다. 파일을 다시 확인해주세요.');
    }

    const worksheet = workbook.worksheets[0];
    if (!worksheet) {
      throw new Error('Excel 파일에 워크시트가 없습니다.');
    };
    if (worksheet.rowCount === 0) {
      throw new Error('워크시트에 데이터가 없습니다.');
    }

    const headerRow = worksheet.getRow(HEADER_ROW_INDEX);
    if (!headerRow || !Array.isArray(headerRow.values)) {
      throw new Error('Excel 파일의 워크시트에서 유효한 헤더 행을 찾지 못했습니다.');
    }

    // 헤더 매핑 생성
    const columnToFieldMap = new Map<number, string>();
    headerRow.eachCell((cell, colIndex) => {
      if (typeof cell.value === 'string') {
        const headerValue = cell.value.trim();
        const fieldName = HEADER_MAPPING[headerValue];
        if (fieldName) {
          columnToFieldMap.set(colIndex, fieldName);
        }
      }
    });

    // 필수 헤더 확인
    const requiredFields = ['category', 'category2', 'item', 'classification', 'detail'];
    const foundFields = new Set(columnToFieldMap.values());
    const missingFields = requiredFields.filter(field => !foundFields.has(field));
    
    if (missingFields.length > 0) {
      throw new Error(`필수 헤더가 누락되었습니다: ${missingFields.join(', ')}`);
    }
    const errorRows: { rowIndex: number; message: string }[] = [];
    const rowDataList: ExcelRowData[] = [];
    const criteriaMap = new Map<string, {
      criteria: NewRegEvalCriteria,
      criteriaDetails: (NewRegEvalCriteriaDetails & { rowIndex: number, toDelete: boolean })[],
    }>();

    for (let r = DATA_START_ROW_INDEX; r <= worksheet.rowCount; r += 1) {
      const row = worksheet.getRow(r);
      if (!row) {
        continue;
      }
      
      const lastCellValue = row.getCell(row.cellCount).value;
      const isDelete = typeof lastCellValue === 'string' && lastCellValue.toLowerCase() === 'd';

      const rowFields = {} as Record<string, any>;
      columnToFieldMap.forEach((fieldName, colIdx) => {
        let cellValue = row.getCell(colIdx).value;
        
        // 한글 라벨을 영문 값으로 변환
        if (fieldName === 'category' && typeof cellValue === 'string') {
          const found = REG_EVAL_CRITERIA_CATEGORY.find(item => item.label === cellValue?.toString().trim());
          cellValue = found ? found.value : cellValue;
        } else if (fieldName === 'category2' && typeof cellValue === 'string') {
          const found = REG_EVAL_CRITERIA_CATEGORY2.find(item => item.label === cellValue?.toString().trim());
          cellValue = found ? found.value : cellValue;
        } else if (fieldName === 'item' && typeof cellValue === 'string') {
          const found = REG_EVAL_CRITERIA_ITEM.find(item => item.label === cellValue?.toString().trim());
          cellValue = found ? found.value : cellValue;
        }
        
        rowFields[fieldName] = cellValue ?? null;
      });

      const requiredFields = ['category', 'category2', 'item', 'classification', 'detail'];
      for (const field of requiredFields) {
        if (!rowFields[field]) {
          errorRows.push({ rowIndex: r, message: `필수 필드 누락: ${field}` });
        }
      }

      if (!REG_EVAL_CRITERIA_CATEGORY_ENUM.includes(rowFields.category)) {
        errorRows.push({ rowIndex: r, message: `유효하지 않은 Category 값: ${rowFields.category}` });
      }
    
      if (!REG_EVAL_CRITERIA_CATEGORY2_ENUM.includes(rowFields.category2)) {
        errorRows.push({ rowIndex: r, message: `유효하지 않은 Score Category 값: ${rowFields.category2}` });
      }
    
      if (!REG_EVAL_CRITERIA_ITEM_ENUM.includes(rowFields.item)) {
        errorRows.push({ rowIndex: r, message: `유효하지 않은 Item 값: ${rowFields.item}` });
      }

      const criteriaKey = [
        rowFields.criteriaId ?? '',
        rowFields.category,
        rowFields.category2,
        rowFields.item,
        rowFields.classification,
        rowFields.range ?? '',
      ].join('|');
    
      const criteriaDetail: NewRegEvalCriteriaDetails = {
        id: rowFields.id,
        criteriaId: rowFields.criteriaId,
        detail: rowFields.detail,
        orderIndex: rowFields.orderIndex,
        scoreEquipShip: rowFields.scoreEquipShip,
        scoreEquipMarine: rowFields.scoreEquipMarine,
        scoreBulkShip: rowFields.scoreBulkShip,
        scoreBulkMarine: rowFields.scoreBulkMarine,
      };
    
      if (!criteriaMap.has(criteriaKey)) {
        const criteria: NewRegEvalCriteria = {
          id: rowFields.criteriaId,
          category: rowFields.category,
          category2: rowFields.category2,
          item: rowFields.item,
          classification: rowFields.classification,
          range: rowFields.range,
          remarks: rowFields.remarks,
        };
    
        criteriaMap.set(criteriaKey, {
          criteria,
          criteriaDetails: [{
            ...criteriaDetail,
            rowIndex: r,
            toDelete: isDelete,
          }],
        });
      } else {
        const existing = criteriaMap.get(criteriaKey)!;
        existing.criteriaDetails.push({
          ...criteriaDetail,
          rowIndex: r,
          toDelete: isDelete,
        });
      }
    }

    criteriaMap.forEach(({ criteria, criteriaDetails }) => {
      rowDataList.push({
        criteriaData: criteria,
        detailList: criteriaDetails,
      });
    });

    if (errorRows.length > 0) {
      const workbook = new ExcelJS.Workbook();
      const sheet = workbook.addWorksheet('Error List');
      sheet.columns = [
        { header: 'Row Index', key: 'rowIndex', width: 10 },
        { header: 'Error Message', key: 'message', width: 50 },
      ];
      errorRows.forEach((errorRow) => {
        sheet.addRow({
          rowIndex: errorRow.rowIndex,
          message: errorRow.message,
        });
      });
      const buffer = await workbook.xlsx.writeBuffer();
      const errorFile = new File(
        [buffer],
        'error_rows.xlsx',
        {
          type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
          lastModified: Date.now(),
        }
      );

      return {
        errorFile,
        errorMessage: '입력된 데이터 중에서 잘못된 데이터가 있어 오류 파일을 생성했습니다.',
      };
    }

    const existingData = await db.transaction(async (tx) => {
      return await selectRegEvalCriteria(tx, { limit: Number.MAX_SAFE_INTEGER });
    });
    const existingIds = existingData.map((row) => row.criteriaId!)
    const existingIdSet = new Set<number>(existingIds);

    const createList: {
      criteriaData: NewRegEvalCriteria,
      detailList: Omit<NewRegEvalCriteriaDetails, 'criteriaId'>[],
    }[] = [];
    const updateList: {
      id: number,
      criteriaData: Partial<RegEvalCriteria>,
      detailList: Partial<RegEvalCriteriaDetails>[],
    }[] = [];
    const deleteIdList: number[] = [];

    for (const { criteriaData, detailList } of rowDataList) {
      const { id: criteriaId } = criteriaData;
      const allMarkedForDelete = detailList.every(d => d.toDelete);
      if (allMarkedForDelete) {
        if (criteriaId && existingIdSet.has(criteriaId)) {
          deleteIdList.push(criteriaId);
        }
        continue;
      }
    
      if (!criteriaId) {
        // eslint-disable-next-line @typescript-eslint/no-unused-vars
        const { id, ...newCriteriaData } = criteriaData;
        const newDetailList = detailList.map(d => {
          if (d.id != null) {
            throw new Error(`새로운 기준 항목에 ID가 존재합니다: ${d.rowIndex}행`);
          }
          // eslint-disable-next-line @typescript-eslint/no-unused-vars
          const { rowIndex, toDelete, id, criteriaId, ...rest } = d;
          return rest;
        });
    
        createList.push({
          criteriaData: newCriteriaData,
          detailList: newDetailList,
        });
      } else if (existingIdSet.has(criteriaId)) {
        const matchedExistingDetails = existingData.filter(d => d.criteriaId === criteriaId);
        const hasDeletedDetail = detailList.some(d => d.toDelete === true);
        const hasNewDetail = detailList.some(d => d.id == null);
        const matchedExistingCriteria = matchedExistingDetails[0];
        const criteriaChanged = (
          matchedExistingCriteria.category !== criteriaData.category ||
          matchedExistingCriteria.scoreCategory !== criteriaData.category2 ||
          matchedExistingCriteria.item !== criteriaData.item ||
          matchedExistingCriteria.classification !== criteriaData.classification ||
          matchedExistingCriteria.range !== criteriaData.range ||
          matchedExistingCriteria.remarks !== criteriaData.remarks
        );
        const detailChanged = detailList.some(d => {
          if (!d.id) {
            return false;
          }
          const matched = matchedExistingDetails.find(e => e.id === d.id);
          if (!matched) {
            throw Error(`존재하지 않는 잘못된 ID(${d.id})가 있습니다.`);
          }
          return (
            matched.detail !== d.detail ||
            matched.orderIndex !== d.orderIndex ||
            matched.scoreEquipShip !== d.scoreEquipShip ||
            matched.scoreEquipMarine !== d.scoreEquipMarine ||
            matched.scoreBulkShip !== d.scoreBulkShip ||
            matched.scoreBulkMarine !== d.scoreBulkMarine
          );
        });

        if (hasDeletedDetail || hasNewDetail || criteriaChanged || detailChanged) {
          const updatedDetails = detailList
            .filter(d => !d.toDelete)
            .map(d => {
              // eslint-disable-next-line @typescript-eslint/no-unused-vars
              const { rowIndex, toDelete, ...rest } = d;
              const cleaned = Object.fromEntries(
                Object.entries(rest).map(([key, value]) => [
                  key,
                  value === '' ? null : value,
                ])
              );
              return cleaned;
            });

          updateList.push({
            id: criteriaId,
            criteriaData,
            detailList: updatedDetails,
          });
        }
      } else {
        throw Error(`존재하지 않는 잘못된 Criteria ID(${criteriaId})가 있습니다.`);
      }
    }
    
    if (createList.length > 0) {
      for (const { criteriaData, detailList } of createList) {
        await createRegEvalCriteriaWithDetails(criteriaData, detailList);
      }
    }
    if (updateList.length > 0) {
      for (const { id, criteriaData, detailList } of updateList) {
        await modifyRegEvalCriteriaWithDetails(id, criteriaData, detailList);
      }
    }
    if (deleteIdList.length > 0) {
      for (const id of deleteIdList) {
        await removeRegEvalCriteria(id);
      }
    }

    const msg: string[] = [];
    if (createList.length > 0) {
      msg.push(`${createList.length}건 생성`);
    }
    if (updateList.length > 0) {
      msg.push(`${updateList.length}건 수정`);
    }
    if (deleteIdList.length > 0) {
      msg.push(`${deleteIdList.length}건 삭제`);
    }
    const successMessage = msg.length > 0
      ? '기준 항목이 정상적으로 ' + msg.join(', ') + '되었습니다.'
      : '변경사항이 존재하지 않습니다.';

    return {
      errorFile: null,
      errorMessage: null,
      successMessage,
    };
  } catch (error) {
    let message = 'Excel 파일을 읽는 중 오류가 발생했습니다.';
    if (error instanceof Error) {
      message = error.message;
    }

    return {
      errorFile: null,
      errorMessage: message,
    };
  }
}