import { unstable_cache } from "next/cache"
import { count, desc, asc, and, or, gte, lte, ilike, eq } from "drizzle-orm"
import { filterColumns } from "@/lib/filter-columns"
import db from "@/db/db"
import { bRfqs, projects, users } from "@/db/schema" // 실제 스키마 import 경로에 맞게 수정
import { rfqDashboardView } from "@/db/schema" // 뷰 import
import type { SQL } from "drizzle-orm"
import { GetRFQDashboardSchema } from "./validations"

export async function getRFQDashboard(input: GetRFQDashboardSchema) {
  return unstable_cache(
    async () => {
      try {
        const offset = (input.page - 1) * input.perPage;

        const rfqFilterMapping = createRFQFilterMapping();
        const joinedTables = getRFQJoinedTables();

        console.log(input, "RFQ Dashboard input")

        // 1) 고급 필터 조건
        let advancedWhere: SQL<unknown> | undefined = undefined;
        if (input.filters && input.filters.length > 0) {
          advancedWhere = filterColumns({
            table: rfqDashboardView,
            filters: input.filters,
            joinOperator: input.joinOperator || 'and',
            joinedTables,
            customColumnMapping: rfqFilterMapping,
          });
        }

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

        // 3) 글로벌 검색 조건
        let globalWhere: SQL<unknown> | undefined = undefined;
        if (input.search) {
          const s = `%${input.search}%`;
          
          const validSearchConditions: SQL<unknown>[] = [];
          
          const rfqCodeCondition = ilike(rfqDashboardView.rfqCode, s);
          if (rfqCodeCondition) validSearchConditions.push(rfqCodeCondition);
          
          const descriptionCondition = ilike(rfqDashboardView.description, s);
          if (descriptionCondition) validSearchConditions.push(descriptionCondition);
          
          const projectNameCondition = ilike(rfqDashboardView.projectName, s);
          if (projectNameCondition) validSearchConditions.push(projectNameCondition);
          
          const projectCodeCondition = ilike(rfqDashboardView.projectCode, s);
          if (projectCodeCondition) validSearchConditions.push(projectCodeCondition);
          
          const picNameCondition = ilike(rfqDashboardView.picName, s);
          if (picNameCondition) validSearchConditions.push(picNameCondition);
          
          const packageNoCondition = ilike(rfqDashboardView.packageNo, s);
          if (packageNoCondition) validSearchConditions.push(packageNoCondition);
          
          const packageNameCondition = ilike(rfqDashboardView.packageName, s);
          if (packageNameCondition) validSearchConditions.push(packageNameCondition);
          
          if (validSearchConditions.length > 0) {
            globalWhere = or(...validSearchConditions);
          }
        }

        // 4) 날짜 조건
        let dueDateFromWhere: SQL<unknown> | undefined = undefined;
        let dueDateToWhere: SQL<unknown> | undefined = undefined;
        
        if (input.dueDateFrom) {
          const fromDate = new Date(input.dueDateFrom);
          dueDateFromWhere = gte(rfqDashboardView.dueDate, fromDate);
        }
        
        if (input.dueDateTo) {
          const toDate = new Date(input.dueDateTo);
          dueDateToWhere = lte(rfqDashboardView.dueDate, toDate);
        }

        // 5) 진행률 조건
        let progressWhere: SQL<unknown> | undefined = undefined;
        if (input.progressMin > 0 || input.progressMax < 100) {
          const progressConditions: SQL<unknown>[] = [];
          if (input.progressMin > 0) {
            progressConditions.push(gte(rfqDashboardView.overallProgress, input.progressMin));
          }
          if (input.progressMax < 100) {
            progressConditions.push(lte(rfqDashboardView.overallProgress, input.progressMax));
          }
          if (progressConditions.length > 0) {
            progressWhere = and(...progressConditions);
          }
        }

        // 6) 최종 WHERE 조건 생성
        const whereConditions: SQL<unknown>[] = [];
        
        if (advancedWhere) whereConditions.push(advancedWhere);
        if (basicWhere) whereConditions.push(basicWhere);
        if (globalWhere) whereConditions.push(globalWhere);
        if (dueDateFromWhere) whereConditions.push(dueDateFromWhere);
        if (dueDateToWhere) whereConditions.push(dueDateToWhere);
        if (progressWhere) whereConditions.push(progressWhere);
        
        const finalWhere = whereConditions.length > 0 ? and(...whereConditions) : undefined;

        // 7) 전체 데이터 수 조회
        const totalResult = await db
          .select({ count: count() })
          .from(rfqDashboardView)
          .where(finalWhere);

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

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

        // 8) 정렬 및 페이징 처리된 데이터 조회
        const orderByColumns = input.sort.map((sort) => {
          const column = sort.id as keyof typeof rfqDashboardView.$inferSelect;
          return sort.desc ? desc(rfqDashboardView[column]) : asc(rfqDashboardView[column]);
        });

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

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

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

        return { data: rfqData, pageCount, total };
      } catch (err) {
        console.error("Error in getRFQDashboard:", err);
        return { data: [], pageCount: 0, total: 0 };
      }
    },
    [JSON.stringify(input)],
    {
      revalidate: 3600,
      tags: ["rfq-dashboard"],
    }
  )();
}

// 헬퍼 함수들
function createRFQFilterMapping() {
  return {
    // 뷰의 컬럼명과 실제 필터링할 컬럼 매핑
    rfqCode: rfqDashboardView.rfqCode,
    description: rfqDashboardView.description,
    status: rfqDashboardView.status,
    projectName: rfqDashboardView.projectName,
    projectCode: rfqDashboardView.projectCode,
    picName: rfqDashboardView.picName,
    packageNo: rfqDashboardView.packageNo,
    packageName: rfqDashboardView.packageName,
    dueDate: rfqDashboardView.dueDate,
    overallProgress: rfqDashboardView.overallProgress,
    createdAt: rfqDashboardView.createdAt,
  };
}

function getRFQJoinedTables() {
  return {
    // 조인된 테이블 정보 (뷰이므로 실제로는 사용되지 않을 수 있음)
    projects,
    users,
  };
}

// ================================================================
// 3. RFQ Dashboard 타입 정의
// ================================================================
export interface RFQDashboard {
  rfqId: number;
  rfqCode: string;
  description: string | null;
  status: string;
  dueDate: Date | null;
  projectCode: string | null;
  projectName: string | null;
  projectType: string | null;
  packageNo: string | null;
  packageName: string | null;
  picName: string | null;
  totalAttachments: number;
  initialVendorCount: number;
  finalVendorCount: number;
  initialResponseRate: number;
  finalResponseRate: number;
  overallProgress: number;
  daysToDeadline: number;
  createdAt: Date;
  // 추가 계산 필드들
  statusBadge: {
    variant: "default" | "outline" | "secondary" | "destructive" | "success";
    label: string;
  };
  progressBadge: {
    variant: "default" | "outline" | "secondary" | "destructive" | "success";
    label: string;
  };
  urgencyLevel: "high" | "medium" | "low";
}