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.security.MessageDigest;
import java.util.*;

@Service
@Slf4j
public class ExcelMappingService extends BaseService {

    @Autowired private ObjectMapper objectMapper;

    /**
     * 정렬된 컬럼 목록의 MD5 해시 생성
     */
    public String generateColumnsHash(List<String> columns) {
        List<String> sorted = new ArrayList<>(columns);
        Collections.sort(sorted);
        String joined = String.join("|", sorted);
        try {
            MessageDigest md = MessageDigest.getInstance("MD5");
            byte[] digest = md.digest(joined.getBytes("UTF-8"));
            StringBuilder sb = new StringBuilder();
            for (byte b : digest) sb.append(String.format("%02x", b & 0xff));
            return sb.toString();
        } catch (Exception e) {
            throw new RuntimeException("해시 생성 실패", e);
        }
    }

    /**
     * 엑셀 컬럼 구조로 매핑 템플릿 조회
     */
    public Map<String, Object> findMappingByColumns(Map<String, Object> params) {
        String tableName = String.valueOf(params.get("table_name"));
        List<String> excelColumns = toStringList(params.get("excel_columns"));
        String companyCode = (String) params.getOrDefault("company_code", "*");

        String hash = generateColumnsHash(excelColumns);
        Map<String, Object> qp = new HashMap<>();
        qp.put("table_name", tableName);
        qp.put("excel_columns_hash", hash);
        qp.put("company_code", companyCode);

        if ("*".equals(companyCode)) {
            return sqlSession.selectOne("excelMapping.findExcelMappingByHash", qp);
        } else {
            return sqlSession.selectOne("excelMapping.findExcelMappingByHashAndCompany", qp);
        }
    }

    /**
     * 매핑 템플릿 저장 (UPSERT)
     */
    @Transactional
    public Map<String, Object> saveMappingTemplate(Map<String, Object> params) {
        String tableName = String.valueOf(params.get("table_name"));
        List<String> excelColumns = toStringList(params.get("excel_columns"));
        Object columnMappings = params.get("column_mappings");
        String companyCode = (String) params.getOrDefault("company_code", "*");

        String hash = generateColumnsHash(excelColumns);

        String excelColumnsJson;
        String columnMappingsJson;
        try {
            excelColumnsJson = objectMapper.writeValueAsString(excelColumns);
            columnMappingsJson = columnMappings instanceof String
                ? (String) columnMappings
                : objectMapper.writeValueAsString(columnMappings);
        } catch (Exception e) {
            throw new RuntimeException("JSON 변환 실패", e);
        }

        Map<String, Object> qp = new HashMap<>();
        qp.put("table_name", tableName);
        qp.put("excel_columns", excelColumnsJson);
        qp.put("excel_columns_hash", hash);
        qp.put("column_mappings", columnMappingsJson);
        qp.put("company_code", companyCode);
        sqlSession.insert("excelMapping.upsertExcelMapping", qp);

        // 저장 후 조회
        return findMappingByColumns(Map.of(
            "table_name", tableName,
            "excel_columns", excelColumns,
            "company_code", companyCode
        ));
    }

    /**
     * 테이블의 매핑 템플릿 목록 조회
     */
    public List<Map<String, Object>> getMappingTemplates(Map<String, Object> params) {
        String tableName = String.valueOf(params.get("table_name"));
        String companyCode = (String) params.getOrDefault("company_code", "*");
        Map<String, Object> qp = new HashMap<>();
        qp.put("table_name", tableName);
        qp.put("company_code", companyCode);

        if ("*".equals(companyCode)) {
            return sqlSession.selectList("excelMapping.getExcelMappingListByTable", qp);
        } else {
            return sqlSession.selectList("excelMapping.getExcelMappingListByTableAndCompany", qp);
        }
    }

    /**
     * 매핑 템플릿 삭제
     */
    @Transactional
    public boolean deleteMappingTemplate(Map<String, Object> params) {
        String companyCode = (String) params.getOrDefault("company_code", "*");
        int deleted;
        if ("*".equals(companyCode)) {
            deleted = sqlSession.delete("excelMapping.deleteExcelMapping", params);
        } else {
            deleted = sqlSession.delete("excelMapping.deleteExcelMappingByCompany", params);
        }
        return deleted > 0;
    }

    @SuppressWarnings("unchecked")
    private List<String> toStringList(Object obj) {
        if (obj instanceof List) {
            List<?> list = (List<?>) obj;
            List<String> result = new ArrayList<>();
            for (Object item : list) result.add(String.valueOf(item));
            return result;
        }
        return Collections.emptyList();
    }
}
