"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) };
  }
}