-- 创建用户并设置密码
CREATE USER 'admin_user'@'localhost' IDENTIFIED BY 'AdminPass';
CREATE USER 'coach_user'@'localhost' IDENTIFIED BY 'CoachPass';
CREATE USER 'student_user'@'localhost' IDENTIFIED BY 'StudentPass';

-- 授予管理员权限
GRANT ALL PRIVILEGES ON fitness_center.* TO 'admin_user'@'localhost';

-- 创建视图供教练使用
CREATE VIEW coach_appointments AS
SELECT * FROM APPOINTMENT WHERE coach_id = SUBSTRING_INDEX(USER(), '@', 1);

CREATE VIEW coach_training_records AS
SELECT * FROM TRAINING_RECORD WHERE coach_id = SUBSTRING_INDEX(USER(), '@', 1);

-- 授予教练权限
GRANT SELECT, INSERT ON fitness_center.FEEDBACK TO 'coach_user'@'localhost';
GRANT SELECT, UPDATE ON fitness_center.coach_appointments TO 'coach_user'@'localhost';
GRANT SELECT ON fitness_center.STUDENT TO 'coach_user'@'localhost';
GRANT SELECT ON fitness_center.coach_training_records TO 'coach_user'@'localhost';

-- 创建视图供学生使用
CREATE VIEW student_appointments AS
SELECT * FROM APPOINTMENT WHERE student_id = SUBSTRING_INDEX(USER(), '@', 1);

CREATE VIEW student_personal_info AS
SELECT * FROM PERSONAL_INFO WHERE student_id = SUBSTRING_INDEX(USER(), '@', 1);

CREATE VIEW student_training_records AS
SELECT * FROM TRAINING_RECORD WHERE student_id = SUBSTRING_INDEX(USER(), '@', 1);

-- 授予学生权限
GRANT SELECT, INSERT ON fitness_center.FEEDBACK TO 'student_user'@'localhost';
GRANT SELECT, UPDATE ON fitness_center.student_appointments TO 'student_user'@'localhost';
GRANT SELECT ON fitness_center.student_personal_info TO 'student_user'@'localhost';
GRANT SELECT ON fitness_center.student_training_records TO 'student_user'@'localhost';

-- 刷新权限
FLUSH PRIVILEGES;