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

'use server';

/* IMPORT */
import {
  and,
  asc,
  desc,
  ilike,
  or,count, eq
} from 'drizzle-orm';
import {
  deleteRegEvalCriteria,
  deleteRegEvalCriteriaDetails,
  insertRegEvalCriteria,
  insertRegEvalCriteriaDetails,
  selectRegEvalCriteriaWithDetails,
  updateRegEvalCriteria,
  updateRegEvalCriteriaDetails,
} from './repository';
import db from '@/db/db';
import { filterColumns } from '@/lib/filter-columns';
import { 
  regEvalCriteria, regEvalCriteriaDetails,
  type NewRegEvalCriteria,
  type NewRegEvalCriteriaDetails,
  type RegEvalCriteria,
  type RegEvalCriteriaDetails,
} from '@/db/schema';
import { type GetRegEvalCriteriaSchema } from './validations';
import { getServerSession } from 'next-auth';
import { authOptions } from '@/app/api/auth/[...nextauth]/route';

/* EXCEL EXPORT SERVER ACTIONS */
import ExcelJS from "exceljs";
import { REG_EVAL_CRITERIA_CATEGORY, REG_EVAL_CRITERIA_CATEGORY2, REG_EVAL_CRITERIA_ITEM } from "@/db/schema";


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

/* TYPES */

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

/* HELPER FUNCTION FOR GETTING CURRENT USER ID */
async function getCurrentUserId(): Promise<number> {
  try {
    const session = await getServerSession(authOptions);
    return session?.user?.id ? Number(session.user.id) : 3; // 기본값 3, 실제 환경에서는 적절한 기본값 설정
  } catch (error) {
    console.error('Error getting current user ID:', error);
    return 3; // 기본값 3
  }
}

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

/* FUNCTION FOR GETTING CRITERIA - 메인 기준 목록만 가져오기 */
async function getRegEvalCriteria(input: GetRegEvalCriteriaSchema) {
  try {
    const offset = (input.page - 1) * input.perPage;
    const advancedWhere = filterColumns({
      table: regEvalCriteria, // view 대신 메인 테이블 사용
      filters: input.filters,
      joinOperator: input.joinOperator,
    });

    // Filtering - 메인 테이블 컬럼들 기준으로 검색
    let globalWhere;
    if (input.search) {
      const s = `%${input.search}%`;
      globalWhere = or(
        ilike(regEvalCriteria.category, s),
        ilike(regEvalCriteria.item, s),
        ilike(regEvalCriteria.classification, s),
        ilike(regEvalCriteria.range, s), // range 검색 추가
      );
    }
    const finalWhere = and(advancedWhere, globalWhere);

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

    // Getting Data - 메인 기준 데이터만 가져오기
    const { data, total } = await db.transaction(async (tx) => {
      const data = await tx
        .select()
        .from(regEvalCriteria)
        .where(finalWhere)
        .orderBy(...orderBy)
        .offset(offset)
        .limit(input.perPage);
      
      const totalResult = await tx
      .select({ count: count() })
        .from(regEvalCriteria)
        .where(finalWhere);
      
      const total = totalResult[0]?.count || 0;

      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 GETTING CRITERIA DETAILS ONLY - 특정 기준의 상세 항목들만 가져오기 */
async function getRegEvalCriteriaDetails(criteriaId: number) {
  try {
    return await db.transaction(async (tx) => {
      const details = await tx
        .select()
        .from(regEvalCriteriaDetails)
        .where(eq(regEvalCriteriaDetails.criteriaId, criteriaId))
        .orderBy(asc(regEvalCriteriaDetails.orderIndex));
      
      return details;
    });
  } catch (err) {
    console.error('Error in Getting Regular Evaluation Criteria Details: ', err);
    return [];
  }
}

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

/* FUNCTION FOR CREATING CRITERIA WITH FIXED SCORES */
async function createRegEvalCriteriaFixed(
  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 (Fixed): ', error);
    throw new Error('Failed to Create New Regular Evaluation Criteria (Fixed)');
  }
}

/* FUNCTION FOR CREATING CRITERIA WITH VARIABLE SCORES */
async function createRegEvalCriteriaVariable(
  criteriaData: NewRegEvalCriteria,
) {
  try {
    return await db.transaction(async (tx) => {
      const criteria = await insertRegEvalCriteria(tx, {
        ...criteriaData,
      });

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

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

/* FUNCTION FOR MODIFYING CRITERIA WITH FIXED SCORES */
async function modifyRegEvalCriteriaFixed(
  id: number,
  criteriaData: Partial<RegEvalCriteria>,
  detailList: Partial<RegEvalCriteriaDetails>[],
) {
  try {
    const currentUserId = await getCurrentUserId();
    
    return await db.transaction(async (tx) => {
      // 고정 점수용 criteria 업데이트 (variableScore 필드는 null로 설정)
      const modifiedCriteria = await updateRegEvalCriteria(tx, id, {
        ...criteriaData,
        variableScoreMin: null,
        variableScoreMax: null,
        variableScoreUnit: null,
        updatedBy: currentUserId,
      });

      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 Fixed Scores: ', error);
    throw new Error('Failed to Modify Regular Evaluation Criteria with Fixed Scores');
  }
}

/* FUNCTION FOR MODIFYING CRITERIA WITH VARIABLE SCORES */
async function modifyRegEvalCriteriaVariable(
  id: number,
  criteriaData: Partial<RegEvalCriteria>,
) {
  try {
    const currentUserId = await getCurrentUserId();
    
    return await db.transaction(async (tx) => {
      // 변동 점수용 criteria 업데이트 (기존 details 모두 삭제)
      const modifiedCriteria = await updateRegEvalCriteria(tx, id, {
        ...criteriaData,
        updatedBy: currentUserId,
      });

      // 변동 점수에서는 기존 모든 세부사항 삭제
      const originCriteria = await getRegEvalCriteriaWithDetails(id);
      const originCriteriaDetails = originCriteria?.criteriaDetails || [];
      
      for (const item of originCriteriaDetails) {
        await deleteRegEvalCriteriaDetails(tx, item.id);
      }

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

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

/* 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 GENERATING EXCEL FILE FROM DATABASE */
async function generateRegEvalCriteriaExcel(): Promise<ArrayBuffer> {
  try {
    const workbook = new ExcelJS.Workbook();
    workbook.creator = "EVCP System";
    workbook.created = new Date();
    
    const worksheet = workbook.addWorksheet("평가 기준");

    const columnHeaders = [
      { key: "category", header: "평가부문", width: 15 },
      { key: "category2", header: "점수구분", width: 15 },
      { key: "item", header: "항목", width: 15 },
      { key: "classification", header: "구분", width: 20 },
      { key: "range", header: "범위", width: 20 },
      { key: "remarks", header: "비고", width: 20 },
      { key: "scoreType", header: "점수 유형", width: 15 },
      { key: "variableScoreMin", header: "최소 점수", width: 15 },
      { key: "variableScoreMax", header: "최대 점수", width: 15 },
      { key: "variableScoreUnit", header: "단위", width: 15 },
      { key: "detail", header: "평가내용", width: 30 },
      { key: "scoreEquipShip", header: "기자재-조선", width: 18 },
      { key: "scoreEquipMarine", header: "기자재-해양", width: 18 },
      { key: "scoreBulkShip", header: "벌크-조선", width: 18 },
      { key: "scoreBulkMarine", header: "벌크-해양", width: 18 },
      { key: "orderIndex", header: "정렬 순서", width: 12 },
    ];

    worksheet.columns = columnHeaders;

    const headerRow = worksheet.getRow(1);
    headerRow.font = { bold: true };
    headerRow.fill = {
      type: "pattern",
      pattern: "solid",
      fgColor: { argb: "FFCCCCCC" },
    };
    headerRow.alignment = { horizontal: "center", vertical: "middle" };

    // 실제 DB에서 1:n 관계 데이터 가져오기
    const allCriteria = await db.transaction(async (tx) => {
      const criteria = await tx
        .select()
        .from(regEvalCriteria)
        .orderBy(asc(regEvalCriteria.id));

      const result: any[] = [];
      for (const criterion of criteria) {
        const details = await tx
          .select()
          .from(regEvalCriteriaDetails)
          .where(eq(regEvalCriteriaDetails.criteriaId, criterion.id))
          .orderBy(asc(regEvalCriteriaDetails.orderIndex));

        if (details.length === 0) {
          result.push({
            category: criterion.category,
            category2: criterion.category2,
            item: criterion.item,
            classification: criterion.classification,
            range: criterion.range || '',
            remarks: criterion.remarks || '',
            scoreType: criterion.scoreType === 'fixed' ? '고정' : '변동',
            variableScoreMin: criterion.variableScoreMin || '',
            variableScoreMax: criterion.variableScoreMax || '',
            variableScoreUnit: criterion.variableScoreUnit || '',
            detail: '',
            scoreEquipShip: '',
            scoreEquipMarine: '',
            scoreBulkShip: '',
            scoreBulkMarine: '',
            orderIndex: '',
          });
        } else {
          for (const detail of details) {
            result.push({
              category: criterion.category,
              category2: criterion.category2,
              item: criterion.item,
              classification: criterion.classification,
              range: criterion.range || '',
              remarks: criterion.remarks || '',
              scoreType: criterion.scoreType === 'fixed' ? '고정' : '변동',
              variableScoreMin: criterion.variableScoreMin || '',
              variableScoreMax: criterion.variableScoreMax || '',
              variableScoreUnit: criterion.variableScoreUnit || '',
              detail: detail.detail,
              scoreEquipShip: detail.scoreEquipShip || '',
              scoreEquipMarine: detail.scoreEquipMarine || '',
              scoreBulkShip: detail.scoreBulkShip || '',
              scoreBulkMarine: detail.scoreBulkMarine || '',
              orderIndex: detail.orderIndex?.toString() || '',
            });
          }
        }
      }
      return result;
    });

    allCriteria.forEach((row) => {
      const rowData: Record<string, unknown> = {};
      columnHeaders.forEach((col) => {
        let value = row[col.key as keyof typeof row];
        
        if (col.key === "category") {
          value = REG_EVAL_CRITERIA_CATEGORY.find(item => item.value === value)?.label || value;
        } else if (col.key === "category2") {
          value = REG_EVAL_CRITERIA_CATEGORY2.find(item => item.value === value)?.label || value;
        } else if (col.key === "item") {
          value = REG_EVAL_CRITERIA_ITEM.find(item => item.value === value)?.label || value;
        }
        
        rowData[col.key] = value || "";
      });
      worksheet.addRow(rowData);
    });

    // 데이터 검증을 위한 숨겨진 시트 생성
    const validationSheet = workbook.addWorksheet("ValidationData");
    validationSheet.state = "hidden";

    const categoryOptions = REG_EVAL_CRITERIA_CATEGORY.map(item => item.label);
    const category2Options = REG_EVAL_CRITERIA_CATEGORY2.map(item => item.label);
    const itemOptions = REG_EVAL_CRITERIA_ITEM.map(item => item.label);
    const scoreTypeOptions = ['고정', '변동'];

    validationSheet.getColumn(1).values = ["평가부문", ...categoryOptions];
    validationSheet.getColumn(2).values = ["점수구분", ...category2Options];
    validationSheet.getColumn(3).values = ["항목", ...itemOptions];
    validationSheet.getColumn(4).values = ["점수 유형", ...scoreTypeOptions];

    // 드롭다운 설정
    const categoryColIndex = columnHeaders.findIndex(col => col.key === "category") + 1;
    const category2ColIndex = columnHeaders.findIndex(col => col.key === "category2") + 1;
    const itemColIndex = columnHeaders.findIndex(col => col.key === "item") + 1;
    const scoreTypeColIndex = columnHeaders.findIndex(col => col.key === "scoreType") + 1;

    const dataRowCount = allCriteria.length + 1; // 헤더 포함
    const maxRows = Math.max(dataRowCount + 50, 1000); // 추가 행을 위한 여유

    if (categoryColIndex > 0) {
      (worksheet as any).dataValidations.add(`${worksheet.getColumn(categoryColIndex).letter}2:${worksheet.getColumn(categoryColIndex).letter}${maxRows}`, {
        type: "list",
        allowBlank: false,
        formulae: [`ValidationData!$A$2:$A$${categoryOptions.length + 1}`],
      });
    }

    if (category2ColIndex > 0) {
      (worksheet as any).dataValidations.add(`${worksheet.getColumn(category2ColIndex).letter}2:${worksheet.getColumn(category2ColIndex).letter}${maxRows}`, {
        type: "list",
        allowBlank: false,
        formulae: [`ValidationData!$B$2:$B$${category2Options.length + 1}`],
      });
    }

    if (itemColIndex > 0) {
      (worksheet as any).dataValidations.add(`${worksheet.getColumn(itemColIndex).letter}2:${worksheet.getColumn(itemColIndex).letter}${maxRows}`, {
        type: "list",
        allowBlank: false,
        formulae: [`ValidationData!$C$2:$C$${itemOptions.length + 1}`],
      });
    }

    if (scoreTypeColIndex > 0) {
      (worksheet as any).dataValidations.add(`${worksheet.getColumn(scoreTypeColIndex).letter}2:${worksheet.getColumn(scoreTypeColIndex).letter}${maxRows}`, {
        type: "list",
        allowBlank: false,
        formulae: [`ValidationData!$D$2:$D$${scoreTypeOptions.length + 1}`],
      });
    }

    worksheet.eachRow((row) => {
      row.eachCell((cell) => {
        cell.border = {
          top: { style: "thin" },
          left: { style: "thin" },
          bottom: { style: "thin" },
          right: { style: "thin" },
        };
      });
    });

    return await workbook.xlsx.writeBuffer() as ArrayBuffer;
  } catch (error) {
    console.error('Error in generating Excel file:', error);
    throw new Error('Failed to generate Excel file');
  }
}

/* FUNCTION FOR GENERATING EXCEL TEMPLATE */
async function generateRegEvalCriteriaTemplate(): Promise<ArrayBuffer> {
  try {
    const workbook = new ExcelJS.Workbook();
    workbook.creator = "EVCP System";
    workbook.created = new Date();
    
    const worksheet = workbook.addWorksheet("평가 기준 템플릿");

    const templateHeaders = [
      { key: "category", header: "평가부문", width: 15 },
      { key: "category2", header: "점수구분", width: 15 },
      { key: "item", header: "항목", width: 15 },
      { key: "classification", header: "구분", width: 20 },
      { key: "range", header: "범위", width: 20 },
      { key: "remarks", header: "비고", width: 20 },
      { key: "scoreType", header: "점수 유형", width: 15 },
      { key: "variableScoreMin", header: "최소 점수", width: 15 },
      { key: "variableScoreMax", header: "최대 점수", width: 15 },
      { key: "variableScoreUnit", header: "단위", width: 15 },
      { key: "detail", header: "평가내용", width: 30 },
      { key: "scoreEquipShip", header: "기자재-조선", width: 18 },
      { key: "scoreEquipMarine", header: "기자재-해양", width: 18 },
      { key: "scoreBulkShip", header: "벌크-조선", width: 18 },
      { key: "scoreBulkMarine", header: "벌크-해양", width: 18 },
      { key: "orderIndex", header: "정렬 순서", width: 12 },
    ];

    worksheet.columns = templateHeaders;

    const headerRow = worksheet.getRow(1);
    headerRow.font = { bold: true };
    headerRow.fill = {
      type: "pattern",
      pattern: "solid",
      fgColor: { argb: "FFCCCCCC" },
    };
    headerRow.alignment = { horizontal: "center", vertical: "middle" };

    const exampleData = [
      {
        category: "품질",
        category2: "공정",
        item: "품질",
        classification: "품질관리시스템",
        range: "ISO 9001 인증",
        remarks: "국제 표준 품질관리시스템 인증",
        scoreType: "고정",
        variableScoreMin: "",
        variableScoreMax: "",
        variableScoreUnit: "",
        detail: "우수 (ISO 9001 인증보유)",
        scoreEquipShip: "10",
        scoreEquipMarine: "10",
        scoreBulkShip: "8",
        scoreBulkMarine: "8",
        orderIndex: "1",
      },
      {
        category: "품질",
        category2: "공정",
        item: "품질",
        classification: "품질관리시스템",
        range: "ISO 9001 인증",
        remarks: "국제 표준 품질관리시스템 인증",
        scoreType: "고정",
        variableScoreMin: "",
        variableScoreMax: "",
        variableScoreUnit: "",
        detail: "보통 (자체 품질관리시스템)",
        scoreEquipShip: "6",
        scoreEquipMarine: "6",
        scoreBulkShip: "5",
        scoreBulkMarine: "5",
        orderIndex: "2",
      },
      {
        category: "품질",
        category2: "공정",
        item: "품질",
        classification: "품질관리시스템",
        range: "ISO 9001 인증",
        remarks: "국제 표준 품질관리시스템 인증",
        scoreType: "고정",
        variableScoreMin: "",
        variableScoreMax: "",
        variableScoreUnit: "",
        detail: "미흡 (품질관리시스템 미비)",
        scoreEquipShip: "2",
        scoreEquipMarine: "2",
        scoreBulkShip: "1",
        scoreBulkMarine: "1",
        orderIndex: "3",
      },
      {
        category: "조달",
        category2: "가격",
        item: "납기",
        classification: "납기준수율",
        range: "최근 3년간 납기준수율",
        remarks: "계약대비 실제 납기준수 비율",
        scoreType: "변동",
        variableScoreMin: "0",
        variableScoreMax: "20",
        variableScoreUnit: "점",
        detail: "",
        scoreEquipShip: "",
        scoreEquipMarine: "",
        scoreBulkShip: "",
        scoreBulkMarine: "",
        orderIndex: "",
      },
    ];

    exampleData.forEach((row) => {
      worksheet.addRow(row);
    });


    const validationSheet = workbook.addWorksheet("ValidationData");
    validationSheet.state = "hidden";

    const categoryOptions = REG_EVAL_CRITERIA_CATEGORY.map(item => item.label);
    const category2Options = REG_EVAL_CRITERIA_CATEGORY2.map(item => item.label);
    const itemOptions = REG_EVAL_CRITERIA_ITEM.map(item => item.label);
    const scoreTypeOptions = ['고정', '변동'];

    validationSheet.getColumn(1).values = ["평가부문", ...categoryOptions];
    validationSheet.getColumn(2).values = ["점수구분", ...category2Options];
    validationSheet.getColumn(3).values = ["항목", ...itemOptions];
    validationSheet.getColumn(4).values = ["점수 유형", ...scoreTypeOptions];

    const categoryColIndex = templateHeaders.findIndex(col => col.key === "category") + 1;
    const category2ColIndex = templateHeaders.findIndex(col => col.key === "category2") + 1;
    const itemColIndex = templateHeaders.findIndex(col => col.key === "item") + 1;
    const scoreTypeColIndex = templateHeaders.findIndex(col => col.key === "scoreType") + 1;

    if (categoryColIndex > 0) {
      (worksheet as any).dataValidations.add(`${worksheet.getColumn(categoryColIndex).letter}2:${worksheet.getColumn(categoryColIndex).letter}1000`, {
        type: "list",
        allowBlank: false,
        formulae: [`ValidationData!$A$2:$A$${categoryOptions.length + 1}`],
      });
    }

    if (category2ColIndex > 0) {
      (worksheet as any).dataValidations.add(`${worksheet.getColumn(category2ColIndex).letter}2:${worksheet.getColumn(category2ColIndex).letter}1000`, {
        type: "list",
        allowBlank: false,
        formulae: [`ValidationData!$B$2:$B$${category2Options.length + 1}`],
      });
    }

    if (itemColIndex > 0) {
      (worksheet as any).dataValidations.add(`${worksheet.getColumn(itemColIndex).letter}2:${worksheet.getColumn(itemColIndex).letter}1000`, {
        type: "list",
        allowBlank: false,
        formulae: [`ValidationData!$C$2:$C$${itemOptions.length + 1}`],
      });
    }

    if (scoreTypeColIndex > 0) {
      (worksheet as any).dataValidations.add(`${worksheet.getColumn(scoreTypeColIndex).letter}2:${worksheet.getColumn(scoreTypeColIndex).letter}1000`, {
        type: "list",
        allowBlank: false,
        formulae: [`ValidationData!$D$2:$D$${scoreTypeOptions.length + 1}`],
      });
    }

    worksheet.eachRow((row) => {
      row.eachCell((cell) => {
        cell.border = {
          top: { style: "thin" },
          left: { style: "thin" },
          bottom: { style: "thin" },
          right: { style: "thin" },
        };
      });
    });

    return await workbook.xlsx.writeBuffer() as ArrayBuffer;
  } catch (error) {
    console.error('Error in generating Excel template:', error);
    throw new Error('Failed to generate Excel template');
  }
}

/* EXCEL IMPORT CLIENT-FRIENDLY SERVER ACTION */
async function importRegEvalCriteriaExcel(file: File): Promise<{
  errorFile?: Blob;
  errorMessage?: string;
  successMessage?: string;
}> {
  try {
    const arrayBuffer = await file.arrayBuffer();
    const workbook = new ExcelJS.Workbook();
    await workbook.xlsx.load(arrayBuffer);
    
    const worksheet = workbook.getWorksheet(1);
    if (!worksheet) {
      return { errorMessage: '워크시트를 찾을 수 없습니다.' };
    }

    const errors: string[] = [];
    const groupedData: Map<string, any[]> = new Map();

    // 각 행 처리
    worksheet.eachRow((row, rowIndex) => {
      if (rowIndex === 1) return; // 헤더 건너뛰기

      try {
        const rowData = {
          category: row.getCell(1).value?.toString()?.trim() || '',
          category2: row.getCell(2).value?.toString()?.trim() || '',
          item: row.getCell(3).value?.toString()?.trim() || '',
          classification: row.getCell(4).value?.toString()?.trim() || '',
          range: row.getCell(5).value?.toString()?.trim() || '',
          remarks: row.getCell(6).value?.toString()?.trim() || '',
          scoreType: row.getCell(7).value?.toString()?.trim() || '',
          variableScoreMin: row.getCell(8).value || '',
          variableScoreMax: row.getCell(9).value || '',
          variableScoreUnit: row.getCell(10).value?.toString()?.trim() || '',
          detail: row.getCell(11).value?.toString()?.trim() || '',
          scoreEquipShip: row.getCell(12).value || '',
          scoreEquipMarine: row.getCell(13).value || '',
          scoreBulkShip: row.getCell(14).value || '',
          scoreBulkMarine: row.getCell(15).value || '',
          orderIndex: row.getCell(16).value || '',
        };

        // 빈 행 건너뛰기
        if (!rowData.category && !rowData.category2 && !rowData.item && !rowData.classification) {
          return;
        }

        // 필수 필드 검증
        if (!rowData.category || !rowData.category2 || !rowData.item || !rowData.classification) {
          errors.push(`행 ${rowIndex}: 필수 필드(평가부문, 점수구분, 항목, 구분)가 누락되었습니다.`);
          return;
        }

        // 점수 유형 검증
        if (rowData.scoreType !== '고정' && rowData.scoreType !== '변동') {
          errors.push(`행 ${rowIndex}: 점수 유형은 '고정' 또는 '변동'이어야 합니다.`);
          return;
        }

        // 변동 점수 유형 검증
        if (rowData.scoreType === '변동') {
          if (rowData.scoreEquipShip || rowData.scoreEquipMarine || 
              rowData.scoreBulkShip || rowData.scoreBulkMarine) {
            errors.push(`행 ${rowIndex}: 변동 점수 유형에서는 개별 배점을 입력할 수 없습니다.`);
            return;
          }
          if (rowData.detail) {
            errors.push(`행 ${rowIndex}: 변동 점수 유형에서는 평가내용을 입력할 수 없습니다.`);
            return;
          }
        }

        // 고정 점수 유형 검증
        if (rowData.scoreType === '고정') {
          if (!rowData.detail) {
            errors.push(`행 ${rowIndex}: 고정 점수 유형에서는 평가내용이 필수입니다.`);
            return;
          }
        }

        // 한글 라벨을 영어 값으로 변환
        const categoryValue = REG_EVAL_CRITERIA_CATEGORY.find(item => item.label === rowData.category)?.value || rowData.category;
        const category2Value = REG_EVAL_CRITERIA_CATEGORY2.find(item => item.label === rowData.category2)?.value || rowData.category2;
        const itemValue = REG_EVAL_CRITERIA_ITEM.find(item => item.label === rowData.item)?.value || rowData.item;

        // 데이터 그룹화
        const groupKey = `${categoryValue}-${category2Value}-${itemValue}-${rowData.classification}`;
        if (!groupedData.has(groupKey)) {
          groupedData.set(groupKey, []);
        }
        groupedData.get(groupKey)!.push({
          ...rowData,
          category: categoryValue,
          category2: category2Value,
          item: itemValue,
          scoreType: rowData.scoreType === '고정' ? 'fixed' : 'variable'
        });

      } catch (error) {
        errors.push(`행 ${rowIndex}: 데이터 처리 중 오류가 발생했습니다 - ${error}`);
      }
    });

    // 에러가 있으면 에러 파일 생성 (Blob으로 반환)
    if (errors.length > 0) {
      const errorWorkbook = new ExcelJS.Workbook();
      const errorWorksheet = errorWorkbook.addWorksheet('Import Errors');
      
      errorWorksheet.columns = [
        { header: '오류 내용', key: 'error', width: 80 },
      ];

      errorWorksheet.getRow(1).font = { bold: true };
      errorWorksheet.getRow(1).fill = {
        type: 'pattern',
        pattern: 'solid',
        fgColor: { argb: 'FFFF0000' }
      };

      errors.forEach(error => {
        errorWorksheet.addRow({ error });
      });

      const buffer = await errorWorkbook.xlsx.writeBuffer();
      const errorFile = new Blob([buffer], { 
        type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' 
      });
      
      return { 
        errorFile,
        errorMessage: `${errors.length}개의 오류가 발견되었습니다. 오류 파일을 확인하세요.` 
      };
    }

    // 성공한 데이터 처리
    let importedCount = 0;
    const currentUserId = await getCurrentUserId();
    
    for (const [, groupData] of groupedData) {
      const firstRow = groupData[0];
      
      const criteriaData = {
        category: firstRow.category,
        category2: firstRow.category2,
        item: firstRow.item,
        classification: firstRow.classification,
        range: firstRow.range || null,
        remarks: firstRow.remarks || null,
        scoreType: firstRow.scoreType,
        variableScoreMin: firstRow.variableScoreMin ? String(firstRow.variableScoreMin) : null,
        variableScoreMax: firstRow.variableScoreMax ? String(firstRow.variableScoreMax) : null,
        variableScoreUnit: firstRow.variableScoreUnit || null,
        createdBy: currentUserId,
        updatedBy: currentUserId,
      };

      if (firstRow.scoreType === 'fixed') {
        const detailList = groupData.map((detailData, index) => ({
          detail: detailData.detail,
          scoreEquipShip: detailData.scoreEquipShip ? String(detailData.scoreEquipShip) : null,
          scoreEquipMarine: detailData.scoreEquipMarine ? String(detailData.scoreEquipMarine) : null,
          scoreBulkShip: detailData.scoreBulkShip ? String(detailData.scoreBulkShip) : null,
          scoreBulkMarine: detailData.scoreBulkMarine ? String(detailData.scoreBulkMarine) : null,
          orderIndex: detailData.orderIndex ? parseInt(String(detailData.orderIndex)) : index,
        }));
        
        await createRegEvalCriteriaFixed(criteriaData, detailList);
      } else {
        await createRegEvalCriteriaVariable(criteriaData);
      }
      
      importedCount++;
    }

    return { successMessage: `Excel 파일이 성공적으로 업로드되었습니다. ${importedCount}개의 평가 기준이 추가되었습니다.` };
  } catch (error) {
    console.error('Error in Importing Regular Evaluation Criteria from Excel:', error);
    return { errorMessage: 'Excel 파일 처리 중 오류가 발생했습니다.' };
  }
}

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

/* EXPORT */
export {
  createRegEvalCriteriaFixed,
  createRegEvalCriteriaVariable,
  modifyRegEvalCriteriaFixed,
  modifyRegEvalCriteriaVariable,
  getRegEvalCriteria,
  getRegEvalCriteriaWithDetails,
  getRegEvalCriteriaDetails,
  removeRegEvalCriteria,
  removeRegEvalCriteriaDetails,
  generateRegEvalCriteriaExcel,
  generateRegEvalCriteriaTemplate,
  importRegEvalCriteriaExcel,
};