// app/actions/purchase-requests.ts
"use server";

import db from "@/db/db";
import { purchaseRequestsView, purchaseRequests, purchaseRequestAttachments, rfqsLast, rfqLastAttachments, rfqLastAttachmentRevisions, rfqPrItems, users } from "@/db/schema";
import { eq, and, desc, ilike, or, sql, asc, inArray ,like} from "drizzle-orm";
import { revalidatePath, revalidateTag } from "next/cache";
import { getServerSession } from 'next-auth/next'
import { authOptions } from '@/app/api/auth/[...nextauth]/route'
import { filterColumns } from "@/lib/filter-columns";
import { unstable_cache } from "@/lib/unstable-cache";
import { GetPurchaseRequestsSchema } from "./validations";
import { z } from "zod"

const createRequestSchema = z.object({
    requestTitle: z.string().min(1),
    requestDescription: z.string().optional(),
    projectId: z.number().optional(),
    projectCode: z.string().optional(),
    projectName: z.string().optional(),
    projectCompany: z.string().optional(),
    projectSite: z.string().optional(),
    classNo: z.string().optional(),
    packageNo: z.string().optional(),
    packageName: z.string().optional(),
    majorItemMaterialCategory: z.string().optional(),
    majorItemMaterialDescription: z.string().optional(),
    smCode: z.string().optional(),
    estimatedBudget: z.string().optional(),
    requestedDeliveryDate: z.date().optional(),
    items: z.array(z.object({
        id: z.string(),
        itemCode: z.string(),
        itemName: z.string(),
        specification: z.string(),
        quantity: z.number(),
        unit: z.string(),
        estimatedUnitPrice: z.number().optional(),
        remarks: z.string().optional(),
    })).optional(),
    attachments: z.array(z.object({
        fileName: z.string(),
        originalFileName: z.string(),
        filePath: z.string(),
        fileSize: z.number(),
        fileType: z.string(),
    })).optional(),
})

// 구매요청 생성
export async function createPurchaseRequest(input: z.infer<typeof createRequestSchema>) {
    try {
        const session = await getServerSession(authOptions)
        if (!session?.user?.id) {
            return { error: "인증되지 않은 사용자입니다." }
        }

        const userId = Number(session.user.id)
        const data = createRequestSchema.parse(input)

        const result = await db.transaction(async (tx) => {
            // 요청 번호 생성
            const year = new Date().getFullYear()
            const lastRequest = await tx
                .select()
                .from(purchaseRequests)
                .where(ilike(purchaseRequests.requestCode, `PR-${year}-%`))
                .orderBy(desc(purchaseRequests.requestCode))
                .limit(1)

            const nextNumber = lastRequest.length > 0
                ? parseInt(lastRequest[0].requestCode.split('-')[2]) + 1
                : 1

            const requestCode = `PR-${year}-${String(nextNumber).padStart(5, '0')}`

            // 요청 생성
            const [request] = await tx
                .insert(purchaseRequests)
                .values({
                    requestCode,
                    projectId: data.projectId,
                    projectCode: data.projectCode,
                    projectName: data.projectName,
                    projectCompany: data.projectCompany,
                    projectSite: data.projectSite,
                    classNo: data.classNo,
                    packageNo: data.packageNo,
                    packageName: data.packageName,
                    majorItemMaterialCategory: data.majorItemMaterialCategory,
                    majorItemMaterialDescription: data.majorItemMaterialDescription,
                    smCode: data.smCode,
                    requestTitle: data.requestTitle,
                    requestDescription: data.requestDescription,
                    estimatedBudget: data.estimatedBudget,
                    requestedDeliveryDate: data.requestedDeliveryDate,
                    items: data.items || [],
                    engPicId: userId,
                    engPicName: session.user.name,
                    status: "작성중",
                    createdBy: userId,
                    updatedBy: userId,
                })
                .returning()

            // 첨부파일 저장
            if (data.attachments && data.attachments.length > 0) {
                await tx.insert(purchaseRequestAttachments).values(
                    data.attachments.map(file => ({
                        requestId: request.id,
                        ...file,
                        category: "설계문서",
                        createdBy: userId,
                    }))
                )
            }

            return request
        })

        revalidateTag("purchase-requests")

        return {
            success: true,
            data: result,
            message: `구매요청 ${result.requestCode}이(가) 생성되었습니다.`
        }
    } catch (error) {
        console.error("Create purchase request error:", error)
        return { error: "구매요청 생성 중 오류가 발생했습니다." }
    }
}

// 구매요청 수정
export async function updatePurchaseRequest(
    id: number,
    input: Partial<z.infer<typeof createRequestSchema>>
) {
    try {
        const session = await getServerSession(authOptions)
        if (!session?.user?.id) {
            return { error: "인증되지 않은 사용자입니다." }
        }

        const userId = Number(session.user.id)

        const result = await db.transaction(async (tx) => {
            // 수정 가능한 상태인지 확인
            const [existing] = await tx
                .select()
                .from(purchaseRequests)
                .where(eq(purchaseRequests.id, id))
                .limit(1)

            if (!existing) {
                return { error: "구매요청을 찾을 수 없습니다." }
            }

            if (existing.status !== "작성중") {
                return { error: "작성중 상태의 요청만 수정할 수 있습니다." }
            }

            // 구매요청 업데이트
            const [updated] = await tx
                .update(purchaseRequests)
                .set({
                    projectId: input.projectId,
                    projectCode: input.projectCode,
                    projectName: input.projectName,
                    projectCompany: input.projectCompany,
                    projectSite: input.projectSite,
                    classNo: input.classNo,
                    packageNo: input.packageNo,
                    packageName: input.packageName,
                    majorItemMaterialCategory: input.majorItemMaterialCategory,
                    majorItemMaterialDescription: input.majorItemMaterialDescription,
                    smCode: input.smCode,
                    requestTitle: input.requestTitle,
                    requestDescription: input.requestDescription,
                    estimatedBudget: input.estimatedBudget,
                    requestedDeliveryDate: input.requestedDeliveryDate,
                    items: input.items || [],
                    updatedBy: userId,
                    updatedAt: new Date(),
                })
                .where(eq(purchaseRequests.id, id))
                .returning()

            // 첨부파일 처리
            if (input.attachments !== undefined) {
                // 기존 첨부파일 모두 삭제
                await tx
                    .delete(purchaseRequestAttachments)
                    .where(eq(purchaseRequestAttachments.requestId, id))

                // 새 첨부파일 추가
                if (input.attachments && input.attachments.length > 0) {
                    await tx.insert(purchaseRequestAttachments).values(
                        input.attachments.map(file => ({
                            requestId: id,
                            fileName: file.fileName,
                            originalFileName: file.originalFileName,
                            filePath: file.filePath,
                            fileSize: file.fileSize,
                            fileType: file.fileType,
                            category: "설계문서",
                            createdBy: userId,
                        }))
                    )
                }
            }

            return updated
        })

        revalidateTag("purchase-requests")

        return {
            success: true,
            data: result,
            message: "구매요청이 수정되었습니다."
        }
    } catch (error) {
        console.error("Update purchase request error:", error)
        return { error: "구매요청 수정 중 오류가 발생했습니다." }
    }
}

// 첨부파일 삭제
export async function deletePurchaseRequestAttachment(attachmentId: number) {
    const session = await getServerSession(authOptions)
    if (!session?.user?.id) throw new Error("Unauthorized");
    await db.delete(purchaseRequestAttachments)
        .where(eq(purchaseRequestAttachments.id, attachmentId));

    revalidatePath("/evcp/purchase-requests");
}

// 요청 확정 (작성중 → 요청완료)
export async function confirmPurchaseRequest(requestId: number) {
    const session = await getServerSession(authOptions)
    if (!session?.user?.id) throw new Error("Unauthorized");

    const userId = Number(session.user.id)

    const [request] = await db
        .select()
        .from(purchaseRequests)
        .where(eq(purchaseRequests.id, requestId));

    if (!request) throw new Error("Request not found");
    if (request.status !== "작성중") {
        throw new Error("Only draft requests can be confirmed");
    }

    const [updated] = await db
        .update(purchaseRequests)
        .set({
            status: "요청완료",
            confirmedAt: new Date(),
            confirmedBy: userId,
            updatedBy: userId,
            updatedAt: new Date(),
        })
        .where(eq(purchaseRequests.id, requestId))
        .returning();

    revalidatePath("/evcp/purchase-requests");
    return updated;
}

// 요청 승인 및 RFQ 생성 (첨부파일 이관 포함)
export async function approvePurchaseRequestAndCreateRfq(
    requestId: number,
    purchasePicId?: number
) {
    const session = await getServerSession(authOptions)
    if (!session?.user?.id) throw new Error("Unauthorized");
    const userId = Number(session.user.id)


    return await db.transaction(async (tx) => {
        // 구매 요청 및 첨부파일 조회
        const [request] = await tx
            .select()
            .from(purchaseRequests)
            .where(eq(purchaseRequests.id, requestId));

        const attachments = await tx
            .select()
            .from(purchaseRequestAttachments)
            .where(eq(purchaseRequestAttachments.requestId, requestId));


    const rfqCode = await generateItbRfqCode(purchasePicId);

        const [rfq] = await tx.insert(rfqsLast).values({
            rfqCode,
            projectId: request.projectId,
            itemCode: request.items?.[0]?.itemCode,
            itemName: request.items?.[0]?.itemName,
            packageNo: request.packageNo,
            packageName: request.packageName,
            EngPicName: request.engPicName,
            pic: purchasePicId || null,
            status: "RFQ 생성",
            projectCompany: request.projectCompany,
            projectSite: request.projectSite,
            smCode: request.smCode,
            createdBy: userId,
            updatedBy: userId,
        }).returning();

        // 첨부파일 이관
        for (const [index, attachment] of attachments.entries()) {
            const [rfqAttachment] = await tx.insert(rfqLastAttachments).values({
                attachmentType: "설계",
                serialNo: `ENG-${String(index + 1).padStart(3, '0')}`,
                rfqId: rfq.id,
                description: attachment.description || `설계문서 - ${attachment.originalFileName}`,
                currentRevision: "Rev.0",
                createdBy: userId,
            }).returning();

            const [revision] = await tx.insert(rfqLastAttachmentRevisions).values({
                attachmentId: rfqAttachment.id,
                revisionNo: "Rev.0",
                revisionComment: "구매 요청에서 이관된 설계 문서",
                isLatest: true,
                fileName: attachment.fileName,
                originalFileName: attachment.originalFileName,
                filePath: attachment.filePath,
                fileSize: attachment.fileSize,
                fileType: attachment.fileType,
                createdBy: userId,
            }).returning();

            await tx
                .update(rfqLastAttachments)
                .set({ latestRevisionId: revision.id })
                .where(eq(rfqLastAttachments.id, rfqAttachment.id));
        }

        // 구매 요청 상태 업데이트
        await tx
            .update(purchaseRequests)
            .set({
                status: "RFQ생성완료",
                rfqId: rfq.id,
                rfqCode: rfq.rfqCode,
                rfqCreatedAt: new Date(),
                purchasePicId,
                updatedBy: userId,
                updatedAt: new Date(),
            })
            .where(eq(purchaseRequests.id, requestId));

        return rfq;
    });
}

export async function getAllPurchaseRequests(input: GetPurchaseRequestsSchema) {
    return unstable_cache(
        async () => {
            // 페이징
            const offset = ((input.page ?? 1) - 1) * (input.perPage ?? 10);
            const limit = input.perPage ?? 10;

            // 고급 필터
            const advancedWhere = filterColumns({
                table: purchaseRequestsView,
                filters: input.filters ?? [],
                joinOperator: input.joinOperator ?? "and",
            });

            // 글로벌 검색
            let globalWhere;
            if (input.search) {
                const s = `%${input.search}%`;
                globalWhere = or(
                    sql`${purchaseRequestsView.requestCode} ILIKE ${s}`,
                    sql`${purchaseRequestsView.requestTitle} ILIKE ${s}`,
                    sql`${purchaseRequestsView.projectCode} ILIKE ${s}`,
                    sql`${purchaseRequestsView.projectName} ILIKE ${s}`,
                    sql`${purchaseRequestsView.packageNo} ILIKE ${s}`,
                    sql`${purchaseRequestsView.packageName} ILIKE ${s}`,
                    sql`${purchaseRequestsView.engPicName} ILIKE ${s}`,
                    sql`${purchaseRequestsView.purchasePicName} ILIKE ${s}`,
                    sql`${purchaseRequestsView.majorItemMaterialCategory} ILIKE ${s}`,
                    sql`${purchaseRequestsView.smCode} ILIKE ${s}`
                );
            }

            // 최종 WHERE
            const finalWhere = and(advancedWhere, globalWhere);

            // 정렬
            const orderBy = input.sort?.length
                ? input.sort.map((s) => {
                    const col = (purchaseRequestsView as any)[s.id];
                    return s.desc ? desc(col) : asc(col);
                })
                : [desc(purchaseRequestsView.createdAt)];

            // 메인 SELECT
            const [rows, total] = await db.transaction(async (tx) => {
                const data = await tx
                    .select()
                    .from(purchaseRequestsView)
                    .where(finalWhere)
                    .orderBy(...orderBy)
                    .offset(offset)
                    .limit(limit);

                const [{ count }] = await tx
                    .select({ count: sql<number>`count(*)`.as("count") })
                    .from(purchaseRequestsView)
                    .where(finalWhere);

                return [data, Number(count)];
            });

            const pageCount = Math.ceil(total / limit);
            return { data: rows, pageCount };
        },
        [JSON.stringify(input)],
        {
            revalidate: 60,
            tags: ["purchase-requests"],
        }
    )();
}

// 통계 정보 조회 함수 (선택사항)
export async function getPurchaseRequestStats() {
    return unstable_cache(
        async () => {
            const stats = await db
                .select({
                    total: sql<number>`count(*)`.as("total"),
                    draft: sql<number>`count(*) filter (where status = '작성중')`.as("draft"),
                    rfqCreated: sql<number>`count(*) filter (where status = 'RFQ생성완료')`.as("rfqCreated"),
                })
                .from(purchaseRequestsView);

            return stats[0];
        },
        [],
        {
            revalidate: 60,
            tags: ["purchase-request-stats"],
        }
    )();
}


// 스키마 정의
const deleteSchema = z.object({
    ids: z.array(z.number()),
})


// 구매요청 삭제 (단일 또는 복수)
export async function deletePurchaseRequests(input: z.infer<typeof deleteSchema>) {
    try {
        const session = await getServerSession(authOptions)
        if (!session?.user?.id) {
            return { error: "인증되지 않은 사용자입니다." }
        }

        const { ids } = deleteSchema.parse(input)

        if (ids.length === 0) {
            return { error: "삭제할 요청을 선택해주세요." }
        }

        // 삭제 가능한 상태인지 확인 (작성중 상태만 삭제 가능)
        const requests = await db
            .select({
                id: purchaseRequests.id,
                status: purchaseRequests.status,
                requestCode: purchaseRequests.requestCode,
            })
            .from(purchaseRequests)
            .where(inArray(purchaseRequests.id, ids))

        const nonDeletable = requests.filter(req => req.status !== "작성중")

        if (nonDeletable.length > 0) {
            const codes = nonDeletable.map(req => req.requestCode).join(", ")
            return {
                error: `다음 요청은 삭제할 수 없습니다: ${codes}. 작성중 상태의 요청만 삭제 가능합니다.`
            }
        }

        // 트랜잭션으로 삭제 처리
        await db.transaction(async (tx) => {
            // 1. 첨부파일 먼저 삭제
            await tx
                .delete(purchaseRequestAttachments)
                .where(inArray(purchaseRequestAttachments.requestId, ids))

            // 2. 구매요청 삭제
            await tx
                .delete(purchaseRequests)
                .where(inArray(purchaseRequests.id, ids))
        })

        // 캐시 무효화
        revalidateTag("purchase-requests")

        return {
            success: true,
            message: ids.length === 1
                ? "구매요청이 삭제되었습니다."
                : `${ids.length}개의 구매요청이 삭제되었습니다.`
        }
    } catch (error) {
        console.error("Delete purchase requests error:", error)
        return { error: "구매요청 삭제 중 오류가 발생했습니다." }
    }
}

export async function getPurchaseRequestAttachments(requestId: number) {
    try {
        const attachments = await db
            .select({
                id: purchaseRequestAttachments.id,
                fileName: purchaseRequestAttachments.fileName,
                originalFileName: purchaseRequestAttachments.originalFileName,
                filePath: purchaseRequestAttachments.filePath,
                fileSize: purchaseRequestAttachments.fileSize,
                fileType: purchaseRequestAttachments.fileType,
                category: purchaseRequestAttachments.category,
                description: purchaseRequestAttachments.description,
                createdBy: purchaseRequestAttachments.createdBy,
                createdAt: purchaseRequestAttachments.createdAt,
            })
            .from(purchaseRequestAttachments)
            .where(eq(purchaseRequestAttachments.requestId, requestId))
            .orderBy(desc(purchaseRequestAttachments.createdAt))

        return {
            success: true,
            data: attachments
        }
    } catch (error) {
        console.error("Get attachments error:", error)
        return { 
            success: false,
            error: "첨부파일 조회 중 오류가 발생했습니다.",
            data: [] 
        }
    }
}


export async function generateItbRfqCode(purchasePicId?: number): Promise<string> {
    try {
      let userCode = "???";
  
      // purchasePicId가 있으면 users 테이블에서 userCode 조회
      if (purchasePicId) {
        const [user] = await db
          .select({ userCode: users.userCode })
          .from(users)
          .where(eq(users.id, purchasePicId))
          .limit(1);
  
        if (user?.userCode) {
          userCode = user.userCode;
        }
      }
  
      // 동일한 userCode로 시작하는 마지막 RFQ 조회
      const lastRfq = await db
        .select({ rfqCode: rfqsLast.rfqCode })
        .from(rfqsLast)
        .where(like(rfqsLast.rfqCode, `I${userCode}%`))
        .orderBy(desc(rfqsLast.createdAt))
        .limit(1);
  
      let nextNumber = 1;
  
      if (lastRfq.length > 0 && lastRfq[0].rfqCode) {
        const rfqCode = lastRfq[0].rfqCode;
        const serialNumber = rfqCode.slice(-5); // 마지막 5자리
  
        if (/^\d{5}$/.test(serialNumber)) {
          nextNumber = parseInt(serialNumber) + 1;
        }
      }
  
      const paddedNumber = String(nextNumber).padStart(5, "0");
  
      return `I${userCode}${paddedNumber}`;
    } catch (error) {
      console.error("Error generating ITB RFQ code:", error);
      const fallback = Date.now().toString().slice(-5);
      return `I???${fallback}`;
    }
  }
  

  // lib/purchase-requests/service.ts에 추가

// 여러 구매 요청 승인 및 RFQ 생성
export async function approvePurchaseRequestsAndCreateRfqs(
    requestIds: number[],
    purchasePicId?: number
  ) {
    try {
      const session = await getServerSession(authOptions)
      if (!session?.user?.id) throw new Error("Unauthorized");
      const userId = Number(session.user.id)
  
      const results = []
  
      for (const requestId of requestIds) {
        try {
          const result = await db.transaction(async (tx) => {
            // 구매 요청 조회
            const [request] = await tx
              .select()
              .from(purchaseRequests)
              .where(eq(purchaseRequests.id, requestId))
  
            if (!request) {
              throw new Error(`구매 요청 ${requestId}를 찾을 수 없습니다.`)
            }
  
            if (request.status === "RFQ생성완료") {
              return { skipped: true, requestId, message: "이미 RFQ가 생성되었습니다." }
            }
  
            const attachments = await tx
              .select()
              .from(purchaseRequestAttachments)
              .where(eq(purchaseRequestAttachments.requestId, requestId))
  
            const rfqCode = await generateItbRfqCode(purchasePicId)
  
            const [rfq] = await tx
              .insert(rfqsLast)
              .values({
                rfqCode,
                projectId: request.projectId,
                itemCode: request.items?.[0]?.itemCode,
                itemName: request.items?.[0]?.itemName,
                packageNo: request.packageNo,
                packageName: request.packageName,
                EngPicName: request.engPicName,
                pic: purchasePicId || null,
                status: "RFQ 생성",
                projectCompany: request.projectCompany,
                projectSite: request.projectSite,
                smCode: request.smCode,
                createdBy: userId,
                updatedBy: userId,
              })
              .returning()
  
            // 첨부파일 이관
            for (const [index, attachment] of attachments.entries()) {
              const [rfqAttachment] = await tx
                .insert(rfqLastAttachments)
                .values({
                  attachmentType: "설계",
                  serialNo: `ENG-${String(index + 1).padStart(3, "0")}`,
                  rfqId: rfq.id,
                  description:
                    attachment.description ||
                    `설계문서 - ${attachment.originalFileName}`,
                  currentRevision: "Rev.0",
                  createdBy: userId,
                })
                .returning()
  
              const [revision] = await tx
                .insert(rfqLastAttachmentRevisions)
                .values({
                  attachmentId: rfqAttachment.id,
                  revisionNo: "Rev.0",
                  revisionComment: "구매 요청에서 이관된 설계 문서",
                  isLatest: true,
                  fileName: attachment.fileName,
                  originalFileName: attachment.originalFileName,
                  filePath: attachment.filePath,
                  fileSize: attachment.fileSize,
                  fileType: attachment.fileType,
                  createdBy: userId,
                })
                .returning()
  
              await tx
                .update(rfqLastAttachments)
                .set({ latestRevisionId: revision.id })
                .where(eq(rfqLastAttachments.id, rfqAttachment.id))
            }
  
            // 품목 이관
            if (request.items && request.items.length > 0) {
                console.log("🚀 품목 이관 시작:", {
                    requestId,
                    itemsCount: request.items.length,
                    items: request.items
                });
                
                const prItemsData = request.items.map((item, index) => ({
                    rfqsLastId: rfq.id,
                    rfqItem: `${index + 1}`.padStart(3, '0'),
                    prItem: `${index + 1}`.padStart(3, '0'),
                    prNo: rfqCode,
                    materialCode: item.itemCode,
                    materialDescription: item.itemName,
                    quantity: item.quantity,
                    uom: item.unit,
                    majorYn: index === 0,
                    remark: item.remarks || null,
                }));
                
                console.log("🔍 삽입할 데이터:", prItemsData);
                
                const insertedItems = await tx.insert(rfqPrItems).values(prItemsData).returning();
                console.log("✅ 품목 이관 완료:", insertedItems);
            } else {
                console.log("❌ 품목이 없음:", {
                    requestId,
                    hasItems: !!request.items,
                    itemsLength: request.items?.length || 0
                });
            }

            // 구매 요청 상태 업데이트
            await tx
              .update(purchaseRequests)
              .set({
                status: "RFQ생성완료",
                rfqId: rfq.id,
                rfqCode: rfq.rfqCode,
                rfqCreatedAt: new Date(),
                purchasePicId,
                updatedBy: userId,
                updatedAt: new Date(),
              })
              .where(eq(purchaseRequests.id, requestId))
  
            return { success: true, rfq, requestId }
          })
  
          results.push(result)
        } catch (err: any) {
          console.error(`구매 요청 ${requestId} 처리 중 오류:`, err)
          results.push({
            success: false,
            requestId,
            error: err.message || "알 수 없는 오류 발생",
          })
        }
      }
  
      // 캐시 무효화
      revalidateTag("purchase-requests")
      revalidateTag( "purchase-request-stats")
    
      revalidateTag("rfqs")
  
      return results
    } catch (err: any) {
      console.error("approvePurchaseRequestsAndCreateRfqs 실행 오류:", err)
      throw new Error(err.message || "구매 요청 처리 중 오류가 발생했습니다.")
    }
  }