package cn.com.capcloud.api.action.service.util;

import cn.com.capcloud.api.action.service.ApiDao;
import cn.com.capcloud.ccui.core.common.json.JackJSONObject;
import cn.com.capcloud.ccui.core.common.utils.StringUtil;
import cn.com.capcloud.core.lucene.SearchParam;
import cn.com.capcloud.core.noentitydb.DBCheck;
import org.springframework.jdbc.core.JdbcTemplate;

import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author LiaoJiaHui
 * @Create 2026/4/27
 */
public class QueryConditionUtil {

    //tableSql这个参数是查询表名的。
    public static String getWhereSqlByFieldInfo(String tableSql, List<Map<String,Object>> fieldList, String jsonStr, JdbcTemplate jdbcTemplate, JackJSONObject retJson){
        JackJSONObject requestJson=JackJSONObject.fromObject(jsonStr);
        JackJSONObject queryParamJson=requestJson.getJackJSONObject("queryParam");
        Map<String,Object> map=new HashMap<>();
        List list=new ArrayList();
        String whereSql="";
        for (int i = 0; i < fieldList.size(); i++) {
            Map<String,Object> field=fieldList.get(i);
            String fieldName=String.valueOf(field.get("name"));
            map.put(fieldName,field);//map里存一份用fieldInfo,fieldName为key
            String queryParamValue=String.valueOf(queryParamJson.get(fieldName));//检索值
            String showType=String.valueOf(field.get("show_type"));//表单类型
            String isFuzzySearch=String.valueOf(field.get("is_fuzzy_search"));//查询类型：精确||模糊
            String queryType=String.valueOf(field.get("query_type"));
            String isQuery=String.valueOf(field.get("is_query"));
            // 点查询
            if (!"null".equals(isQuery) && isQuery.equals("Y")
                    && !"null".equals(queryType) && queryType.equals("0")
                    && !"null".equals(queryParamValue)) {
                if (!(fieldName.equals("starttime") || fieldName.equals("endtime"))) {
                    if (field.get("type").equals("String")) {
                        if (showType.equals("checkbox")||showType.equals("selectCheckbox")){
                            if(!"".equals(queryParamValue)){
                                String[] checkboxArray = queryParamValue.split(",");
                                for (int j = 0; j < checkboxArray.length; j++) {
                                    whereSql += " and FIND_IN_SET('" +checkboxArray[j]+"',"+ fieldName + ")";
                                }
                            }
                        }else if (showType.equals("tree")){
                            String isRecursion=String.valueOf(queryParamJson.get("isRecursion"));//是否递归
                            String treePkFieldName=String.valueOf(field.get("tree_pk_field_name"));
                            if(!"".equals(queryParamValue)&&("null".equals(isRecursion)||isRecursion.equals("Y"))){
                                String queryFunctionSql="SHOW FUNCTION STATUS WHERE NAME='"+field.get("dict_code")+"' AND DB ='"+ DBCheck.getDatabaseName()+"'";
                                if(DBCheck.isKingbase()){
                                    queryFunctionSql="SELECT proname, proargtypes FROM pg_proc WHERE proname = '"+field.get("dict_code")+"';";
                                }
                                if(DBCheck.isH2()){
                                    String sql="SELECT * FROM tbl_data_tree WHERE status=0 and tree_code='"+field.get("dict_code")+"'";
                                    List<Map<String, Object>> treeList=jdbcTemplate.queryForList(sql);//todo 做一个不存在校验
                                    Map<String, Object> treeInfo=treeList.get(0);
                                    tableSql ="WITH RECURSIVE "+field.get("dict_code")+"("+treeInfo.get("t_id")+") AS (\n" +
                                            "    SELECT "+treeInfo.get("t_id")+"\n" +
                                            "    FROM "+treeInfo.get("t_table_name")+"\n" +
                                            "    WHERE "+treeInfo.get("t_id")+" = '"+queryParamValue+"'\n" +
                                            "    UNION ALL\n" +
                                            "    SELECT t."+treeInfo.get("t_id")+"\n" +
                                            "    FROM "+treeInfo.get("t_table_name")+" t\n" +
                                            "    JOIN "+field.get("dict_code")+" p ON t."+treeInfo.get("t_p_id")+" = p."+treeInfo.get("t_id")+"\n" +
                                            ")\n"+tableSql;
                                    whereSql += " and (" +fieldName+" in (select id from "+field.get("dict_code")+")";
                                    if(!treePkFieldName.equals("null")&&treePkFieldName.length()>0){
                                        whereSql+=" or "+treePkFieldName+"='"+queryParamValue+"'";
                                    }
                                }else{
                                    if(jdbcTemplate.queryForList(queryFunctionSql).size()==0){
                                        retJson.putAll(JackJSONObject.fromObject(ApiDao.getReturnDataByCode("5050",jdbcTemplate)));
                                        retJson.put("message",retJson.get("message")+" tree_code:"+field.get("dict_code"));
                                        return "-1";
                                    }
                                    tableSql +=",(SELECT "+field.get("dict_code")+"('"+ queryParamValue + "') as _tree_ids_"+fieldName+") _khc_"+fieldName+" ";
                                    whereSql += " and (FIND_IN_SET(" +fieldName+",_khc_"+fieldName+"._tree_ids_"+fieldName+")";
                                    if(!treePkFieldName.equals("null")&&treePkFieldName.length()>0){
                                        whereSql+=" or "+treePkFieldName+"='"+queryParamValue+"'";
                                    }
                                }

                                whereSql += ")";
                            }else if(!"".equals(queryParamValue)&&isRecursion.equals("N")){
                                whereSql += " and (" + fieldName + " = '"+queryParamValue+"'";
                                if(!treePkFieldName.equals("null")&&treePkFieldName.length()>0){
                                    whereSql+=" or "+treePkFieldName+"='"+queryParamValue+"'";
                                }
                                whereSql += ")";
                            }
                            if(DBCheck.isKingbase()||DBCheck.isH2()){
                                whereSql += " and LENGTH("+fieldName+")>0";
                            }else{
                                whereSql += " and " + fieldName + " != ''";
                            }
                        }else if (showType.equals("tag")){
                            if(!"".equals(queryParamValue)){
                                String[] tagArray = queryParamValue.split(",");
                                for (int j = 0; j < tagArray.length; j++) {
                                    whereSql += " and FIND_IN_SET('" +tagArray[j]+"',"+ fieldName + ")";
                                }
                            }
                        }else if (!"null".equals(isFuzzySearch)&&isFuzzySearch.equals("0")) {
                            if (!"null".equals(queryParamValue) && queryParamValue.length() > 0) {
                                whereSql += " and " + fieldName + " like '%" + queryParamValue + "%'";
                            }
                        }else if (!"null".equals(isFuzzySearch)&&isFuzzySearch.equals("2")) {
                            if (!"null".equals(queryParamValue) && queryParamValue.length() > 0) {
                                whereSql += " and " + fieldName + " = '"+ queryParamValue + "'";
                            }
                        }else if (!"null".equals(isFuzzySearch)&&isFuzzySearch.equals("3")) {
                            if (!"null".equals(queryParamValue) && queryParamValue.length() > 0) {
                                whereSql += " and " + fieldName + " > "+ queryParamValue;
                            }
                        }else if (!"null".equals(isFuzzySearch)&&isFuzzySearch.equals("4")) {
                            if (!"null".equals(queryParamValue) && queryParamValue.length() > 0) {
                                whereSql += " and " + fieldName + " < "+ queryParamValue;
                            }
                        }else if (!"null".equals(isFuzzySearch)&&isFuzzySearch.equals("5")) {
                            if (!"null".equals(queryParamValue) && queryParamValue.length() > 0) {
                                whereSql += " and " + fieldName + " != '"+ queryParamValue + "'";
                            }
                        }else if (!"null".equals(isFuzzySearch)&&isFuzzySearch.equals("6")) {
                            if (!"null".equals(queryParamValue) && queryParamValue.length() > 0) {
                                whereSql += " and " + fieldName + " >= "+ queryParamValue;
                            }
                        }else if (!"null".equals(isFuzzySearch)&&isFuzzySearch.equals("7")) {
                            if (!"null".equals(queryParamValue) && queryParamValue.length() > 0) {
                                whereSql += " and " + fieldName + " <= "+ queryParamValue;
                            }
                        }
                    } else if (StringUtil.isNotEmpty(queryParamValue)) {
                        if(fieldName.equals("create_org_id")) {
                            whereSql += " and " + fieldName + " in ("
                                    + queryParamValue+")";
                        }else {
                            if (!"null".equals(isFuzzySearch)&&isFuzzySearch.equals("2")) {
                                if (!"null".equals(queryParamValue) && queryParamValue.length() > 0) {
                                    whereSql += " and " + fieldName + " = "+ queryParamValue;
                                }
                            }else if (!"null".equals(isFuzzySearch)&&isFuzzySearch.equals("3")) {
                                if (!"null".equals(queryParamValue) && queryParamValue.length() > 0) {
                                    whereSql += " and " + fieldName + " > "+ queryParamValue;
                                }
                            }else if (!"null".equals(isFuzzySearch)&&isFuzzySearch.equals("4")) {
                                if (!"null".equals(queryParamValue) && queryParamValue.length() > 0) {
                                    whereSql += " and " + fieldName + "< "+ queryParamValue;
                                }
                            }else if (!"null".equals(isFuzzySearch)&&isFuzzySearch.equals("5")) {
                                if (!"null".equals(queryParamValue) && queryParamValue.length() > 0) {
                                    whereSql += " and " + fieldName + "!= "+ queryParamValue;
                                }
                            }else if (!"null".equals(isFuzzySearch)&&isFuzzySearch.equals("6")) {
                                if (!"null".equals(queryParamValue) && queryParamValue.length() > 0) {
                                    whereSql += " and " + fieldName + ">= "+ queryParamValue;
                                }
                            }else if (!"null".equals(isFuzzySearch)&&isFuzzySearch.equals("7")) {
                                if (!"null".equals(queryParamValue) && queryParamValue.length() > 0) {
                                    whereSql += " and " + fieldName + "<= "+ queryParamValue;
                                }
                            }
                        }
                    }
                }
            } else if(!"null".equals(isQuery) && isQuery.equals("Y")
                    && !"null".equals(queryType) && queryType.equals("1")
                    && !"null".equals(String.valueOf(queryParamJson.get(fieldName)))) {
                //段查询（一个字段）
                String fieldValue = String.valueOf(queryParamJson.get(fieldName));
                String[] array = fieldValue.split(",");
                if (array.length!=0&&array[0].length()>0&&array[1].length()>0) {
                    String sql=" ";
                    if(!array[0].equals("null")&&array[0].length()>0){
                        sql=" and (";
                        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
                        // 将字符串转化为 LocalDate
                        LocalDate startDate = LocalDate.parse(array[0], formatter);

                        // 将开始日期减去一天
                        LocalDate adjustedDate = startDate.minusDays(1);
                        sql+=fieldName+">='"+adjustedDate+" 23:59:59' ";
                        if(!array[1].equals("null")&&array[1].length()>0){
                            sql+=" and ";
                            sql+=fieldName+"<='"+array[1]+" 23:59:59' ";
                        }
                        sql+=" ) ";
                    }else{
                        if(!array[1].equals("null")&&array[1].length()>0){
                            sql+=" and ";
                            sql+=fieldName+"<='"+array[1]+" 23:59:59' ";
                        }
                    }
                    whereSql+=sql;
                }
            } else if(!"null".equals(isQuery) && isQuery.equals("Y")  && !"null".equals(queryType) && queryType.equals("2")
                    && !"null".equals(queryParamValue)) {
                //段查询（两个字段），有就先全部存到一个list里最后再拼接sql语句
                if (StringUtil.isNotEmpty(queryParamValue)) {
                    list.add(fieldName);//标识段查询的fieldName
                }
            } else {
                if (StringUtil.isNotEmpty(queryParamValue)) {
                    //除 page_parent_uuid 其他字段查询都需要在query组件配置，否则传参进来也不会做为检索条件，防止第三方通过请求传敏感字段查询如：id字段等
                    if(isQuery.equals("Y")||fieldName.equals("page_parent_uuid")){
                        if (field.get("type").equals("String")) {
                            whereSql += " and " + fieldName + " like '" + queryParamValue + "'";
                        } else {
                            if(fieldName.equals("create_org_id")) {
                                whereSql += " and " + fieldName + " in (" + queryParamValue+")";
                            }else {
                                whereSql += " and " + fieldName + " = " + queryParamValue;
                            }
                        }
                    }
                }
            }
        }
        //段查询(两个字段配对)
        for (int i = 0; i < list.size(); i++) {
            Map<String,Object> fieldInfo= (Map<String, Object>) map.get(list.get(i));
            String referenceField=String.valueOf(fieldInfo.get("segment_reference_field"));
            String referenceValue=String.valueOf(queryParamJson.get(referenceField));
            String field=String.valueOf(fieldInfo.get("name"));
            String value=String.valueOf(queryParamJson.get(list.get(i)));
            list.remove(i);
            i--;
            Boolean isNull=false;
            if("null".equals(referenceValue)||referenceValue.length()==0){
                isNull=true;
                referenceValue="";
            }else{
                list.remove(referenceField);
            }
            if("start".equals(String.valueOf(fieldInfo.get("segment_type")))){
                if(isNull){
                    whereSql += " and ((" +field+">='"+value+"')"
                            +"OR ("+referenceField+">='"+value+"'))";
                }else{
                    whereSql += " and ((" +field+">='"+value+"' and "+field+"<='"+referenceValue+"')"
                            +"OR ("+referenceField+">='"+value+"' and "+referenceField+"<='"+referenceValue+"'))";
                }
            }else if("end".equals(String.valueOf(fieldInfo.get("segment_type")))){
                if(isNull){
                    whereSql += " and (("+referenceField+"<='"+value+"')"
                            +"OR ("+field+"<='"+value+"'))";
                }else{
                    whereSql += " and ((" +referenceField+">='"+referenceValue+"' and "+referenceField+"<='"+value+"')"
                            +"OR ("+field+">='"+referenceValue+"' and "+field+"<='"+value+"'))";
                }
            }
        }
        //table search 表格搜索框
        String tableSearchValue=String.valueOf(queryParamJson.get("tableSearchValue"));
        String tableSearchField=String.valueOf(queryParamJson.get("tableSearchField"));
        if(!tableSearchValue.equals("null")&&tableSearchValue.length()>0){
            String tableSearchSql="";
            if(tableSearchField.equals("")){
                for (int i = 0; i < fieldList.size(); i++) {
                    String fieldName=String.valueOf(fieldList.get(i).get("name"));
                    String isQuery=String.valueOf(fieldList.get(i).get("is_query"));
                    if(isQuery.equals("Y")){
                        tableSearchSql+=" "+fieldName+" like'%"+tableSearchValue+"%' or";
                    }
                }
                if(tableSearchSql.length()>0){
                    tableSearchSql=tableSearchSql.substring(1,tableSearchSql.length()-2);//将最后的一个or去掉。
                    whereSql+= " and ("+tableSearchSql+")";
                }
            }else{
                whereSql+= " and "+tableSearchField+" like'%"+tableSearchValue+"%' ";
            }

        }
        String queryIds=String.valueOf(queryParamJson.get("queryIds"));//表单里套页面才会有此参数（并且表单类型是：page）
        if(StringUtil.isNotEmpty(queryIds)){
            whereSql+=" and FIND_IN_SET(id ,'"+queryIds+"' ) ";
        }
        Map<String,Object> fieldInfo= (Map<String, Object>) map.get("status");
        if(fieldInfo==null){
            whereSql=tableSql+" where 1=1 "+whereSql+" ";
        }else{
            whereSql=tableSql+" where 1=1 and status='0' "+whereSql+" ";
        }
        return whereSql;
    }
    public static List<SearchParam> getLuceneSearchParam(String tableName, List<Map<String,Object>> fieldList, String jsonStr, JdbcTemplate jdbcTemplate, JackJSONObject retJson){
        JackJSONObject requestJson=JackJSONObject.fromObject(jsonStr);
        JackJSONObject queryParamJson=requestJson.getJackJSONObject("queryParam");
        Map<String,Object> map=new HashMap<>();
        List<SearchParam> listSearch=new ArrayList<>();
        for (int i = 0; i < fieldList.size(); i++) {
            Map<String,Object> fieldInfo=fieldList.get(i);
            String fieldName=String.valueOf(fieldInfo.get("name"));
            map.put(fieldName,fieldList.get(i));//map里存一份用fieldInfo,fieldName为key
            String queryParamValue=String.valueOf(queryParamJson.get(fieldName));//检索值
            String isFuzzySearch=String.valueOf(fieldInfo.get("is_fuzzy_search"));//查询类型：精确||模糊
            String queryType=String.valueOf(fieldInfo.get("query_type"));
            String isQuery=String.valueOf(fieldInfo.get("is_query"));
            // 点查询
            if (!"null".equals(isQuery) && isQuery.equals("Y")
                    && !"null".equals(queryType) && queryType.equals("0")
                    && !"null".equals(queryParamValue)) {
                if (!(fieldName.equals("starttime") || fieldName.equals("endtime"))) {
                    if (!"null".equals(isFuzzySearch)&&isFuzzySearch.equals("0")) {//模糊查询（分词）
                        if (!"null".equals(queryParamValue) && queryParamValue.length() > 0) {
                            SearchParam sear=new SearchParam(queryParamValue,fieldName,1,tableName);//todo 默认searchType：0 不分词，以后再适配分词
                            Map<String,Object> filterMap=new HashMap<>();
                            sear.setFilterMap(filterMap);
                            if(!queryParamValue.equals("null")&&queryParamValue.length()>0){
                                listSearch.add(sear);
                            }
                        }
                    }else if (!"null".equals(isFuzzySearch)&&isFuzzySearch.equals("2")) {//精确查询（不分词） todo 后续使用独立的字典
                        if (!"null".equals(queryParamValue) && queryParamValue.length() > 0) {
                            SearchParam sear=new SearchParam(queryParamValue,fieldName,0,tableName);
                            Map<String,Object> filterMap=new HashMap<>();
                            sear.setFilterMap(filterMap);
                            if(!queryParamValue.equals("null")&&queryParamValue.length()>0){
                                listSearch.add(sear);
                            }
                        }
                    }
                }
            } else {
                if (!"null".equals(queryParamValue) && queryParamValue.length() > 0) {
                    //除 page_parent_uuid 其他字段查询都需要在query组件配置，否则传参进来也不会做为检索条件，防止第三方通过请求传敏感字段查询如：id字段等
                    if(isQuery.equals("Y")||fieldName.equals("page_parent_uuid")){
                        SearchParam sear=new SearchParam(queryParamValue,fieldName,0,tableName);
                        Map<String,Object> filterMap=new HashMap<>();
                        sear.setFilterMap(filterMap);
                        if(!queryParamValue.equals("null")&&queryParamValue.length()>0){
                            listSearch.add(sear);
                        }
                    }
                }
            }
        }
        //table search 表格搜索框
        String tableSearchValue=String.valueOf(queryParamJson.get("tableSearchValue"));
        String tableSearchField=String.valueOf(queryParamJson.get("tableSearchField"));
        if(!tableSearchValue.equals("null")&&tableSearchValue.length()>0){
            String fieldNameStr="";
            if(tableSearchField.equals("")){
                for (int i = 0; i < fieldList.size(); i++) {
                    String fieldName=String.valueOf(fieldList.get(i).get("name"));
                    String isQuery=String.valueOf(fieldList.get(i).get("is_query"));
                    if(isQuery.equals("Y")){
                        fieldNameStr+=fieldName+",";
                    }
                }
                if(!fieldNameStr.equals("")){
                    fieldNameStr=fieldNameStr.substring(0,fieldNameStr.length()-1);
                }
                SearchParam sear=new SearchParam(tableSearchValue,fieldNameStr,1,tableName);//todo 默认searchType：1 不分词，以后再适配分词
                Map<String,Object> filterMap=new HashMap<>();
                sear.setFilterMap(filterMap);
                listSearch.add(sear);
            }else{
                SearchParam sear=new SearchParam(tableSearchValue,tableSearchField,1,tableName);//todo 默认searchType：1 不分词，以后再适配分词
                Map<String,Object> filterMap=new HashMap<>();
                sear.setFilterMap(filterMap);
                listSearch.add(sear);
            }

        }
        return listSearch;
    }
}
