From d288108a7d294389a21a3df9d3f7fbcc5697ffac Mon Sep 17 00:00:00 2001
From: Tor Andersson <tor@ccxvii.net>
Date: Thu, 14 Mar 2024 20:03:52 +0100
Subject: Add and track score in player/role assignment.

2 points for a win, 1 point for a tie, 0 points for a loss.
---
 schema.sql | 21 +++++++++++++++++++++
 server.js  |  4 ++--
 2 files changed, 23 insertions(+), 2 deletions(-)

diff --git a/schema.sql b/schema.sql
index a0a3b02..049f6a8 100644
--- a/schema.sql
+++ b/schema.sql
@@ -454,6 +454,7 @@ create table if not exists players (
 	is_invite integer,
 	time_used real,
 	time_added real,
+	score integer,
 	primary key (game_id, role)
 ) without rowid;
 
@@ -648,6 +649,26 @@ begin
 		games.game_id = old.game_id;
 end;
 
+-- Trigger to update player score when game ends.
+
+drop trigger if exists trigger_update_score;
+create trigger trigger_update_score after update of result on games
+begin
+	update players
+		set score = (
+			case
+				when new.result is null then null
+				when new.result = role then 2
+				when new.result = 'Draw' then 1
+				when instr(new.result, role) then 1
+				else 0
+			end
+		)
+	where
+		players.game_id = new.game_id
+	;
+end;
+
 -- Trigger to track time spent!
 
 drop trigger if exists trigger_time_used_update;
diff --git a/server.js b/server.js
index 7ab5214..e9baf8d 100644
--- a/server.js
+++ b/server.js
@@ -3143,7 +3143,7 @@ const SQL_USER_STATS = SQL(`
 		titles.title_name,
 		scenario,
 		role,
-		sum(role=result) as won,
+		sum(score) / 2 as won,
 		count(*) as total
 	from
 		players
@@ -3163,7 +3163,7 @@ const SQL_USER_STATS = SQL(`
 		titles.title_name,
 		scenario,
 		null as role,
-		sum(role=result) as won,
+		sum(score) / 2 as won,
 		count(*) as total
 	from
 		players
-- 
cgit v1.2.3