import { debugLog, debugSuccess, debugError } from '@/lib/debug-utils';
import db from '@/db/db';
import { projects } from '@/db/schema/projects';
import { PROJECT_MASTER_CMCTB_PROJ_MAST } from '@/db/schema/MDG/mdg';
import { eq } from 'drizzle-orm';

// MDG 데이터 타입 정의
export type MDGProjectData = typeof PROJECT_MASTER_CMCTB_PROJ_MAST.$inferInsert;

// 비즈니스 테이블 데이터 타입 정의
export type ProjectData = typeof projects.$inferInsert;

/**
 * MDG 프로젝트 마스터 데이터를 비즈니스 테이블 projects에 매핑하여 저장
 */
export async function mapAndSaveMDGProjectData(
  mdgProjects: MDGProjectData[]
): Promise<{ success: boolean; message: string; processedCount: number }> {
  try {
    debugLog('MDG 프로젝트 마스터 데이터 매핑 시작', { count: mdgProjects.length });

    if (mdgProjects.length === 0) {
      return { success: true, message: '처리할 데이터가 없습니다', processedCount: 0 };
    }

    const mappedProjects: ProjectData[] = [];
    let processedCount = 0;

    for (const mdgProject of mdgProjects) {
      try {
        // MDG 데이터를 projects 테이블 구조에 매핑
        const mappedProject = mapMDGToProject(mdgProject);
        
        if (mappedProject) {
          mappedProjects.push(mappedProject);
          processedCount++;
        }
      } catch (error) {
        debugError('개별 프로젝트 매핑 중 오류', { 
          project: mdgProject.PROJ_NO, 
          error: error instanceof Error ? error.message : 'Unknown error' 
        });
        // 개별 오류는 로그만 남기고 계속 진행
        continue;
      }
    }

    if (mappedProjects.length === 0) {
      return { success: false, message: '매핑된 프로젝트가 없습니다', processedCount: 0 };
    }

    // 데이터베이스에 저장
    await saveProjectsToDatabase(mappedProjects);

    debugSuccess('MDG 프로젝트 마스터 데이터 매핑 완료', { 
      total: mdgProjects.length, 
      processed: processedCount 
    });

    return { 
      success: true, 
      message: `${processedCount}개 프로젝트 매핑 및 저장 완료`, 
      processedCount 
    };

  } catch (error) {
    const errorMessage = error instanceof Error ? error.message : 'Unknown error';
    debugError('MDG 프로젝트 마스터 데이터 매핑 중 오류 발생', { error: errorMessage });
    return { 
      success: false, 
      message: `매핑 실패: ${errorMessage}`, 
      processedCount: 0 
    };
  }
}

/**
 * MDG 프로젝트 데이터를 비즈니스 테이블 projects 구조로 변환
 * 모든 MDG 컬럼을 비즈니스 테이블에 매핑
 */
function mapMDGToProject(mdgProject: MDGProjectData): ProjectData | null {
  try {
    // 필수 필드 검증
    if (!mdgProject.PROJ_NO) {
      debugError('PROJ_NO가 없는 프로젝트 데이터', { project: mdgProject });
      return null;
    }

    // MDG의 모든 컬럼을 비즈니스 테이블에 매핑
    const mappedProject: ProjectData = {
      // 기존 매핑 컬럼들
      code: mdgProject.PROJ_NO || '',
      name: mdgProject.PROJ_DSC || '',
      type: checkProjectType(mdgProject.BIZCLS || '', mdgProject.QM_CLS || ''), // BIZCLS와 QM_CLS를 기준으로 판단
      
      // MDG의 모든 컬럼 매핑 (그대로 수신)
      AS_GRNT_PRD: mdgProject.AS_GRNT_PRD || null,
      BIZCLS: mdgProject.BIZCLS || null,
      BIZLOC_CD: mdgProject.BIZLOC_CD || null,
      BIZ_DMN: mdgProject.BIZ_DMN || null,
      BP_DL_DT: mdgProject.BP_DL_DT || null,
      CHN_PROJ_TP: mdgProject.CHN_PROJ_TP || null,
      CLS_1: mdgProject.CLS_1 || null,
      CLS_2: mdgProject.CLS_2 || null,
      CNRT_CNTN_YN: mdgProject.CNRT_CNTN_YN || null,
      CNRT_DL_DT: mdgProject.CNRT_DL_DT || null,
      CNRT_DT: mdgProject.CNRT_DT || null,
      CNRT_RESV_YN: mdgProject.CNRT_RESV_YN || null,
      CO_CD: mdgProject.CO_CD || null,
      CSTM_PO_NO: mdgProject.CSTM_PO_NO || null,
      DEL_YN: mdgProject.DEL_YN || null,
      DIGT_PDT_GRP: mdgProject.DIGT_PDT_GRP || null,
      DIST_PATH: mdgProject.DIST_PATH || null,
      DL_BF_PROJ_NM: mdgProject.DL_BF_PROJ_NM || null,
      DL_CSTM_CD: mdgProject.DL_CSTM_CD || null,
      DOCK_CD: mdgProject.DOCK_CD || null,
      DSN_CHRGR: mdgProject.DSN_CHRGR || null,
      FIN_GRNT_FN_DT: mdgProject.FIN_GRNT_FN_DT || null,
      GENT_CNT: mdgProject.GENT_CNT || null,
      GOV: mdgProject.GOV || null,
      GRNT_STDT: mdgProject.GRNT_STDT || null,
      IF_STAT: mdgProject.IF_STAT || null,
      IMO_NO: mdgProject.IMO_NO || null,
      INQY_NO: mdgProject.INQY_NO || null,
      INQY_SEQ: mdgProject.INQY_SEQ || null,
      IO_GB: mdgProject.IO_GB || null,
      MNG_ACOT_DMN: mdgProject.MNG_ACOT_DMN || null,
      MN_ENGN_TP_CD: mdgProject.MN_ENGN_TP_CD || null,
      MSHIP_NO: mdgProject.MSHIP_NO || null,
      NEW_MC_YN: mdgProject.NEW_MC_YN || null,
      NTTP: mdgProject.NTTP || null,
      ORDR_GRNT_FN_DT: mdgProject.ORDR_GRNT_FN_DT || null,
      ORDR_GRNT_PRD: mdgProject.ORDR_GRNT_PRD || null,
      OWN_1: mdgProject.OWN_1 || null,
      OWN_AB: mdgProject.OWN_AB || null,
      OWN_NM: mdgProject.OWN_NM || null,
      PDT_LVL_4: mdgProject.PDT_LVL_4 || null,
      PLNT_CD: mdgProject.PLNT_CD || null,
      PRCTR: mdgProject.PRCTR || null,
      PRGS_STAT: mdgProject.PRGS_STAT || null,
      PROJ_CRTE_REQ_DT: mdgProject.PROJ_CRTE_REQ_DT || null,
      PROJ_CRTE_REQ_EMPNO: mdgProject.PROJ_CRTE_REQ_EMPNO || null,
      PROJ_DL_PLN_DT: mdgProject.PROJ_DL_PLN_DT || null,
      PROJ_DL_RT_DT: mdgProject.PROJ_DL_RT_DT || null,
      PROJ_DTL_TP: mdgProject.PROJ_DTL_TP || null,
      PROJ_ETC_TP: mdgProject.PROJ_ETC_TP || null,
      PROJ_GB: mdgProject.PROJ_GB || null,
      PROJ_PRGS_YN: mdgProject.PROJ_PRGS_YN || null,
      PROJ_PROF: mdgProject.PROJ_PROF || null,
      PROJ_SCP: mdgProject.PROJ_SCP || null,
      PROJ_WBS_TP: mdgProject.PROJ_WBS_TP || null,
      PRO_PROJ_NO: mdgProject.PRO_PROJ_NO || null,
      QM_CLS: mdgProject.QM_CLS || null,
      REF_NO: mdgProject.REF_NO || null,
      RLTD_PROJ: mdgProject.RLTD_PROJ || null,
      RL_DL_DT: mdgProject.RL_DL_DT || null,
      SALE_GRP: mdgProject.SALE_GRP || null,
      SALE_ORG_CD: mdgProject.SALE_ORG_CD || null,
      SA_DT: mdgProject.SA_DT || null,
      SERS_NO: mdgProject.SERS_NO || null,
      SERS_YN: mdgProject.SERS_YN || null,
      SHTYPE: mdgProject.SHTYPE || null,
      SHTYPE_CD: mdgProject.SHTYPE_CD || null,
      SHTYPE_GRP: mdgProject.SHTYPE_GRP || null,
      SHTYPE_UOM: mdgProject.SHTYPE_UOM || null,
      SKND: mdgProject.SKND || null,
      SRC_SYS_ID: mdgProject.SRC_SYS_ID || null,
      STDT: mdgProject.STDT || null,
      SYS_ACOT_CLSD_DT: mdgProject.SYS_ACOT_CLSD_DT || null,
      TOT_CNRT_CNT: mdgProject.TOT_CNRT_CNT || null,
      TYPE_MDG: mdgProject.TYPE || null, // 원본 TYPE 컬럼
      WP_PROJ_TP: mdgProject.WP_PROJ_TP || null,
      
      // id, createdAt, updatedAt는 자동 생성
    };

    debugLog('프로젝트 매핑 완료', { 
      original: mdgProject.PROJ_NO, 
      mapped: mappedProject.code 
    });

    return mappedProject;

  } catch (error) {
    debugError('프로젝트 매핑 중 오류', { 
      project: mdgProject.PROJ_NO, 
      error: error instanceof Error ? error.message : 'Unknown error' 
    });
    return null;
  }
}

/**
 * 매핑된 프로젝트 데이터를 데이터베이스에 저장
 */
async function saveProjectsToDatabase(mappedProjects: ProjectData[]): Promise<void> {
  try {
    debugLog('프로젝트 데이터베이스 저장 시작', { count: mappedProjects.length });

    await db.transaction(async (tx) => {
      // 기존 데이터와 중복 체크 및 UPSERT
      for (const project of mappedProjects) {
        if (project.code) {
          // code가 있는 경우 기존 데이터 확인
          const existingProject = await tx
            .select({ id: projects.id })
            .from(projects)
            .where(eq(projects.code, project.code))
            .limit(1);

          if (existingProject.length > 0) {
            // 기존 데이터 업데이트 - 모든 MDG 컬럼 포함
            await tx
              .update(projects)
              .set({
                code: project.code,
                name: project.name,
                type: project.type,
                AS_GRNT_PRD: project.AS_GRNT_PRD,
                BIZCLS: project.BIZCLS,
                BIZLOC_CD: project.BIZLOC_CD,
                BIZ_DMN: project.BIZ_DMN,
                BP_DL_DT: project.BP_DL_DT,
                CHN_PROJ_TP: project.CHN_PROJ_TP,
                CLS_1: project.CLS_1,
                CLS_2: project.CLS_2,
                CNRT_CNTN_YN: project.CNRT_CNTN_YN,
                CNRT_DL_DT: project.CNRT_DL_DT,
                CNRT_DT: project.CNRT_DT,
                CNRT_RESV_YN: project.CNRT_RESV_YN,
                CO_CD: project.CO_CD,
                CSTM_PO_NO: project.CSTM_PO_NO,
                DEL_YN: project.DEL_YN,
                DIGT_PDT_GRP: project.DIGT_PDT_GRP,
                DIST_PATH: project.DIST_PATH,
                DL_BF_PROJ_NM: project.DL_BF_PROJ_NM,
                DL_CSTM_CD: project.DL_CSTM_CD,
                DOCK_CD: project.DOCK_CD,
                DSN_CHRGR: project.DSN_CHRGR,
                FIN_GRNT_FN_DT: project.FIN_GRNT_FN_DT,
                GENT_CNT: project.GENT_CNT,
                GOV: project.GOV,
                GRNT_STDT: project.GRNT_STDT,
                IF_STAT: project.IF_STAT,
                IMO_NO: project.IMO_NO,
                INQY_NO: project.INQY_NO,
                INQY_SEQ: project.INQY_SEQ,
                IO_GB: project.IO_GB,
                MNG_ACOT_DMN: project.MNG_ACOT_DMN,
                MN_ENGN_TP_CD: project.MN_ENGN_TP_CD,
                MSHIP_NO: project.MSHIP_NO,
                NEW_MC_YN: project.NEW_MC_YN,
                NTTP: project.NTTP,
                ORDR_GRNT_FN_DT: project.ORDR_GRNT_FN_DT,
                ORDR_GRNT_PRD: project.ORDR_GRNT_PRD,
                OWN_1: project.OWN_1,
                OWN_AB: project.OWN_AB,
                OWN_NM: project.OWN_NM,
                PDT_LVL_4: project.PDT_LVL_4,
                PLNT_CD: project.PLNT_CD,
                PRCTR: project.PRCTR,
                PRGS_STAT: project.PRGS_STAT,
                PROJ_CRTE_REQ_DT: project.PROJ_CRTE_REQ_DT,
                PROJ_CRTE_REQ_EMPNO: project.PROJ_CRTE_REQ_EMPNO,
                PROJ_DL_PLN_DT: project.PROJ_DL_PLN_DT,
                PROJ_DL_RT_DT: project.PROJ_DL_RT_DT,
                PROJ_DTL_TP: project.PROJ_DTL_TP,
                PROJ_ETC_TP: project.PROJ_ETC_TP,
                PROJ_GB: project.PROJ_GB,
                PROJ_PRGS_YN: project.PROJ_PRGS_YN,
                PROJ_PROF: project.PROJ_PROF,
                PROJ_SCP: project.PROJ_SCP,
                PROJ_WBS_TP: project.PROJ_WBS_TP,
                PRO_PROJ_NO: project.PRO_PROJ_NO,
                QM_CLS: project.QM_CLS,
                REF_NO: project.REF_NO,
                RLTD_PROJ: project.RLTD_PROJ,
                RL_DL_DT: project.RL_DL_DT,
                SALE_GRP: project.SALE_GRP,
                SALE_ORG_CD: project.SALE_ORG_CD,
                SA_DT: project.SA_DT,
                SERS_NO: project.SERS_NO,
                SERS_YN: project.SERS_YN,
                SHTYPE: project.SHTYPE,
                SHTYPE_CD: project.SHTYPE_CD,
                SHTYPE_GRP: project.SHTYPE_GRP,
                SHTYPE_UOM: project.SHTYPE_UOM,
                SKND: project.SKND,
                SRC_SYS_ID: project.SRC_SYS_ID,
                STDT: project.STDT,
                SYS_ACOT_CLSD_DT: project.SYS_ACOT_CLSD_DT,
                TOT_CNRT_CNT: project.TOT_CNRT_CNT,
                TYPE_MDG: project.TYPE_MDG,
                WP_PROJ_TP: project.WP_PROJ_TP,
                updatedAt: new Date(),
              })
              .where(eq(projects.code, project.code));
          } else {
            // 새 데이터 삽입
            await tx.insert(projects).values(project);
          }
        } else {
          // code가 없는 경우 새 데이터 삽입
          await tx.insert(projects).values(project);
        }
      }
    });

    debugSuccess('프로젝트 데이터베이스 저장 완료', { count: mappedProjects.length });

  } catch (error) {
    debugError('프로젝트 데이터베이스 저장 중 오류', { error });
    throw error;
  }
}

// === Utility Functions ===

function checkProjectType(bizCls: string, qmCls: string) {
  // 둘 다 null이면 ship
  if (!bizCls && !qmCls) {
    return 'ship';
  }
  // 하나라도 'S'이면 ship
  if (bizCls === 'S' || qmCls === 'S') {
    return 'ship';
  }
  // 그 외는 plant
  return 'plant';
}