path: root/lib/vendor-document/service.ts

"use server"

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 { 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 { saveFile } from "../file-stroage"
import path from "path"

/**
 * 특정 vendorId에 속한 문서 목록 조회
 */
export async function getVendorDocumentLists(input: GetVendorDcoumentsSchema, id: number) {
    return unstable_cache(
        async () => {
            try {
                const offset = (input.page - 1) * input.perPage;

                // advancedTable 모드면 filterColumns()로 where 절 구성
                const advancedWhere = filterColumns({
                    table: vendorDocumentsView,
                    filters: input.filters,
                    joinOperator: input.joinOperator,
                });

                let globalWhere
                if (input.search) {
                    const s = `%${input.search}%`
                    globalWhere = or(ilike(vendorDocumentsView.title, s), ilike(vendorDocumentsView.docNumber, s)
                    )
                    // 필요시 여러 칼럼 OR조건 (status, priority, etc)
                }

                const finalWhere = and(advancedWhere, globalWhere, eq(vendorDocumentsView.contractId, id));
                const orderBy =
                    input.sort.length > 0
                        ? input.sort.map((item) =>
                            item.desc ? desc(vendorDocumentsView[item.id]) : asc(vendorDocumentsView[item.id])
                        )
                        : [asc(vendorDocumentsView.createdAt)];

                // 트랜잭션 내부에서 Repository 호출
                const { data, total } = await db.transaction(async (tx) => {
                    const data = await selectVendorDocuments(tx, {
                        where: finalWhere,
                        orderBy,
                        offset,
                        limit: input.perPage,
                    });
                    const total = await countVendorDocuments(tx, finalWhere);
                    return { data, total };
                });

                const pageCount = Math.ceil(total / input.perPage);


                return { data, pageCount };
            } catch (err) {
                // 에러 발생 시 디폴트
                return { data: [], pageCount: 0 };
            }
        },
        [JSON.stringify(input), String(id)], // Include id in the cache key
        {
            revalidate: 3600,
            tags: [`vendor-docuemnt-${id}`],
        }
    )();
}


// getDocumentVersionsByDocId 함수 수정 - 업로더 타입으로 필터링 추가
export async function getDocumentVersionsByDocId(
  docId: number,
) {
  // 모든 조건을 배열로 관리
  const conditions: SQL<unknown>[] = [eq(issueStages.documentId, docId)];
  

  
  // 쿼리 실행
  const rows = await db
    .select({
      // stage 정보
      stageId: issueStages.id,
      stageName: issueStages.stageName,
      planDate: issueStages.planDate,
      actualDate: issueStages.actualDate,
      
      // revision 정보
      revisionId: revisions.id,
      revision: revisions.revision,
      uploaderType: revisions.uploaderType,
      uploaderName: revisions.uploaderName,
      comment: revisions.comment,
      status: revisions.status,
      approvedDate: revisions.approvedDate,
      
      // attachment 정보
      attachmentId: documentAttachments.id,
      fileName: documentAttachments.fileName,
      filePath: documentAttachments.filePath,
      fileType: documentAttachments.fileType,
      DocumentSubmitDate: revisions.createdAt,
    })
    .from(issueStages)
    .leftJoin(revisions, eq(issueStages.id, revisions.issueStageId))
    .leftJoin(documentAttachments, eq(revisions.id, documentAttachments.revisionId))
    .where(and(...conditions))
    .orderBy(issueStages.id, revisions.id, documentAttachments.id);

  // 결과를 처리하여 프론트엔드 형식으로 변환
  // 스테이지+리비전별로 그룹화
  const stageRevMap = new Map();
  // 리비전이 있는 스테이지 ID 추적
  const stagesWithRevisions = new Set();

  for (const row of rows) {
    const stageId = row.stageId;
    

    // 리비전이 있는 경우 처리
    if (row.revisionId) {
      // 리비전이 있는 스테이지 추적
      stagesWithRevisions.add(stageId);
      
      const key = `${stageId}-${row.revisionId}`;
      
      if (!stageRevMap.has(key)) {
        stageRevMap.set(key, {
          id: row.revisionId,
          stage: row.stageName,
          revision: row.revision,
          uploaderType: row.uploaderType,
          uploaderName: row.uploaderName || null,
          comment: row.comment || null,
          status: row.status || null,
          planDate: row.planDate,
          actualDate: row.actualDate,
          approvedDate: row.approvedDate,
          DocumentSubmitDate: row.DocumentSubmitDate,
          attachments: []
        });
      }
      
      // attachmentId가 있는 경우에만 첨부파일 추가
      if (row.attachmentId) {
        stageRevMap.get(key).attachments.push({
          id: row.attachmentId,
          fileName: row.fileName,
          filePath: row.filePath,
          fileType: row.fileType
        });
      }
    }
  }


  // 최종 결과 생성
  const result = [
    ...stageRevMap.values()
  ];

  // 스테이지 이름으로 정렬하고, 같은 스테이지 내에서는 리비전이 없는 항목이 먼저 오도록 정렬
  result.sort((a, b) => {
    if (a.stage !== b.stage) {
      return a.stage.localeCompare(b.stage);
    }
    
    // 같은 스테이지 내에서는 리비전이 없는 항목이 먼저 오도록
    if (a.revision === null) return -1;
    if (b.revision === null) return 1;
    
    // 두 항목 모두 리비전이 있는 경우 리비전 번호로 정렬
    return a.revision - b.revision;
  });

  return result;
}
// createRevisionAction 함수 수정 - 확장된 업로더 타입 지원
export async function createRevisionAction(formData: FormData) {

  const stage = formData.get("stage") as string | null
  const revision = formData.get("revision") as string | null
  const docIdStr = formData.get("documentId") as string
  const docId = parseInt(docIdStr, 10)
  const customFileName = formData.get("customFileName") as string;

  // 업로더 타입 추가 (기본값: "vendor")
  const uploaderType = formData.get("uploaderType") as string || "vendor"
  const uploaderName = formData.get("uploaderName") as string | null
  const comment = formData.get("comment") as string | null
  
  // 추가 필드들 (옵션)
  const uploaderId = formData.get("uploaderId") as string | null
  const reviewerId = formData.get("reviewerId") as string | null
  const reviewerName = formData.get("reviewerName") as string | null
  const reviewComments = formData.get("reviewComments") as string | null
  
  if (!docId || Number.isNaN(docId)) {
    throw new Error("Invalid or missing documentId")
  }
  if (!stage || !revision) {
    throw new Error("Missing stage/revision")
  }
  
  // 업로더 타입 검증
  if (!['vendor', 'client', 'shi'].includes(uploaderType)) {
    throw new Error(`Invalid uploaderType: ${uploaderType}. Must be one of: vendor, client, shi`);
  }
  
  // 트랜잭션 시작
  return await db.transaction(async (tx) => {
    // (1) issueStageId 찾기 (stageName + documentId)
    let issueStageId: number;
    const stageRecord = await tx
      .select()
      .from(issueStages)
      .where(and(eq(issueStages.stageName, stage), eq(issueStages.documentId, docId)))
      .limit(1)
    
    if (!stageRecord.length) {
      // Stage가 없으면 새로 생성
      const [newStage] = await tx
        .insert(issueStages)
        .values({
          documentId: docId,
          stageName: stage,
          updatedAt: new Date(),
        })
        .returning()
      
      issueStageId = newStage.id
    } else {
      issueStageId = stageRecord[0].id
    }
    
    // (2) Revision 찾기 또는 생성 (issueStageId + revision 조합)
    let revisionId: number;
    const revisionRecord = await tx
      .select()
      .from(revisions)
      .where(and(eq(revisions.issueStageId, issueStageId), eq(revisions.revision, revision)))
      .limit(1)
    
    // 기본 상태값 설정 (새로운 상태값 사용)
    let revisionStatus = 'SUBMITTED';
    if (uploaderType === 'client') revisionStatus = 'UNDER_REVIEW';
    if (uploaderType === 'shi') revisionStatus = 'APPROVED';
    
    // 현재 날짜
    const now = new Date();
    const today = now.toISOString().split('T')[0]; // YYYY-MM-DD 형식
    
    if (!revisionRecord.length) {
      // Revision이 없으면 새로 생성
      const [newRevision] = await tx
        .insert(revisions)
        .values({
          issueStageId,
          revision,
          uploaderType,
          uploaderId: uploaderId ? parseInt(uploaderId, 10) : undefined,
          uploaderName: uploaderName || undefined,
          revisionStatus,
          uploadedAt: today,
          // 상태에 따른 날짜 설정
          reviewStartDate: revisionStatus === 'UNDER_REVIEW' ? today : undefined,
          approvedDate: revisionStatus === 'APPROVED' ? today : undefined,
          // 검토자 정보
          reviewerId: reviewerId ? parseInt(reviewerId, 10) : undefined,
          reviewerName: reviewerName || undefined,
          reviewComments: reviewComments || undefined,
          comment: comment || undefined,
          updatedAt: now,
        })
        .returning()
      
      revisionId = newRevision.id
    } else {
      // 이미 존재하는 경우, 업로더 타입이 다르거나 다른 정보가 변경되면 업데이트
      const existingRevision = revisionRecord[0];
      const needsUpdate = 
        existingRevision.uploaderType !== uploaderType ||
        existingRevision.uploaderName !== uploaderName ||
        existingRevision.comment !== comment;
      
      if (needsUpdate) {
        // 상태 변경에 따른 날짜 업데이트 로직
        const updateValues: any = {
          uploaderType,
          uploaderId: uploaderId ? parseInt(uploaderId, 10) : undefined,
          uploaderName: uploaderName || undefined,
          revisionStatus,
          reviewerId: reviewerId ? parseInt(reviewerId, 10) : undefined,
          reviewerName: reviewerName || undefined,
          reviewComments: reviewComments || undefined,
          comment: comment || undefined,
          updatedAt: now,
        };
        
        // 상태가 변경된 경우 해당 날짜 필드 업데이트
        if (existingRevision.revisionStatus !== revisionStatus) {
          switch (revisionStatus) {
            case 'UNDER_REVIEW':
              if (!existingRevision.reviewStartDate) {
                updateValues.reviewStartDate = today;
              }
              break;
            case 'APPROVED':
              if (!existingRevision.approvedDate) {
                updateValues.approvedDate = today;
              }
              break;
            case 'REJECTED':
              if (!existingRevision.rejectedDate) {
                updateValues.rejectedDate = today;
              }
              break;
          }
        }
        
        await tx
          .update(revisions)
          .set(updateValues)
          .where(eq(revisions.id, existingRevision.id))
      }
      revisionId = existingRevision.id
    }
    
    // (3) 파일 처리
    const file = formData.get("attachment") as File | null
    let attachmentRecord: typeof documentAttachments.$inferSelect | null = null;
    
    if (file && file.size > 0) {

      const ext = path.extname(customFileName)
      const saveResult = await saveFile({file,directory:`documents`, originalName:customFileName})
      
      // 파일 정보를 documentAttachments 테이블에 저장
      const result = await tx
        .insert(documentAttachments)
        .values({
          revisionId,
          fileName: customFileName,
          filePath: saveResult.publicPath!,
          fileSize: file.size,
          fileType: ext.replace('.', '').toLowerCase(),
          updatedAt: new Date(),
        })
        .returning()
      
      // 첫 번째 결과만 할당
      attachmentRecord = result[0]
    }
    
    // (4) Documents 테이블의 updatedAt 갱신 (docId가 documents.id)
    await tx
      .update(documents)
      .set({ updatedAt: new Date() })
      .where(eq(documents.id, docId))
    
    return attachmentRecord
  })
}


export async function getStageNamesByDocumentId(documentId: number) {
  try {
    if (!documentId || Number.isNaN(documentId)) {
      throw new Error("Invalid document ID");
    }

    const stageRecords = await db
      .select({ stageName: issueStages.stageName })
      .from(issueStages)
      .where(eq(issueStages.documentId, documentId))
      .orderBy(issueStages.stageName);
    
    // stageName 배열로 변환
    return stageRecords.map(record => record.stageName);
  } catch (error) {
    console.error("Error fetching stage names:", error);
    return []; // 오류 발생시 빈 배열 반환
  }
}


// Define the return types
export interface Document {
  id: number;
  docNumber: string;
  title: string;
}

export interface IssueStage {
  id: number;
  stageName: string;
}

export interface Revision {
  revision: string;
}

// Server Action: Fetch documents by packageId (contractItems.id)
export async function fetchDocumentsByPackageId(packageId: number): Promise<Document[]> {
  try {
    // First, find the contractId from contractItems where id = packageId
    const contractItemResult = await db.select({ contractId: contractItems.contractId })
      .from(contractItems)
      .where(eq(contractItems.id, packageId))
      .limit(1);

    if (!contractItemResult.length) {
      return [];
    }

    const contractId = contractItemResult[0].contractId;

    // Then, get documents associated with this contractId
    const docsResult = await db.select({
      id: documents.id,
      docNumber: documents.docNumber,
      title: documents.title,
    })
      .from(documents)
      .where(eq(documents.contractId, contractId))
      .orderBy(documents.docNumber);

    return docsResult;
  } catch (error) {
    console.error("Error fetching documents:", error);
    return [];
  }
}

// Server Action: Fetch stages by documentId
export async function fetchStagesByDocumentId(documentId: number): Promise<IssueStage[]> {
  try {
    const stagesResult = await db.select({
      id: issueStages.id,
      stageName: issueStages.stageName,
    })
      .from(issueStages)
      .where(eq(issueStages.documentId, documentId))
      .orderBy(issueStages.stageName);

    return stagesResult;
  } catch (error) {
    console.error("Error fetching stages:", error);
    return [];
  }
}

// Server Action: Fetch revisions by documentId and stageName
export async function fetchRevisionsByStageParams(
  documentId: number, 
  stageName: string
): Promise<Revision[]> {
  try {
    // First, find the issueStageId
    const stageResult = await db.select({ id: issueStages.id })
      .from(issueStages)
      .where(
        and(
          eq(issueStages.documentId, documentId),
          eq(issueStages.stageName, stageName)
        )
      )
      .limit(1);

    if (!stageResult.length) {
      return [];
    }

    const issueStageId = stageResult[0].id;

    // Then, get revisions for this stage
    const revisionsResult = await db.select({
      revision: revisions.revision,
    })
      .from(revisions)
      .where(eq(revisions.issueStageId, issueStageId))
      .orderBy(revisions.revision);

    return revisionsResult;
  } catch (error) {
    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",
    };
  }
}