package incheon.uis.uld.service;


import org.apache.ibatis.binding.BindingException;
import java.util.*;

public class G2FSqlProviderX {

    // ======== Public: MyBatis Provider entry points ========

    /**
     * 배치 UPDATE (CASE WHEN) — 동적 PK 컬럼 사용
     * 필요 파라미터(@Param("param")):
     *  - schemaName : String
     *  - tableName  : String
     *  - srid       : Integer
     *  - columns    : List<Map>  (각 map: column_name, data_type)
     *  - features   : List<Map>  (각 map: gid(PK값), geometryString, properties:Map)
     *  - x_pk_column: String     (예: "gid" / "ftc" / "idn" 등)
     */
    public String batchUpdateLayerFeatures(Map<String, Object> root) {
        Map<String,Object> p = getParam(root); // @Param("param") 언랩

        final String schema = quoteIdent( safeIdent((String)p.get("schemaName")) );
        final String table  = quoteIdent( safeIdent((String)p.get("tableName")) );
        final String pkCol  = quoteIdent( safeIdent((String)p.get("x_pk_column")) );
        final int    srid   = toInt(p.get("srid"), 3857);

        @SuppressWarnings("unchecked")
        List<Map<String,Object>> columns = (List<Map<String,Object>>) p.get("columns");
        @SuppressWarnings("unchecked")
        List<Map<String,Object>> features = (List<Map<String,Object>>) p.get("features");

        if (features == null || features.isEmpty()) {
            return "UPDATE " + schema + "." + table + " SET " + pkCol + " = " + pkCol + " WHERE 1=0";
        }

        // 유효성
        if (pkCol == null || pkCol.isBlank() || pkCol.equals("\"\"")) {
            throw new BindingException("[G2FSqlProviderX] x_pk_column 이 비어있습니다.");
        }

        // ---- validColumns: 첫 feature.properties 에 있는 키만 갱신 ----
        @SuppressWarnings("unchecked")
        Map<String,Object> firstProps = (Map<String,Object>) features.get(0).get("properties");
        Set<String> valid = new LinkedHashSet<>();
        if (firstProps != null && columns != null) {
            for (Map<String,Object> col : columns) {
                String colName = (String) col.get("column_name");
                if (colName != null && firstProps.containsKey(colName)) {
                    valid.add(colName);
                }
            }
        }

        StringBuilder sql = new StringBuilder();
        sql.append("UPDATE ").append(schema).append(".").append(table).append(" SET ");

        // 1) GEOMETRY (CASE WHEN)
        sql.append("\"geom\" = CASE (").append(pkCol).append(")::text ");
        for (int i = 0; i < features.size(); i++) {
            sql.append("WHEN #{param.features[").append(i).append("].gid}::text THEN ")
                    .append("ST_Transform(ST_SetSRID(ST_Force2D(ST_GeomFromGeoJSON(#{param.features[")
                    .append(i).append("].geometryString})), 3857), #{param.srid}) ");
        }
        sql.append("ELSE \"geom\" END");

        // 2) 속성 컬럼 (각각 CASE WHEN)
        if (valid != null && !valid.isEmpty()) {
            for (String colName : valid) {
                String dataType = findType(columns, colName);
                String qCol = quoteIdent(safeIdent(colName));

                sql.append(", ").append(qCol).append(" = CASE (").append(pkCol).append(")::text ");
                for (int i = 0; i < features.size(); i++) {
                    sql.append("WHEN #{param.features[").append(i).append("].gid}::text THEN ")
                            .append("#{param.features[").append(i).append("].properties.").append(colName).append("}");
                    if (!"varchar".equalsIgnoreCase(dataType)) {
                        sql.append("::").append(dataType);
                    }
                    sql.append(" ");
                }
                sql.append("ELSE ").append(qCol).append(" END");
            }
        }

        // 3) WHERE IN (PK) — text 비교로 타입 충돌 방지
        sql.append(" WHERE (").append(pkCol).append(")::text IN (");
        for (int i = 0; i < features.size(); i++) {
            if (i > 0) sql.append(", ");
            sql.append("#{param.features[").append(i).append("].gid}::text");
        }
        sql.append(")");

        return sql.toString();
    }

    /**
     * Soft Delete: use_yn = 'Y'
     * 필요 파라미터(@Param("param")):
     *  - schemaName : String
     *  - tableName  : String
     *  - x_pk_column: String
     *  - pkValues   : List<Object>  (삭제할 PK 값들)
     */
    public String batchSoftDeleteByPk(Map<String, Object> root) {
        Map<String,Object> p = getParam(root);

        final String schema = quoteIdent( safeIdent((String)p.get("schemaName")) );
        final String table  = quoteIdent( safeIdent((String)p.get("tableName")) );
        final String pkCol  = quoteIdent( safeIdent((String)p.get("x_pk_column")) );

        @SuppressWarnings("unchecked")
        List<Object> pkValues = (List<Object>) p.get("pkValues");

        if (pkValues == null || pkValues.isEmpty()) {
            return "UPDATE " + schema + "." + table + " SET use_yn = use_yn WHERE 1=0";
        }
        if (pkCol == null || pkCol.isBlank() || pkCol.equals("\"\"")) {
            throw new BindingException("[G2FSqlProviderX] x_pk_column 이 비어있습니다.");
        }

        StringBuilder sql = new StringBuilder();
        sql.append("UPDATE ").append(schema).append(".").append(table)
                .append(" SET use_yn = 'Y'")
                .append(" WHERE (").append(pkCol).append(")::text IN (");

        for (int i = 0; i < pkValues.size(); i++) {
            if (i > 0) sql.append(", ");
            sql.append("#{param.pkValues[").append(i).append("]}::text");
        }
        sql.append(")");

        return sql.toString();
    }

    // ======== Helpers ========

    @SuppressWarnings("unchecked")
    private static Map<String,Object> getParam(Map<String,Object> root) {
        // @Param("param") 로 전달하면 MyBatis가 "param" 키로 감쌈
        Object direct = root.get("param");
        if (direct instanceof Map) return (Map<String,Object>) direct;
        return root; // 혹시 직접 map을 보냈다면 그대로 사용
    }

    private static int toInt(Object o, int def) {
        if (o == null) return def;
        if (o instanceof Number n) return n.intValue();

        try {
            return Integer.parseInt(o.toString().trim());
        } catch (NumberFormatException e) {
            return def;
        }
    }

    private static String findType(List<Map<String,Object>> columns, String colName) {
        if (columns == null) return "varchar";
        for (Map<String,Object> c : columns) {
            String name = String.valueOf(c.get("column_name"));
            if (colName.equalsIgnoreCase(name)) {
                Object dt = c.get("data_type");
                String t = (dt == null) ? "varchar" : String.valueOf(dt);
                return (t.isBlank() ? "varchar" : t);
            }
        }
        return "varchar";
    }

    /** identifier 화이트리스트: 영문/숫자/_ 만 허용 */
    private static String safeIdent(String raw) {
        if (raw == null) return "";
        return raw.replaceAll("[^A-Za-z0-9_]", "");
    }

    /** "ident" 로 감싸기 */
    private static String quoteIdent(String ident) {
        if (ident == null || ident.isBlank()) return "\"\"";
        return "\"" + ident + "\"";
    }
}