-- 添加新学员的存储过程
CREATE OR REPLACE FUNCTION AddNewStudent(
    _student_id VARCHAR(255),
    _name VARCHAR(255),
    _password VARCHAR(255),
    _phone VARCHAR(20),
    _email VARCHAR(255)
) RETURNS VOID AS $$
DECLARE
    _info_id INT;
BEGIN
    INSERT INTO personal_info (phone, email) VALUES (_phone, _email) RETURNING info_id INTO _info_id;
    INSERT INTO student (student_id, name, password, personal_info_id) VALUES (_student_id, _name, _password, _info_id);
END;
$$ LANGUAGE plpgsql;

-- 更新教练电话的存储过程
CREATE OR REPLACE FUNCTION UpdateCoachPhone(
    _coach_id VARCHAR(255),
    _new_phone VARCHAR(20)
) RETURNS VOID AS $$
BEGIN
    UPDATE personal_info SET phone = _new_phone WHERE info_id = (SELECT personal_info_id FROM coach WHERE coach_id = _coach_id);
END;
$$ LANGUAGE plpgsql;

-- 获取某个学生的所有预约的存储过程
CREATE OR REPLACE FUNCTION GetStudentAppointments(
    _student_id VARCHAR(255)
) RETURNS TABLE(appointment_id INT, coach_name VARCHAR(255), venue_name VARCHAR(255), period VARCHAR(50), status VARCHAR(50)) AS $$
BEGIN
    RETURN QUERY
    SELECT a.appointment_id, c.name AS coach_name, v.name AS venue_name, a.period, a.status
    FROM appointment a
    JOIN coach c ON a.coach_id = c.coach_id
    JOIN venue v ON a.venue_id = v.venue_id
    WHERE a.student_id = _student_id;
END;
$$ LANGUAGE plpgsql;

-- 获取某个教练的所有反馈的存储过程
CREATE OR REPLACE FUNCTION GetCoachFeedbacks(
    _coach_id VARCHAR(255)
) RETURNS TABLE(feedback_id INT, content TEXT, date DATE) AS $$
BEGIN
    RETURN QUERY
    SELECT f.feedback_id, f.content, f.date
    FROM feedback f
    JOIN training_record t ON f.feedback_id = t.feedback_id
    WHERE t.coach_id = _coach_id;
END;
$$ LANGUAGE plpgsql;

-- 获取某个场地的所有可用时间段的存储过程
CREATE OR REPLACE FUNCTION GetAvailableSlotsForVenue(
    _venue_id INT
) RETURNS TABLE(slot_id INT, start_time TIME, end_time TIME) AS $$
BEGIN
    RETURN QUERY
    SELECT bs.slot_id, bs.start_time, bs.end_time
    FROM booking_slot bs
    LEFT JOIN appointment a ON bs.venue_id = a.venue_id AND bs.start_time <= a.period::time AND bs.end_time >= a.period::time
    WHERE bs.venue_id = _venue_id AND a.appointment_id IS NULL;
END;
$$ LANGUAGE plpgsql;