Revised by
Mariusz KryĆski
6699e9e
| with recursive rnd_move(move) as (
select *, random() rnd from generate_series(1,9) move
), winning_positions(winning_position) as (
values (ARRAY[1, 2, 3]), (ARRAY[4, 5, 6]), (ARRAY[7, 8, 9]),
(ARRAY[1, 4, 7]), (ARRAY[2, 5, 8]), (ARRAY[3, 6, 9]),
(ARRAY[1, 5, 9]), (ARRAY[3, 5, 7])
), game as (
select ARRAY[]::int[] moves
union
(
select moves || ARRAY[move]
from game, rnd_move
where not move = any(moves)
order by rnd limit 1
)
), game_with_xo_moves as (
select moves[1:1] || moves[3:3] || moves[5:5] || moves[7:7] || moves[9:9] as x_moves,
moves[2:2] || moves[4:4] || moves[6:6] || moves[8:8] as o_moves
from game
), game_with_winner as (
select x_moves, o_moves, winning_position, lag(winning_position is not null) over () as win_pos_lag
from game_with_xo_moves left join winning_positions on x_moves @> winning_position or o_moves @> winning_position
), final_game as (
select x_moves, o_moves, winning_position from game_with_winner where not win_pos_lag
) select case when 1 = ANY(x_moves) then 'X' when 1 = ANY(o_moves) then 'O' else '.' end ||
case when 2 = ANY(x_moves) then 'X' when 2 = ANY(o_moves) then 'O' else '.' end ||
case when 3 = ANY(x_moves) then 'X' when 3 = ANY(o_moves) then 'O' else '.' end || chr(10) ||
case when 4 = ANY(x_moves) then 'X' when 4 = ANY(o_moves) then 'O' else '.' end ||
case when 5 = ANY(x_moves) then 'X' when 5 = ANY(o_moves) then 'O' else '.' end ||
case when 6 = ANY(x_moves) then 'X' when 6 = ANY(o_moves) then 'O' else '.' end || chr(10) ||
case when 7 = ANY(x_moves) then 'X' when 7 = ANY(o_moves) then 'O' else '.' end ||
case when 8 = ANY(x_moves) then 'X' when 8 = ANY(o_moves) then 'O' else '.' end ||
case when 9 = ANY(x_moves) then 'X' when 9 = ANY(o_moves) then 'O' else '.' end || chr(10) as board,
case when x_moves @> winning_position then 'X wins'
when o_moves @> winning_position then 'O wins'
end as winner
from final_game g;
|