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

import { vendorInvestigationAttachments, vendorInvestigations, vendorInvestigationsView } from "@/db/schema/vendors"
import { GetVendorsInvestigationSchema, updateVendorInvestigationSchema } from "./validations"
import { asc, desc, ilike, inArray, and, or, gte, lte, eq, isNull, count } from "drizzle-orm";
import { revalidateTag, unstable_noStore } 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";

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.investigationNotes, s),
            ilike(vendorInvestigationsView.vendorEmail, s)
            // etc.
          )
        }

        // 3) Combine finalWhere
        // Example: Only show vendorStatus = "PQ_SUBMITTED"
        const finalWhere = and(
          advancedWhere,
          globalWhere,
          // eq(vendorInvestigationsView.vendorStatus, "PQ_APPROVED")
        )



        // 5) 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.investigationCreatedAt)]

        // 6) 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 }
        })

        // 7) Calculate pageCount
        const pageCount = Math.ceil(total / input.perPage)

        console.log(data,"data")

        // Now 'data' already contains JSON arrays of contacts & items
        // thanks to the subqueries in the view definition!
        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.investigationCreatedAt)],
    })

    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) Separate text fields from file fields
    const textEntries: Record<string, string> = {}
    for (const [key, value] of formData.entries()) {
      if (typeof value === "string") {
        textEntries[key] = value
      }
    }

    // 2) Convert text-based "investigationId" to a number
    if (textEntries.investigationId) {
      textEntries.investigationId = String(Number(textEntries.investigationId))
    }

    // 3) Parse/validate with Zod
    const parsed = updateVendorInvestigationSchema.parse(textEntries)
    // parsed is type UpdateVendorInvestigationSchema

    // 4) Update the vendor_investigations table
    await db
      .update(vendorInvestigations)
      .set({
        investigationStatus: parsed.investigationStatus,
        scheduledStartAt: parsed.scheduledStartAt
          ? new Date(parsed.scheduledStartAt)
          : null,
        scheduledEndAt: parsed.scheduledEndAt ? new Date(parsed.scheduledEndAt) : null,
        completedAt: parsed.completedAt ? new Date(parsed.completedAt) : null,
        investigationNotes: parsed.investigationNotes ?? "",
        updatedAt: new Date(),
      })
      .where(eq(vendorInvestigations.id, parsed.investigationId))

    // 5) Handle file attachments
    //    formData.getAll("attachments") can contain multiple files
    const files = formData.getAll("attachments") as File[]

    // Make sure the folder exists
    const uploadDir = path.join(process.cwd(), "public", "vendor-investigation")
    if (!fs.existsSync(uploadDir)) {
      fs.mkdirSync(uploadDir, { recursive: true })
    }

    for (const file of files) {
      if (file && file.size > 0) {
        // Create a unique filename
        const ext = path.extname(file.name) // e.g. ".pdf"
        const newFileName = `${uuid()}${ext}`

        const filePath = path.join(uploadDir, newFileName)

        // 6) Write file to disk
        const arrayBuffer = await file.arrayBuffer()
        const buffer = Buffer.from(arrayBuffer)
        fs.writeFileSync(filePath, buffer)

        // 7) Insert a record in vendor_investigation_attachments
        await db.insert(vendorInvestigationAttachments).values({
          investigationId: parsed.investigationId,
          fileName: file.name, // original name
          filePath: `/vendor-investigation/${newFileName}`, // relative path in public/
          attachmentType: "REPORT", // or user-specified
        })
      }
    }

    // Revalidate anything if needed
    revalidateTag("vendors-in-investigation")

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