(김준회) MDG 수신 구조 개선 및 MDG SOAP 송신 액션 & 테스트 페이지 구성

1 files changed, 584 insertions, 0 deletions

diff --git a/lib/soap/mdg/send/vendor-master/action.ts b/lib/soap/mdg/send/vendor-master/action.ts
new file mode 100644
index 00000000..34ce242c
--- /dev/null
+++ b/lib/soap/mdg/send/vendor-master/action.ts
@@ -0,0 +1,584 @@
+'use server'
+
+import db from "@/db/db";
+import { 
+  VENDOR_MASTER_BP_HEADER,
+  VENDOR_MASTER_BP_HEADER_ADDRESS,
+  VENDOR_MASTER_BP_HEADER_ADDRESS_AD_EMAIL,
+  VENDOR_MASTER_BP_HEADER_ADDRESS_AD_FAX,
+  VENDOR_MASTER_BP_HEADER_ADDRESS_AD_POSTAL,
+  VENDOR_MASTER_BP_HEADER_ADDRESS_AD_TEL,
+  VENDOR_MASTER_BP_HEADER_ADDRESS_AD_URL,
+  VENDOR_MASTER_BP_HEADER_BP_TAXNUM,
+  VENDOR_MASTER_BP_HEADER_BP_VENGEN,
+  VENDOR_MASTER_BP_HEADER_BP_VENGEN_BP_COMPNY,
+  VENDOR_MASTER_BP_HEADER_BP_VENGEN_BP_COMPNY_BP_WHTAX,
+  VENDOR_MASTER_BP_HEADER_BP_VENGEN_BP_PORG,
+  VENDOR_MASTER_BP_HEADER_BP_VENGEN_BP_PORG_ZVPFN
+} from "@/db/schema/MDG/mdg";
+import { eq, sql, desc } from "drizzle-orm";
+import { withSoapLogging } from "../../utils";
+import fs from 'fs';
+import path from 'path';
+
+// WSDL 엔드포인트 URL (WSDL에서 추출)
+const MDG_ENDPOINT_URL = "http://shii8dvddb01.hec.serp.shi.samsung.net:50000/sap/xi/engine?type=entry&version=3.0&Sender.Service=P2038_D&Interface=http%3A%2F%2Fshi.samsung.co.kr%2FP2_MD%2FMDZ%5EP2MD3007_AO&QualityOfService=ExactlyOnce";
+
+// CSV 파싱 및 필드 정의 ------------------------------------------------
+interface CsvField {
+  table: string;
+  field: string;
+  mandatory: boolean;
+}
+
+function parseCsv(content: string): CsvField[] {
+  const lines = content.trim().split('\n');
+  return lines.slice(1).map(line => {
+    const parts = line.split(',');
+    return {
+      table: parts[1]?.trim(),
+      field: parts[2]?.trim(),
+      mandatory: parts[3]?.trim() === 'M'
+    } as CsvField;
+  });
+}
+
+// 모듈 초기화 시 CSV 로드
+const CSV_PATH = path.join(process.cwd(), 'public', 'wsdl', 'P2MD3007_AO.csv');
+let CSV_FIELDS: CsvField[] = [];
+try {
+  const csvRaw = fs.readFileSync(CSV_PATH, 'utf-8');
+  CSV_FIELDS = parseCsv(csvRaw);
+} catch (e) {
+  console.error('CSV 로딩 실패:', e);
+}
+
+// XML escape helper
+const escapeXml = (unsafe: string) => unsafe.replace(/[<>&'"']/g, (c) => {
+  switch (c) {
+    case '<': return '&lt;';
+    case '>': return '&gt;';
+    case '&': return '&amp;';
+    case '"': return '&quot;';
+    case "'": return '&apos;';
+    default: return c;
+  }
+});
+
+// VENDOR 마스터 데이터를 MDG로 송신하는 액션
+export async function sendVendorMasterToMDG(vendorCodes: string[]): Promise<{
+  success: boolean;
+  message: string;
+  results?: Array<{ vendorCode: string; success: boolean; error?: string }>;
+}> {
+  try {
+    console.log(`🚀 VENDOR_MASTER 송신 시작: ${vendorCodes.length}개 벤더`);
+    
+    const results: Array<{ vendorCode: string; success: boolean; error?: string }> = [];
+    
+    // 각 VENDOR 코드별로 개별 전송 (MDG 시스템의 처리 제한 고려)
+    for (const vendorCode of vendorCodes) {
+      try {
+        console.log(`📤 VENDOR ${vendorCode} 데이터 조회 중...`);
+        
+        // 데이터베이스에서 VENDOR 데이터 조회
+        const vendorData = await fetchVendorData(vendorCode);
+        
+        if (!vendorData) {
+          results.push({
+            vendorCode,
+            success: false,
+            error: 'VENDOR 데이터를 찾을 수 없습니다.'
+          });
+          continue;
+        }
+        
+        // XML 생성
+        const soapXml = buildSoapXML(vendorData);
+        console.log(`📄 VENDOR ${vendorCode} XML 생성 완료`);
+        
+        // SOAP 요청 전송
+        await withSoapLogging(
+          'OUTBOUND',
+          'MDG',
+          'IF_MDZ_EVCP_VENDOR_MASTER',
+          soapXml,
+          async () => {
+            const response = await fetch(MDG_ENDPOINT_URL, {
+              method: 'POST',
+              headers: {
+                'Content-Type': 'text/xml; charset=utf-8',
+                'SOAPAction': 'http://sap.com/xi/WebService/soap1.1',
+              },
+              body: soapXml,
+            });
+            
+            if (!response.ok) {
+              throw new Error(`HTTP ${response.status}: ${response.statusText}`);
+            }
+            
+            const responseText = await response.text();
+            console.log(`✅ VENDOR ${vendorCode} MDG 전송 성공`);
+            
+            // SOAP Fault 체크
+            if (responseText.includes('soap:Fault') || responseText.includes('SOAP:Fault')) {
+              throw new Error(`MDG SOAP Fault: ${responseText}`);
+            }
+            
+            return responseText;
+          }
+        );
+        
+        results.push({
+          vendorCode,
+          success: true
+        });
+        
+      } catch (error) {
+        console.error(`❌ VENDOR ${vendorCode} 전송 실패:`, error);
+        results.push({
+          vendorCode,
+          success: false,
+          error: error instanceof Error ? error.message : 'Unknown error'
+        });
+      }
+    }
+    
+    const successCount = results.filter(r => r.success).length;
+    const failCount = results.length - successCount;
+    
+    console.log(`🎉 VENDOR_MASTER 송신 완료: 성공 ${successCount}개, 실패 ${failCount}개`);
+    
+    return {
+      success: failCount === 0,
+      message: `전송 완료: 성공 ${successCount}개, 실패 ${failCount}개`,
+      results
+    };
+    
+  } catch (error) {
+    console.error('❌ VENDOR_MASTER 송신 중 전체 오류 발생:', error);
+    return {
+      success: false,
+      message: error instanceof Error ? error.message : 'Unknown error'
+    };
+  }
+}
+
+// 데이터베이스에서 VENDOR 데이터 조회
+async function fetchVendorData(vendorCode: string) {
+  try {
+    // 1. 헤더 데이터 조회
+    const [vendorHeader] = await db
+      .select()
+      .from(VENDOR_MASTER_BP_HEADER)
+      .where(eq(VENDOR_MASTER_BP_HEADER.VNDRCD, vendorCode))
+      .limit(1);
+    
+    if (!vendorHeader) {
+      return null;
+    }
+    
+    // 2. 관련 데이터 병렬 조회
+    const [
+      addresses,
+      adEmails,
+      adFaxes,
+      adPostals,
+      adTels,
+      adUrls,
+      bpTaxnums,
+      bpVengens,
+      bpCompnies,
+      bpWhtaxes,
+      bpPorgs,
+      zvpfns
+    ] = await Promise.all([
+      db.select().from(VENDOR_MASTER_BP_HEADER_ADDRESS).where(eq(VENDOR_MASTER_BP_HEADER_ADDRESS.VNDRCD, vendorCode)),
+      db.select().from(VENDOR_MASTER_BP_HEADER_ADDRESS_AD_EMAIL).where(eq(VENDOR_MASTER_BP_HEADER_ADDRESS_AD_EMAIL.VNDRCD, vendorCode)),
+      db.select().from(VENDOR_MASTER_BP_HEADER_ADDRESS_AD_FAX).where(eq(VENDOR_MASTER_BP_HEADER_ADDRESS_AD_FAX.VNDRCD, vendorCode)),
+      db.select().from(VENDOR_MASTER_BP_HEADER_ADDRESS_AD_POSTAL).where(eq(VENDOR_MASTER_BP_HEADER_ADDRESS_AD_POSTAL.VNDRCD, vendorCode)),
+      db.select().from(VENDOR_MASTER_BP_HEADER_ADDRESS_AD_TEL).where(eq(VENDOR_MASTER_BP_HEADER_ADDRESS_AD_TEL.VNDRCD, vendorCode)),
+      db.select().from(VENDOR_MASTER_BP_HEADER_ADDRESS_AD_URL).where(eq(VENDOR_MASTER_BP_HEADER_ADDRESS_AD_URL.VNDRCD, vendorCode)),
+      db.select().from(VENDOR_MASTER_BP_HEADER_BP_TAXNUM).where(eq(VENDOR_MASTER_BP_HEADER_BP_TAXNUM.VNDRCD, vendorCode)),
+      db.select().from(VENDOR_MASTER_BP_HEADER_BP_VENGEN).where(eq(VENDOR_MASTER_BP_HEADER_BP_VENGEN.VNDRCD, vendorCode)),
+      db.select().from(VENDOR_MASTER_BP_HEADER_BP_VENGEN_BP_COMPNY).where(eq(VENDOR_MASTER_BP_HEADER_BP_VENGEN_BP_COMPNY.VNDRCD, vendorCode)),
+      db.select().from(VENDOR_MASTER_BP_HEADER_BP_VENGEN_BP_COMPNY_BP_WHTAX).where(eq(VENDOR_MASTER_BP_HEADER_BP_VENGEN_BP_COMPNY_BP_WHTAX.VNDRCD, vendorCode)),
+      db.select().from(VENDOR_MASTER_BP_HEADER_BP_VENGEN_BP_PORG).where(eq(VENDOR_MASTER_BP_HEADER_BP_VENGEN_BP_PORG.VNDRCD, vendorCode)),
+      db.select().from(VENDOR_MASTER_BP_HEADER_BP_VENGEN_BP_PORG_ZVPFN).where(eq(VENDOR_MASTER_BP_HEADER_BP_VENGEN_BP_PORG_ZVPFN.VNDRCD, vendorCode))
+    ]);
+    
+    return {
+      vendorHeader,
+      addresses,
+      adEmails,
+      adFaxes,
+      adPostals,
+      adTels,
+      adUrls,
+      bpTaxnums,
+      bpVengens,
+      bpCompnies,
+      bpWhtaxes,
+      bpPorgs,
+      zvpfns
+    };
+    
+  } catch (error) {
+    console.error(`VENDOR ${vendorCode} 데이터 조회 실패:`, error);
+    throw error;
+  }
+}
+
+// SOAP XML 생성 (WSDL 구조에 맞춤)
+function buildSoapXML(vendorData: NonNullable<Awaited<ReturnType<typeof fetchVendorData>>>): string {
+  const { vendorHeader, addresses, adEmails, adFaxes, adPostals, adTels, adUrls, bpTaxnums, bpVengens } = vendorData;
+
+  // 값 추출 매핑 ------------------------------------
+  const mapping: Record<string, string | undefined> = {
+    // Header
+    BP_HEADER: vendorHeader?.VNDRCD,
+    ZZSRMCD: 'EVCP',
+    TITLE: vendorHeader?.TITLE ?? '',
+    BU_SORT1: adPostals[0]?.VNDRNM_ABRV_1,
+    NAME_ORG1: adPostals[0]?.VNDRNM_1,
+    KTOKK: bpVengens[0]?.ACNT_GRP,
+    MASTERFLAG: 'X',
+    IBND_TYPE: 'U',
+    // Address mandatory (first)
+    ADDRNO: addresses[0]?.ADDRNO,
+    AD_NATION: adPostals[0]?.INTL_ADR_VER_ID,
+    COUNTRY: adPostals[0]?.NTN_CD,
+    LANGU_COM: adPostals[0]?.LANG_KEY,
+    POST_COD1: adPostals[0]?.CITY_ZIP_NO,
+    CITY1: adPostals[0]?.VNDRNM_1,
+    MC_STREET: adPostals[0]?.ADR_1,
+    // Phone/Fax mandatory fields
+    AD_CONSNO: '001',
+    T_COUNTRY: adTels[0]?.CTRY_CD ?? 'KR',
+    F_COUNTRY: adFaxes[0]?.CTRY_CD ?? 'KR',
+    // Tax
+    BP_TX_TYP: bpTaxnums[0]?.TX_NO_CTG ?? 'KR2',
+    TAXNUM: bpVengens[0]?.VAT_REG_NO,
+    // Default others can be added as needed
+  };
+
+  // 필드 순서에 따라 XML 생성
+  const seen = new Set<string>();
+  const uniqueFields = CSV_FIELDS.filter(f => {
+    if (seen.has(f.field)) return false;
+    seen.add(f.field);
+    return true;
+  });
+
+  const fieldXml = uniqueFields.map(f => {
+    const val = mapping[f.field] ?? '';
+    return `<${f.field}>${escapeXml(val ?? '')}</${f.field}>`;
+  }).join('\n      ');
+
+  const supplierMasterXml = `<SUPPLIER_MASTER>\n      ${fieldXml}\n    </SUPPLIER_MASTER>`;
+
+  const soapEnvelope = `<?xml version="1.0" encoding="UTF-8"?>\n<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:p1="http://shi.samsung.co.kr/P2_MD/MDZ">\n  <soap:Header/>\n  <soap:Body>\n    <p1:MT_P2MD3007_S>\n      <P2MD3007_S>\n        ${supplierMasterXml}\n      </P2MD3007_S>\n    </p1:MT_P2MD3007_S>\n  </soap:Body>\n</soap:Envelope>`;
+
+  return soapEnvelope.trim();
+}
+
+// 특정 VENDOR만 송신하는 유틸리티 함수
+export async function sendSingleVendorToMDG(vendorCode: string) {
+  return await sendVendorMasterToMDG([vendorCode]);
+}
+
+// 모든 VENDOR 송신하는 유틸리티 함수 (주의: 대량 데이터 처리)
+export async function sendAllVendorsToMDG() {
+  try {
+    // 모든 VENDOR 코드 조회
+    const vendors = await db
+      .select({ VNDRCD: VENDOR_MASTER_BP_HEADER.VNDRCD })
+      .from(VENDOR_MASTER_BP_HEADER);
+    
+    const vendorCodes = vendors.map(v => v.VNDRCD);
+    
+    if (vendorCodes.length === 0) {
+      return {
+        success: false,
+        message: '송신할 VENDOR 데이터가 없습니다.'
+      };
+    }
+    
+    console.log(`⚠️ 전체 VENDOR 송신 요청: ${vendorCodes.length}개`);
+    
+    // 배치 처리 (10개씩 분할하여 처리)
+    const batchSize = 10;
+    const results: Array<{ vendorCode: string; success: boolean; error?: string }> = [];
+    
+    for (let i = 0; i < vendorCodes.length; i += batchSize) {
+      const batch = vendorCodes.slice(i, i + batchSize);
+      console.log(`📦 배치 ${Math.floor(i / batchSize) + 1} 처리 중... (${batch.length}개)`);
+      
+      const batchResult = await sendVendorMasterToMDG(batch);
+      if (batchResult.results) {
+        results.push(...batchResult.results);
+      }
+      
+      // 배치 간 잠깐 대기 (서버 부하 방지)
+      if (i + batchSize < vendorCodes.length) {
+        await new Promise(resolve => setTimeout(resolve, 1000));
+      }
+    }
+    
+    const successCount = results.filter(r => r.success).length;
+    const failCount = results.length - successCount;
+    
+    return {
+      success: failCount === 0,
+      message: `전체 송신 완료: 성공 ${successCount}개, 실패 ${failCount}개`,
+      results
+    };
+    
+  } catch (error) {
+    console.error('전체 VENDOR 송신 중 오류:', error);
+    return {
+      success: false,
+      message: error instanceof Error ? error.message : 'Unknown error'
+    };
+  }
+}
+
+// 수정된 VENDOR만 송신하는 액션 (updatedAt != createdAt 조건)
+export async function sendModifiedVendorsToMDG(): Promise<{
+  success: boolean;
+  message: string;
+  results?: Array<{ vendorCode: string; success: boolean; error?: string }>;
+}> {
+  try {
+    console.log('🔍 수정된 VENDOR 데이터 조회 중...');
+    
+    // updatedAt과 createdAt이 다른 VENDOR 조회 (수정된 것들)
+    const modifiedVendors = await db
+      .select({ 
+        VNDRCD: VENDOR_MASTER_BP_HEADER.VNDRCD,
+        createdAt: VENDOR_MASTER_BP_HEADER.createdAt,
+        updatedAt: VENDOR_MASTER_BP_HEADER.updatedAt
+      })
+      .from(VENDOR_MASTER_BP_HEADER)
+      .where(
+        // PostgreSQL에서 timestamp 비교 (밀리초 차이 고려)
+        sql`EXTRACT(EPOCH FROM ${VENDOR_MASTER_BP_HEADER.updatedAt}) - EXTRACT(EPOCH FROM ${VENDOR_MASTER_BP_HEADER.createdAt}) > 1`
+      );
+    
+    const vendorCodes = modifiedVendors.map(v => v.VNDRCD);
+    
+    if (vendorCodes.length === 0) {
+      console.log('📝 수정된 VENDOR 데이터가 없습니다.');
+      return {
+        success: true,
+        message: '수정된 VENDOR 데이터가 없습니다.'
+      };
+    }
+    
+    console.log(`📋 수정된 VENDOR ${vendorCodes.length}개 발견:`, vendorCodes);
+    
+    // 수정된 VENDOR들의 수정 시간 로그
+    modifiedVendors.forEach(vendor => {
+      console.log(`  - ${vendor.VNDRCD}: 생성 ${vendor.createdAt?.toISOString()}, 수정 ${vendor.updatedAt?.toISOString()}`);
+    });
+    
+    // 배치 처리로 송신
+    const batchSize = 10;
+    const results: Array<{ vendorCode: string; success: boolean; error?: string }> = [];
+    
+    for (let i = 0; i < vendorCodes.length; i += batchSize) {
+      const batch = vendorCodes.slice(i, i + batchSize);
+      console.log(`📦 수정 데이터 배치 ${Math.floor(i / batchSize) + 1} 처리 중... (${batch.length}개)`);
+      
+      const batchResult = await sendVendorMasterToMDG(batch);
+      if (batchResult.results) {
+        results.push(...batchResult.results);
+      }
+      
+      // 배치 간 대기
+      if (i + batchSize < vendorCodes.length) {
+        await new Promise(resolve => setTimeout(resolve, 1000));
+      }
+    }
+    
+    const successCount = results.filter(r => r.success).length;
+    const failCount = results.length - successCount;
+    
+    console.log(`🎯 수정된 VENDOR 송신 완료: 성공 ${successCount}개, 실패 ${failCount}개`);
+    
+    return {
+      success: failCount === 0,
+      message: `수정된 VENDOR 송신 완료: 성공 ${successCount}개, 실패 ${failCount}개`,
+      results
+    };
+    
+  } catch (error) {
+    console.error('❌ 수정된 VENDOR 송신 중 오류:', error);
+    return {
+      success: false,
+      message: error instanceof Error ? error.message : 'Unknown error'
+    };
+  }
+}
+
+// 테스트용 N건 송신 액션
+export async function sendNVendorsToMDG(
+  count: number, 
+  startFrom: number = 0
+): Promise<{
+  success: boolean;
+  message: string;
+  results?: Array<{ vendorCode: string; success: boolean; error?: string }>;
+}> {
+  try {
+    if (count <= 0) {
+      return {
+        success: false,
+        message: '송신할 건수는 1 이상이어야 합니다.'
+      };
+    }
+    
+    console.log(`🧪 테스트용 VENDOR 송신: ${count}건 (${startFrom}번째부터)`);
+    
+    // N건의 VENDOR 코드 조회
+    const vendors = await db
+      .select({ VNDRCD: VENDOR_MASTER_BP_HEADER.VNDRCD })
+      .from(VENDOR_MASTER_BP_HEADER)
+      .limit(count)
+      .offset(startFrom);
+    
+    const vendorCodes = vendors.map(v => v.VNDRCD);
+    
+    if (vendorCodes.length === 0) {
+      return {
+        success: false,
+        message: `${startFrom}번째부터 ${count}건의 VENDOR 데이터가 없습니다.`
+      };
+    }
+    
+    console.log(`📋 테스트 대상 VENDOR ${vendorCodes.length}개:`, vendorCodes);
+    
+    // 송신 실행
+    const result = await sendVendorMasterToMDG(vendorCodes);
+    
+    console.log(`🧪 테스트 송신 완료: ${vendorCodes.length}개 처리`);
+    
+    return {
+      ...result,
+      message: `테스트 송신 완료 (${vendorCodes.length}개): ${result.message}`
+    };
+    
+  } catch (error) {
+    console.error('❌ 테스트 송신 중 오류:', error);
+    return {
+      success: false,
+      message: error instanceof Error ? error.message : 'Unknown error'
+    };
+  }
+}
+
+// 최신 수정된 N건 송신 액션 (최근 수정 순)
+export async function sendRecentModifiedVendorsToMDG(
+  count: number = 5
+): Promise<{
+  success: boolean;
+  message: string;
+  results?: Array<{ vendorCode: string; success: boolean; error?: string }>;
+}> {
+  try {
+    console.log(`🕒 최근 수정된 VENDOR ${count}건 조회 중...`);
+    
+    // 최근 수정된 순으로 N건 조회
+    const recentVendors = await db
+      .select({ 
+        VNDRCD: VENDOR_MASTER_BP_HEADER.VNDRCD,
+        updatedAt: VENDOR_MASTER_BP_HEADER.updatedAt
+      })
+      .from(VENDOR_MASTER_BP_HEADER)
+      .where(
+        // 수정된 항목만 (updatedAt != createdAt)
+        sql`EXTRACT(EPOCH FROM ${VENDOR_MASTER_BP_HEADER.updatedAt}) - EXTRACT(EPOCH FROM ${VENDOR_MASTER_BP_HEADER.createdAt}) > 1`
+      )
+      .orderBy(desc(VENDOR_MASTER_BP_HEADER.updatedAt))
+      .limit(count);
+    
+    const vendorCodes = recentVendors.map(v => v.VNDRCD);
+    
+    if (vendorCodes.length === 0) {
+      return {
+        success: true,
+        message: '최근 수정된 VENDOR 데이터가 없습니다.'
+      };
+    }
+    
+    console.log(`📋 최근 수정된 VENDOR ${vendorCodes.length}개:`, 
+      recentVendors.map(v => `${v.VNDRCD}(${v.updatedAt?.toISOString()})`));
+    
+    // 송신 실행
+    const result = await sendVendorMasterToMDG(vendorCodes);
+    
+    console.log(`🕒 최근 수정 데이터 송신 완료`);
+    
+    return {
+      ...result,
+      message: `최근 수정된 ${vendorCodes.length}개 송신 완료: ${result.message}`
+    };
+    
+  } catch (error) {
+    console.error('❌ 최근 수정 데이터 송신 중 오류:', error);
+    return {
+      success: false,
+      message: error instanceof Error ? error.message : 'Unknown error'
+    };
+  }
+}
+
+// 통계 조회 유틸리티 함수
+export async function getVendorSendStatistics(): Promise<{
+  total: number;
+  modified: number;
+  lastModified?: Date;
+  oldestUnmodified?: Date;
+}> {
+  try {
+    const [totalResult] = await db
+      .select({ count: sql<number>`count(*)` })
+      .from(VENDOR_MASTER_BP_HEADER);
+    
+    const [modifiedResult] = await db
+      .select({ count: sql<number>`count(*)` })
+      .from(VENDOR_MASTER_BP_HEADER)
+      .where(
+        sql`EXTRACT(EPOCH FROM ${VENDOR_MASTER_BP_HEADER.updatedAt}) - EXTRACT(EPOCH FROM ${VENDOR_MASTER_BP_HEADER.createdAt}) > 1`
+      );
+    
+    const [lastModifiedResult] = await db
+      .select({ updatedAt: VENDOR_MASTER_BP_HEADER.updatedAt })
+      .from(VENDOR_MASTER_BP_HEADER)
+      .where(
+        sql`EXTRACT(EPOCH FROM ${VENDOR_MASTER_BP_HEADER.updatedAt}) - EXTRACT(EPOCH FROM ${VENDOR_MASTER_BP_HEADER.createdAt}) > 1`
+      )
+      .orderBy(desc(VENDOR_MASTER_BP_HEADER.updatedAt))
+      .limit(1);
+    
+    const [oldestUnmodifiedResult] = await db
+      .select({ createdAt: VENDOR_MASTER_BP_HEADER.createdAt })
+      .from(VENDOR_MASTER_BP_HEADER)
+      .where(
+        sql`EXTRACT(EPOCH FROM ${VENDOR_MASTER_BP_HEADER.updatedAt}) - EXTRACT(EPOCH FROM ${VENDOR_MASTER_BP_HEADER.createdAt}) <= 1`
+      )
+      .orderBy(VENDOR_MASTER_BP_HEADER.createdAt)
+      .limit(1);
+    
+    return {
+      total: totalResult.count,
+      modified: modifiedResult.count,
+      lastModified: lastModifiedResult?.updatedAt || undefined,
+      oldestUnmodified: oldestUnmodifiedResult?.createdAt || undefined
+    };
+    
+  } catch (error) {
+    console.error('통계 조회 실패:', error);
+    throw error;
+  }
+}