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

"use server";

import { GetVendorPOSchema } from "./validations";
import { getVendorPOsPage } from "./mock-data";
import { VendorPO, VendorPOItem } from "./types";
import db from "@/db/db";
import { contracts, contractItems } from "@/db/schema/contract";
import { projects } from "@/db/schema/projects";
import { vendors } from "@/db/schema/vendors";
import { items } from "@/db/schema/items";
import { eq, and, or, ilike, count, desc, asc } from "drizzle-orm";

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

    // 벤더 필터링 (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":
              whereConditions.push(ilike(contracts.status, `%${filter.value}%`));
              break;
            case "contractType":
              whereConditions.push(ilike(contracts.purchaseDocType, `%${filter.value}%`));
              break;
            case "currency":
              whereConditions.push(eq(contracts.currency, filter.value));
              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,
        
        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
    };
    
    // 목업 데이터 사용 (개발/테스트용)
    // const result = getVendorPOsPage(
    //   input.page,
    //   input.perPage,
    //   input.search,
    //   input.filters
    // );

    // 실제 데이터베이스 연동시에는 아래와 같은 구조로 구현
    // const offset = (input.page - 1) * input.perPage;
    // 
    // // 검색 조건 구성
    // let whereConditions = [];
    // if (input.search) {
    //   const searchTerm = `%${input.search}%`;
    //   whereConditions.push(
    //     or(
    //       ilike(vendorPOTable.contractNo, searchTerm),
    //       ilike(vendorPOTable.contractName, searchTerm),
    //       ilike(vendorPOTable.projectName, searchTerm)
    //     )
    //   );
    // }
    //
    // // 필터 조건 추가
    // if (input.contractStatus) {
    //   whereConditions.push(eq(vendorPOTable.contractStatus, input.contractStatus));
    // }
    //
    // const finalWhere = whereConditions.length > 0 ? and(...whereConditions) : undefined;
    //
    // // 정렬 조건
    // const orderBy = input.sort.length > 0
    //   ? input.sort.map((item) =>
    //       item.desc
    //         ? desc(vendorPOTable[item.id])
    //         : asc(vendorPOTable[item.id])
    //     )
    //   : [desc(vendorPOTable.lastModifiedDate)];
    //
    // // 데이터 조회
    // const data = await db
    //   .select()
    //   .from(vendorPOTable)
    //   .where(finalWhere)
    //   .orderBy(...orderBy)
    //   .offset(offset)
    //   .limit(input.perPage);
    //
    // // 총 개수 조회
    // const [{ count }] = await db
    //   .select({ count: count() })
    //   .from(vendorPOTable)
    //   .where(finalWhere);
    //
    // const pageCount = Math.ceil(count / input.perPage);

    return {
      data: result.data,
      pageCount: result.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 result = getVendorPOsPage(1, 100); // 모든 데이터 가져오기
    const po = result.data.find(item => item.id === id);
    
    return po || null;
  } catch (err) {
    console.error("Error in getVendorPOById:", err);
    return null;
  }
}

/**
 * 벤더 PO 액션 처리
 * PCR생성, 승인, 거절 등의 액션을 처리
 */
export async function handleVendorPOAction(
  poId: number,
  action: string,
  data?: any
): Promise<{ success: boolean; message: string }> {
  try {
    // 목업에서는 성공 응답만 반환
    // 실제 구현시에는 각 액션별로 비즈니스 로직 구현
    
    switch (action) {
      case "pcr-create":
        return { success: true, message: "PCR이 성공적으로 생성되었습니다." };
      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,
        description: contractItems.description,
        quantity: contractItems.quantity,
        unitPrice: contractItems.unitPrice,
        taxRate: contractItems.taxRate,
        taxAmount: contractItems.taxAmount,
        totalLineAmount: contractItems.totalLineAmount,
        remark: contractItems.remark,
        
        // 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 타입으로 변환
    const vendorPOItems: VendorPOItem[] = rawItems.map(row => ({
      contractNo: row.contractNo || '',
      itemNo: row.itemCode || 'AUTO-ITEM', // mock 데이터용
      prNo: `PR-${new Date().getFullYear()}-${String(row.id).padStart(4, '0')}`, // mock 데이터용
      materialGroup: row.packageCode || 'Unknown Group',
      priceStandard: 'FOB', // mock 데이터용 기본값
      materialNo: row.itemCode || '',
      itemDescription: row.itemName || '',
      materialSpec: row.description || '',
      fittingNo: undefined, // contract_items에 없는 필드
      cert: undefined, // contract_items에 없는 필드
      material: row.gradeMaterial || undefined,
      specification: row.description || '',
      quantity: row.quantity || 1,
      quantityUnit: row.unitOfMeasure || 'EA',
      weight: undefined, // contract_items에 없는 필드
      weightUnit: undefined, // contract_items에 없는 필드
      totalWeight: undefined, // contract_items에 없는 필드
      unitPrice: row.unitPrice ? Number(row.unitPrice) : 0,
      priceUnit: 'KRW', // 기본값
      priceUnitValue: '원/EA', // 기본값
      contractAmount: row.totalLineAmount ? Number(row.totalLineAmount) : 0,
      adjustmentAmount: undefined, // contract_items에 없는 필드
      deliveryDate: new Date().toISOString().split('T')[0], // 기본값 (오늘 날짜)
      vatType: row.taxRate && Number(row.taxRate) > 0 ? '과세' : '면세',
      steelSpec: row.steelType || undefined,
      prManager: 'AUTO-MANAGER', // mock 데이터용 기본값
    }));

    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;
  }
}