DRB-CnC/Server/utilities/mysqlHandler.js

require('dotenv').config();
const mysql = require('mysql');
const utils = require('./utils');
const { nodeObject, clientObject, connectionObject } = require("./recordHelper");
const { DebugBuilder } = require("../utilities/debugBuilder");
const { BufferToJson, getClientObjectByClientID } = require("../utilities/utils");

const log = new DebugBuilder("server", "mysSQLHandler");
 
const connection = mysql.createPool({
    host: process.env.NODE_DB_HOST,
    user: process.env.NODE_DB_USER,
    password: process.env.NODE_DB_PASS,
    database: process.env.NODE_DB_NAME
});

const nodesTable = `${process.env.NODE_DB_NAME}.nodes`;
const nodeConnectionsTable = `${process.env.NODE_DB_NAME}.node_connections`;

/**
 * Return a node object from a single SQL row
 * 
 * @param {object} row The row to convert to a node object
 * @returns {nodeObject} The converted node object to be used downstream
 */
function returnNodeObjectFromRow(row) {
    return new nodeObject({
        _id: row.id,
        _name: row.name,
        _ip: row.ip,
        _port: row.port,
        _location: row.location,
        _nearbySystems: BufferToJson(row.nearbySystems),
        _online: (row.online === 1) ? true : false,
        });
}

/**
 * Wrapper to convert an array of rows to an array of nodeObjects
 * 
 * @param {array} rows The array of SQL results to be converted into node objects
 * @returns {array} An array of node objects
 */
function returnNodeObjectFromRows(rows) {
    var i = 0;
    for (var row of rows){
        log.DEBUG("Row: ", row);
        rows[i] = returnNodeObjectFromRow(row);
        i += 1;
    }

    log.DEBUG("Converted Objects from Rows: ", rows);
    return rows;
}

/**
 * Returns a connection object from an SQL row
 * 
 * @param {*} row The SQL row to convert to a connection object
 * @returns {connectionObject}
 */
async function returnConnectionObjectFromRow(row) {
    if (Array.isArray(row)) row = row[0]
    log.DEBUG("Connection row: ", row);
    return new connectionObject({
        _connection_id: row.connection_id,
        _node: await getNodeInfoFromId(row.id),
        _client_object: await getClientObjectByClientID(row.discord_client_id)
    });
}

/** Get all nodes the server knows about regardless of status
 * @param {*} callback Callback function
 */
exports.getAllNodes = (callback) => {
    const sqlQuery = `SELECT * FROM ${nodesTable}`
    runSQL(sqlQuery, (rows) => {        
        if(!rows || rows.length == 0) callback(undefined);
                
        return callback(returnNodeObjectFromRows(rows));
    })
}

/**
 * Get all Nodes synchronously **May not be working**
 * 
 * @returns 
 */
exports.getAllNodesSync = async () => {
    const sqlQuery = `SELECT * FROM ${nodesTable}`    
    const rows = await runSQL(sqlQuery);

    console.log("Rows: ", rows);

    return returnNodeObjectFromRows(rows);
}

/** Get all nodes that have the online status set true (are online)
 * @param callback Callback function
 */
exports.getOnlineNodes = (callback) => {
    const sqlQuery = `SELECT * FROM ${nodesTable} WHERE online = 1;`
    runSQL(sqlQuery, (rows) => {
        return callback(returnNodeObjectFromRows(rows));
    })
}

/** Get info on a node based on ID
 * @param nodeId The ID of the node 
 * @param callback Callback function
 */ 
 async function getNodeInfoFromId(nodeId, callback = undefined) {
    if (!nodeId) throw new Error("No node ID given when trying to fetch node");
    log.DEBUG("Getting node from ID: ", nodeId);
    const sqlQuery = `SELECT * FROM ${nodesTable} WHERE id = ${nodeId}`

    const sqlResponse = await new Promise((recordResolve, recordReject) => {
		runSQL(sqlQuery, (rows) => {
            recordResolve(rows);
        })
	});

    // Call back the first (and theoretically only) row
    // Specify 0 so downstream functions don't have to worry about it
    return (callback) ? callback(returnNodeObjectFromRow(sqlResponse[0])) : returnNodeObjectFromRow(sqlResponse[0]);
}
exports.getNodeInfoFromId = getNodeInfoFromId

/** Add a new node to the DB
 * @param nodeObject Node information object
 * @param callback Callback function
 */
exports.addNewNode = async (nodeObject, callback) => {
    if (!nodeObject.name) throw new Error("No name provided");
    const name = nodeObject.name,
        ip = nodeObject.ip,
        port = nodeObject.port,
        location = nodeObject.location,
        nearbySystems = utils.JsonToBuffer(nodeObject.nearbySystems),
        online = nodeObject.online,
        connected = 0;
    const sqlQuery  = `INSERT INTO ${nodesTable} (name, ip, port, location, nearbySystems, online, connected) VALUES ('${name}', '${ip}', ${port}, '${location}', '${nearbySystems}', ${online}, ${connected})`;

    const sqlResponse = await new Promise((recordResolve, recordReject) => {
		runSQL(sqlQuery, (rows) => {
            recordResolve(rows);
        })
	});

    // Call back the first (and theoretically only) row
    // Specify 0 so downstream functions don't have to worry about it
    return (callback) ? callback(returnNodeObjectFromRow(sqlResponse)) : returnNodeObjectFromRow(sqlResponse);
}

/** Update the known info on a node
 * @param nodeObject Node information object
 * @param callback Callback function
 */
exports.updateNodeInfo = async (nodeObject, callback = undefined) => {
    if(!nodeObject.id) throw new Error("Attempted to updated node without providing ID", nodeObject);
    const name = nodeObject.name,
        ip = nodeObject.ip,
        port = nodeObject.port,
        location = nodeObject.location,
        online = nodeObject.online
    let queryParams = [],
        nearbySystems = nodeObject.nearbySystems;

    if (name) queryParams.push(`name = '${name}'`);
    if (ip) queryParams.push(`ip = '${ip}'`);
    if (port) queryParams.push(`port = ${port}`);
    if (location) queryParams.push(`location = '${location}'`);
    if (nearbySystems) {
        nearbySystems = utils.JsonToBuffer(nearbySystems)
        queryParams.push(`nearbySystems = '${nearbySystems}'`);
    }
    if (typeof online === "boolean" || typeof online === "number") {
        if (online || online === 1) queryParams.push(`online = 1`);
        else queryParams.push(`online = 0`);
    }

    let sqlQuery = `UPDATE ${nodesTable} SET`
    if (!queryParams || queryParams.length === 0) return (callback) ? callback(undefined) : undefined;
    if (queryParams.length === 1) {
        sqlQuery = `${sqlQuery} ${queryParams[0]}`
    } else {
        let i = 0;
        for (const param of queryParams) {
            if (i === queryParams.length-1) {
                sqlQuery = `${sqlQuery} ${param}`
                i += 1;
            }
            else {
                sqlQuery = `${sqlQuery} ${param},`
                i += 1;
            }
        }
    }

    sqlQuery = `${sqlQuery} WHERE id = ${nodeObject.id};`

    const sqlResponse = await new Promise((recordResolve, recordReject) => {
		runSQL(sqlQuery, (rows) => {
            recordResolve(rows);
        })
	});

    if (sqlResponse.affectedRows === 1) return (callback) ? callback(true) : true;
    else return (callback) ? callback(returnNodeObjectFromRows(sqlResponse)) : returnNodeObjectFromRows(sqlResponse);
}

/**
 * Add a new connection to the DB when a bot has been connected to the server
 * 
 * @param {*} nodeObject The node object that is being used for this connection
 * @param {*} clientId The client ID Object being used for this connection
 * @param {*} callback [OPTIONAL] The callback function to be called with the results, will return otherwise
 */
exports.addNodeConnection = (nodeObject, clientObject, callback = undefined) => {
    if (!nodeObject.id || !clientObject.clientId) throw new Error("Tried to add a connection without a client and/or node ID");
    const sqlQuery  = `INSERT INTO ${nodeConnectionsTable} (id, discord_client_id) VALUES (${nodeObject.id}, '${clientObject.clientId}')`;

    const sqlResponse = new Promise((recordResolve, recordReject) => {
		runSQL(sqlQuery, (rows) => {
            recordResolve(rows);
        })
	});
    
    if (!sqlResponse) throw new Error("No result from added connection"); 
    return (callback) ? callback(true) : true;
}

/**
 * Check what node is connected with a given client ID object
 * 
 * @param {*} clientId The client ID object used to search for a connected node
 * @param {*} callback [OPTIONAL] The callback function to be called with the results, return will be used otherwise
 */
exports.checkNodeConnectionByClientId = async (clientId, callback = undefined) => {
    if (!clientId.clientId) throw new Error("Tried to check a connection without a client ID");
    const sqlQuery  = `SELECT * FROM ${nodeConnectionsTable} WHERE discord_client_id = '${clientId.clientId}'`;

    const sqlResponse = await new Promise((recordResolve, recordReject) => {
		runSQL(sqlQuery, (rows) => {
            recordResolve(rows);
        })
	});
    
    log.VERBOSE("SQL Response from checking connection: ", sqlResponse);
    
    if (!sqlResponse) return (callback) ? callback(undefined) : undefined;
    const newNodeObject = await getNodeInfoFromId(sqlResponse[0].id);
    log.DEBUG("Node Object from SQL Response: ", newNodeObject);
    return (callback) ? callback(newNodeObject) : newNodeObject;
}

/**
 * Get a connection by node ID
 * 
 * @param {*} nodeId The ID to search for a connection with
 * @param {*} callback [OPTIONAL] The callback function to be called with the results, return will be used otherwise
 * @returns {connectionObject}
 */
exports.getConnectionByNodeId = async (nodeId, callback = undefined) => {
    const sqlQuery  = `SELECT * FROM ${nodeConnectionsTable} WHERE id = '${nodeId}'`;

    const sqlResponse = await new Promise((recordResolve, recordReject) => {
		runSQL(sqlQuery, (rows) => {
            recordResolve(rows);
        })
	});
    
    log.VERBOSE("SQL Response from checking connection: ", sqlResponse);
    
    if (!sqlResponse | sqlResponse.length == 0) return (callback) ? callback(undefined) : undefined;
    const newConnectionObject = await returnConnectionObjectFromRow(sqlResponse)
    log.DEBUG("Connection Object from SQL Response: ", newConnectionObject);
    return (callback) ? callback(newConnectionObject) : newConnectionObject;
}

/**
 * Remove a node connection by the node
 * 
 * @param {*} nodeId The node ID of the node to remove connections of
 * @param {*} callback [OPTIONAL] The callback function to callback with the results, return will be used otherwise
 * @returns 
 */
exports.removeNodeConnectionByNodeId = async (nodeId, callback = undefined) => {    
    const sqlQuery  = `DELETE FROM ${nodeConnectionsTable} WHERE id = '${nodeId}'`;

    const sqlResponse = await new Promise((recordResolve, recordReject) => {
		runSQL(sqlQuery, (rows) => {
            recordResolve(rows);
        })
	});
    log.VERBOSE("SQL Response from removing connection: ", sqlResponse);
    
    if (!sqlResponse) return (callback) ? callback(undefined) : undefined;
    return (callback) ? callback(sqlResponse) : sqlResponse;
}

/**
 * Gets all connected nodes
 * 
 * @param {*} callback [OPTIONAL] The callback function to callback with the results, return will be used otherwise
 * @returns {nodeObject}
 */
exports.getConnectedNodes = async (callback = undefined) =>  {
    const sqlQuery  = `SELECT * FROM ${nodeConnectionsTable}`;

    const sqlResponse = await new Promise((recordResolve, recordReject) => {
		runSQL(sqlQuery, (rows) => {
            recordResolve(rows);
        })
	});
    
    log.VERBOSE("SQL Response from checking connection: ", sqlResponse);
    
    if (!sqlResponse) return (callback) ? callback(undefined) : undefined;
    var nodeObjects = []
    for (const row of sqlResponse) {
        const newNodeObject = await getNodeInfoFromId(row.id);
        log.DEBUG("Node Object from SQL Response: ", newNodeObject);
        nodeObjects.push(newNodeObject);
    }    
    return (callback) ? callback(nodeObjects) : nodeObjects;
}

/**
 * Returns all connections
 * 
 * @param {*} callback [OPTIONAL] The callback function to callback with the results, return will be used otherwise
 * @returns {connectionObject}
 */
exports.getAllConnections = async (callback = undefined) => {
    const sqlQuery  = `SELECT * FROM ${nodeConnectionsTable}`;

    const sqlResponse = await new Promise((recordResolve, recordReject) => {
		runSQL(sqlQuery, (rows) => {
            recordResolve(rows);
        })
	});
    
    log.VERBOSE("SQL Response from checking connection: ", sqlResponse);
    
    if (!sqlResponse) return (callback) ? callback(undefined) : undefined;
    var connectionObjects = []
    for (const row of sqlResponse) {
        connectionObjects.push(await returnConnectionObjectFromRow(row));
    }    
    return (callback) ? callback(connectionObjects) : connectionObjects;
}

// Function to run and handle SQL errors
function runSQL(sqlQuery, callback = undefined, error = (err) => {
    console.log(err);
    throw err;
}) {
    connection.query(sqlQuery, (err, rows) => {
        if (err) return error(err);
        //console.log('The rows are:', rows);        
        return (callback) ? callback(rows) : rows
    })
}

exports.closeConnection = () => {
    connection.end()
}