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

'use server'

import db  from "@/db/db";
import {
  reviewerEvaluations,
  reviewerEvaluationsView,
  reviewerEvaluationDetails,
  regEvalCriteriaDetails,
  evaluationTargetReviewers,
  evaluationTargets,
  regEvalCriteria,
  periodicEvaluations,
  reviewerEvaluationAttachments,
  users
} from "@/db/schema";
import { and, inArray, asc, desc, eq, ilike, or, SQL, count , sql} from "drizzle-orm";
import { filterColumns } from "@/lib/filter-columns";
import { DEPARTMENT_CATEGORY_MAPPING, EvaluationFormData,  GetSHIEvaluationsSubmitSchema, REVIEWER_TYPES, ReviewerType } from "./validation";
import { AttachmentInfo, EvaluationQuestionItem } from "@/types/evaluation-form";


// ===============================================================================
// UTILITY FUNCTIONS
// ===============================================================================

/**
 * division과 materialType을 기반으로 reviewerType을 계산합니다
 */
function calculateReviewerType(division: string, materialType: string): ReviewerType {
  if (division === 'SHIP') {
    if (materialType === 'EQUIPMENT' || materialType === 'EQUIPMENT_BULK') {
      return REVIEWER_TYPES.EQUIPMENT_SHIP;
    } else if (materialType === 'BULK') {
      return REVIEWER_TYPES.BULK_SHIP;
    }
    return REVIEWER_TYPES.EQUIPMENT_SHIP; // 기본값
  } else if (division === 'PLANT') {
    if (materialType === 'EQUIPMENT' || materialType === 'EQUIPMENT_BULK') {
      return REVIEWER_TYPES.EQUIPMENT_MARINE;
    } else if (materialType === 'BULK') {
      return REVIEWER_TYPES.BULK_MARINE;
    }
    return REVIEWER_TYPES.EQUIPMENT_MARINE; // 기본값
  }
  return REVIEWER_TYPES.EQUIPMENT_SHIP; // 기본값
}

/**
 * reviewerType에 따라 해당하는 점수 필드를 가져옵니다
 */
function getScoreByReviewerType(
  detailRecord: {
    scoreEquipShip: string | null;
    scoreEquipMarine: string | null;
    scoreBulkShip: string | null;
    scoreBulkMarine: string | null;
  },
  reviewerType: ReviewerType
): number | null {
  let score: string | null = null;
  
  switch (reviewerType) {
    case REVIEWER_TYPES.EQUIPMENT_SHIP:
      score = detailRecord.scoreEquipShip;
      break;
    case REVIEWER_TYPES.EQUIPMENT_MARINE:
      score = detailRecord.scoreEquipMarine;
      break;
    case REVIEWER_TYPES.BULK_SHIP:
      score = detailRecord.scoreBulkShip;
      break;
    case REVIEWER_TYPES.BULK_MARINE:
      score = detailRecord.scoreBulkMarine;
      break;
  }
  
  return score ? parseFloat(score) : null;
}


function getCategoryFilterByDepartment(departmentCode: string): SQL<unknown> {
  const categoryMapping = DEPARTMENT_CATEGORY_MAPPING as Record<string, string>;
  const category = categoryMapping[departmentCode] || 'administrator';
  return eq(regEvalCriteria.category, category);
}


// ===============================================================================
// MAIN FUNCTIONS
// ===============================================================================



/**
 * 평가 폼 데이터를 조회하고, 응답 레코드가 없으면 생성합니다
 */
export async function getEvaluationFormData(reviewerEvaluationId: number): Promise<EvaluationFormData | null> {
  try {
    console.log(`[SERVER] getEvaluationFormData called with ID: ${reviewerEvaluationId}`);

    // reviewerEvaluationId 유효성 검사
    if (!reviewerEvaluationId || reviewerEvaluationId <= 0) {
      console.error(`[SERVER] Invalid reviewerEvaluationId: ${reviewerEvaluationId}`);
      return null;
    }

    // 1. 리뷰어 평가 정보 조회 (부서 정보 + 평가 대상 정보 포함)
    let reviewerEvaluationInfo;
    try {
      reviewerEvaluationInfo = await db
        .select({
          id: reviewerEvaluations.id,
          periodicEvaluationId: reviewerEvaluations.periodicEvaluationId,
          evaluationTargetReviewerId: reviewerEvaluations.evaluationTargetReviewerId,
          isCompleted: reviewerEvaluations.isCompleted,
          // evaluationTargetReviewers 테이블에서 부서 정보
          departmentCode: evaluationTargetReviewers.departmentCode,
          // evaluationTargets 테이블에서 division과 materialType 정보
          division: evaluationTargets.division,
          materialType: evaluationTargets.materialType,
          vendorName: evaluationTargets.vendorName,
          vendorCode: evaluationTargets.vendorCode,
        })
        .from(reviewerEvaluations)
        .leftJoin(
          evaluationTargetReviewers,
          eq(reviewerEvaluations.evaluationTargetReviewerId, evaluationTargetReviewers.id)
        )
        .leftJoin(
          evaluationTargets,
          eq(evaluationTargetReviewers.evaluationTargetId, evaluationTargets.id)
        )
        .where(eq(reviewerEvaluations.id, reviewerEvaluationId))
        .limit(1);
    } catch (dbError) {
      console.error(`[SERVER] Database query failed for ID ${reviewerEvaluationId}:`, dbError);
      throw new Error(`데이터베이스 조회 중 오류가 발생했습니다: ${dbError instanceof Error ? dbError.message : 'Unknown database error'}`);
    }

    if (reviewerEvaluationInfo.length === 0) {
      console.warn(`[SERVER] Reviewer evaluation not found for ID: ${reviewerEvaluationId}`);
      return null;
    }

    const evaluation = reviewerEvaluationInfo[0];

    // 필수 필드 검증 및 상세 로그
    console.log(`[SERVER] Found evaluation data:`, {
      id: evaluation.id,
      division: evaluation.division,
      materialType: evaluation.materialType,
      departmentCode: evaluation.departmentCode,
      vendorName: evaluation.vendorName,
      vendorCode: evaluation.vendorCode
    });

    if (!evaluation.division || !evaluation.materialType || !evaluation.departmentCode) {
      console.error(`[SERVER] Missing required evaluation data for ID ${reviewerEvaluationId}:`, {
        id: evaluation.id,
        division: evaluation.division,
        materialType: evaluation.materialType,
        departmentCode: evaluation.departmentCode,
        vendorName: evaluation.vendorName,
        vendorCode: evaluation.vendorCode
      });
      return null;
    }

    // 1-1. division과 materialType을 기반으로 reviewerType 계산
    const reviewerType = calculateReviewerType(evaluation.division, evaluation.materialType);

    // 2. 부서에 따른 카테고리 필터링 로직
    const categoryFilter = getCategoryFilterByDepartment(evaluation.departmentCode);

    // 3. 해당 부서에 맞는 평가 기준들과 답변 옵션들 조회
    let criteriaWithDetails;
    try {
      criteriaWithDetails = await db
      .select({
        // 질문 정보 (실제 스키마 기준)
        criteriaId: regEvalCriteria.id,
        category: regEvalCriteria.category,        // 평가부문
        category2: regEvalCriteria.category2,      // 점수유형  
        item: regEvalCriteria.item,                // 항목
        classification: regEvalCriteria.classification, // 구분 (실제 질문)
        range: regEvalCriteria.range,              // 범위 (실제로 평가명)
        remarks: regEvalCriteria.remarks,
        scoreType: regEvalCriteria.scoreType,      // ✅ fixed | variable
        variableScoreMin: regEvalCriteria.variableScoreMin,
        variableScoreMax: regEvalCriteria.variableScoreMax, 
        variableScoreUnit: regEvalCriteria.variableScoreUnit, // ✅ 오타 있지만 실제 스키마 따름
        
        // 답변 옵션 정보
        detailId: regEvalCriteriaDetails.id,
        detail: regEvalCriteriaDetails.detail,
        orderIndex: regEvalCriteriaDetails.orderIndex,
        scoreEquipShip: regEvalCriteriaDetails.scoreEquipShip,
        scoreEquipMarine: regEvalCriteriaDetails.scoreEquipMarine,
        scoreBulkShip: regEvalCriteriaDetails.scoreBulkShip,
        scoreBulkMarine: regEvalCriteriaDetails.scoreBulkMarine,
      })
      .from(regEvalCriteria)
      .leftJoin(
        regEvalCriteriaDetails,
        eq(regEvalCriteria.id, regEvalCriteriaDetails.criteriaId)
      )
      .where(categoryFilter)
      .orderBy(
        regEvalCriteria.id,
        regEvalCriteriaDetails.orderIndex
      );
    } catch (criteriaError) {
      console.error(`[SERVER] Failed to fetch evaluation criteria for ID ${reviewerEvaluationId}:`, criteriaError);
      throw new Error(`평가 기준 조회 중 오류가 발생했습니다: ${criteriaError instanceof Error ? criteriaError.message : 'Unknown criteria error'}`);
    }

    if (!criteriaWithDetails || criteriaWithDetails.length === 0) {
      console.warn(`[SERVER] No evaluation criteria found for ID ${reviewerEvaluationId} with department ${evaluation.departmentCode}`);
      return null;
    }

    // 4. 기존 응답 데이터 조회 (실제 답변만)
    let existingResponses;
    try {
      existingResponses = await db
        .select({
          id: reviewerEvaluationDetails.id,
          reviewerEvaluationId: reviewerEvaluationDetails.reviewerEvaluationId,
          regEvalCriteriaDetailsId: reviewerEvaluationDetails.regEvalCriteriaDetailsId,
          score: reviewerEvaluationDetails.score,
          comment: reviewerEvaluationDetails.comment,
          createdAt: reviewerEvaluationDetails.createdAt,
          updatedAt: reviewerEvaluationDetails.updatedAt,
        })
        .from(reviewerEvaluationDetails)
        .where(eq(reviewerEvaluationDetails.reviewerEvaluationId, reviewerEvaluationId));
    } catch (responseError) {
      console.error(`[SERVER] Failed to fetch existing responses for ID ${reviewerEvaluationId}:`, responseError);
      existingResponses = []; // 기본값 설정
    }

    // 📎 5. 첨부파일 정보 조회
    let attachmentsData;
    try {
      attachmentsData = await db
      .select({
        // 첨부파일 정보
        attachmentId: reviewerEvaluationAttachments.id,
        originalFileName: reviewerEvaluationAttachments.originalFileName,
        storedFileName: reviewerEvaluationAttachments.storedFileName,
        publicPath: reviewerEvaluationAttachments.publicPath,
        fileSize: reviewerEvaluationAttachments.fileSize,
        mimeType: reviewerEvaluationAttachments.mimeType,
        fileExtension: reviewerEvaluationAttachments.fileExtension,
        description: reviewerEvaluationAttachments.description,
        uploadedBy: reviewerEvaluationAttachments.uploadedBy,
        attachmentCreatedAt: reviewerEvaluationAttachments.createdAt,
        attachmentUpdatedAt: reviewerEvaluationAttachments.updatedAt,
        
        // 업로드한 사용자 정보
        uploadedByName: users.name,
        
        // 평가 세부사항 정보 (어떤 질문에 대한 첨부파일인지 확인)
        evaluationDetailId: reviewerEvaluationDetails.id,
        regEvalCriteriaDetailsId: reviewerEvaluationDetails.regEvalCriteriaDetailsId,
        
        // 평가 기준 정보 (질문 식별용)
        criteriaId: regEvalCriteriaDetails.criteriaId,
        category: regEvalCriteria.category,
      })
      .from(reviewerEvaluationAttachments)
      .leftJoin(
        reviewerEvaluationDetails,
        eq(reviewerEvaluationAttachments.reviewerEvaluationDetailId, reviewerEvaluationDetails.id)
      )
      .leftJoin(
        regEvalCriteriaDetails,
        eq(reviewerEvaluationDetails.regEvalCriteriaDetailsId, regEvalCriteriaDetails.id)
      )
      .leftJoin(
        regEvalCriteria,
        eq(regEvalCriteriaDetails.criteriaId, regEvalCriteria.id)
      )
      .leftJoin(
        users,
        eq(reviewerEvaluationAttachments.uploadedBy, users.id)
      )
      .where(eq(reviewerEvaluationDetails.reviewerEvaluationId, reviewerEvaluationId))
      .orderBy(desc(reviewerEvaluationAttachments.createdAt));
    } catch (attachmentError) {
      console.error(`[SERVER] Failed to fetch attachments for ID ${reviewerEvaluationId}:`, attachmentError);
      attachmentsData = []; // 기본값 설정
    }

    // 📎 6. 첨부파일을 질문별로 그룹화
    const attachmentsByQuestion = new Map<number, AttachmentInfo[]>();
    const attachmentsByCategory = new Map<string, number>();

    attachmentsData.forEach(attachment => {
      // Variable 타입 질문의 경우 criteriaId가 null일 수 있으므로 처리
      const questionKey = attachment.criteriaId || attachment.evaluationDetailId;
      
      if (!attachmentsByQuestion.has(questionKey)) {
        attachmentsByQuestion.set(questionKey, []);
      }

      const attachmentInfo: AttachmentInfo = {
        id: attachment.attachmentId,
        originalFileName: attachment.originalFileName,
        storedFileName: attachment.storedFileName,
        publicPath: attachment.publicPath,
        fileSize: attachment.fileSize,
        mimeType: attachment.mimeType || undefined,
        fileExtension: attachment.fileExtension || undefined,
        description: attachment.description || undefined,
        uploadedBy: attachment.uploadedBy,
        uploadedByName: attachment.uploadedByName || undefined,
        createdAt: new Date(attachment.attachmentCreatedAt),
        updatedAt: new Date(attachment.attachmentUpdatedAt),
      };

      attachmentsByQuestion.get(questionKey)!.push(attachmentInfo);

      // 카테고리별 파일 수 집계
      const category = attachment.category || '기타';
      attachmentsByCategory.set(category, (attachmentsByCategory.get(category) || 0) + 1);
    });

    // 7. 질문별로 그룹화하고 답변 옵션들 정리
    const questionsMap = new Map<number, EvaluationQuestionItem>();

    criteriaWithDetails.forEach(record => {
      if (!record.detailId) return; // 답변 옵션이 없는 경우 스킵

      const criteriaId = record.criteriaId;

      // 해당 reviewerType에 맞는 점수 가져오기
      const score = getScoreByReviewerType(record, reviewerType);
      if (score === null) return; // 해당 리뷰어 타입에 점수가 없으면 스킵

      // 질문이 이미 존재하는지 확인
      if (!questionsMap.has(criteriaId)) {
        const questionAttachments = attachmentsByQuestion.get(criteriaId) || [];
        
        questionsMap.set(criteriaId, {
          criteriaId: record.criteriaId,
          category: record.category,
          category2: record.category2,
          item: record.item,
          classification: record.classification,
          range: record.range || null,
          scoreType: record.scoreType,
          remarks: record.remarks,
          availableOptions: [],
          responseId: null,
          selectedDetailId: null, // ✅ 초기값은 null (아직 선택하지 않음)
          currentScore: null,
          currentComment: null,
          
          // 📎 첨부파일 정보 추가
          attachments: questionAttachments,
          attachmentCount: questionAttachments.length,
          attachmentTotalSize: questionAttachments.reduce((sum, att) => sum + (att.fileSize || 0), 0),
        });
      }

      // 답변 옵션 추가
      const question = questionsMap.get(criteriaId)!;
      question.availableOptions.push({
        detailId: record.detailId || 0,
        detail: record.detail || '',
        score: score,
        orderIndex: record.orderIndex || 0,
      });
    });

    // 8. ✅ Variable 타입 질문 처리 (첨부파일은 있지만 criteriaId가 null인 경우)
    const variableTypeAttachments = new Map<number, AttachmentInfo[]>();
    attachmentsData.forEach(attachment => {
      if (!attachment.criteriaId && attachment.regEvalCriteriaDetailsId === null) {
        // Variable 타입 질문의 첨부파일
        if (!variableTypeAttachments.has(attachment.evaluationDetailId)) {
          variableTypeAttachments.set(attachment.evaluationDetailId, []);
        }
        variableTypeAttachments.get(attachment.evaluationDetailId)!.push({
          id: attachment.attachmentId,
          originalFileName: attachment.originalFileName,
          storedFileName: attachment.storedFileName,
          publicPath: attachment.publicPath,
          fileSize: attachment.fileSize,
          mimeType: attachment.mimeType || undefined,
          fileExtension: attachment.fileExtension || undefined,
          description: attachment.description || undefined,
          uploadedBy: attachment.uploadedBy,
          uploadedByName: attachment.uploadedByName || undefined,
          createdAt: new Date(attachment.attachmentCreatedAt),
          updatedAt: new Date(attachment.attachmentUpdatedAt),
        });
      }
    });

    // 9. 기존 응답 데이터를 질문에 매핑
    const existingResponsesMap = new Map<number | null, typeof existingResponses[0]>();
    const responseDetailMap = new Map<number, typeof existingResponses[0]>();

    existingResponses.forEach(response => {
      if (response.regEvalCriteriaDetailsId) {
        existingResponsesMap.set(response.regEvalCriteriaDetailsId, response);
      } else {
        // Variable 타입 응답 (regEvalCriteriaDetailsId가 null)
        responseDetailMap.set(response.id, response);
      }
    });

    // 10. 각 질문에 현재 응답 정보 매핑
    const questions: EvaluationQuestionItem[] = [];
    questionsMap.forEach(question => {
      // 현재 선택된 답변 찾기 (실제 응답이 있는 경우에만)
      let selectedResponse: any = null;
      for (const option of question.availableOptions) {
        const response = existingResponsesMap.get(option.detailId);
        if (response) {
          selectedResponse = response;
          question.selectedDetailId = option.detailId;
          break;
        }
      }

      if (selectedResponse) {
        question.responseId = selectedResponse.id;
        question.currentScore = selectedResponse.score;
        question.currentComment = selectedResponse.comment;
      }
      // ✅ else 케이스: 아직 답변하지 않은 상태 (모든 값이 null)

      questions.push(question);
    });

    // 📎 11. 전체 첨부파일 통계 계산
    const attachmentStats = {
      totalFiles: attachmentsData.length,
      totalSize: attachmentsData.reduce((sum, att) => sum + (att.fileSize || 0), 0),
      questionsWithAttachments: attachmentsByQuestion.size + variableTypeAttachments.size,
      filesByCategory: Object.fromEntries(attachmentsByCategory),
    };

    return {
      evaluationInfo: { 
        ...evaluation, 
        reviewerType
      },
      questions,
      attachmentStats,
    };

  } catch (err) {
    console.error(`[SERVER] Error in getEvaluationFormData for ID ${reviewerEvaluationId}:`, err);
    // 데이터베이스 연결 오류나 쿼리 실행 오류 등은 여기서 처리
    if (err instanceof Error) {
      if (err.message.includes('Connection') || err.message.includes('timeout')) {
        console.error(`[SERVER] Database connection error: ${err.message}`);
      } else if (err.message.includes('syntax') || err.message.includes('column')) {
        console.error(`[SERVER] Database query error: ${err.message}`);
      }
    }
    return null;
  }
}


/**
 * 평가 제출 목록을 조회합니다
 */
export async function getSHIEvaluationSubmissions(input: GetSHIEvaluationsSubmitSchema, userId: number) {
  try {
    const offset = (input.page - 1) * input.perPage;

    // 고급 필터링
    const advancedWhere = filterColumns({
      table: reviewerEvaluationsView,
      filters: input.filters,
      joinOperator: input.joinOperator,
    });

    // 전역 검색
    let globalWhere: SQL<unknown> | undefined;
    if (input.search) {
      const s = `%${input.search}%`;
      globalWhere = or(
        ilike(reviewerEvaluationsView.isCompleted, s),
      );
    }

    const existingReviewer = await db.query.evaluationTargetReviewers.findMany({
      where: eq(evaluationTargetReviewers.reviewerUserId, userId),
    });


    const finalWhere = and(
      advancedWhere,
      globalWhere,
      inArray(reviewerEvaluationsView.evaluationTargetReviewerId, existingReviewer.map(e => e.id)),
    );


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

    // 데이터 조회
    const { data, total } = await db.transaction(async (tx) => {
      // 메인 데이터 조회
      const data = await tx
        .select()
        .from(reviewerEvaluationsView)
        .where(finalWhere)
        .orderBy(...orderBy)
        .limit(input.perPage)
        .offset(offset);

      // 총 개수 조회
      const totalResult = await tx
        .select({ count: count() })
        .from(reviewerEvaluationsView)
        .where(finalWhere);
      const total = totalResult[0]?.count || 0;

      return { data, total };
    });

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

/**
 * 특정 평가 제출의 상세 정보를 조회합니다
 */
export async function getSHIEvaluationSubmissionById(id: number) {
  try {
    const result = await db
      .select()
      .from(reviewerEvaluationsView)
      .where(
        and(
          eq(reviewerEvaluationsView.evaluationTargetReviewerId, id),
        )
      )
      .limit(1);

    if (result.length === 0) {
      return null;
    }

    const submission = result[0];

    // 응답 데이터도 함께 조회
    const [generalResponses] = await Promise.all([
      db
        .select()
        .from(reviewerEvaluationDetails)
        .where(
          and(
            eq(reviewerEvaluationDetails.reviewerEvaluationId, id),
          )
        ),
    ]);

    return {
      ...submission,
      generalResponses,
    };
  } catch (err) {
    console.error('Error in getEvaluationSubmissionById:', err);
    return null;
  }
}

/**
 * 평가 응답을 업데이트합니다
 */
// 기존 updateEvaluationResponse 함수를 확장하여 첨부파일 처리 지원

export async function updateEvaluationResponse(
  reviewerEvaluationId: number,
  selectedDetailId: number,
  comment?: string,
  customScore?: number
) {
  try {
    let reviewerEvaluationDetailId: number | null = null;

    await db.transaction(async (tx) => {
      // 1. 선택된 답변 옵션의 정보 조회 (variable 타입이 아닌 경우)
      let selectedDetail: any = null;
      let score: number;

      if (selectedDetailId !== -1) {
        const detailResult = await tx
          .select()
          .from(regEvalCriteriaDetails)
          .where(eq(regEvalCriteriaDetails.id, selectedDetailId))
          .limit(1);

        if (detailResult.length === 0) {
          throw new Error('Selected detail not found');
        }
        selectedDetail = detailResult[0];
      }

      // 2. reviewerEvaluation 정보 조회 (periodicEvaluationId, division, materialType 포함)
      const reviewerEvaluationInfo = await tx
        .select({
          periodicEvaluationId: reviewerEvaluations.periodicEvaluationId,
          // evaluationTargetReviewers 테이블에서 부서 정보
          departmentCode: evaluationTargetReviewers.departmentCode,
          // evaluationTargets 테이블에서 division과 materialType 정보
          division: evaluationTargets.division,
          materialType: evaluationTargets.materialType,
        })
        .from(reviewerEvaluations)
        .leftJoin(
          evaluationTargetReviewers,
          eq(reviewerEvaluations.evaluationTargetReviewerId, evaluationTargetReviewers.id)
        )
        .leftJoin(
          evaluationTargets,
          eq(evaluationTargetReviewers.evaluationTargetId, evaluationTargets.id)
        )
        .where(eq(reviewerEvaluations.id, reviewerEvaluationId))
        .limit(1);

      if (reviewerEvaluationInfo.length === 0) {
        throw new Error('Reviewer evaluation not found');
      }

      const evaluation = reviewerEvaluationInfo[0];

      // 필수 필드 검증
      if (!evaluation.division || !evaluation.materialType || !evaluation.departmentCode) {
        throw new Error('Missing required evaluation data');
      }

      const { periodicEvaluationId } = evaluation;

      // 3. periodicEvaluation의 현재 상태 확인 및 업데이트
      const currentStatus = await tx
        .select({
          status: periodicEvaluations.status,
        })
        .from(periodicEvaluations)
        .where(eq(periodicEvaluations.id, periodicEvaluationId))
        .limit(1);

      if (currentStatus.length > 0 && currentStatus[0].status !== "IN_REVIEW") {
        await tx
          .update(periodicEvaluations)
          .set({
            status: "IN_REVIEW",
            updatedAt: new Date(),
          })
          .where(eq(periodicEvaluations.id, periodicEvaluationId));
      }

      // 4. 점수 결정
      if (selectedDetailId === -1) {
        // variable 타입인 경우 customScore 사용
        if (customScore === undefined) {
          throw new Error('Custom score is required for variable type');
        }
        score = customScore;
      } else {
        // 일반 타입인 경우 리뷰어 타입에 맞는 점수 가져오기
        if (!selectedDetail) {
          throw new Error('Selected detail not found');
        }

        // reviewerType 계산
        const reviewerTypeForScore = calculateReviewerType(evaluation.division, evaluation.materialType);
        const calculatedScore = getScoreByReviewerType(selectedDetail, reviewerTypeForScore);
        if (calculatedScore === null) {
          throw new Error('Score not found for this reviewer type');
        }
        score = calculatedScore;
      }

      // 5. 해당 평가 기준에 대한 기존 응답들 삭제
      let criteriaId: number;
      if (selectedDetailId === -1) {
        // variable 타입인 경우, criteriaId를 별도로 조회해야 함
        // 이 부분은 실제 데이터 구조에 따라 조정 필요
        throw new Error('Variable type criteria ID lookup not implemented');
      } else {
        criteriaId = selectedDetail!.criteriaId;
      }

      await tx
        .delete(reviewerEvaluationDetails)
        .where(
          and(
            eq(reviewerEvaluationDetails.reviewerEvaluationId, reviewerEvaluationId),
            sql`${reviewerEvaluationDetails.regEvalCriteriaDetailsId} IN (
              SELECT id FROM reg_eval_criteria_details WHERE criteria_id = ${criteriaId}
            )`
          )
        );

      // 6. 새로운 응답 생성
      const [newDetail] = await tx
        .insert(reviewerEvaluationDetails)
        .values({
          reviewerEvaluationId,
          regEvalCriteriaDetailsId: selectedDetailId === -1 ? null : selectedDetailId,
          score: score.toString(),
          comment,
        })
        .returning({
          id: reviewerEvaluationDetails.id,
        });

      reviewerEvaluationDetailId = newDetail.id;

      // 7. 카테고리별 점수 계산 및 총점 업데이트
      await recalculateEvaluationScores(tx, reviewerEvaluationId);
    });

    return { 
      success: true, 
      reviewerEvaluationDetailId 
    };
  } catch (err) {
    console.error('Error in updateEvaluationResponse:', err);
    throw err;
  }
}

// variable 타입을 위한 별도 함수
export async function updateVariableEvaluationResponse(
  reviewerEvaluationId: number,
  criteriaId: number,
  score: number,
  comment?: string
) {
  try {
    let reviewerEvaluationDetailId: number | null = null;

    await db.transaction(async (tx) => {
      // 1. reviewerEvaluation 정보 조회
      const reviewerEvaluationInfo = await tx
        .select({
          periodicEvaluationId: reviewerEvaluations.periodicEvaluationId,
        })
        .from(reviewerEvaluations)
        .where(eq(reviewerEvaluations.id, reviewerEvaluationId))
        .limit(1);

      if (reviewerEvaluationInfo.length === 0) {
        throw new Error('Reviewer evaluation not found');
      }

      const { periodicEvaluationId } = reviewerEvaluationInfo[0];

      // 2. 상태 업데이트
      const currentStatus = await tx
        .select({
          status: periodicEvaluations.status,
        })
        .from(periodicEvaluations)
        .where(eq(periodicEvaluations.id, periodicEvaluationId))
        .limit(1);

      if (currentStatus.length > 0 && currentStatus[0].status !== "IN_REVIEW") {
        await tx
          .update(periodicEvaluations)
          .set({
            status: "IN_REVIEW",
            updatedAt: new Date(),
          })
          .where(eq(periodicEvaluations.id, periodicEvaluationId));
      }

      // 3. 해당 평가 기준에 대한 기존 응답들 삭제
      if (criteriaId) {
        await tx
          .delete(reviewerEvaluationDetails)
          .where(
            and(
              eq(reviewerEvaluationDetails.reviewerEvaluationId, reviewerEvaluationId),
              sql`${reviewerEvaluationDetails.regEvalCriteriaDetailsId} IN (
                SELECT id FROM reg_eval_criteria_details WHERE criteria_id = ${criteriaId}
              )`
            )
          );
      }

      // 4. 새로운 응답 생성 (variable 타입은 regEvalCriteriaDetailsId가 null)
      const [newDetail] = await tx
        .insert(reviewerEvaluationDetails)
        .values({
          reviewerEvaluationId,
          regEvalCriteriaDetailsId: null, // variable 타입은 null
          score: score.toString(),
          comment,
        })
        .returning({
          id: reviewerEvaluationDetails.id,
        });

      reviewerEvaluationDetailId = newDetail.id;

      // 5. 점수 재계산
      await recalculateEvaluationScores(tx, reviewerEvaluationId);
    });

    return { 
      success: true, 
      reviewerEvaluationDetailId 
    };
  } catch (err) {
    console.error('Error in updateVariableEvaluationResponse:', err);
    throw err;
  }
}

// 첨부파일과 함께 평가 응답 업데이트
// export async function updateEvaluationResponseWithAttachment(
//   reviewerEvaluationId: number,
//   selectedDetailId: number,
//   comment?: string,
//   customScore?: number,
//   attachmentFile?: File,
//   attachmentDescription?: string
// ) {
//   try {
//     // 1. 먼저 평가 응답 업데이트
//     const updateResult = selectedDetailId === -1 && customScore !== undefined ?
//       await updateVariableEvaluationResponse(reviewerEvaluationId, /* criteriaId 필요 */, customScore, comment) :
//       await updateEvaluationResponse(reviewerEvaluationId, selectedDetailId, comment, customScore);

//     if (!updateResult.success || !updateResult.reviewerEvaluationDetailId) {
//       throw new Error('Failed to update evaluation response');
//     }

//     // 2. 첨부파일이 있으면 저장
//     if (attachmentFile) {
//       const fileResult = await saveFile({
//         file: attachmentFile,
//         directory: "evaluation-attachments",
//         originalName: attachmentFile.name,
//       });

//       if (!fileResult.success) {
//         throw new Error(fileResult.error || "파일 저장에 실패했습니다.");
//       }

//       // 3. DB에 첨부파일 정보 저장
//       await db.insert(reviewerEvaluationAttachments).values({
//         reviewerEvaluationDetailId: updateResult.reviewerEvaluationDetailId,
//         originalFileName: attachmentFile.name,
//         storedFileName: fileResult.fileName!,
//         filePath: fileResult.filePath!,
//         publicPath: fileResult.publicPath!,
//         fileSize: attachmentFile.size,
//         mimeType: attachmentFile.type,
//         fileExtension: attachmentFile.name.split('.').pop()?.toLowerCase() || '',
//         description: attachmentDescription || null,
//         uploadedBy: /* session.user.id 필요 */,
//       });
//     }

//     return { success: true };
//   } catch (err) {
//     console.error('Error in updateEvaluationResponseWithAttachment:', err);
//     throw err;
//   }
// }

/**
 * 평가 점수 재계산
 */
async function recalculateEvaluationScores(tx: any, reviewerEvaluationId: number) {
  await tx
    .update(reviewerEvaluations)
    .set({
      updatedAt: new Date(),
    })
    .where(eq(reviewerEvaluations.id, reviewerEvaluationId));
}


export async function completeEvaluation(
  reviewerEvaluationId: number,
  reviewerComment?: string
) {
  try {
    await db.transaction(async (tx) => {
      // 1. 먼저 해당 리뷰어 평가를 완료로 표시
      const updatedEvaluation = await tx
        .update(reviewerEvaluations)
        .set({
          isCompleted: true,
          completedAt: new Date(),
          reviewerComment,
          updatedAt: new Date(),
        })
        .where(eq(reviewerEvaluations.id, reviewerEvaluationId))
        .returning({ periodicEvaluationId: reviewerEvaluations.periodicEvaluationId });

      if (updatedEvaluation.length === 0) {
        throw new Error('Reviewer evaluation not found');
      }

      const { periodicEvaluationId } = updatedEvaluation[0];

      // 2. 같은 periodicEvaluationId를 가진 모든 리뷰어 평가가 완료되었는지 확인
      const allEvaluations = await tx
        .select({
          isCompleted: reviewerEvaluations.isCompleted,
        })
        .from(reviewerEvaluations)
        .where(eq(reviewerEvaluations.periodicEvaluationId, periodicEvaluationId));

      // 3. 모든 평가가 완료되었는지 확인
      const allCompleted = allEvaluations.every(evaluation => evaluation.isCompleted);

      // 4. 모든 평가가 완료되었다면 periodicEvaluations의 status 업데이트
      if (allCompleted) {
        await tx
          .update(periodicEvaluations)
          .set({
            status: "REVIEW_COMPLETED",
            updatedAt: new Date(),
          })
          .where(eq(periodicEvaluations.id, periodicEvaluationId));
      }
    });

    return { success: true };
  } catch (err) {
    console.error('Error in completeEvaluation:', err);
    throw err;
  }
}