package com.ga.api.service.upload;

import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.ga.common.exception.BizException;
import com.ga.common.exception.ErrorCode;
import com.ga.core.vo.upload.UploadTemplateColumnVO;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.stereotype.Component;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * 12종 변환 규칙 실행기.
 *
 * 지원 규칙: TRIM / UPPER / LOWER / DATE / DIVIDE / MULTIPLY /
 *            LOOKUP / CODE_MAP / FIXED / REPLACE / SUBSTRING / CONCAT / DEFAULT
 */
@Slf4j
@Component
@RequiredArgsConstructor
public class TransformEngine {

    private static final ObjectMapper MAPPER = new ObjectMapper();

    private final LookupCache lookupCache;

    /**
     * 엑셀 Row 를 템플릿 컬럼 정의에 따라 변환하여 Map 으로 반환.
     *
     * @param row        xlsx-streamer Row
     * @param columns    템플릿 컬럼 정의
     * @param lookupData preload()가 반환한 요청 범위 LOOKUP 캐시
     * @return { targetField → 변환된 값 }
     */
    public Map<String, Object> transform(Row row, List<UploadTemplateColumnVO> columns,
                                         Map<String, Map<String, Object>> lookupData) {
        Map<String, Object> result = new LinkedHashMap<>();

        for (UploadTemplateColumnVO col : columns) {
            try {
                Object rawValue = extractRaw(row, col);
                Object transformed = applyTransform(rawValue, col, row, columns, lookupData);
                result.put(col.getTargetField(), transformed);
            } catch (BizException e) {
                throw e;
            } catch (Exception e) {
                throw new BizException(ErrorCode.EXCEL_PARSE_FAIL,
                        "컬럼[" + col.getTargetField() + "] 변환 오류: " + e.getMessage());
            }
        }

        return result;
    }

    /**
     * 단일 컬럼에 변환 규칙을 적용.
     */
    public Object applyTransform(Object value, UploadTemplateColumnVO col,
                                  Row row, List<UploadTemplateColumnVO> allColumns,
                                  Map<String, Map<String, Object>> lookupData) {
        String rule = col.getTransformRule();

        // FIXED 는 원본 값 무시
        if ("FIXED".equalsIgnoreCase(rule)) {
            return col.getFixedValue();
        }

        // DEFAULT: 값 없을 때만 적용
        if ("DEFAULT".equalsIgnoreCase(rule)) {
            return (value == null || value.toString().isBlank()) ? col.getDefaultValue() : value;
        }

        if (rule == null || rule.isBlank()) {
            return applyTargetTypeCast(value, col);
        }

        return switch (rule.toUpperCase()) {
            case "TRIM"      -> value == null ? null : value.toString().trim();
            case "UPPER"     -> value == null ? null : value.toString().trim().toUpperCase();
            case "LOWER"     -> value == null ? null : value.toString().trim().toLowerCase();
            case "DATE"      -> applyDate(value, col);
            case "DIVIDE"    -> applyDivide(value, col);
            case "MULTIPLY"  -> applyMultiply(value, col);
            case "LOOKUP"    -> applyLookup(value, col, lookupData);
            case "CODE_MAP"  -> applyCodeMap(value, col, lookupData);
            case "REPLACE"   -> applyReplace(value, col);
            case "SUBSTRING" -> applySubstring(value, col);
            case "CONCAT"    -> applyConcat(row, col);
            default          -> applyTargetTypeCast(value, col);
        };
    }

    // =========================================================
    // Raw 값 추출
    // =========================================================

    private Object extractRaw(Row row, UploadTemplateColumnVO col) {
        if ("FIXED".equalsIgnoreCase(col.getSourceType())) {
            return col.getFixedValue();
        }
        if ("EXPRESSION".equalsIgnoreCase(col.getSourceType())) {
            return col.getFixedValue();
        }

        // COLUMN: sourceColumn (0-based index)
        Integer srcCol = col.getSourceColumn();
        if (srcCol == null) return null;

        Cell cell = row.getCell(srcCol);
        if (cell == null) return null;

        return readCell(cell);
    }

    private Object readCell(Cell cell) {
        return switch (cell.getCellType()) {
            case STRING -> {
                String v = cell.getStringCellValue();
                yield (v == null || v.isBlank()) ? null : v;
            }
            case NUMERIC -> {
                if (DateUtil.isCellDateFormatted(cell)) {
                    yield cell.getDateCellValue().toInstant()
                            .atZone(java.time.ZoneId.systemDefault()).toLocalDate();
                }
                double d = cell.getNumericCellValue();
                yield (d == Math.floor(d) && !Double.isInfinite(d))
                        ? String.valueOf((long) d)
                        : String.valueOf(d);
            }
            case BOOLEAN -> String.valueOf(cell.getBooleanCellValue());
            case FORMULA -> {
                try { yield cell.getStringCellValue(); }
                catch (Exception e) { yield String.valueOf(cell.getNumericCellValue()); }
            }
            default -> null;
        };
    }

    // =========================================================
    // 개별 변환 규칙
    // =========================================================

    private Object applyDate(Object value, UploadTemplateColumnVO col) {
        if (value == null) return null;
        if (value instanceof LocalDate) return value.toString();
        String s = value.toString().trim();
        if (s.isBlank()) return null;
        String fmt = col.getDateFormat() != null ? col.getDateFormat() : "yyyyMMdd";
        try {
            LocalDate d = LocalDate.parse(s, DateTimeFormatter.ofPattern(fmt));
            return d.toString();
        } catch (Exception e) {
            throw new BizException(ErrorCode.EXCEL_PARSE_FAIL,
                    "날짜 파싱 실패 [" + s + "] format=" + fmt);
        }
    }

    private Object applyDivide(Object value, UploadTemplateColumnVO col) {
        if (value == null) return null;
        Integer divisor = col.getNumberDivide();
        if (divisor == null || divisor == 0) return value;
        BigDecimal bd = toBigDecimal(value);
        return bd.divide(BigDecimal.valueOf(divisor), 10, java.math.RoundingMode.HALF_UP)
                  .stripTrailingZeros();
    }

    private Object applyMultiply(Object value, UploadTemplateColumnVO col) {
        if (value == null) return null;
        Integer multiplier = col.getNumberMultiply();
        if (multiplier == null) return value;
        BigDecimal bd = toBigDecimal(value);
        return bd.multiply(BigDecimal.valueOf(multiplier)).stripTrailingZeros();
    }

    private Object applyLookup(Object value, UploadTemplateColumnVO col,
                               Map<String, Map<String, Object>> lookupData) {
        if (value == null) return null;
        Object result = lookupCache.lookup(
                lookupData,
                col.getLookupTable(),
                col.getLookupSourceField(),
                value.toString(),
                col.getLookupTargetField());
        if (result == null && col.getDefaultValue() != null) {
            return col.getDefaultValue();
        }
        return result;
    }

    private Object applyCodeMap(Object value, UploadTemplateColumnVO col,
                                Map<String, Map<String, Object>> lookupData) {
        if (value == null) return null;
        Object mapped = lookupCache.lookup(
                lookupData, "common_code", "code", value.toString(), "code_name");
        return mapped != null ? mapped : (col.getDefaultValue() != null ? col.getDefaultValue() : value);
    }

    private Object applyReplace(Object value, UploadTemplateColumnVO col) {
        if (value == null) return null;
        String s = value.toString();
        String param = col.getTransformParam();
        if (param == null || param.isBlank()) return s;
        try {
            JsonNode node = MAPPER.readTree(param);
            String find    = node.path("find").asText("");
            String replace = node.path("replace").asText("");
            return s.replace(find, replace);
        } catch (Exception e) {
            log.warn("REPLACE param 파싱 실패: {}", param);
            return s;
        }
    }

    private Object applySubstring(Object value, UploadTemplateColumnVO col) {
        if (value == null) return null;
        String s = value.toString();
        String param = col.getTransformParam();
        if (param == null || param.isBlank()) return s;
        try {
            JsonNode node = MAPPER.readTree(param);
            int start = node.path("start").asInt(0);
            int end   = node.path("end").asInt(s.length());
            end   = Math.min(end, s.length());
            start = Math.max(0, Math.min(start, end));
            return s.substring(start, end);
        } catch (Exception e) {
            log.warn("SUBSTRING param 파싱 실패: {}", param);
            return s;
        }
    }

    private Object applyConcat(Row row, UploadTemplateColumnVO col) {
        String param = col.getTransformParam();
        if (param == null || param.isBlank()) return null;
        try {
            JsonNode node    = MAPPER.readTree(param);
            JsonNode colsNode = node.path("columns");
            String separator = node.path("separator").asText("");

            StringBuilder sb = new StringBuilder();
            for (JsonNode c : colsNode) {
                int colIdx = c.asInt(-1);
                if (colIdx < 0) continue;
                Cell cell = row.getCell(colIdx);
                if (cell != null) {
                    Object v = readCell(cell);
                    if (v != null) {
                        if (sb.length() > 0) sb.append(separator);
                        sb.append(v);
                    }
                }
            }
            return sb.toString();
        } catch (Exception e) {
            log.warn("CONCAT param 파싱 실패: {}", param);
            return null;
        }
    }

    private Object applyTargetTypeCast(Object value, UploadTemplateColumnVO col) {
        if (value == null) return null;
        String targetType = col.getTargetType();
        if (targetType == null) return value;
        String s = value.toString().trim();
        return switch (targetType.toUpperCase()) {
            case "NUMBER", "INTEGER" -> {
                try { yield Long.parseLong(s.replace(",", "")); }
                catch (NumberFormatException e) { yield s; }
            }
            case "DECIMAL" -> {
                try { yield new BigDecimal(s.replace(",", "")); }
                catch (NumberFormatException e) { yield s; }
            }
            case "DATE"    -> applyDate(value, col);
            default        -> s;
        };
    }

    private BigDecimal toBigDecimal(Object value) {
        if (value instanceof BigDecimal bd) return bd;
        try {
            return new BigDecimal(value.toString().replace(",", "").trim());
        } catch (NumberFormatException e) {
            throw new BizException(ErrorCode.EXCEL_PARSE_FAIL,
                    "숫자 변환 실패: " + value);
        }
    }
}
