2351 lines
65 KiB
PL/PgSQL
2351 lines
65 KiB
PL/PgSQL
-- =============================================================================
|
|
-- AgenciaPsi — Functions — Core (profiles, tenants, membros, auth)
|
|
-- =============================================================================
|
|
-- handle_new_user, ensure_personal_tenant, whoami, my_tenants,
|
|
-- tenant_add_member, tenant_remove_member, tenant_invite, guards, etc.
|
|
-- =============================================================================
|
|
|
|
CREATE FUNCTION public.__rls_ping() RETURNS text
|
|
LANGUAGE sql STABLE
|
|
AS $$
|
|
select 'ok'::text;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.__rls_ping() OWNER TO supabase_admin;
|
|
|
|
SET default_tablespace = '';
|
|
|
|
SET default_table_access_method = heap;
|
|
|
|
--
|
|
-- Name: subscriptions; Type: TABLE; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
|
|
CREATE FUNCTION public.ensure_personal_tenant() RETURNS uuid
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
AS $$
|
|
DECLARE
|
|
v_uid uuid;
|
|
v_existing uuid;
|
|
BEGIN
|
|
v_uid := auth.uid();
|
|
IF v_uid IS NULL THEN
|
|
RAISE EXCEPTION 'Not authenticated';
|
|
END IF;
|
|
|
|
SELECT tm.tenant_id INTO v_existing
|
|
FROM public.tenant_members tm
|
|
JOIN public.tenants t ON t.id = tm.tenant_id
|
|
WHERE tm.user_id = v_uid
|
|
AND tm.status = 'active'
|
|
AND t.kind IN ('therapist', 'saas')
|
|
ORDER BY tm.created_at DESC
|
|
LIMIT 1;
|
|
|
|
IF v_existing IS NOT NULL THEN
|
|
RETURN v_existing;
|
|
END IF;
|
|
|
|
RETURN public.provision_account_tenant(v_uid, 'therapist');
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.ensure_personal_tenant() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: ensure_personal_tenant_for_user(uuid); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.ensure_personal_tenant_for_user(p_user_id uuid) RETURNS uuid
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
AS $$
|
|
declare
|
|
v_uid uuid;
|
|
v_existing uuid;
|
|
v_tenant uuid;
|
|
v_email text;
|
|
v_name text;
|
|
begin
|
|
v_uid := p_user_id;
|
|
if v_uid is null then
|
|
raise exception 'Missing user id';
|
|
end if;
|
|
|
|
-- só considera tenant pessoal (kind='saas')
|
|
select tm.tenant_id
|
|
into v_existing
|
|
from public.tenant_members tm
|
|
join public.tenants t on t.id = tm.tenant_id
|
|
where tm.user_id = v_uid
|
|
and tm.status = 'active'
|
|
and t.kind = 'saas'
|
|
order by tm.created_at desc
|
|
limit 1;
|
|
|
|
if v_existing is not null then
|
|
return v_existing;
|
|
end if;
|
|
|
|
select email into v_email
|
|
from auth.users
|
|
where id = v_uid;
|
|
|
|
v_name := coalesce(split_part(v_email, '@', 1), 'Conta');
|
|
|
|
insert into public.tenants (name, kind, created_at)
|
|
values (v_name || ' (Pessoal)', 'saas', 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.ensure_personal_tenant_for_user(p_user_id uuid) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: faq_votar(uuid); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.faq_votar(faq_id uuid) RETURNS void
|
|
CREATE FUNCTION public.ensure_personal_tenant_for_user(p_user_id uuid) RETURNS uuid
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
AS $$
|
|
declare
|
|
v_uid uuid;
|
|
v_existing uuid;
|
|
v_tenant uuid;
|
|
v_email text;
|
|
v_name text;
|
|
begin
|
|
v_uid := p_user_id;
|
|
if v_uid is null then
|
|
raise exception 'Missing user id';
|
|
end if;
|
|
|
|
-- só considera tenant pessoal (kind='saas')
|
|
select tm.tenant_id
|
|
into v_existing
|
|
from public.tenant_members tm
|
|
join public.tenants t on t.id = tm.tenant_id
|
|
where tm.user_id = v_uid
|
|
and tm.status = 'active'
|
|
and t.kind = 'saas'
|
|
order by tm.created_at desc
|
|
limit 1;
|
|
|
|
if v_existing is not null then
|
|
return v_existing;
|
|
end if;
|
|
|
|
select email into v_email
|
|
from auth.users
|
|
where id = v_uid;
|
|
|
|
v_name := coalesce(split_part(v_email, '@', 1), 'Conta');
|
|
|
|
insert into public.tenants (name, kind, created_at)
|
|
values (v_name || ' (Pessoal)', 'saas', 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.ensure_personal_tenant_for_user(p_user_id uuid) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: faq_votar(uuid); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.faq_votar(faq_id uuid) RETURNS void
|
|
CREATE FUNCTION public.get_my_email() RETURNS text
|
|
LANGUAGE sql SECURITY DEFINER
|
|
SET search_path TO 'public', 'auth'
|
|
AS $$
|
|
select lower(email)
|
|
from auth.users
|
|
where id = auth.uid();
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.get_my_email() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: guard_account_type_immutable(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.guard_account_type_immutable() RETURNS trigger
|
|
CREATE FUNCTION public.guard_account_type_immutable() RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
BEGIN
|
|
IF OLD.account_type <> 'free' AND NEW.account_type IS DISTINCT FROM OLD.account_type THEN
|
|
RAISE EXCEPTION 'account_type é imutável após escolha (atual: "%" para tentativa: "%"). Para mudar de perfil, crie uma nova conta.', OLD.account_type, NEW.account_type
|
|
USING ERRCODE = 'P0001';
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.guard_account_type_immutable() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: guard_locked_commitment(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
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.guard_patient_cannot_own_tenant() RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
DECLARE
|
|
v_account_type text;
|
|
BEGIN
|
|
SELECT account_type INTO v_account_type
|
|
FROM public.profiles
|
|
WHERE id = NEW.user_id;
|
|
|
|
IF v_account_type = 'patient' AND NEW.role IN ('tenant_admin', 'therapist') THEN
|
|
RAISE EXCEPTION 'Usuário com perfil "patient" não pode ser proprietário ou terapeuta de um tenant. Se tornou profissional? Crie uma nova conta.'
|
|
USING ERRCODE = 'P0001';
|
|
END IF;
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.guard_patient_cannot_own_tenant() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: guard_tenant_kind_immutable(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.guard_tenant_kind_immutable() RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
BEGIN
|
|
IF NEW.kind IS DISTINCT FROM OLD.kind THEN
|
|
RAISE EXCEPTION 'tenants.kind é imutável após criação. Tentativa de alterar "%" para "%".', OLD.kind, NEW.kind
|
|
USING ERRCODE = 'P0001';
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.guard_tenant_kind_immutable() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: handle_new_user(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.handle_new_user() RETURNS trigger
|
|
CREATE FUNCTION public.guard_tenant_kind_immutable() RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
BEGIN
|
|
IF NEW.kind IS DISTINCT FROM OLD.kind THEN
|
|
RAISE EXCEPTION 'tenants.kind é imutável após criação. Tentativa de alterar "%" para "%".', OLD.kind, NEW.kind
|
|
USING ERRCODE = 'P0001';
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.guard_tenant_kind_immutable() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: handle_new_user(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.handle_new_user() RETURNS trigger
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
SET search_path TO 'public'
|
|
AS $$
|
|
BEGIN
|
|
INSERT INTO public.profiles (id, role, account_type)
|
|
VALUES (NEW.id, 'portal_user', 'free')
|
|
ON CONFLICT (id) DO NOTHING;
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.handle_new_user() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: handle_new_user_create_personal_tenant(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.handle_new_user_create_personal_tenant() RETURNS trigger
|
|
CREATE FUNCTION public.handle_new_user() RETURNS trigger
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
SET search_path TO 'public'
|
|
AS $$
|
|
BEGIN
|
|
INSERT INTO public.profiles (id, role, account_type)
|
|
VALUES (NEW.id, 'portal_user', 'free')
|
|
ON CONFLICT (id) DO NOTHING;
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.handle_new_user() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: handle_new_user_create_personal_tenant(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.handle_new_user_create_personal_tenant() RETURNS trigger
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
AS $$
|
|
BEGIN
|
|
-- Desabilitado. Tenant criado no onboarding via provision_account_tenant().
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.handle_new_user_create_personal_tenant() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: has_feature(uuid, text); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.has_feature(p_owner_id uuid, p_feature_key text) RETURNS boolean
|
|
CREATE FUNCTION public.handle_new_user_create_personal_tenant() RETURNS trigger
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
AS $$
|
|
BEGIN
|
|
-- Desabilitado. Tenant criado no onboarding via provision_account_tenant().
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.handle_new_user_create_personal_tenant() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: has_feature(uuid, text); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.has_feature(p_owner_id uuid, p_feature_key text) RETURNS boolean
|
|
LANGUAGE sql STABLE
|
|
AS $$
|
|
select exists (
|
|
select 1
|
|
from public.owner_feature_entitlements e
|
|
where e.owner_id = p_owner_id
|
|
and e.feature_key = p_feature_key
|
|
);
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.has_feature(p_owner_id uuid, p_feature_key text) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: is_clinic_tenant(uuid); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.is_clinic_tenant(_tenant_id uuid) RETURNS boolean
|
|
CREATE FUNCTION public.has_feature(p_owner_id uuid, p_feature_key text) RETURNS boolean
|
|
LANGUAGE sql STABLE
|
|
AS $$
|
|
select exists (
|
|
select 1
|
|
from public.owner_feature_entitlements e
|
|
where e.owner_id = p_owner_id
|
|
and e.feature_key = p_feature_key
|
|
);
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.has_feature(p_owner_id uuid, p_feature_key text) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: is_clinic_tenant(uuid); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.is_clinic_tenant(_tenant_id uuid) RETURNS boolean
|
|
LANGUAGE sql STABLE
|
|
AS $$
|
|
SELECT EXISTS (
|
|
SELECT 1 FROM public.tenants t
|
|
WHERE t.id = _tenant_id
|
|
AND t.kind IN ('clinic', 'clinic_coworking', 'clinic_reception', 'clinic_full')
|
|
);
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.is_clinic_tenant(_tenant_id uuid) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: is_saas_admin(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.is_saas_admin() RETURNS boolean
|
|
CREATE FUNCTION public.is_clinic_tenant(_tenant_id uuid) RETURNS boolean
|
|
LANGUAGE sql STABLE
|
|
AS $$
|
|
SELECT EXISTS (
|
|
SELECT 1 FROM public.tenants t
|
|
WHERE t.id = _tenant_id
|
|
AND t.kind IN ('clinic', 'clinic_coworking', 'clinic_reception', 'clinic_full')
|
|
);
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.is_clinic_tenant(_tenant_id uuid) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: is_saas_admin(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.is_saas_admin() RETURNS boolean
|
|
LANGUAGE sql STABLE
|
|
AS $$
|
|
select exists (
|
|
select 1 from public.saas_admins sa
|
|
where sa.user_id = auth.uid()
|
|
);
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.is_saas_admin() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: is_tenant_admin(uuid); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.is_tenant_admin(p_tenant_id uuid) RETURNS boolean
|
|
CREATE FUNCTION public.is_saas_admin() RETURNS boolean
|
|
LANGUAGE sql STABLE
|
|
AS $$
|
|
select exists (
|
|
select 1 from public.saas_admins sa
|
|
where sa.user_id = auth.uid()
|
|
);
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.is_saas_admin() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: is_tenant_admin(uuid); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.is_tenant_admin(p_tenant_id uuid) RETURNS boolean
|
|
LANGUAGE sql STABLE SECURITY DEFINER
|
|
SET search_path TO 'public'
|
|
SET row_security TO 'off'
|
|
AS $$
|
|
select exists (
|
|
select 1
|
|
from public.tenant_members tm
|
|
where tm.tenant_id = p_tenant_id
|
|
and tm.user_id = auth.uid()
|
|
and tm.role = 'tenant_admin'
|
|
and tm.status = 'active'
|
|
);
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.is_tenant_admin(p_tenant_id uuid) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: is_tenant_member(uuid); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.is_tenant_member(_tenant_id uuid) RETURNS boolean
|
|
CREATE FUNCTION public.is_tenant_admin(p_tenant_id uuid) RETURNS boolean
|
|
LANGUAGE sql STABLE SECURITY DEFINER
|
|
SET search_path TO 'public'
|
|
SET row_security TO 'off'
|
|
AS $$
|
|
select exists (
|
|
select 1
|
|
from public.tenant_members tm
|
|
where tm.tenant_id = p_tenant_id
|
|
and tm.user_id = auth.uid()
|
|
and tm.role = 'tenant_admin'
|
|
and tm.status = 'active'
|
|
);
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.is_tenant_admin(p_tenant_id uuid) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: is_tenant_member(uuid); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.is_tenant_member(_tenant_id uuid) RETURNS boolean
|
|
LANGUAGE sql STABLE
|
|
AS $$
|
|
select exists (
|
|
select 1
|
|
from public.tenant_members m
|
|
where m.tenant_id = _tenant_id
|
|
and m.user_id = auth.uid()
|
|
and m.status = 'active'
|
|
);
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.is_tenant_member(_tenant_id uuid) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: is_therapist_tenant(uuid); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.is_therapist_tenant(_tenant_id uuid) RETURNS boolean
|
|
CREATE FUNCTION public.is_tenant_member(_tenant_id uuid) RETURNS boolean
|
|
LANGUAGE sql STABLE
|
|
AS $$
|
|
select exists (
|
|
select 1
|
|
from public.tenant_members m
|
|
where m.tenant_id = _tenant_id
|
|
and m.user_id = auth.uid()
|
|
and m.status = 'active'
|
|
);
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.is_tenant_member(_tenant_id uuid) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: is_therapist_tenant(uuid); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.is_therapist_tenant(_tenant_id uuid) RETURNS boolean
|
|
LANGUAGE sql STABLE
|
|
AS $$
|
|
SELECT EXISTS (
|
|
SELECT 1 FROM public.tenants t
|
|
WHERE t.id = _tenant_id AND t.kind = 'therapist'
|
|
);
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.is_therapist_tenant(_tenant_id uuid) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: jwt_email(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.jwt_email() RETURNS text
|
|
CREATE FUNCTION public.is_therapist_tenant(_tenant_id uuid) RETURNS boolean
|
|
LANGUAGE sql STABLE
|
|
AS $$
|
|
SELECT EXISTS (
|
|
SELECT 1 FROM public.tenants t
|
|
WHERE t.id = _tenant_id AND t.kind = 'therapist'
|
|
);
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.is_therapist_tenant(_tenant_id uuid) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: jwt_email(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.jwt_email() RETURNS text
|
|
LANGUAGE sql STABLE
|
|
AS $$
|
|
select nullif(lower(current_setting('request.jwt.claim.email', true)), '');
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.jwt_email() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: list_financial_records(uuid, integer, integer, text, text, uuid, integer, integer); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.list_financial_records(p_owner_id uuid, p_year integer DEFAULT NULL::integer, p_month integer DEFAULT NULL::integer, p_type text DEFAULT NULL::text, p_status text DEFAULT NULL::text, p_patient_id uuid DEFAULT NULL::uuid, p_limit integer DEFAULT 50, p_offset integer DEFAULT 0) RETURNS SETOF public.financial_records
|
|
CREATE FUNCTION public.jwt_email() RETURNS text
|
|
LANGUAGE sql STABLE
|
|
AS $$
|
|
select nullif(lower(current_setting('request.jwt.claim.email', true)), '');
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.jwt_email() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: list_financial_records(uuid, integer, integer, text, text, uuid, integer, integer); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.list_financial_records(p_owner_id uuid, p_year integer DEFAULT NULL::integer, p_month integer DEFAULT NULL::integer, p_type text DEFAULT NULL::text, p_status text DEFAULT NULL::text, p_patient_id uuid DEFAULT NULL::uuid, p_limit integer DEFAULT 50, p_offset integer DEFAULT 0) RETURNS SETOF public.financial_records
|
|
CREATE FUNCTION public.my_tenants() RETURNS TABLE(tenant_id uuid, role text, status text, kind text)
|
|
LANGUAGE sql STABLE
|
|
AS $$
|
|
select
|
|
tm.tenant_id,
|
|
tm.role,
|
|
tm.status,
|
|
t.kind
|
|
from public.tenant_members tm
|
|
join public.tenants t on t.id = tm.tenant_id
|
|
where tm.user_id = auth.uid();
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.my_tenants() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: notice_track_click(uuid); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
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
|
|
CREATE FUNCTION public.on_new_user_seed_patient_groups() RETURNS trigger
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
SET search_path TO 'public'
|
|
AS $$
|
|
BEGIN
|
|
PERFORM public.seed_default_patient_groups(NEW.id);
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.on_new_user_seed_patient_groups() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: patients_validate_member_consistency(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.patients_validate_member_consistency() RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
DECLARE
|
|
v_tenant_responsible uuid;
|
|
v_tenant_therapist uuid;
|
|
BEGIN
|
|
-- responsible_member sempre deve existir e ser do tenant
|
|
SELECT tenant_id INTO v_tenant_responsible
|
|
FROM public.tenant_members
|
|
WHERE id = NEW.responsible_member_id;
|
|
|
|
IF v_tenant_responsible IS NULL THEN
|
|
RAISE EXCEPTION 'Responsible member not found';
|
|
END IF;
|
|
|
|
IF NEW.tenant_id IS NULL THEN
|
|
RAISE EXCEPTION 'tenant_id is required';
|
|
END IF;
|
|
|
|
IF v_tenant_responsible <> NEW.tenant_id THEN
|
|
RAISE EXCEPTION 'Responsible member must belong to the same tenant';
|
|
END IF;
|
|
|
|
-- therapist scope: therapist_member_id deve existir e ser do mesmo tenant
|
|
IF NEW.patient_scope = 'therapist' THEN
|
|
IF NEW.therapist_member_id IS NULL THEN
|
|
RAISE EXCEPTION 'therapist_member_id is required when patient_scope=therapist';
|
|
END IF;
|
|
|
|
SELECT tenant_id INTO v_tenant_therapist
|
|
FROM public.tenant_members
|
|
WHERE id = NEW.therapist_member_id;
|
|
|
|
IF v_tenant_therapist IS NULL THEN
|
|
RAISE EXCEPTION 'Therapist member not found';
|
|
END IF;
|
|
|
|
IF v_tenant_therapist <> NEW.tenant_id THEN
|
|
RAISE EXCEPTION 'Therapist member must belong to the same tenant';
|
|
END IF;
|
|
END IF;
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.patients_validate_member_consistency() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: patients_validate_responsible_member_tenant(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.patients_validate_responsible_member_tenant() RETURNS trigger
|
|
CREATE FUNCTION public.prevent_promoting_to_system() RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
begin
|
|
if new.is_system = true and old.is_system is distinct from true then
|
|
raise exception 'Não é permitido transformar um grupo comum em grupo do sistema.';
|
|
end if;
|
|
return new;
|
|
end;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.prevent_promoting_to_system() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: prevent_saas_membership(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.prevent_saas_membership() RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
BEGIN
|
|
IF EXISTS (
|
|
SELECT 1
|
|
FROM public.profiles
|
|
WHERE id = NEW.user_id
|
|
AND role = 'saas_admin'
|
|
) THEN
|
|
RAISE EXCEPTION 'SaaS admin cannot belong to tenant';
|
|
END IF;
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.prevent_saas_membership() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: prevent_system_group_changes(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.prevent_system_group_changes() RETURNS trigger
|
|
CREATE FUNCTION public.prevent_saas_membership() RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
BEGIN
|
|
IF EXISTS (
|
|
SELECT 1
|
|
FROM public.profiles
|
|
WHERE id = NEW.user_id
|
|
AND role = 'saas_admin'
|
|
) THEN
|
|
RAISE EXCEPTION 'SaaS admin cannot belong to tenant';
|
|
END IF;
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.prevent_saas_membership() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: prevent_system_group_changes(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.prevent_system_group_changes() RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
begin
|
|
-- Se for grupo do sistema, regras rígidas:
|
|
if old.is_system = true then
|
|
|
|
-- nunca pode deletar
|
|
if tg_op = 'DELETE' then
|
|
raise exception 'Grupos padrão do sistema não podem ser alterados ou excluídos.';
|
|
end if;
|
|
|
|
if tg_op = 'UPDATE' then
|
|
-- permite SOMENTE mudar tenant_id e/ou updated_at
|
|
-- qualquer mudança de conteúdo permanece proibida
|
|
if
|
|
new.nome is distinct from old.nome or
|
|
new.descricao is distinct from old.descricao or
|
|
new.cor is distinct from old.cor or
|
|
new.is_active is distinct from old.is_active or
|
|
new.is_system is distinct from old.is_system or
|
|
new.owner_id is distinct from old.owner_id or
|
|
new.therapist_id is distinct from old.therapist_id or
|
|
new.created_at is distinct from old.created_at
|
|
then
|
|
raise exception 'Grupos padrão do sistema não podem ser alterados ou excluídos.';
|
|
end if;
|
|
|
|
-- chegou aqui: só tenant_id/updated_at mudaram -> ok
|
|
return new;
|
|
end if;
|
|
|
|
end if;
|
|
|
|
-- não-system: deixa passar
|
|
if tg_op = 'DELETE' then
|
|
return old;
|
|
end if;
|
|
|
|
return new;
|
|
end;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.prevent_system_group_changes() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: provision_account_tenant(uuid, text, text); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.provision_account_tenant(p_user_id uuid, p_kind text, p_name text DEFAULT NULL::text) RETURNS uuid
|
|
CREATE FUNCTION public.provision_account_tenant(p_user_id uuid, p_kind text, p_name text DEFAULT NULL::text) RETURNS uuid
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
AS $$
|
|
DECLARE
|
|
v_tenant_id uuid;
|
|
v_account_type text;
|
|
v_name text;
|
|
BEGIN
|
|
IF p_kind NOT IN ('therapist', 'clinic_coworking', 'clinic_reception', 'clinic_full') THEN
|
|
RAISE EXCEPTION 'kind inválido: "%". Use: therapist, clinic_coworking, clinic_reception, clinic_full.', p_kind
|
|
USING ERRCODE = 'P0001';
|
|
END IF;
|
|
|
|
v_account_type := CASE WHEN p_kind = 'therapist' THEN 'therapist' ELSE 'clinic' END;
|
|
|
|
IF EXISTS (
|
|
SELECT 1
|
|
FROM public.tenant_members tm
|
|
JOIN public.tenants t ON t.id = tm.tenant_id
|
|
WHERE tm.user_id = p_user_id
|
|
AND tm.role = 'tenant_admin'
|
|
AND tm.status = 'active'
|
|
AND t.kind = p_kind
|
|
) THEN
|
|
RAISE EXCEPTION 'Usuário já possui um tenant do tipo "%".', p_kind
|
|
USING ERRCODE = 'P0001';
|
|
END IF;
|
|
|
|
v_name := COALESCE(
|
|
NULLIF(TRIM(p_name), ''),
|
|
(
|
|
SELECT COALESCE(NULLIF(TRIM(pr.full_name), ''), SPLIT_PART(au.email, '@', 1))
|
|
FROM public.profiles pr
|
|
JOIN auth.users au ON au.id = pr.id
|
|
WHERE pr.id = p_user_id
|
|
),
|
|
'Conta'
|
|
);
|
|
|
|
INSERT INTO public.tenants (name, kind, created_at)
|
|
VALUES (v_name, p_kind, now())
|
|
RETURNING id INTO v_tenant_id;
|
|
|
|
INSERT INTO public.tenant_members (tenant_id, user_id, role, status, created_at)
|
|
VALUES (v_tenant_id, p_user_id, 'tenant_admin', 'active', now());
|
|
|
|
UPDATE public.profiles
|
|
SET account_type = v_account_type
|
|
WHERE id = p_user_id;
|
|
|
|
PERFORM public.seed_determined_commitments(v_tenant_id);
|
|
|
|
RETURN v_tenant_id;
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.provision_account_tenant(p_user_id uuid, p_kind text, p_name text) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: FUNCTION provision_account_tenant(p_user_id uuid, p_kind text, p_name text); Type: COMMENT; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
COMMENT ON FUNCTION public.provision_account_tenant(p_user_id uuid, p_kind text, p_name text) IS 'Cria o tenant do tipo correto e atualiza account_type no profile. Chamar no onboarding após escolha/pagamento de plano therapist ou clinic. p_kind: therapist | clinic_coworking | clinic_reception | clinic_full';
|
|
|
|
|
|
--
|
|
-- Name: reactivate_subscription(uuid); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.reactivate_subscription(p_subscription_id uuid) RETURNS public.subscriptions
|
|
CREATE FUNCTION public.tenant_accept_invite(p_token uuid) RETURNS jsonb
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
SET search_path TO 'public', 'auth'
|
|
AS $$
|
|
declare
|
|
v_uid uuid;
|
|
v_email text;
|
|
v_invite public.tenant_invites%rowtype;
|
|
begin
|
|
-- 1) precisa estar autenticado
|
|
v_uid := auth.uid();
|
|
if v_uid is null then
|
|
raise exception 'not_authenticated' using errcode = 'P0001';
|
|
end if;
|
|
|
|
-- 2) pega email real do usuário logado sem depender do JWT claim
|
|
select u.email
|
|
into v_email
|
|
from auth.users u
|
|
where u.id = v_uid;
|
|
|
|
if v_email is null or length(trim(v_email)) = 0 then
|
|
raise exception 'missing_user_email' using errcode = 'P0001';
|
|
end if;
|
|
|
|
-- 3) carrega o invite e trava linha (evita 2 aceites concorrentes)
|
|
select *
|
|
into v_invite
|
|
from public.tenant_invites i
|
|
where i.token = p_token
|
|
for update;
|
|
|
|
if not found then
|
|
raise exception 'invite_not_found' using errcode = 'P0001';
|
|
end if;
|
|
|
|
-- 4) validações de estado
|
|
if v_invite.revoked_at is not null then
|
|
raise exception 'invite_revoked' using errcode = 'P0001';
|
|
end if;
|
|
|
|
if v_invite.accepted_at is not null then
|
|
raise exception 'invite_already_accepted' using errcode = 'P0001';
|
|
end if;
|
|
|
|
if v_invite.expires_at is not null and v_invite.expires_at <= now() then
|
|
raise exception 'invite_expired' using errcode = 'P0001';
|
|
end if;
|
|
|
|
-- 5) valida email (case-insensitive)
|
|
if lower(trim(v_invite.email)) <> lower(trim(v_email)) then
|
|
raise exception 'email_mismatch' using errcode = 'P0001';
|
|
end if;
|
|
|
|
-- 6) consome o invite
|
|
update public.tenant_invites
|
|
set accepted_at = now(),
|
|
accepted_by = v_uid
|
|
where id = v_invite.id;
|
|
|
|
-- 7) cria ou reativa o membership
|
|
insert into public.tenant_members (tenant_id, user_id, role, status, created_at)
|
|
values (v_invite.tenant_id, v_uid, v_invite.role, 'active', now())
|
|
on conflict (tenant_id, user_id)
|
|
do update set
|
|
role = excluded.role,
|
|
status = 'active';
|
|
|
|
-- 8) retorno útil pro front (você já tenta ler tenant_id no AcceptInvitePage)
|
|
return jsonb_build_object(
|
|
'ok', true,
|
|
'tenant_id', v_invite.tenant_id,
|
|
'role', v_invite.role
|
|
);
|
|
end;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.tenant_accept_invite(p_token uuid) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: tenant_members; Type: TABLE; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE TABLE public.tenant_members (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
tenant_id uuid NOT NULL,
|
|
user_id uuid NOT NULL,
|
|
role text NOT NULL,
|
|
status text DEFAULT 'active'::text NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
|
|
|
|
ALTER TABLE public.tenant_members OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: tenant_add_member_by_email(uuid, text, text); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.tenant_add_member_by_email(p_tenant_id uuid, p_email text, p_role text DEFAULT 'therapist'::text) RETURNS public.tenant_members
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
SET search_path TO 'public', 'auth'
|
|
AS $$
|
|
declare
|
|
v_target_uid uuid;
|
|
v_member public.tenant_members%rowtype;
|
|
v_is_admin boolean;
|
|
v_email text;
|
|
begin
|
|
if p_tenant_id is null then
|
|
raise exception 'tenant_id é obrigatório';
|
|
end if;
|
|
|
|
v_email := lower(trim(coalesce(p_email, '')));
|
|
if v_email = '' then
|
|
raise exception 'email é obrigatório';
|
|
end if;
|
|
|
|
-- valida role permitida
|
|
if p_role not in ('tenant_admin','therapist','secretary','patient') then
|
|
raise exception 'role inválida: %', p_role;
|
|
end if;
|
|
|
|
-- apenas admin do tenant (role real no banco)
|
|
select exists (
|
|
select 1
|
|
from public.tenant_members tm
|
|
where tm.tenant_id = p_tenant_id
|
|
and tm.user_id = auth.uid()
|
|
and tm.role = 'tenant_admin'
|
|
and coalesce(tm.status,'active') = 'active'
|
|
) into v_is_admin;
|
|
|
|
if not v_is_admin then
|
|
raise exception 'sem permissão: apenas admin da clínica pode adicionar membros';
|
|
end if;
|
|
|
|
-- acha usuário pelo e-mail no Supabase Auth
|
|
select u.id
|
|
into v_target_uid
|
|
from auth.users u
|
|
where lower(u.email) = v_email
|
|
limit 1;
|
|
|
|
if v_target_uid is null then
|
|
raise exception 'nenhum usuário encontrado com este e-mail';
|
|
end if;
|
|
|
|
-- cria ou reativa membro
|
|
insert into public.tenant_members (tenant_id, user_id, role, status)
|
|
values (p_tenant_id, v_target_uid, p_role, 'active')
|
|
on conflict (tenant_id, user_id)
|
|
do update set
|
|
role = excluded.role,
|
|
status = 'active'
|
|
returning * into v_member;
|
|
|
|
return v_member;
|
|
end;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.tenant_add_member_by_email(p_tenant_id uuid, p_email text, p_role text) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: tenant_feature_allowed(uuid, text); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.tenant_feature_allowed(p_tenant_id uuid, p_feature_key text) RETURNS boolean
|
|
CREATE FUNCTION public.tenant_add_member_by_email(p_tenant_id uuid, p_email text, p_role text DEFAULT 'therapist'::text) RETURNS public.tenant_members
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
SET search_path TO 'public', 'auth'
|
|
AS $$
|
|
declare
|
|
v_target_uid uuid;
|
|
v_member public.tenant_members%rowtype;
|
|
v_is_admin boolean;
|
|
v_email text;
|
|
begin
|
|
if p_tenant_id is null then
|
|
raise exception 'tenant_id é obrigatório';
|
|
end if;
|
|
|
|
v_email := lower(trim(coalesce(p_email, '')));
|
|
if v_email = '' then
|
|
raise exception 'email é obrigatório';
|
|
end if;
|
|
|
|
-- valida role permitida
|
|
if p_role not in ('tenant_admin','therapist','secretary','patient') then
|
|
raise exception 'role inválida: %', p_role;
|
|
end if;
|
|
|
|
-- apenas admin do tenant (role real no banco)
|
|
select exists (
|
|
select 1
|
|
from public.tenant_members tm
|
|
where tm.tenant_id = p_tenant_id
|
|
and tm.user_id = auth.uid()
|
|
and tm.role = 'tenant_admin'
|
|
and coalesce(tm.status,'active') = 'active'
|
|
) into v_is_admin;
|
|
|
|
if not v_is_admin then
|
|
raise exception 'sem permissão: apenas admin da clínica pode adicionar membros';
|
|
end if;
|
|
|
|
-- acha usuário pelo e-mail no Supabase Auth
|
|
select u.id
|
|
into v_target_uid
|
|
from auth.users u
|
|
where lower(u.email) = v_email
|
|
limit 1;
|
|
|
|
if v_target_uid is null then
|
|
raise exception 'nenhum usuário encontrado com este e-mail';
|
|
end if;
|
|
|
|
-- cria ou reativa membro
|
|
insert into public.tenant_members (tenant_id, user_id, role, status)
|
|
values (p_tenant_id, v_target_uid, p_role, 'active')
|
|
on conflict (tenant_id, user_id)
|
|
do update set
|
|
role = excluded.role,
|
|
status = 'active'
|
|
returning * into v_member;
|
|
|
|
return v_member;
|
|
end;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.tenant_add_member_by_email(p_tenant_id uuid, p_email text, p_role text) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: tenant_feature_allowed(uuid, text); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.tenant_feature_allowed(p_tenant_id uuid, p_feature_key text) RETURNS boolean
|
|
CREATE FUNCTION public.tenant_feature_allowed(p_tenant_id uuid, p_feature_key text) RETURNS boolean
|
|
LANGUAGE sql STABLE
|
|
AS $$
|
|
select exists (
|
|
select 1
|
|
from public.v_tenant_entitlements v
|
|
where v.tenant_id = p_tenant_id
|
|
and v.feature_key = p_feature_key
|
|
and coalesce(v.allowed, false) = true
|
|
);
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.tenant_feature_allowed(p_tenant_id uuid, p_feature_key text) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: tenant_feature_enabled(uuid, text); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.tenant_feature_enabled(p_tenant_id uuid, p_feature_key text) RETURNS boolean
|
|
LANGUAGE sql STABLE
|
|
AS $$
|
|
select coalesce(
|
|
(select tf.enabled
|
|
from public.tenant_features tf
|
|
where tf.tenant_id = p_tenant_id and tf.feature_key = p_feature_key),
|
|
false
|
|
);
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.tenant_feature_enabled(p_tenant_id uuid, p_feature_key text) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: tenant_features_guard_with_plan(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.tenant_features_guard_with_plan() RETURNS trigger
|
|
CREATE FUNCTION public.tenant_feature_enabled(p_tenant_id uuid, p_feature_key text) RETURNS boolean
|
|
LANGUAGE sql STABLE
|
|
AS $$
|
|
select coalesce(
|
|
(select tf.enabled
|
|
from public.tenant_features tf
|
|
where tf.tenant_id = p_tenant_id and tf.feature_key = p_feature_key),
|
|
false
|
|
);
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.tenant_feature_enabled(p_tenant_id uuid, p_feature_key text) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: tenant_features_guard_with_plan(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.tenant_features_guard_with_plan() RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
declare
|
|
v_allowed boolean;
|
|
begin
|
|
-- só valida quando está habilitando
|
|
if new.enabled is distinct from true then
|
|
return new;
|
|
end if;
|
|
|
|
-- permitido pelo plano do tenant?
|
|
select exists (
|
|
select 1
|
|
from public.v_tenant_entitlements_full v
|
|
where v.tenant_id = new.tenant_id
|
|
and v.feature_key = new.feature_key
|
|
and v.allowed = true
|
|
)
|
|
into v_allowed;
|
|
|
|
if not v_allowed then
|
|
raise exception 'Feature % não permitida pelo plano atual do tenant %.',
|
|
new.feature_key, new.tenant_id
|
|
using errcode = 'P0001';
|
|
end if;
|
|
|
|
return new;
|
|
end;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.tenant_features_guard_with_plan() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: tenant_has_feature(uuid, text); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.tenant_has_feature(_tenant_id uuid, _feature text) RETURNS boolean
|
|
LANGUAGE sql STABLE
|
|
AS $$
|
|
select
|
|
exists (
|
|
select 1
|
|
from public.v_tenant_entitlements e
|
|
where e.tenant_id = _tenant_id
|
|
and e.feature_key = _feature
|
|
and e.allowed = true
|
|
)
|
|
or exists (
|
|
select 1
|
|
from public.tenant_features tf
|
|
where tf.tenant_id = _tenant_id
|
|
and tf.feature_key = _feature
|
|
and tf.enabled = true
|
|
);
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.tenant_has_feature(_tenant_id uuid, _feature text) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: tenant_invite_member_by_email(uuid, text, text); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.tenant_invite_member_by_email(p_tenant_id uuid, p_email text, p_role text) RETURNS uuid
|
|
CREATE FUNCTION public.tenant_features_guard_with_plan() RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
declare
|
|
v_allowed boolean;
|
|
begin
|
|
-- só valida quando está habilitando
|
|
if new.enabled is distinct from true then
|
|
return new;
|
|
end if;
|
|
|
|
-- permitido pelo plano do tenant?
|
|
select exists (
|
|
select 1
|
|
from public.v_tenant_entitlements_full v
|
|
where v.tenant_id = new.tenant_id
|
|
and v.feature_key = new.feature_key
|
|
and v.allowed = true
|
|
)
|
|
into v_allowed;
|
|
|
|
if not v_allowed then
|
|
raise exception 'Feature % não permitida pelo plano atual do tenant %.',
|
|
new.feature_key, new.tenant_id
|
|
using errcode = 'P0001';
|
|
end if;
|
|
|
|
return new;
|
|
end;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.tenant_features_guard_with_plan() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: tenant_has_feature(uuid, text); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.tenant_has_feature(_tenant_id uuid, _feature text) RETURNS boolean
|
|
LANGUAGE sql STABLE
|
|
AS $$
|
|
select
|
|
exists (
|
|
select 1
|
|
from public.v_tenant_entitlements e
|
|
where e.tenant_id = _tenant_id
|
|
and e.feature_key = _feature
|
|
and e.allowed = true
|
|
)
|
|
or exists (
|
|
select 1
|
|
from public.tenant_features tf
|
|
where tf.tenant_id = _tenant_id
|
|
and tf.feature_key = _feature
|
|
and tf.enabled = true
|
|
);
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.tenant_has_feature(_tenant_id uuid, _feature text) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: tenant_invite_member_by_email(uuid, text, text); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.tenant_invite_member_by_email(p_tenant_id uuid, p_email text, p_role text) RETURNS uuid
|
|
CREATE FUNCTION public.tenant_has_feature(_tenant_id uuid, _feature text) RETURNS boolean
|
|
LANGUAGE sql STABLE
|
|
AS $$
|
|
select
|
|
exists (
|
|
select 1
|
|
from public.v_tenant_entitlements e
|
|
where e.tenant_id = _tenant_id
|
|
and e.feature_key = _feature
|
|
and e.allowed = true
|
|
)
|
|
or exists (
|
|
select 1
|
|
from public.tenant_features tf
|
|
where tf.tenant_id = _tenant_id
|
|
and tf.feature_key = _feature
|
|
and tf.enabled = true
|
|
);
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.tenant_has_feature(_tenant_id uuid, _feature text) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: tenant_invite_member_by_email(uuid, text, text); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.tenant_invite_member_by_email(p_tenant_id uuid, p_email text, p_role text) RETURNS uuid
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
SET search_path TO 'public', 'auth'
|
|
AS $$
|
|
declare
|
|
v_email text;
|
|
v_my_email text;
|
|
v_token uuid;
|
|
v_updated int;
|
|
begin
|
|
-- validações básicas
|
|
if p_tenant_id is null then
|
|
raise exception 'tenant_id inválido' using errcode = 'P0001';
|
|
end if;
|
|
|
|
v_email := lower(trim(coalesce(p_email, '')));
|
|
if v_email = '' then
|
|
raise exception 'Informe um email' using errcode = 'P0001';
|
|
end if;
|
|
|
|
-- role permitido (ajuste se quiser)
|
|
if p_role is null or p_role not in ('therapist', 'secretary') then
|
|
raise exception 'Role inválido (use therapist/secretary)' using errcode = 'P0001';
|
|
end if;
|
|
|
|
-- ✅ bloqueio: auto-convite
|
|
v_my_email := public.get_my_email();
|
|
if v_my_email is not null and v_email = v_my_email then
|
|
raise exception 'Você não pode convidar o seu próprio email.' using errcode = 'P0001';
|
|
end if;
|
|
|
|
-- ✅ bloqueio: já é membro ativo do tenant
|
|
if exists (
|
|
select 1
|
|
from tenant_members tm
|
|
join auth.users au on au.id = tm.user_id
|
|
where tm.tenant_id = p_tenant_id
|
|
and tm.status = 'active'
|
|
and lower(au.email) = v_email
|
|
) then
|
|
raise exception 'Este email já está vinculado a esta clínica.' using errcode = 'P0001';
|
|
end if;
|
|
|
|
-- ✅ permissão: só admin do tenant pode convidar
|
|
if not exists (
|
|
select 1
|
|
from tenant_members me
|
|
where me.tenant_id = p_tenant_id
|
|
and me.user_id = auth.uid()
|
|
and me.status = 'active'
|
|
and me.role in ('tenant_admin','clinic_admin')
|
|
) then
|
|
raise exception 'Sem permissão para convidar membros.' using errcode = 'P0001';
|
|
end if;
|
|
|
|
-- Gera token (reenvio simples / regeneração)
|
|
v_token := gen_random_uuid();
|
|
|
|
-- 1) tenta "regerar" um convite pendente existente (mesmo email)
|
|
update tenant_invites
|
|
set token = v_token,
|
|
role = p_role,
|
|
created_at = now(),
|
|
expires_at = now() + interval '7 days',
|
|
accepted_at = null,
|
|
revoked_at = null
|
|
where tenant_id = p_tenant_id
|
|
and lower(email) = v_email
|
|
and accepted_at is null
|
|
and revoked_at is null;
|
|
|
|
get diagnostics v_updated = row_count;
|
|
|
|
-- 2) se não atualizou nada, cria convite novo
|
|
if v_updated = 0 then
|
|
insert into tenant_invites (tenant_id, email, role, token, created_at, expires_at)
|
|
values (p_tenant_id, v_email, p_role, v_token, now(), now() + interval '7 days');
|
|
end if;
|
|
|
|
return v_token;
|
|
end;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.tenant_invite_member_by_email(p_tenant_id uuid, p_email text, p_role text) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: tenant_reactivate_member(uuid, uuid); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.tenant_reactivate_member(p_tenant_id uuid, p_member_user_id uuid) RETURNS void
|
|
CREATE FUNCTION public.tenant_invite_member_by_email(p_tenant_id uuid, p_email text, p_role text) RETURNS uuid
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
SET search_path TO 'public', 'auth'
|
|
AS $$
|
|
declare
|
|
v_email text;
|
|
v_my_email text;
|
|
v_token uuid;
|
|
v_updated int;
|
|
begin
|
|
-- validações básicas
|
|
if p_tenant_id is null then
|
|
raise exception 'tenant_id inválido' using errcode = 'P0001';
|
|
end if;
|
|
|
|
v_email := lower(trim(coalesce(p_email, '')));
|
|
if v_email = '' then
|
|
raise exception 'Informe um email' using errcode = 'P0001';
|
|
end if;
|
|
|
|
-- role permitido (ajuste se quiser)
|
|
if p_role is null or p_role not in ('therapist', 'secretary') then
|
|
raise exception 'Role inválido (use therapist/secretary)' using errcode = 'P0001';
|
|
end if;
|
|
|
|
-- ✅ bloqueio: auto-convite
|
|
v_my_email := public.get_my_email();
|
|
if v_my_email is not null and v_email = v_my_email then
|
|
raise exception 'Você não pode convidar o seu próprio email.' using errcode = 'P0001';
|
|
end if;
|
|
|
|
-- ✅ bloqueio: já é membro ativo do tenant
|
|
if exists (
|
|
select 1
|
|
from tenant_members tm
|
|
join auth.users au on au.id = tm.user_id
|
|
where tm.tenant_id = p_tenant_id
|
|
and tm.status = 'active'
|
|
and lower(au.email) = v_email
|
|
) then
|
|
raise exception 'Este email já está vinculado a esta clínica.' using errcode = 'P0001';
|
|
end if;
|
|
|
|
-- ✅ permissão: só admin do tenant pode convidar
|
|
if not exists (
|
|
select 1
|
|
from tenant_members me
|
|
where me.tenant_id = p_tenant_id
|
|
and me.user_id = auth.uid()
|
|
and me.status = 'active'
|
|
and me.role in ('tenant_admin','clinic_admin')
|
|
) then
|
|
raise exception 'Sem permissão para convidar membros.' using errcode = 'P0001';
|
|
end if;
|
|
|
|
-- Gera token (reenvio simples / regeneração)
|
|
v_token := gen_random_uuid();
|
|
|
|
-- 1) tenta "regerar" um convite pendente existente (mesmo email)
|
|
update tenant_invites
|
|
set token = v_token,
|
|
role = p_role,
|
|
created_at = now(),
|
|
expires_at = now() + interval '7 days',
|
|
accepted_at = null,
|
|
revoked_at = null
|
|
where tenant_id = p_tenant_id
|
|
and lower(email) = v_email
|
|
and accepted_at is null
|
|
and revoked_at is null;
|
|
|
|
get diagnostics v_updated = row_count;
|
|
|
|
-- 2) se não atualizou nada, cria convite novo
|
|
if v_updated = 0 then
|
|
insert into tenant_invites (tenant_id, email, role, token, created_at, expires_at)
|
|
values (p_tenant_id, v_email, p_role, v_token, now(), now() + interval '7 days');
|
|
end if;
|
|
|
|
return v_token;
|
|
end;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.tenant_invite_member_by_email(p_tenant_id uuid, p_email text, p_role text) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: tenant_reactivate_member(uuid, uuid); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.tenant_reactivate_member(p_tenant_id uuid, p_member_user_id uuid) RETURNS void
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
SET search_path TO 'public'
|
|
SET row_security TO 'off'
|
|
AS $$
|
|
begin
|
|
if auth.uid() is null then
|
|
raise exception 'not_authenticated';
|
|
end if;
|
|
|
|
if not public.is_tenant_admin(p_tenant_id) then
|
|
raise exception 'not_allowed';
|
|
end if;
|
|
|
|
update public.tenant_members
|
|
set status = 'active'
|
|
where tenant_id = p_tenant_id
|
|
and user_id = p_member_user_id;
|
|
|
|
if not found then
|
|
raise exception 'membership_not_found';
|
|
end if;
|
|
end;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.tenant_reactivate_member(p_tenant_id uuid, p_member_user_id uuid) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: tenant_remove_member(uuid, uuid); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.tenant_remove_member(p_tenant_id uuid, p_member_user_id uuid) RETURNS void
|
|
CREATE FUNCTION public.tenant_reactivate_member(p_tenant_id uuid, p_member_user_id uuid) RETURNS void
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
SET search_path TO 'public'
|
|
SET row_security TO 'off'
|
|
AS $$
|
|
begin
|
|
if auth.uid() is null then
|
|
raise exception 'not_authenticated';
|
|
end if;
|
|
|
|
if not public.is_tenant_admin(p_tenant_id) then
|
|
raise exception 'not_allowed';
|
|
end if;
|
|
|
|
update public.tenant_members
|
|
set status = 'active'
|
|
where tenant_id = p_tenant_id
|
|
and user_id = p_member_user_id;
|
|
|
|
if not found then
|
|
raise exception 'membership_not_found';
|
|
end if;
|
|
end;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.tenant_reactivate_member(p_tenant_id uuid, p_member_user_id uuid) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: tenant_remove_member(uuid, uuid); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.tenant_remove_member(p_tenant_id uuid, p_member_user_id uuid) RETURNS void
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
SET search_path TO 'public'
|
|
SET row_security TO 'off'
|
|
AS $$
|
|
declare
|
|
v_role text;
|
|
begin
|
|
if auth.uid() is null then
|
|
raise exception 'not_authenticated';
|
|
end if;
|
|
|
|
if not public.is_tenant_admin(p_tenant_id) then
|
|
raise exception 'not_allowed';
|
|
end if;
|
|
|
|
if p_member_user_id = auth.uid() then
|
|
raise exception 'cannot_remove_self';
|
|
end if;
|
|
|
|
-- pega role atual do membro (se não existir, erro)
|
|
select role into v_role
|
|
from public.tenant_members
|
|
where tenant_id = p_tenant_id
|
|
and user_id = p_member_user_id;
|
|
|
|
if v_role is null then
|
|
raise exception 'membership_not_found';
|
|
end if;
|
|
|
|
-- trava: se for therapist, não pode remover com eventos futuros
|
|
if v_role = 'therapist' then
|
|
if exists (
|
|
select 1
|
|
from public.agenda_eventos e
|
|
where e.owner_id = p_tenant_id
|
|
and e.terapeuta_id = p_member_user_id
|
|
and e.inicio_em >= now()
|
|
and e.status::text not in ('cancelado','cancelled','canceled')
|
|
limit 1
|
|
) then
|
|
raise exception 'cannot_remove_therapist_with_future_events';
|
|
end if;
|
|
end if;
|
|
|
|
update public.tenant_members
|
|
set status = 'inactive'
|
|
where tenant_id = p_tenant_id
|
|
and user_id = p_member_user_id;
|
|
|
|
if not found then
|
|
raise exception 'membership_not_found';
|
|
end if;
|
|
end;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.tenant_remove_member(p_tenant_id uuid, p_member_user_id uuid) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: tenant_remove_member_soft(uuid, uuid); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.tenant_remove_member_soft(p_tenant_id uuid, p_member_user_id uuid) RETURNS void
|
|
CREATE FUNCTION public.tenant_remove_member(p_tenant_id uuid, p_member_user_id uuid) RETURNS void
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
SET search_path TO 'public'
|
|
SET row_security TO 'off'
|
|
AS $$
|
|
declare
|
|
v_role text;
|
|
begin
|
|
if auth.uid() is null then
|
|
raise exception 'not_authenticated';
|
|
end if;
|
|
|
|
if not public.is_tenant_admin(p_tenant_id) then
|
|
raise exception 'not_allowed';
|
|
end if;
|
|
|
|
if p_member_user_id = auth.uid() then
|
|
raise exception 'cannot_remove_self';
|
|
end if;
|
|
|
|
-- pega role atual do membro (se não existir, erro)
|
|
select role into v_role
|
|
from public.tenant_members
|
|
where tenant_id = p_tenant_id
|
|
and user_id = p_member_user_id;
|
|
|
|
if v_role is null then
|
|
raise exception 'membership_not_found';
|
|
end if;
|
|
|
|
-- trava: se for therapist, não pode remover com eventos futuros
|
|
if v_role = 'therapist' then
|
|
if exists (
|
|
select 1
|
|
from public.agenda_eventos e
|
|
where e.owner_id = p_tenant_id
|
|
and e.terapeuta_id = p_member_user_id
|
|
and e.inicio_em >= now()
|
|
and e.status::text not in ('cancelado','cancelled','canceled')
|
|
limit 1
|
|
) then
|
|
raise exception 'cannot_remove_therapist_with_future_events';
|
|
end if;
|
|
end if;
|
|
|
|
update public.tenant_members
|
|
set status = 'inactive'
|
|
where tenant_id = p_tenant_id
|
|
and user_id = p_member_user_id;
|
|
|
|
if not found then
|
|
raise exception 'membership_not_found';
|
|
end if;
|
|
end;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.tenant_remove_member(p_tenant_id uuid, p_member_user_id uuid) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: tenant_remove_member_soft(uuid, uuid); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.tenant_remove_member_soft(p_tenant_id uuid, p_member_user_id uuid) RETURNS void
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
SET search_path TO 'public'
|
|
SET row_security TO 'off'
|
|
AS $$
|
|
begin
|
|
if auth.uid() is null then
|
|
raise exception 'not_authenticated';
|
|
end if;
|
|
|
|
if not public.is_tenant_admin(p_tenant_id) then
|
|
raise exception 'not_allowed';
|
|
end if;
|
|
|
|
if p_member_user_id = auth.uid() then
|
|
raise exception 'cannot_remove_self';
|
|
end if;
|
|
|
|
update public.tenant_members
|
|
set status = 'inactive'
|
|
where tenant_id = p_tenant_id
|
|
and user_id = p_member_user_id;
|
|
|
|
if not found then
|
|
raise exception 'membership_not_found';
|
|
end if;
|
|
end;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.tenant_remove_member_soft(p_tenant_id uuid, p_member_user_id uuid) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: tenant_revoke_invite(uuid, text, text); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.tenant_revoke_invite(p_tenant_id uuid, p_email text, p_role text) RETURNS void
|
|
CREATE FUNCTION public.tenant_remove_member_soft(p_tenant_id uuid, p_member_user_id uuid) RETURNS void
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
SET search_path TO 'public'
|
|
SET row_security TO 'off'
|
|
AS $$
|
|
begin
|
|
if auth.uid() is null then
|
|
raise exception 'not_authenticated';
|
|
end if;
|
|
|
|
if not public.is_tenant_admin(p_tenant_id) then
|
|
raise exception 'not_allowed';
|
|
end if;
|
|
|
|
if p_member_user_id = auth.uid() then
|
|
raise exception 'cannot_remove_self';
|
|
end if;
|
|
|
|
update public.tenant_members
|
|
set status = 'inactive'
|
|
where tenant_id = p_tenant_id
|
|
and user_id = p_member_user_id;
|
|
|
|
if not found then
|
|
raise exception 'membership_not_found';
|
|
end if;
|
|
end;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.tenant_remove_member_soft(p_tenant_id uuid, p_member_user_id uuid) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: tenant_revoke_invite(uuid, text, text); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.tenant_revoke_invite(p_tenant_id uuid, p_email text, p_role text) RETURNS void
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
SET search_path TO 'public'
|
|
SET row_security TO 'off'
|
|
AS $$
|
|
declare
|
|
v_email text;
|
|
begin
|
|
if auth.uid() is null then
|
|
raise exception 'not_authenticated';
|
|
end if;
|
|
|
|
if not public.is_tenant_admin(p_tenant_id) then
|
|
raise exception 'not_allowed';
|
|
end if;
|
|
|
|
v_email := lower(trim(p_email));
|
|
|
|
update public.tenant_invites
|
|
set revoked_at = now(),
|
|
revoked_by = auth.uid()
|
|
where tenant_id = p_tenant_id
|
|
and lower(email) = v_email
|
|
and role = p_role
|
|
and accepted_at is null
|
|
and revoked_at is null;
|
|
|
|
if not found then
|
|
raise exception 'invite_not_found';
|
|
end if;
|
|
end;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.tenant_revoke_invite(p_tenant_id uuid, p_email text, p_role text) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: tenant_set_member_status(uuid, uuid, text); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.tenant_set_member_status(p_tenant_id uuid, p_member_user_id uuid, p_new_status text) RETURNS void
|
|
CREATE FUNCTION public.tenant_revoke_invite(p_tenant_id uuid, p_email text, p_role text) RETURNS void
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
SET search_path TO 'public'
|
|
SET row_security TO 'off'
|
|
AS $$
|
|
declare
|
|
v_email text;
|
|
begin
|
|
if auth.uid() is null then
|
|
raise exception 'not_authenticated';
|
|
end if;
|
|
|
|
if not public.is_tenant_admin(p_tenant_id) then
|
|
raise exception 'not_allowed';
|
|
end if;
|
|
|
|
v_email := lower(trim(p_email));
|
|
|
|
update public.tenant_invites
|
|
set revoked_at = now(),
|
|
revoked_by = auth.uid()
|
|
where tenant_id = p_tenant_id
|
|
and lower(email) = v_email
|
|
and role = p_role
|
|
and accepted_at is null
|
|
and revoked_at is null;
|
|
|
|
if not found then
|
|
raise exception 'invite_not_found';
|
|
end if;
|
|
end;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.tenant_revoke_invite(p_tenant_id uuid, p_email text, p_role text) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: tenant_set_member_status(uuid, uuid, text); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.tenant_set_member_status(p_tenant_id uuid, p_member_user_id uuid, p_new_status text) RETURNS void
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
SET search_path TO 'public'
|
|
SET row_security TO 'off'
|
|
AS $$
|
|
begin
|
|
if auth.uid() is null then
|
|
raise exception 'not_authenticated';
|
|
end if;
|
|
|
|
-- valida status (adapte aos seus valores reais)
|
|
if p_new_status not in ('active','inactive','suspended','invited') then
|
|
raise exception 'invalid_status: %', p_new_status;
|
|
end if;
|
|
|
|
if not public.is_tenant_admin(p_tenant_id) then
|
|
raise exception 'not_allowed';
|
|
end if;
|
|
|
|
-- evita desativar a si mesmo (opcional)
|
|
if p_member_user_id = auth.uid() and p_new_status <> 'active' then
|
|
raise exception 'cannot_disable_self';
|
|
end if;
|
|
|
|
update public.tenant_members
|
|
set status = p_new_status
|
|
where tenant_id = p_tenant_id
|
|
and user_id = p_member_user_id;
|
|
|
|
if not found then
|
|
raise exception 'membership_not_found';
|
|
end if;
|
|
end;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.tenant_set_member_status(p_tenant_id uuid, p_member_user_id uuid, p_new_status text) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: tenant_update_member_role(uuid, uuid, text); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.tenant_update_member_role(p_tenant_id uuid, p_member_user_id uuid, p_new_role text) RETURNS void
|
|
CREATE FUNCTION public.tenant_set_member_status(p_tenant_id uuid, p_member_user_id uuid, p_new_status text) RETURNS void
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
SET search_path TO 'public'
|
|
SET row_security TO 'off'
|
|
AS $$
|
|
begin
|
|
if auth.uid() is null then
|
|
raise exception 'not_authenticated';
|
|
end if;
|
|
|
|
-- valida status (adapte aos seus valores reais)
|
|
if p_new_status not in ('active','inactive','suspended','invited') then
|
|
raise exception 'invalid_status: %', p_new_status;
|
|
end if;
|
|
|
|
if not public.is_tenant_admin(p_tenant_id) then
|
|
raise exception 'not_allowed';
|
|
end if;
|
|
|
|
-- evita desativar a si mesmo (opcional)
|
|
if p_member_user_id = auth.uid() and p_new_status <> 'active' then
|
|
raise exception 'cannot_disable_self';
|
|
end if;
|
|
|
|
update public.tenant_members
|
|
set status = p_new_status
|
|
where tenant_id = p_tenant_id
|
|
and user_id = p_member_user_id;
|
|
|
|
if not found then
|
|
raise exception 'membership_not_found';
|
|
end if;
|
|
end;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.tenant_set_member_status(p_tenant_id uuid, p_member_user_id uuid, p_new_status text) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: tenant_update_member_role(uuid, uuid, text); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.tenant_update_member_role(p_tenant_id uuid, p_member_user_id uuid, p_new_role text) RETURNS void
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
SET search_path TO 'public'
|
|
SET row_security TO 'off'
|
|
AS $$
|
|
begin
|
|
-- exige auth
|
|
if auth.uid() is null then
|
|
raise exception 'not_authenticated';
|
|
end if;
|
|
|
|
-- valida role
|
|
if p_new_role not in ('tenant_admin','therapist','secretary','patient') then
|
|
raise exception 'invalid_role: %', p_new_role;
|
|
end if;
|
|
|
|
-- somente tenant_admin ativo pode alterar role
|
|
if not public.is_tenant_admin(p_tenant_id) then
|
|
raise exception 'not_allowed';
|
|
end if;
|
|
|
|
-- evita o admin remover o próprio admin sem querer (opcional mas recomendado)
|
|
if p_member_user_id = auth.uid() and p_new_role <> 'tenant_admin' then
|
|
raise exception 'cannot_demote_self';
|
|
end if;
|
|
|
|
update public.tenant_members
|
|
set role = p_new_role
|
|
where tenant_id = p_tenant_id
|
|
and user_id = p_member_user_id;
|
|
|
|
if not found then
|
|
raise exception 'membership_not_found';
|
|
end if;
|
|
end;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.tenant_update_member_role(p_tenant_id uuid, p_member_user_id uuid, p_new_role text) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: toggle_plan(uuid); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.toggle_plan(owner uuid) RETURNS void
|
|
CREATE FUNCTION public.tenant_update_member_role(p_tenant_id uuid, p_member_user_id uuid, p_new_role text) RETURNS void
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
SET search_path TO 'public'
|
|
SET row_security TO 'off'
|
|
AS $$
|
|
begin
|
|
-- exige auth
|
|
if auth.uid() is null then
|
|
raise exception 'not_authenticated';
|
|
end if;
|
|
|
|
-- valida role
|
|
if p_new_role not in ('tenant_admin','therapist','secretary','patient') then
|
|
raise exception 'invalid_role: %', p_new_role;
|
|
end if;
|
|
|
|
-- somente tenant_admin ativo pode alterar role
|
|
if not public.is_tenant_admin(p_tenant_id) then
|
|
raise exception 'not_allowed';
|
|
end if;
|
|
|
|
-- evita o admin remover o próprio admin sem querer (opcional mas recomendado)
|
|
if p_member_user_id = auth.uid() and p_new_role <> 'tenant_admin' then
|
|
raise exception 'cannot_demote_self';
|
|
end if;
|
|
|
|
update public.tenant_members
|
|
set role = p_new_role
|
|
where tenant_id = p_tenant_id
|
|
and user_id = p_member_user_id;
|
|
|
|
if not found then
|
|
raise exception 'membership_not_found';
|
|
end if;
|
|
end;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.tenant_update_member_role(p_tenant_id uuid, p_member_user_id uuid, p_new_role text) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: toggle_plan(uuid); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.toggle_plan(owner uuid) RETURNS void
|
|
CREATE FUNCTION public.user_has_feature(_user_id uuid, _feature text) RETURNS boolean
|
|
LANGUAGE sql STABLE
|
|
AS $$
|
|
select exists (
|
|
select 1
|
|
from public.v_user_entitlements e
|
|
where e.user_id = _user_id
|
|
and e.feature_key = _feature
|
|
and e.allowed = true
|
|
);
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.user_has_feature(_user_id uuid, _feature text) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: validate_support_session(text); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
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)
|
|
LANGUAGE sql STABLE
|
|
AS $$
|
|
select auth.uid() as uid, auth.role() as role;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.whoami() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: apply_rls(jsonb, integer); Type: FUNCTION; Schema: realtime; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION realtime.apply_rls(wal jsonb, max_record_bytes integer DEFAULT (1024 * 1024)) RETURNS SETOF realtime.wal_rls
|
|
CREATE FUNCTION public.current_member_id(p_tenant_id uuid) RETURNS uuid
|
|
LANGUAGE sql STABLE
|
|
AS $$
|
|
select tm.id
|
|
from public.tenant_members tm
|
|
where tm.tenant_id = p_tenant_id
|
|
and tm.user_id = auth.uid()
|
|
limit 1
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.current_member_id(p_tenant_id uuid) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: current_member_role(uuid); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.current_member_role(p_tenant_id uuid) RETURNS text
|
|
LANGUAGE sql STABLE
|
|
AS $$
|
|
select tm.role
|
|
from public.tenant_members tm
|
|
where tm.tenant_id = p_tenant_id
|
|
and tm.user_id = auth.uid()
|
|
limit 1
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.current_member_role(p_tenant_id uuid) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: debit_addon_credit(uuid, text, uuid, text); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.debit_addon_credit(p_tenant_id uuid, p_addon_type text, p_queue_id uuid DEFAULT NULL::uuid, p_description text DEFAULT 'Consumo'::text) RETURNS jsonb
|
|
CREATE FUNCTION public.dev_list_auth_users(p_limit integer DEFAULT 50) RETURNS TABLE(id uuid, email text, created_at timestamp with time zone)
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
SET search_path TO 'public', 'auth'
|
|
AS $$
|
|
begin
|
|
-- só saas_admin pode ver
|
|
if not exists (
|
|
select 1
|
|
from public.profiles p
|
|
where p.id = auth.uid()
|
|
and p.role = 'saas_admin'
|
|
) then
|
|
return;
|
|
end if;
|
|
|
|
return query
|
|
select
|
|
u.id,
|
|
u.email,
|
|
u.created_at
|
|
from auth.users u
|
|
order by u.created_at desc
|
|
limit greatest(1, least(coalesce(p_limit, 50), 500));
|
|
end;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.dev_list_auth_users(p_limit integer) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: dev_list_custom_users(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.dev_list_custom_users() RETURNS TABLE(user_id uuid, email text, created_at timestamp with time zone, global_role text, tenant_role text, tenant_id uuid, password_dev text, kind text)
|
|
LANGUAGE sql SECURITY DEFINER
|
|
SET search_path TO 'public'
|
|
AS $$
|
|
with base as (
|
|
select
|
|
u.id as user_id,
|
|
lower(u.email) as email,
|
|
u.created_at
|
|
from auth.users u
|
|
where lower(u.email) not in (
|
|
'clinic@agenciapsi.com.br',
|
|
'therapist@agenciapsi.com.br',
|
|
'patient@agenciapsi.com.br',
|
|
'saas@agenciapsi.com.br'
|
|
)
|
|
),
|
|
prof as (
|
|
select p.id, p.role as global_role
|
|
from public.profiles p
|
|
),
|
|
last_membership as (
|
|
select distinct on (tm.user_id)
|
|
tm.user_id,
|
|
tm.tenant_id,
|
|
tm.role as tenant_role,
|
|
tm.created_at
|
|
from public.tenant_members tm
|
|
where tm.status = 'active'
|
|
order by tm.user_id, tm.created_at desc
|
|
)
|
|
select
|
|
b.user_id,
|
|
b.email,
|
|
b.created_at,
|
|
pr.global_role,
|
|
lm.tenant_role,
|
|
lm.tenant_id,
|
|
dc.password_dev,
|
|
dc.kind
|
|
from base b
|
|
left join prof pr on pr.id = b.user_id
|
|
left join last_membership lm on lm.user_id = b.user_id
|
|
left join public.dev_user_credentials dc on lower(dc.email) = b.email
|
|
order by b.created_at desc;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.dev_list_custom_users() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: dev_list_intent_leads(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.dev_list_intent_leads() RETURNS TABLE(email text, last_intent_at timestamp with time zone, plan_key text, billing_interval text, status text, tenant_id uuid)
|
|
LANGUAGE sql SECURITY DEFINER
|
|
SET search_path TO 'public'
|
|
AS $$
|
|
select
|
|
lower(si.email) as email,
|
|
max(si.created_at) as last_intent_at,
|
|
(array_agg(si.plan_key order by si.created_at desc))[1] as plan_key,
|
|
(array_agg(si.interval order by si.created_at desc))[1] as billing_interval,
|
|
(array_agg(si.status order by si.created_at desc))[1] as status,
|
|
(array_agg(si.tenant_id order by si.created_at desc))[1] as tenant_id
|
|
from public.subscription_intents si
|
|
where si.email is not null
|
|
and not exists (
|
|
select 1
|
|
from auth.users au
|
|
where lower(au.email) = lower(si.email)
|
|
)
|
|
group by lower(si.email)
|
|
order by max(si.created_at) desc;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.dev_list_intent_leads() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: dev_public_debug_snapshot(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.dev_public_debug_snapshot() RETURNS TABLE(users_total integer, tenants_total integer, intents_new_total integer, latest_intents jsonb)
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
SET search_path TO 'public'
|
|
AS $_$
|
|
declare
|
|
v_latest jsonb;
|
|
begin
|
|
select jsonb_agg(
|
|
jsonb_build_object(
|
|
'created_at', si.created_at,
|
|
'email_masked',
|
|
regexp_replace(lower(si.email), '(^.).*(@.*$)', '\1***\2'),
|
|
'plan_key', si.plan_key,
|
|
'status', si.status
|
|
)
|
|
order by si.created_at desc
|
|
)
|
|
into v_latest
|
|
from (
|
|
select si.*
|
|
from public.subscription_intents si
|
|
where si.email is not null
|
|
order by si.created_at desc
|
|
limit 5
|
|
) si;
|
|
|
|
return query
|
|
select
|
|
(select count(*)::int from auth.users) as users_total,
|
|
(select count(*)::int from public.tenants) as tenants_total,
|
|
(select count(*)::int from public.subscription_intents where status = 'new') as intents_new_total,
|
|
coalesce(v_latest, '[]'::jsonb) as latest_intents;
|
|
end;
|
|
$_$;
|
|
|
|
|
|
ALTER FUNCTION public.dev_public_debug_snapshot() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: ensure_personal_tenant(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.ensure_personal_tenant() RETURNS uuid
|