Source: app.js

/**
	@namespace app
	@since 23/10/2020
	@version 1.0
	@author Brennan Wilkes
*/
//Includes
const path = require("path");
const moment = require("moment");
const {server} = require(path.join(__dirname,"backend","server.js"));
const {database} = require(path.join(__dirname,"backend","database.js"));
//Setup
require('dotenv').config();
const ADMIN_USER = process.env.ADMIN_USER;
const ADMIN_PASS = process.env.ADMIN_PASS;
/**
	Validates a number to be inserted into MariaDB
	@param {number} n Number to validate
	@param {number} d Default value to return in the case of invalid input. Defaults to 0
	@returns {number} A number which is guaranteed to be valid. Returns d in n is invalid
	@memberof app
*/
const validateNum = (n,d=0) => (typeof(n)==="number" && !isNaN(n) ? n : d);
/**
	Creates a new promise and immediately rejects it
	@returns {Promise} Always rejected promise which resolves to "Invalid credentials"
	@memberof app
*/
const failedLogin = () => {
	return new Promise((res,rej)=> {
		rej("Invalid credentials");
	});
}
/**
	Search Query object to validate user input for SQL queries.
	Contains a list of all used parameters for all queries.
	User input is compared to this parameters, and validated.
	Data members of this object are guaranteed to be valid.
	Applies %% wrapping when appropriate.
	@class
	@memberof app
*/
const searchQuery = {
	/**
		Name (used mainly for drink name, but can also refer to ingredient names)
		@type {string}
	*/
	name : "",
	/**
		Array of all ingredients which must be required by a drink
		@type {string[]}
	*/
	contains: [""],
	/**
		User who ordered a drink
		@type {string}
	*/
	orderedBy: "",
	/**
		If a drink is on the rocks.
		"1" for ice.
		"0" for no ice.
		"" for either.
		@type {string}
	*/
	onIce: "",
	/**
		Method of mixing drink.
		Can be either "shaken" or "stirred".
		"" for either.
		@type {string}
	*/
	mixMethod: "",
	/**
		Minimum alcohol percentage.
		Defaults to 0.
		@type {number}
	*/
	percentage: 0,
	/**
		Minimum drink rating
		Defaults to 0.
		@type {number}
	*/
	rating: 0,
	/**
		Maximum drink price
		Defaults to 100.
		@type {number}
	*/
	price: 100,
	/**
		If a drink contains a sweet ingredient.
		"1" for sweet.
		"0" for savoury.
		"" for either or anything.
		@type {string}
	*/
	isSweet: "",
	/**
		If a drink contains a liquor or liquer.
		"1" for liquor.
		"0" for liquer.
		"" for either or anything.
		@type {string}
	*/
	liquor: "",
	/**
		ID of the glass drink must be drunk from
		"" for any glass.
		@type {string}
	*/
	glass: "",
	/**
		ID of the drink
		"" for any drink.
		@type {string}
	*/
	id: "",
	/**
		Username of person ordering drink.
		Defaults to Unknown Name
		@type {string}
	*/
	userName: "",
	/**
		Date of birth of person ordering drink.
		Defaults to Jan 1st 1970.
		@type {string}
	*/
	userDob: "1970-01-01",
	/**
		ID of the drink, but with different validation.
		Defaults to 0.
		@type {number}
	*/
	drinkId: 0,
	/**
		Resets all string datamembers to empty
	*/
	reset(){
		Object.keys(this).forEach(key => {
			if(typeof(this[key])=="string"){
				this[key] = "";
			}
		});
	},
	/**
		Adds all given parameters of params as datamembers
		In practice this updates datamembers with all recognizable attributes
		in the given object. This means that extra clutter will not be injected.
		@param {object} params Object of key/value pairs to record.
	*/
	update(params){
		this.reset();
		Object.keys(params).forEach(key => {
			this[key] = params[key];
		});
	},
	/**
		Where the magic happens. Iterates over all datamembers and applies both
		general and specific sanitzization. Inputs still must be injected via
		a prepared statement.
	*/
	sanitzize(){
		//Wraps all string type datamembers who are not userDob or userName in %'s.
		//This is for LIKE sql comparison.
		//Attributes glass and id will only be wrapped if their length is 0.
		Object.keys(this).forEach(key => {
			if(typeof(this[key])=="string" && key!=="userDob" && key!=="userName" ){
				this[key] = key==="glass"||key==="id" ? (this[key].length > 0 ? this[key].toLowerCase() : "%%" ) : `%${this[key].toLowerCase()}%`;
			}
		});
		this.contains = this.contains.map(c => `%${c.toLowerCase()}%`);
		//Resets contains array to defaults if invalid data is given.
		if(!this.contains || typeof(this.contains)!=="object" || !this.contains.length){
			this.contains = [""];
		}
		//Validates userDob as a correctly formatted date.
		this.userDob = moment(this.userDob, 'YYYY/MM/DD').isValid() ? moment(this.userDob,'YYYY-MM-DD').format("YYYY-MM-DD") : "1970-01-01";
		//Applies default value to userName
		this.userName = this.userName.length > 0 ? this.userName : "Unknown Name";
		//Number validation for numerical attributes.
		this.rating = validateNum(this.rating);
		this.percentage = validateNum(this.percentage);
		this.price = validateNum(this.price,100);
		this.drinkId = validateNum(this.drinkId,0);
	},
	/**
		Generates an appropriate ordered list of parameters for the advanced search query.
		@returns {string[]} Ordered list of SQL parameters
	*/
	getArgs(){
		let args = [
			searchQuery.mixMethod,
			searchQuery.onIce,
			searchQuery.name,
			searchQuery.rating,
			searchQuery.glass,
			searchQuery.price,
			...searchQuery.contains
		];
		if(searchQuery.percentage > 0 || searchQuery.liquor.length > 2){
			args.push(searchQuery.percentage);
			args.push(searchQuery.liquor);
		}
		if(searchQuery.orderedBy && searchQuery.orderedBy.length > 2){
			args.push(searchQuery.orderedBy);
		}
		if(searchQuery.isSweet.length > 2){
			args.push(searchQuery.isSweet);
		}
		return args;
	}
}
//Initialize DB connection
database.init();
//Initialize webserver
server.init();
//Get all drinks
//Projection query 1
server.route("drinks", req => database.get(`SELECT * FROM drinkRecipe LIMIT 100`));
//Advanced search
//Join query and division query
server.route("drinks/advanced", req => {
	//Record and sanitzize user inputs
	searchQuery.update(req.body);
	searchQuery.sanitzize();
	//Initial contains ingredient SQL codeblock.
	let containsSQL = `
		SELECT drinkRequires.drinkId FROM drinkRequires
		INNER JOIN ingredient
			ON drinkRequires.ingredientId=ingredient.id
		WHERE ingredient.name LIKE ?
	`;
	//Recursively wrap for each additional queried ingredient.
	searchQuery.contains.slice(1).forEach((c, i) => {
		containsSQL = ` SELECT drinkRequires.drinkId FROM drinkRequires
		INNER JOIN ingredient
			ON drinkRequires.ingredientId=ingredient.id
		WHERE drinkRequires.drinkId IN
			(${containsSQL})
			AND ingredient.name LIKE ? `;
	});
	//Wrap in a group
	containsSQL = `(${containsSQL})groupContains`;
	//Advanced multi parameters search
	return database.get(`
		SELECT DISTINCT drinkRecipe.* FROM drinkRecipe
		INNER JOIN (
			SELECT DISTINCT drinkRecipe.* FROM drinkRecipe
			INNER JOIN (
				SELECT drinkRequires.* FROM drinkRequires
				INNER JOIN (
					SELECT DISTINCT drinkRecipe.* FROM drinkRecipe
					WHERE mixMethod LIKE ?
					AND onIce LIKE ?
					AND name LIKE ?
					AND rating>=?
					AND glassID LIKE ?
					AND price<=?
				)group1
					ON drinkRequires.drinkId=group1.id
				INNER JOIN ingredient
					ON ingredientId=ingredient.id `+ (searchQuery.percentage > 0 || searchQuery.liquor.length > 2 ? `
					INNER JOIN alcohol
						ON ingredient.id=alcohol.id
					INNER JOIN alcoholType
						ON alcohol.percentage=alcoholType.percentage ` : ` `) + `
				INNER JOIN ${containsSQL}
					ON groupContains.drinkId=drinkRequires.drinkId
				` + (searchQuery.percentage > 0 || searchQuery.liquor.length > 2 ? `
					WHERE alcohol.percentage>=?
					AND alcoholType.liquor LIKE ?
					`: ` `) + `
			)group2
				ON group2.drinkId=drinkRecipe.id ` + (searchQuery.orderedBy && searchQuery.orderedBy.length > 2 ?
				`INNER JOIN transaction
					ON group2.drinkId=transaction.drinkId
				WHERE UPPER(transaction.customerName) LIKE UPPER(?)` :
					` `)+`
		)group3
			ON group3.id=drinkRecipe.id
			INNER JOIN drinkRequires
				ON drinkRequires.drinkId=drinkRecipe.id` + (searchQuery.isSweet.length > 2 ? `
			INNER JOIN ingredient
				ON ingredientId=ingredient.id
			INNER JOIN juice
				ON ingredient.id=juice.id
			INNER JOIN juiceFruit
				ON juice.fruitName=juiceFruit.fruitName
				AND juiceFruit.isSweet LIKE ? `: ` `) + " LIMIT 100",searchQuery.getArgs());
}, "post");
//Search by name or id
//Selection Query
server.route("drinks", req => {
	searchQuery.update(req.body);
	searchQuery.sanitzize();
	return database.get(`SELECT drinkRecipe.*,
		parent.name as versionOfName FROM drinkRecipe
		LEFT JOIN (SELECT drinkRecipe.* FROM drinkRecipe)parent
		ON drinkRecipe.versionOf=parent.id
		WHERE drinkRecipe.name LIKE ? AND drinkRecipe.id LIKE ?`,[searchQuery.name,searchQuery.id]);
}, "post");
//ingredients by quantity
//Selection Query 2
server.route("ingredients", req => {
	return database.get(`SELECT ingredient.* from ingredient INNER JOIN ingredientAvailable ON ingredient.quantity=ingredientAvailable.quantity WHERE ingredientAvailable.isAvailable=false`);
});
//ingredients by drink Id
//Selection Query 3
server.route("ingredients", req => {
	searchQuery.update(req.body);
	searchQuery.sanitzize();
	return database.get(`SELECT drinkRequires.quantity AS quantityInDrink, ingredient.*, alcohol.glassId, alcoholType.*, juiceFruit.* FROM
		(SELECT drinkRecipe.id FROM drinkRecipe WHERE drinkRecipe.id=?)queriedDrink
		INNER JOIN drinkRequires ON queriedDrink.id=drinkRequires.drinkId
		INNER JOIN ingredient ON drinkRequires.ingredientId=ingredient.id
		LEFT JOIN alcohol ON ingredient.id=alcohol.id
		LEFT JOIN juice ON ingredient.id=juice.id
		LEFT JOIN juiceFruit ON juice.fruitName=juiceFruit.fruitName
		LEFT JOIN alcoholType ON alcohol.percentage=alcoholType.percentage`,[searchQuery.id]);
}, "post");
//ingredient by ingredient Id
//Selection Query 4
server.route("ingredient", req => {
	searchQuery.update(req.body);
	searchQuery.sanitzize();
	return database.get(`
		SELECT ingredient.*, alcohol.glassId, glass.name as glassName, alcoholType.*, juiceFruit.* from ingredient
		LEFT JOIN alcohol ON ingredient.id=alcohol.id
		LEFT JOIN juice ON ingredient.id=juice.id
		LEFT JOIN juiceFruit ON juice.fruitName=juiceFruit.fruitName
		LEFT JOIN alcoholType ON alcohol.percentage=alcoholType.percentage
		LEFT JOIN glass ON alcohol.glassId=glass.id
		WHERE ingredient.id=?`,[searchQuery.id]);
}, "post");
//Number of drinks ordered by specific user
//Aggregation query 1
server.route("drinks/orderCount", req => {
	searchQuery.update(req.body);
	searchQuery.sanitzize();
	return database.get(`SELECT COUNT(transaction.id) FROM transaction WHERE UPPER(transaction.customerName) UPPER(?)`,[searchQuery.orderedBy]);
},"post");
//Most ordered drink
//Aggregation query 2
server.route("popular/drinks", req => {
	return database.get(`SELECT drinkRecipe.* FROM (SELECT transaction.drinkId FROM transaction GROUP BY drinkId ORDER BY COUNT(drinkId) DESC LIMIT 10)popular INNER JOIN drinkRecipe ON drinkId=id`);
});
//Most ordered ingredient
//Nested Aggregation query
server.route("popular/ingredients", req => {
	return database.get(`SELECT ingredient.* FROM (SELECT drinkRequires.ingredientId FROM transaction INNER JOIN drinkRequires ON transaction.drinkId=drinkRequires.drinkId GROUP BY drinkRequires.ingredientId ORDER BY COUNT(drinkRequires.ingredientId) DESC LIMIT 25)popular INNER JOIN ingredient ON popular.ingredientId=ingredient.id`);
});
//Return list of glasses
//Projection Query 2
server.route("glasses", req => database.get(`SELECT * FROM glass`));
server.route("orders", req => database.get(`
	SELECT transaction.*, drinkRecipe.name, drinkRecipe.price
	FROM transaction INNER JOIN drinkRecipe
	ON transaction.drinkId=drinkRecipe.id
	ORDER BY transaction.date DESC`));
//Order ingredient by ingredient Id
//UPDATE query 1
server.route("order", req => {
	if(req.body.userName === ADMIN_USER && req.body.userPass === ADMIN_PASS){
		searchQuery.update(req.body);
		searchQuery.sanitzize();
		return database.get(`UPDATE ingredient SET ingredient.quantity=ingredient.quantity+10 WHERE ingredient.id=?`,[searchQuery.id]);
	}
	else{
		return failedLogin();
	}
}, "post");
//Delete ingredient by ingredient Id
//DELETE query 1
server.route("delete/ingredient", req => {
	if(req.body.userName === ADMIN_USER && req.body.userPass === ADMIN_PASS){
		searchQuery.update(req.body);
		searchQuery.sanitzize();
		return database.get(`DELETE FROM ingredient WHERE ingredient.id=?`,[searchQuery.id]);
	}
	else{
		return failedLogin();
	}
}, "post");
//Delete drink drinkRecipe by drinkRecipe Id
//DELETE query 2
server.route("delete/drink", req => {
	if(req.body.userName === ADMIN_USER && req.body.userPass === ADMIN_PASS){
		searchQuery.update(req.body);
		searchQuery.sanitzize();
		return database.get(`DELETE FROM drinkRecipe WHERE drinkRecipe.id=?`,[searchQuery.id]);
	}
	else{
		return failedLogin();
	}
}, "post");
//Add a new customer
//INSERT / UPDATE query 3
server.route("customer", req => {
	searchQuery.update(req.body);
	searchQuery.sanitzize();
	return database.insert("customer",{fullName:searchQuery.userName,dateOfBirth:searchQuery.userDob},true);
}, "post");
//Order a drink
//INSERT query / UPDATE query 3
server.route("purchase", req => {
	searchQuery.update(req.body);
	searchQuery.sanitzize();
	//Generate a custom Promise event
	return new Promise((resolve,reject)=> {
		//Get a list of ingredient of the requested drink
		database.get(`
			SELECT drinkRequires.drinkId, drinkRequires.ingredientId, drinkRequires.quantity as quantityInDrink, ingredientAvailable.* FROM drinkRequires
			INNER JOIN ingredient ON drinkRequires.ingredientId=ingredient.id
			INNER JOIN ingredientAvailable ON ingredient.quantity=ingredientAvailable.quantity
			WHERE drinkRequires.drinkId=?`,[searchQuery.drinkId])
		.then(getRes => {
			//Determine if all ingredients are available.
			if(getRes.reduce((status,nextIngr) => status * nextIngr.isAvailable,1)){
				//Insert the new transaction record
				database.insert("transaction",{
					date:moment().format("YYYY-MM-DD"),
					drinkId:searchQuery.drinkId,
					customerName:searchQuery.userName})
				.then(orderRes => {
					//Recursively decrement database quantity of all ingredients
					let sqlPromises = []
					let sqlQuery;
					getRes.forEach((ingr, i) => {
						sqlQuery = database.get(
							`UPDATE ingredient SET ingredient.quantity=? WHERE ingredient.id=?`,[
								Math.max(0,parseInt(Math.ceil(ingr.quantity)) - parseInt(Math.ceil(ingr.quantityInDrink))),
								ingr.ingredientId]);
						sqlPromises.push(sqlQuery);
					});
					//On resolution of all queries, resolve to the final data
					//On errors anywhere along the chain, reject the Promise with the error details
					Promise.all(sqlPromises).then(res => {
						resolve(orderRes);
					}).catch( err => {
						reject(err);
					});
				}).catch(err => {
					reject(err);
				});
			}
			//Return 409
			else{
				reject("Drink not available");
			}
		}).catch(err=>{
			reject(err);
		});
	});
}, "post");
//Start the webserver
server.start();