/* eslint-disable @typescript-eslint/no-explicit-any */
// EMPLOYEE_MASTER
import { NextRequest } from "next/server";
import db from "@/db/db";
import { 
  EMPLOYEE_MASTER_CMCTB_EMP_MDG,
  EMPLOYEE_MASTER_CMCTB_EMP_MDG_BANM,
  EMPLOYEE_MASTER_CMCTB_EMP_MDG_BINM,
  EMPLOYEE_MASTER_CMCTB_EMP_MDG_COMPNM,
  EMPLOYEE_MASTER_CMCTB_EMP_MDG_CORPNM,
  EMPLOYEE_MASTER_CMCTB_EMP_MDG_COUNTRYNM,
  EMPLOYEE_MASTER_CMCTB_EMP_MDG_DEPTCODE,
  EMPLOYEE_MASTER_CMCTB_EMP_MDG_DEPTCODE_PCCDNM,
  EMPLOYEE_MASTER_CMCTB_EMP_MDG_DEPTNM,
  EMPLOYEE_MASTER_CMCTB_EMP_MDG_DHJOBGDNM,
  EMPLOYEE_MASTER_CMCTB_EMP_MDG_GJOBDUTYNM,
  EMPLOYEE_MASTER_CMCTB_EMP_MDG_GJOBGRDNM,
  EMPLOYEE_MASTER_CMCTB_EMP_MDG_GJOBGRDTYPE,
  EMPLOYEE_MASTER_CMCTB_EMP_MDG_GJOBNM,
  EMPLOYEE_MASTER_CMCTB_EMP_MDG_GNNM,
  EMPLOYEE_MASTER_CMCTB_EMP_MDG_JOBDUTYNM,
  EMPLOYEE_MASTER_CMCTB_EMP_MDG_JOBGRDNM,
  EMPLOYEE_MASTER_CMCTB_EMP_MDG_JOBNM,
  EMPLOYEE_MASTER_CMCTB_EMP_MDG_KTLNM,
  EMPLOYEE_MASTER_CMCTB_EMP_MDG_OKTLNM,
  EMPLOYEE_MASTER_CMCTB_EMP_MDG_ORGBICDNM,
  EMPLOYEE_MASTER_CMCTB_EMP_MDG_ORGCOMPNM,
  EMPLOYEE_MASTER_CMCTB_EMP_MDG_ORGCORPNM,
  EMPLOYEE_MASTER_CMCTB_EMP_MDG_ORGDEPTNM,
  EMPLOYEE_MASTER_CMCTB_EMP_MDG_ORGPDEPNM,
  EMPLOYEE_MASTER_CMCTB_EMP_MDG_PDEPTNM
} from "@/db/schema/MDG/mdg";
import {
  ToXMLFields,
  serveWsdl,
  createXMLParser,
  extractRequestData,
  convertXMLToDBData,
  processNestedArray,
  createErrorResponse,
  createSuccessResponse,
  replaceSubTableData,
  withSoapLogging
} from "@/lib/soap/utils";
import {
  bulkUpsert,
  bulkReplaceSubTableData
} from "@/lib/soap/batch-utils";


// 스키마에서 직접 타입 추론
type EmpMdgData = typeof EMPLOYEE_MASTER_CMCTB_EMP_MDG.$inferInsert;
type EmpBanmData = typeof EMPLOYEE_MASTER_CMCTB_EMP_MDG_BANM.$inferInsert;
type EmpBinmData = typeof EMPLOYEE_MASTER_CMCTB_EMP_MDG_BINM.$inferInsert;
type EmpCompnmData = typeof EMPLOYEE_MASTER_CMCTB_EMP_MDG_COMPNM.$inferInsert;
type EmpCorpnmData = typeof EMPLOYEE_MASTER_CMCTB_EMP_MDG_CORPNM.$inferInsert;
type EmpCountrynmData = typeof EMPLOYEE_MASTER_CMCTB_EMP_MDG_COUNTRYNM.$inferInsert;
type EmpDeptcodeData = typeof EMPLOYEE_MASTER_CMCTB_EMP_MDG_DEPTCODE.$inferInsert;
type EmpDeptcodePccdnmData = typeof EMPLOYEE_MASTER_CMCTB_EMP_MDG_DEPTCODE_PCCDNM.$inferInsert;
type EmpDeptnmData = typeof EMPLOYEE_MASTER_CMCTB_EMP_MDG_DEPTNM.$inferInsert;
type EmpDhjobgdnmData = typeof EMPLOYEE_MASTER_CMCTB_EMP_MDG_DHJOBGDNM.$inferInsert;
type EmpGjobdutynmData = typeof EMPLOYEE_MASTER_CMCTB_EMP_MDG_GJOBDUTYNM.$inferInsert;
type EmpGjobgrdnmData = typeof EMPLOYEE_MASTER_CMCTB_EMP_MDG_GJOBGRDNM.$inferInsert;
type EmpGjobgrdtypeData = typeof EMPLOYEE_MASTER_CMCTB_EMP_MDG_GJOBGRDTYPE.$inferInsert;
type EmpGjobnmData = typeof EMPLOYEE_MASTER_CMCTB_EMP_MDG_GJOBNM.$inferInsert;
type EmpGnnmData = typeof EMPLOYEE_MASTER_CMCTB_EMP_MDG_GNNM.$inferInsert;
type EmpJobdutynmData = typeof EMPLOYEE_MASTER_CMCTB_EMP_MDG_JOBDUTYNM.$inferInsert;
type EmpJobgrdnmData = typeof EMPLOYEE_MASTER_CMCTB_EMP_MDG_JOBGRDNM.$inferInsert;
type EmpJobnmData = typeof EMPLOYEE_MASTER_CMCTB_EMP_MDG_JOBNM.$inferInsert;
type EmpKtlnmData = typeof EMPLOYEE_MASTER_CMCTB_EMP_MDG_KTLNM.$inferInsert;
type EmpOktlnmData = typeof EMPLOYEE_MASTER_CMCTB_EMP_MDG_OKTLNM.$inferInsert;
type EmpOrgbicdnmData = typeof EMPLOYEE_MASTER_CMCTB_EMP_MDG_ORGBICDNM.$inferInsert;
type EmpOrgcompnmData = typeof EMPLOYEE_MASTER_CMCTB_EMP_MDG_ORGCOMPNM.$inferInsert;
type EmpOrgcorpnmData = typeof EMPLOYEE_MASTER_CMCTB_EMP_MDG_ORGCORPNM.$inferInsert;
type EmpOrgdeptnmData = typeof EMPLOYEE_MASTER_CMCTB_EMP_MDG_ORGDEPTNM.$inferInsert;
type EmpOrgpdepnmData = typeof EMPLOYEE_MASTER_CMCTB_EMP_MDG_ORGPDEPNM.$inferInsert;
type EmpPdeptnmData = typeof EMPLOYEE_MASTER_CMCTB_EMP_MDG_PDEPTNM.$inferInsert;

// XML 구조 타입
type EmpMdgXML = ToXMLFields<Omit<EmpMdgData, 'id' | 'createdAt' | 'updatedAt'>> & {
  BANM?: ToXMLFields<Omit<EmpBanmData, 'id' | 'createdAt' | 'updatedAt' | 'EMPID'>>[];
  BINM?: ToXMLFields<Omit<EmpBinmData, 'id' | 'createdAt' | 'updatedAt' | 'EMPID'>>[];
  COMPNM?: ToXMLFields<Omit<EmpCompnmData, 'id' | 'createdAt' | 'updatedAt' | 'EMPID'>>[];
  CORPNM?: ToXMLFields<Omit<EmpCorpnmData, 'id' | 'createdAt' | 'updatedAt' | 'EMPID'>>[];
  COUNTRYNM?: ToXMLFields<Omit<EmpCountrynmData, 'id' | 'createdAt' | 'updatedAt' | 'EMPID'>>[];
  DEPTCODE?: ToXMLFields<Omit<EmpDeptcodeData, 'id' | 'createdAt' | 'updatedAt' | 'EMPID'>>[];
  DEPTCODE_PCCDNM?: ToXMLFields<Omit<EmpDeptcodePccdnmData, 'id' | 'createdAt' | 'updatedAt' | 'EMPID'>>[];
  DEPTNM?: ToXMLFields<Omit<EmpDeptnmData, 'id' | 'createdAt' | 'updatedAt' | 'EMPID'>>[];
  DHJOBGDNM?: ToXMLFields<Omit<EmpDhjobgdnmData, 'id' | 'createdAt' | 'updatedAt' | 'EMPID'>>[];
  GJOBDUTYNM?: ToXMLFields<Omit<EmpGjobdutynmData, 'id' | 'createdAt' | 'updatedAt' | 'EMPID'>>[];
  GJOBGRDNM?: ToXMLFields<Omit<EmpGjobgrdnmData, 'id' | 'createdAt' | 'updatedAt' | 'EMPID'>>[];
  GJOBGRDTYPE?: ToXMLFields<Omit<EmpGjobgrdtypeData, 'id' | 'createdAt' | 'updatedAt' | 'EMPID'>>[];
  GJOBNM?: ToXMLFields<Omit<EmpGjobnmData, 'id' | 'createdAt' | 'updatedAt' | 'EMPID'>>[];
  GNNM?: ToXMLFields<Omit<EmpGnnmData, 'id' | 'createdAt' | 'updatedAt' | 'EMPID'>>[];
  JOBDUTYNM?: ToXMLFields<Omit<EmpJobdutynmData, 'id' | 'createdAt' | 'updatedAt' | 'EMPID'>>[];
  JOBGRDNM?: ToXMLFields<Omit<EmpJobgrdnmData, 'id' | 'createdAt' | 'updatedAt' | 'EMPID'>>[];
  JOBNM?: ToXMLFields<Omit<EmpJobnmData, 'id' | 'createdAt' | 'updatedAt' | 'EMPID'>>[];
  KTLNM?: ToXMLFields<Omit<EmpKtlnmData, 'id' | 'createdAt' | 'updatedAt' | 'EMPID'>>[];
  OKTLNM?: ToXMLFields<Omit<EmpOktlnmData, 'id' | 'createdAt' | 'updatedAt' | 'EMPID'>>[];
  ORGBICDNM?: ToXMLFields<Omit<EmpOrgbicdnmData, 'id' | 'createdAt' | 'updatedAt' | 'EMPID'>>[];
  ORGCOMPNM?: ToXMLFields<Omit<EmpOrgcompnmData, 'id' | 'createdAt' | 'updatedAt' | 'EMPID'>>[];
  ORGCORPNM?: ToXMLFields<Omit<EmpOrgcorpnmData, 'id' | 'createdAt' | 'updatedAt' | 'EMPID'>>[];
  ORGDEPTNM?: ToXMLFields<Omit<EmpOrgdeptnmData, 'id' | 'createdAt' | 'updatedAt' | 'EMPID'>>[];
  ORGPDEPNM?: ToXMLFields<Omit<EmpOrgpdepnmData, 'id' | 'createdAt' | 'updatedAt' | 'EMPID'>>[];
  PDEPTNM?: ToXMLFields<Omit<EmpPdeptnmData, 'id' | 'createdAt' | 'updatedAt' | 'EMPID'>>[];
};

// 처리된 데이터 구조
interface ProcessedEmployeeData {
  employee: EmpMdgData;
  banm: EmpBanmData[];
  binm: EmpBinmData[];
  compnm: EmpCompnmData[];
  corpnm: EmpCorpnmData[];
  countrynm: EmpCountrynmData[];
  deptcode: EmpDeptcodeData[];
  deptcodePccdnm: EmpDeptcodePccdnmData[];
  deptnm: EmpDeptnmData[];
  dhjobgdnm: EmpDhjobgdnmData[];
  gjobdutynm: EmpGjobdutynmData[];
  gjobgrdnm: EmpGjobgrdnmData[];
  gjobgrdtype: EmpGjobgrdtypeData[];
  gjobnm: EmpGjobnmData[];
  gnnm: EmpGnnmData[];
  jobdutynm: EmpJobdutynmData[];
  jobgrdnm: EmpJobgrdnmData[];
  jobnm: EmpJobnmData[];
  ktlnm: EmpKtlnmData[];
  oktlnm: EmpOktlnmData[];
  orgbicdnm: EmpOrgbicdnmData[];
  orgcompnm: EmpOrgcompnmData[];
  orgcorpnm: EmpOrgcorpnmData[];
  orgdeptnm: EmpOrgdeptnmData[];
  orgpdepnm: EmpOrgpdepnmData[];
  pdeptnm: EmpPdeptnmData[];
}

export async function GET(request: NextRequest) {
  const url = new URL(request.url);
  if (url.searchParams.has('wsdl')) {
    return serveWsdl('IF_MDZ_EVCP_EMPLOYEE_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_EMPLOYEE_MASTER.wsdl');
  }

  const body = await request.text();
  
  return withSoapLogging(
    'INBOUND',
    'MDG',
    'IF_MDZ_EVCP_EMPLOYEE_MASTER',
    body,
    async () => {
      console.log('🚀 EMPLOYEE_MASTER 수신 시작, 데이터 길이:', body.length);

      const parser = createXMLParser([
        'CMCTB_EMP_MDG', 'BANM', 'BINM', 'COMPNM', 'CORPNM', 'COUNTRYNM', 'DEPTCODE', 
        'DEPTCODE_PCCDNM', 'DEPTNM', 'DHJOBGDNM', 'GJOBDUTYNM', 'GJOBGRDNM', 'GJOBGRDTYPE', 
        'GJOBNM', 'GNNM', 'JOBDUTYNM', 'JOBGRDNM', 'JOBNM', 'KTLNM', 'OKTLNM', 'ORGBICDNM', 
        'ORGCOMPNM', 'ORGCORPNM', 'ORGDEPTNM', 'ORGPDEPNM', 'PDEPTNM'
      ]);
      
      const parsedData = parser.parse(body);
      console.log('XML root keys:', Object.keys(parsedData));
      
      const requestData = extractRequestData(parsedData, 'IF_MDZ_EVCP_EMPLOYEE_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_EMPLOYEE_MASTERReq or CMCTB_EMP_MDG data');
      }
      
      console.log('Validating request data structure:', 
        `CMCTB_EMP_MDG: ${requestData.CMCTB_EMP_MDG ? 'found' : 'not found'}`
      );
      
      if (requestData.CMCTB_EMP_MDG && Array.isArray(requestData.CMCTB_EMP_MDG) && requestData.CMCTB_EMP_MDG.length > 0) {
        console.log('First CMCTB_EMP_MDG sample:', JSON.stringify(requestData.CMCTB_EMP_MDG[0], null, 2));
      }
      
      // XML 데이터를 DB 삽입 가능한 형태로 변환
      const processedEmployees = transformEmployeeData(requestData.CMCTB_EMP_MDG as EmpMdgXML[] || []);
      
      // 필수 필드 검증
      for (const employeeData of processedEmployees) {
        if (!employeeData.employee.EMPID) {
          throw new Error('Missing required field: EMPID in employee');
        }
      }
      
      // 데이터베이스 저장
      await saveToDatabase(processedEmployees);
      
      console.log(`🎉 처리 완료: ${processedEmployees.length}개 사원 데이터`);
      
      return createSuccessResponse('http://60.101.108.100/api/IF_MDZ_EVCP_EMPLOYEE_MASTER/');
    }
  ).catch(error => {
    return createErrorResponse(error);
  });
}

// XML 데이터를 DB 삽입 가능한 형태로 변환
/**
 * EMPLOYEE 마스터 데이터 변환 함수
 * 
 * 데이터 처리 아키텍처:
 * 1. 최상위 테이블 (EMPLOYEE_MASTER_CMCTB_EMP_MDG)
 *    - EMPID가 unique 필드로 충돌 시 upsert 처리
 * 
 * 2. 하위 테이블들 (BANM, BINM, COMPNM, CORPNM, COUNTRYNM, DEPTCODE, 
 *                 DEPTCODE_PCCDNM, DEPTNM, DHJOBGDNM, GJOBDUTYNM, GJOBGRDNM, 
 *                 GJOBGRDTYPE, GJOBNM, GNNM, JOBDUTYNM, JOBGRDNM, JOBNM, 
 *                 KTLNM, OKTLNM, ORGBICDNM, ORGCOMPNM, ORGCORPNM, ORGDEPTNM, 
 *                 ORGPDEPNM, PDEPTNM)
 *    - FK(EMPID)로 연결
 *    - 별도 필수 필드 없음 (스키마에서 notNull() 제거 예정)
 *    - 전체 데이터셋 기반 삭제 후 재삽입 처리
 * 
 * @param empData XML에서 파싱된 EMPLOYEE 데이터
 * @returns 처리된 EMPLOYEE 데이터 구조
 */
function transformEmployeeData(empData: EmpMdgXML[]): ProcessedEmployeeData[] {
  if (!empData || !Array.isArray(empData)) {
    return [];
  }
  
  return empData.map(emp => {
    const empidKey = emp.EMPID || '';
    const fkData = { EMPID: empidKey };
    
    // 1단계: EMP_MDG (루트 - unique 필드: EMPID)
    const employee = convertXMLToDBData<EmpMdgData>(
      emp as Record<string, string | undefined>,
      fkData
    );
    
    // 2단계: 하위 테이블들 (FK: EMPID) - 25개 테이블
    const banm = processNestedArray(emp.BANM, (item) => convertXMLToDBData<EmpBanmData>(item as Record<string, string | undefined>, fkData), fkData);
    const binm = processNestedArray(emp.BINM, (item) => convertXMLToDBData<EmpBinmData>(item as Record<string, string | undefined>, fkData), fkData);
    const compnm = processNestedArray(emp.COMPNM, (item) => convertXMLToDBData<EmpCompnmData>(item as Record<string, string | undefined>, fkData), fkData);
    const corpnm = processNestedArray(emp.CORPNM, (item) => convertXMLToDBData<EmpCorpnmData>(item as Record<string, string | undefined>, fkData), fkData);
    const countrynm = processNestedArray(emp.COUNTRYNM, (item) => convertXMLToDBData<EmpCountrynmData>(item as Record<string, string | undefined>, fkData), fkData);
    const deptcode = processNestedArray(emp.DEPTCODE, (item) => convertXMLToDBData<EmpDeptcodeData>(item as Record<string, string | undefined>, fkData), fkData);
    const deptcodePccdnm = processNestedArray(emp.DEPTCODE_PCCDNM, (item) => convertXMLToDBData<EmpDeptcodePccdnmData>(item as Record<string, string | undefined>, fkData), fkData);
    const deptnm = processNestedArray(emp.DEPTNM, (item) => convertXMLToDBData<EmpDeptnmData>(item as Record<string, string | undefined>, fkData), fkData);
    const dhjobgdnm = processNestedArray(emp.DHJOBGDNM, (item) => convertXMLToDBData<EmpDhjobgdnmData>(item as Record<string, string | undefined>, fkData), fkData);
    const gjobdutynm = processNestedArray(emp.GJOBDUTYNM, (item) => convertXMLToDBData<EmpGjobdutynmData>(item as Record<string, string | undefined>, fkData), fkData);
    const gjobgrdnm = processNestedArray(emp.GJOBGRDNM, (item) => convertXMLToDBData<EmpGjobgrdnmData>(item as Record<string, string | undefined>, fkData), fkData);
    const gjobgrdtype = processNestedArray(emp.GJOBGRDTYPE, (item) => convertXMLToDBData<EmpGjobgrdtypeData>(item as Record<string, string | undefined>, fkData), fkData);
    const gjobnm = processNestedArray(emp.GJOBNM, (item) => convertXMLToDBData<EmpGjobnmData>(item as Record<string, string | undefined>, fkData), fkData);
    const gnnm = processNestedArray(emp.GNNM, (item) => convertXMLToDBData<EmpGnnmData>(item as Record<string, string | undefined>, fkData), fkData);
    const jobdutynm = processNestedArray(emp.JOBDUTYNM, (item) => convertXMLToDBData<EmpJobdutynmData>(item as Record<string, string | undefined>, fkData), fkData);
    const jobgrdnm = processNestedArray(emp.JOBGRDNM, (item) => convertXMLToDBData<EmpJobgrdnmData>(item as Record<string, string | undefined>, fkData), fkData);
    const jobnm = processNestedArray(emp.JOBNM, (item) => convertXMLToDBData<EmpJobnmData>(item as Record<string, string | undefined>, fkData), fkData);
    const ktlnm = processNestedArray(emp.KTLNM, (item) => convertXMLToDBData<EmpKtlnmData>(item as Record<string, string | undefined>, fkData), fkData);
    const oktlnm = processNestedArray(emp.OKTLNM, (item) => convertXMLToDBData<EmpOktlnmData>(item as Record<string, string | undefined>, fkData), fkData);
    const orgbicdnm = processNestedArray(emp.ORGBICDNM, (item) => convertXMLToDBData<EmpOrgbicdnmData>(item as Record<string, string | undefined>, fkData), fkData);
    const orgcompnm = processNestedArray(emp.ORGCOMPNM, (item) => convertXMLToDBData<EmpOrgcompnmData>(item as Record<string, string | undefined>, fkData), fkData);
    const orgcorpnm = processNestedArray(emp.ORGCORPNM, (item) => convertXMLToDBData<EmpOrgcorpnmData>(item as Record<string, string | undefined>, fkData), fkData);
    const orgdeptnm = processNestedArray(emp.ORGDEPTNM, (item) => convertXMLToDBData<EmpOrgdeptnmData>(item as Record<string, string | undefined>, fkData), fkData);
    const orgpdepnm = processNestedArray(emp.ORGPDEPNM, (item) => convertXMLToDBData<EmpOrgpdepnmData>(item as Record<string, string | undefined>, fkData), fkData);
    const pdeptnm = processNestedArray(emp.PDEPTNM, (item) => convertXMLToDBData<EmpPdeptnmData>(item as Record<string, string | undefined>, fkData), fkData);
    
    return {
      employee,
      banm,
      binm,
      compnm,
      corpnm,
      countrynm,
      deptcode,
      deptcodePccdnm,
      deptnm,
      dhjobgdnm,
      gjobdutynm,
      gjobgrdnm,
      gjobgrdtype,
      gjobnm,
      gnnm,
      jobdutynm,
      jobgrdnm,
      jobnm,
      ktlnm,
      oktlnm,
      orgbicdnm,
      orgcompnm,
      orgcorpnm,
      orgdeptnm,
      orgpdepnm,
      pdeptnm
    };
  });
}

// 데이터베이스 저장 함수
/**
 * 처리된 EMPLOYEE 데이터를 데이터베이스에 저장
 * 
 * 저장 전략:
 * 1. 최상위 테이블: EMPID 기준 upsert (충돌 시 업데이트)
 * 2. 하위 테이블들: FK(EMPID) 기준 전체 삭제 후 재삽입
 *    - 송신 XML이 전체 데이터셋을 포함하므로 부분 업데이트 불필요
 *    - 데이터 일관성과 단순성 확보
 *    - 25개의 하위 테이블을 병렬 처리로 성능 최적화
 * 
 * @param processedEmployees 변환된 EMPLOYEE 데이터 배열
 */
async function saveToDatabase(processedEmployees: ProcessedEmployeeData[]) {
  console.log(`데이터베이스(배치) 저장 시작: ${processedEmployees.length}개 사원 데이터`);
  
  try {
    await db.transaction(async (tx) => {
      // 1) 부모 테이블 데이터 준비
      const employeeRows = processedEmployees
        .map((e) => e.employee)
        .filter((e): e is EmpMdgData => !!e.EMPID);

      const empids = employeeRows.map((e) => e.EMPID as string);

      // 2) 하위 테이블 데이터 평탄화
      const banm = processedEmployees.flatMap((e) => e.banm);
      const binm = processedEmployees.flatMap((e) => e.binm);
      const compnm = processedEmployees.flatMap((e) => e.compnm);
      const corpnm = processedEmployees.flatMap((e) => e.corpnm);
      const countrynm = processedEmployees.flatMap((e) => e.countrynm);
      const deptcode = processedEmployees.flatMap((e) => e.deptcode);
      const deptcodePccdnm = processedEmployees.flatMap((e) => e.deptcodePccdnm);
      const deptnm = processedEmployees.flatMap((e) => e.deptnm);
      const dhjobgdnm = processedEmployees.flatMap((e) => e.dhjobgdnm);
      const gjobdutynm = processedEmployees.flatMap((e) => e.gjobdutynm);
      const gjobgrdnm = processedEmployees.flatMap((e) => e.gjobgrdnm);
      const gjobgrdtype = processedEmployees.flatMap((e) => e.gjobgrdtype);
      const gjobnm = processedEmployees.flatMap((e) => e.gjobnm);
      const gnnm = processedEmployees.flatMap((e) => e.gnnm);
      const jobdutynm = processedEmployees.flatMap((e) => e.jobdutynm);
      const jobgrdnm = processedEmployees.flatMap((e) => e.jobgrdnm);
      const jobnm = processedEmployees.flatMap((e) => e.jobnm);
      const ktlnm = processedEmployees.flatMap((e) => e.ktlnm);
      const oktlnm = processedEmployees.flatMap((e) => e.oktlnm);
      const orgbicdnm = processedEmployees.flatMap((e) => e.orgbicdnm);
      const orgcompnm = processedEmployees.flatMap((e) => e.orgcompnm);
      const orgcorpnm = processedEmployees.flatMap((e) => e.orgcorpnm);
      const orgdeptnm = processedEmployees.flatMap((e) => e.orgdeptnm);
      const orgpdepnm = processedEmployees.flatMap((e) => e.orgpdepnm);
      const pdeptnm = processedEmployees.flatMap((e) => e.pdeptnm);

      // 3) 데이터베이스 저장
      await bulkUpsert(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG, employeeRows, 'EMPID');

      // 4) 하위 테이블 데이터 저장
      await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_BANM, banm, 'EMPID', empids);
      await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_BINM, binm, 'EMPID', empids);
      await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_COMPNM, compnm, 'EMPID', empids);
      await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_CORPNM, corpnm, 'EMPID', empids);
      await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_COUNTRYNM, countrynm, 'EMPID', empids);
      await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_DEPTCODE, deptcode, 'EMPID', empids);
      await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_DEPTCODE_PCCDNM, deptcodePccdnm, 'EMPID', empids);
      await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_DEPTNM, deptnm, 'EMPID', empids);
      await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_DHJOBGDNM, dhjobgdnm, 'EMPID', empids);
      await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_GJOBDUTYNM, gjobdutynm, 'EMPID', empids);
      await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_GJOBGRDNM, gjobgrdnm, 'EMPID', empids);
      await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_GJOBGRDTYPE, gjobgrdtype, 'EMPID', empids);
      await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_GJOBNM, gjobnm, 'EMPID', empids);
      await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_GNNM, gnnm, 'EMPID', empids);
      await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_JOBDUTYNM, jobdutynm, 'EMPID', empids);
      await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_JOBGRDNM, jobgrdnm, 'EMPID', empids);
      await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_JOBNM, jobnm, 'EMPID', empids);
      await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_KTLNM, ktlnm, 'EMPID', empids);
      await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_OKTLNM, oktlnm, 'EMPID', empids);
      await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_ORGBICDNM, orgbicdnm, 'EMPID', empids);
      await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_ORGCOMPNM, orgcompnm, 'EMPID', empids);
      await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_ORGCORPNM, orgcorpnm, 'EMPID', empids);
      await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_ORGDEPTNM, orgdeptnm, 'EMPID', empids);
      await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_ORGPDEPNM, orgpdepnm, 'EMPID', empids);
      await bulkReplaceSubTableData(tx, EMPLOYEE_MASTER_CMCTB_EMP_MDG_PDEPTNM, pdeptnm, 'EMPID', empids);
    });

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