kickkingsapi/schemas/rpc_collect_match_entries.sql

-- One-shot: run in Supabase Dashboard → SQL Editor **after** schemas/alter_matches_escrow.sql
-- Atomically moves both players' entry_fee RC into a per-match escrow user.
-- Idempotent: if entries_collected_at is already set, returns success without changing balances.
-- Requires users.rc to support numeric debits (prefer bigint via alter_users_rc_bigint.sql).

create or replace function public.collect_match_entries(p_match_id bigint)
returns jsonb
language plpgsql
security definer
set search_path = public
as $$
declare
  v_match public.matches%rowtype;
  v_escrow_id bigint;
  v_entry_fee bigint;
  v_bet_fee_pct integer;
  v_fee_each bigint;
  v_hold_each bigint;
  v_bet_fee_raw text;
begin
  if p_match_id is null or p_match_id < 1 then
    raise exception 'invalid_match';
  end if;

  select * into v_match
  from public.matches
  where id = p_match_id
  for update;

  if not found then
    raise exception 'match_not_found';
  end if;

  if v_match.entries_collected_at is not null then
    return jsonb_build_object(
      'ok', true,
      'already_collected', true,
      'match_id', p_match_id,
      'escrow_user_id', v_match.escrow_user_id,
      'entry_fee', coalesce(v_match.entry_fee, 0),
      'entries_collected_at', v_match.entries_collected_at
    );
  end if;

  if v_match.user_red is null or v_match.user_blue is null then
    raise exception 'players_incomplete';
  end if;

  if v_match.red_joined_at is null or v_match.blue_joined_at is null then
    raise exception 'joins_incomplete';
  end if;

  if v_match.settled_at is not null or v_match.winner_id is not null then
    raise exception 'match_already_settled';
  end if;

  v_entry_fee := coalesce(v_match.entry_fee, 0);
  if v_entry_fee < 0 then
    raise exception 'invalid_entry_fee';
  end if;

  select value into v_bet_fee_raw
  from public.settings
  where key = 'bet_fee'
  limit 1;

  begin
    v_bet_fee_pct := coalesce(nullif(trim(v_bet_fee_raw), '')::integer, 10);
  exception when others then
    v_bet_fee_pct := 10;
  end;

  if v_bet_fee_pct < 0 or v_bet_fee_pct > 100 then
    v_bet_fee_pct := 10;
  end if;

  v_fee_each := ceil((v_entry_fee::numeric * v_bet_fee_pct) / 100)::bigint;
  v_hold_each := v_entry_fee - v_fee_each;
  if v_hold_each < 0 then
    raise exception 'invalid_fee_split';
  end if;

  if v_match.escrow_user_id is not null then
    v_escrow_id := v_match.escrow_user_id;
  else
    insert into public.users (username, email, password, cc, rc)
    values (
      'match_escrow_' || p_match_id::text,
      null,
      null,
      0,
      0
    )
    returning id into v_escrow_id;

    update public.matches
    set escrow_user_id = v_escrow_id
    where id = p_match_id;
  end if;

  if v_entry_fee > 0 then
    update public.users
    set rc = coalesce(rc, 0) - v_entry_fee
    where id = v_match.user_red
      and coalesce(rc, 0) >= v_entry_fee;
    if not found then
      raise exception 'insufficient_rc_red';
    end if;

    update public.users
    set rc = coalesce(rc, 0) - v_entry_fee
    where id = v_match.user_blue
      and coalesce(rc, 0) >= v_entry_fee;
    if not found then
      raise exception 'insufficient_rc_blue';
    end if;

    update public.users
    set rc = coalesce(rc, 0) + (v_entry_fee * 2)
    where id = v_escrow_id;
    if not found then
      raise exception 'escrow_not_found';
    end if;

    if v_hold_each > 0 then
      insert into public.transactions ("from", "to", amount, remarks, match_id)
      values
        (v_match.user_red, v_escrow_id, v_hold_each, 'entry_hold', p_match_id),
        (v_match.user_blue, v_escrow_id, v_hold_each, 'entry_hold', p_match_id);
    end if;
    if v_fee_each > 0 then
      insert into public.transactions ("from", "to", amount, remarks, match_id)
      values
        (v_match.user_red, v_escrow_id, v_fee_each, 'entry_fee', p_match_id),
        (v_match.user_blue, v_escrow_id, v_fee_each, 'entry_fee', p_match_id);
    end if;
  end if;

  update public.matches
  set entries_collected_at = now()
  where id = p_match_id
    and entries_collected_at is null;

  return jsonb_build_object(
    'ok', true,
    'already_collected', false,
    'match_id', p_match_id,
    'escrow_user_id', v_escrow_id,
    'entry_fee', v_entry_fee,
    'fee_each', v_fee_each,
    'pot_rc', v_entry_fee * 2,
    'entries_collected_at', now()
  );
end;
$$;

revoke all on function public.collect_match_entries(bigint) from public;
grant execute on function public.collect_match_entries(bigint) to service_role;