path: root/lib/po/service.ts

"use server";
import path from "path";
import { v4 as uuidv4 } from "uuid";
import { headers } from "next/headers";
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 { vendors, vendorContacts } from "@/db/schema/vendors";
import { revalidatePath } from "next/cache";
import * as z from "zod";
import { POContent } from "@/lib/docuSign/types";

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

    const headersList = await headers();
    const host = headersList.get("host");
    const proto = headersList.get("x-forwarded-proto") || "http"; // 기본값은 http
    const origin = `${proto}://${host}`;

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

      const signer = validatedData.signers.find(
        (c) => c.signerType === "REQUESTER"
      );

      const vendor = validatedData.signers.find(
        (c) => c.signerType === "VENDOR"
      );

      if (!vendor || !signer) {
        return {
          success: true,
          message: `협력업체 서명자를 확인할 수 없습니다.`,
        };
      }

      const { vendorContactId } = vendor;

      if (!vendorContactId) {
        return {
          success: true,
          message: `계약 번호를 확인할 수 없습니다.`,
        };
      }

      const [vendorInfoData] = await tx
        .select({
          vendorContract: vendorContacts,
          vendorInfo: vendors,
        })
        .from(vendorContacts)
        .leftJoin(vendors, eq(vendorContacts.vendorId, vendors.id))
        .where(eq(vendorContacts.id, vendorContactId))
        .limit(1);

      const { vendorContract, vendorInfo } = vendorInfoData;

      const docuSignTempId = "73b04617-477c-4ec8-8a32-c8da701f6b0c";

      const { totalAmount = "0", tax = "0" } = contract;

      const totalAmountNum = Number(totalAmount);
      const taxNum = Number(tax);
      const taxRate = ((taxNum / totalAmountNum) * 100).toFixed(2);

      const contractInfo: POContent = [
        { tabLabel: "po_no", value: contractNo },
        { tabLabel: "vendor_name", value: vendorInfo?.vendorName ?? "" },
        { tabLabel: "po_date", value: contract?.startDate ?? "" },
        { tabLabel: "project_name", value: contract.contractName },
        { tabLabel: "vendor_location", value: vendorInfo?.address ?? "" },
        { tabLabel: "shi_email", value: signer.signerEmail },
        { tabLabel: "vendor_email", value: vendorContract.contactEmail },
        { tabLabel: "po_desc", value: contract.contractName },
        { tabLabel: "qty", value: "1" },
        { tabLabel: "unit_price", value: totalAmountNum.toLocaleString() },
        { tabLabel: "total", value: totalAmountNum.toLocaleString() },
        {
          tabLabel: "grand_total_amount",
          value: totalAmountNum.toLocaleString(),
        },
        { tabLabel: "tax_rate", value: taxRate },
        { tabLabel: "tax_total", value: taxNum.toLocaleString() },
        {
          tabLabel: "payment_amount",
          value: (totalAmountNum + taxNum).toLocaleString(),
        },
        {
          tabLabel: "remark",
          value: `결제 조건: ${contract.paymentTerms}
납품 조건: ${contract.deliveryTerms}
납품 기한: ${contract.deliveryDate}
납품 장소: ${contract.deliveryLocation}     
계약 종료일/유효 기간: ${contract.endDate}          
Remarks:${contract.remarks}`,
        },
      ];

      const sendDocuSign = await fetch(`${origin}/api/po/sendDocuSign`, {
        method: "POST",
        headers: {
          "Content-Type": "application/json", // ✅ 이거 꼭 있어야 함!
        },
        body: JSON.stringify({
          docuSignTempId,
          contractInfo,
          contractorInfo: {
            email: "dts@dtsolution.co.kr",
            name: "삼성중공업",
            roleName: "shi",
          },
          subcontractorinfo: {
            email: vendorContract.contactEmail,
            name: vendorInfo?.vendorName,
            roleName: "vendor",
          },
          ccInfo: [
            // {
            //   email: "kiman.kim@dtsolution.io",
            //   name: "김기만",
            //   roleName: "cc",
            // },
          ],
        }),
      }).then((data) => data.json());

      const { success: sendDocuSignResult, envelopeId } = sendDocuSign;

      await tx
        .update(contracts)
        .set({
          status: sendDocuSignResult ? "PENDING_SIGNATURE" : "Docu Sign Failed",
        })
        .where(eq(contracts.id, validatedData.contractId));

      if (!sendDocuSignResult) {
        return {
          success: false,
          message: "DocuSign Mail 발송에 실패하였습니다.",
        };
      }

      // Update contract status to indicate pending signatures

      const fileName = `${contractNo}-signature.pdf`;
      const ext = path.extname(fileName);
      const uniqueName = uuidv4() + ext;

      // 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/${uniqueName}`, // 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 }
            : {}),
        });
      }

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