package incheon.ags.ias.dataHoprReg.service.impl;

import incheon.ags.ias.dataHoprReg.mapper.JbgdSyncMapper;
import incheon.ags.ias.dataHoprReg.service.JbgdSyncService;
import incheon.ags.ias.dataHoprReg.util.DataFileParseUtil;
import incheon.ags.ias.dataHoprReg.vo.JbgdVO;
import incheon.com.cmm.exception.BusinessException;
import incheon.com.file.service.ComFileService;
import incheon.com.file.vo.ComFileDtlVO;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.egovframe.rte.fdl.cmmn.EgovAbstractServiceImpl;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.io.File;
import java.nio.file.Paths;
import java.util.*;

/**
 * 직급 동기화 서비스 구현
 */
@Slf4j
@Service("jbgdSyncService")
@RequiredArgsConstructor
public class JbgdSyncServiceImpl extends EgovAbstractServiceImpl implements JbgdSyncService {

    private final JbgdSyncMapper jbgdSyncMapper;
    private final ComFileService comFileService;

    @Value("${Globals.comfile.storage.path:../upload}")
    private String uploadPath;

    /**
     * 파일 헤더 → DB 컬럼 매핑 (직급)
     * 원천파일 헤더: 직급코드, 공무원구분코드, 직종, 직종세분류코드, 직군코드, 직렬코드, 직류코드, 계급코드, 직급명, 폐지여부
     */
    private static final Map<String, String> JBGD_HEADER_TO_FIELD = new LinkedHashMap<>();
    static {
        // 직급코드 (jbgdCd)
        JBGD_HEADER_TO_FIELD.put("직급코드", "jbgdCd");
        JBGD_HEADER_TO_FIELD.put("jbgd_cd", "jbgdCd");
        // 관직구분코드 (pbofcSeCd) - 원천파일: "공무원구분코드"
        JBGD_HEADER_TO_FIELD.put("공무원구분코드", "pbofcSeCd");
        JBGD_HEADER_TO_FIELD.put("관직구분코드", "pbofcSeCd");
        JBGD_HEADER_TO_FIELD.put("pbofc_se_cd", "pbofcSeCd");
        // 직업 (ocpt) - 원천파일: "직종"
        JBGD_HEADER_TO_FIELD.put("직종", "ocpt");
        JBGD_HEADER_TO_FIELD.put("직업", "ocpt");
        JBGD_HEADER_TO_FIELD.put("ocpt", "ocpt");
        // 직업상세분류코드 (ocptDclsfCd) - 원천파일: "직종세분류코드"
        JBGD_HEADER_TO_FIELD.put("직종세분류코드", "ocptDclsfCd");
        JBGD_HEADER_TO_FIELD.put("직업상세분류코드", "ocptDclsfCd");
        JBGD_HEADER_TO_FIELD.put("ocpt_dclsf_cd", "ocptDclsfCd");
        // 직군코드 (jgrpCd)
        JBGD_HEADER_TO_FIELD.put("직군코드", "jgrpCd");
        JBGD_HEADER_TO_FIELD.put("jgrp_cd", "jgrpCd");
        // 직렬코드 (jbsrsCd)
        JBGD_HEADER_TO_FIELD.put("직렬코드", "jbsrsCd");
        JBGD_HEADER_TO_FIELD.put("jbsrs_cd", "jbsrsCd");
        // 직무코드 (tpocCd) - 원천파일: "직류코드"
        JBGD_HEADER_TO_FIELD.put("직류코드", "tpocCd");
        JBGD_HEADER_TO_FIELD.put("tpoc_cd", "tpocCd");
        // 직위등급코드 (echlnCd) - 원천파일: "계급코드"
        JBGD_HEADER_TO_FIELD.put("계급코드", "echlnCd");
        JBGD_HEADER_TO_FIELD.put("echln_cd", "echlnCd");
        // 직급명 (jbgdNm)
        JBGD_HEADER_TO_FIELD.put("직급명", "jbgdNm");
        JBGD_HEADER_TO_FIELD.put("jbgd_nm", "jbgdNm");
        // 사용여부 (ablYn) - 원천파일: "폐지여부"
        JBGD_HEADER_TO_FIELD.put("폐지여부", "ablYn");
        JBGD_HEADER_TO_FIELD.put("사용여부", "ablYn");
        JBGD_HEADER_TO_FIELD.put("abl_yn", "ablYn");
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public int syncFromFile(String atchFileId, String operatorId) throws Exception {
        log.info("========== 직급 파일 동기화 시작 (atchFileId: {}) ==========", atchFileId);

        // 1. 파일 정보 조회
        List<ComFileDtlVO> fileList = comFileService.selectComFileDtlList(atchFileId);
        if (fileList == null || fileList.isEmpty()) {
            throw new BusinessException("첨부파일 정보를 찾을 수 없습니다.");
        }
        ComFileDtlVO fileInfo = fileList.get(0);

        // 2. 파일 경로 구성
        String filePath = Paths.get(uploadPath, fileInfo.getStrgPathNm(), fileInfo.getStrgFileNm()).toString();
        File file = new File(filePath);
        if (!file.exists()) {
            throw new BusinessException("파일이 존재하지 않습니다: " + filePath);
        }
        log.info("파일 경로: {}", filePath);

        // 3. 파일 파싱 (MS949 우선)
        List<String> headers = new ArrayList<>();
        List<List<String>> dataRows = DataFileParseUtil.parseFile(file, false, headers);
        log.info("파싱 완료 - 헤더: {}, 데이터 행 수: {}", headers.size(), dataRows.size());

        // 4. VO 변환
        List<JbgdVO> jbgdList = new ArrayList<>();
        for (List<String> row : dataRows) {
            JbgdVO vo = convertToJbgdVO(headers, row);
            if (vo.getJbgdCd() != null && !vo.getJbgdCd().isEmpty()) {
                jbgdList.add(vo);
            }
        }
        log.info("유효한 직급 데이터 건수: {}", jbgdList.size());

        // 5. 동기화 실행
        Map<String, Integer> syncResult = syncJbgds(jbgdList, operatorId);
        int totalProcessed = syncResult.get("inserted") + syncResult.get("updated") + syncResult.get("deleted");

        log.info("========== 직급 파일 동기화 완료 ==========");
        return totalProcessed;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public Map<String, Integer> syncJbgds(List<JbgdVO> fileDataList, String operatorId) throws Exception {
        log.info("========== 직급 동기화 시작 ==========");
        log.info("파일 데이터 건수: {}", fileDataList.size());

        List<JbgdVO> existingList = jbgdSyncMapper.selectAllJbgds();
        Map<String, JbgdVO> existingMap = new HashMap<>();
        for (JbgdVO jbgd : existingList) {
            existingMap.put(jbgd.getJbgdCd(), jbgd);
        }
        log.info("기존 DB 직급 건수: {}", existingMap.size());

        Map<String, JbgdVO> fileMap = new HashMap<>();
        for (JbgdVO fileJbgd : fileDataList) {
            if (fileJbgd.getJbgdCd() != null && !fileJbgd.getJbgdCd().isEmpty()) {
                fileMap.put(fileJbgd.getJbgdCd(), fileJbgd);
            }
        }

        List<JbgdVO> toInsert = new ArrayList<>();
        List<JbgdVO> toUpdate = new ArrayList<>();
        List<String> toDelete = new ArrayList<>();
        int unchangedCount = 0;

        for (Map.Entry<String, JbgdVO> entry : fileMap.entrySet()) {
            String jbgdCd = entry.getKey();
            JbgdVO fileJbgd = entry.getValue();

            if (!existingMap.containsKey(jbgdCd)) {
                toInsert.add(fileJbgd);
            } else {
                JbgdVO existing = existingMap.get(jbgdCd);
                if (isJbgdChanged(existing, fileJbgd)) {
                    toUpdate.add(fileJbgd);
                } else {
                    unchangedCount++;
                }
            }
        }

        for (String existingCd : existingMap.keySet()) {
            if (!fileMap.containsKey(existingCd)) {
                toDelete.add(existingCd);
            }
        }

        log.info("INSERT: {}, UPDATE: {}, DELETE: {}, 변경없음: {}",
                toInsert.size(), toUpdate.size(), toDelete.size(), unchangedCount);

        for (JbgdVO jbgd : toUpdate) {
            jbgdSyncMapper.updateJbgd(jbgd);
        }
        for (String jbgdCd : toDelete) {
            jbgdSyncMapper.deleteJbgd(jbgdCd);
        }
        for (JbgdVO jbgd : toInsert) {
            jbgdSyncMapper.insertJbgd(jbgd);
        }

        log.info("========== 직급 동기화 완료 ==========");

        Map<String, Integer> result = new HashMap<>();
        result.put("inserted", toInsert.size());
        result.put("updated", toUpdate.size());
        result.put("deleted", toDelete.size());
        result.put("unchanged", unchangedCount);
        return result;
    }

    private boolean isJbgdChanged(JbgdVO existing, JbgdVO file) {
        if (!equalsNullSafe(existing.getJbgdNm(), file.getJbgdNm())) return true;
        if (!equalsNullSafe(existing.getPbofcSeCd(), file.getPbofcSeCd())) return true;
        if (!equalsNullSafe(existing.getOcpt(), file.getOcpt())) return true;
        if (!equalsNullSafe(existing.getOcptDclsfCd(), file.getOcptDclsfCd())) return true;
        if (!equalsNullSafe(existing.getJgrpCd(), file.getJgrpCd())) return true;
        if (!equalsNullSafe(existing.getJbsrsCd(), file.getJbsrsCd())) return true;
        if (!equalsNullSafe(existing.getTpocCd(), file.getTpocCd())) return true;
        if (!equalsNullSafe(existing.getEchlnCd(), file.getEchlnCd())) return true;
        if (!equalsNullSafe(existing.getAblYn(), file.getAblYn())) return true;
        return false;
    }

    @Override
    public JbgdVO convertToJbgdVO(List<String> headers, List<String> values) {
        JbgdVO vo = new JbgdVO();
        for (int i = 0; i < headers.size() && i < values.size(); i++) {
            String header = headers.get(i).trim().toLowerCase();
            String value = values.get(i).trim();
            String field = JBGD_HEADER_TO_FIELD.get(header);
            if (field == null) {
                for (Map.Entry<String, String> entry : JBGD_HEADER_TO_FIELD.entrySet()) {
                    if (entry.getKey().equalsIgnoreCase(header)) {
                        field = entry.getValue();
                        break;
                    }
                }
            }
            if (field != null && !value.isEmpty()) {
                setJbgdFieldValue(vo, field, value);
            }
        }
        return vo;
    }

    private void setJbgdFieldValue(JbgdVO vo, String field, String value) {
        switch (field) {
            case "jbgdCd": vo.setJbgdCd(value); break;
            case "pbofcSeCd": vo.setPbofcSeCd(value); break;
            case "ocpt": vo.setOcpt(value); break;
            case "ocptDclsfCd": vo.setOcptDclsfCd(value); break;
            case "jgrpCd": vo.setJgrpCd(value); break;
            case "jbsrsCd": vo.setJbsrsCd(value); break;
            case "tpocCd": vo.setTpocCd(value); break;
            case "echlnCd": vo.setEchlnCd(value); break;
            case "jbgdNm": vo.setJbgdNm(value); break;
            case "ablYn": vo.setAblYn(value); break;
        }
    }

    private boolean equalsNullSafe(String a, String b) {
        String sa = (a == null) ? "" : a.trim();
        String sb = (b == null) ? "" : b.trim();
        return sa.equals(sb);
    }
}