fleettickets/migrations/16_crq_dashboard.sql

-- 16_crq_dashboard.sql — fleettickets · CRQ dashboard parity (view + read functions)
-- ─────────────────────────────────────────────────────────────────────────────
-- Brings CRQ to FleetOps-dashboard parity with INC, so the Tickets tab's CRQ
-- sub-tab works "just like INC". Mirrors, against tickets.crq:
--   tickets.crq_open_sla              ← mirror of tickets.inc_open_sla (08)
--   reporting.fn_crq_dashboard        ← mirror of reporting.fn_inc_dashboard (09/12)
--   reporting.fn_crq_search           ← mirror of reporting.fn_inc_search (13)
--   reporting.fn_crq_filter_options   ← mirror of reporting.fn_inc_filter_options (14)
-- consumed by dashboard_api GET /webhook/crq-dashboard | crq-search | crq-filter-options.
--
-- Differences from the INC view: tickets.crq has no `geog` column (mig 05 is INC-only)
-- and its latitude/longitude come from `raw` (empty in the feed), so crq_open_sla omits
-- geog and derives latitude/longitude from `geom`. The 48h SLA rule is reused verbatim
-- for layout parity — installation-lifecycle SLA semantics may be refined later.
--
-- Idempotent (CREATE OR REPLACE / VIEW). Requires migration 15 (tickets.crq + typed cols).
-- ─────────────────────────────────────────────────────────────────────────────

SET search_path = tickets, public;

-- ── crq_open_sla — open CRQ tickets with derived SLA (mirror of inc_open_sla) ─
CREATE OR REPLACE VIEW tickets.crq_open_sla AS
SELECT
  ticket_id,
  normalized_status,
  bucket,
  cluster,
  region,
  location_name,
  assigned_team,
  owner,
  sla_status AS source_sla_status,
  mttr,                                              -- minutes (null until closed)
  COALESCE(created_at_service, first_seen_at)                       AS sla_clock,
  CASE WHEN created_at_service IS NOT NULL THEN 'service' ELSE 'first_seen' END AS sla_clock_source,
  round((EXTRACT(EPOCH FROM now() - COALESCE(created_at_service, first_seen_at)) / 3600)::numeric, 1) AS hours_open,
  CASE
    WHEN COALESCE(created_at_service, first_seen_at) IS NULL              THEN 'unknown'
    WHEN now() - COALESCE(created_at_service, first_seen_at) >= interval '48h' THEN 'breached'
    WHEN now() - COALESCE(created_at_service, first_seen_at) >= interval '36h' THEN 'at_risk'
    ELSE 'ok'
  END AS sla_state,
  created_at_service,
  first_seen_at,
  scheduled_at,
  CASE WHEN geom IS NOT NULL THEN ST_Y(geom) END AS latitude,
  CASE WHEN geom IS NOT NULL THEN ST_X(geom) END AS longitude,
  geo_source,
  geom
FROM tickets.crq
WHERE is_actionable;

COMMENT ON VIEW tickets.crq_open_sla IS
  'Open (is_actionable) CRQ tickets with derived SLA (48h rule; clock = created_at_service '
  'or first_seen_at fallback). Mirror of inc_open_sla; no geog. fleettickets 16.';

-- ── fn_crq_dashboard — mirror of fn_inc_dashboard over tickets.crq ───────────
CREATE OR REPLACE FUNCTION reporting.fn_crq_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 $function$
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.crq_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.crq
    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 $function$;

-- ── fn_crq_search — mirror of fn_inc_search over tickets.crq ──────────────────
CREATE OR REPLACE FUNCTION reporting.fn_crq_search(
  p_ticket_id text DEFAULT NULL,
  p_owner     text DEFAULT NULL,
  p_cluster   text DEFAULT NULL,
  p_status    text DEFAULT NULL,
  p_state     text DEFAULT 'closed',
  p_from      timestamptz DEFAULT NULL,
  p_to        timestamptz DEFAULT NULL,
  p_limit     integer DEFAULT 500
)
 RETURNS jsonb LANGUAGE plpgsql STABLE AS $function$
DECLARE
  v_state text := lower(COALESCE(NULLIF(p_state, ''), 'closed'));
  v_limit integer := LEAST(GREATEST(COALESCE(p_limit, 500), 1), 5000);
  v_result jsonb;
BEGIN
  p_ticket_id := NULLIF(trim(p_ticket_id), '');
  p_owner     := NULLIF(trim(p_owner), '');
  p_cluster   := NULLIF(p_cluster, '');
  p_status    := NULLIF(p_status, '');

  WITH hits AS (
    SELECT ticket_id, normalized_status, cluster, region, location_name,
           initcap(lower(NULLIF(owner, ''))) AS owner, assigned_team,
           sla_status, mttr, closed_at, created_at_service, is_actionable,
           CASE WHEN geom IS NOT NULL THEN ST_Y(geom) END AS lat,
           CASE WHEN geom IS NOT NULL THEN ST_X(geom) END AS lng
    FROM tickets.crq
    WHERE (p_ticket_id IS NULL OR ticket_id ILIKE '%' || p_ticket_id || '%')
      AND (p_owner     IS NULL OR lower(owner) LIKE '%' || lower(p_owner) || '%')
      AND (p_cluster   IS NULL OR cluster = p_cluster)
      AND (p_status    IS NULL OR normalized_status = p_status)
      AND CASE v_state
            WHEN 'open' THEN COALESCE(is_actionable, false)
            WHEN 'all'  THEN COALESCE(is_actionable, false)
                             OR (closed_at IS NOT NULL
                                 AND (p_from IS NULL OR closed_at >= p_from)
                                 AND (p_to   IS NULL OR closed_at <  p_to))
            ELSE             NOT COALESCE(is_actionable, false)   -- 'closed'
                             AND closed_at IS NOT NULL
                             AND (p_from IS NULL OR closed_at >= p_from)
                             AND (p_to   IS NULL OR closed_at <  p_to)
          END
  ),
  total AS (SELECT count(*) AS n FROM hits),
  page AS (
    SELECT * FROM hits
    ORDER BY closed_at DESC NULLS LAST, created_at_service DESC NULLS LAST
    LIMIT v_limit
  )
  SELECT jsonb_build_object(
    'count',     (SELECT n FROM total),
    'truncated', (SELECT n FROM total) > v_limit,
    'limit',     v_limit,
    'state',     v_state,
    'rows', COALESCE((SELECT jsonb_agg(to_jsonb(page)
                             ORDER BY page.closed_at DESC NULLS LAST,
                                      page.created_at_service DESC NULLS LAST)
                      FROM page), '[]'::jsonb)
  ) INTO v_result;

  RETURN v_result;
END $function$;

-- ── fn_crq_filter_options — mirror of fn_inc_filter_options over tickets.crq ──
CREATE OR REPLACE FUNCTION reporting.fn_crq_filter_options()
 RETURNS jsonb LANGUAGE sql STABLE AS $function$
  SELECT jsonb_build_object(
    'owners', (SELECT COALESCE(jsonb_agg(o ORDER BY o), '[]'::jsonb)
               FROM (SELECT DISTINCT initcap(lower(NULLIF(owner, ''))) AS o
                     FROM tickets.crq WHERE NULLIF(owner, '') IS NOT NULL) s),
    'clusters', (SELECT COALESCE(jsonb_agg(c ORDER BY c), '[]'::jsonb)
                 FROM (SELECT DISTINCT cluster AS c
                       FROM tickets.crq WHERE NULLIF(cluster, '') IS NOT NULL) s),
    'open_ticket_ids', (SELECT COALESCE(jsonb_agg(ticket_id ORDER BY ticket_id), '[]'::jsonb)
                        FROM tickets.crq WHERE COALESCE(is_actionable, false))
  );
$function$;

-- ── 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 SELECT ON tickets.crq_open_sla TO dashboard_ro;
    GRANT EXECUTE ON FUNCTION reporting.fn_crq_dashboard(text, text, text, timestamptz, timestamptz) TO dashboard_ro;
    GRANT EXECUTE ON FUNCTION reporting.fn_crq_search(text, text, text, text, text, timestamptz, timestamptz, integer) TO dashboard_ro;
    GRANT EXECUTE ON FUNCTION reporting.fn_crq_filter_options() TO dashboard_ro;
  END IF;
  IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'grafana_ro') THEN
    GRANT SELECT ON tickets.crq_open_sla TO grafana_ro;
    GRANT EXECUTE ON FUNCTION reporting.fn_crq_dashboard(text, text, text, timestamptz, timestamptz) TO grafana_ro;
    GRANT EXECUTE ON FUNCTION reporting.fn_crq_search(text, text, text, text, text, timestamptz, timestamptz, integer) TO grafana_ro;
    GRANT EXECUTE ON FUNCTION reporting.fn_crq_filter_options() TO grafana_ro;
  END IF;
END $grants$;