package com.erp.service;

import com.erp.common.BaseService;
import com.fasterxml.jackson.databind.ObjectMapper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.*;

@Service
@Slf4j
public class BatchService extends BaseService {

    @Autowired
    private CommonService commonService;

    @Autowired
    private ObjectMapper objectMapper;

    private static final String NS = "batch.";
    private static final String EXT_NS = "externalDbConnection.";

    public Map<String, Object> getBatchList(Map<String, Object> params) {
        commonService.applyCompanyCodeFilter(params);
        commonService.applyPagination(params);
        Integer totalObj = sqlSession.selectOne(NS + "getBatchListCnt", params);
        int totalCount = totalObj != null ? totalObj : 0;
        List<Map<String, Object>> list = sqlSession.selectList(NS + "getBatchList", params);
        return commonService.buildListResponse(list, totalCount, params);
    }

    public Map<String, Object> getBatchInfo(Map<String, Object> params) {
        commonService.applyCompanyCodeFilter(params);
        Map<String, Object> batch = sqlSession.selectOne(NS + "getBatchInfo", params);
        if (batch != null) {
            attachMappings(batch);
        }
        return batch;
    }

    @Transactional
    public Map<String, Object> insertBatch(Map<String, Object> params) {
        sqlSession.insert(NS + "insertBatch", params);
        Long id = params.get("id") != null ? Long.parseLong(params.get("id").toString()) : null;
        if (id != null) {
            // batch_configs INSERT 직후 mappings 동기화 (params 에 mappings 키가 있을 때만)
            if (params.containsKey("mappings")) {
                syncMappings(id,
                        toStr(params.get("company_code")),
                        toMappingList(params.get("mappings")),
                        toStr(params.get("created_by")));
            }
            Map<String, Object> infoParams = new HashMap<>();
            infoParams.put("id", id);
            Map<String, Object> result = sqlSession.selectOne(NS + "getBatchInfo", infoParams);
            if (result != null) attachMappings(result);
            return result;
        }
        return params;
    }

    @Transactional
    public Map<String, Object> updateBatch(Map<String, Object> params) {
        commonService.applyCompanyCodeFilter(params);
        sqlSession.update(NS + "updateBatch", params);
        Long id = params.get("id") != null ? Long.parseLong(params.get("id").toString()) : null;
        // replace-all: body 에 mappings 키가 들어왔으면 (빈 배열 포함) 매핑 전체 교체
        if (id != null && params.containsKey("mappings")) {
            syncMappings(id,
                    toStr(params.get("company_code")),
                    toMappingList(params.get("mappings")),
                    toStr(params.get("updated_by") != null ? params.get("updated_by") : params.get("created_by")));
        }
        Map<String, Object> infoParams = new HashMap<>();
        infoParams.put("id", params.get("id"));
        Map<String, Object> result = sqlSession.selectOne(NS + "getBatchInfo", infoParams);
        if (result != null) attachMappings(result);
        return result;
    }

    // ── batch_mappings replace-all 동기화 ─────────────────────────────────────

    /** batch_config_id 의 매핑을 전부 지우고 mappings 리스트로 다시 채운다. */
    private void syncMappings(Long batchConfigId, String companyCode,
                              List<Map<String, Object>> mappings, String userId) {
        Map<String, Object> delParams = new HashMap<>();
        delParams.put("batch_config_id", batchConfigId);
        sqlSession.delete(NS + "deleteBatchMappingsByConfigId", delParams);

        if (mappings == null || mappings.isEmpty()) return;

        for (int i = 0; i < mappings.size(); i++) {
            Map<String, Object> row = new HashMap<>(mappings.get(i));
            row.put("batch_config_id", batchConfigId);
            if (row.get("company_code") == null) row.put("company_code", companyCode);
            if (row.get("created_by") == null) row.put("created_by", userId);
            if (row.get("mapping_order") == null) row.put("mapping_order", i + 1);
            stringifyJsonField(row, "mapping_config");
            sqlSession.insert(NS + "insertBatchMapping", row);
        }
    }

    /** getBatchInfo 결과에 batch_mappings 리스트 attach. */
    private void attachMappings(Map<String, Object> batch) {
        Object idObj = batch.get("id");
        if (idObj == null) return;
        Map<String, Object> params = new HashMap<>();
        params.put("batch_config_id", idObj);
        List<Map<String, Object>> mappings = sqlSession.selectList(NS + "getBatchMappingsByConfigId", params);
        if (mappings != null) {
            for (Map<String, Object> row : mappings) parseJsonField(row, "mapping_config");
        }
        batch.put("batch_mappings", mappings != null ? mappings : new ArrayList<>());
    }

    /** JSONB → 객체. SELECT 결과의 TEXT cast 값을 파싱해 Map/List 로 되돌린다. */
    private void parseJsonField(Map<String, Object> row, String key) {
        Object val = row.get(key);
        if (val instanceof String && !((String) val).isEmpty()) {
            try {
                row.put(key, objectMapper.readValue((String) val, Object.class));
            } catch (Exception e) {
                log.warn("Failed to parse JSONB field '{}': {}", key, e.getMessage());
            }
        }
    }

    /** 객체 → JSON 문자열. INSERT 전 ::jsonb 캐스팅을 위해 직렬화한다. null 은 그대로 둠. */
    private void stringifyJsonField(Map<String, Object> params, String key) {
        Object val = params.get(key);
        if (val == null || val instanceof String) return;
        try {
            params.put(key, objectMapper.writeValueAsString(val));
        } catch (Exception e) {
            log.warn("Failed to stringify field '{}': {}", key, e.getMessage());
            params.put(key, null);
        }
    }

    @SuppressWarnings("unchecked")
    private List<Map<String, Object>> toMappingList(Object raw) {
        if (raw == null) return new ArrayList<>();
        if (raw instanceof List) return (List<Map<String, Object>>) raw;
        return new ArrayList<>();
    }

    private String toStr(Object v) {
        return v != null ? v.toString() : null;
    }

    @Transactional
    public int deleteBatch(Map<String, Object> params) {
        commonService.applyCompanyCodeFilter(params);
        return sqlSession.delete(NS + "deleteBatch", params);
    }

    public List<Map<String, Object>> getConnections(String companyCode) {
        List<Map<String, Object>> result = new ArrayList<>();

        Map<String, Object> internal = new LinkedHashMap<>();
        internal.put("id", "internal");
        internal.put("name", "내부 DB");
        internal.put("type", "internal");
        result.add(internal);

        try {
            Map<String, Object> params = new HashMap<>();
            params.put("company_code", companyCode);
            params.put("is_active", "Y");
            commonService.applyCompanyCodeFilter(params);
            List<Map<String, Object>> externalConns = sqlSession.selectList(EXT_NS + "getExternalDbConnectionList", params);
            for (Map<String, Object> conn : externalConns) {
                Map<String, Object> item = new LinkedHashMap<>();
                item.put("id", conn.get("id"));
                item.put("name", conn.get("connection_name"));
                item.put("type", "external");
                item.put("db_type", conn.get("db_type"));
                result.add(item);
            }
        } catch (Exception e) {
            log.warn("외부 DB 연결 목록 조회 실패: {}", e.getMessage());
        }

        return result;
    }

    public List<Map<String, Object>> getTables(String type, Long connectionId) {
        if ("internal".equals(type)) {
            return sqlSession.selectList(NS + "getInternalTables");
        }
        return new ArrayList<>();
    }

    public List<Map<String, Object>> getColumns(String type, Long connectionId, String tableName) {
        if ("internal".equals(type)) {
            Map<String, Object> params = new HashMap<>();
            params.put("table_name", tableName);
            return sqlSession.selectList(NS + "getInternalTableColumns", params);
        }
        return new ArrayList<>();
    }
}
