package com.hx.other.service.controller;

import com.alibaba.fastjson.JSONObject;
import com.hx.common.BaseController;
import com.hx.other.service.api.ai.SqlAiApi;
import com.hx.other.service.model.BaseEntity;
import com.hx.other.service.model.QueryRecord;
import com.hx.other.service.service.QueryRecordService;
import com.hx.other.service.service.SqlQueryTempService;
import com.hx.other.service.util.AiQueryUtil;
import com.hx.other.service.vo.BaseVo;
import com.hx.other.service.vo.ai.SqlVo;
import com.hx.resultTool.Result;
import com.hx.util.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import java.util.Calendar;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * ai查询控制器
 * @Author: cmg
 * @Date: 2023/4/23 10:29
 */
@RestController
@RequestMapping("/ai")
public class SqlQueryController extends BaseController {

    @Resource
    private SqlQueryTempService sqlQueryTempService;

    @Autowired
    @Qualifier("readJdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    @Resource
    private SqlAiApi sqlAiApi;

    @Resource
    private QueryRecordService queryRecordService;

    @PostMapping("/query")
    public Result query(@RequestBody(required = false) BaseVo baseVo)
    {

        if(baseVo == null || StringUtils.isEmpty(baseVo.getKeyWord()))
        {
            throwParamException("请输入查询条件");
        }

        QueryRecord queryRecord = new QueryRecord();
        queryRecord.setContent(baseVo.getKeyWord());

        //先从模板里匹配
        String sql = sqlQueryTempService.selectByContent(baseVo.getKeyWord());
        if(StringUtils.isEmpty(sql))
        {
            //记录向ai查询时的毫秒数
            queryRecord.setQueryTime(Calendar.getInstance().getTimeInMillis());

            //模板里没有，从ai里查询
            JSONObject obj = new JSONObject();
            obj.put("msg", baseVo.getKeyWord());
            obj = AiQueryUtil.aiQuery(sqlAiApi, obj);

            //记录ai返回的豪秒数
            queryRecord.setAiResultTime(Calendar.getInstance().getTimeInMillis());

            if(obj != null && 100 == obj.getIntValue("code"))
            {
                //判断返回是否sql
                if(obj.getBooleanValue("isSql"))
                {
                    //是sql
                    sql = obj.getString("now");
                    //记录ai返回的结果
                    queryRecord.setAiResult(obj.getString("origin"));

                }

                //记录ai查询的content
                queryRecord.setAiQueryContent(obj.getString("content"));
            }
        }else{
            queryRecord.setIsFromQuery(BaseEntity.YES);
        }

        List<Map<String, Object>> list = null;
        if(!StringUtils.isEmpty(sql))
        {
            //记录查询的sql
            queryRecord.setSqlStr(sql);

            list =  AiQueryUtil.jdbcQuery(jdbcTemplate, sql);

            //记录sql查询返回的豪秒数
            queryRecord.setSqlTime(Calendar.getInstance().getTimeInMillis());
        }

        SqlVo sqlVo = new SqlVo();
        sqlVo.setNow(sql);
        sqlVo.setOrigin(queryRecord.getAiResult());
        sqlVo.setQueryContent(queryRecord.getAiQueryContent());
        sqlVo.setIsArr(1);
        sqlVo.setStatus(StringUtils.isEmpty(sql) ? 0 : 1);
        sqlVo.setMsg(list == null ? "查无信息" : list);
        sqlVo.setAiQueryTime(queryRecord.getQueryTime());
        sqlVo.setAiResultTime(queryRecord.getAiResultTime());
        sqlVo.setSqlResultTime(queryRecord.getSqlTime());

        if(list != null)
        {
            //当返回是个统计数值时，直接返回数值
            if(list.size() == 1)
            {
                Set<String> set = list.get(0).keySet();
                if(set.size() == 1) {
                    for (String key : set) {
                        if (key.toUpperCase().indexOf("COUNT") != -1 || key.toUpperCase().indexOf("SUM") != -1) {
                            sqlVo.setIsArr(0);
                            sqlVo.setMsg(list.get(0).get(key));
                        }
                    }
                }
            }
        }

        queryRecordService.insert(queryRecord);

        return Result.success(sqlVo);
    }

    @PostMapping("/query/test")
    public Result queryTest(@RequestBody(required = false) BaseVo baseVo)
    {

        if(baseVo == null || StringUtils.isEmpty(baseVo.getKeyWord()))
        {
            throwParamException("请输入查询条件");
        }

        QueryRecord queryRecord = new QueryRecord();
        queryRecord.setContent(baseVo.getKeyWord());

        //先从模板里匹配
        String sql = sqlQueryTempService.selectByContent(baseVo.getKeyWord());
        if(StringUtils.isEmpty(sql))
        {
            //记录向ai查询时的毫秒数
            queryRecord.setQueryTime(Calendar.getInstance().getTimeInMillis());

            //模板里没有，从ai里查询
            JSONObject obj = new JSONObject();
            obj.put("msg", baseVo.getKeyWord());
            obj = AiQueryUtil.aiQuery(sqlAiApi, obj);

            //记录ai返回的豪秒数
            queryRecord.setAiResultTime(Calendar.getInstance().getTimeInMillis());

            if(obj != null && 100 == obj.getIntValue("code"))
            {
                //判断返回是否sql
                if(obj.getBooleanValue("isSql"))
                {
                    //是sql
                    sql = obj.getString("now");
                    //记录ai返回的结果
                    queryRecord.setAiResult(obj.getString("origin"));

                }

                //记录ai查询的content
                queryRecord.setAiQueryContent(obj.getString("content"));
            }
        }else{
            queryRecord.setIsFromQuery(BaseEntity.YES);
        }

        List<Map<String, Object>> list = null;
        if(!StringUtils.isEmpty(sql))
        {
            //记录查询的sql
            queryRecord.setSqlStr(sql);

            list =  AiQueryUtil.jdbcQuery(jdbcTemplate, sql);

            //记录sql查询返回的豪秒数
            queryRecord.setSqlTime(Calendar.getInstance().getTimeInMillis());
        }

        SqlVo sqlVo = new SqlVo();
        sqlVo.setIsArr(1);
        sqlVo.setStatus(StringUtils.isEmpty(sql) ? 0 : 1);
        sqlVo.setMsg(list == null ? "查无信息" : list);

        if(list != null)
        {
            //当返回是个统计数值时，直接返回数值
            if(list.size() == 1)
            {
                Set<String> set = list.get(0).keySet();
                if(set.size() == 1) {
                    for (String key : set) {
                        if (key.toUpperCase().indexOf("COUNT") != -1 || key.toUpperCase().indexOf("SUM") != -1) {
                            sqlVo.setIsArr(0);
                            sqlVo.setMsg(list.get(0).get(key));
                        }
                    }
                }
            }
        }

        queryRecordService.insert(queryRecord);

        return Result.success(sqlVo);
    }
}