package com.artfess.dataShare.util;

import com.artfess.base.util.StringUtil;
import com.artfess.base.util.UniqueIdUtil;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.HashMap;
import java.util.Map;

/**
 * mysql数据库操作工具类
 */
public class MySQLUtils {
    public static void initChangeSource(JdbcTemplate template,String user, String pwd,String dbName){
        createDataUser(template,user,pwd);
        createDataBase(template,dbName);
        createGrant(template,"select,insert,update,create,trigger",dbName,user);
    }

    //创建用户
    public static void createDataUser(JdbcTemplate template,String user, String pwd){
        String sql="create user "+user+"@'%' IDENTIFIED WITH mysql_native_password BY '"+pwd+"'";
        template.execute(sql);
    }

    //删除用户
    public static void dropDataUser(JdbcTemplate template,String user){
        String sql = "drop user " + user + "@'%'";
        template.execute(sql);
    }

    public static void updateDateUser(JdbcTemplate template,String user,String pwd){
        String sql = "set password for '"+user+"'@'%' = '"+pwd+"'";
        template.execute(sql);
    }

    //创建数据库
    public static void createDataBase(JdbcTemplate template,String dbName){
        String sql="create database "+dbName;
        template.execute(sql);
    }

    //用户数据库授权 默认全部权限  grant=select,insert,update
    public static void createGrant(JdbcTemplate template,String grant,String dbName,String user){
        if(StringUtil.isEmpty(grant)){
            grant="all";
        }
        String sql="grant "+grant+" on "+dbName+".* to "+user;
        template.execute(sql);
    }

    public static void createGrant(JdbcTemplate template,String grant,String dbName,String tableName,String user){
        if(StringUtil.isEmpty(grant)){
            grant="all";
        }
        String sql="grant "+grant+" on "+dbName+"."+tableName+" to "+user;
        template.execute(sql);
    }

    public static void revokeGrant(JdbcTemplate template,String grant,String dbName,String tableName,String user){
        if(StringUtil.isEmpty(grant)){
            grant="all";
        }
        String sql="revoke "+grant+" on "+dbName+"."+tableName+" from "+user;
        template.execute(sql);
    }

    public static void removeBaseUser(JdbcTemplate template,String dbName,String user){
        String sql="drop database "+dbName;
        template.execute(sql);
        String sqlUser="drop user "+user;
        template.execute(sqlUser);
    }

    public static Map<String,String> createTiger(JdbcTemplate template, String tableName, String companyName, String sysName, String sysCode, String name, String code){
        Map<String,String> map=new HashMap<>();
        //String id= UniqueIdUtil.getSuid();
       /* String insertTigerSql="CREATE TRIGGER "+tableName+"_insert_tiger " + tableName + "\n"+
                "    AFTER INSERT  ON " + tableName + " FOR EACH ROW  \n" +
                "         BEGIN  \n" +
                "        insert into BIZ_CLUSTER_COLLECT_DATA(id_,COMPANY_NAME_,SYS_NAME_,SYS_CODE_,NAME_,CODE_,TABLE_NAME_,SRC_ID_,DATA_TYPE_,CREATE_TIME_) " +
                "           values(new.id_,'"+companyName+"','"+sysName+"','"+sysCode+"','"+name+"','"+code+"','"+tableName+"',new.id_,1,now());\n"+
                "        insert into BIZ_CLUSTER_TEMP_DATA(id_,COMPANY_NAME_,SYS_NAME_,SYS_CODE_,NAME_,CODE_,TABLE_NAME_,SRC_ID_,DATA_TYPE_,CREATE_TIME_) " +
                "           values(new.id_,'"+companyName+"','"+sysName+"','"+sysCode+"','"+name+"','"+code+"','"+tableName+"',new.id_,1,now());\n"+
                "        END";*/
        String insertTigerSql=initTriggerSql( tableName, companyName, sysName, sysCode, name, code,"INSERT","1");
        template.execute("drop trigger if exists "+tableName+"_INSERT_tiger");
        template.execute(insertTigerSql);
        map.put("insert",insertTigerSql);

        //String id= UniqueIdUtil.getSuid();
       /* String insertUpdateSql="CREATE TRIGGER "+tableName+"_update_tiger \n"+
                "    AFTER UPDATE  ON " + tableName + " FOR EACH ROW  \n" +
                "         BEGIN  \n" +
                "        insert into BIZ_CLUSTER_COLLECT_DATA(id_,COMPANY_NAME_,SYS_NAME_,SYS_CODE_,NAME_,CODE_,TABLE_NAME_,SRC_ID_,DATA_TYPE_,CREATE_TIME_) " +
                "           values(new.id_,'"+companyName+"','"+sysName+"','"+sysCode+"','"+name+"','"+code+"','"+tableName+"',new.id_,2,now());\n"+
                "        insert into BIZ_CLUSTER_TEMP_DATA(id_,COMPANY_NAME_,SYS_NAME_,SYS_CODE_,NAME_,CODE_,TABLE_NAME_,SRC_ID_,DATA_TYPE_,CREATE_TIME_) " +
                "           values(new.id_,'"+companyName+"','"+sysName+"','"+sysCode+"','"+name+"','"+code+"','"+tableName+"',new.id_,2,now());\n"+
                "        END";*/
        String insertUpdateSql=initTriggerSql( tableName, companyName, sysName, sysCode, name, code,"UPDATE","2");
        template.execute("drop trigger if exists "+tableName+"_UPDATE_tiger");
        template.execute(insertUpdateSql);
        map.put("update",insertUpdateSql);

        //String id= UniqueIdUtil.getSuid();
        /*String insertDelSql="CREATE TRIGGER "+tableName+"_del_tiger " + tableName + "\n"+
                "    AFTER DELETE  ON " + tableName + " FOR EACH ROW  \n" +
                "         BEGIN  \n" +
                "        insert into BIZ_CLUSTER_COLLECT_DATA(id_,COMPANY_NAME_,SYS_NAME_,SYS_CODE_,NAME_,CODE_,TABLE_NAME_,SRC_ID_,DATA_TYPE_,CREATE_TIME_) " +
                "           values(new.id_,'"+companyName+"','"+sysName+"','"+sysCode+"','"+name+"','"+code+"','"+tableName+"',new.id_,3,now());\n"+
                "        insert into BIZ_CLUSTER_TEMP_DATA(id_,COMPANY_NAME_,SYS_NAME_,SYS_CODE_,NAME_,CODE_,TABLE_NAME_,SRC_ID_,DATA_TYPE_,CREATE_TIME_) " +
                "           values(new.id_,'"+companyName+"','"+sysName+"','"+sysCode+"','"+name+"','"+code+"','"+tableName+"',new.id_,3,now());\n"+
                "        END";*/
        String insertDelSql=initTriggerSql( tableName, companyName, sysName, sysCode, name, code,"DELETE","3");
        template.execute("drop trigger if exists "+tableName+"_DELETE_tiger");
        template.execute(insertDelSql);
        map.put("delete",insertDelSql);
        return map;
    }

    public static String initTriggerSql(String tableName,String companyName,String sysName,String sysCode,String name,String code,String type,String typeIndex){
        String triggerSql="CREATE TRIGGER "+tableName+"_"+type+"_tiger \n"+
                "    AFTER "+type+"  ON " + tableName + " FOR EACH ROW  \n" +
                "         BEGIN  \n" +
                "        insert into BIZ_CLUSTER_COLLECT_DATA(id_,COMPANY_NAME_,SYS_NAME_,SYS_CODE_,NAME_,CODE_,TABLE_NAME_,SRC_ID_,DATA_TYPE_,CREATE_TIME_) " +
                "           values(uuid(),'"+companyName+"','"+sysName+"','"+sysCode+"','"+name+"','"+code+"','"+tableName+"',new.id_,"+typeIndex+",now());\n"+
                "        insert into BIZ_CLUSTER_TEMP_DATA(id_,COMPANY_NAME_,SYS_NAME_,SYS_CODE_,NAME_,CODE_,TABLE_NAME_,SRC_ID_,DATA_TYPE_,CREATE_TIME_) " +
                "           values(uuid(),'"+companyName+"','"+sysName+"','"+sysCode+"','"+name+"','"+code+"','"+tableName+"',new.id_,"+typeIndex+",now());\n"+
                "        END";
        if("DELETE".equals(type)){
            triggerSql="CREATE TRIGGER "+tableName+"_"+type+"_tiger \n"+
                    "    AFTER "+type+"  ON " + tableName + " FOR EACH ROW  \n" +
                    "         BEGIN  \n" +
                    "        insert into BIZ_CLUSTER_COLLECT_DATA(id_,COMPANY_NAME_,SYS_NAME_,SYS_CODE_,NAME_,CODE_,TABLE_NAME_,SRC_ID_,DATA_TYPE_,CREATE_TIME_) " +
                    "           values(uuid(),'"+companyName+"','"+sysName+"','"+sysCode+"','"+name+"','"+code+"','"+tableName+"',old.id_,"+typeIndex+",now());\n"+
                    "        insert into BIZ_CLUSTER_TEMP_DATA(id_,COMPANY_NAME_,SYS_NAME_,SYS_CODE_,NAME_,CODE_,TABLE_NAME_,SRC_ID_,DATA_TYPE_,CREATE_TIME_) " +
                    "           values(uuid(),'"+companyName+"','"+sysName+"','"+sysCode+"','"+name+"','"+code+"','"+tableName+"',old.id_,"+typeIndex+",now());\n"+
                    "        END";
        }
        return triggerSql;
    }

    public static void createCollectTable(JdbcTemplate template){
        //数据采集临时表（用于采集任务）
        //template.execute("drop table if exists BIZ_CLUSTER_TEMP_DATA");
        String tempTable="create table BIZ_CLUSTER_TEMP_DATA\n" +
                "(\n" +
                "   ID_                  VARCHAR(64) not null,\n" +
                "   COMPANY_NAME_        VARCHAR(120) not null default '' comment '单位名称',\n" +
                "   SYS_NAME_            VARCHAR(120) comment '系统名称',\n" +
                "   SYS_CODE_            VARCHAR(120) comment '系统编码',\n" +
                "   NAME_                varchar(64) default null comment '数据资源项名称',\n" +
                "   CODE_                varchar(64) comment '数据资源项编码',\n" +
                "   TABLE_NAME_          VARCHAR(64) comment '数据表名',\n" +
                "   SRC_ID_              VARCHAR(64) default '' comment '数据表ID',\n" +
                "   DATA_TYPE_           int default NULL comment '数据操作标记（1：新增，2：修改，3：删除）',\n" +
                "   CREATE_TIME_         datetime default null comment '数据操作时间',\n" +
                "   primary key (ID_)\n" +
                ")";
        template.execute(tempTable);
        //template.execute("alter table BIZ_CLUSTER_TEMP_DATA comment '数据变化临时表（用于数据变化统计）'");

        //数据变化临时表（用于数据变化统计）
        //template.execute("drop table if exists BIZ_CLUSTER_TEMP_DATA");
        String collectTable="create table BIZ_CLUSTER_COLLECT_DATA\n" +
                "(\n" +
                "   ID_                  VARCHAR(64) not null,\n" +
                "   COMPANY_NAME_        VARCHAR(120) not null default '' comment '单位名称',\n" +
                "   SYS_NAME_            VARCHAR(120) comment '系统名称',\n" +
                "   SYS_CODE_            VARCHAR(120) comment '系统编码',\n" +
                "   NAME_                varchar(64) default NULL comment '数据资源项名称',\n" +
                "   CODE_                varchar(64) comment '数据资源项编码',\n" +
                "   TABLE_NAME_          VARCHAR(64) comment '数据表名',\n" +
                "   SRC_ID_              VARCHAR(64) default NULL comment '数据表ID',\n" +
                "   DATA_TYPE_           int default NULL comment '数据操作标记（1：新增，2：修改，3：删除）',\n" +
                "   CREATE_TIME_         datetime default null comment '数据操作时间',\n" +
                "   primary key (ID_)\n" +
                ")";
        template.execute(collectTable);
        //template.execute("alter table BIZ_CLUSTER_COLLECT_DATA comment '数据采集临时表（用于采集任务）'");
    }
}