import { NextRequest } from "next/server";
import db from "@/db/db";
import { 
  CUSTOMER_MASTER_BP_HEADER,
  CUSTOMER_MASTER_BP_HEADER_ADDRESS,
  CUSTOMER_MASTER_BP_HEADER_ADDRESS_AD_EMAIL,
  CUSTOMER_MASTER_BP_HEADER_ADDRESS_AD_FAX,
  CUSTOMER_MASTER_BP_HEADER_ADDRESS_AD_POSTAL,
  CUSTOMER_MASTER_BP_HEADER_ADDRESS_AD_TEL,
  CUSTOMER_MASTER_BP_HEADER_ADDRESS_AD_URL,
  CUSTOMER_MASTER_BP_HEADER_BP_CUSGEN,
  CUSTOMER_MASTER_BP_HEADER_BP_CUSGEN_ZCOMPANY,
  CUSTOMER_MASTER_BP_HEADER_BP_CUSGEN_ZSALES,
  CUSTOMER_MASTER_BP_HEADER_BP_CUSGEN_ZSALES_ZCPFN,
  CUSTOMER_MASTER_BP_HEADER_BP_CUSGEN_ZTAXIND,
  CUSTOMER_MASTER_BP_HEADER_BP_CUSGEN_ZVATREG,
  CUSTOMER_MASTER_BP_HEADER_BP_TAXNUM
} from "@/db/schema/MDG/mdg";
import {
  ToXMLFields,
  serveWsdl,
  createXMLParser,
  extractRequestData,
  convertXMLToDBData,
  processNestedArray,
  createErrorResponse,
  createSuccessResponse,
  withSoapLogging
} from "@/lib/soap/utils";
import {
  bulkUpsert,
  bulkReplaceSubTableData
} from "@/lib/soap/batch-utils";

// 스키마에서 직접 타입 추론
type BpHeaderData = typeof CUSTOMER_MASTER_BP_HEADER.$inferInsert;
type AddressData = typeof CUSTOMER_MASTER_BP_HEADER_ADDRESS.$inferInsert;
type AdEmailData = typeof CUSTOMER_MASTER_BP_HEADER_ADDRESS_AD_EMAIL.$inferInsert;
type AdFaxData = typeof CUSTOMER_MASTER_BP_HEADER_ADDRESS_AD_FAX.$inferInsert;
type AdPostalData = typeof CUSTOMER_MASTER_BP_HEADER_ADDRESS_AD_POSTAL.$inferInsert;
type AdTelData = typeof CUSTOMER_MASTER_BP_HEADER_ADDRESS_AD_TEL.$inferInsert;
type AdUrlData = typeof CUSTOMER_MASTER_BP_HEADER_ADDRESS_AD_URL.$inferInsert;
type BpCusgenData = typeof CUSTOMER_MASTER_BP_HEADER_BP_CUSGEN.$inferInsert;
type ZcompanyData = typeof CUSTOMER_MASTER_BP_HEADER_BP_CUSGEN_ZCOMPANY.$inferInsert;
type ZsalesData = typeof CUSTOMER_MASTER_BP_HEADER_BP_CUSGEN_ZSALES.$inferInsert;
type ZcpfnData = typeof CUSTOMER_MASTER_BP_HEADER_BP_CUSGEN_ZSALES_ZCPFN.$inferInsert;
type ZtaxindData = typeof CUSTOMER_MASTER_BP_HEADER_BP_CUSGEN_ZTAXIND.$inferInsert;
type ZvatregData = typeof CUSTOMER_MASTER_BP_HEADER_BP_CUSGEN_ZVATREG.$inferInsert;
type BpTaxnumData = typeof CUSTOMER_MASTER_BP_HEADER_BP_TAXNUM.$inferInsert;

// XML 구조 타입
type BpHeaderXML = ToXMLFields<Omit<BpHeaderData, 'id' | 'createdAt' | 'updatedAt'>> & {
  ADDRESS?: AddressXML[];
  BP_TAXNUM?: BpTaxnumXML[]; 
  BP_CUSGEN?: BpCusgenXML[];
};

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

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

type BpCusgenXML = ToXMLFields<Omit<BpCusgenData, 'id' | 'createdAt' | 'updatedAt' | 'BP_HEADER'>> & {
  ZVATREG?: ZvatregXML[];
  ZTAXIND?: ZtaxindXML[];
  ZCOMPANY?: ZcompanyXML[];
  ZSALES?: ZsalesXML[];
};

type ZvatregXML = ToXMLFields<Omit<ZvatregData, 'id' | 'createdAt' | 'updatedAt' | 'BP_HEADER'>>;
type ZtaxindXML = ToXMLFields<Omit<ZtaxindData, 'id' | 'createdAt' | 'updatedAt' | 'BP_HEADER'>>;
type ZcompanyXML = ToXMLFields<Omit<ZcompanyData, 'id' | 'createdAt' | 'updatedAt' | 'BP_HEADER'>>;

type ZsalesXML = ToXMLFields<Omit<ZsalesData, 'id' | 'createdAt' | 'updatedAt' | 'BP_HEADER'>> & {
  ZCPFN?: ZcpfnXML[];
};

type ZcpfnXML = ToXMLFields<Omit<ZcpfnData, 'id' | 'createdAt' | 'updatedAt' | 'BP_HEADER'>>;
type BpTaxnumXML = ToXMLFields<Omit<BpTaxnumData, 'id' | 'createdAt' | 'updatedAt' | 'BP_HEADER'>>;

// 처리된 데이터 구조
interface ProcessedCustomerData {
  bpHeader: BpHeaderData;
  addresses: AddressData[];
  adEmails: AdEmailData[];
  adFaxes: AdFaxData[];
  adPostals: AdPostalData[];
  adTels: AdTelData[];
  adUrls: AdUrlData[];
  bpCusgens: BpCusgenData[];
  zvatregs: ZvatregData[];
  ztaxinds: ZtaxindData[];
  zcompanies: ZcompanyData[];
  zsales: ZsalesData[];
  zcpfns: ZcpfnData[];
  bpTaxnums: BpTaxnumData[];
}

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

  const body = await request.text();
  
  // SOAP 로깅 래퍼 함수 사용
  return withSoapLogging(
    'INBOUND',
    'MDG',
    'IF_MDZ_EVCP_CUSTOMER_MASTER',
    body,
    async () => {
      console.log('🚀 CUSTOMER_MASTER 수신 시작, 데이터 길이:', body.length);

      const parser = createXMLParser([
        'BP_HEADER', 'ADDRESS', 'AD_EMAIL', 'AD_FAX', 'AD_POSTAL', 'AD_TEL', 'AD_URL',
        'BP_CUSGEN', 'ZVATREG', 'ZTAXIND', 'ZCOMPANY', 'ZSALES', 'ZCPFN', 'BP_TAXNUM'
      ]);
      
      const parsedData = parser.parse(body);
      console.log('XML root keys:', Object.keys(parsedData));
      
      const requestData = extractRequestData(parsedData, 'IF_MDZ_EVCP_CUSTOMER_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_CUSTOMER_MASTERReq or BP_HEADER data');
      }
      
      console.log('Validating request data structure:', 
        `BP_HEADER: ${requestData.BP_HEADER ? 'found' : 'not found'}`
      );
      
      if (requestData.BP_HEADER && Array.isArray(requestData.BP_HEADER) && requestData.BP_HEADER.length > 0) {
        console.log('First BP_HEADER sample:', JSON.stringify(requestData.BP_HEADER[0], null, 2));
      }
      
      // XML 데이터를 DB 삽입 가능한 형태로 변환
      const processedCustomers = transformCustomerData(requestData.BP_HEADER as BpHeaderXML[] || []);
      
      // 필수 필드 검증
      for (const customerData of processedCustomers) {
        if (!customerData.bpHeader.BP_HEADER) {
          throw new Error('Missing required field: BP_HEADER in customer');
        }
      }
      
      // 데이터베이스 저장
      await saveToDatabase(processedCustomers);
      
      console.log(`🎉 처리 완료: ${processedCustomers.length}개 고객 데이터`);
      
      return createSuccessResponse('http://60.101.108.100/api/IF_MDZ_EVCP_CUSTOMER_MASTER/');
    }
  ).catch(error => {
    // withSoapLogging에서 이미 에러 로그를 처리하므로, 여기서는 응답만 생성
    return createErrorResponse(error);
  });
}

// XML 데이터를 DB 삽입 가능한 형태로 변환
/**
 * CUSTOMER 마스터 데이터 변환 함수
 * 
 * 데이터 처리 아키텍처:
 * 1. 최상위 테이블 (CUSTOMER_MASTER_BP_HEADER)
 *    - BP_HEADER가 unique 필드로 충돌 시 upsert 처리
 * 
 * 2. 하위 테이블들 (ADDRESS, BP_CUSGEN, BP_TAXNUM 등)
 *    - FK(BP_HEADER)로 연결
 *    - 별도 필수 필드 없음 (스키마에서 notNull() 제거 예정)
 *    - 전체 데이터셋 기반 삭제 후 재삽입 처리
 * 
 * 3. 중첩 하위 테이블들 (AD_EMAIL, ZVATREG, ZSALES 등)
 *    - 동일하게 FK(BP_HEADER)로 연결
 *    - 전체 데이터셋 기반 처리
 * 
 * @param bpHeaderData XML에서 파싱된 CUSTOMER 헤더 데이터
 * @returns 처리된 CUSTOMER 데이터 구조
 */
function transformCustomerData(bpHeaderData: BpHeaderXML[]): ProcessedCustomerData[] {
  if (!bpHeaderData || !Array.isArray(bpHeaderData)) {
    return [];
  }
  
  return bpHeaderData.map(bpHeader => {
    const bpHeaderKey = bpHeader.BP_HEADER || '';
    const fkData = { BP_HEADER: bpHeaderKey };
    
    // 1단계: BP_HEADER (루트 - unique 필드: BP_HEADER)
    const bpHeaderConverted = convertXMLToDBData<BpHeaderData>(
      bpHeader as Record<string, string | undefined>,
      fkData
    );
    
    // 2단계: 직속 하위 테이블들 (FK: BP_HEADER)
    const addresses = processNestedArray(
      bpHeader.ADDRESS,
      (addr) => convertXMLToDBData<AddressData>(addr as Record<string, string | undefined>, fkData),
      fkData
    );
    
    const bpTaxnums = processNestedArray(
      bpHeader.BP_TAXNUM,
      (item) => convertXMLToDBData<BpTaxnumData>(item as Record<string, string | undefined>, fkData),
      fkData
    );
    
    const bpCusgens = processNestedArray(
      bpHeader.BP_CUSGEN,
      (cusgen) => convertXMLToDBData<BpCusgenData>(cusgen as Record<string, string | undefined>, fkData),
      fkData
    );
    
    // 3단계: ADDRESS의 하위 테이블들 (FK: BP_HEADER)
    const adEmails = bpHeader.ADDRESS?.flatMap(addr => 
      processNestedArray(addr.AD_EMAIL, (item) => 
        convertXMLToDBData<AdEmailData>(item as Record<string, string | undefined>, fkData), fkData)
    ) || [];
    
    const adFaxes = bpHeader.ADDRESS?.flatMap(addr => 
      processNestedArray(addr.AD_FAX, (item) => 
        convertXMLToDBData<AdFaxData>(item as Record<string, string | undefined>, fkData), fkData)
    ) || [];
    
    const adPostals = bpHeader.ADDRESS?.flatMap(addr => 
      processNestedArray(addr.AD_POSTAL, (item) => 
        convertXMLToDBData<AdPostalData>(item as Record<string, string | undefined>, fkData), fkData)
    ) || [];
    
    const adTels = bpHeader.ADDRESS?.flatMap(addr => 
      processNestedArray(addr.AD_TEL, (item) => 
        convertXMLToDBData<AdTelData>(item as Record<string, string | undefined>, fkData), fkData)
    ) || [];
    
    const adUrls = bpHeader.ADDRESS?.flatMap(addr => 
      processNestedArray(addr.AD_URL, (item) => 
        convertXMLToDBData<AdUrlData>(item as Record<string, string | undefined>, fkData), fkData)
    ) || [];
    
    // 3단계: BP_CUSGEN의 하위 테이블들 (FK: BP_HEADER)
    const zvatregs = bpHeader.BP_CUSGEN?.flatMap(cusgen => 
      processNestedArray(cusgen.ZVATREG, (item) => 
        convertXMLToDBData<ZvatregData>(item as Record<string, string | undefined>, fkData), fkData)
    ) || [];
    
    const ztaxinds = bpHeader.BP_CUSGEN?.flatMap(cusgen => 
      processNestedArray(cusgen.ZTAXIND, (item) => 
        convertXMLToDBData<ZtaxindData>(item as Record<string, string | undefined>, fkData), fkData)
    ) || [];
    
    const zcompanies = bpHeader.BP_CUSGEN?.flatMap(cusgen => 
      processNestedArray(cusgen.ZCOMPANY, (item) => 
        convertXMLToDBData<ZcompanyData>(item as Record<string, string | undefined>, fkData), fkData)
    ) || [];
    
    const zsales = bpHeader.BP_CUSGEN?.flatMap(cusgen => 
      processNestedArray(cusgen.ZSALES, (item) => 
        convertXMLToDBData<ZsalesData>(item as Record<string, string | undefined>, fkData), fkData)
    ) || [];
    
    // 4단계: 더 깊은 중첩 테이블들 (FK: BP_HEADER)
    const zcpfns = bpHeader.BP_CUSGEN?.flatMap(cusgen => 
      cusgen.ZSALES?.flatMap(sales => 
        processNestedArray(sales.ZCPFN, (item) => 
          convertXMLToDBData<ZcpfnData>(item as Record<string, string | undefined>, fkData), fkData)
      ) || []
    ) || [];
    
    return {
      bpHeader: bpHeaderConverted,
      addresses,
      adEmails,
      adFaxes,
      adPostals,
      adTels,
      adUrls,
      bpCusgens,
      zvatregs,
      ztaxinds,
      zcompanies,
      zsales,
      zcpfns,
      bpTaxnums
    };
  });
}

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

      const bpHeaderKeys = bpHeaderRows.map((h) => h.BP_HEADER as string);

      // 2) 하위 테이블 데이터 평탄화
      const addresses = processedCustomers.flatMap((c) => c.addresses);
      const adEmails = processedCustomers.flatMap((c) => c.adEmails);
      const adFaxes = processedCustomers.flatMap((c) => c.adFaxes);
      const adPostals = processedCustomers.flatMap((c) => c.adPostals);
      const adTels = processedCustomers.flatMap((c) => c.adTels);
      const adUrls = processedCustomers.flatMap((c) => c.adUrls);
      const bpCusgens = processedCustomers.flatMap((c) => c.bpCusgens);
      const zvatregs = processedCustomers.flatMap((c) => c.zvatregs);
      const ztaxinds = processedCustomers.flatMap((c) => c.ztaxinds);
      const zcompanies = processedCustomers.flatMap((c) => c.zcompanies);
      const zsales = processedCustomers.flatMap((c) => c.zsales);
      const zcpfns = processedCustomers.flatMap((c) => c.zcpfns);
      const bpTaxnums = processedCustomers.flatMap((c) => c.bpTaxnums);

      // 3) 부모 테이블 UPSERT (배치)
      await bulkUpsert(tx, CUSTOMER_MASTER_BP_HEADER, bpHeaderRows, 'BP_HEADER');

      // 4) 하위 테이블 교체 (배치)
      await Promise.all([
        bulkReplaceSubTableData(tx, CUSTOMER_MASTER_BP_HEADER_ADDRESS, addresses, CUSTOMER_MASTER_BP_HEADER_ADDRESS.BP_HEADER, bpHeaderKeys),
        bulkReplaceSubTableData(tx, CUSTOMER_MASTER_BP_HEADER_ADDRESS_AD_EMAIL, adEmails, CUSTOMER_MASTER_BP_HEADER_ADDRESS_AD_EMAIL.BP_HEADER, bpHeaderKeys),
        bulkReplaceSubTableData(tx, CUSTOMER_MASTER_BP_HEADER_ADDRESS_AD_FAX, adFaxes, CUSTOMER_MASTER_BP_HEADER_ADDRESS_AD_FAX.BP_HEADER, bpHeaderKeys),
        bulkReplaceSubTableData(tx, CUSTOMER_MASTER_BP_HEADER_ADDRESS_AD_POSTAL, adPostals, CUSTOMER_MASTER_BP_HEADER_ADDRESS_AD_POSTAL.BP_HEADER, bpHeaderKeys),
        bulkReplaceSubTableData(tx, CUSTOMER_MASTER_BP_HEADER_ADDRESS_AD_TEL, adTels, CUSTOMER_MASTER_BP_HEADER_ADDRESS_AD_TEL.BP_HEADER, bpHeaderKeys),
        bulkReplaceSubTableData(tx, CUSTOMER_MASTER_BP_HEADER_ADDRESS_AD_URL, adUrls, CUSTOMER_MASTER_BP_HEADER_ADDRESS_AD_URL.BP_HEADER, bpHeaderKeys),
        bulkReplaceSubTableData(tx, CUSTOMER_MASTER_BP_HEADER_BP_CUSGEN, bpCusgens, CUSTOMER_MASTER_BP_HEADER_BP_CUSGEN.BP_HEADER, bpHeaderKeys),
        bulkReplaceSubTableData(tx, CUSTOMER_MASTER_BP_HEADER_BP_CUSGEN_ZVATREG, zvatregs, CUSTOMER_MASTER_BP_HEADER_BP_CUSGEN_ZVATREG.BP_HEADER, bpHeaderKeys),
        bulkReplaceSubTableData(tx, CUSTOMER_MASTER_BP_HEADER_BP_CUSGEN_ZTAXIND, ztaxinds, CUSTOMER_MASTER_BP_HEADER_BP_CUSGEN_ZTAXIND.BP_HEADER, bpHeaderKeys),
        bulkReplaceSubTableData(tx, CUSTOMER_MASTER_BP_HEADER_BP_CUSGEN_ZCOMPANY, zcompanies, CUSTOMER_MASTER_BP_HEADER_BP_CUSGEN_ZCOMPANY.BP_HEADER, bpHeaderKeys),
        bulkReplaceSubTableData(tx, CUSTOMER_MASTER_BP_HEADER_BP_CUSGEN_ZSALES, zsales, CUSTOMER_MASTER_BP_HEADER_BP_CUSGEN_ZSALES.BP_HEADER, bpHeaderKeys),
        bulkReplaceSubTableData(tx, CUSTOMER_MASTER_BP_HEADER_BP_CUSGEN_ZSALES_ZCPFN, zcpfns, CUSTOMER_MASTER_BP_HEADER_BP_CUSGEN_ZSALES_ZCPFN.BP_HEADER, bpHeaderKeys),
        bulkReplaceSubTableData(tx, CUSTOMER_MASTER_BP_HEADER_BP_TAXNUM, bpTaxnums, CUSTOMER_MASTER_BP_HEADER_BP_TAXNUM.BP_HEADER, bpHeaderKeys),
      ]);
    });

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