AdvancedReports: advanced_reports.sql

-- CityFix - напредни извештаи (фаза P6)
SET search_path TO project;

-- Извештај 1: Квартален учинок во решавањето на пријавите по категорија
WITH report_times AS (
    SELECT r.report_id, r.category_id, r.status, r.created_at,
           date_trunc('quarter', r.created_at) AS quarter,
           MIN(l.changed_at) FILTER (WHERE l.status = 'received') AS received_at,
           MIN(l.changed_at) FILTER (WHERE l.status = 'resolved') AS resolved_at
    FROM reports r
    JOIN status_logs l ON l.report_id = r.report_id
    GROUP BY r.report_id
),
per_quarter AS (
    SELECT quarter, category_id,
           COUNT(*)                                     AS total,
           COUNT(*) FILTER (WHERE status = 'resolved')  AS resolved,
           COUNT(*) FILTER (WHERE status = 'rejected')  AS rejected,
           AVG(EXTRACT(EPOCH FROM received_at - created_at) / 3600)  AS avg_response_hours,
           AVG(EXTRACT(EPOCH FROM resolved_at - created_at) / 86400) AS avg_resolution_days
    FROM report_times
    GROUP BY quarter, category_id
)
SELECT to_char(pq.quarter, 'YYYY-"Q"Q')                         AS quarter,
       c.name                                                   AS category,
       pq.total, pq.resolved, pq.rejected,
       ROUND(100.0 * pq.resolved / pq.total, 1)                 AS resolved_pct,
       ROUND(pq.avg_response_hours, 1)                          AS avg_response_hours,
       ROUND(pq.avg_resolution_days, 1)                         AS avg_resolution_days,
       ROUND(pq.avg_resolution_days - prev.avg_resolution_days, 1) AS change_vs_prev_quarter,
       RANK() OVER (PARTITION BY pq.quarter
                    ORDER BY pq.avg_resolution_days DESC NULLS LAST)      AS slowest_rank
FROM per_quarter pq
JOIN categories c ON c.category_id = pq.category_id
LEFT JOIN per_quarter prev
       ON prev.category_id = pq.category_id
      AND prev.quarter = pq.quarter - INTERVAL '3 months'
ORDER BY pq.quarter, slowest_rank;

-- Извештај 2: Жаришта на повторувачки проблеми
WITH resolution AS (
    SELECT report_id, MIN(changed_at) FILTER (WHERE status = 'resolved') AS resolved_at
    FROM status_logs
    GROUP BY report_id
),
located AS (
    SELECT r.report_id, r.category_id, r.citizen_id, r.status, r.created_at, r.location_text,
           ROUND(r.latitude  / 0.005) * 0.005 AS cell_lat,
           ROUND(r.longitude / 0.005) * 0.005 AS cell_lon,
           res.resolved_at
    FROM reports r
    JOIN resolution res ON res.report_id = r.report_id
    WHERE r.latitude IS NOT NULL
      AND r.status <> 'rejected'
)
SELECT RANK() OVER (ORDER BY COUNT(*) * COUNT(DISTINCT date_trunc('quarter', l.created_at)) DESC) AS hotspot_rank,
       l.cell_lat, l.cell_lon,
       c.name                                                    AS category,
       MAX(l.location_text)                                      AS example_address,
       COUNT(*)                                                  AS reports,
       COUNT(DISTINCT l.citizen_id)                              AS distinct_citizens,
       COUNT(DISTINCT date_trunc('quarter', l.created_at))       AS quarters_with_reports,
       COUNT(*) FILTER (WHERE l.status NOT IN ('resolved', 'rejected')) AS open_now,
       ROUND(AVG(EXTRACT(EPOCH FROM l.resolved_at - l.created_at) / 86400), 1) AS avg_resolution_days,
       to_char(MAX(l.created_at), 'DD.MM.YYYY')                  AS last_report
FROM located l
JOIN categories c ON c.category_id = l.category_id
GROUP BY l.cell_lat, l.cell_lon, c.name
HAVING COUNT(*) >= 3
   AND COUNT(DISTINCT date_trunc('quarter', l.created_at)) >= 2
ORDER BY hotspot_rank, reports DESC;