"use server";

import { eq } from "drizzle-orm";
import db from "@/db/db";
import { 
  techVendors, 
  techVendorPossibleItems
} from "@/db/schema/techVendors";
import { itemShipbuilding, itemOffshoreTop, itemOffshoreHull } from "@/db/schema/items";
import { unstable_cache } from "@/lib/unstable-cache";

// 새로운 스키마에 맞는 데이터 인터페이스
export interface TechVendorPossibleItemsData {
  id: number;
  vendorId: number;
  shipbuildingItemId: number | null;
  offshoreTopItemId: number | null;
  offshoreHullItemId: number | null;
  createdAt: Date;
  updatedAt: Date;
  // 조인된 벤더 정보
  vendorCode: string | null;
  vendorName: string;
  vendorEmail: string | null;
  vendorStatus: string;
  techVendorType: string;
  // 조인된 아이템 정보
  itemCode: string;
  workType: string | null;
  shipTypes: string | null;
  itemList: string | null;
  subItemList: string | null;
}

export interface GetTechVendorPossibleItemsSchema {
  page: number;
  perPage: number;
  search?: string;
  filters?: Array<{ id: string; value: string | number | boolean; operator?: string }>;
  joinOperator?: "and" | "or";
  sort?: Array<{ id: string; desc: boolean }>;
  vendorCode?: string;
  vendorName?: string;
  vendorEmail?: string;
  vendorStatus?: string;
  itemCode?: string;
  vendorType?: string;
}

/**
 * 새로운 스키마에 맞는 tech vendor possible items 조회 함수
 * 벤더 정보와 각 아이템 테이블별 정보를 조인해서 가져옴
 */
export async function getTechVendorPossibleItems(input: GetTechVendorPossibleItemsSchema) {
  return unstable_cache(
    async () => {
      try {
        const offset = (input.page - 1) * input.perPage;

        // 한 쿼리로 모든 아이템 조회 (각 레코드는 3개 중 하나의 ID만 가짐)
        const rawItems = await db
          .select({
            id: techVendorPossibleItems.id,
            vendorId: techVendorPossibleItems.vendorId,
            shipbuildingItemId: techVendorPossibleItems.shipbuildingItemId,
            offshoreTopItemId: techVendorPossibleItems.offshoreTopItemId,
            offshoreHullItemId: techVendorPossibleItems.offshoreHullItemId,
            createdAt: techVendorPossibleItems.createdAt,
            updatedAt: techVendorPossibleItems.updatedAt,
            // 벤더 정보
            vendorCode: techVendors.vendorCode,
            vendorName: techVendors.vendorName,
            vendorEmail: techVendors.email,
            vendorStatus: techVendors.status,
            techVendorType: techVendors.techVendorType,
            // 조선 아이템 정보 (shipbuildingItemId가 있을 때만)
            shipItemCode: itemShipbuilding.itemCode,
            shipWorkType: itemShipbuilding.workType,
            shipItemList: itemShipbuilding.itemList,
            shipTypes: itemShipbuilding.shipTypes,
            // 해양 TOP 아이템 정보 (offshoreTopItemId가 있을 때만)
            topItemCode: itemOffshoreTop.itemCode,
            topWorkType: itemOffshoreTop.workType,
            topItemList: itemOffshoreTop.itemList,
            topSubItemList: itemOffshoreTop.subItemList,
            // 해양 HULL 아이템 정보 (offshoreHullItemId가 있을 때만)
            hullItemCode: itemOffshoreHull.itemCode,
            hullWorkType: itemOffshoreHull.workType,
            hullItemList: itemOffshoreHull.itemList,
            hullSubItemList: itemOffshoreHull.subItemList,
          })
          .from(techVendorPossibleItems)
          .innerJoin(techVendors, eq(techVendorPossibleItems.vendorId, techVendors.id))
          .leftJoin(itemShipbuilding, eq(techVendorPossibleItems.shipbuildingItemId, itemShipbuilding.id))
          .leftJoin(itemOffshoreTop, eq(techVendorPossibleItems.offshoreTopItemId, itemOffshoreTop.id))
          .leftJoin(itemOffshoreHull, eq(techVendorPossibleItems.offshoreHullItemId, itemOffshoreHull.id));

        // 결과를 통합된 형태로 변환
        const allItems: TechVendorPossibleItemsData[] = rawItems.map((item: Record<string, any>) => ({
          id: item.id,
          vendorId: item.vendorId,
          shipbuildingItemId: item.shipbuildingItemId,
          offshoreTopItemId: item.offshoreTopItemId,
          offshoreHullItemId: item.offshoreHullItemId,
          createdAt: item.createdAt,
          updatedAt: item.updatedAt,
          vendorCode: item.vendorCode,
          vendorName: item.vendorName,
          vendorEmail: item.vendorEmail,
          vendorStatus: item.vendorStatus,
          techVendorType: item.techVendorType,
          // 어떤 타입의 아이템인지에 따라 적절한 값 선택
          itemCode: item.shipItemCode || item.topItemCode || item.hullItemCode || "",
          workType: item.shipWorkType || item.topWorkType || item.hullWorkType,
          itemList: item.shipItemList || item.topItemList || item.hullItemList,
          shipTypes: item.shipTypes, // 조선에만 있음
          subItemList: item.topSubItemList || item.hullSubItemList, // 해양에만 있음
        }));

        let filteredItems = allItems;

        // 필터링 적용
        if (input.search) {
          const s = input.search.toLowerCase();
          filteredItems = filteredItems.filter(item => 
            item.vendorCode?.toLowerCase().includes(s) ||
            item.vendorName?.toLowerCase().includes(s) ||
            item.vendorEmail?.toLowerCase().includes(s) ||
            item.itemCode?.toLowerCase().includes(s) ||
            item.workType?.toLowerCase().includes(s) ||
            item.itemList?.toLowerCase().includes(s) ||
            item.shipTypes?.toLowerCase().includes(s) ||
            item.subItemList?.toLowerCase().includes(s)
          );
        }

        // 개별 필터들
        if (input.vendorCode) {
          filteredItems = filteredItems.filter(item => 
            item.vendorCode?.toLowerCase().includes(input.vendorCode!.toLowerCase())
          );
        }

        if (input.vendorName) {
          filteredItems = filteredItems.filter(item => 
            item.vendorName?.toLowerCase().includes(input.vendorName!.toLowerCase())
          );
        }

        if (input.vendorEmail) {
          filteredItems = filteredItems.filter(item => 
            item.vendorEmail?.toLowerCase().includes(input.vendorEmail!.toLowerCase())
          );
        }

        if (input.vendorStatus) {
          filteredItems = filteredItems.filter(item => 
            item.vendorStatus === input.vendorStatus
          );
        }

        if (input.itemCode) {
          filteredItems = filteredItems.filter(item => 
            item.itemCode?.toLowerCase().includes(input.itemCode!.toLowerCase())
          );
        }

        // 벤더 타입 필터링
        if (input.vendorType && input.vendorType !== "all") {
          const vendorTypeMap = {
            "ship": "조선",
            "top": "해양TOP", 
            "hull": "해양HULL"
          };
          
          const actualVendorType = vendorTypeMap[input.vendorType as keyof typeof vendorTypeMap] || input.vendorType;
          
          if (actualVendorType) {
            filteredItems = filteredItems.filter(item => 
              item.techVendorType?.includes(actualVendorType)
            );
          }
        }

        // 정렬
        if (input.sort && input.sort.length > 0) {
          filteredItems.sort((a, b) => {
            for (const sortItem of input.sort!) {
              const aVal = (a as Record<string, any>)[sortItem.id] || "";
              const bVal = (b as Record<string, any>)[sortItem.id] || "";
              
              if (aVal < bVal) return sortItem.desc ? 1 : -1;
              if (aVal > bVal) return sortItem.desc ? -1 : 1;
            }
            return 0;
          });
        } else {
          // 기본 정렬: createdAt 내림차순
          filteredItems.sort((a, b) => b.createdAt.getTime() - a.createdAt.getTime());
        }

        const totalCount = filteredItems.length;
        const pageCount = Math.ceil(totalCount / input.perPage);

        // 페이지네이션 적용
        const data = filteredItems.slice(offset, offset + input.perPage);

        return { data, pageCount, totalCount };
      } catch (error) {
        console.error("Error fetching tech vendor possible items:", error);
        return { data: [], pageCount: 0, totalCount: 0 };
      }
    },
    [JSON.stringify(input)],
    {
      revalidate: 3600,
      tags: ["tech-vendor-possible-items"],
    }
  )();
}