"use server"

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 { eq, desc, ilike, count, or } from "drizzle-orm"
import { revalidatePath } from "next/cache"
import { unstable_noStore } from "next/cache"
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) {
  unstable_noStore()

  try {
    const offset = (input.page - 1) * input.per_page
    
    console.log("=== getContactPossibleItems DEBUG ===")
    console.log("Input:", input)
    console.log("Offset:", offset)
    
    // 검색 조건 (벤더명, 연락처명으로만 검색)
    let whereCondition
    if (input.search) {
      const searchTerm = `%${input.search}%`
      whereCondition = or(
        ilike(techVendors.vendorName, searchTerm),
        ilike(techVendorContacts.contactName, searchTerm)
      )
      console.log("Search term:", searchTerm)
    } else {
      console.log("No search condition")
    }

    // 새로운 스키마에 맞게 수정 - 아이템 정보를 별도로 조회해야 함
    console.log("Executing data query...")
    
    // 1단계: 기본 매핑 정보 조회
    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(whereCondition)
      .orderBy(desc(techSalesContactPossibleItems.createdAt))
      .offset(offset)
      .limit(input.per_page)

    // 2단계: 각 아이템의 상세 정보를 별도로 조회하여 합치기
    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,
      };
    }))

    console.log("Items found:", items.length)
    console.log("First 3 items:", items.slice(0, 3))

    // 전체 개수 조회 (동일한 조인과 검색 조건 적용)
    console.log("Executing count query...")
    const [{ count: total }] = 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(whereCondition)

    console.log("Total count:", total)

    const pageCount = Math.ceil(total / input.per_page)

    console.log("Final result:", { dataLength: items.length, pageCount, total })
    console.log("=== END DEBUG ===")

    return {
      data: items as ContactPossibleItemDetail[],
      pageCount,
      total,
    }
  } catch (err) {
    console.error("=== ERROR in getContactPossibleItems ===")
    console.error("Error fetching contact possible items:", err)
    console.error("Input was:", input)
    console.error("=== END ERROR ===")
    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: "담당자별 아이템 삭제에 실패했습니다." }
  }
}