Fix SQL query to get completed games
[vchess.git] / server / models / User.js
CommitLineData
c5c47010
BA
1const db = require("../utils/database");
2const genToken = require("../utils/tokenGenerator");
3const params = require("../config/parameters");
4const sendEmail = require('../utils/mailer');
8d7e2786
BA
5
6/*
7 * Structure:
8 * _id: integer
9 * name: varchar
10 * email: varchar
11 * loginToken: token on server only
12 * loginTime: datetime (validity)
13 * sessionToken: token in cookies for authentication
14 * notify: boolean (send email notifications for corr games)
83494c7f 15 * created: datetime
0234201f 16 * newsRead: datetime
8d7e2786
BA
17 */
18
0234201f
BA
19const UserModel = {
20 checkNameEmail: function(o) {
866842c3 21 return (
188b4a8f
BA
22 (!o.name || !!(o.name.match(/^[\w-]+$/))) &&
23 (!o.email || !!(o.email.match(/^[\w.+-]+@[\w.+-]+$/)))
866842c3 24 );
dac39588 25 },
98db2082 26
0234201f 27 create: function(name, email, notify, cb) {
dac39588 28 db.serialize(function() {
866842c3 29 const query =
dac39588
BA
30 "INSERT INTO Users " +
31 "(name, email, notify, created) VALUES " +
866842c3
BA
32 "('" + name + "','" + email + "'," + notify + "," + Date.now() + ")";
33 db.run(query, function(err) {
0234201f 34 cb(err, { id: this.lastID });
dac39588
BA
35 });
36 });
37 },
8d7e2786 38
866842c3 39 // Find one user by id, name, email, or token
0234201f 40 getOne: function(by, value, cb) {
dac39588
BA
41 const delimiter = (typeof value === "string" ? "'" : "");
42 db.serialize(function() {
43 const query =
44 "SELECT * " +
45 "FROM Users " +
46 "WHERE " + by + " = " + delimiter + value + delimiter;
47 db.get(query, cb);
48 });
49 },
8d7e2786 50
ed9c9c37
BA
51 getByIds: function(ids, cb) {
52 db.serialize(function() {
53 const query =
54 "SELECT id, name " +
1f49533d 55 "FROM Users " +
ed9c9c37
BA
56 "WHERE id IN (" + ids + ")";
57 db.all(query, cb);
58 });
1f49533d
BA
59 },
60
dac39588
BA
61 /////////
62 // MODIFY
8d7e2786 63
0234201f 64 setLoginToken: function(token, id) {
dac39588
BA
65 db.serialize(function() {
66 const query =
67 "UPDATE Users " +
866842c3 68 "SET loginToken = '" + token + "',loginTime = " + Date.now() + " " +
0234201f 69 "WHERE id = " + id;
866842c3 70 db.run(query);
dac39588
BA
71 });
72 },
8d7e2786 73
0234201f 74 setNewsRead: function(id) {
d9a7a1e4
BA
75 db.serialize(function() {
76 const query =
77 "UPDATE Users " +
78 "SET newsRead = " + Date.now() + " " +
0234201f 79 "WHERE id = " + id;
d9a7a1e4
BA
80 db.run(query);
81 });
82 },
83
dac39588
BA
84 // Set session token only if empty (first login)
85 // NOTE: weaker security (but avoid to re-login everywhere after each logout)
86 // TODO: option would be to reset all tokens periodically, e.g. every 3 months
0234201f 87 trySetSessionToken: function(id, cb) {
dac39588 88 db.serialize(function() {
866842c3 89 let query =
dac39588
BA
90 "SELECT sessionToken " +
91 "FROM Users " +
0234201f 92 "WHERE id = " + id;
866842c3 93 db.get(query, (err,ret) => {
dac39588 94 const token = ret.sessionToken || genToken(params.token.length);
866842c3 95 query =
dac39588 96 "UPDATE Users " +
866842c3 97 // Also empty the login token to invalidate future attempts
dac39588
BA
98 "SET loginToken = NULL" +
99 (!ret.sessionToken ? (", sessionToken = '" + token + "'") : "") + " " +
0234201f 100 "WHERE id = " + id;
866842c3
BA
101 db.run(query);
102 cb(token);
dac39588
BA
103 });
104 });
105 },
ab4f4bf2 106
0234201f 107 updateSettings: function(user) {
dac39588
BA
108 db.serialize(function() {
109 const query =
110 "UPDATE Users " +
111 "SET name = '" + user.name + "'" +
112 ", email = '" + user.email + "'" +
113 ", notify = " + user.notify + " " +
114 "WHERE id = " + user.id;
866842c3 115 db.run(query);
dac39588
BA
116 });
117 },
5d04793e
BA
118
119 /////////////////
120 // NOTIFICATIONS
121
0234201f 122 notify: function(user, message) {
fe4c7e67 123 const subject = "vchess.club - notification";
f53871db 124 const body = "Hello " + user.name + " !" + `
a749972c 125` + message;
fe4c7e67
BA
126 sendEmail(params.mail.noreply, user.email, subject, body);
127 },
128
0234201f 129 tryNotify: function(id, message) {
fe4c7e67 130 UserModel.getOne("id", id, (err,user) => {
0234201f 131 if (!err && user.notify) UserModel.notify(user, message);
2be5d614 132 });
83494c7f
BA
133 },
134
135 ////////////
136 // CLEANING
ab4f4bf2 137
0234201f 138 cleanUsersDb: function() {
83494c7f
BA
139 const tsNow = Date.now();
140 // 86400000 = 24 hours in milliseconds
141 const day = 86400000;
142 db.serialize(function() {
143 const query =
a97bdbda 144 "SELECT id, sessionToken, created, name, email " +
83494c7f
BA
145 "FROM Users";
146 db.all(query, (err, users) => {
0234201f 147 let toRemove = [];
83494c7f 148 users.forEach(u => {
0234201f 149 // Remove users unlogged for > 24h
83494c7f 150 if (!u.sessionToken && tsNow - u.created > day)
a97bdbda
BA
151 {
152 notify(
153 u,
154 "Your account has been deleted because " +
155 "you didn't log in for 24h after registration"
156 );
a97bdbda 157 }
83494c7f 158 });
0234201f
BA
159 if (toRemove.length > 0) {
160 db.run(
161 "DELETE FROM Users " +
162 "WHERE id IN (" + toRemove.join(",") + ")"
163 );
164 }
83494c7f
BA
165 });
166 });
167 },
168}
d431028c 169
ab4f4bf2 170module.exports = UserModel;