-- =========================================
-- UMS 会员管理模块（yudao-module-ums）
-- 建表 + 字典数据 + BPM 分类 + 菜单
-- =========================================

-- ----- 1. 入会申请单表 -----
DROP TABLE IF EXISTS `ums_application`;
CREATE TABLE `ums_application` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
  `no` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '申请编号',
  `member_type` tinyint NOT NULL COMMENT '会员类型：1-个人 2-单位',
  `applicant_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '申请人姓名（个人）或单位名称（单位）',
  `contact_phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '联系电话',
  `contact_email` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '邮箱',
  `legal_person` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '法定代表人（单位专有）',
  `organization_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '统一社会信用代码（单位专有）',
  `research_direction` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '研究方向',
  `work_unit` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '工作单位（个人专有）',
  `title` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '职称（个人专有）',
  `member_grade` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '申请等级',
  `region_code` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '地区编码',
  `industry` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '行业',
  `status` tinyint NOT NULL DEFAULT '0' COMMENT '状态：0-草稿 15-审批中 20-已通过 35-已驳回',
  `process_instance_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT 'BPM 流程实例 ID',
  `remark` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '备注',
  `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '创建者',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '更新者',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_no` (`no`) USING BTREE,
  KEY `idx_status` (`status`) USING BTREE,
  KEY `idx_member_type` (`member_type`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='会员-入会申请单';

-- ----- 2. 会员表 -----
DROP TABLE IF EXISTS `ums_member`;
CREATE TABLE `ums_member` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
  `no` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '会员编号',
  `user_id` bigint DEFAULT NULL COMMENT '关联系统用户 ID',
  `application_id` bigint DEFAULT NULL COMMENT '来源申请单 ID',
  `member_type` tinyint NOT NULL COMMENT '会员类型：1-个人 2-单位',
  `member_grade` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '会员等级',
  `name` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '姓名（个人）或单位名称（单位）',
  `contact_user` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '联系人（单位用）',
  `contact_phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '联系电话',
  `contact_email` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '邮箱',
  `legal_person` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '法定代表人（单位专有）',
  `organization_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '统一社会信用代码',
  `research_direction` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '研究方向',
  `work_unit` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '工作单位（个人专有）',
  `title` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '职称（个人专有）',
  `unit_intro` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '单位简介（单位专有）',
  `region_code` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '地区编码',
  `industry` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '行业',
  `join_date` date DEFAULT NULL COMMENT '入会日期',
  `expire_date` date DEFAULT NULL COMMENT '有效期至',
  `status` tinyint NOT NULL DEFAULT '1' COMMENT '状态：1-正常 0-暂停',
  `remark` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '备注',
  `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '创建者',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '更新者',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_no` (`no`) USING BTREE,
  KEY `idx_user_id` (`user_id`) USING BTREE,
  KEY `idx_region_code` (`region_code`) USING BTREE,
  KEY `idx_industry` (`industry`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='会员';

-- ----- 3. 字典数据 -----

-- ums_application_status（入会申请状态）
DELETE FROM system_dict_type WHERE `type` = 'ums_application_status';
INSERT INTO system_dict_type (id, name, type, status, remark, creator, create_time, updater, update_time, deleted)
VALUES (80001, '入会申请状态', 'ums_application_status', 0, '入会申请审批状态', '1', NOW(), '1', NOW(), b'0');
DELETE FROM system_dict_data WHERE dict_type = 'ums_application_status';
INSERT INTO system_dict_data (id, sort, label, value, dict_type, status, color_type, remark, creator, create_time, updater, update_time, deleted)
VALUES (8000101, 1, '草稿', '0', 'ums_application_status', 0, 'default', '', '1', NOW(), '1', NOW(), b'0');
INSERT INTO system_dict_data (id, sort, label, value, dict_type, status, color_type, remark, creator, create_time, updater, update_time, deleted)
VALUES (8000102, 2, '审批中', '15', 'ums_application_status', 0, 'processing', '', '1', NOW(), '1', NOW(), b'0');
INSERT INTO system_dict_data (id, sort, label, value, dict_type, status, color_type, remark, creator, create_time, updater, update_time, deleted)
VALUES (8000103, 3, '已通过', '20', 'ums_application_status', 0, 'success', '', '1', NOW(), '1', NOW(), b'0');
INSERT INTO system_dict_data (id, sort, label, value, dict_type, status, color_type, remark, creator, create_time, updater, update_time, deleted)
VALUES (8000104, 4, '已驳回', '35', 'ums_application_status', 0, 'danger', '', '1', NOW(), '1', NOW(), b'0');

-- ums_member_type（会员类型）
DELETE FROM system_dict_type WHERE `type` = 'ums_member_type';
INSERT INTO system_dict_type (id, name, type, status, remark, creator, create_time, updater, update_time, deleted)
VALUES (80002, '会员类型', 'ums_member_type', 0, '个人会员/单位会员', '1', NOW(), '1', NOW(), b'0');
DELETE FROM system_dict_data WHERE dict_type = 'ums_member_type';
INSERT INTO system_dict_data (id, sort, label, value, dict_type, status, color_type, remark, creator, create_time, updater, update_time, deleted)
VALUES (8000201, 1, '个人会员', '1', 'ums_member_type', 0, 'default', '', '1', NOW(), '1', NOW(), b'0');
INSERT INTO system_dict_data (id, sort, label, value, dict_type, status, color_type, remark, creator, create_time, updater, update_time, deleted)
VALUES (8000202, 2, '单位会员', '2', 'ums_member_type', 0, 'default', '', '1', NOW(), '1', NOW(), b'0');

-- ums_member_status（会员状态）
DELETE FROM system_dict_type WHERE `type` = 'ums_member_status';
INSERT INTO system_dict_type (id, name, type, status, remark, creator, create_time, updater, update_time, deleted)
VALUES (80003, '会员状态', 'ums_member_status', 0, '会员正常/暂停', '1', NOW(), '1', NOW(), b'0');
DELETE FROM system_dict_data WHERE dict_type = 'ums_member_status';
INSERT INTO system_dict_data (id, sort, label, value, dict_type, status, color_type, remark, creator, create_time, updater, update_time, deleted)
VALUES (8000301, 1, '正常', '1', 'ums_member_status', 0, 'success', '', '1', NOW(), '1', NOW(), b'0');
INSERT INTO system_dict_data (id, sort, label, value, dict_type, status, color_type, remark, creator, create_time, updater, update_time, deleted)
VALUES (8000302, 2, '暂停', '0', 'ums_member_status', 0, 'warning', '', '1', NOW(), '1', NOW(), b'0');

-- ums_personal_grade（个人会员等级）
DELETE FROM system_dict_type WHERE `type` = 'ums_personal_grade';
INSERT INTO system_dict_type (id, name, type, status, remark, creator, create_time, updater, update_time, deleted)
VALUES (80004, '个人会员等级', 'ums_personal_grade', 0, '个人会员级别', '1', NOW(), '1', NOW(), b'0');
DELETE FROM system_dict_data WHERE dict_type = 'ums_personal_grade';
INSERT INTO system_dict_data (id, sort, label, value, dict_type, status, color_type, remark, creator, create_time, updater, update_time, deleted)
VALUES (8000401, 1, '普通会员', '1', 'ums_personal_grade', 0, 'default', '', '1', NOW(), '1', NOW(), b'0');
INSERT INTO system_dict_data (id, sort, label, value, dict_type, status, color_type, remark, creator, create_time, updater, update_time, deleted)
VALUES (8000402, 2, '理事', '2', 'ums_personal_grade', 0, 'processing', '', '1', NOW(), '1', NOW(), b'0');
INSERT INTO system_dict_data (id, sort, label, value, dict_type, status, color_type, remark, creator, create_time, updater, update_time, deleted)
VALUES (8000403, 3, '常务理事', '3', 'ums_personal_grade', 0, 'processing', '', '1', NOW(), '1', NOW(), b'0');
INSERT INTO system_dict_data (id, sort, label, value, dict_type, status, color_type, remark, creator, create_time, updater, update_time, deleted)
VALUES (8000404, 4, '副理事长', '4', 'ums_personal_grade', 0, 'warning', '', '1', NOW(), '1', NOW(), b'0');
INSERT INTO system_dict_data (id, sort, label, value, dict_type, status, color_type, remark, creator, create_time, updater, update_time, deleted)
VALUES (8000405, 5, '理事长', '5', 'ums_personal_grade', 0, 'danger', '', '1', NOW(), '1', NOW(), b'0');

-- ums_unit_grade（单位会员等级）
DELETE FROM system_dict_type WHERE `type` = 'ums_unit_grade';
INSERT INTO system_dict_type (id, name, type, status, remark, creator, create_time, updater, update_time, deleted)
VALUES (80005, '单位会员等级', 'ums_unit_grade', 0, '单位会员级别', '1', NOW(), '1', NOW(), b'0');
DELETE FROM system_dict_data WHERE dict_type = 'ums_unit_grade';
INSERT INTO system_dict_data (id, sort, label, value, dict_type, status, color_type, remark, creator, create_time, updater, update_time, deleted)
VALUES (8000501, 1, '普通单位', '1', 'ums_unit_grade', 0, 'default', '', '1', NOW(), '1', NOW(), b'0');
INSERT INTO system_dict_data (id, sort, label, value, dict_type, status, color_type, remark, creator, create_time, updater, update_time, deleted)
VALUES (8000502, 2, '理事单位', '2', 'ums_unit_grade', 0, 'processing', '', '1', NOW(), '1', NOW(), b'0');
INSERT INTO system_dict_data (id, sort, label, value, dict_type, status, color_type, remark, creator, create_time, updater, update_time, deleted)
VALUES (8000503, 3, '副会长单位', '3', 'ums_unit_grade', 0, 'warning', '', '1', NOW(), '1', NOW(), b'0');
INSERT INTO system_dict_data (id, sort, label, value, dict_type, status, color_type, remark, creator, create_time, updater, update_time, deleted)
VALUES (8000504, 4, '会长单位', '4', 'ums_unit_grade', 0, 'danger', '', '1', NOW(), '1', NOW(), b'0');

-- ums_industry（行业）
DELETE FROM system_dict_type WHERE `type` = 'ums_industry';
INSERT INTO system_dict_type (id, name, type, status, remark, creator, create_time, updater, update_time, deleted)
VALUES (80006, '会员行业', 'ums_industry', 0, '学会会员所属行业', '1', NOW(), '1', NOW(), b'0');
DELETE FROM system_dict_data WHERE dict_type = 'ums_industry';
INSERT INTO system_dict_data (id, sort, label, value, dict_type, status, color_type, remark, creator, create_time, updater, update_time, deleted)
VALUES (8000601, 1, '智能制造', '1', 'ums_industry', 0, 'default', '', '1', NOW(), '1', NOW(), b'0');
INSERT INTO system_dict_data (id, sort, label, value, dict_type, status, color_type, remark, creator, create_time, updater, update_time, deleted)
VALUES (8000602, 2, '新能源', '2', 'ums_industry', 0, 'default', '', '1', NOW(), '1', NOW(), b'0');
INSERT INTO system_dict_data (id, sort, label, value, dict_type, status, color_type, remark, creator, create_time, updater, update_time, deleted)
VALUES (8000603, 3, '电子信息', '3', 'ums_industry', 0, 'default', '', '1', NOW(), '1', NOW(), b'0');
INSERT INTO system_dict_data (id, sort, label, value, dict_type, status, color_type, remark, creator, create_time, updater, update_time, deleted)
VALUES (8000604, 4, '生物医药', '4', 'ums_industry', 0, 'default', '', '1', NOW(), '1', NOW(), b'0');
INSERT INTO system_dict_data (id, sort, label, value, dict_type, status, color_type, remark, creator, create_time, updater, update_time, deleted)
VALUES (8000605, 5, '新材料', '5', 'ums_industry', 0, 'default', '', '1', NOW(), '1', NOW(), b'0');
INSERT INTO system_dict_data (id, sort, label, value, dict_type, status, color_type, remark, creator, create_time, updater, update_time, deleted)
VALUES (8000606, 6, '节能环保', '6', 'ums_industry', 0, 'default', '', '1', NOW(), '1', NOW(), b'0');
INSERT INTO system_dict_data (id, sort, label, value, dict_type, status, color_type, remark, creator, create_time, updater, update_time, deleted)
VALUES (8000607, 7, '咨询服务', '7', 'ums_industry', 0, 'default', '', '1', NOW(), '1', NOW(), b'0');
INSERT INTO system_dict_data (id, sort, label, value, dict_type, status, color_type, remark, creator, create_time, updater, update_time, deleted)
VALUES (8000608, 8, '其他', '8', 'ums_industry', 0, 'default', '', '1', NOW(), '1', NOW(), b'0');

-- ----- 4. BPM 分类 -----
DELETE FROM bpm_category WHERE `code` = 'ums_membership_approve';
INSERT INTO bpm_category (id, name, code, description, status, sort, creator, create_time, updater, update_time, deleted)
VALUES (8001, '入会审批', 'ums_membership_approve', '会员入会申请两级审批（秘书处初审→理事会审议）', 0, 1, '1', NOW(), '1', NOW(), b'0');

-- ----- 5. 菜单 -----
-- 注意：菜单 ID 不要与现有菜单冲突
-- 顶级目录（type=1），parent_id=0，图标 ep:user
DELETE FROM system_menu WHERE id BETWEEN 1075490 AND 1075503;
INSERT INTO system_menu (id, name, permission, type, sort, parent_id, path, icon, component, component_name, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
VALUES (1075490, '会员管理', '', 1, 80, 0, '/ums', 'ep:user', '', '', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');

-- 入会申请（菜单，type=2）
INSERT INTO system_menu (id, name, permission, type, sort, parent_id, path, icon, component, component_name, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
VALUES (1075491, '入会申请', '', 2, 1, 1075490, 'application', 'ep:edit', 'ums/application/index', 'UmsApplication', 0, b'1', b'1', b'0', '1', NOW(), '1', NOW(), b'0');

-- 会员管理（菜单，type=2）
INSERT INTO system_menu (id, name, permission, type, sort, parent_id, path, icon, component, component_name, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
VALUES (1075492, '会员列表', '', 2, 2, 1075490, 'member', 'ep:user-filled', 'ums/member/index', 'UmsMember', 0, b'1', b'1', b'0', '1', NOW(), '1', NOW(), b'0');

-- 会员通讯录（菜单，type=2，只读）
INSERT INTO system_menu (id, name, permission, type, sort, parent_id, path, icon, component, component_name, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
VALUES (1075493, '会员通讯录', '', 2, 3, 1075490, 'directory', 'ep:notebook', 'ums/member/directory', 'UmsMemberDirectory', 0, b'1', b'1', b'0', '1', NOW(), '1', NOW(), b'0');

-- 按钮权限（type=3）—— 入会申请
INSERT INTO system_menu (id, name, permission, type, sort, parent_id, creator, create_time, updater, update_time, deleted)
VALUES (1075494, '入会申请查询', 'ums:application:query', 3, 1, 1075491, '1', NOW(), '1', NOW(), b'0');
INSERT INTO system_menu (id, name, permission, type, sort, parent_id, creator, create_time, updater, update_time, deleted)
VALUES (1075495, '入会申请创建', 'ums:application:create', 3, 2, 1075491, '1', NOW(), '1', NOW(), b'0');
INSERT INTO system_menu (id, name, permission, type, sort, parent_id, creator, create_time, updater, update_time, deleted)
VALUES (1075496, '入会申请提交审批', 'ums:application:submit', 3, 3, 1075491, '1', NOW(), '1', NOW(), b'0');
INSERT INTO system_menu (id, name, permission, type, sort, parent_id, creator, create_time, updater, update_time, deleted)
VALUES (1075501, '入会申请更新', 'ums:application:update', 3, 4, 1075491, '1', NOW(), '1', NOW(), b'0');
INSERT INTO system_menu (id, name, permission, type, sort, parent_id, creator, create_time, updater, update_time, deleted)
VALUES (1075502, '入会申请删除', 'ums:application:delete', 3, 5, 1075491, '1', NOW(), '1', NOW(), b'0');

-- 按钮权限 —— 会员管理
INSERT INTO system_menu (id, name, permission, type, sort, parent_id, creator, create_time, updater, update_time, deleted)
VALUES (1075497, '会员查询', 'ums:member:query', 3, 1, 1075492, '1', NOW(), '1', NOW(), b'0');
INSERT INTO system_menu (id, name, permission, type, sort, parent_id, creator, create_time, updater, update_time, deleted)
VALUES (1075498, '会员创建', 'ums:member:create', 3, 2, 1075492, '1', NOW(), '1', NOW(), b'0');
INSERT INTO system_menu (id, name, permission, type, sort, parent_id, creator, create_time, updater, update_time, deleted)
VALUES (1075499, '会员更新', 'ums:member:update', 3, 3, 1075492, '1', NOW(), '1', NOW(), b'0');
INSERT INTO system_menu (id, name, permission, type, sort, parent_id, creator, create_time, updater, update_time, deleted)
VALUES (1075503, '会员删除', 'ums:member:delete', 3, 4, 1075492, '1', NOW(), '1', NOW(), b'0');

-- 按钮权限 —— 通讯录（只读）
INSERT INTO system_menu (id, name, permission, type, sort, parent_id, creator, create_time, updater, update_time, deleted)
VALUES (1075500, '通讯录查询', 'ums:member:directory', 3, 1, 1075493, '1', NOW(), '1', NOW(), b'0');

-- ----- 6. 默认部门（会员系统账号所属部门） -----
DELETE FROM system_dept WHERE id = 129;
INSERT INTO system_dept (id, name, parent_id, sort, leader_user_id, phone, email, status, creator, create_time, updater, update_time, deleted)
VALUES (129, '学会会员', 0, 99, NULL, '', '', 0, '1', NOW(), '1', NOW(), b'0');