path: root/lib/contact-possible-items/service.ts

"use server"

import { revalidatePath } from 'next/cache'
import { eq, and, or, desc, asc, count, ilike, SQL, gte, lte } from 'drizzle-orm'
import db from '@/db/db'
import { techSalesContactPossibleItems } from "@/db/schema/techSales"
import { techVendors, techVendorContacts, techVendorPossibleItems } from "@/db/schema/techVendors"
import { itemShipbuilding, itemOffshoreTop, itemOffshoreHull } from "@/db/schema/items"
import { filterColumns } from '@/lib/filter-columns'
import { GetContactPossibleItemsSchema } from "./validations"

// 담당자별 아이템 상세 타입 정의 (뷰 기반)
export interface ContactPossibleItemDetail {
  id: number
  contactId: number
  vendorPossibleItemId: number
  createdAt: Date
  updatedAt: Date
  
  // 벤더 정보
  vendorId: number
  vendorName: string | null
  vendorCode: string | null
  vendorEmail: string | null
  vendorPhone: string | null
  vendorCountry: string | null
  vendorStatus: string | null
  techVendorType: string | null
  
  // 연락처 정보
  contactName: string | null
  contactPosition: string | null
  contactTitle: string | null
  contactEmail: string | null
  contactPhone: string | null
  contactCountry: string | null
  isPrimary: boolean | null
  
  // 아이템 정보
  workType: string | null
  shipTypes: string | null
  itemCode: string | null
  itemList: string | null
  subItemList: string | null
}

/**
 * 담당자별 아이템 목록 조회 (간단한 필터, 정렬, 검색 지원)
 */
export async function getContactPossibleItems(input: GetContactPossibleItemsSchema) {
  try {
    const offset = (input.page - 1) * input.perPage

    // ✅ 1) 고급 필터 조건
    let advancedWhere: SQL<unknown> | undefined = undefined
    if (input.filters && input.filters.length > 0) {
      advancedWhere = filterColumns({
        table: techSalesContactPossibleItems,
        filters: input.filters as any,
        joinOperator: input.joinOperator || 'and',
      })
    }

    // ✅ 2) 기본 필터 조건들
    const basicConditions: SQL<unknown>[] = []

    if (input.vendorName) {
      basicConditions.push(ilike(techVendors.vendorName, `%${input.vendorName}%`))
    }

    if (input.contactName) {
      basicConditions.push(ilike(techVendorContacts.contactName, `%${input.contactName}%`))
    }

    if (input.vendorCode) {
      basicConditions.push(ilike(techVendors.vendorCode, `%${input.vendorCode}%`))
    }

    // 아이템 코드와 작업 유형은 조인된 테이블에서 검색
    if (input.itemCode) {
      const itemCodeConditions = [
        ilike(itemShipbuilding.itemCode, `%${input.itemCode}%`),
        ilike(itemOffshoreTop.itemCode, `%${input.itemCode}%`),
        ilike(itemOffshoreHull.itemCode, `%${input.itemCode}%`)
      ]
      basicConditions.push(or(...itemCodeConditions))
    }

    if (input.workType) {
      const workTypeConditions = [
        ilike(itemShipbuilding.workType, `%${input.workType}%`),
        ilike(itemOffshoreTop.workType, `%${input.workType}%`),
        ilike(itemOffshoreHull.workType, `%${input.workType}%`)
      ]
      basicConditions.push(or(...workTypeConditions))
    }

    const basicWhere = basicConditions.length > 0 ? and(...basicConditions) : undefined

    // ✅ 3) 글로벌 검색 조건
    let globalWhere: SQL<unknown> | undefined = undefined
    if (input.search) {
      const s = `%${input.search}%`
      const searchConditions = [
        ilike(techVendors.vendorName, s),
        ilike(techVendorContacts.contactName, s),
        ilike(techVendors.vendorCode, s),
        ilike(techVendors.email, s),
        ilike(techVendorContacts.contactEmail, s),
      ]
      globalWhere = or(...searchConditions)
    }

    // ✅ 4) 최종 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

    // ✅ 5) 전체 개수 조회 (단순 조인으로 변경)
    const totalResult = await db
      .select({ count: count() })
      .from(techSalesContactPossibleItems)
      .leftJoin(techVendorContacts, eq(techSalesContactPossibleItems.contactId, techVendorContacts.id))
      .leftJoin(techVendorPossibleItems, eq(techSalesContactPossibleItems.vendorPossibleItemId, techVendorPossibleItems.id))
      .leftJoin(techVendors, eq(techVendorContacts.vendorId, techVendors.id))
      .where(finalWhere)

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

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

    console.log("Total contact possible items:", total)

    // ✅ 6) 정렬 및 페이징
    const orderByColumns: any[] = []

    for (const sort of input.sort) {
      const column = sort.id

      // techSalesContactPossibleItems 테이블의 컬럼들
      if (column in techSalesContactPossibleItems) {
        const contactItemColumn = techSalesContactPossibleItems[column as keyof typeof techSalesContactPossibleItems]
        orderByColumns.push(sort.desc ? desc(contactItemColumn) : asc(contactItemColumn))
      }
      // techVendors 테이블의 컬럼들
      else if (column === 'vendorName' || column === 'vendorCode' || column === 'email') {
        const vendorColumn = techVendors[column as keyof typeof techVendors]
        orderByColumns.push(sort.desc ? desc(vendorColumn) : asc(vendorColumn))
      }
      // techVendorContacts 테이블의 컬럼들
      else if (column === 'contactName' || column === 'contactEmail') {
        const contactColumn = techVendorContacts[column as keyof typeof techVendorContacts]
        orderByColumns.push(sort.desc ? desc(contactColumn) : asc(contactColumn))
      }
      // 조인된 테이블의 컬럼들 (실제로는 계산된 값이므로 기본 정렬 사용)
      else if (column === 'itemCode' || column === 'workType') {
        // 조인된 테이블의 컬럼은 직접 정렬할 수 없으므로 기본 정렬 사용
        orderByColumns.push(desc(techSalesContactPossibleItems.createdAt))
      }
    }

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

    // ✅ 7) 메인 쿼리 (단순 조인으로 변경)
    const basicItems = await db
      .select({
        // 기본 매핑 정보
        id: techSalesContactPossibleItems.id,
        contactId: techSalesContactPossibleItems.contactId,
        vendorPossibleItemId: techSalesContactPossibleItems.vendorPossibleItemId,
        createdAt: techSalesContactPossibleItems.createdAt,
        updatedAt: techSalesContactPossibleItems.updatedAt,

        // 벤더 정보
        vendorId: techVendors.id,
        vendorName: techVendors.vendorName,
        vendorCode: techVendors.vendorCode,
        vendorEmail: techVendors.email,
        vendorPhone: techVendors.phone,
        vendorCountry: techVendors.country,
        vendorStatus: techVendors.status,
        techVendorType: techVendors.techVendorType,

        // 연락처 정보
        contactName: techVendorContacts.contactName,
        contactPosition: techVendorContacts.contactPosition,
        contactTitle: techVendorContacts.contactTitle,
        contactEmail: techVendorContacts.contactEmail,
        contactPhone: techVendorContacts.contactPhone,
        contactCountry: techVendorContacts.contactCountry,
        isPrimary: techVendorContacts.isPrimary,

        // 벤더 가능 아이템 ID 정보
        shipbuildingItemId: techVendorPossibleItems.shipbuildingItemId,
        offshoreTopItemId: techVendorPossibleItems.offshoreTopItemId,
        offshoreHullItemId: techVendorPossibleItems.offshoreHullItemId,
      })
      .from(techSalesContactPossibleItems)
      .leftJoin(techVendorContacts, eq(techSalesContactPossibleItems.contactId, techVendorContacts.id))
      .leftJoin(techVendorPossibleItems, eq(techSalesContactPossibleItems.vendorPossibleItemId, techVendorPossibleItems.id))
      .leftJoin(techVendors, eq(techVendorContacts.vendorId, techVendors.id))
      .where(finalWhere)
      .orderBy(...orderByColumns)
      .offset(offset)
      .limit(input.perPage)

    // ✅ 8) 각 아이템의 상세 정보를 별도로 조회하여 합치기
    const items = await Promise.all(basicItems.map(async (item) => {
      let itemCode = null;
      let workType = null;
      let shipTypes = null;
      let itemList = null;
      let subItemList = null;

      if (item.shipbuildingItemId) {
        const shipItem = await db
          .select({
            itemCode: itemShipbuilding.itemCode,
            workType: itemShipbuilding.workType,
            shipTypes: itemShipbuilding.shipTypes,
            itemList: itemShipbuilding.itemList,
          })
          .from(itemShipbuilding)
          .where(eq(itemShipbuilding.id, item.shipbuildingItemId))
          .limit(1);

        if (shipItem.length > 0) {
          itemCode = shipItem[0].itemCode;
          workType = shipItem[0].workType;
          shipTypes = shipItem[0].shipTypes;
          itemList = shipItem[0].itemList;
        }
      } else if (item.offshoreTopItemId) {
        const topItem = await db
          .select({
            itemCode: itemOffshoreTop.itemCode,
            workType: itemOffshoreTop.workType,
            itemList: itemOffshoreTop.itemList,
            subItemList: itemOffshoreTop.subItemList,
          })
          .from(itemOffshoreTop)
          .where(eq(itemOffshoreTop.id, item.offshoreTopItemId))
          .limit(1);

        if (topItem.length > 0) {
          itemCode = topItem[0].itemCode;
          workType = topItem[0].workType;
          itemList = topItem[0].itemList;
          subItemList = topItem[0].subItemList;
        }
      } else if (item.offshoreHullItemId) {
        const hullItem = await db
          .select({
            itemCode: itemOffshoreHull.itemCode,
            workType: itemOffshoreHull.workType,
            itemList: itemOffshoreHull.itemList,
            subItemList: itemOffshoreHull.subItemList,
          })
          .from(itemOffshoreHull)
          .where(eq(itemOffshoreHull.id, item.offshoreHullItemId))
          .limit(1);

        if (hullItem.length > 0) {
          itemCode = hullItem[0].itemCode;
          workType = hullItem[0].workType;
          itemList = hullItem[0].itemList;
          subItemList = hullItem[0].subItemList;
        }
      }

      return {
        ...item,
        itemCode,
        workType,
        shipTypes,
        itemList,
        subItemList,
      };
    }))

    // ✅ 9) 페이지 수 계산
    const pageCount = Math.ceil(total / input.perPage)

    return { data: items as ContactPossibleItemDetail[], pageCount, total }
  } catch (err) {
    console.error("Error fetching contact possible items:", err)
    return { data: [], pageCount: 0, total: 0 }
  }
}

/**
 * 담당자별 아이템 삭제
 */
export async function deleteContactPossibleItem(id: number) {
  try {
    await db
      .delete(techSalesContactPossibleItems)
      .where(eq(techSalesContactPossibleItems.id, id))

    revalidatePath("/evcp/contact-possible-items")
    return { success: true }
  } catch (error) {
    console.error("담당자별 아이템 삭제 오류:", error)
    return { success: false, error: "담당자별 아이템 삭제에 실패했습니다." }
  }
}

/**
 * 여러 담당자별 아이템 삭제
 */
export async function deleteContactPossibleItems(ids: number[]) {
  try {
    await db
      .delete(techSalesContactPossibleItems)
      .where(
        or(...ids.map(id => eq(techSalesContactPossibleItems.id, id)))
      )

    revalidatePath("/evcp/contact-possible-items")
    return { success: true }
  } catch (error) {
    console.error("담당자별 아이템 일괄 삭제 오류:", error)
    return { success: false, error: "담당자별 아이템 삭제에 실패했습니다." }
  }
}