// Import des modules nécessaires
const WebSocketServer = require('ws');
const crypto = require('crypto');
const mariadb = require('mariadb');
const moment = require('moment');
const { exec } = require('child_process');
const { execSync } = require('child_process');

// Déclaration des variables globales
const nodeID	= 'NODE';
const traceLevel = 0;
const rootDir	= __dirname.substring(0, __dirname.lastIndexOf("\\"));

// Création de la connexion à la base MariaDBtrue
const pool = mariadb.createPool({
	host: 'localhost',
	port: 3306,
	user:'splendor', 
	password: '7uj5ktja',
	database: 'splendor',
	bigIntAsNumber: true,
	decimalAsNumber: true,
	bigIntAsNumber: true
});

// Création du serveur WebSocket
const wss = new WebSocketServer.Server({ port: 8000 })
trace('> Node_WS en service sur le port 8000', 1);

// Creating connection using websocket
wss.on("connection", ws => {
	trace('Nouvelle connexion entrante', 1);
	// Association d'un ID unique à cette connexion
	ws.id = crypto.randomUUID();
	ws.name = 'inconnu';

	//on message from client
	ws.onmessage = (event) => {
		messageProcessing(ws, event);
	}

	// handling what to do when clients disconnects from server
	ws.onclose = () => {
		trace(`Clôture connexion WebClient ${ws.name}`, 1);
		if(ws.name!='inconnu') sendMessageByID('Remove player', {'login':ws.name}, ws.id, false);
		sqlUpdate('player', {'status':''}, `login="${ws.name}"`)
	};
	// handling client connection error
	ws.onerror = function () {
		console.error(" !!! Erreur de connexion client")
	}
});

/* ===============================================================
		TRAITEMENT DES MESSAGES ENTRANTS
	===============================================================	*/
async function messageProcessing(socket, event) {
	trace(`Réception message : ${event.data}`, 1);
	var player = {};

	if(isJsonString(event.data)) {
		// Données message sous forme JSON
		const message = JSON.parse(event.data);
		const sender = (message.from) ? message.from : 'unknown';
		const notification = message.notif;
		switch(notification.toLowerCase()) {
			case 'stop node':
				trace('Admin - Stop Node', 1);
				if(sender=="admin") process.exit();
				break;
			case 'flush games':
				trace('Admin - Flush Games', 1);
				// Vidage des tables des jeux
				sqlQuery('TRUNCATE g_privilege');
				sqlQuery('TRUNCATE g_card');
				sqlQuery('TRUNCATE g_token');
				sqlQuery('TRUNCATE game');
				// Libération de tous les clients connectés
				wss.clients.forEach( client => {
					if(client.name!='unknown') sqlUpdate('player', {'status':'available'}, `login='${client.name}'`);
				});
				// Broadcast Game Error
				sendMessageByName('Game error', null);
				break;
			// ----------------- WELCOME -------------------
			case 'login':
				// Recherche du joueur dans la base
				player = await getPlayerDataByLogin(sender);
				if(player) {
					socket.name = sender;
					await sqlUpdate('player', {'status':'available'}, `login="${player.login}"`);
					sendMessageByName('Welcome', '', player.login);
				} else {
					sendMessageByID('Unknown', '', socket.id);
				}
				break;
			case 'subscribe':
				break;
			// --------------- WAITING ROOM -------------------
			case 'hello':
				// On s'assure que la Socket porte bien le nom de l'emetteur
				if(sender!='unknown') {
					socket.name = sender;
					player = await getPlayerDataByLogin(sender);
					await sqlUpdate('player', {'status':'available'}, `login="${player.login}"`);
				}
				// Parcours des joueurs en ligne
				wss.clients.forEach( async client => {
					if(client.name!='unknown') {
						player = await getPlayerDataByLogin(client.name);
						if(player.login==sender) {
							sendMessageByID('Add player', player);
						} else {
							sendMessageByName('Add player', player, sender);
						}
					};
				});
				break;
			case 'request to play':
				player = await getPlayerDataByLogin(message.data);
				if(player) {
					if(player.status=='available') {
						await sqlUpdate('player', {'status':'invited'}, `login="${player.login}"`);
						player.status = 'invited';
						sendMessageByID('Update player', player)
						sendMessageByName('requested to play', sender, player.login);
					} else {
						sendMessageByName('Update player', player, sender);
					}
				}
				break;
			case 'request denied':
				player = await getPlayerDataByLogin(sender);
				if(player) {
					if(player.status=='invited') {
						await sqlUpdate('player', {'status':'available'}, `login="${sender}"`);
						player.status = 'available';
						sendMessageByID('Update player', player);
						sendMessageByName('Request rejected from', sender, message.data);
						sendMessageByName('Update player', {'login':message.data, 'status':'available'}, sender)
					} else {
						sendMessageByID('Update player', player, socket.id);
					}
				}
				break;
			case 'request accepted':
				//requestToPlayAccepted(sender, message.data)
				var playerA = await getPlayerDataByLogin(message.data);
				var playerB = await getPlayerDataByLogin(sender);
				if((playerA.status=='available' || playerA.status=='invited') && playerB.status=='invited') {
					// Initialisation ou reprise de la partie
					const gameID = await initGame(playerA.id, playerB.id);
					// Mise à jour du statut des joueurs
					await sqlUpdate('player', {'status':'in game', 'gameID':gameID}, `login="${playerA.login}"`);
					playerA.status='in game'; playerA.gameID = gameID;
					sendMessageByID('Update player', playerA);
					await sqlUpdate('player', {'status':'in game', 'gameID':gameID}, `login="${playerB.login}"`);
					playerB.status='in game'; playerB.gameID = gameID;
					sendMessageByID('Update player', playerB);
					// Ouverture du plateau de jeu
					sendMessageToPlayers(gameID, 'Game Ready', gameID);
				}

				break;
			case 'resume game':
				break;
			case 'ban':
				break;
			// --------------- BOARD GAME -------------------
			case 'in game':
				// On s'assure que la Socket porte bien le nom de l'emetteur
				if(sender!='unknown') socket.name = sender;
				// Le joueur rejoint la partie
				playerJoinGame(sender, message.data.gameID);
				break;
			case 'update data':
				// Mise à jour de données de jeu
				updateData(sender, message.data);
				break;
			case 'info':
				sendMessageByName('info', message.data, message.data.opponent);
				break;
			default:
				trace(" !!! Réception d'un message inconnu", 0, true);
		}
	} else {
		trace(' !!! Message reçu pas au format JSON', 0, true);
	}
}

/* ===============================================================
		ENVOI DES MESSAGES
	===============================================================	*/
function sendMessageByID(notification, data=null, recipientID=null, allClient=true) {
	var message = {};
	message['from'] = nodeID;
	message['notif']	= notification;
	if(data) message['data'] = data;
	trace('Préparation message:', 3);
	trace(message, 3);
	wss.clients.forEach( client => {
		if(!recipientID || recipientID && allClient && client.id==recipientID || recipientID && !allClient && client.id!=recipientID) {
			trace(`... envoyé à '${client.name}' (by ID)`, 10);
			client.send(JSON.stringify(message));
		}
	});
}
function sendMessageByName(notification, data=null, recipientName=null, allClient=true) {
	var message = {};
	message['from'] = nodeID;
	message['notif']	= notification;
	if(data) message['data'] = data;
	trace('Préparation message:', 3);
	trace(message, 3);
	wss.clients.forEach( client => {
		if(!recipientName || recipientName && allClient && client.name==recipientName || recipientName && !allClient && client.name!=recipientName) {
			trace(`... envoyé à '${client.name}' (by Name)`, 10);
			client.send(JSON.stringify(message));
		}
	});
}

async function sendMessageToPlayers(gameID, notification, data=null) {
	const game = await getGameData(gameID);
	if(game) {
		var message = {};
		message['from'] = nodeID;
		message['notif']	= notification;
		if(data) message['data'] = data;
		trace(`Préparation message '${JSON.stringify(message)}'`, 9);
		wss.clients.forEach( client => {
			if(client.name==game.loginA || client.name==game.loginB) {
				trace(`... envoyé à '${client.name}' (by ID)`, 10);
				client.send(JSON.stringify(message));
			}
		});
	}
}

/* ===============================================================
		GESTION REQUETES SQL
	===============================================================	*/
// Exécution d'une requête générique
async function sqlQuery(query) {
	let cnxDB;
	try {
   	cnxDB = await pool.getConnection();
		trace(`SQL request : ${query}`, 9);
   	const rows = await cnxDB.query(query);
		return rows;
	} catch (err) {
		trace(err, 0, true);
   	throw err;
	} finally {
   	if(cnxDB) cnxDB.release(); // Libère la connexion
	}
}

// Requête de d'insertion 
async function sqlInsert(table, data) {
	let cnxDB;
	try {
		cnxDB = await pool.getConnection(); 
		const champs = Object.keys(data).join(',');
		const valeurs = Object.values(data).map(val => cnxDB.escape(val)).join(',');
		const query = `INSERT INTO ${table} (${champs}) VALUES (${valeurs})`;
		trace(`SQL request : ${query}`, 9);
		const result = await cnxDB.query(`INSERT INTO ${table} (${champs}) VALUES (${valeurs})`);
		return parseInt(result.insertId, 10);
	} catch (err) {
		trace(err, 0, true);
		throw err;
	} finally {
		if(cnxDB) cnxDB.release(); // Libère la connexion
	}
}
 
 // Requête d'Update
 async function sqlUpdate(table, data, filter) {
	let cnxDB;
	try {
		cnxDB = await pool.getConnection();
		// Création de l'ensemble des tuples à modifier
		var values = '';
		for(const field in data){
			if(data.hasOwnProperty(field)) {
				values += `${field}=`+cnxDB.escape(data[field])+`, `;
			}
		}
		// Suppression de la dernière virgule
		values = values.slice(0, -2);
 		const query = `UPDATE ${table} SET ${values} WHERE ${filter}`
		trace(`SQL request : ${query}`, 9);
		await cnxDB.query(query);
		return true
	} catch (err) {
		trace(err, 0, true);
		throw err;
	} finally {
		if (cnxDB) cnxDB.release(); // Libère la connexion
	}
 }
 
/* ===============================================================
		PROCEDURES DIVERSES
	===============================================================	*/
// Test si une chaine est interprétable en tant que JSON
function isJsonString(str) {
	try {
		JSON.parse(str);
	} catch (e) {
		return false;
	}
	return true;
}

// Créé une table contenant une série mélangée
function getRandomSeries(size) {
	var series=[];
	for(let i=1;  i<=size; i++) series.push(i);
	randomize(series);
	return series;
}

// Mélange une table
function randomize(tab) {
	var i, j, tmp;
	for (i = tab.length - 1; i > 0; i--) {
		 j = Math.floor(Math.random() * (i + 1));
		 tmp = tab[i];
		 tab[i] = tab[j];
		 tab[j] = tmp;
	}
	return tab;
}

function schuffle(tab) {
	var i, j, tmp;
	for (i = tab.length - 1; i > 0; i--) {
		 j = Math.floor(Math.random() * (i + 1));
		 tmp = tab[i];
		 tab[i] = tab[j];
		 tab[j] = tmp;
	}
	return tab;
}

function trace(msg, level=1, error=false) {
	if(level<=traceLevel) {
		let datenow = new Date().toLocaleDateString();
		let heurenow = new Date().toLocaleTimeString();
		let now = datenow+' '+heurenow;
		if(!error) {
			console.log(now+' : '+msg);
		} else {
			console.error(now+' : '+msg);
		}
	}
}

/* ===============================================================
		GESTION DU JEU
	===============================================================	*/
async function getGameData(gameID) {
	trace(`Retreive Game Data for gameID #${gameID}`, 1);
	const data = await sqlQuery(`SELECT	game.id, game.status, game.round, game.step, game.playerTurn, playerA.login AS loginA, playerB.login AS loginB,
		CASE game.playerTurn	WHEN 'A' THEN playerA.login ELSE playerB.login END AS playerLogin,
		CASE game.playerTurn WHEN 'A' THEN playerB.login ELSE playerA.login END AS opponentLogin
		FROM	game JOIN player playerA ON game.playerA_ID=playerA.id JOIN	player playerB ON game.playerB_ID=playerB.id WHERE	game.id = ${gameID}`);
	if(data.length==1) {
		return data[0];
	} else {
		return false
	}
}

async function getGameTokens(gameID) {
	trace(`Retreive Game Tokens for gameID #${gameID}`, 1);
	const tokens = await sqlQuery(`SELECT * FROM token, g_token WHERE g_token.tokenID=token.id AND g_token.gameID=${gameID} ORDER BY slot, rank`);
	return tokens;
}

async function getGameCards(gameID) {
	trace(`Retreive Game Cards for gameID #${gameID}`, 1);
	const cards = await sqlQuery(`SELECT * FROM card, g_card WHERE g_card.cardID=card.id AND g_card.gameID=${gameID} ORDER BY slot, rank`);
	return cards;
}

async function getGamePrivileges(gameID) {
	trace(`Retreive Game Privilege for gameID #${gameID}`, 1);
	const privileges = await sqlQuery(`SELECT * FROM g_privilege WHERE  g_privilege.gameID=${gameID} ORDER BY slot, rank`);
	return privileges;
}

async function getFullGame(gameID) {
	trace(`Get Game full data for gameID#${gameID}`, 1);
	var game = {};
	game['data']		 = await getGameData(gameID);
	game['tokens']		 = await getGameTokens(gameID);
	game['cards']		 = await getGameCards(gameID);
	game['privileges'] = await getGamePrivileges(gameID);
	return game;
}

async function getPlayerDataByLogin(login) {
	trace(`Get player data for ${login}`, 1);
	const players = await sqlQuery(`SELECT id, login, gameCount, score, status, gameID FROM player WHERE login="${login}"`);
	if(players.length==1) {
		var player = players[0];
		trace(`Player data for ${login} : ${JSON.stringify(player)}`, 8);
		return player;
	} else {
		return false
	}
}

async function getPlayerGameDataByLogin(login) {
	trace(`Get Player Game Data for ${login}`, 1);
	const players = await sqlQuery(`SELECT player.id, player.login, player.status, player.gameID, game.playerTurn AS gameTurn,	IF(player.id=game.playerA_ID,playerB.login,playerA.login) AS opponent FROM game JOIN player playerA ON game.playerA_ID=playerA.id JOIN player playerB ON game.playerB_ID=playerB.id LEFT JOIN player ON game.id=player.gameID WHERE player.login='${login}'`);
	if(players.length==1) return players[0]; else return false
}

// Initialisation
async function initGame(playerA_ID, playerB_ID) {
	trace('Initialize or retreive game', 1);
	const games = await sqlQuery(`SELECT * FROM game WHERE status<>'terminated' AND ((playerA_ID=${playerA_ID} AND playerB_ID=${playerB_ID}) OR (playerA_ID=${playerB_ID} AND playerB_ID=${playerA_ID}))`)
	if(games.length>0) {
		trace(`gameID#${games[0]} retreived`, 1);
		return games[0].id;
	}

	// Tirage au sort du joueur
	const playerTurn = (Math.random()<0.5)? 'A' : 'B';
	const opponentSlot = (playerTurn==='A')? 'B' : 'A';
	//Création de la partie
	const gameID = await sqlInsert('game', {'dateTime':moment().format('YYYY-MM-DD hh:mm:ss'), 'playerA_ID':playerA_ID, 'playerB_ID':playerB_ID, 'status':'init', 'round':1, 'step':'', 'playerTurn':playerTurn});
	// Chargement des caractéristiques des Cartes
	const cards = await sqlQuery('SELECT * FROM card');

	// Création et mélange des cartes N1
	random = getRandomSeries(30);
	for(let i=0;  i<=29; i++) {
		var cardID = i+1;
		var card = cards.find(card => card.id===cardID)
		await sqlInsert('g_card', {'gameID':gameID, 'cardID':cardID  , 'slot':'', 'rank':random[i], 'color':card.initColor});
	}
	// Tirage des 5 cartes retournées
	for(let i=1; i<=5; i++) await drawCard(gameID, 1, 'X', i);

	// Création et mélange des cartes N2
	random = getRandomSeries(24);
	for(let i=0;  i<=23; i++) {
		var cardID = i+40;
		var card = cards.find(card => card.id===cardID)
		await sqlInsert('g_card', {'gameID':gameID, 'cardID':cardID , 'slot':'', 'rank':random[i], 'color':card.initColor});
	}
	// Tirage des 4 cartes retournées
	for(let i=1; i<=4; i++) await drawCard(gameID, 2, 'Y', i);

	// Création et mélange des cartes N3
	random = getRandomSeries(13);
	for(let i=1;  i<=12; i++) {
		var cardID = i+70;
		var card = cards.find(card => card.id===cardID)
		await sqlInsert('g_card', {'gameID':gameID, 'cardID':cardID , 'slot':'', 'rank':random[i], 'color':card.initColor})
	}
	// Tirage des 3 cartes retournées
	for(let i=1; i<=3; i++) await drawCard(gameID, 3, 'Z', i);
	
	// Création des cartes Royales
	for(let i=1;  i<=4; i++) {
		var cardID = i+100;
		await sqlInsert('g_card', {'gameID':gameID, 'cardID':cardID , 'slot':'R', 'rank':i, 'color':'neutral'});
	}
	
	// Création et mélange des jetons (positionné directement sur le plateau 'P')
	random = getRandomSeries(25);
	for(let i=0;  i<=24; i++) await sqlInsert('g_token', {'gameID':gameID, 'tokenID':i+1  , 'slot':'C', 'rank':random[i]});

	// Création des Privileges
	await sqlInsert('g_privilege', {'gameID':gameID, 'privilegeID':1, 'slot':'P', 'rank':1});
	await sqlInsert('g_privilege', {'gameID':gameID, 'privilegeID':2, 'slot':'P', 'rank':2});
	await sqlInsert('g_privilege', {'gameID':gameID, 'privilegeID':3, 'slot':opponentSlot, 'rank': 1});
	
	trace(`gameID#${gameID} initialized`, 1);
	return gameID;
}

async function drawCard(gameID, level, slot, rank) {
	trace(`gameID#${gameID} drawCard`, 1);
	// Identification de la première carte 'disponible' selon le niveau et affectation au slot
	const cards = await sqlQuery(`SELECT g_card.cardID FROM g_card, card WHERE g_card.gameID=${gameID} AND card.level=${level} AND g_card.slot='' ORDER BY g_card.rank LIMIT 1;`);
	const cardId = cards[0].cardID;
	// Affectation de la carte au slot désigné
	await sqlUpdate('g_card', {'slot':slot, 'rank':rank}, `cardID=${cardId}`);
}

async function updateData(login, data) {
	trace(`Request Update Game from ${login}`, 3);
	trace(data, 4);
	const playerData = await getPlayerGameDataByLogin(login);
	if(playerData) {
		if(data.gameData) {
			trace(`Request Update Game from ${login} - gameData: ${JSON.stringify(data.gameData)}`, 4);
			const gameData = data.gameData;
			sqlUpdate('game', {'playerTurn':gameData.playerTurn, 'status':gameData.status, 'round':gameData.round, 'step':gameData.step, 'winnerID':gameData.winnerID}, `game.id=${gameData.id}`);
			sendMessageByName('Update Data', {'gameData':gameData}, playerData.opponent);
			if(gameData.status=='terminated') {
				// Mise à jour des scrores des joueurs
				const winnerData = await getPlayerDataByLogin(login);
				sqlUpdate('player', {'gameCount':winnerData.gameCount+1, 'score':winnerData.score+1, 'status':'available', 'gameID':null}, `player.id=${winnerData.id}`)
				const looserData = await getPlayerDataByLogin(playerData.opponent);
				sqlUpdate('player', {'gameCount':winnerData.gameCount+1, 'status':'available', 'gameID':null}, `player.id=${looserData.id}`)
				sqlUpdate('game', {'playerTurn':'', 'status':'terminated', 'round':gameData.round, 'step':'', 'winnerID':winnerData.id}, `game.id=${gameData.id}`);
				// Suppression des données de la partie
				sqlQuery(`DELETE FROM g_card WHERE gameID=${gameData.id}`);
				sqlQuery(`DELETE FROM g_token WHERE gameID=${gameData.id}`);
				sqlQuery(`DELETE FROM g_privilege WHERE gameID=${gameData.id}`);
			} else {
				// Backup game
				execCmdSync('del backup-3.sql');
				execCmdSync('ren backup-2.sql backup-3.sql');
				execCmdSync('ren backup-1.sql backup-2.sql');
				execCmd('"C:/WebServer/bin/MariaDB 11.1/bin/mariadb-dump.exe" --databases splendor --add-drop-database --add-drop-table --skip-add-locks -i --user=root --password=GQ0j$LN= > "backup-1.sql"');
			}
		}
		if(data.cards) {
			trace(`Request Update Cards from ${login} - cards: ${JSON.stringify(data.cards)}`, 4);
			const cards = data.cards;
			cards.forEach(card => sqlUpdate('g_card', {'color':card.color, 'slot':card.slot, 'rank':card.rank}, `gameID=${playerData.gameID} AND cardID=${card.cardID}`));
			sendMessageByName('Update Data', {'cards':cards}, playerData.opponent);
		}
		if(data.tokens) {
			trace(`Request Update Tokens from ${login} - tokens: ${JSON.stringify(data.tokens)}`, 4);
			const tokens = data.tokens;
			tokens.forEach(token => sqlUpdate('g_token', {'slot':token.slot, 'rank':token.rank}, `gameID=${playerData.gameID} AND tokenID=${token.tokenID}`));
			sendMessageByName('Update Data', {'tokens':tokens}, playerData.opponent);
		}
		if(data.privileges) {
			trace(`Request Update Privileges from ${login} - privileges: ${JSON.stringify(data.privileges)}`, 4);
			const privileges = data.privileges;
			privileges.forEach(privilege => sqlUpdate('g_privilege', {'slot':privilege.slot, 'rank':privilege.rank}, `gameID=${playerData.gameID} AND privilegeID=${privilege.privilegeID}`));
			sendMessageByName('Update Data', {'privileges':privileges}, playerData.opponent);
		}
	}
}

async function playerJoinGame(login, gameID) {
	trace(`${login} join game#${gameID}`, 1);
	// Recherche du joueur dans la base
	player = await getPlayerDataByLogin(login);
	if(player) {
		// Mise à jour du statut du Joueur
		sqlUpdate('player', {'status':'in game', 'gameID':gameID}, `login="${login}"`);
	} else {
		sendMessageByName('Player error', null, login);
		return false;
	}
	const gameData = await getFullGame(gameID);
	trace('Game Data: '+JSON.stringify(gameData), 9);
	if(gameData) {
		sendMessageByName('Game Data', gameData, login);
	} else {
		gameError(login);
	}
}

function gameError(login) {
	sqlUpdate('player', {'status':'available', 'gameID':null}, `login="${login}"`);
	sendMessageByName('Game error', null, sender);
}

function execCmd(cmd) {
	trace('Execute command: '+cmd, 1);
	exec(cmd, (error, stdout, stderr) => {
		if (error) { trace(`error: ${error.message}`, 5); return; }
		if (stderr) { trace(`stderr: ${stderr}`, 3); return; }
		trace(`stdout: ${stdout}`, 2);
	});
}

function execCmdSync(cmd) {
	trace('Execute command sync.: '+cmd, 1);
	execSync(cmd, (error, stdout, stderr) => {
		if (error) { trace(`error: ${error.message}`, 5); return; }
		if (stderr) { trace(`stderr: ${stderr}`, 3); return; }
		trace(`stdout: ${stdout}`, 2);
	});
}