package com.salpa.online.service.impl;

import com.salpa.common.core.domain.AjaxResult;
import com.salpa.online.service.OnlineService;
import com.salpa.subject.domain.MonitorDataSource;
import com.salpa.subject.mapper.DataSourcePropertyMapper;
import com.salpa.subject.mapper.DatabaseMapper;
import com.salpa.subject.service.ProjectService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.*;

@Service
public class OnlineServiceImpl implements OnlineService {

    @Autowired
    private DatabaseMapper databaseMapper;

    @Autowired
    private ProjectService projectService;

    @Autowired
    private DataSourcePropertyMapper dataSourcePropertyMapper;

    /**
     * 获取数据源
     * @return
     */
    @Override
    public List<MonitorDataSource> getDataSource() {
        List<MonitorDataSource> list = databaseMapper.selectDataSourceList();
        return list;
    }

    /**
     * 连接数据库
     * @param databaseName
     * @return
     */
    @Override
    public AjaxResult connectDataSource(String databaseName) {
        MonitorDataSource monitorDataSource = databaseMapper.selectByDatabaseName(databaseName);
        List<Map<String,Object>> properties = dataSourcePropertyMapper.selectPropertyBySourceId(monitorDataSource.getId());
        Connection connection = projectService.create(monitorDataSource, properties);
        List<String> tables = new ArrayList<>();
        if (connection == null) {
            return AjaxResult.error("连接失败");
        } else {
            try {
                String[] tableTypes = {"TABLE"};
                ResultSet tablesResult = connection.getMetaData()
                        .getTables(databaseName, monitorDataSource.getSchemaName(), null, tableTypes);
                while (tablesResult.next()) {
                    tables.add(tablesResult.getString("TABLE_NAME"));
                }
                tablesResult.close();//关闭tablesResult对象
                connection.close();//关闭Connection对象
            } catch (Exception throwAbles) {
                throwAbles.printStackTrace();
                return AjaxResult.error(throwAbles.getMessage());
            }
        }
        return AjaxResult.success(tables);
    }

    /**
     * 执行sql语句
     * @param databaseName
     * @param script
     * @return
     */
    @Override
    public AjaxResult testExecute(String databaseName, String script) {
        Map<String, Map<String,Object>> columns = new LinkedHashMap();
        MonitorDataSource monitorDataSource = databaseMapper.selectByDatabaseName(databaseName);
        List<Map<String,Object>> properties = dataSourcePropertyMapper.selectPropertyBySourceId(monitorDataSource.getId());
        Map<String,Object> reslutMap = new LinkedHashMap<>();
        List<Map<String, Object>> rows = new ArrayList<>();
        try {
            Connection connection = projectService.create(monitorDataSource, properties);
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(script);
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            for (int i = 0; i < columnCount; i++) {
                Map<String,Object> map = new HashMap<>();
                map.put("name",metaData.getColumnName(i+1));
                map.put("type",metaData.getColumnTypeName(i+1));
                columns.put(metaData.getColumnName(i+1),map);
            }
            while(resultSet.next()){
                Map<String, Object> row = new LinkedHashMap<>();
                for (int i = 0; i < columnCount; i++) {
                    Object object = resultSet.getObject(metaData.getColumnLabel(i+1));
                    if (metaData.getColumnTypeName(i+1).equals("DATETIME") || metaData.getColumnTypeName(i+1).equals("TIMESTAMP") || metaData.getColumnTypeName(i+1).equals("DATE")) {
                        row.put(metaData.getColumnName(i+1),resultSet.getString(metaData.getColumnLabel(i+1)));
                    } else {
                        row.put(metaData.getColumnName(i+1),object);
                    }
                }
                rows.add(row);
            }
            resultSet.close();//关闭ResultSet对象
            statement.close();//关闭Statement对象
            connection.close();//关闭Connection对象
            reslutMap.put("columns",columns);
            reslutMap.put("rows",rows);
        } catch (SQLException throwAbles) {
            throwAbles.printStackTrace();
            return AjaxResult.error(throwAbles.getMessage());
        }
        return AjaxResult.success(reslutMap);
    }
}
