1 files changed, 704 insertions, 2 deletions

diff --git a/lib/vendor-document/service.ts b/lib/vendor-document/service.ts
index bf2b0b7a..48e3fa3f 100644
--- a/lib/vendor-document/service.ts
+++ b/lib/vendor-document/service.ts
@@ -2,14 +2,14 @@
 
 import { eq, SQL } from "drizzle-orm"
 import db from "@/db/db"
-import { documentAttachments, documents, issueStages, revisions, vendorDocumentsView } from "@/db/schema/vendorDocu"
+import { stageSubmissions, stageDocuments, stageIssueStages,documentAttachments, documents, issueStages, revisions, stageDocumentsView,vendorDocumentsView ,stageSubmissionAttachments, StageIssueStage, StageDocumentsView, StageDocument,} from "@/db/schema/vendorDocu"
 import { GetVendorDcoumentsSchema } from "./validations"
 import { unstable_cache } from "@/lib/unstable-cache";
 import { filterColumns } from "@/lib/filter-columns";
 import { getErrorMessage } from "@/lib/handle-error";
 import { asc, desc, ilike, inArray, and, gte, lte, not, or , isNotNull, isNull} from "drizzle-orm";
 import { countVendorDocuments, selectVendorDocuments } from "./repository"
-import { contractItems } from "@/db/schema"
+import { contractItems, projects, items,contracts } from "@/db/schema"
 import { saveFile } from "../file-stroage"
 import path from "path"
 
@@ -494,4 +494,706 @@ export async function fetchRevisionsByStageParams(
     console.error("Error fetching revisions:", error);
     return [];
   }
+}
+
+// 타입 정의
+type SubmissionInfo = {
+  id: number;
+  revisionNumber: number;
+  revisionCode: string;
+  revisionType: string;
+  submissionStatus: string;
+  submittedBy: string;
+  submittedAt: Date;
+  reviewStatus: string | null;
+  buyerSystemStatus: string | null;
+  syncStatus: string;
+};
+
+type AttachmentInfo = {
+  id: number;
+  fileName: string;
+  originalFileName: string;
+  fileSize: number;
+  fileType: string | null;
+  storageUrl: string | null;
+  syncStatus: string;
+  buyerSystemStatus: string | null;
+  uploadedAt: Date;
+};
+
+// Server Action: Fetch documents by projectCode and packageCode
+export async function fetchDocumentsByProjectAndPackage(
+  projectCode: string,
+  packageCode: string
+): Promise<StageDocument[]> {
+  try {
+    // First, find the project by code
+    const projectResult = await db
+      .select({ id: projects.id })
+      .from(projects)
+      .where(eq(projects.code, projectCode))
+      .limit(1);
+
+    if (!projectResult.length) {
+      return [];
+    }
+
+    const projectId = projectResult[0].id;
+
+    // Find contract through contractItems joined with items table
+    const contractItemResult = await db
+      .select({ 
+        contractId: contractItems.contractId 
+      })
+      .from(contractItems)
+      .innerJoin(contracts, eq(contractItems.contractId, contracts.id))
+      .innerJoin(items, eq(contractItems.itemId, items.id))
+      .where(
+        and(
+          eq(contracts.projectId, projectId),
+          eq(items.packageCode, packageCode)
+        )
+      )
+      .limit(1);
+
+    if (!contractItemResult.length) {
+      return [];
+    }
+
+    const contractId = contractItemResult[0].contractId;
+
+    // Get stage documents
+    const docsResult = await db
+      .select({
+        id: stageDocuments.id,
+        docNumber: stageDocuments.docNumber,
+        title: stageDocuments.title,
+        vendorDocNumber: stageDocuments.vendorDocNumber,
+        status: stageDocuments.status,
+        issuedDate: stageDocuments.issuedDate,
+        docClass: stageDocuments.docClass,
+        projectId: stageDocuments.projectId,
+        vendorId: stageDocuments.vendorId,
+        contractId: stageDocuments.contractId,
+        buyerSystemStatus: stageDocuments.buyerSystemStatus,
+        buyerSystemComment: stageDocuments.buyerSystemComment,
+        lastSyncedAt: stageDocuments.lastSyncedAt,
+        syncStatus: stageDocuments.syncStatus,
+        syncError: stageDocuments.syncError,
+        syncVersion: stageDocuments.syncVersion,
+        lastModifiedBy: stageDocuments.lastModifiedBy,
+        createdAt: stageDocuments.createdAt,
+        updatedAt: stageDocuments.updatedAt,
+      })
+      .from(stageDocuments)
+      .where(
+        and(
+          eq(stageDocuments.projectId, projectId),
+          eq(stageDocuments.contractId, contractId),
+          eq(stageDocuments.status, "ACTIVE")
+        )
+      )
+      .orderBy(stageDocuments.docNumber);
+
+    return docsResult;
+  } catch (error) {
+    console.error("Error fetching documents:", error);
+    return [];
+  }
+}
+
+// Server Action: Fetch stages by documentId
+export async function fetchStagesByDocumentIdPlant(
+  documentId: number
+): Promise<StageIssueStage[]> {
+  try {
+    const stagesResult = await db
+      .select({
+        id: stageIssueStages.id,
+        documentId: stageIssueStages.documentId,
+        stageName: stageIssueStages.stageName,
+        planDate: stageIssueStages.planDate,
+        actualDate: stageIssueStages.actualDate,
+        stageStatus: stageIssueStages.stageStatus,
+        stageOrder: stageIssueStages.stageOrder,
+        priority: stageIssueStages.priority,
+        assigneeId: stageIssueStages.assigneeId,
+        assigneeName: stageIssueStages.assigneeName,
+        reminderDays: stageIssueStages.reminderDays,
+        description: stageIssueStages.description,
+        notes: stageIssueStages.notes,
+        createdAt: stageIssueStages.createdAt,
+        updatedAt: stageIssueStages.updatedAt,
+      })
+      .from(stageIssueStages)
+      .where(eq(stageIssueStages.documentId, documentId))
+      .orderBy(stageIssueStages.stageOrder, stageIssueStages.stageName);
+
+    return stagesResult;
+  } catch (error) {
+    console.error("Error fetching stages:", error);
+    return [];
+  }
+}
+
+// Server Action: Fetch submissions (revisions) by documentId and stageName
+export async function fetchSubmissionsByStageParams(
+  documentId: number,
+  stageName: string
+): Promise<SubmissionInfo[]> {
+  try {
+    // First, find the stageId
+    const stageResult = await db
+      .select({ id: stageIssueStages.id })
+      .from(stageIssueStages)
+      .where(
+        and(
+          eq(stageIssueStages.documentId, documentId),
+          eq(stageIssueStages.stageName, stageName)
+        )
+      )
+      .limit(1);
+
+    if (!stageResult.length) {
+      return [];
+    }
+
+    const stageId = stageResult[0].id;
+
+    // Then, get submissions for this stage
+    const submissionsResult = await db
+      .select({
+        id: stageSubmissions.id,
+        revisionNumber: stageSubmissions.revisionNumber,
+        revisionCode: stageSubmissions.revisionCode,
+        revisionType: stageSubmissions.revisionType,
+        submissionStatus: stageSubmissions.submissionStatus,
+        submittedBy: stageSubmissions.submittedBy,
+        submittedAt: stageSubmissions.submittedAt,
+        reviewStatus: stageSubmissions.reviewStatus,
+        buyerSystemStatus: stageSubmissions.buyerSystemStatus,
+        syncStatus: stageSubmissions.syncStatus,
+      })
+      .from(stageSubmissions)
+      .where(eq(stageSubmissions.stageId, stageId))
+      .orderBy(stageSubmissions.revisionNumber);
+
+    return submissionsResult;
+  } catch (error) {
+    console.error("Error fetching submissions:", error);
+    return [];
+  }
+}
+
+// View를 활용한 더 효율적인 조회
+export async function fetchDocumentsViewByProjectAndPackage(
+  projectCode: string,
+  packageCode: string
+): Promise<StageDocumentsView[]> {
+  try {
+    // First, find the project by code
+    const projectResult = await db
+      .select({ id: projects.id })
+      .from(projects)
+      .where(eq(projects.code, projectCode))
+      .limit(1);
+
+    if (!projectResult.length) {
+      return [];
+    }
+
+    const projectId = projectResult[0].id;
+
+    // Find contract through contractItems joined with items
+    const contractItemResult = await db
+      .select({ 
+        contractId: contractItems.contractId 
+      })
+      .from(contractItems)
+      .innerJoin(contracts, eq(contractItems.contractId, contracts.id))
+      .innerJoin(items, eq(contractItems.itemId, items.id))
+      .where(
+        and(
+          eq(contracts.projectId, projectId),
+          eq(items.packageCode, packageCode)
+        )
+      )
+      .limit(1);
+
+    if (!contractItemResult.length) {
+      return [];
+    }
+
+    const contractId = contractItemResult[0].contractId;
+
+    // Use the view for enriched data (includes progress, current stage, etc.)
+    const documentsViewResult = await db
+      .select()
+      .from(stageDocumentsView)
+      .where(
+        and(
+          eq(stageDocumentsView.projectId, projectId),
+          eq(stageDocumentsView.contractId, contractId),
+          eq(stageDocumentsView.status, "ACTIVE")
+        )
+      )
+      .orderBy(stageDocumentsView.docNumber);
+
+    return documentsViewResult;
+  } catch (error) {
+    console.error("Error fetching documents view:", error);
+    return [];
+  }
+}
+
+// Server Action: Fetch submission attachments by submissionId
+export async function fetchAttachmentsBySubmissionId(
+  submissionId: number
+): Promise<AttachmentInfo[]> {
+  try {
+    const attachmentsResult = await db
+      .select({
+        id: stageSubmissionAttachments.id,
+        fileName: stageSubmissionAttachments.fileName,
+        originalFileName: stageSubmissionAttachments.originalFileName,
+        fileSize: stageSubmissionAttachments.fileSize,
+        fileType: stageSubmissionAttachments.fileType,
+        storageUrl: stageSubmissionAttachments.storageUrl,
+        syncStatus: stageSubmissionAttachments.syncStatus,
+        buyerSystemStatus: stageSubmissionAttachments.buyerSystemStatus,
+        uploadedAt: stageSubmissionAttachments.uploadedAt,
+      })
+      .from(stageSubmissionAttachments)
+      .where(
+        and(
+          eq(stageSubmissionAttachments.submissionId, submissionId),
+          eq(stageSubmissionAttachments.status, "ACTIVE")
+        )
+      )
+      .orderBy(stageSubmissionAttachments.uploadedAt);
+
+    return attachmentsResult;
+  } catch (error) {
+    console.error("Error fetching attachments:", error);
+    return [];
+  }
+}
+
+// 추가 헬퍼: 특정 제출의 상세 정보 (첨부파일 포함)
+export async function getSubmissionWithAttachments(submissionId: number) {
+  try {
+    const [submission] = await db
+      .select({
+        id: stageSubmissions.id,
+        stageId: stageSubmissions.stageId,
+        documentId: stageSubmissions.documentId,
+        revisionNumber: stageSubmissions.revisionNumber,
+        revisionCode: stageSubmissions.revisionCode,
+        revisionType: stageSubmissions.revisionType,
+        submissionStatus: stageSubmissions.submissionStatus,
+        submittedBy: stageSubmissions.submittedBy,
+        submittedByEmail: stageSubmissions.submittedByEmail,
+        submittedAt: stageSubmissions.submittedAt,
+        reviewedBy: stageSubmissions.reviewedBy,
+        reviewedAt: stageSubmissions.reviewedAt,
+        submissionTitle: stageSubmissions.submissionTitle,
+        submissionDescription: stageSubmissions.submissionDescription,
+        reviewStatus: stageSubmissions.reviewStatus,
+        reviewComments: stageSubmissions.reviewComments,
+        vendorId: stageSubmissions.vendorId,
+        totalFiles: stageSubmissions.totalFiles,
+        buyerSystemStatus: stageSubmissions.buyerSystemStatus,
+        syncStatus: stageSubmissions.syncStatus,
+        createdAt: stageSubmissions.createdAt,
+        updatedAt: stageSubmissions.updatedAt,
+      })
+      .from(stageSubmissions)
+      .where(eq(stageSubmissions.id, submissionId))
+      .limit(1);
+
+    if (!submission) {
+      return null;
+    }
+
+    const attachments = await fetchAttachmentsBySubmissionId(submissionId);
+
+    return {
+      ...submission,
+      attachments,
+    };
+  } catch (error) {
+    console.error("Error getting submission with attachments:", error);
+    return null;
+  }
+}
+
+
+interface CreateSubmissionResult {
+  success: boolean;
+  error?: string;
+  submissionId?: number;
+}
+
+export async function createSubmissionAction(
+  formData: FormData
+): Promise<CreateSubmissionResult> {
+  try {
+    // Extract form data
+    const documentId = formData.get("documentId") as string;
+    const stageName = formData.get("stageName") as string;
+    const revisionCode = formData.get("revisionCode") as string;
+    const customFileName = formData.get("customFileName") as string;
+    const submittedBy = formData.get("submittedBy") as string;
+    const submittedByEmail = formData.get("submittedByEmail") as string | null;
+    const submissionTitle = formData.get("submissionTitle") as string | null;
+    const submissionDescription = formData.get("submissionDescription") as string | null;
+    const vendorId = formData.get("vendorId") as string;
+    const attachment = formData.get("attachment") as File | null;
+
+    // Validate required fields
+    if (!documentId || !stageName || !revisionCode || !submittedBy || !vendorId) {
+      return {
+        success: false,
+        error: "Missing required fields",
+      };
+    }
+
+    const parsedDocumentId = parseInt(documentId, 10);
+    const parsedVendorId = parseInt(vendorId, 10);
+
+    // Validate parsed numbers
+    if (isNaN(parsedDocumentId) || isNaN(parsedVendorId)) {
+      return {
+        success: false,
+        error: "Invalid documentId or vendorId",
+      };
+    }
+
+    // Find the document
+    const [document] = await db
+      .select()
+      .from(stageDocuments)
+      .where(eq(stageDocuments.id, parsedDocumentId))
+      .limit(1);
+
+    if (!document) {
+      return {
+        success: false,
+        error: "Document not found",
+      };
+    }
+
+    // Find the stage
+    const [stage] = await db
+      .select()
+      .from(stageIssueStages)
+      .where(
+        and(
+          eq(stageIssueStages.documentId, parsedDocumentId),
+          eq(stageIssueStages.stageName, stageName)
+        )
+      )
+      .limit(1);
+
+    if (!stage) {
+      return {
+        success: false,
+        error: `Stage "${stageName}" not found for this document`,
+      };
+    }
+
+    const stageId = stage.id;
+
+    // Get the latest revision number for this stage
+    const existingSubmissions = await db
+      .select({
+        revisionNumber: stageSubmissions.revisionNumber,
+      })
+      .from(stageSubmissions)
+      .where(eq(stageSubmissions.stageId, stageId))
+      .orderBy(desc(stageSubmissions.revisionNumber))
+      .limit(1);
+
+    const nextRevisionNumber = existingSubmissions.length > 0
+      ? existingSubmissions[0].revisionNumber + 1
+      : 1;
+
+    // Check if revision code already exists for this stage
+    const [existingRevisionCode] = await db
+      .select()
+      .from(stageSubmissions)
+      .where(
+        and(
+          eq(stageSubmissions.stageId, stageId),
+          eq(stageSubmissions.revisionCode, revisionCode)
+        )
+      )
+      .limit(1);
+
+    if (existingRevisionCode) {
+      return {
+        success: false,
+        error: `Revision code "${revisionCode}" already exists for this stage`,
+      };
+    }
+
+    // Get vendor code from vendors table
+    const [vendor] = await db
+      .select({ vendorCode: vendors.vendorCode })
+      .from(vendors)
+      .where(eq(vendors.id, parsedVendorId))
+      .limit(1);
+
+    const vendorCode = vendor?.vendorCode || parsedVendorId.toString();
+
+    // Determine revision type
+    const revisionType = nextRevisionNumber === 1 ? "INITIAL" : "RESUBMISSION";
+
+    // Create the submission
+    const [newSubmission] = await db
+      .insert(stageSubmissions)
+      .values({
+        stageId,
+        documentId: parsedDocumentId,
+        revisionNumber: nextRevisionNumber,
+        revisionCode,
+        revisionType,
+        submissionStatus: "SUBMITTED",
+        submittedBy,
+        submittedByEmail: submittedByEmail || undefined,
+        submittedAt: new Date(),
+        submissionTitle: submissionTitle || undefined,
+        submissionDescription: submissionDescription || undefined,
+        vendorId: parsedVendorId,
+        vendorCode,
+        totalFiles: attachment ? 1 : 0,
+        totalFileSize: attachment ? attachment.size : 0,
+        syncStatus: "pending",
+        syncVersion: 0,
+        lastModifiedBy: "EVCP",
+        totalFilesToSync: attachment ? 1 : 0,
+        syncedFilesCount: 0,
+        failedFilesCount: 0,
+      })
+      .returning();
+
+    if (!newSubmission) {
+      return {
+        success: false,
+        error: "Failed to create submission",
+      };
+    }
+
+    // Upload attachment if provided
+    if (attachment) {
+      try {
+        // Generate unique filename
+        const fileExtension = customFileName.split(".").pop() || "docx";
+        const timestamp = Date.now();
+        const randomString = crypto.randomBytes(8).toString("hex");
+        const uniqueFileName = `submissions/${parsedDocumentId}/${stageId}/${timestamp}_${randomString}.${fileExtension}`;
+
+        // Calculate checksum
+        const buffer = await attachment.arrayBuffer();
+        const checksum = crypto
+          .createHash("md5")
+          .update(Buffer.from(buffer))
+          .digest("hex");
+
+        // Upload to Vercel Blob (or your storage solution)
+        const blob = await put(uniqueFileName, attachment, {
+          access: "public",
+          contentType: attachment.type || "application/octet-stream",
+        });
+
+        // Create attachment record
+        await db.insert(stageSubmissionAttachments).values({
+          submissionId: newSubmission.id,
+          fileName: uniqueFileName,
+          originalFileName: customFileName,
+          fileType: attachment.type || "application/octet-stream",
+          fileExtension,
+          fileSize: attachment.size,
+          storageType: "S3",
+          storagePath: blob.url,
+          storageUrl: blob.url,
+          mimeType: attachment.type || "application/octet-stream",
+          checksum,
+          documentType: "DOCUMENT",
+          uploadedBy: submittedBy,
+          uploadedAt: new Date(),
+          status: "ACTIVE",
+          syncStatus: "pending",
+          syncVersion: 0,
+          lastModifiedBy: "EVCP",
+          isPublic: false,
+        });
+
+        // Update submission with file info
+        await db
+          .update(stageSubmissions)
+          .set({
+            totalFiles: 1,
+            totalFileSize: attachment.size,
+            totalFilesToSync: 1,
+            updatedAt: new Date(),
+          })
+          .where(eq(stageSubmissions.id, newSubmission.id));
+      } catch (uploadError) {
+        console.error("Error uploading attachment:", uploadError);
+        
+        // Rollback: Delete the submission if file upload fails
+        await db
+          .delete(stageSubmissions)
+          .where(eq(stageSubmissions.id, newSubmission.id));
+        
+        return {
+          success: false,
+          error: uploadError instanceof Error 
+            ? `File upload failed: ${uploadError.message}` 
+            : "File upload failed",
+        };
+      }
+    }
+
+    // Update stage status to SUBMITTED
+    await db
+      .update(stageIssueStages)
+      .set({
+        stageStatus: "SUBMITTED",
+        updatedAt: new Date(),
+      })
+      .where(eq(stageIssueStages.id, stageId));
+
+    // Update document's last modified info
+    await db
+      .update(stageDocuments)
+      .set({
+        lastModifiedBy: "EVCP",
+        syncVersion: document.syncVersion + 1,
+        updatedAt: new Date(),
+      })
+      .where(eq(stageDocuments.id, parsedDocumentId));
+
+    // Revalidate relevant paths
+    revalidatePath(`/projects/${document.projectId}/documents`);
+    revalidatePath(`/vendor/documents`);
+    revalidatePath(`/vendor/submissions`);
+
+    return {
+      success: true,
+      submissionId: newSubmission.id,
+    };
+  } catch (error) {
+    console.error("Error creating submission:", error);
+    return {
+      success: false,
+      error: error instanceof Error ? error.message : "Unknown error occurred",
+    };
+  }
+}
+
+// Additional helper: Update submission status
+export async function updateSubmissionStatus(
+  submissionId: number,
+  status: string,
+  reviewedBy?: string,
+  reviewComments?: string
+): Promise<CreateSubmissionResult> {
+  try {
+    const reviewStatus = 
+      status === "APPROVED" ? "APPROVED" : 
+      status === "REJECTED" ? "REJECTED" : 
+      "PENDING";
+
+    await db
+      .update(stageSubmissions)
+      .set({
+        submissionStatus: status,
+        reviewStatus,
+        reviewComments: reviewComments || undefined,
+        reviewedBy: reviewedBy || undefined,
+        reviewedAt: new Date(),
+        updatedAt: new Date(),
+      })
+      .where(eq(stageSubmissions.id, submissionId));
+
+    // If approved, update stage status
+    if (status === "APPROVED") {
+      const [submission] = await db
+        .select({ stageId: stageSubmissions.stageId })
+        .from(stageSubmissions)
+        .where(eq(stageSubmissions.id, submissionId))
+        .limit(1);
+
+      if (submission) {
+        await db
+          .update(stageIssueStages)
+          .set({
+            stageStatus: "APPROVED",
+            actualDate: new Date().toISOString().split('T')[0],
+            updatedAt: new Date(),
+          })
+          .where(eq(stageIssueStages.id, submission.stageId));
+      }
+    }
+
+    return { success: true };
+  } catch (error) {
+    console.error("Error updating submission status:", error);
+    return { 
+      success: false, 
+      error: error instanceof Error ? error.message : "Failed to update submission status" 
+    };
+  }
+}
+
+// Helper: Delete submission
+export async function deleteSubmissionAction(
+  submissionId: number
+): Promise<CreateSubmissionResult> {
+  try {
+    // Get submission info first
+    const [submission] = await db
+      .select()
+      .from(stageSubmissions)
+      .where(eq(stageSubmissions.id, submissionId))
+      .limit(1);
+
+    if (!submission) {
+      return {
+        success: false,
+        error: "Submission not found",
+      };
+    }
+
+    // Delete attachments from storage
+    const attachments = await db
+      .select()
+      .from(stageSubmissionAttachments)
+      .where(eq(stageSubmissionAttachments.submissionId, submissionId));
+
+    // TODO: Delete files from blob storage
+    // for (const attachment of attachments) {
+    //   await del(attachment.storageUrl);
+    // }
+
+    // Delete submission (cascade will delete attachments)
+    await db
+      .delete(stageSubmissions)
+      .where(eq(stageSubmissions.id, submissionId));
+
+    // Revalidate paths
+    revalidatePath(`/vendor/documents`);
+    revalidatePath(`/vendor/submissions`);
+
+    return { success: true };
+  } catch (error) {
+    console.error("Error deleting submission:", error);
+    return {
+      success: false,
+      error: error instanceof Error ? error.message : "Failed to delete submission",
+    };
+  }
 }
\ No newline at end of file