-- 08_inc_open_sla_view.sql — fleettickets · open-ticket SLA view
-- ─────────────────────────────────────────────────────────────────────────────
-- reporting view over the open (is_actionable) INC tickets with a DERIVED SLA
-- state, since the source sla_status is only meaningful once a ticket is closed.
--
-- SLA clock = created_at_service, falling back to first_seen_at (the export's
-- first-sighting) when creation is missing (~70% of open rows lack
-- created_at_service); sla_clock_source flags which was used. Contract rule: 48h.
-- hours_open / sla_state use now(), so the view is always current. Includes the
-- routing-grade geog so dispatch can join straight off it.
-- ─────────────────────────────────────────────────────────────────────────────

SET search_path = tickets, public;

CREATE OR REPLACE VIEW tickets.inc_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,
  latitude,
  longitude,
  geo_source,
  geom,
  geog
FROM tickets.inc
WHERE is_actionable;

COMMENT ON VIEW tickets.inc_open_sla IS
  'Open (is_actionable) INC tickets with derived SLA (48h rule; clock = created_at_service '
  'or first_seen_at fallback). fleettickets 08.';

-- 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.inc_open_sla TO dashboard_ro;
  END IF;
  IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'grafana_ro') THEN
    GRANT SELECT ON tickets.inc_open_sla TO grafana_ro;
  END IF;
END $grants$;