package com.wgcloud.controller;

import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import com.github.pagehelper.PageInfo;
import com.wgcloud.config.CommonConfig;
import com.wgcloud.entity.DbInfo;
import com.wgcloud.entity.DbTable;
import com.wgcloud.entity.DbTableCount;
import com.wgcloud.service.*;
import com.wgcloud.util.*;
import com.wgcloud.util.jdbc.RDSConnection;
import com.wgcloud.util.staticvar.StaticKeys;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.mvc.support.RedirectAttributes;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @version v3.3
 * @ClassName:DbTableController.java
 * @author: http://www.wgstart.com
 * @date: 2021年1月16日
 * @Description: 数据表管理
 * @Copyright: 2019-2021 wgcloud. All rights reserved.
 */
@Controller
@RequestMapping("/dbTable")
public class DbTableController {


    private static final Logger logger = LoggerFactory.getLogger(DbTableController.class);

    @Resource
    private DbInfoService dbInfoService;
    @Resource
    private DbTableService dbTableService;
    @Resource
    private DbTableCountService dbTableCountService;
    @Resource
    private LogInfoService logInfoService;
    @Autowired
    private TokenUtils tokenUtils;
    @Resource
    private ExcelExportService excelExportService;
    @Resource
    private DashboardService dashboardService;
    @Autowired
    CommonConfig commonConfig;


    /**
     * agent查询监控数据表列表
     *
     * @param paramBean
     * @return
     */
    @ResponseBody
    @RequestMapping(value = "agentList")
    public String agentList(@RequestBody String paramBean) {
        JSONObject agentJsonObject = (JSONObject) JSONUtil.parse(paramBean);
        if (!tokenUtils.checkAgentToken(agentJsonObject)) {
            logger.error(StaticKeys.TOKEN_ERROR);
            return ResDataUtils.resetErrorJson(StaticKeys.TOKEN_ERROR);
        }
        Map<String, Object> params = new HashMap<String, Object>();
        try {
            params.put("active", StaticKeys.ON_STATE);
            if (null != agentJsonObject.get("dbInfoIds") && !StringUtils.isEmpty(agentJsonObject.get("dbInfoIds").toString())) {
                params.put("dbInfoIds", agentJsonObject.get("dbInfoIds").toString().split(","));
            }
            List<DbTable> dbTableList = dbTableService.selectAllByParams(params);
            List<DbTable> dbTableListResult = new ArrayList<>();
            //sql信息加密处理 begin
            for (DbTable dbTable : dbTableList) {
                dbTable.setWhereVal(DESUtil.encryption(dbTable.getWhereVal()));
                String sqlinkey = FormatUtil.haveSqlDanger(dbTable.getWhereVal(), commonConfig.getSqlInKeys());
                if (!StringUtils.isEmpty(sqlinkey)) {
                    logger.error("统计SQL语句含有sql敏感字符" + sqlinkey);
                    continue;
                }
                dbTableListResult.add(dbTable);
            }
            //sql信息加密处理 end
            return ResDataUtils.resetSuccessJson(dbTableListResult);
        } catch (Exception e) {
            logger.error("agent获取监控数据表信息错误", e);
            logInfoService.save("agent获取监控数据表信息错误", e.toString(), StaticKeys.LOG_XTCZ);
            return ResDataUtils.resetErrorJson(e.toString());
        }
    }

    /**
     * 根据条件查询列表
     *
     * @param model
     * @param request
     * @return
     */
    @RequestMapping(value = "list")
    public String dbTableList(DbTable DbTable, Model model, HttpServletRequest request) {
        Map<String, Object> params = new HashMap<String, Object>();
        try {

            StringBuffer url = new StringBuffer();
            String dbInfoId = null;
            if (!StringUtils.isEmpty(DbTable.getDbInfoId())) {
                dbInfoId = DbTable.getDbInfoId();
                params.put("dbInfoId", dbInfoId.trim());
                url.append("&dbInfoId=").append(dbInfoId);
            }
            if (!StringUtils.isEmpty(DbTable.getAccount())) {
                params.put("account", DbTable.getAccount());
                url.append("&account=").append(DbTable.getAccount());
            }
            if (!StringUtils.isEmpty(DbTable.getRemark())) {
                params.put("remark", DbTable.getRemark());
                url.append("&remark=").append(DbTable.getRemark());
            }
            if (!StringUtils.isEmpty(DbTable.getOrderBy())) {
                params.put("orderBy", DbTable.getOrderBy());
                params.put("orderType", DbTable.getOrderType());
                url.append("&orderBy=").append(DbTable.getOrderBy());
                url.append("&orderType=").append(DbTable.getOrderType());
            }

            //校验是否需要添加过滤用户查询条件
            HostUtil.addAccountquery(request, params);


            PageInfo<DbTable> pageInfo = dbTableService.selectByParams(params, DbTable.getPage(), DbTable.getPageSize());
            PageUtil.initPageNumber(pageInfo, model);
            params.clear();

            //设置数据表的一些额外附加值 begin
            List<DbInfo> dbInfoList = dbInfoService.selectAllByParams(params);
            //设置数据库logo
            dbInfoService.dbAddLogo(dbInfoList);
            for (DbTable dbTable : pageInfo.getList()) {
                for (DbInfo dbInfo : dbInfoList) {
                    if (dbInfo.getId().equals(dbTable.getDbInfoId())) {
                        //暂时借用取值的列名，来存贮下数据源名称，显示用
                        dbTable.setTableName(dbInfo.getAliasName());
                        //设置数据源logo
                        dbTable.setImage(dbInfo.getImage());

                        //设置所属用户账号 begin
                        if (StaticKeys.TRUE_VAL.equals(commonConfig.getUserInfoManage())) {
                            dbTable.setAccount(dbInfo.getAccount());
                        }
                        //设置所属用户账号 begin
                    }
                }
            }
            //设置数据表的一些额外附加值 end

            //设置用户列表
            HostUtil.addAccountListModel(model);

            model.addAttribute("pageUrl", "/dbTable/list?1=1" + url.toString());
            model.addAttribute("page", pageInfo);
            model.addAttribute("dbTable", DbTable);
            model.addAttribute("dbInfoList", dbInfoList);
        } catch (Exception e) {
            logger.error("查询数据表信息错误", e);
            logInfoService.save("查询数据表信息错误", e.toString(), StaticKeys.LOG_XTCZ);

        }
        return "mysql/tableList";
    }


    /**
     * 保存数据源表信息
     *
     * @param DbTable
     * @param model
     * @param request
     * @return
     */
    @RequestMapping(value = "save")
    public String saveDbTable(DbTable DbTable, Model model, HttpServletRequest request) {
        try {

            //检测有无sql敏感字 begin
            String sqlinkey = FormatUtil.haveSqlDanger(DbTable.getWhereVal(), commonConfig.getSqlInKeys());
            if (!StringUtils.isEmpty(sqlinkey)) {
                model.addAttribute("dbTable", DbTable);
                List<DbInfo> dbInfoList = dbInfoService.selectAllByParams(new HashMap<>());
                model.addAttribute("dbInfoList", dbInfoList);
                model.addAttribute("sqlInKeys", commonConfig.getSqlInKeys());
                model.addAttribute("msg", "统计SQL语句含有sql敏感字符" + sqlinkey + "，请检查");
                return "mysql/addTable";
            }
            //检测有无sql敏感字 end

            if (StringUtils.isEmpty(DbTable.getId())) {
                dbTableService.save(DbTable);
                dbTableService.saveLog(request, StaticKeys.LOG_ADD, DbTable);
            } else {
                dbTableService.updateById(DbTable);
                dbTableService.saveLog(request, StaticKeys.LOG_UPDATE, DbTable);
            }
        } catch (Exception e) {
            logger.error("保存数据表错误", e);
            logInfoService.save("保存数据表错误", e.toString(), StaticKeys.LOG_XTCZ);
        }
        return "redirect:/dbTable/list";
    }


    /**
     * 查看数据源表信息
     *
     * @param DbTable
     * @param model
     * @param request
     * @return
     */
    @RequestMapping(value = "edit")
    public String editDbTable(DbTable DbTable, Model model, HttpServletRequest request) {
        try {
            String id = request.getParameter("id");
            DbTable dbTableInfo = new DbTable();
            if (!StringUtils.isEmpty(id)) {
                dbTableInfo = dbTableService.selectById(id);
            }

            //校验是否需要添加过滤用户查询条件
            Map<String, Object> params = new HashMap<String, Object>();
            HostUtil.addAccountquery(request, params);

            List<DbInfo> dbInfoList = dbInfoService.selectAllByParams(params);

            // redis和mongodb不支持数据表 begin
            List<DbInfo> dbInfoListResult = new ArrayList<>();
            for (DbInfo dbInfo : dbInfoList) {
                if (RDSConnection.KEY_REDIS.equals(dbInfo.getDbType()) || RDSConnection.KEY_MONGODB.equals(dbInfo.getDbType())) {
                    continue;
                }
                dbInfoListResult.add(dbInfo);
            }
            // redis和mongodb不支持数据表 end

            model.addAttribute("dbInfoList", dbInfoListResult);
            model.addAttribute("dbTable", dbTableInfo);
            model.addAttribute("sqlInKeys", commonConfig.getSqlInKeys());
        } catch (Exception e) {
            logger.error("查看数据表错误", e);
            logInfoService.save("查看数据表错误", e.toString(), StaticKeys.LOG_XTCZ);
        }
        return "mysql/addTable";
    }

    /**
     * 查看数据源表图表统计信息
     *
     * @param DbTable
     * @param model
     * @param request
     * @return
     */
    @RequestMapping(value = "viewChart")
    public String viewChartDbTable(DbTable DbTable, Model model, HttpServletRequest request) {
        try {
            String id = request.getParameter("id");
            String startTime = request.getParameter(StaticKeys.SEARCH_START_TIME);
            String endTime = request.getParameter(StaticKeys.SEARCH_END_TIME);
            String am = request.getParameter("am");
            if (!StringUtils.isEmpty(id)) {
                DbTable dbTableInfo = dbTableService.selectById(id);
                Map<String, Object> params = new HashMap<String, Object>();
                dashboardService.setDateParam(am, startTime, endTime, params, model);
                model.addAttribute("amList", dashboardService.getAmList());
                params.put("dbTableId", id);
                List<DbTableCount> dbTableCounts = dbTableCountService.selectAllByParams(params);
                model.addAttribute("dbTableCounts", JSONUtil.parseArray(dbTableCounts));
                // 设置图表的副标题，数据量的最高、平均、最低值
                dbTableCountService.setSubtitle(model, dbTableCounts);
                model.addAttribute("dbTable", dbTableInfo);
            }
        } catch (Exception e) {
            logger.error("查看数据表图表统计错误", e);
            logInfoService.save("查看数据表图表统计错误", e.toString(), StaticKeys.LOG_XTCZ);
        }
        return "mysql/tableView";
    }

    /**
     * 查看该数据表统计图导出excel
     *
     * @param model
     * @param request
     * @return
     */
    @RequestMapping(value = "chartExcel")
    public void chartExcel(Model model, HttpServletRequest request, HttpServletResponse response) {
        String errorMsg = "数据表统计图导出excel错误";
        String id = request.getParameter("id");
        String startTime = request.getParameter(StaticKeys.SEARCH_START_TIME);
        String endTime = request.getParameter(StaticKeys.SEARCH_END_TIME);
        String am = request.getParameter("am");
        try {
            if (StringUtils.isEmpty(id)) {
                response.setContentType("text/html;charset=UTF-8");
                response.getOutputStream().write(StaticKeys.EXCEL_PARAM_ERROR.getBytes());
                return;
            }
            Map<String, Object> params = new HashMap<String, Object>();
            dashboardService.setDateParam(am, startTime, endTime, params, model);
            params.put("dbTableId", id);
            excelExportService.exportDbTableExcel(params, response);
        } catch (Exception e) {
            logger.error(errorMsg, e);
            logInfoService.save(errorMsg, e.toString(), StaticKeys.LOG_XTCZ);
        }
    }


    /**
     * 删除数据源表
     *
     * @param model
     * @param request
     * @param redirectAttributes
     * @return
     */
    @RequestMapping(value = "del")
    public String delete(Model model, HttpServletRequest request, RedirectAttributes redirectAttributes) {
        String errorMsg = "删除数据表信息错误";
        try {
            if (!StringUtils.isEmpty(request.getParameter("id"))) {
                String[] ids = request.getParameter("id").split(",");
                for (String id : ids) {
                    DbTable dbTable = dbTableService.selectById(id);
                    dbTableService.saveLog(request, StaticKeys.LOG_DEL, dbTable);
                }
                dbTableService.deleteById(ids);
            }
        } catch (Exception e) {
            logger.error(errorMsg, e);
            logInfoService.save(errorMsg, e.toString(), StaticKeys.LOG_XTCZ);
        }

        return "redirect:/dbTable/list";
    }

}