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

export async function getVendorCandidates(input: GetVendorsCandidateSchema) {
  return unstable_cache(
    async () => {
      try {
        const offset = (input.page - 1) * input.perPage

        // 1) Advanced filters
        const advancedWhere = filterColumns({
          table: vendorCandidates,
          filters: input.filters,
          joinOperator: input.joinOperator,
        })

        // 2) Global search
        let globalWhere
        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),
            // etc.
          )
        }

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



        // 5) Sorting
        const orderBy =
          input.sort && input.sort.length > 0
            ? input.sort.map((item) =>
              item.desc
                ? desc(vendorCandidates[item.id])
                : asc(vendorCandidates[item.id])
            )
            : [desc(vendorCandidates.createdAt)]

        // 6) Query & count
        const { data, total } = await db.transaction(async (tx) => {
          // a) Select from the view
          const candidatesData = await tx
            .select()
            .from(vendorCandidates)
            .where(finalWhere)
            .orderBy(...orderBy)
            .offset(offset)
            .limit(input.perPage)

          // b) Count total
          const resCount = await tx
            .select({ count: count() })
            .from(vendorCandidates)
            .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) {
  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();

    // Invalidate cache
    revalidateTag("vendor-candidates");

    return { success: true, data: newCandidate };
  } 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) {
  try {
    // Validate input
    const validated = updateVendorCandidateSchema.parse(input);
    
    // Prepare update data (excluding id)
    const { id, ...updateData } = validated;
    
    // 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`,
        }
      });
    }

    // Invalidate cache
    revalidateTag("vendor-candidates");

    return { success: true, data: updatedCandidate };
  } 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
}: {
  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" };
    }

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

    // Invalidate cache
    revalidateTag("vendor-candidates");

    return { 
      success: true, 
      data: updatedCandidates,
      count: updatedCandidates.length
    };
  } catch (error) {
    console.error("Failed to bulk update vendor candidates:", error);
    return { success: false, error: getErrorMessage(error) };
  }
}




/**
 * Remove multiple vendor candidates by their IDs
 */
export async function removeCandidates(input: RemoveCandidatesInput) {
  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 });

    // If no candidates were deleted, return an error
    if (!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})`)
    );

    // 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),
    };
  } catch (error) {
    console.error("Failed to remove vendor candidates:", error);
    return { success: false, error: getErrorMessage(error) };
  }
}