651 lines
20 KiB
PL/PgSQL
651 lines
20 KiB
PL/PgSQL
-- =============================================================================
|
|
-- AgenciaPsi — Functions — Agenda
|
|
-- =============================================================================
|
|
-- agenda_cfg_sync, agendador_dias_disponiveis, agendador_gerar_slug,
|
|
-- agendador_slots_disponiveis, cancel_recurrence_from,
|
|
-- cancelar_eventos_serie, fn_agenda_regras_semanais_no_overlap,
|
|
-- split_recurrence_at, sync_busy_mirror, set_updated_at_recurrence
|
|
-- =============================================================================
|
|
|
|
CREATE FUNCTION public.agenda_cfg_sync() RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
begin
|
|
if new.agenda_view_mode = 'custom' then
|
|
new.usar_horario_admin_custom := true;
|
|
new.admin_inicio_visualizacao := new.agenda_custom_start;
|
|
new.admin_fim_visualizacao := new.agenda_custom_end;
|
|
else
|
|
new.usar_horario_admin_custom := false;
|
|
end if;
|
|
|
|
return new;
|
|
end;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.agenda_cfg_sync() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: agendador_dias_disponiveis(text, integer, integer); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.agendador_dias_disponiveis(p_slug text, p_ano integer, p_mes integer) RETURNS TABLE(data date, tem_slots boolean)
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
SET search_path TO 'public'
|
|
AS $$
|
|
DECLARE
|
|
v_owner_id uuid;
|
|
v_antecedencia int;
|
|
v_agora timestamptz;
|
|
v_data date;
|
|
v_data_inicio date;
|
|
v_data_fim date;
|
|
v_db_dow int;
|
|
v_tem_slot boolean;
|
|
v_bloqueado boolean;
|
|
BEGIN
|
|
SELECT c.owner_id, c.antecedencia_minima_horas
|
|
INTO v_owner_id, v_antecedencia
|
|
FROM public.agendador_configuracoes c
|
|
WHERE c.link_slug = p_slug AND c.ativo = true
|
|
LIMIT 1;
|
|
|
|
IF v_owner_id IS NULL THEN RETURN; END IF;
|
|
|
|
v_agora := now();
|
|
v_data_inicio := make_date(p_ano, p_mes, 1);
|
|
v_data_fim := (v_data_inicio + interval '1 month' - interval '1 day')::date;
|
|
|
|
v_data := v_data_inicio;
|
|
WHILE v_data <= v_data_fim LOOP
|
|
v_db_dow := extract(dow from v_data::timestamp)::int;
|
|
|
|
-- ── Dia inteiro bloqueado? (agenda_bloqueios) ─────────────────────────
|
|
SELECT EXISTS (
|
|
SELECT 1 FROM public.agenda_bloqueios b
|
|
WHERE b.owner_id = v_owner_id
|
|
AND b.data_inicio <= v_data
|
|
AND COALESCE(b.data_fim, v_data) >= v_data
|
|
AND b.hora_inicio IS NULL -- bloqueio de dia inteiro
|
|
AND (
|
|
(NOT b.recorrente)
|
|
OR (b.recorrente AND b.dia_semana = v_db_dow)
|
|
)
|
|
) INTO v_bloqueado;
|
|
|
|
IF v_bloqueado THEN
|
|
v_data := v_data + 1;
|
|
CONTINUE;
|
|
END IF;
|
|
|
|
-- ── Tem slots disponíveis no dia? ─────────────────────────────────────
|
|
SELECT EXISTS (
|
|
SELECT 1 FROM public.agenda_online_slots s
|
|
WHERE s.owner_id = v_owner_id
|
|
AND s.weekday = v_db_dow
|
|
AND s.enabled = true
|
|
AND (v_data::text || ' ' || s.time::text)::timestamp
|
|
AT TIME ZONE 'America/Sao_Paulo'
|
|
>= v_agora + (v_antecedencia || ' hours')::interval
|
|
) INTO v_tem_slot;
|
|
|
|
IF v_tem_slot THEN
|
|
data := v_data;
|
|
tem_slots := true;
|
|
RETURN NEXT;
|
|
END IF;
|
|
|
|
v_data := v_data + 1;
|
|
END LOOP;
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.agendador_dias_disponiveis(p_slug text, p_ano integer, p_mes integer) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: agendador_gerar_slug(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.agendador_gerar_slug() RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
DECLARE
|
|
v_slug text;
|
|
v_exists boolean;
|
|
BEGIN
|
|
-- só gera se ativou e não tem slug ainda
|
|
IF NEW.ativo = true AND (NEW.link_slug IS NULL OR NEW.link_slug = '') THEN
|
|
LOOP
|
|
v_slug := lower(substring(replace(gen_random_uuid()::text, '-', ''), 1, 8));
|
|
SELECT EXISTS (
|
|
SELECT 1 FROM public.agendador_configuracoes
|
|
WHERE link_slug = v_slug AND owner_id <> NEW.owner_id
|
|
) INTO v_exists;
|
|
EXIT WHEN NOT v_exists;
|
|
END LOOP;
|
|
NEW.link_slug := v_slug;
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.agendador_gerar_slug() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: agendador_slots_disponiveis(text, date); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.agendador_slots_disponiveis(p_slug text, p_data date) RETURNS TABLE(hora time without time zone, disponivel boolean)
|
|
CREATE FUNCTION public.agendador_gerar_slug() RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
DECLARE
|
|
v_slug text;
|
|
v_exists boolean;
|
|
BEGIN
|
|
-- só gera se ativou e não tem slug ainda
|
|
IF NEW.ativo = true AND (NEW.link_slug IS NULL OR NEW.link_slug = '') THEN
|
|
LOOP
|
|
v_slug := lower(substring(replace(gen_random_uuid()::text, '-', ''), 1, 8));
|
|
SELECT EXISTS (
|
|
SELECT 1 FROM public.agendador_configuracoes
|
|
WHERE link_slug = v_slug AND owner_id <> NEW.owner_id
|
|
) INTO v_exists;
|
|
EXIT WHEN NOT v_exists;
|
|
END LOOP;
|
|
NEW.link_slug := v_slug;
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.agendador_gerar_slug() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: agendador_slots_disponiveis(text, date); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.agendador_slots_disponiveis(p_slug text, p_data date) RETURNS TABLE(hora time without time zone, disponivel boolean)
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
SET search_path TO 'public'
|
|
AS $$
|
|
DECLARE
|
|
v_owner_id uuid;
|
|
v_duracao int;
|
|
v_antecedencia int;
|
|
v_agora timestamptz;
|
|
v_db_dow int;
|
|
v_slot time;
|
|
v_slot_fim time;
|
|
v_slot_ts timestamptz;
|
|
v_ocupado boolean;
|
|
-- loop de recorrências
|
|
v_rule RECORD;
|
|
v_rule_start_dow int;
|
|
v_first_occ date;
|
|
v_day_diff int;
|
|
v_ex_type text;
|
|
BEGIN
|
|
SELECT c.owner_id, c.duracao_sessao_min, c.antecedencia_minima_horas
|
|
INTO v_owner_id, v_duracao, v_antecedencia
|
|
FROM public.agendador_configuracoes c
|
|
WHERE c.link_slug = p_slug AND c.ativo = true
|
|
LIMIT 1;
|
|
|
|
IF v_owner_id IS NULL THEN RETURN; END IF;
|
|
|
|
v_agora := now();
|
|
v_db_dow := extract(dow from p_data::timestamp)::int;
|
|
|
|
-- ── Dia inteiro bloqueado? (agenda_bloqueios sem hora) ───────────────────
|
|
-- Se sim, não há nenhum slot disponível — retorna vazio.
|
|
IF EXISTS (
|
|
SELECT 1 FROM public.agenda_bloqueios b
|
|
WHERE b.owner_id = v_owner_id
|
|
AND b.data_inicio <= p_data
|
|
AND COALESCE(b.data_fim, p_data) >= p_data
|
|
AND b.hora_inicio IS NULL -- bloqueio de dia inteiro
|
|
AND (
|
|
(NOT b.recorrente)
|
|
OR (b.recorrente AND b.dia_semana = v_db_dow)
|
|
)
|
|
) THEN
|
|
RETURN;
|
|
END IF;
|
|
|
|
FOR v_slot IN
|
|
SELECT s.time
|
|
FROM public.agenda_online_slots s
|
|
WHERE s.owner_id = v_owner_id
|
|
AND s.weekday = v_db_dow
|
|
AND s.enabled = true
|
|
ORDER BY s.time
|
|
LOOP
|
|
v_slot_fim := v_slot + (v_duracao || ' minutes')::interval;
|
|
v_ocupado := false;
|
|
|
|
-- ── Antecedência mínima ──────────────────────────────────────────────────
|
|
v_slot_ts := (p_data::text || ' ' || v_slot::text)::timestamp
|
|
AT TIME ZONE 'America/Sao_Paulo';
|
|
IF v_slot_ts < v_agora + (v_antecedencia || ' hours')::interval THEN
|
|
v_ocupado := true;
|
|
END IF;
|
|
|
|
-- ── Bloqueio de horário específico (agenda_bloqueios com hora) ───────────
|
|
IF NOT v_ocupado THEN
|
|
SELECT EXISTS (
|
|
SELECT 1 FROM public.agenda_bloqueios b
|
|
WHERE b.owner_id = v_owner_id
|
|
AND b.data_inicio <= p_data
|
|
AND COALESCE(b.data_fim, p_data) >= p_data
|
|
AND b.hora_inicio IS NOT NULL
|
|
AND b.hora_inicio < v_slot_fim
|
|
AND b.hora_fim > v_slot
|
|
AND (
|
|
(NOT b.recorrente)
|
|
OR (b.recorrente AND b.dia_semana = v_db_dow)
|
|
)
|
|
) INTO v_ocupado;
|
|
END IF;
|
|
|
|
-- ── Eventos avulsos internos (agenda_eventos) ────────────────────────────
|
|
IF NOT v_ocupado THEN
|
|
SELECT EXISTS (
|
|
SELECT 1 FROM public.agenda_eventos e
|
|
WHERE e.owner_id = v_owner_id
|
|
AND e.status::text NOT IN ('cancelado', 'faltou')
|
|
AND (e.inicio_em AT TIME ZONE 'America/Sao_Paulo')::date = p_data
|
|
AND (e.inicio_em AT TIME ZONE 'America/Sao_Paulo')::time < v_slot_fim
|
|
AND (e.fim_em AT TIME ZONE 'America/Sao_Paulo')::time > v_slot
|
|
) INTO v_ocupado;
|
|
END IF;
|
|
|
|
-- ── Recorrências ativas (recurrence_rules) ───────────────────────────────
|
|
IF NOT v_ocupado THEN
|
|
FOR v_rule IN
|
|
SELECT
|
|
r.id,
|
|
r.start_date::date AS start_date,
|
|
r.end_date::date AS end_date,
|
|
r.start_time::time AS start_time,
|
|
r.end_time::time AS end_time,
|
|
COALESCE(r.interval, 1)::int AS interval
|
|
FROM public.recurrence_rules r
|
|
WHERE r.owner_id = v_owner_id
|
|
AND r.status = 'ativo'
|
|
AND p_data >= r.start_date::date
|
|
AND (r.end_date IS NULL OR p_data <= r.end_date::date)
|
|
AND v_db_dow = ANY(r.weekdays)
|
|
AND r.start_time::time < v_slot_fim
|
|
AND r.end_time::time > v_slot
|
|
LOOP
|
|
v_rule_start_dow := extract(dow from v_rule.start_date)::int;
|
|
v_first_occ := v_rule.start_date
|
|
+ (((v_db_dow - v_rule_start_dow + 7) % 7))::int;
|
|
v_day_diff := (p_data - v_first_occ)::int;
|
|
|
|
IF v_day_diff >= 0 AND v_day_diff % (7 * v_rule.interval) = 0 THEN
|
|
v_ex_type := NULL;
|
|
SELECT ex.type INTO v_ex_type
|
|
FROM public.recurrence_exceptions ex
|
|
WHERE ex.recurrence_id = v_rule.id
|
|
AND ex.original_date = p_data
|
|
LIMIT 1;
|
|
|
|
IF v_ex_type IS NULL OR v_ex_type NOT IN (
|
|
'cancel_session', 'patient_missed',
|
|
'therapist_canceled', 'holiday_block',
|
|
'reschedule_session'
|
|
) THEN
|
|
v_ocupado := true;
|
|
EXIT;
|
|
END IF;
|
|
END IF;
|
|
END LOOP;
|
|
END IF;
|
|
|
|
-- ── Recorrências remarcadas para este dia ────────────────────────────────
|
|
IF NOT v_ocupado THEN
|
|
SELECT EXISTS (
|
|
SELECT 1
|
|
FROM public.recurrence_exceptions ex
|
|
JOIN public.recurrence_rules r ON r.id = ex.recurrence_id
|
|
WHERE r.owner_id = v_owner_id
|
|
AND r.status = 'ativo'
|
|
AND ex.type = 'reschedule_session'
|
|
AND ex.new_date = p_data
|
|
AND COALESCE(ex.new_start_time, r.start_time)::time < v_slot_fim
|
|
AND COALESCE(ex.new_end_time, r.end_time)::time > v_slot
|
|
) INTO v_ocupado;
|
|
END IF;
|
|
|
|
-- ── Solicitações públicas pendentes ──────────────────────────────────────
|
|
IF NOT v_ocupado THEN
|
|
SELECT EXISTS (
|
|
SELECT 1 FROM public.agendador_solicitacoes sol
|
|
WHERE sol.owner_id = v_owner_id
|
|
AND sol.status = 'pendente'
|
|
AND sol.data_solicitada = p_data
|
|
AND sol.hora_solicitada = v_slot
|
|
AND (sol.reservado_ate IS NULL OR sol.reservado_ate > v_agora)
|
|
) INTO v_ocupado;
|
|
END IF;
|
|
|
|
hora := v_slot;
|
|
disponivel := NOT v_ocupado;
|
|
RETURN NEXT;
|
|
END LOOP;
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.agendador_slots_disponiveis(p_slug text, p_data date) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: auto_create_financial_record_from_session(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.auto_create_financial_record_from_session() RETURNS trigger
|
|
CREATE FUNCTION public.cancel_recurrence_from(p_recurrence_id uuid, p_from_date date) RETURNS void
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
SET search_path TO 'public'
|
|
AS $$
|
|
BEGIN
|
|
UPDATE public.recurrence_rules
|
|
SET
|
|
end_date = p_from_date - INTERVAL '1 day',
|
|
open_ended = false,
|
|
status = CASE
|
|
WHEN p_from_date <= start_date THEN 'cancelado'
|
|
ELSE status
|
|
END,
|
|
updated_at = now()
|
|
WHERE id = p_recurrence_id;
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.cancel_recurrence_from(p_recurrence_id uuid, p_from_date date) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: cancel_subscription(uuid); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.cancel_subscription(p_subscription_id uuid) RETURNS public.subscriptions
|
|
CREATE FUNCTION public.cancelar_eventos_serie(p_serie_id uuid, p_a_partir_de timestamp with time zone DEFAULT now()) RETURNS integer
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
AS $$
|
|
DECLARE
|
|
v_count integer;
|
|
BEGIN
|
|
UPDATE public.agenda_eventos
|
|
SET status = 'cancelado',
|
|
updated_at = now()
|
|
WHERE serie_id = p_serie_id
|
|
AND inicio_em >= p_a_partir_de
|
|
AND status NOT IN ('realizado', 'cancelado');
|
|
|
|
GET DIAGNOSTICS v_count = ROW_COUNT;
|
|
RETURN v_count;
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.cancelar_eventos_serie(p_serie_id uuid, p_a_partir_de timestamp with time zone) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: FUNCTION cancelar_eventos_serie(p_serie_id uuid, p_a_partir_de timestamp with time zone); Type: COMMENT; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
COMMENT ON FUNCTION public.cancelar_eventos_serie(p_serie_id uuid, p_a_partir_de timestamp with time zone) IS 'Cancela todos os eventos futuros de uma série a partir de p_a_partir_de (inclusive).
|
|
Não cancela eventos já realizados.';
|
|
|
|
|
|
--
|
|
-- Name: change_subscription_plan(uuid, uuid); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.change_subscription_plan(p_subscription_id uuid, p_new_plan_id uuid) RETURNS public.subscriptions
|
|
CREATE FUNCTION public.fn_agenda_regras_semanais_no_overlap() RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
declare
|
|
v_count int;
|
|
begin
|
|
if new.ativo is false then
|
|
return new;
|
|
end if;
|
|
|
|
select count(*) into v_count
|
|
from public.agenda_regras_semanais r
|
|
where r.owner_id = new.owner_id
|
|
and r.dia_semana = new.dia_semana
|
|
and r.ativo is true
|
|
and (tg_op = 'INSERT' or r.id <> new.id)
|
|
and (new.hora_inicio < r.hora_fim and new.hora_fim > r.hora_inicio);
|
|
|
|
if v_count > 0 then
|
|
raise exception 'Janela sobreposta: já existe uma regra ativa nesse intervalo.';
|
|
end if;
|
|
|
|
return new;
|
|
end;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.fn_agenda_regras_semanais_no_overlap() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: get_financial_report(uuid, date, date, text); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.get_financial_report(p_owner_id uuid, p_start_date date, p_end_date date, p_group_by text DEFAULT 'month'::text) RETURNS TABLE(group_key text, group_label text, total_receitas numeric, total_despesas numeric, saldo numeric, total_pendente numeric, total_overdue numeric, count_records bigint)
|
|
CREATE FUNCTION public.set_updated_at_recurrence() RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
BEGIN NEW.updated_at = now(); RETURN NEW; END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.set_updated_at_recurrence() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: split_recurrence_at(uuid, date); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.split_recurrence_at(p_recurrence_id uuid, p_from_date date) RETURNS uuid
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
SET search_path TO 'public'
|
|
AS $$
|
|
DECLARE
|
|
v_old public.recurrence_rules;
|
|
v_new_id uuid;
|
|
BEGIN
|
|
-- busca a regra original
|
|
SELECT * INTO v_old
|
|
FROM public.recurrence_rules
|
|
WHERE id = p_recurrence_id;
|
|
|
|
IF NOT FOUND THEN
|
|
RAISE EXCEPTION 'recurrence_rule % não encontrada', p_recurrence_id;
|
|
END IF;
|
|
|
|
-- encerra a regra antiga na data anterior
|
|
UPDATE public.recurrence_rules
|
|
SET
|
|
end_date = p_from_date - INTERVAL '1 day',
|
|
open_ended = false,
|
|
updated_at = now()
|
|
WHERE id = p_recurrence_id;
|
|
|
|
-- cria nova regra a partir de p_from_date
|
|
INSERT INTO public.recurrence_rules (
|
|
tenant_id, owner_id, therapist_id, patient_id,
|
|
determined_commitment_id, type, interval, weekdays,
|
|
start_time, end_time, timezone, duration_min,
|
|
start_date, end_date, max_occurrences, open_ended,
|
|
modalidade, titulo_custom, observacoes, extra_fields, status
|
|
)
|
|
SELECT
|
|
tenant_id, owner_id, therapist_id, patient_id,
|
|
determined_commitment_id, type, interval, weekdays,
|
|
start_time, end_time, timezone, duration_min,
|
|
p_from_date, v_old.end_date, v_old.max_occurrences, v_old.open_ended,
|
|
modalidade, titulo_custom, observacoes, extra_fields, status
|
|
FROM public.recurrence_rules
|
|
WHERE id = p_recurrence_id
|
|
RETURNING id INTO v_new_id;
|
|
|
|
RETURN v_new_id;
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.split_recurrence_at(p_recurrence_id uuid, p_from_date date) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: subscription_intents_view_insert(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.subscription_intents_view_insert() RETURNS trigger
|
|
CREATE FUNCTION public.sync_busy_mirror_agenda_eventos() RETURNS trigger
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
SET search_path TO 'public'
|
|
AS $$
|
|
declare
|
|
clinic_tenant uuid;
|
|
is_personal boolean;
|
|
should_mirror boolean;
|
|
begin
|
|
-- Anti-recursão: espelho não espelha
|
|
if (tg_op <> 'DELETE') then
|
|
if new.mirror_of_event_id is not null then
|
|
return new;
|
|
end if;
|
|
else
|
|
if old.mirror_of_event_id is not null then
|
|
return old;
|
|
end if;
|
|
end if;
|
|
|
|
-- Define se é pessoal e se deve espelhar
|
|
if (tg_op = 'DELETE') then
|
|
is_personal := (old.tenant_id = old.owner_id);
|
|
should_mirror := (old.visibility_scope in ('busy_only','private'));
|
|
else
|
|
is_personal := (new.tenant_id = new.owner_id);
|
|
should_mirror := (new.visibility_scope in ('busy_only','private'));
|
|
end if;
|
|
|
|
-- Se não é pessoal, não faz nada
|
|
if not is_personal then
|
|
if (tg_op = 'DELETE') then
|
|
return old;
|
|
end if;
|
|
return new;
|
|
end if;
|
|
|
|
-- DELETE: remove espelhos existentes
|
|
if (tg_op = 'DELETE') then
|
|
delete from public.agenda_eventos e
|
|
where e.mirror_of_event_id = old.id
|
|
and e.mirror_source = 'personal_busy_mirror';
|
|
|
|
return old;
|
|
end if;
|
|
|
|
-- INSERT/UPDATE:
|
|
-- Se não deve espelhar, remove espelhos e sai
|
|
if not should_mirror then
|
|
delete from public.agenda_eventos e
|
|
where e.mirror_of_event_id = new.id
|
|
and e.mirror_source = 'personal_busy_mirror';
|
|
|
|
return new;
|
|
end if;
|
|
|
|
-- Para cada clínica onde o usuário é therapist active, cria/atualiza o "Ocupado"
|
|
for clinic_tenant in
|
|
select tm.tenant_id
|
|
from public.tenant_members tm
|
|
where tm.user_id = new.owner_id
|
|
and tm.role = 'therapist'
|
|
and tm.status = 'active'
|
|
and tm.tenant_id <> new.owner_id
|
|
loop
|
|
insert into public.agenda_eventos (
|
|
tenant_id,
|
|
owner_id,
|
|
terapeuta_id,
|
|
paciente_id,
|
|
tipo,
|
|
status,
|
|
titulo,
|
|
observacoes,
|
|
inicio_em,
|
|
fim_em,
|
|
mirror_of_event_id,
|
|
mirror_source,
|
|
visibility_scope,
|
|
created_at,
|
|
updated_at
|
|
) values (
|
|
clinic_tenant,
|
|
new.owner_id,
|
|
new.owner_id,
|
|
null,
|
|
'bloqueio'::public.tipo_evento_agenda,
|
|
'agendado'::public.status_evento_agenda,
|
|
'Ocupado',
|
|
null,
|
|
new.inicio_em,
|
|
new.fim_em,
|
|
new.id,
|
|
'personal_busy_mirror',
|
|
'public',
|
|
now(),
|
|
now()
|
|
)
|
|
on conflict (tenant_id, mirror_of_event_id) where mirror_of_event_id is not null
|
|
do update set
|
|
owner_id = excluded.owner_id,
|
|
terapeuta_id = excluded.terapeuta_id,
|
|
tipo = excluded.tipo,
|
|
status = excluded.status,
|
|
titulo = excluded.titulo,
|
|
observacoes = excluded.observacoes,
|
|
inicio_em = excluded.inicio_em,
|
|
fim_em = excluded.fim_em,
|
|
updated_at = now();
|
|
end loop;
|
|
|
|
-- Limpa espelhos de clínicas onde o vínculo therapist active não existe mais
|
|
delete from public.agenda_eventos e
|
|
where e.mirror_of_event_id = new.id
|
|
and e.mirror_source = 'personal_busy_mirror'
|
|
and not exists (
|
|
select 1
|
|
from public.tenant_members tm
|
|
where tm.user_id = new.owner_id
|
|
and tm.role = 'therapist'
|
|
and tm.status = 'active'
|
|
and tm.tenant_id = e.tenant_id
|
|
);
|
|
|
|
return new;
|
|
end;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.sync_busy_mirror_agenda_eventos() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: sync_overdue_financial_records(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.sync_overdue_financial_records() RETURNS integer
|