package com.erp.service;

import com.erp.common.BaseService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import java.util.*;

@Service
@Slf4j
public class EntityReferenceService extends BaseService {

    private static final String NS = "entityReference.";

    public Map<String, Object> getEntityReferenceData(Map<String, Object> params) {
        String tableName   = (String) params.get("table_name");
        String columnName  = (String) params.get("column_name");
        String companyCode = (String) params.get("company_code");
        int    limit       = toInt(params.getOrDefault("limit", 100));
        Object search      = params.get("search");

        Map<String, Object> columnInfo = sqlSession.selectOne(NS + "selectColumnInfo",
                Map.of("table_name", tableName, "column_name", columnName));

        if (columnInfo == null) {
            throw new IllegalArgumentException(
                    "컬럼 정보를 찾을 수 없습니다: " + tableName + "." + columnName);
        }

        String inputType = (String) columnInfo.get("input_type");
        if (!"entity".equals(inputType)) {
            throw new IllegalStateException(
                    "컬럼 '" + tableName + "." + columnName + "'은 entity 타입이 아닙니다. inputType: " + inputType);
        }

        String referenceTable  = (String) columnInfo.get("reference_table");
        String referenceColumn = (String) columnInfo.get("reference_column");
        String displayColumn   = columnInfo.get("display_column") != null
                ? (String) columnInfo.get("display_column") : "name";

        if (referenceTable == null || referenceColumn == null) {
            throw new IllegalStateException(
                    "Entity 타입 컬럼 '" + tableName + "." + columnName
                            + "'에 참조 테이블 정보가 설정되지 않았습니다.");
        }

        String safeTable   = sanitizeIdentifier(referenceTable);
        String safeRefCol  = sanitizeIdentifier(referenceColumn);
        String safeDispCol = sanitizeIdentifier(displayColumn);

        if (safeTable.isEmpty() || safeRefCol.isEmpty() || safeDispCol.isEmpty()) {
            throw new IllegalArgumentException("잘못된 참조 테이블/컬럼 이름입니다.");
        }

        Integer tableExistsObj = sqlSession.selectOne(NS + "checkTableExistsInSchema",
                Map.of("table_name", safeTable));
        int tableExists = tableExistsObj != null ? tableExistsObj : 0;
        if (tableExists == 0) {
            throw new IllegalStateException("참조 테이블 '" + safeTable + "'이 존재하지 않습니다.");
        }

        Integer hasCompanyCodeObj = sqlSession.selectOne(NS + "checkTableHasCompanyCode",
                Map.of("table_name", safeTable));
        int hasCompanyCode = hasCompanyCodeObj != null ? hasCompanyCodeObj : 0;

        Map<String, Object> queryParams = new HashMap<>();
        queryParams.put("table_name",       safeTable);
        queryParams.put("reference_column", safeRefCol);
        queryParams.put("display_column",   safeDispCol);
        queryParams.put("company_code",     companyCode);
        queryParams.put("has_company_code",  hasCompanyCode > 0);
        queryParams.put("limit",           limit);
        if (search != null && !search.toString().isBlank()) {
            queryParams.put("search_like", "%" + search + "%");
        }

        log.info("엔티티 참조 데이터 조회: {}.{} -> {}.{} (display: {}), company={}",
                tableName, columnName, safeTable, safeRefCol, safeDispCol, companyCode);

        List<Map<String, Object>> rows = sqlSession.selectList(NS + "selectReferenceData", queryParams);

        List<Map<String, Object>> options = new ArrayList<>();
        for (Map<String, Object> row : rows) {
            Object val  = row.get("ref_value");
            Object disp = row.get("display_name");
            Map<String, Object> opt = new LinkedHashMap<>();
            opt.put("value", val != null ? val.toString() : "");
            opt.put("label", disp != null ? disp.toString() : (val != null ? val.toString() : ""));
            options.add(opt);
        }

        Map<String, Object> referenceInfo = new LinkedHashMap<>();
        referenceInfo.put("reference_table",  safeTable);
        referenceInfo.put("reference_column", safeRefCol);
        referenceInfo.put("display_column",   safeDispCol);

        Map<String, Object> result = new LinkedHashMap<>();
        result.put("options",       options);
        result.put("reference_info", referenceInfo);
        return result;
    }

    public Map<String, Object> getCodeData(Map<String, Object> params) {
        String codeInfo = (String) params.get("code_info");
        String companyCode  = (String) params.get("company_code");
        int    limit        = toInt(params.getOrDefault("limit", 100));
        Object search       = params.get("search");

        Map<String, Object> queryParams = new HashMap<>();
        queryParams.put("code_info", codeInfo);
        queryParams.put("company_code",  companyCode);
        queryParams.put("limit",        limit);
        if (search != null && !search.toString().isBlank()) {
            queryParams.put("search_like", "%" + search + "%");
        }

        log.info("공통 코드 데이터 조회: category={}, company={}", codeInfo, companyCode);

        List<Map<String, Object>> rows = sqlSession.selectList(NS + "selectCodeData", queryParams);

        List<Map<String, Object>> options = new ArrayList<>();
        for (Map<String, Object> row : rows) {
            Map<String, Object> opt = new LinkedHashMap<>();
            opt.put("value", String.valueOf(row.get("code_value")));
            opt.put("label", String.valueOf(row.get("code_name")));
            options.add(opt);
        }

        Map<String, Object> result = new LinkedHashMap<>();
        result.put("options",      options);
        result.put("code_info", codeInfo);
        return result;
    }

    private String sanitizeIdentifier(String name) {
        if (name == null) return "";
        return name.replaceAll("[^a-zA-Z0-9_]", "");
    }

    private int toInt(Object val) {
        if (val == null) return 0;
        if (val instanceof Number) return ((Number) val).intValue();
        try { return Integer.parseInt(val.toString()); }
        catch (NumberFormatException e) { return 0; }
    }
}
