fleettickets/migrations/13_inc_search_fn.sql

-- 13_inc_search_fn.sql — fleettickets · INC ticket explorer (search) function
-- ─────────────────────────────────────────────────────────────────────────────
-- reporting.fn_inc_search — ad-hoc ticket lookup by id / engineer / cluster /
-- status / state / time, for the FleetOps "Ticket explorer" card. Returns
-- { count, truncated, limit, state, rows }. Consumed by dashboard_api
-- GET /webhook/inc-search.
--
-- RECOVERED INTO VERSION CONTROL 2026-06-26: this migration was applied to the live
-- DB on 2026-06-19 but the file was never committed. Recovered verbatim from the live
-- definition (pg_get_functiondef) so a fresh DB rebuilds faithfully; the live ledger
-- already lists it, so run_migrations skips it there. The crq mirror is in 16.
-- Idempotent (CREATE OR REPLACE).
-- ─────────────────────────────────────────────────────────────────────────────

SET search_path = tickets, public;

CREATE OR REPLACE FUNCTION reporting.fn_inc_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.inc
    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$;

DO $grants$
BEGIN
  IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'dashboard_ro') THEN
    GRANT EXECUTE ON FUNCTION reporting.fn_inc_search(text, text, text, text, text, timestamptz, timestamptz, integer) TO dashboard_ro;
  END IF;
  IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'grafana_ro') THEN
    GRANT EXECUTE ON FUNCTION reporting.fn_inc_search(text, text, text, text, text, timestamptz, timestamptz, integer) TO grafana_ro;
  END IF;
END $grants$;