package org.matrix.autotest.controller;

import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.matrix.actuators.datasource.DataSourceDTO;
import org.matrix.actuators.datasource.IDataSourceService;
import org.matrix.actuators.sql.SqlExpActuator;
import org.matrix.autotest.entity.GrammarTable;
import org.matrix.autotest.entity.runSqlQuery;
import org.matrix.database.service.IConnectService;
import org.matrix.exception.GlobalException;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.http.ResponseEntity;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.*;

import java.util.*;

/**
 * SqlController.
 *
 * @author Matrix <xhyrzldf@gmail.com>
 * @since 2022/1/24 at 8:57 PM
 * Suffering is the most powerful teacher of life.
 */
@SuppressWarnings("ALL")
@Slf4j
@RestController
@RequestMapping("/db")
public class SqlController {

    @Value("${spring.datasource.dynamic.datasource.master.driverClassName}")
    private String driver;

    @Value("${spring.datasource.dynamic.datasource.master.url}")
    private String url;

    @Value("${spring.datasource.dynamic.datasource.master.username}")
    private String username;

    @Value("${spring.datasource.dynamic.datasource.master.password}")
    private String password;

    private final JdbcTemplate jdbcTemplate;
    private final IDataSourceService dataSourceService;
    private final SqlExpActuator sqlActuator;
    private final IConnectService connectService;
    @SuppressWarnings("FieldCanBeLocal")
    private final String GET_ALL_TABLES = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='key_stone';";

    public SqlController(JdbcTemplate jdbcTemplate, IDataSourceService dataSourceService, SqlExpActuator sqlActuator, IConnectService connectService) {
        this.jdbcTemplate = jdbcTemplate;
        this.dataSourceService = dataSourceService;
        this.sqlActuator = sqlActuator;
        this.connectService = connectService;
    }

    /**
     * 递归地运行一段SQL(可以递归解析环境变量，动态变量)
     *
     * @param sqlQuery 要查询的SQL
     * @return SQL结果 以MAP的JSON形式传，但是字段数量不固定
     */
    @PostMapping("/runSql")
    @ApiOperation("递归地运行一段SQL")
    public ResponseEntity<List<Map<String, Object>>> runSql(@RequestBody runSqlQuery sqlQuery) {
        List<Map<String, Object>> results = sqlActuator.runSqlRec(sqlQuery.getSql(), sqlQuery.getProjectId(), sqlQuery.getEnvId(), sqlQuery.getConnectId());
        return ResponseEntity.ok(results);
    }

    /**
     * 运行一段SQL,要确定可以连接的数据源(用于连接的数据源)环境(用于寻找环境变量)项目id(用于确定需要递归解析的动态变量)
     *
     * @param sql 要查询的SQL语句
     * @return SQL结果 以MAP的JSON形式传，但是字段数量不固定
     */
    @GetMapping("/sql")
    @ApiOperation("运行一段SQL")
    public ResponseEntity<List<Map<String, Object>>> runSql(@RequestParam String sql) {
        // todo 这里要增加切换数据源的部分
        dataSourceService.switchMainDataSource();
        return ResponseEntity.ok(jdbcTemplate.queryForList(sql));
    }

    /**
     * 获得默认主库的所有表的名称
     *
     * @return 默认主库的所有表的名称
     */
    @GetMapping("/tableNames")
    @ApiOperation("获得默认主库的所有表的名称")
    public ResponseEntity<List<Map<String, Object>>> getTableNames() {
        return ResponseEntity.ok(jdbcTemplate.queryForList(GET_ALL_TABLES));
    }

    @GetMapping("/hints")
    @ApiOperation("获得数据库的语法提示")
    public ResponseEntity<List<GrammarTable>> getDbHints(@RequestParam Long connectId) {
        String tableSql = "SELECT a.table_name tableName,a.table_comment tableDes,b.COLUMN_NAME fieldName,b.column_comment fieldDes,b.column_type fieldType FROM information_schema. TABLES a LEFT JOIN information_schema. COLUMNS b ON a.table_name = b.TABLE_NAME WHERE a.table_schema = '%s' ORDER BY a.table_name";
        // 切换数据源，执行SQL
        try {
            DataSourceDTO dataSourceDTO = Optional.ofNullable(connectService.getById(connectId))
                    .orElseThrow(() -> new GlobalException(String.format("没有找到id = %d 的连接池connect对象", connectId)))
                    .toDataSourceDTO();
            Set<String> dataSources = dataSourceService.switchDataSource(dataSourceDTO);
            log.info("当前使用的的数据源 {}", dataSourceService.peek());
        } catch (Exception e) {
            e.printStackTrace();
            throw new GlobalException("数据库连接失败或表不存在");
        }
        String databaseName = jdbcTemplate.queryForList("select database();").get(0).get("database()").toString();
        tableSql = String.format(tableSql, databaseName);
        List<GrammarTable> grammarTables = new GrammarTable().trans2Table(jdbcTemplate.queryForList(tableSql));
        dataSourceService.switchMainDataSource();
        return ResponseEntity.ok(grammarTables);
    }

    @GetMapping("/actuator")
    @ApiOperation("获得用例表kt_test_case的语法提示")
    public ResponseEntity<List<GrammarTable>> getDBActuator() {
        String tableSql = "SELECT a.table_name tableName,a.table_comment tableDes,b.COLUMN_NAME fieldName,b.column_comment fieldDes,b.column_type fieldType FROM information_schema. TABLES a LEFT JOIN information_schema. COLUMNS b ON a.table_name = b.TABLE_NAME WHERE a.table_name = 'kt_test_case' AND a.table_schema = 'key_stone' ORDER BY a.table_name";
        DataSourceDTO dataSourceDTO = new DataSourceDTO();
        dataSourceDTO.setPoolName("key_stone");
        dataSourceDTO.setUsername(username);
        dataSourceDTO.setPassword(password);
        dataSourceDTO.setUrl(url);
        dataSourceDTO.setDriverClassName(driver);
        Set<String> dataSources = dataSourceService.switchDataSource(dataSourceDTO);
        log.info("当前使用的的数据源 {}", dataSourceService.peek());
        List<GrammarTable> grammarTables = new GrammarTable().trans2Table(jdbcTemplate.queryForList(tableSql));
        dataSourceService.switchMainDataSource();
        return ResponseEntity.ok(grammarTables);
    }
}
