Add "xtime" column for games to record finish date when state is missing.

2 files changed, 4 insertions, 3 deletions

diff --git a/schema.sql b/schema.sql
index 144ba7d..8e5ff4d 100644
--- a/schema.sql
+++ b/schema.sql
@@ -279,7 +279,8 @@ create table if not exists games (
 	is_random boolean default 0,
 	notice text,
 	status integer default 0,
-	result text
+	result text,
+	xtime datetime
 );
 
 create index if not exists games_title_idx on games(title_id);
@@ -356,7 +357,7 @@ create view game_view as
 		games.*,
 		titles.title_name,
 		owner.name as owner_name,
-		game_state.mtime,
+		coalesce(game_state.mtime, xtime) as mtime,
 		game_state.active
 	from
 		games
diff --git a/server.js b/server.js
index 4cc8e05..65f5a7a 100644
--- a/server.js
+++ b/server.js
@@ -1061,7 +1061,7 @@ const SQL_DELETE_GAME_NOTE = SQL("DELETE FROM game_notes WHERE game_id=? AND rol
 
 const SQL_SELECT_GAME_STATE = SQL("SELECT state FROM game_state WHERE game_id=?").pluck()
 const SQL_UPDATE_GAME_STATE = SQL("INSERT OR REPLACE INTO game_state (game_id,state,active,mtime) VALUES (?,?,?,datetime())")
-const SQL_UPDATE_GAME_RESULT = SQL("UPDATE games SET status=?, result=? WHERE game_id=?")
+const SQL_UPDATE_GAME_RESULT = SQL("UPDATE games SET status=?, result=?, xtime=datetime() WHERE game_id=?")
 const SQL_UPDATE_GAME_PRIVATE = SQL("UPDATE games SET is_private=1 WHERE game_id=?")
 
 const SQL_INSERT_REPLAY = SQL("insert into game_replay (game_id,replay_id,role,action,arguments) values (?, (select coalesce(max(replay_id), 0) + 1 from game_replay where game_id=?) ,?,?,?) returning replay_id").pluck()