package com.xly.service;

import cn.hutool.core.text.CharSequenceUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONUtil;
import com.alibaba.fastjson.JSONObject;
import com.xly.constant.ErrorCode;
import com.xly.constant.ProcedureConstant;
import com.xly.exception.dto.BusinessException;
import com.xly.mapper.DynamicExeDbMapper;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import java.util.*;
import java.util.stream.Stream;

/***
 * @Author 钱豹
 * @Date 22:39 2026/1/30
 * @Param
 * @return
 * @Description 动态执行SQL业务类
 **/
@Slf4j
@Service("dynamicExeDbService")
@RequiredArgsConstructor
public class DynamicExeDbService {

    private final DynamicExeDbMapper dynamicExeDbMapper;
    private	static final String PARAMETER_NAME = "PARAMETER_NAME";
    private	static final String PARAMETER_MODE = "PARAMETER_MODE";
    private	static final String DATA_TYPE = "DATA_TYPE";
    private	static final String INT = "int";



    /***
     * @Author 钱豹
     * @Date 22:43 2026/1/30
     * @Param []
     * @return java.util.List<java.util.Map<java.lang.String,java.lang.Object>>
     * @Description 查询SQL执行
     **/
    public List<Map<String,Object>> findSql(Map<String,Object> searMap,String sSql){
        Map<String,Object> findMap = new HashMap<>(searMap);
        findMap.put("sSql",sSql);
        return dynamicExeDbMapper.findSql(findMap);
    }

    /***
     * @Author 钱豹
     * @Date 22:44 2026/1/30
     * @Param [updMap]
     * @return void
     * @Description 更新SQL执行
     **/
    public void updateSql(Map<String,Object> updMap,String sSql){
         Map<String,Object> updateMap = new HashMap<>(updMap);
         updateMap.put("sSql",sSql);
         dynamicExeDbMapper.updateSql(updateMap);
    }

    /***
     * @Author 钱豹
     * @Date 22:45 2026/1/30
     * @Param [updMap]
     * @return void
     * @Description 新增SQL执行
     **/
    public void addSql(Map<String,Object> addMap,String sSql){
        Map<String,Object> addSql = new HashMap<>(addMap);
        addSql.put("sSql",sSql);
        dynamicExeDbMapper.addSql(addSql);
    }

    /****
     * @Author 钱豹
     * @Date 22:56 2026/1/30
     * @Param [delMap]
     * @return void
     * @Description 删除QL执行
     **/
    public void delSql(Map<String,Object> delMap,String sSql){
        Map<String,Object> delSql = new HashMap<>(delMap);
        delSql.put("sSql",sSql);
        dynamicExeDbMapper.delSql(delSql);
    }


    /***
     * @Author 钱豹
     * @Date 22:57 2026/1/30
     * @Param [searMap, sProName]
     * @return java.util.List<java.util.List<java.util.Map<java.lang.String,java.lang.Object>>>
     * @Description 动态SQL执行 默认10个结果集返回
     **/
    public List<List<Map<String,Object>>> getCallProMoreResult(Map<String, Object> searMap) {
        List<List<Map<String,Object>>> outListData = null;
        try{
            //
            outListData = this.dynamicExeDbMapper.getCallProMoreResult(searMap);
        }catch (Exception e){
            log.error("执行失败，失败原因：",e);
        }
        String sMassage = (ObjectUtil.isEmpty(searMap.get(ProcedureConstant.SRETURN)))? StrUtil.EMPTY:searMap.get(ProcedureConstant.SRETURN).toString();
        String sCode = (ObjectUtil.isEmpty(searMap.get(ProcedureConstant.SCODE)))? StrUtil.EMPTY:searMap.get(ProcedureConstant.SCODE).toString();
        if(ObjectUtil.isNotEmpty(sCode)
                && Integer.valueOf(sCode)< 0){
            throw new BusinessException(Integer.valueOf(sCode),sMassage);
        }
        return outListData;
    }
    /***
     * @Author 钱豹
     * @Date 9:33 2026/1/31
     * @Param [searMap, sProName]
     * @return java.util.Map<java.lang.String,java.lang.Object>
     * @Description 调用过程
     **/
    public Map<String,Object> getCallPro(Map<String, Object> searMap, String sProName){
        List<Map<String,Object>> outListData = null;
        long oldRead = System.currentTimeMillis();
        try{
            outListData = this.dynamicExeDbMapper.getCallPro (searMap);
        }catch (Exception e){
            log.error("调用过程异常",e);
            this.doException(e,searMap, sProName);
        }
        String sMsgText = null;
        if(ObjectUtil.isNotEmpty(searMap.get(ProcedureConstant.SRETURN))){
            sMsgText = searMap.get(ProcedureConstant.SRETURN).toString();
        }
        Map<String,Object> outMapData = new HashMap<>(8);
        Set<String> outSet = new HashSet<>();
        if(ObjectUtil.isNotEmpty(searMap.get(ProcedureConstant.OUTSETSTRING))){
            outSet = (Set<String>) searMap.get(ProcedureConstant.OUTSETSTRING);
        }
        //返回出参返回的数据
        outSet.forEach(out->{
            try{
                if(ObjectUtil.isNotEmpty(searMap.get(out))
                        && JSONUtil.isJson(searMap.get(out).toString())){
                    if(JSONUtil.isJsonObj(searMap.get(out).toString())){
                        outMapData.put(out, JSONObject.parseObject(searMap.get(out).toString()));
                    }else if(JSONUtil.isJsonArray(searMap.get(out).toString())){
                        outMapData.put(out, JSONObject.parseArray(searMap.get(out).toString()));
                    }
                }else{
                    outMapData.put(out, searMap.get(out));
                }
            }catch (Exception e){
                outMapData.put(out, searMap.get(out));
            }
        });
        Map<String,Object> retMap = new HashMap<>(4);
        retMap.put(ProcedureConstant.OUTLIST,ObjectUtil.isNotEmpty(outListData) ? outListData : new ArrayList<>());
        retMap.put(ProcedureConstant.OUTMAP,ObjectUtil.isNotEmpty(outMapData) ? outMapData : new HashMap<>(4));
        retMap.put(ProcedureConstant.SRETURN,ObjectUtil.isNotEmpty(sMsgText) ? sMsgText : "");
        retMap.put(ProcedureConstant.SCODE,searMap.get(ProcedureConstant.SCODE));
        return retMap;
    }
    /***
     * @Author 钱豹
     * @Date 9:22 2026/1/31
     * @Param [searMap, sProName, msg]
     * @return java.lang.String
     * @Description 过程执行错误异常提醒
     **/
    public String getProExceptionMsg(Map<String, Object> searMap, String sProName , String msg){
        StringBuffer sb = new StringBuffer();
        try{
            //根据过程名称获取过程参数
            List<Map<String, Object>> proList = getProcParam(sProName);
            sb.append("{ CALL ").append(sProName).append("(");
            Stream.iterate(0, i -> i + 1).limit(proList.size()).forEach(i -> {
                if(ProcedureConstant.OUT.equalsIgnoreCase(proList.get(i).get(PARAMETER_MODE).toString())){
                    if(i==0){
                        sb.append("@").append(proList.get(i).get(PARAMETER_NAME));
                    }else{
                        sb.append(",@").append(proList.get(i).get(PARAMETER_NAME));
                    }
                }else{
                    if(ObjectUtil.isEmpty(searMap.get(proList.get(i).get(PARAMETER_NAME)))){
                        if(i==0){
                            sb.append("NULL");
                        }else{
                            sb.append(",NULL");
                        }
                    }else{
                        String sControl = ObjectUtil.isNotEmpty(searMap.get(proList.get(i).get(PARAMETER_NAME)))?
                                searMap.get(proList.get(i).get(PARAMETER_NAME)).toString(): CharSequenceUtil.EMPTY;
                        String sPlit = "'";
                        if(sControl.contains("'")){
                            sPlit = "\"";
                        }
                        if(i==0){
                            sb.append(sPlit).append(sControl).append(sPlit);
                        }else{
                            sb.append(",").append(sPlit).append(sControl).append(sPlit);
                        }
                    }
                }
            });
            sb.append(") ");
            msg = sb.toString();
        }catch (Exception e){
            e.printStackTrace();
        }
        return msg;
    }

    public Map<String, Object> getDoProMap(String sProName, Map<String, Object> params) throws BusinessException{
        Map<String,Object> searMap = new HashMap<>(4);
        try{

            List<Map<String, Object>> proList = getProcParam( sProName);
            //{CALL mytest(#{ownerid,mode=IN,jdbcType=INTEGER},#{examcount,mode=OUT,jdbcType=INTEGER})}
            StringBuffer sb = new StringBuffer();
            sb.append("{CALL ").append(sProName).append("(");
            Set<String> outSet = new HashSet<>(4);
            Stream.iterate(0, i -> i + 1).limit(proList.size()).forEach(i -> {
                if(i==0){
                    sb.append("#{").append(proList.get(i).get(PARAMETER_NAME)).append(",mode=").append(proList.get(i).get(PARAMETER_MODE));
                }else{
                    sb.append(",#{").append(proList.get(i).get(PARAMETER_NAME)).append(",mode=").append(proList.get(i).get(PARAMETER_MODE));
                }
                if(ProcedureConstant.IN.equalsIgnoreCase(proList.get(i).get(PARAMETER_MODE).toString())){
                    if(params.get(proList.get(i).get(PARAMETER_NAME)) != null){
                        if(params.get(proList.get(i).get(PARAMETER_NAME)) instanceof List
                                || params.get(proList.get(i).get(PARAMETER_NAME)) instanceof Map){
                            searMap.put(proList.get(i).get(PARAMETER_NAME).toString(),
                                    JSONUtil.toJsonStr(params.get(proList.get(i).get(PARAMETER_NAME)))
                            );
                        }else{
                            searMap.put(proList.get(i).get(PARAMETER_NAME).toString(),
                                    params.get(proList.get(i).get(PARAMETER_NAME)));
                        }
                    }else{
                        searMap.put(proList.get(i).get(PARAMETER_NAME).toString(),null);
                    }
//                    if(proList.get(i).get(PARAMETER_NAME).toString().startsWith("s")){
//                        sb.append(",jdbcType=").append("LONGVARCHAR");
//                    }
                }else{
                    if(proList.get(i).get(DATA_TYPE).equals(INT)){
                        sb.append(",jdbcType=").append("INTEGER");
                        searMap.put(proList.get(i).get(PARAMETER_NAME).toString(),0);
                    }else{
                        searMap.put(proList.get(i).get(PARAMETER_NAME).toString(),proList.get(i).get(PARAMETER_NAME).toString());
                        sb.append(",jdbcType=").append("LONGVARCHAR");
                    }
                    outSet.add(proList.get(i).get(PARAMETER_NAME).toString());
                }
                sb.append("}");
            });
            sb.append(")} ");
            //获取过程返回
            searMap.put("sSql",sb.toString());
            searMap.put("outSet",outSet);
        }catch (Exception e){
            throw new BusinessException(ErrorCode.DB_ERROR,"解析获取过程查询sSql异常。异常原因："+e.getMessage());
        }
        return searMap;
    }

    /***
     * @Author 钱豹
     * @Date 9:25 2026/1/31
     * @Param [proName]
     * @return java.util.List<java.util.Map<java.lang.String,java.lang.Object>>
     * @Description 动态获取过程参数
     **/
    public List<Map<String, Object>> getProcParam(String proName){
        StringBuffer paramSql = new StringBuffer();
        Map<String, Object> serMap=new HashMap<>(4);
        paramSql.append(" SELECT PARAMETER_NAME,PARAMETER_MODE,DATA_TYPE,ORDINAL_POSITION from information_schema.PARAMETERS ");
        paramSql.append(" WHERE SPECIFIC_NAME=#{sProName}");
        paramSql.append(" AND SPECIFIC_SCHEMA= DATABASE() ORDER BY ORDINAL_POSITION ASC");
        serMap.put("sSql", paramSql.toString());
        serMap.put("sProName",proName);
        List<Map<String,Object>> paramList = dynamicExeDbMapper.findSql(serMap);
        if(paramList==null || paramList.size()<1){
            throw new BusinessException(ErrorCode.DB_ERROR,"数据库中未查询到过程，过程名称："+proName+",请检查!");
        }
        return paramList;
    }

    private void doException(Exception e,Map<String, Object> searMap,String sProName){
        String msg = StrUtil.EMPTY;
        String exMsg = "";
        try{
            msg = getProExceptionMsg(searMap, sProName,JSONUtil.toJsonStr(searMap));
            if(ObjectUtil.isNotEmpty( e.getMessage())){
                String[] msA=  e.getMessage().split(":");
                exMsg = msA[msA.length-1];
            }
        }catch (Exception ex){

        }
        //-10标识过程中返回错误异常，非正常提示异常
        throw new BusinessException(ErrorCode.DB_ERROR,"调用过程执行语句报错,SQL:"+msg+" ; 失败原因:"+ exMsg);
    }

}