-- Outcome tracking for advice — turns confirmed_by + age into a signal
-- the dashboard (and future reflect thread) can use to measure how
-- useful the advice stream actually is.
--
-- Two views, both materialized at query time (not MATERIALIZED VIEW —
-- traffic is low and we want freshness):
--   advice_outcomes        : per-advice classification
--   advice_weekly_outcomes : aggregated win-rate per ISO week

-- Outcome classification per advice row.
--
--   win      → verify (advice predicted a real fix the user shipped)
--              apply  (user trusted Apply with AI)
--   loss     → intentional (user said "this is intentional, future
--              advice should respect")
--              audit  (audit thread auto-confirmed as noise)
--   neutral  → user (manually confirmed; no signal about advice quality)
--   ignored  → unconfirmed AND created_at older than 7 days
--   pending  → unconfirmed AND younger than 7 days
--   noise    → administrative cleanups (cleanup, audit-noise) — excluded
--              from aggregations

CREATE OR REPLACE VIEW public.advice_outcomes AS
SELECT
    id,
    project,
    severity,
    created_at,
    confirmed_at,
    confirmed_by,
    CASE
        WHEN confirmed_by IN ('verify', 'apply') THEN 'win'
        WHEN confirmed_by IN ('intentional', 'audit') THEN 'loss'
        WHEN confirmed_by = 'user' THEN 'neutral'
        WHEN confirmed_at IS NULL
             AND created_at < (now() - interval '7 days') THEN 'ignored'
        WHEN confirmed_at IS NULL THEN 'pending'
        ELSE 'noise'
    END AS outcome
FROM public.worker_events
WHERE event_type = 'advice';

ALTER VIEW public.advice_outcomes SET (security_invoker = true);
GRANT SELECT ON public.advice_outcomes TO authenticated, service_role;

-- Weekly aggregation. Each row = one ISO-week bucket.
--   resolution_rate = (win + loss + neutral + ignored) / total
--                     "of the advice from this week, how much got a
--                      definite outcome?"
--   win_rate        = win / (win + loss + neutral)
--                     "when something was resolved, how often was the
--                      advice useful?"
-- Excludes 'pending' (still in flight) and 'noise' (admin cleanup).

CREATE OR REPLACE VIEW public.advice_weekly_outcomes AS
SELECT
    date_trunc('week', created_at) AS week_start,
    COUNT(*) AS total,
    COUNT(*) FILTER (WHERE outcome = 'win')      AS wins,
    COUNT(*) FILTER (WHERE outcome = 'loss')     AS losses,
    COUNT(*) FILTER (WHERE outcome = 'neutral')  AS neutral,
    COUNT(*) FILTER (WHERE outcome = 'ignored')  AS ignored,
    COUNT(*) FILTER (WHERE outcome = 'pending')  AS pending,
    ROUND(
        100.0
        * COUNT(*) FILTER (WHERE outcome IN ('win','loss','neutral','ignored'))
        / NULLIF(COUNT(*) FILTER (WHERE outcome != 'noise'), 0),
        1
    ) AS resolution_rate_pct,
    ROUND(
        100.0
        * COUNT(*) FILTER (WHERE outcome = 'win')
        / NULLIF(COUNT(*) FILTER (WHERE outcome IN ('win','loss','neutral')), 0),
        1
    ) AS win_rate_pct
FROM public.advice_outcomes
WHERE outcome != 'noise'
GROUP BY date_trunc('week', created_at)
ORDER BY week_start DESC;

ALTER VIEW public.advice_weekly_outcomes SET (security_invoker = true);
GRANT SELECT ON public.advice_weekly_outcomes TO authenticated, service_role;
