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
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
| CREATE TABLE IF NOT EXISTS `sys_user` (
| `id` BIGINT NOT NULL AUTO_INCREMENT,
| `username` VARCHAR(64) NOT NULL,
| `password` VARCHAR(255) NOT NULL,
| `nickname` VARCHAR(64) NULL,
| `real_name` VARCHAR(64) NULL COMMENT '人员名称',
| `phone` VARCHAR(20) NULL COMMENT '联系电话',
| `remark` VARCHAR(255) NULL COMMENT '备注',
| `status` TINYINT NOT NULL DEFAULT 1,
| `deleted` TINYINT(1) NOT NULL DEFAULT 0,
| `last_login_at` DATETIME NULL COMMENT '最后登录时间',
| `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`),
| UNIQUE KEY `uk_sys_user_username` (`username`),
| UNIQUE KEY `uk_sys_user_phone` (`phone`),
| KEY `idx_sys_user_status` (`status`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='后台帐号表';
|
| CREATE TABLE IF NOT EXISTS `sys_role` (
| `id` BIGINT NOT NULL AUTO_INCREMENT,
| `role_name` VARCHAR(64) NOT NULL COMMENT '角色名称',
| `role_code` VARCHAR(64) NOT NULL COMMENT '角色编码',
| `status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态：1启用 0禁用',
| `remark` VARCHAR(255) DEFAULT NULL COMMENT '备注',
| `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`),
| UNIQUE KEY `uk_sys_role_name` (`role_name`),
| UNIQUE KEY `uk_sys_role_code` (`role_code`),
| KEY `idx_sys_role_status` (`status`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='后台角色表';
|
| CREATE TABLE IF NOT EXISTS `sys_menu` (
| `id` BIGINT NOT NULL AUTO_INCREMENT,
| `parent_id` BIGINT NOT NULL DEFAULT 0 COMMENT '父级ID，0表示根节点',
| `name` VARCHAR(64) NOT NULL COMMENT '权限/菜单名称',
| `menu_code` VARCHAR(64) NOT NULL COMMENT '菜单编码',
| `type` CHAR(1) NOT NULL COMMENT '类型：M模块 C菜单 B按钮',
| `path` VARCHAR(128) DEFAULT NULL COMMENT '路由路径',
| `component` VARCHAR(128) DEFAULT NULL COMMENT '前端组件',
| `icon` VARCHAR(64) DEFAULT NULL COMMENT '图标',
| `sort` INT NOT NULL DEFAULT 0 COMMENT '排序值',
| `status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态：1启用 0禁用',
| `visible` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否显示：1显示 0隐藏',
| `permission_key` VARCHAR(128) DEFAULT NULL COMMENT '权限标识',
| `remark` VARCHAR(255) DEFAULT NULL COMMENT '备注',
| `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`),
| UNIQUE KEY `uk_sys_menu_code` (`menu_code`),
| KEY `idx_sys_menu_parent_id` (`parent_id`),
| KEY `idx_sys_menu_type` (`type`),
| KEY `idx_sys_menu_status` (`status`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='后台菜单与权限表';
|
| CREATE TABLE IF NOT EXISTS `sys_user_role` (
| `id` BIGINT NOT NULL AUTO_INCREMENT,
| `user_id` BIGINT NOT NULL COMMENT '帐号ID',
| `role_id` BIGINT NOT NULL COMMENT '角色ID',
| `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
| PRIMARY KEY (`id`),
| UNIQUE KEY `uk_sys_user_role` (`user_id`, `role_id`),
| KEY `idx_sys_user_role_role_id` (`role_id`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='帐号角色关联表';
|
| CREATE TABLE IF NOT EXISTS `sys_role_menu` (
| `id` BIGINT NOT NULL AUTO_INCREMENT,
| `role_id` BIGINT NOT NULL COMMENT '角色ID',
| `menu_id` BIGINT NOT NULL COMMENT '菜单/权限ID',
| `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
| PRIMARY KEY (`id`),
| UNIQUE KEY `uk_sys_role_menu` (`role_id`, `menu_id`),
| KEY `idx_sys_role_menu_menu_id` (`menu_id`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色菜单权限关联表';
|
| INSERT IGNORE INTO `sys_role` (`id`, `role_name`, `role_code`, `status`, `remark`, `deleted`, `created_by`, `updated_by`)
| VALUES (1, '超级管理员', 'SUPER_ADMIN', 1, '默认拥有全部后台权限', 0, '系统初始化', '系统初始化');
|
| INSERT IGNORE INTO `sys_menu`
| (`id`, `parent_id`, `name`, `menu_code`, `type`, `path`, `component`, `icon`, `sort`, `status`, `visible`, `permission_key`, `remark`, `deleted`)
| VALUES
| (1000, 0, '系统与安全', 'system_security', 'M', '/system', NULL, 'setting', 100, 1, 1, NULL, '系统与安全模块', 0),
| (1100, 1000, '帐号管理', 'system_account', 'C', '/system/account', 'Account/UserList', NULL, 10, 1, 1, 'system:account:view', '帐号管理菜单', 0),
| (1101, 1100, '新增帐号', 'system_account_add', 'B', NULL, NULL, NULL, 1, 1, 1, 'system:account:add', '帐号新增按钮', 0),
| (1102, 1100, '编辑帐号', 'system_account_edit', 'B', NULL, NULL, NULL, 2, 1, 1, 'system:account:edit', '帐号编辑按钮', 0),
| (1103, 1100, '启用禁用', 'system_account_status', 'B', NULL, NULL, NULL, 3, 1, 1, 'system:account:status', '帐号状态按钮', 0),
| (1104, 1100, '重置密码', 'system_account_reset_password', 'B', NULL, NULL, NULL, 4, 1, 1, 'system:account:reset-password', '重置密码按钮', 0),
| (1105, 1100, '删除帐号', 'system_account_delete', 'B', NULL, NULL, NULL, 5, 1, 1, 'system:account:delete', '删除帐号按钮', 0),
| (1200, 1000, '角色管理', 'system_role', 'C', '/system/role', 'Account/RoleList', NULL, 20, 1, 1, 'system:role:view', '角色管理菜单', 0),
| (1201, 1200, '角色详情', 'system_role_detail', 'B', NULL, NULL, NULL, 1, 1, 1, 'system:role:detail', '角色详情按钮', 0),
| (1202, 1200, '新增角色', 'system_role_add', 'B', NULL, NULL, NULL, 2, 1, 1, 'system:role:add', '角色新增按钮', 0),
| (1203, 1200, '编辑角色', 'system_role_edit', 'B', NULL, NULL, NULL, 3, 1, 1, 'system:role:edit', '角色编辑按钮', 0),
| (1204, 1200, '删除角色', 'system_role_delete', 'B', NULL, NULL, NULL, 4, 1, 1, 'system:role:delete', '角色删除按钮', 0),
| (1300, 1000, '系统设置', 'system_setting_group', 'M', '/system/settings', NULL, NULL, 30, 1, 1, NULL, '系统设置目录', 0),
| (1310, 1300, '积分设置', 'system_points_settings', 'C', '/system/settings/points', 'System/PointsSettings', NULL, 10, 1, 1, 'system:points-settings:view', '积分设置菜单', 0),
| (1311, 1310, '编辑积分设置', 'system_points_settings_edit', 'B', NULL, NULL, NULL, 1, 1, 1, 'system:points-settings:edit', '积分设置编辑按钮', 0),
| (2000, 0, '用户与会员', 'member_root', 'M', '/member', NULL, 'team', 200, 1, 1, NULL, '用户与会员模块', 0),
| (2100, 2000, '用户', 'member_user_group', 'M', '/member/user', NULL, NULL, 10, 1, 1, NULL, '用户分组菜单', 0),
| (2110, 2100, '用户管理', 'member_user_manage', 'C', '/member/user/manage', 'Member/UserList', NULL, 10, 1, 1, 'member:user:view', '用户管理菜单', 0),
| (2118, 2100, '诊所管理', 'member_clinic_manage', 'C', '/member/user/clinic', 'Member/ClinicList', NULL, 11, 1, 1, 'member:clinic:view', '诊所管理菜单', 0),
| (2119, 2100, '会员用户', 'member_only_user_manage', 'C', '/member/user/member-users', 'Member/MemberUserList', NULL, 12, 1, 1, 'member:user:member:view', '会员用户菜单', 0),
| (2200, 2000, '会员', 'member_vip_group', 'M', '/member/vip', NULL, NULL, 20, 1, 1, NULL, '会员分组菜单', 0),
| (2210, 2200, '会员价格设置', 'member_vip_price_settings', 'C', '/member/vip/price', 'Member/VipPriceSettings', NULL, 10, 1, 1, 'member:vip-price:view', '会员价格设置菜单', 0),
| (2211, 2210, '编辑会员价格设置', 'member_vip_price_settings_edit', 'B', NULL, NULL, NULL, 1, 1, 1, 'member:vip-price:edit', '会员价格设置编辑按钮', 0),
| (2220, 2200, '会员介绍设置', 'member_vip_intro_settings', 'C', '/member/vip/intro', 'Member/VipIntroSettings', NULL, 20, 1, 1, 'member:vip-intro:view', '会员介绍设置菜单', 0),
| (2221, 2220, '编辑会员介绍设置', 'member_vip_intro_settings_edit', 'B', NULL, NULL, NULL, 1, 1, 1, 'member:vip-intro:edit', '会员介绍设置编辑按钮', 0),
| (2111, 2110, '用户详情', 'member_user_detail', 'B', NULL, NULL, NULL, 1, 1, 1, 'member:user:detail', '用户详情按钮', 0),
| (2112, 2110, '编辑用户', 'member_user_edit', 'B', NULL, NULL, NULL, 2, 1, 1, 'member:user:edit', '编辑用户按钮', 0),
| (2113, 2110, '启用禁用用户', 'member_user_status', 'B', NULL, NULL, NULL, 3, 1, 1, 'member:user:status', '启用禁用用户按钮', 0),
| (2114, 2110, '重置用户密码', 'member_user_reset_password', 'B', NULL, NULL, NULL, 4, 1, 1, 'member:user:reset-password', '重置用户密码按钮', 0),
| (2115, 2110, '删除用户', 'member_user_delete', 'B', NULL, NULL, NULL, 5, 1, 1, 'member:user:delete', '删除用户按钮', 0),
| (2116, 2110, '导出用户', 'member_user_export', 'B', NULL, NULL, NULL, 6, 1, 1, 'member:user:export', '导出用户按钮', 0),
| (2117, 2110, '解绑微信', 'member_user_unbind_wechat', 'B', NULL, NULL, NULL, 7, 1, 1, 'member:user:unbind-wechat', '解绑微信按钮', 0),
| (3000, 0, '课程与学习', 'live_root', 'M', '/live', NULL, 'read', 300, 1, 1, NULL, '课程与学习模块', 0),
| (3050, 3000, '课程管理', 'live_course_manage_root', 'M', '/live/course', NULL, NULL, 10, 1, 1, NULL, '课程管理目录', 0),
| (3060, 3050, '养身直播', 'live_course_broadcast_manage', 'C', '/live/course/broadcast', 'Live/BroadcastList', NULL, 10, 1, 1, 'live:course:view', '养身直播菜单', 0),
| (3061, 3060, '直播详情', 'live_course_detail', 'B', NULL, NULL, NULL, 1, 1, 1, 'live:course:detail', '养身直播详情按钮', 0),
| (3062, 3060, '编辑直播', 'live_course_edit', 'B', NULL, NULL, NULL, 2, 1, 1, 'live:course:edit', '养身直播编辑按钮', 0),
| (3063, 3060, '启用禁用直播', 'live_course_status', 'B', NULL, NULL, NULL, 3, 1, 1, 'live:course:status', '养身直播状态按钮', 0),
| (3070, 3060, '场次管理', 'live_session_manage', 'B', NULL, NULL, NULL, 10, 1, 1, 'live:session:detail', '直播场次管理权限', 0),
| (3071, 3060, '新增场次', 'live_session_add', 'B', NULL, NULL, NULL, 11, 1, 1, 'live:session:add', '直播场次新增按钮', 0),
| (3072, 3060, '编辑场次', 'live_session_edit', 'B', NULL, NULL, NULL, 12, 1, 1, 'live:session:edit', '直播场次编辑按钮', 0),
| (3073, 3060, '开播场次', 'live_session_start', 'B', NULL, NULL, NULL, 13, 1, 1, 'live:session:start', '直播场次开播按钮', 0),
| (3074, 3060, '结束场次', 'live_session_end', 'B', NULL, NULL, NULL, 14, 1, 1, 'live:session:end', '直播场次结束按钮', 0),
| (3075, 3060, '复制场次', 'live_session_copy', 'B', NULL, NULL, NULL, 15, 1, 1, 'live:session:copy', '直播场次复制按钮', 0),
| (3076, 3060, '删除场次', 'live_session_delete', 'B', NULL, NULL, NULL, 16, 1, 1, 'live:session:delete', '直播场次删除按钮', 0),
| (3077, 3060, '推送开播提醒', 'live_session_push_notification', 'B', NULL, NULL, NULL, 17, 1, 1, 'live:session:push-notification', '直播场次推送提醒按钮', 0),
| (3080, 3050, '直播预约记录', 'live_reserve_view', 'C', '/live/course/reserve-records', 'Live/ReserveRecords', NULL, 20, 1, 1, 'live:reserve:view', '直播预约记录菜单', 0),
| (3081, 3050, '直播观看记录', 'live_watch_view', 'C', '/live/course/watch-records', 'Live/WatchRecords', NULL, 21, 1, 1, 'live:watch:view', '直播观看记录菜单', 0),
| (3090, 3050, '分类管理', 'live_category_manage', 'C', '/live/course/category', 'Live/CategoryList', NULL, 22, 1, 1, 'live:category:view', '分类管理菜单', 0),
| (3091, 3090, '新增分类', 'live_category_add', 'B', NULL, NULL, NULL, 1, 1, 1, 'live:category:add', '分类新增按钮', 0),
| (3092, 3090, '编辑分类', 'live_category_edit', 'B', NULL, NULL, NULL, 2, 1, 1, 'live:category:edit', '分类编辑按钮', 0),
| (3093, 3090, '删除分类', 'live_category_delete', 'B', NULL, NULL, NULL, 3, 1, 1, 'live:category:delete', '分类删除按钮', 0),
| (3082, 3060, '答题记录', 'live_quiz_view', 'B', NULL, NULL, NULL, 22, 1, 1, 'live:quiz:view', '直播答题记录查看权限', 0),
| (3100, 3050, '授课专家管理', 'live_expert_manage', 'C', '/live/course/expert', 'Live/ExpertList', NULL, 30, 1, 1, 'live:expert:view', '授课专家管理菜单', 0),
| (3101, 3100, '新增授课专家', 'live_expert_add', 'B', NULL, NULL, NULL, 1, 1, 1, 'live:expert:add', '授课专家新增按钮', 0),
| (3102, 3100, '编辑授课专家', 'live_expert_edit', 'B', NULL, NULL, NULL, 2, 1, 1, 'live:expert:edit', '授课专家编辑按钮', 0),
| (3103, 3100, '启用禁用授课专家', 'live_expert_status', 'B', NULL, NULL, NULL, 3, 1, 1, 'live:expert:status', '授课专家状态按钮', 0),
| (3104, 3100, '删除授课专家', 'live_expert_delete', 'B', NULL, NULL, NULL, 4, 1, 1, 'live:expert:delete', '授课专家删除按钮', 0),
| (3110, 3050, '养身课程', 'live_health_course_manage', 'C', '/live/course/health', 'Live/HealthCourseList', NULL, 40, 1, 1, 'live:health-course:view', '养身课程菜单', 0),
| (3111, 3110, '养身课程详情', 'live_health_course_detail', 'B', NULL, NULL, NULL, 1, 1, 1, 'live:health-course:detail', '养身课程详情按钮', 0),
| (3112, 3110, '新增养身课程', 'live_health_course_add', 'B', NULL, NULL, NULL, 2, 1, 1, 'live:health-course:add', '养身课程新增按钮', 0),
| (3113, 3110, '编辑养身课程', 'live_health_course_edit', 'B', NULL, NULL, NULL, 3, 1, 1, 'live:health-course:edit', '养身课程编辑按钮', 0),
| (3114, 3110, '删除养身课程', 'live_health_course_delete', 'B', NULL, NULL, NULL, 4, 1, 1, 'live:health-course:delete', '养身课程删除按钮', 0),
| (3115, 3110, '启用禁用养身课程', 'live_health_course_status', 'B', NULL, NULL, NULL, 5, 1, 1, 'live:health-course:status', '养身课程状态按钮', 0),
| (3116, 3110, '导出养身课程', 'live_health_course_export', 'B', NULL, NULL, NULL, 6, 1, 1, 'live:health-course:export', '养身课程导出按钮', 0),
| (3117, 3110, '新增视频课程', 'live_health_course_video_add', 'B', NULL, NULL, NULL, 7, 1, 1, 'live:health-course:video:add', '养身课程视频新增按钮', 0),
| (3118, 3110, '编辑视频课程', 'live_health_course_video_edit', 'B', NULL, NULL, NULL, 8, 1, 1, 'live:health-course:video:edit', '养身课程视频编辑按钮', 0),
| (3119, 3110, '删除视频课程', 'live_health_course_video_delete', 'B', NULL, NULL, NULL, 9, 1, 1, 'live:health-course:video:delete', '养身课程视频删除按钮', 0),
| (4000, 0, '交易与财务', 'trade_root', 'M', '/trade', NULL, 'moneyCollect', 400, 1, 1, NULL, '交易与财务模块', 0),
| (4100, 4000, '订单', 'trade_order_group', 'M', '/trade/order', NULL, NULL, 10, 1, 1, NULL, '订单目录', 0),
| (4110, 4100, '积分兑换', 'trade_points_exchange', 'C', '/trade/order/points-exchange', 'Trade/PointsExchange', NULL, 10, 1, 1, 'points:exchange:view', '积分兑换菜单', 0),
| (4111, 4110, '兑换详情', 'trade_points_exchange_detail', 'B', NULL, NULL, NULL, 1, 1, 1, 'points:exchange:detail', '积分兑换详情按钮', 0),
| (4112, 4110, '积分兑换发货', 'trade_points_exchange_ship', 'B', NULL, NULL, NULL, 2, 1, 1, 'points:exchange:ship', '积分兑换发货按钮', 0),
| (4113, 4110, '积分兑换删除', 'trade_points_exchange_delete', 'B', NULL, NULL, NULL, 3, 1, 1, 'points:exchange:delete', '积分兑换删除按钮', 0),
| (4114, 4110, '积分兑换导出', 'trade_points_exchange_export', 'B', NULL, NULL, NULL, 4, 1, 1, 'points:exchange:export', '积分兑换导出按钮', 0),
| (4200, 4000, '财务流水', 'trade_finance_flow_group', 'M', '/trade/flow', NULL, NULL, 20, 1, 1, NULL, '财务流水目录', 0),
| (4210, 4200, '积分收支明细', 'trade_points_ledger', 'C', '/trade/flow/points-ledger', 'Trade/PointsLedger', NULL, 10, 1, 1, 'points:ledger:view', '积分收支明细菜单', 0),
| (4211, 4210, '导出积分收支明细', 'trade_points_ledger_export', 'B', NULL, NULL, NULL, 1, 1, 1, 'points:ledger:export', '积分收支明细导出按钮', 0);
|
| INSERT IGNORE INTO `sys_role_menu` (`role_id`, `menu_id`)
| VALUES
| (1, 1000), (1, 1100), (1, 1101), (1, 1102), (1, 1103), (1, 1104), (1, 1105),
| (1, 1200), (1, 1201), (1, 1202), (1, 1203), (1, 1204), (1, 1300), (1, 1310), (1, 1311),
| (1, 2000), (1, 2100), (1, 2110), (1, 2111), (1, 2112), (1, 2113), (1, 2114), (1, 2115), (1, 2116), (1, 2117), (1, 2118), (1, 2119),
| (1, 2200), (1, 2210), (1, 2211), (1, 2220), (1, 2221),
| (1, 3000), (1, 3050), (1, 3060), (1, 3061), (1, 3062), (1, 3063),
| (1, 3070), (1, 3071), (1, 3072), (1, 3073), (1, 3074), (1, 3075), (1, 3076), (1, 3077),
| (1, 3080), (1, 3081), (1, 3082), (1, 3090), (1, 3091), (1, 3092), (1, 3093),
| (1, 3100), (1, 3101), (1, 3102), (1, 3103), (1, 3104),
| (1, 3110), (1, 3111), (1, 3112), (1, 3113), (1, 3114), (1, 3115), (1, 3116), (1, 3117), (1, 3118), (1, 3119),
| (1, 4000), (1, 4100), (1, 4110), (1, 4111), (1, 4112), (1, 4113), (1, 4114),
| (1, 4200), (1, 4210), (1, 4211);
|
| -- ----------------------------
| -- 养生直播：专家（最小）
| -- ----------------------------
| CREATE TABLE IF NOT EXISTS `tcm_expert` (
| `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
| `name` VARCHAR(64) NOT NULL,
| `avatar` VARCHAR(255) DEFAULT NULL,
| `specialty` VARCHAR(500) DEFAULT NULL COMMENT '擅长领域',
| `intro` TEXT DEFAULT NULL COMMENT '专家介绍',
| `status` TINYINT NOT NULL DEFAULT 1,
| `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_expert_status` (`status`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='授课专家表';
|
| INSERT IGNORE INTO `tcm_expert` (`id`, `name`, `avatar`, `specialty`, `intro`, `status`, `deleted`)
| VALUES (10, '王丽梅', 'https://example.com/avatar/expert-10.png', '中医体质调理、四季养生', '<p>擅长中医养生、体质辨识与日常调理指导。</p>', 1, 0);
|
| -- ----------------------------
| -- 用户：诊所
| -- ----------------------------
| CREATE TABLE IF NOT EXISTS `tcm_clinic` (
| `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
| `name` VARCHAR(128) NOT NULL COMMENT '诊所名称',
| `contact_name` VARCHAR(64) DEFAULT NULL COMMENT '负责人姓名',
| `contact_phone` VARCHAR(20) DEFAULT NULL COMMENT '负责人电话',
| `province_code` VARCHAR(32) DEFAULT NULL COMMENT '省编码',
| `province_name` VARCHAR(64) DEFAULT NULL COMMENT '省名称',
| `city_code` VARCHAR(32) DEFAULT NULL COMMENT '市编码',
| `city_name` VARCHAR(64) DEFAULT NULL COMMENT '市名称',
| `district_code` VARCHAR(32) DEFAULT NULL COMMENT '区编码',
| `district_name` VARCHAR(64) DEFAULT NULL COMMENT '区名称',
| `address` VARCHAR(255) DEFAULT NULL COMMENT '诊所地址',
| `business_license_url` VARCHAR(255) DEFAULT NULL COMMENT '营业执照图片',
| `status` TINYINT(1) 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_clinic_status` (`status`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='诊所表';
|
| INSERT IGNORE INTO `tcm_clinic` (`id`, `name`, `contact_name`, `contact_phone`, `province_code`, `province_name`, `city_code`, `city_name`, `district_code`, `district_name`, `address`, `business_license_url`, `status`, `deleted`)
| VALUES (1, '王太室堂中医', '王顾', '14725836901', '510000', '四川省', '510100', '成都市', '510106', '金牛区', '四川省成都市金牛区XXXXXXX', 'https://example.com/license/clinic-1.png', 1, 0);
|
| -- ----------------------------
| -- 用户：学员（C端用户）
| -- ----------------------------
| CREATE TABLE IF NOT EXISTS `tcm_app_user` (
| `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
| `nickname` VARCHAR(64) NOT NULL COMMENT '用户名称（昵称）',
| `phone` VARCHAR(20) DEFAULT NULL COMMENT '手机号（登录账号）',
| `password` VARCHAR(255) DEFAULT NULL COMMENT '登录密码（加密存储）',
| `avatar` VARCHAR(255) DEFAULT NULL COMMENT '头像URL',
| `wechat_openid` VARCHAR(128) DEFAULT NULL COMMENT '微信OpenID',
| `wechat_nickname` VARCHAR(64) DEFAULT NULL COMMENT '微信昵称',
| `wechat_avatar` VARCHAR(255) DEFAULT NULL COMMENT '微信头像URL',
| `wechat_bound_at` DATETIME DEFAULT NULL COMMENT '微信绑定时间',
| `type` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '用户类型：0个人 1诊所',
| `gender` TINYINT(1) DEFAULT NULL COMMENT '性别：0女 1男',
| `age` TINYINT UNSIGNED DEFAULT NULL COMMENT '年龄',
| `intro` VARCHAR(500) DEFAULT NULL COMMENT '个人介绍',
| `status` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '状态：1启用 0禁用',
| `clinic_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '绑定诊所ID（type=1时有值）',
| `member_expire_at` DATETIME DEFAULT NULL COMMENT '会员有效期（NULL=非会员，有值且未过期=会员）',
| `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_app_user_phone` (`phone`),
| KEY `idx_app_user_clinic` (`clinic_id`),
| KEY `idx_app_user_status` (`status`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='APP用户表（C端用户）';
|
| INSERT IGNORE INTO `tcm_app_user` (`id`, `nickname`, `phone`, `password`, `avatar`, `wechat_openid`, `wechat_nickname`, `wechat_avatar`, `wechat_bound_at`, `type`, `gender`, `age`, `intro`, `status`, `clinic_id`, `member_expire_at`, `deleted`)
| VALUES (200, '张三', '13800008888', NULL, 'https://example.com/avatar/app-user-200.png', 'wx_openid_200', '小张', 'https://example.com/avatar/wechat-200.png', CURRENT_TIMESTAMP, 1, 1, 19, 'If you find an element of your interface requires instructions, then you need to redesign it.', 1, 1, NULL, 0);
|
| -- ----------------------------
| -- 会员价格配置
| -- ----------------------------
| CREATE TABLE IF NOT EXISTS `tcm_member_price_config` (
| `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
| `month_card_price` DECIMAL(10,2) NOT NULL COMMENT '月卡会员价格',
| `month_card_days` INT NOT NULL COMMENT '月卡时长',
| `month_card_enabled` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '月卡是否启用',
| `quarter_card_price` DECIMAL(10,2) NOT NULL COMMENT '季卡会员价格',
| `quarter_card_days` INT NOT NULL COMMENT '季卡时长',
| `quarter_card_enabled` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '季卡是否启用',
| `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`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='会员价格配置表';
|
| INSERT IGNORE INTO `tcm_member_price_config`
| (`id`, `month_card_price`, `month_card_days`, `month_card_enabled`, `quarter_card_price`, `quarter_card_days`, `quarter_card_enabled`, `deleted`)
| VALUES
| (1, 69.00, 30, 1, 199.00, 90, 1, 0);
|
| -- ----------------------------
| -- 会员介绍配置
| -- ----------------------------
| CREATE TABLE IF NOT EXISTS `tcm_member_intro_config` (
| `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
| `module_count` INT NOT NULL DEFAULT 0 COMMENT '模块数量',
| `description` TEXT DEFAULT NULL COMMENT '会员权益说明',
| `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`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='会员介绍配置主表';
|
| CREATE TABLE IF NOT EXISTS `tcm_member_intro_item` (
| `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
| `config_id` BIGINT UNSIGNED NOT NULL COMMENT '配置ID',
| `image_url` VARCHAR(255) NOT NULL COMMENT '权益图标',
| `description` VARCHAR(16) NOT NULL COMMENT '权益名称',
| `sort` INT NOT NULL DEFAULT 1 COMMENT '排序',
| `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_member_intro_item_config_sort` (`config_id`, `sort`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='会员介绍图文模块表';
|
| INSERT IGNORE INTO `tcm_member_intro_config`
| (`id`, `module_count`, `description`, `deleted`)
| VALUES
| (1, 2, '开通会员后可享受会员专属权益与精选内容服务。', 0);
|
| INSERT IGNORE INTO `tcm_member_intro_item`
| (`id`, `config_id`, `image_url`, `description`, `sort`, `deleted`)
| VALUES
| (1, 1, 'https://dummyimage.com/120x120/1677ff/ffffff&text=折扣', '折扣', 1, 0),
| (2, 1, 'https://dummyimage.com/120x120/5b8ff9/ffffff&text=专区', '专区', 2, 0);
|
| -- ----------------------------
| -- 养生直播：课程
| -- ----------------------------
| CREATE TABLE IF NOT EXISTS `tcm_live_course_category` (
| `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '分类ID',
| `type` VARCHAR(32) NOT NULL COMMENT '分类类型：LEVEL/DIFFICULTY',
| `name` VARCHAR(32) NOT NULL COMMENT '分类名称',
| `sort` INT NOT NULL DEFAULT 0 COMMENT '排序值',
| `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_type_sort` (`type`, `sort`),
| KEY `idx_type_name` (`type`, `name`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='直播课程分类表';
|
| INSERT IGNORE INTO `tcm_live_course_category` (`id`, `type`, `name`, `sort`, `deleted`)
| VALUES
| (1, 'LEVEL', '初级', 10, 0),
| (2, 'LEVEL', '中级', 20, 0),
| (3, 'LEVEL', '高级', 30, 0),
| (4, 'DIFFICULTY', '简单', 10, 0),
| (5, 'DIFFICULTY', '普通', 20, 0),
| (6, 'DIFFICULTY', '困难', 30, 0);
|
| -- ----------------------------
| -- 养身课程
| -- ----------------------------
| CREATE TABLE IF NOT EXISTS `tcm_health_course` (
| `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '养身课程ID',
| `title` VARCHAR(128) NOT NULL COMMENT '课程名称',
| `level_category_id` BIGINT UNSIGNED NOT NULL COMMENT '养身等级分类ID',
| `difficulty_category_id` BIGINT UNSIGNED NOT NULL COMMENT '难度分类ID',
| `expert_id` BIGINT UNSIGNED NOT NULL COMMENT '授课专家ID',
| `sale_price` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '课程售价',
| `summary` VARCHAR(500) DEFAULT NULL COMMENT '课程简介',
| `target_users` VARCHAR(255) DEFAULT NULL COMMENT '适用人群',
| `cover_img` VARCHAR(255) DEFAULT NULL COMMENT '课程封面图URL',
| `banner_imgs` JSON DEFAULT NULL COMMENT '课程宣传图URL列表（多图）',
| `content` LONGTEXT DEFAULT NULL COMMENT '课程介绍（富文本）',
| `base_sales_count` INT NOT NULL DEFAULT 0 COMMENT '基础销量',
| `real_sales_count` INT NOT NULL DEFAULT 0 COMMENT '真实销量',
| `learn_user_count` INT NOT NULL DEFAULT 0 COMMENT '累计学习人数',
| `favorite_count` INT NOT NULL DEFAULT 0 COMMENT '收藏量',
| `total_duration_seconds` INT NOT NULL DEFAULT 0 COMMENT '课程总时长（秒）',
| `video_count` INT NOT NULL DEFAULT 0 COMMENT '视频小节数',
| `status` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '状态：1启用 0禁用',
| `is_home_recommend` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '首页推荐：1是 0否',
| `sort` INT NOT NULL DEFAULT 0 COMMENT '排序值',
| `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_health_course_level` (`level_category_id`),
| KEY `idx_health_course_difficulty` (`difficulty_category_id`),
| KEY `idx_health_course_expert` (`expert_id`),
| KEY `idx_health_course_status` (`status`),
| KEY `idx_health_course_home_recommend` (`is_home_recommend`),
| KEY `idx_health_course_created_at` (`created_at`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='养身课程表';
|
| INSERT IGNORE INTO `tcm_health_course`
| (`id`, `title`, `level_category_id`, `difficulty_category_id`, `expert_id`, `sale_price`, `summary`, `target_users`, `cover_img`, `banner_imgs`, `content`, `base_sales_count`, `real_sales_count`, `learn_user_count`, `favorite_count`, `total_duration_seconds`, `video_count`, `status`, `is_home_recommend`, `sort`, `deleted`)
| VALUES
| (1, '中医针灸资格证', 1, 4, 10, 599.80, '面向针灸入门与基础提升的系统课程。', '针灸初学者、养身爱好者',
| 'https://example.com/health/course-1-cover.png',
| JSON_ARRAY('https://example.com/health/course-1-banner-1.png', 'https://example.com/health/course-1-banner-2.png', 'https://example.com/health/course-1-banner-3.png'),
| '<p>课程介绍：覆盖针灸基础理论、常见穴位与实操思路。</p>',
| 120, 33, 358, 35, 1118, 5, 1, 1, 10, 0);
|
| CREATE TABLE IF NOT EXISTS `tcm_health_course_video` (
| `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '视频小节ID',
| `course_id` BIGINT UNSIGNED NOT NULL COMMENT '所属养身课程ID',
| `chapter_name` VARCHAR(128) NOT NULL COMMENT '小节名称',
| `chapter_remark` VARCHAR(500) DEFAULT NULL COMMENT '小节备注',
| `video_url` VARCHAR(512) DEFAULT NULL COMMENT '视频地址',
| `duration_seconds` INT NOT NULL DEFAULT 0 COMMENT '视频时长（秒）',
| `sale_type` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '售价类型：0免费 1收费',
| `sale_price` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '小节售价',
| `play_count` INT NOT NULL DEFAULT 0 COMMENT '播放次数',
| `sort` INT NOT NULL DEFAULT 0 COMMENT '排序值',
| `status` TINYINT(1) 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_health_course_video_course_sort` (`course_id`, `sort`),
| KEY `idx_health_course_video_status` (`course_id`, `status`),
| KEY `idx_health_course_video_sale_type` (`sale_type`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='养身课程视频小节表';
|
| INSERT IGNORE INTO `tcm_health_course_video`
| (`id`, `course_id`, `chapter_name`, `chapter_remark`, `video_url`, `duration_seconds`, `sale_type`, `sale_price`, `play_count`, `sort`, `status`, `deleted`)
| VALUES
| (1, 1, '方剂学', '赠送添加视频课程页，需自动传入课程', 'https://example.com/health/course-1-video-1.mp4', 238, 0, 0.00, 358, 10, 1, 0),
| (2, 1, '药剂学', '系统课程配套讲解', 'https://example.com/health/course-1-video-2.mp4', 238, 1, 5999.99, 358, 20, 1, 0),
| (3, 1, '方剂学进阶', '面向基础学习后的专题补充', 'https://example.com/health/course-1-video-3.mp4', 642, 1, 5999.99, 358, 30, 1, 0);
|
| CREATE TABLE IF NOT EXISTS `tcm_live_course` (
| `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '直播课程ID',
| `title` VARCHAR(128) NOT NULL COMMENT '课程名称',
| `level` VARCHAR(32) DEFAULT NULL COMMENT '课程级别：初级/中级/高级',
| `expert_id` BIGINT UNSIGNED NOT NULL COMMENT '授课专家ID',
| `cover_img` VARCHAR(255) DEFAULT NULL COMMENT '课程封面图URL',
| `banner_imgs` JSON DEFAULT NULL COMMENT '课程宣传图URL列表（多图）',
| `intro` LONGTEXT DEFAULT NULL COMMENT '课程介绍（富文本）',
| `target_users` VARCHAR(255) DEFAULT NULL COMMENT '适用人群',
| `quiz_delay_minutes` INT NOT NULL DEFAULT 0 COMMENT '可答题时间：直播开始N分钟后解锁答题',
| `quiz_points` INT NOT NULL DEFAULT 0 COMMENT '答题可得积分',
| `status` TINYINT(1) 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_expert` (`expert_id`),
| KEY `idx_status` (`status`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='养生直播课程表';
|
| INSERT IGNORE INTO `tcm_live_course`
| (`id`, `title`, `level`, `expert_id`, `cover_img`, `banner_imgs`, `intro`, `target_users`, `quiz_delay_minutes`, `quiz_points`, `status`, `deleted`)
| VALUES
| (1, '养生直播课程1', '初级', 10,
| 'https://example.com/live/course-1-cover.png',
| JSON_ARRAY('https://example.com/live/course-1-banner-1.png', 'https://example.com/live/course-1-banner-2.png'),
| '<p>课程介绍：养生直播课程1（初始化数据）</p>',
| '零基础/入门学员',
| 30, 2, 1, 0),
| (2, '养生直播课程2', '中级', 10,
| 'https://example.com/live/course-2-cover.png',
| JSON_ARRAY('https://example.com/live/course-2-banner-1.png', 'https://example.com/live/course-2-banner-2.png'),
| '<p>课程介绍：养生直播课程2（初始化数据）</p>',
| '有一定基础学员',
| 30, 2, 1, 0);
|
| -- ----------------------------
| -- 养生直播：场次
| -- ----------------------------
| CREATE TABLE IF NOT EXISTS `tcm_live_session` (
| `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '场次ID',
| `live_course_id` BIGINT UNSIGNED NOT NULL COMMENT '所属直播课程ID',
| `start_time` DATETIME DEFAULT NULL COMMENT '开始直播时间',
| `video_url` VARCHAR(512) DEFAULT NULL COMMENT '视频地址',
| `base_reserve_count` INT NOT NULL DEFAULT 0 COMMENT '基础预约量（初始刷基数）',
| `base_watch_count` INT NOT NULL DEFAULT 0 COMMENT '基础观看量（初始刷基数）',
| `real_reserve_count` INT NOT NULL DEFAULT 0 COMMENT '真实预约人数',
| `real_watch_count` INT NOT NULL DEFAULT 0 COMMENT '真实观看人数',
| `reserve_convert_rate` DECIMAL(5,2) NOT NULL DEFAULT 0.00 COMMENT '预约转化率（%）',
| `completion_rate` DECIMAL(5,2) NOT NULL DEFAULT 0.00 COMMENT '完播率（%）',
| `effective_watch_rate` DECIMAL(5,2) NOT NULL DEFAULT 0.00 COMMENT '有效观看率（%）',
| `quiz_correct_count` INT NOT NULL DEFAULT 0 COMMENT '答题正确人数',
| `points_issued` INT NOT NULL DEFAULT 0 COMMENT '累计发放积分',
| `live_status` TINYINT NOT NULL DEFAULT 0 COMMENT '上课状态：0未开始 1直播中 2已结束',
| `actual_start_time` DATETIME DEFAULT NULL COMMENT '实际开播时间',
| `actual_end_time` DATETIME DEFAULT NULL COMMENT '实际结束时间',
| `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_live_course` (`live_course_id`),
| KEY `idx_start_time` (`start_time`),
| KEY `idx_live_status` (`live_status`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='直播场次表';
|
| -- ----------------------------
| -- 养生直播：场次题目（内嵌）
| -- ----------------------------
| CREATE TABLE IF NOT EXISTS `tcm_live_session_question` (
| `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '题目ID',
| `session_id` BIGINT UNSIGNED NOT NULL COMMENT '所属场次ID',
| `sort` TINYINT NOT NULL DEFAULT 1 COMMENT '题目序号：1/2/3',
| `stem` TEXT NOT NULL COMMENT '题干内容',
| `option_a` VARCHAR(256) NOT NULL COMMENT '选项A内容',
| `option_b` VARCHAR(256) NOT NULL COMMENT '选项B内容',
| `option_c` VARCHAR(256) NOT NULL COMMENT '选项C内容',
| `correct_option` CHAR(1) NOT NULL COMMENT '正确答案：A/B/C',
| `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`),
| UNIQUE KEY `uk_session_sort_deleted` (`session_id`, `sort`, `deleted`),
| KEY `idx_session` (`session_id`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='直播场次内嵌题目表';
|
| -- ----------------------------
| -- 养生直播：预约记录
| -- ----------------------------
| CREATE TABLE IF NOT EXISTS `tcm_live_reserve` (
| `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
| `live_course_id` BIGINT UNSIGNED NOT NULL COMMENT '直播课程ID',
| `session_id` BIGINT UNSIGNED NOT NULL COMMENT '场次ID',
| `app_user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
| `reserve_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '预约时间',
| `is_cancel` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否取消：0否 1是',
| PRIMARY KEY (`id`),
| UNIQUE KEY `uk_session_app_user` (`session_id`, `app_user_id`),
| KEY `idx_live_course_reserve` (`live_course_id`),
| KEY `idx_app_user_reserve` (`app_user_id`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='直播预约记录表';
|
| -- ----------------------------
| -- 养生直播：观看记录
| -- ----------------------------
| CREATE TABLE IF NOT EXISTS `tcm_live_watch_record` (
| `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
| `live_course_id` BIGINT UNSIGNED NOT NULL COMMENT '直播课程ID',
| `session_id` BIGINT UNSIGNED NOT NULL COMMENT '场次ID',
| `app_user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
| `watch_start` DATETIME NOT NULL COMMENT '开始观看时间',
| `watch_end` DATETIME DEFAULT NULL COMMENT '结束观看时间',
| `duration_seconds` INT NOT NULL DEFAULT 0 COMMENT '累计观看时长（秒）',
| `is_completed` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否完播：1是 0否',
| `is_effective` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否有效观看：1是 0否',
| PRIMARY KEY (`id`),
| KEY `idx_session_watch` (`session_id`),
| KEY `idx_app_user_watch` (`app_user_id`),
| KEY `idx_live_course_watch` (`live_course_id`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='直播观看记录表';
|
| -- ----------------------------
| -- 养生直播：答题记录
| -- ----------------------------
| CREATE TABLE IF NOT EXISTS `tcm_live_session_quiz_record` (
| `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
| `session_id` BIGINT UNSIGNED NOT NULL COMMENT '场次ID',
| `app_user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
| `question_id` BIGINT UNSIGNED NOT NULL COMMENT '题目ID',
| `chosen_option` CHAR(1) NOT NULL COMMENT '学员所选答案：A/B/C',
| `is_correct` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否答对：1是 0否',
| `points_earned` INT NOT NULL DEFAULT 0 COMMENT '本题获得积分',
| `answered_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '答题时间',
| PRIMARY KEY (`id`),
| UNIQUE KEY `uk_session_app_user_question` (`session_id`, `app_user_id`, `question_id`),
| KEY `idx_session_quiz` (`session_id`),
| KEY `idx_app_user_quiz` (`app_user_id`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='场次答题记录表';
|
| -- ----------------------------
| -- 积分设置
| -- ----------------------------
| CREATE TABLE IF NOT EXISTS `tcm_points_config` (
| `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
| `activity_start_at` DATETIME NOT NULL COMMENT '活动开始时间',
| `activity_end_at` DATETIME NOT NULL COMMENT '活动结束时间',
| `gift_cover` VARCHAR(255) NOT NULL COMMENT '礼品封面图',
| `gift_name` VARCHAR(64) NOT NULL COMMENT '礼品名称',
| `required_points` INT NOT NULL COMMENT '所需积分',
| `settlement_status` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '结算状态：0待结算 1已结算',
| `settled_at` DATETIME DEFAULT NULL COMMENT '结算时间',
| `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_points_config_end` (`activity_end_at`),
| KEY `idx_points_config_status` (`settlement_status`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='积分设置表';
|
| INSERT IGNORE INTO `tcm_points_config`
| (`id`, `activity_start_at`, `activity_end_at`, `gift_cover`, `gift_name`, `required_points`, `settlement_status`, `settled_at`, `deleted`)
| VALUES
| (1, '2026-03-01 00:00:00', '2026-03-31 23:59:59', 'https://example.com/points/gift-cover.png', '艾灸养生礼盒', 100, 0, NULL, 0);
|
| -- ----------------------------
| -- 用户积分账户
| -- ----------------------------
| CREATE TABLE IF NOT EXISTS `tcm_user_points_account` (
| `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
| `app_user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
| `points_balance` INT NOT NULL DEFAULT 0 COMMENT '当前积分余额',
| `last_settled_config_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '最后一次结算配置ID',
| `last_cleared_at` DATETIME DEFAULT NULL COMMENT '最后一次清零时间',
| `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`),
| UNIQUE KEY `uk_points_account_user` (`app_user_id`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户积分账户表';
|
| INSERT IGNORE INTO `tcm_user_points_account`
| (`id`, `app_user_id`, `points_balance`, `last_settled_config_id`, `last_cleared_at`, `deleted`)
| VALUES
| (1, 200, 120, NULL, NULL, 0);
|
| -- ----------------------------
| -- 积分兑换汇总订单
| -- ----------------------------
| CREATE TABLE IF NOT EXISTS `tcm_points_exchange_order` (
| `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
| `order_no` VARCHAR(32) NOT NULL COMMENT '汇总订单编号',
| `config_id` BIGINT UNSIGNED NOT NULL COMMENT '积分配置ID',
| `gift_cover` VARCHAR(255) DEFAULT NULL COMMENT '礼品封面图',
| `gift_name` VARCHAR(64) NOT NULL COMMENT '礼品名称',
| `required_points` INT NOT NULL COMMENT '所需积分',
| `activity_start_at` DATETIME NOT NULL COMMENT '活动开始时间',
| `activity_end_at` DATETIME NOT NULL COMMENT '活动结束时间',
| `clinic_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '诊所ID',
| `clinic_app_user_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '诊所用户ID',
| `clinic_name` VARCHAR(128) DEFAULT NULL COMMENT '诊所名称快照',
| `receiver_name` VARCHAR(64) DEFAULT NULL COMMENT '收件人姓名',
| `receiver_phone` VARCHAR(20) DEFAULT NULL COMMENT '收件人电话',
| `receiver_address` VARCHAR(255) DEFAULT NULL COMMENT '收货地址',
| `item_count` INT NOT NULL DEFAULT 0 COMMENT '兑换人数',
| `total_points` INT NOT NULL DEFAULT 0 COMMENT '汇总积分',
| `status` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '状态：0待发货 1已发货 2已收货',
| `tracking_no` VARCHAR(32) DEFAULT NULL COMMENT '物流单号',
| `shipped_at` DATETIME DEFAULT NULL COMMENT '发货时间',
| `completed_at` DATETIME DEFAULT NULL COMMENT '完成时间',
| `cancelled_at` DATETIME DEFAULT NULL COMMENT '取消时间',
| `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`),
| UNIQUE KEY `uk_points_exchange_order_no` (`order_no`),
| KEY `idx_points_exchange_order_config` (`config_id`),
| KEY `idx_points_exchange_order_clinic_app_user_id` (`clinic_app_user_id`),
| KEY `idx_points_exchange_order_status` (`status`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='积分兑换汇总订单表';
|
| -- ----------------------------
| -- 积分兑换明细订单
| -- ----------------------------
| CREATE TABLE IF NOT EXISTS `tcm_points_exchange_order_item` (
| `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
| `order_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '汇总订单ID',
| `order_no` VARCHAR(32) NOT NULL COMMENT '明细订单编号',
| `app_user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
| `user_nickname` VARCHAR(64) DEFAULT NULL COMMENT '用户昵称快照',
| `user_phone` VARCHAR(20) DEFAULT NULL COMMENT '用户电话快照',
| `clinic_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '诊所ID',
| `clinic_name` VARCHAR(128) DEFAULT NULL COMMENT '诊所名称快照',
| `province_code` VARCHAR(32) DEFAULT NULL COMMENT '省编码快照',
| `province_name` VARCHAR(64) DEFAULT NULL COMMENT '省名称快照',
| `city_code` VARCHAR(32) DEFAULT NULL COMMENT '市编码快照',
| `city_name` VARCHAR(64) DEFAULT NULL COMMENT '市名称快照',
| `district_code` VARCHAR(32) DEFAULT NULL COMMENT '区编码快照',
| `district_name` VARCHAR(64) DEFAULT NULL COMMENT '区名称快照',
| `settled_points` INT NOT NULL DEFAULT 0 COMMENT '结算前积分',
| `status` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '状态：0待发货 1待取货 2兑换完成',
| `tracking_no` VARCHAR(32) DEFAULT NULL COMMENT '物流单号',
| `shipped_at` DATETIME DEFAULT NULL COMMENT '发货时间',
| `completed_at` DATETIME DEFAULT NULL COMMENT '完成时间',
| `cancelled_at` DATETIME DEFAULT NULL COMMENT '取消时间',
| `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`),
| UNIQUE KEY `uk_points_exchange_item_order_no` (`order_no`),
| KEY `idx_points_exchange_item_order_id` (`order_id`),
| KEY `idx_points_exchange_item_user_id` (`app_user_id`),
| KEY `idx_points_exchange_item_status` (`status`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='积分兑换明细订单表';
|
| -- ----------------------------
| -- 积分收支流水
| -- ----------------------------
| CREATE TABLE IF NOT EXISTS `tcm_points_ledger` (
| `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
| `app_user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
| `user_nickname` VARCHAR(64) DEFAULT NULL COMMENT '用户昵称快照',
| `user_phone` VARCHAR(20) DEFAULT NULL COMMENT '用户电话快照',
| `clinic_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '诊所ID',
| `clinic_name` VARCHAR(128) DEFAULT NULL COMMENT '诊所名称快照',
| `province_code` VARCHAR(32) DEFAULT NULL COMMENT '省编码快照',
| `province_name` VARCHAR(64) DEFAULT NULL COMMENT '省名称快照',
| `city_code` VARCHAR(32) DEFAULT NULL COMMENT '市编码快照',
| `city_name` VARCHAR(64) DEFAULT NULL COMMENT '市名称快照',
| `district_code` VARCHAR(32) DEFAULT NULL COMMENT '区编码快照',
| `district_name` VARCHAR(64) DEFAULT NULL COMMENT '区名称快照',
| `income_expense_type` VARCHAR(32) NOT NULL COMMENT '收支类型：INCOME/EXPENSE',
| `business_type` VARCHAR(32) NOT NULL COMMENT '业务类型：LIVE_WATCH/POINTS_EXCHANGE',
| `content_name` VARCHAR(128) DEFAULT NULL COMMENT '内容名称',
| `reference_type` VARCHAR(32) DEFAULT NULL COMMENT '引用类型',
| `reference_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '引用ID',
| `reference_no` VARCHAR(64) DEFAULT NULL COMMENT '引用编号',
| `points_delta` INT NOT NULL DEFAULT 0 COMMENT '积分变动值',
| `operated_at` DATETIME NOT NULL COMMENT '操作时间',
| `source_type` VARCHAR(32) NOT NULL COMMENT '源类型',
| `source_id` BIGINT UNSIGNED NOT NULL COMMENT '源数据ID',
| `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`),
| UNIQUE KEY `uk_points_ledger_source` (`source_type`, `source_id`),
| KEY `idx_points_ledger_user` (`app_user_id`),
| KEY `idx_points_ledger_operated_at` (`operated_at`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='积分收支流水表';
|
|
