-- 12_inc_dashboard_by_owner.sql — fleettickets · closure-by-engineer analytics -- ───────────────────────────────────────────────────────────────────────────── -- CREATE OR REPLACE of reporting.fn_inc_dashboard (supersedes migration 09) to add -- closure-by-engineer analytics for the FleetOps Tickets dashboard: -- -- metrics.by_owner — over the windowed CLOSED set, who closed how many, grouped by -- a CASE-NORMALIZED owner. The offline closing system emits the -- same engineer in mixed case ('Elikana Mabonga' vs -- 'ELIKANA MABONGA'); normalizing with initcap(lower(...)) merges -- those duplicates (102 -> 58 distinct owners observed). Returns -- an array of { owner, closed, breached, avg_mttr_min } sorted by -- closed desc — a ready-to-render leaderboard. -- -- The owner field on the open/closed GeoJSON features is normalized the SAME way, so -- the leaderboard and the per-ticket drill-down (the closed features already carry -- owner + closed_at) agree on casing. -- -- Everything else is unchanged from migration 09. The dashboard_api route is a plain -- passthrough, so no API change is needed — only this DB function. Idempotent. -- ───────────────────────────────────────────────────────────────────────────── SET search_path = tickets, public; CREATE OR REPLACE FUNCTION reporting.fn_inc_dashboard( p_cluster text DEFAULT NULL, p_status text DEFAULT NULL, p_window text DEFAULT 'today', p_from timestamptz DEFAULT NULL, p_to timestamptz DEFAULT NULL ) RETURNS jsonb LANGUAGE plpgsql STABLE AS $fn$ DECLARE v_now_eat timestamp; v_from timestamptz; v_to timestamptz; v_preset text; v_days numeric; v_result jsonb; BEGIN p_cluster := NULLIF(p_cluster, ''); p_status := NULLIF(p_status, ''); v_now_eat := now() AT TIME ZONE 'Africa/Nairobi'; -- ── resolve the window ────────────────────────────────────────────────────── IF p_from IS NOT NULL OR p_to IS NOT NULL THEN v_preset := 'custom'; v_from := COALESCE(p_from, '-infinity'::timestamptz); v_to := COALESCE(p_to, 'infinity'::timestamptz); ELSE v_preset := lower(COALESCE(NULLIF(p_window, ''), 'today')); IF v_preset = 'week' THEN v_from := date_trunc('week', v_now_eat) AT TIME ZONE 'Africa/Nairobi'; v_to := (date_trunc('week', v_now_eat) + interval '1 week') AT TIME ZONE 'Africa/Nairobi'; ELSIF v_preset = 'month' THEN v_from := date_trunc('month', v_now_eat) AT TIME ZONE 'Africa/Nairobi'; v_to := (date_trunc('month', v_now_eat) + interval '1 month') AT TIME ZONE 'Africa/Nairobi'; ELSE v_preset := 'today'; v_from := date_trunc('day', v_now_eat) AT TIME ZONE 'Africa/Nairobi'; v_to := (date_trunc('day', v_now_eat) + interval '1 day') AT TIME ZONE 'Africa/Nairobi'; END IF; END IF; IF v_from > '-infinity'::timestamptz AND v_to < 'infinity'::timestamptz THEN v_days := GREATEST(EXTRACT(EPOCH FROM (v_to - v_from)) / 86400.0, 1); ELSE v_days := NULL; -- open-ended custom window → per-day average not meaningful END IF; -- ── build payload ─────────────────────────────────────────────────────────── WITH open_t AS ( SELECT * FROM tickets.inc_open_sla WHERE (p_cluster IS NULL OR cluster = p_cluster) AND (p_status IS NULL OR normalized_status = p_status) ), closed_t AS ( SELECT ticket_id, normalized_status, cluster, region, location_name, assigned_team, owner, closed_at, mttr, sla_status, geo_source, geom FROM tickets.inc WHERE NOT COALESCE(is_actionable, false) AND closed_at IS NOT NULL AND closed_at >= v_from AND closed_at < v_to AND (p_cluster IS NULL OR cluster = p_cluster) AND (p_status IS NULL OR normalized_status = p_status) ) SELECT jsonb_build_object( 'window', jsonb_build_object('from', v_from, 'to', v_to, 'preset', v_preset), 'open', jsonb_build_object( 'type', 'FeatureCollection', 'features', COALESCE(( SELECT jsonb_agg(jsonb_build_object( 'type', 'Feature', 'properties', jsonb_build_object( 'ticket_id', ticket_id, 'normalized_status', normalized_status, 'cluster', cluster, 'region', region, 'location_name', location_name, 'assigned_team', assigned_team, 'owner', initcap(lower(NULLIF(owner, ''))), 'geo_source', geo_source, 'sla_state', sla_state, 'hours_open', hours_open), 'geometry', ST_AsGeoJSON(geom)::jsonb)) FROM open_t WHERE geom IS NOT NULL), '[]'::jsonb) ), 'closed', jsonb_build_object( 'type', 'FeatureCollection', 'features', COALESCE(( SELECT jsonb_agg(jsonb_build_object( 'type', 'Feature', 'properties', jsonb_build_object( 'ticket_id', ticket_id, 'normalized_status', normalized_status, 'cluster', cluster, 'region', region, 'location_name', location_name, 'assigned_team', assigned_team, 'owner', initcap(lower(NULLIF(owner, ''))), 'geo_source', geo_source, 'closed_at', closed_at, 'mttr', mttr, 'sla_status', sla_status), 'geometry', ST_AsGeoJSON(geom)::jsonb)) FROM closed_t WHERE geom IS NOT NULL), '[]'::jsonb) ), 'metrics', jsonb_build_object( 'open_now', (SELECT count(*) FROM open_t), 'closed_in_window', (SELECT count(*) FROM closed_t), 'sla', jsonb_build_object( 'open', (SELECT jsonb_build_object( 'breached', count(*) FILTER (WHERE sla_state = 'breached'), 'at_risk', count(*) FILTER (WHERE sla_state = 'at_risk'), 'ok', count(*) FILTER (WHERE sla_state = 'ok'), 'unknown', count(*) FILTER (WHERE sla_state = 'unknown')) FROM open_t), 'closed', (SELECT jsonb_build_object( 'compliant', count(*) FILTER (WHERE sla_status = 'Compliant'), 'breached', count(*) FILTER (WHERE sla_status = 'Breached')) FROM closed_t) ), 'by_status', COALESCE((SELECT jsonb_object_agg(s, c) FROM ( SELECT COALESCE(normalized_status, '(none)') AS s, count(*) AS c FROM ( SELECT normalized_status FROM open_t UNION ALL SELECT normalized_status FROM closed_t) u GROUP BY 1) z), '{}'::jsonb), 'by_cluster', COALESCE((SELECT jsonb_object_agg(cl, c) FROM ( SELECT COALESCE(cluster, '(none)') AS cl, count(*) AS c FROM ( SELECT cluster FROM open_t UNION ALL SELECT cluster FROM closed_t) u GROUP BY 1) z), '{}'::jsonb), -- closures by engineer (CASE-NORMALIZED owner) — leaderboard for "who closed". 'by_owner', COALESCE((SELECT jsonb_agg(jsonb_build_object( 'owner', o, 'closed', c, 'breached', b, 'avg_mttr_min', a) ORDER BY c DESC, o) FROM ( SELECT COALESCE(initcap(lower(NULLIF(owner, ''))), '(unattributed)') AS o, count(*) AS c, count(*) FILTER (WHERE sla_status = 'Breached') AS b, round(avg(mttr) FILTER (WHERE mttr IS NOT NULL), 1) AS a FROM closed_t GROUP BY 1) z), '[]'::jsonb), 'closure_rate', jsonb_build_object( 'per_day_avg', CASE WHEN v_days IS NULL THEN NULL ELSE round((SELECT count(*) FROM closed_t)::numeric / v_days, 2) END, 'series', COALESCE((SELECT jsonb_agg(jsonb_build_object('day', d, 'count', c) ORDER BY d) FROM ( SELECT (closed_at AT TIME ZONE 'Africa/Nairobi')::date AS d, count(*) AS c FROM closed_t GROUP BY 1) z), '[]'::jsonb) ), 'avg_mttr_min', (SELECT round(avg(mttr), 1) FROM closed_t WHERE mttr IS NOT NULL) ), 'freshness', (SELECT jsonb_object_agg(dataset, jsonb_build_object( 'export_type', export_type, 'exported_at', exported_at, 'records_ingested', records_ingested, 'ingested_at', ingested_at)) FROM tickets.import_meta) ) INTO v_result; RETURN v_result; END $fn$; COMMENT ON FUNCTION reporting.fn_inc_dashboard(text, text, text, timestamptz, timestamptz) IS 'FleetOps INC operations dashboard: open (live) + closed (windowed) GeoJSON + ticket ' 'metrics incl. by_owner closure leaderboard (case-normalized), filtered by ' 'cluster/status/time (EAT). fleettickets 12 (supersedes 09).'; -- grants (guarded: roles may not exist on a fresh DB) DO $grants$ BEGIN IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'dashboard_ro') THEN GRANT EXECUTE ON FUNCTION reporting.fn_inc_dashboard(text, text, text, timestamptz, timestamptz) TO dashboard_ro; END IF; IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'grafana_ro') THEN GRANT EXECUTE ON FUNCTION reporting.fn_inc_dashboard(text, text, text, timestamptz, timestamptz) TO grafana_ro; END IF; END $grants$;