94 lines
6.3 KiB
SQL
94 lines
6.3 KiB
SQL
-- ============================================================
|
||
-- V23: 教练账号测试数据
|
||
-- 描述: 创建教练角色、教练用户及相关团课测试数据
|
||
-- 日期: 2026-07-14
|
||
-- ============================================================
|
||
|
||
-- ============================================================
|
||
-- 1. 新增教练专属权限
|
||
-- 教练只能查看自己教授的团课/预约/签到信息，不能修改数据库
|
||
-- ============================================================
|
||
INSERT INTO sys_permission (permission_name, permission_code, resource, action, description, status, create_by, update_by, created_at, updated_at) VALUES
|
||
('查看自己的团课', 'coach:course:view', '/api/coach/courses', 'GET', '查看教练自己教授的团课列表及详情', 1, 'system', 'system', NOW(), NOW()),
|
||
('查看自己团课的预约', 'coach:booking:view', '/api/coach/bookings', 'GET', '查看教练自己团课的会员预约记录', 1, 'system', 'system', NOW(), NOW()),
|
||
('查看自己团课的签到', 'coach:signIn:view', '/api/coach/signins', 'GET', '查看教练自己团课的会员签到记录', 1, 'system', 'system', NOW(), NOW()),
|
||
('教练执行签到', 'coach:signIn:create', '/api/coach/signins', 'POST', '教练为自己团课的会员执行签到操作', 1, 'system', 'system', NOW(), NOW())
|
||
ON CONFLICT (permission_code) DO NOTHING;
|
||
|
||
-- ============================================================
|
||
-- 2. 教练角色
|
||
-- ============================================================
|
||
INSERT INTO sys_role (id, role_name, role_key, role_sort, status, create_by, update_by, created_at, updated_at)
|
||
VALUES (5, '教练', 'coach', 5, 1, 'system', 'system', NOW(), NOW())
|
||
ON CONFLICT (role_key) DO NOTHING;
|
||
|
||
-- ============================================================
|
||
-- 3. 教练用户（密码: Test@123）
|
||
-- ============================================================
|
||
INSERT INTO sys_user (id, username, password, email, phone, nickname, status, create_by, update_by, created_at, updated_at)
|
||
VALUES (20, 'coach', '$2a$12$nZ1EMUpZQljbnEdIKzH72eHlDJKUmHmHppnTTVth/SlHs5VpSAr8C', 'coach@novalon.com', '13900000001', '张教练', 1, 'system', 'system', NOW(), NOW())
|
||
ON CONFLICT (username) DO NOTHING;
|
||
|
||
-- ============================================================
|
||
-- 4. 教练角色关联
|
||
-- ============================================================
|
||
INSERT INTO user_role (user_id, role_id, created_by, created_at)
|
||
VALUES (20, 5, 'system', NOW())
|
||
ON CONFLICT (user_id, role_id) DO NOTHING;
|
||
|
||
-- ============================================================
|
||
-- 5. 为教练角色分配有限权限
|
||
-- 教练只能查看自己教授的团课、查看自己团课的预约/签到信息、为自己团课的会员签到
|
||
-- 不能对数据库进行写操作（签到属于业务操作，非数据库直接写入）
|
||
-- ============================================================
|
||
INSERT INTO sys_role_permission (role_id, permission_id, create_by, update_by, created_at, updated_at)
|
||
SELECT 5, id, 'system', 'system', NOW(), NOW()
|
||
FROM sys_permission
|
||
WHERE permission_code IN (
|
||
'coach:course:view', -- 查看自己的团课
|
||
'coach:booking:view', -- 查看自己团课的预约信息
|
||
'coach:signIn:view', -- 查看自己团课的签到信息
|
||
'coach:signIn:create', -- 为自己团课的会员签到
|
||
'system:notice:view' -- 查看系统公告
|
||
)
|
||
AND NOT EXISTS (
|
||
SELECT 1 FROM sys_role_permission WHERE role_id = 5 AND permission_id = sys_permission.id
|
||
);
|
||
|
||
-- ============================================================
|
||
-- 6. 为教练分配今天的测试团课
|
||
-- ============================================================
|
||
INSERT INTO group_course (course_name, coach_id, course_type, start_time, end_time, max_members, current_members, status, location, description, stored_value_amount, create_by, created_at, updated_at)
|
||
VALUES
|
||
('核心力量训练', 20, (SELECT id FROM group_course_type WHERE type_name = '核心训练' LIMIT 1),
|
||
CURRENT_DATE + TIME '08:00', CURRENT_DATE + TIME '09:00', 15, 3, '0', '健身房B区', '针对核心肌群的专项训练，适合所有水平学员', 50.00, 'system', NOW(), NOW()),
|
||
|
||
('瑜伽入门', 20, (SELECT id FROM group_course_type WHERE type_name = '瑜伽入门' LIMIT 1),
|
||
CURRENT_DATE + TIME '10:00', CURRENT_DATE + TIME '11:00', 12, 5, '0', '瑜伽教室', '基础的瑜伽体式教学，新课上线欢迎参加', 50.00, 'system', NOW(), NOW()),
|
||
|
||
('动感单车', 20, (SELECT id FROM group_course_type WHERE type_name = '动感单车' LIMIT 1),
|
||
CURRENT_DATE + TIME '14:00', CURRENT_DATE + TIME '15:00', 20, 8, '0', '单车房', '高强度燃脂骑行，动感音乐带你飞', 50.00, 'system', NOW(), NOW()),
|
||
|
||
('晚间普拉提', 20, (SELECT id FROM group_course_type WHERE type_name = '普拉提' LIMIT 1),
|
||
CURRENT_DATE + TIME '18:00', CURRENT_DATE + TIME '19:00', 10, 2, '0', '普拉提教室', '注重核心力量和身体控制，放松一天疲劳', 50.00, 'system', NOW(), NOW());
|
||
|
||
-- ============================================================
|
||
-- 7. 为教练创建一个未来的团课（方便测试时间表）
|
||
-- ============================================================
|
||
INSERT INTO group_course (course_name, coach_id, course_type, start_time, end_time, max_members, current_members, status, location, description, stored_value_amount, create_by, created_at, updated_at)
|
||
VALUES
|
||
('HIIT燃脂训练', 20, (SELECT id FROM group_course_type WHERE type_name = 'HIIT训练' LIMIT 1),
|
||
CURRENT_DATE + INTERVAL '1 day' + TIME '08:00', CURRENT_DATE + INTERVAL '1 day' + TIME '09:00',
|
||
15, 0, '0', '综合训练区', '高强度间歇训练，挑战心肺极限', 50.00, 'system', NOW(), NOW()),
|
||
|
||
('周末瑜伽', 20, (SELECT id FROM group_course_type WHERE type_name = '瑜伽进阶' LIMIT 1),
|
||
CURRENT_DATE + INTERVAL '3 days' + TIME '09:00', CURRENT_DATE + INTERVAL '3 days' + TIME '10:30',
|
||
12, 0, '0', '瑜伽教室', '针对有基础的进阶瑜伽课程', 50.00, 'system', NOW(), NOW());
|
||
|
||
-- ============================================================
|
||
-- 8. 重置序列值
|
||
-- ============================================================
|
||
SELECT setval('sys_role_id_seq', GREATEST((SELECT COALESCE(MAX(id), 0) FROM sys_role), 5));
|
||
SELECT setval('sys_user_id_seq', GREATEST((SELECT COALESCE(MAX(id), 0) FROM sys_user), 20));
|
||
SELECT setval('group_course_id_seq', (SELECT COALESCE(MAX(id), 0) FROM group_course));
|