678 lines
29 KiB
PL/PgSQL
678 lines
29 KiB
PL/PgSQL
-- ============================================================
|
||
-- Migration 012: Dashboard RPCs v2 — alineación al instrumento real
|
||
-- Correcciones respecto a migration 009:
|
||
-- _dash_responses : departamento filtra por responses.qi_zone (no JOIN a answers)
|
||
-- rpc_dashboard_kpis : K1 = 1.6+1.13 (sin 1.1); K2 = 5.2 en-dash; K3 = avg 4.3 raw 1–5
|
||
-- rpc_prevalencia_por_depto : group by qi_zone
|
||
-- rpc_perfil_poblacion : (era rpc_distribucion_cuidado) — renombrada
|
||
-- rpc_score_organizacional : 0.7×6.1 + 0.3×6.5; 6.5='No lo sé' → NULL
|
||
-- rpc_apoyos_demandados : (era rpc_politicas_gap) — solo 6.2, renombrada
|
||
-- rpc_intensidad_vs_bienestar : (era rpc_intensidad_vs_productividad) — renombrada
|
||
-- rpc_saturacion_cuidador : (era rpc_riesgo_retencion) — nueva lógica 4.3+5.1+5.2
|
||
-- Todas mantienen: SECURITY DEFINER, search_path fijado, k-anonimato k≥5.
|
||
-- ============================================================
|
||
|
||
-- Eliminar funciones renombradas
|
||
DROP FUNCTION IF EXISTS public.rpc_distribucion_cuidado(uuid, jsonb);
|
||
DROP FUNCTION IF EXISTS public.rpc_politicas_gap(uuid, jsonb);
|
||
DROP FUNCTION IF EXISTS public.rpc_intensidad_vs_productividad(uuid, jsonb);
|
||
DROP FUNCTION IF EXISTS public.rpc_riesgo_retencion(uuid, jsonb);
|
||
|
||
-- ── Helper: responses filtradas por cohort ────────────────────
|
||
-- Usa qi_zone directamente (poblado desde A1 en submit-survey.ts).
|
||
-- Evita un JOIN a answers por consulta.
|
||
CREATE OR REPLACE FUNCTION public._dash_responses(
|
||
p_survey_id uuid,
|
||
p_depto text DEFAULT NULL,
|
||
p_genero text[] DEFAULT NULL,
|
||
p_etario text[] DEFAULT NULL,
|
||
p_modalidad text[] DEFAULT NULL
|
||
)
|
||
RETURNS TABLE(response_id uuid)
|
||
LANGUAGE sql STABLE SECURITY DEFINER
|
||
SET search_path = public
|
||
AS $$
|
||
SELECT r.id
|
||
FROM public.responses r
|
||
WHERE r.survey_id = p_survey_id
|
||
AND (p_depto IS NULL OR r.qi_zone = p_depto)
|
||
AND (p_genero IS NULL OR cardinality(p_genero) = 0 OR EXISTS (
|
||
SELECT 1 FROM public.answers a
|
||
JOIN public.questions q ON q.id = a.question_id
|
||
WHERE a.response_id = r.id AND q.code = 'A6'
|
||
AND a.value #>> '{}' = ANY(p_genero)
|
||
))
|
||
AND (p_etario IS NULL OR cardinality(p_etario) = 0
|
||
OR r.qi_age_bucket = ANY(p_etario))
|
||
AND (p_modalidad IS NULL OR cardinality(p_modalidad) = 0 OR EXISTS (
|
||
SELECT 1 FROM public.answers a
|
||
JOIN public.questions q ON q.id = a.question_id
|
||
WHERE a.response_id = r.id AND q.code = 'A7'
|
||
AND a.value #>> '{}' = ANY(p_modalidad)
|
||
));
|
||
$$;
|
||
|
||
-- ── Helper: parse filters JSONB → arrays ──────────────────────
|
||
CREATE OR REPLACE FUNCTION public._dash_parse_filters(p_filters jsonb)
|
||
RETURNS TABLE(
|
||
f_depto text,
|
||
f_genero text[],
|
||
f_etario text[],
|
||
f_modalidad text[]
|
||
)
|
||
LANGUAGE sql IMMUTABLE SECURITY DEFINER
|
||
SET search_path = public
|
||
AS $$
|
||
SELECT
|
||
NULLIF(p_filters->>'depto',''),
|
||
ARRAY(SELECT jsonb_array_elements_text(p_filters->'genero'))::text[],
|
||
ARRAY(SELECT jsonb_array_elements_text(p_filters->'etario'))::text[],
|
||
ARRAY(SELECT jsonb_array_elements_text(p_filters->'modalidad'))::text[];
|
||
$$;
|
||
|
||
-- ── KPIs ─────────────────────────────────────────────────────
|
||
-- K1: cuidadores = 1.6='Sí' OR 1.13='Sí'
|
||
-- K2: 5.2 IN ('Varias veces (4–10 veces al año)', 'Con frecuencia (más de 10 veces al año)') ← en-dash
|
||
-- K3: promedio raw de 4.3 (rating 1–5), devuelto como decimal 1.0–5.0
|
||
CREATE OR REPLACE FUNCTION public.rpc_dashboard_kpis(
|
||
p_survey_id uuid,
|
||
p_filters jsonb DEFAULT '{}'::jsonb
|
||
)
|
||
RETURNS jsonb
|
||
LANGUAGE plpgsql STABLE SECURITY DEFINER
|
||
SET search_path = public
|
||
AS $$
|
||
DECLARE
|
||
v_f record;
|
||
v_n bigint;
|
||
v_cuidadores bigint;
|
||
v_bienestar_avg numeric;
|
||
v_bienestar_n bigint;
|
||
v_ausencias bigint;
|
||
BEGIN
|
||
SELECT * INTO v_f FROM public._dash_parse_filters(p_filters);
|
||
|
||
SELECT count(*) INTO v_n
|
||
FROM public._dash_responses(p_survey_id, v_f.f_depto, v_f.f_genero, v_f.f_etario, v_f.f_modalidad);
|
||
|
||
IF v_n < 5 THEN
|
||
RETURN jsonb_build_object('status','suppressed','min_n',5,'n',v_n);
|
||
END IF;
|
||
|
||
-- K1: 1.6='Sí' OR 1.13='Sí' (cuidadores de otros)
|
||
SELECT count(DISTINCT dr.response_id) INTO v_cuidadores
|
||
FROM public._dash_responses(p_survey_id, v_f.f_depto, v_f.f_genero, v_f.f_etario, v_f.f_modalidad) dr
|
||
WHERE EXISTS (
|
||
SELECT 1 FROM public.answers a
|
||
JOIN public.questions q ON q.id = a.question_id
|
||
WHERE a.response_id = dr.response_id
|
||
AND q.code IN ('1.6','1.13')
|
||
AND a.value #>> '{}' = 'Sí'
|
||
);
|
||
|
||
-- K3: promedio raw 4.3 (1–5)
|
||
SELECT avg((a.value #>> '{}')::numeric), count(*)
|
||
INTO v_bienestar_avg, v_bienestar_n
|
||
FROM public._dash_responses(p_survey_id, v_f.f_depto, v_f.f_genero, v_f.f_etario, v_f.f_modalidad) dr
|
||
JOIN public.answers a ON a.response_id = dr.response_id
|
||
JOIN public.questions q ON q.id = a.question_id
|
||
WHERE q.code = '4.3'
|
||
AND a.value #>> '{}' ~ '^[0-9]+$';
|
||
|
||
-- K2: ausencias recurrentes 5.2 — EN-DASH obligatorio
|
||
SELECT count(DISTINCT dr.response_id) INTO v_ausencias
|
||
FROM public._dash_responses(p_survey_id, v_f.f_depto, v_f.f_genero, v_f.f_etario, v_f.f_modalidad) dr
|
||
JOIN public.answers a ON a.response_id = dr.response_id
|
||
JOIN public.questions q ON q.id = a.question_id
|
||
WHERE q.code = '5.2'
|
||
AND a.value #>> '{}' IN (
|
||
'Varias veces (4–10 veces al año)',
|
||
'Con frecuencia (más de 10 veces al año)'
|
||
);
|
||
|
||
RETURN jsonb_build_object(
|
||
'status', 'ok',
|
||
'n', v_n,
|
||
'pct_cuidadores', ROUND(v_cuidadores::numeric / v_n * 100, 1),
|
||
'score_bienestar', CASE WHEN v_bienestar_n > 0
|
||
THEN ROUND(v_bienestar_avg, 1)
|
||
ELSE NULL END,
|
||
'pct_ausencias', ROUND(v_ausencias::numeric / v_n * 100, 1)
|
||
);
|
||
END;
|
||
$$;
|
||
|
||
-- ── Prevalencia por departamento ──────────────────────────────
|
||
-- Usa qi_zone de responses (sin JOIN a answers para departamento).
|
||
-- Cuidadores = 1.6='Sí' OR 1.13='Sí'.
|
||
CREATE OR REPLACE FUNCTION public.rpc_prevalencia_por_depto(
|
||
p_survey_id uuid,
|
||
p_filters jsonb DEFAULT '{}'::jsonb
|
||
)
|
||
RETURNS jsonb
|
||
LANGUAGE plpgsql STABLE SECURITY DEFINER
|
||
SET search_path = public
|
||
AS $$
|
||
DECLARE
|
||
v_f record;
|
||
v_total bigint;
|
||
v_rows jsonb;
|
||
BEGIN
|
||
SELECT * INTO v_f FROM public._dash_parse_filters(p_filters);
|
||
|
||
SELECT count(*) INTO v_total
|
||
FROM public._dash_responses(p_survey_id, v_f.f_depto, v_f.f_genero, v_f.f_etario, v_f.f_modalidad);
|
||
|
||
IF v_total < 5 THEN
|
||
RETURN jsonb_build_object('status','suppressed','min_n',5,'n',v_total);
|
||
END IF;
|
||
|
||
SELECT jsonb_agg(row) INTO v_rows FROM (
|
||
SELECT jsonb_build_object(
|
||
'depto', depto_val,
|
||
'n', CASE WHEN n_depto >= 5 THEN n_depto ELSE NULL END,
|
||
'pct_cuidadores', CASE WHEN n_depto >= 5
|
||
THEN ROUND(n_cuid::numeric / n_depto * 100, 1)
|
||
ELSE NULL END,
|
||
'suppressed', (n_depto < 5)
|
||
) AS row
|
||
FROM (
|
||
SELECT
|
||
r.qi_zone AS depto_val,
|
||
count(DISTINCT dr.response_id) AS n_depto,
|
||
count(DISTINCT dr.response_id) FILTER (
|
||
WHERE EXISTS (
|
||
SELECT 1 FROM public.answers a2
|
||
JOIN public.questions q2 ON q2.id = a2.question_id
|
||
WHERE a2.response_id = dr.response_id
|
||
AND q2.code IN ('1.6','1.13')
|
||
AND a2.value #>> '{}' = 'Sí'
|
||
)
|
||
) AS n_cuid
|
||
FROM public._dash_responses(p_survey_id, NULL, v_f.f_genero, v_f.f_etario, v_f.f_modalidad) dr
|
||
JOIN public.responses r ON r.id = dr.response_id
|
||
WHERE r.qi_zone IS NOT NULL
|
||
GROUP BY r.qi_zone
|
||
) sub
|
||
ORDER BY depto_val
|
||
) t;
|
||
|
||
RETURN jsonb_build_object('status','ok','n_total',v_total,'data', COALESCE(v_rows,'[]'::jsonb));
|
||
END;
|
||
$$;
|
||
|
||
-- ── Perfil de la población (era rpc_distribucion_cuidado) ─────
|
||
-- 1.1 = disc. propia, 1.6 = familiar, 1.13 = adulto mayor
|
||
CREATE OR REPLACE FUNCTION public.rpc_perfil_poblacion(
|
||
p_survey_id uuid,
|
||
p_filters jsonb DEFAULT '{}'::jsonb
|
||
)
|
||
RETURNS jsonb
|
||
LANGUAGE plpgsql STABLE SECURITY DEFINER
|
||
SET search_path = public
|
||
AS $$
|
||
DECLARE
|
||
v_f record;
|
||
v_total bigint;
|
||
v_disc bigint;
|
||
v_familiar bigint;
|
||
v_mayor bigint;
|
||
v_ninguno bigint;
|
||
BEGIN
|
||
SELECT * INTO v_f FROM public._dash_parse_filters(p_filters);
|
||
SELECT count(*) INTO v_total
|
||
FROM public._dash_responses(p_survey_id, v_f.f_depto, v_f.f_genero, v_f.f_etario, v_f.f_modalidad);
|
||
IF v_total < 5 THEN
|
||
RETURN jsonb_build_object('status','suppressed','min_n',5,'n',v_total);
|
||
END IF;
|
||
|
||
SELECT count(DISTINCT dr.response_id) INTO v_disc
|
||
FROM public._dash_responses(p_survey_id, v_f.f_depto, v_f.f_genero, v_f.f_etario, v_f.f_modalidad) dr
|
||
JOIN public.answers a ON a.response_id = dr.response_id
|
||
JOIN public.questions q ON q.id = a.question_id
|
||
WHERE q.code = '1.1' AND a.value #>> '{}' = 'Sí';
|
||
|
||
SELECT count(DISTINCT dr.response_id) INTO v_familiar
|
||
FROM public._dash_responses(p_survey_id, v_f.f_depto, v_f.f_genero, v_f.f_etario, v_f.f_modalidad) dr
|
||
JOIN public.answers a ON a.response_id = dr.response_id
|
||
JOIN public.questions q ON q.id = a.question_id
|
||
WHERE q.code = '1.6' AND a.value #>> '{}' = 'Sí';
|
||
|
||
SELECT count(DISTINCT dr.response_id) INTO v_mayor
|
||
FROM public._dash_responses(p_survey_id, v_f.f_depto, v_f.f_genero, v_f.f_etario, v_f.f_modalidad) dr
|
||
JOIN public.answers a ON a.response_id = dr.response_id
|
||
JOIN public.questions q ON q.id = a.question_id
|
||
WHERE q.code = '1.13' AND a.value #>> '{}' = 'Sí';
|
||
|
||
SELECT count(DISTINCT dr.response_id) INTO v_ninguno
|
||
FROM public._dash_responses(p_survey_id, v_f.f_depto, v_f.f_genero, v_f.f_etario, v_f.f_modalidad) dr
|
||
WHERE NOT EXISTS (
|
||
SELECT 1 FROM public.answers a
|
||
JOIN public.questions q ON q.id = a.question_id
|
||
WHERE a.response_id = dr.response_id
|
||
AND q.code IN ('1.1','1.6','1.13')
|
||
AND a.value #>> '{}' = 'Sí'
|
||
);
|
||
|
||
RETURN jsonb_build_object(
|
||
'status', 'ok',
|
||
'n', v_total,
|
||
'disc_propia', ROUND(v_disc::numeric / v_total * 100, 1),
|
||
'familiar', ROUND(v_familiar::numeric / v_total * 100, 1),
|
||
'adulto_mayor', ROUND(v_mayor::numeric / v_total * 100, 1),
|
||
'ninguno', ROUND(v_ninguno::numeric / v_total * 100, 1)
|
||
);
|
||
END;
|
||
$$;
|
||
|
||
-- ── Score organizacional (Madurez) ────────────────────────────
|
||
-- Formula: 0.7 × 6.1_norm + 0.3 × 6.5_norm
|
||
-- 6.1: Sí=100, Parcialmente=66, No invisible=33, No nunca=0
|
||
-- 6.5: Sí=100, Tal vez=66, No necesario=33, No lo sé=NULL (no entra al avg)
|
||
CREATE OR REPLACE FUNCTION public.rpc_score_organizacional(
|
||
p_survey_id uuid,
|
||
p_filters jsonb DEFAULT '{}'::jsonb
|
||
)
|
||
RETURNS jsonb
|
||
LANGUAGE plpgsql STABLE SECURITY DEFINER
|
||
SET search_path = public
|
||
AS $$
|
||
DECLARE
|
||
v_f record;
|
||
v_total bigint;
|
||
v_score_6_1 numeric;
|
||
v_score_6_5 numeric;
|
||
v_score_final numeric;
|
||
v_zona text;
|
||
BEGIN
|
||
SELECT * INTO v_f FROM public._dash_parse_filters(p_filters);
|
||
SELECT count(*) INTO v_total
|
||
FROM public._dash_responses(p_survey_id, v_f.f_depto, v_f.f_genero, v_f.f_etario, v_f.f_modalidad);
|
||
IF v_total < 5 THEN
|
||
RETURN jsonb_build_object('status','suppressed','min_n',5,'n',v_total);
|
||
END IF;
|
||
|
||
SELECT AVG(CASE
|
||
WHEN a.value #>> '{}' = 'Sí, me siento acompañado/a' THEN 100.0
|
||
WHEN a.value #>> '{}' = 'Parcialmente, hay cierta comprensión' THEN 66.0
|
||
WHEN a.value #>> '{}' = 'No, es algo bastante invisible' THEN 33.0
|
||
WHEN a.value #>> '{}' = 'No, nunca se ha hablado del tema' THEN 0.0
|
||
ELSE NULL
|
||
END) INTO v_score_6_1
|
||
FROM public._dash_responses(p_survey_id, v_f.f_depto, v_f.f_genero, v_f.f_etario, v_f.f_modalidad) dr
|
||
JOIN public.answers a ON a.response_id = dr.response_id
|
||
JOIN public.questions q ON q.id = a.question_id
|
||
WHERE q.code = '6.1';
|
||
|
||
-- 'No lo sé' → NULL: AVG ignora NULLs en PostgreSQL
|
||
SELECT AVG(CASE
|
||
WHEN a.value #>> '{}' = 'Sí, definitivamente' THEN 100.0
|
||
WHEN a.value #>> '{}' = 'Tal vez, dependería del caso' THEN 66.0
|
||
WHEN a.value #>> '{}' = 'No lo considero necesario' THEN 33.0
|
||
WHEN a.value #>> '{}' = 'No lo sé' THEN NULL
|
||
ELSE NULL
|
||
END) INTO v_score_6_5
|
||
FROM public._dash_responses(p_survey_id, v_f.f_depto, v_f.f_genero, v_f.f_etario, v_f.f_modalidad) dr
|
||
JOIN public.answers a ON a.response_id = dr.response_id
|
||
JOIN public.questions q ON q.id = a.question_id
|
||
WHERE q.code = '6.5';
|
||
|
||
v_score_final := ROUND(
|
||
0.7 * COALESCE(v_score_6_1, 0.0) + 0.3 * COALESCE(v_score_6_5, 0.0),
|
||
1
|
||
);
|
||
|
||
v_zona := CASE
|
||
WHEN v_score_final < 34 THEN 'baja'
|
||
WHEN v_score_final < 67 THEN 'desarrollo'
|
||
ELSE 'consciente'
|
||
END;
|
||
|
||
RETURN jsonb_build_object('status','ok','n',v_total,'score',v_score_final,'zona',v_zona);
|
||
END;
|
||
$$;
|
||
|
||
-- ── Heatmap: horas de cuidado × frecuencia ausentismo (2.2 × 5.2) ──
|
||
-- Sin cambios estructurales respecto a migration 009.
|
||
CREATE OR REPLACE FUNCTION public.rpc_heatmap_intensidad(
|
||
p_survey_id uuid,
|
||
p_filters jsonb DEFAULT '{}'::jsonb
|
||
)
|
||
RETURNS jsonb
|
||
LANGUAGE plpgsql STABLE SECURITY DEFINER
|
||
SET search_path = public
|
||
AS $$
|
||
DECLARE
|
||
v_f record;
|
||
v_total bigint;
|
||
v_rows jsonb;
|
||
BEGIN
|
||
SELECT * INTO v_f FROM public._dash_parse_filters(p_filters);
|
||
SELECT count(*) INTO v_total
|
||
FROM public._dash_responses(p_survey_id, v_f.f_depto, v_f.f_genero, v_f.f_etario, v_f.f_modalidad);
|
||
IF v_total < 5 THEN
|
||
RETURN jsonb_build_object('status','suppressed','min_n',5,'n',v_total);
|
||
END IF;
|
||
|
||
SELECT jsonb_agg(row) INTO v_rows FROM (
|
||
SELECT jsonb_build_object(
|
||
'horas', horas_bin,
|
||
'ausencias', aus_bin,
|
||
'n', CASE WHEN cnt >= 5 THEN cnt ELSE NULL END,
|
||
'suppressed', (cnt < 5)
|
||
) AS row
|
||
FROM (
|
||
SELECT
|
||
ah.value #>> '{}' AS horas_bin,
|
||
aa.value #>> '{}' AS aus_bin,
|
||
count(DISTINCT dr.response_id) AS cnt
|
||
FROM public._dash_responses(p_survey_id, v_f.f_depto, v_f.f_genero, v_f.f_etario, v_f.f_modalidad) dr
|
||
JOIN public.answers ah ON ah.response_id = dr.response_id
|
||
JOIN public.questions qh ON qh.id = ah.question_id AND qh.code = '2.2'
|
||
JOIN public.answers aa ON aa.response_id = dr.response_id
|
||
JOIN public.questions qa ON qa.id = aa.question_id AND qa.code = '5.2'
|
||
GROUP BY 1, 2
|
||
) sub
|
||
ORDER BY horas_bin, aus_bin
|
||
) t;
|
||
|
||
RETURN jsonb_build_object('status','ok','n_total',v_total,'data', COALESCE(v_rows,'[]'::jsonb));
|
||
END;
|
||
$$;
|
||
|
||
-- ── Pirámide demográfica (A5 × A6) ───────────────────────────
|
||
-- Sin cambios respecto a migration 009.
|
||
CREATE OR REPLACE FUNCTION public.rpc_piramide_demografica(
|
||
p_survey_id uuid,
|
||
p_filters jsonb DEFAULT '{}'::jsonb
|
||
)
|
||
RETURNS jsonb
|
||
LANGUAGE plpgsql STABLE SECURITY DEFINER
|
||
SET search_path = public
|
||
AS $$
|
||
DECLARE
|
||
v_f record;
|
||
v_total bigint;
|
||
v_rows jsonb;
|
||
BEGIN
|
||
SELECT * INTO v_f FROM public._dash_parse_filters(p_filters);
|
||
SELECT count(*) INTO v_total
|
||
FROM public._dash_responses(p_survey_id, v_f.f_depto, v_f.f_genero, v_f.f_etario, v_f.f_modalidad);
|
||
IF v_total < 5 THEN
|
||
RETURN jsonb_build_object('status','suppressed','min_n',5,'n',v_total);
|
||
END IF;
|
||
|
||
SELECT jsonb_agg(row) INTO v_rows FROM (
|
||
SELECT jsonb_build_object(
|
||
'etario', etario_val,
|
||
'genero', genero_val,
|
||
'n', CASE WHEN cnt >= 5 THEN cnt ELSE NULL END,
|
||
'suppressed', (cnt < 5)
|
||
) AS row
|
||
FROM (
|
||
SELECT
|
||
ae.value #>> '{}' AS etario_val,
|
||
ag.value #>> '{}' AS genero_val,
|
||
count(DISTINCT dr.response_id) AS cnt
|
||
FROM public._dash_responses(p_survey_id, v_f.f_depto, v_f.f_genero, v_f.f_etario, v_f.f_modalidad) dr
|
||
JOIN public.answers ae ON ae.response_id = dr.response_id
|
||
JOIN public.questions qe ON qe.id = ae.question_id AND qe.code = 'A5'
|
||
JOIN public.answers ag ON ag.response_id = dr.response_id
|
||
JOIN public.questions qg ON qg.id = ag.question_id AND qg.code = 'A6'
|
||
GROUP BY 1, 2
|
||
) sub
|
||
ORDER BY etario_val, genero_val
|
||
) t;
|
||
|
||
RETURN jsonb_build_object('status','ok','n_total',v_total,'data', COALESCE(v_rows,'[]'::jsonb));
|
||
END;
|
||
$$;
|
||
|
||
-- ── Apoyos más demandados (era rpc_politicas_gap) ────────────
|
||
-- Solo 6.2 (multi-select max 3). Top 8 por frecuencia, N≥5.
|
||
CREATE OR REPLACE FUNCTION public.rpc_apoyos_demandados(
|
||
p_survey_id uuid,
|
||
p_filters jsonb DEFAULT '{}'::jsonb
|
||
)
|
||
RETURNS jsonb
|
||
LANGUAGE plpgsql STABLE SECURITY DEFINER
|
||
SET search_path = public
|
||
AS $$
|
||
DECLARE
|
||
v_f record;
|
||
v_total bigint;
|
||
v_rows jsonb;
|
||
BEGIN
|
||
SELECT * INTO v_f FROM public._dash_parse_filters(p_filters);
|
||
SELECT count(*) INTO v_total
|
||
FROM public._dash_responses(p_survey_id, v_f.f_depto, v_f.f_genero, v_f.f_etario, v_f.f_modalidad);
|
||
IF v_total < 5 THEN
|
||
RETURN jsonb_build_object('status','suppressed','min_n',5,'n',v_total);
|
||
END IF;
|
||
|
||
SELECT jsonb_agg(row ORDER BY cnt DESC) INTO v_rows FROM (
|
||
SELECT jsonb_build_object(
|
||
'politica', opt,
|
||
'n', cnt,
|
||
'pct', ROUND(cnt::numeric / v_total * 100, 1)
|
||
) AS row
|
||
FROM (
|
||
SELECT
|
||
jsonb_array_elements_text(a.value) AS opt,
|
||
count(DISTINCT dr.response_id) AS cnt
|
||
FROM public._dash_responses(p_survey_id, v_f.f_depto, v_f.f_genero, v_f.f_etario, v_f.f_modalidad) dr
|
||
JOIN public.answers a ON a.response_id = dr.response_id
|
||
JOIN public.questions q ON q.id = a.question_id
|
||
WHERE q.code = '6.2'
|
||
AND jsonb_typeof(a.value) = 'array'
|
||
GROUP BY 1
|
||
) sub
|
||
WHERE cnt >= 5
|
||
ORDER BY cnt DESC
|
||
LIMIT 8
|
||
) t;
|
||
|
||
RETURN jsonb_build_object('status','ok','n_total',v_total,'data', COALESCE(v_rows,'[]'::jsonb));
|
||
END;
|
||
$$;
|
||
|
||
-- ── Intensidad de cuidado vs. Bienestar (era rpc_intensidad_vs_productividad) ──
|
||
-- 2.2 × 4.3 — sin cambios de lógica respecto a migration 009.
|
||
CREATE OR REPLACE FUNCTION public.rpc_intensidad_vs_bienestar(
|
||
p_survey_id uuid,
|
||
p_filters jsonb DEFAULT '{}'::jsonb
|
||
)
|
||
RETURNS jsonb
|
||
LANGUAGE plpgsql STABLE SECURITY DEFINER
|
||
SET search_path = public
|
||
AS $$
|
||
DECLARE
|
||
v_f record;
|
||
v_total bigint;
|
||
v_rows jsonb;
|
||
BEGIN
|
||
SELECT * INTO v_f FROM public._dash_parse_filters(p_filters);
|
||
SELECT count(*) INTO v_total
|
||
FROM public._dash_responses(p_survey_id, v_f.f_depto, v_f.f_genero, v_f.f_etario, v_f.f_modalidad);
|
||
IF v_total < 5 THEN
|
||
RETURN jsonb_build_object('status','suppressed','min_n',5,'n',v_total);
|
||
END IF;
|
||
|
||
SELECT jsonb_agg(row) INTO v_rows FROM (
|
||
SELECT jsonb_build_object(
|
||
'horas', horas_bin,
|
||
'bienestar', bien_bin,
|
||
'n', CASE WHEN cnt >= 5 THEN cnt ELSE NULL END,
|
||
'suppressed', (cnt < 5)
|
||
) AS row
|
||
FROM (
|
||
SELECT
|
||
ah.value #>> '{}' AS horas_bin,
|
||
ab.value #>> '{}' AS bien_bin,
|
||
count(DISTINCT dr.response_id) AS cnt
|
||
FROM public._dash_responses(p_survey_id, v_f.f_depto, v_f.f_genero, v_f.f_etario, v_f.f_modalidad) dr
|
||
JOIN public.answers ah ON ah.response_id = dr.response_id
|
||
JOIN public.questions qh ON qh.id = ah.question_id AND qh.code = '2.2'
|
||
JOIN public.answers ab ON ab.response_id = dr.response_id
|
||
JOIN public.questions qb ON qb.id = ab.question_id AND qb.code = '4.3'
|
||
WHERE ab.value #>> '{}' ~ '^[0-9]+$'
|
||
GROUP BY 1, 2
|
||
) sub
|
||
ORDER BY horas_bin, bien_bin
|
||
) t;
|
||
|
||
RETURN jsonb_build_object('status','ok','n_total',v_total,'data', COALESCE(v_rows,'[]'::jsonb));
|
||
END;
|
||
$$;
|
||
|
||
-- ── Saturación del cuidador (era rpc_riesgo_retencion) ───────
|
||
-- Indicador compuesto: 4.3 (bienestar) + 5.1 (rendimiento) + 5.2 (ausentismo)
|
||
-- Alta saturación: 3 criterios. Media: 2. Baja: 0–1.
|
||
-- Columnas: Segmento · N · % del total · Apoyo más demandado (6.2)
|
||
CREATE OR REPLACE FUNCTION public.rpc_saturacion_cuidador(
|
||
p_survey_id uuid,
|
||
p_filters jsonb DEFAULT '{}'::jsonb
|
||
)
|
||
RETURNS jsonb
|
||
LANGUAGE plpgsql STABLE SECURITY DEFINER
|
||
SET search_path = public
|
||
AS $$
|
||
DECLARE
|
||
v_f record;
|
||
v_total bigint;
|
||
v_n_alta bigint;
|
||
v_n_media bigint;
|
||
v_n_baja bigint;
|
||
v_apo_alta text;
|
||
v_apo_media text;
|
||
v_apo_baja text;
|
||
BEGIN
|
||
SELECT * INTO v_f FROM public._dash_parse_filters(p_filters);
|
||
SELECT count(*) INTO v_total
|
||
FROM public._dash_responses(p_survey_id, v_f.f_depto, v_f.f_genero, v_f.f_etario, v_f.f_modalidad);
|
||
IF v_total < 5 THEN
|
||
RETURN jsonb_build_object('status','suppressed','min_n',5,'n',v_total);
|
||
END IF;
|
||
|
||
-- Contar por segmento en una sola pasada
|
||
SELECT
|
||
count(*) FILTER (WHERE n_crit = 3),
|
||
count(*) FILTER (WHERE n_crit = 2),
|
||
count(*) FILTER (WHERE n_crit <= 1)
|
||
INTO v_n_alta, v_n_media, v_n_baja
|
||
FROM (
|
||
SELECT
|
||
dr.response_id,
|
||
(CASE WHEN EXISTS (
|
||
SELECT 1 FROM public.answers a JOIN public.questions q ON q.id=a.question_id
|
||
WHERE a.response_id=dr.response_id AND q.code='4.3'
|
||
AND a.value #>> '{}' ~ '^[0-9]+$'
|
||
AND (a.value #>> '{}')::int <= 2
|
||
) THEN 1 ELSE 0 END
|
||
+ CASE WHEN EXISTS (
|
||
SELECT 1 FROM public.answers a JOIN public.questions q ON q.id=a.question_id
|
||
WHERE a.response_id=dr.response_id AND q.code='5.1'
|
||
AND a.value #>> '{}' = 'Casi todos los días'
|
||
) THEN 1 ELSE 0 END
|
||
+ CASE WHEN EXISTS (
|
||
SELECT 1 FROM public.answers a JOIN public.questions q ON q.id=a.question_id
|
||
WHERE a.response_id=dr.response_id AND q.code='5.2'
|
||
AND a.value #>> '{}' IN (
|
||
'Varias veces (4–10 veces al año)',
|
||
'Con frecuencia (más de 10 veces al año)'
|
||
)
|
||
) THEN 1 ELSE 0 END) AS n_crit
|
||
FROM public._dash_responses(p_survey_id, v_f.f_depto, v_f.f_genero, v_f.f_etario, v_f.f_modalidad) dr
|
||
) sub;
|
||
|
||
-- Apoyo más demandado (6.2) por segmento, solo si N≥5
|
||
IF v_n_alta >= 5 THEN
|
||
SELECT opt INTO v_apo_alta FROM (
|
||
SELECT jsonb_array_elements_text(a.value) AS opt,
|
||
count(DISTINCT dr.response_id) AS cnt
|
||
FROM (
|
||
SELECT response_id FROM (
|
||
SELECT dr2.response_id,
|
||
(CASE WHEN EXISTS (SELECT 1 FROM public.answers a2 JOIN public.questions q2 ON q2.id=a2.question_id WHERE a2.response_id=dr2.response_id AND q2.code='4.3' AND a2.value#>>'{}' ~ '^[0-9]+$' AND (a2.value#>>'{}')::int <= 2) THEN 1 ELSE 0 END
|
||
+ CASE WHEN EXISTS (SELECT 1 FROM public.answers a3 JOIN public.questions q3 ON q3.id=a3.question_id WHERE a3.response_id=dr2.response_id AND q3.code='5.1' AND a3.value#>>'{}' = 'Casi todos los días') THEN 1 ELSE 0 END
|
||
+ CASE WHEN EXISTS (SELECT 1 FROM public.answers a4 JOIN public.questions q4 ON q4.id=a4.question_id WHERE a4.response_id=dr2.response_id AND q4.code='5.2' AND a4.value#>>'{}' IN ('Varias veces (4–10 veces al año)','Con frecuencia (más de 10 veces al año)')) THEN 1 ELSE 0 END) AS n_crit
|
||
FROM public._dash_responses(p_survey_id, v_f.f_depto, v_f.f_genero, v_f.f_etario, v_f.f_modalidad) dr2
|
||
) s WHERE n_crit = 3
|
||
) seg
|
||
JOIN public.answers a ON a.response_id = seg.response_id
|
||
JOIN public.questions q ON q.id = a.question_id
|
||
WHERE q.code = '6.2' AND jsonb_typeof(a.value) = 'array'
|
||
GROUP BY opt ORDER BY cnt DESC LIMIT 1
|
||
) t;
|
||
END IF;
|
||
|
||
IF v_n_media >= 5 THEN
|
||
SELECT opt INTO v_apo_media FROM (
|
||
SELECT jsonb_array_elements_text(a.value) AS opt,
|
||
count(DISTINCT dr.response_id) AS cnt
|
||
FROM (
|
||
SELECT response_id FROM (
|
||
SELECT dr2.response_id,
|
||
(CASE WHEN EXISTS (SELECT 1 FROM public.answers a2 JOIN public.questions q2 ON q2.id=a2.question_id WHERE a2.response_id=dr2.response_id AND q2.code='4.3' AND a2.value#>>'{}' ~ '^[0-9]+$' AND (a2.value#>>'{}')::int <= 2) THEN 1 ELSE 0 END
|
||
+ CASE WHEN EXISTS (SELECT 1 FROM public.answers a3 JOIN public.questions q3 ON q3.id=a3.question_id WHERE a3.response_id=dr2.response_id AND q3.code='5.1' AND a3.value#>>'{}' = 'Casi todos los días') THEN 1 ELSE 0 END
|
||
+ CASE WHEN EXISTS (SELECT 1 FROM public.answers a4 JOIN public.questions q4 ON q4.id=a4.question_id WHERE a4.response_id=dr2.response_id AND q4.code='5.2' AND a4.value#>>'{}' IN ('Varias veces (4–10 veces al año)','Con frecuencia (más de 10 veces al año)')) THEN 1 ELSE 0 END) AS n_crit
|
||
FROM public._dash_responses(p_survey_id, v_f.f_depto, v_f.f_genero, v_f.f_etario, v_f.f_modalidad) dr2
|
||
) s WHERE n_crit = 2
|
||
) seg
|
||
JOIN public.answers a ON a.response_id = seg.response_id
|
||
JOIN public.questions q ON q.id = a.question_id
|
||
WHERE q.code = '6.2' AND jsonb_typeof(a.value) = 'array'
|
||
GROUP BY opt ORDER BY cnt DESC LIMIT 1
|
||
) t;
|
||
END IF;
|
||
|
||
IF v_n_baja >= 5 THEN
|
||
SELECT opt INTO v_apo_baja FROM (
|
||
SELECT jsonb_array_elements_text(a.value) AS opt,
|
||
count(DISTINCT dr.response_id) AS cnt
|
||
FROM (
|
||
SELECT response_id FROM (
|
||
SELECT dr2.response_id,
|
||
(CASE WHEN EXISTS (SELECT 1 FROM public.answers a2 JOIN public.questions q2 ON q2.id=a2.question_id WHERE a2.response_id=dr2.response_id AND q2.code='4.3' AND a2.value#>>'{}' ~ '^[0-9]+$' AND (a2.value#>>'{}')::int <= 2) THEN 1 ELSE 0 END
|
||
+ CASE WHEN EXISTS (SELECT 1 FROM public.answers a3 JOIN public.questions q3 ON q3.id=a3.question_id WHERE a3.response_id=dr2.response_id AND q3.code='5.1' AND a3.value#>>'{}' = 'Casi todos los días') THEN 1 ELSE 0 END
|
||
+ CASE WHEN EXISTS (SELECT 1 FROM public.answers a4 JOIN public.questions q4 ON q4.id=a4.question_id WHERE a4.response_id=dr2.response_id AND q4.code='5.2' AND a4.value#>>'{}' IN ('Varias veces (4–10 veces al año)','Con frecuencia (más de 10 veces al año)')) THEN 1 ELSE 0 END) AS n_crit
|
||
FROM public._dash_responses(p_survey_id, v_f.f_depto, v_f.f_genero, v_f.f_etario, v_f.f_modalidad) dr2
|
||
) s WHERE n_crit <= 1
|
||
) seg
|
||
JOIN public.answers a ON a.response_id = seg.response_id
|
||
JOIN public.questions q ON q.id = a.question_id
|
||
WHERE q.code = '6.2' AND jsonb_typeof(a.value) = 'array'
|
||
GROUP BY opt ORDER BY cnt DESC LIMIT 1
|
||
) t;
|
||
END IF;
|
||
|
||
RETURN jsonb_build_object(
|
||
'status', 'ok',
|
||
'n_total', v_total,
|
||
'segmentos', jsonb_build_array(
|
||
jsonb_build_object(
|
||
'segmento', 'Alta saturación',
|
||
'n', CASE WHEN v_n_alta >= 5 THEN v_n_alta ELSE NULL END,
|
||
'pct', CASE WHEN v_n_alta >= 5 THEN ROUND(v_n_alta::numeric/v_total*100,1) ELSE NULL END,
|
||
'apoyo', CASE WHEN v_n_alta >= 5 THEN v_apo_alta ELSE NULL END,
|
||
'suppressed', (v_n_alta < 5)
|
||
),
|
||
jsonb_build_object(
|
||
'segmento', 'Saturación media',
|
||
'n', CASE WHEN v_n_media >= 5 THEN v_n_media ELSE NULL END,
|
||
'pct', CASE WHEN v_n_media >= 5 THEN ROUND(v_n_media::numeric/v_total*100,1) ELSE NULL END,
|
||
'apoyo', CASE WHEN v_n_media >= 5 THEN v_apo_media ELSE NULL END,
|
||
'suppressed', (v_n_media < 5)
|
||
),
|
||
jsonb_build_object(
|
||
'segmento', 'Baja saturación',
|
||
'n', CASE WHEN v_n_baja >= 5 THEN v_n_baja ELSE NULL END,
|
||
'pct', CASE WHEN v_n_baja >= 5 THEN ROUND(v_n_baja::numeric/v_total*100,1) ELSE NULL END,
|
||
'apoyo', CASE WHEN v_n_baja >= 5 THEN v_apo_baja ELSE NULL END,
|
||
'suppressed', (v_n_baja < 5)
|
||
)
|
||
)
|
||
);
|
||
END;
|
||
$$;
|