~20250428 작업사항

1 files changed, 292 insertions, 129 deletions

diff --git a/lib/vendor-candidates/service.ts b/lib/vendor-candidates/service.ts
index 68971f18..bfeb3090 100644
--- a/lib/vendor-candidates/service.ts
+++ b/lib/vendor-candidates/service.ts
@@ -1,6 +1,5 @@
 "use server"; // Next.js 서버 액션에서 직접 import하려면 (선택)
 
-import { vendorCandidates} from "@/db/schema/vendors"
 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";
@@ -10,16 +9,20 @@ import db from "@/db/db";
 import { sendEmail } from "../mail/sendEmail";
 import { CreateVendorCandidateSchema, createVendorCandidateSchema, GetVendorsCandidateSchema, RemoveCandidatesInput, removeCandidatesSchema, updateVendorCandidateSchema, UpdateVendorCandidateSchema } from "./validations";
 import { PgTransaction } from "drizzle-orm/pg-core";
+import { users, vendorCandidateLogs, vendorCandidates, vendorCandidatesWithVendorInfo } from "@/db/schema";
+import { headers } from 'next/headers';
 
 export async function getVendorCandidates(input: GetVendorsCandidateSchema) {
   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: vendorCandidates,
+          table: vendorCandidatesWithVendorInfo,
           filters: input.filters,
           joinOperator: input.joinOperator,
         })
@@ -29,12 +32,16 @@ export async function getVendorCandidates(input: GetVendorsCandidateSchema) {
         if (input.search) {
           const s = `%${input.search}%`
           globalWhere = or(
-            ilike(vendorCandidates.companyName, s),
-            ilike(vendorCandidates.contactEmail, s),
-            ilike(vendorCandidates.contactPhone, s),
-            ilike(vendorCandidates.country, s),
-            ilike(vendorCandidates.source, s),
-            ilike(vendorCandidates.status, s),
+            ilike(vendorCandidatesWithVendorInfo.companyName, s),
+            ilike(vendorCandidatesWithVendorInfo.contactEmail, s),
+            ilike(vendorCandidatesWithVendorInfo.contactPhone, s),
+            ilike(vendorCandidatesWithVendorInfo.country, s),
+            ilike(vendorCandidatesWithVendorInfo.source, s),
+            ilike(vendorCandidatesWithVendorInfo.status, s),
+            ilike(vendorCandidatesWithVendorInfo.taxId, s),
+            ilike(vendorCandidatesWithVendorInfo.items, s),
+            ilike(vendorCandidatesWithVendorInfo.remark, s),
+            ilike(vendorCandidatesWithVendorInfo.address, s),
             // etc.
           )
         }
@@ -44,6 +51,8 @@ export async function getVendorCandidates(input: GetVendorsCandidateSchema) {
         const finalWhere = and(
           advancedWhere,
           globalWhere,
+          fromDate ? gte(vendorCandidatesWithVendorInfo.createdAt, fromDate) : undefined,
+          toDate ? lte(vendorCandidatesWithVendorInfo.createdAt, toDate) : undefined
         )
 
 
@@ -53,17 +62,17 @@ export async function getVendorCandidates(input: GetVendorsCandidateSchema) {
           input.sort && input.sort.length > 0
             ? input.sort.map((item) =>
               item.desc
-                ? desc(vendorCandidates[item.id])
-                : asc(vendorCandidates[item.id])
+                ? desc(vendorCandidatesWithVendorInfo[item.id])
+                : asc(vendorCandidatesWithVendorInfo[item.id])
             )
-            : [desc(vendorCandidates.createdAt)]
+            : [desc(vendorCandidatesWithVendorInfo.createdAt)]
 
         // 6) Query & count
         const { data, total } = await db.transaction(async (tx) => {
           // a) Select from the view
           const candidatesData = await tx
             .select()
-            .from(vendorCandidates)
+            .from(vendorCandidatesWithVendorInfo)
             .where(finalWhere)
             .orderBy(...orderBy)
             .offset(offset)
@@ -72,7 +81,7 @@ export async function getVendorCandidates(input: GetVendorsCandidateSchema) {
           // b) Count total
           const resCount = await tx
             .select({ count: count() })
-            .from(vendorCandidates)
+            .from(vendorCandidatesWithVendorInfo)
             .where(finalWhere)
 
           return { data: candidatesData, total: resCount[0]?.count }
@@ -98,30 +107,48 @@ export async function getVendorCandidates(input: GetVendorsCandidateSchema) {
   )()
 }
 
-export async function createVendorCandidate(input: CreateVendorCandidateSchema) {
+export async function createVendorCandidate(input: CreateVendorCandidateSchema, userId: number) {
   try {
     // Validate input
     const validated = createVendorCandidateSchema.parse(input);
 
-    // Insert into database
-    const [newCandidate] = await db
-      .insert(vendorCandidates)
-      .values({
-        companyName: validated.companyName,
-        contactEmail: validated.contactEmail,
-        contactPhone: validated.contactPhone || null,
-        country: validated.country || null,
-        source: validated.source || null,
-        status: validated.status,
-        createdAt: new Date(),
-        updatedAt: new Date(),
-      })
-      .returning();
+    // 트랜잭션으로 데이터 삽입과 로그 기록을 원자적으로 처리
+    const result = await db.transaction(async (tx) => {
+      // Insert into database
+      const [newCandidate] = await tx
+        .insert(vendorCandidates)
+        .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();
+      
+      // 로그에 기록
+      await tx.insert(vendorCandidateLogs).values({
+        vendorCandidateId: newCandidate.id,
+        userId: userId,
+        action: "create",
+        newStatus: newCandidate.status,
+        comment: `Created new vendor candidate: ${newCandidate.companyName}`
+      });
+      
+      return newCandidate;
+    });
 
     // Invalidate cache
     revalidateTag("vendor-candidates");
 
-    return { success: true, data: newCandidate };
+    return { success: true, data: result };
   } catch (error) {
     console.error("Failed to create vendor candidate:", error);
     return { success: false, error: getErrorMessage(error) };
@@ -187,60 +214,107 @@ export async function getVendorCandidateCounts() {
 /**
  * Update a vendor candidate
  */
-export async function updateVendorCandidate(input: UpdateVendorCandidateSchema) {
+export async function updateVendorCandidate(input: UpdateVendorCandidateSchema, userId: number) {
   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(),
     };
-
-    // Update database
-    const [updatedCandidate] = await db
-      .update(vendorCandidates)
-      .set(dataToUpdate)
-      .where(eq(vendorCandidates.id, id))
-      .returning();
-
-    // If status was updated to "INVITED", send email
-    if (validated.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: `${process.env.NEXT_PUBLIC_APP_URL}/en/partners`,
-        }
+    
+    const result = await db.transaction(async (tx) => {
+      // 현재 데이터 조회 (상태 변경 감지를 위해)
+      const [existingCandidate] = await tx
+        .select()
+        .from(vendorCandidates)
+        .where(eq(vendorCandidates.id, id));
+      
+      if (!existingCandidate) {
+        throw new Error("Vendor candidate not found");
+      }
+      
+      // Update database
+      const [updatedCandidate] = await tx
+        .update(vendorCandidates)
+        .set(dataToUpdate)
+        .where(eq(vendorCandidates.id, id))
+        .returning();
+      
+      // 로그 작성
+      const statusChanged = 
+        updateData.status && 
+        existingCandidate.status !== updateData.status;
+      
+      await tx.insert(vendorCandidateLogs).values({
+        vendorCandidateId: id,
+        userId: userId,
+        action: statusChanged ? "status_change" : "update",
+        oldStatus: statusChanged ? existingCandidate.status : undefined,
+        newStatus: statusChanged ? updateData.status : undefined,
+        comment: statusChanged 
+          ? `Status changed from ${existingCandidate.status} to ${updateData.status}`
+          : `Updated vendor candidate: ${existingCandidate.companyName}`
       });
-    }
 
+
+
+      
+      // 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`,
+          }
+        });
+        
+        // 이메일 전송 로그
+        await tx.insert(vendorCandidateLogs).values({
+          vendorCandidateId: id,
+          userId: userId,
+          action: "invite_sent",
+          comment: `Invitation email sent to ${updatedCandidate.contactEmail}`
+        });
+      }
+      
+      return updatedCandidate;
+    });
+    
     // Invalidate cache
     revalidateTag("vendor-candidates");
-
-    return { success: true, data: updatedCandidate };
+    
+    return { success: true, data: result };
   } catch (error) {
     console.error("Failed to update vendor candidate:", error);
     return { success: false, error: getErrorMessage(error) };
   }
 }
 
-/**
- * Update status of multiple vendor candidates at once
- */
 export async function bulkUpdateVendorCandidateStatus({
   ids,
-  status
+  status,
+  userId,
+  comment
 }: {
   ids: number[],
-  status: "COLLECTED" | "INVITED" | "DISCARDED"
+  status: "COLLECTED" | "INVITED" | "DISCARDED",
+  userId: number,
+  comment?: string
 }) {
   try {
     // Validate inputs
@@ -252,50 +326,86 @@ export async function bulkUpdateVendorCandidateStatus({
       return { success: false, error: "Invalid status" };
     }
 
-    // Get current data of candidates (needed for email sending)
-    const candidatesBeforeUpdate = await db
-      .select()
-      .from(vendorCandidates)
-      .where(inArray(vendorCandidates.id, ids));
-
-    // Update all records
-    const updatedCandidates = await db
-      .update(vendorCandidates)
-      .set({
-        status,
-        updatedAt: new Date(),
-      })
-      .where(inArray(vendorCandidates.id, ids))
-      .returning();
-
-    // If status is "INVITED", send emails to all updated candidates
-    if (status === "INVITED") {
-      const emailPromises = updatedCandidates
-        .filter(candidate => candidate.contactEmail)
-        .map(candidate => 
-          sendEmail({
-            to: candidate.contactEmail!,
-            subject: "Invitation to Register as a Vendor",
-            template: "vendor-invitation",
-            context: {
-              companyName: candidate.companyName,
-              language: "en",
-              registrationLink: `${process.env.NEXT_PUBLIC_APP_URL}/en/partners`,
-            }
-          })
-        );
-      
-      // Wait for all emails to be sent
-      await Promise.all(emailPromises);
-    }
+    const headersList = await headers();
+    const host = headersList.get('host') || 'localhost:3000';
 
+    const baseUrl = `http://${host}`
+    
+    const result = await db.transaction(async (tx) => {
+      // Get current data of candidates (needed for email sending and logging)
+      const candidatesBeforeUpdate = await tx
+        .select()
+        .from(vendorCandidates)
+        .where(inArray(vendorCandidates.id, ids));
+      
+      // Update all records
+      const updatedCandidates = await tx
+        .update(vendorCandidates)
+        .set({
+          status,
+          updatedAt: new Date(),
+        })
+        .where(inArray(vendorCandidates.id, ids))
+        .returning();
+      
+      // 각 후보자에 대한 로그 생성
+      const logPromises = candidatesBeforeUpdate.map(candidate => {
+        if (candidate.status === status) {
+          // 상태가 변경되지 않은 경우 로그 생성하지 않음
+          return Promise.resolve();
+        }
+        
+        return tx.insert(vendorCandidateLogs).values({
+          vendorCandidateId: candidate.id,
+          userId: userId,
+          action: "status_change",
+          oldStatus: candidate.status,
+          newStatus: status,
+          comment: comment || `Bulk status update to ${status}`
+        });
+      });
+      
+      await Promise.all(logPromises);
+      
+      // 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`,
+              }
+            });
+            
+            // 이메일 발송 로그
+            await tx.insert(vendorCandidateLogs).values({
+              vendorCandidateId: candidate.id,
+              userId: userId,
+              action: "invite_sent",
+              comment: `Invitation email sent to ${candidate.contactEmail}`
+            });
+          });
+        
+        // Wait for all emails to be sent
+        await Promise.all(emailPromises);
+      }
+      
+      return updatedCandidates;
+    });
+    
     // Invalidate cache
     revalidateTag("vendor-candidates");
-
-    return { 
-      success: true, 
-      data: updatedCandidates,
-      count: updatedCandidates.length
+    
+    return {
+      success: true,
+      data: result,
+      count: result.length
     };
   } catch (error) {
     console.error("Failed to bulk update vendor candidates:", error);
@@ -303,58 +413,111 @@ export async function bulkUpdateVendorCandidateStatus({
   }
 }
 
-
-
-
-/**
- * Remove multiple vendor candidates by their IDs
- */
-export async function removeCandidates(input: RemoveCandidatesInput) {
+// 4. 후보자 삭제 함수 업데이트
+export async function removeCandidates(input: RemoveCandidatesInput, userId: number) {
   try {
     // Validate input
     const validated = removeCandidatesSchema.parse(input);
-
-    // Get candidates before deletion (for logging purposes)
-    const candidatesBeforeDelete = await db
-      .select({
-        id: vendorCandidates.id,
-        companyName: vendorCandidates.companyName,
-      })
-      .from(vendorCandidates)
-      .where(inArray(vendorCandidates.id, validated.ids));
-
-    // Delete the candidates
-    const deletedCandidates = await db
-      .delete(vendorCandidates)
-      .where(inArray(vendorCandidates.id, validated.ids))
-      .returning({ id: vendorCandidates.id });
-
+    
+    const result = await db.transaction(async (tx) => {
+      // Get candidates before deletion (for logging purposes)
+      const candidatesBeforeDelete = await tx
+        .select()
+        .from(vendorCandidates)
+        .where(inArray(vendorCandidates.id, validated.ids));
+      
+      // 각 삭제될 후보자에 대한 로그 생성
+      for (const candidate of candidatesBeforeDelete) {
+        await tx.insert(vendorCandidateLogs).values({
+          vendorCandidateId: candidate.id,
+          userId: userId,
+          action: "delete",
+          oldStatus: candidate.status,
+          comment: `Deleted vendor candidate: ${candidate.companyName}`
+        });
+      }
+      
+      // Delete the candidates
+      const deletedCandidates = await tx
+        .delete(vendorCandidates)
+        .where(inArray(vendorCandidates.id, validated.ids))
+        .returning({ id: vendorCandidates.id });
+      
+      return {
+        deletedCandidates,
+        candidatesBeforeDelete
+      };
+    });
+    
     // If no candidates were deleted, return an error
-    if (!deletedCandidates.length) {
+    if (!result.deletedCandidates.length) {
       return {
         success: false,
         error: "No candidates were found with the provided IDs",
       };
     }
-
+    
     // Log deletion for audit purposes
     console.log(
-      `Deleted ${deletedCandidates.length} vendor candidates:`,
-      candidatesBeforeDelete.map(c => `${c.id} (${c.companyName})`)
+      `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: deletedCandidates.length,
-      deletedIds: deletedCandidates.map(c => c.id),
+      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) };
   }
+}
+
+export interface CandidateLogWithUser {
+  id: number
+  vendorCandidateId: number
+  userId: number
+  userName: string | null
+  userEmail: string | null
+  action: string
+  oldStatus: string | null
+  newStatus: string | null
+  comment: string | null
+  createdAt: Date
+}
+
+export async function getCandidateLogs(candidateId: number): Promise<CandidateLogWithUser[]> {
+  try {
+    const logs = await db
+      .select({
+        // vendor_candidate_logs 필드
+        id: vendorCandidateLogs.id,
+        vendorCandidateId: vendorCandidateLogs.vendorCandidateId,
+        userId: vendorCandidateLogs.userId,
+        action: vendorCandidateLogs.action,
+        oldStatus: vendorCandidateLogs.oldStatus,
+        newStatus: vendorCandidateLogs.newStatus,
+        comment: vendorCandidateLogs.comment,
+        createdAt: vendorCandidateLogs.createdAt,
+
+        // 조인한 users 테이블 필드
+        userName: users.name,
+        userEmail: users.email,
+      })
+      .from(vendorCandidateLogs)
+      .leftJoin(users, eq(vendorCandidateLogs.userId, users.id))
+      .where(eq(vendorCandidateLogs.vendorCandidateId, candidateId))
+      .orderBy(desc(vendorCandidateLogs.createdAt))
+
+    return logs
+  } catch (error) {
+    console.error("Failed to fetch candidate logs with user info:", error)
+    throw error
+  }
 }
\ No newline at end of file