From b1b753e317daa10e03a6a3b210d185539fac176b Mon Sep 17 00:00:00 2001
From: Tor Andersson <tor@ccxvii.net>
Date: Tue, 12 Sep 2023 23:56:45 +0200
Subject: Calculate Elo ratings.

Primarily for use with future matchmaking system to provide better games for
everyone.

Show top 5 players of each game on the game pages.
---
 schema.sql | 37 +++++++++++++++++++++++++++++++++++++
 1 file changed, 37 insertions(+)

(limited to 'schema.sql')

diff --git a/schema.sql b/schema.sql
index eef0a26..bebccb5 100644
--- a/schema.sql
+++ b/schema.sql
@@ -147,6 +147,42 @@ create view user_dynamic_view as
 		users
 	;
 
+-- Elo ratings & match making --
+
+create table if not exists ratings (
+	title_id integer,
+	user_id integer,
+	rating integer,
+	count integer,
+	last timestamp,
+	primary key (title_id, user_id)
+) without rowid;
+
+drop view if exists rating_view;
+create view rating_view as
+	select
+		title_id, name, rating, count, last
+	from
+		ratings
+		natural join users
+	order by
+		title_id,
+		rating desc
+;
+
+drop view if exists player_rating_view;
+create view player_rating_view as
+	select
+		games.game_id,
+		players.user_id,
+		players.role,
+		coalesce(rating, 1500) as rating,
+		coalesce(count, 0) as count
+	from players
+	natural join games
+	natural left join ratings
+;
+
 -- Friend and Block Lists --
 
 create table if not exists contacts (
@@ -487,6 +523,7 @@ begin
 	delete from threads where author_id = old.user_id;
 	delete from game_chat where user_id = old.user_id;
 	delete from players where user_id = old.user_id;
+	delete from ratings where user_id = old.user_id;
 	update games set owner_id = 0 where owner_id = old.user_id;
 end;
 
-- 
cgit v1.2.3

