= UC0007 Prototype Implementation - View Resource Usage Analytics =

'''Initiating actor:''' Faculty Administrator

'''Other actors:''' None

The administrator accesses an analytics dashboard with seven reports covering reservation statistics, resource utilization, user activity, and trends. Each report runs an aggregate SQL query and displays the results as a formatted table.

== Scenario ==

 1. The administrator selects ''View Resource Usage Analytics'' from the main menu. The system shows the analytics sub-menu.
{{{
=== Resource Usage Analytics ===

  1. Summary Overview
  2. Reservations per Resource
  3. Most Active Users
  4. Reservation Status Distribution
  5. Busiest Days of the Week
  6. Resource Type Utilization
  7. Monthly Reservation Trends
  8. Show All Reports
  0. Cancel / Go back
}}}

 2. The administrator selects ''Summary Overview''. The system runs the following query:
{{{
SELECT COUNT(*) AS total_reservations,
       COUNT(*) FILTER (WHERE status = 'approved') AS approved,
       COUNT(*) FILTER (WHERE status = 'pending') AS pending,
       COUNT(*) FILTER (WHERE status = 'rejected') AS rejected,
       COUNT(*) FILTER (WHERE status = 'completed') AS completed,
       COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled,
       COUNT(DISTINCT user_id) AS unique_users,
       COUNT(DISTINCT resource_id) AS unique_resources
FROM reservations;
}}}

The system displays the summary:
{{{
  --- Summary Overview ---
  Total:              26
  Approved:           17
  Pending:            4
  Rejected:           1
  Completed:          3
  Cancelled:          1
  Unique Users:       10
  Unique Resources:   16
}}}

 3. The administrator selects ''Reservation Status Distribution''. The system runs:
{{{
SELECT status, COUNT(*) AS count,
       ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS percentage
FROM reservations
GROUP BY status
ORDER BY count DESC;
}}}

The system displays the breakdown with percentages:
{{{
  --- Reservation Status Distribution ---
  Status    | Count | Percentage (%)
  ----------+-------+---------------
  approved  | 17    | 65.4
  pending   | 4     | 15.4
  completed | 3     | 11.5
  rejected  | 1     | 3.8
  cancelled | 1     | 3.8
}}}

 4. The administrator selects ''Most Active Users''. The system runs:
{{{
SELECT u.first_name || ' ' || u.last_name, ut.type_name,
       COUNT(res.reservation_id) AS total,
       COUNT(res.reservation_id) FILTER (WHERE res.status IN ('approved', 'completed')) AS successful,
       COUNT(res.reservation_id) FILTER (WHERE res.status = 'rejected') AS rejected
FROM users u
JOIN user_types ut ON u.type_id = ut.type_id
LEFT JOIN reservations res ON u.user_id = res.user_id
GROUP BY u.user_id, u.first_name, u.last_name, ut.type_name
HAVING COUNT(res.reservation_id) > 0
ORDER BY total DESC;
}}}

The system displays the most active users:
{{{
  --- Most Active Users ---
  User                    | Role           | Total | Successful | Rejected
  ------------------------+----------------+-------+------------+---------
  Nikola Trajkovski       | Teaching Staff | 8     | 7          | 1
  Elena Stojanova         | Teaching Staff | 6     | 5          | 0
  Ivana Kostadinova       | Teaching Staff | 3     | 3          | 0
  Aleksandar Georgieski   | Teaching Staff | 2     | 1          | 0
  Stefan Nikolov          | Student        | 2     | 2          | 0
  ...
}}}

 5. The administrator selects ''Busiest Days of the Week''. The system queries only approved or completed reservations on physical resources:
{{{
SELECT TRIM(TO_CHAR(res.start_time, 'Day')),
       COUNT(*) AS reservation_count
FROM reservations res
JOIN resources r ON res.resource_id = r.resource_id
JOIN resource_types rt ON r.type_id = rt.type_id
WHERE rt.is_physical = TRUE
  AND res.status IN ('approved', 'completed')
GROUP BY TO_CHAR(res.start_time, 'Day'), EXTRACT(ISODOW FROM res.start_time)
ORDER BY EXTRACT(ISODOW FROM res.start_time);
}}}

The system displays the results:
{{{
  --- Busiest Days of the Week (Physical Resources) ---
  Day        | Reservations
  -----------+-------------
  Tuesday    | 4
  Wednesday  | 3
  Thursday   | 3
  Friday     | 5
  Sunday     | 1
}}}

 6. The administrator selects ''Resource Type Utilization''. The system runs:
{{{
SELECT rt.type_name,
       CASE WHEN rt.is_physical THEN 'Physical' ELSE 'Digital' END,
       COUNT(DISTINCT r.resource_id) AS total_resources,
       COUNT(res.reservation_id) AS total_reservations,
       ROUND(COUNT(res.reservation_id)::NUMERIC /
             NULLIF(COUNT(DISTINCT r.resource_id), 0), 1)
FROM resource_types rt
LEFT JOIN resources r ON rt.type_id = r.type_id
LEFT JOIN reservations res ON r.resource_id = res.resource_id
GROUP BY rt.type_id, rt.type_name, rt.is_physical
ORDER BY total_reservations DESC;
}}}

The system displays the utilization per resource type:
{{{
  --- Resource Type Utilization ---
  Type                | Category | Resources | Reservations | Avg/Resource
  --------------------+----------+-----------+--------------+-------------
  Classroom           | Physical | 6         | 10           | 1.7
  Computer Laboratory | Physical | 4         | 5            | 1.3
  Conference Room     | Physical | 3         | 2            | 0.7
  3D Printer          | Physical | 2         | 2            | 1.0
  Virtual Machine     | Digital  | 3         | 2            | 0.7
  Online Service      | Digital  | 3         | 2            | 0.7
  Projector           | Physical | 2         | 2            | 1.0
  Software License    | Digital  | 4         | 1            | 0.3
}}}

 7. The administrator selects ''Monthly Reservation Trends''. The system runs:
{{{
SELECT TO_CHAR(DATE_TRUNC('month', created_at), 'YYYY-MM'),
       COUNT(*) AS created,
       COUNT(*) FILTER (WHERE status IN ('approved', 'completed')) AS successful,
       COUNT(*) FILTER (WHERE status = 'rejected') AS rejected,
       COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled
FROM reservations
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY DATE_TRUNC('month', created_at);
}}}

The system displays the monthly trend:
{{{
  --- Monthly Reservation Trends ---
  Month   | Created | Successful | Rejected | Cancelled
  --------+---------+------------+----------+----------
  2026-01 | 15      | 14         | 0        | 0
  2026-02 | 11      | 6          | 1        | 1
}}}