package com.salpa.subject.service.impl;

import com.alibaba.fastjson2.JSON;
import com.alibaba.fastjson2.JSONObject;
import com.github.pagehelper.PageHelper;
import com.salpa.common.constant.UserConstants;
import com.salpa.subject.domain.*;
import com.salpa.subject.domain.vo.MonitorDataSourceVo;
import com.salpa.subject.domain.vo.OperationLogVo;
import com.salpa.subject.domain.vo.ProjectSyncRule;
import com.salpa.subject.mapper.*;
import com.salpa.subject.service.ProjectService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;
import java.util.Properties;

/**
 * 项目管理实现类
 *
 * @author zhuff
 */
@Service
public class ProjectServiceImpl implements ProjectService {

    @Autowired
    private ProjectMapper projectMapper;

    @Autowired
    private DatabaseMapper databaseMapper;

    @Autowired
    private DataSourcePropertyMapper dataSourcePropertyMapper;

    @Autowired
    private ProjectSyncRuleMapper projectSyncRuleMapper;

    @Autowired
    private DatabaseTypeMapper databaseTypeMapper;

    @Autowired
    private OperationLogMapper operationLogMapper;

    /**
     * 获取数据源管理列表
     * @author zhuff
     * @param monitorDataSourceVo
     * @return
     */
    @Override
    public List<Map<String , Object>> getList(MonitorDataSourceVo monitorDataSourceVo) {
        PageHelper.startPage(monitorDataSourceVo.getPageNum(),monitorDataSourceVo.getPageSize());
        List<Map<String , Object>> list = projectMapper.getList(monitorDataSourceVo);
        for (Map<String, Object> map : list) {
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String createAt1 = dateFormat.format(map.get("createAt"));
            map.put("createAt",createAt1);
            if (map.get("isAutoSync").equals(false)) {
                map.put("isAutoSync",0);
            } else {
                map.put("isAutoSync",1);
            }
        }
        return list;
    }

    /**
     * 验证项目是否已经存在
     * @param projectName
     * @return
     */
    @Override
    public String checkProjectNameUnique(String projectName) {
        int count = projectMapper.checkProjectNameUnique(projectName);
        if (count > 0)
        {
            return UserConstants.NOT_UNIQUE;
        }
        return UserConstants.UNIQUE;
    }

    /**
     * 新增项目及数据库相关信息
     * @param monitorProject
     * @return
     */
    @Override
    public int addProject(MonitorProject monitorProject) {
        /*新增项目*/
        int resultInt = projectMapper.addProject(monitorProject);
        /*新增数据库*/
        monitorProject.getMonitorDataSource().setProjectId(monitorProject.getId());
        databaseMapper.addDatabaseSource(monitorProject.getMonitorDataSource());
        /*新增数据库参数*/
        Map[] properties = monitorProject.getMonitorDataSource().getProperties();
        if (properties != null){
            for (int i = 0; i < properties.length; i++) {
                MonitorDataSourceProperty monitorDataSourceProperty = new MonitorDataSourceProperty();
                monitorDataSourceProperty.setDataSourceId(monitorProject.getMonitorDataSource().getId());
                monitorDataSourceProperty.setDataSourceKey(properties[i].get("dataSourceKey").toString());
                monitorDataSourceProperty.setDataSourceValue(properties[i].get("dataSourceValue").toString());
                dataSourcePropertyMapper.addDataSourceProperty(monitorDataSourceProperty);
            }
        }
        /*新增项目高级配置*/
        monitorProject.getProjectSyncRule().setProjectId(monitorProject.getId());
        String ignoreTableNameRegex = "";
        if (monitorProject.getProjectSyncRule().getIgnoreTableNameRegexArray()!=null){
            String[] string = monitorProject.getProjectSyncRule().getIgnoreTableNameRegexArray();
            for (int i = 0; i < string.length; i++) {
                if (ignoreTableNameRegex == "") {
                    ignoreTableNameRegex += string[i];
                } else {
                    ignoreTableNameRegex += "," + string[i];
                }
            }
        }
        String ignoreColumnNameRegex = "";
        if (monitorProject.getProjectSyncRule().getIgnoreColumnNameRegexArray()!=null){
            String[] string1 = monitorProject.getProjectSyncRule().getIgnoreColumnNameRegexArray();
            for (int i = 0; i < string1.length; i++) {
                if (ignoreColumnNameRegex == "") {
                    ignoreColumnNameRegex += string1[i];
                } else {
                    ignoreColumnNameRegex += "," + string1[i];
                }
            }
        }
        projectSyncRuleMapper.addProjectSyncRule(monitorProject.getProjectSyncRule(),JSONObject.toJSONString(ignoreTableNameRegex),JSONObject.toJSONString(ignoreColumnNameRegex));
        return resultInt;
    }

    /**
     * 根据id获取项目
     * @param id
     * @return
     */
    @Override
    public MonitorProject getProjectById(Integer id) {
        MonitorProject monitorProject = projectMapper.selectById(id);
        MonitorDataSource monitorDataSource = databaseMapper.selectDataSourceById(id);
        List<Map<String ,Object>> list = dataSourcePropertyMapper.selectPropertyBySourceId(monitorDataSource.getId());
        if (list != null){
            Map[] properties = new Map[list.size()];
            int i = 0;
            for (Map<String, Object> map : list) {
                properties[i] = map;
                i++;
            }
            monitorDataSource.setProperties(properties);
        }
        monitorProject.setMonitorDataSource(monitorDataSource);
        MonitorProjectSyncRule monitorProjectSyncRule = projectSyncRuleMapper.selectByProjectId(id);
        if (monitorProjectSyncRule != null) {
            ProjectSyncRule projectSyncRule = new ProjectSyncRule();
            String[] split = monitorProjectSyncRule.getIgnoreTableNameRegexArray().substring(1,monitorProjectSyncRule.getIgnoreTableNameRegexArray().length()-1).split(",");
            String[] split1 = monitorProjectSyncRule.getIgnoreColumnNameRegexArray().substring(1,monitorProjectSyncRule.getIgnoreColumnNameRegexArray().length()-1).split(",");
            if (split.length>0 && !split[0].equals("")){
                projectSyncRule.setIgnoreTableNameRegexArray(split);
            }
            if (split1.length>0 && !split1[0].equals("")){
                projectSyncRule.setIgnoreColumnNameRegexArray(split1);
            }
            projectSyncRule.setAutoSync(monitorProjectSyncRule.isAutoSync());
            projectSyncRule.setAutoSyncCron(monitorProjectSyncRule.getAutoSyncCron());
            monitorProject.setProjectSyncRule(projectSyncRule);
        }
        return monitorProject;
    }

    /**
     * 验证数据库是否已经存在
     * @param databaseName
     * @return
     */
    @Override
    public String checkDatabaseNameUnique(String databaseName) {
        int count = projectMapper.checkDatabaseNameUnique(databaseName);
        if (count > 0)
        {
            return UserConstants.NOT_UNIQUE;
        }
        return UserConstants.UNIQUE;
    }

    /**
     * 获取项目
     * @param id
     * @return
     */
    @Override
    public MonitorProject getById(Integer id) {
        return projectMapper.selectById(id);
    }

    /**
     * 获取数据库
     * @param id
     * @return
     */
    @Override
    public MonitorDataSource getDataSource(Integer id) {
        return databaseMapper.selectDataSourceById(id);
    }

    /**
     * 修改项目及数据库相关信息
     * @param monitorProject
     * @return
     */
    @Override
    public int updateProject(MonitorProject monitorProject) {
        /*修改数据库*/
        databaseMapper.updateDateSource(monitorProject.getMonitorDataSource());
        /*修改数据库参数*/
        dataSourcePropertyMapper.deleteDataSourceProperty(monitorProject.getMonitorDataSource().getId());
        Map[] properties = monitorProject.getMonitorDataSource().getProperties();
        if (properties != null){
            for (int i = 0; i < properties.length; i++) {
                MonitorDataSourceProperty monitorDataSourceProperty = new MonitorDataSourceProperty();
                monitorDataSourceProperty.setDataSourceId(monitorProject.getMonitorDataSource().getId());
                monitorDataSourceProperty.setDataSourceKey(properties[i].get("dataSourceKey").toString());
                monitorDataSourceProperty.setDataSourceValue(properties[i].get("dataSourceValue").toString());
                dataSourcePropertyMapper.addDataSourceProperty(monitorDataSourceProperty);
            }
        }
        /*修改项目高级配置*/
        projectSyncRuleMapper.deletProjectSyncRule(monitorProject.getId());
        monitorProject.getProjectSyncRule().setProjectId(monitorProject.getId());
        String ignoreTableNameRegex = "";
        if (monitorProject.getProjectSyncRule().getIgnoreTableNameRegexArray() !=null){
            String[] string = monitorProject.getProjectSyncRule().getIgnoreTableNameRegexArray();
            for (int i = 0; i < string.length; i++) {
                if (ignoreTableNameRegex == "") {
                    ignoreTableNameRegex += string[i];
                } else {
                    ignoreTableNameRegex += "," + string[i];
                }
            }
        }
        String ignoreColumnNameRegex = "";
        if (monitorProject.getProjectSyncRule().getIgnoreColumnNameRegexArray() != null) {
            String[] string1 = monitorProject.getProjectSyncRule().getIgnoreColumnNameRegexArray();
            for (int i = 0; i < string1.length; i++) {
                if (ignoreColumnNameRegex == "") {
                    ignoreColumnNameRegex += string1[i];
                } else {
                    ignoreColumnNameRegex += "," + string1[i];
                }
            }
        }
        projectSyncRuleMapper.addProjectSyncRule(monitorProject.getProjectSyncRule(),JSONObject.toJSONString(ignoreTableNameRegex),JSONObject.toJSONString(ignoreColumnNameRegex));
        /*修改项目*/
        return projectMapper.updateProject(monitorProject);
    }

    /**
     * 删除项目
     * @param id
     * @return
     */
    @Override
    public int deleteProject(Integer id) {
        /*删除项目高级配置*/
        projectSyncRuleMapper.deletProjectSyncRule(id);
        /*删除数据库参数*/
        dataSourcePropertyMapper.deleteDataSourceProperty(databaseMapper.selectDataSourceById(id).getId());
        /*删除数据库*/
        databaseMapper.deleteDataSource(id);
        /*删除项目*/
        return projectMapper.deleteProject(id);
    }

    /**
     * 测试连接
     * @param monitorDataSource
     */
    @Override
    public int testConnection(MonitorDataSource monitorDataSource) {
        MonitorDatabaseType monitorDatabaseType = databaseTypeMapper.getTypeByName(monitorDataSource.getDatabaseType());
        /*这是要连接的数据库加载器*/
        String driverName = monitorDatabaseType.getJdbcDriverClassName();
        /*这是要连接的端口号以及数据库名称*/
        String urlPattern = monitorDatabaseType.getUrlPattern();
        String dbURL = urlPattern.replace("{{jdbc.protocol}}", monitorDatabaseType.getJdbcProtocol())
                .replace("{{db.url}}", monitorDataSource.getUrl())
                .replace("{{db.name}}", monitorDataSource.getDatabaseName())
                .replace("{{db.schema}}", monitorDataSource.getSchemaName());
        Properties info = new Properties();
        /*连接参数*/
        if (monitorDataSource.getProperties() != null) {
            for (int i = 0; i < monitorDataSource.getProperties().length; i++) {
                info.put(monitorDataSource.getProperties()[i].get("dataSourceKey"),monitorDataSource.getProperties()[i].get("dataSourceValue"));
            }
        }
        /*用户名*/
        String userName = monitorDataSource.getUsername();
        /*用户密码*/
        String userpwd = monitorDataSource.getPassword();
        info.put("user" ,userName);
        info.put("password" ,userpwd);
        int i = 0;
        try {
            Class.forName(driverName);
        }catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
            i = 1;
        }
        try {
            DriverManager.getConnection(dbURL,info);
            i = 3;
        }catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
            i = 2;
        }
        return i;
    }

    /**
     * 获取日志列表
     * @param operationLogVo
     * @return
     */
    @Override
    public List<MonitorOperationLog> getLogList(OperationLogVo operationLogVo) {
        PageHelper.startPage(operationLogVo.getPageNum(),operationLogVo.getPageSize());
        List<MonitorOperationLog> list = operationLogMapper.getList(operationLogVo.getProjectId());
        for (MonitorOperationLog monitorOperationLog : list) {
            Map map = (Map) JSON.parse(monitorOperationLog.getOperationResponse());
            monitorOperationLog.setOperationResponse(map.get("msg").toString());
        }
        return list;
    }

    /**
     * 连接数据源
     * @param dataSource
     * @param properties
     * @return
     */
    @Override
    public Connection create(MonitorDataSource dataSource, List<Map<String, Object>> properties) {
        MonitorDatabaseType monitorDatabaseType = databaseTypeMapper.getTypeByName(dataSource.getDatabaseType());
        /*这是要连接的数据库加载器*/
        String driverName = monitorDatabaseType.getJdbcDriverClassName();
        /*这是要连接的端口号以及数据库名称*/
        String urlPattern = monitorDatabaseType.getUrlPattern();
        String dbURL = urlPattern.replace("{{jdbc.protocol}}", monitorDatabaseType.getJdbcProtocol())
                .replace("{{db.url}}", dataSource.getUrl())
                .replace("{{db.name}}", dataSource.getDatabaseName())
                .replace("{{db.schema}}", dataSource.getSchemaName());
        Properties info = new Properties();
        /*连接参数*/
        if (properties != null) {
            for (Map<String, Object> property : properties) {
                info.put(property.get("dataSourceKey"),property.get("dataSourceValue"));
            }
        }
        /*用户名*/
        String userName = dataSource.getUsername();
        /*用户密码*/
        String userpwd = dataSource.getPassword();
        info.put("user" ,userName);
        info.put("password" ,userpwd);
        try {
            Class.forName(driverName);
            System.out.println("驱动加载成功");
        }catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
            System.out.println("驱动加载失败");
        }
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(dbURL, info);
            System.out.println("数据库连接成功");
        }catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
            System.out.println("数据库连接失败");
        }
        return connection;
    }

    public void addDataSource(MonitorDataSource monitorDataSource){
        databaseMapper.addDatabaseSource(monitorDataSource);
    }
}
