96 lines
3.2 KiB
PL/PgSQL
96 lines
3.2 KiB
PL/PgSQL
-- =====================================================================
|
|
-- 0026_manager_seed_rpcs.sql
|
|
--
|
|
-- The frontend's "supabase" client is actually a thin shim that only
|
|
-- supports rpc + select. Manager seeding of fee_schedule and fx_rates
|
|
-- therefore needs SECURITY DEFINER wrapper RPCs (also a defense-in-depth
|
|
-- improvement over relying on RLS for INSERT).
|
|
-- =====================================================================
|
|
|
|
set search_path = app, public;
|
|
|
|
create or replace function app.set_fee_bracket(
|
|
p_shop uuid,
|
|
p_service_code text,
|
|
p_currency app.currency_code,
|
|
p_min_amount numeric,
|
|
p_max_amount numeric,
|
|
p_fee_fixed numeric,
|
|
p_fee_pct numeric,
|
|
p_commission_fixed numeric,
|
|
p_commission_pct numeric
|
|
) returns uuid
|
|
language plpgsql
|
|
security definer
|
|
set search_path = app, public
|
|
as $$
|
|
declare
|
|
v_id uuid;
|
|
begin
|
|
if not app.has_role_in_shop(p_shop, 'manager')
|
|
and not app.has_role_in_shop(p_shop, 'owner')
|
|
then
|
|
raise exception 'manager or owner role required';
|
|
end if;
|
|
if p_min_amount is null or p_max_amount is null
|
|
or p_min_amount < 0 or p_max_amount <= p_min_amount then
|
|
raise exception 'invalid amount range';
|
|
end if;
|
|
|
|
insert into app.fee_schedule(
|
|
shop_id, service_code, currency,
|
|
min_amount, max_amount,
|
|
fee_fixed, fee_pct, commission_fixed, commission_pct
|
|
) values (
|
|
p_shop, p_service_code, p_currency,
|
|
p_min_amount, p_max_amount,
|
|
coalesce(p_fee_fixed,0), coalesce(p_fee_pct,0),
|
|
coalesce(p_commission_fixed,0), coalesce(p_commission_pct,0)
|
|
) returning id into v_id;
|
|
|
|
perform app.log_auth_event('fee_bracket_set', p_shop, null,
|
|
jsonb_build_object('id', v_id, 'service_code', p_service_code,
|
|
'currency', p_currency));
|
|
return v_id;
|
|
end;
|
|
$$;
|
|
revoke all on function app.set_fee_bracket(uuid, text, app.currency_code, numeric, numeric, numeric, numeric, numeric, numeric) from public;
|
|
grant execute on function app.set_fee_bracket(uuid, text, app.currency_code, numeric, numeric, numeric, numeric, numeric, numeric) to authenticated;
|
|
|
|
create or replace function app.set_fx_rate(
|
|
p_shop uuid,
|
|
p_usd_to_lbp numeric,
|
|
p_tolerance_pct numeric
|
|
) returns uuid
|
|
language plpgsql
|
|
security definer
|
|
set search_path = app, public
|
|
as $$
|
|
declare
|
|
v_id uuid;
|
|
begin
|
|
if not app.has_role_in_shop(p_shop, 'manager')
|
|
and not app.has_role_in_shop(p_shop, 'owner')
|
|
then
|
|
raise exception 'manager or owner role required';
|
|
end if;
|
|
if p_usd_to_lbp is null or p_usd_to_lbp <= 0 then
|
|
raise exception 'rate must be > 0';
|
|
end if;
|
|
if coalesce(p_tolerance_pct,0) < 0 or coalesce(p_tolerance_pct,0) > 50 then
|
|
raise exception 'tolerance must be between 0 and 50';
|
|
end if;
|
|
|
|
insert into app.fx_rates(shop_id, usd_to_lbp_rate, tolerance_pct)
|
|
values (p_shop, p_usd_to_lbp, coalesce(p_tolerance_pct,1.0))
|
|
returning id into v_id;
|
|
|
|
perform app.log_auth_event('fx_rate_set', p_shop, null,
|
|
jsonb_build_object('id', v_id, 'rate', p_usd_to_lbp,
|
|
'tolerance_pct', p_tolerance_pct));
|
|
return v_id;
|
|
end;
|
|
$$;
|
|
revoke all on function app.set_fx_rate(uuid, numeric, numeric) from public;
|
|
grant execute on function app.set_fx_rate(uuid, numeric, numeric) to authenticated;
|