From 3ec391a75f6edad855fc218c8369b0ce52158bb0 Mon Sep 17 00:00:00 2001
From: Tor Andersson <tor@ccxvii.net>
Date: Fri, 25 Apr 2025 20:00:09 +0200
Subject: Move archive export statement into schema view.

---
 schema.sql | 33 +++++++++++++++++++++++++++++++++
 1 file changed, 33 insertions(+)

(limited to 'schema.sql')

diff --git a/schema.sql b/schema.sql
index 8cda3d8..02a4c99 100644
--- a/schema.sql
+++ b/schema.sql
@@ -683,6 +683,39 @@ create view game_export_view as
 	from games as outer
 	;
 
+drop view if exists game_replay_view;
+create view game_replay_view as
+	select
+		game_id,
+		json_object(
+			'players',
+				(select json_group_array(
+						json_object('role', role, 'name', name)
+					)
+					from players
+					left join users using(user_id)
+					where game_id = outer.game_id
+				),
+			'state',
+				(select json(state)
+					from game_state
+					where game_id = outer.game_id
+				),
+			'replay',
+				(select json_group_array(
+						case when arguments is null then
+							json_array(role, action)
+						else
+							json_array(role, action, json(arguments))
+						end
+					)
+					from game_replay
+					where game_id = outer.game_id
+				)
+		) as export
+	from games as outer
+	;
+
 -- Tournaments --
 
 create table if not exists tm_seeds (
-- 
cgit v1.2.3

