path: root/app/api/(S-ERP)/(MDG)/IF_MDZ_EVCP_VENDOR_MASTER/route.ts

import { NextRequest } from "next/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 {
  ToXMLFields,
  serveWsdl,
  createXMLParser,
  extractRequestData,
  convertXMLToDBData,
  processNestedArray,
  createErrorResponse,
  createSuccessResponse,
  withSoapLogging
} from "@/lib/soap/utils";
import {
  bulkUpsert,
  bulkReplaceSubTableData
} from "@/lib/soap/batch-utils";

// 스키마에서 직접 타입 추론
type VendorHeaderData = typeof VENDOR_MASTER_BP_HEADER.$inferInsert;
type AddressData = typeof VENDOR_MASTER_BP_HEADER_ADDRESS.$inferInsert;
type AdEmailData = typeof VENDOR_MASTER_BP_HEADER_ADDRESS_AD_EMAIL.$inferInsert;
type AdFaxData = typeof VENDOR_MASTER_BP_HEADER_ADDRESS_AD_FAX.$inferInsert;
type AdPostalData = typeof VENDOR_MASTER_BP_HEADER_ADDRESS_AD_POSTAL.$inferInsert;
type AdTelData = typeof VENDOR_MASTER_BP_HEADER_ADDRESS_AD_TEL.$inferInsert;
type AdUrlData = typeof VENDOR_MASTER_BP_HEADER_ADDRESS_AD_URL.$inferInsert;
type BpTaxnumData = typeof VENDOR_MASTER_BP_HEADER_BP_TAXNUM.$inferInsert;
type BpVengenData = typeof VENDOR_MASTER_BP_HEADER_BP_VENGEN.$inferInsert;
type BpCompnyData = typeof VENDOR_MASTER_BP_HEADER_BP_VENGEN_BP_COMPNY.$inferInsert;
type BpWhtaxData = typeof VENDOR_MASTER_BP_HEADER_BP_VENGEN_BP_COMPNY_BP_WHTAX.$inferInsert;
type BpPorgData = typeof VENDOR_MASTER_BP_HEADER_BP_VENGEN_BP_PORG.$inferInsert;
type ZvpfnData = typeof VENDOR_MASTER_BP_HEADER_BP_VENGEN_BP_PORG_ZVPFN.$inferInsert;

// XML 구조 타입
type VendorHeaderXML = ToXMLFields<Omit<VendorHeaderData, 'id' | 'createdAt' | 'updatedAt'>> & {
  ADDRESS?: AddressXML[];
  BP_TAXNUM?: BpTaxnumXML[];
  BP_VENGEN?: BpVengenXML[];
};

type AddressXML = ToXMLFields<Omit<AddressData, 'id' | 'createdAt' | 'updatedAt' | 'VNDRCD'>> & {
  AD_EMAIL?: AdEmailXML[];
  AD_FAX?: AdFaxXML[];
  AD_POSTAL?: AdPostalXML[];
  AD_TEL?: AdTelXML[];
  AD_URL?: AdUrlXML[];
};

type AdEmailXML = ToXMLFields<Omit<AdEmailData, 'id' | 'createdAt' | 'updatedAt' | 'VNDRCD'>>;
type AdFaxXML = ToXMLFields<Omit<AdFaxData, 'id' | 'createdAt' | 'updatedAt' | 'VNDRCD'>>;
type AdPostalXML = ToXMLFields<Omit<AdPostalData, 'id' | 'createdAt' | 'updatedAt' | 'VNDRCD'>>;
type AdTelXML = ToXMLFields<Omit<AdTelData, 'id' | 'createdAt' | 'updatedAt' | 'VNDRCD'>>;
type AdUrlXML = ToXMLFields<Omit<AdUrlData, 'id' | 'createdAt' | 'updatedAt' | 'VNDRCD'>>;

type BpTaxnumXML = ToXMLFields<Omit<BpTaxnumData, 'id' | 'createdAt' | 'updatedAt' | 'VNDRCD'>>;

type BpVengenXML = ToXMLFields<Omit<BpVengenData, 'id' | 'createdAt' | 'updatedAt' | 'VNDRCD'>> & {
  BP_COMPNY?: BpCompnyXML[];
  BP_PORG?: BpPorgXML[];
};

type BpCompnyXML = ToXMLFields<Omit<BpCompnyData, 'id' | 'createdAt' | 'updatedAt' | 'VNDRCD'>> & {
  BP_WHTAX?: BpWhtaxXML[];
};

type BpWhtaxXML = ToXMLFields<Omit<BpWhtaxData, 'id' | 'createdAt' | 'updatedAt' | 'VNDRCD'>>;

type BpPorgXML = ToXMLFields<Omit<BpPorgData, 'id' | 'createdAt' | 'updatedAt' | 'VNDRCD'>> & {
  ZVPFN?: ZvpfnXML[];
};

type ZvpfnXML = ToXMLFields<Omit<ZvpfnData, 'id' | 'createdAt' | 'updatedAt' | 'VNDRCD'>>;

// 처리된 데이터 구조
interface ProcessedVendorData {
  vendorHeader: VendorHeaderData;
  addresses: AddressData[];
  adEmails: AdEmailData[];
  adFaxes: AdFaxData[];
  adPostals: AdPostalData[];
  adTels: AdTelData[];
  adUrls: AdUrlData[];
  bpTaxnums: BpTaxnumData[];
  bpVengens: BpVengenData[];
  bpCompnies: BpCompnyData[];
  bpWhtaxes: BpWhtaxData[];
  bpPorgs: BpPorgData[];
  zvpfns: ZvpfnData[];
}

export async function GET(request: NextRequest) {
  const url = new URL(request.url);
  if (url.searchParams.has('wsdl')) {
    return serveWsdl('IF_MDZ_EVCP_VENDOR_MASTER.wsdl');
  }
  
  return new Response('Method Not Allowed', { status: 405 });
}

export async function POST(request: NextRequest) {
  const url = new URL(request.url);
  if (url.searchParams.has('wsdl')) {
    return serveWsdl('IF_MDZ_EVCP_VENDOR_MASTER.wsdl');
  }

  const body = await request.text();
  
  return withSoapLogging(
    'INBOUND',
    'S-ERP',
    'IF_MDZ_EVCP_VENDOR_MASTER',
    body,
    async () => {
      console.log('🚀 VENDOR_MASTER 수신 시작, 데이터 길이:', body.length);

      const parser = createXMLParser([
        'BP_HEADER', 'ADDRESS', 'AD_EMAIL', 'AD_FAX', 'AD_POSTAL', 'AD_TEL', 'AD_URL',
        'BP_TAXNUM', 'BP_VENGEN', 'BP_COMPNY', 'BP_WHTAX', 'BP_PORG', 'ZVPFN'
      ]);
      
      const parsedData = parser.parse(body);
      console.log('XML root keys:', Object.keys(parsedData));
      
      const requestData = extractRequestData(parsedData, 'IF_MDZ_EVCP_VENDOR_MASTERReq');
      
      if (!requestData) {
        console.error('Could not find valid request data in the received payload');
        console.error('Received XML structure:', JSON.stringify(parsedData, null, 2));
        throw new Error('Missing request data - could not find IF_MDZ_EVCP_VENDOR_MASTERReq or BP_HEADER data');
      }
      
      console.log('Validating request data structure:', 
        `BP_HEADER: ${requestData.BP_HEADER ? 'found' : 'not found'}`
      );
      
      if (requestData.BP_HEADER && Array.isArray(requestData.BP_HEADER) && requestData.BP_HEADER.length > 0) {
        console.log('First BP_HEADER sample:', JSON.stringify(requestData.BP_HEADER[0], null, 2));
      }
      
      // XML 데이터를 DB 삽입 가능한 형태로 변환
      const processedVendors = transformVendorData(requestData.BP_HEADER as VendorHeaderXML[] || []);
      
      // 필수 필드 검증
      for (const vendorData of processedVendors) {
        if (!vendorData.vendorHeader.VNDRCD) {
          throw new Error('Missing required field: VNDRCD in vendor');
        }
      }
      
      // 데이터베이스 저장
      await saveToDatabase(processedVendors);
      
      console.log(`🎉 처리 완료: ${processedVendors.length}개 벤더 데이터`);
      
      return createSuccessResponse('http://60.101.108.100/api/IF_MDZ_EVCP_VENDOR_MASTER/');
    }
  ).catch(error => {
    return createErrorResponse(error);
  });
}

// XML 데이터를 DB 삽입 가능한 형태로 변환
/**
 * VENDOR 마스터 데이터 변환 함수
 * 
 * 데이터 처리 아키텍처:
 * 1. 최상위 테이블 (VENDOR_MASTER_BP_HEADER)
 *    - VNDRCD가 unique 필드로 충돌 시 upsert 처리
 * 
 * 2. 하위 테이블들 (ADDRESS, BP_TAXNUM, BP_VENGEN 등)
 *    - FK(VNDRCD)로 연결
 *    - 별도 필수 필드 없음 (스키마에서 notNull() 제거 예정)
 *    - 전체 데이터셋 기반 삭제 후 재삽입 처리
 * 
 * 3. 중첩 하위 테이블들 (AD_EMAIL, AD_FAX, BP_COMPNY 등)
 *    - 동일하게 FK(VNDRCD)로 연결
 *    - 전체 데이터셋 기반 처리
 * 
 * @param vendorHeaderData XML에서 파싱된 VENDOR 헤더 데이터
 * @returns 처리된 VENDOR 데이터 구조
 */
function transformVendorData(vendorHeaderData: VendorHeaderXML[]): ProcessedVendorData[] {
  if (!vendorHeaderData || !Array.isArray(vendorHeaderData)) {
    return [];
  }
  
  return vendorHeaderData.map(vendorHeader => {
    const vndrcdKey = vendorHeader.VNDRCD || '';
    const fkData = { VNDRCD: vndrcdKey };
    
    // 1단계: VENDOR_HEADER (루트 - unique 필드: VNDRCD)
    const vendorHeaderConverted = convertXMLToDBData<VendorHeaderData>(
      vendorHeader as Record<string, string | undefined>,
      fkData
    );
    
    // 2단계: 직속 하위 테이블들 (FK: VNDRCD)
    const addresses = processNestedArray(
      vendorHeader.ADDRESS,
      (addr) => convertXMLToDBData<AddressData>(addr as Record<string, string | undefined>, fkData),
      fkData
    );
    
    const bpTaxnums = processNestedArray(
      vendorHeader.BP_TAXNUM,
      (item) => convertXMLToDBData<BpTaxnumData>(item as Record<string, string | undefined>, fkData),
      fkData
    );
    
    const bpVengens = processNestedArray(
      vendorHeader.BP_VENGEN,
      (vengen) => convertXMLToDBData<BpVengenData>(vengen as Record<string, string | undefined>, fkData),
      fkData
    );
    
    // 3단계: ADDRESS의 하위 테이블들 (FK: VNDRCD)
    const adEmails = vendorHeader.ADDRESS?.flatMap(addr => 
      processNestedArray(addr.AD_EMAIL, (item) => 
        convertXMLToDBData<AdEmailData>(item as Record<string, string | undefined>, fkData), fkData)
    ) || [];
    
    const adFaxes = vendorHeader.ADDRESS?.flatMap(addr => 
      processNestedArray(addr.AD_FAX, (item) => 
        convertXMLToDBData<AdFaxData>(item as Record<string, string | undefined>, fkData), fkData)
    ) || [];
    
    const adPostals = vendorHeader.ADDRESS?.flatMap(addr => 
      processNestedArray(addr.AD_POSTAL, (item) => 
        convertXMLToDBData<AdPostalData>(item as Record<string, string | undefined>, fkData), fkData)
    ) || [];
    
    const adTels = vendorHeader.ADDRESS?.flatMap(addr => 
      processNestedArray(addr.AD_TEL, (item) => 
        convertXMLToDBData<AdTelData>(item as Record<string, string | undefined>, fkData), fkData)
    ) || [];
    
    const adUrls = vendorHeader.ADDRESS?.flatMap(addr => 
      processNestedArray(addr.AD_URL, (item) => 
        convertXMLToDBData<AdUrlData>(item as Record<string, string | undefined>, fkData), fkData)
    ) || [];
    
    // 3단계: BP_VENGEN의 하위 테이블들 (FK: VNDRCD)
    const bpCompnies = vendorHeader.BP_VENGEN?.flatMap(vengen => 
      processNestedArray(vengen.BP_COMPNY, (item) => 
        convertXMLToDBData<BpCompnyData>(item as Record<string, string | undefined>, fkData), fkData)
    ) || [];
    
    const bpPorgs = vendorHeader.BP_VENGEN?.flatMap(vengen => 
      processNestedArray(vengen.BP_PORG, (item) => 
        convertXMLToDBData<BpPorgData>(item as Record<string, string | undefined>, fkData), fkData)
    ) || [];
    
    // 4단계: 더 깊은 중첩 테이블들 (FK: VNDRCD)
    const bpWhtaxes = vendorHeader.BP_VENGEN?.flatMap(vengen => 
      vengen.BP_COMPNY?.flatMap(compny => 
        processNestedArray(compny.BP_WHTAX, (item) => 
          convertXMLToDBData<BpWhtaxData>(item as Record<string, string | undefined>, fkData), fkData)
      ) || []
    ) || [];
    
    const zvpfns = vendorHeader.BP_VENGEN?.flatMap(vengen => 
      vengen.BP_PORG?.flatMap(porg => 
        processNestedArray(porg.ZVPFN, (item) => 
          convertXMLToDBData<ZvpfnData>(item as Record<string, string | undefined>, fkData), fkData)
      ) || []
    ) || [];
    
    return {
      vendorHeader: vendorHeaderConverted,
      addresses,
      adEmails,
      adFaxes,
      adPostals,
      adTels,
      adUrls,
      bpTaxnums,
      bpVengens,
      bpCompnies,
      bpWhtaxes,
      bpPorgs,
      zvpfns
    };
  });
}

// 데이터베이스 저장 함수
/**
 * 처리된 VENDOR 데이터를 데이터베이스에 저장
 * 
 * 저장 전략:
 * 1. 최상위 테이블: VNDRCD 기준 upsert (충돌 시 업데이트)
 * 2. 하위 테이블들: FK(VNDRCD) 기준 전체 삭제 후 재삽입
 *    - 송신 XML이 전체 데이터셋을 포함하므로 부분 업데이트 불필요
 *    - 데이터 일관성과 단순성 확보
 * 
 * @param processedVendors 변환된 VENDOR 데이터 배열
 */
async function saveToDatabase(processedVendors: ProcessedVendorData[]) {
  console.log(`데이터베이스(배치) 저장 시작: ${processedVendors.length}개 벤더 데이터`);
  try {
    await db.transaction(async (tx) => {
      // 1) 부모 테이블 데이터 준비
      const vendorHeaderRows = processedVendors
        .map((v) => v.vendorHeader)
        .filter((v): v is VendorHeaderData => !!v.VNDRCD);

      const vndrCds = vendorHeaderRows.map((v) => v.VNDRCD as string);

      // 2) 하위 테이블 데이터 평탄화
      const addresses = processedVendors.flatMap((v) => v.addresses);
      const adEmails = processedVendors.flatMap((v) => v.adEmails);
      const adFaxes = processedVendors.flatMap((v) => v.adFaxes);
      const adPostals = processedVendors.flatMap((v) => v.adPostals);
      const adTels = processedVendors.flatMap((v) => v.adTels);
      const adUrls = processedVendors.flatMap((v) => v.adUrls);
      const bpTaxnums = processedVendors.flatMap((v) => v.bpTaxnums);
      const bpVengens = processedVendors.flatMap((v) => v.bpVengens);
      const bpCompnies = processedVendors.flatMap((v) => v.bpCompnies);
      const bpWhtaxes = processedVendors.flatMap((v) => v.bpWhtaxes);
      const bpPorgs = processedVendors.flatMap((v) => v.bpPorgs);
      const zvpfns = processedVendors.flatMap((v) => v.zvpfns);

      // 3) 부모 테이블 UPSERT (배치)
      await bulkUpsert(tx, VENDOR_MASTER_BP_HEADER, vendorHeaderRows, 'VNDRCD');

      // 4) 하위 테이블 교체 (배치) 
      // 정의서에서 하위 테이블 키를 알려주지 않았고, 정시템도 모른다고 하므로 최상위 테이블 PK 기준 전체 삭제 후 삽입
      await Promise.all([
        bulkReplaceSubTableData(tx, VENDOR_MASTER_BP_HEADER_ADDRESS, addresses, VENDOR_MASTER_BP_HEADER_ADDRESS.VNDRCD, vndrCds),
        bulkReplaceSubTableData(tx, VENDOR_MASTER_BP_HEADER_BP_TAXNUM, bpTaxnums, VENDOR_MASTER_BP_HEADER_BP_TAXNUM.VNDRCD, vndrCds),
        bulkReplaceSubTableData(tx, VENDOR_MASTER_BP_HEADER_BP_VENGEN, bpVengens, VENDOR_MASTER_BP_HEADER_BP_VENGEN.VNDRCD, vndrCds),

        bulkReplaceSubTableData(tx, VENDOR_MASTER_BP_HEADER_ADDRESS_AD_EMAIL, adEmails, VENDOR_MASTER_BP_HEADER_ADDRESS_AD_EMAIL.VNDRCD, vndrCds),
        bulkReplaceSubTableData(tx, VENDOR_MASTER_BP_HEADER_ADDRESS_AD_FAX, adFaxes, VENDOR_MASTER_BP_HEADER_ADDRESS_AD_FAX.VNDRCD, vndrCds),
        bulkReplaceSubTableData(tx, VENDOR_MASTER_BP_HEADER_ADDRESS_AD_POSTAL, adPostals, VENDOR_MASTER_BP_HEADER_ADDRESS_AD_POSTAL.VNDRCD, vndrCds),
        bulkReplaceSubTableData(tx, VENDOR_MASTER_BP_HEADER_ADDRESS_AD_TEL, adTels, VENDOR_MASTER_BP_HEADER_ADDRESS_AD_TEL.VNDRCD, vndrCds),
        bulkReplaceSubTableData(tx, VENDOR_MASTER_BP_HEADER_ADDRESS_AD_URL, adUrls, VENDOR_MASTER_BP_HEADER_ADDRESS_AD_URL.VNDRCD, vndrCds),

        bulkReplaceSubTableData(tx, VENDOR_MASTER_BP_HEADER_BP_VENGEN_BP_COMPNY, bpCompnies, VENDOR_MASTER_BP_HEADER_BP_VENGEN_BP_COMPNY.VNDRCD, vndrCds),
        bulkReplaceSubTableData(tx, VENDOR_MASTER_BP_HEADER_BP_VENGEN_BP_COMPNY_BP_WHTAX, bpWhtaxes, VENDOR_MASTER_BP_HEADER_BP_VENGEN_BP_COMPNY_BP_WHTAX.VNDRCD, vndrCds),
        bulkReplaceSubTableData(tx, VENDOR_MASTER_BP_HEADER_BP_VENGEN_BP_PORG, bpPorgs, VENDOR_MASTER_BP_HEADER_BP_VENGEN_BP_PORG.VNDRCD, vndrCds),
        bulkReplaceSubTableData(tx, VENDOR_MASTER_BP_HEADER_BP_VENGEN_BP_PORG_ZVPFN, zvpfns, VENDOR_MASTER_BP_HEADER_BP_VENGEN_BP_PORG_ZVPFN.VNDRCD, vndrCds),
      ]);
    });

    console.log(`데이터베이스(배치) 저장 완료: ${processedVendors.length}개 벤더`);
    return true;
  } catch (error) {
    console.error('데이터베이스 저장 중 오류 발생:', error);
    throw error;
  }
}