(merge) 대표님/최겸 작업사항 머지

1 files changed, 395 insertions, 0 deletions

diff --git a/lib/tech-vendor-candidates/service.ts b/lib/tech-vendor-candidates/service.ts
new file mode 100644
index 00000000..47832236
--- /dev/null
+++ b/lib/tech-vendor-candidates/service.ts
@@ -0,0 +1,395 @@
+"use server"; // Next.js 서버 액션에서 직접 import하려면 (선택)
+
+import { asc, desc, ilike, inArray, and, or, gte, lte, eq, count } from "drizzle-orm";
+import { revalidateTag } 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 { CreateVendorCandidateSchema, createVendorCandidateSchema, GetTechVendorsCandidateSchema, RemoveTechCandidatesInput, removeTechCandidatesSchema, updateVendorCandidateSchema, UpdateVendorCandidateSchema } from "./validations";
+import { PgTransaction } from "drizzle-orm/pg-core";
+import { techVendorCandidates, techVendorCandidatesWithVendorInfo } from "@/db/schema/techVendors";
+import { headers } from 'next/headers';
+
+export async function getVendorCandidates(input: GetTechVendorsCandidateSchema) {
+  return unstable_cache(
+    async () => {
+      try {
+        const offset = (input.page - 1) * input.perPage
+        const fromDate = input.from ? new Date(input.from) : undefined;
+        const toDate = input.to ? new Date(input.to) : undefined;
+
+        // 1) Advanced filters
+        const advancedWhere = filterColumns({
+          table: techVendorCandidatesWithVendorInfo,
+          filters: input.filters,
+          joinOperator: input.joinOperator,
+        })
+
+        // 2) Global search
+        let globalWhere
+        if (input.search) {
+          const s = `%${input.search}%`
+          globalWhere = or(
+            ilike(techVendorCandidatesWithVendorInfo.companyName, s),
+            ilike(techVendorCandidatesWithVendorInfo.contactEmail, s),
+            ilike(techVendorCandidatesWithVendorInfo.contactPhone, s),
+            ilike(techVendorCandidatesWithVendorInfo.country, s),
+            ilike(techVendorCandidatesWithVendorInfo.source, s),
+            ilike(techVendorCandidatesWithVendorInfo.status, s),
+            ilike(techVendorCandidatesWithVendorInfo.taxId, s),
+            ilike(techVendorCandidatesWithVendorInfo.items, s),
+            ilike(techVendorCandidatesWithVendorInfo.remark, s),
+            ilike(techVendorCandidatesWithVendorInfo.address, s),
+            // etc.
+          )
+        }
+
+        // 3) Combine finalWhere
+        const finalWhere = and(
+          advancedWhere,
+          globalWhere,
+          fromDate ? gte(techVendorCandidatesWithVendorInfo.createdAt, fromDate) : undefined,
+          toDate ? lte(techVendorCandidatesWithVendorInfo.createdAt, toDate) : undefined
+        )
+
+        // 5) Sorting
+        const orderBy =
+          input.sort && input.sort.length > 0
+            ? input.sort.map((item) =>
+              item.desc
+                ? desc(techVendorCandidatesWithVendorInfo[item.id])
+                : asc(techVendorCandidatesWithVendorInfo[item.id])
+            )
+            : [desc(techVendorCandidatesWithVendorInfo.createdAt)]
+
+        // 6) Query & count
+        const { data, total } = await db.transaction(async (tx) => {
+          // a) Select from the view
+          const candidatesData = await tx
+            .select()
+            .from(techVendorCandidatesWithVendorInfo)
+            .where(finalWhere)
+            .orderBy(...orderBy)
+            .offset(offset)
+            .limit(input.perPage)
+
+          // b) Count total
+          const resCount = await tx
+            .select({ count: count() })
+            .from(techVendorCandidatesWithVendorInfo)
+            .where(finalWhere)
+
+          return { data: candidatesData, total: resCount[0]?.count }
+        })
+
+        // 7) Calculate pageCount
+        const pageCount = Math.ceil(total / input.perPage)
+
+        return { data, pageCount }
+      } catch (err) {
+        console.error(err)
+        return { data: [], pageCount: 0 }
+      }
+    },
+    // Cache key
+    [JSON.stringify(input)],
+    {
+      revalidate: 3600,
+      tags: ["tech-vendor-candidates"],
+    }
+  )()
+}
+
+export async function createVendorCandidate(input: CreateVendorCandidateSchema) {
+  try {
+    // Validate input
+    const validated = createVendorCandidateSchema.parse(input);
+
+    // 트랜잭션으로 데이터 삽입
+    const result = await db.transaction(async (tx) => {
+      // Insert into database
+      const [newCandidate] = await tx
+        .insert(techVendorCandidates)
+        .values({
+          companyName: validated.companyName,
+          contactEmail: validated.contactEmail,
+          contactPhone: validated.contactPhone || null,
+          taxId: validated.taxId || "",
+          address: validated.address || null,
+          country: validated.country || null,
+          source: validated.source || null,
+          status: validated.status || "COLLECTED",
+          remark: validated.remark || null,
+          items: validated.items || "", // items가 필수 필드이므로 빈 문자열이라도 제공
+          vendorId: validated.vendorId || null,
+          updatedAt: new Date(),
+        })
+        .returning();
+      
+      return newCandidate;
+    });
+
+    // Invalidate cache
+    revalidateTag("tech-vendor-candidates");
+
+    return { success: true, data: result };
+  } catch (error) {
+    console.error("Failed to create tech vendor candidate:", error);
+    return { success: false, error: getErrorMessage(error) };
+  }
+}
+
+
+// Helper function to group tech vendor candidates by status
+async function groupVendorCandidatesByStatus(tx: PgTransaction<Record<string, never>, Record<string, never>, Record<string, never>>) {
+  return tx
+    .select({
+      status: techVendorCandidates.status,
+      count: count(),
+    })
+    .from(techVendorCandidates)
+    .groupBy(techVendorCandidates.status);
+}
+
+/**
+ * Get count of tech vendor candidates grouped by status
+ */
+export async function getVendorCandidateCounts() {
+  return unstable_cache(
+    async () => {
+      try {
+        // Initialize counts object with all possible statuses set to 0
+        const initial: Record<"COLLECTED" | "INVITED" | "DISCARDED", number> = {
+          COLLECTED: 0,
+          INVITED: 0,
+          DISCARDED: 0,
+        };
+
+        // Execute query within transaction and transform results
+        const result = await db.transaction(async (tx) => {
+          const rows = await groupVendorCandidatesByStatus(tx);
+          return rows.reduce<Record<string, number>>((acc, { status, count }) => {
+            if (status in acc) {
+              acc[status] = count;
+            }
+            return acc;
+          }, initial);
+        });
+
+        return result;
+      } catch (err) {
+        console.error("Failed to get tech vendor candidate counts:", err);
+        return {
+          COLLECTED: 0,
+          INVITED: 0,
+          DISCARDED: 0,
+        };
+      }
+    },
+    ["tech-vendor-candidate-status-counts"], // Cache key
+    {
+      revalidate: 3600, // Revalidate every hour
+    }
+  )();
+}
+
+
+/**
+ * Update a vendor candidate
+ */
+export async function updateVendorCandidate(input: UpdateVendorCandidateSchema) {
+  try {
+    // Validate input
+    const validated = updateVendorCandidateSchema.parse(input);
+    
+    // Prepare update data (excluding id)
+    const { id, ...updateData } = validated;
+
+    const headersList = await headers();
+    const host = headersList.get('host') || 'localhost:3000';
+
+    const baseUrl = `http://${host}`
+    
+    // Add updatedAt timestamp
+    const dataToUpdate = {
+      ...updateData,
+      updatedAt: new Date(),
+    };
+    
+    const result = await db.transaction(async (tx) => {
+      // 현재 데이터 조회 (상태 변경 감지를 위해)
+      const [existingCandidate] = await tx
+        .select()
+        .from(techVendorCandidates)
+        .where(eq(techVendorCandidates.id, id));
+      
+      if (!existingCandidate) {
+        throw new Error("Tech vendor candidate not found");
+      }
+      
+      // Update database
+      const [updatedCandidate] = await tx
+        .update(techVendorCandidates)
+        .set(dataToUpdate)
+        .where(eq(techVendorCandidates.id, id))
+        .returning();
+      
+      // 로그 작성
+      const statusChanged = 
+        updateData.status && 
+        existingCandidate.status !== updateData.status;
+      
+      // If status was updated to "INVITED", send email
+      if (statusChanged && updateData.status === "INVITED" && updatedCandidate.contactEmail) {
+        await sendEmail({
+          to: updatedCandidate.contactEmail,
+          subject: "Invitation to Register as a Vendor",
+          template: "vendor-invitation",
+          context: {
+            companyName: updatedCandidate.companyName,
+            language: "en",
+            registrationLink: `${baseUrl}/en/partners`,
+          }
+        });
+      }
+      
+      return updatedCandidate;
+    });
+    
+    // Invalidate cache
+    revalidateTag("vendor-candidates");
+    
+    return { success: true, data: result };
+  } catch (error) {
+    console.error("Failed to update vendor candidate:", error);
+    return { success: false, error: getErrorMessage(error) };
+  }
+}
+
+export async function bulkUpdateVendorCandidateStatus({
+  ids,
+  status,
+}: {
+  ids: number[],
+  status: "COLLECTED" | "INVITED" | "DISCARDED",
+}) {
+  try {
+    // Validate inputs
+    if (!ids.length) {
+      return { success: false, error: "No IDs provided" };
+    }
+    
+    if (!["COLLECTED", "INVITED", "DISCARDED"].includes(status)) {
+      return { success: false, error: "Invalid status" };
+    }
+
+    const headersList = await headers();
+    const host = headersList.get('host') || 'localhost:3000';
+
+    const baseUrl = `http://${host}`
+    
+    const result = await db.transaction(async (tx) => {
+      // Update all records
+      const updatedCandidates = await tx
+        .update(techVendorCandidates)
+        .set({
+          status,
+          updatedAt: new Date(),
+        })
+        .where(inArray(techVendorCandidates.id, ids))
+        .returning();
+      
+      // If status is "INVITED", send emails to all updated candidates
+      if (status === "INVITED") {
+        const emailPromises = updatedCandidates
+          .filter(candidate => candidate.contactEmail)
+          .map(async (candidate) => {
+            await sendEmail({
+              to: candidate.contactEmail!,
+              subject: "Invitation to Register as a Vendor",
+              template: "vendor-invitation",
+              context: {
+                companyName: candidate.companyName,
+                language: "en",
+                registrationLink: `${baseUrl}/en/partners`,
+              }
+            });
+          });
+        
+        // Wait for all emails to be sent
+        await Promise.all(emailPromises);
+      }
+      
+      return updatedCandidates;
+    });
+    
+    // Invalidate cache
+    revalidateTag("vendor-candidates");
+    
+    return {
+      success: true,
+      data: result,
+      count: result.length
+    };
+  } catch (error) {
+    console.error("Failed to bulk update vendor candidates:", error);
+    return { success: false, error: getErrorMessage(error) };
+  }
+}
+
+// 4. 후보자 삭제 함수 업데이트
+export async function removeCandidates(input: RemoveTechCandidatesInput) {
+  try {
+    // Validate input
+    const validated = removeTechCandidatesSchema.parse(input);
+    
+    const result = await db.transaction(async (tx) => {
+      // Get candidates before deletion (for logging purposes)
+      const candidatesBeforeDelete = await tx
+        .select()
+        .from(techVendorCandidates)
+        .where(inArray(techVendorCandidates.id, validated.ids));
+      
+      // Delete the candidates
+      const deletedCandidates = await tx
+        .delete(techVendorCandidates)
+        .where(inArray(techVendorCandidates.id, validated.ids))
+        .returning({ id: techVendorCandidates.id });
+      
+      return {
+        deletedCandidates,
+        candidatesBeforeDelete
+      };
+    });
+    
+    // If no candidates were deleted, return an error
+    if (!result.deletedCandidates.length) {
+      return {
+        success: false,
+        error: "No candidates were found with the provided IDs",
+      };
+    }
+    
+    // Log deletion for audit purposes
+    console.log(
+      `Deleted ${result.deletedCandidates.length} vendor candidates:`,
+      result.candidatesBeforeDelete.map(c => `${c.id} (${c.companyName})`)
+    );
+    
+    // Invalidate cache
+    revalidateTag("vendor-candidates");
+    revalidateTag("vendor-candidate-status-counts");
+    revalidateTag("vendor-candidate-total-count");
+    
+    return {
+      success: true,
+      count: result.deletedCandidates.length,
+      deletedIds: result.deletedCandidates.map(c => c.id),
+    };
+  } catch (error) {
+    console.error("Failed to remove vendor candidates:", error);
+    return { success: false, error: getErrorMessage(error) };
+  }
+}
\ No newline at end of file