'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 { CSV_FIELDS } from './csv-fields';
import { sendSoapXml, type SoapSendConfig, type SoapLogInfo } from "@/lib/soap/sender";
import { debugLog, debugError, debugSuccess } from '@/lib/debug-utils';

// SAP XI 엔드포인트 URL
const MDG_ENDPOINT_URL = "http://shii8dvddb01.hec.serp.shi.samsung.net:50000/sap/xi/engine?type=entry&version=3.0&Sender.Service=P2038_Q&Interface=http%3A%2F%2Fshi.samsung.co.kr%2FP2_MD%2FMDZ%5EP2MD3007_AO&QualityOfService=ExactlyOnce";

// CSV 필드 정의는 ./csv-fields.ts에서 import

// SOAP Body Content 생성 함수 (WSDL 요구사항에 맞게 수정)
function createVendorMasterSoapBodyContent(supplierMaster: Record<string, string>): Record<string, unknown> {
  return {
    'p1:MT_P2MD3007_S': {  // WSDL에서 사용하는 p1 접두사 적용
      'SUPPLIER_MASTER': supplierMaster
    }
  };
}

// MDG로 VENDOR 마스터 SOAP XML 전송하는 함수 (sender.ts 사용)
async function sendVendorMasterToMDGInternal(supplierMaster: Record<string, string>): Promise<{
  success: boolean;
  message: string;
  responseText?: string;
  requestXml?: string;
  responseHeaders?: Record<string, string>;
  statusCode?: number;
  requestHeaders?: Record<string, string>;
}> {
  try {
    // SOAP Body Content 생성
    const soapBodyContent = createVendorMasterSoapBodyContent(supplierMaster);
    
    // SOAP 전송 설정
    const config: SoapSendConfig = {
      endpoint: MDG_ENDPOINT_URL,
      envelope: soapBodyContent,
      soapAction: 'http://sap.com/xi/WebService/soap1.1',
      timeout: 60000, // VENDOR 마스터 전송은 60초 타임아웃
      retryCount: 3,
      retryDelay: 2000,
      namespace: 'http://shi.samsung.co.kr/P2_MD/MDZ', // MDG 전용 네임스페이스
      prefix: 'p1' // WSDL에서 사용하는 p1 접두사
    };

    // 로그 정보
    const logInfo: SoapLogInfo = {
      direction: 'OUTBOUND',
      system: 'S-ERP MDG',
      interface: 'IF_MDZ_EVCP_VENDOR_MASTER'
    };

    debugLog(`📤 VENDOR 마스터 전송 시작`);
    debugLog(`🔍 SUPPLIER_MASTER 데이터: ${Object.keys(supplierMaster).length}개 필드`);

    // SOAP XML 전송
    const result = await sendSoapXml(config, logInfo);
    
    // 응답 상세 정보 로깅
    debugLog('📊 SOAP 전송 결과 상세:', {
      success: result.success,
      message: result.message,
      statusCode: result.statusCode,
      hasResponseText: !!result.responseText,
      responseTextLength: result.responseText?.length,
      hasRequestXml: !!result.requestXml,
      requestXmlLength: result.requestXml?.length,
      headers: result.headers
    });
    
    if (result.success) {
      debugSuccess(`✅ VENDOR 마스터 전송 성공`);
    } else {
      debugError(`❌ VENDOR 마스터 전송 실패, 오류: ${result.message}`);
    }

    return {
      success: result.success,
      message: result.success ? '전송 성공' : result.message,
      responseText: result.responseText,
      requestXml: result.requestXml,
      responseHeaders: result.headers,
      statusCode: result.statusCode,
      requestHeaders: result.requestHeaders
    };

  } catch (error) {
    debugError('❌ VENDOR 마스터 전송 중 오류 발생:', error);
    return {
      success: false,
      message: error instanceof Error ? error.message : 'Unknown error'
    };
  }
}

// 데이터베이스에서 VENDOR 데이터 조회
async function fetchVendorData(vendorCode: string) {
  try {
    const [vendorHeader] = await db
      .select()
      .from(VENDOR_MASTER_BP_HEADER)
      .where(eq(VENDOR_MASTER_BP_HEADER.VNDRCD, vendorCode))
      .limit(1);

    if (!vendorHeader) {
      return null;
    }

    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) {
    debugError(`VENDOR ${vendorCode} 데이터 조회 실패:`, error);
    throw error;
  }
}

// SUPPLIER_MASTER 데이터 생성
function buildSupplierMasterData(vendorData: NonNullable<Awaited<ReturnType<typeof fetchVendorData>>>) {
  const { vendorHeader, addresses, adFaxes, adPostals, adTels, bpTaxnums, bpVengens } = vendorData;

  const mapping: Record<string, string | undefined> = {
    BP_HEADER: vendorHeader?.VNDRCD,
    ZZSRMCD: 'EVCP',
    TITLE: '',
    BU_SORT1: adPostals[0]?.VNDRNM_ABRV_1 ?? undefined,
    NAME_ORG1: adPostals[0]?.VNDRNM_1 ?? undefined,
    KTOKK: bpVengens[0]?.ACNT_GRP ?? undefined,
    MASTERFLAG: 'X',
    IBND_TYPE: 'U',
    ADDRNO: addresses[0]?.ADDRNO,
    AD_NATION: adPostals[0]?.INTL_ADR_VER_ID ?? undefined,
    COUNTRY: adPostals[0]?.NTN_CD ?? undefined,
    LANGU_COM: adPostals[0]?.LANG_KEY ?? undefined,
    POST_COD1: adPostals[0]?.CITY_ZIP_NO ?? undefined,
    CITY1: adPostals[0]?.VNDRNM_1 ?? undefined,
    MC_STREET: adPostals[0]?.ADR_1 ?? undefined,
    AD_CONSNO: '001',
    T_COUNTRY: adTels[0]?.NTN_CD ?? 'KR',
    F_COUNTRY: adFaxes[0]?.NTN_CD ?? 'KR',
    BP_TX_TYP: bpTaxnums[0]?.TX_NO_CTG ?? 'KR2',
    TAXNUM: bpVengens[0]?.VAT_REG_NO ?? undefined,
  };

  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 supplierMaster: Record<string, string> = {};
  uniqueFields.forEach(f => {
    supplierMaster[f.field] = mapping[f.field] ?? '';
  });

  return supplierMaster;
}

// ========================================
// 메인 송신 함수들
// ========================================

// VENDOR 마스터 데이터를 MDG로 송신하는 액션
export async function sendVendorMasterToMDG(vendorCodes: string[]): Promise<{
  success: boolean;
  message: string;
  results?: Array<{ vendorCode: string; success: boolean; error?: string }>;
}> {
  try {
    debugLog(`🚀 VENDOR_MASTER 송신 시작: ${vendorCodes.length}개 벤더`);

    const results: Array<{ vendorCode: string; success: boolean; error?: string }> = [];

    for (const vendorCode of vendorCodes) {
      try {
        debugLog(`📤 VENDOR ${vendorCode} 데이터 조회 중...`);

        const vendorData = await fetchVendorData(vendorCode);

        if (!vendorData) {
          results.push({
            vendorCode,
            success: false,
            error: 'VENDOR 데이터를 찾을 수 없습니다.'
          });
          continue;
        }

        const supplierMaster = buildSupplierMasterData(vendorData);
        debugLog(`📄 VENDOR ${vendorCode} 데이터 생성 완료`);

        const result = await sendVendorMasterToMDGInternal(supplierMaster);

        if (result.success) {
          debugSuccess(`✅ VENDOR ${vendorCode} MDG 전송 성공`);
          results.push({
            vendorCode,
            success: true
          });
        } else {
          debugError(`❌ VENDOR ${vendorCode} 전송 실패: ${result.message}`);
          results.push({
            vendorCode,
            success: false,
            error: result.message
          });
        }

      } catch (error) {
        debugError(`❌ 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;

    debugSuccess(`🎉 VENDOR_MASTER 송신 완료: 성공 ${successCount}개, 실패 ${failCount}개`);

    return {
      success: failCount === 0,
      message: `전송 완료: 성공 ${successCount}개, 실패 ${failCount}개`,
      results
    };

  } catch (error) {
    debugError('❌ VENDOR_MASTER 송신 중 전체 오류 발생:', error);
    return {
      success: false,
      message: error instanceof Error ? error.message : 'Unknown error'
    };
  }
}

// 필수 필드 검증 함수
function validateMandatoryFields(formData: Record<string, string>): {
  isValid: boolean;
  missingFields: string[];
  errorMessage: string;
} {
  const missingFields: string[] = [];

  // CSV_FIELDS에서 mandatory가 true인 필드들만 필수 필드로 체크
  CSV_FIELDS.forEach(field => {
    if (field.mandatory) {
      const value = formData[field.field];
      if (!value || value.trim() === '') {
        missingFields.push(field.field);
      }
    }
  });

  if (missingFields.length > 0) {
    return {
      isValid: false,
      missingFields,
      errorMessage: `필수 필드가 누락되었습니다: ${missingFields.join(', ')}`
    };
  }

  return {
    isValid: true,
    missingFields: [],
    errorMessage: ''
  };
}

// 테스트용 폼 데이터 송신 함수
export async function sendTestVendorDataToMDG(formData: Record<string, string>): Promise<{
  success: boolean;
  message: string;
  responseData?: unknown;
  generatedXML?: string;
  responseHeaders?: Record<string, string>;
  statusCode?: number;
  requestHeaders?: Record<string, string>;
}> {
  try {
    debugLog('🚀 테스트용 VENDOR 데이터 송신 시작');

    // 필수 필드 검증
    const validation = validateMandatoryFields(formData);
    if (!validation.isValid) {
      debugError('❌ 필수 필드 누락:', validation.missingFields);
      return {
        success: false,
        message: validation.errorMessage,
        responseData: undefined,
        generatedXML: undefined
      };
    }

    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 supplierMaster: Record<string, string> = {};
    uniqueFields.forEach(f => {
      supplierMaster[f.field] = formData[f.field] ?? '';
    });

    debugLog('📄 SUPPLIER_MASTER 데이터 생성 완료');

    const result = await sendVendorMasterToMDGInternal(supplierMaster);

    // 응답 정보 상세 로깅
    debugLog('📊 테스트 송신 결과:', {
      success: result.success,
      statusCode: result.statusCode,
      responseHeaders: result.responseHeaders,
      hasResponseText: !!result.responseText,
      responseTextLength: result.responseText?.length,
      hasRequestXml: !!result.requestXml,
      requestXmlLength: result.requestXml?.length
    });

    return {
      success: result.success,
      message: result.success ? '테스트 송신이 완료되었습니다.' : result.message,
      responseData: result.responseText,
      generatedXML: result.requestXml,
      responseHeaders: result.responseHeaders,
      statusCode: result.statusCode,
      requestHeaders: result.requestHeaders
    };

  } catch (error) {
    debugError('❌ 테스트 송신 실패:', error);
    return {
      success: false,
      message: error instanceof Error ? error.message : 'Unknown error',
      responseData: undefined,
      generatedXML: undefined
    };
  }
}

// ========================================
// 유틸리티 함수들
// ========================================

// 특정 VENDOR만 송신
export async function sendSingleVendorToMDG(vendorCode: string) {
  return await sendVendorMasterToMDG([vendorCode]);
}

// 모든 VENDOR 송신 (주의: 대량 데이터 처리)
export async function sendAllVendorsToMDG() {
  try {
    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 데이터가 없습니다.'
      };
    }

    debugLog(`⚠️ 전체 VENDOR 송신 요청: ${vendorCodes.length}개`);

    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);
      debugLog(`📦 배치 ${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) {
    debugError('전체 VENDOR 송신 중 오류:', error);
    return {
      success: false,
      message: error instanceof Error ? error.message : 'Unknown error'
    };
  }
}

// 수정된 VENDOR만 송신
export async function sendModifiedVendorsToMDG(): Promise<{
  success: boolean;
  message: string;
  results?: Array<{ vendorCode: string; success: boolean; error?: string }>;
}> {
  try {
    debugLog('🔍 수정된 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(
        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) {
      debugLog('📝 수정된 VENDOR 데이터가 없습니다.');
      return {
        success: true,
        message: '수정된 VENDOR 데이터가 없습니다.'
      };
    }

    debugLog(`📋 수정된 VENDOR ${vendorCodes.length}개 발견:`, vendorCodes);

    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);
      debugLog(`📦 수정 데이터 배치 ${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;

    debugSuccess(`🎯 수정된 VENDOR 송신 완료: 성공 ${successCount}개, 실패 ${failCount}개`);

    return {
      success: failCount === 0,
      message: `수정된 VENDOR 송신 완료: 성공 ${successCount}개, 실패 ${failCount}개`,
      results
    };

  } catch (error) {
    debugError('❌ 수정된 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 이상이어야 합니다.'
      };
    }

    debugLog(`🧪 테스트용 VENDOR 송신: ${count}건 (${startFrom}번째부터)`);

    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 데이터가 없습니다.`
      };
    }

    debugLog(`📋 테스트 대상 VENDOR ${vendorCodes.length}개:`, vendorCodes);

    const result = await sendVendorMasterToMDG(vendorCodes);

    debugSuccess(`🧪 테스트 송신 완료: ${vendorCodes.length}개 처리`);

    return {
      ...result,
      message: `테스트 송신 완료 (${vendorCodes.length}개): ${result.message}`
    };

  } catch (error) {
    debugError('❌ 테스트 송신 중 오류:', 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 {
    debugLog(`🕒 최근 수정된 VENDOR ${count}건 조회 중...`);

    const recentVendors = await db
      .select({
        VNDRCD: VENDOR_MASTER_BP_HEADER.VNDRCD,
        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(count);

    const vendorCodes = recentVendors.map(v => v.VNDRCD);

    if (vendorCodes.length === 0) {
      return {
        success: true,
        message: '최근 수정된 VENDOR 데이터가 없습니다.'
      };
    }

    debugLog(`📋 최근 수정된 VENDOR ${vendorCodes.length}개:`,
      recentVendors.map(v => `${v.VNDRCD}(${v.updatedAt?.toISOString()})`));

    const result = await sendVendorMasterToMDG(vendorCodes);

    debugSuccess(`🕒 최근 수정 데이터 송신 완료`);

    return {
      ...result,
      message: `최근 수정된 ${vendorCodes.length}개 송신 완료: ${result.message}`
    };

  } catch (error) {
    debugError('❌ 최근 수정 데이터 송신 중 오류:', 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) {
    debugError('통계 조회 실패:', error);
    throw error;
  }
}