package com.erp.service;

import com.erp.common.BaseService;
import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.time.LocalDate;
import java.util.*;

@Service
@RequiredArgsConstructor
@Slf4j
public class NumberingRuleService extends BaseService {

    private final JdbcTemplate jdbcTemplate;
    private final ObjectMapper objectMapper;

    private static final String NS = "numberingRule.";

    // ================================================================
    // ■ CRUD
    // ================================================================

    /** GET / → 회사별 채번 규칙 전체 목록 (parts 포함) */
    public List<Map<String, Object>> getRuleList(String companyCode) {
        Map<String, Object> params = new HashMap<>();
        params.put("company_code", companyCode);
        List<Map<String, Object>> rules = sqlSession.selectList(NS + "getRuleList", params);
        loadPartsForRules(rules, companyCode);
        return rules;
    }

    /** GET /:ruleId → 단건 조회 (parts 포함) */
    public Map<String, Object> getRuleById(String ruleId, String companyCode) {
        Map<String, Object> params = new HashMap<>();
        params.put("rule_id", ruleId);
        params.put("company_code", companyCode);
        Map<String, Object> rule = sqlSession.selectOne(NS + "getRuleById", params);
        if (rule == null) return null;
        loadPartsForRule(rule, companyCode);
        return rule;
    }

    /** POST / → 규칙 생성 (parts 포함) */
    @Transactional
    public Map<String, Object> createRule(Map<String, Object> body, String companyCode) {
        Map<String, Object> params = new HashMap<>();
        params.put("rule_id",          body.get("rule_id"));
        params.put("rule_name",         body.get("rule_name"));
        params.put("description",      body.get("description"));
        params.put("separator",        body.getOrDefault("separator", "-"));
        params.put("reset_period",      body.getOrDefault("reset_period", "none"));
        params.put("current_sequence",  body.getOrDefault("current_sequence", 1));
        params.put("table_name",        body.get("table_name"));
        params.put("column_name",       body.get("column_name"));
        params.put("company_code",      companyCode);
        params.put("category_column",   body.get("category_column"));
        params.put("category_value_id",  body.get("category_value_id"));
        params.put("created_by",        body.get("user_id"));

        sqlSession.insert(NS + "insertRule", params);

        // parts 삽입
        Object partsObj = body.get("parts");
        if (partsObj instanceof List) {
            @SuppressWarnings("unchecked")
            List<Map<String, Object>> parts = (List<Map<String, Object>>) partsObj;
            insertParts(parts, (String) body.get("rule_id"), companyCode);
        }

        return getRuleById((String) body.get("rule_id"), companyCode);
    }

    /** PUT /:ruleId → 규칙 수정 */
    @Transactional
    public Map<String, Object> updateRule(String ruleId, Map<String, Object> body, String companyCode) {
        Map<String, Object> params = new HashMap<>(body);
        params.put("rule_id",      ruleId);
        params.put("company_code", companyCode);
        int updated = sqlSession.update(NS + "updateRule", params);
        if (updated == 0) throw new RuntimeException("규칙을 찾을 수 없거나 권한이 없습니다");

        // parts 교체 (있는 경우)
        Object partsObj = body.get("parts");
        if (partsObj instanceof List) {
            Map<String, Object> delParams = new HashMap<>();
            delParams.put("rule_id",      ruleId);
            delParams.put("company_code", companyCode);
            sqlSession.delete(NS + "deleteRulePartsByRuleId", delParams);

            @SuppressWarnings("unchecked")
            List<Map<String, Object>> parts = (List<Map<String, Object>>) partsObj;
            insertParts(parts, ruleId, companyCode);
        }

        return getRuleById(ruleId, companyCode);
    }

    /** DELETE /:ruleId → 규칙 삭제 */
    @Transactional
    public void deleteRule(String ruleId, String companyCode) {
        // parts 먼저 삭제 (FK 제약이 없을 경우 대비)
        Map<String, Object> params = new HashMap<>();
        params.put("rule_id",      ruleId);
        params.put("company_code", companyCode);
        sqlSession.delete(NS + "deleteRulePartsByRuleId", params);
        sqlSession.delete(NS + "deleteSequencesByRuleId", params);
        int deleted = sqlSession.delete(NS + "deleteRule", params);
        if (deleted == 0) throw new RuntimeException("규칙을 찾을 수 없거나 권한이 없습니다");
    }

    // ================================================================
    // ■ 코드 생성 Actions
    // ================================================================

    /** POST /:ruleId/preview → 순번 증가 없이 미리보기 */
    public String previewCode(String ruleId, String companyCode,
                              Map<String, Object> formData, String manualInputValue) {
        Map<String, Object> rule = getRuleById(ruleId, companyCode);
        if (rule == null) throw new RuntimeException("규칙을 찾을 수 없습니다");

        @SuppressWarnings("unchecked")
        List<Map<String, Object>> parts = (List<Map<String, Object>>) rule.get("parts");
        List<Map<String, Object>> sortedParts = sortedParts(parts);

        // prefix_key 기반 현재 순번 조회
        List<String> manualValues = manualInputValue != null ? List.of(manualInputValue) : null;
        String prefixKey = buildPrefixKey(rule, formData, manualValues);
        long currentSeq = getSequenceForPrefix(ruleId, companyCode, prefixKey);

        String separator = (String) rule.getOrDefault("separator", "");

        List<String> partValues = computePartValues(sortedParts, formData, currentSeq + 1, true);
        return joinPartsWithSeparators(partValues, sortedParts, separator);
    }

    /** POST /:ruleId/allocate → 순번 증가 후 코드 반환 */
    @Transactional
    public String allocateCode(String ruleId, String companyCode,
                               Map<String, Object> formData, String userInputCode) {
        Map<String, Object> rule = getRuleById(ruleId, companyCode);
        if (rule == null) throw new RuntimeException("규칙을 찾을 수 없습니다");

        @SuppressWarnings("unchecked")
        List<Map<String, Object>> parts = (List<Map<String, Object>>) rule.get("parts");
        List<Map<String, Object>> sortedParts = sortedParts(parts);

        // 수동 값 추출
        List<String> extractedManualValues = new ArrayList<>();
        boolean hasManualPart = sortedParts.stream()
                .anyMatch(p -> "manual".equals(p.get("generation_method")));
        if (hasManualPart && userInputCode != null) {
            extractedManualValues = extractManualValues(rule, sortedParts, userInputCode, formData);
        }

        // prefix_key 기반 순번 증가
        String prefixKey = buildPrefixKey(rule, formData,
                extractedManualValues.isEmpty() ? null : extractedManualValues);
        boolean hasSequence = sortedParts.stream()
                .anyMatch(p -> "sequence".equals(p.get("part_type")));

        long allocatedSequence = 0;
        if (hasSequence) {
            allocatedSequence = incrementSequenceForPrefix(ruleId, companyCode, prefixKey);
            // numbering_rules.current_sequence 동기화
            Map<String, Object> seqParams = new HashMap<>();
            seqParams.put("rule_id",          ruleId);
            seqParams.put("company_code",      companyCode);
            seqParams.put("current_sequence",  allocatedSequence);
            sqlSession.update(NS + "updateCurrentSequenceInRule", seqParams);
        }

        // 수동 파트 값 적용
        List<String> partValues = computePartValuesWithManual(
                sortedParts, formData, allocatedSequence, extractedManualValues);

        String separator = (String) rule.getOrDefault("separator", "");
        return joinPartsWithSeparators(partValues, sortedParts, separator);
    }

    /** POST /:ruleId/generate (deprecated) → allocateCode 위임 */
    @Transactional
    public String generateCode(String ruleId, String companyCode) {
        return allocateCode(ruleId, companyCode, null, null);
    }

    /**
     * POST /:ruleId/reset → 순번 초기화 (admin)
     *
     * 두 테이블 다 처리:
     *  1. numbering_rule_sequences (prefix 별 발번 카운터, 실제 ground truth) 전체 DELETE → 다음 발번 1 부터
     *  2. numbering_rules.current_sequence (표시용) 직접 0 으로 set
     *     - admin 전용 SQL `setCurrentSequenceInRule` 사용 (GREATEST 없음)
     */
    @Transactional
    public void resetSequence(String ruleId, String companyCode) {
        Map<String, Object> params = new HashMap<>();
        params.put("rule_id",         ruleId);
        params.put("company_code",    companyCode);
        params.put("current_sequence", 0);
        sqlSession.delete(NS + "deleteSequencesByRuleId", params);
        sqlSession.update(NS + "setCurrentSequenceInRule", params);
        log.info("시퀀스 초기화 완료: ruleId={}, companyCode={}", ruleId, companyCode);
    }

    /**
     * PUT /:ruleId/sequence → 현재 시퀀스 임의 값으로 수정 (admin)
     *
     * admin 이 "지금 카운터를 N 으로 set" 의도. 다음 발번은 N+1 부터.
     * 두 테이블 다 처리:
     *  1. numbering_rule_sequences (prefix 별 실제 카운터) 전체 DELETE
     *     → 다음 allocate 시 새 row 가 INSERT (current_sequence=1) 되거나
     *       또는 admin set 값을 기반으로 시작하도록 별도 처리 필요할 수 있음
     *     - 운영 전 단계라 historical sequence 폐기 안전
     *  2. numbering_rules.current_sequence 를 newSequence 로 set
     */
    @Transactional
    public void updateRuleSequence(String ruleId, Integer newSequence, String companyCode) {
        Map<String, Object> params = new HashMap<>();
        params.put("rule_id",         ruleId);
        params.put("company_code",    companyCode);
        params.put("current_sequence", newSequence);
        sqlSession.delete(NS + "deleteSequencesByRuleId", params);
        sqlSession.update(NS + "setCurrentSequenceInRule", params);
        log.info("시퀀스 수정 완료: ruleId={}, newSequence={}, companyCode={}", ruleId, newSequence, companyCode);
    }

    // ================================================================
    // ■ Available Rules
    // ================================================================

    /** GET /available/:menuObjid? → 메뉴별 사용 가능한 규칙 목록 */
    public List<Map<String, Object>> getAvailableRulesForMenu(String companyCode, String menuObjid) {
        Map<String, Object> params = new HashMap<>();
        params.put("company_code", companyCode);
        if (menuObjid != null) params.put("menu_objid", menuObjid);
        List<Map<String, Object>> rules = sqlSession.selectList(NS + "getAvailableRulesForMenu", params);
        loadPartsForRules(rules, companyCode);
        return rules;
    }

    /** GET /available-for-screen?tableName= → 화면별 사용 가능한 규칙 목록 */
    public List<Map<String, Object>> getAvailableRulesForScreen(String companyCode, String tableName) {
        Map<String, Object> params = new HashMap<>();
        params.put("company_code", companyCode);
        params.put("table_name",   tableName);
        List<Map<String, Object>> rules = sqlSession.selectList(NS + "getAvailableRulesForScreen", params);
        loadPartsForRules(rules, companyCode);
        return rules;
    }

    // ================================================================
    // ■ Column-based Lookup
    // ================================================================

    /** GET /by-column/:tableName/:columnName */
    @Transactional
    public Map<String, Object> getNumberingRuleByColumn(String companyCode,
                                                         String tableName, String columnName) {
        Map<String, Object> params = new HashMap<>();
        params.put("company_code", companyCode);
        params.put("table_name",   tableName);
        params.put("column_name",  columnName);

        Map<String, Object> rule = sqlSession.selectOne(NS + "getRuleByColumn", params);

        // fallback: column_name이 비어있는 레거시 규칙
        if (rule == null) {
            rule = sqlSession.selectOne(NS + "getRuleByColumnFallback", params);
            if (rule != null) {
                // column_name 자동 업데이트 (레거시 마이그레이션)
                Map<String, Object> upParams = new HashMap<>();
                upParams.put("rule_id",      rule.get("rule_id"));
                upParams.put("company_code", companyCode);
                upParams.put("column_name",  columnName);
                sqlSession.update(NS + "updateRuleColumnName", upParams);
                rule.put("column_name", columnName);
            }
        }

        if (rule != null) {
            loadPartsForRule(rule, companyCode);
        }
        return rule;
    }

    // ================================================================
    // ■ Test Table Operations (같은 테이블 사용, 동일 로직)
    // ================================================================

    /** GET /test/list/:menuObjid? → 테스트용 규칙 목록 */
    public List<Map<String, Object>> getRulesFromTest(String companyCode, String menuObjid) {
        return getAvailableRulesForMenu(companyCode, menuObjid);
    }

    /** POST /test/save → UPSERT 규칙 */
    @Transactional
    public Map<String, Object> saveRuleToTest(Map<String, Object> body, String companyCode) {
        String ruleId = (String) body.get("rule_id");

        Map<String, Object> lookupParams = new HashMap<>();
        lookupParams.put("rule_id",      ruleId);
        lookupParams.put("company_code", companyCode);
        Map<String, Object> existing = sqlSession.selectOne(NS + "getRuleById", lookupParams);

        if (existing != null) {
            // 업데이트
            Map<String, Object> upParams = new HashMap<>(body);
            upParams.put("rule_id",      ruleId);
            upParams.put("company_code", companyCode);
            sqlSession.update(NS + "updateRule", upParams);

            // parts 교체
            sqlSession.delete(NS + "deleteRulePartsByRuleId", lookupParams);
        } else {
            // 신규 등록
            createRule(body, companyCode);
            return getRuleById(ruleId, companyCode);
        }

        // parts 삽입
        Object partsObj = body.get("parts");
        if (partsObj instanceof List) {
            @SuppressWarnings("unchecked")
            List<Map<String, Object>> parts = (List<Map<String, Object>>) partsObj;
            insertParts(parts, ruleId, companyCode);
        }

        return getRuleById(ruleId, companyCode);
    }

    /** DELETE /test/:ruleId → 테스트 규칙 삭제 */
    @Transactional
    public void deleteRuleFromTest(String ruleId, String companyCode) {
        deleteRule(ruleId, companyCode);
    }

    // ================================================================
    // ■ Copy for Company (SUPER_ADMIN)
    // ================================================================

    @Transactional
    public Map<String, Object> copyRulesForCompany(String sourceCompanyCode,
                                                    String targetCompanyCode) {
        Map<String, Object> srcParams = new HashMap<>();
        srcParams.put("company_code", sourceCompanyCode);
        List<Map<String, Object>> sourceRules = sqlSession.selectList(NS + "getRulesForCopy", srcParams);

        int copied = 0;
        int skipped = 0;

        for (Map<String, Object> rule : sourceRules) {
            String ruleId = (String) rule.get("rule_id");

            // 대상 회사에 이미 존재하는지 확인
            Map<String, Object> checkParams = new HashMap<>();
            checkParams.put("rule_id",      ruleId);
            checkParams.put("company_code", targetCompanyCode);
            Map<String, Object> existing = sqlSession.selectOne(NS + "getRuleById", checkParams);

            if (existing != null) {
                skipped++;
                continue;
            }

            // 규칙 복사
            Map<String, Object> insertParams = new HashMap<>(rule);
            insertParams.put("company_code",     targetCompanyCode);
            insertParams.put("current_sequence", 0);
            insertParams.put("created_by",       "system");
            sqlSession.insert(NS + "insertRule", insertParams);

            // parts 복사
            Map<String, Object> partsParams = new HashMap<>();
            partsParams.put("rule_id",      ruleId);
            partsParams.put("company_code", sourceCompanyCode);
            List<Map<String, Object>> parts = sqlSession.selectList(NS + "getRulePartsForCopy", partsParams);
            insertParts(parts, ruleId, targetCompanyCode);

            copied++;
        }

        Map<String, Object> result = new HashMap<>();
        result.put("copied",  copied);
        result.put("skipped", skipped);
        result.put("total",   sourceRules.size());
        return result;
    }

    // ================================================================
    // ■ Private Helpers
    // ================================================================

    private void insertParts(List<Map<String, Object>> parts, String ruleId, String companyCode) {
        for (Map<String, Object> part : parts) {
            Map<String, Object> partParams = new HashMap<>(part);
            partParams.put("rule_id",      ruleId);
            partParams.put("company_code", companyCode);

            // auto_config: separatorAfter 통합
            Map<String, Object> autoConfig = parseAutoConfig(part.get("auto_config"));
            Object sepAfter = part.getOrDefault("separator_after", "-");
            autoConfig.put("separator_after", sepAfter);
            partParams.put("auto_config",   toJsonString(autoConfig));

            Map<String, Object> manualConfig = parseAutoConfig(part.get("manual_config"));
            partParams.put("manual_config", toJsonString(manualConfig));

            sqlSession.insert(NS + "insertRulePart", partParams);
        }
    }

    private void loadPartsForRules(List<Map<String, Object>> rules, String companyCode) {
        for (Map<String, Object> rule : rules) {
            loadPartsForRule(rule, companyCode);
        }
    }

    private void loadPartsForRule(Map<String, Object> rule, String companyCode) {
        String ruleId = (String) rule.get("rule_id");
        Map<String, Object> params = new HashMap<>();
        params.put("rule_id",      ruleId);
        params.put("company_code", companyCode);
        List<Map<String, Object>> parts = sqlSession.selectList(NS + "getRulePartsByRuleId", params);
        // autoConfig에서 separatorAfter 추출
        for (Map<String, Object> part : parts) {
            Map<String, Object> ac = parseAutoConfig(part.get("auto_config"));
            if (ac.containsKey("separator_after")) {
                part.put("separator_after", ac.get("separator_after"));
            }
        }
        rule.put("parts", parts);
    }

    private List<Map<String, Object>> sortedParts(List<Map<String, Object>> parts) {
        List<Map<String, Object>> sorted = new ArrayList<>(parts);
        sorted.sort(Comparator.comparingInt(p -> toInt(p.get("order"), 0)));
        return sorted;
    }

    /** 순번 조회 (없으면 0 반환) */
    private long getSequenceForPrefix(String ruleId, String companyCode, String prefixKey) {
        Map<String, Object> params = new HashMap<>();
        params.put("rule_id",      ruleId);
        params.put("company_code", companyCode);
        params.put("prefix_key",   prefixKey);
        Map<String, Object> row = sqlSession.selectOne(NS + "getSequenceForPrefix", params);
        if (row == null) return 0L;
        Object seq = row.get("current_sequence");
        return seq == null ? 0L : ((Number) seq).longValue();
    }

    /**
     * 순번 증가 UPSERT – ON CONFLICT DO UPDATE RETURNING.
     *
     * INSERT 분기의 base 값:
     *  - 동일 prefix 의 row 가 없을 때 (첫 발번 / admin reset 후 / 새 카테고리 등)
     *    `numbering_rules.current_sequence + 1` 부터 시작.
     *  - 의미: admin 이 sequence 를 N 으로 set 하고 historical sequences 를 비웠을 때,
     *    다음 발번이 N+1 부터 정확히 시작되도록.
     *  - numbering_rules row 가 없는 비정상 케이스는 0+1=1.
     */
    private long incrementSequenceForPrefix(String ruleId, String companyCode, String prefixKey) {
        String sql = """
            INSERT INTO numbering_rule_sequences
              (rule_id, company_code, prefix_key, current_sequence, last_allocated_at)
            VALUES (
              ?, ?, ?,
              COALESCE((
                SELECT current_sequence
                FROM numbering_rules
                WHERE rule_id = ?
                  AND (company_code = ? OR company_code = '*')
                LIMIT 1
              ), 0) + 1,
              NOW()
            )
            ON CONFLICT (rule_id, company_code, prefix_key)
            DO UPDATE SET
              current_sequence = numbering_rule_sequences.current_sequence + 1,
              last_allocated_at = NOW()
            RETURNING current_sequence
            """;
        Long newSeq = jdbcTemplate.queryForObject(sql, Long.class,
                ruleId, companyCode, prefixKey, ruleId, companyCode);
        return newSeq != null ? newSeq : 1L;
    }

    /** prefix_key 생성 (sequence 파트 제외 + 구분자 '|') */
    private String buildPrefixKey(Map<String, Object> rule,
                                   Map<String, Object> formData,
                                   List<String> manualValues) {
        @SuppressWarnings("unchecked")
        List<Map<String, Object>> parts = (List<Map<String, Object>>) rule.get("parts");
        List<Map<String, Object>> sortedParts = sortedParts(parts);

        List<String> prefixParts = new ArrayList<>();
        int manualIndex = 0;

        for (Map<String, Object> part : sortedParts) {
            String partType         = (String) part.get("part_type");
            String generationMethod = (String) part.get("generation_method");

            if ("sequence".equals(partType)) continue;

            if ("manual".equals(generationMethod)) {
                String val = (manualValues != null && manualIndex < manualValues.size())
                        ? manualValues.get(manualIndex) : "";
                manualIndex++;
                if (val != null && !val.isEmpty()) prefixParts.add(val);
                continue;
            }

            Map<String, Object> ac = parseAutoConfig(part.get("auto_config"));
            String computed = computeSinglePartValue(partType, ac, formData);
            if (computed != null && !computed.isEmpty()) {
                prefixParts.add(computed);
            }
        }

        return String.join("|", prefixParts);
    }

    /** preview용: 각 파트 값 계산 (manual=____, sequence=예상 순번) */
    private List<String> computePartValues(List<Map<String, Object>> sortedParts,
                                            Map<String, Object> formData,
                                            long nextSequence,
                                            boolean isPreview) {
        List<String> values = new ArrayList<>();
        for (Map<String, Object> part : sortedParts) {
            String partType         = (String) part.get("part_type");
            String generationMethod = (String) part.get("generation_method");
            Map<String, Object> ac  = parseAutoConfig(part.get("auto_config"));

            if ("manual".equals(generationMethod)) {
                values.add(isPreview ? "____" : "");
            } else if ("sequence".equals(partType)) {
                int length    = toInt(ac.get("sequence_length"), 3);
                int startFrom = toInt(ac.get("start_from"), 1);
                long seq = isPreview ? (nextSequence - 1 + startFrom) : nextSequence;
                values.add(String.format("%0" + length + "d", seq));
            } else {
                values.add(computeSinglePartValue(partType, ac, formData));
            }
        }
        return values;
    }

    /** allocate용: 수동 파트 값 포함 */
    private List<String> computePartValuesWithManual(List<Map<String, Object>> sortedParts,
                                                      Map<String, Object> formData,
                                                      long allocatedSequence,
                                                      List<String> manualValues) {
        List<String> values = new ArrayList<>();
        int manualIndex = 0;

        for (Map<String, Object> part : sortedParts) {
            String partType         = (String) part.get("part_type");
            String generationMethod = (String) part.get("generation_method");
            Map<String, Object> ac  = parseAutoConfig(part.get("auto_config"));

            if ("manual".equals(generationMethod)) {
                String val = (manualValues != null && manualIndex < manualValues.size())
                        ? manualValues.get(manualIndex) : "";
                manualIndex++;
                values.add(val != null ? val : "");
            } else if ("sequence".equals(partType)) {
                int length = toInt(ac.get("sequence_length"), 3);
                values.add(String.format("%0" + length + "d", allocatedSequence));
            } else {
                values.add(computeSinglePartValue(partType, ac, formData));
            }
        }
        return values;
    }

    /** 단일 파트 값 계산 (sequence/manual 제외) */
    private String computeSinglePartValue(String partType,
                                           Map<String, Object> ac,
                                           Map<String, Object> formData) {
        return switch (partType) {
            case "text" -> String.valueOf(ac.getOrDefault("text_value", "TEXT"));

            case "number" -> {
                int length = toInt(ac.get("number_length"), 3);
                int value  = toInt(ac.get("number_value"),  1);
                yield String.format("%0" + length + "d", value);
            }

            case "date" -> {
                String dateFormat = (String) ac.getOrDefault("date_format", "YYYYMMDD");
                if (Boolean.TRUE.equals(ac.get("use_column_value"))
                        && ac.get("source_column_name") != null && formData != null) {
                    Object colVal = formData.get(ac.get("source_column_name"));
                    if (colVal != null) {
                        try {
                            LocalDate ld = LocalDate.parse(colVal.toString().substring(0, 10));
                            yield formatDate(ld, dateFormat);
                        } catch (Exception ignored) { /* fall through */ }
                    }
                }
                yield formatDate(LocalDate.now(), dateFormat);
            }

            case "category" -> resolveCategoryFormat(ac, formData);

            case "reference" -> {
                String refCol = (String) ac.get("reference_column_name");
                if (refCol != null && formData != null && formData.containsKey(refCol)) {
                    yield String.valueOf(formData.get(refCol));
                }
                yield "";
            }

            default -> "";
        };
    }

    /** category 파트 format 해석 */
    private String resolveCategoryFormat(Map<String, Object> ac, Map<String, Object> formData) {
        String categoryKey = (String) ac.get("category_key");
        if (categoryKey == null || formData == null) return "";

        String colName = categoryKey.contains(".") ? categoryKey.split("\\.")[1] : categoryKey;
        Object selectedValue = formData.get(colName);
        if (selectedValue == null) return "";

        String selectedStr = selectedValue.toString();

        @SuppressWarnings("unchecked")
        List<Map<String, Object>> mappings =
                (List<Map<String, Object>>) ac.getOrDefault("category_mappings", new ArrayList<>());

        Map<String, Object> match = mappings.stream().filter(m -> {
            Object vid  = m.get("category_value_id");
            Object code = m.get("category_value_code");
            Object label = m.get("category_value_label");
            if (vid != null  && vid.toString().equals(selectedStr))   return true;
            if (code != null && code.toString().equals(selectedStr))  return true;
            if (label != null && label.toString().equals(selectedStr)) return true;
            return false;
        }).findFirst().orElse(null);

        if (match == null) {
            // category_values 테이블에서 코드→id 역변환 시도
            try {
                String[] parts = categoryKey.contains(".") ? categoryKey.split("\\.") : new String[]{categoryKey, categoryKey};
                Map<String, Object> row = jdbcTemplate.queryForMap(
                    "SELECT value_id, value_label FROM category_values " +
                    "WHERE table_name = ? AND column_name = ? AND value_code = ? LIMIT 1",
                    parts[0], parts[1], selectedStr);
                String resolvedId    = String.valueOf(row.get("value_id"));
                String resolvedLabel = String.valueOf(row.get("value_label"));
                match = mappings.stream().filter(m -> {
                    Object vid = m.get("category_value_id");
                    Object lbl = m.get("category_value_label");
                    if (vid != null && vid.toString().equals(resolvedId)) return true;
                    if (lbl != null && lbl.toString().equals(resolvedLabel)) return true;
                    return false;
                }).findFirst().orElse(null);
            } catch (Exception ignored) { /* fallback: 빈 문자열 */ }
        }

        return match != null ? String.valueOf(match.getOrDefault("format", "")) : "";
    }

    /** parts + separator 결합 */
    private String joinPartsWithSeparators(List<String> values,
                                            List<Map<String, Object>> sortedParts,
                                            String globalSeparator) {
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < values.size(); i++) {
            sb.append(values.get(i));
            if (i < values.size() - 1) {
                Map<String, Object> part = sortedParts.get(i);
                Map<String, Object> ac   = parseAutoConfig(part.get("auto_config"));
                Object sep = part.containsKey("separator_after") ? part.get("separator_after")
                        : ac.getOrDefault("separator_after", globalSeparator != null ? globalSeparator : "");
                sb.append(sep != null ? sep.toString() : "");
            }
        }
        return sb.toString();
    }

    /**
     * allocateCode 시 사용자 입력 코드에서 수동 파트 값 추출
     * (단순 구현: 수동 파트 1개인 경우 userInputCode 전체 사용)
     */
    private List<String> extractManualValues(Map<String, Object> rule,
                                              List<Map<String, Object>> sortedParts,
                                              String userInputCode,
                                              Map<String, Object> formData) {
        List<Map<String, Object>> manualParts = sortedParts.stream()
                .filter(p -> "manual".equals(p.get("generation_method")))
                .toList();

        if (manualParts.isEmpty() || userInputCode == null) return new ArrayList<>();

        // 수동 파트 1개 → 전체 사용
        if (manualParts.size() == 1) {
            return new ArrayList<>(List.of(userInputCode));
        }

        // 수동 파트 2개 이상 → 구분자로 분리 시도 (간소화 구현)
        String separator = (String) rule.getOrDefault("separator", "-");
        String[] split = userInputCode.split(java.util.regex.Pattern.quote(separator), -1);
        return new ArrayList<>(Arrays.asList(split));
    }

    /** date 포맷 변환 */
    private String formatDate(LocalDate date, String format) {
        String year  = String.valueOf(date.getYear());
        String yy    = year.substring(2);
        String month = String.format("%02d", date.getMonthValue());
        String day   = String.format("%02d", date.getDayOfMonth());
        return switch (format) {
            case "YYYY"     -> year;
            case "YY"       -> yy;
            case "YYYYMM"   -> year + month;
            case "YYMM"     -> yy   + month;
            case "YYYYMMDD" -> year + month + day;
            case "YYMMDD"   -> yy   + month + day;
            default         -> year + month + day;
        };
    }

    /** auto_config / manual_config JSON 파싱 */
    @SuppressWarnings("unchecked")
    private Map<String, Object> parseAutoConfig(Object raw) {
        if (raw == null) return new HashMap<>();
        if (raw instanceof Map) return (Map<String, Object>) raw;
        try {
            return objectMapper.readValue(raw.toString(),
                    new TypeReference<Map<String, Object>>() {});
        } catch (Exception e) {
            return new HashMap<>();
        }
    }

    /** Map → JSON 문자열 변환 */
    private String toJsonString(Map<String, Object> map) {
        try {
            return objectMapper.writeValueAsString(map);
        } catch (Exception e) {
            return "{}";
        }
    }

    private int toInt(Object val, int def) {
        if (val == null) return def;
        return ((Number) val).intValue();
    }
}
