From c235a8239177d38117f3313d17b904b66a806378 Mon Sep 17 00:00:00 2001
From: Tor Andersson <tor@ccxvii.net>
Date: Wed, 14 Feb 2024 23:20:44 +0100
Subject: Add import-game and export-game scripts.

To bulk transfer all game state, replay, and snapshots into a new game.
---
 schema.sql | 50 ++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 50 insertions(+)

(limited to 'schema.sql')

diff --git a/schema.sql b/schema.sql
index 36c2efc..a0a3b02 100644
--- a/schema.sql
+++ b/schema.sql
@@ -556,6 +556,56 @@ create view invite_reminder as
 		is_invite = 1
 	;
 
+-- Export game state as JSON
+
+drop view if exists game_export_view;
+create view game_export_view as
+	select
+		game_id,
+		json_object(
+			'setup', json_object(
+					'game_id', game_id,
+					'title_id', title_id,
+					'scenario', scenario,
+					'options', json(options),
+					'player_count', player_count,
+					'notice', notice
+				),
+			'players',
+				(select json_group_array(
+						json_object('role', role, 'name', name)
+					)
+					from players
+					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
+				),
+			'snaps',
+				(select json_group_array(
+						json_array(replay_id, json(state))
+					)
+					from game_snap
+					where game_id = outer.game_id
+				)
+		) as export
+	from games as outer
+	;
+
 -- Trigger to update player counts when players join and part games
 
 drop trigger if exists trigger_join_game;
-- 
cgit v1.2.3