UC0007 - Review Applications

Initiating actor: Main Organiser

A Main Organiser reviews applications for their event edition and updates application statuses. The scenario reviews Hackathon participation applications.

Scenario

1. The Main Organiser opens the application review page for Impact Hackathon 2026.

2. The application lists applications for Impact Hackathon 2026.

SELECT
    a.application_id,
    s.student_index,
    s.first_name,
    s.last_name,
    a.application_type,
    a.function_name,
    a.application_status,
    a.motivational_letter
FROM applications a
JOIN students s
    ON s.student_index = a.student_index
WHERE a.et_name = 'Hackathon'
  AND a.event_title = 'Impact Hackathon 2026'
  AND a.event_start_time = TIMESTAMP '2026-12-12 09:00:00'
ORDER BY a.application_type, a.function_name, a.application_status, s.last_name, s.first_name;

3. The Main Organiser selects Lina Stamenovska's participation application and accepts it.

4. The application updates the application status.

UPDATE applications
SET application_status = 'accepted'
WHERE application_id = (
    SELECT a.application_id
    FROM applications a
    WHERE a.student_index = 230031
      AND a.et_name = 'Hackathon'
      AND a.event_title = 'Impact Hackathon 2026'
      AND a.event_start_time = TIMESTAMP '2026-12-12 09:00:00'
      AND a.application_type = 'event_participation'
    ORDER BY a.application_id
    LIMIT 1
);

5. The application shows the updated application.

SELECT
    a.application_id,
    s.first_name,
    s.last_name,
    a.application_type,
    a.function_name,
    a.application_status
FROM applications a
JOIN students s
    ON s.student_index = a.student_index
WHERE a.student_index = 230031
  AND a.et_name = 'Hackathon'
  AND a.event_title = 'Impact Hackathon 2026'
  AND a.event_start_time = TIMESTAMP '2026-12-12 09:00:00'
  AND a.application_type = 'event_participation'
ORDER BY a.application_id;

6. The application shows accepted function applications for Impact Hackathon 2026.

SELECT
    ctf.function_name,
    COALESCE(s.first_name || ' ' || s.last_name, 'not assigned') AS accepted_member,
    a.application_status
FROM core_team_functions ctf
LEFT JOIN applications a
    ON a.et_name = ctf.et_name
   AND a.event_title = ctf.event_title
   AND a.event_start_time = ctf.event_start_time
   AND a.function_name = ctf.function_name
   AND a.application_type = 'core_team_function'
   AND a.application_status = 'accepted'
LEFT JOIN students s
    ON s.student_index = a.student_index
WHERE ctf.et_name = 'Hackathon'
  AND ctf.event_title = 'Impact Hackathon 2026'
  AND ctf.event_start_time = TIMESTAMP '2026-12-12 09:00:00'
ORDER BY ctf.function_name;