378 lines
12 KiB
PL/PgSQL
378 lines
12 KiB
PL/PgSQL
-- One-shot: run in Supabase Dashboard → SQL Editor **after**
|
|
-- schemas/rpc_collect_match_entries.sql, schemas/alter_matches_cc_awarded.sql,
|
|
-- schemas/alter_users_mmr.sql, and schemas/alter_matches_mmr.sql.
|
|
-- Atomically settles a match: sets winner_id, pays prize + house fee only from the match escrow,
|
|
-- grants prize_cc to connected non-forfeiting participants, applies hidden Clash-style MMR, marks settled_at.
|
|
-- Idempotent if already settled for same winner (does not credit CC or MMR twice).
|
|
-- Dual forfeit: if already settled as a forfeit and this call is also p_forfeit true with a
|
|
-- different winner, return the stored outcome (already_settled) instead of winner_already_set.
|
|
-- First request to take the row lock wins; the second forfeiter is ignored.
|
|
--
|
|
-- p_red_connected / p_blue_connected: dedicated server live-connection flags at settle.
|
|
-- NULL (omitted, old dedicated builds or 2-arg calls) awards CC to that side (previous behaviour).
|
|
-- Do not infer connectedness from p_winner_id — a last-disconnect winner can be disconnected.
|
|
-- CC is never paid to a disconnected player, and never to the loser when the match is a forfeit
|
|
-- (leave, disconnect, last-disconnect-wins, or skip/auto-forfeit with p_forfeit true).
|
|
--
|
|
-- p_red_score / p_blue_score / p_forfeit: optional; drive hidden MMR percent (not player-visible).
|
|
-- Leave/disconnect forfeits use 20% MMR. Skip-forfeit (p_forfeit true, both still connected) uses the score table.
|
|
-- CREATE OR REPLACE cannot change the argument list — drop prior signatures first.
|
|
|
|
drop function if exists public.settle_match_reward(bigint, bigint);
|
|
drop function if exists public.settle_match_reward(bigint, bigint, boolean, boolean);
|
|
drop function if exists public.settle_match_reward(bigint, bigint, boolean, boolean, integer, integer, boolean);
|
|
|
|
create or replace function public.settle_match_reward(
|
|
p_match_id bigint,
|
|
p_winner_id bigint,
|
|
p_red_connected boolean default null,
|
|
p_blue_connected boolean default null,
|
|
p_red_score integer default null,
|
|
p_blue_score integer default null,
|
|
p_forfeit boolean default null
|
|
)
|
|
returns jsonb
|
|
language plpgsql
|
|
security definer
|
|
set search_path = public
|
|
as $$
|
|
declare
|
|
v_match public.matches%rowtype;
|
|
v_system bigint := 1;
|
|
v_entry_fee bigint;
|
|
v_prize_cc bigint;
|
|
v_bet_fee_pct integer;
|
|
v_fee_each bigint;
|
|
v_house_total bigint;
|
|
v_rc_prize bigint;
|
|
v_escrow_rc numeric;
|
|
v_bet_fee_raw text;
|
|
v_award_red boolean;
|
|
v_award_blue boolean;
|
|
v_red_mmr integer;
|
|
v_blue_mmr integer;
|
|
v_winner_mmr integer;
|
|
v_loser_mmr integer;
|
|
v_loser_id bigint;
|
|
v_loser_score integer;
|
|
v_loser_connected boolean;
|
|
v_forfeit boolean;
|
|
v_leave_forfeit boolean;
|
|
v_offset integer;
|
|
v_raw integer;
|
|
v_pct numeric;
|
|
v_delta integer;
|
|
begin
|
|
if p_match_id is null or p_match_id < 1 then
|
|
raise exception 'invalid_match';
|
|
end if;
|
|
if p_winner_id is null or p_winner_id < 1 then
|
|
raise exception 'invalid_winner';
|
|
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.settled_at is not null then
|
|
-- Same winner, or a second forfeit that lost the race: first settle stands.
|
|
if v_match.winner_id = p_winner_id
|
|
or (p_forfeit is true and v_match.forfeit is true) then
|
|
return jsonb_build_object(
|
|
'ok', true,
|
|
'already_settled', true,
|
|
'match_id', p_match_id,
|
|
'winner_id', v_match.winner_id,
|
|
'entry_fee', coalesce(v_match.entry_fee, 0),
|
|
'rc_prize', null,
|
|
'participant_cc', coalesce(v_match.prize_cc, 100),
|
|
'cc_awarded_red', coalesce(v_match.cc_awarded_red, true),
|
|
'cc_awarded_blue', coalesce(v_match.cc_awarded_blue, true),
|
|
'mmr_delta', v_match.mmr_delta,
|
|
'forfeit', v_match.forfeit,
|
|
'red_score', v_match.red_score,
|
|
'blue_score', v_match.blue_score,
|
|
'settled_at', v_match.settled_at
|
|
);
|
|
end if;
|
|
raise exception 'winner_already_set';
|
|
end if;
|
|
|
|
if v_match.winner_id is not null and v_match.winner_id <> p_winner_id then
|
|
if p_forfeit is true and v_match.forfeit is true then
|
|
return jsonb_build_object(
|
|
'ok', true,
|
|
'already_settled', true,
|
|
'match_id', p_match_id,
|
|
'winner_id', v_match.winner_id,
|
|
'entry_fee', coalesce(v_match.entry_fee, 0),
|
|
'rc_prize', null,
|
|
'participant_cc', coalesce(v_match.prize_cc, 100),
|
|
'cc_awarded_red', coalesce(v_match.cc_awarded_red, true),
|
|
'cc_awarded_blue', coalesce(v_match.cc_awarded_blue, true),
|
|
'mmr_delta', v_match.mmr_delta,
|
|
'forfeit', v_match.forfeit,
|
|
'red_score', v_match.red_score,
|
|
'blue_score', v_match.blue_score,
|
|
'settled_at', v_match.settled_at
|
|
);
|
|
end if;
|
|
raise exception 'winner_already_set';
|
|
end if;
|
|
|
|
if v_match.entries_collected_at is null then
|
|
raise exception 'entries_not_collected';
|
|
end if;
|
|
|
|
if v_match.escrow_user_id is null then
|
|
raise exception 'escrow_missing';
|
|
end if;
|
|
|
|
if p_winner_id is distinct from v_match.user_red
|
|
and p_winner_id is distinct from v_match.user_blue then
|
|
raise exception 'winner_not_participant';
|
|
end if;
|
|
|
|
if v_match.user_red is null or v_match.user_blue is null then
|
|
raise exception 'players_incomplete';
|
|
end if;
|
|
|
|
v_entry_fee := coalesce(v_match.entry_fee, 0);
|
|
v_prize_cc := coalesce(v_match.prize_cc, 100);
|
|
if v_prize_cc < 0 then
|
|
v_prize_cc := 100;
|
|
end if;
|
|
|
|
-- Lock both participants in id order (MMR + later CC/RC updates).
|
|
if v_match.user_red < v_match.user_blue then
|
|
select coalesce(mmr, 1000) into v_red_mmr
|
|
from public.users where id = v_match.user_red for update;
|
|
if not found then
|
|
raise exception 'winner_not_found';
|
|
end if;
|
|
select coalesce(mmr, 1000) into v_blue_mmr
|
|
from public.users where id = v_match.user_blue for update;
|
|
if not found then
|
|
raise exception 'winner_not_found';
|
|
end if;
|
|
else
|
|
select coalesce(mmr, 1000) into v_blue_mmr
|
|
from public.users where id = v_match.user_blue for update;
|
|
if not found then
|
|
raise exception 'winner_not_found';
|
|
end if;
|
|
select coalesce(mmr, 1000) into v_red_mmr
|
|
from public.users where id = v_match.user_red for update;
|
|
if not found then
|
|
raise exception 'winner_not_found';
|
|
end if;
|
|
end if;
|
|
|
|
if p_winner_id = v_match.user_red then
|
|
v_winner_mmr := v_red_mmr;
|
|
v_loser_mmr := v_blue_mmr;
|
|
v_loser_id := v_match.user_blue;
|
|
v_loser_score := p_blue_score;
|
|
v_loser_connected := p_blue_connected;
|
|
else
|
|
v_winner_mmr := v_blue_mmr;
|
|
v_loser_mmr := v_red_mmr;
|
|
v_loser_id := v_match.user_red;
|
|
v_loser_score := p_red_score;
|
|
v_loser_connected := p_red_connected;
|
|
end if;
|
|
|
|
-- Forfeit: explicit flag (leave / skip / auto), or loser disconnected, or both disconnected.
|
|
-- NULL connected flags (legacy) are not treated as disconnect.
|
|
v_leave_forfeit := (p_red_connected is not distinct from false and p_blue_connected is not distinct from false)
|
|
or (v_loser_connected is not distinct from false);
|
|
v_forfeit := coalesce(p_forfeit, false) or v_leave_forfeit;
|
|
|
|
-- CC: connected finishers only, and never the forfeiting side (the loser on a forfeit).
|
|
v_award_red := (v_match.user_red is not null) and coalesce(p_red_connected, true);
|
|
v_award_blue := (v_match.user_blue is not null) and coalesce(p_blue_connected, true);
|
|
if v_forfeit then
|
|
if p_winner_id = v_match.user_red then
|
|
v_award_blue := false;
|
|
else
|
|
v_award_red := false;
|
|
end if;
|
|
end if;
|
|
|
|
-- 20% MMR only for leave/disconnect. Skip-forfeit (both still connected) uses the score table.
|
|
if v_leave_forfeit then
|
|
v_pct := 0.20;
|
|
elsif v_loser_score is null then
|
|
v_pct := 1.00;
|
|
elsif v_loser_score <= 0 then
|
|
v_pct := 1.00;
|
|
elsif v_loser_score = 1 then
|
|
v_pct := 0.90;
|
|
else
|
|
v_pct := 0.75;
|
|
end if;
|
|
|
|
v_offset := round((v_loser_mmr - v_winner_mmr)::numeric / 20)::integer;
|
|
if v_offset > 20 then
|
|
v_offset := 20;
|
|
elsif v_offset < -20 then
|
|
v_offset := -20;
|
|
end if;
|
|
v_raw := 30 + v_offset;
|
|
v_delta := greatest(1, round(v_raw::numeric * v_pct)::integer);
|
|
|
|
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_house_total := v_fee_each * 2;
|
|
v_rc_prize := (v_entry_fee * 2) - v_house_total;
|
|
|
|
if v_rc_prize < 0 then
|
|
raise exception 'invalid_prize';
|
|
end if;
|
|
|
|
select coalesce(rc, 0) into v_escrow_rc
|
|
from public.users
|
|
where id = v_match.escrow_user_id
|
|
for update;
|
|
|
|
if not found then
|
|
raise exception 'escrow_not_found';
|
|
end if;
|
|
|
|
if v_escrow_rc <> (v_entry_fee * 2) then
|
|
raise exception 'escrow_balance_mismatch';
|
|
end if;
|
|
|
|
if v_rc_prize > 0 then
|
|
update public.users
|
|
set rc = coalesce(rc, 0) - v_rc_prize
|
|
where id = v_match.escrow_user_id
|
|
and coalesce(rc, 0) >= v_rc_prize;
|
|
if not found then
|
|
raise exception 'insufficient_escrow';
|
|
end if;
|
|
|
|
update public.users
|
|
set rc = coalesce(rc, 0) + v_rc_prize
|
|
where id = p_winner_id;
|
|
if not found then
|
|
raise exception 'winner_not_found';
|
|
end if;
|
|
|
|
insert into public.transactions ("from", "to", amount, remarks, match_id)
|
|
values (v_match.escrow_user_id, p_winner_id, v_rc_prize, 'reward', p_match_id);
|
|
end if;
|
|
|
|
if v_house_total > 0 then
|
|
update public.users
|
|
set rc = coalesce(rc, 0) - v_house_total
|
|
where id = v_match.escrow_user_id
|
|
and coalesce(rc, 0) >= v_house_total;
|
|
if not found then
|
|
raise exception 'insufficient_escrow';
|
|
end if;
|
|
|
|
update public.users
|
|
set rc = coalesce(rc, 0) + v_house_total
|
|
where id = v_system;
|
|
if not found then
|
|
raise exception 'system_not_found';
|
|
end if;
|
|
|
|
insert into public.transactions ("from", "to", amount, remarks, match_id)
|
|
values (v_match.escrow_user_id, v_system, v_house_total, 'bet_fee', p_match_id);
|
|
end if;
|
|
|
|
-- Escrow must be empty after settle.
|
|
update public.users
|
|
set rc = 0
|
|
where id = v_match.escrow_user_id
|
|
and coalesce(rc, 0) = 0;
|
|
if not found then
|
|
raise exception 'escrow_not_zero';
|
|
end if;
|
|
|
|
if v_award_red then
|
|
update public.users
|
|
set cc = coalesce(cc, 0) + v_prize_cc
|
|
where id = v_match.user_red;
|
|
end if;
|
|
if v_award_blue then
|
|
update public.users
|
|
set cc = coalesce(cc, 0) + v_prize_cc
|
|
where id = v_match.user_blue;
|
|
end if;
|
|
|
|
update public.users
|
|
set mmr = coalesce(mmr, 1000) + v_delta
|
|
where id = p_winner_id;
|
|
if not found then
|
|
raise exception 'winner_not_found';
|
|
end if;
|
|
|
|
update public.users
|
|
set mmr = greatest(0, coalesce(mmr, 1000) - v_delta)
|
|
where id = v_loser_id;
|
|
if not found then
|
|
raise exception 'winner_not_found';
|
|
end if;
|
|
|
|
update public.matches
|
|
set
|
|
winner_id = p_winner_id,
|
|
settled_at = now(),
|
|
cc_awarded_red = v_award_red,
|
|
cc_awarded_blue = v_award_blue,
|
|
red_score = p_red_score,
|
|
blue_score = p_blue_score,
|
|
forfeit = v_forfeit,
|
|
mmr_delta = v_delta
|
|
where id = p_match_id
|
|
and settled_at is null;
|
|
|
|
if not found then
|
|
raise exception 'settle_race';
|
|
end if;
|
|
|
|
return jsonb_build_object(
|
|
'ok', true,
|
|
'already_settled', false,
|
|
'match_id', p_match_id,
|
|
'winner_id', p_winner_id,
|
|
'entry_fee', v_entry_fee,
|
|
'rc_prize', v_rc_prize,
|
|
'house_fee', v_house_total,
|
|
'participant_cc', v_prize_cc,
|
|
'cc_awarded_red', v_award_red,
|
|
'cc_awarded_blue', v_award_blue,
|
|
'mmr_delta', v_delta,
|
|
'forfeit', v_forfeit,
|
|
'red_score', p_red_score,
|
|
'blue_score', p_blue_score,
|
|
'settled_at', now()
|
|
);
|
|
end;
|
|
$$;
|
|
|
|
revoke all on function public.settle_match_reward(bigint, bigint, boolean, boolean, integer, integer, boolean) from public;
|
|
grant execute on function public.settle_match_reward(bigint, bigint, boolean, boolean, integer, integer, boolean) to service_role;
|