1 files changed, 2 insertions, 704 deletions

diff --git a/lib/vendor-document/service.ts b/lib/vendor-document/service.ts
index 48e3fa3f..bf2b0b7a 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 { stageSubmissions, stageDocuments, stageIssueStages,documentAttachments, documents, issueStages, revisions, stageDocumentsView,vendorDocumentsView ,stageSubmissionAttachments, StageIssueStage, StageDocumentsView, StageDocument,} from "@/db/schema/vendorDocu"
+import { documentAttachments, documents, issueStages, revisions, vendorDocumentsView } 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, projects, items,contracts } from "@/db/schema"
+import { contractItems } from "@/db/schema"
 import { saveFile } from "../file-stroage"
 import path from "path"
 
@@ -494,706 +494,4 @@ 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