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

'use server'

import { and, or, desc, asc, ilike, eq, isNull, sql, count } 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";


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: "OCEAN" | "SHIPYARD"
  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 newEvaluationTarget = await tx
        .insert(evaluationTargets)
        .values({
          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,
          ldClaimAmount: input.ldClaimAmount?.toString() || "0",
          ldClaimCurrency: input.ldClaimCurrency || "KRW",
        })
        .returning({ id: evaluationTargets.id });

      const evaluationTargetId = newEvaluationTarget[0].id;

      // ✅ 담당자들 지정 (departmentNameFrom 추가)
      if (input.reviewers && input.reviewers.length > 0) {
        // 담당자들의 부서 정보 조회
        const reviewerIds = input.reviewers.map(r => r.reviewerUserId);
        const reviewerInfos = await tx
          .select({
            id: users.id,
            departmentName: users.departmentName, // users 테이블에 부서명 필드가 있다고 가정
          })
          .from(users)
          .where(sql`${users.id} = ANY(${reviewerIds})`);

        const reviewerAssignments = input.reviewers.map((reviewer) => {
          const reviewerInfo = reviewerInfos.find(info => info.id === reviewer.reviewerUserId);
          
          return {
            evaluationTargetId,
            departmentCode: reviewer.departmentCode,
            departmentNameFrom: reviewerInfo?.departmentName || null, // ✅ 실제 부서명 저장
            reviewerUserId: reviewer.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 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,
    };
  });
}