'use server'

import { and, or, desc, asc, ilike, eq, isNull, sql, count, inArray, gte, lte } 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,
  periodicEvaluations,
  reviewerEvaluations,
  evaluationSubmissions,
  generalEvaluations,
  esgEvaluationItems,
  contracts,
  projects
} 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";
import type { SQL } from "drizzle-orm"
import { DEPARTMENT_CODE_LABELS } from "@/types/evaluation";
import { revalidatePath } from "next/cache";

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;

    // ✅ getRFQ 방식과 동일한 필터링 처리
    // 1) 고급 필터 조건
    let advancedWhere: SQL<unknown> | undefined = undefined;
    if (input.filters && input.filters.length > 0) {
      advancedWhere = filterColumns({
        table: evaluationTargetsWithDepartments,
        filters: input.filters,
        joinOperator: input.joinOperator || 'and',
      });
    }

    // 2) 기본 필터 조건
    let basicWhere: SQL<unknown> | undefined = undefined;
    if (input.basicFilters && input.basicFilters.length > 0) {
      basicWhere = filterColumns({
        table: evaluationTargetsWithDepartments,
        filters: input.basicFilters,
        joinOperator: input.basicJoinOperator || 'and',
      });
    }

    // 3) 글로벌 검색 조건
    let globalWhere: SQL<unknown> | undefined = undefined;
    if (input.search) {
      const s = `%${input.search}%`;

      const validSearchConditions: SQL<unknown>[] = [];

      const vendorCodeCondition = ilike(evaluationTargetsWithDepartments.vendorCode, s);
      if (vendorCodeCondition) validSearchConditions.push(vendorCodeCondition);

      const vendorNameCondition = ilike(evaluationTargetsWithDepartments.vendorName, s);
      if (vendorNameCondition) validSearchConditions.push(vendorNameCondition);

      const adminCommentCondition = ilike(evaluationTargetsWithDepartments.adminComment, s);
      if (adminCommentCondition) validSearchConditions.push(adminCommentCondition);

      const consolidatedCommentCondition = ilike(evaluationTargetsWithDepartments.consolidatedComment, s);
      if (consolidatedCommentCondition) validSearchConditions.push(consolidatedCommentCondition);

      // 담당자 이름으로도 검색
      const orderReviewerCondition = ilike(evaluationTargetsWithDepartments.orderReviewerName, s);
      if (orderReviewerCondition) validSearchConditions.push(orderReviewerCondition);

      const procurementReviewerCondition = ilike(evaluationTargetsWithDepartments.procurementReviewerName, s);
      if (procurementReviewerCondition) validSearchConditions.push(procurementReviewerCondition);

      const qualityReviewerCondition = ilike(evaluationTargetsWithDepartments.qualityReviewerName, s);
      if (qualityReviewerCondition) validSearchConditions.push(qualityReviewerCondition);

      const designReviewerCondition = ilike(evaluationTargetsWithDepartments.designReviewerName, s);
      if (designReviewerCondition) validSearchConditions.push(designReviewerCondition);

      const csReviewerCondition = ilike(evaluationTargetsWithDepartments.csReviewerName, s);
      if (csReviewerCondition) validSearchConditions.push(csReviewerCondition);

      if (validSearchConditions.length > 0) {
        globalWhere = or(...validSearchConditions);
      }
    }

    // ✅ getRFQ 방식과 동일한 WHERE 조건 생성
    const whereConditions: SQL<unknown>[] = [];

    if (advancedWhere) whereConditions.push(advancedWhere);
    if (basicWhere) whereConditions.push(basicWhere);
    if (globalWhere) whereConditions.push(globalWhere);

    const finalWhere = whereConditions.length > 0 ? and(...whereConditions) : undefined;

    // ✅ getRFQ 방식과 동일한 전체 데이터 수 조회 (Transaction 제거)
    const totalResult = await db
      .select({ count: count() })
      .from(evaluationTargetsWithDepartments)
      .where(finalWhere);

    const total = totalResult[0]?.count || 0;

    if (total === 0) {
      return { data: [], pageCount: 0, total: 0 };
    }

    console.log("Total evaluation targets:", total);

    // ✅ getRFQ 방식과 동일한 정렬 및 페이징 처리된 데이터 조회
    const orderByColumns = input.sort.map((sort) => {
      const column = sort.id as keyof typeof evaluationTargetsWithDepartments.$inferSelect;
      return sort.desc ? desc(evaluationTargetsWithDepartments[column]) : asc(evaluationTargetsWithDepartments[column]);
    });

    if (orderByColumns.length === 0) {
      orderByColumns.push(desc(evaluationTargetsWithDepartments.createdAt));
    }

    const evaluationData = await db
      .select()
      .from(evaluationTargetsWithDepartments)
      .where(finalWhere)
      .orderBy(...orderByColumns)
      .limit(input.perPage)
      .offset(offset);

    const pageCount = Math.ceil(total / input.perPage);

    return { data: evaluationData, pageCount, total };
  } catch (err) {
    console.error("Error in getEvaluationTargets:", err);
    // ✅ getRFQ 방식과 동일한 에러 반환 (total 포함)
    return { data: [], pageCount: 0, total: 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 = 'PLANT' then 1 else 0 end)`,
          shipyardDivision: sql<number>`sum(case when division = 'SHIP' 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 getServerSession(authOptions)

    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: Number(user[0].id),
                  assignedBy:Number( 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 })
        console.log("Auto-updating status:", { hasConsensus, approvals })

        await tx
          .update(evaluationTargets)
          .set({ 
            consensusStatus: hasConsensus,
            confirmedAt: hasConsensus ? new Date() : null,
            confirmedBy: hasConsensus ? Number(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]) => {

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


export async function confirmEvaluationTargets(
  targetIds: number[], 
  evaluationPeriod?: string // "상반기", "하반기", "연간" 등
) {
  try {
    const session = await getServerSession(authOptions)
    
    if (!session?.user) {
      return { success: false, error: "인증이 필요합니다." }
    }
    
    if (targetIds.length === 0) {
      return { success: false, error: "선택된 평가 대상이 없습니다." }
    }

    // 평가 기간이 없으면 현재 날짜 기준으로 자동 결정
    // const currentPeriod = evaluationPeriod || getCurrentEvaluationPeriod()
    const currentPeriod ="연간"
    
    // 트랜잭션으로 처리
    const result = 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("확정 가능한 평가 대상이 없습니다. (의견 일치 상태인 대기중 항목만 확정 가능)")
      }
      
      const confirmedTargetIds = eligibleTargets.map(target => target.id)
      
      // 1. 평가 대상 상태를 CONFIRMED로 변경
      await tx
        .update(evaluationTargets)
        .set({
          status: "CONFIRMED",
          confirmedAt: new Date(),
          confirmedBy: Number(session.user.id),
          updatedAt: new Date()
        })
        .where(inArray(evaluationTargets.id, confirmedTargetIds))
      
      // 2. 각 확정된 평가 대상에 대해 periodicEvaluations 레코드 생성
      const periodicEvaluationsToCreate = []
      
      for (const target of eligibleTargets) {
        // 이미 해당 기간에 평가가 존재하는지 확인
        const existingEvaluation = await tx
          .select({ id: periodicEvaluations.id })
          .from(periodicEvaluations)
          .where(
            and(
              eq(periodicEvaluations.evaluationTargetId, target.id),
              // eq(periodicEvaluations.evaluationPeriod, currentPeriod)
            )
          )
          .limit(1)
        
        // 없으면 생성 목록에 추가
        if (existingEvaluation.length === 0) {
          periodicEvaluationsToCreate.push({
            evaluationTargetId: target.id,
            evaluationPeriod: currentPeriod,
            // 평가년도에 따른 제출 마감일 설정 (예: 상반기는 7월 말, 하반기는 1월 말)
            submissionDeadline: getSubmissionDeadline(target.evaluationYear, currentPeriod),
            status: "PENDING_SUBMISSION" as const,
            createdAt: new Date(),
            updatedAt: new Date()
          })
        }
        console.log("periodicEvaluationsToCreate", periodicEvaluationsToCreate)
      }
      
      // 3. periodicEvaluations 레코드들 일괄 생성
      let createdEvaluationsCount = 0
      if (periodicEvaluationsToCreate.length > 0) {
        const createdEvaluations = await tx
          .insert(periodicEvaluations)
          .values(periodicEvaluationsToCreate)
          .returning({ id: periodicEvaluations.id })
        
        createdEvaluationsCount = createdEvaluations.length
      }
      console.log("createdEvaluationsCount", createdEvaluationsCount)
      // 4. 평가 항목 수 조회 (evaluationSubmissions 생성을 위해)
      const [generalItemsCount, esgItemsCount] = await Promise.all([
        // 활성화된 일반평가 항목 수
        tx.select({ count: count() })
          .from(generalEvaluations)
          .where(eq(generalEvaluations.isActive, true)),
        
        // 활성화된 ESG 평가항목 수
        tx.select({ count: count() })
          .from(esgEvaluationItems)
          .where(eq(esgEvaluationItems.isActive, true))
      ])
      
      const totalGeneralItems = generalItemsCount[0]?.count || 0
      const totalEsgItems = esgItemsCount[0]?.count || 0

      // 5. 각 periodicEvaluation에 대해 담당자별 reviewerEvaluations도 생성
      // if (periodicEvaluationsToCreate.length > 0) {
      //   // 새로 생성된 periodicEvaluations 조회
      //   const newPeriodicEvaluations = await tx
      //     .select({
      //       id: periodicEvaluations.id,
      //       evaluationTargetId: periodicEvaluations.evaluationTargetId
      //     })
      //     .from(periodicEvaluations)
      //     .where(
      //       and(
      //         inArray(periodicEvaluations.evaluationTargetId, confirmedTargetIds),
      //         eq(periodicEvaluations.evaluationPeriod, currentPeriod)
      //       )
      //     )
        
      //   // 각 평가에 대해 담당자별 reviewerEvaluations 생성
      //   for (const periodicEval of newPeriodicEvaluations) {
      //     // 해당 evaluationTarget의 담당자들 조회
      //     const reviewers = await tx
      //       .select()
      //       .from(evaluationTargetReviewers)
      //       .where(eq(evaluationTargetReviewers.evaluationTargetId, periodicEval.evaluationTargetId))
          
      //     if (reviewers.length > 0) {
      //       const reviewerEvaluationsToCreate = reviewers.map(reviewer => ({
      //         periodicEvaluationId: periodicEval.id,
      //         evaluationTargetReviewerId: reviewer.id,
      //         isCompleted: false,
      //         createdAt: new Date(),
      //         updatedAt: new Date()
      //       }))
            
      //       await tx
      //         .insert(reviewerEvaluations)
      //         .values(reviewerEvaluationsToCreate)
      //     }
      //   }
      // }
      
      // 6. 벤더별 evaluationSubmissions 레코드 생성
      const evaluationSubmissionsToCreate = []
      
      // 생성된 periodicEvaluations의 ID를 매핑하기 위한 맵 생성
      const periodicEvaluationIdMap = new Map()
      if (createdEvaluationsCount > 0) {
        const createdEvaluations = await tx
          .select({
            id: periodicEvaluations.id,
            evaluationTargetId: periodicEvaluations.evaluationTargetId
          })
          .from(periodicEvaluations)
          .where(
            and(
              inArray(periodicEvaluations.evaluationTargetId, confirmedTargetIds),
              eq(periodicEvaluations.evaluationPeriod, currentPeriod)
            )
          )
        
                 // evaluationTargetId를 키로 하는 맵 생성
         createdEvaluations.forEach(periodicEval => {
           periodicEvaluationIdMap.set(periodicEval.evaluationTargetId, periodicEval.id)
         })
      }
      console.log("periodicEvaluationIdMap", periodicEvaluationIdMap)
      
      for (const target of eligibleTargets) {
        // 이미 해당 년도/기간에 제출 레코드가 있는지 확인
        const existingSubmission = await tx
          .select({ id: evaluationSubmissions.id })
          .from(evaluationSubmissions)
          .where(
            and(
              eq(evaluationSubmissions.companyId, target.vendorId),
              eq(evaluationSubmissions.evaluationYear, target.evaluationYear),
              // eq(evaluationSubmissions.evaluationRound, currentPeriod)
            )
          )
          .limit(1)
        
        // 없으면 생성 목록에 추가
        if (existingSubmission.length === 0) {
          const periodicEvaluationId = periodicEvaluationIdMap.get(target.id)
          if (periodicEvaluationId) {
            evaluationSubmissionsToCreate.push({
              companyId: target.vendorId,
              periodicEvaluationId: periodicEvaluationId,
              evaluationYear: target.evaluationYear,
              evaluationRound: currentPeriod,
              submissionStatus: "draft" as const,
              totalGeneralItems: totalGeneralItems,
              completedGeneralItems: 0,
              totalEsgItems: totalEsgItems,
              completedEsgItems: 0,
              isActive: true,
              createdAt: new Date(),
              updatedAt: new Date()
            })
          }
        }
      }
      // 7. evaluationSubmissions 레코드들 일괄 생성
      let createdSubmissionsCount = 0
      if (evaluationSubmissionsToCreate.length > 0) {
        const createdSubmissions = await tx
          .insert(evaluationSubmissions)
          .values(evaluationSubmissionsToCreate)
          .returning({ id: evaluationSubmissions.id })
        
        createdSubmissionsCount = createdSubmissions.length
      }
      
      return {
        confirmedTargetIds,
        createdEvaluationsCount,
        createdSubmissionsCount,
        totalConfirmed: confirmedTargetIds.length
      }
    })
    
    return {
      success: true,
      message: `${result.totalConfirmed}개 평가 대상이 확정되었습니다. ${result.createdEvaluationsCount}개의 정기평가와 ${result.createdSubmissionsCount}개의 제출 요청이 생성되었습니다.`,
      confirmedCount: result.totalConfirmed,
      createdEvaluationsCount: result.createdEvaluationsCount,
      createdSubmissionsCount: result.createdSubmissionsCount
    }
    
  } catch (error) {
    console.error("Error confirming evaluation targets:", error)
    return {
      success: false,
      error: error instanceof Error ? error.message : "확정 처리 중 오류가 발생했습니다."
    }
  }
}


// 현재 날짜 기준으로 평가 기간 결정하는 헬퍼 함수
function getCurrentEvaluationPeriod(): string {
  const now = new Date()
  const month = now.getMonth() + 1 // 0-based이므로 +1
  
  // 1~6월: 상반기, 7~12월: 하반기
  return month <= 6 ? "상반기" : "하반기"
}

// 평가년도와 기간에 따른 제출 마감일 설정하는 헬퍼 함수
function getSubmissionDeadline(evaluationYear: number, period: string): Date {
  const year = evaluationYear
  
  if (period === "상반기") {
    // 상반기 평가는 다음 해 6월 말까지
    return new Date(year, 5, 31) // 7월은 6 (0-based)
  } else if (period === "하반기") {
    // 하반기 평가는 다음 올해 12월 말까지
    return new Date(year, 11, 31) // 1월은 0 (0-based)
  } else {
    // 연간 평가는 올해 6월 말까지
    return new Date(year, 5, 31) // 3월은 2 (0-based)
  }
}

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 getServerSession(authOptions)

    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}/evcp/evaluation-target-list`,
          requestDate: new Date().toLocaleString('ko-KR')
        }
      })
    })

    await Promise.all(emailPromises)

    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 : "의견 요청 중 오류가 발생했습니다." 
    }
  }
}



interface AutoGenerateResult {
  success: boolean
  message: string
  error?: string
  generatedCount?: number
  skippedCount?: number
  details?: {
    shipTargets: number
    plantTargets: number
    duplicateSkipped: number
  }
}

/**
 * 자동으로 평가 대상을 생성하는 서버 액션
 * 전년도 10월부터 현재년도 9월까지의 계약을 기준으로 평가 대상을 생성
 */
export async function autoGenerateEvaluationTargets(
  evaluationYear: number,
  adminUserId: number
): Promise<AutoGenerateResult> {
  try {
    // 평가 기간 계산 (전년도 10월 ~ 현재년도 9월)
    const startDate = `${evaluationYear - 1}-10-01`
    const endDate = `${evaluationYear}-09-30`

    console.log(`Generating evaluation targets for period: ${startDate} to ${endDate}`)

    // 1. 해당 기간의 계약들과 관련 정보를 조회
    const contractsWithDetails = await db
      .select({
        contractId: contracts.id,
        vendorId: contracts.vendorId,
        projectId: contracts.projectId,
        startDate: contracts.startDate,
        // vendor 정보
        vendorCode: vendors.vendorCode,
        vendorName: vendors.vendorName,
        vendorType: vendors.country ==="KR"? "DOMESTIC":"FOREIGN", // DOMESTIC | FOREIGN
        // project 정보  
        projectType: projects.type, // ship | plant
      })
      .from(contracts)
      .innerJoin(vendors, eq(contracts.vendorId, vendors.id))
      .innerJoin(projects, eq(contracts.projectId, projects.id))
      .where(
        and(
          gte(contracts.startDate, startDate),
          lte(contracts.startDate, endDate)
        )
      )

    if (contractsWithDetails.length === 0) {
      return {
        success: true,
        message: "해당 기간에 생성할 평가 대상이 없습니다.",
        generatedCount: 0,
        skippedCount: 0
      }
    }

    console.log(`Found ${contractsWithDetails.length} contracts in the period`)

    // 2. 벤더별, 구분별로 그룹화하여 중복 제거
    const targetGroups = new Map<string, {
      vendorId: number
      vendorCode: string
      vendorName: string
      domesticForeign: "DOMESTIC" | "FOREIGN"
      division: "SHIP" | "PLANT"
      materialType: "EQUIPMENT" | "BULK" | "EQUIPMENT_BULK"
    }>()

    contractsWithDetails.forEach(contract => {
      const division = contract.projectType === "ship" ? "SHIP" : "PLANT"
      const key = `${contract.vendorId}-${division}`
      
      if (!targetGroups.has(key)) {
        targetGroups.set(key, {
          vendorId: contract.vendorId,
          vendorCode: contract.vendorCode,
          vendorName: contract.vendorName,
          domesticForeign: contract.vendorType === "DOMESTIC" ? "DOMESTIC" : "FOREIGN",
          division: division as "SHIP" | "PLANT",
          // 기본값으로 EQUIPMENT 설정 (추후 더 정교한 로직 필요시 수정)
          materialType: "EQUIPMENT" as const
        })
      }
    })

    console.log(`Created ${targetGroups.size} unique vendor-division combinations`)

    // 3. 이미 존재하는 평가 대상 확인
    const existingTargetsKeys = new Set<string>()
    if (targetGroups.size > 0) {
      const vendorIds = Array.from(targetGroups.values()).map(t => t.vendorId)
      const existingTargets = await db
        .select({
          vendorId: evaluationTargets.vendorId,
          division: evaluationTargets.division
        })
        .from(evaluationTargets)
        .where(
          and(
            eq(evaluationTargets.evaluationYear, evaluationYear),
            inArray(evaluationTargets.vendorId, vendorIds)
          )
        )

      existingTargets.forEach(target => {
        existingTargetsKeys.add(`${target.vendorId}-${target.division}`)
      })
    }

    console.log(`Found ${existingTargetsKeys.size} existing targets`)

    // 4. 새로운 평가 대상만 필터링
    const newTargets = Array.from(targetGroups.entries())
      .filter(([key]) => !existingTargetsKeys.has(key))
      .map(([_, target]) => target)

    if (newTargets.length === 0) {
      return {
        success: true,
        message: "이미 모든 평가 대상이 생성되어 있습니다.",
        generatedCount: 0,
        skippedCount: targetGroups.size
      }
    }

    // 5. 평가 대상 생성
    const evaluationTargetsToInsert = newTargets.map(target => ({
      evaluationYear,
      division: target.division,
      vendorId: target.vendorId,
      vendorCode: target.vendorCode,
      vendorName: target.vendorName,
      domesticForeign: target.domesticForeign,
      materialType: target.materialType,
      status: "PENDING" as const,
      adminUserId,
      ldClaimCount: 0,
      ldClaimAmount: "0",
      ldClaimCurrency: "KRW" as const
    }))

    // 배치로 삽입
    await db.insert(evaluationTargets).values(evaluationTargetsToInsert)

    // 통계 계산
    const shipTargets = newTargets.filter(t => t.division === "SHIP").length
    const plantTargets = newTargets.filter(t => t.division === "PLANT").length
    const duplicateSkipped = existingTargetsKeys.size

    console.log(`Successfully created ${newTargets.length} evaluation targets`)

    // 캐시 무효화
    revalidatePath("/evcp/evaluation-target-list")
    revalidatePath("/procurement/evaluation-target-list")

    return {
      success: true,
      message: `${newTargets.length}개의 평가 대상이 성공적으로 생성되었습니다.`,
      generatedCount: newTargets.length,
      skippedCount: duplicateSkipped,
      details: {
        shipTargets,
        plantTargets,
        duplicateSkipped
      }
    }

  } catch (error) {
    console.error("Error auto generating evaluation targets:", error)
    return {
      success: false,
      error: error instanceof Error ? error.message : "알 수 없는 오류가 발생했습니다.",
      message: "평가 대상 자동 생성에 실패했습니다."
    }
  }
}