byGalax
265 lines
8.0 KiB
PL/PgSQL
265 lines
8.0 KiB
PL/PgSQL
-- Phase 5B: per-conversation mini-games (Tic-Tac-Toe + Vier-Gewinnt /
|
||
-- Connect Four).
|
||
--
|
||
-- conversation_games: one row per game. The bubble in the chat is a
|
||
-- regular `messages` row whose plaintext payload is
|
||
-- `{v:1, type:'game', game_id:<id>, game_type:<'ttt'|'c4'>}`.
|
||
--
|
||
-- state JSON shape:
|
||
-- ttt: { kind: 'ttt', board: [null|0|1 × 9] } (row-major 3×3)
|
||
-- c4: { kind: 'c4', board: [null|0|1 × 42] } (row-major 6 rows × 7 cols)
|
||
--
|
||
-- players JSON: [user_id_a, user_id_b] — indices 0 and 1 map to board cells.
|
||
-- current_turn_user_id alternates; null once finished.
|
||
-- winner_user_id null on draw or unfinished.
|
||
|
||
create table if not exists public.conversation_games (
|
||
id uuid primary key default gen_random_uuid(),
|
||
conversation_id uuid not null references public.conversations(id) on delete cascade,
|
||
game_type text not null check (game_type in ('ttt', 'c4')),
|
||
state jsonb not null,
|
||
players jsonb not null,
|
||
current_turn_user_id uuid null references auth.users(id) on delete set null,
|
||
winner_user_id uuid null references auth.users(id) on delete set null,
|
||
created_at timestamptz not null default now(),
|
||
finished_at timestamptz null
|
||
);
|
||
|
||
create index if not exists conversation_games_conv_idx
|
||
on public.conversation_games(conversation_id, created_at desc);
|
||
|
||
alter table public.conversation_games enable row level security;
|
||
|
||
drop policy if exists conversation_games_select on public.conversation_games;
|
||
drop policy if exists conversation_games_insert on public.conversation_games;
|
||
|
||
create policy conversation_games_select
|
||
on public.conversation_games
|
||
for select
|
||
using (public.is_conversation_member(conversation_id));
|
||
|
||
create policy conversation_games_insert
|
||
on public.conversation_games
|
||
for insert
|
||
with check (
|
||
public.is_conversation_member(conversation_id)
|
||
and current_turn_user_id = auth.uid()
|
||
and (players->>0)::uuid = auth.uid()
|
||
);
|
||
|
||
alter table public.conversation_games replica identity full;
|
||
|
||
do $$
|
||
begin
|
||
if not exists (
|
||
select 1
|
||
from pg_publication_tables
|
||
where pubname = 'supabase_realtime'
|
||
and schemaname = 'public'
|
||
and tablename = 'conversation_games'
|
||
) then
|
||
execute 'alter publication supabase_realtime add table public.conversation_games';
|
||
end if;
|
||
end
|
||
$$;
|
||
|
||
-- ----------------------------------------------------------------------
|
||
-- Pure winner-check + board-full helpers.
|
||
-- ----------------------------------------------------------------------
|
||
|
||
create or replace function public.ttt_check_winner(board jsonb)
|
||
returns int
|
||
language plpgsql
|
||
immutable
|
||
as $$
|
||
declare
|
||
lines int[][] := array[
|
||
array[0,1,2], array[3,4,5], array[6,7,8],
|
||
array[0,3,6], array[1,4,7], array[2,5,8],
|
||
array[0,4,8], array[2,4,6]
|
||
];
|
||
ln int[];
|
||
a jsonb;
|
||
b jsonb;
|
||
c jsonb;
|
||
begin
|
||
foreach ln slice 1 in array lines loop
|
||
a := board->ln[1];
|
||
b := board->ln[2];
|
||
c := board->ln[3];
|
||
if jsonb_typeof(a) = 'number' and a = b and b = c then
|
||
return (a)::int;
|
||
end if;
|
||
end loop;
|
||
return null;
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.c4_check_winner(board jsonb)
|
||
returns int
|
||
language plpgsql
|
||
immutable
|
||
as $$
|
||
declare
|
||
rows constant int := 6;
|
||
cols constant int := 7;
|
||
r int;
|
||
c int;
|
||
i int;
|
||
d_r int;
|
||
d_c int;
|
||
directions int[][] := array[
|
||
array[0, 1],
|
||
array[1, 0],
|
||
array[1, 1],
|
||
array[1, -1]
|
||
];
|
||
dir int[];
|
||
base jsonb;
|
||
cell jsonb;
|
||
ok boolean;
|
||
begin
|
||
for r in 0..rows-1 loop
|
||
for c in 0..cols-1 loop
|
||
base := board->(r * cols + c);
|
||
if jsonb_typeof(base) <> 'number' then continue; end if;
|
||
foreach dir slice 1 in array directions loop
|
||
d_r := dir[1];
|
||
d_c := dir[2];
|
||
if r + d_r * 3 < 0 or r + d_r * 3 >= rows then continue; end if;
|
||
if c + d_c * 3 < 0 or c + d_c * 3 >= cols then continue; end if;
|
||
ok := true;
|
||
for i in 1..3 loop
|
||
cell := board->((r + d_r * i) * cols + (c + d_c * i));
|
||
if cell is null or cell <> base then ok := false; exit; end if;
|
||
end loop;
|
||
if ok then return (base)::int; end if;
|
||
end loop;
|
||
end loop;
|
||
end loop;
|
||
return null;
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.board_is_full(board jsonb)
|
||
returns boolean
|
||
language sql
|
||
immutable
|
||
as $$
|
||
select not exists (
|
||
select 1 from jsonb_array_elements(board) elt where jsonb_typeof(elt) is distinct from 'number'
|
||
);
|
||
$$;
|
||
|
||
-- ----------------------------------------------------------------------
|
||
-- The state-machine RPC.
|
||
-- ----------------------------------------------------------------------
|
||
|
||
create or replace function public.game_make_move(p_game_id uuid, p_move jsonb)
|
||
returns jsonb
|
||
language plpgsql
|
||
security definer
|
||
set search_path = public
|
||
as $$
|
||
declare
|
||
v_game record;
|
||
v_state jsonb;
|
||
v_board jsonb;
|
||
v_kind text;
|
||
v_player_idx int;
|
||
v_other_user uuid;
|
||
v_winner_idx int;
|
||
v_finished boolean;
|
||
v_cell int;
|
||
v_col int;
|
||
v_row int;
|
||
v_cols constant int := 7;
|
||
v_rows constant int := 6;
|
||
v_target_row int;
|
||
begin
|
||
if auth.uid() is null then
|
||
raise exception 'not_authenticated' using errcode = '28000';
|
||
end if;
|
||
|
||
select * into v_game from public.conversation_games where id = p_game_id for update;
|
||
if not found then
|
||
raise exception 'game_not_found' using errcode = 'P0002';
|
||
end if;
|
||
if v_game.finished_at is not null then
|
||
raise exception 'game_finished' using errcode = '42501';
|
||
end if;
|
||
if v_game.current_turn_user_id is null
|
||
or v_game.current_turn_user_id <> auth.uid() then
|
||
raise exception 'not_your_turn' using errcode = '42501';
|
||
end if;
|
||
|
||
if (v_game.players->>0)::uuid = auth.uid() then
|
||
v_player_idx := 0;
|
||
v_other_user := (v_game.players->>1)::uuid;
|
||
elsif (v_game.players->>1)::uuid = auth.uid() then
|
||
v_player_idx := 1;
|
||
v_other_user := (v_game.players->>0)::uuid;
|
||
else
|
||
raise exception 'not_a_player' using errcode = '42501';
|
||
end if;
|
||
|
||
v_state := v_game.state;
|
||
v_kind := v_state->>'kind';
|
||
v_board := v_state->'board';
|
||
|
||
if v_kind = 'ttt' then
|
||
if (p_move->'cell') is null or jsonb_typeof(p_move->'cell') <> 'number' then
|
||
raise exception 'bad_move' using errcode = '22023';
|
||
end if;
|
||
v_cell := (p_move->>'cell')::int;
|
||
if v_cell < 0 or v_cell > 8 then
|
||
raise exception 'bad_move' using errcode = '22023';
|
||
end if;
|
||
if jsonb_typeof(v_board->v_cell) = 'number' then
|
||
raise exception 'cell_taken' using errcode = '42501';
|
||
end if;
|
||
v_board := jsonb_set(v_board, array[v_cell::text], to_jsonb(v_player_idx));
|
||
v_winner_idx := public.ttt_check_winner(v_board);
|
||
|
||
elsif v_kind = 'c4' then
|
||
if (p_move->'column') is null or jsonb_typeof(p_move->'column') <> 'number' then
|
||
raise exception 'bad_move' using errcode = '22023';
|
||
end if;
|
||
v_col := (p_move->>'column')::int;
|
||
if v_col < 0 or v_col >= v_cols then
|
||
raise exception 'bad_move' using errcode = '22023';
|
||
end if;
|
||
v_target_row := -1;
|
||
for v_row in reverse v_rows - 1 .. 0 loop
|
||
if jsonb_typeof(v_board->(v_row * v_cols + v_col)) is distinct from 'number' then
|
||
v_target_row := v_row;
|
||
exit;
|
||
end if;
|
||
end loop;
|
||
if v_target_row < 0 then
|
||
raise exception 'column_full' using errcode = '42501';
|
||
end if;
|
||
v_board := jsonb_set(v_board, array[(v_target_row * v_cols + v_col)::text], to_jsonb(v_player_idx));
|
||
v_winner_idx := public.c4_check_winner(v_board);
|
||
|
||
else
|
||
raise exception 'unknown_game_kind' using errcode = '42501';
|
||
end if;
|
||
|
||
v_state := jsonb_set(v_state, '{board}', v_board);
|
||
v_finished := v_winner_idx is not null or public.board_is_full(v_board);
|
||
|
||
update public.conversation_games
|
||
set state = v_state,
|
||
current_turn_user_id = case when v_finished then null else v_other_user end,
|
||
winner_user_id = case when v_winner_idx is not null then (v_game.players->>v_winner_idx)::uuid else null end,
|
||
finished_at = case when v_finished then now() else null end
|
||
where id = p_game_id;
|
||
|
||
return v_state;
|
||
end;
|
||
$$;
|
||
|
||
revoke all on function public.game_make_move(uuid, jsonb) from public;
|
||
grant execute on function public.game_make_move(uuid, jsonb) to authenticated;
|