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

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 { 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: vendorCandidatesWithVendorInfo,
          filters: input.filters,
          joinOperator: input.joinOperator,
        })

        // 2) Global search
        let globalWhere
        if (input.search) {
          const s = `%${input.search}%`
          globalWhere = or(
            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.
          )
        }

        // 3) Combine finalWhere
        // Example: Only show vendorStatus = "PQ_SUBMITTED"
        const finalWhere = and(
          advancedWhere,
          globalWhere,
          fromDate ? gte(vendorCandidatesWithVendorInfo.createdAt, fromDate) : undefined,
          toDate ? lte(vendorCandidatesWithVendorInfo.createdAt, toDate) : undefined
        )



        // 5) Sorting
        const orderBy =
          input.sort && input.sort.length > 0
            ? input.sort.map((item) =>
              item.desc
                ? desc(vendorCandidatesWithVendorInfo[item.id])
                : asc(vendorCandidatesWithVendorInfo[item.id])
            )
            : [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(vendorCandidatesWithVendorInfo)
            .where(finalWhere)
            .orderBy(...orderBy)
            .offset(offset)
            .limit(input.perPage)

          // b) Count total
          const resCount = await tx
            .select({ count: count() })
            .from(vendorCandidatesWithVendorInfo)
            .where(finalWhere)

          return { data: candidatesData, total: resCount[0]?.count }
        })

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

        // 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: ["vendor-candidates"],
    }
  )()
}

export async function createVendorCandidate(input: CreateVendorCandidateSchema, userId: number) {
  try {
    // Validate input
    const validated = createVendorCandidateSchema.parse(input);

    // 트랜잭션으로 데이터 삽입과 로그 기록을 원자적으로 처리
    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: result };
  } catch (error) {
    console.error("Failed to create vendor candidate:", error);
    return { success: false, error: getErrorMessage(error) };
  }
}


// Helper function to group vendor candidates by status
async function groupVendorCandidatesByStatus(  tx: PgTransaction<any, any, any>,) {
  return tx
    .select({
      status: vendorCandidates.status,
      count: count(),
    })
    .from(vendorCandidates)
    .groupBy(vendorCandidates.status);
}

/**
 * Get count of 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 vendor candidate counts:", err);
        return {
          COLLECTED: 0,
          INVITED: 0,
          DISCARDED: 0,
        };
      }
    },
    ["vendor-candidate-status-counts"], // Cache key
    {
      revalidate: 3600, // Revalidate every hour
      // tags: ["vendor-candidates"], // Use the same tag as other vendor candidate functions
    }
  )();
}


/**
 * Update a vendor candidate
 */
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(),
    };
    
    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 !== undefined && 
        existingCandidate.status !== updateData.status;
      
       // 상태가 변경된 경우에만 상태 변경 로그 기록
       if (statusChanged) {
        await tx.insert(vendorCandidateLogs).values({
          vendorCandidateId: id,
          userId: userId,
          action: "status_change",
          oldStatus: existingCandidate.status,
          newStatus: updateData.status,
          comment: `Status changed from ${existingCandidate.status} to ${updateData.status}`
        });
      }
      
      // 상태가 변경되지 않았지만 다른 필드가 변경된 경우에만 일반 업데이트 로그 기록
      // (실제로 변경된 필드가 있는지 확인하는 로직은 복잡하므로, 상태 변경이 아닌 경우에만 로그 기록)
      // 참고: 모든 필드가 동일한 경우도 있지만, 사용자가 저장 버튼을 눌렀다는 것은 변경 의도가 있다는 의미
      if (!statusChanged) {
        // 다른 필드 변경 여부를 간단히 확인 (실제로는 더 정교한 비교가 필요할 수 있음)
        const hasOtherChanges = Object.keys(updateData).some(key => {
          if (key === 'status' || key === 'updatedAt') return false;
          return (existingCandidate as any)[key] !== (updateData as any)[key];
        });
        
        if (hasOtherChanges) {
          await tx.insert(vendorCandidateLogs).values({
            vendorCandidateId: id,
            userId: userId,
            action: "update",
            comment: `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: result };
  } catch (error) {
    console.error("Failed to update vendor candidate:", error);
    return { success: false, error: getErrorMessage(error) };
  }
}

export async function bulkUpdateVendorCandidateStatus({
  ids,
  status,
  userId,
  comment
}: {
  ids: number[],
  status: "COLLECTED" | "INVITED" | "DISCARDED",
  userId: number,
  comment?: string
}) {
  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) => {
      // 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: 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: RemoveCandidatesInput, userId: number) {
  try {
    // Validate input
    const validated = removeCandidatesSchema.parse(input);
    
    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 (!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) };
  }
}

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