-- ============================================================
-- Migration 005: Función agg_segment — agregación con k-anonimato
--
-- DISEÑO DE SEGURIDAD:
--   SECURITY DEFINER → puede leer responses directamente (bypasa RLS).
--   Pero aplica tres capas de protección propias:
--     a) Valida que el caller (org_admin) sea dueño de la encuesta.
--     b) Whitelists las dimensiones permitidas (previene SQL injection).
--     c) Suprime toda celda con count < k_threshold → devuelve n=NULL,
--        suppressed=TRUE en lugar del valor real.
--
--   El texto libre (type='text_long') NUNCA aparece en el output:
--   la función agrega solo sobre cuasi-identificadores de responses,
--   no sobre el contenido de answers (regla no negociable #4 CLAUDE.md).
--
-- SIGNATURE:
--   agg_segment(p_survey_id uuid, p_dimensions text[])
--   RETURNS TABLE(segment jsonb, n bigint, suppressed boolean)
--
--   p_dimensions: subconjunto de ['qi_age_bucket', 'qi_sector', 'qi_zone']
--   segment: objeto JSONB con los valores de las dimensiones pedidas
--   n:       count del segmento, o NULL si suppressed
--   suppressed: TRUE si count < k_threshold de la encuesta
-- ============================================================

CREATE OR REPLACE FUNCTION public.agg_segment(
    p_survey_id  uuid,
    p_dimensions text[]
)
RETURNS TABLE (
    segment    jsonb,
    n          bigint,
    suppressed boolean
)
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
    v_k_threshold  int;
    v_valid_dims   CONSTANT text[] := ARRAY['qi_age_bucket', 'qi_sector', 'qi_zone'];
    v_dim          text;
    v_select_cols  text[] := ARRAY[]::text[];
    v_json_kv      text[] := ARRAY[]::text[];
    v_group_cols   text;
    v_inner_sql    text;
    v_outer_sql    text;
BEGIN
    -- 1. Verificar que la encuesta existe y obtener su umbral de k-anonimato
    SELECT k_threshold INTO v_k_threshold
    FROM public.surveys
    WHERE id = p_survey_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Survey not found: %', p_survey_id;
    END IF;

    -- 2. Verificar autorización del caller
    --    Si es org_admin, la encuesta debe pertenecer a su organización.
    --    analyst usa service_role (bypasa RLS y esta función), no llega acá con app_role='analyst'.
    IF public.current_app_role() = 'org_admin' THEN
        IF NOT EXISTS (
            SELECT 1 FROM public.surveys
            WHERE id = p_survey_id AND org_id = public.current_org_id()
        ) THEN
            RAISE EXCEPTION
                'Access denied: survey % does not belong to caller organization',
                p_survey_id;
        END IF;
    END IF;

    -- 3. Validar que se pidió al menos una dimensión
    IF p_dimensions IS NULL OR array_length(p_dimensions, 1) IS NULL THEN
        RAISE EXCEPTION 'At least one dimension is required';
    END IF;

    -- 4. Validar dimensiones contra whitelist (prevención de SQL injection)
    FOREACH v_dim IN ARRAY p_dimensions LOOP
        IF NOT (v_dim = ANY(v_valid_dims)) THEN
            RAISE EXCEPTION
                'Invalid dimension: "%" — allowed: %',
                v_dim,
                array_to_string(v_valid_dims, ', ');
        END IF;
        v_select_cols := v_select_cols || quote_ident(v_dim);
        v_json_kv     := v_json_kv     || (quote_literal(v_dim) || ', ' || quote_ident(v_dim));
    END LOOP;

    v_group_cols := array_to_string(v_select_cols, ', ');

    -- 5. SQL interno: agrupar responses por las dimensiones pedidas
    v_inner_sql := format(
        'SELECT
             jsonb_build_object(%s) AS segment,
             count(*)              AS raw_n
         FROM public.responses
         WHERE survey_id = %L
         GROUP BY %s',
        array_to_string(v_json_kv, ', '),
        p_survey_id,
        v_group_cols
    );

    -- 6. SQL externo: aplicar umbral de k-anonimato
    --    n = NULL  y suppressed = TRUE  cuando raw_n < k_threshold
    --    n = raw_n y suppressed = FALSE cuando raw_n >= k_threshold
    v_outer_sql := format(
        'SELECT
             segment,
             CASE WHEN raw_n >= %s THEN raw_n ELSE NULL END AS n,
             (raw_n < %s)                                   AS suppressed
         FROM (%s) sub
         ORDER BY suppressed DESC, segment',
        v_k_threshold,
        v_k_threshold,
        v_inner_sql
    );

    RETURN QUERY EXECUTE v_outer_sql;
END;
$$;

-- org_admin puede llamar a esta función (aplica su propia autorización interna)
GRANT EXECUTE ON FUNCTION public.agg_segment(uuid, text[]) TO authenticated;