(김준회) 벤더 스키마를 MDG로부터 분리, Model Master 스키마 및 수신 route 추가

1 files changed, 927 insertions, 0 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
new file mode 100644
index 00000000..6c73cf08
--- /dev/null
+++ b/app/api/(S-ERP)/(MDG)/IF_MDZ_EVCP_MODEL_MASTER/route.ts
@@ -0,0 +1,927 @@
+import { XMLParser } from "fast-xml-parser";
+import { readFileSync } from "fs";
+import { NextRequest, NextResponse } from "next/server";
+import { join } from "path";
+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;
+  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 });
+    }
+}
+
+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 });
+}
+
+// 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');
+        }
+        
+        // 데이터 유효성 검증
+        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',
+            },
+        });
+    }
+}
+
+// SOAP Body나 루트에서 요청 데이터 추출하는 헬퍼 함수
+function extractRequestData(data: SoapBodyData): Record<string, unknown> | null {
+    if (!data) return null;
+    
+    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>;
+    }
+    
+    // 다른 키 검색
+    for (const key of Object.keys(data)) {
+        if (key.includes('IF_MDZ_EVCP_MODEL_MASTERReq')) {
+            return data[key] as Record<string, unknown>;
+        }
+    }
+    
+    // MATL이 직접 있는 경우
+    if (data.MATL && Array.isArray(data.MATL)) {
+        return data;
+    }
+    
+    return null;
+}
+
+// XML MATL 데이터를 내부 Material 형식으로 변환하는 함수
+function transformMatlData(matlData: MatlXML[]): Material[] {
+    if (!matlData || !Array.isArray(matlData)) {
+        return [];
+    }
+    
+    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;
+    });
+}
+
+// 데이터베이스 저장 함수
+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,
+                            });
+                        }
+                    }
+                }
+
+                // 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,
+                            });
+                        }
+                    }
+                }
+            }
+        });
+
+        console.log(`${data.materials.length}개의 자재 데이터 처리 완료.`);
+        return true;
+    } catch (error) {
+        console.error('데이터베이스 저장 중 오류 발생:', error);
+        throw error;
+    }
+}