import db from "@/db/db"
import { 
  periodicEvaluationsView, 
  type PeriodicEvaluationView 
} from "@/db/schema"
import { 
  and, 
  asc, 
  count, 
  desc, 
  ilike, 
  or, 
  type SQL 
} from "drizzle-orm"
import { filterColumns } from "@/lib/filter-columns"
import { GetEvaluationTargetsSchema } from "../evaluation-target-list/validation";

export async function getPeriodicEvaluations(input: GetEvaluationTargetsSchema) {
    try {
      const offset = (input.page - 1) * input.perPage;
  
      // ✅ getEvaluationTargets 방식과 동일한 필터링 처리
      // 1) 고급 필터 조건
      let advancedWhere: SQL<unknown> | undefined = undefined;
      if (input.filters && input.filters.length > 0) {
        advancedWhere = filterColumns({
          table: periodicEvaluationsView,
          filters: input.filters,
          joinOperator: input.joinOperator || 'and',
        });
      }
  
      // 2) 기본 필터 조건
      let basicWhere: SQL<unknown> | undefined = undefined;
      if (input.basicFilters && input.basicFilters.length > 0) {
        basicWhere = filterColumns({
          table: periodicEvaluationsView,
          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(periodicEvaluationsView.vendorCode, s);
        if (vendorCodeCondition) validSearchConditions.push(vendorCodeCondition);
  
        const vendorNameCondition = ilike(periodicEvaluationsView.vendorName, s);
        if (vendorNameCondition) validSearchConditions.push(vendorNameCondition);
  
        // 평가 관련 코멘트로 검색
        const evaluationNoteCondition = ilike(periodicEvaluationsView.evaluationNote, s);
        if (evaluationNoteCondition) validSearchConditions.push(evaluationNoteCondition);
  
        const adminCommentCondition = ilike(periodicEvaluationsView.evaluationTargetAdminComment, s);
        if (adminCommentCondition) validSearchConditions.push(adminCommentCondition);
  
        const consolidatedCommentCondition = ilike(periodicEvaluationsView.evaluationTargetConsolidatedComment, s);
        if (consolidatedCommentCondition) validSearchConditions.push(consolidatedCommentCondition);
  
        // 최종 확정자 이름으로 검색
        const finalizedByUserNameCondition = ilike(periodicEvaluationsView.finalizedByUserName, s);
        if (finalizedByUserNameCondition) validSearchConditions.push(finalizedByUserNameCondition);
  
        if (validSearchConditions.length > 0) {
          globalWhere = or(...validSearchConditions);
        }
      }
  
      // ✅ getEvaluationTargets 방식과 동일한 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;
  
      // ✅ getEvaluationTargets 방식과 동일한 전체 데이터 수 조회
      const totalResult = await db
        .select({ count: count() })
        .from(periodicEvaluationsView)
        .where(finalWhere);
  
      const total = totalResult[0]?.count || 0;
  
      if (total === 0) {
        return { data: [], pageCount: 0, total: 0 };
      }
  
      console.log("Total periodic evaluations:", total);
  
      // ✅ getEvaluationTargets 방식과 동일한 정렬 및 페이징 처리된 데이터 조회
      const orderByColumns = input.sort.map((sort) => {
        const column = sort.id as keyof typeof periodicEvaluationsView.$inferSelect;
        return sort.desc ? desc(periodicEvaluationsView[column]) : asc(periodicEvaluationsView[column]);
      });
  
      if (orderByColumns.length === 0) {
        orderByColumns.push(desc(periodicEvaluationsView.createdAt));
      }
  
      const periodicEvaluationsData = await db
        .select()
        .from(periodicEvaluationsView)
        .where(finalWhere)
        .orderBy(...orderByColumns)
        .limit(input.perPage)
        .offset(offset);
  
      const pageCount = Math.ceil(total / input.perPage);
  
      return { data: periodicEvaluationsData, pageCount, total };
    } catch (err) {
      console.error("Error in getPeriodicEvaluations:", err);
      // ✅ getEvaluationTargets 방식과 동일한 에러 반환 (total 포함)
      return { data: [], pageCount: 0, total: 0 };
    }
  }