path: root/lib/po/vendor-table/service.ts

"use server";

import { GetVendorPOSchema } from "./validations";
import { VendorPO, VendorPOItem } from "./types";
import db from "@/db/db";
import { contracts, contractItems, ContractStatus } from "@/db/schema/contract";
import { projects } from "@/db/schema/projects";
import { vendors } from "@/db/schema/vendors";
import { items } from "@/db/schema/items";
import { users } from "@/db/schema/users";
import { revalidatePath } from "next/cache";
import { eq, and, or, ilike, count, desc, asc, SQL } from "drizzle-orm";

/**
 * 벤더 PO 목록 조회
 * contracts 테이블에서 실제 데이터를 조회합니다.
 */
export async function getVendorPOs(input: GetVendorPOSchema) {
  try {
    // 실제 데이터베이스 조회
    const offset = (input.page - 1) * input.perPage;
    
    // 검색 조건 구성
    const whereConditions: SQL<unknown>[] = [];
    if (input.search) {
      const searchTerm = `%${input.search}%`;
      const searchCondition = or(
        ilike(contracts.contractNo, searchTerm),
        ilike(contracts.contractName, searchTerm),
        ilike(projects.name, searchTerm),
        ilike(vendors.vendorName, searchTerm)
      );
      if (searchCondition) whereConditions.push(searchCondition);
    }

    // 벤더 필터링 (partners 페이지에서 사용)
    if (input.vendorId && input.vendorId > 0) {
      whereConditions.push(eq(contracts.vendorId, input.vendorId));
    }

    // 필터 조건 추가
    if (input.filters && input.filters.length > 0) {
      for (const filter of input.filters) {
        if (filter.id && filter.value) {
          switch (filter.id) {
            case "contractStatus":
              const statusCondition = ilike(contracts.status, `%${filter.value}%`);
              if (statusCondition) whereConditions.push(statusCondition);
              break;
            case "contractType":
              const typeCondition = ilike(contracts.purchaseDocType, `%${filter.value}%`);
              if (typeCondition) whereConditions.push(typeCondition);
              break;
            case "currency":
              whereConditions.push(eq(contracts.currency, filter.value as string));
              break;
            // 추가 필터 조건들...
          }
        }
      }
    }

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

    // 정렬 조건
    const orderBy = input.sort && input.sort.length > 0
      ? input.sort.map((item) => {
          switch (item.id) {
            case "contractNo":
              return item.desc ? desc(contracts.contractNo) : asc(contracts.contractNo);
            case "contractName":
              return item.desc ? desc(contracts.contractName) : asc(contracts.contractName);
            case "lastModifiedDate":
              return item.desc ? desc(contracts.updatedAt) : asc(contracts.updatedAt);
            default:
              return desc(contracts.updatedAt);
          }
        })
      : [desc(contracts.updatedAt)];

    // 데이터 조회 (조인 포함)
    const rawData = await db
      .select({
        // contracts 테이블 필드들
        id: contracts.id,
        contractNo: contracts.contractNo,
        contractName: contracts.contractName,
        status: contracts.status,
        startDate: contracts.startDate,
        endDate: contracts.endDate,
        currency: contracts.currency,
        totalAmount: contracts.totalAmount,
        totalAmountKrw: contracts.totalAmountKrw,
        paymentTerms: contracts.paymentTerms,
        deliveryTerms: contracts.deliveryTerms,
        deliveryLocation: contracts.deliveryLocation,
        shippmentPlace: contracts.shippmentPlace,
        exchangeRate: contracts.exchangeRate,
        
        // SAP ECC 추가 필드들
        poVersion: contracts.poVersion,
        purchaseDocType: contracts.purchaseDocType,
        purchaseOrg: contracts.purchaseOrg,
        purchaseGroup: contracts.purchaseGroup,
        poConfirmStatus: contracts.poConfirmStatus,
        contractGuaranteeCode: contracts.contractGuaranteeCode,
        defectGuaranteeCode: contracts.defectGuaranteeCode,
        guaranteePeriodCode: contracts.guaranteePeriodCode,
        advancePaymentYn: contracts.advancePaymentYn,
        budgetAmount: contracts.budgetAmount,
        budgetCurrency: contracts.budgetCurrency,
        electronicContractYn: contracts.electronicContractYn,
        electronicApprovalDate: contracts.electronicApprovalDate,
        electronicApprovalTime: contracts.electronicApprovalTime,
        ownerApprovalYn: contracts.ownerApprovalYn,
        plannedInOutFlag: contracts.plannedInOutFlag,
        settlementStandard: contracts.settlementStandard,
        weightSettlementFlag: contracts.weightSettlementFlag,
        priceIndexYn: contracts.priceIndexYn,
        writtenContractNo: contracts.writtenContractNo,
        contractVersion: contracts.contractVersion,

        // 계약서 내용 및 노트
        contractContent: contracts.contractContent,
        remarks: contracts.remarks,

        createdAt: contracts.createdAt,
        updatedAt: contracts.updatedAt,
        
        // 조인된 테이블 필드들
        projectName: projects.name,
        vendorName: vendors.vendorName,
      })
      .from(contracts)
      .leftJoin(projects, eq(contracts.projectId, projects.id))
      .leftJoin(vendors, eq(contracts.vendorId, vendors.id))
      .where(finalWhere)
      .orderBy(...orderBy)
      .offset(offset)
      .limit(input.perPage);

    // 총 개수 조회
    const [{ totalCount }] = await db
      .select({ totalCount: count() })
      .from(contracts)
      .leftJoin(projects, eq(contracts.projectId, projects.id))
      .leftJoin(vendors, eq(contracts.vendorId, vendors.id))
      .where(finalWhere);

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

    // VendorPO 타입으로 변환
    const data: VendorPO[] = rawData.map(row => ({
        id: row.id,
        contractNo: row.contractNo || '',
        revision: 'Rev.01', // mock 데이터용 기본값
        itemNo: 'ITM-AUTO', // mock 데이터용 기본값
        contractStatus: row.status || '',
        contractType: row.purchaseDocType || '',
        details: '상세보기', // mock 데이터용 기본값
        projectName: row.projectName || '',
        contractName: row.contractName || '',
        contractPeriod: row.startDate && row.endDate 
          ? `${row.startDate} ~ ${row.endDate}` 
          : '',
        contractQuantity: '1 LOT', // 기본값 (실제로는 contract_items에서 계산 필요)
        currency: row.currency || 'KRW',
        paymentTerms: row.paymentTerms || '',
        tax: '10%', // 기본값 (실제로는 contract_items에서 계산 필요)
        exchangeRate: row.exchangeRate?.toString() || '',
        deliveryTerms: row.deliveryTerms || '',
        purchaseManager: '', // 사용자 테이블 조인 필요
        poReceiveDate: row.createdAt?.toISOString().split('T')[0] || '',
        contractDate: row.startDate || '',
        lcNo: undefined,
        priceIndexTarget: row.priceIndexYn === 'Y',
        linkedContractNo: undefined,
        lastModifiedDate: row.updatedAt?.toISOString().split('T')[0] || '',
        lastModifiedBy: '', // 사용자 테이블 조인 필요
        
        // SAP ECC 추가 필드들
        poVersion: row.poVersion || undefined,
        purchaseDocType: row.purchaseDocType || undefined,
        purchaseOrg: row.purchaseOrg || undefined,
        purchaseGroup: row.purchaseGroup || undefined,
        poConfirmStatus: row.poConfirmStatus || undefined,
        contractGuaranteeCode: row.contractGuaranteeCode || undefined,
        defectGuaranteeCode: row.defectGuaranteeCode || undefined,
        guaranteePeriodCode: row.guaranteePeriodCode || undefined,
        advancePaymentYn: row.advancePaymentYn || undefined,
        budgetAmount: row.budgetAmount ? Number(row.budgetAmount) : undefined,
        budgetCurrency: row.budgetCurrency || undefined,
        totalAmount: row.totalAmount ? Number(row.totalAmount) : undefined,
        totalAmountKrw: row.totalAmountKrw ? Number(row.totalAmountKrw) : undefined,
        electronicContractYn: row.electronicContractYn || undefined,
        electronicApprovalDate: row.electronicApprovalDate || undefined,
        electronicApprovalTime: row.electronicApprovalTime || undefined,
        ownerApprovalYn: row.ownerApprovalYn || undefined,
        plannedInOutFlag: row.plannedInOutFlag || undefined,
        settlementStandard: row.settlementStandard || undefined,
        weightSettlementFlag: row.weightSettlementFlag || undefined,
        priceIndexYn: row.priceIndexYn || undefined,
        writtenContractNo: row.writtenContractNo || undefined,
        contractVersion: row.contractVersion || undefined,
    }));

    return {
      data,
      pageCount
    };
  } catch (err) {
    console.error("Error in getVendorPOs:", err);
    return { data: [], pageCount: 0 };
  }
}

/**
 * 벤더 PO 상세 정보 조회
 */
export async function getVendorPOById(id: number): Promise<VendorPO | null> {
  try {
    const [row] = await db
      .select({
        id: contracts.id,
        contractNo: contracts.contractNo,
        contractName: contracts.contractName,
        contractContent: contracts.contractContent,
        remarks: contracts.remarks,
        status: contracts.status,
        startDate: contracts.startDate,
        endDate: contracts.endDate,
        deliveryDate: contracts.deliveryDate,
        currency: contracts.currency,
        totalAmount: contracts.totalAmount,
        totalAmountKrw: contracts.totalAmountKrw,
        paymentTerms: contracts.paymentTerms,
        deliveryTerms: contracts.deliveryTerms,
        deliveryLocation: contracts.deliveryLocation,
        shippmentPlace: contracts.shippmentPlace,
        exchangeRate: contracts.exchangeRate,
        poVersion: contracts.poVersion,
        purchaseDocType: contracts.purchaseDocType,
        purchaseOrg: contracts.purchaseOrg,
        purchaseGroup: contracts.purchaseGroup,
        poConfirmStatus: contracts.poConfirmStatus,
        contractGuaranteeCode: contracts.contractGuaranteeCode,
        defectGuaranteeCode: contracts.defectGuaranteeCode,
        guaranteePeriodCode: contracts.guaranteePeriodCode,
        advancePaymentYn: contracts.advancePaymentYn,
        budgetAmount: contracts.budgetAmount,
        budgetCurrency: contracts.budgetCurrency,
        electronicContractYn: contracts.electronicContractYn,
        electronicApprovalDate: contracts.electronicApprovalDate,
        electronicApprovalTime: contracts.electronicApprovalTime,
        ownerApprovalYn: contracts.ownerApprovalYn,
        plannedInOutFlag: contracts.plannedInOutFlag,
        settlementStandard: contracts.settlementStandard,
        weightSettlementFlag: contracts.weightSettlementFlag,
        priceIndexYn: contracts.priceIndexYn,
        writtenContractNo: contracts.writtenContractNo,
        contractVersion: contracts.contractVersion,
        createdAt: contracts.createdAt,
        updatedAt: contracts.updatedAt,
        
        // 프로젝트 정보
        projectName: projects.name,
        projectCode: projects.code,
        
        // 벤더 정보
        vendorName: vendors.vendorName,
        vendorAddress: vendors.address,
        vendorAddressDetail: vendors.addressDetail,
        vendorPhone: vendors.phone,
        vendorEmail: vendors.email,
        
        // 구매담당자 정보 (purchaseGroup으로 조회)
        purchaseManagerName: users.name,
      })
      .from(contracts)
      .leftJoin(projects, eq(contracts.projectId, projects.id))
      .leftJoin(vendors, eq(contracts.vendorId, vendors.id))
      .leftJoin(users, eq(contracts.purchaseGroup, users.userCode))
      .where(eq(contracts.id, id))
      .limit(1);

    if (!row) return null;

    // VendorPO 타입으로 변환
    const po: VendorPO = {
      id: row.id,
      contractNo: row.contractNo || '',
      revision: 'Rev.01',
      itemNo: 'ITM-AUTO',
      contractStatus: row.status || '',
      contractType: row.purchaseDocType || '',
      details: '상세보기',
      projectName: row.projectName || '',
      projectCode: row.projectCode || undefined,
      contractName: row.contractName || '',
      contractContent: row.contractContent || undefined,
      remarks: row.remarks || undefined,
      contractPeriod: row.startDate && row.endDate ? `${row.startDate} ~ ${row.endDate}` : '',
      contractQuantity: '1 LOT',
      currency: row.currency || 'KRW',
      paymentTerms: row.paymentTerms || '',
      tax: '10%',
      exchangeRate: row.exchangeRate?.toString() || '',
      deliveryTerms: row.deliveryTerms || '',
      purchaseManager: row.purchaseManagerName || '',
      poReceiveDate: row.createdAt?.toISOString().split('T')[0] || '',
      contractDate: row.startDate || '',
      lcNo: undefined,
      priceIndexTarget: row.priceIndexYn === 'Y',
      linkedContractNo: undefined,
      lastModifiedDate: row.updatedAt?.toISOString().split('T')[0] || '',
      lastModifiedBy: '',
      
      // 벤더 정보
      vendorName: row.vendorName || undefined,
      vendorAddress: row.vendorAddress || undefined,
      vendorAddressDetail: row.vendorAddressDetail || undefined,
      vendorPhone: row.vendorPhone || undefined,
      vendorEmail: row.vendorEmail || undefined,
      
      // 구매담당자 정보
      purchaseManagerName: row.purchaseManagerName || undefined,
      
      // SAP 필드
      poVersion: row.poVersion || undefined,
      purchaseDocType: row.purchaseDocType || undefined,
      purchaseOrg: row.purchaseOrg || undefined,
      purchaseGroup: row.purchaseGroup || undefined,
      poConfirmStatus: row.poConfirmStatus || undefined,
      contractGuaranteeCode: row.contractGuaranteeCode || undefined,
      defectGuaranteeCode: row.defectGuaranteeCode || undefined,
      guaranteePeriodCode: row.guaranteePeriodCode || undefined,
      advancePaymentYn: row.advancePaymentYn || undefined,
      budgetAmount: row.budgetAmount ? Number(row.budgetAmount) : undefined,
      budgetCurrency: row.budgetCurrency || undefined,
      totalAmount: row.totalAmount ? Number(row.totalAmount) : undefined,
      totalAmountKrw: row.totalAmountKrw ? Number(row.totalAmountKrw) : undefined,
      electronicContractYn: row.electronicContractYn || undefined,
      electronicApprovalDate: row.electronicApprovalDate || undefined,
      electronicApprovalTime: row.electronicApprovalTime || undefined,
      ownerApprovalYn: row.ownerApprovalYn || undefined,
      plannedInOutFlag: row.plannedInOutFlag || undefined,
      settlementStandard: row.settlementStandard || undefined,
      weightSettlementFlag: row.weightSettlementFlag || undefined,
      priceIndexYn: row.priceIndexYn || undefined,
      writtenContractNo: row.writtenContractNo || undefined,
      contractVersion: row.contractVersion || undefined,
    };

    return po;
  } catch (err) {
    console.error("Error in getVendorPOById:", err);
    return null;
  }
}

/**
 * 벤더 PO 액션 처리
 * PCR생성, 승인, 거절 등의 액션을 처리
 */
export async function handleVendorPOAction(
  poId: number,
  action: string
): Promise<{ success: boolean; message: string }> {
  try {
    // 목업에서는 성공 응답만 반환
    // 실제 구현시에는 각 액션별로 비즈니스 로직 구현
    
    switch (action) {
      case "pcr-create":
        return { success: true, message: "개발중" };
      case "approve":
        return { success: true, message: '개발중' };
      case "cancel-approve":
        return { success: true, message: '개발중' };
      case "reject-contract":
        return { success: true, message: '개발중' };
      case "print-contract":
        return { success: true, message: '개발중' };
      default:
        return { success: false, message: '개발중' };
    }
  } catch (err) {
    console.error("Error in handleVendorPOAction:", err);
    return { success: false, message: "액션 처리 중 오류가 발생했습니다." };
  }
}

/**
 * 특정 계약의 상세품목 조회
 * contract_items 테이블에서 실제 데이터를 조회합니다.
 */
export async function getVendorPOItems(contractId: number): Promise<VendorPOItem[]> {
  try {
    const rawItems = await db
      .select({
        // contract_items 테이블 필드들
        id: contractItems.id,
        contractId: contractItems.contractId,
        itemId: contractItems.itemId,
        itemNo: contractItems.itemNo, // EBELP - 구매오더품목번호 (품번)
        prNo: contractItems.prNo, // BANFN - 구매요청번호 (PR번호)
        prItemNo: contractItems.prItemNo, // BNFPO - 구매요청품목번호 (PR 품번)
        materialGroup: contractItems.materialGroup, // MATKL - 자재그룹
        weight: contractItems.weight, // NTGEW - 순중량
        weightUnit: contractItems.weightUnit, // GEWEI - 중량단위
        totalWeight: contractItems.totalWeight, // BRGEW - 총중량
        description: contractItems.description,
        quantity: contractItems.quantity,
        unitPrice: contractItems.unitPrice, // NETPR - 구매단가
        
        // 가격 관련 추가 필드
        PEINH: contractItems.PEINH, // 가격단위값
        BPRME: contractItems.BPRME, // 구매단가단위
        ZNETPR: contractItems.ZNETPR, // 발주단가
        ZREF_NETPR: contractItems.ZREF_NETPR, // 참조단가
        
        taxRate: contractItems.taxRate,
        taxAmount: contractItems.taxAmount,
        taxType: contractItems.taxType, // MWSKZ - 매출부가가치세코드
        totalLineAmount: contractItems.totalLineAmount,
        remark: contractItems.remark,
        
        // SAP ECC 추가 필드들
        ZPO_UNIT: contractItems.ZPO_UNIT, // 구매오더수량단위
        NETWR: contractItems.NETWR, // 오더정가 (최종 정가)
        BRTWR: contractItems.BRTWR, // 오더총액 (기본 총액)
        ZPDT_EXDS_AMT: contractItems.ZPDT_EXDS_AMT, // 할인/할증금액
        
        // 위치 정보
        WERKS: contractItems.WERKS, // 플랜트코드
        LGORT: contractItems.LGORT, // 저장위치
        
        // RFQ 추적
        ANFNR: contractItems.ANFNR, // RFQ번호
        ANFPS: contractItems.ANFPS, // RFQ품목번호
        
        // 자재 추적
        ZPO_LOT_NO: contractItems.ZPO_LOT_NO, // Steel Material Marking No
        
        // 볼륨 정보
        VOLUM: contractItems.VOLUM, // 볼륨
        VOLEH: contractItems.VOLEH, // 볼륨단위
        
        // 날짜 정보
        ZPO_DLV_DT: contractItems.ZPO_DLV_DT, // PO납기일자
        ZPLN_ST_DT: contractItems.ZPLN_ST_DT, // 예정시작일자
        ZPLN_ED_DT: contractItems.ZPLN_ED_DT, // 예정종료일자
        LFDAT: contractItems.LFDAT, // PR Delivery Date
        ZRCV_DT: contractItems.ZRCV_DT, // 구매접수일자
        
        // 기타
        ZCON_IND: contractItems.ZCON_IND, // 시리즈구분
        
        // contracts 테이블 필드들
        contractNo: contracts.contractNo,
        
        // items 테이블 필드들
        itemCode: items.itemCode,
        itemName: items.itemName,
        packageCode: items.packageCode,
        unitOfMeasure: items.unitOfMeasure,
        gradeMaterial: items.gradeMaterial,
        steelType: items.steelType,
        smCode: items.smCode,
      })
      .from(contractItems)
      .leftJoin(contracts, eq(contractItems.contractId, contracts.id))
      .leftJoin(items, eq(contractItems.itemId, items.id))
      .where(eq(contractItems.contractId, contractId))
      .orderBy(contractItems.id);

    // VendorPOItem 타입으로 변환
    // SAP에서 제공하지 않는 데이터는 임의로 만들지 않고 그대로 표시 (FE에서 '-' 처리)
    const vendorPOItems: VendorPOItem[] = rawItems.map(row => ({
      contractNo: row.contractNo || '',
      itemNo: row.itemNo || '', // EBELP - 구매오더품목번호 (품번)
      prNo: row.prNo || '', // BANFN - 구매요청번호 (PR번호)
      prItemNo: row.prItemNo || '', // BNFPO - 구매요청품목번호 (PR 품번)
      materialGroup: row.materialGroup || '', // MATKL - 자재그룹
      priceStandard: '', // SAP에서 제공되지 않음 - FE에서 '-' 처리
      materialNo: row.itemCode || '',
      itemDescription: row.itemName || '',
      materialSpec: row.description || '',
      fittingNo: undefined, // SAP에서 제공되지 않음
      cert: undefined, // SAP에서 제공되지 않음
      material: row.gradeMaterial || undefined,
      specification: row.description || '',
      quantity: row.quantity ?? 0, // null/undefined면 0, 0이면 0 표시
      quantityUnit: row.ZPO_UNIT || row.unitOfMeasure || '', // SAP 단위 우선, 없으면 items 테이블 단위
      ZPO_UNIT: row.ZPO_UNIT || undefined, // SAP 구매오더수량단위
      weight: row.weight ? Number(row.weight) : undefined, // NTGEW - 순중량
      weightUnit: row.weightUnit || undefined, // GEWEI - 중량단위
      totalWeight: row.totalWeight ? Number(row.totalWeight) : undefined, // BRGEW - 총중량
      unitPrice: row.unitPrice ? Number(row.unitPrice) : 0, // NETPR - 구매단가
      
      // 가격 관련 추가 필드
      PEINH: row.PEINH ?? undefined, // 가격단위값
      BPRME: row.BPRME || undefined, // 구매단가단위
      ZNETPR: row.ZNETPR ? Number(row.ZNETPR) : undefined, // 발주단가
      ZREF_NETPR: row.ZREF_NETPR ? Number(row.ZREF_NETPR) : undefined, // 참조단가
      
      priceUnit: row.BPRME || '', // BPRME을 priceUnit으로 사용
      priceUnitValue: row.PEINH ? String(row.PEINH) : '', // PEINH를 priceUnitValue로 사용
      contractAmount: row.NETWR ? Number(row.NETWR) : (row.totalLineAmount ? Number(row.totalLineAmount) : 0), // NETWR 우선, 없으면 totalLineAmount
      adjustmentAmount: row.ZPDT_EXDS_AMT ? Number(row.ZPDT_EXDS_AMT) : undefined, // SAP 조정금액
      
      // SAP ECC 금액 필드
      NETWR: row.NETWR ? Number(row.NETWR) : undefined, // 오더정가 (최종 정가)
      BRTWR: row.BRTWR ? Number(row.BRTWR) : undefined, // 오더총액 (기본 총액)
      ZPDT_EXDS_AMT: row.ZPDT_EXDS_AMT ? Number(row.ZPDT_EXDS_AMT) : undefined, // 할인/할증금액
      
      // 위치 정보
      WERKS: row.WERKS || undefined, // 플랜트코드
      LGORT: row.LGORT || undefined, // 저장위치
      
      // RFQ 추적
      ANFNR: row.ANFNR || undefined, // RFQ번호
      ANFPS: row.ANFPS || undefined, // RFQ품목번호
      
      // 자재 추적
      ZPO_LOT_NO: row.ZPO_LOT_NO || undefined, // Steel Material Marking No
      
      // 볼륨 정보
      VOLUM: row.VOLUM ? Number(row.VOLUM) : undefined, // 볼륨
      VOLEH: row.VOLEH || undefined, // 볼륨단위
      
      deliveryDate: row.ZPO_DLV_DT || '', // SAP 납기일자, 없으면 빈 문자열 (FE에서 '-' 처리)
      ZPO_DLV_DT: row.ZPO_DLV_DT || undefined, // SAP PO납기일자
      ZPLN_ST_DT: row.ZPLN_ST_DT || undefined, // SAP 예정시작일자
      ZPLN_ED_DT: row.ZPLN_ED_DT || undefined, // SAP 예정종료일자
      LFDAT: row.LFDAT || undefined, // PR Delivery Date
      ZRCV_DT: row.ZRCV_DT || undefined, // 구매접수일자
      
      // 기타
      ZCON_IND: row.ZCON_IND || undefined, // 시리즈구분 (SS 등)
      
      vatType: row.taxType || '', // MWSKZ - 매출부가가치세코드 (V1, V2 등)
      steelSpec: row.steelType || undefined,
      prManager: '', // SAP에서 제공되지 않음 - FE에서 '-' 처리
      remark: row.remark || undefined,
    }));

    return vendorPOItems;
  } catch (err) {
    console.error("Error in getVendorPOItems:", err);
    throw err;
  }
}

/**
 * 계약번호로 상세품목 조회 (외부에서 contractNo로 호출할 때 사용)
 */
export async function getVendorPOItemsByContractNo(contractNo: string): Promise<VendorPOItem[]> {
  try {
    // 먼저 계약 ID 조회
    const contract = await db.query.contracts.findFirst({
      where: eq(contracts.contractNo, contractNo),
    });
    
    if (!contract) {
      console.warn(`Contract not found: ${contractNo}`);
      return [];
    }
    
    return await getVendorPOItems(contract.id);
  } catch (err) {
    console.error("Error in getVendorPOItemsByContractNo:", err);
    throw err;
  }
}

/**
 * PCR 생성 요청: PCR 생성 요청 후, 상태 변경
 */
export async function createPcrRequest(contractId: number) {
  try {
    // TODO PCR 생성 요청 로직 구현

    // PCR 생성 요청 상태로 변경
    await db.update(contracts).set({ status: ContractStatus.PCR_REQUEST }).where(eq(contracts.id, contractId));

    // 캐시 무효화하여 변경사항 즉시 반영
    revalidatePath("/partners/po");
  } catch (err) {
    console.error("Error in createPcrRequest:", err);
    throw err;
  }

  return {
    success: true,
    message: "PCR 생성 요청이 성공적으로 완료되었습니다."
  };
}

/**
 * 계약 승인 처리: 상태만 변경
 */
export async function acceptContract(contractId: number) {
  try {
    await db.update(contracts).set({ status: ContractStatus.COMPLETE_THE_CONTRACT }).where(eq(contracts.id, contractId));

    // 캐시 무효화하여 변경사항 즉시 반영
    revalidatePath("/partners/po");
  } catch (err) {
    console.error("Error in acceptContract:", err);
    throw err;
  }

  return {
    success: true,
    message: "계약이 성공적으로 승인되었습니다."
  };
}
/**
 * 계약 승인 취소 처리: 상태만 변경
 */
export async function cancelAcceptContract(contractId: number) {
  try {

    // 계약 승인 상태에서만 취소 가능
    const contract = await db.query.contracts.findFirst({
      where: eq(contracts.id, contractId),
    });
    if (!contract) {
      throw new Error("계약을 찾을 수 없습니다.");
    }
    if (contract.status !== ContractStatus.COMPLETE_THE_CONTRACT) {
      throw new Error("계약 승인 상태가 아닙니다.");
    }

    // 취소 처리
    await db.update(contracts).set({ status: ContractStatus.CONTRACT_ACCEPT_REQUEST }).where(eq(contracts.id, contractId));

    // 캐시 무효화하여 변경사항 즉시 반영
    revalidatePath("/partners/po");
  } catch (err) {
    console.error("Error in cancelAcceptContract:", err);
    throw err;
  }

  return {
    success: true,
    message: "계약이 성공적으로 승인 취소되었습니다."
  };
}

/**
 * 계약 거절 처리: 거절 사유를 입력받고, 상태 변경
 */
export async function rejectContract(contractId: number, rejectionReason: string) {
  try {
    await db.update(contracts).set({ status: ContractStatus.REJECT_TO_ACCEPT_CONTRACT, rejectionReason }).where(eq(contracts.id, contractId));

    // 캐시 무효화하여 변경사항 즉시 반영
    revalidatePath("/partners/po");
  } catch (err) {
    console.error("Error in rejectContract:", err);
    throw err;
  }

  return {
    success: true,
    message: "계약이 성공적으로 거절되었습니다."
  };
}

/**
 * 벤더 코멘트 저장
 */
export async function saveVendorComment(contractId: number, vendorComment: string) {
  try {
    await db.update(contracts).set({ 
      vendorComment,
      updatedAt: new Date()
    }).where(eq(contracts.id, contractId));

    // 캐시 무효화하여 변경사항 즉시 반영
    revalidatePath("/partners/po");
  } catch (err) {
    console.error("Error in saveVendorComment:", err);
    throw err;
  }

  return {
    success: true,
    message: "의견이 성공적으로 저장되었습니다."
  };
}

/**
 * SHI 코멘트 저장 (EVCP용)
 */
export async function saveSHIComment(contractId: number, shiComment: string) {
  try {
    await db.update(contracts).set({ 
      shiComment,
      updatedAt: new Date()
    }).where(eq(contracts.id, contractId));

    // 캐시 무효화하여 변경사항 즉시 반영
    revalidatePath("/evcp/po");
    revalidatePath(`/evcp/po/${contractId}`);
  } catch (err) {
    console.error("Error in saveSHIComment:", err);
    throw err;
  }

  return {
    success: true,
    message: "SHI 의견이 성공적으로 저장되었습니다."
  };
}

/**
 * 특정 계약의 상세 정보 조회 (EVCP/SHI용)
 */
export async function getContractDetail(contractId: number) {
  try {
    // contractId 유효성 검사
    if (!contractId || isNaN(contractId)) {
      return { success: false, error: "유효하지 않은 계약 ID입니다." };
    }
    
    // 계약 기본 정보 조회
    const [contractData] = await db
      .select({
        // contracts 테이블 필드들
        id: contracts.id,
        contractNo: contracts.contractNo,
        contractName: contracts.contractName,
        status: contracts.status,
        startDate: contracts.startDate,
        endDate: contracts.endDate,
        contractDate: contracts.createdAt,
        currency: contracts.currency,
        totalAmount: contracts.totalAmount,
        totalAmountKrw: contracts.totalAmountKrw,
        paymentTerms: contracts.paymentTerms,
        deliveryTerms: contracts.deliveryTerms,
        exchangeRate: contracts.exchangeRate,
        rejectionReason: contracts.rejectionReason,
        
        // SAP ECC 추가 필드들
        poVersion: contracts.poVersion,
        purchaseDocType: contracts.purchaseDocType,
        purchaseOrg: contracts.purchaseOrg,
        purchaseGroup: contracts.purchaseGroup,
        poConfirmStatus: contracts.poConfirmStatus,
        
        // 계약/보증 관련
        contractGuaranteeCode: contracts.contractGuaranteeCode,
        defectGuaranteeCode: contracts.defectGuaranteeCode,
        guaranteePeriodCode: contracts.guaranteePeriodCode,
        advancePaymentYn: contracts.advancePaymentYn,
        
        // 계약서 내용 및 노트
        contractContent: contracts.contractContent,
        remarks: contracts.remarks,
        vendorComment: contracts.vendorComment,
        shiComment: contracts.shiComment,
        
        createdAt: contracts.createdAt,
        updatedAt: contracts.updatedAt,
        
        // 조인된 테이블 필드들
        projectId: projects.id,
        projectName: projects.name,
        projectCode: projects.code,
        vendorId: vendors.id,
        vendorName: vendors.vendorName,
        vendorCode: vendors.vendorCode,
      })
      .from(contracts)
      .leftJoin(projects, eq(contracts.projectId, projects.id))
      .leftJoin(vendors, eq(contracts.vendorId, vendors.id))
      .where(eq(contracts.id, contractId))
      .limit(1);

    if (!contractData) {
      return { success: false, error: "계약 정보를 찾을 수 없습니다." };
    }

    // 계약 품목 조회
    const items = await getVendorPOItems(contractId);

    return {
      success: true,
      data: {
        ...contractData,
        items,
      },
    };
  } catch (error) {
    console.error("Error fetching contract detail:", error);
    return { success: false, error: "계약 상세 정보 조회 중 오류가 발생했습니다." };
  }
}

/**
 * 특정 계약의 상세 정보 조회 (벤더용 계약 상세 페이지)
 */
export async function getVendorContractDetail(contractId: number, vendorId: number) {
  try {
    // contractId 유효성 검사
    if (!contractId || isNaN(contractId)) {
      return { success: false, error: "유효하지 않은 계약 ID입니다." };
    }
    
    // 계약 기본 정보 조회 (벤더 필터링 포함)
    const [contractData] = await db
      .select({
        // contracts 테이블 필드들
        id: contracts.id,
        contractNo: contracts.contractNo,
        contractName: contracts.contractName,
        status: contracts.status,
        startDate: contracts.startDate,
        endDate: contracts.endDate,
        contractDate: contracts.createdAt,
        currency: contracts.currency,
        totalAmount: contracts.totalAmount,
        totalAmountKrw: contracts.totalAmountKrw,
        paymentTerms: contracts.paymentTerms,
        deliveryTerms: contracts.deliveryTerms,
        exchangeRate: contracts.exchangeRate,
        
        // SAP ECC 추가 필드들
        poVersion: contracts.poVersion,
        purchaseDocType: contracts.purchaseDocType,
        purchaseOrg: contracts.purchaseOrg,
        purchaseGroup: contracts.purchaseGroup,
        poConfirmStatus: contracts.poConfirmStatus,
        
        // 계약/보증 관련
        contractGuaranteeCode: contracts.contractGuaranteeCode,
        defectGuaranteeCode: contracts.defectGuaranteeCode,
        guaranteePeriodCode: contracts.guaranteePeriodCode,
        advancePaymentYn: contracts.advancePaymentYn,
        
        // 계약서 내용 및 노트
        contractContent: contracts.contractContent,
        remarks: contracts.remarks,
        vendorComment: contracts.vendorComment,
        shiComment: contracts.shiComment,
        
        createdAt: contracts.createdAt,
        updatedAt: contracts.updatedAt,
        
        // 조인된 테이블 필드들
        projectId: projects.id,
        projectName: projects.name,
        projectCode: projects.code,
        vendorId: vendors.id,
        vendorName: vendors.vendorName,
        vendorCode: vendors.vendorCode,
      })
      .from(contracts)
      .leftJoin(projects, eq(contracts.projectId, projects.id))
      .leftJoin(vendors, eq(contracts.vendorId, vendors.id))
      .where(
        and(
          eq(contracts.id, contractId),
          eq(contracts.vendorId, vendorId) // 벤더 권한 체크
        )
      )
      .limit(1);

    if (!contractData) {
      return { success: false, error: "계약 정보를 찾을 수 없거나 접근 권한이 없습니다." };
    }

    // 계약 품목 조회
    const items = await getVendorPOItems(contractId);

    return {
      success: true,
      data: {
        ...contractData,
        items,
      },
    };
  } catch (error) {
    console.error("Error fetching contract detail:", error);
    return { success: false, error: "계약 상세 정보 조회 중 오류가 발생했습니다." };
  }
}