1 files changed, 15 insertions, 1 deletions

diff --git a/schema.sql b/schema.sql
index 2840934..65edadf 100644
--- a/schema.sql
+++ b/schema.sql
@@ -254,7 +254,12 @@ create view user_dynamic_view as
 				owner_id = users.user_id
 				and status = 0
 				and join_count = 0
-		) as waiting,
+		) + (
+			select count(1) from unseen_games where user_id = users.user_id
+		) + (
+			select count(1) from unread_chats where user_id = users.user_id
+		)
+		as waiting,
 		is_banned
 	from
 		users
@@ -506,6 +511,12 @@ create table if not exists unread_chats (
 	primary key (user_id, game_id)
 ) without rowid;
 
+create table if not exists unseen_games (
+	user_id integer,
+	game_id integer,
+	primary key (user_id, game_id)
+) without rowid;
+
 drop view if exists game_chat_view;
 create view game_chat_view as
 	select
@@ -1055,6 +1066,7 @@ begin
 	delete from game_snap where game_id = old.game_id;
 	delete from game_notes where game_id = old.game_id;
 	delete from unread_chats where game_id = old.game_id;
+	delete from unseen_games where game_id = old.game_id;
 end;
 
 -- Triggers to clean up without relying on foreign key cascades
@@ -1068,6 +1080,7 @@ begin
 	delete from game_snap where game_id = old.game_id;
 	delete from game_notes where game_id = old.game_id;
 	delete from unread_chats where game_id = old.game_id;
+	delete from unseen_games where game_id = old.game_id;
 	delete from players where game_id = old.game_id;
 end;
 
@@ -1085,6 +1098,7 @@ begin
 	delete from tokens where user_id = old.user_id;
 	delete from read_threads where user_id = old.user_id;
 	delete from unread_chats where user_id = old.user_id;
+	delete from unseen_games where user_id = old.user_id;
 	delete from contacts where me = old.user_id or you = old.user_id;
 	delete from messages where from_id = old.user_id or to_id = old.user_id;
 	delete from posts where author_id = old.user_id;