import { TableName } from '@/lib/oracle-db/nonsap/oracle-schema';

export interface TableSyncConfig {
  /** 차분 동기화에 사용할 타임스탬프 컬럼 (우선순위 순) */
  timestampColumns: string[];
  /** 기본키 컬럼들 */
  primaryKeys: string[];
  /** 정렬에 사용할 컬럼 (차분 동기화용) */
  orderByColumns: string[];
  /** 인덱스가 있는 컬럼인지 여부 */
  hasTimestampIndex: boolean;
  /** 예상 변경 빈도 (high/medium/low) */
  changeFrequency: 'high' | 'medium' | 'low';
}

// 테이블별 동기화 설정
export const TABLE_SYNC_CONFIG: Record<TableName, TableSyncConfig> = {
  // 자재 관련 테이블 (변경 빈도 높음)
  'CMCTB_MAT_BSE': {
    timestampColumns: ['FIN_CHG_DTM', 'FS_INP_DTM', 'IF_DT'],
    primaryKeys: ['MAT_NO'],
    orderByColumns: ['FIN_CHG_DTM', 'MAT_NO'],
    hasTimestampIndex: true,
    changeFrequency: 'high'
  },
  'CMCTB_MAT_PLNT': {
    timestampColumns: ['IF_DT', 'IF_TM'],
    primaryKeys: ['MAT_NO', 'PLNT'],
    orderByColumns: ['IF_DT', 'IF_TM', 'MAT_NO'],
    hasTimestampIndex: true,
    changeFrequency: 'high'
  },

  // Customer 관련 테이블 (변경 빈도 중간)
  'CMCTB_CUSTOMER_GENERAL': {
    timestampColumns: ['IF_DT', 'CHG_DT'],
    primaryKeys: ['CSTM_CD'],
    orderByColumns: ['IF_DT', 'IF_TM', 'CSTM_CD'],
    hasTimestampIndex: true,
    changeFrequency: 'medium'
  },
  'CMCTB_CUSTOMER_ADDR': {
    timestampColumns: ['IF_DT', 'IF_TM'],
    primaryKeys: ['CSTM_CD', 'ADR_NO', 'INTL_ADR_VER_ID'],
    orderByColumns: ['IF_DT', 'IF_TM', 'CSTM_CD'],
    hasTimestampIndex: true,
    changeFrequency: 'medium'
  },

  // 코드 관련 테이블 (변경 빈도 낮음) - 기본값으로 설정
  'CMCTB_CD': {
    timestampColumns: ['CHG_DT', 'CRTE_DT'],
    primaryKeys: [], // PostgreSQL 스키마에 기본키 미정의
    orderByColumns: ['CHG_DT', 'CHG_TM', 'CD_CLF'],
    hasTimestampIndex: false,
    changeFrequency: 'low'
  },
  'CMCTB_CDNM': {
    timestampColumns: ['CHG_DT', 'CHG_TM', 'CRTE_DT', 'CRTE_TM'],
    primaryKeys: [], // PostgreSQL 스키마에 기본키 미정의
    orderByColumns: ['CHG_DT', 'CHG_TM', 'LANG_KEY', 'CD_CLF'],
    hasTimestampIndex: false,
    changeFrequency: 'low'
  },
  'CMCTB_CD_CLF': {
    timestampColumns: ['XDATS', 'XTIMS', 'CHG_DT', 'CHG_TM'],
    primaryKeys: [], // PostgreSQL 스키마에 기본키 미정의
    orderByColumns: ['XDATS', 'XTIMS', 'CD_CLF'],
    hasTimestampIndex: false,
    changeFrequency: 'low'
  },
  'CMCTB_CD_CLF_NM': {
    timestampColumns: ['CHG_DT', 'CHG_TM', 'CRTE_DT', 'CRTE_TM'],
    primaryKeys: [], // PostgreSQL 스키마에 기본키 미정의
    orderByColumns: ['CHG_DT', 'CHG_TM', 'LANG_KEY', 'CD_CLF'],
    hasTimestampIndex: false,
    changeFrequency: 'low'
  },

  // Customer 관련 테이블 (변경 빈도 중간)
  'CMCTB_CUSTOMER_CFPN': {
    timestampColumns: ['IF_DT', 'IF_TM'],
    primaryKeys: ['CSTM_CD', 'SALE_ORG_CD', 'DIST_PATH', 'PDT_GRP', 'PTNR_SKL', 'PTNR_CNT'],
    orderByColumns: ['IF_DT', 'IF_TM', 'CSTM_CD'],
    hasTimestampIndex: true,
    changeFrequency: 'medium'
  },
  'CMCTB_CUSTOMER_COMPNY': {
    timestampColumns: ['IF_DT', 'IF_TM'],
    primaryKeys: ['CSTM_CD', 'CO_ID'],
    orderByColumns: ['IF_DT', 'IF_TM', 'CSTM_CD'],
    hasTimestampIndex: true,
    changeFrequency: 'medium'
  },
  'CMCTB_CUSTOMER_REPREMAIL': {
    timestampColumns: ['IF_DT', 'IF_TM'],
    primaryKeys: ['CSTM_CD', 'ADR_NO', 'REPR_SER', 'VLD_ST_DT'],
    orderByColumns: ['IF_DT', 'IF_TM', 'CSTM_CD'],
    hasTimestampIndex: true,
    changeFrequency: 'medium'
  },
  'CMCTB_CUSTOMER_REPRFAX': {
    timestampColumns: ['IF_DT', 'IF_TM'],
    primaryKeys: ['CSTM_CD', 'ADR_NO', 'REPR_SER', 'VLD_ST_DT'],
    orderByColumns: ['IF_DT', 'IF_TM', 'CSTM_CD'],
    hasTimestampIndex: true,
    changeFrequency: 'medium'
  },
  'CMCTB_CUSTOMER_REPRTEL': {
    timestampColumns: ['IF_DT', 'IF_TM'],
    primaryKeys: ['CSTM_CD', 'ADR_NO', 'REPR_SER', 'VLD_ST_DT'],
    orderByColumns: ['IF_DT', 'IF_TM', 'CSTM_CD'],
    hasTimestampIndex: true,
    changeFrequency: 'medium'
  },
  'CMCTB_CUSTOMER_REPRURL': {
    timestampColumns: ['IF_DT', 'IF_TM'],
    primaryKeys: ['CSTM_CD', 'ADR_NO', 'REPR_SER', 'VLD_ST_DT'],
    orderByColumns: ['IF_DT', 'IF_TM', 'CSTM_CD'],
    hasTimestampIndex: true,
    changeFrequency: 'medium'
  },
  'CMCTB_CUSTOMER_SORG': {
    timestampColumns: ['IF_DT', 'IF_TM'],
    primaryKeys: ['CSTM_CD', 'SALE_ORG_CD', 'DIST_PATH', 'PDT_GRP'],
    orderByColumns: ['IF_DT', 'IF_TM', 'CSTM_CD'],
    hasTimestampIndex: true,
    changeFrequency: 'medium'
  },
  'CMCTB_CUSTOMER_TAXCD': {
    timestampColumns: ['IF_DT', 'IF_TM'],
    primaryKeys: ['CSTM_CD', 'DPRT_NTN', 'TX_CTG'],
    orderByColumns: ['IF_DT', 'IF_TM', 'CSTM_CD'],
    hasTimestampIndex: true,
    changeFrequency: 'medium'
  },
  'CMCTB_CUSTOMER_TAXNUM': {
    timestampColumns: ['IF_DT', 'IF_TM'],
    primaryKeys: ['CSTM_CD', 'TX_NO_CTG'],
    orderByColumns: ['IF_DT', 'IF_TM', 'CSTM_CD'],
    hasTimestampIndex: true,
    changeFrequency: 'medium'
  },

  // 자재 관련 테이블 (변경 빈도 높음)
  'CMCTB_MAT_CLAS': {
    timestampColumns: ['CHG_DT'],
    primaryKeys: ['CLAS_CD'],
    orderByColumns: ['CHG_DT', 'CLAS_CD'],
    hasTimestampIndex: false,
    changeFrequency: 'medium'
  },
  'CMCTB_MAT_CLAS_SPCHAR': {
    timestampColumns: ['CHG_DT'],
    primaryKeys: ['CLAS_CD', 'SPCHAR_CD'],
    orderByColumns: ['CHG_DT', 'CLAS_CD'],
    hasTimestampIndex: false,
    changeFrequency: 'medium'
  },
  'CMCTB_MAT_DSC': {
    timestampColumns: [], // 시간 컬럼 없음
    primaryKeys: ['MAT_NO', 'LANG_KEY'],
    orderByColumns: ['MAT_NO', 'LANG_KEY'],
    hasTimestampIndex: false,
    changeFrequency: 'low'
  },
  'CMCTB_MAT_SPCHAR': {
    timestampColumns: [], // 시간 컬럼 없음
    primaryKeys: ['MAT_NO', 'SPCHAR_CD'],
    orderByColumns: ['MAT_NO', 'SPCHAR_CD'],
    hasTimestampIndex: false,
    changeFrequency: 'medium'
  },
  'CMCTB_MAT_SPCHAR_MAST': {
    timestampColumns: ['CHG_DT'],
    primaryKeys: ['SPCHAR_CD'],
    orderByColumns: ['CHG_DT', 'SPCHAR_CD'],
    hasTimestampIndex: false,
    changeFrequency: 'low'
  },
  'CMCTB_MAT_SPCHAR_VAL': {
    timestampColumns: ['CHG_DT'],
    primaryKeys: ['SPCHAR_CD', 'SPCHAR_VAL_CD'],
    orderByColumns: ['CHG_DT', 'SPCHAR_CD'],
    hasTimestampIndex: false,
    changeFrequency: 'low'
  },
  'CMCTB_MAT_UOM': {
    timestampColumns: [], // 시간 컬럼 없음
    primaryKeys: ['MAT_NO', 'SBST_UOM'],
    orderByColumns: ['MAT_NO', 'SBST_UOM'],
    hasTimestampIndex: false,
    changeFrequency: 'low'
  },

  // 프로젝트 관련 테이블
  'CMCTB_PROJ_BIZCLS': {
    timestampColumns: [], // 시간 컬럼 없음
    primaryKeys: ['PROJ_NO', 'TYPE'],
    orderByColumns: ['PROJ_NO', 'TYPE'],
    hasTimestampIndex: false,
    changeFrequency: 'medium'
  },
  'CMCTB_PROJ_MAST': {
    timestampColumns: ['XDATS', 'XTIMS'],
    primaryKeys: ['PROJ_NO'],
    orderByColumns: ['XDATS', 'XTIMS', 'PROJ_NO'],
    hasTimestampIndex: true,
    changeFrequency: 'medium'
  },
  'CMCTB_PROJ_WBS': {
    timestampColumns: ['XDATS', 'XTIMS'],
    primaryKeys: ['PROJ_NO', 'WBS_ELMT'],
    orderByColumns: ['XDATS', 'XTIMS', 'PROJ_NO'],
    hasTimestampIndex: true,
    changeFrequency: 'medium'
  },

  // Vendor 관련 테이블
  'CMCTB_VENDOR_ADDR': {
    timestampColumns: ['IF_DT', 'IF_TM'],
    primaryKeys: ['VNDRCD', 'INTL_ADR_VER_ID'],
    orderByColumns: ['IF_DT', 'IF_TM', 'VNDRCD'],
    hasTimestampIndex: true,
    changeFrequency: 'medium'
  },
  'CMCTB_VENDOR_COMPNY': {
    timestampColumns: ['IF_DT', 'IF_TM'],
    primaryKeys: ['VNDRCD', 'CO_CD'],
    orderByColumns: ['IF_DT', 'IF_TM', 'VNDRCD'],
    hasTimestampIndex: true,
    changeFrequency: 'medium'
  },
  'CMCTB_VENDOR_GENERAL': {
    timestampColumns: ['IF_DT', 'IF_TM', 'CHG_DT', 'CHG_TM'],
    primaryKeys: ['VNDRCD'],
    orderByColumns: ['IF_DT', 'IF_TM', 'VNDRCD'],
    hasTimestampIndex: true,
    changeFrequency: 'medium'
  },
  'CMCTB_VENDOR_GRP': {
    timestampColumns: ['CHG_DT', 'CHG_TM', 'CRTE_DT', 'CRTE_TM'],
    primaryKeys: ['VNDRCD', 'BIZ_GRP_CD'],
    orderByColumns: ['CHG_DT', 'CHG_TM', 'VNDRCD'],
    hasTimestampIndex: false,
    changeFrequency: 'medium'
  },
  'CMCTB_VENDOR_INCO': {
    timestampColumns: ['CHG_DT', 'CHG_TM', 'CRTE_DT', 'CRTE_TM'],
    primaryKeys: ['VNDRCD'],
    orderByColumns: ['CHG_DT', 'CHG_TM', 'VNDRCD'],
    hasTimestampIndex: false,
    changeFrequency: 'medium'
  },
  'CMCTB_VENDOR_PORG': {
    timestampColumns: ['IF_DT', 'IF_TM'],
    primaryKeys: ['VNDRCD', 'PUR_ORG_CD'],
    orderByColumns: ['IF_DT', 'IF_TM', 'VNDRCD'],
    hasTimestampIndex: true,
    changeFrequency: 'medium'
  },
  'CMCTB_VENDOR_REPREMAIL': {
    timestampColumns: ['IF_DT', 'IF_TM'],
    primaryKeys: ['VNDRCD', 'REPR_SER', 'VLD_ST_DT'],
    orderByColumns: ['IF_DT', 'IF_TM', 'VNDRCD'],
    hasTimestampIndex: true,
    changeFrequency: 'medium'
  },
  'CMCTB_VENDOR_REPRFAX': {
    timestampColumns: ['IF_DT', 'IF_TM'],
    primaryKeys: ['VNDRCD', 'REPR_SER', 'VLD_ST_DT'],
    orderByColumns: ['IF_DT', 'IF_TM', 'VNDRCD'],
    hasTimestampIndex: true,
    changeFrequency: 'medium'
  },
  'CMCTB_VENDOR_REPRTEL': {
    timestampColumns: ['IF_DT', 'IF_TM'],
    primaryKeys: ['VNDRCD', 'REPR_SER', 'VLD_ST_DT'],
    orderByColumns: ['IF_DT', 'IF_TM', 'VNDRCD'],
    hasTimestampIndex: true,
    changeFrequency: 'medium'
  },
  'CMCTB_VENDOR_REPRURL': {
    timestampColumns: ['IF_DT', 'IF_TM'],
    primaryKeys: ['VNDRCD', 'REPR_SER', 'VLD_ST_DT'],
    orderByColumns: ['IF_DT', 'IF_TM', 'VNDRCD'],
    hasTimestampIndex: true,
    changeFrequency: 'medium'
  },
  'CMCTB_VENDOR_TAXNUM': {
    timestampColumns: ['IF_DT', 'IF_TM'],
    primaryKeys: ['VNDRCD', 'TX_NO_CTG'],
    orderByColumns: ['IF_DT', 'IF_TM', 'VNDRCD'],
    hasTimestampIndex: true,
    changeFrequency: 'medium'
  },
  'CMCTB_VENDOR_VFPN': {
    timestampColumns: ['IF_DT', 'IF_TM'],
    primaryKeys: ['VNDRCD', 'PUR_ORG_CD', 'VNDR_SUB_NO', 'PLNT_CD', 'PTNR_SKL', 'PTNR_CNT'],
    orderByColumns: ['IF_DT', 'IF_TM', 'VNDRCD'],
    hasTimestampIndex: true,
    changeFrequency: 'medium'
  },
  'CMCTB_VENDOR_WHTHX': {
    timestampColumns: ['IF_DT', 'IF_TM'],
    primaryKeys: ['VNDRCD', 'CO_CD', 'SRCE_TX_TP'],
    orderByColumns: ['IF_DT', 'IF_TM', 'VNDRCD'],
    hasTimestampIndex: true,
    changeFrequency: 'medium'
  },

  // 견적 관련 테이블
  'PLFTB_ESTM_PROJ_MAST': {
    timestampColumns: ['FIN_CHG_DTM', 'FS_INP_DTM', 'ESTM_AOM_STAT_CHG_DTM'],
    primaryKeys: ['ESTM_PROJ_NO'],
    orderByColumns: ['FIN_CHG_DTM', 'ESTM_PROJ_NO'],
    hasTimestampIndex: true,
    changeFrequency: 'high'
  }
};

// 기본 설정 (설정되지 않은 테이블용)
const DEFAULT_CONFIG: TableSyncConfig = {
  timestampColumns: ['CHG_DT', 'IF_DT', 'CRTE_DT'],
  primaryKeys: [], // 기본키 없음 - onConflictDoNothing 사용
  orderByColumns: ['CHG_DT'],
  hasTimestampIndex: false,
  changeFrequency: 'medium'
};

/**
 * 테이블의 동기화 설정 가져오기
 */
export function getTableSyncConfig(tableName: TableName): TableSyncConfig {
  return TABLE_SYNC_CONFIG[tableName] || DEFAULT_CONFIG;
}

/**
 * 차분 동기화가 가능한 테이블인지 확인
 */
export function canUseDeltaSync(tableName: TableName): boolean {
  const config = getTableSyncConfig(tableName);
  return config.timestampColumns.length > 0 && config.timestampColumns[0] !== 'ROWID';
}

/**
 * 테이블의 최적 타임스탬프 컬럼 가져오기
 */
export function getTimestampColumn(tableName: TableName): string | null {
  const config = getTableSyncConfig(tableName);
  return config.timestampColumns.length > 0 ? config.timestampColumns[0] : null;
}