"use server"

import { eq, SQL } from "drizzle-orm"
import db from "@/db/db"
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 path from "path";
import fs from "fs/promises";
import { v4 as uuidv4 } from "uuid"
import { contractItems } from "@/db/schema"

/**
 * 특정 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
  
  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 status = 'submitted';
    if (uploaderType === 'client') status = 'reviewed';
    if (uploaderType === 'shi') status = 'official';
    
    if (!revisionRecord.length) {
      // Revision이 없으면 새로 생성
      const [newRevision] = await tx
        .insert(revisions)
        .values({
          issueStageId,
          revision,
          uploaderType,
          uploaderName: uploaderName || undefined,
          comment: comment || undefined,
          status,
          updatedAt: new Date(),
        })
        .returning()
      
      revisionId = newRevision.id
    } else {
      // 이미 존재하는 경우, 업로더 타입이 다르면 업데이트
      if (revisionRecord[0].uploaderType !== uploaderType) {
        await tx
          .update(revisions)
          .set({
            uploaderType,
            uploaderName: uploaderName || undefined,
            comment: comment || undefined,
            status,
            updatedAt: new Date(),
          })
          .where(eq(revisions.id, revisionRecord[0].id))
      }
      revisionId = revisionRecord[0].id
    }
    
    // (3) 파일 처리
    const file = formData.get("attachment") as File | null
    let attachmentRecord: typeof documentAttachments.$inferSelect | null = null;
    
    if (file && file.size > 0) {
      const originalName = customFileName
      const ext = path.extname(originalName)
      const uniqueName = uuidv4() + ext
      const baseDir = path.join(process.cwd(), "public", "documents")
      const savePath = path.join(baseDir, uniqueName)
      
      const arrayBuffer = await file.arrayBuffer()
      const buffer = Buffer.from(arrayBuffer)
      await fs.writeFile(savePath, buffer)
      
      // 파일 정보를 documentAttachments 테이블에 저장
      const result = await tx
        .insert(documentAttachments)
        .values({
          revisionId,
          fileName: originalName,
          filePath: "/documents/" + uniqueName,
          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 [];
  }
}