= UC0008 - Volunteer for Event Session =

'''Initiating actor:''' Member

A Member views event sessions that need volunteers and volunteers for one selected session.

== Scenario ==

1. The Member opens the volunteer page for Job Fair 2026.

2. The application lists event sessions with the number of assigned and missing volunteers.

{{{#!sql
SELECT
    es.ee_id,
    es.es_no,
    est.est_name,
    es.es_start_time,
    l.l_name,
    es.es_title,
    es.needed_no_volunteers,
    COUNT(v.student_index) AS assigned_volunteers,
    GREATEST(es.needed_no_volunteers - COUNT(v.student_index), 0) AS missing_volunteers
FROM event_sessions es
JOIN event_session_types est
    ON est.est_id = es.est_id
JOIN locations l
    ON l.l_id = es.l_id
LEFT JOIN volunteers v
    ON v.ee_id = es.ee_id
   AND v.es_no = es.es_no
WHERE es.ee_id = 4
GROUP BY
    es.ee_id,
    es.es_no,
    est.est_name,
    es.es_start_time,
    l.l_name,
    es.es_title,
    es.needed_no_volunteers
ORDER BY es.es_start_time, l.l_name, es.es_no;
}}}

3. The Member chooses the Engineering Companies Stands session.

4. The application stores the volunteer assignment.

{{{#!sql
INSERT INTO volunteers (
    student_index,
    ee_id,
    es_no,
    volunteer_role
) VALUES (
    230024,
    4,
    3,
    'stand support'
);
}}}

5. The application shows volunteers assigned to the selected session.

{{{#!sql
SELECT
    v.student_index,
    s.first_name,
    s.last_name,
    ee.ee_title,
    es.es_no,
    es.es_title,
    v.volunteer_role
FROM volunteers v
JOIN students s
    ON s.student_index = v.student_index
JOIN event_editions ee
    ON ee.ee_id = v.ee_id
JOIN event_sessions es
    ON es.ee_id = v.ee_id
   AND es.es_no = v.es_no
WHERE v.ee_id = 4
  AND v.es_no = 3
ORDER BY s.last_name, s.first_name;
}}}