(김준회) MDG SOAP 수신 유틸리티 및 API 엔드포인트, 스키마

1 files changed, 243 insertions, 898 deletions

diff --git a/app/api/(S-ERP)/(MDG)/IF_MDZ_EVCP_MODEL_MASTER/route.ts b/app/api/(S-ERP)/(MDG)/IF_MDZ_EVCP_MODEL_MASTER/route.ts
index 6c73cf08..cb8de491 100644
--- a/app/api/(S-ERP)/(MDG)/IF_MDZ_EVCP_MODEL_MASTER/route.ts
+++ b/app/api/(S-ERP)/(MDG)/IF_MDZ_EVCP_MODEL_MASTER/route.ts
@@ -1,927 +1,272 @@
-import { XMLParser } from "fast-xml-parser";
-import { readFileSync } from "fs";
-import { NextRequest, NextResponse } from "next/server";
-import { join } from "path";
+import { NextRequest } from "next/server";
 import db from "@/db/db";
-import { MATL, DESC, PLNT, UNIT, CLASSASGN, CHARASGN } from "@/db/schema/MDG/modelMaster";
-import { eq } from "drizzle-orm";
-
-// 요청 데이터 인터페이스 정의
-interface RequestData {
-  materials: Material[];
-}
-
-// 애플리케이션 내부 데이터 모델 (XML 필드와 1:1 매핑)
-interface Material {
-  matnr?: string;        // Material Number
-  mbrsh?: string;        // Industry Sector
-  mtart?: string;        // Material Type
-  lvorm?: string;        // Deletion flag
-  meins?: string;        // Base Unit of Measure
-  matkl?: string;        // Material Group
-  bismt?: string;        // Old Material Number
-  spart?: string;        // Division
-  prdha?: string;        // Product Hierarchy
-  mstae?: string;        // Cross-plant Material Status
-  mstde?: string;        // Cross-distribution-chain Material Status
-  brgew?: string;        // Gross Weight
-  gewei?: string;        // Weight Unit
-  ntgew?: string;        // Net Weight
-  volum?: string;        // Volume
-  voleh?: string;        // Volume Unit
-  groes?: string;        // Size/dimensions
-  laeng?: string;        // Length
-  breit?: string;        // Width
-  hoehe?: string;        // Height
-  meabm?: string;        // Unit of Dimension
-  magrv?: string;        // Material Group: Packaging Materials
-  vhart?: string;        // Packaging Material Type
-  zzname?: string;       // Material Name (Custom)
-  zzspec?: string;       // Material Specification (Custom)
-  zzdesc?: string;       // Material Description (Custom)
-  zzmmtyp?: string;      // Material Type (Custom)
-  zzregdt?: string;      // Registration Date (Custom)
-  zzregtm?: string;      // Registration Time (Custom)
-  zzregus?: string;      // Registration User (Custom)
-  zzappdt?: string;      // Approval Date (Custom)
-  zzapptm?: string;      // Approval Time (Custom)
-  zzappus?: string;      // Approval User (Custom)
-  zzlamdt?: string;      // Last Modified Date (Custom)
-  zzlamtm?: string;      // Last Modified Time (Custom)
-  zzlamus?: string;      // Last Modified User (Custom)
-  zzprflg?: string;      // Process Flag (Custom)
-  zzdokar?: string;      // Document Type (Custom)
-  zzdoknr?: string;      // Document Number (Custom)
-  zzdoktl?: string;      // Document Part (Custom)
-  zzdokvr?: string;      // Document Version (Custom)
-  descriptions?: Description[];
-  plants?: Plant[];
-  units?: Unit[];
-  classAssignments?: ClassAssignment[];
-  characteristicAssignments?: CharacteristicAssignment[];
-}
-
-interface Description {
-  matnr?: string;        // Material Number
-  spras?: string;        // Language Key
-  maktx?: string;        // Material Description
-}
-
-interface Plant {
-  matnr?: string;        // Material Number
-  werks?: string;        // Plant
-  lvorm?: string;        // Deletion Flag
-  mmsta?: string;        // Plant-specific Material Status
-  mmstd?: string;        // Plant-specific Material Status Valid From
-  zzmtarp?: string;      // Custom Field
-  zzregdt?: string;      // Registration Date (Custom)
-  zzregtm?: string;      // Registration Time (Custom)
-  zzregus?: string;      // Registration User (Custom)
-  zzlamdt?: string;      // Last Modified Date (Custom)
-  zzlamtm?: string;      // Last Modified Time (Custom)
-  zzlamus?: string;      // Last Modified User (Custom)
-  zzprflg?: string;      // Process Flag (Custom)
-}
-
-interface Unit {
-  matnr?: string;        // Material Number
-  meinh?: string;        // Unit of Measure
-  umrez?: string;        // Numerator for Conversion to Base UoM
-  umren?: string;        // Denominator for Conversion to Base UoM
-  laeng?: string;        // Length
-  breit?: string;        // Width
-  hoehe?: string;        // Height
-  meabm?: string;        // Unit of Dimension
-  volum?: string;        // Volume
-  voleh?: string;        // Volume Unit
-  brgew?: string;        // Gross Weight
-  gewei?: string;        // Weight Unit
-}
-
-interface ClassAssignment {
-  matnr?: string;        // Material Number
-  class?: string;        // Class
-  klart?: string;        // Class Type
-}
-
-interface CharacteristicAssignment {
-  matnr?: string;        // Material Number
-  class?: string;        // Class
-  klart?: string;        // Class Type
-  atnam?: string;        // Characteristic Name
-  atwrt?: string;        // Characteristic Value
-  atflv?: string;        // Value From
-  atawe?: string;        // Value To
-  atflb?: string;        // Description
-  ataw1?: string;        // Additional Value
-  atbez?: string;        // Characteristic Description
-  atwtb?: string;        // Characteristic Value Description
-}
-
-// SOAP XML 데이터 구조 인터페이스
-// XML 기준 대문자 필드명 사용
-interface MatlXML {
-  MATNR?: string;
-  MBRSH?: string;
-  MTART?: string;
-  LVORM?: string;
-  MEINS?: string;
-  MATKL?: string;
-  BISMT?: string;
-  SPART?: string;
-  PRDHA?: string;
-  MSTAE?: string;
-  MSTDE?: string;
-  BRGEW?: string;
-  GEWEI?: string;
-  NTGEW?: string;
-  VOLUM?: string;
-  VOLEH?: string;
-  GROES?: string;
-  LAENG?: string;
-  BREIT?: string;
-  HOEHE?: string;
-  MEABM?: string;
-  MAGRV?: string;
-  VHART?: string;
-  ZZNAME?: string;
-  ZZSPEC?: string;
-  ZZDESC?: string;
-  ZZMMTYP?: string;
-  ZZREGDT?: string;
-  ZZREGTM?: string;
-  ZZREGUS?: string;
-  ZZAPPDT?: string;
-  ZZAPPTM?: string;
-  ZZAPPUS?: string;
-  ZZLAMDT?: string;
-  ZZLAMTM?: string;
-  ZZLAMUS?: string;
-  ZZPRFLG?: string;
-  ZZDOKAR?: string;
-  ZZDOKNR?: string;
-  ZZDOKTL?: string;
-  ZZDOKVR?: string;
+import { 
+  MODEL_MASTER_MATL, 
+  MODEL_MASTER_MATL_DESC, 
+  MODEL_MASTER_MATL_PLNT, 
+  MODEL_MASTER_MATL_UNIT, 
+  MODEL_MASTER_MATL_CLASSASGN, 
+  MODEL_MASTER_MATL_CHARASGN 
+} from "@/db/schema/MDG/mdg";
+import {
+  ToXMLFields,
+  serveWsdl,
+  createXMLParser,
+  extractRequestData,
+  convertXMLToDBData,
+  processNestedArray,
+  createErrorResponse,
+  createSuccessResponse,
+  replaceSubTableData,
+  withSoapLogging
+} from "../utils";
+
+// 스키마에서 직접 타입 추론 (Insert와 XML을 통합)
+type MatlData = typeof MODEL_MASTER_MATL.$inferInsert;
+type MatlDescData = typeof MODEL_MASTER_MATL_DESC.$inferInsert;
+type MatlPlntData = typeof MODEL_MASTER_MATL_PLNT.$inferInsert;
+type MatlUnitData = typeof MODEL_MASTER_MATL_UNIT.$inferInsert;
+type MatlClassAsgnData = typeof MODEL_MASTER_MATL_CLASSASGN.$inferInsert;
+type MatlCharAsgnData = typeof MODEL_MASTER_MATL_CHARASGN.$inferInsert;
+
+// XML에서 받는 데이터 구조 (스키마와 동일한 구조, string 타입)
+type MatlXML = ToXMLFields<Omit<MatlData, 'id' | 'createdAt' | 'updatedAt'>> & {
   DESC?: DescXML[];
   PLNT?: PlntXML[];
   UNIT?: UnitXML[];
   CLASSASGN?: ClassAsgnXML[];
   CHARASGN?: CharAsgnXML[];
-}
-
-interface DescXML {
-  MATNR?: string;
-  SPRAS?: string;
-  MAKTX?: string;
-}
-
-interface PlntXML {
-  MATNR?: string;
-  WERKS?: string;
-  LVORM?: string;
-  MMSTA?: string;
-  MMSTD?: string;
-  ZZMTARP?: string;
-  ZZREGDT?: string;
-  ZZREGTM?: string;
-  ZZREGUS?: string;
-  ZZLAMDT?: string;
-  ZZLAMTM?: string;
-  ZZLAMUS?: string;
-  ZZPRFLG?: string;
-}
-
-interface UnitXML {
-  MATNR?: string;
-  MEINH?: string;
-  UMREZ?: string;
-  UMREN?: string;
-  LAENG?: string;
-  BREIT?: string;
-  HOEHE?: string;
-  MEABM?: string;
-  VOLUM?: string;
-  VOLEH?: string;
-  BRGEW?: string;
-  GEWEI?: string;
-}
-
-interface ClassAsgnXML {
-  MATNR?: string;
-  CLASS?: string;
-  KLART?: string;
-}
-
-interface CharAsgnXML {
-  MATNR?: string;
-  CLASS?: string;
-  KLART?: string;
-  ATNAM?: string;
-  ATWRT?: string;
-  ATFLV?: string;
-  ATAWE?: string;
-  ATFLB?: string;
-  ATAW1?: string;
-  ATBEZ?: string;
-  ATWTB?: string;
-}
-
-// SOAP Body에 대한 데이터 타입 정의
-interface SoapBodyData {
-  [key: string]: unknown;
-  IF_MDZ_EVCP_MODEL_MASTERReq?: Record<string, unknown>;
-  'tns:IF_MDZ_EVCP_MODEL_MASTERReq'?: Record<string, unknown>;
-  'ns1:IF_MDZ_EVCP_MODEL_MASTERReq'?: Record<string, unknown>;
-  'p0:IF_MDZ_EVCP_MODEL_MASTERReq'?: Record<string, unknown>;
-  MATL?: MatlXML[];
-}
-
-function serveWsdl() {
-    try {
-        const wsdlPath = join(process.cwd(), 'public', 'wsdl', 'IF_MDZ_EVCP_MODEL_MASTER.wsdl');
-        const wsdlContent = readFileSync(wsdlPath, 'utf-8');
-
-        return new NextResponse(wsdlContent, {
-            headers: {
-                'Content-Type': 'text/xml; charset=utf-8',
-            },
-        });
-    } catch (error) {
-        console.error('Failed to read WSDL file:', error);
-        return new NextResponse('WSDL file not found', { status: 404 });
-    }
+};
+
+type DescXML = ToXMLFields<Omit<MatlDescData, 'id' | 'createdAt' | 'updatedAt'>>;
+type PlntXML = ToXMLFields<Omit<MatlPlntData, 'id' | 'createdAt' | 'updatedAt'>>;
+type UnitXML = ToXMLFields<Omit<MatlUnitData, 'id' | 'createdAt' | 'updatedAt'>>;
+type ClassAsgnXML = ToXMLFields<Omit<MatlClassAsgnData, 'id' | 'createdAt' | 'updatedAt'>>;
+type CharAsgnXML = ToXMLFields<Omit<MatlCharAsgnData, 'id' | 'createdAt' | 'updatedAt'>>;
+
+// 처리된 데이터 구조 (Insert와 동일)
+interface ProcessedMaterialData {
+  material: MatlData;
+  descriptions: MatlDescData[];
+  plants: MatlPlntData[];
+  units: MatlUnitData[];
+  classAssignments: MatlClassAsgnData[];
+  characteristicAssignments: MatlCharAsgnData[];
 }
 
 export async function GET(request: NextRequest) {
-    const url = new URL(request.url);
-    if (url.searchParams.has('wsdl')) {
-        return serveWsdl();
-    }
-    
-    return new NextResponse('Method Not Allowed', { status: 405 });
+  const url = new URL(request.url);
+  if (url.searchParams.has('wsdl')) {
+    return serveWsdl('IF_MDZ_EVCP_MODEL_MASTER.wsdl');
+  }
+  
+  return new Response('Method Not Allowed', { status: 405 });
 }
 
-// WSDL 기반의 SOAP 요청 (데이터 전송건) 처리하기 (HTTP)
 export async function POST(request: NextRequest) {
-    const url = new URL(request.url);
-    if (url.searchParams.has('wsdl')) {
-        return serveWsdl();
-    }
-
-    try {
-        // 요청 본문 (MDZ 데이터)를 가져오기
-        const body = await request.text();
-        
-        // 요청 로깅
-        console.log('Request Body 일부:', body.substring(0, 200) + (body.length > 200 ? '...' : ''));
-
-        // XML 파서 설정하기
-        const parser = new XMLParser({
-            ignoreAttributes: false,
-            attributeNamePrefix: '@_',
-            parseAttributeValue: false, // 값 조작 방지
-            trimValues: true,
-            isArray: (name: string) => {
-                return ['MATL', 'DESC', 'PLNT', 'UNIT', 'CLASSASGN', 'CHARASGN'].includes(name);
-            },
-            parseTagValue: false, // 값 조작 방지
-            allowBooleanAttributes: true,
-        });
-
-        // XML 파싱하기
-        const parsedData = parser.parse(body);
-        
-        // 디버깅용 - 최상위 구조 확인
-        console.log('XML root keys:', Object.keys(parsedData));
-        
-        // 재할당 가능한 변수 선언
-        let requestData = null;
-
-        // 가능한 경로 확인
-        if (parsedData?.['soap:Envelope']?.['soap:Body']) {
-            const soapBody = parsedData['soap:Envelope']['soap:Body'];
-            requestData = extractRequestData(soapBody);
-        } else if (parsedData?.['SOAP:Envelope']?.['SOAP:Body']) {
-            const soapBody = parsedData['SOAP:Envelope']['SOAP:Body'];
-            requestData = extractRequestData(soapBody);
-        } else if (parsedData?.['Envelope']?.['Body']) {
-            const soapBody = parsedData['Envelope']['Body'];
-            requestData = extractRequestData(soapBody);
-        } else if (parsedData?.['soapenv:Envelope']?.['soapenv:Body']) {
-            const soapBody = parsedData['soapenv:Envelope']['soapenv:Body'];
-            requestData = extractRequestData(soapBody);
-        } else if (parsedData?.['IF_MDZ_EVCP_MODEL_MASTERReq']) {
-            requestData = parsedData['IF_MDZ_EVCP_MODEL_MASTERReq'];
-            console.log('Found direct IF_MDZ_EVCP_MODEL_MASTERReq data');
-        } else if (parsedData?.['ns1:IF_MDZ_EVCP_MODEL_MASTERReq']) {
-            requestData = parsedData['ns1:IF_MDZ_EVCP_MODEL_MASTERReq'];
-            console.log('Found direct ns1:IF_MDZ_EVCP_MODEL_MASTERReq data');
-        } else if (parsedData?.['p0:IF_MDZ_EVCP_MODEL_MASTERReq']) {
-            requestData = parsedData['p0:IF_MDZ_EVCP_MODEL_MASTERReq'];
-            console.log('Found direct p0:IF_MDZ_EVCP_MODEL_MASTERReq data');
-        } else {
-            // 루트 레벨에서 MATL을 직접 찾기
-            if (parsedData?.MATL) {
-                requestData = parsedData;
-                console.log('Found MATL data at root level');
-            } else {
-                // 다른 모든 키에 대해 확인
-                for (const key of Object.keys(parsedData)) {
-                    const value = parsedData[key];
-                    // 데이터 구조가 맞는지 확인 (MATL이 있는지)
-                    if (value && value.MATL) {
-                        requestData = value;
-                        console.log(`Found data in root key: ${key}`);
-                        break;
-                    }
-                    
-                    // 키 이름에 IF_MDZ_EVCP_MODEL_MASTERReq가 포함되어 있는지 확인
-                    if (key.includes('IF_MDZ_EVCP_MODEL_MASTERReq')) {
-                        requestData = value;
-                        console.log(`Found data in root key with matching name: ${key}`);
-                        break;
-                    }
-                }
-            }
-        }
-        
-        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_MODEL_MASTERReq or MATL data');
+  const url = new URL(request.url);
+  if (url.searchParams.has('wsdl')) {
+    return serveWsdl('IF_MDZ_EVCP_MODEL_MASTER.wsdl');
+  }
+
+  const body = await request.text();
+  
+  return withSoapLogging(
+    'INBOUND',
+    'S-ERP',
+    'IF_MDZ_EVCP_MODEL_MASTER',
+    body,
+    async () => {
+      console.log('Request Body 일부:', body.substring(0, 200) + (body.length > 200 ? '...' : ''));
+
+      const parser = createXMLParser(['MATL', 'DESC', 'PLNT', 'UNIT', 'CLASSASGN', 'CHARASGN']);
+      const parsedData = parser.parse(body);
+      console.log('XML root keys:', Object.keys(parsedData));
+      
+      const requestData = extractRequestData(parsedData, 'IF_MDZ_EVCP_MODEL_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_MODEL_MASTERReq or MATL data');
+      }
+      
+      console.log('Validating request data structure:', 
+        `MATL: ${requestData.MATL ? 'found' : 'not found'}`
+      );
+      
+      if (requestData.MATL && Array.isArray(requestData.MATL) && requestData.MATL.length > 0) {
+        console.log('First MATL sample:', JSON.stringify(requestData.MATL[0], null, 2));
+      }
+      
+      // XML 데이터를 DB 삽입 가능한 형태로 변환
+      const processedMaterials = transformMatlData(requestData.MATL as MatlXML[] || []);
+      
+      // 필수 필드 검증
+      for (const materialData of processedMaterials) {
+        if (!materialData.material.MATNR) {
+          throw new Error('Missing required field: MATNR in material');
         }
-        
-        // 데이터 유효성 검증
-        console.log('Validating request data structure:', 
-            `MATL: ${requestData.MATL ? 'found' : 'not found'}`
-        );
-        
-        // 샘플 데이터 로깅
-        if (requestData.MATL && Array.isArray(requestData.MATL) && requestData.MATL.length > 0) {
-            console.log('First MATL sample:', JSON.stringify(requestData.MATL[0], null, 2));
-        }
-        
-        // 데이터 구조 정규화 - MDZ 데이터를 우리 애플리케이션 모델로 변환
-        const normalizedData: RequestData = {
-            materials: transformMatlData(requestData.MATL)
-        };
-        
-        // 기본 유효성 검사 - 필수 필드 확인
-        for (const material of normalizedData.materials) {
-            if (!material.matnr) {
-                throw new Error('Missing required field: matnr in material');
-            }
-        }
-        
-        // 데이터베이스 저장
-        await saveToDatabase(normalizedData);
-        
-        console.log(`Processed ${normalizedData.materials.length} materials`);
-        
-        // XML 응답 생성
-        const xmlResponse = `<?xml version="1.0" encoding="UTF-8"?>
-<soap:Envelope 
-  xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" 
-  xmlns:tns="http://60.101.108.100/api/IF_MDZ_EVCP_MODEL_MASTER/">
-  <soap:Body>
-  </soap:Body>
-</soap:Envelope>`;
-        
-        return new NextResponse(xmlResponse, {
-            headers: {
-                'Content-Type': 'text/xml; charset=utf-8',
-            },
-        });
-    } catch (error: unknown) {
-        console.error('API Error:', error);
-        
-        // XML 에러 응답
-        const errorResponse = `<?xml version="1.0" encoding="UTF-8"?>
-<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
-  <soap:Body>
-    <soap:Fault>
-      <faultcode>soap:Server</faultcode>
-      <faultstring>${error instanceof Error ? ('[from eVCP]: ' + error.message) : 'Unknown error'}</faultstring>
-    </soap:Fault>
-  </soap:Body>
-</soap:Envelope>`;
-        
-        return new NextResponse(errorResponse, {
-            status: 500,
-            headers: {
-                'Content-Type': 'text/xml; charset=utf-8',
-            },
-        });
+      }
+      
+      // 데이터베이스 저장
+      await saveToDatabase(processedMaterials);
+      
+      console.log(`Processed ${processedMaterials.length} materials`);
+      
+      return createSuccessResponse('http://60.101.108.100/api/IF_MDZ_EVCP_MODEL_MASTER/');
     }
+  ).catch(error => {
+    return createErrorResponse(error);
+  });
 }
 
-// SOAP Body나 루트에서 요청 데이터 추출하는 헬퍼 함수
-function extractRequestData(data: SoapBodyData): Record<string, unknown> | null {
-    if (!data) return null;
+// XML 데이터를 DB 삽입 가능한 형태로 변환
+function transformMatlData(matlData: MatlXML[]): ProcessedMaterialData[] {
+  if (!matlData || !Array.isArray(matlData)) {
+    return [];
+  }
+  
+  return matlData.map(matl => {
+    // 메인 Material 데이터 변환 (자동)
+    const material = convertXMLToDBData<MatlData>(
+      matl as Record<string, string | undefined>, 
+      ['MATNR']
+    );
     
-    if (data['IF_MDZ_EVCP_MODEL_MASTERReq']) {
-        return data['IF_MDZ_EVCP_MODEL_MASTERReq'] as Record<string, unknown>;
-    } else if (data['tns:IF_MDZ_EVCP_MODEL_MASTERReq']) {
-        return data['tns:IF_MDZ_EVCP_MODEL_MASTERReq'] as Record<string, unknown>;
-    } else if (data['ns1:IF_MDZ_EVCP_MODEL_MASTERReq']) {
-        return data['ns1:IF_MDZ_EVCP_MODEL_MASTERReq'] as Record<string, unknown>;
-    } else if (data['p0:IF_MDZ_EVCP_MODEL_MASTERReq']) {
-        return data['p0:IF_MDZ_EVCP_MODEL_MASTERReq'] as Record<string, unknown>;
+    // 필수 필드 보정 (MATNR이 빈 문자열이면 안됨)
+    if (!material.MATNR) {
+      material.MATNR = '';
     }
     
-    // 다른 키 검색
-    for (const key of Object.keys(data)) {
-        if (key.includes('IF_MDZ_EVCP_MODEL_MASTERReq')) {
-            return data[key] as Record<string, unknown>;
-        }
-    }
+    // FK 데이터 준비
+    const fkData = { MATNR: matl.MATNR || '' };
     
-    // MATL이 직접 있는 경우
-    if (data.MATL && Array.isArray(data.MATL)) {
-        return data;
-    }
+    // Description 데이터 변환 (자동)
+    const descriptions = processNestedArray(
+      matl.DESC, 
+      (desc) => convertXMLToDBData<MatlDescData>(desc, ['MATNR'], fkData),
+      fkData
+    );
     
-    return null;
-}
-
-// XML MATL 데이터를 내부 Material 형식으로 변환하는 함수
-function transformMatlData(matlData: MatlXML[]): Material[] {
-    if (!matlData || !Array.isArray(matlData)) {
-        return [];
-    }
+    // Plant 데이터 변환 (자동)
+    const plants = processNestedArray(
+      matl.PLNT, 
+      (plnt) => convertXMLToDBData<MatlPlntData>(plnt, ['MATNR'], fkData),
+      fkData
+    );
     
-    return matlData.map(matl => {
-        const material: Material = {
-            matnr: matl.MATNR,
-            mbrsh: matl.MBRSH,
-            mtart: matl.MTART,
-            lvorm: matl.LVORM,
-            meins: matl.MEINS,
-            matkl: matl.MATKL,
-            bismt: matl.BISMT,
-            spart: matl.SPART,
-            prdha: matl.PRDHA,
-            mstae: matl.MSTAE,
-            mstde: matl.MSTDE,
-            brgew: matl.BRGEW,
-            gewei: matl.GEWEI,
-            ntgew: matl.NTGEW,
-            volum: matl.VOLUM,
-            voleh: matl.VOLEH,
-            groes: matl.GROES,
-            laeng: matl.LAENG,
-            breit: matl.BREIT,
-            hoehe: matl.HOEHE,
-            meabm: matl.MEABM,
-            magrv: matl.MAGRV,
-            vhart: matl.VHART,
-            zzname: matl.ZZNAME,
-            zzspec: matl.ZZSPEC,
-            zzdesc: matl.ZZDESC,
-            zzmmtyp: matl.ZZMMTYP,
-            zzregdt: matl.ZZREGDT,
-            zzregtm: matl.ZZREGTM,
-            zzregus: matl.ZZREGUS,
-            zzappdt: matl.ZZAPPDT,
-            zzapptm: matl.ZZAPPTM,
-            zzappus: matl.ZZAPPUS,
-            zzlamdt: matl.ZZLAMDT,
-            zzlamtm: matl.ZZLAMTM,
-            zzlamus: matl.ZZLAMUS,
-            zzprflg: matl.ZZPRFLG,
-            zzdokar: matl.ZZDOKAR,
-            zzdoknr: matl.ZZDOKNR,
-            zzdoktl: matl.ZZDOKTL,
-            zzdokvr: matl.ZZDOKVR,
-        };
-        
-        // DESC 항목 처리
-        if (matl.DESC && Array.isArray(matl.DESC)) {
-            material.descriptions = matl.DESC.map((desc: DescXML) => ({
-                matnr: desc.MATNR,
-                spras: desc.SPRAS,
-                maktx: desc.MAKTX
-            }));
-        }
-        
-        // PLNT 항목 처리
-        if (matl.PLNT && Array.isArray(matl.PLNT)) {
-            
-            material.plants = matl.PLNT.map((plnt: PlntXML) => ({
-                matnr: plnt.MATNR,
-                werks: plnt.WERKS,
-                lvorm: plnt.LVORM,
-                mmsta: plnt.MMSTA,
-                mmstd: plnt.MMSTD,
-                zzmtarp: plnt.ZZMTARP,
-                zzregdt: plnt.ZZREGDT,
-                zzregtm: plnt.ZZREGTM,
-                zzregus: plnt.ZZREGUS,
-                zzlamdt: plnt.ZZLAMDT,
-                zzlamtm: plnt.ZZLAMTM,
-                zzlamus: plnt.ZZLAMUS,
-                zzprflg: plnt.ZZPRFLG
-            }));
-        }
-        
-        // UNIT 항목 처리
-        if (matl.UNIT && Array.isArray(matl.UNIT)) {
-            material.units = matl.UNIT.map((unit: UnitXML) => ({
-                matnr: unit.MATNR,
-                meinh: unit.MEINH,
-                umrez: unit.UMREZ,
-                umren: unit.UMREN,
-                laeng: unit.LAENG,
-                breit: unit.BREIT,
-                hoehe: unit.HOEHE,
-                meabm: unit.MEABM,
-                volum: unit.VOLUM,
-                voleh: unit.VOLEH,
-                brgew: unit.BRGEW,
-                gewei: unit.GEWEI
-            }));
-        }
-        
-        // CLASSASGN 항목 처리
-        if (matl.CLASSASGN && Array.isArray(matl.CLASSASGN)) {
-            material.classAssignments = matl.CLASSASGN.map((cls: ClassAsgnXML) => ({
-                matnr: cls.MATNR,
-                class: cls.CLASS,
-                klart: cls.KLART
-            }));
-        }
-        
-        // CHARASGN 항목 처리
-        if (matl.CHARASGN && Array.isArray(matl.CHARASGN)) {
-            material.characteristicAssignments = matl.CHARASGN.map((char: CharAsgnXML) => ({
-                matnr: char.MATNR,
-                class: char.CLASS,
-                klart: char.KLART,
-                atnam: char.ATNAM,
-                atwrt: char.ATWRT,
-                atflv: char.ATFLV,
-                atawe: char.ATAWE,
-                atflb: char.ATFLB,
-                ataw1: char.ATAW1,
-                atbez: char.ATBEZ,
-                atwtb: char.ATWTB
-            }));
-        }
-        
-        return material;
-    });
+    // Unit 데이터 변환 (자동)
+    const units = processNestedArray(
+      matl.UNIT, 
+      (unit) => convertXMLToDBData<MatlUnitData>(unit, ['MATNR'], fkData),
+      fkData
+    );
+    
+    // Class Assignment 데이터 변환 (자동)
+    const classAssignments = processNestedArray(
+      matl.CLASSASGN, 
+      (cls) => convertXMLToDBData<MatlClassAsgnData>(cls, ['MATNR'], fkData),
+      fkData
+    );
+    
+    // Characteristic Assignment 데이터 변환 (자동)
+    const characteristicAssignments = processNestedArray(
+      matl.CHARASGN, 
+      (char) => convertXMLToDBData<MatlCharAsgnData>(char, ['MATNR'], fkData),
+      fkData
+    );
+    
+    return {
+      material,
+      descriptions,
+      plants,
+      units,
+      classAssignments,
+      characteristicAssignments
+    };
+  });
 }
 
 // 데이터베이스 저장 함수
-async function saveToDatabase(data: RequestData) {
-    console.log(`데이터베이스 저장 함수가 호출됨. ${data.materials.length}개의 자재 데이터 수신.`);
-    
-    try {
-        // 트랜잭션으로 모든 데이터 처리
-        await db.transaction(async (tx) => {
-            for (const material of data.materials) {
-                if (!material.matnr) {
-                    console.warn('자재번호(MATNR)가 없는 항목 발견, 건너뜁니다.');
-                    continue;
-                }
-
-                // 1. MATL 테이블 Upsert
-                await tx.insert(MATL)
-                    .values({
-                        MATNR: material.matnr,
-                        MBRSH: material.mbrsh || null,
-                        MTART: material.mtart || null,
-                        LVORM: material.lvorm || null,
-                        MEINS: material.meins || null,
-                        MATKL: material.matkl || null,
-                        BISMT: material.bismt || null,
-                        SPART: material.spart || null,
-                        PRDHA: material.prdha || null,
-                        MSTAE: material.mstae || null,
-                        MSTDE: material.mstde || null,
-                        BRGEW: material.brgew || null,
-                        GEWEI: material.gewei || null,
-                        NTGEW: material.ntgew || null,
-                        VOLUM: material.volum || null,
-                        VOLEH: material.voleh || null,
-                        GROES: material.groes || null,
-                        LAENG: material.laeng || null,
-                        BREIT: material.breit || null,
-                        HOEHE: material.hoehe || null,
-                        MEABM: material.meabm || null,
-                        MAGRV: material.magrv || null,
-                        VHART: material.vhart || null,
-                        ZZNAME: material.zzname || null,
-                        ZZSPEC: material.zzspec || null,
-                        ZZDESC: material.zzdesc || null,
-                        ZZMMTYP: material.zzmmtyp || null,
-                        ZZREGDT: material.zzregdt || null,
-                        ZZREGTM: material.zzregtm || null,
-                        ZZREGUS: material.zzregus || null,
-                        ZZAPPDT: material.zzappdt || null,
-                        ZZAPPTM: material.zzapptm || null,
-                        ZZAPPUS: material.zzappus || null,
-                        ZZLAMDT: material.zzlamdt || null,
-                        ZZLAMTM: material.zzlamtm || null,
-                        ZZLAMUS: material.zzlamus || null,
-                        ZZPRFLG: material.zzprflg || null,
-                        ZZDOKAR: material.zzdokar || null,
-                        ZZDOKNR: material.zzdoknr || null,
-                        ZZDOKTL: material.zzdoktl || null,
-                        ZZDOKVR: material.zzdokvr || null,
-                    })
-                    .onConflictDoUpdate({
-                        target: MATL.MATNR,
-                        set: {
-                            MBRSH: material.mbrsh || null,
-                            MTART: material.mtart || null,
-                            LVORM: material.lvorm || null,
-                            MEINS: material.meins || null,
-                            MATKL: material.matkl || null,
-                            BISMT: material.bismt || null,
-                            SPART: material.spart || null,
-                            PRDHA: material.prdha || null,
-                            MSTAE: material.mstae || null,
-                            MSTDE: material.mstde || null,
-                            BRGEW: material.brgew || null,
-                            GEWEI: material.gewei || null,
-                            NTGEW: material.ntgew || null,
-                            VOLUM: material.volum || null,
-                            VOLEH: material.voleh || null,
-                            GROES: material.groes || null,
-                            LAENG: material.laeng || null,
-                            BREIT: material.breit || null,
-                            HOEHE: material.hoehe || null,
-                            MEABM: material.meabm || null,
-                            MAGRV: material.magrv || null,
-                            VHART: material.vhart || null,
-                            ZZNAME: material.zzname || null,
-                            ZZSPEC: material.zzspec || null,
-                            ZZDESC: material.zzdesc || null,
-                            ZZMMTYP: material.zzmmtyp || null,
-                            ZZREGDT: material.zzregdt || null,
-                            ZZREGTM: material.zzregtm || null,
-                            ZZREGUS: material.zzregus || null,
-                            ZZAPPDT: material.zzappdt || null,
-                            ZZAPPTM: material.zzapptm || null,
-                            ZZAPPUS: material.zzappus || null,
-                            ZZLAMDT: material.zzlamdt || null,
-                            ZZLAMTM: material.zzlamtm || null,
-                            ZZLAMUS: material.zzlamus || null,
-                            ZZPRFLG: material.zzprflg || null,
-                            ZZDOKAR: material.zzdokar || null,
-                            ZZDOKNR: material.zzdoknr || null,
-                            ZZDOKTL: material.zzdoktl || null,
-                            ZZDOKVR: material.zzdokvr || null,
-                            updatedAt: new Date(),
-                        }
-                    });
-
-                // 2. 하위 테이블 데이터 처리 (Upsert)
-                // DESC 테이블 데이터 처리
-                if (material.descriptions && material.descriptions.length > 0) {
-                    // 기존 데이터 조회 (해당 자재의 모든 설명)
-                    const existingDescs = await tx.select().from(DESC)
-                        .where(eq(DESC.MATNR, material.matnr));
-                    
-                    // 설명 데이터 매핑
-                    const existingDescsMap = new Map(
-                        existingDescs.map(desc => [`${desc.MATNR}-${desc.SPRAS}`, desc])
-                    );
-                    
-                    for (const desc of material.descriptions) {
-                        if (!desc.matnr && !material.matnr) continue; // 자재번호 필수
-                        
-                        const matnr = desc.matnr || material.matnr;
-                        const spras = desc.spras || '';
-                        const key = `${matnr}-${spras}`;
-                        
-                        if (existingDescsMap.has(key)) {
-                            // 기존 데이터 업데이트
-                            await tx.update(DESC)
-                                .set({
-                                    MAKTX: desc.maktx || null,
-                                    updatedAt: new Date()
-                                })
-                                .where(eq(DESC.id, existingDescsMap.get(key)!.id));
-                        } else {
-                            // 신규 데이터 삽입
-                            await tx.insert(DESC).values({
-                                MATNR: matnr,
-                                SPRAS: desc.spras || null,
-                                MAKTX: desc.maktx || null,
-                            });
-                        }
-                    }
-                }
-
-                // PLNT 테이블 데이터 처리
-                if (material.plants && material.plants.length > 0) {
-                    // 기존 데이터 조회
-                    const existingPlants = await tx.select().from(PLNT)
-                        .where(eq(PLNT.MATNR, material.matnr));
-                    
-                    // 플랜트 데이터 매핑
-                    const existingPlantsMap = new Map(
-                        existingPlants.map(plant => [`${plant.MATNR}-${plant.WERKS}`, plant])
-                    );
-                    
-                    for (const plant of material.plants) {
-                        if (!plant.matnr && !material.matnr) continue; // 자재번호 필수
-                        if (!plant.werks) continue; // 플랜트 코드 필수
-                        
-                        const matnr = plant.matnr || material.matnr;
-                        const werks = plant.werks;
-                        const key = `${matnr}-${werks}`;
-                        
-                        if (existingPlantsMap.has(key)) {
-                            // 기존 데이터 업데이트
-                            await tx.update(PLNT)
-                                .set({
-                                    LVORM: plant.lvorm || null,
-                                    MMSTA: plant.mmsta || null,
-                                    MMSTD: plant.mmstd || null,
-                                    ZZMTARP: plant.zzmtarp || null,
-                                    ZZREGDT: plant.zzregdt || null,
-                                    ZZREGTM: plant.zzregtm || null,
-                                    ZZREGUS: plant.zzregus || null,
-                                    ZZLAMDT: plant.zzlamdt || null,
-                                    ZZLAMTM: plant.zzlamtm || null,
-                                    ZZLAMUS: plant.zzlamus || null,
-                                    ZZPRFLG: plant.zzprflg || null,
-                                    updatedAt: new Date()
-                                })
-                                .where(eq(PLNT.id, existingPlantsMap.get(key)!.id));
-                        } else {
-                            // 신규 데이터 삽입
-                            await tx.insert(PLNT).values({
-                                MATNR: matnr,
-                                WERKS: werks,
-                                LVORM: plant.lvorm || null,
-                                MMSTA: plant.mmsta || null,
-                                MMSTD: plant.mmstd || null,
-                                ZZMTARP: plant.zzmtarp || null,
-                                ZZREGDT: plant.zzregdt || null,
-                                ZZREGTM: plant.zzregtm || null,
-                                ZZREGUS: plant.zzregus || null,
-                                ZZLAMDT: plant.zzlamdt || null,
-                                ZZLAMTM: plant.zzlamtm || null,
-                                ZZLAMUS: plant.zzlamus || null,
-                                ZZPRFLG: plant.zzprflg || null,
-                            });
-                        }
-                    }
-                }
-
-                // UNIT 테이블 데이터 처리
-                if (material.units && material.units.length > 0) {
-                    // 기존 데이터 조회
-                    const existingUnits = await tx.select().from(UNIT)
-                        .where(eq(UNIT.MATNR, material.matnr));
-                    
-                    // 단위 데이터 매핑
-                    const existingUnitsMap = new Map(
-                        existingUnits.map(unit => [`${unit.MATNR}-${unit.MEINH}`, unit])
-                    );
-                    
-                    for (const unit of material.units) {
-                        if (!unit.matnr && !material.matnr) continue; // 자재번호 필수
-                        if (!unit.meinh) continue; // 단위 코드 필수
-                        
-                        const matnr = unit.matnr || material.matnr;
-                        const meinh = unit.meinh;
-                        const key = `${matnr}-${meinh}`;
-                        
-                        if (existingUnitsMap.has(key)) {
-                            // 기존 데이터 업데이트
-                            await tx.update(UNIT)
-                                .set({
-                                    UMREZ: unit.umrez || null,
-                                    UMREN: unit.umren || null,
-                                    LAENG: unit.laeng || null,
-                                    BREIT: unit.breit || null,
-                                    HOEHE: unit.hoehe || null,
-                                    MEABM: unit.meabm || null,
-                                    VOLUM: unit.volum || null,
-                                    VOLEH: unit.voleh || null,
-                                    BRGEW: unit.brgew || null,
-                                    GEWEI: unit.gewei || null,
-                                    updatedAt: new Date()
-                                })
-                                .where(eq(UNIT.id, existingUnitsMap.get(key)!.id));
-                        } else {
-                            // 신규 데이터 삽입
-                            await tx.insert(UNIT).values({
-                                MATNR: matnr,
-                                MEINH: meinh,
-                                UMREZ: unit.umrez || null,
-                                UMREN: unit.umren || null,
-                                LAENG: unit.laeng || null,
-                                BREIT: unit.breit || null,
-                                HOEHE: unit.hoehe || null,
-                                MEABM: unit.meabm || null,
-                                VOLUM: unit.volum || null,
-                                VOLEH: unit.voleh || null,
-                                BRGEW: unit.brgew || null,
-                                GEWEI: unit.gewei || null,
-                            });
-                        }
-                    }
-                }
-
-                // CLASSASGN 테이블 데이터 처리
-                if (material.classAssignments && material.classAssignments.length > 0) {
-                    // 기존 데이터 조회
-                    const existingClassAsgns = await tx.select().from(CLASSASGN)
-                        .where(eq(CLASSASGN.MATNR, material.matnr));
-                    
-                    // 클래스 할당 데이터 매핑
-                    const existingClassAsgnsMap = new Map(
-                        existingClassAsgns.map(cls => [`${cls.MATNR}-${cls.CLASS}-${cls.KLART}`, cls])
-                    );
-                    
-                    for (const cls of material.classAssignments) {
-                        if (!cls.matnr && !material.matnr) continue; // 자재번호 필수
-                        if (!cls.class || !cls.klart) continue; // 클래스 및 유형 필수
-                        
-                        const matnr = cls.matnr || material.matnr;
-                        const clsVal = cls.class;
-                        const klart = cls.klart;
-                        const key = `${matnr}-${clsVal}-${klart}`;
-                        
-                        if (!existingClassAsgnsMap.has(key)) {
-                            // 클래스 할당은 기본키 자체가 변경되는 경우가 드물어 신규 삽입만 처리
-                            await tx.insert(CLASSASGN).values({
-                                MATNR: matnr,
-                                CLASS: clsVal,
-                                KLART: klart,
-                            });
-                        }
-                    }
-                }
+async function saveToDatabase(processedMaterials: ProcessedMaterialData[]) {
+  console.log(`데이터베이스 저장 함수가 호출됨. ${processedMaterials.length}개의 자재 데이터 수신.`);
+  
+  try {
+    await db.transaction(async (tx) => {
+      for (const materialData of processedMaterials) {
+        const { material, descriptions, plants, units, classAssignments, characteristicAssignments } = materialData;
+        
+        if (!material.MATNR) {
+          console.warn('자재번호(MATNR)가 없는 항목 발견, 건너뜁니다.');
+          continue;
+        }
 
-                // CHARASGN 테이블 데이터 처리
-                if (material.characteristicAssignments && material.characteristicAssignments.length > 0) {
-                    // 기존 데이터 조회
-                    const existingCharAsgns = await tx.select().from(CHARASGN)
-                        .where(eq(CHARASGN.MATNR, material.matnr));
-                    
-                    // 특성 할당 데이터 매핑
-                    const existingCharAsgnsMap = new Map(
-                        existingCharAsgns.map(char => 
-                            [`${char.MATNR}-${char.CLASS}-${char.KLART}-${char.ATNAM}`, char]
-                        )
-                    );
-                    
-                    for (const char of material.characteristicAssignments) {
-                        if (!char.matnr && !material.matnr) continue; // 자재번호 필수
-                        if (!char.class || !char.klart || !char.atnam) continue; // 클래스, 유형, 특성명 필수
-                        
-                        const matnr = char.matnr || material.matnr;
-                        const clsVal = char.class;
-                        const klart = char.klart;
-                        const atnam = char.atnam;
-                        const key = `${matnr}-${clsVal}-${klart}-${atnam}`;
-                        
-                        if (existingCharAsgnsMap.has(key)) {
-                            // 기존 데이터 업데이트
-                            await tx.update(CHARASGN)
-                                .set({
-                                    ATWRT: char.atwrt || null,
-                                    ATFLV: char.atflv || null,
-                                    ATAWE: char.atawe || null,
-                                    ATFLB: char.atflb || null,
-                                    ATAW1: char.ataw1 || null,
-                                    ATBEZ: char.atbez || null,
-                                    ATWTB: char.atwtb || null,
-                                    updatedAt: new Date()
-                                })
-                                .where(eq(CHARASGN.id, existingCharAsgnsMap.get(key)!.id));
-                        } else {
-                            // 신규 데이터 삽입
-                            await tx.insert(CHARASGN).values({
-                                MATNR: matnr,
-                                CLASS: clsVal,
-                                KLART: klart,
-                                ATNAM: atnam,
-                                ATWRT: char.atwrt || null,
-                                ATFLV: char.atflv || null,
-                                ATAWE: char.atawe || null,
-                                ATFLB: char.atflb || null,
-                                ATAW1: char.ataw1 || null,
-                                ATBEZ: char.atbez || null,
-                                ATWTB: char.atwtb || null,
-                            });
-                        }
-                    }
-                }
+        // 1. MATL 테이블 Upsert (최상위 테이블)
+        await tx.insert(MODEL_MASTER_MATL)
+          .values(material)
+          .onConflictDoUpdate({
+            target: MODEL_MASTER_MATL.MATNR,
+            set: {
+              ...material,
+              updatedAt: new Date(),
             }
-        });
+          });
+
+        // 2. 하위 테이블 데이터 처리 - FK 기준으로 전체 삭제 후 재삽입
+        await Promise.all([
+          // DESC 테이블 처리
+          replaceSubTableData(
+            tx, 
+            MODEL_MASTER_MATL_DESC, 
+            descriptions, 
+            'MATNR', 
+            material.MATNR
+          ),
+          
+          // PLNT 테이블 처리
+          replaceSubTableData(
+            tx, 
+            MODEL_MASTER_MATL_PLNT, 
+            plants, 
+            'MATNR', 
+            material.MATNR
+          ),
+          
+          // UNIT 테이블 처리
+          replaceSubTableData(
+            tx, 
+            MODEL_MASTER_MATL_UNIT, 
+            units, 
+            'MATNR', 
+            material.MATNR
+          ),
+          
+          // CLASSASGN 테이블 처리
+          replaceSubTableData(
+            tx, 
+            MODEL_MASTER_MATL_CLASSASGN, 
+            classAssignments, 
+            'MATNR', 
+            material.MATNR
+          ),
+          
+          // CHARASGN 테이블 처리
+          replaceSubTableData(
+            tx, 
+            MODEL_MASTER_MATL_CHARASGN, 
+            characteristicAssignments, 
+            'MATNR', 
+            material.MATNR
+          )
+        ]);
+      }
+    });
 
-        console.log(`${data.materials.length}개의 자재 데이터 처리 완료.`);
-        return true;
-    } catch (error) {
-        console.error('데이터베이스 저장 중 오류 발생:', error);
-        throw error;
-    }
+    console.log(`${processedMaterials.length}개의 자재 데이터 처리 완료.`);
+    return true;
+  } catch (error) {
+    console.error('데이터베이스 저장 중 오류 발생:', error);
+    throw error;
+  }
 }