path: root/lib/evaluation-target-list/service.ts

'use server'

import { and, or, desc, asc, ilike, eq, isNull, sql, count, inArray } from "drizzle-orm";

import { getFiltersStateParser, getSortingStateParser } from "@/lib/parsers";
import { filterColumns } from "@/lib/filter-columns";
import db from "@/db/db";
import {
  evaluationTargets,
  evaluationTargetReviewers,
  evaluationTargetReviews,
  users,
  vendors,
  type EvaluationTargetStatus,
  type Division,
  type MaterialType,
  type DomesticForeign,
  EVALUATION_DEPARTMENT_CODES,
  EvaluationTargetWithDepartments,
  evaluationTargetsWithDepartments
} from "@/db/schema";
import { GetEvaluationTargetsSchema } from "./validation";
import { PgTransaction } from "drizzle-orm/pg-core";
import { getServerSession } from "next-auth/next"
import { authOptions } from "@/app/api/auth/[...nextauth]/route"
import { sendEmail } from "../mail/sendEmail";

export async function selectEvaluationTargetsFromView(
  tx: PgTransaction<any, any, any>,
  params: {
    where?: any;
    orderBy?: (ReturnType<typeof asc> | ReturnType<typeof desc>)[];
    offset?: number;
    limit?: number;
  }
) {
  const { where, orderBy, offset = 0, limit = 10 } = params;

  return tx
    .select()
    .from(evaluationTargetsWithDepartments)
    .where(where)
    .orderBy(...(orderBy ?? []))
    .offset(offset)
    .limit(limit);
}

/** 총 개수 count */
export async function countEvaluationTargetsFromView(
  tx: PgTransaction<any, any, any>,
  where?: any
) {
  const res = await tx
    .select({ count: count() })
    .from(evaluationTargetsWithDepartments)
    .where(where);

  return res[0]?.count ?? 0;
}

// ============= 메인 서버 액션도 함께 수정 =============

export async function getEvaluationTargets(input: GetEvaluationTargetsSchema) {
  try {
    const offset = (input.page - 1) * input.perPage;

    // 고급 필터링 (View 테이블 기준)
    const advancedWhere = filterColumns({
      table: evaluationTargetsWithDepartments,
      filters: input.filters,
      joinOperator: input.joinOperator,
    });

    // 베이직 필터링 (커스텀 필터)
    let basicWhere;
    if (input.basicFilters && input.basicFilters.length > 0) {
      basicWhere = filterColumns({
        table: evaluationTargetsWithDepartments,
        filters: input.basicFilters,
        joinOperator: input.basicJoinOperator || "and",
      });
    }

    // 전역 검색 (View 테이블 기준)
    let globalWhere;
    if (input.search) {
      const s = `%${input.search}%`;
      globalWhere = or(
        ilike(evaluationTargetsWithDepartments.vendorCode, s),
        ilike(evaluationTargetsWithDepartments.vendorName, s),
        ilike(evaluationTargetsWithDepartments.adminComment, s),
        ilike(evaluationTargetsWithDepartments.consolidatedComment, s),
        // 담당자 이름으로도 검색 가능
        ilike(evaluationTargetsWithDepartments.orderReviewerName, s),
        ilike(evaluationTargetsWithDepartments.procurementReviewerName, s),
        ilike(evaluationTargetsWithDepartments.qualityReviewerName, s),
        ilike(evaluationTargetsWithDepartments.designReviewerName, s),
        ilike(evaluationTargetsWithDepartments.csReviewerName, s)
      );
    }

    const finalWhere = and(advancedWhere, basicWhere, globalWhere);

    // 정렬 (View 테이블 기준)
    const orderBy = input.sort.length > 0
      ? input.sort.map((item) => {
        const column = evaluationTargetsWithDepartments[item.id as keyof typeof evaluationTargetsWithDepartments];
        return item.desc ? desc(column) : asc(column);
      })
      : [desc(evaluationTargetsWithDepartments.createdAt)];

    // 데이터 조회 - View 테이블 사용
    const { data, total } = await db.transaction(async (tx) => {
      const data = await selectEvaluationTargetsFromView(tx, {
        where: finalWhere,
        orderBy,
        offset,
        limit: input.perPage,
      });

      const total = await countEvaluationTargetsFromView(tx, finalWhere);
      return { data, total };
    });

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

// ============= 개별 조회 함수도 업데이트 =============

export async function getEvaluationTargetById(id: number): Promise<EvaluationTargetWithDepartments | null> {
  try {
    const results = await db.transaction(async (tx) => {
      return await selectEvaluationTargetsFromView(tx, {
        where: eq(evaluationTargetsWithDepartments.id, id),
        limit: 1,
      });
    });

    return results[0] || null;
  } catch (err) {
    console.error("Error in getEvaluationTargetById:", err);
    return null;
  }
}

// 통계 조회도 View 기반으로 변경
export async function getEvaluationTargetsStats(evaluationYear: number) {
  try {
    const stats = await db.transaction(async (tx) => {
      const result = await tx
        .select({
          total: count(),
          pending: sql<number>`sum(case when status = 'PENDING' then 1 else 0 end)`,
          confirmed: sql<number>`sum(case when status = 'CONFIRMED' then 1 else 0 end)`,
          excluded: sql<number>`sum(case when status = 'EXCLUDED' then 1 else 0 end)`,
          consensusTrue: sql<number>`sum(case when consensus_status = true then 1 else 0 end)`,
          consensusFalse: sql<number>`sum(case when consensus_status = false then 1 else 0 end)`,
          consensusNull: sql<number>`sum(case when consensus_status is null then 1 else 0 end)`,
          oceanDivision: sql<number>`sum(case when division = 'OCEAN' then 1 else 0 end)`,
          shipyardDivision: sql<number>`sum(case when division = 'SHIPYARD' then 1 else 0 end)`,
        })
        .from(evaluationTargetsWithDepartments)
        .where(eq(evaluationTargetsWithDepartments.evaluationYear, evaluationYear));

      return result[0];
    });

    return stats;
  } catch (err) {
    console.error("Error in getEvaluationTargetsStats:", err);
    return null;
  }
}


// ============= 수동 생성 관련 서버 액션 =============

// 평가 대상 수동 생성 인터페이스
export interface CreateEvaluationTargetInput {
  evaluationYear: number;
  division: Division;
  vendorId: number;
  materialType: MaterialType;
  adminComment?: string;
  // 각 부서별 담당자 지정
  reviewers: {
    departmentCode: keyof typeof EVALUATION_DEPARTMENT_CODES;
    reviewerUserId: number;
  }[];
}

// 평가 대상 수동 생성
// service.ts 파일의 CreateEvaluationTargetInput 타입 수정
export interface CreateEvaluationTargetInput {
  evaluationYear: number
  division: "PLANT" | "SHIP"
  vendorId: number
  materialType: "EQUIPMENT" | "BULK" | "EQUIPMENT_BULK"
  adminComment?: string
  // ✅ 추가된 L/D 클레임 필드들
  ldClaimCount?: number
  ldClaimAmount?: number
  ldClaimCurrency?: "KRW" | "USD" | "EUR" | "JPY"
  reviewers: Array<{
    departmentCode: string
    reviewerUserId: number
  }>
}

// createEvaluationTarget 함수 수정
// service.ts 수정
export async function createEvaluationTarget(
  input: CreateEvaluationTargetInput,
  createdBy: number
) {
  console.log(input, "input")
  try {
    return await db.transaction(async (tx) => {
      // 벤더 정보 조회
      const vendor = await tx
        .select({
          id: vendors.id,
          vendorCode: vendors.vendorCode,
          vendorName: vendors.vendorName,
          country: vendors.country,
        })
        .from(vendors)
        .where(eq(vendors.id, input.vendorId))
        .limit(1);

      if (!vendor.length) {
        throw new Error("벤더를 찾을 수 없습니다.");
      }

      const vendorInfo = vendor[0];

      // 중복 체크
      const existing = await tx
        .select({ id: evaluationTargets.id })
        .from(evaluationTargets)
        .where(
          and(
            eq(evaluationTargets.evaluationYear, input.evaluationYear),
            eq(evaluationTargets.vendorId, input.vendorId),
            eq(evaluationTargets.materialType, input.materialType)
          )
        )
        .limit(1);

      if (existing.length > 0) {
        throw new Error("이미 동일한 평가 대상이 존재합니다.");
      }

      // 🔧 수정: 타입 추론 문제 해결
      const targetValues: typeof evaluationTargets.$inferInsert = {
        evaluationYear: input.evaluationYear,
        division: input.division,
        vendorId: input.vendorId,
        vendorCode: vendorInfo.vendorCode ?? '',
        vendorName: vendorInfo.vendorName,
        domesticForeign: vendorInfo.country === 'KR' ? 'DOMESTIC' : 'FOREIGN',
        materialType: input.materialType,
        status: 'PENDING',
        adminComment: input.adminComment,
        adminUserId: createdBy,
        ldClaimCount: input.ldClaimCount ?? 0,
        // 🔧 수정: decimal 타입은 숫자로 처리
        ldClaimAmount: input.ldClaimAmount?.toString() ?? '0',
        ldClaimCurrency: input.ldClaimCurrency ?? 'KRW',
      }

      console.log(targetValues)

      // 평가 대상 생성
      const newEvaluationTarget = await tx
        .insert(evaluationTargets)
        .values(targetValues)
        .returning({ id: evaluationTargets.id });

      const evaluationTargetId = newEvaluationTarget[0].id;

      // 담당자들 지정
      if (input.reviewers && input.reviewers.length > 0) {
        const reviewerIds = input.reviewers.map(r => r.reviewerUserId);
        
        // 🔧 수정: SQL 배열 처리 개선
        const reviewerInfos = await tx
          .select({
            id: users.id,
          })
          .from(users)
          .where(inArray(users.id, reviewerIds)); // sql 대신 inArray 사용

        const reviewerAssignments: typeof evaluationTargetReviewers.$inferInsert[] =
          input.reviewers.map(r => {
            const info = reviewerInfos.find(i => i.id === r.reviewerUserId);
            return {
              evaluationTargetId,
              departmentCode: r.departmentCode,
              departmentNameFrom: info?.departmentName ?? "TEST 부서",
              reviewerUserId: r.reviewerUserId,
              assignedBy: createdBy,
            };
          });

        await tx.insert(evaluationTargetReviewers).values(reviewerAssignments);
      }

      return {
        success: true,
        evaluationTargetId,
        message: "평가 대상이 성공적으로 생성되었습니다.",
      };
    });
  } catch (error) {
    console.error("Error creating evaluation target:", error);
    return {
      success: false,
      error: error instanceof Error ? error.message : "평가 대상 생성 중 오류가 발생했습니다.",
    };
  }
}

//업데이트 입력 타입 정의
export interface UpdateEvaluationTargetInput {
  id: number
  adminComment?: string
  consolidatedComment?: string
  ldClaimCount?: number
  ldClaimAmount?: number
  ldClaimCurrency?: "KRW" | "USD" | "EUR" | "JPY"
  consensusStatus?: boolean | null
  orderIsApproved?: boolean | null
  procurementIsApproved?: boolean | null
  qualityIsApproved?: boolean | null
  designIsApproved?: boolean | null
  csIsApproved?: boolean | null
  // 담당자 이메일 변경
  orderReviewerEmail?: string
  procurementReviewerEmail?: string
  qualityReviewerEmail?: string
  designReviewerEmail?: string
  csReviewerEmail?: string
}

export interface UpdateEvaluationTargetInput {
  id: number
  // 기본 정보
  adminComment?: string
  consolidatedComment?: string
  ldClaimCount?: number
  ldClaimAmount?: number
  ldClaimCurrency?: "KRW" | "USD" | "EUR" | "JPY"
  consensusStatus?: boolean | null
  
  // 각 부서별 평가 결과
  orderIsApproved?: boolean | null
  procurementIsApproved?: boolean | null
  qualityIsApproved?: boolean | null
  designIsApproved?: boolean | null
  csIsApproved?: boolean | null
  
  // 담당자 이메일 (사용자 ID로 변환됨)
  orderReviewerEmail?: string
  procurementReviewerEmail?: string
  qualityReviewerEmail?: string
  designReviewerEmail?: string
  csReviewerEmail?: string
}

export async function updateEvaluationTarget(input: UpdateEvaluationTargetInput) {
  console.log(input, "update input")
  
  try {
    const session = await auth()
    if (!session?.user) {
      throw new Error("인증이 필요합니다.")
    }

    return await db.transaction(async (tx) => {
      // 평가 대상 존재 확인
      const existing = await tx
        .select({ id: evaluationTargets.id })
        .from(evaluationTargets)
        .where(eq(evaluationTargets.id, input.id))
        .limit(1)

      if (!existing.length) {
        throw new Error("평가 대상을 찾을 수 없습니다.")
      }

      // 1. 기본 정보 업데이트
      const updateFields: Partial<typeof evaluationTargets.$inferInsert> = {}

      if (input.adminComment !== undefined) {
        updateFields.adminComment = input.adminComment
      }
      if (input.consolidatedComment !== undefined) {
        updateFields.consolidatedComment = input.consolidatedComment
      }
      if (input.ldClaimCount !== undefined) {
        updateFields.ldClaimCount = input.ldClaimCount
      }
      if (input.ldClaimAmount !== undefined) {
        updateFields.ldClaimAmount = input.ldClaimAmount.toString()
      }
      if (input.ldClaimCurrency !== undefined) {
        updateFields.ldClaimCurrency = input.ldClaimCurrency
      }
      if (input.consensusStatus !== undefined) {
        updateFields.consensusStatus = input.consensusStatus
      }

      // 기본 정보가 있으면 업데이트
      if (Object.keys(updateFields).length > 0) {
        updateFields.updatedAt = new Date()
        
        await tx
          .update(evaluationTargets)
          .set(updateFields)
          .where(eq(evaluationTargets.id, input.id))
      }

      // 2. 담당자 정보 업데이트
      const reviewerUpdates = [
        { departmentCode: EVALUATION_DEPARTMENT_CODES.ORDER_EVAL, email: input.orderReviewerEmail },
        { departmentCode: EVALUATION_DEPARTMENT_CODES.PROCUREMENT_EVAL, email: input.procurementReviewerEmail },
        { departmentCode: EVALUATION_DEPARTMENT_CODES.QUALITY_EVAL, email: input.qualityReviewerEmail },
        { departmentCode: EVALUATION_DEPARTMENT_CODES.DESIGN_EVAL, email: input.designReviewerEmail },
        { departmentCode: EVALUATION_DEPARTMENT_CODES.CS_EVAL, email: input.csReviewerEmail },
      ]

      for (const update of reviewerUpdates) {
        if (update.email !== undefined) {
          // 기존 담당자 제거
          await tx
            .delete(evaluationTargetReviewers)
            .where(
              and(
                eq(evaluationTargetReviewers.evaluationTargetId, input.id),
                eq(evaluationTargetReviewers.departmentCode, update.departmentCode)
              )
            )

          // 새 담당자 추가 (이메일이 있는 경우만)
          if (update.email) {
            // 이메일로 사용자 ID 조회
            const user = await tx
              .select({ id: users.id })
              .from(users)
              .where(eq(users.email, update.email))
              .limit(1)

            if (user.length > 0) {
              await tx
                .insert(evaluationTargetReviewers)
                .values({
                  evaluationTargetId: input.id,
                  departmentCode: update.departmentCode,
                  reviewerUserId: user[0].id,
                  assignedBy: session.user.id,
                })
            }
          }
        }
      }

      // 3. 평가 결과 업데이트
      const reviewUpdates = [
        { departmentCode: EVALUATION_DEPARTMENT_CODES.ORDER_EVAL, isApproved: input.orderIsApproved },
        { departmentCode: EVALUATION_DEPARTMENT_CODES.PROCUREMENT_EVAL, isApproved: input.procurementIsApproved },
        { departmentCode: EVALUATION_DEPARTMENT_CODES.QUALITY_EVAL, isApproved: input.qualityIsApproved },
        { departmentCode: EVALUATION_DEPARTMENT_CODES.DESIGN_EVAL, isApproved: input.designIsApproved },
        { departmentCode: EVALUATION_DEPARTMENT_CODES.CS_EVAL, isApproved: input.csIsApproved },
      ]

      for (const review of reviewUpdates) {
        if (review.isApproved !== undefined) {
          // 해당 부서의 담당자 조회
          const reviewer = await tx
            .select({ 
              reviewerUserId: evaluationTargetReviewers.reviewerUserId 
            })
            .from(evaluationTargetReviewers)
            .where(
              and(
                eq(evaluationTargetReviewers.evaluationTargetId, input.id),
                eq(evaluationTargetReviewers.departmentCode, review.departmentCode)
              )
            )
            .limit(1)

          if (reviewer.length > 0) {
            // 기존 평가 결과 삭제
            await tx
              .delete(evaluationTargetReviews)
              .where(
                and(
                  eq(evaluationTargetReviews.evaluationTargetId, input.id),
                  eq(evaluationTargetReviews.reviewerUserId, reviewer[0].reviewerUserId)
                )
              )

            // 새 평가 결과 추가 (null이 아닌 경우만)
            if (review.isApproved !== null) {
              await tx
                .insert(evaluationTargetReviews)
                .values({
                  evaluationTargetId: input.id,
                  reviewerUserId: reviewer[0].reviewerUserId,
                  departmentCode: review.departmentCode,
                  isApproved: review.isApproved,
                  reviewedAt: new Date(),
                })
            }
          }
        }
      }

      // 4. 의견 일치 상태 및 전체 상태 자동 계산
      const currentReviews = await tx
        .select({
          isApproved: evaluationTargetReviews.isApproved,
          departmentCode: evaluationTargetReviews.departmentCode,
        })
        .from(evaluationTargetReviews)
        .where(eq(evaluationTargetReviews.evaluationTargetId, input.id))

      console.log("Current reviews:", currentReviews)

      // 최소 3개 부서에서 평가가 완료된 경우 의견 일치 상태 계산
      if (currentReviews.length >= 3) {
        const approvals = currentReviews.map(r => r.isApproved)
        const allApproved = approvals.every(approval => approval === true)
        const allRejected = approvals.every(approval => approval === false)
        const hasConsensus = allApproved || allRejected

        let newStatus: "PENDING" | "CONFIRMED" | "EXCLUDED" = "PENDING"
        if (hasConsensus) {
          newStatus = allApproved ? "CONFIRMED" : "EXCLUDED"
        }

        console.log("Auto-updating status:", { hasConsensus, newStatus, approvals })

        await tx
          .update(evaluationTargets)
          .set({ 
            consensusStatus: hasConsensus,
            status: newStatus,
            confirmedAt: hasConsensus ? new Date() : null,
            confirmedBy: hasConsensus ? session.user.id : null,
            updatedAt: new Date()
          })
          .where(eq(evaluationTargets.id, input.id))
      }

      return {
        success: true,
        message: "평가 대상이 성공적으로 수정되었습니다.",
      }
    })
  } catch (error) {
    console.error("Error updating evaluation target:", error)
    return {
      success: false,
      error: error instanceof Error ? error.message : "평가 대상 수정 중 오류가 발생했습니다.",
    }
  }
}

// 담당자 목록 조회 시 부서 정보도 함께 반환
export async function getAvailableReviewers(departmentCode?: string) {
  try {
    const reviewers = await db
      .select({
        id: users.id,
        name: users.name,
        email: users.email,
        // departmentName: "API로 추후", // ✅ 부서명도 반환
      })
      .from(users)
      .orderBy(users.name)
      .limit(100);

    return reviewers;
  } catch (error) {
    console.error("Error fetching available reviewers:", error);
    return [];
  }
}

// 사용 가능한 벤더 목록 조회
export async function getAvailableVendors(search?: string) {
  try {
    let query = db
      .select({
        id: vendors.id,
        vendorCode: vendors.vendorCode,
        vendorName: vendors.vendorName,
        status: vendors.status,
      })
      .from(vendors)
      .where(
        and(
          // 활성 상태인 벤더만
          // eq(vendors.status, "ACTIVE"),
          // 검색어가 있으면 적용
          search
            ? or(
              ilike(vendors.vendorCode, `%${search}%`),
              ilike(vendors.vendorName, `%${search}%`)
            )
            : undefined
        )
      )
      .orderBy(vendors.vendorName)
      .limit(100);

    return await query;
  } catch (error) {
    console.error("Error fetching available vendors:", error);
    return [];
  }
}


// 부서 정보 조회 (상수에서)
export async function getDepartmentInfo() {
  return Object.entries(EVALUATION_DEPARTMENT_CODES).map(([key, value]) => {
    const departmentNames = {
      ORDER_EVAL: "발주 평가 담당",
      PROCUREMENT_EVAL: "조달 평가 담당",
      QUALITY_EVAL: "품질 평가 담당",
      DESIGN_EVAL: "설계 평가 담당",
      CS_EVAL: "CS 평가 담당",
    };

    return {
      code: value,
      name: departmentNames[key as keyof typeof departmentNames],
      key,
    };
  });
}


export async function confirmEvaluationTargets(targetIds: number[]) {
  try {
    const session = await getServerSession(authOptions)

    if (!session?.user) {
      return { success: false, error: "인증이 필요합니다." }
    }

    if (targetIds.length === 0) {
      return { success: false, error: "선택된 평가 대상이 없습니다." }
    }

    // 트랜잭션으로 처리
    await db.transaction(async (tx) => {
      // 확정 가능한 대상들 확인 (PENDING 상태이면서 consensusStatus가 true인 것들)
      const eligibleTargets = await tx
        .select()
        .from(evaluationTargets)
        .where(
          and(
            inArray(evaluationTargets.id, targetIds),
            eq(evaluationTargets.status, "PENDING"),
            eq(evaluationTargets.consensusStatus, true)
          )
        )

      if (eligibleTargets.length === 0) {
        throw new Error("확정 가능한 평가 대상이 없습니다. (의견 일치 상태인 대기중 항목만 확정 가능)")
      }

      // 상태를 CONFIRMED로 변경
      const confirmedTargetIds = eligibleTargets.map(target => target.id)
      await tx
        .update(evaluationTargets)
        .set({
          status: "CONFIRMED",
          confirmedAt: new Date(),
          confirmedBy: Number(session.user.id),
          updatedAt: new Date()
        })
        .where(inArray(evaluationTargets.id, confirmedTargetIds))

      return confirmedTargetIds
    })


    return { 
      success: true, 
      message: `${targetIds.length}개 평가 대상이 확정되었습니다.`,
      confirmedCount: targetIds.length
    }

  } catch (error) {
    console.error("Error confirming evaluation targets:", error)
    return { 
      success: false, 
      error: error instanceof Error ? error.message : "확정 처리 중 오류가 발생했습니다." 
    }
  }
}

export async function excludeEvaluationTargets(targetIds: number[]) {
  try {
    const session = await getServerSession(authOptions)
    if (!session?.user) {
      return { success: false, error: "인증이 필요합니다." }
    }

    if (targetIds.length === 0) {
      return { success: false, error: "선택된 평가 대상이 없습니다." }
    }

    // 트랜잭션으로 처리
    await db.transaction(async (tx) => {
      // 제외 가능한 대상들 확인 (PENDING 상태인 것들)
      const eligibleTargets = await tx
        .select()
        .from(evaluationTargets)
        .where(
          and(
            inArray(evaluationTargets.id, targetIds),
            eq(evaluationTargets.status, "PENDING")
          )
        )

      if (eligibleTargets.length === 0) {
        throw new Error("제외 가능한 평가 대상이 없습니다. (대기중 상태인 항목만 제외 가능)")
      }

      // 상태를 EXCLUDED로 변경
      const excludedTargetIds = eligibleTargets.map(target => target.id)
      await tx
        .update(evaluationTargets)
        .set({
          status: "EXCLUDED",
          updatedAt: new Date()
        })
        .where(inArray(evaluationTargets.id, excludedTargetIds))

      return excludedTargetIds
    })


    return { 
      success: true, 
      message: `${targetIds.length}개 평가 대상이 제외되었습니다.`,
      excludedCount: targetIds.length
    }

  } catch (error) {
    console.error("Error excluding evaluation targets:", error)
    return { 
      success: false, 
      error: error instanceof Error ? error.message : "제외 처리 중 오류가 발생했습니다." 
    }
  }
}

export async function requestEvaluationReview(targetIds: number[], message?: string) {
  try {
    const session = await auth()
    if (!session?.user) {
      return { success: false, error: "인증이 필요합니다." }
    }

    if (targetIds.length === 0) {
      return { success: false, error: "선택된 평가 대상이 없습니다." }
    }

    // 선택된 평가 대상들과 담당자 정보 조회
    const targetsWithReviewers = await db
      .select({
        id: evaluationTargets.id,
        vendorCode: evaluationTargets.vendorCode,
        vendorName: evaluationTargets.vendorName,
        materialType: evaluationTargets.materialType,
        evaluationYear: evaluationTargets.evaluationYear,
        status: evaluationTargets.status,
        reviewerEmail: users.email,
        reviewerName: users.name,
        departmentCode: evaluationTargetReviewers.departmentCode,
        departmentName: evaluationTargetReviewers.departmentNameFrom,
      })
      .from(evaluationTargets)
      .leftJoin(
        evaluationTargetReviewers,
        eq(evaluationTargets.id, evaluationTargetReviewers.evaluationTargetId)
      )
      .leftJoin(
        users,
        eq(evaluationTargetReviewers.reviewerUserId, users.id)
      )
      .where(
        and(
          inArray(evaluationTargets.id, targetIds),
          eq(evaluationTargets.status, "PENDING")
        )
      )

    if (targetsWithReviewers.length === 0) {
      return { success: false, error: "의견 요청 가능한 평가 대상이 없습니다." }
    }

    // 평가 대상별로 그룹화
    const targetGroups = targetsWithReviewers.reduce((acc, item) => {
      if (!acc[item.id]) {
        acc[item.id] = {
          id: item.id,
          vendorCode: item.vendorCode,
          vendorName: item.vendorName,
          materialType: item.materialType,
          evaluationYear: item.evaluationYear,
          reviewers: []
        }
      }
      
      if (item.reviewerEmail) {
        acc[item.id].reviewers.push({
          email: item.reviewerEmail,
          name: item.reviewerName,
          departmentCode: item.departmentCode,
          departmentName: item.departmentName
        })
      }
      
      return acc
    }, {} as Record<number, any>)

    const targets = Object.values(targetGroups)

    // 모든 담당자 이메일 수집 (중복 제거)
    const reviewerEmails = new Set<string>()
    const reviewerInfo = new Map<string, { name: string; departments: string[] }>()

    targets.forEach(target => {
      target.reviewers.forEach((reviewer: any) => {
        if (reviewer.email) {
          reviewerEmails.add(reviewer.email)
          
          if (!reviewerInfo.has(reviewer.email)) {
            reviewerInfo.set(reviewer.email, {
              name: reviewer.name || reviewer.email,
              departments: []
            })
          }
          
          const info = reviewerInfo.get(reviewer.email)!
          if (reviewer.departmentName && !info.departments.includes(reviewer.departmentName)) {
            info.departments.push(reviewer.departmentName)
          }
        }
      })
    })

    if (reviewerEmails.size === 0) {
      return { success: false, error: "담당자가 지정되지 않은 평가 대상입니다." }
    }

    // 각 담당자에게 이메일 발송
    const emailPromises = Array.from(reviewerEmails).map(email => {
      const reviewer = reviewerInfo.get(email)!
      
      return sendEmail({
        to: email,
        subject: `벤더 평가 의견 요청 - ${targets.length}건`,
        template: "evaluation-review-request",
        context: {
          requesterName: session.user.name || session.user.email,
          reviewerName: reviewer.name,
          targetCount: targets.length,
          targets: targets.map(target => ({
            vendorCode: target.vendorCode,
            vendorName: target.vendorName,
            materialType: target.materialType,
            evaluationYear: target.evaluationYear
          })),
          message: message || "",
          reviewUrl: `${process.env.NEXTAUTH_URL}/evaluation-targets`,
          requestDate: new Date().toLocaleString('ko-KR')
        }
      })
    })

    await Promise.all(emailPromises)

    revalidatePath("/evaluation-targets")
    return { 
      success: true, 
      message: `${reviewerEmails.size}명의 담당자에게 의견 요청 이메일이 발송되었습니다.`,
      emailCount: reviewerEmails.size
    }

  } catch (error) {
    console.error("Error requesting evaluation review:", error)
    return { 
      success: false, 
      error: error instanceof Error ? error.message : "의견 요청 중 오류가 발생했습니다." 
    }
  }
}