-- ----------------------------
-- 插入积分商城代金券相关菜单配置
-- ----------------------------
INSERT INTO `sys_menu` (`menu_name`,`parent_id`,`order_num`,`path`,`component`,`query`,`is_frame`,`is_cache`,`menu_type`,`visible`,`status`,`perms`,`icon`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES ('积分商城',0,15,'shopping',NULL,NULL,1,0,'M','0','0',NULL,'shopping','admin','2023-06-08 14:04:56','',NULL,'');
SELECT @parentId := LAST_INSERT_ID();

INSERT INTO `sys_menu` (`menu_name`,`parent_id`,`order_num`,`path`,`component`,`query`,`is_frame`,`is_cache`,`menu_type`,`visible`,`status`,`perms`,`icon`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES ('代金券管理', @parentId,0,'voucher','goods/voucher/index',NULL,1,0,'C','0','0',NULL,'money','admin','2023-06-08 14:06:24','',NULL,'');
INSERT INTO `sys_menu` (`menu_name`,`parent_id`,`order_num`,`path`,`component`,`query`,`is_frame`,`is_cache`,`menu_type`,`visible`,`status`,`perms`,`icon`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES ('代金券兑换管理', @parentId,1,'exchange','goods/voucher/exchange',NULL,1,0,'C','0','0',NULL,'build','admin','2023-06-08 15:00:05','',NULL,'');

INSERT INTO `sys_menu` (`menu_name`,`parent_id`,`order_num`,`path`,`component`,`query`,`is_frame`,`is_cache`,`menu_type`,`visible`,`status`,`perms`,`icon`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES ('代金券统计',3139,3,'statvoucher','stat/voucher/index',NULL,1,0,'C','0','0',NULL,'chart','admin','2023-06-12 10:18:43','',NULL,'');
INSERT INTO `sys_menu` (`menu_name`,`parent_id`,`order_num`,`path`,`component`,`query`,`is_frame`,`is_cache`,`menu_type`,`visible`,`status`,`perms`,`icon`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES ('兑换数统计',3139,4,'exchange','stat/integral/exchange',NULL,1,0,'C','0','0','','chart','admin','2023-06-13 13:47:24','admin','2023-06-13 13:48:55','');
INSERT INTO `sys_menu` (`menu_name`,`parent_id`,`order_num`,`path`,`component`,`query`,`is_frame`,`is_cache`,`menu_type`,`visible`,`status`,`perms`,`icon`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES ('核销数统计',3139,5,'writeOff','stat/writeOff/index',NULL,1,0,'C','0','0','','chart','admin','2023-06-12 10:27:31','admin','2023-06-13 13:45:15','');

-- ----------------------------
-- 代金券相关字典配置
-- ----------------------------
INSERT INTO `sys_dict_type` (`dict_name`,`dict_type`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES ('社区','community','0','admin','2023-06-09 15:31:11','',NULL,NULL);
INSERT INTO `sys_dict_type` (`dict_name`,`dict_type`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES ('领取群体','receive_tags','0','admin','2023-06-09 16:13:32','',NULL,NULL);
INSERT INTO `sys_dict_type` (`dict_name`,`dict_type`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES ('领取状态','voucher_status','0','admin','2023-06-09 16:50:50','',NULL,NULL);
INSERT INTO `sys_dict_type` (`dict_name`,`dict_type`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES ('商品状态','goods_status','0','admin','2023-06-09 16:50:50','',NULL,NULL);

INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (0,'中海南溪','中海南溪','community',NULL,'default','N','0','admin','2023-06-09 15:31:26','',NULL,NULL);
INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (0,'环城社区','环城社区','community',NULL,'default','N','0','admin','2023-06-09 15:31:41','',NULL,NULL);

INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (0,'下沉干部','下沉干部','receive_tags',NULL,'default','N','0','admin','2023-06-09 16:14:12','',NULL,NULL);
INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (0,'在职党员','在职党员','receive_tags',NULL,'default','N','0','admin','2023-06-09 16:14:22','',NULL,NULL);
INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (0,'网格长','网格长','receive_tags',NULL,'default','N','0','admin','2023-06-09 16:14:30','',NULL,NULL);
INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (0,'楼栋长','楼栋长','receive_tags',NULL,'default','N','0','admin','2023-06-09 16:14:38','',NULL,NULL);
INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (0,'单元长','单元长','receive_tags',NULL,'default','N','0','admin','2023-06-09 16:14:45','',NULL,NULL);
INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (0,'志愿者','志愿者','receive_tags',NULL,'default','N','0','admin','2023-06-09 16:14:52','',NULL,NULL);
INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (0,'社区工作者','社区工作者','receive_tags',NULL,'default','N','0','admin','2023-06-09 16:15:00','',NULL,NULL);
INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (0,'居民','居民','receive_tags',NULL,'default','N','0','admin','2023-06-09 16:15:08','',NULL,NULL);
INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (0,'所有','所有','receive_tags',NULL,'default','N','0','admin','2023-06-09 16:15:20','',NULL,NULL);

INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (0,'已兑换','1','voucher_status',NULL,'default','N','0','admin','2023-06-09 16:51:06','',NULL,NULL);
INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (0,'已核销','9','voucher_status',NULL,'default','N','0','admin','2023-06-09 16:51:17','',NULL,NULL);

INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (0,'上架','1','goods_status',NULL,'default','N','0','admin','2023-06-15 14:13:50','',NULL,NULL);
INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (1,'下架','0','goods_status',NULL,'default','N','0','admin','2023-06-15 14:13:57','',NULL,NULL);

SET SQL_SAFE_UPDATES = 0;
delete from sys_dict_data where dict_type = 'voucher_category';
SET SQL_SAFE_UPDATES = 1;

INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (0,'生活服务','live','voucher_category',NULL,'default','N','0','admin','2023-05-26 16:39:42','admin','2023-06-13 11:26:43',NULL);
INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (0,'文化娱乐','entertainment','voucher_category',NULL,'default','N','0','admin','2023-05-26 16:40:08','',NULL,NULL);
INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (0,'其他','other','voucher_category',NULL,'default','N','0','admin','2023-05-26 16:41:09','',NULL,NULL);

INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (1,'幸福餐厅','幸福餐厅','voucher_category',NULL,'default','N','0','admin','2023-05-26 13:52:49','admin','2023-06-13 11:19:19','live');
INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (1,'超市','超市','voucher_category',NULL,'default','N','0','admin','2023-05-26 13:53:29','admin','2023-06-13 11:28:19','live');
INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (1,'理疗按摩','理疗按摩','voucher_category',NULL,'default','N','0','admin','2023-06-13 11:18:27','admin','2023-06-13 11:19:02','live');
INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (1,'生鲜','生鲜','voucher_category',NULL,'default','N','0','admin','2023-06-13 11:19:47','',NULL,'live');
INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (1,'洗衣','洗衣','voucher_category',NULL,'default','N','0','admin','2023-06-13 11:20:01','admin','2023-06-13 11:20:21','live');
INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (1,'理发','理发','voucher_category',NULL,'default','N','0','admin','2023-06-13 11:20:15','',NULL,'live');
INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (1,'汽修','汽修','voucher_category',NULL,'default','N','0','admin','2023-06-13 11:20:37','',NULL,'live');
INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (1,'洗浴','洗浴','voucher_category',NULL,'default','N','0','admin','2023-06-13 11:20:52','',NULL,'live');
INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (2,'幸福课堂','幸福课堂','voucher_category',NULL,'default','N','0','admin','2023-05-26 13:53:08','admin','2023-06-13 11:21:10','entertainment');

-- ----------------------------
-- 创建触发器，每新增一条biz_user记录，同时：
--      插入一条biz_user_tag记录，tag默认为居民
--      插入一条che_points，积分默认为0（便于手工调整赋分）
-- ----------------------------
DROP TRIGGER IF EXISTS `insert_biz_user_tag`;
DROP TRIGGER IF EXISTS `insert_biz_user`;
DELIMITER $$
CREATE TRIGGER `insert_biz_user` AFTER INSERT ON `biz_user` FOR EACH ROW
BEGIN
    INSERT INTO `biz_user_tag` (`user_id`, `tag`) VALUES (NEW.user_id, '其他');
    INSERT INTO `che_points` (`user_id`, `honor_points`, `spend_points`) VALUES (NEW.user_id, 0, 0);
END$$
DELIMITER ;

DROP TRIGGER IF EXISTS `insert_che_resident`;
DELIMITER $$
CREATE TRIGGER `insert_che_resident` AFTER INSERT ON `che_resident` FOR EACH ROW
BEGIN
    UPDATE `biz_user_tag` SET tag = '居民' WHERE user_id = NEW.applicant_id;
END$$
DELIMITER ;