= 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.

{{{#!sql
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.

{{{#!sql
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.

{{{#!sql
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.

{{{#!sql
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;
}}}