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