777 lines
26 KiB
PL/PgSQL
777 lines
26 KiB
PL/PgSQL
-- =============================================================================
|
|
-- AgenciaPsi — Functions — Compromissos, Suporte, SaaS
|
|
-- =============================================================================
|
|
-- seed_determined_commitments, delete_commitment_full,
|
|
-- delete_determined_commitment, guard_locked_commitment,
|
|
-- create_support_session, revoke_support_session, validate_support_session,
|
|
-- saas_votar_doc, faq_votar, notice_track_click/view,
|
|
-- sanitize_phone_br, create_clinic_tenant
|
|
-- =============================================================================
|
|
|
|
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 (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
owner_id uuid NOT NULL,
|
|
tenant_id uuid,
|
|
type public.financial_record_type DEFAULT 'receita'::public.financial_record_type NOT NULL,
|
|
amount numeric(10,2) NOT NULL,
|
|
description text,
|
|
category text,
|
|
payment_method text,
|
|
paid_at timestamp with time zone,
|
|
due_date date,
|
|
installments smallint DEFAULT 1,
|
|
installment_number smallint DEFAULT 1,
|
|
installment_group uuid,
|
|
agenda_evento_id uuid,
|
|
patient_id uuid,
|
|
clinic_fee_pct numeric(5,2) DEFAULT 0,
|
|
clinic_fee_amount numeric(10,2) DEFAULT 0,
|
|
net_amount numeric(10,2) GENERATED ALWAYS AS ((amount - clinic_fee_amount)) STORED,
|
|
insurance_plan_id uuid,
|
|
notes text,
|
|
tags text[],
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
deleted_at timestamp with time zone,
|
|
discount_amount numeric(10,2) DEFAULT 0 NOT NULL,
|
|
final_amount numeric(10,2) DEFAULT 0 NOT NULL,
|
|
status text DEFAULT 'pending'::text NOT NULL,
|
|
category_id uuid,
|
|
CONSTRAINT financial_records_amount_check CHECK ((amount >= (0)::numeric)),
|
|
CONSTRAINT financial_records_clinic_fee_amount_check CHECK ((clinic_fee_amount >= (0)::numeric)),
|
|
CONSTRAINT financial_records_clinic_fee_pct_check CHECK (((clinic_fee_pct >= (0)::numeric) AND (clinic_fee_pct <= (100)::numeric))),
|
|
CONSTRAINT financial_records_discount_amount_check CHECK ((discount_amount >= (0)::numeric)),
|
|
CONSTRAINT financial_records_final_amount_check CHECK ((final_amount >= (0)::numeric)),
|
|
CONSTRAINT financial_records_installments_check CHECK ((installments >= 1)),
|
|
CONSTRAINT financial_records_status_check CHECK ((status = ANY (ARRAY['pending'::text, 'paid'::text, 'partial'::text, 'overdue'::text, 'cancelled'::text, 'refunded'::text])))
|
|
);
|
|
|
|
|
|
ALTER TABLE public.financial_records OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: create_financial_record_for_session(uuid, uuid, uuid, uuid, numeric, date); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.create_financial_record_for_session(p_tenant_id uuid, p_owner_id uuid, p_patient_id uuid, p_agenda_evento_id uuid, p_amount numeric, p_due_date date) RETURNS SETOF public.financial_records
|
|
CREATE FUNCTION public.create_support_session(p_tenant_id uuid, p_ttl_minutes integer DEFAULT 60) RETURNS json
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
SET search_path TO 'public'
|
|
AS $$
|
|
DECLARE
|
|
v_admin_id uuid;
|
|
v_role text;
|
|
v_token text;
|
|
v_expires timestamp with time zone;
|
|
v_session support_sessions;
|
|
BEGIN
|
|
-- Verifica autenticação
|
|
v_admin_id := auth.uid();
|
|
IF v_admin_id IS NULL THEN
|
|
RAISE EXCEPTION 'Não autenticado.' USING ERRCODE = 'P0001';
|
|
END IF;
|
|
|
|
-- Verifica role saas_admin
|
|
SELECT role INTO v_role
|
|
FROM public.profiles
|
|
WHERE id = v_admin_id;
|
|
|
|
IF v_role <> 'saas_admin' THEN
|
|
RAISE EXCEPTION 'Acesso negado. Somente saas_admin pode criar sessões de suporte.'
|
|
USING ERRCODE = 'P0002';
|
|
END IF;
|
|
|
|
-- Valida TTL (1 a 120 minutos)
|
|
IF p_ttl_minutes < 1 OR p_ttl_minutes > 120 THEN
|
|
RAISE EXCEPTION 'TTL inválido. Use entre 1 e 120 minutos.'
|
|
USING ERRCODE = 'P0003';
|
|
END IF;
|
|
|
|
-- Valida tenant
|
|
IF NOT EXISTS (SELECT 1 FROM public.tenants WHERE id = p_tenant_id) THEN
|
|
RAISE EXCEPTION 'Tenant não encontrado.'
|
|
USING ERRCODE = 'P0004';
|
|
END IF;
|
|
|
|
-- Gera token único (64 chars hex, sem pgcrypto)
|
|
v_token := replace(gen_random_uuid()::text, '-', '') || replace(gen_random_uuid()::text, '-', '');
|
|
v_expires := now() + (p_ttl_minutes || ' minutes')::interval;
|
|
|
|
-- Insere sessão
|
|
INSERT INTO public.support_sessions (tenant_id, admin_id, token, expires_at)
|
|
VALUES (p_tenant_id, v_admin_id, v_token, v_expires)
|
|
RETURNING * INTO v_session;
|
|
|
|
RETURN json_build_object(
|
|
'token', v_session.token,
|
|
'expires_at', v_session.expires_at,
|
|
'session_id', v_session.id
|
|
);
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.create_support_session(p_tenant_id uuid, p_ttl_minutes integer) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: therapist_payouts; Type: TABLE; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE TABLE public.therapist_payouts (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
owner_id uuid NOT NULL,
|
|
tenant_id uuid NOT NULL,
|
|
period_start date NOT NULL,
|
|
period_end date NOT NULL,
|
|
total_sessions integer DEFAULT 0 NOT NULL,
|
|
gross_amount numeric(10,2) DEFAULT 0 NOT NULL,
|
|
clinic_fee_total numeric(10,2) DEFAULT 0 NOT NULL,
|
|
net_amount numeric(10,2) DEFAULT 0 NOT NULL,
|
|
status text DEFAULT 'pending'::text NOT NULL,
|
|
paid_at timestamp with time zone,
|
|
notes text,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
CONSTRAINT therapist_payouts_clinic_fee_total_check CHECK ((clinic_fee_total >= (0)::numeric)),
|
|
CONSTRAINT therapist_payouts_gross_amount_check CHECK ((gross_amount >= (0)::numeric)),
|
|
CONSTRAINT therapist_payouts_net_amount_check CHECK ((net_amount >= (0)::numeric)),
|
|
CONSTRAINT therapist_payouts_period_chk CHECK ((period_end >= period_start)),
|
|
CONSTRAINT therapist_payouts_status_check CHECK ((status = ANY (ARRAY['pending'::text, 'paid'::text, 'cancelled'::text])))
|
|
);
|
|
|
|
|
|
ALTER TABLE public.therapist_payouts OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: create_therapist_payout(uuid, uuid, date, date); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.create_therapist_payout(p_tenant_id uuid, p_therapist_id uuid, p_period_start date, p_period_end date) RETURNS public.therapist_payouts
|
|
CREATE FUNCTION public.delete_commitment_full(p_tenant_id uuid, p_commitment_id uuid) RETURNS jsonb
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
SET search_path TO 'public'
|
|
AS $$
|
|
declare
|
|
v_is_native boolean;
|
|
v_fields int := 0;
|
|
v_logs int := 0;
|
|
v_parent int := 0;
|
|
begin
|
|
if auth.uid() is null then
|
|
raise exception 'Not authenticated';
|
|
end if;
|
|
|
|
if not exists (
|
|
select 1
|
|
from public.tenant_members tm
|
|
where tm.tenant_id = p_tenant_id
|
|
and tm.user_id = auth.uid()
|
|
and tm.status = 'active'
|
|
) then
|
|
raise exception 'Not allowed';
|
|
end if;
|
|
|
|
select dc.is_native
|
|
into v_is_native
|
|
from public.determined_commitments dc
|
|
where dc.tenant_id = p_tenant_id
|
|
and dc.id = p_commitment_id;
|
|
|
|
if v_is_native is null then
|
|
raise exception 'Commitment not found';
|
|
end if;
|
|
|
|
if v_is_native = true then
|
|
raise exception 'Cannot delete native commitment';
|
|
end if;
|
|
|
|
delete from public.determined_commitment_fields
|
|
where tenant_id = p_tenant_id
|
|
and commitment_id = p_commitment_id;
|
|
get diagnostics v_fields = row_count;
|
|
|
|
delete from public.commitment_time_logs
|
|
where tenant_id = p_tenant_id
|
|
and commitment_id = p_commitment_id;
|
|
get diagnostics v_logs = row_count;
|
|
|
|
delete from public.determined_commitments
|
|
where tenant_id = p_tenant_id
|
|
and id = p_commitment_id;
|
|
get diagnostics v_parent = row_count;
|
|
|
|
if v_parent <> 1 then
|
|
raise exception 'Parent not deleted (RLS/owner issue).';
|
|
end if;
|
|
|
|
return jsonb_build_object(
|
|
'ok', true,
|
|
'deleted', jsonb_build_object(
|
|
'fields', v_fields,
|
|
'logs', v_logs,
|
|
'commitment', v_parent
|
|
)
|
|
);
|
|
end;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.delete_commitment_full(p_tenant_id uuid, p_commitment_id uuid) OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: delete_determined_commitment(uuid, uuid); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.delete_determined_commitment(p_tenant_id uuid, p_commitment_id uuid) RETURNS jsonb
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
SET search_path TO 'public'
|
|
AS $$
|
|
declare
|
|
v_is_native boolean;
|
|
v_fields_deleted int := 0;
|
|
v_logs_deleted int := 0;
|
|
v_commitment_deleted int := 0;
|
|
begin
|
|
if auth.uid() is null then
|
|
raise exception 'Not authenticated';
|
|
end if;
|
|
|
|
if not exists (
|
|
select 1
|
|
from public.tenant_members tm
|
|
where tm.tenant_id = p_tenant_id
|
|
and tm.user_id = auth.uid()
|
|
and tm.status = 'active'
|
|
) then
|
|
raise exception 'Not allowed';
|
|
end if;
|
|
|
|
select dc.is_native
|
|
into v_is_native
|
|
from public.determined_commitments dc
|
|
where dc.tenant_id = p_tenant_id
|
|
and dc.id = p_commitment_id;
|
|
|
|
if v_is_native is null then
|
|
raise exception 'Commitment not found for tenant';
|
|
end if;
|
|
|
|
if v_is_native = true then
|
|
raise exception 'Cannot delete native commitment';
|
|
end if;
|
|
|
|
delete from public.determined_commitment_fields f
|
|
where f.tenant_id = p_tenant_id
|
|
and f.commitment_id = p_commitment_id;
|
|
get diagnostics v_fields_deleted = row_count;
|
|
|
|
delete from public.commitment_time_logs l
|
|
where l.tenant_id = p_tenant_id
|
|
and l.commitment_id = p_commitment_id;
|
|
get diagnostics v_logs_deleted = row_count;
|
|
|
|
delete from public.determined_commitments dc
|
|
where dc.tenant_id = p_tenant_id
|
|
and dc.id = p_commitment_id;
|
|
get diagnostics v_commitment_deleted = row_count;
|
|
|
|
if v_commitment_deleted <> 1 then
|
|
raise exception 'Delete did not remove the commitment (tenant mismatch?)';
|
|
end if;
|
|
|
|
return jsonb_build_object(
|
|
'ok', true,
|
|
'tenant_id', p_tenant_id,
|
|
'commitment_id', p_commitment_id,
|
|
'deleted', jsonb_build_object(
|
|
'fields', v_fields_deleted,
|
|
'logs', v_logs_deleted,
|
|
'commitment', v_commitment_deleted
|
|
)
|
|
);
|
|
end;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.delete_determined_commitment(p_tenant_id uuid, p_commitment_id uuid) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: dev_list_auth_users(integer); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.dev_list_auth_users(p_limit integer DEFAULT 50) RETURNS TABLE(id uuid, email text, created_at timestamp with time zone)
|
|
CREATE FUNCTION public.faq_votar(faq_id uuid) RETURNS void
|
|
LANGUAGE sql SECURITY DEFINER
|
|
AS $$
|
|
update public.saas_faq
|
|
set votos = votos + 1,
|
|
updated_at = now()
|
|
where id = faq_id
|
|
and ativo = true;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.faq_votar(faq_id uuid) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: fix_all_subscription_mismatches(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.fix_all_subscription_mismatches() RETURNS void
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
AS $$
|
|
declare
|
|
r record;
|
|
begin
|
|
for r in
|
|
select distinct s.user_id as owner_id
|
|
from public.subscriptions s
|
|
where s.status = 'active'
|
|
and s.user_id is not null
|
|
loop
|
|
perform public.rebuild_owner_entitlements(r.owner_id);
|
|
end loop;
|
|
end;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.fix_all_subscription_mismatches() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: fn_agenda_regras_semanais_no_overlap(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.fn_agenda_regras_semanais_no_overlap() RETURNS trigger
|
|
CREATE FUNCTION public.guard_locked_commitment() RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
begin
|
|
if (old.is_locked = true) then
|
|
if (tg_op = 'DELETE') then
|
|
raise exception 'Compromisso bloqueado não pode ser excluído.';
|
|
end if;
|
|
|
|
if (tg_op = 'UPDATE') then
|
|
if (new.active = false) then
|
|
raise exception 'Compromisso bloqueado não pode ser desativado.';
|
|
end if;
|
|
|
|
-- trava renomear (mantém o "Sessão" sempre igual)
|
|
if (new.name is distinct from old.name) then
|
|
raise exception 'Compromisso bloqueado não pode ser renomeado.';
|
|
end if;
|
|
|
|
-- se quiser travar descrição também, descomente:
|
|
-- if (new.description is distinct from old.description) then
|
|
-- raise exception 'Compromisso bloqueado não pode alterar descrição.';
|
|
-- end if;
|
|
end if;
|
|
end if;
|
|
|
|
return new;
|
|
end;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.guard_locked_commitment() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: guard_no_change_core_plan_key(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.guard_no_change_core_plan_key() RETURNS trigger
|
|
CREATE FUNCTION public.notice_track_click(p_notice_id uuid) RETURNS void
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
AS $$
|
|
begin
|
|
update public.global_notices
|
|
set clicks_count = clicks_count + 1
|
|
where id = p_notice_id;
|
|
end;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.notice_track_click(p_notice_id uuid) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: notice_track_view(uuid); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.notice_track_view(p_notice_id uuid) RETURNS void
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
AS $$
|
|
begin
|
|
update public.global_notices
|
|
set views_count = views_count + 1
|
|
where id = p_notice_id;
|
|
end;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.notice_track_view(p_notice_id uuid) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: notify_on_intake(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.notify_on_intake() RETURNS trigger
|
|
CREATE FUNCTION public.revoke_support_session(p_token text) RETURNS boolean
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
SET search_path TO 'public'
|
|
AS $$
|
|
DECLARE
|
|
v_admin_id uuid;
|
|
v_role text;
|
|
BEGIN
|
|
v_admin_id := auth.uid();
|
|
IF v_admin_id IS NULL THEN
|
|
RAISE EXCEPTION 'Não autenticado.' USING ERRCODE = 'P0001';
|
|
END IF;
|
|
|
|
SELECT role INTO v_role FROM public.profiles WHERE id = v_admin_id;
|
|
IF v_role <> 'saas_admin' THEN
|
|
RAISE EXCEPTION 'Acesso negado.' USING ERRCODE = 'P0002';
|
|
END IF;
|
|
|
|
DELETE FROM public.support_sessions
|
|
WHERE token = p_token
|
|
AND admin_id = v_admin_id;
|
|
|
|
RETURN FOUND;
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.revoke_support_session(p_token text) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: rotate_patient_invite_token(text); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.rotate_patient_invite_token(p_new_token text) RETURNS uuid
|
|
CREATE FUNCTION public.saas_votar_doc(p_doc_id uuid, p_util boolean) RETURNS jsonb
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
AS $$
|
|
declare
|
|
v_uid uuid := auth.uid();
|
|
v_voto_antigo boolean;
|
|
begin
|
|
if v_uid is null then
|
|
raise exception 'Não autenticado';
|
|
end if;
|
|
|
|
-- Verifica se já votou
|
|
select util into v_voto_antigo
|
|
from public.saas_doc_votos
|
|
where doc_id = p_doc_id and user_id = v_uid;
|
|
|
|
if found then
|
|
-- Já votou igual → cancela o voto (toggle)
|
|
if v_voto_antigo = p_util then
|
|
delete from public.saas_doc_votos
|
|
where doc_id = p_doc_id and user_id = v_uid;
|
|
|
|
update public.saas_docs set
|
|
votos_util = greatest(0, votos_util - (case when p_util then 1 else 0 end)),
|
|
votos_nao_util = greatest(0, votos_nao_util - (case when not p_util then 1 else 0 end)),
|
|
updated_at = now()
|
|
where id = p_doc_id;
|
|
|
|
return jsonb_build_object('acao', 'removido', 'util', null);
|
|
else
|
|
-- Mudou de voto
|
|
update public.saas_doc_votos set util = p_util, updated_at = now()
|
|
where doc_id = p_doc_id and user_id = v_uid;
|
|
|
|
update public.saas_docs set
|
|
votos_util = greatest(0, votos_util + (case when p_util then 1 else -1 end)),
|
|
votos_nao_util = greatest(0, votos_nao_util + (case when not p_util then 1 else -1 end)),
|
|
updated_at = now()
|
|
where id = p_doc_id;
|
|
|
|
return jsonb_build_object('acao', 'atualizado', 'util', p_util);
|
|
end if;
|
|
else
|
|
-- Primeiro voto
|
|
insert into public.saas_doc_votos (doc_id, user_id, util)
|
|
values (p_doc_id, v_uid, p_util);
|
|
|
|
update public.saas_docs set
|
|
votos_util = votos_util + (case when p_util then 1 else 0 end),
|
|
votos_nao_util = votos_nao_util + (case when not p_util then 1 else 0 end),
|
|
updated_at = now()
|
|
where id = p_doc_id;
|
|
|
|
return jsonb_build_object('acao', 'registrado', 'util', p_util);
|
|
end if;
|
|
end;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.saas_votar_doc(p_doc_id uuid, p_util boolean) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: safe_delete_patient(uuid); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.safe_delete_patient(p_patient_id uuid) RETURNS jsonb
|
|
CREATE FUNCTION public.sanitize_phone_br(raw_phone text) RETURNS text
|
|
LANGUAGE plpgsql IMMUTABLE
|
|
AS $$ DECLARE digits text;
|
|
BEGIN
|
|
digits := regexp_replace(COALESCE(raw_phone, ''), '[^0-9]', '', 'g');
|
|
IF digits = '' THEN RETURN ''; END IF;
|
|
IF length(digits) = 10 OR length(digits) = 11 THEN
|
|
digits := '55' || digits;
|
|
END IF;
|
|
RETURN digits;
|
|
END; $$;
|
|
|
|
|
|
ALTER FUNCTION public.sanitize_phone_br(raw_phone text) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: seed_default_financial_categories(uuid); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.seed_default_financial_categories(p_user_id uuid) RETURNS void
|
|
CREATE FUNCTION public.seed_determined_commitments(p_tenant_id uuid) RETURNS void
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
AS $$
|
|
declare
|
|
v_id uuid;
|
|
begin
|
|
-- Sessão (locked + sempre ativa)
|
|
if not exists (
|
|
select 1 from public.determined_commitments
|
|
where tenant_id = p_tenant_id and is_native = true and native_key = 'session'
|
|
) then
|
|
insert into public.determined_commitments
|
|
(tenant_id, is_native, native_key, is_locked, active, name, description)
|
|
values
|
|
(p_tenant_id, true, 'session', true, true, 'Sessão', 'Sessão com paciente');
|
|
end if;
|
|
|
|
-- Leitura
|
|
if not exists (
|
|
select 1 from public.determined_commitments
|
|
where tenant_id = p_tenant_id and is_native = true and native_key = 'reading'
|
|
) then
|
|
insert into public.determined_commitments
|
|
(tenant_id, is_native, native_key, is_locked, active, name, description)
|
|
values
|
|
(p_tenant_id, true, 'reading', false, true, 'Leitura', 'Praticar leitura');
|
|
end if;
|
|
|
|
-- Supervisão
|
|
if not exists (
|
|
select 1 from public.determined_commitments
|
|
where tenant_id = p_tenant_id and is_native = true and native_key = 'supervision'
|
|
) then
|
|
insert into public.determined_commitments
|
|
(tenant_id, is_native, native_key, is_locked, active, name, description)
|
|
values
|
|
(p_tenant_id, true, 'supervision', false, true, 'Supervisão', 'Supervisão');
|
|
end if;
|
|
|
|
-- Aula ✅ (corrigido)
|
|
if not exists (
|
|
select 1 from public.determined_commitments
|
|
where tenant_id = p_tenant_id and is_native = true and native_key = 'class'
|
|
) then
|
|
insert into public.determined_commitments
|
|
(tenant_id, is_native, native_key, is_locked, active, name, description)
|
|
values
|
|
(p_tenant_id, true, 'class', false, false, 'Aula', 'Dar aula');
|
|
end if;
|
|
|
|
-- Análise pessoal
|
|
if not exists (
|
|
select 1 from public.determined_commitments
|
|
where tenant_id = p_tenant_id and is_native = true and native_key = 'analysis'
|
|
) then
|
|
insert into public.determined_commitments
|
|
(tenant_id, is_native, native_key, is_locked, active, name, description)
|
|
values
|
|
(p_tenant_id, true, 'analysis', false, true, 'Análise Pessoal', 'Minha análise pessoal');
|
|
end if;
|
|
|
|
-- -------------------------------------------------------
|
|
-- Campos padrão (idempotentes por (commitment_id, key))
|
|
-- -------------------------------------------------------
|
|
|
|
-- Leitura
|
|
select id into v_id
|
|
from public.determined_commitments
|
|
where tenant_id = p_tenant_id and is_native = true and native_key = 'reading'
|
|
limit 1;
|
|
|
|
if v_id is not null then
|
|
if not exists (select 1 from public.determined_commitment_fields where commitment_id = v_id and key = 'book') then
|
|
insert into public.determined_commitment_fields (tenant_id, commitment_id, key, label, field_type, required, sort_order)
|
|
values (p_tenant_id, v_id, 'book', 'Livro', 'text', false, 10);
|
|
end if;
|
|
|
|
if not exists (select 1 from public.determined_commitment_fields where commitment_id = v_id and key = 'author') then
|
|
insert into public.determined_commitment_fields (tenant_id, commitment_id, key, label, field_type, required, sort_order)
|
|
values (p_tenant_id, v_id, 'author', 'Autor', 'text', false, 20);
|
|
end if;
|
|
|
|
if not exists (select 1 from public.determined_commitment_fields where commitment_id = v_id and key = 'notes') then
|
|
insert into public.determined_commitment_fields (tenant_id, commitment_id, key, label, field_type, required, sort_order)
|
|
values (p_tenant_id, v_id, 'notes', 'Observação', 'textarea', false, 30);
|
|
end if;
|
|
end if;
|
|
|
|
-- Supervisão
|
|
select id into v_id
|
|
from public.determined_commitments
|
|
where tenant_id = p_tenant_id and is_native = true and native_key = 'supervision'
|
|
limit 1;
|
|
|
|
if v_id is not null then
|
|
if not exists (select 1 from public.determined_commitment_fields where commitment_id = v_id and key = 'supervisor') then
|
|
insert into public.determined_commitment_fields (tenant_id, commitment_id, key, label, field_type, required, sort_order)
|
|
values (p_tenant_id, v_id, 'supervisor', 'Supervisor', 'text', false, 10);
|
|
end if;
|
|
|
|
if not exists (select 1 from public.determined_commitment_fields where commitment_id = v_id and key = 'topic') then
|
|
insert into public.determined_commitment_fields (tenant_id, commitment_id, key, label, field_type, required, sort_order)
|
|
values (p_tenant_id, v_id, 'topic', 'Assunto', 'text', false, 20);
|
|
end if;
|
|
|
|
if not exists (select 1 from public.determined_commitment_fields where commitment_id = v_id and key = 'notes') then
|
|
insert into public.determined_commitment_fields (tenant_id, commitment_id, key, label, field_type, required, sort_order)
|
|
values (p_tenant_id, v_id, 'notes', 'Observação', 'textarea', false, 30);
|
|
end if;
|
|
end if;
|
|
|
|
-- Aula
|
|
select id into v_id
|
|
from public.determined_commitments
|
|
where tenant_id = p_tenant_id and is_native = true and native_key = 'class'
|
|
limit 1;
|
|
|
|
if v_id is not null then
|
|
if not exists (select 1 from public.determined_commitment_fields where commitment_id = v_id and key = 'theme') then
|
|
insert into public.determined_commitment_fields (tenant_id, commitment_id, key, label, field_type, required, sort_order)
|
|
values (p_tenant_id, v_id, 'theme', 'Tema', 'text', false, 10);
|
|
end if;
|
|
|
|
if not exists (select 1 from public.determined_commitment_fields where commitment_id = v_id and key = 'group') then
|
|
insert into public.determined_commitment_fields (tenant_id, commitment_id, key, label, field_type, required, sort_order)
|
|
values (p_tenant_id, v_id, 'group', 'Turma', 'text', false, 20);
|
|
end if;
|
|
|
|
if not exists (select 1 from public.determined_commitment_fields where commitment_id = v_id and key = 'notes') then
|
|
insert into public.determined_commitment_fields (tenant_id, commitment_id, key, label, field_type, required, sort_order)
|
|
values (p_tenant_id, v_id, 'notes', 'Observação', 'textarea', false, 30);
|
|
end if;
|
|
end if;
|
|
|
|
-- Análise
|
|
select id into v_id
|
|
from public.determined_commitments
|
|
where tenant_id = p_tenant_id and is_native = true and native_key = 'analysis'
|
|
limit 1;
|
|
|
|
if v_id is not null then
|
|
if not exists (select 1 from public.determined_commitment_fields where commitment_id = v_id and key = 'analyst') then
|
|
insert into public.determined_commitment_fields (tenant_id, commitment_id, key, label, field_type, required, sort_order)
|
|
values (p_tenant_id, v_id, 'analyst', 'Analista', 'text', false, 10);
|
|
end if;
|
|
|
|
if not exists (select 1 from public.determined_commitment_fields where commitment_id = v_id and key = 'focus') then
|
|
insert into public.determined_commitment_fields (tenant_id, commitment_id, key, label, field_type, required, sort_order)
|
|
values (p_tenant_id, v_id, 'focus', 'Foco', 'text', false, 20);
|
|
end if;
|
|
|
|
if not exists (select 1 from public.determined_commitment_fields where commitment_id = v_id and key = 'notes') then
|
|
insert into public.determined_commitment_fields (tenant_id, commitment_id, key, label, field_type, required, sort_order)
|
|
values (p_tenant_id, v_id, 'notes', 'Observação', 'textarea', false, 30);
|
|
end if;
|
|
end if;
|
|
end;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.seed_determined_commitments(p_tenant_id uuid) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: set_insurance_plans_updated_at(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.set_insurance_plans_updated_at() RETURNS trigger
|
|
CREATE FUNCTION public.validate_support_session(p_token text) RETURNS json
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
SET search_path TO 'public'
|
|
AS $$
|
|
DECLARE
|
|
v_session support_sessions;
|
|
BEGIN
|
|
IF p_token IS NULL OR length(trim(p_token)) < 32 THEN
|
|
RETURN json_build_object('valid', false, 'tenant_id', null);
|
|
END IF;
|
|
|
|
SELECT * INTO v_session
|
|
FROM public.support_sessions
|
|
WHERE token = p_token
|
|
AND expires_at > now()
|
|
LIMIT 1;
|
|
|
|
IF NOT FOUND THEN
|
|
RETURN json_build_object('valid', false, 'tenant_id', null);
|
|
END IF;
|
|
|
|
RETURN json_build_object(
|
|
'valid', true,
|
|
'tenant_id', v_session.tenant_id
|
|
);
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.validate_support_session(p_token text) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: whoami(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.whoami() RETURNS TABLE(uid uuid, role text)
|