'use server'

import db  from "@/db/db";
import { 
  reviewerEvaluations,
  reviewerEvaluationsView,
  reviewerEvaluationDetails,
  regEvalCriteriaDetails,
  regEvalCriteriaView,
  NewReviewerEvaluationDetail,
  ReviewerEvaluationDetail,
  evaluationTargetReviewers,
  evaluationTargets,
  regEvalCriteria,
  periodicEvaluations
} from "@/db/schema";
import { and, asc, desc, eq, ilike, or, SQL, count , sql, avg, isNotNull} from "drizzle-orm";
import { filterColumns } from "@/lib/filter-columns";
import { DEPARTMENT_CATEGORY_MAPPING, EvaluationFormData, EvaluationQuestionItem, GetSHIEvaluationsSubmitSchema, REVIEWER_TYPES, ReviewerType } from "./validation";


// ===============================================================================
// 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: any,
  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 {
    // 1. 리뷰어 평가 정보 조회 (부서 정보 + 평가 대상 정보 포함)
    const 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);

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

    const evaluation = reviewerEvaluationInfo[0];

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

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

    // 3. 해당 부서에 맞는 평가 기준들과 답변 옵션들 조회
    const 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
      );

    // 4. 기존 응답 데이터 조회 (실제 답변만)
    const 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(
        and(
          eq(reviewerEvaluationDetails.reviewerEvaluationId, reviewerEvaluationId),
          // ✅ null이 아닌 실제 응답만 조회
          isNotNull(reviewerEvaluationDetails.regEvalCriteriaDetailsId)
        )
      );

    // 5. 질문별로 그룹화하고 답변 옵션들 정리
    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)) {
        questionsMap.set(criteriaId, {
          criteriaId: record.criteriaId,
          category: record.category,
          category2: record.category2,
          item: record.item,
          classification: record.classification,
          range: record.range,
          scoreType: record.scoreType,
          remarks: record.remarks,
          availableOptions: [],
          responseId: null,
          selectedDetailId: null, // ✅ 초기값은 null (아직 선택하지 않음)
          currentScore: null,
          currentComment: null,
        });
      }

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

    // 6. ✅ 초기 응답 생성하지 않음 - 사용자가 실제로 답변할 때만 생성

    // 7. 기존 응답 데이터를 질문에 매핑
    const existingResponsesMap = new Map(
      existingResponses.map(r => [r.regEvalCriteriaDetailsId, r])
    );

    // 8. 각 질문에 현재 응답 정보 매핑
    const questions: EvaluationQuestionItem[] = [];
    questionsMap.forEach(question => {
      // 현재 선택된 답변 찾기 (실제 응답이 있는 경우에만)
      let selectedResponse = 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);
    });

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

  } catch (err) {
    console.error('Error in getEvaluationFormData:', err);
    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.findFirst({
      where: eq(evaluationTargetReviewers.reviewerUserId, userId),
    });



    const finalWhere = and(
      advancedWhere,
      globalWhere,
      eq(reviewerEvaluationsView.evaluationTargetReviewerId, existingReviewer?.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;
  }
}

/**
 * 평가 응답을 업데이트합니다
 */
export async function updateEvaluationResponse(
  reviewerEvaluationId: number,
  selectedDetailId: number,
  comment?: string
) {
  try {
    await db.transaction(async (tx) => {
      // 1. 선택된 답변 옵션의 정보 조회
      const selectedDetail = await tx
        .select()
        .from(regEvalCriteriaDetails)
        .where(eq(regEvalCriteriaDetails.id, selectedDetailId))
        .limit(1);

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

      // 2. reviewerEvaluation 정보 조회 (periodicEvaluationId 포함)
      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];

      // 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. 리뷰어 타입 정보 조회
      const evaluationInfo = await getEvaluationFormData(reviewerEvaluationId);
      if (!evaluationInfo) {
        throw new Error('Evaluation not found');
      }

      // 5. 해당 리뷰어 타입에 맞는 점수 가져오기
      const score = getScoreByReviewerType(selectedDetail[0], evaluationInfo.evaluationInfo.reviewerType);
      if (score === null) {
        throw new Error('Score not found for this reviewer type');
      }

      // 6. 같은 질문에 대한 기존 응답들 삭제
      const criteriaId = selectedDetail[0].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}
            )`
          )
        );

      // 7. 새로운 응답 생성
      await tx
        .insert(reviewerEvaluationDetails)
        .values({
          reviewerEvaluationId,
          regEvalCriteriaDetailsId: selectedDetailId,
          score: score.toString(),
          comment,
        });

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

    return { success: true };
  } catch (err) {
    console.error('Error in updateEvaluationResponse:', 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;
  }
}