path: root/lib/evaluation-criteria/service.ts

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

'use server';

/* IMPORT */
import {
  and,
  asc,
  desc,
  ilike,
  or,
} from 'drizzle-orm';
import {
  countRegEvalCriteria,
  deleteRegEvalCriteria,
  deleteRegEvalCriteriaDetails,
  insertRegEvalCriteria,
  insertRegEvalCriteriaDetails,
  selectRegEvalCriteria,
  selectRegEvalCriteriaWithDetails,
  updateRegEvalCriteria,
  updateRegEvalCriteriaDetails,
} from './repository';
import db from '@/db/db';
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,
  regEvalCriteriaView,
  type NewRegEvalCriteria,
  type NewRegEvalCriteriaDetails,
  type RegEvalCriteria,
  type RegEvalCriteriaDetails,
  type RegEvalCriteriaView,
} from '@/db/schema';
import { type GetRegEvalCriteriaSchema } from './validations';

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

/* 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 = 2;
const DATA_START_ROW_INDEX = 3;
const EXCEL_HEADERS = [
  'Category',
  'Score Category',
  'Item',
  'Classification',
  'Range',
  'Detail',
  'Remarks',
  'ID',
  'Criteria ID',
  'Order Index',
  'Equipment-Shipbuilding Score',
  'Equipment-Marine Engineering Score',
  'Bulk-Shipbuilding Score',
  'Bulk-Marine Engineering Score',
];

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

/* FUNCTION FOR GETTING CRITERIA */
async function getRegEvalCriteria(input: GetRegEvalCriteriaSchema) {
  try {
    const offset = (input.page - 1) * input.perPage;
    const advancedWhere = filterColumns({
      table: regEvalCriteriaView,
      filters: input.filters,
      joinOperator: input.joinOperator,
    });

    // Filtering
    let globalWhere;
    if (input.search) {
      const s = `%${input.search}%`;
      globalWhere = or(
        ilike(regEvalCriteriaView.category, s),
        ilike(regEvalCriteriaView.item, s),
        ilike(regEvalCriteriaView.classification, s),
      );
    }
    const finalWhere = and(advancedWhere, globalWhere);

    // Sorting
    const orderBy = input.sort.length > 0
      ? input.sort.map((item) => {
          return item.desc
            ? desc(regEvalCriteriaView[item.id])
            : asc(regEvalCriteriaView[item.id]);
        })
      : [asc(regEvalCriteriaView.id)];

    // Getting Data
    const { data, total } = await db.transaction(async (tx) => {
      const data = await selectRegEvalCriteria(tx, {
        where: finalWhere,
        orderBy,
        offset,
        limit: input.perPage,
      });
      const total = await countRegEvalCriteria(tx, finalWhere);

      return { data, total };
    });
    const pageCount = Math.ceil(total / input.perPage);
    return { data, pageCount };
  } catch (err) {
    console.error('Error in Getting Regular Evaluation Criteria: ', err);
    return { data: [], pageCount: 0 };
  }
}

/* FUNCTION FOR GETTING CRITERIA WITH DETAILS */
async function getRegEvalCriteriaWithDetails(id: number) {
  try {
    return await db.transaction(async (tx) => {
      return await selectRegEvalCriteriaWithDetails(tx, id);
    });
  } catch (err) {
    console.error('Error in Getting Regular Evaluation Criteria with Details: ', err);
    return null;
  }
}

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

/* FUNCTION FOR CREATING CRITERIA WITH DETAILS */
async function createRegEvalCriteriaWithDetails(
  criteriaData: NewRegEvalCriteria,
  detailList: Omit<NewRegEvalCriteriaDetails, 'criteriaId'>[],
) {
  try {
    return await db.transaction(async (tx) => {
      const criteria = await insertRegEvalCriteria(tx, criteriaData);
      const criteriaId = criteria.id;
      const newDetailList = detailList.map((detailItem, index) => ({
        ...detailItem,
        criteriaId,
        orderIndex: detailItem.orderIndex || index,
      }));

      const criteriaDetails: NewRegEvalCriteriaDetails[] = [];
      for (let idx = 0; idx < newDetailList.length; idx += 1) {
        criteriaDetails.push(await insertRegEvalCriteriaDetails(tx, newDetailList[idx]));
      }

      return { ...criteria, criteriaDetails };
    });
  } catch (error) {
    console.error('Error in Creating New Regular Evaluation Criteria with Details: ', error);
    throw new Error('Failed to Create New Regular Evaluation Criteria with Details');
  }
}

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

/* FUNCTION FOR MODIFYING CRITERIA WITH DETAILS */
async function modifyRegEvalCriteriaWithDetails(
  id: number,
  criteriaData: Partial<RegEvalCriteria>,
  detailList: Partial<RegEvalCriteriaDetails>[],
) {
  try {
    return await db.transaction(async (tx) => {
      const modifiedCriteria = await updateRegEvalCriteria(tx, id, criteriaData);
      const originCriteria = await getRegEvalCriteriaWithDetails(id);
      const originCriteriaDetails = originCriteria?.criteriaDetails || [];
      const detailIdList = detailList
        .filter(item => item.id !== undefined)
        .map(item => item.id);
      const toDeleteIdList = originCriteriaDetails.filter(
        (item) => !detailIdList.includes(item.id),
      );

      for (const item of toDeleteIdList) {
        await deleteRegEvalCriteriaDetails(tx, item.id);
      }

      const criteriaDetails = [];
      for (let idx = 0; idx < detailList.length; idx += 1) {
        const detailItem = detailList[idx];
        const isUpdate = detailItem.id;
        const isInsert = !detailItem.id && detailItem.detail;

        if (isUpdate) {
          const updatedDetail = await updateRegEvalCriteriaDetails(tx, detailItem.id!, detailItem);
          criteriaDetails.push(updatedDetail);
        } else if (isInsert) {
          const newDetailItem = {
            ...detailItem,
            criteriaId: id,
            detail: detailItem.detail!,
            orderIndex: detailItem.orderIndex || idx,
          };
          const insertedDetail = await insertRegEvalCriteriaDetails(tx, newDetailItem);
          criteriaDetails.push(insertedDetail);
        }
      }

      return { ...modifiedCriteria, criteriaDetails };
    });
  } catch (error) {
    console.error('Error in Modifying Regular Evaluation Criteria with Details: ', error);
    throw new Error('Failed to Modify Regular Evaluation Criteria with Details');
  }
}

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

/* FUNCTION FOR REMOVING CRITERIA WITH DETAILS */
async function removeRegEvalCriteria(id: number) {
  try {
    return await db.transaction(async (tx) => {
      return await deleteRegEvalCriteria(tx, id);
    });
  } catch (err) {
    console.error('Error in Removing Regular Evaluation Criteria with Details: ', err);
    throw new Error('Failed to Remove Regular Evaluation Criteria with Details');
  }
}

/* FUNCTION FOR REMOVING CRITERIA DETAILS */
async function removeRegEvalCriteriaDetails(id: number) {
  try {
    return await db.transaction(async (tx) => {
      return await deleteRegEvalCriteriaDetails(tx, id);
    });
  } catch (err) {
    console.error('Error in Removing Regular Evaluation Criteria Details: ', err);
    throw new Error('Failed to Remove Regular Evaluation Criteria Details');
  }
}

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

/* FUNCTION FOR IMPORTING EXCEL FILES */
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 || headerRow.cellCount < EXCEL_HEADERS.length || !Array.isArray(headerRow.values)) {
      throw new Error('Excel 파일의 워크시트에서 유효한 헤더 행을 찾지 못했습니다.');
    }
    const headerValues = headerRow?.values?.slice(1);
    const isHeaderMatched = EXCEL_HEADERS.every((header, idx) => {
      const actualHeader = (headerValues[idx] ?? '').toString().trim();
      return actualHeader === header;
    });
    if (!isHeaderMatched) {
      throw new Error('Excel 파일의 워크시트에서 유효한 헤더 행을 찾지 못했습니다.');
    }

    const columnIndexMap = new Map<string, number>();
    headerRow.eachCell((cell, colIndex) => {
      if (typeof cell.value === 'string') {
        columnIndexMap.set(cell.value.trim(), colIndex);
      }
    });

    const columnToFieldMap = new Map<number, keyof RegEvalCriteriaView>();
    regEvalCriteriaColumnsConfig.forEach((cfg) => {
      if (!cfg.excelHeader) {
        return;
      }
      const colIndex = columnIndexMap.get(cfg.excelHeader.trim());
      if (colIndex !== undefined) {
        columnToFieldMap.set(colIndex, cfg.id);
      }
    });
    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((fieldId, colIdx) => {
        const cellValue = row.getCell(colIdx).value;
        rowFields[fieldId] = 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,
      });
    });
    // console.log('원본 데이터: ');
    // console.dir(rowDataList, { depth: null });

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

    // console.log('기존 데이터: ');
    // console.dir(existingData, { depth: null });

    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.category2 !== 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})가 있습니다.`);
      }
    }
    console.log('생성: ');
    console.dir(createList, { depth: null });
    console.log('업뎃: ');
    console.dir(updateList, { depth: null });
    console.log('삭제: ', deleteIdList);
    
    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,
    };
  }
}

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

/* EXPORT */
export {
  createRegEvalCriteriaWithDetails,
  modifyRegEvalCriteriaWithDetails,
  getRegEvalCriteria,
  getRegEvalCriteriaWithDetails,
  importRegEvalCriteriaExcel,
  removeRegEvalCriteria,
  removeRegEvalCriteriaDetails,
};