"use server"

import db from "@/db/db"
import { GetPOSchema } from "./validations"
import { unstable_cache } from "@/lib/unstable-cache";
import { filterColumns } from "@/lib/filter-columns";
import { asc, desc, ilike, inArray, and, gte, lte, not, or, eq, count} from "drizzle-orm";
import { countPos, selectPos } from "./repository";

import { contractEnvelopes, contractsDetailView, contractSigners,contracts  } from "@/db/schema/contract";
import { revalidatePath } from "next/cache";
import * as z from "zod"
import { vendorContacts } from "@/db/schema/vendors";

/**
 * PQ 목록 조회
 */
export async function getPOs(input: GetPOSchema) {
    return unstable_cache(
        async () => {
            try {
                const offset = (input.page - 1) * input.perPage;
                
                // 1. Try a simple query first to make sure the view works at all
                try {
                    const testQuery = await db.select({ count: count() })
                        .from(contractsDetailView);
                    console.log("Test query result:", testQuery);
                } catch (testErr) {
                    console.error("Test query failed:", testErr);
                }
                
                // 2. Build where clause with more careful handling
                let advancedWhere;
                try {
                    advancedWhere = filterColumns({
                        table: contractsDetailView,
                        filters: input.filters,
                        joinOperator: input.joinOperator,
                    });
                    console.log("Advanced where clause built successfully");
                } catch (whereErr) {
                    console.error("Error building advanced where:", whereErr);
                    advancedWhere = undefined;
                }
                
                let globalWhere;
                if (input.search) {
                    try {
                        const s = `%${input.search}%`;
                        globalWhere = or(
                            ilike(contractsDetailView.contractNo, s),
                            ilike(contractsDetailView.contractName, s),
                        );
                        console.log("Global where clause built successfully");
                    } catch (searchErr) {
                        console.error("Error building search where:", searchErr);
                        globalWhere = undefined;
                    }
                }
                
                // 3. Combine where clauses safely
                let finalWhere;
                if (advancedWhere && globalWhere) {
                    finalWhere = and(advancedWhere, globalWhere);
                } else {
                    finalWhere = advancedWhere || globalWhere;
                }
                
                
                // 4. Build order by
                let orderBy;
                try {
                    orderBy = input.sort.length > 0
                        ? input.sort.map((item) =>
                            item.desc ? desc(contractsDetailView[item.id]) : asc(contractsDetailView[item.id])
                        )
                        : [asc(contractsDetailView.createdAt)];
                } catch (orderErr) {
                    console.error("Error building order by:", orderErr);
                    orderBy = [asc(contractsDetailView.createdAt)];
                }
                
                // 5. Execute queries with proper error handling
                let data = [];
                let total = 0;
                
                try {
                    // Try without transaction first for better error visibility
                    const queryBuilder = db.select()
                        .from(contractsDetailView);
                    
                    // Add where clause if it exists
                    if (finalWhere) {
                        queryBuilder.where(finalWhere);
                    }
                    
                    // Add ordering
                    queryBuilder.orderBy(...orderBy);
                    
                    // Add pagination
                    queryBuilder.offset(offset).limit(input.perPage);
                    
                    // Execute query
                    data = await queryBuilder;
                    
                    // Get total count
                    const countBuilder = db.select({ count: count() })
                        .from(contractsDetailView);
                    
                    if (finalWhere) {
                        countBuilder.where(finalWhere);
                    }
                    
                    const countResult = await countBuilder;
                    total = countResult[0]?.count || 0;
                    
                } catch (queryErr) {
                    console.error("Query execution failed:", queryErr);
                    throw queryErr; // Rethrow to be caught by the outer try/catch
                }
                
                const pageCount = Math.ceil(total / input.perPage);
                
                return { data, pageCount };
            } catch (err) {
                // More detailed error logging
                console.error("Error in getPOs:", err);
                if (err instanceof Error) {
                    console.error("Error message:", err.message);
                    console.error("Error stack:", err.stack);
                }
                return { data: [], pageCount: 0 };
            }
        },
        [JSON.stringify(input)],
        {
            revalidate: 3600,
            tags: [`po`],
        }
    )();
}

// Schema for a single signer
const signerSchema = z.object({
    signerEmail: z.string().email(),
    signerName: z.string().min(1),
    signerPosition: z.string(),
    signerType: z.enum(["REQUESTER", "VENDOR"]),
    vendorContactId: z.number().optional(),
  });
  
  // Schema for the entire request
  const signatureRequestSchema = z.object({
    contractId: z.number(),
    signers: z.array(signerSchema).min(1, "At least one signer is required")
  });
  
  /**
   * Server action to request electronic signatures for a contract from multiple parties
   */
  export async function requestSignatures(
    input: z.infer<typeof signatureRequestSchema>
  ): Promise<{ success: boolean; message: string }> {
    try {
      // Validate the input
      const validatedData = signatureRequestSchema.parse(input);
      
      // Use a transaction to ensure data consistency
      return await db.transaction(async (tx) => {
        // Get contract details using standard select
        const [contract] = await tx
          .select()
          .from(contracts)
          .where(eq(contracts.id, validatedData.contractId))
          .limit(1);
        
        if (!contract) {
          throw new Error(`Contract with ID ${validatedData.contractId} not found`);
        }
        
        // Generate unique envelope ID
        const envelopeId = `env-${Date.now()}-${Math.floor(Math.random() * 1000)}`;
        
        // Get contract number or fallback
        const contractNo = contract.contractNo || `contract-${validatedData.contractId}`;
        
        // Create a single envelope for all signers
        const [newEnvelope] = await tx.insert(contractEnvelopes)
          .values({
            contractId: validatedData.contractId,
            envelopeId: envelopeId,
            envelopeStatus: "sent",
            fileName: `${contractNo}-signature.pdf`, // Required field
            filePath: `/contracts/${validatedData.contractId}/signatures/${envelopeId}.pdf`, // Required field
            // Add any other required fields based on your schema
          })
          .returning();
        
        // Check for duplicate emails
        const signerEmails = new Set();
        for (const signer of validatedData.signers) {
          if (signerEmails.has(signer.signerEmail)) {
            throw new Error(`Duplicate signer email: ${signer.signerEmail}`);
          }
          signerEmails.add(signer.signerEmail);
        }
        
        // Create signer records for each signer
        for (const signer of validatedData.signers) {
          await tx.insert(contractSigners)
            .values({
              envelopeId: newEnvelope.id,
              signerEmail: signer.signerEmail,
              signerName: signer.signerName,
              signerPosition: signer.signerPosition,
              signerStatus: "sent",
              signerType: signer.signerType,
              // Only include vendorContactId if it's provided and the signer is a vendor
              ...(signer.vendorContactId && signer.signerType === "VENDOR" 
                ? { vendorContactId: signer.vendorContactId } 
                : {})
            });
        }
        
        // Update contract status to indicate pending signatures
        await tx.update(contracts)
          .set({ status: "PENDING_SIGNATURE" })
          .where(eq(contracts.id, validatedData.contractId));
        
        // In a real implementation, you would send the envelope to DocuSign or similar service
        // For example:
        // const docusignResult = await docusignClient.createEnvelope({
        //   recipients: validatedData.signers.map(signer => ({
        //     email: signer.signerEmail,
        //     name: signer.signerName,
        //     recipientType: signer.signerType === "REQUESTER" ? "signer" : "cc",
        //     routingOrder: signer.signerType === "REQUESTER" ? 1 : 2,
        //   })),
        //   documentId: `contract-${validatedData.contractId}`,
        //   // other DocuSign-specific parameters
        // });
        
        // Revalidate the path to refresh the data
        revalidatePath("/po");
        
        // Return success response
        return { 
          success: true, 
          message: `Signature requests sent to ${validatedData.signers.length} recipient(s)` 
        };
      });
    } catch (error) {
      console.error("Error requesting electronic signatures:", error);
      return { 
        success: false, 
        message: error instanceof Error ? error.message : "Failed to send signature requests" 
      };
    }
  }

  export async function getVendorContacts(vendorId: number) {
    try {
      const contacts = await db
        .select({
          id: vendorContacts.id,
          contactName: vendorContacts.contactName,
          contactEmail: vendorContacts.contactEmail,
          contactPosition: vendorContacts.contactPosition,
          contactPhone: vendorContacts.contactPhone,
          isPrimary: vendorContacts.isPrimary,
        })
        .from(vendorContacts)
        .where(eq(vendorContacts.vendorId, vendorId))
        .orderBy(vendorContacts.isPrimary, vendorContacts.contactName);
      
      return contacts;
    } catch (error) {
      console.error("Error fetching vendor contacts:", error);
      throw new Error("Failed to fetch vendor contacts");
    }
  }