Pause inactive users from tournament queue.

Remove very inactive users from tournament queue.

Count tournaments as "open" while they still have players queued.

3 files changed, 64 insertions, 32 deletions

diff --git a/schema.sql b/schema.sql
index e945916..4ae8bb5 100644
--- a/schema.sql
+++ b/schema.sql
@@ -632,6 +632,17 @@ create table if not exists tm_winners (
 
 create index if not exists tm_winners_pool_idx on tm_winners(pool_id);
 
+drop view if exists tm_queue_view;
+create view tm_queue_view as
+	select
+		tm_queue.*
+	from
+		tm_queue
+		join user_last_seen using(user_id)
+	where
+		julianday() - julianday(atime) < 3
+	;
+
 drop view if exists tm_pool_active_view;
 create view tm_pool_active_view as
 	select
diff --git a/server.js b/server.js
index f98ff22..bd22beb 100644
--- a/server.js
+++ b/server.js
@@ -2593,6 +2593,7 @@ function purge_game_ticker() {
 	QUERY_PURGE_ACTIVE_GAMES.run()
 	QUERY_PURGE_FINISHED_GAMES.run()
 	QUERY_PURGE_MESSAGES.run()
+	TM_DELETE_QUEUE_INACTIVE.run()
 }
 
 // Purge abandoned games every 31 minutes.
@@ -2662,9 +2663,20 @@ setTimeout(time_control_ticker, 13 * 1000)
 
 const designs = require("./designs.js")
 
+const TM_SELECT_BANNED = SQL("select exists ( select 1 from tm_banned where user_id=? )").pluck()
 const TM_INSERT_BANNED = SQL("insert or ignore into tm_banned (user_id, time) values (?, datetime())")
+
 const TM_DELETE_QUEUE_ALL = SQL("delete from tm_queue where user_id=?")
 
+const TM_DELETE_QUEUE_INACTIVE = SQL(`
+	delete from tm_queue where exists (
+		select 1
+		from user_last_seen
+		where user_last_seen.user_id = tm_queue.user_id
+		and julianday() - julianday(atime) > 14
+	)
+`)
+
 const TM_MAY_JOIN_ANY_SEED = SQL(`
 	select ( select notify and is_verified from users where user_id=@user_id )
 	or ( select exists ( select 1 from webhooks where user_id=@user_id and error is null ) )
@@ -2673,16 +2685,21 @@ const TM_MAY_JOIN_ANY_SEED = SQL(`
 `).pluck()
 
 const TM_MAY_JOIN_SEED = SQL(`
-	select ( select not exists ( select 1 from tm_banned where user_id=@user_id ) )
-	and ( select coalesce(is_open, 0) as may_join from tm_seeds where seed_id=@seed_id )
+	select is_open or exists ( select 1 from tm_queue_view where tm_queue_view.seed_id = tm_seeds.seed_id )
+	from tm_seeds
+	where seed_id=?
 `).pluck()
 
+function is_banned_from_tournaments(user_id) {
+	return TM_SELECT_BANNED.get(user_id)
+}
+
 function may_join_any_seed(user_id) {
 	return DEBUG || TM_MAY_JOIN_ANY_SEED.get({user_id})
 }
 
-function may_join_seed(user_id, seed_id) {
-	return TM_MAY_JOIN_SEED.get({user_id,seed_id})
+function may_join_seed(seed_id) {
+	return TM_MAY_JOIN_SEED.get(seed_id)
 }
 
 const TM_SEED_LIST_ALL = SQL(`
@@ -2690,8 +2707,9 @@ const TM_SEED_LIST_ALL = SQL(`
 		tm_seeds.*,
 		sum(level is 1) as queue_size,
 		sum(user_id is ?) as is_queued
-	from tm_seeds left join tm_queue using(seed_id)
-	where is_open
+	from tm_seeds left join tm_queue_view using(seed_id)
+	where
+		is_open or exists ( select 1 from tm_queue_view where tm_queue_view.seed_id = tm_seeds.seed_id )
 	group by seed_id
 	order by seed_name
 `)
@@ -2701,8 +2719,10 @@ const TM_SEED_LIST_TITLE = SQL(`
 		tm_seeds.*,
 		sum(level is 1) as queue_size,
 		sum(user_id is ?) as is_queued
-	from tm_seeds left join tm_queue using(seed_id)
-	where title_id = ? and is_open
+	from tm_seeds left join tm_queue_view using(seed_id)
+	where title_id = ? and (
+		is_open or exists ( select 1 from tm_queue_view where tm_queue_view.seed_id = tm_seeds.seed_id )
+	)
 	group by seed_id
 	order by seed_name
 `)
@@ -2712,7 +2732,7 @@ const TM_SEED_LIST_USER = SQL(`
 		tm_seeds.*,
 		sum(level is 1) as queue_size,
 		sum(user_id is ?) as is_queued
-	from tm_seeds left join tm_queue using(seed_id)
+	from tm_seeds left join tm_queue_view using(seed_id)
 	group by seed_id
 	having is_queued
 	order by seed_name
@@ -2768,7 +2788,7 @@ const TM_POOL_LIST_SEED_FINISHED = SQL("select * from tm_pool_finished_view wher
 
 const TM_SELECT_QUEUE_BLACKLIST = SQL(`
 	with qq as (
-		select user_id from tm_queue where seed_id=? and level=?
+		select user_id from tm_queue_view where seed_id=? and level=?
 	)
 	select me, you
 	from contacts
@@ -2776,8 +2796,8 @@ const TM_SELECT_QUEUE_BLACKLIST = SQL(`
 	where relation < 0 and exists (select 1 from qq where user_id = you)
 `)
 
-const TM_SELECT_QUEUE_NAMES = SQL("select user_id, name, level from tm_queue join users using(user_id) where seed_id=? and level=? order by time")
-const TM_SELECT_QUEUE = SQL("select user_id from tm_queue where seed_id=? and level=? order by time desc").pluck()
+const TM_SELECT_QUEUE_NAMES = SQL("select user_id, name, level from tm_queue_view join users using(user_id) where seed_id=? and level=? order by time")
+const TM_SELECT_QUEUE = SQL("select user_id from tm_queue_view where seed_id=? and level=? order by time desc").pluck()
 const TM_DELETE_QUEUE = SQL("delete from tm_queue where user_id=? and seed_id=? and level=?")
 const TM_INSERT_QUEUE = SQL("insert or ignore into tm_queue (user_id, seed_id, level) values (?,?,?)")
 
@@ -2949,9 +2969,9 @@ const TM_SELECT_SEED_READY_MINI_CUP = SQL(`
 		seed_id, level
 	from
 		tm_seeds
-		join tm_queue using(seed_id)
+		join tm_queue_view using(seed_id)
 	where
-		is_open and seed_name like 'mc.%'
+		seed_name like 'mc.%'
 		and julianday(time) < julianday('now', '-30 seconds')
 	group by
 		seed_id, level
@@ -2982,11 +3002,13 @@ app.get("/tm/seed/:seed_name", function (req, res) {
 
 	let error = null
 	let may_register = false
-	if (req.user && seed.is_open) {
-		if (!may_join_any_seed(req.user.user_id))
+	if (req.user) {
+		if (is_banned_from_tournaments(req.user.user_id))
+			error = "You may not join any tournaments."
+		else if (!may_join_any_seed(req.user.user_id))
 			error = "Please verify your mail address and enable notifications to join tournaments."
-		else if (!may_join_seed(req.user.user_id, seed_id))
-			error = "You may not register for this tournament."
+		else if (!may_join_seed(seed_id))
+			error = "This tournament is closed."
 		else
 			may_register = true
 	}
@@ -3015,10 +3037,12 @@ app.get("/tm/pool/:pool_name", function (req, res) {
 app.post("/api/tm/register/:seed_id", must_be_logged_in, function (req, res) {
 	let seed_id = req.params.seed_id | 0
 	let user_id = req.user.user_id
+	if (is_banned_from_tournaments(req.user.user_id))
+		return res.status(401).send("You may not join any tournaments.")
 	if (!may_join_any_seed(user_id))
 		return res.status(401).send("You may not join any tournaments right now.")
-	if (!may_join_seed(user_id, seed_id))
-		return res.status(401).send("You may not join this tournament.")
+	if (!may_join_seed(seed_id))
+		return res.status(401).send("This tournament is closed.")
 	TM_INSERT_QUEUE.run(user_id, seed_id, 1)
 	return res.redirect(req.headers.referer)
 })
diff --git a/views/tm_seed.pug b/views/tm_seed.pug
index 81574ca..9902f4f 100644
--- a/views/tm_seed.pug
+++ b/views/tm_seed.pug
@@ -48,7 +48,7 @@ html
 					else
 						td #{seed.round_count} sequential
 
-			if seed.is_open
+			if seed.is_open || queues.some(q => q.length > 0)
 				each queue,ix in queues
 					table.half
 						thead
@@ -66,21 +66,18 @@ html
 									td Nobody
 
 					if user
-						if ix === 0
-							if may_register
-								if !queue.find(p => p.user_id === user.user_id)
-									form(method="post" action="/api/tm/register/" + seed.seed_id)
-										button(type="submit") Register
-										button(disabled) Withdraw
-							else
-								div
-									button(disabled) Register
-									button(disabled) Withdraw
-
 						if queue.find(p => p.user_id === user.user_id)
 							form(method="post" action="/api/tm/withdraw/" + seed.seed_id + "/" + (ix+1))
 								button(disabled) Register
 								button(type="submit") Withdraw
+						else if ix === 0 && may_register
+							form(method="post" action="/api/tm/register/" + seed.seed_id)
+								button(type="submit") Register
+								button(disabled) Withdraw
+						else
+							div
+								button(disabled) Register
+								button(disabled) Withdraw
 
 						if user.user_id === 1
 							if queue.length >= seed.pool_size