package incheon.ags.mrb.style.mapper;

import java.util.regex.Pattern;

public class ClassificationSQLProvider {

    // 한글, 영문, 숫자, 언더스코어 허용 (PostgreSQL 한글 컬럼명 지원)
    private static final Pattern IDENT = Pattern.compile("[A-Za-z0-9_\\u3131-\\u318E\\uAC00-\\uD7A3]+");

    // 스키마.테이블 형식 (한글 허용)
    private static final Pattern IDENT_WITH_SCHEMA = Pattern.compile("[A-Za-z0-9_\\u3131-\\u318E\\uAC00-\\uD7A3]+(\\.[A-Za-z0-9_\\u3131-\\u318E\\uAC00-\\uD7A3]+)?");

    private static void assertTableIdent(String s) {
        if (s == null || !IDENT_WITH_SCHEMA.matcher(s).matches()) {
            throw new IllegalArgumentException("Invalid table identifier: " + s);
        }
    }

    private static void assertIdent(String s) {
        if (s == null || !IDENT.matcher(s).matches()) {
            throw new IllegalArgumentException("Invalid identifier: " + s);
        }
    }

    private static String q(String ident) {
        return "\"" + ident + "\"";
    }

    private static boolean isMixedCase(String str) {
        if (str == null || str.isEmpty()) return false;
        boolean hasUpper = false;
        boolean hasLower = false;
        for (char c : str.toCharArray()) {
            if (Character.isUpperCase(c)) hasUpper = true;
            if (Character.isLowerCase(c)) hasLower = true;
            if (hasUpper && hasLower) return true;
        }
        return false;
    }

    public String getEqualClassification(String lyrPhysNm, String columnName, int numBreaks) {

        assertTableIdent(lyrPhysNm);
        assertIdent(columnName);
        if (isMixedCase(columnName)) {
            columnName = q(columnName);
        }

        return "WITH stats AS (" +
                "SELECT MIN(" + columnName + ") AS min_val, " +
                "MAX(" + columnName + ") AS max_val " +
                "FROM " + lyrPhysNm + ") " +
                "SELECT min_val + (max_val - min_val) * i / " + numBreaks +
                " AS break_value FROM stats, generate_series(0, " +
                numBreaks + ") AS s(i)";
    }

    public String getQuantileClassification(String lyrPhysNm, String columnName, int numBreaks) {

        assertTableIdent(lyrPhysNm);
        assertIdent(columnName);
        if (isMixedCase(columnName)) {
            columnName = q(columnName);
        }

        return "WITH quantiles AS ( " +
                "  SELECT generate_series(0.0, 1.0, 1.0 / " + numBreaks + ") AS p " +
                ") " +
                "SELECT percentile_cont(p) WITHIN GROUP (ORDER BY " + columnName + ") AS break_value " +
                "FROM " + lyrPhysNm + ", quantiles " +
                "GROUP BY p " +
                "ORDER BY break_value";
    }
}