# postgres_exporter custom queries for pg_trickle
#
# Usage: pass this file to postgres_exporter via --extend.query-path
# (or PG_EXPORTER_EXTEND_QUERY_PATH env var).
#
# All metrics are prefixed "pg_trickle_".
# Requires pg_trickle 0.11.0+ with pgtrickle.pg_stat_stream_tables and quick_health views

# ── Overall cluster health summary ─────────────────────────────────────────────

pg_trickle_health:
  query: |
    SELECT
        total_stream_tables,
        error_tables,
        stale_tables,
        CASE WHEN scheduler_running THEN 1 ELSE 0 END AS scheduler_running,
        CASE
            WHEN status = 'OK'       THEN 0
            WHEN status = 'WARNING'  THEN 1
            WHEN status = 'CRITICAL' THEN 2
            ELSE 3
        END AS health_status_code
    FROM pgtrickle.quick_health
  metrics:
    - total_stream_tables:
        usage: GAUGE
        description: "Total number of registered stream tables"
    - error_tables:
        usage: GAUGE
        description: "Number of tables currently in ERROR state or with consecutive errors"
    - stale_tables:
        usage: GAUGE
        description: "Number of tables with data older than their configured schedule"
    - scheduler_running:
        usage: GAUGE
        description: "1 if the pg_trickle scheduler background worker is alive"
    - health_status_code:
        usage: GAUGE
        description: "Overall cluster health: 0=OK, 1=WARNING, 2=CRITICAL"

# ── Per-table refresh statistics ─────────────────────────────────────────────

pg_trickle_table_stats:
  query: |
    SELECT
        pgt_schema                                    AS schema,
        pgt_name                                      AS name,
        status,
        refresh_mode,
        CASE WHEN is_populated THEN 1 ELSE 0 END      AS is_populated,
        EXTRACT(EPOCH FROM staleness)::float8          AS staleness_seconds,
        CASE WHEN stale THEN 1 ELSE 0 END              AS stale,
        consecutive_errors,
        COALESCE(total_refreshes, 0)                  AS total_refreshes,
        COALESCE(successful_refreshes, 0)             AS successful_refreshes,
        COALESCE(failed_refreshes, 0)                 AS failed_refreshes,
        COALESCE(total_rows_inserted, 0)              AS rows_inserted_total,
        COALESCE(total_rows_deleted, 0)               AS rows_deleted_total,
        COALESCE(avg_duration_ms, 0)                  AS avg_duration_ms
    FROM pgtrickle.pg_stat_stream_tables
  metrics:
    - schema:
        usage: LABEL
        description: "Schema of the stream table"
    - name:
        usage: LABEL
        description: "Name of the stream table"
    - status:
        usage: LABEL
        description: "Current lifecycle status (ACTIVE, SUSPENDED, ERROR, INITIALIZING)"
    - refresh_mode:
        usage: LABEL
        description: "Configured refresh mode (FULL, DIFFERENTIAL, AUTO, IMMEDIATE)"
    - is_populated:
        usage: GAUGE
        description: "1 if the stream table has been populated at least once"
    - staleness_seconds:
        usage: GAUGE
        description: "Seconds since last successful refresh (NULL = never refreshed)"
    - stale:
        usage: GAUGE
        description: "1 if the table data is older than its configured schedule"
    - consecutive_errors:
        usage: GAUGE
        description: "Number of consecutive refresh failures (resets on success)"
    - total_refreshes:
        usage: COUNTER
        description: "Total number of refresh cycles completed"
    - successful_refreshes:
        usage: COUNTER
        description: "Number of refresh cycles that completed successfully"
    - failed_refreshes:
        usage: COUNTER
        description: "Number of refresh cycles that failed"
    - rows_inserted_total:
        usage: COUNTER
        description: "Total rows inserted across all refreshes"
    - rows_deleted_total:
        usage: COUNTER
        description: "Total rows deleted across all refreshes"
    - avg_duration_ms:
        usage: GAUGE
        description: "Rolling average refresh duration in milliseconds"

# ── Stream table status counts ────────────────────────────────────────────────

pg_trickle_status_counts:
  query: |
    SELECT
        status,
        count(*)::bigint AS stream_tables_total
    FROM pgtrickle.pgt_stream_tables
    GROUP BY status
  metrics:
    - status:
        usage: LABEL
        description: "Lifecycle status"
    - stream_tables_total:
        usage: GAUGE
        description: "Number of stream tables in this status"

# ── Refresh history: recent error rate ───────────────────────────────────────

pg_trickle_recent_refresh_stats:
  query: |
    SELECT
        st.pgt_schema                                         AS schema,
        st.pgt_name                                           AS name,
        count(*) FILTER (WHERE h.status = 'FAILED')          AS recent_failures,
        count(*) FILTER (WHERE h.status = 'COMPLETED')       AS recent_successes,
        COALESCE(
            avg(EXTRACT(EPOCH FROM (h.end_time - h.start_time)) * 1000)
            FILTER (WHERE h.status = 'COMPLETED' AND h.end_time IS NOT NULL),
            0.0
        )::float8                                            AS recent_avg_duration_ms,
        COALESCE(
            max(EXTRACT(EPOCH FROM (h.end_time - h.start_time)) * 1000)
            FILTER (WHERE h.status = 'COMPLETED' AND h.end_time IS NOT NULL),
            0.0
        )::float8                                            AS recent_max_duration_ms
    FROM pgtrickle.pgt_stream_tables st
    JOIN pgtrickle.pgt_refresh_history h ON h.pgt_id = st.pgt_id
    WHERE h.start_time > now() - interval '1 hour'
    GROUP BY st.pgt_schema, st.pgt_name
  metrics:
    - schema:
        usage: LABEL
        description: "Schema of the stream table"
    - name:
        usage: LABEL
        description: "Name of the stream table"
    - recent_failures:
        usage: GAUGE
        description: "Number of failed refreshes in the last hour"
    - recent_successes:
        usage: GAUGE
        description: "Number of successful refreshes in the last hour"
    - recent_avg_duration_ms:
        usage: GAUGE
        description: "Average refresh duration (ms) over the last hour"
    - recent_max_duration_ms:
        usage: GAUGE
        description: "Maximum refresh duration (ms) over the last hour"

# ── Template cache statistics (UX-1) ──────────────────────────────────────────

pg_trickle_cache:
  query: |
    SELECT
        l1_hits,
        l1_evictions,
        delta_cache_entries
    FROM pgtrickle.cache_stats()
  metrics:
    - l1_hits:
        usage: COUNTER
        description: "Total L1 template cache hits (avoids delta query regeneration)"
    - l1_evictions:
        usage: COUNTER
        description: "Total L1 template cache evictions"
    - delta_cache_entries:
        usage: GAUGE
        description: "Current number of entries in the delta template cache"

# ── Deployment health summary (UX-4) ──────────────────────────────────────────

pg_trickle_health_summary:
  query: |
    SELECT
        total_stream_tables,
        active_tables,
        error_tables,
        stale_tables,
        CASE WHEN scheduler_running THEN 1 ELSE 0 END AS scheduler_running,
        total_refreshes_1h,
        failed_refreshes_1h,
        COALESCE(avg_refresh_ms_1h, 0) AS avg_refresh_ms_1h,
        COALESCE(p99_refresh_ms_1h, 0) AS p99_refresh_ms_1h,
        cache_hit_rate
    FROM pgtrickle.health_summary()
  metrics:
    - total_stream_tables:
        usage: GAUGE
        description: "Total registered stream tables"
    - active_tables:
        usage: GAUGE
        description: "Tables in ACTIVE state"
    - error_tables:
        usage: GAUGE
        description: "Tables in ERROR state"
    - stale_tables:
        usage: GAUGE
        description: "Stale stream tables"
    - scheduler_running:
        usage: GAUGE
        description: "1 if the scheduler is alive"
    - total_refreshes_1h:
        usage: GAUGE
        description: "Total refreshes in the last hour"
    - failed_refreshes_1h:
        usage: GAUGE
        description: "Failed refreshes in the last hour"
    - avg_refresh_ms_1h:
        usage: GAUGE
        description: "Average refresh latency (ms) over the last hour"
    - p99_refresh_ms_1h:
        usage: GAUGE
        description: "P99 refresh latency (ms) over the last hour"
    - cache_hit_rate:
        usage: GAUGE
        description: "Template cache hit rate (0.0–1.0)"

# ── Reliability counters (OPS-10-02) ─────────────────────────────────────────
# Requires pg_trickle 0.50.0+.
# Alert rules (suggested thresholds):
#   - pg_trickle_reliability_invalidation_ring_overflows_total > 0
#     over 5 minutes → deployment has >1024 simultaneously-DDL'd STs
#   - pg_trickle_reliability_dag_cycles_detected_total > 0
#     at any point → circular dependency without allow_circular = on
#   - pg_trickle_reliability_template_cache_stale_evictions_total spike
#     → rapid schema churn, may predict GROUP_RESCAN fallback storms

pg_trickle_reliability:
  query: |
    SELECT
        invalidation_ring_overflows,
        dag_cycles_detected,
        template_cache_stale_evictions
    FROM pgtrickle.reliability_counters()
  metrics:
    - invalidation_ring_overflows:
        usage: COUNTER
        description: "Total invalidation ring overflow events (each means >1024 STs invalidated simultaneously)"
    - dag_cycles_detected:
        usage: COUNTER
        description: "Total DAG cycle detections (should always be 0 in steady state)"
    - template_cache_stale_evictions:
        usage: COUNTER
        description: "Delta template cache entries evicted due to defining_query_hash mismatch (schema change indicator)"
