1284 lines
37 KiB
PL/PgSQL
1284 lines
37 KiB
PL/PgSQL
-- =============================================================================
|
|
-- AgenciaPsi — Functions — Plans, Subscriptions, Billing
|
|
-- =============================================================================
|
|
-- activate_subscription_from_intent, cancel_subscription,
|
|
-- change_subscription_plan, toggle_plan, transition_subscription,
|
|
-- reactivate_subscription, rebuild_owner_entitlements,
|
|
-- fix_all_subscription_mismatches, subscription_intents_view_insert,
|
|
-- subscriptions_validate_scope, admin_fix_plan_target,
|
|
-- set_tenant_feature_exception, guard_no_change_core_plan_key, etc.
|
|
-- =============================================================================
|
|
|
|
CREATE FUNCTION public.activate_subscription_from_intent(p_intent_id uuid) RETURNS public.subscriptions
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
AS $$
|
|
declare
|
|
v_intent record;
|
|
v_sub public.subscriptions;
|
|
v_days int;
|
|
v_user_id uuid;
|
|
v_plan_id uuid;
|
|
v_target text;
|
|
begin
|
|
-- lê pela VIEW unificada
|
|
select * into v_intent
|
|
from public.subscription_intents
|
|
where id = p_intent_id;
|
|
|
|
if not found then
|
|
raise exception 'Intent não encontrado: %', p_intent_id;
|
|
end if;
|
|
|
|
if v_intent.status <> 'paid' then
|
|
raise exception 'Intent precisa estar paid para ativar assinatura';
|
|
end if;
|
|
|
|
-- resolve target e plan_id via plans.key
|
|
select p.id, p.target
|
|
into v_plan_id, v_target
|
|
from public.plans p
|
|
where p.key = v_intent.plan_key
|
|
limit 1;
|
|
|
|
if v_plan_id is null then
|
|
raise exception 'Plano não encontrado em plans.key = %', v_intent.plan_key;
|
|
end if;
|
|
|
|
v_target := lower(coalesce(v_target, ''));
|
|
|
|
-- ✅ supervisor adicionado
|
|
if v_target not in ('clinic', 'therapist', 'supervisor') then
|
|
raise exception 'Target inválido em plans.target: %', v_target;
|
|
end if;
|
|
|
|
-- regra por target
|
|
if v_target = 'clinic' then
|
|
if v_intent.tenant_id is null then
|
|
raise exception 'Intent sem tenant_id';
|
|
end if;
|
|
else
|
|
-- therapist ou supervisor: vinculado ao user
|
|
v_user_id := v_intent.user_id;
|
|
if v_user_id is null then
|
|
v_user_id := v_intent.created_by_user_id;
|
|
end if;
|
|
end if;
|
|
|
|
if v_target in ('therapist', 'supervisor') and v_user_id is null then
|
|
raise exception 'Não foi possível determinar user_id para assinatura %.', v_target;
|
|
end if;
|
|
|
|
-- cancela assinatura ativa anterior
|
|
if v_target = 'clinic' then
|
|
update public.subscriptions
|
|
set status = 'cancelled',
|
|
cancelled_at = now()
|
|
where tenant_id = v_intent.tenant_id
|
|
and plan_id = v_plan_id
|
|
and status = 'active';
|
|
else
|
|
-- therapist ou supervisor
|
|
update public.subscriptions
|
|
set status = 'cancelled',
|
|
cancelled_at = now()
|
|
where user_id = v_user_id
|
|
and plan_id = v_plan_id
|
|
and status = 'active'
|
|
and tenant_id is null;
|
|
end if;
|
|
|
|
-- duração do plano (30 dias para mensal)
|
|
v_days := case
|
|
when lower(coalesce(v_intent.interval, 'month')) = 'year' then 365
|
|
else 30
|
|
end;
|
|
|
|
-- cria nova assinatura
|
|
insert into public.subscriptions (
|
|
user_id,
|
|
plan_id,
|
|
status,
|
|
started_at,
|
|
expires_at,
|
|
cancelled_at,
|
|
activated_at,
|
|
tenant_id,
|
|
plan_key,
|
|
interval,
|
|
source,
|
|
created_at,
|
|
updated_at
|
|
)
|
|
values (
|
|
case when v_target = 'clinic' then null else v_user_id end,
|
|
v_plan_id,
|
|
'active',
|
|
now(),
|
|
now() + make_interval(days => v_days),
|
|
null,
|
|
now(),
|
|
case when v_target = 'clinic' then v_intent.tenant_id else null end,
|
|
v_intent.plan_key,
|
|
v_intent.interval,
|
|
'manual',
|
|
now(),
|
|
now()
|
|
)
|
|
returning * into v_sub;
|
|
|
|
-- grava vínculo intent → subscription
|
|
if v_target = 'clinic' then
|
|
update public.subscription_intents_tenant
|
|
set subscription_id = v_sub.id
|
|
where id = p_intent_id;
|
|
else
|
|
update public.subscription_intents_personal
|
|
set subscription_id = v_sub.id
|
|
where id = p_intent_id;
|
|
end if;
|
|
|
|
return v_sub;
|
|
end;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.activate_subscription_from_intent(p_intent_id uuid) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: admin_credit_addon(uuid, text, integer, uuid, text, text, integer); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.admin_credit_addon(p_tenant_id uuid, p_addon_type text, p_amount integer, p_product_id uuid DEFAULT NULL::uuid, p_description text DEFAULT 'Crédito manual'::text, p_payment_method text DEFAULT 'manual'::text, p_price_cents integer DEFAULT 0) RETURNS jsonb
|
|
CREATE FUNCTION public.admin_credit_addon(p_tenant_id uuid, p_addon_type text, p_amount integer, p_product_id uuid DEFAULT NULL::uuid, p_description text DEFAULT 'Crédito manual'::text, p_payment_method text DEFAULT 'manual'::text, p_price_cents integer DEFAULT 0) RETURNS jsonb
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
SET search_path TO 'public'
|
|
AS $$
|
|
DECLARE
|
|
v_credit addon_credits%ROWTYPE;
|
|
v_balance_before INTEGER;
|
|
v_balance_after INTEGER;
|
|
v_tx_id UUID;
|
|
BEGIN
|
|
-- Upsert addon_credits
|
|
INSERT INTO addon_credits (tenant_id, addon_type, balance, total_purchased)
|
|
VALUES (p_tenant_id, p_addon_type, 0, 0)
|
|
ON CONFLICT (tenant_id, addon_type) DO NOTHING;
|
|
|
|
-- Lock e leitura
|
|
SELECT * INTO v_credit
|
|
FROM addon_credits
|
|
WHERE tenant_id = p_tenant_id AND addon_type = p_addon_type
|
|
FOR UPDATE;
|
|
|
|
v_balance_before := v_credit.balance;
|
|
v_balance_after := v_credit.balance + p_amount;
|
|
|
|
-- Atualiza saldo
|
|
UPDATE addon_credits
|
|
SET balance = v_balance_after,
|
|
total_purchased = total_purchased + p_amount,
|
|
low_balance_notified = CASE WHEN v_balance_after > COALESCE(low_balance_threshold, 10) THEN false ELSE low_balance_notified END,
|
|
updated_at = now()
|
|
WHERE id = v_credit.id;
|
|
|
|
-- Registra transação
|
|
INSERT INTO addon_transactions (
|
|
tenant_id, addon_type, type, amount,
|
|
balance_before, balance_after,
|
|
product_id, description,
|
|
admin_user_id, payment_method, price_cents
|
|
) VALUES (
|
|
p_tenant_id, p_addon_type, 'purchase', p_amount,
|
|
v_balance_before, v_balance_after,
|
|
p_product_id, p_description,
|
|
auth.uid(), p_payment_method, p_price_cents
|
|
)
|
|
RETURNING id INTO v_tx_id;
|
|
|
|
RETURN jsonb_build_object(
|
|
'success', true,
|
|
'transaction_id', v_tx_id,
|
|
'balance_before', v_balance_before,
|
|
'balance_after', v_balance_after
|
|
);
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.admin_credit_addon(p_tenant_id uuid, p_addon_type text, p_amount integer, p_product_id uuid, p_description text, p_payment_method text, p_price_cents integer) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: FUNCTION admin_credit_addon(p_tenant_id uuid, p_addon_type text, p_amount integer, p_product_id uuid, p_description text, p_payment_method text, p_price_cents integer); Type: COMMENT; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
COMMENT ON FUNCTION public.admin_credit_addon(p_tenant_id uuid, p_addon_type text, p_amount integer, p_product_id uuid, p_description text, p_payment_method text, p_price_cents integer) IS 'Admin adiciona créditos de add-on a um tenant. Cria registro se não existir.';
|
|
|
|
|
|
--
|
|
-- Name: admin_delete_email_template_global(uuid); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.admin_delete_email_template_global(p_id uuid) RETURNS boolean
|
|
CREATE FUNCTION public.admin_delete_email_template_global(p_id uuid) RETURNS boolean
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
SET search_path TO 'public'
|
|
AS $$
|
|
BEGIN
|
|
DELETE FROM public.email_templates_global WHERE id = p_id;
|
|
IF NOT FOUND THEN
|
|
RAISE EXCEPTION 'Template com id % não encontrado', p_id;
|
|
END IF;
|
|
RETURN true;
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.admin_delete_email_template_global(p_id uuid) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: admin_fix_plan_target(text, text); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.admin_fix_plan_target(p_plan_key text, p_new_target text) RETURNS void
|
|
CREATE FUNCTION public.admin_fix_plan_target(p_plan_key text, p_new_target text) RETURNS void
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
AS $$
|
|
declare
|
|
v_plan_id uuid;
|
|
begin
|
|
-- (opcional) restringe targets válidos
|
|
if p_new_target not in ('clinic','therapist') then
|
|
raise exception 'Target inválido: %', p_new_target using errcode='P0001';
|
|
end if;
|
|
|
|
-- trava o plano
|
|
select id into v_plan_id
|
|
from public.plans
|
|
where key = p_plan_key
|
|
for update;
|
|
|
|
if v_plan_id is null then
|
|
raise exception 'Plano não encontrado: %', p_plan_key using errcode='P0001';
|
|
end if;
|
|
|
|
-- segurança: não mexer se existe subscription
|
|
if exists (select 1 from public.subscriptions s where s.plan_id = v_plan_id) then
|
|
raise exception 'Plano % possui subscriptions. Migração bloqueada.', p_plan_key using errcode='P0001';
|
|
end if;
|
|
|
|
-- liga bypass SOMENTE nesta transação
|
|
perform set_config('app.plan_migration_bypass', '1', true);
|
|
|
|
update public.plans
|
|
set target = p_new_target
|
|
where id = v_plan_id;
|
|
|
|
end
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.admin_fix_plan_target(p_plan_key text, p_new_target text) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: admin_upsert_email_template_global(uuid, text, text, text, text, text, text, boolean, jsonb); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.admin_upsert_email_template_global(p_id uuid DEFAULT NULL::uuid, p_key text DEFAULT NULL::text, p_domain text DEFAULT NULL::text, p_channel text DEFAULT 'email'::text, p_subject text DEFAULT NULL::text, p_body_html text DEFAULT NULL::text, p_body_text text DEFAULT NULL::text, p_is_active boolean DEFAULT true, p_variables jsonb DEFAULT '{}'::jsonb) RETURNS jsonb
|
|
CREATE FUNCTION public.admin_upsert_email_template_global(p_id uuid DEFAULT NULL::uuid, p_key text DEFAULT NULL::text, p_domain text DEFAULT NULL::text, p_channel text DEFAULT 'email'::text, p_subject text DEFAULT NULL::text, p_body_html text DEFAULT NULL::text, p_body_text text DEFAULT NULL::text, p_is_active boolean DEFAULT true, p_variables jsonb DEFAULT '{}'::jsonb) RETURNS jsonb
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
SET search_path TO 'public'
|
|
AS $$
|
|
DECLARE
|
|
v_result jsonb;
|
|
v_id uuid;
|
|
BEGIN
|
|
-- UPDATE existente
|
|
IF p_id IS NOT NULL THEN
|
|
UPDATE public.email_templates_global
|
|
SET
|
|
subject = COALESCE(p_subject, subject),
|
|
body_html = COALESCE(p_body_html, body_html),
|
|
body_text = p_body_text,
|
|
is_active = p_is_active,
|
|
variables = COALESCE(p_variables, variables),
|
|
version = version + 1
|
|
WHERE id = p_id
|
|
RETURNING to_jsonb(email_templates_global.*) INTO v_result;
|
|
|
|
IF v_result IS NULL THEN
|
|
RAISE EXCEPTION 'Template com id % não encontrado', p_id;
|
|
END IF;
|
|
|
|
RETURN v_result;
|
|
END IF;
|
|
|
|
-- INSERT novo
|
|
IF p_key IS NULL OR p_domain IS NULL OR p_subject IS NULL OR p_body_html IS NULL THEN
|
|
RAISE EXCEPTION 'key, domain, subject e body_html são obrigatórios para novo template';
|
|
END IF;
|
|
|
|
INSERT INTO public.email_templates_global (key, domain, channel, subject, body_html, body_text, is_active, variables)
|
|
VALUES (p_key, p_domain, p_channel, p_subject, p_body_html, p_body_text, p_is_active, p_variables)
|
|
RETURNING to_jsonb(email_templates_global.*) INTO v_result;
|
|
|
|
RETURN v_result;
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.admin_upsert_email_template_global(p_id uuid, p_key text, p_domain text, p_channel text, p_subject text, p_body_html text, p_body_text text, p_is_active boolean, p_variables jsonb) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: agenda_cfg_sync(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.agenda_cfg_sync() RETURNS trigger
|
|
CREATE FUNCTION public.cancel_subscription(p_subscription_id uuid) RETURNS public.subscriptions
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
AS $$
|
|
declare
|
|
v_sub public.subscriptions;
|
|
v_owner_type text;
|
|
v_owner_ref uuid;
|
|
begin
|
|
|
|
select *
|
|
into v_sub
|
|
from public.subscriptions
|
|
where id = p_subscription_id
|
|
for update;
|
|
|
|
if not found then
|
|
raise exception 'Subscription não encontrada';
|
|
end if;
|
|
|
|
if v_sub.status = 'canceled' then
|
|
return v_sub;
|
|
end if;
|
|
|
|
if v_sub.tenant_id is not null then
|
|
v_owner_type := 'clinic';
|
|
v_owner_ref := v_sub.tenant_id;
|
|
elsif v_sub.user_id is not null then
|
|
v_owner_type := 'therapist';
|
|
v_owner_ref := v_sub.user_id;
|
|
else
|
|
v_owner_type := null;
|
|
v_owner_ref := null;
|
|
end if;
|
|
|
|
update public.subscriptions
|
|
set status = 'canceled',
|
|
cancel_at_period_end = false,
|
|
updated_at = now()
|
|
where id = p_subscription_id
|
|
returning * into v_sub;
|
|
|
|
insert into public.subscription_events(
|
|
subscription_id,
|
|
owner_id,
|
|
owner_type,
|
|
owner_ref,
|
|
event_type,
|
|
old_plan_id,
|
|
new_plan_id,
|
|
created_by,
|
|
reason,
|
|
source,
|
|
metadata
|
|
)
|
|
values (
|
|
v_sub.id,
|
|
v_owner_ref,
|
|
v_owner_type,
|
|
v_owner_ref,
|
|
'canceled',
|
|
v_sub.plan_id,
|
|
v_sub.plan_id,
|
|
auth.uid(),
|
|
'Cancelamento manual via admin',
|
|
'admin_panel',
|
|
jsonb_build_object('previous_status', 'active')
|
|
);
|
|
|
|
if v_owner_ref is not null then
|
|
insert into public.entitlements_invalidation(owner_id, changed_at)
|
|
values (v_owner_ref, now())
|
|
on conflict (owner_id)
|
|
do update set changed_at = excluded.changed_at;
|
|
end if;
|
|
|
|
return v_sub;
|
|
|
|
end;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.cancel_subscription(p_subscription_id uuid) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: cancelar_eventos_serie(uuid, timestamp with time zone); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.cancelar_eventos_serie(p_serie_id uuid, p_a_partir_de timestamp with time zone DEFAULT now()) RETURNS integer
|
|
CREATE FUNCTION public.change_subscription_plan(p_subscription_id uuid, p_new_plan_id uuid) RETURNS public.subscriptions
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
AS $$
|
|
declare
|
|
v_sub public.subscriptions;
|
|
v_old_plan uuid;
|
|
v_new_key text;
|
|
|
|
v_owner_type text;
|
|
v_owner_ref uuid;
|
|
|
|
v_new_target text;
|
|
v_sub_target text;
|
|
begin
|
|
select *
|
|
into v_sub
|
|
from public.subscriptions
|
|
where id = p_subscription_id
|
|
for update;
|
|
|
|
if not found then
|
|
raise exception 'Subscription não encontrada';
|
|
end if;
|
|
|
|
v_old_plan := v_sub.plan_id;
|
|
|
|
if v_old_plan = p_new_plan_id then
|
|
return v_sub;
|
|
end if;
|
|
|
|
select key, target
|
|
into v_new_key, v_new_target
|
|
from public.plans
|
|
where id = p_new_plan_id;
|
|
|
|
if v_new_key is null then
|
|
raise exception 'Plano não encontrado';
|
|
end if;
|
|
|
|
v_new_target := lower(coalesce(v_new_target, ''));
|
|
|
|
v_sub_target := case
|
|
when v_sub.tenant_id is not null then 'clinic'
|
|
else 'therapist'
|
|
end;
|
|
|
|
if v_new_target <> v_sub_target then
|
|
raise exception 'Plano inválido para este tipo de assinatura. Assinatura é % e o plano é %.',
|
|
v_sub_target, v_new_target
|
|
using errcode = 'P0001';
|
|
end if;
|
|
|
|
if v_sub.tenant_id is not null then
|
|
v_owner_type := 'clinic';
|
|
v_owner_ref := v_sub.tenant_id;
|
|
elsif v_sub.user_id is not null then
|
|
v_owner_type := 'therapist';
|
|
v_owner_ref := v_sub.user_id;
|
|
else
|
|
v_owner_type := null;
|
|
v_owner_ref := null;
|
|
end if;
|
|
|
|
update public.subscriptions
|
|
set plan_id = p_new_plan_id,
|
|
plan_key = v_new_key,
|
|
updated_at = now()
|
|
where id = p_subscription_id
|
|
returning * into v_sub;
|
|
|
|
insert into public.subscription_events(
|
|
subscription_id,
|
|
owner_id,
|
|
owner_type,
|
|
owner_ref,
|
|
event_type,
|
|
old_plan_id,
|
|
new_plan_id,
|
|
created_by,
|
|
reason,
|
|
source,
|
|
metadata
|
|
)
|
|
values (
|
|
v_sub.id,
|
|
v_owner_ref,
|
|
v_owner_type,
|
|
v_owner_ref,
|
|
'plan_changed',
|
|
v_old_plan,
|
|
p_new_plan_id,
|
|
auth.uid(),
|
|
'Plan change via DEV menu',
|
|
'dev_menu',
|
|
jsonb_build_object(
|
|
'previous_plan', v_old_plan,
|
|
'new_plan', p_new_plan_id,
|
|
'new_plan_key', v_new_key,
|
|
'new_plan_target', v_new_target
|
|
)
|
|
);
|
|
|
|
if v_owner_ref is not null then
|
|
insert into public.entitlements_invalidation (owner_id, changed_at)
|
|
values (v_owner_ref, now())
|
|
on conflict (owner_id)
|
|
do update set changed_at = excluded.changed_at;
|
|
end if;
|
|
|
|
return v_sub;
|
|
end;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.change_subscription_plan(p_subscription_id uuid, p_new_plan_id uuid) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: cleanup_notification_queue(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.cleanup_notification_queue() RETURNS integer
|
|
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
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
SET search_path TO 'public'
|
|
AS $$
|
|
DECLARE
|
|
v_credit addon_credits%ROWTYPE;
|
|
v_balance_before INTEGER;
|
|
v_balance_after INTEGER;
|
|
BEGIN
|
|
-- Lock e leitura
|
|
SELECT * INTO v_credit
|
|
FROM addon_credits
|
|
WHERE tenant_id = p_tenant_id AND addon_type = p_addon_type AND is_active = true
|
|
FOR UPDATE;
|
|
|
|
IF NOT FOUND THEN
|
|
RETURN jsonb_build_object('success', false, 'reason', 'no_credits', 'balance', 0);
|
|
END IF;
|
|
|
|
-- Verifica saldo
|
|
IF v_credit.balance <= 0 THEN
|
|
RETURN jsonb_build_object('success', false, 'reason', 'insufficient_balance', 'balance', 0);
|
|
END IF;
|
|
|
|
-- Verifica rate limit diário
|
|
IF v_credit.daily_limit IS NOT NULL THEN
|
|
-- Reset se passou do dia
|
|
IF v_credit.daily_reset_at IS NULL OR v_credit.daily_reset_at < date_trunc('day', now()) THEN
|
|
UPDATE addon_credits SET daily_used = 0, daily_reset_at = date_trunc('day', now()) + interval '1 day' WHERE id = v_credit.id;
|
|
v_credit.daily_used := 0;
|
|
END IF;
|
|
|
|
IF v_credit.daily_used >= v_credit.daily_limit THEN
|
|
RETURN jsonb_build_object('success', false, 'reason', 'daily_limit_reached', 'balance', v_credit.balance);
|
|
END IF;
|
|
END IF;
|
|
|
|
-- Verifica rate limit horário
|
|
IF v_credit.hourly_limit IS NOT NULL THEN
|
|
IF v_credit.hourly_reset_at IS NULL OR v_credit.hourly_reset_at < date_trunc('hour', now()) THEN
|
|
UPDATE addon_credits SET hourly_used = 0, hourly_reset_at = date_trunc('hour', now()) + interval '1 hour' WHERE id = v_credit.id;
|
|
v_credit.hourly_used := 0;
|
|
END IF;
|
|
|
|
IF v_credit.hourly_used >= v_credit.hourly_limit THEN
|
|
RETURN jsonb_build_object('success', false, 'reason', 'hourly_limit_reached', 'balance', v_credit.balance);
|
|
END IF;
|
|
END IF;
|
|
|
|
-- Verifica expiração
|
|
IF v_credit.expires_at IS NOT NULL AND v_credit.expires_at < now() THEN
|
|
RETURN jsonb_build_object('success', false, 'reason', 'credits_expired', 'balance', v_credit.balance);
|
|
END IF;
|
|
|
|
v_balance_before := v_credit.balance;
|
|
v_balance_after := v_credit.balance - 1;
|
|
|
|
-- Debita
|
|
UPDATE addon_credits
|
|
SET balance = v_balance_after,
|
|
total_consumed = total_consumed + 1,
|
|
daily_used = COALESCE(daily_used, 0) + 1,
|
|
hourly_used = COALESCE(hourly_used, 0) + 1,
|
|
updated_at = now()
|
|
WHERE id = v_credit.id;
|
|
|
|
-- Registra transação
|
|
INSERT INTO addon_transactions (
|
|
tenant_id, addon_type, type, amount,
|
|
balance_before, balance_after,
|
|
queue_id, description
|
|
) VALUES (
|
|
p_tenant_id, p_addon_type, 'consume', -1,
|
|
v_balance_before, v_balance_after,
|
|
p_queue_id, p_description
|
|
);
|
|
|
|
RETURN jsonb_build_object(
|
|
'success', true,
|
|
'balance_before', v_balance_before,
|
|
'balance_after', v_balance_after
|
|
);
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.debit_addon_credit(p_tenant_id uuid, p_addon_type text, p_queue_id uuid, p_description text) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: FUNCTION debit_addon_credit(p_tenant_id uuid, p_addon_type text, p_queue_id uuid, p_description text); Type: COMMENT; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
COMMENT ON FUNCTION public.debit_addon_credit(p_tenant_id uuid, p_addon_type text, p_queue_id uuid, p_description text) IS 'Debita 1 crédito de add-on. Verifica saldo, rate limits e expiração.';
|
|
|
|
|
|
--
|
|
-- Name: delete_commitment_full(uuid, uuid); Type: FUNCTION; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE FUNCTION public.delete_commitment_full(p_tenant_id uuid, p_commitment_id uuid) RETURNS jsonb
|
|
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
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
declare
|
|
v_count int;
|
|
begin
|
|
if new.ativo is false then
|
|
return new;
|
|
end if;
|
|
|
|
select count(*) into v_count
|
|
from public.agenda_regras_semanais r
|
|
where r.owner_id = new.owner_id
|
|
and r.dia_semana = new.dia_semana
|
|
and r.ativo is true
|
|
and (tg_op = 'INSERT' or r.id <> new.id)
|
|
and (new.hora_inicio < r.hora_fim and new.hora_fim > r.hora_inicio);
|
|
|
|
if v_count > 0 then
|
|
raise exception 'Janela sobreposta: já existe uma regra ativa nesse intervalo.';
|
|
end if;
|
|
|
|
return new;
|
|
end;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.fn_agenda_regras_semanais_no_overlap() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: get_financial_report(uuid, date, date, text); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.get_financial_report(p_owner_id uuid, p_start_date date, p_end_date date, p_group_by text DEFAULT 'month'::text) RETURNS TABLE(group_key text, group_label text, total_receitas numeric, total_despesas numeric, saldo numeric, total_pendente numeric, total_overdue numeric, count_records bigint)
|
|
CREATE FUNCTION public.guard_no_change_core_plan_key() RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
begin
|
|
if old.key in ('clinic_free','clinic_pro','therapist_free','therapist_pro')
|
|
and new.key is distinct from old.key then
|
|
raise exception 'Não é permitido alterar a key do plano padrão (%).', old.key
|
|
using errcode = 'P0001';
|
|
end if;
|
|
|
|
return new;
|
|
end $$;
|
|
|
|
|
|
ALTER FUNCTION public.guard_no_change_core_plan_key() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: guard_no_change_plan_target(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.guard_no_change_plan_target() RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
declare
|
|
v_bypass text;
|
|
begin
|
|
-- bypass controlado por sessão/transação:
|
|
-- só passa se app.plan_migration_bypass = '1'
|
|
v_bypass := current_setting('app.plan_migration_bypass', true);
|
|
|
|
if v_bypass = '1' then
|
|
return new;
|
|
end if;
|
|
|
|
-- comportamento original (bloqueia qualquer mudança)
|
|
if new.target is distinct from old.target then
|
|
raise exception 'Não é permitido alterar target do plano (%) de % para %.',
|
|
old.key, old.target, new.target
|
|
using errcode = 'P0001';
|
|
end if;
|
|
|
|
return new;
|
|
end
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.guard_no_change_plan_target() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: guard_no_delete_core_plans(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.guard_no_delete_core_plans() RETURNS trigger
|
|
CREATE FUNCTION public.guard_no_change_plan_target() RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
declare
|
|
v_bypass text;
|
|
begin
|
|
-- bypass controlado por sessão/transação:
|
|
-- só passa se app.plan_migration_bypass = '1'
|
|
v_bypass := current_setting('app.plan_migration_bypass', true);
|
|
|
|
if v_bypass = '1' then
|
|
return new;
|
|
end if;
|
|
|
|
-- comportamento original (bloqueia qualquer mudança)
|
|
if new.target is distinct from old.target then
|
|
raise exception 'Não é permitido alterar target do plano (%) de % para %.',
|
|
old.key, old.target, new.target
|
|
using errcode = 'P0001';
|
|
end if;
|
|
|
|
return new;
|
|
end
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.guard_no_change_plan_target() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: guard_no_delete_core_plans(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.guard_no_delete_core_plans() RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
begin
|
|
if old.key in ('clinic_free','clinic_pro','therapist_free','therapist_pro') then
|
|
raise exception 'Plano padrão (%) não pode ser removido.', old.key
|
|
using errcode = 'P0001';
|
|
end if;
|
|
|
|
return old;
|
|
end $$;
|
|
|
|
|
|
ALTER FUNCTION public.guard_no_delete_core_plans() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: guard_patient_cannot_own_tenant(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.guard_patient_cannot_own_tenant() RETURNS trigger
|
|
CREATE FUNCTION public.guard_no_delete_core_plans() RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
begin
|
|
if old.key in ('clinic_free','clinic_pro','therapist_free','therapist_pro') then
|
|
raise exception 'Plano padrão (%) não pode ser removido.', old.key
|
|
using errcode = 'P0001';
|
|
end if;
|
|
|
|
return old;
|
|
end $$;
|
|
|
|
|
|
ALTER FUNCTION public.guard_no_delete_core_plans() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: guard_patient_cannot_own_tenant(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.guard_patient_cannot_own_tenant() RETURNS trigger
|
|
CREATE FUNCTION public.reactivate_subscription(p_subscription_id uuid) RETURNS public.subscriptions
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
AS $$
|
|
declare
|
|
v_sub public.subscriptions;
|
|
v_owner_type text;
|
|
v_owner_ref uuid;
|
|
begin
|
|
|
|
select *
|
|
into v_sub
|
|
from public.subscriptions
|
|
where id = p_subscription_id
|
|
for update;
|
|
|
|
if not found then
|
|
raise exception 'Subscription não encontrada';
|
|
end if;
|
|
|
|
if v_sub.status = 'active' then
|
|
return v_sub;
|
|
end if;
|
|
|
|
if v_sub.tenant_id is not null then
|
|
v_owner_type := 'clinic';
|
|
v_owner_ref := v_sub.tenant_id;
|
|
elsif v_sub.user_id is not null then
|
|
v_owner_type := 'therapist';
|
|
v_owner_ref := v_sub.user_id;
|
|
else
|
|
v_owner_type := null;
|
|
v_owner_ref := null;
|
|
end if;
|
|
|
|
update public.subscriptions
|
|
set status = 'active',
|
|
cancel_at_period_end = false,
|
|
updated_at = now()
|
|
where id = p_subscription_id
|
|
returning * into v_sub;
|
|
|
|
insert into public.subscription_events(
|
|
subscription_id,
|
|
owner_id,
|
|
owner_type,
|
|
owner_ref,
|
|
event_type,
|
|
old_plan_id,
|
|
new_plan_id,
|
|
created_by,
|
|
reason,
|
|
source,
|
|
metadata
|
|
)
|
|
values (
|
|
v_sub.id,
|
|
v_owner_ref,
|
|
v_owner_type,
|
|
v_owner_ref,
|
|
'reactivated',
|
|
v_sub.plan_id,
|
|
v_sub.plan_id,
|
|
auth.uid(),
|
|
'Reativação manual via admin',
|
|
'admin_panel',
|
|
jsonb_build_object('previous_status', 'canceled')
|
|
);
|
|
|
|
if v_owner_ref is not null then
|
|
insert into public.entitlements_invalidation(owner_id, changed_at)
|
|
values (v_owner_ref, now())
|
|
on conflict (owner_id)
|
|
do update set changed_at = excluded.changed_at;
|
|
end if;
|
|
|
|
return v_sub;
|
|
|
|
end;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.reactivate_subscription(p_subscription_id uuid) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: rebuild_owner_entitlements(uuid); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.rebuild_owner_entitlements(p_owner_id uuid) RETURNS void
|
|
CREATE FUNCTION public.rebuild_owner_entitlements(p_owner_id uuid) RETURNS void
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
AS $$
|
|
declare
|
|
v_plan_id uuid;
|
|
begin
|
|
-- Plano ativo do owner (owner = subscriptions.user_id)
|
|
select s.plan_id
|
|
into v_plan_id
|
|
from public.subscriptions s
|
|
where s.user_id = p_owner_id
|
|
and s.status = 'active'
|
|
order by s.created_at desc
|
|
limit 1;
|
|
|
|
-- Sempre zera entitlements do owner (rebuild)
|
|
delete from public.owner_feature_entitlements e
|
|
where e.owner_id = p_owner_id;
|
|
|
|
-- Se não tem assinatura ativa, acabou
|
|
if v_plan_id is null then
|
|
return;
|
|
end if;
|
|
|
|
-- Recria entitlements esperados pelo plano
|
|
insert into public.owner_feature_entitlements (owner_id, feature_key, sources, limits_list)
|
|
select
|
|
p_owner_id as owner_id,
|
|
f.key as feature_key,
|
|
array['plan'::text] as sources,
|
|
'{}'::jsonb as limits_list
|
|
from public.plan_features pf
|
|
join public.features f on f.id = pf.feature_id
|
|
where pf.plan_id = v_plan_id;
|
|
|
|
end;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.rebuild_owner_entitlements(p_owner_id uuid) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: revoke_support_session(text); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.revoke_support_session(p_token text) RETURNS boolean
|
|
CREATE FUNCTION public.set_tenant_feature_exception(p_tenant_id uuid, p_feature_key text, p_enabled boolean, p_reason text DEFAULT NULL::text) RETURNS void
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
AS $$
|
|
begin
|
|
-- ✅ Só owner ou admin do tenant podem alterar features
|
|
if not exists (
|
|
select 1 from public.tenant_members
|
|
where tenant_id = p_tenant_id
|
|
and user_id = auth.uid()
|
|
and role in ('owner', 'admin')
|
|
and status = 'active'
|
|
) then
|
|
raise exception 'Acesso negado: apenas owner/admin pode alterar features do tenant.';
|
|
end if;
|
|
|
|
insert into public.tenant_features (tenant_id, feature_key, enabled)
|
|
values (p_tenant_id, p_feature_key, p_enabled)
|
|
on conflict (tenant_id, feature_key)
|
|
do update set enabled = excluded.enabled;
|
|
|
|
insert into public.tenant_feature_exceptions_log (
|
|
tenant_id, feature_key, enabled, reason, created_by
|
|
) values (
|
|
p_tenant_id, p_feature_key, p_enabled, p_reason, auth.uid()
|
|
);
|
|
end;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.set_tenant_feature_exception(p_tenant_id uuid, p_feature_key text, p_enabled boolean, p_reason text) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: set_updated_at(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.set_updated_at() RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
BEGIN
|
|
NEW.updated_at = now();
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.set_updated_at() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: set_updated_at_recurrence(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.set_updated_at_recurrence() RETURNS trigger
|
|
CREATE FUNCTION public.subscription_intents_view_insert() RETURNS trigger
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
AS $$
|
|
declare
|
|
v_target text;
|
|
v_plan_id uuid;
|
|
begin
|
|
select p.id, p.target into v_plan_id, v_target
|
|
from public.plans p
|
|
where p.key = new.plan_key;
|
|
|
|
if v_plan_id is null then
|
|
raise exception 'Plano inválido: plan_key=%', new.plan_key;
|
|
end if;
|
|
|
|
if lower(v_target) = 'clinic' then
|
|
if new.tenant_id is null then
|
|
raise exception 'Intenção clinic exige tenant_id.';
|
|
end if;
|
|
|
|
insert into public.subscription_intents_tenant (
|
|
id, tenant_id, created_by_user_id, email,
|
|
plan_id, plan_key, interval, amount_cents, currency,
|
|
status, source, notes, created_at, paid_at
|
|
) values (
|
|
coalesce(new.id, gen_random_uuid()),
|
|
new.tenant_id, new.created_by_user_id, new.email,
|
|
v_plan_id, new.plan_key, coalesce(new.interval,'month'),
|
|
new.amount_cents, coalesce(new.currency,'BRL'),
|
|
coalesce(new.status,'pending'), coalesce(new.source,'manual'),
|
|
new.notes, coalesce(new.created_at, now()), new.paid_at
|
|
);
|
|
|
|
new.plan_target := 'clinic';
|
|
return new;
|
|
end if;
|
|
|
|
-- therapist ou supervisor → tabela personal
|
|
if lower(v_target) in ('therapist', 'supervisor') then
|
|
insert into public.subscription_intents_personal (
|
|
id, user_id, created_by_user_id, email,
|
|
plan_id, plan_key, interval, amount_cents, currency,
|
|
status, source, notes, created_at, paid_at
|
|
) values (
|
|
coalesce(new.id, gen_random_uuid()),
|
|
new.user_id, new.created_by_user_id, new.email,
|
|
v_plan_id, new.plan_key, coalesce(new.interval,'month'),
|
|
new.amount_cents, coalesce(new.currency,'BRL'),
|
|
coalesce(new.status,'pending'), coalesce(new.source,'manual'),
|
|
new.notes, coalesce(new.created_at, now()), new.paid_at
|
|
);
|
|
|
|
new.plan_target := lower(v_target); -- 'therapist' ou 'supervisor'
|
|
return new;
|
|
end if;
|
|
|
|
raise exception 'Target de plano não suportado: %', v_target;
|
|
end;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.subscription_intents_view_insert() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: subscriptions_validate_scope(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.subscriptions_validate_scope() RETURNS trigger
|
|
CREATE FUNCTION public.subscriptions_validate_scope() RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
DECLARE
|
|
v_target text;
|
|
BEGIN
|
|
SELECT lower(p.target) INTO v_target
|
|
FROM public.plans p
|
|
WHERE p.id = NEW.plan_id;
|
|
|
|
IF v_target IS NULL THEN
|
|
RAISE EXCEPTION 'Plano inválido (target nulo).';
|
|
END IF;
|
|
|
|
IF v_target = 'clinic' THEN
|
|
IF NEW.tenant_id IS NULL THEN
|
|
RAISE EXCEPTION 'Assinatura clinic exige tenant_id.';
|
|
END IF;
|
|
IF NEW.user_id IS NOT NULL THEN
|
|
RAISE EXCEPTION 'Assinatura clinic não pode ter user_id (XOR).';
|
|
END IF;
|
|
|
|
ELSIF v_target IN ('therapist', 'supervisor') THEN
|
|
-- supervisor é pessoal como therapist
|
|
IF NEW.tenant_id IS NOT NULL THEN
|
|
RAISE EXCEPTION 'Assinatura % não deve ter tenant_id.', v_target;
|
|
END IF;
|
|
IF NEW.user_id IS NULL THEN
|
|
RAISE EXCEPTION 'Assinatura % exige user_id.', v_target;
|
|
END IF;
|
|
|
|
ELSIF v_target = 'patient' THEN
|
|
IF NEW.tenant_id IS NOT NULL THEN
|
|
RAISE EXCEPTION 'Assinatura patient não deve ter tenant_id.';
|
|
END IF;
|
|
IF NEW.user_id IS NULL THEN
|
|
RAISE EXCEPTION 'Assinatura patient exige user_id.';
|
|
END IF;
|
|
|
|
ELSE
|
|
RAISE EXCEPTION 'Target de plano inválido: %', v_target;
|
|
END IF;
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.subscriptions_validate_scope() OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: sync_busy_mirror_agenda_eventos(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.sync_busy_mirror_agenda_eventos() RETURNS trigger
|
|
CREATE FUNCTION public.toggle_plan(owner uuid) RETURNS void
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
AS $$
|
|
declare
|
|
current_key text;
|
|
new_key text;
|
|
begin
|
|
select p.key into current_key
|
|
from subscriptions s
|
|
join plans p on p.id = s.plan_id
|
|
where s.owner_id = owner
|
|
and s.status = 'active';
|
|
|
|
new_key := case
|
|
when current_key = 'pro' then 'free'
|
|
else 'pro'
|
|
end;
|
|
|
|
update subscriptions s
|
|
set plan_id = p.id
|
|
from plans p
|
|
where p.key = new_key
|
|
and s.owner_id = owner
|
|
and s.status = 'active';
|
|
end;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.toggle_plan(owner uuid) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: transition_subscription(uuid, text, text, jsonb); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.transition_subscription(p_subscription_id uuid, p_to_status text, p_reason text DEFAULT NULL::text, p_metadata jsonb DEFAULT NULL::jsonb) RETURNS public.subscriptions
|
|
CREATE FUNCTION public.transition_subscription(p_subscription_id uuid, p_to_status text, p_reason text DEFAULT NULL::text, p_metadata jsonb DEFAULT NULL::jsonb) RETURNS public.subscriptions
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
AS $$
|
|
declare
|
|
v_sub public.subscriptions;
|
|
v_uid uuid;
|
|
v_is_allowed boolean := false;
|
|
begin
|
|
v_uid := auth.uid();
|
|
|
|
select *
|
|
into v_sub
|
|
from public.subscriptions
|
|
where id = p_subscription_id;
|
|
|
|
if not found then
|
|
raise exception 'Assinatura não encontrada';
|
|
end if;
|
|
|
|
-- =====================================================
|
|
-- 🔐 BLOCO DE AUTORIZAÇÃO
|
|
-- =====================================================
|
|
|
|
-- 1) SaaS admin pode tudo
|
|
if is_saas_admin() then
|
|
v_is_allowed := true;
|
|
end if;
|
|
|
|
-- 2) Assinatura pessoal (therapist)
|
|
if not v_is_allowed
|
|
and v_sub.tenant_id is null
|
|
and v_sub.user_id = v_uid then
|
|
v_is_allowed := true;
|
|
end if;
|
|
|
|
-- 3) Assinatura de clinic (tenant)
|
|
if not v_is_allowed
|
|
and v_sub.tenant_id is not null then
|
|
|
|
if exists (
|
|
select 1
|
|
from public.tenant_members tm
|
|
where tm.tenant_id = v_sub.tenant_id
|
|
and tm.user_id = v_uid
|
|
and tm.status = 'active'
|
|
and tm.role = 'tenant_admin'
|
|
) then
|
|
v_is_allowed := true;
|
|
end if;
|
|
|
|
end if;
|
|
|
|
if not v_is_allowed then
|
|
raise exception 'Sem permissão para transicionar esta assinatura';
|
|
end if;
|
|
|
|
-- =====================================================
|
|
-- 🧠 TRANSIÇÃO
|
|
-- =====================================================
|
|
|
|
update public.subscriptions
|
|
set status = p_to_status,
|
|
updated_at = now(),
|
|
cancelled_at = case when p_to_status = 'cancelled' then now() else cancelled_at end,
|
|
suspended_at = case when p_to_status = 'suspended' then now() else suspended_at end,
|
|
past_due_since = case when p_to_status = 'past_due' then now() else past_due_since end,
|
|
expired_at = case when p_to_status = 'expired' then now() else expired_at end,
|
|
activated_at = case when p_to_status = 'active' then now() else activated_at end
|
|
where id = p_subscription_id
|
|
returning * into v_sub;
|
|
|
|
-- =====================================================
|
|
-- 🧾 EVENT LOG
|
|
-- =====================================================
|
|
|
|
insert into public.subscription_events (
|
|
subscription_id,
|
|
owner_id,
|
|
event_type,
|
|
created_at,
|
|
created_by,
|
|
source,
|
|
reason,
|
|
metadata,
|
|
owner_type,
|
|
owner_ref
|
|
)
|
|
values (
|
|
v_sub.id,
|
|
coalesce(v_sub.tenant_id, v_sub.user_id),
|
|
'status_changed',
|
|
now(),
|
|
v_uid,
|
|
'manual_transition',
|
|
p_reason,
|
|
p_metadata,
|
|
case when v_sub.tenant_id is not null then 'tenant' else 'personal' end,
|
|
coalesce(v_sub.tenant_id, v_sub.user_id)
|
|
);
|
|
|
|
return v_sub;
|
|
end;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.transition_subscription(p_subscription_id uuid, p_to_status text, p_reason text, p_metadata jsonb) OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: trg_fn_financial_records_auto_overdue(); Type: FUNCTION; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE FUNCTION public.trg_fn_financial_records_auto_overdue() RETURNS trigger
|