path: root/lib/vendor-investigation/service.ts

"use server"; // Next.js 서버 액션에서 직접 import하려면 (선택)

import { items, vendorInvestigationAttachments, vendorInvestigations, vendorInvestigationsView, vendorPossibleItems, vendors } from "@/db/schema/"
import { GetVendorsInvestigationSchema, updateVendorInvestigationSchema } from "./validations"
import { asc, desc, ilike, inArray, and, or, gte, lte, eq, isNull, count } from "drizzle-orm";
import { revalidateTag, unstable_noStore, revalidatePath } from "next/cache";
import { filterColumns } from "@/lib/filter-columns";
import { unstable_cache } from "@/lib/unstable-cache";
import { getErrorMessage } from "@/lib/handle-error";
import db from "@/db/db";
import { sendEmail } from "../mail/sendEmail";
import fs from "fs"
import path from "path"
import { v4 as uuid } from "uuid"
import { vendorsLogs } from "@/db/schema";
import { cache } from "react"
import { deleteFile } from "../file-stroage";

export async function getVendorsInvestigation(input: GetVendorsInvestigationSchema) {
  return unstable_cache(
    async () => {
      try {
        const offset = (input.page - 1) * input.perPage
        
        // 1) Advanced filters
        const advancedWhere = filterColumns({
          table: vendorInvestigationsView,
          filters: input.filters,
          joinOperator: input.joinOperator,
        })
        
        // 2) Global search
        let globalWhere
        if (input.search) {
          const s = `%${input.search}%`
          globalWhere = or(
            // 협력업체 정보
            ilike(vendorInvestigationsView.vendorName, s),
            ilike(vendorInvestigationsView.vendorCode, s),
            
            // 담당자 정보 (새로 추가)
            ilike(vendorInvestigationsView.requesterName, s),
            ilike(vendorInvestigationsView.qmManagerName, s),
            
            // 실사 정보
            ilike(vendorInvestigationsView.investigationNotes, s),
            ilike(vendorInvestigationsView.investigationStatus, s),
            ilike(vendorInvestigationsView.investigationAddress, s),
            ilike(vendorInvestigationsView.investigationMethod, s),
            
            // 평가 결과
            ilike(vendorInvestigationsView.evaluationResult, s)
          )
        }
        // 3) Combine finalWhere
        const finalWhere = and(
          advancedWhere,
          globalWhere
        )
        
        // 4) Sorting
        const orderBy =
          input.sort && input.sort.length > 0
            ? input.sort.map((item) =>
                item.desc
                  ? desc(vendorInvestigationsView[item.id])
                  : asc(vendorInvestigationsView[item.id])
              )
            : [desc(vendorInvestigationsView.createdAt)]
        
        // 5) Query & count
        const { data, total } = await db.transaction(async (tx) => {
          // a) Select from the view
          const investigationsData = await tx
            .select()
            .from(vendorInvestigationsView)
            .where(finalWhere)
            .orderBy(...orderBy)
            .offset(offset)
            .limit(input.perPage)
          
          // b) Count total
          const resCount = await tx
            .select({ count: count() })
            .from(vendorInvestigationsView)
            .where(finalWhere)

          return { data: investigationsData, total: resCount[0]?.count }
        })
        
        // 6) Calculate pageCount
        const pageCount = Math.ceil(total / input.perPage)
        
        // Data is already in the correct format from the simplified view
        return { data, pageCount }
      } catch (err) {
        console.error(err)
        return { data: [], pageCount: 0 }
      }
    },
    // Cache key
    [JSON.stringify(input)],
    {
      revalidate: 3600,
      tags: ["vendors-in-investigation"],
    }
  )()
}
/**
 * Get existing investigations for a list of vendor IDs
 * 
 * @param vendorIds Array of vendor IDs to check for existing investigations
 * @returns Array of investigation data
 */
export async function getExistingInvestigationsForVendors(vendorIds: number[]) {
  if (!vendorIds.length) return []

  try {
    // Query the vendorInvestigationsView using the vendorIds
    const investigations = await db.query.vendorInvestigations.findMany({
      where: inArray(vendorInvestigationsView.vendorId, vendorIds),
      orderBy: [desc(vendorInvestigationsView.createdAt)],
    })

    return investigations
  } catch (error) {
    console.error("Error fetching existing investigations:", error)
    return []
  }
}

interface RequestInvestigateVendorsInput {
  ids: number[]
}

export async function requestInvestigateVendors({
  ids, userId // userId를 추가
}: RequestInvestigateVendorsInput & { userId: number }) {
  try {
    if (!ids || ids.length === 0) {
      return { error: "No vendor IDs provided." }
    }

    const result = await db.transaction(async (tx) => {
      // 1. Create a new investigation row for each vendor
      const newRecords = await tx
        .insert(vendorInvestigations)
        .values(
          ids.map((vendorId) => ({
            vendorId
          }))
        )
        .returning();

      // 2. 각 벤더에 대해 로그 기록
      await Promise.all(
        ids.map(async (vendorId) => {
          await tx.insert(vendorsLogs).values({
            vendorId: vendorId,
            userId: userId,
            action: "investigation_requested",
            comment: "Investigation requested for this vendor",
          });
        })
      );

      return newRecords;
    });

    // 3. 이메일 발송 (트랜잭션 외부에서 실행)
    await sendEmail({
      to: "dujin.kim@dtsolution.io",
      subject: "New Vendor Investigation(s) Requested",
      template: "investigation-request",
      context: {
        language: "ko",
        vendorIds: ids,
        notes: "Please initiate the planned investigations soon."
      },
    });

    // 4. 캐시 무효화
    revalidateTag("vendors");
    revalidateTag("vendor-investigations");

    return { data: result, error: null }
  } catch (err: unknown) {
    const errorMessage = err instanceof Error ? err.message : String(err)
    return { error: errorMessage }
  }
}


// 개선된 서버 액션 - 텍스트 데이터만 처리
export async function updateVendorInvestigationAction(formData: FormData) {
  try {
    // 1) 텍스트 필드만 추출
    const textEntries: Record<string, string> = {}
    for (const [key, value] of formData.entries()) {
      if (typeof value === "string") {
        textEntries[key] = value
      }
    }

    // 2) 적절한 타입으로 변환
    const processedEntries: any = {}
    
    // 필수 필드
    if (textEntries.investigationId) {
      processedEntries.investigationId = Number(textEntries.investigationId)
    }
    if (textEntries.investigationStatus) {
      processedEntries.investigationStatus = textEntries.investigationStatus
    }

    // 선택적 enum 필드
    if (textEntries.investigationMethod) {
      processedEntries.investigationMethod = textEntries.investigationMethod
    }

    // 선택적 문자열 필드
    if (textEntries.investigationAddress) {
      processedEntries.investigationAddress = textEntries.investigationAddress
    }
    if (textEntries.investigationMethod) {
      processedEntries.investigationMethod = textEntries.investigationMethod
    }
    if (textEntries.investigationNotes) {
      processedEntries.investigationNotes = textEntries.investigationNotes
    }

    // 선택적 날짜 필드
    if (textEntries.forecastedAt) {
      processedEntries.forecastedAt = new Date(textEntries.forecastedAt)
    }
    if (textEntries.requestedAt) {
      processedEntries.requestedAt = new Date(textEntries.requestedAt)
    }
    if (textEntries.confirmedAt) {
      processedEntries.confirmedAt = new Date(textEntries.confirmedAt)
    }
    if (textEntries.completedAt) {
      processedEntries.completedAt = new Date(textEntries.completedAt)
    }

    // 선택적 숫자 필드
    if (textEntries.evaluationScore) {
      processedEntries.evaluationScore = Number(textEntries.evaluationScore)
    }

    // 선택적 평가 결과
    if (textEntries.evaluationResult) {
      processedEntries.evaluationResult = textEntries.evaluationResult
    }

    // 3) Zod로 파싱/검증
    const parsed = updateVendorInvestigationSchema.parse(processedEntries)

    // 4) 업데이트 데이터 준비 - 실제로 제공된 필드만 포함
    const updateData: any = {
      investigationStatus: parsed.investigationStatus,
      updatedAt: new Date(),
    }

    // 선택적 필드들은 존재할 때만 추가
    if (parsed.investigationMethod !== undefined) {
      updateData.investigationMethod = parsed.investigationMethod
    }
    if (parsed.investigationAddress !== undefined) {
      updateData.investigationAddress = parsed.investigationAddress
    }
    if (parsed.investigationMethod !== undefined) {
      updateData.investigationMethod = parsed.investigationMethod
    }
    if (parsed.forecastedAt !== undefined) {
      updateData.forecastedAt = parsed.forecastedAt
    }
    if (parsed.requestedAt !== undefined) {
      updateData.requestedAt = parsed.requestedAt
    }
    if (parsed.confirmedAt !== undefined) {
      updateData.confirmedAt = parsed.confirmedAt
    }
    if (parsed.completedAt !== undefined) {
      updateData.completedAt = parsed.completedAt
    }
    if (parsed.evaluationScore !== undefined) {
      updateData.evaluationScore = parsed.evaluationScore
    }
    if (parsed.evaluationResult !== undefined) {
      updateData.evaluationResult = parsed.evaluationResult
    }
    if (parsed.investigationNotes !== undefined) {
      updateData.investigationNotes = parsed.investigationNotes
    }
    // evaluationType이 null이 아니고, status가 계획중(PLANNED) 이라면, 진행중(IN_PROGRESS)으로 바꿔주는 로직 추가
    if (parsed.evaluationResult !== null && parsed.investigationStatus === "PLANNED") {
      updateData.investigationStatus = "IN_PROGRESS";
    }

    // 5) vendor_investigations 테이블 업데이트
    await db
      .update(vendorInvestigations)
      .set(updateData)
      .where(eq(vendorInvestigations.id, parsed.investigationId))

    // 6) 캐시 무효화
    revalidateTag("vendors-in-investigation")
    revalidateTag("pq-submissions")
    revalidateTag("vendor-pq-submissions")
    revalidatePath("/evcp/pq_new")

    return { data: "OK", error: null }
  } catch (err: unknown) {
    console.error("Investigation update error:", err)
    const message = err instanceof Error ? err.message : String(err)
    return { error: message }
  }
}
// 실사 첨부파일 조회 함수
export async function getInvestigationAttachments(investigationId: number) {
  try {
    const attachments = await db
      .select()
      .from(vendorInvestigationAttachments)
      .where(eq(vendorInvestigationAttachments.investigationId, investigationId))
      .orderBy(vendorInvestigationAttachments.createdAt)

    return { success: true, attachments }
  } catch (error) {
    console.error("첨부파일 조회 실패:", error)
    return { success: false, error: "첨부파일 조회에 실패했습니다.", attachments: [] }
  }
}

// 첨부파일 삭제 함수
export async function deleteInvestigationAttachment(attachmentId: number) {
  try {
    // 파일 정보 조회
    const [attachment] = await db
      .select()
      .from(vendorInvestigationAttachments)
      .where(eq(vendorInvestigationAttachments.id, attachmentId))
      .limit(1)

    if (!attachment) {
      return { success: false, error: "첨부파일을 찾을 수 없습니다." }
    }

    await deleteFile(attachment.filePath)

    // 데이터베이스에서 레코드 삭제
    await db
      .delete(vendorInvestigationAttachments)
      .where(eq(vendorInvestigationAttachments.id, attachmentId))

    // 캐시 무효화
    revalidateTag("vendors-in-investigation")
    
    return { success: true }
  } catch (error) {
    console.error("첨부파일 삭제 실패:", error)
    return { success: false, error: "첨부파일 삭제에 실패했습니다." }
  }
}

// 첨부파일 다운로드 정보 조회
export async function getAttachmentDownloadInfo(attachmentId: number) {
  try {
    const [attachment] = await db
      .select({
        fileName: vendorInvestigationAttachments.fileName,
        filePath: vendorInvestigationAttachments.filePath,
        mimeType: vendorInvestigationAttachments.mimeType,
        fileSize: vendorInvestigationAttachments.fileSize,
      })
      .from(vendorInvestigationAttachments)
      .where(eq(vendorInvestigationAttachments.id, attachmentId))
      .limit(1)

    if (!attachment) {
      return { success: false, error: "첨부파일을 찾을 수 없습니다." }
    }

  
    return { 
      success: true, 
      downloadInfo: {
        fileName: attachment.fileName,
        filePath: attachment.filePath,
        mimeType: attachment.mimeType,
        fileSize: attachment.fileSize,
      }
    }
  } catch (error) {
    console.error("첨부파일 정보 조회 실패:", error)
    return { success: false, error: "첨부파일 정보 조회에 실패했습니다." }
  }
}
/**
 * Get vendor details by ID
 */
export const getVendorById = cache(async (vendorId: number) => {
  try {
    const [vendorData] = await db
      .select({
        id: vendors.id,
        name: vendors.vendorName,
        code: vendors.vendorCode,
        taxId: vendors.taxId,
        email: vendors.email,
        phone: vendors.phone,
        website: vendors.website,
        address: vendors.address,
        country: vendors.country,
        status: vendors.status,
        description: vendors.items, // Using items field as description for now
        vendorTypeId: vendors.vendorTypeId,
        representativeName: vendors.representativeName,
        representativeBirth: vendors.representativeBirth,
        representativeEmail: vendors.representativeEmail,
        representativePhone: vendors.representativePhone,
        corporateRegistrationNumber: vendors.corporateRegistrationNumber,
        creditAgency: vendors.creditAgency,
        creditRating: vendors.creditRating,
        cashFlowRating: vendors.cashFlowRating,
        businessSize: vendors.businessSize,
        createdAt: vendors.createdAt,
        updatedAt: vendors.updatedAt,
      })
      .from(vendors)
      .where(eq(vendors.id, vendorId))
      .limit(1)

    if (!vendorData) {
      throw new Error(`Vendor with ID ${vendorId} not found`)
    }

    return vendorData
  } catch (error) {
    console.error("Error fetching vendor:", error)
    throw new Error("Failed to fetch vendor details")
  }
})

/**
 * Get vendor items by vendor ID with caching
 */
export async function getVendorItemsByVendorId(vendorId: number) {
  return unstable_cache(
    async () => {
      try {
        // Join vendorPossibleItems with items table to get complete item information
        const vendorItems = await db
          .select({
            id: vendorPossibleItems.id,
            vendorId: vendorPossibleItems.vendorId,
            itemCode: vendorPossibleItems.itemCode,
            itemName: items.itemName,
            description: items.description,
            createdAt: vendorPossibleItems.createdAt,
            updatedAt: vendorPossibleItems.updatedAt,
          })
          .from(vendorPossibleItems)
          .leftJoin(
            items,
            eq(vendorPossibleItems.itemCode, items.itemCode)
          )
          .where(eq(vendorPossibleItems.vendorId, vendorId))
          .orderBy(vendorPossibleItems.createdAt)

        return vendorItems
      } catch (error) {
        console.error("Error fetching vendor items:", error)
        throw new Error("Failed to fetch vendor items")
      }
    },
    // Cache key
    [`vendor-items-${vendorId}`],
    {
      revalidate: 3600, // Cache for 1 hour
      tags: [`vendor-items-${vendorId}`, "vendor-items"],
    }
  )()
}

/**
 * Get all items for a vendor (alternative function name for clarity)
 */
export const getVendorPossibleItems = cache(async (vendorId: number) => {
  return getVendorItemsByVendorId(vendorId)
})

/**
 * Get vendor contacts by vendor ID
 * This function assumes you have a vendorContacts table
 */
export const getVendorContacts = cache(async (vendorId: number) => {
  try {
    // Note: This assumes you have a vendorContacts table
    // If you don't have this table yet, you can return an empty array
    // or implement based on your actual contacts storage structure
    
    // For now, returning empty array since vendorContacts table wasn't provided
    return []
    
    /* 
    // Uncomment and modify when you have vendorContacts table:
    const contacts = await db
      .select({
        id: vendorContacts.id,
        contactName: vendorContacts.name,
        contactEmail: vendorContacts.email,
        contactPhone: vendorContacts.phone,
        contactPosition: vendorContacts.position,
        isPrimary: vendorContacts.isPrimary,
        isActive: vendorContacts.isActive,
        createdAt: vendorContacts.createdAt,
        updatedAt: vendorContacts.updatedAt,
      })
      .from(vendorContacts)
      .where(
        and(
          eq(vendorContacts.vendorId, vendorId),
          eq(vendorContacts.isActive, true)
        )
      )

    return contacts
    */
  } catch (error) {
    console.error("Error fetching vendor contacts:", error)
    return []
  }
})

/**
 * Add an item to a vendor
 */
export async function addVendorItem(vendorId: number, itemCode: string) {
  try {
    // Check if the item exists
    const [item] = await db
      .select()
      .from(items)
      .where(eq(items.itemCode, itemCode))
      .limit(1)

    if (!item) {
      throw new Error(`Item with code ${itemCode} not found`)
    }

    // Check if the vendor-item relationship already exists
    const [existingRelation] = await db
      .select()
      .from(vendorPossibleItems)
      .where(
        eq(vendorPossibleItems.vendorId, vendorId) &&
        eq(vendorPossibleItems.itemCode, itemCode)
      )
      .limit(1)

    if (existingRelation) {
      throw new Error("This item is already associated with the vendor")
    }

    // Add the item to the vendor
    const [newVendorItem] = await db
      .insert(vendorPossibleItems)
      .values({
        vendorId,
        itemCode,
      })
      .returning()

    // Revalidate cache
    revalidateTag(`vendor-items-${vendorId}`)
    revalidateTag("vendor-items")

    return newVendorItem
  } catch (error) {
    console.error("Error adding vendor item:", error)
    throw new Error("Failed to add item to vendor")
  }
}

/**
 * Remove an item from a vendor
 */
export async function removeVendorItem(vendorId: number, itemCode: string) {
  try {
    await db
      .delete(vendorPossibleItems)
      .where(
        eq(vendorPossibleItems.vendorId, vendorId) &&
        eq(vendorPossibleItems.itemCode, itemCode)
      )

    // Revalidate cache
    revalidateTag(`vendor-items-${vendorId}`)
    revalidateTag("vendor-items")

    return { success: true }
  } catch (error) {
    console.error("Error removing vendor item:", error)
    throw new Error("Failed to remove item from vendor")
  }
}

/**
 * Get all available items (for adding to vendors)
 */
export const getAllItems = cache(async () => {
  try {
    const allItems = await db
      .select({
        id: items.id,
        itemCode: items.itemCode,
        itemName: items.itemName,
        description: items.description,
        createdAt: items.createdAt,
        updatedAt: items.updatedAt,
      })
      .from(items)
      .orderBy(items.itemName)

    return allItems
  } catch (error) {
    console.error("Error fetching all items:", error)
    throw new Error("Failed to fetch items")
  }
})