path: root/lib/pcr/service.ts

"use server"

import { unstable_noStore, revalidatePath } from "next/cache";
import db from "@/db/db";
import {
  pcrPo,
  pcrPr,
  vendors,
  pcrPrAttachment
} from "@/db/schema";
import { and, desc, eq, ilike, or, sql, inArray, count, asc } from "drizzle-orm";
import { getErrorMessage } from "@/lib/handle-error";
import {
  type PcrPoFilters,
} from "./types";
import { getServerSession } from "next-auth/next";
import { authOptions } from "@/app/api/auth/[...nextauth]/route";

// 정렬 타입 정의
// 의도적으로 any 사용 - drizzle ORM의 orderBy 타입이 복잡함
// eslint-disable-next-line @typescript-eslint/no-explicit-any
type OrderByType = any;

/**
 * PCR_PO 데이터를 조회하는 기본 함수 (JOIN 포함)
 */
function selectPcrPoWithJoin() {
  return db
    .select({
      // PCR_PO 필드들
      id: pcrPo.id,
      pcrApprovalStatus: pcrPo.pcrApprovalStatus,
      changeType: pcrPo.changeType,
      details: pcrPo.details,
      project: pcrPo.project,
      pcrRequestDate: pcrPo.pcrRequestDate,
      poContractNumber: pcrPo.poContractNumber,
      revItemNumber: pcrPo.revItemNumber,
      purchaseContractManager: pcrPo.purchaseContractManager,
      pcrCreator: pcrPo.pcrCreator,
      poContractAmountBefore: pcrPo.poContractAmountBefore,
      poContractAmountAfter: pcrPo.poContractAmountAfter,
      contractCurrency: pcrPo.contractCurrency,
      pcrReason: pcrPo.pcrReason,
      detailsReason: pcrPo.detailsReason,
      rejectionReason: pcrPo.rejectionReason,
      pcrResponseDate: pcrPo.pcrResponseDate,
      vendorId: pcrPo.vendorId,
      createdBy: pcrPo.createdBy,
      updatedBy: pcrPo.updatedBy,
      createdAt: pcrPo.createdAt,
      updatedAt: pcrPo.updatedAt,

      // JOIN된 필드들
      vendorName: vendors.vendorName,
    })
    .from(pcrPo)
    .leftJoin(vendors, eq(pcrPo.vendorId, vendors.id))
    .$dynamic();
}

/**
 * PCR_PR 데이터를 조회하는 기본 함수
 */
function selectPcrPrWithJoin() {
  return db
    .select({
      // PCR_PR 필드들
      id: pcrPr.id,
      materialNumber: pcrPr.materialNumber,
      materialDetails: pcrPr.materialDetails,
      quantityBefore: pcrPr.quantityBefore,
      quantityAfter: pcrPr.quantityAfter,
      weightBefore: pcrPr.weightBefore,
      weightAfter: pcrPr.weightAfter,
      subcontractorWeightBefore: pcrPr.subcontractorWeightBefore,
      subcontractorWeightAfter: pcrPr.subcontractorWeightAfter,
      supplierWeightBefore: pcrPr.supplierWeightBefore,
      supplierWeightAfter: pcrPr.supplierWeightAfter,
      specDrawingBefore: pcrPr.specDrawingBefore,
      specDrawingAfter: pcrPr.specDrawingAfter,
      initialPoContractDate: pcrPr.initialPoContractDate,
      specChangeDate: pcrPr.specChangeDate,
      poContractModifiedDate: pcrPr.poContractModifiedDate,
      confirmationDate: pcrPr.confirmationDate,
      designManager: pcrPr.designManager,
      poContractNumber: pcrPr.poContractNumber,
      createdBy: pcrPr.createdBy,
      updatedBy: pcrPr.updatedBy,
      createdAt: pcrPr.createdAt,
      updatedAt: pcrPr.updatedAt,
    })
    .from(pcrPr)
    .$dynamic();
}

/**
 * PCR_PO 데이터를 조회하고 카운트하는 함수들
 */
const selectPcrPoWithJoinDynamic = selectPcrPoWithJoin();
const countPcrPoWithJoin = () =>
  db
    .select({ count: count() })
    .from(pcrPo)
    .leftJoin(vendors, eq(pcrPo.vendorId, vendors.id));

/**
 * PCR_PR 데이터를 조회하고 카운트하는 함수들
 */
const selectPcrPrWithJoinDynamic = selectPcrPrWithJoin();

/**
 * PCR_PO 목록 조회 (EvcP 페이지용 - 모든 데이터 조회)
 */
export async function getPcrPoList(input: {
  page?: number;
  perPage?: number;
  sort?: OrderByType;
  filters?: PcrPoFilters;
  search?: string;
}) {
  unstable_noStore();

  try {
    const { page = 1, perPage = 10, sort, filters, search } = input;

    const offset = (page - 1) * perPage;

    // 필터링 조건 구성
    const whereConditions: any[] = [];

    if (filters) {
      if (filters.pcrApprovalStatus) {
        whereConditions.push(eq(pcrPo.pcrApprovalStatus, filters.pcrApprovalStatus));
      }
      if (filters.changeType) {
        whereConditions.push(eq(pcrPo.changeType, filters.changeType));
      }
      if (filters.project) {
        whereConditions.push(ilike(pcrPo.project, `%${filters.project}%`));
      }
      if (filters.poContractNumber) {
        whereConditions.push(ilike(pcrPo.poContractNumber, `%${filters.poContractNumber}%`));
      }
      if (filters.vendorId) {
        whereConditions.push(eq(pcrPo.vendorId, filters.vendorId));
      }
      if (filters.startDate) {
        whereConditions.push(sql`${pcrPo.pcrRequestDate} >= ${filters.startDate}`);
      }
      if (filters.endDate) {
        whereConditions.push(sql`${pcrPo.pcrRequestDate} <= ${filters.endDate}`);
      }
    }

    // 검색 조건
    if (search) {
      whereConditions.push(
        or(
          ilike(pcrPo.poContractNumber, `%${search}%`),
          ilike(pcrPo.project, `%${search}%`),
          ilike(pcrPo.pcrCreator, `%${search}%`),
          ilike(vendors.vendorName, `%${search}%`)
        )
      );
    }

    const whereClause = whereConditions.length > 0 ? and(...whereConditions) : undefined;

    // 데이터 조회
    const data = await selectPcrPoWithJoinDynamic
      .where(whereClause)
      .orderBy(sort ? sort : desc(pcrPo.createdAt))
      .limit(perPage)
      .offset(offset);

    // 전체 카운트
    const totalCount = await countPcrPoWithJoin()
      .where(whereClause)
      .then((result) => result[0]?.count ?? 0);

    return {
      data,
      totalCount,
      pageCount: Math.ceil(totalCount / perPage),
    };
  } catch (error) {
    console.error("PCR_PO 목록 조회 오류:", error);
    throw new Error("PCR_PO 데이터를 조회하는데 실패했습니다.");
  }
}

/**
 * PCR_PO 목록 조회 (Partners 페이지용 - 현재 사용자의 vendorId에 해당하는 데이터만 조회)
 */
export async function getPcrPoListForPartners(input: {
  page?: number;
  perPage?: number;
  sort?: OrderByType;
  filters?: Omit<PcrPoFilters, 'vendorId'>;
  search?: string;
  vendorId: number;
}) {
  unstable_noStore();

  try {
    const { page = 1, perPage = 10, sort, filters, search, vendorId } = input;

    const offset = (page - 1) * perPage;

    // 필터링 조건 구성 (vendorId는 필수로 포함)
    const whereConditions = [eq(pcrPo.vendorId, vendorId)];

    if (filters) {
      if (filters.pcrApprovalStatus) {
        whereConditions.push(eq(pcrPo.pcrApprovalStatus, filters.pcrApprovalStatus));
      }
      if (filters.changeType) {
        whereConditions.push(eq(pcrPo.changeType, filters.changeType));
      }
      if (filters.project) {
        whereConditions.push(ilike(pcrPo.project, `%${filters.project}%`));
      }
      if (filters.poContractNumber) {
        whereConditions.push(ilike(pcrPo.poContractNumber, `%${filters.poContractNumber}%`));
      }
      if (filters.startDate) {
        whereConditions.push(sql`${pcrPo.pcrRequestDate} >= ${filters.startDate}`);
      }
      if (filters.endDate) {
        whereConditions.push(sql`${pcrPo.pcrRequestDate} <= ${filters.endDate}`);
      }
    }

    // 검색 조건
    if (search) {
      whereConditions.push(
        or(
          ilike(pcrPo.poContractNumber, `%${search}%`),
          ilike(pcrPo.project, `%${search}%`),
          ilike(pcrPo.pcrCreator, `%${search}%`)
        )
      );
    }

    const whereClause = and(...whereConditions);

    // 데이터 조회
    const data = await selectPcrPoWithJoinDynamic
      .where(whereClause)
      .orderBy(sort ? sort : desc(pcrPo.createdAt))
      .limit(perPage)
      .offset(offset);

    // 전체 카운트
    const totalCount = await countPcrPoWithJoin()
      .where(whereClause)
      .then((result) => result[0]?.count ?? 0);

    return {
      data,
      totalCount,
      pageCount: Math.ceil(totalCount / perPage),
    };
  } catch (error) {
    console.error("PCR_PO Partners 목록 조회 오류:", error);
    throw new Error("PCR_PO 데이터를 조회하는데 실패했습니다.");
  }
}

/**
 * 특정 PO/계약 번호에 해당하는 PCR_PR 데이터 조회
 */
export async function getPcrPrListByPoContractNumber(poContractNumber: string) {
  unstable_noStore();

  try {
    // PCR_PR 데이터 조회
    const pcrPrData = await selectPcrPrWithJoinDynamic
      .where(eq(pcrPr.poContractNumber, poContractNumber))
      .orderBy(asc(pcrPr.materialNumber));

    // 첨부파일 데이터 조회 (해당 PCR_PR들의 ID로)
    const pcrPrIds = pcrPrData.map(item => item.id);
    let attachments: any[] = [];

    if (pcrPrIds.length > 0) {
      const attachmentsResult = await db
        .select()
        .from(pcrPrAttachment)
        .where(inArray(pcrPrAttachment.pcrPrId, pcrPrIds))
        .orderBy(pcrPrAttachment.pcrPrId, pcrPrAttachment.type);

      attachments = attachmentsResult;
    }

    // PCR_PR 데이터에 첨부파일 추가
    const dataWithAttachments = pcrPrData.map(pcrPrItem => ({
      ...pcrPrItem,
      attachments: attachments.filter(att => att.pcrPrId === pcrPrItem.id)
    }));

    return dataWithAttachments;
  } catch (error) {
    console.error("PCR_PR 조회 오류:", error);
    throw new Error("PCR_PR 데이터를 조회하는데 실패했습니다.");
  }
}

/**
 * PCR_PO 단일 조회
 */
export async function getPcrPoById(id: number) {
  unstable_noStore();

  try {
    const data = await selectPcrPoWithJoinDynamic
      .where(eq(pcrPo.id, id))
      .limit(1);

    if (data.length === 0) {
      throw new Error("PCR_PO 데이터를 찾을 수 없습니다.");
    }

    return data[0];
  } catch (error) {
    console.error("PCR_PO 단일 조회 오류:", error);
    throw new Error("PCR_PO 데이터를 조회하는데 실패했습니다.");
  }
}

/**
 * PCR_PR 단일 조회
 */
export async function getPcrPrById(id: number) {
  unstable_noStore();

  try {
    const data = await selectPcrPrWithJoinDynamic
      .where(eq(pcrPr.id, id))
      .limit(1);

    if (data.length === 0) {
      throw new Error("PCR_PR 데이터를 찾을 수 없습니다.");
    }

    return data[0];
  } catch (error) {
    console.error("PCR_PR 단일 조회 오류:", error);
    throw new Error("PCR_PR 데이터를 조회하는데 실패했습니다.");
  }
}

/**
 * PCR_PO 생성
 */
export async function createPcrPo(data: {
  pcrApprovalStatus?: string;
  changeType?: string;
  details?: string;
  project?: string;
  pcrRequestDate: Date;
  poContractNumber: string;
  revItemNumber?: string;
  purchaseContractManager?: string;
  pcrCreator?: string;
  poContractAmountBefore?: number;
  poContractAmountAfter?: number;
  contractCurrency?: string;
  pcrReason?: string;
  detailsReason?: string;
  rejectionReason?: string;
  pcrResponseDate?: Date;
  vendorId?: number;
}) {
  try {
    const session = await getServerSession(authOptions);
    if (!session?.user?.id) {
      throw new Error("로그인이 필요합니다.");
    }

    const now = new Date();
    const insertData = {
      ...data,
      pcrApprovalStatus: data.pcrApprovalStatus || 'PENDING',
      changeType: data.changeType || 'OTHER',
      contractCurrency: data.contractCurrency || 'KRW',
      createdBy: session.user.id,
      updatedBy: session.user.id,
      createdAt: now,
      updatedAt: now,
    };

    const result = await db.insert(pcrPo).values(insertData).returning();

    // 캐시 무효화
    revalidatePath("/evcp/pcr");
    revalidatePath("/partners/pcr");

    return {
      success: true,
      data: result[0],
      message: "PCR_PO가 성공적으로 생성되었습니다."
    };
  } catch (error) {
    console.error("PCR_PO 생성 오류:", error);
    return {
      success: false,
      error: getErrorMessage(error),
    };
  }
}

/**
 * PCR_PR 생성
 */
export async function createPcrPr(data: {
  materialNumber: string;
  materialDetails?: string;
  quantityBefore?: number;
  quantityAfter?: number;
  weightBefore?: number;
  weightAfter?: number;
  subcontractorWeightBefore?: number;
  subcontractorWeightAfter?: number;
  supplierWeightBefore?: number;
  supplierWeightAfter?: number;
  specDrawingBefore?: string;
  specDrawingAfter?: string;
  initialPoContractDate?: Date;
  specChangeDate?: Date;
  poContractModifiedDate?: Date;
  confirmationDate?: Date;
  designManager?: string;
  poContractNumber: string;
}) {
  try {
    const session = await getServerSession(authOptions);
    if (!session?.user?.id) {
      throw new Error("로그인이 필요합니다.");
    }

    const now = new Date();
    const insertData = {
      ...data,
      createdBy: session.user.id,
      updatedBy: session.user.id,
      createdAt: now,
      updatedAt: now,
    };

    const result = await db.insert(pcrPr).values(insertData).returning();

    // 캐시 무효화
    revalidatePath("/evcp/pcr");
    revalidatePath("/partners/pcr");

    return {
      success: true,
      data: result[0],
      message: "PCR_PR이 성공적으로 생성되었습니다."
    };
  } catch (error) {
    console.error("PCR_PR 생성 오류:", error);
    return {
      success: false,
      error: getErrorMessage(error),
    };
  }
}

/**
 * PCR_PO 업데이트
 */
export async function updatePcrPo(id: number, data: Partial<{
  pcrApprovalStatus: string;
  changeType: string;
  details: string;
  project: string;
  pcrRequestDate: Date;
  poContractNumber: string;
  revItemNumber: string;
  purchaseContractManager: string;
  pcrCreator: string;
  poContractAmountBefore: number;
  poContractAmountAfter: number;
  contractCurrency: string;
  pcrReason: string;
  detailsReason: string;
  rejectionReason: string;
  pcrResponseDate: Date;
  vendorId: number;
}>) {
  try {
    const session = await getServerSession(authOptions);
    if (!session?.user?.id) {
      throw new Error("로그인이 필요합니다.");
    }

    const updateData = {
      ...data,
      updatedBy: session.user.id,
      updatedAt: new Date(),
    };

    const result = await db
      .update(pcrPo)
      .set(updateData)
      .where(eq(pcrPo.id, id))
      .returning();

    if (result.length === 0) {
      return {
        success: false,
        error: "PCR_PO 데이터를 찾을 수 없습니다.",
      };
    }

    // 캐시 무효화
    revalidatePath("/evcp/pcr");
    revalidatePath("/partners/pcr");

    return {
      success: true,
      data: result[0],
      message: "PCR_PO가 성공적으로 업데이트되었습니다."
    };
  } catch (error) {
    console.error("PCR_PO 업데이트 오류:", error);
    return {
      success: false,
      error: getErrorMessage(error),
    };
  }
}

/**
 * PCR_PR 업데이트
 */
export async function updatePcrPr(id: number, data: Partial<{
  materialNumber: string;
  materialDetails: string;
  quantityBefore: number;
  quantityAfter: number;
  weightBefore: number;
  weightAfter: number;
  subcontractorWeightBefore: number;
  subcontractorWeightAfter: number;
  supplierWeightBefore: number;
  supplierWeightAfter: number;
  specDrawingBefore: string;
  specDrawingAfter: string;
  initialPoContractDate: Date;
  specChangeDate: Date;
  poContractModifiedDate: Date;
  confirmationDate: Date;
  designManager: string;
  poContractNumber: string;
}>) {
  try {
    const session = await getServerSession(authOptions);
    if (!session?.user?.id) {
      throw new Error("로그인이 필요합니다.");
    }

    const updateData = {
      ...data,
      updatedBy: session.user.id,
      updatedAt: new Date(),
    };

    const result = await db
      .update(pcrPr)
      .set(updateData)
      .where(eq(pcrPr.id, id))
      .returning();

    if (result.length === 0) {
      return {
        success: false,
        error: "PCR_PR 데이터를 찾을 수 없습니다.",
      };
    }

    // 캐시 무효화
    revalidatePath("/evcp/pcr");
    revalidatePath("/partners/pcr");

    return {
      success: true,
      data: result[0],
      message: "PCR_PR이 성공적으로 업데이트되었습니다."
    };
  } catch (error) {
    console.error("PCR_PR 업데이트 오류:", error);
    return {
      success: false,
      error: getErrorMessage(error),
    };
  }
}

/**
 * PCR_PO 삭제
 */
export async function deletePcrPo(id: number) {
  try {
    const session = await getServerSession(authOptions);
    if (!session?.user?.id) {
      throw new Error("로그인이 필요합니다.");
    }

    const result = await db
      .delete(pcrPo)
      .where(eq(pcrPo.id, id))
      .returning();

    if (result.length === 0) {
      return {
        success: false,
        error: "PCR_PO 데이터를 찾을 수 없습니다.",
      };
    }

    // 캐시 무효화
    revalidatePath("/evcp/pcr");
    revalidatePath("/partners/pcr");

    return {
      success: true,
      message: "PCR_PO가 성공적으로 삭제되었습니다."
    };
  } catch (error) {
    console.error("PCR_PO 삭제 오류:", error);
    return {
      success: false,
      error: getErrorMessage(error),
    };
  }
}

/**
 * PCR_PR 삭제
 */
export async function deletePcrPr(id: number) {
  try {
    const session = await getServerSession(authOptions);
    if (!session?.user?.id) {
      throw new Error("로그인이 필요합니다.");
    }

    const result = await db
      .delete(pcrPr)
      .where(eq(pcrPr.id, id))
      .returning();

    if (result.length === 0) {
      return {
        success: false,
        error: "PCR_PR 데이터를 찾을 수 없습니다.",
      };
    }

    // 캐시 무효화
    revalidatePath("/evcp/pcr");
    revalidatePath("/partners/pcr");

    return {
      success: true,
      message: "PCR_PR이 성공적으로 삭제되었습니다."
    };
  } catch (error) {
    console.error("PCR_PR 삭제 오류:", error);
    return {
      success: false,
      error: getErrorMessage(error),
    };
  }
}

/**
 * 간단한 협력업체 목록 조회 (PCR 생성용)
 */
export async function getVendorsForPcr(input?: {
  vendorId?: number; // 특정 vendorId만 조회 (Partners 페이지용)
  limit?: number; // 조회 개수 제한
}) {
  unstable_noStore();

  try {
    const { vendorId, limit = 100 } = input || {};

    let query = db
      .select({
        id: vendors.id,
        vendorName: vendors.vendorName,
        vendorCode: vendors.vendorCode,
      })
      .from(vendors)
      .orderBy(asc(vendors.vendorName));

    // 특정 vendorId 필터링 (Partners 페이지용)
    if (vendorId) {
      query = query.where(eq(vendors.id, vendorId));
    }

    // 개수 제한
    if (limit) {
      query = query.limit(limit);
    }

    const result = await query;

    return {
      success: true,
      data: result,
    };
  } catch (error) {
    console.error("협력업체 조회 오류:", error);
    return {
      success: false,
      error: getErrorMessage(error),
      data: [],
    };
  }
}

/**
 * PCR_PR 첨부파일 조회
 */
export async function getPcrPrAttachments(pcrPrId: number) {
  unstable_noStore();

  try {
    const result = await db
      .select()
      .from(pcrPrAttachment)
      .where(eq(pcrPrAttachment.pcrPrId, pcrPrId))
      .orderBy(pcrPrAttachment.type, pcrPrAttachment.createdAt);

    return {
      success: true,
      data: result,
    };
  } catch (error) {
    console.error("PCR_PR 첨부파일 조회 오류:", error);
    return {
      success: false,
      error: getErrorMessage(error),
      data: [],
    };
  }
}