-- workplus-se update from 5.2.0 to 5.5.0
-- include workplus and calendar database



-- 20220617
CREATE TABLE `user_token_logs` (
  `domain_id` varchar(48) COLLATE utf8mb4_unicode_ci NOT NULL,
  `token_user` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `token_device` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `token` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `token_time` bigint(20) NOT NULL,
  `refresh_time` bigint(20) NOT NULL,
  `expire_time` bigint(20) NOT NULL,
  `last_token` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `last_token_time` bigint(20) DEFAULT NULL,
  `last_refresh_time` bigint(20) DEFAULT NULL,
  `last_expire_time` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`token_user`,`token_device`,`domain_id`) USING BTREE,
  KEY `user_token_logs_index_1` (`refresh_time`,`domain_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 20220719
-- 以下语句已存在4.10.8版本
ALTER TABLE `org_settings` ADD COLUMN `bing_settings` text NOT NULL;
UPDATE `org_settings` SET `bing_settings` = '{}';

-- 20220721
-- 以下语句已存在4.10.8版本
ALTER TABLE `bings` ADD COLUMN `channel` varchar(48) NOT NULL;
ALTER TABLE `bings` ADD COLUMN `reply_policy` varchar(48) NOT NULL;
UPDATE `bings` SET `channel` = 'message';
UPDATE `bings` SET `reply_policy` = 'broadcast';
ALTER TABLE `bings` ADD COLUMN `conversation_id` varchar(48);
ALTER TABLE `bings` ADD COLUMN `conversation_type` varchar(48);
ALTER TABLE `bings` CHANGE COLUMN `initiator_id` `owner_id` varchar(48) NOT NULL;
ALTER TABLE `bings` CHANGE COLUMN `initiator_domain` `owner_type` varchar(48) NOT NULL;
ALTER TABLE `bings` ADD COLUMN `delay_trigger` varchar(200);
ALTER TABLE `bings` ADD COLUMN `remind_trigger` varchar(200);
ALTER TABLE `bings` ADD COLUMN `content_body` text;
ALTER TABLE `bings` ADD COLUMN `create_time` bigint(20) NOT NULL;
ALTER TABLE `bings` ADD COLUMN `modify_time` bigint(20) NOT NULL;
ALTER TABLE `bings` ADD INDEX `bing_idx_5` USING BTREE(`conversation_id`, `conversation_type`);
UPDATE `bings` SET `create_time` = `bing_time`;
UPDATE `bings` SET `modify_time` = `bing_time`;
UPDATE `bings` b SET b.`owner_type` = 'USER';
ALTER TABLE `bings` DROP INDEX `bing_idx_2`;
ALTER TABLE `bings` ADD INDEX `bing_idx_2` USING BTREE(`owner_id`, `create_time`);
ALTER TABLE `bings` ADD INDEX `bing_idx_6` USING BTREE(`modify_time`);

ALTER TABLE `bing_members` ADD COLUMN `phone` varchar(50);
ALTER TABLE `bing_members` ADD COLUMN `conversation_id` varchar(48);
ALTER TABLE `bing_members` ADD COLUMN `conversation_type` varchar(48);
ALTER TABLE `bing_members` ADD INDEX `bing_member_idx_8` USING BTREE(`conversation_id`, `conversation_type`);
ALTER TABLE `bing_members` ADD COLUMN `recycled` tinyint(1) NOT NULL DEFAULT '0';
ALTER TABLE `bing_members` ADD COLUMN `recycle_time` bigint(20);
ALTER TABLE `bing_members` ADD COLUMN `deleted` tinyint(1) NOT NULL DEFAULT '0';
ALTER TABLE `bing_members` ADD COLUMN `delete_time` bigint(20);

-- 20220725
-- w6s-settings
-- 以下语句已存在4.10.8版本
ALTER TABLE `domain_whitelist_settings` RENAME `domain_scope_list_settings`;

-- 20220728
-- w6s-settings
CREATE TABLE `client_metadata` (
  `domain_id` varchar(48) COLLATE utf8mb4_unicode_ci NOT NULL,
  `client_id` varchar(48) COLLATE utf8mb4_unicode_ci NOT NULL,
  `category` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `property` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `metadata` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `refresh_time` bigint(20) NOT NULL,
  PRIMARY KEY (`client_id`,`category`,`property`,`domain_id`),
  KEY `client_metadata_index_1` (`client_id`,`domain_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 20220803
-- todo_participants
-- 以下语句已存在4.10.8版本
ALTER TABLE `todo_participants` DROP INDEX `todo_participants_index_2`;
ALTER TABLE `todo_participants` DROP COLUMN `todo_keep_domain`;
ALTER TABLE `todo_participants` DROP COLUMN `todo_keeper_id`;
ALTER TABLE `todo_participants` ADD COLUMN `role` int(11) NOT NULL;
ALTER TABLE `todo_participants` ADD COLUMN `status` int(11) NOT NULL;
UPDATE `todo_participants` SET `role` = 1;
UPDATE `todo_participants` SET `status` = 1;
ALTER TABLE `todo_participants` ADD COLUMN `starred` tinyint(1) NOT NULL DEFAULT '0';
ALTER TABLE `todo_participants` ADD COLUMN `star_time` bigint(20);
ALTER TABLE `todo_participants` ADD COLUMN `recycled` tinyint(1) NOT NULL DEFAULT '0';
ALTER TABLE `todo_participants` ADD COLUMN `recycle_time` bigint(20);
ALTER TABLE `todo_participants` ADD COLUMN `deleted` tinyint(1) NOT NULL DEFAULT '0';
ALTER TABLE `todo_participants` ADD COLUMN `delete_time` bigint(20);
ALTER TABLE `todo_participants` ADD COLUMN `placeholder` tinyint(1) NOT NULL DEFAULT '0';
ALTER TABLE `todo_participants` CHANGE COLUMN `operation_time` `create_time` bigint(20) NOT NULL;
ALTER TABLE `todo_participants` CHANGE COLUMN `avatar` `avatar` varchar(128);
ALTER TABLE `todo_participants` ADD COLUMN `modify_time` bigint(20) NOT NULL;
ALTER TABLE `todo_participants` ADD COLUMN `complete_time` bigint(20) ;
UPDATE `todo_participants` SET `modify_time` = `create_time`;
UPDATE `todo_participants` SET `complete_time` = `create_time`;
ALTER TABLE `todo_participants` ADD COLUMN `expire_time` bigint(20) NOT NULL;
ALTER TABLE `todo_participants` DROP PRIMARY KEY;
ALTER TABLE `todo_participants` ADD PRIMARY KEY(`_id`, `todo_id`, `role`);
ALTER TABLE `todo_participants` ADD INDEX `todo_participants_index_2` USING BTREE(`expire_time`, `status`);
ALTER TABLE `todo_participants` ADD INDEX `todo_participants_index_4` USING BTREE(`_id`, `todo_id`, `recycled`);
ALTER TABLE `todo_participants` ADD INDEX `todo_participants_index_5` USING BTREE(`modify_time`, `_id`);

-- todos
-- 以下语句已存在4.10.8版本
ALTER TABLE `todos` DROP INDEX `todos_index_2`;
ALTER TABLE `todos` DROP INDEX `todos_index_4`;
ALTER TABLE `todos` DROP COLUMN `keeper_domain`;
ALTER TABLE `todos` CHANGE COLUMN `modify_ime` `modify_time` bigint(20) NOT NULL;

ALTER TABLE `todos` ADD COLUMN `conversation_id` varchar(48);
ALTER TABLE `todos` ADD COLUMN `conversation_type` varchar(20);
UPDATE `todos` SET `conversation_id` = `owner_id`;
UPDATE `todos` SET `conversation_type` = 'DISCUSSION';
ALTER TABLE `todos` ADD COLUMN `topic` text;
ALTER TABLE `todos` ADD COLUMN `description` text;
ALTER TABLE `todos` ADD COLUMN `priority` int(11) NOT NULL;
UPDATE `todos` SET `priority` = 1;
ALTER TABLE `todos` ADD COLUMN `hyperlinks` text NOT NULL;
ALTER TABLE `todos` ADD COLUMN `attachments` text NOT NULL;
UPDATE `todos` SET `hyperlinks` = '[]';
UPDATE `todos` SET `attachments` = '[]';
ALTER TABLE `todos` ADD COLUMN `repeat_trigger` varchar(200);
ALTER TABLE `todos` ADD COLUMN `remind_trigger` varchar(200);
ALTER TABLE `todos` ADD COLUMN `expire_time` bigint(20) NOT NULL;
UPDATE `todos` SET `expire_time` = 9999999999999;
ALTER TABLE `todos` ADD COLUMN `openness` tinyint(1) NOT NULL DEFAULT '0';
ALTER TABLE `todos` ADD COLUMN `status` int(11) NOT NULL;
UPDATE `todos` SET `status` = 1;
ALTER TABLE `todos` ADD COLUMN `complete_time` bigint(20) ;

ALTER TABLE `todos` ADD INDEX `todos_index_2` USING BTREE(`keeper_id`, `domain_id`);
ALTER TABLE `todos` ADD INDEX `todos_index_4` USING BTREE(`modify_time`, `owner_type`);
ALTER TABLE `todos` ADD INDEX `todos_index_5` USING BTREE(`conversation_id`, `conversation_type`);
ALTER TABLE `todos` ADD INDEX `todos_index_6` USING BTREE(`expire_time`, `owner_type`);

-- todo_trashes
-- 以下语句已存在4.10.8版本
ALTER TABLE `todo_trashes` DROP INDEX `todo_trashes_index_2`;
ALTER TABLE `todo_trashes` DROP INDEX `todo_trashes_index_4`;
ALTER TABLE `todo_trashes` DROP COLUMN `keeper_domain`;
ALTER TABLE `todo_trashes` CHANGE COLUMN `modify_ime` `modify_time` bigint(20) NOT NULL;

ALTER TABLE `todo_trashes` ADD COLUMN `conversation_id` varchar(48);
ALTER TABLE `todo_trashes` ADD COLUMN `conversation_type` varchar(20);
UPDATE `todo_trashes` SET `conversation_id` = `owner_id`;
UPDATE `todo_trashes` SET `conversation_type` = 'DISCUSSION';
ALTER TABLE `todo_trashes` ADD COLUMN `topic` text;
ALTER TABLE `todo_trashes` ADD COLUMN `description` text;
ALTER TABLE `todo_trashes` ADD COLUMN `priority` int(11) NOT NULL;
UPDATE `todo_trashes` SET `priority` = 1;
ALTER TABLE `todo_trashes` ADD COLUMN `hyperlinks` text NOT NULL;
ALTER TABLE `todo_trashes` ADD COLUMN `attachments` text NOT NULL;
UPDATE `todo_trashes` SET `hyperlinks` = '[]';
UPDATE `todo_trashes` SET `attachments` = '[]';
ALTER TABLE `todo_trashes` ADD COLUMN `repeat_trigger` varchar(200);
ALTER TABLE `todo_trashes` ADD COLUMN `remind_trigger` varchar(200);
ALTER TABLE `todo_trashes` ADD COLUMN `expire_time` bigint(20) NOT NULL;
UPDATE `todo_trashes` SET `expire_time` = 9999999999999;
ALTER TABLE `todo_trashes` ADD COLUMN `openness` tinyint(1) NOT NULL DEFAULT '0';
ALTER TABLE `todo_trashes` ADD COLUMN `status` int(11) NOT NULL;
UPDATE `todo_trashes` SET `status` = 1;
ALTER TABLE `todo_trashes` ADD COLUMN `complete_time` bigint(20);

ALTER TABLE `todo_trashes` ADD INDEX `todo_trashes_index_2` USING BTREE(`keeper_id`, `domain_id`);
ALTER TABLE `todo_trashes` ADD INDEX `todo_trashes_index_4` USING BTREE(`modify_time`, `owner_type`);
ALTER TABLE `todo_trashes` ADD INDEX `todo_trashes_index_5` USING BTREE(`conversation_id`, `conversation_type`);
ALTER TABLE `todo_trashes` ADD INDEX `todo_trashes_index_6` USING BTREE(`expire_time`, `owner_type`);

-- 20220805
-- 以下语句已存在4.10.8版本
ALTER TABLE `org_organizations` CHANGE COLUMN `sort_order` `sort_order` bigint(20) NOT NULL;

-- 20220810
ALTER TABLE `user_tags` DROP INDEX `user_tags_index_2`;
ALTER TABLE `user_tags` ADD PRIMARY KEY(`tag`, `domain_id`);
ALTER TABLE `user_tag_relationships` DROP INDEX `user_tag_relationships_index_1`;
ALTER TABLE `user_tag_relationships` ADD PRIMARY KEY(`user_id`, `tag`, `domain_id`);

-- 20220814
ALTER TABLE `discussions` ADD COLUMN `mute_until` bigint(20);
ALTER TABLE `discussion_members` ADD COLUMN `mute_until` bigint(20);

-- 20220825
-- 以下语句已存在4.10.8版本
ALTER TABLE `org_settings` ADD COLUMN `todo_settings` text NOT NULL;
UPDATE `org_settings` SET `todo_settings` = '{}';

-- 20220901
-- 以下语句已存在4.10.8版本
ALTER TABLE `bing_replies` ADD COLUMN `seen_by_domain` varchar(48);
ALTER TABLE `bing_replies` ADD COLUMN `seen_by_id` varchar(48);
UPDATE `bing_replies` SET `seen_by_domain` = `bing_domain`;
UPDATE `bing_replies` SET `seen_by_id` = 'all';
ALTER TABLE `bing_replies` ADD INDEX `bing_reply_idx_3` USING BTREE(`seen_by_id`, `seen_by_domain`, `bing_id`);

-- 20220902
-- w6s-broker
ALTER TABLE `message_conversation_settings` ADD COLUMN `followed` tinyint(1) NOT NULL DEFAULT '0';


-- 20220906
-- w6s-settings
-- 以下语句已存在4.10.8版本
ALTER TABLE `client_releases` ADD COLUMN `release_deny_scopes` text NOT NULL;
UPDATE `client_releases` SET `release_deny_scopes` = '[]';

CREATE TABLE `client_release_deny_scopes` (
  `domain_id` varchar(48) COLLATE utf8mb4_unicode_ci NOT NULL,
  `release_id` varchar(48) COLLATE utf8mb4_unicode_ci NOT NULL,
  `path` varchar(512) COLLATE utf8mb4_unicode_ci NOT NULL,
  `_id` varchar(48) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`_id`),
  KEY `client_release_deny_scopes_index_1` (`release_id`,`domain_id`),
  KEY `client_release_deny_scopes_index_2` (`path`,`release_id`,`domain_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `app_menu_details` (
  `domain_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `app_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `pinyin` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `initial` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `en_name` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `tw_name` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `value` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`_id`),
  KEY `app_menu_details_index_1` (`domain_id`),
  KEY `app_menu_details_index_2` (`app_id`,`domain_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- update `app_menu_details` m set m.`domain_id` = (select a.`domain_id` from `apps` a where m.`app_id` = a.`id` limit 1) WHERE EXISTS (select a.`domain_id` from `apps` a where m.`app_id` = a.`id` limit 1)

-- 20220908
CREATE TABLE `todo_participant_trashes` (
  `participant_id` varchar(48) COLLATE utf8mb4_unicode_ci NOT NULL,
  `domain_id` varchar(48) COLLATE utf8mb4_unicode_ci NOT NULL,
  `role` int(11) NOT NULL,
  `todo_id` varchar(48) COLLATE utf8mb4_unicode_ci NOT NULL,
  `trash_time` bigint(20) NOT NULL,
  `_id` varchar(48) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`_id`),
  KEY `todo_participant_trashes_index_1` (`trash_time`,`participant_id`,`todo_id`),
  KEY `todo_participant_trashes_index_2` (`todo_id`,`participant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 20220914
-- 以下语句已存在4.10.8版本
ALTER TABLE `domain_settings` ADD COLUMN `bing_settings` text NOT NULL;
UPDATE `domain_settings` SET `bing_settings` = '{}';

CREATE TABLE `client_metadata` (
  `domain_id` varchar(48) COLLATE utf8mb4_unicode_ci NOT NULL,
  `client_id` varchar(48) COLLATE utf8mb4_unicode_ci NOT NULL,
  `category_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `category` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `property` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `metadata` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `refresh_time` bigint(20) NOT NULL,
  PRIMARY KEY (`client_id`,`category_id`,`category`,`property`,`domain_id`),
  KEY `client_metadata_index_1` (`client_id`,`category_id`,`domain_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 20220915
ALTER TABLE `bing_members` DROP INDEX `bing_member_idx_8`;
ALTER TABLE `bing_members` ADD INDEX `bing_member_idx_8` USING BTREE(`member_id`, `member_domain`, `recycled`);

-- 20220919
-- 以下语句已存在4.10.8版本
ALTER TABLE `domain_settings` ADD COLUMN `metadata` text NOT NULL;
UPDATE `domain_settings` SET `metadata` = '[]';


-- 20220922
-- w6s-settings
ALTER TABLE `domain_keys` ADD COLUMN `rsa_public_exponent` text;
ALTER TABLE `domain_keys` ADD COLUMN `rsa_private_exponent` text;
ALTER TABLE `domain_keys` ADD COLUMN `rsa_modules` text;
ALTER TABLE `domain_keys` ADD COLUMN `rsa_key_size` int(11);

-- 20221009 如果表数据过大，会长时间锁表。
ALTER TABLE `material_article_contents` ADD COLUMN `metadata` text;

-- 20221013
-- w6s-broker 如果表数据过大，会长时间锁表。
ALTER TABLE `message_plans` ADD COLUMN `material_id` varchar(64);

-- 20221015
ALTER TABLE `org_organizations` ADD COLUMN `metadata` text NOT NULL;
UPDATE `org_organizations` SET `metadata` = '{}';
ALTER TABLE `org_employees` ADD COLUMN `metadata` text NOT NULL;
UPDATE `org_employees` SET `metadata` = '{}';
ALTER TABLE `org_employee_trashes` ADD COLUMN `metadata` text NOT NULL;
UPDATE `org_employee_trashes` SET `metadata` = '{}';

-- 20221128
ALTER TABLE `user_logs` ADD COLUMN `lock_until` bigint(20);


-- 20221207 w6s-badge
CREATE TABLE `badge_gain_cycles`  (
  `id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `badge_id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `begin_time` bigint(20) NOT NULL COMMENT '周期开始时间',
  `end_time` bigint(20) NOT NULL COMMENT '周期结束时间',
  `state` int(11) NOT NULL COMMENT '周期状态 0-未开始 1-生效中 2-已结束 3-提前结束 4-已取消',
  `create_time` bigint(20) NOT NULL,
  `modify_time` bigint(20) NOT NULL,
  PRIMARY KEY (`id`) ,
  KEY `badge_gain_cycles_idx1` (`badge_id`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;


CREATE TABLE `badge_gain_scope_labels`  (
  `id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `badge_id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `label_id` bigint(20) NOT NULL,
  `group_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `badge_scope_labels_unique1`(`badge_id`, `label_id`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;


CREATE TABLE `badge_gain_scopes`  (
  `id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `badge_id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `path` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`) ,
  KEY `badge_scopes_idx1` (`badge_id`, `path`),
  KEY `badge_scopes_idx2` (`path`, `badge_id`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;


CREATE TABLE `badge_grant_logs`  (
  `id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `domain_id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `owner_id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `record_id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `media_id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `operator_id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `operator_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `operator_en_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `operator_type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `badge_id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_account` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `operate_type` int(11) NOT NULL COMMENT '操作类型 1-颁发 2-撤销',
  `revoked` tinyint(1) NOT NULL DEFAULT 0 COMMENT '撤销状态',
  `create_time` bigint(20) NOT NULL,
  `modify_time` bigint(20) NOT NULL,
  PRIMARY KEY (`id`) ,
  KEY `badge_grant_logs_idx1` (`domain_id`, `owner_id`, `record_id`),
  KEY `badge_grant_logs_idx2` (`domain_id`, `owner_id`, `operator_id`),
  KEY `badge_grant_logs_idx3` (`domain_id`, `owner_id`, `badge_id`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;


CREATE TABLE `badge_grant_scope_apps`  (
  `id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `domain_id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `badge_id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `app_id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `feature` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `en_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `icon` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `definitions` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `badge_scope_apps_unique` (`badge_id`, `app_id`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;


CREATE TABLE `badge_grant_statistics`  (
  `id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `badge_id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `gain_cycle_id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '获取周期id',
  `granted_quantity` int(11) NULL DEFAULT 0 COMMENT '颁发数量',
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `badge_grant_statistics_unique1` (`badge_id`, `gain_cycle_id`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;


CREATE TABLE `badge_settings`  (
  `domain_id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `display_settings` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`domain_id`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;


CREATE TABLE `badge_tags`  (
  `domain_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `owner_id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `badge_id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `pinyin` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `initial` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`badge_id`, `name`),
  KEY `badge_tags_idx1` (`domain_id`, `owner_id`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;


CREATE TABLE `badge_user_gain_records`  (
  `id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `domain_id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `owner_id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `badge_id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `gain_cycle_id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '获取周期',
  `org_code` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `wear_status` tinyint(1) NOT NULL DEFAULT 0 COMMENT '佩戴状态',
  `avatar_wear_status` tinyint(1) NOT NULL,
  `deleted` tinyint(1) NOT NULL,
  `create_time` bigint(20) NOT NULL,
  `modify_time` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `badge_user_gain_record_idx2` (`domain_id`, `user_id`, `deleted`, `wear_status`) ,
  KEY `badge_user_gain_record_idx1` (`domain_id`, `owner_id`, `user_id`, `badge_id`, `gain_cycle_id`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;


CREATE TABLE `badge_wall_catalogs`  (
  `id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `domain_id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `owner_id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '分类名称',
  `en_name` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '分类英文名称',
  `parent_id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '父id',
  `path` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `level` int(11) NOT NULL COMMENT '类级',
  `sort_order` int(11) NOT NULL DEFAULT 0 COMMENT '排序号',
  `create_time` bigint(20) NOT NULL,
  `modify_time` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `badge_wall_catalogs_idx1` (`domain_id`, `owner_id`, `path`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;


CREATE TABLE `badge_wall_elements`  (
  `domain_id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `owner_id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `catalog_id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `badge_id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `sort_order` int(11) NOT NULL,
  `create_time` bigint(20) NOT NULL,
  PRIMARY KEY (`domain_id`, `owner_id`, `catalog_id`, `badge_id`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;


CREATE TABLE `badges`  (
  `id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `domain_id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `owner_id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '徽章类型 single, rank, collection',
  `icon` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '徽章图标',
  `name` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '徽章名称',
  `pinyin` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `initial` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `en_name` text CHARACTER SET utf16 COLLATE utf16_unicode_ci NULL COMMENT '徽章英文名称',
  `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '中文描述',
  `en_description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '英文描述',
  `level` int(11) NULL DEFAULT NULL COMMENT '徽章等级',
  `enabled_avatar` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否开启头像框',
  `avatar` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '头像框',
  `avatar_desc` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '头像框描述',
  `avatar_en_desc` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '头像框英文描述',
  `persisted` tinyint(1) NOT NULL COMMENT '是否长期有效',
  `judge_type` int(11) NOT NULL COMMENT '获取判断方式 0-逻辑判断 1-数值判断',
  `gain_condition_settings` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '获取条件设置',
  `gain_button_settings` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '获取按钮设置',
  `additional_info_settings` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '补充说明设置',
  `disabled` tinyint(1) NOT NULL DEFAULT 0 COMMENT '状态 0-下架 1-上架',
  `deleted` tinyint(4) NOT NULL DEFAULT 0 COMMENT '删除状态',
  `create_time` bigint(20) NOT NULL,
  `modify_time` bigint(20) NOT NULL,
  `create_user_id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `create_user_name` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `badges_idx1` (`domain_id`, `owner_id`, `deleted`, `disabled`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

ALTER TABLE org_admins add column (`badge_scope` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL);
UPDATE org_admins set `badge_scope` = 'all';


-- 20221218
-- w6s-settings
ALTER TABLE `domain_settings` ADD COLUMN `bot_settings` varchar(100) NOT NULL;
UPDATE `domain_settings` SET `bot_settings` = '{}';

-- 20221220
ALTER TABLE `apps` ADD COLUMN `bot_channel` varchar(48);


-- 20221221
CREATE TABLE `badge_keepers` (
  `keeper_domain` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `keeper_id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `keeper_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `keeper_avatar` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `badge_domain` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `badge_id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `badge_owner_domain` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `badge_owner_id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `create_time` bigint(20) NOT NULL,
  `sort_order` int(11) NOT NULL,
  PRIMARY KEY (`keeper_id`,`keeper_domain`,`badge_id`,`badge_domain`),
  KEY `badge_keepers_idx_1` (`keeper_id`,`keeper_domain`),
  KEY `badge_keepers_idx_2` (`badge_id`,`badge_domain`),
  KEY `badge_keepers_idx_3` (`badge_owner_id`,`badge_owner_domain`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- w6s-settings
ALTER TABLE `domain_settings` ADD `badge_settings` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;
UPDATE `domain_settings` SET `badge_settings` = '{}' where `badge_settings` is null or `badge_settings` = '';



-- 20221229
CREATE TABLE `user_statuses` (
  `domain_id` varchar(48) COLLATE utf8mb4_unicode_ci NOT NULL,
  `status_id` varchar(48) COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_domain` varchar(48) COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_id` varchar(48) COLLATE utf8mb4_unicode_ci NOT NULL,
  `icon` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `title` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `reply` text COLLATE utf8mb4_unicode_ci,
  `system_reply` text COLLATE utf8mb4_unicode_ci,
  `reply_mode` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `replies` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `suggestion` tinyint(1) NOT NULL,
  `due_time` bigint(20) DEFAULT NULL,
  `source_domain` varchar(48) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `source_id` varchar(48) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `agents` text COLLATE utf8mb4_unicode_ci,
  `agent_desc` text COLLATE utf8mb4_unicode_ci,
  `disabled` tinyint(1) NOT NULL,
  `system` tinyint(1) NOT NULL,
  `create_time` bigint(20) NOT NULL,
  `status_time` bigint(20) NOT NULL,
  PRIMARY KEY (`status_id`),
  KEY `user_statuses_idx1` (`user_id`,`user_domain`),
  KEY `user_statuses_idx2` (`user_id`,`source_id`),
  KEY `user_statuses_idx3` (`source_id`,`source_domain`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 20230106
-- w6s-settings
ALTER TABLE `domain_settings` ADD COLUMN `todo_settings` text NOT NULL;
UPDATE `domain_settings` SET `todo_settings` = '{}';

-- 20230109
-- w6s-settings
ALTER TABLE `domain_settings` MODIFY COLUMN `user_settings` VARCHAR(250) NOT NULL;



-- 20230111
-- w6s-settings
CREATE TABLE `bot_channel_scopes` (
  `domain_id` varchar(48) COLLATE utf8mb4_unicode_ci NOT NULL,
  `bot_id` varchar(48) COLLATE utf8mb4_unicode_ci NOT NULL,
  `owner_domain` varchar(48) COLLATE utf8mb4_unicode_ci NOT NULL,
  `owner` varchar(48) COLLATE utf8mb4_unicode_ci NOT NULL,
  `path` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL,
  `refresh_time` bigint(20) NOT NULL,
  `_id` varchar(48) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`_id`),
  KEY `bot_channel_scopes_index_1` (`bot_id`,`domain_id`),
  KEY `bot_channel_scopes_index_2` (`path`,`domain_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `bot_channel_settings` (
  `domain_id` varchar(48) COLLATE utf8mb4_unicode_ci NOT NULL,
  `channel_id` varchar(48) COLLATE utf8mb4_unicode_ci NOT NULL,
  `provider` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `settings` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `create_time` bigint(20) NOT NULL,
  `modify_time` bigint(20) NOT NULL,
  PRIMARY KEY (`channel_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `bot_channels` (
  `domain_id` varchar(48) COLLATE utf8mb4_unicode_ci NOT NULL,
  `bot_id` varchar(48) COLLATE utf8mb4_unicode_ci NOT NULL,
  `owner_domain` varchar(48) COLLATE utf8mb4_unicode_ci NOT NULL,
  `owner` varchar(48) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `icon` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
  `channel_settings` varchar(48) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `en_mame` text COLLATE utf8mb4_unicode_ci,
  `tw_name` text COLLATE utf8mb4_unicode_ci,
  `desc` text COLLATE utf8mb4_unicode_ci,
  `en_desc` text COLLATE utf8mb4_unicode_ci,
  `tw_desc` text COLLATE utf8mb4_unicode_ci,
  `scopes` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `settings` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `sort_order` int(11) NOT NULL,
  `pinned` tinyint(1) NOT NULL,
  `disabled` tinyint(1) NOT NULL,
  `create_time` bigint(20) NOT NULL,
  `modify_time` bigint(20) NOT NULL,
  PRIMARY KEY (`bot_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 20230112
-- w6s-badges
ALTER TABLE `org_settings` ADD COLUMN `badge_settings` text COLLATE utf8mb4_unicode_ci NOT NULL;
UPDATE `org_settings` SET badge_settings = '{}' where `badge_settings` is null or `badge_settings` = '';

-- 20230210
ALTER TABLE `users` ADD COLUMN `badge` VARCHAR(200);
ALTER TABLE `user_trashes` ADD COLUMN `badge` VARCHAR(200);
ALTER TABLE `org_employees` ADD COLUMN `badge` VARCHAR(200);
ALTER TABLE `org_employee_trashes` ADD COLUMN `badge` VARCHAR(200);

-- 20230302
ALTER TABLE users add column birthday_date varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL;
ALTER TABLE user_trashes add column birthday_date varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL;
update users set birthday_date = DATE_FORMAT(DATE_ADD(FROM_UNIXTIME(0),INTERVAL birthday * 1000 SECOND_MICROSECOND), '%m-%d') where birthday is not null;
update user_trashes set birthday_date = DATE_FORMAT(DATE_ADD(FROM_UNIXTIME(0),INTERVAL birthday * 1000 SECOND_MICROSECOND), '%m-%d') where birthday is not null;

-- 20230303
-- org admin add calendar scope
ALTER TABLE org_admins add column (`calendar_scope` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL);
UPDATE org_admins set calendar_scope = 'all';


-- 20230306
CREATE TABLE `user_status_settings` (
  `domain_id` varchar(48) COLLATE utf8mb4_unicode_ci NOT NULL,
  `status_id` varchar(48) COLLATE utf8mb4_unicode_ci NOT NULL,
  `icon` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `title` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `en_title` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `tw_title` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reply` text COLLATE utf8mb4_unicode_ci,
  `en_reply` text COLLATE utf8mb4_unicode_ci,
  `tw_reply` text COLLATE utf8mb4_unicode_ci,
  `suggestion` tinyint(1) NOT NULL,
  `sort_order` int(11) NOT NULL,
  `disabled` tinyint(1) NOT NULL,
  `refresh_time` bigint(20) NOT NULL,
  PRIMARY KEY (`domain_id`,`status_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 20230315
ALTER TABLE `agent_groups` ADD COLUMN `history_refresh_time` bigint(20) DEFAULT -1;
ALTER TABLE `agents` ADD COLUMN `history_refresh_time` bigint(20) DEFAULT -1;


-- 20230331
ALTER TABLE `org_employee_view_scopes` ADD COLUMN `scope_type` varchar(20) DEFAULT 'view';
ALTER TABLE `org_employee_view_scopes` ADD COLUMN `include_senior` tinyint(1) DEFAULT 0;

ALTER TABLE `org_employee_views` DROP COLUMN `ranks`;
ALTER TABLE `org_employee_views` ADD COLUMN `scope_with_senior` tinyint(1) DEFAULT 0;
ALTER TABLE `org_employee_views` ADD COLUMN `chat_scope_with_senior` tinyint(1) DEFAULT 0;
ALTER TABLE `org_employee_views` ADD COLUMN `nodes` text;
ALTER TABLE `org_employee_views` ADD COLUMN `chat_nodes` text;
ALTER TABLE `org_employee_views` ADD COLUMN `assign_nodes` text;
UPDATE `org_employee_views` SET `nodes` = '[]';
UPDATE `org_employee_views` SET `chat_nodes` = '[]';
UPDATE `org_employee_views` SET `assign_nodes` = '[]';

UPDATE `org_employee_views` SET `type` = 'common', `scope_with_senior` = true, `chat_scope_with_senior` = true WHERE `type` = 'special';
UPDATE `org_employee_view_scopes` SET `view_type` = 'common', `include_senior` = true WHERE `view_type` = 'special';



-- 20230407
-- APP ICON
CREATE TABLE `client_icons` (
  `id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `domain_id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `icon_code` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `media_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `start_time` bigint(20) NOT NULL,
  `start_hint` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `start_en_hint` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `start_tw_hint` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `end_time` bigint(20) DEFAULT NULL,
  `end_hint` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `end_en_hint` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `end_tw_hint` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `remarks` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `create_time` bigint(20) NOT NULL,
  `modify_time` bigint(20) NOT NULL,
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  `operator_id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `client_icon_idx1` (`start_time`,`end_time`,`domain_id`),
  KEY `client_icon_idx2` (`end_time`,`domain_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `client_icon_scopes` (
  `domain_id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `icon_id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `path` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `client_icon_scopes_idx_1` (`icon_id`,`domain_id`),
  KEY `client_icon_scopes_idx_2` (`path`,`icon_id`,`domain_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- app search keywords
CREATE TABLE `app_keywords` (
  `id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `app_id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `domain_id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `app_owner_id` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `app_keywords_idx_1` (`app_id`,`app_owner_id`,`domain_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 以下sql根据实际情况选择执行: app由“可发送必读消息”概念调整为“必读信息号/选读信息号”
-- UPDATE apps SET push_collapsed = NOT push_collapsed


-- 20230419
ALTER TABLE `users` ADD COLUMN `en_name` varchar(200);
ALTER TABLE `users` ADD COLUMN `tw_name` varchar(200);


-- 20230522
ALTER TABLE `apn_hw_channels` ADD COLUMN `noisy_channel` varchar(255);
ALTER TABLE `apn_hw_channels` ADD COLUMN `quietly_channel` varchar(255);
ALTER TABLE `apn_mi_channels` ADD COLUMN `noisy_channel` varchar(255);
ALTER TABLE `apn_mi_channels` ADD COLUMN `quietly_channel` varchar(255);
ALTER TABLE `apn_oppo_channels` ADD COLUMN `noisy_channel` varchar(255);
ALTER TABLE `apn_oppo_channels` ADD COLUMN `quietly_channel` varchar(255);
ALTER TABLE `apn_vivo_channels` ADD COLUMN `noisy_channel` varchar(255);
ALTER TABLE `apn_vivo_channels` ADD COLUMN `quietly_channel` varchar(255);
ALTER TABLE `apn_meizu_channels` ADD COLUMN `noisy_channel` varchar(255);
ALTER TABLE `apn_meizu_channels` ADD COLUMN `quietly_channel` varchar(255);

-- 20221111
-- w6s-broker
ALTER TABLE `message_conversation_settings` ADD COLUMN `alias` varchar(200);

-- 20230104
-- w6s-broker
CREATE TABLE `message_plan_block_scopes` (
  `plan_id` varchar(48) COLLATE utf8mb4_unicode_ci NOT NULL,
  `path` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `id` varchar(48) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `message_plan_block_scopes_idx_1` (`plan_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- w6s-broker
-- 20230107 如果message_plans表数据过大，会长时间锁表。
ALTER TABLE `message_plans` ADD COLUMN `suggestion_only` tinyint(1) DEFAULT '0';


-- 20240514
-- 流程大师新增表
CREATE TABLE ACT_SWF_PROCESS_SETTING(
  ID_ varchar(255) NOT NULL,
  TYPE_ varchar(64) DEFAULT NULL,
  CONTENT_ mediumtext,
  PROCESS_DEF_KEY_ varchar(64) DEFAULT NULL,
  VERSION_ID_ varchar(64) DEFAULT NULL,
  TENANT_ID_ varchar(64) DEFAULT NULL,
  DOMAIN_ID_ varchar(64) DEFAULT NULL,
  CREATE_DATE_ timestamp NULL DEFAULT NULL,
  UPDATE_DATE_ timestamp NULL DEFAULT NULL,
  PRIMARY KEY (ID_),
  KEY ACT_SWF_PROCESS_SETTING_PRO_DEF_KEY (PROCESS_DEF_KEY_) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;