405 lines
15 KiB
PL/PgSQL
405 lines
15 KiB
PL/PgSQL
-- =============================================================================
|
|
-- AgenciaPsi — Functions — Notificações
|
|
-- =============================================================================
|
|
-- cancel_notifications_on_opt_out, cancel_notifications_on_session_cancel,
|
|
-- cancel_patient_pending_notifications, cleanup_notification_queue,
|
|
-- notify_on_intake, notify_on_scheduling, notify_on_session_status,
|
|
-- populate_notification_queue, unstick_notification_queue
|
|
-- =============================================================================
|
|
|
|
CREATE FUNCTION public.cancel_notifications_on_opt_out() RETURNS trigger
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
AS $$
|
|
BEGIN
|
|
-- WhatsApp opt-out
|
|
IF OLD.whatsapp_opt_in = true AND NEW.whatsapp_opt_in = false THEN
|
|
PERFORM public.cancel_patient_pending_notifications(
|
|
NEW.patient_id, 'whatsapp'
|
|
);
|
|
END IF;
|
|
-- Email opt-out
|
|
IF OLD.email_opt_in = true AND NEW.email_opt_in = false THEN
|
|
PERFORM public.cancel_patient_pending_notifications(
|
|
NEW.patient_id, 'email'
|
|
);
|
|
END IF;
|
|
-- SMS opt-out
|
|
IF OLD.sms_opt_in = true AND NEW.sms_opt_in = false THEN
|
|
PERFORM public.cancel_patient_pending_notifications(
|
|
NEW.patient_id, 'sms'
|
|
);
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.cancel_notifications_on_opt_out() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: cancel_notifications_on_session_cancel(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.cancel_notifications_on_session_cancel() RETURNS trigger
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
AS $$
|
|
BEGIN
|
|
IF NEW.status IN ('cancelado', 'excluido')
|
|
AND OLD.status NOT IN ('cancelado', 'excluido')
|
|
THEN
|
|
PERFORM public.cancel_patient_pending_notifications(
|
|
NEW.patient_id, NULL, NEW.id
|
|
);
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.cancel_notifications_on_session_cancel() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: cancel_patient_pending_notifications(uuid, text, uuid); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.cancel_patient_pending_notifications(p_patient_id uuid, p_channel text DEFAULT NULL::text, p_evento_id uuid DEFAULT NULL::uuid) RETURNS integer
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
AS $$
|
|
DECLARE
|
|
v_canceled integer;
|
|
BEGIN
|
|
UPDATE public.notification_queue
|
|
SET status = 'cancelado',
|
|
updated_at = now()
|
|
WHERE patient_id = p_patient_id
|
|
AND status IN ('pendente', 'processando')
|
|
AND (p_channel IS NULL OR channel = p_channel)
|
|
AND (p_evento_id IS NULL OR agenda_evento_id = p_evento_id);
|
|
|
|
GET DIAGNOSTICS v_canceled = ROW_COUNT;
|
|
RETURN v_canceled;
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.cancel_patient_pending_notifications(p_patient_id uuid, p_channel text, p_evento_id uuid) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: cancel_recurrence_from(uuid, date); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.cancel_recurrence_from(p_recurrence_id uuid, p_from_date date) RETURNS void
|
|
CREATE FUNCTION public.cancel_patient_pending_notifications(p_patient_id uuid, p_channel text DEFAULT NULL::text, p_evento_id uuid DEFAULT NULL::uuid) RETURNS integer
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
AS $$
|
|
DECLARE
|
|
v_canceled integer;
|
|
BEGIN
|
|
UPDATE public.notification_queue
|
|
SET status = 'cancelado',
|
|
updated_at = now()
|
|
WHERE patient_id = p_patient_id
|
|
AND status IN ('pendente', 'processando')
|
|
AND (p_channel IS NULL OR channel = p_channel)
|
|
AND (p_evento_id IS NULL OR agenda_evento_id = p_evento_id);
|
|
|
|
GET DIAGNOSTICS v_canceled = ROW_COUNT;
|
|
RETURN v_canceled;
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.cancel_patient_pending_notifications(p_patient_id uuid, p_channel text, p_evento_id uuid) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: cancel_recurrence_from(uuid, date); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.cancel_recurrence_from(p_recurrence_id uuid, p_from_date date) RETURNS void
|
|
CREATE FUNCTION public.cleanup_notification_queue() RETURNS integer
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
AS $$
|
|
DECLARE
|
|
v_deleted integer;
|
|
BEGIN
|
|
DELETE FROM public.notification_queue
|
|
WHERE status IN ('enviado', 'cancelado', 'ignorado')
|
|
AND created_at < now() - interval '90 days';
|
|
|
|
GET DIAGNOSTICS v_deleted = ROW_COUNT;
|
|
RETURN v_deleted;
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.cleanup_notification_queue() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: create_clinic_tenant(text); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.create_clinic_tenant(p_name text) RETURNS uuid
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
AS $$
|
|
declare
|
|
v_uid uuid;
|
|
v_tenant uuid;
|
|
v_name text;
|
|
begin
|
|
v_uid := auth.uid();
|
|
if v_uid is null then
|
|
raise exception 'Not authenticated';
|
|
end if;
|
|
|
|
v_name := nullif(trim(coalesce(p_name, '')), '');
|
|
if v_name is null then
|
|
v_name := 'Clínica';
|
|
end if;
|
|
|
|
insert into public.tenants (name, kind, created_at)
|
|
values (v_name, 'clinic', now())
|
|
returning id into v_tenant;
|
|
|
|
insert into public.tenant_members (tenant_id, user_id, role, status, created_at)
|
|
values (v_tenant, v_uid, 'tenant_admin', 'active', now());
|
|
|
|
return v_tenant;
|
|
end;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.create_clinic_tenant(p_name text) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: financial_records; Type: TABLE; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE TABLE public.financial_records (
|
|
CREATE FUNCTION public.notify_on_intake() RETURNS trigger
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
AS $$
|
|
BEGIN
|
|
IF NEW.status = 'new' THEN
|
|
INSERT INTO public.notifications (
|
|
owner_id,
|
|
tenant_id,
|
|
type,
|
|
ref_id,
|
|
ref_table,
|
|
payload
|
|
)
|
|
VALUES (
|
|
NEW.owner_id,
|
|
NEW.tenant_id,
|
|
'new_patient',
|
|
NEW.id,
|
|
'patient_intake_requests',
|
|
jsonb_build_object(
|
|
'title', 'Novo cadastro externo',
|
|
'detail', COALESCE(NEW.nome_completo, 'Paciente'),
|
|
'deeplink', '/therapist/patients/cadastro/recebidos',
|
|
'avatar_initials', upper(left(COALESCE(NEW.nome_completo, '?'), 2))
|
|
)
|
|
);
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.notify_on_intake() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: notify_on_scheduling(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.notify_on_scheduling() RETURNS trigger
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
AS $$ BEGIN IF NEW.status = 'pendente' THEN
|
|
INSERT INTO public.notifications ( owner_id, tenant_id, type, ref_id, ref_table, payload ) VALUES (
|
|
NEW.owner_id, NEW.tenant_id,
|
|
'new_scheduling', NEW.id, 'agendador_solicitacoes', jsonb_build_object( 'title', 'Nova solicitação de agendamento', 'detail', COALESCE(NEW.paciente_nome, 'Paciente') || ' ' || COALESCE(NEW.paciente_sobrenome, '') || ' — ' || COALESCE(NEW.tipo, ''), 'deeplink', '/therapist/agendamentos-recebidos', 'avatar_initials', upper(left(COALESCE(NEW.paciente_nome, '?'), 1) || left(COALESCE(NEW.paciente_sobrenome, ''), 1)) ) ); END IF; RETURN NEW; END; $$;
|
|
|
|
|
|
ALTER FUNCTION public.notify_on_scheduling() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: notify_on_session_status(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.notify_on_session_status() RETURNS trigger
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
AS $$
|
|
DECLARE
|
|
v_nome text;
|
|
BEGIN
|
|
IF NEW.status IN ('faltou', 'cancelado') AND OLD.status IS DISTINCT FROM NEW.status THEN
|
|
|
|
SELECT nome_completo
|
|
INTO v_nome
|
|
FROM public.patients
|
|
WHERE id = NEW.patient_id
|
|
LIMIT 1;
|
|
|
|
INSERT INTO public.notifications (
|
|
owner_id,
|
|
tenant_id,
|
|
type,
|
|
ref_id,
|
|
ref_table,
|
|
payload
|
|
)
|
|
VALUES (
|
|
NEW.owner_id,
|
|
NEW.tenant_id,
|
|
'session_status',
|
|
NEW.id,
|
|
'agenda_eventos',
|
|
jsonb_build_object(
|
|
'title', CASE WHEN NEW.status = 'faltou' THEN 'Paciente faltou' ELSE 'Sessão cancelada' END,
|
|
'detail', COALESCE(v_nome, 'Paciente') || ' — ' || to_char(NEW.inicio_em, 'DD/MM HH24:MI'),
|
|
'deeplink', '/therapist/agenda',
|
|
'avatar_initials', upper(left(COALESCE(v_nome, '?'), 2))
|
|
)
|
|
);
|
|
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.notify_on_session_status() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: on_new_user_seed_patient_groups(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.on_new_user_seed_patient_groups() RETURNS trigger
|
|
CREATE FUNCTION public.populate_notification_queue() RETURNS void
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
AS $$
|
|
BEGIN
|
|
INSERT INTO public.notification_queue (
|
|
tenant_id, owner_id, agenda_evento_id, patient_id,
|
|
channel, template_key, schedule_key,
|
|
resolved_vars, recipient_address,
|
|
scheduled_at, idempotency_key
|
|
)
|
|
SELECT
|
|
ae.tenant_id,
|
|
ae.owner_id,
|
|
ae.id AS agenda_evento_id,
|
|
ae.patient_id,
|
|
ch.channel,
|
|
'session.' || REPLACE(ns.event_type, '_sessao', '') || '.' || ch.channel,
|
|
ns.schedule_key,
|
|
jsonb_build_object(
|
|
'nome_paciente', COALESCE(p.nome_completo, 'Paciente'),
|
|
'data_sessao', TO_CHAR(ae.inicio_em AT TIME ZONE 'America/Sao_Paulo', 'DD/MM/YYYY'),
|
|
'hora_sessao', TO_CHAR(ae.inicio_em AT TIME ZONE 'America/Sao_Paulo', 'HH24:MI'),
|
|
'nome_terapeuta', COALESCE(prof.full_name, 'Terapeuta'),
|
|
'modalidade', COALESCE(ae.modalidade, 'Presencial'),
|
|
'titulo', COALESCE(ae.titulo, 'Sessão')
|
|
),
|
|
CASE ch.channel
|
|
WHEN 'whatsapp' THEN COALESCE(p.telefone, '')
|
|
WHEN 'sms' THEN COALESCE(p.telefone, '')
|
|
WHEN 'email' THEN COALESCE(p.email_principal, '')
|
|
END,
|
|
CASE
|
|
WHEN (ae.inicio_em - (ns.offset_minutes || ' minutes')::interval)::time
|
|
< ns.allowed_time_start
|
|
THEN DATE_TRUNC('day', ae.inicio_em - (ns.offset_minutes || ' minutes')::interval)
|
|
+ ns.allowed_time_start
|
|
WHEN (ae.inicio_em - (ns.offset_minutes || ' minutes')::interval)::time
|
|
> ns.allowed_time_end
|
|
THEN DATE_TRUNC('day', ae.inicio_em - (ns.offset_minutes || ' minutes')::interval)
|
|
+ ns.allowed_time_start
|
|
ELSE ae.inicio_em - (ns.offset_minutes || ' minutes')::interval
|
|
END,
|
|
ae.id::text || ':' || ns.schedule_key || ':' || ch.channel || ':'
|
|
|| ae.inicio_em::date::text
|
|
FROM public.agenda_eventos ae
|
|
JOIN public.patients p ON p.id = ae.patient_id
|
|
LEFT JOIN public.profiles prof ON prof.id = ae.owner_id
|
|
JOIN public.notification_schedules ns
|
|
ON ns.owner_id = ae.owner_id
|
|
AND ns.is_active = true
|
|
AND ns.deleted_at IS NULL
|
|
AND ns.trigger_type = 'before_event'
|
|
AND ns.event_type = 'lembrete_sessao'
|
|
JOIN public.notification_channels nc
|
|
ON nc.owner_id = ae.owner_id
|
|
AND nc.is_active = true
|
|
AND nc.deleted_at IS NULL
|
|
CROSS JOIN LATERAL (
|
|
SELECT 'whatsapp' AS channel WHERE ns.whatsapp_enabled AND nc.channel = 'whatsapp'
|
|
UNION ALL
|
|
SELECT 'email' AS channel WHERE ns.email_enabled AND nc.channel = 'email'
|
|
UNION ALL
|
|
SELECT 'sms' AS channel WHERE ns.sms_enabled AND nc.channel = 'sms'
|
|
) ch
|
|
LEFT JOIN public.notification_preferences np
|
|
ON np.patient_id = ae.patient_id
|
|
AND np.owner_id = ae.owner_id
|
|
AND np.deleted_at IS NULL
|
|
WHERE
|
|
ae.inicio_em > now()
|
|
AND ae.inicio_em <= now() + interval '48 hours'
|
|
AND ae.status NOT IN ('cancelado', 'faltou')
|
|
AND CASE ch.channel
|
|
WHEN 'whatsapp' THEN COALESCE(p.telefone, '') != ''
|
|
WHEN 'sms' THEN COALESCE(p.telefone, '') != ''
|
|
WHEN 'email' THEN COALESCE(p.email_principal, '') != ''
|
|
END
|
|
AND CASE ch.channel
|
|
WHEN 'whatsapp' THEN COALESCE(np.whatsapp_opt_in, true)
|
|
WHEN 'email' THEN COALESCE(np.email_opt_in, true)
|
|
WHEN 'sms' THEN COALESCE(np.sms_opt_in, false)
|
|
END
|
|
AND EXISTS (
|
|
SELECT 1 FROM public.profiles tp
|
|
WHERE tp.id = ae.owner_id
|
|
AND COALESCE(tp.notify_reminders, true) = true
|
|
)
|
|
ON CONFLICT (idempotency_key) DO NOTHING;
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.populate_notification_queue() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: prevent_promoting_to_system(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.prevent_promoting_to_system() RETURNS trigger
|
|
CREATE FUNCTION public.unstick_notification_queue() RETURNS integer
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
AS $$
|
|
DECLARE
|
|
v_unstuck integer;
|
|
BEGIN
|
|
UPDATE public.notification_queue
|
|
SET status = 'pendente',
|
|
attempts = attempts + 1,
|
|
last_error = 'Timeout: preso em processando por >10min',
|
|
next_retry_at = now() + interval '2 minutes'
|
|
WHERE status = 'processando'
|
|
AND updated_at < now() - interval '10 minutes';
|
|
|
|
GET DIAGNOSTICS v_unstuck = ROW_COUNT;
|
|
RETURN v_unstuck;
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.unstick_notification_queue() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: update_payment_settings_updated_at(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.update_payment_settings_updated_at() RETURNS trigger
|