notice/doc/sql/msg_type.sql

-- -- create database if not exists `notice` default character set utf8 collate utf8_general_ci;
DROP TABLE IF EXISTS `notice`.`message_type`;
create table `notice`.`message_type`(
    `id` int(11) NOT NULL AUTO_INCREMENT comment '主键id',
    
    `sys_name` varchar(64) not null default '' comment '系统名称',
    `pasture_id` int(11) not null default 0 comment '牧场id',
    `service_id` int(11) unsigned not null 0 comment '服务id',
    `type_name` varchar(64) not null default '' comment '消息类型名称',

    `remind_type` int(11) not null default 0 comment '发送方式',
    `date_type` int(11) not null default 0 comment '循环类型',
    `cycle_type` int(11) not null default 0 comment '发送机制',

    `push_date` int(11) not null default 0  comment '推送日期',
    `push_time` varchar(64) not null default '' comment '推送时间',
    `delay_time` int(11) not null default 0 comment '延迟时间',
    `interval_time` int(11) not null default 0 comment '提醒间隔时间',
    
    `push_limit` int(11) not null default 0  comment '推送限制次数',
    `template_id` varchar(64) not null default '' comment '模板id',
    `status` int(11) not null default 0 comment '状态',

    `created_at` DATETIME not null default CURRENT_TIMESTAMP,
     `updated_at` DATETIME not null ON UPDATE CURRENT_TIMESTAMP ,
    primary key (`id`),
    UNIQUE index  (`service_id`,`sys_name`,`pasture_id`),
    index  (`remind_type`),
    index (`date_type`),
    index (`cycle_type`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '消息类型';
-- show index from message_type;
-- describe message_type;

-- update message_type set remind_type =0;
alter table message_type  
-- drop column delay_time,
-- add `delay_time` int(11) not null default 0 comment '延迟时间' after push_time ;
-- drop column create_at,
-- drop column update_at,
-- add status int(11) not null default 0 comment '状态';
--  add `created_at` DATETIME not null default CURRENT_TIMESTAMP,
--  add    `updated_at` DATETIME not null ON UPDATE CURRENT_TIMESTAMP ;
-- drop column remind_type_id,
-- modify column remind_type int(11) unsigned not null default 0 comment '提醒类型id';
-- modify  date_type int(11) not null default 0,
-- modify cycle_type int(11) not null default 0;
--  drop column cycle_type ,
-- add column cycle_type varchar(64) not null default '' AFTER interval_time;
-- modify column `id` int(11) NOT NULL AUTO_INCREMENT before sys_name,
-- modify column service_id int(11) unsigned not null default 0 before type_name,
-- add unique key (`service_id`,`sys_name`,`pasture_id`);
-- add primary key (`id`),
-- modify push_date int(11) not null default 0;
-- modify column `id` int(11) NOT NULL 
-- modify column `id` int(11) NOT NULL AUTO_INCREMENT;
-- modify column `remind_type_id` int(11) unsigned not null default '0' FIRST remind_type;
-- modify column `remind_type_id` int(11)  FIRST remind_type
-- drop index u_msg_type on message_type

-- alter table message
-- -- add push_count int(11) unsigned not null default '0' AFTER `status`;
-- add remind_type_id int(11) unsigned not null default '0' AFTER `status`;

-- insert into message_type
-- (sys_name,id,pasture_id,type_name,remind_type_id,remind_type,push_date,push_time,interval_time,push_limit,template_id,roles_id,users_id)
-- values  
--     -- ('tmrwatch',1,1,'合同金额超期未收',3,'指定周期','w1,w3,w5','9:30','0',3,'BtkN1rWKOJtKP0C64lGxIrPzLRFsYFas-4gupX2-pFo','1,2,3','1,2,3'),
--     ('tmrwatch',2,3,'合同金额超期未收',3,'指定周期','w1,w3,w5','9:30','0',3,'BtkN1rWKOJtKP0C64lGxIrPzLRFsYFas-4gupX2-pFo','1,2,3','1,2,3'),
--     ('tmrwatch',1,3,'合同金额超期未收',3,'指定周期','w1,w3,w5','9:30','0',3,'BtkN1rWKOJtKP0C64lGxIrPzLRFsYFas-4gupX2-pFo','1,2,3','1,2,3');
    -- ('tmrwatch','合同审批',1,'立即','0','0','2','0','BtkN1rWKOJtKP0C64lGxIrPzLRFsYFas-4gupX2-pFo'),
    -- ('tmrwatch','客户回访未完成',2,'延时','48','9:30','24',3,'BtkN1rWKOJtKP0C64lGxIrPzLRFsYFas-4gupX2-pFo'),