1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
| ALTER TABLE tcm_live_course
| ADD COLUMN `password` VARCHAR(255) DEFAULT NULL COMMENT '直播间密码' AFTER `status`;
|
| CREATE TABLE tcm_provincial_agent (
| id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '代理ID，主键，自增',
| agent_name VARCHAR(50) NOT NULL COMMENT '省区负责人姓名',
| province VARCHAR(20) NOT NULL COMMENT '负责省份名称（如：四川省）',
| bank_name VARCHAR(100) COMMENT '开户银行名称（如：中国建设银行）',
| account_number VARCHAR(30) COMMENT '银行账号',
| account_holder VARCHAR(50) COMMENT '账户名称/户名（即开户人姓名或公司名称）',
| branch_name VARCHAR(100) COMMENT '开户支行名称（如：成都高新支行）',
| contact_info VARCHAR(200) COMMENT '联系信息（可填电话、邮箱、手机号等）',
| status TINYINT NOT NULL DEFAULT 1 COMMENT '状态：1启用，0禁用，2注销',
| address VARCHAR(255) COMMENT '详细地址（如：成都市高新区天府大道XX号）',
| `deleted` tinyint(1) NOT NULL DEFAULT '0',
| `created_by` varchar(64) NOT NULL DEFAULT '系统初始化' COMMENT '创建人',
| `updated_by` varchar(64) NOT NULL DEFAULT '系统初始化' COMMENT '更新人',
| `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
| `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
| ) COMMENT='省代理人员信息表，存储代理的基本资料及银行账户信息';
|
| CREATE TABLE tcm_agent_region (
| id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '记录ID，主键，自增',
| agent_id BIGINT NOT NULL COMMENT '关联省代理人员ID，外键指向 provincial_agent.id',
| region_code CHAR(6) NOT NULL COMMENT '6位行政区划编码（如：510101 表示成都市辖区）',
| region_name VARCHAR(100) COMMENT '区域名称（冗余字段，便于直接展示，如：成都市）',
| region_level TINYINT COMMENT '区域行政级别：1-省级，2-市级，3-区县级，4-乡镇级（可根据编码前缀判断）',
| created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
| UNIQUE KEY uk_agent_region (agent_id, region_code) COMMENT '联合唯一索引，防止同一代理重复添加同一区域'
| ) COMMENT='代理负责区域表，存储每个省代理所管辖的具体区域（一对多关系，一个代理可负责多个区域）';
|
| -- 为 tcm_clinic 表新增代理关联字段
| ALTER TABLE tcm_clinic
| ADD COLUMN agent_id BIGINT COMMENT '负责该诊所的省代理ID，tcm_provincial_agent.id'
| AFTER name;
|
| INSERT INTO sys_menu (id, parent_id, name, menu_code, type, path, component, sort, status, visible, permission_key, remark, deleted) VALUES
| (5046, 2100, '省代理管理', 'member_agent_manage', 'C', '/member/user/agent', 'Member/AgentList', 12, 1, 1, 'member:agent:view', '省代理管理菜单', 0),
| (5047, 5046, '新增省代理', 'member_agent_add', 'B', NULL, NULL, 1, 1, 1, 'member:agent:add', '新增省代理权限', 0),
| (5048, 5046, '编辑省代理', 'member_agent_edit', 'B', NULL, NULL, 2, 1, 1, 'member:agent:edit', '编辑省代理权限', 0),
| (5049, 5046, '删除省代理', 'member_agent_delete', 'B', NULL, NULL, 3, 1, 1, 'member:agent:delete', '删除省代理权限', 0),
| (5050, 5046, '省代理详情', 'member_agent_detail', 'B', NULL, NULL, 4, 1, 1, 'member:agent:detail', '省代理详情权限', 0);
|
|
| -- 养生直播：新增设置直播间密码按钮权限
| INSERT INTO sys_menu (id, parent_id, name, menu_code, type, path, component, icon, sort, status, visible, permission_key, remark, deleted)
| VALUES (3064, 3060, '设置直播间密码', 'live_course_password', 'B', NULL, NULL, NULL, 4, 1, 1, 'live:course:password', '设置直播间密码按钮', 0)
| ON DUPLICATE KEY UPDATE parent_id = VALUES(parent_id), name = VALUES(name),
| menu_code = VALUES(menu_code), type = VALUES(type), sort = VALUES(sort), status = VALUES(status),
| visible = VALUES(visible), permission_key = VALUES(permission_key), remark = VALUES(remark), deleted = VALUES(deleted);
|
|
| -- 为 tcm_clinic 表新增银行信息字段（全部加在 status 字段后面）
| ALTER TABLE tcm_clinic
| ADD COLUMN bank_name VARCHAR(100) COMMENT '开户银行名称（如：中国建设银行）' AFTER status,
| ADD COLUMN account_number VARCHAR(30) COMMENT '银行账号' AFTER bank_name,
| ADD COLUMN account_holder VARCHAR(50) COMMENT '账户名称/户名（即开户人姓名或公司名称）' AFTER account_number,
| ADD COLUMN branch_name VARCHAR(100) COMMENT '开户支行名称（如：成都高新支行）' AFTER account_holder;
|
| INSERT INTO sys_menu (id, parent_id, name, menu_code, type, sort, status, visible, permission_key, remark, deleted)
| VALUES (5045, 2118, '编辑', 'member_clinic_edit', 'B', 1, 1, 1, 'member:clinic:edit', '编辑诊所按钮', 0)
| ON DUPLICATE KEY UPDATE permission_key = VALUES(permission_key), deleted = 0;
|
| -- 微赞直播间单例设置：仅保留 Banner 图片地址和直播间地址两个业务字段
| CREATE TABLE IF NOT EXISTS tcm_live_room_setting (
| banner_image_url VARCHAR(500) NOT NULL COMMENT 'Banner图片地址',
| live_room_url VARCHAR(500) NULL COMMENT '直播间地址'
| ) COMMENT='微赞直播间设置';
|
| INSERT INTO sys_menu (id, parent_id, name, menu_code, type, path, component, icon, sort, status, visible, permission_key, remark, deleted)
| VALUES (6000, 3050, '微赞直播间设置', 'live_room_setting', 'C', '/live/course/live-room-setting', 'Live/LiveRoomSetting', NULL, 18, 1, 1, 'live:room-setting:view', '微赞直播间设置菜单', 0)
| ON DUPLICATE KEY UPDATE parent_id = VALUES(parent_id), name = VALUES(name), menu_code = VALUES(menu_code), type = VALUES(type), path = VALUES(path), component = VALUES(component), sort = VALUES(sort), status = VALUES(status), visible = VALUES(visible), permission_key = VALUES(permission_key), remark = VALUES(remark), deleted = 0;
|
| INSERT INTO sys_menu (id, parent_id, name, menu_code, type, path, component, icon, sort, status, visible, permission_key, remark, deleted)
| VALUES (6001, 6000, '编辑微赞直播间设置', 'live_room_setting_edit', 'B', NULL, NULL, NULL, 1, 1, 1, 'live:room-setting:edit', '微赞直播间设置编辑权限', 0)
| ON DUPLICATE KEY UPDATE parent_id = VALUES(parent_id), name = VALUES(name), menu_code = VALUES(menu_code), type = VALUES(type), sort = VALUES(sort), status = VALUES(status), visible = VALUES(visible), permission_key = VALUES(permission_key), remark = VALUES(remark), deleted = 0;
|
| -- 项目介绍菜单和权限配置
| -- 添加时间：2026-08-20
| -- 说明：为项目介绍功能添加菜单和按钮权限
|
| -- 项目介绍菜单（id: 6010）
| INSERT INTO sys_menu (id, parent_id, name, menu_code, type, path, component, icon, sort, status, visible, permission_key, remark, deleted)
| VALUES (6010, 3050, '项目介绍', 'live_project_introduction_manage', 'C', '/live/course/project-introduction', 'Live/ProjectIntroductionList', NULL, 60, 1, 1, 'live:project-introduction:view', '项目介绍菜单', 0)
| ON DUPLICATE KEY UPDATE parent_id = VALUES(parent_id), name = VALUES(name), menu_code = VALUES(menu_code), type = VALUES(type), path = VALUES(path), component = VALUES(component), sort = VALUES(sort), status = VALUES(status), visible = VALUES(visible), permission_key = VALUES(permission_key), remark = VALUES(remark), deleted = 0;
|
| -- 新增项目介绍按钮（id: 6011）
| INSERT INTO sys_menu (id, parent_id, name, menu_code, type, path, component, icon, sort, status, visible, permission_key, remark, deleted)
| VALUES (6011, 6010, '新增项目介绍', 'live_project_introduction_add', 'B', NULL, NULL, NULL, 1, 1, 1, 'live:project-introduction:add', '项目介绍新增按钮', 0)
| ON DUPLICATE KEY UPDATE parent_id = VALUES(parent_id), name = VALUES(name), menu_code = VALUES(menu_code), type = VALUES(type), sort = VALUES(sort), status = VALUES(status), visible = VALUES(visible), permission_key = VALUES(permission_key), remark = VALUES(remark), deleted = 0;
|
| -- 编辑项目介绍按钮（id: 6012）
| INSERT INTO sys_menu (id, parent_id, name, menu_code, type, path, component, icon, sort, status, visible, permission_key, remark, deleted)
| VALUES (6012, 6010, '编辑项目介绍', 'live_project_introduction_edit', 'B', NULL, NULL, NULL, 2, 1, 1, 'live:project-introduction:edit', '项目介绍编辑按钮', 0)
| ON DUPLICATE KEY UPDATE parent_id = VALUES(parent_id), name = VALUES(name), menu_code = VALUES(menu_code), type = VALUES(type), sort = VALUES(sort), status = VALUES(status), visible = VALUES(visible), permission_key = VALUES(permission_key), remark = VALUES(remark), deleted = 0;
|
| -- 删除项目介绍按钮（id: 6013）
| INSERT INTO sys_menu (id, parent_id, name, menu_code, type, path, component, icon, sort, status, visible, permission_key, remark, deleted)
| VALUES (6013, 6010, '删除项目介绍', 'live_project_introduction_delete', 'B', NULL, NULL, NULL, 3, 1, 1, 'live:project-introduction:delete', '项目介绍删除按钮', 0)
| ON DUPLICATE KEY UPDATE parent_id = VALUES(parent_id), name = VALUES(name), menu_code = VALUES(menu_code), type = VALUES(type), sort = VALUES(sort), status = VALUES(status), visible = VALUES(visible), permission_key = VALUES(permission_key), remark = VALUES(remark), deleted = 0;
|
| -- ----------------------------
| -- 项目介绍表
| -- ----------------------------
| CREATE TABLE IF NOT EXISTS `tcm_project_introduction` (
| `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
| `title` VARCHAR(128) NOT NULL COMMENT '标题',
| `cover_img` VARCHAR(512) DEFAULT NULL COMMENT '封面图',
| `category` VARCHAR(32) NOT NULL COMMENT '类别：platform(平台介绍)、mode(营销模式)、action(营销动作)',
| `content_type` TINYINT NOT NULL COMMENT '类型：1图文 2视频',
| `content` MEDIUMTEXT COMMENT '图文内容（富文本HTML）',
| `video_url` VARCHAR(512) DEFAULT NULL COMMENT '视频地址',
| `video_file_name` VARCHAR(255) DEFAULT NULL COMMENT '视频文件名',
| `video_description` VARCHAR(255) DEFAULT NULL COMMENT '视频介绍',
| `click_count` INT NOT NULL DEFAULT 0 COMMENT '点击量',
| `status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态：1启用 0禁用',
| `deleted` TINYINT(1) NOT NULL DEFAULT 0,
| `created_by` VARCHAR(64) NOT NULL DEFAULT '系统' COMMENT '创建人',
| `updated_by` VARCHAR(64) NOT NULL DEFAULT '系统' COMMENT '更新人',
| `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
| `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
| PRIMARY KEY (`id`),
| KEY `idx_category` (`category`),
| KEY `idx_content_type` (`content_type`),
| KEY `idx_status` (`status`),
| KEY `idx_created_at` (`created_at`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='项目介绍表';
|
| -- ----------------------------
| -- 项目介绍密码配置表
| -- ----------------------------
| CREATE TABLE IF NOT EXISTS `tcm_project_introduction_password` (
| `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
| `password_enabled` TINYINT NOT NULL DEFAULT 0 COMMENT '是否启用密码访问：1启用 0不启用',
| `password` VARCHAR(255) DEFAULT NULL COMMENT '访问密码（加密）',
| `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
| `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
| PRIMARY KEY (`id`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='项目介绍密码配置表';
|
| -- 项目介绍密码菜单（id: 6014）
| INSERT INTO sys_menu (id, parent_id, name, menu_code, type, path, component, icon, sort, status, visible, permission_key, remark, deleted)
| VALUES (6014, 3050, '项目介绍密码', 'live_project_introduction_password', 'C', '/live/course/project-introduction-password', 'Live/ProjectIntroductionPassword', NULL, 61, 1, 1, 'live:project-introduction-password:view', '项目介绍密码菜单', 0)
| ON DUPLICATE KEY UPDATE parent_id = VALUES(parent_id), name = VALUES(name), menu_code = VALUES(menu_code), type = VALUES(type), path = VALUES(path), component = VALUES(component), sort = VALUES(sort), status = VALUES(status), visible = VALUES(visible), permission_key = VALUES(permission_key), remark = VALUES(remark), deleted = 0;
|
| -- 编辑项目介绍密码按钮（id: 6015）
| INSERT INTO sys_menu (id, parent_id, name, menu_code, type, path, component, icon, sort, status, visible, permission_key, remark, deleted)
| VALUES (6015, 6014, '编辑项目介绍密码', 'live_project_introduction_password_edit', 'B', NULL, NULL, NULL, 1, 1, 1, 'live:project-introduction-password:edit', '项目介绍密码编辑按钮', 0)
| ON DUPLICATE KEY UPDATE parent_id = VALUES(parent_id), name = VALUES(name), menu_code = VALUES(menu_code), type = VALUES(type), sort = VALUES(sort), status = VALUES(status), visible = VALUES(visible), permission_key = VALUES(permission_key), remark = VALUES(remark), deleted = 0;
|
|
| ALTER TABLE `tcm_health_popularization`
| ADD COLUMN `video_description` VARCHAR(255) DEFAULT NULL COMMENT '视频介绍'
| AFTER `video_file_name`;
|
|
| -- ----------------------------
| -- 新增二级菜单：宣传知识（与课程管理平级，父级为课程与学习 3000）
| -- 并将「项目介绍」「项目介绍密码」菜单从课程管理(3050)迁移到宣传知识下
| -- 添加时间：2026-08-22
| -- ----------------------------
|
| -- 宣传知识菜单（id: 6100）
| INSERT INTO sys_menu (id, parent_id, name, menu_code, type, path, component, icon, sort, status, visible, permission_key, remark, deleted)
| VALUES (6100, 3000, '宣传知识', 'live_promotion_root', 'M', '/live/promotion', NULL, NULL, 20, 1, 1, NULL, '宣传知识目录', 0)
| ON DUPLICATE KEY UPDATE parent_id = VALUES(parent_id), name = VALUES(name), menu_code = VALUES(menu_code), type = VALUES(type), path = VALUES(path), component = VALUES(component), sort = VALUES(sort), status = VALUES(status), visible = VALUES(visible), permission_key = VALUES(permission_key), remark = VALUES(remark), deleted = 0;
|
| -- 将「项目介绍」(6010) 迁移到「宣传知识」(6100) 下，并将路由前缀改为 /live/promotion
| UPDATE sys_menu SET parent_id = 6100, path = '/live/promotion/project-introduction' WHERE id = 6010;
|
| -- 将「项目介绍密码」(6014) 迁移到「宣传知识」(6100) 下，并将路由前缀改为 /live/promotion
| UPDATE sys_menu SET parent_id = 6100, path = '/live/promotion/project-introduction-password' WHERE id = 6014;
|
| -- ----------------------------
| -- 「健康科普」改名为「健康知识」，并从课程管理(3050)迁移到宣传知识(6100)下
| -- 主菜单 id: 3120，子按钮 3121/3122/3123 随父级一起迁移（parent_id 不变，无需单独处理）
| -- 添加时间：2026-08-22
| -- ----------------------------
| UPDATE sys_menu
| SET parent_id = 6100,
| name = '健康知识',
| path = '/live/promotion/health-popularization',
| remark = '健康知识菜单'
| WHERE id = 3120;
|
| -- 子按钮随主菜单改名（parent_id 仍为 3120，不变）
| UPDATE sys_menu SET name = '新增健康知识', remark = '健康知识新增按钮' WHERE id = 3121;
| UPDATE sys_menu SET name = '编辑健康知识', remark = '健康知识编辑按钮' WHERE id = 3122;
| UPDATE sys_menu SET name = '删除健康知识', remark = '健康知识删除按钮' WHERE id = 3123;
|
| -- ----------------------------
| -- 新增「养生科普」菜单及按钮权限（宣传知识 6100 下，与健康知识平级）
| -- 养生科普与健康知识共用 tcm_health_popularization 表，contentType=2（仅视频）
| -- 主菜单 id: 6110，子按钮 6111/6112/6113
| -- 添加时间：2026-08-22
| -- ----------------------------
| INSERT INTO sys_menu (id, parent_id, name, menu_code, type, path, component, icon, sort, status, visible, permission_key, remark, deleted)
| VALUES (6110, 6100, '养生科普', 'live_health_promotion_manage', 'C', '/live/promotion/health-promotion', 'Live/HealthPromotionList', NULL, 51, 1, 1, 'live:health-promotion:view', '养生科普菜单', 0)
| ON DUPLICATE KEY UPDATE parent_id = VALUES(parent_id), name = VALUES(name), menu_code = VALUES(menu_code), type = VALUES(type), path = VALUES(path), component = VALUES(component), sort = VALUES(sort), status = VALUES(status), visible = VALUES(visible), permission_key = VALUES(permission_key), remark = VALUES(remark), deleted = 0;
|
| INSERT INTO sys_menu (id, parent_id, name, menu_code, type, path, component, icon, sort, status, visible, permission_key, remark, deleted)
| VALUES (6111, 6110, '新增养生科普', 'live_health_promotion_add', 'B', NULL, NULL, NULL, 1, 1, 1, 'live:health-promotion:add', '养生科普新增按钮', 0)
| ON DUPLICATE KEY UPDATE parent_id = VALUES(parent_id), name = VALUES(name), menu_code = VALUES(menu_code), type = VALUES(type), sort = VALUES(sort), status = VALUES(status), visible = VALUES(visible), permission_key = VALUES(permission_key), remark = VALUES(remark), deleted = 0;
|
| INSERT INTO sys_menu (id, parent_id, name, menu_code, type, path, component, icon, sort, status, visible, permission_key, remark, deleted)
| VALUES (6112, 6110, '编辑养生科普', 'live_health_promotion_edit', 'B', NULL, NULL, NULL, 2, 1, 1, 'live:health-promotion:edit', '养生科普编辑按钮', 0)
| ON DUPLICATE KEY UPDATE parent_id = VALUES(parent_id), name = VALUES(name), menu_code = VALUES(menu_code), type = VALUES(type), sort = VALUES(sort), status = VALUES(status), visible = VALUES(visible), permission_key = VALUES(permission_key), remark = VALUES(remark), deleted = 0;
|
| INSERT INTO sys_menu (id, parent_id, name, menu_code, type, path, component, icon, sort, status, visible, permission_key, remark, deleted)
| VALUES (6113, 6110, '删除养生科普', 'live_health_promotion_delete', 'B', NULL, NULL, NULL, 3, 1, 1, 'live:health-promotion:delete', '养生科普删除按钮', 0)
| ON DUPLICATE KEY UPDATE parent_id = VALUES(parent_id), name = VALUES(name), menu_code = VALUES(menu_code), type = VALUES(type), sort = VALUES(sort), status = VALUES(status), visible = VALUES(visible), permission_key = VALUES(permission_key), remark = VALUES(remark), deleted = 0;
|
|
