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
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
| 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 '联系电话',
| `avatar` VARCHAR(255) 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),
| (1305, 1300, '系统设置', 'system_basic_settings', 'C', '/system/settings/basic', 'System/BasicSettings', NULL, 5, 1, 1, 'system:basic-settings:view', '系统设置菜单', 0),
| (1306, 1305, '编辑系统设置', 'system_basic_settings_edit', 'B', NULL, NULL, NULL, 1, 1, 1, 'system:basic-settings:edit', '系统设置编辑按钮', 0),
| (1307, 1300, 'APP版本设置', 'system_app_version_settings', 'C', '/system/settings/app-version', 'System/AppVersionSettings', NULL, 15, 1, 1, 'system:app-version:view', 'APP版本设置菜单', 0),
| (1308, 1307, '新增APP版本', 'system_app_version_settings_add', 'B', NULL, NULL, NULL, 1, 1, 1, 'system:app-version:add', '新增APP版本按钮', 0),
| (1309, 1307, '查看APP版本', 'system_app_version_settings_detail', 'B', NULL, NULL, NULL, 2, 1, 1, 'system:app-version:detail', '查看APP版本按钮', 0),
| (1312, 1300, '消息推送配置', 'system_message_push_settings', 'C', '/system/settings/message-push', 'System/MessagePushSettings', NULL, 20, 1, 1, 'system:message-push:view', '消息推送配置菜单', 0),
| (1313, 1312, '编辑消息推送配置', 'system_message_push_settings_edit', 'B', NULL, NULL, NULL, 1, 1, 1, 'system:message-push:edit', '消息推送配置编辑按钮', 0),
| (1314, 1300, 'Banner配置', 'system_banner_settings', 'C', '/system/settings/banner', 'System/BannerSettings', NULL, 12, 1, 1, 'system:banner:view', 'Banner配置菜单', 0),
| (1315, 1314, '新增Banner', 'system_banner_settings_add', 'B', NULL, NULL, NULL, 1, 1, 1, 'system:banner:add', 'Banner新增按钮', 0),
| (1316, 1314, '编辑Banner', 'system_banner_settings_edit', 'B', NULL, NULL, NULL, 2, 1, 1, 'system:banner:edit', 'Banner编辑按钮', 0),
| (1317, 1314, '查看Banner', 'system_banner_settings_detail', 'B', NULL, NULL, NULL, 3, 1, 1, 'system:banner:detail', 'Banner查看按钮', 0),
| (1318, 1314, '删除Banner', 'system_banner_settings_delete', 'B', NULL, NULL, NULL, 4, 1, 1, 'system:banner:delete', 'Banner删除按钮', 0),
| (1319, 1314, '批量删除Banner', 'system_banner_settings_batch_delete', 'B', NULL, NULL, NULL, 5, 1, 1, 'system:banner:batch-delete', 'Banner批量删除按钮', 0),
| (1320, 1314, '切换Banner上架状态', 'system_banner_settings_publish_status', 'B', NULL, NULL, NULL, 6, 1, 1, 'system:banner:publish-status', 'Banner上架状态按钮', 0),
| (1321, 1300, '活动通知', 'system_activity_notice', 'C', '/system/settings/activity-notice', 'System/ActivityNotice', NULL, 13, 1, 1, 'system:activity-notice:view', '活动通知菜单', 0),
| (1322, 1321, '新增活动通知', 'system_activity_notice_add', 'B', NULL, NULL, NULL, 1, 1, 1, 'system:activity-notice:add', '活动通知新增按钮', 0),
| (1323, 1321, '删除活动通知', 'system_activity_notice_delete', 'B', NULL, NULL, NULL, 2, 1, 1, 'system:activity-notice:delete', '活动通知删除按钮', 0),
| (1324, 1300, '平台资质', 'system_platform_qualification', 'C', '/system/settings/platform-qualification', 'System/PlatformQualification', NULL, 14, 1, 1, 'system:platform-qualification:view', '平台资质菜单', 0),
| (1325, 1324, '新增平台资质', 'system_platform_qualification_add', 'B', NULL, NULL, NULL, 1, 1, 1, 'system:platform-qualification:add', '平台资质新增按钮', 0),
| (1326, 1324, '查看平台资质', 'system_platform_qualification_detail', 'B', NULL, NULL, NULL, 2, 1, 1, 'system:platform-qualification:detail', '平台资质详情按钮', 0),
| (1327, 1324, '编辑平台资质', 'system_platform_qualification_edit', 'B', NULL, NULL, NULL, 3, 1, 1, 'system:platform-qualification:edit', '平台资质编辑按钮', 0),
| (1328, 1324, '删除平台资质', 'system_platform_qualification_delete', 'B', NULL, NULL, NULL, 4, 1, 1, 'system:platform-qualification:delete', '平台资质删除按钮', 0),
| (1329, 1324, '切换平台资质上架状态', 'system_platform_qualification_publish_status', 'B', NULL, NULL, NULL, 5, 1, 1, 'system:platform-qualification:publish-status', '平台资质上架状态按钮', 0),
| (1330, 1300, '协议内容', 'system_agreement_content', 'C', '/system/settings/agreement-content', 'System/AgreementContent', NULL, 16, 1, 1, 'system:agreement-content:view', '协议内容菜单', 0),
| (1331, 1330, '编辑协议内容', 'system_agreement_content_edit', 'B', NULL, NULL, NULL, 1, 1, 1, 'system:agreement-content:edit', '协议内容编辑按钮', 0),
| (1332, 1300, '常见问题', 'system_common_question', 'C', '/system/settings/common-question', 'System/CommonQuestion', NULL, 17, 1, 1, 'system:common-question:view', '常见问题菜单', 0),
| (1333, 1332, '新增常见问题', 'system_common_question_add', 'B', NULL, NULL, NULL, 1, 1, 1, 'system:common-question:add', '常见问题新增按钮', 0),
| (1334, 1332, '编辑常见问题', 'system_common_question_edit', 'B', NULL, NULL, NULL, 2, 1, 1, 'system:common-question:edit', '常见问题编辑按钮', 0),
| (1335, 1332, '删除常见问题', 'system_common_question_delete', 'B', NULL, NULL, NULL, 3, 1, 1, 'system:common-question:delete', '常见问题删除按钮', 0),
| (1336, 1300, '用户意见反馈', 'system_user_feedback', 'C', '/system/settings/user-feedback', 'System/UserFeedback', NULL, 18, 1, 1, 'system:user-feedback:view', '用户意见反馈菜单', 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),
| (5000, 0, '统计', 'statistics_root', 'M', '/statistics', NULL, 'barChart', 150, 1, 1, NULL, '统计模块', 0),
| (5010, 5000, '用户数据', 'statistics_user_data', 'C', '/statistics/user-data', 'Statistics/UserData', NULL, 10, 1, 1, 'statistics:user-data:view', '用户数据菜单', 0),
| (5030, 5000, '营收数据', 'statistics_revenue', 'C', '/statistics/revenue', 'Statistics/Revenue', NULL, 30, 1, 1, 'statistics:revenue:view', '营收数据菜单', 0),
| (5031, 5030, '导出营收报表', 'statistics_revenue_export', 'B', NULL, NULL, NULL, 1, 1, 1, 'statistics:revenue:export', '营收数据导出按钮', 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),
| (3120, 3050, '健康科普', 'live_health_popularization_manage', 'C', '/live/course/health-popularization', 'Live/HealthPopularizationList', NULL, 50, 1, 1, 'live:health-popularization:view', '健康科普菜单', 0),
| (3121, 3120, '新增健康科普', 'live_health_popularization_add', 'B', NULL, NULL, NULL, 1, 1, 1, 'live:health-popularization:add', '健康科普新增按钮', 0),
| (3122, 3120, '编辑健康科普', 'live_health_popularization_edit', 'B', NULL, NULL, NULL, 2, 1, 1, 'live:health-popularization:edit', '健康科普编辑按钮', 0),
| (3123, 3120, '删除健康科普', 'live_health_popularization_delete', 'B', NULL, NULL, NULL, 3, 1, 1, 'live:health-popularization: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),
| (4130, 4100, '养生课程购买订单', 'trade_health_course_purchase_order', 'C', '/trade/order/course-purchase', 'Trade/CoursePurchase', NULL, 8, 1, 1, 'course:purchase-order:view', '养生课程购买订单菜单', 0),
| (4131, 4130, '导出养生课程购买订单', 'trade_health_course_purchase_order_export', 'B', NULL, NULL, NULL, 1, 1, 1, 'course:purchase-order:export', '养生课程购买订单导出按钮', 0),
| (4132, 4130, '退款养生课程购买订单', 'trade_health_course_purchase_order_refund', 'B', NULL, NULL, NULL, 2, 1, 1, 'course:purchase-order:refund', '养生课程购买订单退款按钮', 0),
| (4133, 4130, '删除养生课程购买订单', 'trade_health_course_purchase_order_delete', 'B', NULL, NULL, NULL, 3, 1, 1, 'course:purchase-order:delete', '养生课程购买订单删除按钮', 0),
| (4120, 4100, '会员购买订单', 'trade_member_purchase_order', 'C', '/trade/order/member-purchase', 'Trade/MemberPurchase', NULL, 9, 1, 1, 'member:purchase-order:view', '会员购买订单菜单', 0),
| (4121, 4120, '导出会员购买订单', 'trade_member_purchase_order_export', 'B', NULL, NULL, NULL, 1, 1, 1, 'member:purchase-order:export', '会员购买订单导出按钮', 0),
| (4122, 4120, '退款会员购买订单', 'trade_member_purchase_order_refund', 'B', NULL, NULL, NULL, 2, 1, 1, 'member:purchase-order:refund', '会员购买订单退款按钮', 0),
| (4123, 4120, '删除会员购买订单', 'trade_member_purchase_order_delete', 'B', NULL, NULL, NULL, 3, 1, 1, 'member:purchase-order:delete', '会员购买订单删除按钮', 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),
| (4220, 4200, '支付流水', 'trade_payment_flow', 'C', '/trade/flow/payment-flow', 'Trade/PaymentFlow', NULL, 10, 1, 1, 'payment:flow:view', '支付流水菜单', 0),
| (4221, 4220, '导出支付流水', 'trade_payment_flow_export', 'B', NULL, NULL, NULL, 1, 1, 1, 'payment:flow:export', '支付流水导出按钮', 0),
| (4230, 4200, '退费流水', 'trade_refund_flow', 'C', '/trade/flow/refund-flow', 'Trade/RefundFlow', NULL, 20, 1, 1, 'refund:flow:view', '退费流水菜单', 0),
| (4231, 4230, '导出退费流水', 'trade_refund_flow_export', 'B', NULL, NULL, NULL, 1, 1, 1, 'refund:flow:export', '退费流水导出按钮', 0),
| (4210, 4200, '积分收支明细', 'trade_points_ledger', 'C', '/trade/flow/points-ledger', 'Trade/PointsLedger', NULL, 30, 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, 1305), (1, 1306), (1, 1307), (1, 1308), (1, 1309), (1, 1310), (1, 1311), (1, 1312), (1, 1313), (1, 1314), (1, 1315), (1, 1316), (1, 1317), (1, 1318), (1, 1319), (1, 1320), (1, 1321), (1, 1322), (1, 1323), (1, 1324), (1, 1325), (1, 1326), (1, 1327), (1, 1328), (1, 1329), (1, 1330), (1, 1331), (1, 1332), (1, 1333), (1, 1334), (1, 1335), (1, 1336),
| (1, 5000), (1, 5030), (1, 5031),
| (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, 3120), (1, 3121), (1, 3122), (1, 3123),
| (1, 4000), (1, 4100), (1, 4110), (1, 4111), (1, 4112), (1, 4113), (1, 4114), (1, 4120), (1, 4121), (1, 4122), (1, 4123), (1, 4130), (1, 4131), (1, 4132), (1, 4133),
| (1, 4200), (1, 4210), (1, 4211), (1, 4220), (1, 4221), (1, 4230), (1, 4231);
|
| -- ----------------------------
| -- 养生直播：专家（最小）
| -- ----------------------------
| 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 '营业执照图片',
| `invite_code` VARCHAR(16) 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`),
| UNIQUE KEY `uk_clinic_invite_code` (`invite_code`),
| 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`, `invite_code`, `status`, `deleted`)
| VALUES (1, '王太室堂中医', '王顾', '14725836901', '510000', '四川省', '510100', '成都市', '510106', '金牛区', '四川省成都市金牛区XXXXXXX', 'https://example.com/license/clinic-1.png', 'ABC123', 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 '微信绑定时间',
| `apple_user_identifier` VARCHAR(128) DEFAULT NULL COMMENT '苹果用户标识',
| `apple_identity_subject` VARCHAR(128) DEFAULT NULL COMMENT '苹果身份主题标识',
| `apple_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时有值）',
| `superior_clinic_user_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '上级诊所用户ID',
| `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`, `apple_user_identifier`, `apple_identity_subject`, `apple_bound_at`, `type`, `gender`, `age`, `intro`, `status`, `clinic_id`, `superior_clinic_user_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, NULL, NULL, NULL, 1, 1, 19, 'If you find an element of your interface requires instructions, then you need to redesign it.', 1, 1, NULL, 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_purchase_order` (
| `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
| `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 '用户手机号快照',
| `member_type` VARCHAR(32) NOT NULL COMMENT '会员类型：MONTH_CARD月卡 QUARTER_CARD季卡',
| `member_days` INT NOT NULL COMMENT '会员时长天数快照',
| `order_amount` DECIMAL(10,2) NOT NULL COMMENT '订单金额快照',
| `payment_method` VARCHAR(32) NOT NULL COMMENT '付款方式：WECHAT微信 ALIPAY支付宝',
| `refund_amount` DECIMAL(10,2) DEFAULT NULL COMMENT '退款金额',
| `refund_remark` VARCHAR(255) DEFAULT NULL COMMENT '退款备注',
| `status` VARCHAR(32) NOT NULL COMMENT '订单状态：WAITING_PAYMENT待支付 PAID已支付 REFUNDED已退款 CANCELLED已取消',
| `paid_at` DATETIME DEFAULT NULL COMMENT '支付时间',
| `refunded_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_member_purchase_order_no` (`order_no`),
| KEY `idx_member_purchase_order_user` (`app_user_id`),
| KEY `idx_member_purchase_order_status` (`status`),
| KEY `idx_member_purchase_order_member_type` (`member_type`),
| KEY `idx_member_purchase_order_payment_method` (`payment_method`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='会员购买订单表';
|
| INSERT IGNORE INTO `tcm_member_purchase_order`
| (`id`, `order_no`, `app_user_id`, `user_nickname`, `user_phone`, `member_type`, `member_days`, `order_amount`,
| `payment_method`, `refund_amount`, `refund_remark`, `status`, `paid_at`, `refunded_at`, `cancelled_at`, `deleted`)
| VALUES
| (1, 'MPO202310130001', 200, '王美丽', '14712346905', 'MONTH_CARD', 30, 251.50, 'ALIPAY', NULL, NULL, 'PAID',
| '2023-10-13 18:00:00', NULL, NULL, 0),
| (2, 'MPO202310130002', 200, '王美丽', '14712346905', 'QUARTER_CARD', 90, 251.50, 'WECHAT', 251.50, '用户申请退款',
| 'REFUNDED', '2023-10-12 15:20:00', '2023-10-13 09:15:00', NULL, 0),
| (3, 'MPO202310130003', 200, '王美丽', '14712346905', 'MONTH_CARD', 30, 69.00, 'WECHAT', NULL, NULL, 'WAITING_PAYMENT',
| NULL, NULL, NULL, 0),
| (4, 'MPO202310130004', 200, '王美丽', '14712346905', 'QUARTER_CARD', 90, 199.00, 'ALIPAY', NULL, NULL, 'CANCELLED',
| NULL, NULL, '2023-10-11 08:30:00', 0);
|
| -- ----------------------------
| -- 课程购买订单
| -- ----------------------------
| CREATE TABLE IF NOT EXISTS `tcm_course_purchase_order` (
| `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
| `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 '用户手机号快照',
| `course_type` VARCHAR(32) NOT NULL COMMENT '课程类型：HEALTH_COURSE养生课程，可扩展',
| `purchase_target_type` VARCHAR(32) NOT NULL COMMENT '购买对象类型：COURSE整课 COURSE_CHAPTER课程小节',
| `course_id` BIGINT UNSIGNED NOT NULL COMMENT '课程ID',
| `course_video_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '课程小节ID（整课购买为空）',
| `product_name_snapshot` VARCHAR(255) NOT NULL COMMENT '商品名称快照：整课=课程名称 小节=课程名称-小节名称',
| `course_title_snapshot` VARCHAR(128) NOT NULL COMMENT '课程名称快照',
| `chapter_name_snapshot` VARCHAR(128) DEFAULT NULL COMMENT '小节名称快照',
| `cover_img_snapshot` VARCHAR(255) DEFAULT NULL COMMENT '课程封面图快照',
| `order_amount` DECIMAL(10,2) NOT NULL COMMENT '订单金额快照',
| `payment_method` VARCHAR(32) NOT NULL COMMENT '付款方式：WECHAT微信 ALIPAY支付宝',
| `refund_amount` DECIMAL(10,2) DEFAULT NULL COMMENT '退款金额',
| `refund_remark` VARCHAR(255) DEFAULT NULL COMMENT '退款备注',
| `status` VARCHAR(32) NOT NULL COMMENT '订单状态：WAITING_PAYMENT待支付 PAID已支付 REFUNDED已退款 CANCELLED已取消',
| `paid_at` DATETIME DEFAULT NULL COMMENT '支付时间',
| `refunded_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_course_purchase_order_no` (`order_no`),
| KEY `idx_course_purchase_order_user` (`app_user_id`),
| KEY `idx_course_purchase_order_status` (`status`),
| KEY `idx_course_purchase_order_course_type` (`course_type`),
| KEY `idx_course_purchase_order_target_type` (`purchase_target_type`),
| KEY `idx_course_purchase_order_course` (`course_id`),
| KEY `idx_course_purchase_order_course_video` (`course_video_id`),
| KEY `idx_course_purchase_order_payment_method` (`payment_method`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课程购买订单表';
|
| -- ----------------------------
| -- 支付流水
| -- ----------------------------
| CREATE TABLE IF NOT EXISTS `tcm_payment_flow` (
| `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
| `flow_no` VARCHAR(64) NOT NULL COMMENT '平台流水号',
| `business_type` VARCHAR(32) NOT NULL COMMENT '业务类型：COURSE_PURCHASE课程购买 MEMBER_PURCHASE会员购买',
| `business_order_id` BIGINT UNSIGNED NOT NULL COMMENT '业务订单ID',
| `business_order_no` VARCHAR(32) NOT NULL COMMENT '业务订单编号',
| `flow_type` VARCHAR(32) NOT NULL COMMENT '流水类型：PAYMENT支付 REFUND退款',
| `trade_status` VARCHAR(32) NOT NULL COMMENT '交易状态：SUCCESS成功 REFUNDED已退款',
| `app_user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
| `user_nickname` VARCHAR(64) DEFAULT NULL COMMENT '用户昵称快照',
| `user_phone` VARCHAR(20) DEFAULT NULL COMMENT '用户手机号快照',
| `product_name_snapshot` VARCHAR(255) NOT NULL COMMENT '商品名称快照',
| `payment_method` VARCHAR(32) NOT NULL COMMENT '支付方式：WECHAT微信 ALIPAY支付宝',
| `transaction_amount` DECIMAL(10,2) NOT NULL COMMENT '交易金额',
| `third_party_trade_no` VARCHAR(64) DEFAULT NULL COMMENT '第三方支付单号',
| `third_party_refund_no` VARCHAR(64) DEFAULT NULL COMMENT '第三方退款单号',
| `remark` VARCHAR(255) DEFAULT NULL COMMENT '备注',
| `trade_time` DATETIME NOT NULL COMMENT '交易时间',
| `source_type` VARCHAR(32) NOT NULL COMMENT '来源类型：COURSE_PAYMENT课程支付 COURSE_REFUND课程退款 MEMBER_PAYMENT会员支付 MEMBER_REFUND会员退款',
| `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_payment_flow_no` (`flow_no`),
| UNIQUE KEY `uk_payment_flow_source` (`source_type`, `source_id`),
| KEY `idx_payment_flow_business_order` (`business_type`, `business_order_id`),
| KEY `idx_payment_flow_business_order_no` (`business_order_no`),
| KEY `idx_payment_flow_user` (`app_user_id`),
| KEY `idx_payment_flow_flow_type` (`flow_type`),
| KEY `idx_payment_flow_trade_status` (`trade_status`),
| KEY `idx_payment_flow_payment_method` (`payment_method`),
| KEY `idx_payment_flow_trade_time` (`trade_time`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='支付流水表';
|
| -- ----------------------------
| -- APP用户进入系统记录
| -- ----------------------------
| CREATE TABLE IF NOT EXISTS `tcm_app_user_entry_log` (
| `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
| `app_user_id` BIGINT UNSIGNED NOT NULL COMMENT 'APP用户ID',
| `entry_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP 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_app_user_entry_log_user_time` (`app_user_id`, `entry_time`),
| KEY `idx_app_user_entry_log_entry_time` (`entry_time`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='APP用户进入系统记录表';
|
| -- ----------------------------
| -- 会员介绍配置
| -- ----------------------------
| 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 '累计学习人数',
| `studying_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_type` TINYINT NOT NULL DEFAULT 1 COMMENT '课程类型：1养生课程',
| `course_id` BIGINT UNSIGNED NOT NULL COMMENT '所属养生课程ID',
| `chapter_name` VARCHAR(128) NOT NULL COMMENT '小节名称',
| `video_source_type` TINYINT NOT NULL DEFAULT 1 COMMENT '视频来源：1视频地址 2本地上传',
| `video_url` VARCHAR(512) DEFAULT NULL COMMENT '视频地址',
| `video_file_name` VARCHAR(255) DEFAULT NULL COMMENT '本地上传原始文件名',
| `duration_seconds` INT NOT NULL DEFAULT 0 COMMENT '视频时长（秒）',
| `sale_price` DECIMAL(10,2) NOT NULL DEFAULT 0.00 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_type_status` (`course_type`, `status`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='养生课程视频小节表';
|
| INSERT IGNORE INTO `tcm_health_course_video`
| (`id`, `course_type`, `course_id`, `chapter_name`, `video_source_type`, `video_url`, `video_file_name`, `duration_seconds`, `sale_price`, `sort`, `status`, `deleted`)
| VALUES
| (1, 1, 1, '方剂学', 1, 'https://example.com/health/course-1-video-1.mp4', NULL, 238, 0.00, 10, 1, 0),
| (2, 1, 1, '药剂学', 1, 'https://example.com/health/course-1-video-2.mp4', NULL, 238, 5999.99, 20, 1, 0),
| (3, 1, 1, '方剂学进阶', 1, 'https://example.com/health/course-1-video-3.mp4', NULL, 642, 5999.99, 30, 1, 0);
|
| -- ----------------------------
| -- 养生课程：播放记录
| -- ----------------------------
| CREATE TABLE IF NOT EXISTS `tcm_health_course_play_record` (
| `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
| `course_id` BIGINT UNSIGNED NOT NULL COMMENT '养生课程ID',
| `video_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '视频小节ID',
| `app_user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
| `play_start` DATETIME NOT NULL COMMENT '开始播放时间',
| `play_end` DATETIME DEFAULT NULL COMMENT '结束播放时间',
| `duration_seconds` INT NOT NULL DEFAULT 0 COMMENT '累计播放时长（秒）',
| `is_completed` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否完播：1是 0否',
| `last_play_position_seconds` INT NOT NULL DEFAULT 0 COMMENT '本次结束时播放位置（秒）',
| PRIMARY KEY (`id`),
| KEY `idx_health_course_play_course` (`course_id`),
| KEY `idx_health_course_play_video` (`video_id`),
| KEY `idx_health_course_play_user` (`app_user_id`),
| KEY `idx_health_course_play_start` (`play_start`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='养生课程播放记录表';
|
| -- ----------------------------
| -- 养生课程：收藏记录
| -- ----------------------------
| CREATE TABLE IF NOT EXISTS `tcm_health_course_favorite_record` (
| `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
| `course_id` BIGINT UNSIGNED NOT NULL COMMENT '养生课程ID',
| `app_user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
| `action_type` VARCHAR(16) NOT NULL COMMENT '操作类型：FAVORITE/CANCEL',
| `action_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
| PRIMARY KEY (`id`),
| KEY `idx_health_course_favorite_course` (`course_id`),
| KEY `idx_health_course_favorite_user` (`app_user_id`),
| KEY `idx_health_course_favorite_time` (`action_time`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='养生课程收藏记录表';
|
| -- ----------------------------
| -- 养生课程：下载记录
| -- ----------------------------
| CREATE TABLE IF NOT EXISTS `tcm_health_course_download_record` (
| `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
| `app_user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
| `course_id` BIGINT UNSIGNED NOT NULL COMMENT '养生课程ID',
| `course_video_id` BIGINT UNSIGNED NOT NULL COMMENT '视频小节ID',
| `course_title_snapshot` VARCHAR(128) NOT NULL DEFAULT '' COMMENT '课程标题快照',
| `chapter_name_snapshot` VARCHAR(128) NOT NULL DEFAULT '' COMMENT '章节标题快照',
| `cover_img_snapshot` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '封面图快照',
| `video_url_snapshot` VARCHAR(512) NOT NULL DEFAULT '' COMMENT '视频地址快照',
| `duration_seconds_snapshot` 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_download_user_deleted_created` (`app_user_id`, `deleted`, `created_at`),
| KEY `idx_health_course_download_user_video_deleted` (`app_user_id`, `course_video_id`, `deleted`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='养生课程下载记录表';
|
| -- ----------------------------
| -- 养生课程：会员加课记录
| -- ----------------------------
| CREATE TABLE IF NOT EXISTS `tcm_health_course_member_join_record` (
| `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
| `course_id` BIGINT UNSIGNED NOT NULL COMMENT '养生课程ID',
| `app_user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
| `joined_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP 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_health_course_member_join` (`course_id`, `app_user_id`, `deleted`),
| KEY `idx_health_course_member_join_user` (`app_user_id`),
| KEY `idx_health_course_member_join_time` (`joined_at`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='养生课程会员加课记录表';
|
| -- ----------------------------
| -- 内容：咨询点击记录
| -- ----------------------------
| CREATE TABLE IF NOT EXISTS `tcm_content_consult_click_record` (
| `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
| `content_type` VARCHAR(16) NOT NULL COMMENT '内容类型：LIVE/HEALTH_COURSE',
| `content_id` BIGINT UNSIGNED NOT NULL COMMENT '内容ID',
| `app_user_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '用户ID',
| `clicked_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '点击时间',
| PRIMARY KEY (`id`),
| KEY `idx_content_consult_type_content` (`content_type`, `content_id`),
| KEY `idx_content_consult_user` (`app_user_id`),
| KEY `idx_content_consult_time` (`clicked_at`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='内容咨询点击记录表';
|
| CREATE TABLE IF NOT EXISTS `tcm_health_popularization` (
| `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '健康科普ID',
| `title` VARCHAR(128) NOT NULL COMMENT '标题',
| `cover_img` VARCHAR(255) NOT NULL COMMENT '封面图',
| `content_type` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '内容类型：1图文 2视频',
| `content` LONGTEXT DEFAULT NULL COMMENT '图文内容',
| `video_url` VARCHAR(512) DEFAULT NULL COMMENT '视频地址',
| `video_file_name` VARCHAR(255) DEFAULT NULL COMMENT '视频文件名',
| `status` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '状态：1启用 0禁用',
| `is_home_recommend` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '首页推荐：1是 0否',
| `click_count` INT NOT NULL DEFAULT 0 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_health_popularization_type` (`content_type`),
| KEY `idx_health_popularization_status` (`status`),
| KEY `idx_health_popularization_home_recommend` (`is_home_recommend`),
| KEY `idx_health_popularization_created_at` (`created_at`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='健康科普表';
|
| INSERT IGNORE INTO `tcm_health_popularization`
| (`id`, `title`, `cover_img`, `content_type`, `content`, `video_url`, `video_file_name`, `status`, `is_home_recommend`, `click_count`, `sort`, `deleted`)
| VALUES
| (1, '春季养肝小常识', 'https://example.com/health/popularization-1-cover.png', 1,
| '<p>春季宜养肝，规律作息、清淡饮食、适度运动有助于保持身体平衡。</p>', NULL, NULL, 1, 1, 153, 10, 0),
| (2, '穴位按摩入门视频', 'https://example.com/health/popularization-2-cover.png', 2,
| NULL, 'https://example.com/health/popularization-2-video.mp4', '穴位按摩入门.mp4', 1, 0, 89, 20, 0);
|
| INSERT IGNORE INTO `tcm_course_purchase_order`
| (`id`, `order_no`, `app_user_id`, `user_nickname`, `user_phone`, `course_type`, `purchase_target_type`, `course_id`,
| `course_video_id`, `product_name_snapshot`, `course_title_snapshot`, `chapter_name_snapshot`, `cover_img_snapshot`,
| `order_amount`, `payment_method`, `refund_amount`, `refund_remark`, `status`, `paid_at`, `refunded_at`, `cancelled_at`, `deleted`)
| VALUES
| (1, 'CPO202310130001', 200, '王美丽', '14712346905', 'HEALTH_COURSE', 'COURSE', 1,
| NULL, '中医针灸资格证', '中医针灸资格证', NULL, 'https://example.com/health/course-1-cover.png',
| 599.80, 'ALIPAY', NULL, NULL, 'PAID', '2023-10-13 18:00:00', NULL, NULL, 0),
| (2, 'CPO202310130002', 200, '王美丽', '14712346905', 'HEALTH_COURSE', 'COURSE_CHAPTER', 1,
| 2, '中医针灸资格证-药剂学', '中医针灸资格证', '药剂学', 'https://example.com/health/course-1-cover.png',
| 5999.99, 'WECHAT', 5999.99, '用户申请退款', 'REFUNDED', '2023-10-12 15:20:00', '2023-10-13 09:15:00', NULL, 0),
| (3, 'CPO202310130003', 200, '王美丽', '14712346905', 'HEALTH_COURSE', 'COURSE_CHAPTER', 1,
| 3, '中医针灸资格证-方剂学进阶', '中医针灸资格证', '方剂学进阶', 'https://example.com/health/course-1-cover.png',
| 5999.99, 'WECHAT', NULL, NULL, 'WAITING_PAYMENT', NULL, NULL, NULL, 0),
| (4, 'CPO202310130004', 200, '王美丽', '14712346905', 'HEALTH_COURSE', 'COURSE', 1,
| NULL, '中医针灸资格证', '中医针灸资格证', NULL, 'https://example.com/health/course-1-cover.png',
| 599.80, 'ALIPAY', NULL, NULL, 'CANCELLED', NULL, NULL, '2023-10-11 08:30:00', 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 '视频地址',
| `duration_seconds` INT 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='场次答题记录表';
|
| -- ----------------------------
| -- 统计-内容数据：最近7天演示初始化数据
| -- ----------------------------
| INSERT IGNORE INTO `tcm_live_session`
| (`id`, `live_course_id`, `start_time`, `video_url`, `duration_seconds`, `base_reserve_count`, `base_watch_count`, `real_reserve_count`, `real_watch_count`,
| `reserve_convert_rate`, `completion_rate`, `effective_watch_rate`, `quiz_correct_count`, `points_issued`, `live_status`,
| `actual_start_time`, `actual_end_time`, `deleted`)
| VALUES
| (101, 1, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 6 DAY), '19:30:00'), 'https://example.com/live/recent-session-101.mp4', 3000, 8, 12, 3, 3, 37.50, 66.67, 66.67, 1, 8, 2, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 6 DAY), '19:30:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 6 DAY), '20:20:00'), 0),
| (102, 2, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 5 DAY), '19:30:00'), 'https://example.com/live/recent-session-102.mp4', 3000, 9, 14, 4, 4, 44.44, 75.00, 75.00, 1, 10, 2, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 5 DAY), '19:30:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 5 DAY), '20:20:00'), 0),
| (103, 1, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 4 DAY), '19:30:00'), 'https://example.com/live/recent-session-103.mp4', 3000, 10, 16, 4, 4, 40.00, 75.00, 75.00, 1, 12, 2, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 4 DAY), '19:30:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 4 DAY), '20:20:00'), 0),
| (104, 2, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 3 DAY), '19:30:00'), 'https://example.com/live/recent-session-104.mp4', 3000, 12, 18, 5, 5, 41.67, 80.00, 80.00, 1, 14, 2, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 3 DAY), '19:30:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 3 DAY), '20:20:00'), 0),
| (105, 1, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 2 DAY), '19:30:00'), 'https://example.com/live/recent-session-105.mp4', 3000, 12, 20, 5, 5, 41.67, 80.00, 80.00, 1, 16, 2, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 2 DAY), '19:30:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 2 DAY), '20:20:00'), 0),
| (106, 2, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '19:30:00'), 'https://example.com/live/recent-session-106.mp4', 3000, 14, 24, 6, 6, 42.86, 83.33, 83.33, 1, 18, 2, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '19:30:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '20:20:00'), 0),
| (107, 1, TIMESTAMP(CURDATE(), '19:30:00'), 'https://example.com/live/recent-session-107.mp4', 3000, 16, 28, 7, 7, 43.75, 85.71, 85.71, 1, 20, 2, TIMESTAMP(CURDATE(), '19:30:00'), TIMESTAMP(CURDATE(), '20:20:00'), 0);
|
| INSERT IGNORE INTO `tcm_live_session_question`
| (`id`, `session_id`, `sort`, `stem`, `option_a`, `option_b`, `option_c`, `correct_option`, `deleted`)
| VALUES
| (1001, 101, 1, '直播课程101演示题目', '选项A', '选项B', '选项C', 'A', 0),
| (1002, 102, 1, '直播课程102演示题目', '选项A', '选项B', '选项C', 'A', 0),
| (1003, 103, 1, '直播课程103演示题目', '选项A', '选项B', '选项C', 'A', 0),
| (1004, 104, 1, '直播课程104演示题目', '选项A', '选项B', '选项C', 'A', 0),
| (1005, 105, 1, '直播课程105演示题目', '选项A', '选项B', '选项C', 'A', 0),
| (1006, 106, 1, '直播课程106演示题目', '选项A', '选项B', '选项C', 'A', 0),
| (1007, 107, 1, '直播课程107演示题目', '选项A', '选项B', '选项C', 'A', 0);
|
| INSERT IGNORE INTO `tcm_live_watch_record`
| (`id`, `live_course_id`, `session_id`, `app_user_id`, `watch_start`, `watch_end`, `duration_seconds`, `is_completed`, `is_effective`)
| VALUES
| (5001, 1, 101, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 6 DAY), '19:35:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 6 DAY), '19:55:00'), 1200, 1, 1),
| (5002, 1, 101, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 6 DAY), '20:00:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 6 DAY), '20:08:00'), 480, 0, 1),
| (5003, 1, 101, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 6 DAY), '20:12:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 6 DAY), '20:18:00'), 360, 0, 1),
| (5004, 2, 102, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 5 DAY), '19:34:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 5 DAY), '19:58:00'), 1440, 1, 1),
| (5005, 2, 102, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 5 DAY), '20:02:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 5 DAY), '20:10:00'), 480, 0, 1),
| (5006, 2, 102, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 5 DAY), '20:11:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 5 DAY), '20:18:00'), 420, 0, 1),
| (5007, 2, 102, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 5 DAY), '20:19:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 5 DAY), '20:25:00'), 360, 0, 1),
| (5008, 1, 103, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 4 DAY), '19:33:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 4 DAY), '19:56:00'), 1380, 1, 1),
| (5009, 1, 103, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 4 DAY), '19:58:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 4 DAY), '20:06:00'), 480, 0, 1),
| (5010, 1, 103, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 4 DAY), '20:08:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 4 DAY), '20:16:00'), 480, 0, 1),
| (5011, 1, 103, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 4 DAY), '20:18:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 4 DAY), '20:24:00'), 360, 0, 1),
| (5012, 2, 104, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 3 DAY), '19:32:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 3 DAY), '19:58:00'), 1560, 1, 1),
| (5013, 2, 104, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 3 DAY), '20:00:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 3 DAY), '20:07:00'), 420, 0, 1),
| (5014, 2, 104, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 3 DAY), '20:08:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 3 DAY), '20:14:00'), 360, 0, 1),
| (5015, 2, 104, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 3 DAY), '20:15:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 3 DAY), '20:21:00'), 360, 0, 1),
| (5016, 2, 104, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 3 DAY), '20:22:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 3 DAY), '20:28:00'), 360, 0, 1),
| (5017, 1, 105, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 2 DAY), '19:31:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 2 DAY), '19:58:00'), 1620, 1, 1),
| (5018, 1, 105, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 2 DAY), '20:00:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 2 DAY), '20:08:00'), 480, 0, 1),
| (5019, 1, 105, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 2 DAY), '20:09:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 2 DAY), '20:15:00'), 360, 0, 1),
| (5020, 1, 105, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 2 DAY), '20:16:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 2 DAY), '20:22:00'), 360, 0, 1),
| (5021, 1, 105, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 2 DAY), '20:23:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 2 DAY), '20:29:00'), 360, 0, 1),
| (5022, 2, 106, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '19:30:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '19:59:00'), 1740, 1, 1),
| (5023, 2, 106, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '20:00:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '20:07:00'), 420, 0, 1),
| (5024, 2, 106, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '20:08:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '20:15:00'), 420, 0, 1),
| (5025, 2, 106, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '20:16:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '20:22:00'), 360, 0, 1),
| (5026, 2, 106, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '20:23:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '20:29:00'), 360, 0, 1),
| (5027, 2, 106, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '20:30:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '20:36:00'), 360, 0, 1),
| (5028, 1, 107, 200, TIMESTAMP(CURDATE(), '19:30:00'), TIMESTAMP(CURDATE(), '20:00:00'), 1800, 1, 1),
| (5029, 1, 107, 200, TIMESTAMP(CURDATE(), '20:02:00'), TIMESTAMP(CURDATE(), '20:10:00'), 480, 0, 1),
| (5030, 1, 107, 200, TIMESTAMP(CURDATE(), '20:11:00'), TIMESTAMP(CURDATE(), '20:18:00'), 420, 0, 1),
| (5031, 1, 107, 200, TIMESTAMP(CURDATE(), '20:19:00'), TIMESTAMP(CURDATE(), '20:25:00'), 360, 0, 1),
| (5032, 1, 107, 200, TIMESTAMP(CURDATE(), '20:26:00'), TIMESTAMP(CURDATE(), '20:32:00'), 360, 0, 1),
| (5033, 1, 107, 200, TIMESTAMP(CURDATE(), '20:33:00'), TIMESTAMP(CURDATE(), '20:39:00'), 360, 0, 1),
| (5034, 1, 107, 200, TIMESTAMP(CURDATE(), '20:40:00'), TIMESTAMP(CURDATE(), '20:46:00'), 360, 0, 1);
|
| INSERT IGNORE INTO `tcm_health_course_play_record`
| (`id`, `course_id`, `video_id`, `app_user_id`, `play_start`, `play_end`, `duration_seconds`, `is_completed`, `last_play_position_seconds`)
| VALUES
| (5101, 1, 1, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 6 DAY), '10:00:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 6 DAY), '10:18:00'), 1080, 1, 0),
| (5102, 1, 2, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 6 DAY), '15:10:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 6 DAY), '15:18:00'), 480, 0, 480),
| (5103, 1, 1, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 5 DAY), '10:02:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 5 DAY), '10:20:00'), 1080, 1, 0),
| (5104, 1, 3, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 5 DAY), '14:30:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 5 DAY), '14:38:00'), 480, 0, 480),
| (5105, 1, 1, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 4 DAY), '09:55:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 4 DAY), '10:13:00'), 1080, 1, 0),
| (5106, 1, 2, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 4 DAY), '13:20:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 4 DAY), '13:28:00'), 480, 0, 480),
| (5107, 1, 3, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 4 DAY), '19:10:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 4 DAY), '19:18:00'), 480, 0, 480),
| (5108, 1, 1, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 3 DAY), '10:05:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 3 DAY), '10:23:00'), 1080, 1, 0),
| (5109, 1, 2, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 3 DAY), '12:40:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 3 DAY), '12:48:00'), 480, 0, 480),
| (5110, 1, 3, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 3 DAY), '18:25:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 3 DAY), '18:33:00'), 480, 0, 480),
| (5111, 1, 1, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 2 DAY), '10:08:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 2 DAY), '10:26:00'), 1080, 1, 0),
| (5112, 1, 2, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 2 DAY), '13:10:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 2 DAY), '13:18:00'), 480, 0, 480),
| (5113, 1, 3, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 2 DAY), '18:00:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 2 DAY), '18:08:00'), 480, 0, 480),
| (5114, 1, 1, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 2 DAY), '20:10:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 2 DAY), '20:18:00'), 480, 0, 480),
| (5115, 1, 1, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '09:58:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '10:16:00'), 1080, 1, 0),
| (5116, 1, 2, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '12:20:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '12:28:00'), 480, 0, 480),
| (5117, 1, 3, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '17:42:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '17:50:00'), 480, 0, 480),
| (5118, 1, 2, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '21:00:00'), TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '21:08:00'), 480, 0, 480),
| (5119, 1, 1, 200, TIMESTAMP(CURDATE(), '09:50:00'), TIMESTAMP(CURDATE(), '10:08:00'), 1080, 1, 0),
| (5120, 1, 2, 200, TIMESTAMP(CURDATE(), '12:15:00'), TIMESTAMP(CURDATE(), '12:23:00'), 480, 0, 480),
| (5121, 1, 3, 200, TIMESTAMP(CURDATE(), '15:30:00'), TIMESTAMP(CURDATE(), '15:38:00'), 480, 0, 480),
| (5122, 1, 1, 200, TIMESTAMP(CURDATE(), '18:20:00'), TIMESTAMP(CURDATE(), '18:28:00'), 480, 0, 480),
| (5123, 1, 2, 200, TIMESTAMP(CURDATE(), '21:10:00'), TIMESTAMP(CURDATE(), '21:18:00'), 480, 0, 480);
|
| INSERT IGNORE INTO `tcm_health_course_favorite_record`
| (`id`, `course_id`, `app_user_id`, `action_type`, `action_time`)
| VALUES
| (5201, 1, 200, 'FAVORITE', TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 6 DAY), '11:20:00')),
| (5202, 1, 200, 'FAVORITE', TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 5 DAY), '10:50:00')),
| (5203, 1, 200, 'FAVORITE', TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 5 DAY), '19:20:00')),
| (5204, 1, 200, 'FAVORITE', TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 4 DAY), '09:40:00')),
| (5205, 1, 200, 'FAVORITE', TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 4 DAY), '17:10:00')),
| (5206, 1, 200, 'FAVORITE', TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 3 DAY), '11:30:00')),
| (5207, 1, 200, 'FAVORITE', TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 3 DAY), '15:20:00')),
| (5208, 1, 200, 'FAVORITE', TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 3 DAY), '21:10:00')),
| (5209, 1, 200, 'FAVORITE', TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 2 DAY), '10:35:00')),
| (5210, 1, 200, 'FAVORITE', TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 2 DAY), '14:10:00')),
| (5211, 1, 200, 'FAVORITE', TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 2 DAY), '20:40:00')),
| (5212, 1, 200, 'FAVORITE', TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '09:25:00')),
| (5213, 1, 200, 'FAVORITE', TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '13:18:00')),
| (5214, 1, 200, 'FAVORITE', TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '18:45:00')),
| (5215, 1, 200, 'FAVORITE', TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '22:00:00')),
| (5216, 1, 200, 'FAVORITE', TIMESTAMP(CURDATE(), '10:10:00')),
| (5217, 1, 200, 'FAVORITE', TIMESTAMP(CURDATE(), '13:00:00')),
| (5218, 1, 200, 'FAVORITE', TIMESTAMP(CURDATE(), '16:40:00')),
| (5219, 1, 200, 'FAVORITE', TIMESTAMP(CURDATE(), '20:05:00'));
|
| INSERT IGNORE INTO `tcm_content_consult_click_record`
| (`id`, `content_type`, `content_id`, `app_user_id`, `clicked_at`)
| VALUES
| (5301, 'HEALTH_COURSE', 1, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 6 DAY), '11:25:00')),
| (5302, 'LIVE', 2, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 5 DAY), '20:26:00')),
| (5303, 'LIVE', 1, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 4 DAY), '10:06:00')),
| (5304, 'HEALTH_COURSE', 1, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 4 DAY), '15:48:00')),
| (5305, 'LIVE', 2, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 3 DAY), '11:36:00')),
| (5306, 'HEALTH_COURSE', 1, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 3 DAY), '21:15:00')),
| (5307, 'LIVE', 1, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 2 DAY), '10:40:00')),
| (5308, 'HEALTH_COURSE', 1, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 2 DAY), '14:15:00')),
| (5309, 'LIVE', 2, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 2 DAY), '20:44:00')),
| (5310, 'LIVE', 2, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '09:28:00')),
| (5311, 'HEALTH_COURSE', 1, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '13:22:00')),
| (5312, 'LIVE', 1, 200, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '18:50:00')),
| (5313, 'LIVE', 1, 200, TIMESTAMP(CURDATE(), '10:15:00')),
| (5314, 'HEALTH_COURSE', 1, 200, TIMESTAMP(CURDATE(), '13:05:00')),
| (5315, 'LIVE', 2, 200, TIMESTAMP(CURDATE(), '16:45:00')),
| (5316, 'HEALTH_COURSE', 1, 200, TIMESTAMP(CURDATE(), '20:12:00'));
|
| INSERT IGNORE INTO `tcm_live_session_quiz_record`
| (`id`, `session_id`, `app_user_id`, `question_id`, `chosen_option`, `is_correct`, `points_earned`, `answered_at`)
| VALUES
| (5401, 101, 200, 1001, 'A', 1, 8, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 6 DAY), '20:05:00')),
| (5402, 102, 200, 1002, 'A', 1, 10, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 5 DAY), '20:08:00')),
| (5403, 103, 200, 1003, 'A', 1, 12, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 4 DAY), '20:10:00')),
| (5404, 104, 200, 1004, 'A', 1, 14, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 3 DAY), '20:12:00')),
| (5405, 105, 200, 1005, 'A', 1, 16, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 2 DAY), '20:15:00')),
| (5406, 106, 200, 1006, 'A', 1, 18, TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '20:18:00')),
| (5407, 107, 200, 1007, 'A', 1, 20, TIMESTAMP(CURDATE(), '20:20:00'));
|
| -- ----------------------------
| -- 系统设置
| -- ----------------------------
| CREATE TABLE IF NOT EXISTS `tcm_system_settings` (
| `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
| `service_phone` VARCHAR(32) NOT NULL COMMENT '客服电话',
| `service_online_time` VARCHAR(64) NOT NULL COMMENT '客服在线时间',
| `backup_cycle_days` INT NOT NULL COMMENT '备份周期（天）',
| `member_expire_remind_days` INT NOT NULL COMMENT '会员到期提醒天数',
| `startup_page_enabled` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否开启启动页',
| `startup_page_image_url` VARCHAR(1024) NOT NULL DEFAULT '' 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_system_settings`
| (`id`, `service_phone`, `service_online_time`, `backup_cycle_days`, `member_expire_remind_days`, `deleted`)
| VALUES
| (1, '400-800-1234', '09:00-18:00', 7, 3, 0);
|
| -- ----------------------------
| -- APP版本设置
| -- ----------------------------
| CREATE TABLE IF NOT EXISTS `tcm_app_version_config` (
| `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
| `version_no` VARCHAR(32) NOT NULL COMMENT '展示版本号',
| `version_code` INT NOT NULL COMMENT '版本字段',
| `android_file_url` VARCHAR(512) DEFAULT NULL COMMENT '安卓文件地址',
| `android_file_name` VARCHAR(255) DEFAULT NULL COMMENT '安卓文件名称',
| `ios_file_url` VARCHAR(512) DEFAULT NULL COMMENT 'iOS文件地址',
| `ios_file_name` VARCHAR(255) DEFAULT NULL COMMENT 'iOS文件名称',
| `force_update` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否强制更新：1是 0否',
| `update_notice` TEXT NOT 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_app_version_code` (`version_code`),
| KEY `idx_app_version_created_at` (`created_at`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='APP版本设置表';
|
| INSERT IGNORE INTO `tcm_app_version_config`
| (`id`, `version_no`, `version_code`, `android_file_url`, `android_file_name`, `ios_file_url`, `ios_file_name`, `force_update`, `update_notice`, `deleted`)
| VALUES
| (1, '1.0.0', 1, 'https://example.com/app/android/1.0.0.apk', 'app-1.0.0.apk', 'https://example.com/app/ios/1.0.0.ipa', 'app-1.0.0.ipa', 0, '反馈内容反馈内容反馈内容反馈内容反馈内容', 0),
| (2, '1.0.1', 2, 'https://example.com/app/android/1.0.1.apk', 'app-1.0.1.apk', 'https://example.com/app/ios/1.0.1.ipa', 'app-1.0.1.ipa', 1, '反馈内容反馈内容反馈内容反馈内容反馈内容反馈内容反馈内容', 0);
|
| -- ----------------------------
| -- 消息推送配置
| -- ----------------------------
| CREATE TABLE IF NOT EXISTS `tcm_message_push_config` (
| `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
| `notice_type_code` VARCHAR(64) NOT NULL COMMENT '通知类型编码',
| `notice_type_name` VARCHAR(64) NOT NULL COMMENT '通知类型名称',
| `push_enabled` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否开启PUSH推送：1是 0否',
| `sms_enabled` TINYINT(1) NOT NULL DEFAULT 0 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`),
| UNIQUE KEY `uk_message_push_notice_type_code` (`notice_type_code`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='消息推送配置表';
|
| -- ----------------------------
| -- 项目介绍表
| -- ----------------------------
| 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 '类别：平台介绍、营销模式、营销动作',
| `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 '视频文件名',
| `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='项目介绍表';
|
| INSERT IGNORE INTO `tcm_message_push_config`
| (`id`, `notice_type_code`, `notice_type_name`, `push_enabled`, `sms_enabled`, `deleted`)
| VALUES
| (1, 'MEMBER_EXPIRE_REMIND', '会员到期提醒', 1, 1, 0),
| (2, 'LIVE_REMIND', '直播提醒', 1, 1, 0),
| (3, 'ACTIVITY_NOTICE', '活动通知', 1, 0, 0),
| (4, 'ORDER_STATUS_UPDATE', '订单状态更新', 1, 0, 0),
| (5, 'COURSE_UPDATE_NOTICE', '课程更新通知', 1, 0, 0),
| (6, 'STUDY_PROGRESS_REMIND', '学习进度提醒', 1, 0, 0);
|
| -- ----------------------------
| -- Banner配置
| -- ----------------------------
| CREATE TABLE IF NOT EXISTS `tcm_system_banner_config` (
| `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
| `banner_img` VARCHAR(512) NOT NULL COMMENT 'Banner图片',
| `title` VARCHAR(64) NOT NULL COMMENT 'Banner标题',
| `start_at` DATETIME NOT NULL COMMENT '展示开始时间',
| `end_at` DATETIME NOT NULL COMMENT '展示结束时间',
| `jump_type` VARCHAR(32) NOT NULL COMMENT '跳转类型',
| `target_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '跳转目标ID',
| `target_name` VARCHAR(64) DEFAULT NULL COMMENT '跳转目标名称',
| `target_url` VARCHAR(512) DEFAULT NULL COMMENT '三方地址',
| `publish_enabled` 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_system_banner_time` (`start_at`, `end_at`),
| KEY `idx_system_banner_jump_type` (`jump_type`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Banner配置表';
|
| INSERT IGNORE INTO `tcm_system_banner_config`
| (`id`, `banner_img`, `title`, `start_at`, `end_at`, `jump_type`, `target_id`, `target_name`, `target_url`, `publish_enabled`, `deleted`)
| VALUES
| (1, 'https://example.com/banner/member-discount.png', '会员显示折扣', '2024-05-05 12:25:33', '2024-06-01 12:25:33', 'MEMBER', NULL, '会员', NULL, 1, 0),
| (2, 'https://example.com/banner/health-course.png', '兑换商城优惠券更新', '2024-05-05 12:25:33', '2024-06-01 12:25:33', 'HEALTH_COURSE', 1, '养生课程1', NULL, 1, 0),
| (3, 'https://example.com/banner/third-party.png', '标题标题题', '2024-05-05 12:25:33', '2024-06-01 12:25:33', 'THIRD_PARTY_URL', NULL, NULL, 'https://www.baidu.com', 0, 0);
|
| -- ----------------------------
| -- 活动通知
| -- ----------------------------
| CREATE TABLE IF NOT EXISTS `tcm_activity_notice` (
| `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
| `title` VARCHAR(15) NOT NULL COMMENT '通知标题',
| `content_html` LONGTEXT NOT 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_activity_notice_created_at` (`created_at`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='活动通知表';
|
| -- ----------------------------
| -- 平台资质
| -- ----------------------------
| CREATE TABLE IF NOT EXISTS `tcm_platform_qualification` (
| `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
| `qualification_name` VARCHAR(64) NOT NULL COMMENT '资质名称',
| `qualification_type` VARCHAR(16) NOT NULL COMMENT '资质类型：RICH_TEXT/LINK',
| `record_number` VARCHAR(64) NOT NULL COMMENT '备案号',
| `content_html` LONGTEXT DEFAULT NULL COMMENT '资质内容富文本',
| `link_url` VARCHAR(512) DEFAULT NULL COMMENT '跳转链接',
| `sort` INT NOT NULL DEFAULT 0 COMMENT '排序',
| `publish_enabled` 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_platform_qualification_created_at` (`created_at`),
| KEY `idx_platform_qualification_publish_enabled` (`publish_enabled`),
| KEY `idx_platform_qualification_type` (`qualification_type`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='平台资质表';
|
| -- ----------------------------
| -- 协议内容
| -- ----------------------------
| CREATE TABLE IF NOT EXISTS `tcm_agreement_content` (
| `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
| `agreement_type` VARCHAR(64) NOT NULL COMMENT '协议类型',
| `content_html` LONGTEXT 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_agreement_content_type_deleted` (`agreement_type`, `deleted`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='协议内容表';
|
| -- ----------------------------
| -- 常见问题
| -- ----------------------------
| CREATE TABLE IF NOT EXISTS `tcm_common_question` (
| `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
| `title` VARCHAR(15) NOT NULL COMMENT '问题标题',
| `content_html` LONGTEXT 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_common_question_created_at` (`created_at`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='常见问题表';
|
| -- ----------------------------
| -- 用户意见反馈
| -- ----------------------------
| CREATE TABLE IF NOT EXISTS `tcm_user_feedback` (
| `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
| `app_user_id` BIGINT UNSIGNED NOT NULL COMMENT '反馈用户ID',
| `content` TEXT NOT NULL COMMENT '问题和意见',
| `contact` VARCHAR(64) 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_user_feedback_app_user_id` (`app_user_id`),
| KEY `idx_user_feedback_created_at` (`created_at`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户意见反馈表';
|
| INSERT IGNORE INTO `tcm_user_feedback`
| (`id`, `app_user_id`, `content`, `contact`, `deleted`)
| VALUES
| (1, 1, '收不到验证码，已经重试很多次了，请帮忙排查。', '13800138000', 0),
| (2, 2, '课程播放时偶尔会卡住，希望增加清晰度切换。', 'user2@example.com', 0);
|
| -- ----------------------------
| -- 积分设置
| -- ----------------------------
| 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='积分收支流水表';
|
|
