Referrals

-- better-supabase module: referrals
-- @bsb-block referrals@1 managed
-- @bsb-hash sha256:9c3db130d8c6b657a6e3e4eef1beb9ffe77fbfbfb5b147d13edeced21215b734
-- Referral codes per member, attribution when the referred user or organization signs up, and a reward posted once to the ledger when the referral qualifies.
-- Managed by `better-supabase add`; re-running it rewrites this file unless it was edited by hand.
-- Change it through `blocks` in better-supabase.config.ts and the module's SQL hooks.

create or replace function better_supabase.actor_may_write()
returns boolean
language sql
stable
security invoker
set search_path = ''
as $$
  select case
    when auth.jwt() is null then true
    when auth.jwt() -> 'act' ->> 'kind' = 'support'
      then coalesce(auth.jwt() -> 'act' -> 'read_only' = 'false'::jsonb, false)
    when auth.jwt() -> 'act' ->> 'kind' = 'agent'
      then coalesce(auth.jwt() -> 'act' -> 'read_only', 'false'::jsonb) = 'false'::jsonb
    when auth.jwt() -> 'act' ->> 'kind' is not null then false
    when auth.jwt() -> 'act' ->> 'session_id' is not null
      and auth.jwt() -> 'act' ->> 'kind' is null then false
    else true
  end
$$;
revoke execute on function better_supabase.actor_may_write() from public;
grant execute on function better_supabase.actor_may_write() to anon, authenticated, service_role;

create or replace function better_supabase.raise_actor_readonly()
returns void
language plpgsql
volatile
set search_path = ''
as $$
begin
  raise exception 'This session is read-only' using errcode = '42501', hint = 'ACTOR_READ_ONLY';
end;
$$;
revoke execute on function better_supabase.raise_actor_readonly() from public;
grant execute on function better_supabase.raise_actor_readonly() to anon, authenticated, service_role;
-- The tenant scope policies call the module's functions, so they go before
-- the module drops or replaces one and come back at the end.
do $$
begin
  begin
    drop policy if exists bsb_tenant_scope on "better_supabase"."referral_codes";
    drop policy if exists bsb_tenant_scope_select on "better_supabase"."referral_codes";
    drop policy if exists bsb_tenant_scope_insert on "better_supabase"."referral_codes";
    drop policy if exists bsb_tenant_scope_update on "better_supabase"."referral_codes";
    drop policy if exists bsb_tenant_scope_delete on "better_supabase"."referral_codes";
  exception when undefined_table or invalid_schema_name then null;
  end;
  begin
    drop policy if exists bsb_tenant_scope on "better_supabase"."referrals";
    drop policy if exists bsb_tenant_scope_select on "better_supabase"."referrals";
    drop policy if exists bsb_tenant_scope_insert on "better_supabase"."referrals";
    drop policy if exists bsb_tenant_scope_update on "better_supabase"."referrals";
    drop policy if exists bsb_tenant_scope_delete on "better_supabase"."referrals";
  exception when undefined_table or invalid_schema_name then null;
  end;
end $$;

create schema if not exists better_supabase;
grant usage on schema better_supabase to anon, authenticated, service_role;
-- Referral codes and the referrals they bring. A code belongs to the member who
-- shares it, optionally to one invitation. Rewards are bigint minor units of a
-- ledger unit, posted once per referral.
create table if not exists "better_supabase"."referral_codes" (
  "id" uuid primary key default gen_random_uuid(),
  "tenant_id" uuid not null,
  "code" text not null check ("code" ~ '^[A-Z0-9]{4,32}$'),
  "referrer_id" uuid not null,
  -- The invitation the code was made for, so accepting it attributes the referral.
  "invitation_id" uuid,
  "reward_amount" bigint not null default 0 check ("reward_amount" >= 0),
  "reward_unit" text check ("reward_unit" is null or length("reward_unit") between 1 and 32),
  "max_uses" integer check ("max_uses" is null or "max_uses" > 0),
  "uses" integer not null default 0,
  "active" boolean not null default true,
  "expires_at" timestamptz,
  "created_at" timestamptz not null default now(),
  unique ("tenant_id", "code"),
  unique ("tenant_id", "id"),
  check ("reward_amount" = 0 or "reward_unit" is not null)
);
create index if not exists referral_codes_referrer_idx on "better_supabase"."referral_codes" ("tenant_id", "referrer_id");
create unique index if not exists referral_codes_invitation_key on "better_supabase"."referral_codes" ("tenant_id", "invitation_id") where "invitation_id" is not null;
alter table "better_supabase"."referral_codes" enable row level security;
revoke all on "better_supabase"."referral_codes" from public, anon, authenticated;
grant select on "better_supabase"."referral_codes" to authenticated;
grant all on "better_supabase"."referral_codes" to service_role;
drop policy if exists referral_codes_read on "better_supabase"."referral_codes";
create policy referral_codes_read on "better_supabase"."referral_codes"
  for select to authenticated
  using (("tenant_id" in (select better_supabase.tenant_ids_with('referrals.read')) or "referrer_id" = (select auth.uid())));

create table if not exists "better_supabase"."referrals" (
  "id" uuid primary key default gen_random_uuid(),
  "tenant_id" uuid not null,
  "code_id" uuid not null,
  "referrer_id" uuid not null,
  "referred_user_id" uuid,
  "referred_organization_id" uuid,
  "referred_email" text check ("referred_email" is null or length("referred_email") <= 320),
  "status" text not null default 'attributed' check ("status" in ('attributed', 'qualified', 'rewarded', 'void')),
  -- The reward as the code promised it when the referral was attributed.
  "reward_amount" bigint not null default 0 check ("reward_amount" >= 0),
  "reward_unit" text,
  "qualified_at" timestamptz,
  "qualified_by" text,
  "rewarded_at" timestamptz,
  "ledger_transaction_id" uuid,
  "voided_at" timestamptz,
  "created_at" timestamptz not null default now(),
  check ("referred_user_id" is not null or "referred_organization_id" is not null or "referred_email" is not null),
  foreign key ("tenant_id", "code_id") references "better_supabase"."referral_codes" ("tenant_id", "id") on delete cascade
);
create unique index if not exists referrals_user_key on "better_supabase"."referrals" ("tenant_id", "referred_user_id") where "referred_user_id" is not null;
create unique index if not exists referrals_organization_key on "better_supabase"."referrals" ("tenant_id", "referred_organization_id") where "referred_organization_id" is not null;
create index if not exists referrals_code_idx on "better_supabase"."referrals" ("tenant_id", "code_id");
create index if not exists referrals_referrer_idx on "better_supabase"."referrals" ("tenant_id", "referrer_id", "created_at" desc);
alter table "better_supabase"."referrals" enable row level security;
revoke all on "better_supabase"."referrals" from public, anon, authenticated;
grant select on "better_supabase"."referrals" to authenticated;
grant all on "better_supabase"."referrals" to service_role;
drop policy if exists referrals_read on "better_supabase"."referrals";
create policy referrals_read on "better_supabase"."referrals"
  for select to authenticated
  using (("tenant_id" in (select better_supabase.tenant_ids_with('referrals.read')) or "referrer_id" = (select auth.uid())));

-- Creates a referral code for a member (the caller by default). Making a code
-- for someone else needs referrals.manage, the own code referrals.write. code
-- is generated when omitted. The reward is paid in reward_unit on the ledger
-- when a referral qualifies. invitation_id ties the code to an invitation, so
-- accepting it attributes the referral.
create or replace function "better_supabase"."create_referral_code"(tenant uuid, referrer_id uuid default null, code text default null, invitation_id uuid default null, reward_amount bigint default 0, reward_unit text default null, max_uses integer default null, expires_at timestamptz default null)
returns jsonb
language plpgsql
security definer
set search_path = ''
as $$
#variable_conflict use_variable
declare
  v_row "better_supabase"."referral_codes";
  v_referrer uuid := coalesce(create_referral_code.referrer_id, auth.uid());
  v_code text := upper(coalesce(nullif(trim(create_referral_code.code), ''), substr(replace(gen_random_uuid()::text, '-', ''), 1, 8)));
begin
  if not (better_supabase.actor_may_write()) then
    raise exception 'This session is read-only' using errcode = '42501', hint = 'ACTOR_READ_ONLY';
  end if;
  if v_referrer is null then
    raise exception 'sign in to create a referral code' using errcode = '42501', hint = 'REFERRAL_FORBIDDEN';
  end if;
  if not (coalesce(nullif((select auth.jwt()) ->> 'role', ''), session_user::text) in ('service_role', 'postgres', 'supabase_admin') or (create_referral_code.tenant is not null and coalesce(better_supabase.can('tenant', create_referral_code.tenant, 'referrals.write'), false)))
    or (v_referrer is distinct from auth.uid() and not (coalesce(nullif((select auth.jwt()) ->> 'role', ''), session_user::text) in ('service_role', 'postgres', 'supabase_admin') or (create_referral_code.tenant is not null and coalesce(better_supabase.can('tenant', create_referral_code.tenant, 'referrals.manage'), false)))) then
    raise exception 'not allowed to create referral codes' using errcode = '42501', hint = 'REFERRAL_FORBIDDEN';
  end if;
  if v_code !~ '^[A-Z0-9]{4,32}$' or coalesce(create_referral_code.reward_amount, 0) < 0
    or (coalesce(create_referral_code.reward_amount, 0) > 0 and nullif(trim(create_referral_code.reward_unit), '') is null) then
    raise exception 'a code is 4 to 32 letters or digits, and a reward needs a unit' using errcode = '22023', hint = 'REFERRAL_INVALID';
  end if;
  if (coalesce(create_referral_code.reward_amount, 0) > 0 or create_referral_code.max_uses is not null or create_referral_code.expires_at is not null)
    and not (coalesce(nullif((select auth.jwt()) ->> 'role', ''), session_user::text) in ('service_role', 'postgres', 'supabase_admin') or (create_referral_code.tenant is not null and coalesce(better_supabase.can('tenant', create_referral_code.tenant, 'referrals.manage'), false))) then
    raise exception 'rewards, limits and expiry on a code need referrals.manage' using errcode = '42501', hint = 'REFERRAL_FORBIDDEN';
  end if;
  insert into "better_supabase"."referral_codes" ("tenant_id", "code", "referrer_id", "invitation_id", "reward_amount", "reward_unit", "max_uses", "expires_at")
  values (create_referral_code.tenant, v_code, v_referrer, create_referral_code.invitation_id, coalesce(create_referral_code.reward_amount, 0),
    nullif(trim(create_referral_code.reward_unit), ''), create_referral_code.max_uses, create_referral_code.expires_at)
  returning * into v_row;
  null;
  return jsonb_build_object('id', v_row."id", 'organizationId', v_row."tenant_id", 'code', v_row."code", 'referrerId', v_row."referrer_id", 'invitationId', v_row."invitation_id", 'rewardAmount', v_row."reward_amount"::text, 'rewardUnit', v_row."reward_unit", 'maxUses', v_row."max_uses", 'uses', v_row."uses", 'active', v_row."active", 'expiresAt', v_row."expires_at", 'createdAt', v_row."created_at");
end;
$$;
revoke execute on function "better_supabase"."create_referral_code"(uuid, uuid, text, uuid, bigint, text, integer, timestamptz) from public, anon;
grant execute on function "better_supabase"."create_referral_code"(uuid, uuid, text, uuid, bigint, text, integer, timestamptz) to authenticated, service_role;

-- Attributes a referral without checking the caller: attribute_referral and
-- the invitation trigger call it. Attributing a user or an organization that
-- already has a referral returns that referral.
create or replace function "better_supabase"."referral_attribute"(tenant uuid, code text, referred_user uuid default null, referred_org uuid default null, referred_email text default null)
returns jsonb
language plpgsql
security definer
set search_path = ''
as $$
#variable_conflict use_variable
declare
  v_code "better_supabase"."referral_codes";
  v_ref "better_supabase"."referrals";
begin
  if referral_attribute.referred_user is null and referral_attribute.referred_org is null and nullif(trim(referral_attribute.referred_email), '') is null then
    raise exception 'name the referred user, organization or email' using errcode = '22023', hint = 'REFERRAL_INVALID';
  end if;
  select * into v_ref from "better_supabase"."referrals" x
  where x."tenant_id" = referral_attribute.tenant
    and ((referral_attribute.referred_user is not null and x."referred_user_id" = referral_attribute.referred_user)
      or (referral_attribute.referred_org is not null and x."referred_organization_id" = referral_attribute.referred_org));
  if found then
    return jsonb_build_object('id', v_ref."id", 'organizationId', v_ref."tenant_id", 'codeId', v_ref."code_id", 'referrerId', v_ref."referrer_id", 'referredUserId', v_ref."referred_user_id", 'referredOrganizationId', v_ref."referred_organization_id", 'referredEmail', v_ref."referred_email", 'status', v_ref."status", 'rewardAmount', v_ref."reward_amount"::text, 'rewardUnit', v_ref."reward_unit", 'qualifiedAt', v_ref."qualified_at", 'rewardedAt', v_ref."rewarded_at", 'ledgerTransactionId', v_ref."ledger_transaction_id", 'createdAt', v_ref."created_at");
  end if;
  select * into v_code from "better_supabase"."referral_codes" x
  where x."tenant_id" = referral_attribute.tenant and x."code" = upper(trim(referral_attribute.code)) for update;
  if not found or not v_code."active" or (v_code."expires_at" is not null and v_code."expires_at" < now())
    or (v_code."max_uses" is not null and v_code."uses" >= v_code."max_uses") then
    raise exception 'the referral code is unknown, expired or used up' using errcode = 'P0002', hint = 'REFERRAL_CODE_INVALID';
  end if;
  if referral_attribute.referred_user = v_code."referrer_id" then
    raise exception 'a member can''t refer themselves' using errcode = '22023', hint = 'REFERRAL_SELF';
  end if;
  insert into "better_supabase"."referrals" ("tenant_id", "code_id", "referrer_id", "referred_user_id", "referred_organization_id", "referred_email", "reward_amount", "reward_unit")
  values (referral_attribute.tenant, v_code."id", v_code."referrer_id", referral_attribute.referred_user, referral_attribute.referred_org,
    lower(nullif(trim(referral_attribute.referred_email), '')), v_code."reward_amount", v_code."reward_unit")
  returning * into v_ref;
  update "better_supabase"."referral_codes" x set "uses" = x."uses" + 1 where x."id" = v_code."id";
  null;
  return jsonb_build_object('id', v_ref."id", 'organizationId', v_ref."tenant_id", 'codeId', v_ref."code_id", 'referrerId', v_ref."referrer_id", 'referredUserId', v_ref."referred_user_id", 'referredOrganizationId', v_ref."referred_organization_id", 'referredEmail', v_ref."referred_email", 'status', v_ref."status", 'rewardAmount', v_ref."reward_amount"::text, 'rewardUnit', v_ref."reward_unit", 'qualifiedAt', v_ref."qualified_at", 'rewardedAt', v_ref."rewarded_at", 'ledgerTransactionId', v_ref."ledger_transaction_id", 'createdAt', v_ref."created_at");
end;
$$;
revoke execute on function "better_supabase"."referral_attribute"(uuid, text, uuid, uuid, text) from public, anon, authenticated;
grant execute on function "better_supabase"."referral_attribute"(uuid, text, uuid, uuid, text) to service_role;

-- Attributes the user or organization that signed up with a code. Calling it
-- again for the same user or organization returns the first referral. Needs
-- referrals.write.
create or replace function "better_supabase"."attribute_referral"(tenant uuid, code text, referred_user uuid default null, referred_org uuid default null, referred_email text default null)
returns jsonb
language plpgsql
security definer
set search_path = ''
as $$
begin
  if not (coalesce(nullif((select auth.jwt()) ->> 'role', ''), session_user::text) in ('service_role', 'postgres', 'supabase_admin') or (attribute_referral.tenant is not null and coalesce(better_supabase.can('tenant', attribute_referral.tenant, 'referrals.write'), false))) then
    raise exception 'not allowed to attribute referrals' using errcode = '42501', hint = 'REFERRAL_FORBIDDEN';
  end if;
  return "better_supabase"."referral_attribute"(attribute_referral.tenant, attribute_referral.code, attribute_referral.referred_user,
    attribute_referral.referred_org, attribute_referral.referred_email);
end;
$$;
revoke execute on function "better_supabase"."attribute_referral"(uuid, text, uuid, uuid, text) from public, anon;
grant execute on function "better_supabase"."attribute_referral"(uuid, text, uuid, uuid, text) to authenticated, service_role;

-- Marks the referral of a user or organization as qualified after the event
-- that earns the reward, such as its first paid invoice, and posts the reward
-- to the ledger when the code carries one: the reward expense account is
-- debited and the referrer's payable rewards account credited, once per
-- referral. source names what qualified it. Qualifying again returns the
-- referral. Needs referrals.manage.
create or replace function "better_supabase"."qualify_referral"(tenant uuid, referred_user uuid default null, referred_org uuid default null, source text default null)
returns jsonb
language plpgsql
security definer
set search_path = ''
as $$
#variable_conflict use_variable
declare
  v_ref "better_supabase"."referrals";
  v_tx uuid;
begin
  if not (better_supabase.actor_may_write()) then
    raise exception 'This session is read-only' using errcode = '42501', hint = 'ACTOR_READ_ONLY';
  end if;
  if not (coalesce(nullif((select auth.jwt()) ->> 'role', ''), session_user::text) in ('service_role', 'postgres', 'supabase_admin') or (qualify_referral.tenant is not null and coalesce(better_supabase.can('tenant', qualify_referral.tenant, 'referrals.manage'), false))) then
    raise exception 'not allowed to qualify referrals' using errcode = '42501', hint = 'REFERRAL_FORBIDDEN';
  end if;
  select * into v_ref from "better_supabase"."referrals" x
  where x."tenant_id" = qualify_referral.tenant
    and ((qualify_referral.referred_user is not null and x."referred_user_id" = qualify_referral.referred_user)
      or (qualify_referral.referred_org is not null and x."referred_organization_id" = qualify_referral.referred_org))
  for update;
  if not found then
    return null;
  end if;
  if v_ref."status" <> 'attributed' then
    return jsonb_build_object('id', v_ref."id", 'organizationId', v_ref."tenant_id", 'codeId', v_ref."code_id", 'referrerId', v_ref."referrer_id", 'referredUserId', v_ref."referred_user_id", 'referredOrganizationId', v_ref."referred_organization_id", 'referredEmail', v_ref."referred_email", 'status', v_ref."status", 'rewardAmount', v_ref."reward_amount"::text, 'rewardUnit', v_ref."reward_unit", 'qualifiedAt', v_ref."qualified_at", 'rewardedAt', v_ref."rewarded_at", 'ledgerTransactionId', v_ref."ledger_transaction_id", 'createdAt', v_ref."created_at");
  end if;
  update "better_supabase"."referrals" x set "status" = 'qualified', "qualified_at" = now(), "qualified_by" = left(qualify_referral.source, 200)
  where x."id" = v_ref."id" returning * into v_ref;
  null;
  if v_ref."reward_amount" > 0 then
    v_tx := "better_supabase"."ledger_post"(v_ref."tenant_id", 'referral.reward',
      jsonb_build_array(
        jsonb_build_object('account', "better_supabase"."ledger_account_for"(v_ref."tenant_id", 'expense', 'referrals:expense:' || v_ref."reward_unit", 'Referral rewards', v_ref."reward_unit", 0::smallint, null, null, null, true, 'referrals.read', 'referrals', null), 'amount', (-v_ref."reward_amount")::text),
        jsonb_build_object('account', "better_supabase"."ledger_account_for"(v_ref."tenant_id", 'payable', 'referrals:rewards:' || v_ref."referrer_id"::text || ':' || v_ref."reward_unit", 'Referral rewards payable', v_ref."reward_unit", 0::smallint, null, null, v_ref."referrer_id", true, 'referrals.read', 'referrals', null), 'amount', v_ref."reward_amount"::text)),
      'referrals', v_ref."id"::text, null, 'referral:' || v_ref."id"::text, null,
      jsonb_build_object('referrerId', v_ref."referrer_id"));
    update "better_supabase"."referrals" x set "status" = 'rewarded', "rewarded_at" = now(), "ledger_transaction_id" = v_tx
    where x."id" = v_ref."id" returning * into v_ref;
    null;
  end if;
  return jsonb_build_object('id', v_ref."id", 'organizationId', v_ref."tenant_id", 'codeId', v_ref."code_id", 'referrerId', v_ref."referrer_id", 'referredUserId', v_ref."referred_user_id", 'referredOrganizationId', v_ref."referred_organization_id", 'referredEmail', v_ref."referred_email", 'status', v_ref."status", 'rewardAmount', v_ref."reward_amount"::text, 'rewardUnit', v_ref."reward_unit", 'qualifiedAt', v_ref."qualified_at", 'rewardedAt', v_ref."rewarded_at", 'ledgerTransactionId', v_ref."ledger_transaction_id", 'createdAt', v_ref."created_at");
end;
$$;
revoke execute on function "better_supabase"."qualify_referral"(uuid, uuid, uuid, text) from public, anon;
grant execute on function "better_supabase"."qualify_referral"(uuid, uuid, uuid, text) to authenticated, service_role;

-- Voids a referral, for example after fraud or a refund. A reward already
-- posted is reversed on the ledger. Voiding a void referral returns it. Needs
-- referrals.manage.
create or replace function "better_supabase"."void_referral"(referral_id uuid, reason text default null)
returns jsonb
language plpgsql
security definer
set search_path = ''
as $$
#variable_conflict use_variable
declare
  v_ref "better_supabase"."referrals";
begin
  if not (better_supabase.actor_may_write()) then
    raise exception 'This session is read-only' using errcode = '42501', hint = 'ACTOR_READ_ONLY';
  end if;
  select * into v_ref from "better_supabase"."referrals" x where x."id" = void_referral.referral_id for update;
  if not found or not (coalesce(nullif((select auth.jwt()) ->> 'role', ''), session_user::text) in ('service_role', 'postgres', 'supabase_admin') or (v_ref."tenant_id" is not null and coalesce(better_supabase.can('tenant', v_ref."tenant_id", 'referrals.read'), false))) then
    raise exception 'no such referral' using errcode = 'P0002', hint = 'REFERRAL_NOT_FOUND';
  end if;
  if not (coalesce(nullif((select auth.jwt()) ->> 'role', ''), session_user::text) in ('service_role', 'postgres', 'supabase_admin') or (v_ref."tenant_id" is not null and coalesce(better_supabase.can('tenant', v_ref."tenant_id", 'referrals.manage'), false))) then
    raise exception 'not allowed to void referrals' using errcode = '42501', hint = 'REFERRAL_FORBIDDEN';
  end if;
  if v_ref."status" = 'void' then
    return jsonb_build_object('id', v_ref."id", 'organizationId', v_ref."tenant_id", 'codeId', v_ref."code_id", 'referrerId', v_ref."referrer_id", 'referredUserId', v_ref."referred_user_id", 'referredOrganizationId', v_ref."referred_organization_id", 'referredEmail', v_ref."referred_email", 'status', v_ref."status", 'rewardAmount', v_ref."reward_amount"::text, 'rewardUnit', v_ref."reward_unit", 'qualifiedAt', v_ref."qualified_at", 'rewardedAt', v_ref."rewarded_at", 'ledgerTransactionId', v_ref."ledger_transaction_id", 'createdAt', v_ref."created_at");
  end if;
  if v_ref."ledger_transaction_id" is not null then
    perform "better_supabase"."ledger_post"(v_ref."tenant_id", 'referral.reversal',
      (select jsonb_agg(jsonb_build_object('account', e."account_id", 'amount', (-e."amount")::text) order by e."created_at", e."id")
        from "better_supabase"."ledger_entries" e where e."transaction_id" = v_ref."ledger_transaction_id"),
      'referrals', v_ref."id"::text, nullif(trim(void_referral.reason), ''), 'referral-void:' || v_ref."id"::text, null,
      jsonb_build_object('reversal', true), v_ref."ledger_transaction_id");
  end if;
  update "better_supabase"."referrals" x set "status" = 'void', "voided_at" = now()
  where x."id" = v_ref."id" returning * into v_ref;
  null;
  return jsonb_build_object('id', v_ref."id", 'organizationId', v_ref."tenant_id", 'codeId', v_ref."code_id", 'referrerId', v_ref."referrer_id", 'referredUserId', v_ref."referred_user_id", 'referredOrganizationId', v_ref."referred_organization_id", 'referredEmail', v_ref."referred_email", 'status', v_ref."status", 'rewardAmount', v_ref."reward_amount"::text, 'rewardUnit', v_ref."reward_unit", 'qualifiedAt', v_ref."qualified_at", 'rewardedAt', v_ref."rewarded_at", 'ledgerTransactionId', v_ref."ledger_transaction_id", 'createdAt', v_ref."created_at");
end;
$$;
revoke execute on function "better_supabase"."void_referral"(uuid, text) from public, anon;
grant execute on function "better_supabase"."void_referral"(uuid, text) to authenticated, service_role;

-- An organization's referrals, newest first. filters is { referrer, status,
-- limit }. Needs referrals.read; without it a member sees the referrals of
-- their own codes.
create or replace function "better_supabase"."list_referrals"(tenant uuid, filters jsonb default '{}')
returns jsonb
language plpgsql
stable
security definer
set search_path = ''
as $$
#variable_conflict use_variable
declare
  v_filters jsonb := coalesce(list_referrals.filters, '{}'::jsonb);
  v_all boolean := (coalesce(nullif((select auth.jwt()) ->> 'role', ''), session_user::text) in ('service_role', 'postgres', 'supabase_admin') or (list_referrals.tenant is not null and coalesce(better_supabase.can('tenant', list_referrals.tenant, 'referrals.read'), false)));
begin
  return coalesce((
    select jsonb_agg(jsonb_build_object('id', y."id", 'organizationId', y."tenant_id", 'codeId', y."code_id", 'referrerId', y."referrer_id", 'referredUserId', y."referred_user_id", 'referredOrganizationId', y."referred_organization_id", 'referredEmail', y."referred_email", 'status', y."status", 'rewardAmount', y."reward_amount"::text, 'rewardUnit', y."reward_unit", 'qualifiedAt', y."qualified_at", 'rewardedAt', y."rewarded_at", 'ledgerTransactionId', y."ledger_transaction_id", 'createdAt', y."created_at") order by y."created_at" desc)
    from (
      select * from "better_supabase"."referrals" z
      where z."tenant_id" = list_referrals.tenant
        and (v_all or z."referrer_id" = (select auth.uid()))
        and (not v_filters ? 'referrer' or z."referrer_id" = (v_filters ->> 'referrer')::uuid)
        and (not v_filters ? 'status' or z."status" = v_filters ->> 'status')
      order by z."created_at" desc
      limit least(greatest(coalesce((v_filters ->> 'limit')::integer, 100), 1), 500)
    ) y
  ), '[]'::jsonb);
end;
$$;
revoke execute on function "better_supabase"."list_referrals"(uuid, jsonb) from public, anon;
grant execute on function "better_supabase"."list_referrals"(uuid, jsonb) to authenticated, service_role;

-- An organization's referral codes, newest first. filters is { referrer,
-- limit }. Needs referrals.read; without it a member sees their own codes.
create or replace function "better_supabase"."list_referral_codes"(tenant uuid, filters jsonb default '{}')
returns jsonb
language plpgsql
stable
security definer
set search_path = ''
as $$
#variable_conflict use_variable
declare
  v_filters jsonb := coalesce(list_referral_codes.filters, '{}'::jsonb);
  v_all boolean := (coalesce(nullif((select auth.jwt()) ->> 'role', ''), session_user::text) in ('service_role', 'postgres', 'supabase_admin') or (list_referral_codes.tenant is not null and coalesce(better_supabase.can('tenant', list_referral_codes.tenant, 'referrals.read'), false)));
begin
  return coalesce((
    select jsonb_agg(jsonb_build_object('id', y."id", 'organizationId', y."tenant_id", 'code', y."code", 'referrerId', y."referrer_id", 'invitationId', y."invitation_id", 'rewardAmount', y."reward_amount"::text, 'rewardUnit', y."reward_unit", 'maxUses', y."max_uses", 'uses', y."uses", 'active', y."active", 'expiresAt', y."expires_at", 'createdAt', y."created_at") order by y."created_at" desc)
    from (
      select * from "better_supabase"."referral_codes" z
      where z."tenant_id" = list_referral_codes.tenant
        and (v_all or z."referrer_id" = (select auth.uid()))
        and (not v_filters ? 'referrer' or z."referrer_id" = (v_filters ->> 'referrer')::uuid)
      order by z."created_at" desc
      limit least(greatest(coalesce((v_filters ->> 'limit')::integer, 100), 1), 500)
    ) y
  ), '[]'::jsonb);
end;
$$;
revoke execute on function "better_supabase"."list_referral_codes"(uuid, jsonb) from public, anon;
grant execute on function "better_supabase"."list_referral_codes"(uuid, jsonb) to authenticated, service_role;

-- Tenant isolation (ADR 0099): permissive policies combine with or, so one
-- the app adds would widen the block's. These restrictive policies keep each
-- table to the caller's organizations and what the block's policies grant.
-- Until memberships installs, the caller is a member of no organization.
do $$
begin
  if to_regprocedure('better_supabase.member_organization_ids(text[])') is null then
    create function better_supabase.member_organization_ids(roles text[] default null)
    returns setof uuid
    language sql
    stable
    set search_path = ''
    as $members$ select null::uuid where false $members$;
  end if;
end $$;
drop policy if exists bsb_tenant_scope on "better_supabase"."referral_codes";
drop policy if exists bsb_tenant_scope_select on "better_supabase"."referral_codes";
create policy bsb_tenant_scope_select on "better_supabase"."referral_codes" as restrictive
  for select to public
  using (
    "tenant_id" in (select better_supabase.member_organization_ids())
    or (("tenant_id" in (select better_supabase.tenant_ids_with('referrals.read')) or "referrer_id" = (select auth.uid())))
  );
drop policy if exists bsb_tenant_scope_insert on "better_supabase"."referral_codes";
create policy bsb_tenant_scope_insert on "better_supabase"."referral_codes" as restrictive
  for insert to public
  with check ("tenant_id" in (select better_supabase.member_organization_ids()));
drop policy if exists bsb_tenant_scope_update on "better_supabase"."referral_codes";
create policy bsb_tenant_scope_update on "better_supabase"."referral_codes" as restrictive
  for update to public
  using ("tenant_id" in (select better_supabase.member_organization_ids()))
  with check ("tenant_id" in (select better_supabase.member_organization_ids()));
drop policy if exists bsb_tenant_scope_delete on "better_supabase"."referral_codes";
create policy bsb_tenant_scope_delete on "better_supabase"."referral_codes" as restrictive
  for delete to public
  using ("tenant_id" in (select better_supabase.member_organization_ids()));
drop policy if exists bsb_tenant_scope on "better_supabase"."referrals";
drop policy if exists bsb_tenant_scope_select on "better_supabase"."referrals";
create policy bsb_tenant_scope_select on "better_supabase"."referrals" as restrictive
  for select to public
  using (
    "tenant_id" in (select better_supabase.member_organization_ids())
    or (("tenant_id" in (select better_supabase.tenant_ids_with('referrals.read')) or "referrer_id" = (select auth.uid())))
  );
drop policy if exists bsb_tenant_scope_insert on "better_supabase"."referrals";
create policy bsb_tenant_scope_insert on "better_supabase"."referrals" as restrictive
  for insert to public
  with check ("tenant_id" in (select better_supabase.member_organization_ids()));
drop policy if exists bsb_tenant_scope_update on "better_supabase"."referrals";
create policy bsb_tenant_scope_update on "better_supabase"."referrals" as restrictive
  for update to public
  using ("tenant_id" in (select better_supabase.member_organization_ids()))
  with check ("tenant_id" in (select better_supabase.member_organization_ids()));
drop policy if exists bsb_tenant_scope_delete on "better_supabase"."referrals";
create policy bsb_tenant_scope_delete on "better_supabase"."referrals" as restrictive
  for delete to public
  using ("tenant_id" in (select better_supabase.member_organization_ids()));

create schema if not exists better_supabase;
create table if not exists better_supabase.blocks (
  name text primary key,
  block text,
  version integer not null,
  mode text not null,
  installed_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
alter table better_supabase.blocks enable row level security;
revoke all on better_supabase.blocks from public, anon, authenticated;
grant select on better_supabase.blocks to service_role;