From 00f2759e16ec73fa1ecd0254a9c9018530d71892 Mon Sep 17 00:00:00 2001
From: Benjamin Auder <benjamin.auder@somewhere>
Date: Fri, 18 Jan 2019 13:11:32 +0100
Subject: [PATCH] Draft Game and Challenge models

---
 _tmp/TODO                                     |   6 +-
 db/create.sql                                 |   4 +-
 models/Challenge.js                           | 133 +++++++++---------
 models/Game.js                                | 106 +++++++++++++-
 models/Problem.js                             |   3 -
 .../javascripts/components/correspondance.js  |   4 +-
 routes/all.js                                 |   2 +-
 routes/{playing.js => games.js}               |   3 +-
 8 files changed, 185 insertions(+), 76 deletions(-)
 rename routes/{playing.js => games.js} (98%)

diff --git a/_tmp/TODO b/_tmp/TODO
index fcc2b42b..868d4d3e 100644
--- a/_tmp/TODO
+++ b/_tmp/TODO
@@ -1,5 +1,7 @@
-Après ajout d'un problème, passer display à "mine"
-Résoudre le pb "no more problems" when navigating after loading one pb
+Use better-sqlite3 instead of node-sqlite3:
+https://www.npmjs.com/package/better-sqlite3
+
+CRON task remove unlogged users, finished corr games after 7 days, individual challenges older than 7 days
 
 tell opponent that I got the move, for him to start timer (and lose...)
   --> no, not needed and impossible if everybody is offline
diff --git a/db/create.sql b/db/create.sql
index 5f463bfc..75e1b1cd 100644
--- a/db/create.sql
+++ b/db/create.sql
@@ -36,6 +36,7 @@ create table Challenges (
 	added datetime,
 	uid integer,
 	vid integer,
+	nbPlayers integer,
 	foreign key (uid) references Users(id),
 	foreign key (vid) references Variants(id)
 );
@@ -44,7 +45,6 @@ create table Challenges (
 create table WillPlay (
 	cid integer,
 	uid integer,
-	yes boolean,
 	foreign key (cid) references Challenges(id),
 	foreign key (uid) references Users(id)
 );
@@ -53,7 +53,7 @@ create table Games (
 	id integer primary key,
 	vid integer,
 	fen varchar, --initial position
-	score varchar,
+	score varchar default '*',
 	foreign key (vid) references Variants(id)
 );
 
diff --git a/models/Challenge.js b/models/Challenge.js
index 9980921c..adb40224 100644
--- a/models/Challenge.js
+++ b/models/Challenge.js
@@ -1,79 +1,82 @@
 var db = require("../utils/database");
 
 /*
- * Structure:
- *   _id: BSON id
- *   vid: variant ID
- *   from: player ID
- *   to: player ID, undefined if automatch
+ * Structure table Challenges:
+ *   id: integer
+ *   added: datetime
+ *   uid: user id (int)
+ *   vid: variant id (int)
+ *   nbPlayers: integer
+ *
+ * Structure table WillPlay:
+ *   cid: ref challenge id
+ *   uid: ref user id
  */
 
-exports.create = function(vid, from, to, callback)
+exports.create = function(uid, vid, nbPlayers, cb)
 {
-	let chall = {
-		"vid": vid,
-		"from": from
-	};
-	if (!!to)
-		chall.to = to;
-	db.challenges.insert(chall, callback);
-}
-
-//////////
-// GETTERS
-
-exports.getById = function(cid, callback)
-{
-	db.challenges.findOne({_id: cid}, callback);
-}
-
-// For index page: obtain challenges that the player can accept
-exports.getByPlayer = function(uid, callback)
-{
-	db.challenges.aggregate(
-		{$match: {$or: [
-			{"to": uid},
-			{$and: [{"from": {$ne: uid}}, {"to": {$exists: false}}]}
-		]}},
-		{$project: {_id:0, vid:1}},
-		{$group: {_id:"$vid", count:{$sum:1}}},
-		callback);
-}
-
-// For variant page (challenges related to a player)
-exports.getByVariant = function(uid, vid, callback)
-{
-	db.challenges.find({$and: [
-		{"vid": vid},
-		{$or: [
-			{"to": uid},
-			{"from": uid},
-			{"to": {$exists: false}},
-		]}
-	]}, callback);
+	db.serialize({
+		let query =
+			"INSERT INTO Challenges (added, uid, vid, nbPlayers) " +
+			"VALUES (" + Date.now() + "," + uid + "," + vid + "," + nbPlayers + ")";
+		db.run(insertQuery, err => {
+			if (!!err)
+				return cb(err);
+			db.get("SELECT last_insert_rowid() AS rowid", (err2,lastId) => {
+				query =
+					"INSERT INTO WillPlay VALUES " +
+					"(" + lastId["rowid"] + "," + uid + ")";
+					db.run(query, cb);
+				});
+			});
+		});
+	});
 }
 
-//////////
-// REMOVAL
-
-exports.remove = function(cid, callback)
+exports.getOne = function(id, cb)
 {
-	db.challenges.remove({_id: cid}, callback);
+	db.serialize(function() {
+		let query =
+			"SELECT * " +
+			"FROM Challenges c " +
+			"JOIN Variants v " +
+			"  ON c.vid = v.id "
+			"WHERE id = " + id;
+		db.get(query, (err,challengeInfo) => {
+			if (!!err)
+				return cb(err);
+			query =
+				"SELECT w.uid AS id, u.name " +
+				"FROM WillPlay w " +
+				"JOIN Users u " +
+				"  ON w.uid = u.id " +
+				"WHERE w.cid = " + id;
+			db.run(query, (err2,players) => {
+				if (!!err2)
+					return cb(err2);
+				const challenge = {
+					id: id,
+					vname: challengeInfo.name,
+					added: challengeInfo.added,
+					nbPlayers: challengeInfo.nbPlayers,
+					players: players, //currently in
+				};
+				return cb(null, challenge);
+			});
+		});
+	});
 }
 
-// Remove challenges older than 1 month, and 1to1 older than 36h
-exports.removeOld = function()
+exports.remove = function(id)
 {
-	var tsNow = new Date().getTime();
-	// 86400000 = 24 hours in milliseconds
-	var day = 86400000;
-	db.challenges.find({}, (err,challengeArray) => {
-		challengeArray.forEach( c => {
-			if (c._id.getTimestamp() + 30*day < tsNow //automatch
-				|| (!!c.to && c._id.getTimestamp() + 1.5*day < tsNow)) //1 to 1
-			{
-				db.challenges.remove({"_id": c._id});
-			}
-		});
+	db.parallelize(function() {
+		let query =
+			"DELETE FROM Challenges " +
+			"WHERE id = " + id;
+		db.run(query);
+		query =
+			"DELETE FROM WillPlay " +
+			"WHERE cid = " + id;
+		db.run(query);
 	});
 }
diff --git a/models/Game.js b/models/Game.js
index d279514f..72f1ea89 100644
--- a/models/Game.js
+++ b/models/Game.js
@@ -1 +1,105 @@
-//TODO:
+var db = require("../utils/database");
+
+/*
+ * Structure table Games:
+ *   id: game id (int)
+ *   vid: integer (variant id)
+ *   fen: varchar (initial position)
+ *   mainTime: integer
+ *   increment: integer
+ *   score: varchar (result)
+ *
+ * Structure table Players:
+ *   gid: ref game id
+ *   uid: ref user id
+ *   color: character
+ *
+ * Structure table Moves:
+ *   move: varchar (description)
+ *   played: datetime
+ *   idx: integer
+ *   color: character
+ */
+
+exports.create = function(vid, fen, mainTime, increment, players, cb)
+{
+	db.serialize({
+		let query =
+			"INSERT INTO Games (vid, fen, mainTime, increment) " +
+			"VALUES (" + vid + ",'" + fen + "'," + mainTime + "," + increment + ")";
+		db.run(insertQuery, err => {
+			if (!!err)
+				return cb(err);
+			db.get("SELECT last_insert_rowid() AS rowid", (err2,lastId) => {
+				players.forEach(p => {
+					query =
+						"INSERT INTO Players VALUES " +
+						"(" + lastId["rowid"] + "," + p.id + "," + p.color + ")";
+					db.run(query, cb);
+				});
+			});
+		});
+	});
+}
+
+// TODO: queries here could be async, and wait for all to complete
+exports.getOne = function(id, cb)
+{
+	db.serialize(function() {
+		let query =
+			"SELECT v.name AS vname, g.fen, g.score " +
+			"FROM Games g " +
+			"JOIN Variants v " +
+			"  ON g.vid = v.id "
+			"WHERE id = " + id;
+		db.get(query, (err,gameInfo) => {
+			if (!!err)
+				return cb(err);
+			query =
+				"SELECT p.uid AS id, p.color, u.name " +
+				"FROM Players p " +
+				"JOIN Users u " +
+				"  ON p.uid = u.id " +
+				"WHERE p.gid = " + id;
+			db.run(query, (err2,players) => {
+				if (!!err2)
+					return cb(err2);
+				query =
+					"SELECT move AS desc, played, idx, color " +
+					"FROM Moves " +
+					"WHERE gid = " + id;
+				db.run(query, (err3,moves) => {
+					if (!!err3)
+						return cb(err3);
+					const game = {
+						id: id,
+						vname: gameInfo.vname,
+						fen: gameInfo.fen,
+						score: gameInfo.score,
+						players: players,
+						moves: moves,
+					};
+					return cb(null, game);
+				});
+			});
+		});
+	});
+}
+
+exports.remove = function(id)
+{
+	db.parallelize(function() {
+		let query =
+			"DELETE FROM Games " +
+			"WHERE id = " + id;
+		db.run(query);
+		query =
+			"DELETE FROM Players " +
+			"WHERE gid = " + id;
+		db.run(query);
+		query =
+			"DELETE FROM Moves " +
+			"WHERE gid = " + id;
+		db.run(query);
+	});
+}
diff --git a/models/Problem.js b/models/Problem.js
index 99e5b620..8f3a302c 100644
--- a/models/Problem.js
+++ b/models/Problem.js
@@ -21,9 +21,6 @@ exports.create = function(uid, vid, fen, instructions, solution, cb)
 				return cb(err);
 			db.get("SELECT last_insert_rowid() AS rowid", cb);
 		});
-//		const stmt = db.prepare(query);
-//		stmt.run(instructions, solution);
-//		stmt.finalize();
 	});
 }
 
diff --git a/public/javascripts/components/correspondance.js b/public/javascripts/components/correspondance.js
index 47a37757..87de3eb8 100644
--- a/public/javascripts/components/correspondance.js
+++ b/public/javascripts/components/correspondance.js
@@ -1,9 +1,11 @@
 Vue.component("my-correspondance", {
-	//TODO
 	template: `
 		<div class="col-sm-12 col-md-10 col-md-offset-1 col-lg-8 col-lg-offset-2">
 			<p>TODO: load from server, show timeControl + players + link "play"</p>
 			<p>Also tab for current challenges + button "new game"</p>
 		</div>
 	`,
+	created: function() {
+		//TODO
+	},
 });
diff --git a/routes/all.js b/routes/all.js
index 0989b3fb..85815a3b 100644
--- a/routes/all.js
+++ b/routes/all.js
@@ -3,7 +3,7 @@ var router = require("express").Router();
 router.use("/", require("./index"));
 router.use("/", require("./users"));
 router.use("/", require("./messages"));
-//router.use("/", require("./playing"));
+//router.use("/", require("./games"));
 //router.use("/", require("./challenge"));
 router.use("/", require("./problems"));
 router.use("/", require("./variant"));
diff --git a/routes/playing.js b/routes/games.js
similarity index 98%
rename from routes/playing.js
rename to routes/games.js
index 3bdfa35c..808c258c 100644
--- a/routes/playing.js
+++ b/routes/games.js
@@ -92,7 +92,8 @@ router.get("/gamesbyplayer", access.logged, access.ajax, (req,res) => {
 	});
 });
 
-// TODO: if newmove fail, takeback in GUI // TODO: check move structure
+// TODO: if newmove fail, takeback in GUI
+// TODO: check move structure
 // TODO: for corr games, move should contain an optional "message" field ("corr chat" !)
 router.post("/moves", access.logged, access.ajax, (req,res) => {
 	let gid = ObjectId(req.body.gid);
-- 
2.44.0