package org.matrix.remote.service;

import com.baomidou.dynamic.datasource.annotation.DS;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

/**
 * 用于增量同步的初始化操作
 * 1.对需要监听数据的表进行添加触发器
 * 2.与创建记录数据变化的增量表
 * <p>
 * 触发器的内容是对增量表里插入该条数据变化的关键内容
 * 此后可通过读取该增量表来进行增量同步
 * <p>
 * PS:选择性使用
 */
@SuppressWarnings("SqlNoDataSourceInspection")
@DS("zentao")
@Service
public class TriggerInitial {


    @Autowired
    private JdbcTemplate jdbcTemplate;


    /**
     * 创建增量数据表
     * 该表有3个字段
     * type：该条变化数据的操作类型（INSERT、UPDATE、DELETE）
     * entity：该条增量数据属于哪个表
     * key：该条变化数据的唯一标识（一般为主键值 可以根据该标识在原表查出具体数据再做同步更新、删除操作则只需指定标识）
     */
    public void createChangeTable() {
        String dropChangeTableSQL = "DROP TABLE IF EXISTS `zentao`.`kt_change`;\n";
        jdbcTemplate.execute(dropChangeTableSQL);
        String createChangeTableSQL = "CREATE TABLE `zentao`.`kt_change` (\n" +
                "  `type` VARCHAR(11) NULL,\n" +
                "  `entity` VARCHAR(255) NULL,\n" +
                "  `id` VARCHAR(255) NULL);\n";
        jdbcTemplate.execute(createChangeTableSQL);
    }

    /**
     * 创建User表的触发器
     */
    public void createUserTrigger() {
        String dropInsertTrigger = "DROP TRIGGER IF EXISTS `zentao`.`zt_user_AFTER_INSERT`;\n";
        String createInsertTrigger = "CREATE TRIGGER `zentao`.`zt_user_AFTER_INSERT` AFTER INSERT ON `zt_user` FOR EACH ROW\n" +
                "BEGIN\n" +
                "insert into `zentao`.`kt_change` (`type`,`entity`, `id`) values ('insert','user',new.id);\n" +
                "END;";
        jdbcTemplate.execute(dropInsertTrigger);
        jdbcTemplate.execute(createInsertTrigger);
        String dropUpdateTrigger = "DROP TRIGGER IF EXISTS `zentao`.`zt_user_AFTER_UPDATE`;\n";
        String createUpdateTrigger = "CREATE TRIGGER `zentao`.`zt_user_AFTER_UPDATE` AFTER UPDATE ON `zt_user` FOR EACH ROW\n" +
                "BEGIN\n" +
                "IF(new.account != old.account or new.password != old.password or new.realname != old.realname)\n" +
                "THEN\n" +
                "insert into `zentao`.`kt_change` (`type`,`entity`, `id`) values ('update','user',new.id);\n" +
                "END IF;\n" +
                "END;";
        jdbcTemplate.execute(dropUpdateTrigger);
        jdbcTemplate.execute(createUpdateTrigger);
        String dropDeleteTrigger = "DROP TRIGGER IF EXISTS `zentao`.`zt_user_AFTER_DELETE`;\n";
        String createDeleteTrigger = "CREATE TRIGGER `zentao`.`zt_user_AFTER_DELETE` AFTER DELETE ON `zt_user` FOR EACH ROW\n" +
                "BEGIN\n" +
                "insert into `zentao`.`kt_change` (`type`,`entity`, `id`) values ('delete','user',old.id);\n" +
                "END;";
        jdbcTemplate.execute(dropDeleteTrigger);
        jdbcTemplate.execute(createDeleteTrigger);
    }

    /**
     * 创建Project表的触发器
     */
    public void createProjectTrigger() {
        String dropInsertTrigger = "DROP TRIGGER IF EXISTS `zentao`.`zt_project_AFTER_INSERT`;\n";
        String createInsertTrigger = "CREATE TRIGGER `zentao`.`zt_project_AFTER_INSERT` AFTER INSERT ON `zt_project` FOR EACH ROW\n" +
                "BEGIN\n" +
                "insert into `zentao`.`kt_change` (`type`,`entity`, `id`) values ('insert','project',new.id);\n" +
                "END;";
        jdbcTemplate.execute(dropInsertTrigger);
        jdbcTemplate.execute(createInsertTrigger);
        String dropUpdateTrigger = "DROP TRIGGER IF EXISTS `zentao`.`zt_project_AFTER_UPDATE`;\n";
        String createUpdateTrigger = "CREATE TRIGGER `zentao`.`zt_project_AFTER_UPDATE` AFTER UPDATE ON `zt_project` FOR EACH ROW\n" +
                "BEGIN\n" +
                "IF(new.name != old.name)\n" +
                "THEN\n" +
                "insert into `zentao`.`kt_change` (`type`,`entity`, `id`) values ('update','project',new.id);\n" +
                "END IF;\n" +
                "END;";
        jdbcTemplate.execute(dropUpdateTrigger);
        jdbcTemplate.execute(createUpdateTrigger);
        String dropDeleteTrigger = "DROP TRIGGER IF EXISTS `zentao`.`zt_project_AFTER_DELETE`;\n";
        String createDeleteTrigger = "CREATE TRIGGER `zentao`.`zt_project_AFTER_DELETE` AFTER DELETE ON `zt_project` FOR EACH ROW\n" +
                "BEGIN\n" +
                "insert into `zentao`.`kt_change` (`type`,`entity`, `id`) values ('delete','project',old.id);\n" +
                "END;";
        jdbcTemplate.execute(dropDeleteTrigger);
        jdbcTemplate.execute(createDeleteTrigger);
    }

    /**
     * 创建UserView表的触发器
     */
    public void createUserViewTrigger() {
        String dropInsertTrigger = "DROP TRIGGER IF EXISTS `zentao`.`zt_userview_AFTER_INSERT`;\n";
        String createInsertTrigger = "CREATE TRIGGER `zentao`.`zt_userview_AFTER_INSERT` AFTER INSERT ON `zt_userview` FOR EACH ROW\n" +
                "BEGIN\n" +
                "insert into `zentao`.`kt_change` (`type`,`entity`, `id`) values ('insert','userview',new.account);\n" +
                "END;";
        jdbcTemplate.execute(dropInsertTrigger);
        jdbcTemplate.execute(createInsertTrigger);
        String dropUpdateTrigger = "DROP TRIGGER IF EXISTS `zentao`.`zt_userview_AFTER_UPDATE`;\n";
        String createUpdateTrigger = "CREATE TRIGGER `zentao`.`zt_userview_AFTER_UPDATE` AFTER UPDATE ON `zt_userview` FOR EACH ROW\n" +
                "BEGIN\n" +
                "insert into `zentao`.`kt_change` (`type`,`entity`, `id`) values ('update','userview',new.account);\n" +
                "END;";
        jdbcTemplate.execute(dropUpdateTrigger);
        jdbcTemplate.execute(createUpdateTrigger);
        String dropDeleteTrigger = "DROP TRIGGER IF EXISTS `zentao`.`zt_userview_AFTER_DELETE`;\n";
        String createDeleteTrigger = "CREATE TRIGGER `zentao`.`zt_userview_AFTER_DELETE` AFTER DELETE ON `zt_userview` FOR EACH ROW\n" +
                "BEGIN\n" +
                "insert into `zentao`.`kt_change` (`type`,`entity`, `id`) values ('delete','userview',old.account);\n" +
                "END;";
        jdbcTemplate.execute(dropDeleteTrigger);
        jdbcTemplate.execute(createDeleteTrigger);
    }


}
