package com.geline.web.util;

import cn.hutool.core.util.ReUtil;
import cn.hutool.core.util.StrUtil;

import java.util.*;
import java.util.stream.Collectors;

/**
 * @author mx
 * @date 2026年1月22日
 */
public class DynamicSqlWebUtil {

 /**
 * 解析动态SQL+传入参数,生成新的可执行SQL
 * @param dynamicSql
 * @param map
 *
 */
 public static String parseSQL(String dynamicSql, Map<String, Object> map){
 //$xxx+表示检验必传参数
 List<String> requiredKeys = parseRequiredKeyList(dynamicSql);
 if(requiredKeys!=null && requiredKeys.size()>0){
 for(String key : requiredKeys){
 if(!map.containsKey(key)){
 throw new RuntimeException(String.format("%s is required !", key));
 }
 }
 }
 String newSql = dynamicSql;
 for(String key : map.keySet()){
 Object value = map.get(key);
 if(value==null){
 continue;
 }
 if(value instanceof Collection){
 Collection list = (Collection)value;
 if(list.size()>0){
 Object first = list.toArray()[0];
 String values = null;
 if(first instanceof CharSequence){
 List<CharSequence> newList = (List<CharSequence>) list.stream().map(row -> escapeString(row.toString())).collect(Collectors.toList());
 values = "'"+ StrUtil.join("','", newList)+"'";
 }else {
 values = StrUtil.join(",", list);
 }
 newSql = newSql.replace("${"+key+"+}", values);
 newSql = newSql.replace("${"+key+"}", values);
 newSql = newSql.replace("#{"+key+"+}", values);
 newSql = newSql.replace("#{"+key+"}", values);
 }
 }else if(value.getClass().isArray()){
 List list = Arrays.asList((Object[])value);
 if(list.size()>0){
 Object first = list.toArray()[0];
 String values = null;
 if(first instanceof CharSequence){
 List<CharSequence> newList = (List<CharSequence>) list.stream().map(row -> escapeString(row.toString())).collect(Collectors.toList());
 values = "'"+StrUtil.join("','", newList)+"'";
 }else {
 values = StrUtil.join(",", list);
 }
 newSql = newSql.replace("${"+key+"+}", values);
 newSql = newSql.replace("${"+key+"}", values);
 newSql = newSql.replace("#{"+key+"+}", values);
 newSql = newSql.replace("#{"+key+"}", values);
 }
 }else if(value instanceof CharSequence){
 // [and id in(#{idList})] --> and id in('1','2','3')
 newSql = newSql.replace("${"+key+"+}", escapeString(value.toString()));
 newSql = newSql.replace("${"+key+"}", escapeString(value.toString()));
 String wrap = StrUtil.wrap(escapeString(value.toString()), "'");
 newSql = newSql.replace("#{"+key+"+}", wrap);
 newSql = newSql.replace("#{"+key+"}", wrap);
 }else {
 // [and id in(#{idList})] --> and id in(1,2,3)
 newSql = newSql.replace("${"+key+"+}", String.valueOf(value));
 newSql = newSql.replace("${"+key+"}", String.valueOf(value));
 newSql = newSql.replace("#{"+key+"+}", String.valueOf(value));
 newSql = newSql.replace("#{"+key+"}", String.valueOf(value));
 }
 }

 //截取动态条件 [name=${name}]
 List<String> list = ReUtil.findAllGroup0("\\[([^\\]]+)\\]", newSql);
 if(list!=null && list.size()>0){
 for(String subStr : list){
 if(subStr.contains("${") || subStr.contains("#{")){
 //清除有变量条件
 newSql = newSql.replace(subStr, "");
 }else {
 //去除条件的[]
 newSql = newSql.replace(subStr, subStr.substring(1, subStr.length()-1));
 }
 }
 }
 //替换多个空格为一个空格
 newSql = newSql.replaceAll("\\s{2,}", " ");
 return newSql;
 }

 /**
 * 解析获取必填参数变量 ${key+} #{key+}
 * @param dynamicSql
 * [key1, key2]
 */
 public static List<String> parseRequiredKeyList(String dynamicSql){
 List<String> requiredKeys = ReUtil.findAllGroup0("\\{([^\\+]+)\\+\\}", dynamicSql);
 if(requiredKeys!=null && requiredKeys.size()>0){
 return requiredKeys.stream().map(row -> row.substring(1, row.length() - 2)).collect(Collectors.toList());
 }
 return new ArrayList<>();
 }

 public static String escapeString(String value){
 return value.replace("'", "''");
 }

 public static void main(String[] args) {
 String sql = "select * from table where 1=1[ and name like '%${name}%'][ and id in(#{idList})]";
 Map<String, Object> map = new HashMap<>();
 String newSql1 = parseSQL(sql, map);
 //select * from table where 1=1
 System.out.println(newSql1);

 map.put("name", "系統");
 String newSql2 = parseSQL(sql, map);
 //select * from table where 1=1 and name like '%系統%'
 System.out.println(newSql2);

 map.put("idList", Arrays.asList("aa", "bb"));
 String newSql3 = parseSQL(sql, map);
 //select * from table where 1=1 and name like '%系統%' and id in('aa','bb')
 System.out.println(newSql3);

 Integer[] idArray = new Integer[]{1, 5};
 map.put("idList", idArray);
 String newSql4 = parseSQL(sql, map);
 //select * from table where 1=1 and name like '%系統%' and id in(1,5)
 System.out.println(newSql4);

 map.put("idList", new ArrayList<>());
 String newSql5 = parseSQL(sql, map);
 //select * from table where 1=1 and name like '%系統%'
 System.out.println(newSql5);
 }
}