agenciapsilmno/database-novo/migrations/20260423000006_first_response_analytics.sql

-- ==========================================================================
-- Agencia PSI — Migracao: Analytics de tempo de 1ª resposta (Grupo 7.1)
-- ==========================================================================
-- Criado por: Leonardo Nohama
-- Data: 2026-04-23 · Sao Carlos/SP — Brasil
--
-- 3 funcoes RPC pra popular card do dashboard:
--   1. first_response_stats(tenant, from, to, therapist?)
--        Agregados do periodo: avg, median, count, sla_compliance_rate
--   2. first_response_by_therapist(tenant, from, to)
--        Ranking: terapeuta → volume + tempo medio
--   3. first_response_evolution(tenant, from, to, bucket_days)
--        Serie temporal (sparkline): buckets de N dias com avg e count
--
-- Metodo:
--   Um "run" de inbound = sequencia de mensagens inbound consecutivas sem
--   nenhuma outbound entre elas. Isso evita contar multiplas mensagens
--   do paciente (o famoso "vi sua mensagem mas so respondi depois de 3
--   disparos dele"). Pra cada run, o tempo conta a partir do PRIMEIRO
--   inbound ate a PROXIMA outbound.
--
-- Thread key identifico como patient_id OU 'anon:'+from_number, consistente
-- com a view conversation_threads.
--
-- SLA compliance: usa threshold da config conversation_sla_rules.
-- Se nao tem regra ou enabled=false, retorna sla_total=0.
-- ==========================================================================

-- ---------------------------------------------------------------------------
-- fn helper interna: monta CTE dos "runs" de inbound com tempo de resposta
-- ---------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION public._first_response_runs(
    p_tenant_id UUID,
    p_from TIMESTAMPTZ,
    p_to TIMESTAMPTZ
)
RETURNS TABLE (
    thread_key TEXT,
    inbound_started_at TIMESTAMPTZ,
    responded_at TIMESTAMPTZ,
    response_seconds INT,
    responder_id UUID
)
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = public
AS $$
    WITH msgs AS (
        SELECT
            m.id,
            m.tenant_id,
            m.direction,
            m.created_at,
            m.patient_id,
            m.from_number,
            m.to_number,
            -- mesma logica da view conversation_threads
            COALESCE(
                m.patient_id::text,
                'anon:' || COALESCE(
                    CASE WHEN m.direction = 'inbound' THEN m.from_number ELSE m.to_number END,
                    'unknown'
                )
            ) AS tk
        FROM public.conversation_messages m
        WHERE m.tenant_id = p_tenant_id
          AND m.direction IN ('inbound', 'outbound')
          AND m.created_at >= p_from
          AND m.created_at <= p_to
    ),
    with_prev AS (
        SELECT *,
               LAG(direction) OVER (PARTITION BY tenant_id, tk ORDER BY created_at, id) AS prev_direction
        FROM msgs
    ),
    run_starts AS (
        -- Primeira mensagem de cada "run inbound"
        SELECT tk, tenant_id, created_at AS inbound_started_at
        FROM with_prev
        WHERE direction = 'inbound'
          AND (prev_direction IS NULL OR prev_direction = 'outbound')
    )
    SELECT
        r.tk AS thread_key,
        r.inbound_started_at,
        o.created_at AS responded_at,
        EXTRACT(EPOCH FROM (o.created_at - r.inbound_started_at))::INT AS response_seconds,
        -- Quem respondeu: pega o assigned_to atual da thread (snapshot aproximado)
        a.assigned_to AS responder_id
    FROM run_starts r
    LEFT JOIN LATERAL (
        SELECT created_at
        FROM public.conversation_messages m2
        WHERE m2.tenant_id = r.tenant_id
          AND COALESCE(m2.patient_id::text, 'anon:' || COALESCE(m2.to_number, m2.from_number, 'unknown')) = r.tk
          AND m2.direction = 'outbound'
          AND m2.created_at > r.inbound_started_at
        ORDER BY m2.created_at
        LIMIT 1
    ) o ON true
    LEFT JOIN public.conversation_assignments a
        ON a.tenant_id = r.tenant_id AND a.thread_key = r.tk
    WHERE o.created_at IS NOT NULL;   -- so runs que foram respondidos
$$;


-- ---------------------------------------------------------------------------
-- first_response_stats: agregados do periodo
-- ---------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION public.first_response_stats(
    p_tenant_id UUID,
    p_from TIMESTAMPTZ DEFAULT (now() - interval '30 days'),
    p_to TIMESTAMPTZ DEFAULT now(),
    p_therapist_id UUID DEFAULT NULL
)
RETURNS TABLE (
    runs_count INT,
    avg_seconds INT,
    median_seconds INT,
    min_seconds INT,
    max_seconds INT,
    sla_threshold_seconds INT,
    sla_compliant_count INT,
    sla_compliance_rate NUMERIC
)
LANGUAGE plpgsql
STABLE
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
    v_threshold_seconds INT;
BEGIN
    -- Pega threshold do SLA (se habilitado)
    SELECT CASE WHEN enabled THEN threshold_minutes * 60 ELSE NULL END
        INTO v_threshold_seconds
        FROM public.conversation_sla_rules
        WHERE tenant_id = p_tenant_id;

    RETURN QUERY
    WITH runs AS (
        SELECT r.response_seconds, r.responder_id
        FROM public._first_response_runs(p_tenant_id, p_from, p_to) r
        WHERE (p_therapist_id IS NULL OR r.responder_id = p_therapist_id)
    )
    SELECT
        COUNT(*)::INT AS runs_count,
        COALESCE(AVG(response_seconds)::INT, 0) AS avg_seconds,
        COALESCE(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY response_seconds)::INT, 0) AS median_seconds,
        COALESCE(MIN(response_seconds), 0) AS min_seconds,
        COALESCE(MAX(response_seconds), 0) AS max_seconds,
        v_threshold_seconds AS sla_threshold_seconds,
        COUNT(*) FILTER (WHERE v_threshold_seconds IS NOT NULL AND response_seconds <= v_threshold_seconds)::INT AS sla_compliant_count,
        CASE
            WHEN v_threshold_seconds IS NULL OR COUNT(*) = 0 THEN NULL
            ELSE ROUND(100.0 * COUNT(*) FILTER (WHERE response_seconds <= v_threshold_seconds) / COUNT(*), 1)
        END AS sla_compliance_rate
    FROM runs;
END;
$$;

REVOKE ALL ON FUNCTION public.first_response_stats(UUID, TIMESTAMPTZ, TIMESTAMPTZ, UUID) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.first_response_stats(UUID, TIMESTAMPTZ, TIMESTAMPTZ, UUID) TO authenticated, service_role;


-- ---------------------------------------------------------------------------
-- first_response_by_therapist: ranking por terapeuta
-- ---------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION public.first_response_by_therapist(
    p_tenant_id UUID,
    p_from TIMESTAMPTZ DEFAULT (now() - interval '30 days'),
    p_to TIMESTAMPTZ DEFAULT now()
)
RETURNS TABLE (
    therapist_id UUID,
    runs_count INT,
    avg_seconds INT,
    median_seconds INT
)
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = public
AS $$
    SELECT
        r.responder_id AS therapist_id,
        COUNT(*)::INT AS runs_count,
        AVG(r.response_seconds)::INT AS avg_seconds,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY r.response_seconds)::INT AS median_seconds
    FROM public._first_response_runs(p_tenant_id, p_from, p_to) r
    WHERE r.responder_id IS NOT NULL
    GROUP BY r.responder_id
    ORDER BY avg_seconds ASC;
$$;

REVOKE ALL ON FUNCTION public.first_response_by_therapist(UUID, TIMESTAMPTZ, TIMESTAMPTZ) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.first_response_by_therapist(UUID, TIMESTAMPTZ, TIMESTAMPTZ) TO authenticated, service_role;


-- ---------------------------------------------------------------------------
-- first_response_evolution: serie temporal (buckets de N dias)
-- ---------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION public.first_response_evolution(
    p_tenant_id UUID,
    p_from TIMESTAMPTZ DEFAULT (now() - interval '30 days'),
    p_to TIMESTAMPTZ DEFAULT now(),
    p_bucket_days INT DEFAULT 7,
    p_therapist_id UUID DEFAULT NULL
)
RETURNS TABLE (
    bucket_start TIMESTAMPTZ,
    runs_count INT,
    avg_seconds INT
)
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = public
AS $$
    WITH runs AS (
        SELECT r.inbound_started_at, r.response_seconds
        FROM public._first_response_runs(p_tenant_id, p_from, p_to) r
        WHERE (p_therapist_id IS NULL OR r.responder_id = p_therapist_id)
    ),
    bucketed AS (
        SELECT
            -- Janela alinhada a p_from: bucket_index * N dias + p_from
            p_from + (
                FLOOR(EXTRACT(EPOCH FROM (inbound_started_at - p_from)) / (p_bucket_days * 86400))::INT
                * p_bucket_days * interval '1 day'
            ) AS bucket_start,
            response_seconds
        FROM runs
    )
    SELECT
        bucket_start,
        COUNT(*)::INT AS runs_count,
        AVG(response_seconds)::INT AS avg_seconds
    FROM bucketed
    GROUP BY bucket_start
    ORDER BY bucket_start;
$$;

REVOKE ALL ON FUNCTION public.first_response_evolution(UUID, TIMESTAMPTZ, TIMESTAMPTZ, INT, UUID) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.first_response_evolution(UUID, TIMESTAMPTZ, TIMESTAMPTZ, INT, UUID) TO authenticated, service_role;

COMMENT ON FUNCTION public.first_response_stats(UUID, TIMESTAMPTZ, TIMESTAMPTZ, UUID) IS
    'Metricas agregadas de tempo de 1a resposta no periodo. Opcionalmente filtra por responder_id.';

COMMENT ON FUNCTION public.first_response_by_therapist(UUID, TIMESTAMPTZ, TIMESTAMPTZ) IS
    'Ranking de tempo medio de 1a resposta por terapeuta atribuido.';

COMMENT ON FUNCTION public.first_response_evolution(UUID, TIMESTAMPTZ, TIMESTAMPTZ, INT, UUID) IS
    'Serie temporal em buckets de N dias pra sparkline.';