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

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

import { items, vendorInvestigationAttachments, vendorInvestigations, vendorInvestigationsView, vendorPossibleItems, vendors, siteVisitRequests, vendorPQSubmissions, users } from "@/db/schema/"
import { GetVendorsInvestigationSchema, updateVendorInvestigationSchema, updateVendorInvestigationProgressSchema, updateVendorInvestigationResultSchema } 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";
import { saveDRMFile } from "../file-stroage";
import { decryptWithServerAction } from "@/components/drm/drmUtils";
import { format, addDays } from "date-fns";

export async function getVendorsInvestigation(input: GetVendorsInvestigationSchema) {
  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 }
  }
}
/**
 * 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 []
  }
}

// PQ 제출 타입 조회 (investigation.pqSubmissionId → type)
export default async function getPQSubmissionTypeAction(pqSubmissionId: number) {
  try {
    const row = await db
      .select({ type: vendorPQSubmissions.type })
      .from(vendorPQSubmissions)
      .where(eq(vendorPQSubmissions.id, pqSubmissionId))
      .limit(1)
      .then(rows => rows[0]);
    if (!row) return { success: false, error: "PQ submission not found" };
    return { success: true, type: row.type as "GENERAL" | "PROJECT" | "NON_INSPECTION" };
  } catch (e) {
    return { success: false, error: e instanceof Error ? e.message : "Unknown error" };
  }
}

// 실사 계획 취소 액션: 상태를 QM_REVIEW_CONFIRMED로 되돌림
export async function cancelInvestigationPlanAction(investigationId: number) {
  try {
    await db
      .update(vendorInvestigations)
      .set({
        investigationStatus: "QM_REVIEW_CONFIRMED",
        updatedAt: new Date(),
      })
      .where(eq(vendorInvestigations.id, investigationId))

    revalidateTag("vendor-investigations")
    revalidatePath("/evcp/vendor-investigation")

    return { success: true }
  } catch (error) {
    console.error("실사 계획 취소 오류:", error)
    return {
      success: false,
      error: error instanceof Error ? error.message : "알 수 없는 오류",
    }
  }
}

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 }
  }
}


// 실사 진행 관리 업데이트 액션 (PLANNED -> IN_PROGRESS)
export async function updateVendorInvestigationProgressAction(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.investigationAddress) {
      processedEntries.investigationAddress = textEntries.investigationAddress
    }
    if (textEntries.investigationMethod) {
      processedEntries.investigationMethod = textEntries.investigationMethod
    }

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

    // 3) Zod로 파싱/검증 (4개 필수값 규칙 포함)
    const parsed = updateVendorInvestigationProgressSchema.parse(processedEntries)

    // 4) 업데이트 데이터 준비
    const updateData: any = {
      updatedAt: new Date(),
    }

    // 선택적 필드들은 존재할 때만 추가
    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.confirmedAt !== undefined) {
      updateData.confirmedAt = parsed.confirmedAt
    }

    // 실사 방법이 설정되면 QM_REVIEW_CONFIRMED -> IN_PROGRESS로 상태 변경
    if (parsed.investigationMethod) {
      updateData.investigationStatus = "IN_PROGRESS"
    }

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

    // 6) 캐시 무효화
    revalidateTag("vendor-investigations")
    revalidatePath("/evcp/vendor-investigation")

    return { success: true }
  } catch (error) {
    console.error("실사 진행 관리 업데이트 오류:", error)
    return { 
      success: false, 
      error: error instanceof Error ? error.message : "알 수 없는 오류" 
    }
  }
}

// 실사 결과 입력 액션 (IN_PROGRESS -> COMPLETED/CANCELED/SUPPLEMENT_REQUIRED)
export async function updateVendorInvestigationResultAction(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.completedAt) {
      processedEntries.completedAt = new Date(textEntries.completedAt)
    }
    if (textEntries.evaluationScore) {
      processedEntries.evaluationScore = Number(textEntries.evaluationScore)
    }
    if (textEntries.evaluationResult) {
      processedEntries.evaluationResult = textEntries.evaluationResult
    }
    if (textEntries.investigationNotes) {
      processedEntries.investigationNotes = textEntries.investigationNotes
    }

    // attachments는 별도로 업로드되므로 빈 배열로 설정
    processedEntries.attachments = []

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

    // 4) 업데이트 데이터 준비
    const updateData: any = {
      updatedAt: new Date(),
    }

    // 선택적 필드들은 존재할 때만 추가
    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
    }

    // 평가 결과에 따라 상태 자동 변경
    if (parsed.evaluationResult) {
      if (parsed.evaluationResult === "REJECTED") {
        updateData.investigationStatus = "CANCELED"
      } else if (parsed.evaluationResult === "SUPPLEMENT" ||
                 parsed.evaluationResult === "SUPPLEMENT_REINSPECT" ||
                 parsed.evaluationResult === "SUPPLEMENT_DOCUMENT") {
        updateData.investigationStatus = "SUPPLEMENT_REQUIRED"
        // 보완 요청이 있었음을 기록
        updateData.hasSupplementRequested = true
      } else if (parsed.evaluationResult === "APPROVED") {
        updateData.investigationStatus = "COMPLETED"
      }
    }

    // 5) vendor_investigations 테이블 업데이트
    await db
      .update(vendorInvestigations)
      .set(updateData)
      .where(eq(vendorInvestigations.id, parsed.investigationId))
    /*
    현재 보완-서류제출 프로세스는 자동으로 처리됨. 만약 dialog 필요하면 아래 서버액션 분기 필요.(1029/최겸)
    */
    // 5-1) 보완 프로세스 자동 처리 (TO-BE)
    if (parsed.evaluationResult === "SUPPLEMENT_REINSPECT" || parsed.evaluationResult === "SUPPLEMENT_DOCUMENT") {
      // 실사 방법 확인
      const investigation = await db
        .select({
          investigationMethod: vendorInvestigations.investigationMethod,
        })
        .from(vendorInvestigations)
        .where(eq(vendorInvestigations.id, parsed.investigationId))
        .then(rows => rows[0]);

      if (investigation?.investigationMethod === "PRODUCT_INSPECTION" || investigation?.investigationMethod === "SITE_VISIT_EVAL") {
       if (parsed.evaluationResult === "SUPPLEMENT_DOCUMENT") {
          // 보완-서류제출 요청 자동 생성
          await requestSupplementDocumentAction({
            investigationId: parsed.investigationId,
            documentRequests: {
              requiredDocuments: ["보완 서류"],
              additionalRequests: "보완을 위한 서류 제출 요청입니다.",
            }
          });
        }
      }
    }

    // 6) 캐시 무효화
    revalidateTag("vendor-investigations")
    revalidatePath("/evcp/vendor-investigation")
    revalidatePath("/evcp/pq_new")

    return { success: true }
  } catch (error) {
    console.error("실사 결과 업데이트 오류:", error)
    return { 
      success: false, 
      error: error instanceof Error ? error.message : "알 수 없는 오류" 
    }
  }
}

// 기존 함수 (호환성을 위해 유지)
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";
    }

    // 보완 프로세스 분기 로직 (TO-BE)
    if (parsed.evaluationResult === "SUPPLEMENT_REINSPECT" || parsed.evaluationResult === "SUPPLEMENT_DOCUMENT") {
      updateData.investigationStatus = "SUPPLEMENT_REQUIRED";
    }

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

    // 5-1) 보완 프로세스 자동 처리 (TO-BE)
    if (parsed.evaluationResult === "SUPPLEMENT_REINSPECT" || parsed.evaluationResult === "SUPPLEMENT_DOCUMENT") {
      // 실사 방법 확인
      const investigation = await db
        .select({
          investigationMethod: vendorInvestigations.investigationMethod,
        })
        .from(vendorInvestigations)
        .where(eq(vendorInvestigations.id, parsed.investigationId))
        .then(rows => rows[0]);

      if (investigation?.investigationMethod === "PRODUCT_INSPECTION" || investigation?.investigationMethod === "SITE_VISIT_EVAL") {
        if (parsed.evaluationResult === "SUPPLEMENT_REINSPECT") {
          // 보완-재실사 요청 자동 생성
          await requestSupplementReinspectionAction({
            investigationId: parsed.investigationId,
            siteVisitData: {
              inspectionDuration: 1.0, // 기본 1일
              additionalRequests: "보완을 위한 재실사 요청입니다.",
            }
          });
        } else if (parsed.evaluationResult === "SUPPLEMENT_DOCUMENT") {
          // 보완-서류제출 요청 자동 생성
          await requestSupplementDocumentAction({
            investigationId: parsed.investigationId,
            documentRequests: {
              requiredDocuments: ["보완 서류"],
              additionalRequests: "보완을 위한 서류 제출 요청입니다.",
            }
          });
        }
      }
    }

    // 6) 캐시 무효화
    revalidateTag("vendor-investigations")
    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("vendor-investigations")
    
    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")
  }
})

/**
 * Create vendor investigation attachment
 */
export async function createVendorInvestigationAttachmentAction(input: {
  investigationId: number;
  file: File;
  userId?: string;
}) {
  unstable_noStore();

  try {
    console.log(`📎 실사 첨부파일 생성 시작: ${input.file.name}`);

    // 1. saveDRMFile을 사용하여 파일 저장
    const saveResult = await saveDRMFile(
      input.file,
      decryptWithServerAction,
      `vendor-investigation/${input.investigationId}`,
      input.userId
    );

    if (!saveResult.success) {
      throw new Error(`파일 저장 실패: ${input.file.name} - ${saveResult.error}`);
    }

    console.log(`✅ 파일 저장 완료: ${input.file.name} -> ${saveResult.fileName}`);

    // 2. DB에 첨부파일 레코드 생성
    const [insertedAttachment] = await db
      .insert(vendorInvestigationAttachments)
      .values({
        investigationId: input.investigationId,
        fileName: saveResult.fileName!,
        originalFileName: input.file.name,
        filePath: saveResult.publicPath!,
        fileSize: input.file.size,
        mimeType: input.file.type || 'application/octet-stream',
        attachmentType: 'DOCUMENT', // 또는 파일 타입에 따라 결정
        createdAt: new Date(),
        updatedAt: new Date(),
      })
      .returning();

    console.log(`✅ 첨부파일 DB 레코드 생성 완료: ID ${insertedAttachment.id}`);

    // 3. 캐시 무효화
    revalidateTag(`vendor-investigation-${input.investigationId}`);
    revalidateTag("vendor-investigations");

    return {
      success: true,
      attachment: insertedAttachment,
    };
  } catch (error) {
    console.error(`❌ 실사 첨부파일 생성 실패: ${input.file.name}`, error);
    return {
      success: false,
      error: error instanceof Error ? error.message : "알 수 없는 오류",
    };
  }
}

// 보완-재실사 요청 액션
export async function requestSupplementReinspectionAction({
  investigationId,
  siteVisitData
}: {
  investigationId: number;
  siteVisitData: {
    inspectionDuration?: number;
    requestedStartDate?: Date;
    requestedEndDate?: Date;
    shiAttendees?: any;
    vendorRequests?: any;
    additionalRequests?: string;
  };
}) {
  try {
    // 1. 실사 상태를 SUPPLEMENT_REQUIRED로 변경
    await db
      .update(vendorInvestigations)
      .set({
        investigationStatus: "SUPPLEMENT_REQUIRED",
        updatedAt: new Date(),
      })
      .where(eq(vendorInvestigations.id, investigationId));

    // 2. 새로운 방문실사 요청 생성
    const [newSiteVisitRequest] = await db
      .insert(siteVisitRequests)
      .values({
        investigationId: investigationId,
        inspectionDuration: siteVisitData.inspectionDuration,
        requestedStartDate: siteVisitData.requestedStartDate,
        requestedEndDate: siteVisitData.requestedEndDate,
        shiAttendees: siteVisitData.shiAttendees || {},
        vendorRequests: siteVisitData.vendorRequests || {},
        additionalRequests: siteVisitData.additionalRequests,
        status: "REQUESTED",
      })
      .returning();

    // 3. 캐시 무효화
    revalidateTag("vendor-investigations");
    revalidateTag("site-visit-requests");

    return { success: true, siteVisitRequestId: newSiteVisitRequest.id };
  } catch (error) {
    console.error("보완-재실사 요청 실패:", error);
    return { 
      success: false, 
      error: error instanceof Error ? error.message : "알 수 없는 오류" 
    };
  }
}

// 보완-서류제출 요청 액션
export async function requestSupplementDocumentAction({
  investigationId,
  documentRequests
}: {
  investigationId: number;
  documentRequests: {
    requiredDocuments: string[];
    additionalRequests?: string;
  };
}) {
  try {
    // 1. 실사 상태를 SUPPLEMENT_REQUIRED로 변경
    await db
      .update(vendorInvestigations)
      .set({
        investigationStatus: "SUPPLEMENT_REQUIRED",
        updatedAt: new Date(),
      })
      .where(eq(vendorInvestigations.id, investigationId));

    // 2. 실사, 협력업체, 발송자 정보 조회
    const investigationResult = await db
      .select()
      .from(vendorInvestigations)
      .where(eq(vendorInvestigations.id, investigationId))
      .limit(1);
    
    const investigation = investigationResult[0];
    if (!investigation) {
      throw new Error('실사 정보를 찾을 수 없습니다.');
    }

    const vendorResult = await db
      .select()
      .from(vendors)
      .where(eq(vendors.id, investigation.vendorId))
      .limit(1);
    
    const vendor = vendorResult[0];
    if (!vendor) {
      throw new Error('협력업체 정보를 찾을 수 없습니다.');
    }

    const senderResult = await db
      .select()
      .from(users)
      .where(eq(users.id, investigation.requesterId!))
      .limit(1);
    
    const sender = senderResult[0];
    if (!sender) {
      throw new Error('발송자 정보를 찾을 수 없습니다.');
    }

    // 마감일 계산 (발송일 + 7일 또는 실사 예정일 중 먼저 도래하는 날)
    const deadlineDate = (() => {
      const deadlineFromToday = addDays(new Date(), 7);
      if (investigation.forecastedAt) {
        const forecastedDate = new Date(investigation.forecastedAt);
        return forecastedDate < deadlineFromToday ? forecastedDate : deadlineFromToday;
      }
      return deadlineFromToday;
    })();

    // 메일 제목
    const subject = `[SHI Audit] 보완 서류제출 요청 _ ${vendor.vendorName}`;

    // 메일 컨텍스트
    const context = {
      // 기본 정보
      vendorName: vendor.vendorName,
      vendorEmail: vendor.email || '',
      requesterName: sender.name,
      requesterTitle: 'Procurement Manager',
      requesterEmail: sender.email,

      // 보완 요청 서류
      requiredDocuments: documentRequests.requiredDocuments || [],

      // 추가 요청사항
      additionalRequests: documentRequests.additionalRequests || null,

      // 마감일
      deadlineDate: format(deadlineDate, 'yyyy.MM.dd'),

      // 포털 URL
      portalUrl: `${process.env.NEXT_PUBLIC_BASE_URL}/ko/partners/site-visit`,

      // 현재 연도
      currentYear: new Date().getFullYear()
    };

    // 메일 발송 (벤더 이메일로 직접 발송)
    try {
      await sendEmail({
        to: vendor.email || '',
        cc: sender.email,
        subject,
        template: 'supplement-document-request' as string,
        context,
      });

      console.log('보완 서류제출 요청 메일 발송 완료:', {
        to: vendor.email,
        subject,
        vendorName: vendor.vendorName
      });
    } catch (emailError) {
      console.error('보완 서류제출 요청 메일 발송 실패:', emailError);
    }

    // 3. 캐시 무효화
    revalidateTag("vendor-investigations");
    revalidateTag("site-visit-requests");

    return { success: true };
  } catch (error) {
    console.error("보완-서류제출 요청 실패:", error);
    return { 
      success: false, 
      error: error instanceof Error ? error.message : "알 수 없는 오류" 
    };
  }
}

// 보완 서류 제출 완료 액션 (벤더가 서류 제출 완료)
export async function completeSupplementDocumentAction({
  investigationId,
  siteVisitRequestId,
  submittedBy
}: {
  investigationId: number;
  siteVisitRequestId: number;
  submittedBy: number;
}) {
  try {
    // 1. 방문실사 요청 상태를 COMPLETED로 변경
    await db
      .update(siteVisitRequests)
      .set({
        status: "COMPLETED",
        sentAt: new Date(),
        updatedAt: new Date(),
      })
      .where(eq(siteVisitRequests.id, siteVisitRequestId));

    // 2. 실사 상태를 IN_PROGRESS로 변경 (재검토 대기)
    await db
      .update(vendorInvestigations)
      .set({
        investigationStatus: "IN_PROGRESS",
        updatedAt: new Date(),
      })
      .where(eq(vendorInvestigations.id, investigationId));

    // 3. 캐시 무효화
    revalidateTag("vendor-investigations");
    revalidateTag("site-visit-requests");

    return { success: true };
  } catch (error) {
    console.error("보완 서류 제출 완료 처리 실패:", error);
    return { 
      success: false, 
      error: error instanceof Error ? error.message : "알 수 없는 오류" 
    };
  }
}

// 보완 재실사 완료 액션 (재실사 완료 후)
export async function completeSupplementReinspectionAction({
  investigationId,
  siteVisitRequestId,
  evaluationResult,
  evaluationScore,
  investigationNotes
}: {
  investigationId: number;
  siteVisitRequestId: number;
  evaluationResult: "APPROVED" | "SUPPLEMENT" | "REJECTED";
  evaluationScore?: number;
  investigationNotes?: string;
}) {
  try {
    // 1. 방문실사 요청 상태를 COMPLETED로 변경
    await db
      .update(siteVisitRequests)
      .set({
        status: "COMPLETED",
        sentAt: new Date(),
        updatedAt: new Date(),
      })
      .where(eq(siteVisitRequests.id, siteVisitRequestId));

    // 2. 실사 상태 및 평가 결과 업데이트
    const updateData: any = {
      investigationStatus: evaluationResult === "APPROVED" ? "COMPLETED" : "SUPPLEMENT_REQUIRED",
      evaluationResult: evaluationResult,
      updatedAt: new Date(),
    };

    if (evaluationScore !== undefined) {
      updateData.evaluationScore = evaluationScore;
    }
    if (investigationNotes) {
      updateData.investigationNotes = investigationNotes;
    }
    if (evaluationResult === "COMPLETED") {
      updateData.completedAt = new Date();
    }

    await db
      .update(vendorInvestigations)
      .set(updateData)
      .where(eq(vendorInvestigations.id, investigationId));

    // 3. 캐시 무효화
    revalidateTag("vendor-investigations");
    revalidateTag("site-visit-requests");

    return { success: true };
  } catch (error) {
    console.error("보완 재실사 완료 처리 실패:", error);
    return { 
      success: false, 
      error: error instanceof Error ? error.message : "알 수 없는 오류" 
    };
  }
}

// 실사 보완요청 메일 발송 액션
export async function requestInvestigationSupplementAction({
  investigationId,
  vendorId,
  comment,
}: {
  investigationId: number;
  vendorId: number;
  comment: string;
}) {
  unstable_noStore();
  try {
    const headersList = await import("next/headers").then(m => m.headers());
    const host = headersList.get('host') || 'localhost:3000';

    // 실사/벤더 정보 조회
    const investigation = await db
      .select({
        id: vendorInvestigations.id,
        pqSubmissionId: vendorInvestigations.pqSubmissionId,
        investigationAddress: vendorInvestigations.investigationAddress,
      })
      .from(vendorInvestigations)
      .where(eq(vendorInvestigations.id, investigationId))
      .then(rows => rows[0]);

    const vendor = await db
      .select({ email: vendors.email, vendorName: vendors.vendorName })
      .from(vendors)
      .where(eq(vendors.id, vendorId))
      .then(rows => rows[0]);

    if (!vendor?.email) {
      return { success: false, error: "벤더 이메일 정보가 없습니다." };
    }

    // PQ 번호 조회
    let pqNumber = "N/A";
    if (investigation?.pqSubmissionId) {
      const pqRow = await db
        .select({ pqNumber: vendorPQSubmissions.pqNumber })
        .from(vendorPQSubmissions)
        .where(eq(vendorPQSubmissions.id, investigation.pqSubmissionId))
        .then(rows => rows[0]);
      if (pqRow) pqNumber = pqRow.pqNumber;
    }

    // 메일 발송
    const portalUrl = process.env.NEXTAUTH_URL || `http://${host}`;
    const reviewUrl = `${portalUrl}/evcp/vendor-investigation`;

    await sendEmail({
      to: vendor.email,
      subject: `[eVCP] 실사 보완요청 - ${vendor.vendorName}`,
      template: "pq-investigation-supplement-request",
      context: {
        vendorName: vendor.vendorName,
        investigationNumber: pqNumber,
        supplementComment: comment,
        requestedAt: new Date().toLocaleString('ko-KR'),
        reviewUrl: reviewUrl,
        year: new Date().getFullYear(),
      }
    });

    // 실사 상태를 SUPPLEMENT_REQUIRED로 변경 (이미 되어있을 수 있음)
    await db
      .update(vendorInvestigations)
      .set({
        investigationStatus: "SUPPLEMENT_REQUIRED",
        updatedAt: new Date(),
      })
      .where(eq(vendorInvestigations.id, investigationId));

    revalidateTag("vendor-investigations");
    revalidateTag("pq-submissions");

    return { success: true };
  } catch (error) {
    console.error("실사 보완요청 메일 발송 오류:", error);
    return {
      success: false,
      error: error instanceof Error ? error.message : "알 수 없는 오류",
    };
  }
}

// 보완 서류제출 응답 제출 액션
export async function submitSupplementDocumentResponseAction({
  investigationId,
  responseData
}: {
  investigationId: number
  responseData: {
    responseText: string
    attachments: Array<{
      fileName: string
      url: string
      size?: number
    }>
  }
}) {
  try {
    // 1. 실사 상태를 SUPPLEMENT_REQUIRED로 변경
    await db
      .update(vendorInvestigations)
      .set({
        investigationStatus: "SUPPLEMENT_REQUIRED",
        investigationNotes: responseData.responseText,
        updatedAt: new Date(),
      })
      .where(eq(vendorInvestigations.id, investigationId));

    // 2. 첨부 파일 저장
    if (responseData.attachments.length > 0) {
      const attachmentData = responseData.attachments.map(attachment => ({
        investigationId,
        fileName: attachment.fileName,
        filePath: attachment.url,
        fileSize: attachment.size || 0,
        uploadedAt: new Date(),
      }));

      await db.insert(vendorInvestigationAttachments).values(attachmentData);
    }

    // 3. 캐시 무효화
    revalidateTag("vendor-investigations");
    revalidateTag("vendor-investigation-attachments");

    return { success: true };
  } catch (error) {
    console.error("보완 서류제출 응답 처리 실패:", error);
    return {
      success: false,
      error: error instanceof Error ? error.message : "알 수 없는 오류"
    };
  }
}

// QM 담당자 변경 서버 액션
export async function updateQMManagerAction({
  investigationId,
  qmManagerId,
}: {
  investigationId: number;
  qmManagerId: number;
}) {
  try {
    // 1. 실사 정보 조회 (상태 확인)
    const investigation = await db
      .select({
        investigationStatus: vendorInvestigations.investigationStatus,
        currentQmManagerId: vendorInvestigations.qmManagerId,
      })
      .from(vendorInvestigations)
      .where(eq(vendorInvestigations.id, investigationId))
      .limit(1);

    if (!investigation || investigation.length === 0) {
      return {
        success: false,
        error: "실사를 찾을 수 없습니다."
      };
    }

    const currentInvestigation = investigation[0];

    // 2. 상태 검증 (계획 상태만 변경 가능)
    if (currentInvestigation.investigationStatus !== "PLANNED") {
      return {
        success: false,
        error: "계획 상태인 실사만 QM 담당자를 변경할 수 있습니다."
      };
    }

    // 3. QM 담당자 정보 조회
    const qmManager = await db
      .select({
        id: users.id,
        name: users.name,
        email: users.email,
      })
      .from(users)
      .where(eq(users.id, qmManagerId))
      .limit(1);

    if (!qmManager || qmManager.length === 0) {
      return {
        success: false,
        error: "존재하지 않는 QM 담당자입니다."
      };
    }

    const qmUser = qmManager[0];

    // 4. QM 담당자 업데이트
    await db
      .update(vendorInvestigations)
      .set({
        qmManagerId: qmManagerId,
        updatedAt: new Date(),
      })
      .where(eq(vendorInvestigations.id, investigationId));

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

    return {
      success: true,
      message: "QM 담당자가 성공적으로 변경되었습니다."
    };

  } catch (error) {
    console.error("QM 담당자 변경 오류:", error);
    return {
      success: false,
      error: error instanceof Error ? error.message : "QM 담당자 변경 중 오류가 발생했습니다."
    };
  }
}