diff options
| -rw-r--r-- | schema.sql | 37 | ||||
| -rw-r--r-- | server.js | 71 |
2 files changed, 24 insertions, 84 deletions
@@ -62,13 +62,6 @@ create table if not exists tokens ( time datetime ); -create table if not exists last_notified ( - game_id integer, - user_id integer, - time datetime, - primary key (game_id, user_id) -) without rowid; - create table if not exists webhooks ( user_id integer primary key, url text, @@ -525,32 +518,6 @@ create view time_control_view as and clock - (julianday() - julianday(mtime)) < 0 ; -drop view if exists your_turn_reminder; -create view your_turn_reminder as - select - game_id, role, user_id, name, mail, notify - from - games - join players using(game_id) - join users using(user_id) - where - status = 1 - and active in (role, 'Both') - and user_count > 1 - and julianday() > julianday(mtime, '+1 hour') - ; - -drop view if exists invite_reminder; -create view invite_reminder as - select - game_id, role, user_id, name, mail, notify - from - players - join users using(user_id) - where - is_invite = 1 - ; - -- Export game state as JSON drop view if exists game_export_view; @@ -633,6 +600,7 @@ end; drop trigger if exists trigger_accept_invite; create trigger trigger_accept_invite after update of is_invite on players + when old.is_invite and not new.is_invite begin update games @@ -684,7 +652,6 @@ begin delete from game_replay where game_id = old.game_id; delete from game_snap where game_id = old.game_id; delete from game_notes where game_id = old.game_id; - delete from last_notified where game_id = old.game_id; delete from unread_chats where game_id = old.game_id; end; @@ -698,7 +665,6 @@ begin delete from game_replay where game_id = old.game_id; delete from game_snap where game_id = old.game_id; delete from game_notes where game_id = old.game_id; - delete from last_notified where game_id = old.game_id; delete from unread_chats where game_id = old.game_id; delete from players where game_id = old.game_id; end; @@ -710,7 +676,6 @@ begin delete from tokens where user_id = old.user_id; delete from webhooks where user_id = old.user_id; delete from user_last_seen where user_id = old.user_id; - delete from last_notified 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 contacts where me = old.user_id or you = old.user_id; @@ -1978,7 +1978,7 @@ function do_join(res, game_id, role, user_id, user_name, is_invite) { if (!roles.includes(role)) return res.status(404).send("Invalid role.") } - let info = SQL_INSERT_PLAYER_ROLE.run(game_id, role, user_id, is_invite) + let info = SQL_INSERT_PLAYER_ROLE.run(game_id, role, user_id, is_invite ? 2 : 0) if (info.changes === 1) { update_join_clients(game_id) res.send("SUCCESS") @@ -2399,24 +2399,6 @@ function retry_webhook(user_id, webhook, message, retry) { * NOTIFICATIONS */ -const SQL_SELECT_NOTIFIED = SQL("SELECT julianday() < julianday(time, ?) FROM last_notified WHERE game_id=? AND user_id=?").pluck() -const SQL_INSERT_NOTIFIED = SQL("INSERT OR REPLACE INTO last_notified (game_id,user_id,time) VALUES (?,?,datetime())") -const SQL_DELETE_NOTIFIED = SQL("DELETE FROM last_notified WHERE game_id=? AND user_id=?") - -function delete_last_notified(user, game_id) { - SQL_DELETE_NOTIFIED.run(game_id, user.user_id) -} - -function insert_last_notified(user, game_id) { - SQL_INSERT_NOTIFIED.run(game_id, user.user_id) -} - -function should_send_reminder(user, game_id) { - if (!SQL_SELECT_NOTIFIED.get("+23 hours", game_id, user.user_id)) - return true - return false -} - function game_play_link(game_id, title_id, user) { return SITE_URL + play_url(title_id, game_id, user.role) } @@ -2463,9 +2445,6 @@ function send_play_notification(user, game_id, message) { send_notification(user, game_play_link(game_id, title_id, user), `${title_name} #${game_id} (${user.role}) - ${message}`) } -const QUERY_LIST_YOUR_TURN = SQL("SELECT * FROM your_turn_reminder") -const QUERY_LIST_INVITES = SQL("SELECT * FROM invite_reminder") - function send_chat_activity_notification(game_id, p) { send_play_notification(p, game_id, "Chat activity") } @@ -2480,14 +2459,8 @@ function send_your_turn_notification_to_offline_users(game_id, old_active, activ let p_was_active = old_active === p.role || old_active === "Both" let p_is_active = active === p.role || active === "Both" if (!p_was_active && p_is_active) { - if (!is_player_online(game_id, p.user_id)) { - insert_last_notified(p, game_id) + if (!is_player_online(game_id, p.user_id)) send_play_notification(p, game_id, "Your turn") - } else { - delete_last_notified(p, game_id) - } - } else { - delete_last_notified(p, game_id) } } } @@ -2497,7 +2470,6 @@ function send_game_started_notification_to_offline_users(game_id) { for (let p of players) { if (!is_player_online(game_id, p.user_id)) send_play_notification(p, game_id, "Started") - delete_last_notified(p, game_id) } } @@ -2506,33 +2478,36 @@ function send_game_finished_notification_to_offline_users(game_id, result) { for (let p of players) { if (!is_player_online(game_id, p.user_id)) send_play_notification(p, game_id, "Finished (" + result + ")") - delete_last_notified(p, game_id) } } -function notify_your_turn_reminder() { - for (let item of QUERY_LIST_YOUR_TURN.all()) { - if (should_send_reminder(item, item.game_id)) { - insert_last_notified(item, item.game_id) - send_play_notification(item, item.game_id, "Your turn") - } - } -} +const SQL_SELECT_INVITE_NOTIFY = SQL(` + select + game_id, role, user_id, name, mail, notify + from + games + join players using(game_id) + join users using(user_id) + where + status = 0 + and is_invite = 2 + and julianday(mtime) < julianday('now', '-30 seconds') +`) -function notify_invited_reminder() { - for (let item of QUERY_LIST_INVITES.all()) { - if (should_send_reminder(item, item.game_id)) { - insert_last_notified(item, item.game_id) +const SQL_UPDATE_INVITE_NOTIFY = SQL("update players set is_invite=1 where game_id=? and role=?") + +function invite_notify_ticker() { + for (let item of SQL_SELECT_INVITE_NOTIFY.all()) { + try { + SQL_UPDATE_INVITE_NOTIFY.run(item.game_id, item.role) send_join_notification(item, item.game_id, "You have an invitation") + } catch (err) { + console.log(err) } } } -// Send "you've been invited" notifications every 5 minutes. -setInterval(notify_invited_reminder, 5 * 60 * 1000) - -// Check and send daily your turn reminders every 17 minutes. -setInterval(notify_your_turn_reminder, 17 * 60 * 1000) +setInterval(invite_notify_ticker, 53 * 1000) const QUERY_READY_TO_START = SQL(` select |