const Discord = require('discord.js');
const client = new Discord.Client();
const fileExists = require('file-exists');
const mysql = require('mysql');

var result = [];
var pictureInfos = [];

var con = mysql.createConnection({
    host: "192.168.0.130",
    user: "discord",
    password: "PASSWORD",
    database: "discord"
});

con.connect(err => {
    if (err) throw err;
    console.log("Connected to database!");
})

function commandIs(str, msg) { //Will set the start of the command by ! + the command
    return msg.content.toLowerCase().startsWith("~" + str);
}

client.on('ready', () => {
    console.log('The bot is online!');
});

client.on('message', message => {
    var args = message.content.split(/[ ]+/); //Will split the message at every spaces there is



    if (commandIs("sql-inject", message)) { //meant only when the database hasn't been created
        if (args.length === 1 && message.author.id == "194125820418064385") {
            message.channel.sendMessage('Executing Level 3 command as Haidson');

            for (i = 0; i < 1600; ++i) {
                if (fileExists.sync('C:/Users/Haidson/Desktop/bobs/' + i + '.jpg')) {
                    con.query(`INSERT INTO picture (pictureid) VALUES ('` + i + `');`);
                } else if (fileExists.sync('C:/Users/Haidson/Desktop/bobs/' + i + '.jpeg')) {
                    con.query(`INSERT INTO picture (pictureid) VALUES ('` + i + `');`);
                } else if (fileExists.sync('C:/Users/Haidson/Desktop/bobs/' + i + '.png')) {
                    con.query(`INSERT INTO picture (pictureid) VALUES ('` + i + `');`);
                }
            }
        } else {
            message.channel.sendMessage('`Cannot execute a Level 3 command without being Haidson!`');
        }
    }

    if (commandIs("guildid", message)) {
        message.channel.sendMessage(message.guild.id);
    }

    if (commandIs("help", message)) {
        message.channel.sendMessage("```Useful commands for the Tidepod-Chan bot: \nLevel 1 commands: \n~+1 : Vote for the first picture that appeared\n~+2 : Vote for the second picture that appeared\n~fav (1/2) : Will save in the database the pictures you love most\n~fav show : Will PM you all the pictures you saved\n----------------------------------------------------------------\nLevel 2 commands:\n~start [args1] [args2]\n[args1] : Set the type of pictures to be sent ** ~help start for the list of types\n[args2] : Set the minimal rating of pictures that can be sent in the channel (default rating: 1400)\n----------------------------------------------------------------\nLevel 3 commands:\nNote: if you don't have access to those commands, it's because you have nothing to do here\n~sql-inject : update the database to add the newest pictures available and set their default configs\n~sql-wipe : Drops the database and create one from scratch\n~server-blacklist : Blacklist a server based on it's guild ID, blocking all access to the bot```");
    }

    if (commandIs("start", message)) {
        let tempvar = 0;
        con.query(`SELECT * from vote WHERE guildid=` + message.guild.id, (err, rows) => {

            if (err) throw (err);
            if (rows.length > 1) {
                message.channel.sendMessage("There is already another process running! Vote before trying to run another! or stop the current process!");
            } else {
                if (args.length === 1) { //Error message in case of no args

                    message.channel.sendMessage("Not enough arguments!");

                } else if (args.length === 2) {

                    if (!message.channel.nsfw) {
                        message.channel.sendMessage('`Cannot fully execute the command as the channel is not NSFW.`');
                    } else {

                        message.channel.sendMessage('Starting the bot in this channel based on category: ' + args[1]);

                        var counter = 0;
                        do {

                            con.query(`SELECT pictureid, elo FROM picture where type = ` + args[1] + ` ORDER BY RAND() LIMIT 1;`, (err, rows) => {
                                if (err) throw err;

                                if (rows.length < 1) {
                                    message.channel.sendMessage("No result found for the criterias mentioned!");
                                } else {
                                    let pictureid = rows[0].pictureid; //IMPORTANT: WILL CONVERT TO A REGULAR INT
                                    let elo = rows[0].elo;
                                    let guildid = message.guild.id;
                                    con.query(`INSERT INTO  vote (pictureid, currentelo, votes, guildid) VALUES (` + pictureid + `, ` + elo + `, 0, ` + guildid + `)`);

                                    if (fileExists.sync('C:/Users/Haidson/Desktop/bobs/' + pictureid + '.jpg')) {
                                        message.channel.sendFile('C:/Users/Haidson/Desktop/bobs/' + pictureid + '.jpg');
                                    }
                                    else if (fileExists.sync('C:/Users/Haidson/Desktop/bobs/' + pictureid + '.jpeg')) {
                                        message.channel.sendFile('C:/Users/Haidson/Desktop/bobs/' + pictureid + '.jpeg');
                                    }
                                    else if (fileExists.sync('C:/Users/Haidson/Desktop/bobs/' + pictureid + '.png')) {
                                        message.channel.sendFile('C:/Users/Haidson/Desktop/bobs/' + pictureid + '.png');
                                    }
                                }
                            });
                            counter++;
                            setTimeout(function () { //System sleep function
                            }, 1000);

                        } while (counter < 2);

                    }

                } else if (args.length === 3) {

                    if (!message.channel.nsfw) {
                        message.channel.sendMessage('`Cannot fully execute the command as the channel is not NSFW.`');
                    } else {
                        message.channel.sendMessage('Starting the bot in this channel based on category: ' + args[1] + ' and minimal rating of: ' + args[2]);
                        var counter = 0;
                        do {

                            con.query(`SELECT pictureid, elo FROM picture where type = ` + args[1] + ` AND elo >=` + args[2] + ` ORDER BY RAND() LIMIT 1;`, (err, rows) => {
                                if (err) throw err;

                                if (rows.length < 1) {
                                    message.channel.sendMessage("No result found for the criterias mentioned!");
                                } else {
                                    let pictureid = rows[0].pictureid; //IMPORTANT: WILL CONVERT TO A REGULAR INT
                                    let elo = rows[0].elo;
                                    let guildid = message.guild.id;
                                    con.query(`INSERT INTO  vote (pictureid, currentelo, votes, guildid) VALUES (` + pictureid + `, ` + elo + `, 0, ` + guildid + `)`);

                                    if (fileExists.sync('C:/Users/Haidson/Desktop/bobs/' + pictureid + '.jpg')) {
                                        message.channel.sendFile('C:/Users/Haidson/Desktop/bobs/' + pictureid + '.jpg');
                                    }
                                    else if (fileExists.sync('C:/Users/Haidson/Desktop/bobs/' + pictureid + '.jpeg')) {
                                        message.channel.sendFile('C:/Users/Haidson/Desktop/bobs/' + pictureid + '.jpeg');
                                    }
                                    else if (fileExists.sync('C:/Users/Haidson/Desktop/bobs/' + pictureid + '.png')) {
                                        message.channel.sendFile('C:/Users/Haidson/Desktop/bobs/' + pictureid + '.png');
                                    }
                                }
                            });

                            counter++;
                            setTimeout(function () { //System sleep function
                            }, 1000);

                        } while (counter < 2);
                    }

                } else {
                    message.channel.sendMessage('`Too many arguments! Please verify syntax.`');
                }
            }
        });

    }

    if (commandIs("+1", message)) {
        con.query(`SELECT * FROM vote WHERE guildid=` + message.guild.id, (err, rows) => {
            if (rows.length < 1 ){
                message.channel.sendMessage("There is nothing to vote for! Start another round using ~start !");
            }else{
            let pictureid1 = rows[0].pictureid;
            let picture1Votes = rows[0].votes;
            let pictureid2 = rows[1].pictureid;
            con.query(`UPDATE vote SET votes= votes+1 WHERE pictureid=` + pictureid1);

            if ((picture1Votes + 1) >= 2) {
                let previousEloPicture1 = rows[0].currentelo;
                let previousEloPicture2 = rows[1].currentelo;
                var expectedOutcomePicture1 = (1 / (1 + Math.pow(10, ((previousEloPicture2 - previousEloPicture1) / 400))));
                var newElo = previousEloPicture1 + (25 * (2 - expectedOutcomePicture1));
                console.log(previousEloPicture1);
                console.log(previousEloPicture2);
                console.log(expectedOutcomePicture1);
                console.log(newElo);
                con.query(`UPDATE picture SET elo=` + newElo + ` WHERE pictureid=` + pictureid1);

                var expectedOutcomePicture2 = (1 / (1 + Math.pow(10, ((previousEloPicture1 - previousEloPicture2) / 400)))); //Based on loss
                var newEloPicture2 = previousEloPicture2 - (25 * (2 - expectedOutcomePicture2));
                console.log(expectedOutcomePicture2);
                console.log(newEloPicture2);
                con.query(`UPDATE picture SET elo=` + newEloPicture2 + ` WHERE pictureid=` + pictureid2);
                con.query(`DELETE FROM vote WHERE guildid=` + message.guild.id);
            }
            message.channel.sendMessage("Vote casted!");
        }
        });
    }


    if (commandIs("+2", message)) {
        con.query(`SELECT * FROM vote WHERE guildid=` + message.guild.id, (err, rows) => {
            if (rows.length < 1 ){
                message.channel.sendMessage("There is nothing to vote for! Start another round using ~start !");
            }else{
            let pictureid1 = rows[0].pictureid;
            let picture2Votes = rows[1].votes;
            let pictureid2 = rows[1].pictureid;
            con.query(`UPDATE vote SET votes= votes+1 WHERE pictureid=` + pictureid2);

            if ((picture2Votes + 1) >= 2) {
                let previousEloPicture1 = rows[0].currentelo;
                let previousEloPicture2 = rows[1].currentelo;
                var expectedOutcomePicture2 = (1 / (1 + Math.pow(10, ((previousEloPicture1 - previousEloPicture2) / 400))));
                var newElo = previousEloPicture2 + (25 * (2 - expectedOutcomePicture2));
                con.query(`UPDATE picture SET elo=` + newElo + ` WHERE pictureid=` + pictureid2);

                var expectedOutcomePicture1 = (1 / (1 + Math.pow(10, ((previousEloPicture2 - previousEloPicture1) / 400)))); //Based on loss
                var newEloPicture1 = previousEloPicture1 - (25 * (2 - expectedOutcomePicture1));
                con.query(`UPDATE picture SET elo=` + newEloPicture1 + ` WHERE pictureid=` + pictureid1);
                con.query(`DELETE FROM vote WHERE guildid=` + message.guild.id);
            }
            message.channel.sendMessage("Vote casted!");
        }
        });
    }

    if (commandIs("fav", message)) {
        if (args.length === 1) {
            message.channel.sendMessage("Please use ~fav 1, ~fav 2 or ~fav show!");
        } else if (args.length === 2) {
            if (args[1] == "1") {
                con.query(`SELECT * FROM vote WHERE guildid=` + message.guild.id, (err, rows) => {
                    let pictureID = rows[0].pictureid;
                    con.query(`INSERT INTO favorites (uid, pictureid, serverid) VALUES (` + message.author.id + `,` + pictureID + `,` + message.guild.id + `);`);
                }); uid =
                    + message.author.id, (err, rows) => {

                    }
            } else if (args[1] == "2") {
                con.query(`SELECT * FROM vote WHERE guildid=` + message.guild.id, (err, rows) => {
                    let pictureID = rows[1].pictureid;
                    con.query(`INSERT INTO favorites (uid, pictureid, serverid) VALUES (` + message.author.id + `,` + pictureID + `,` + message.guild.id + `);`);
                });

            } else if (args[1] == "show") {
                con.query(`SELECT * FROM favorites WHERE uid=` + message.author.id, (err, rows) => { //TODO: SET A LIMIT IF USER ABOVE 5

                    for (i = 0; i < rows.length; ++i) {
                        let pictureid = rows[i].pictureid
                        if (fileExists.sync('C:/Users/Haidson/Desktop/bobs/' + pictureid + '.jpg')) {
                            message.author.sendFile('C:/Users/Haidson/Desktop/bobs/' + pictureid + '.jpg');
                        }
                        else if (fileExists.sync('C:/Users/Haidson/Desktop/bobs/' + pictureid + '.jpeg')) {
                            message.author.sendFile('C:/Users/Haidson/Desktop/bobs/' + pictureid + '.jpeg');
                        }
                        else if (fileExists.sync('C:/Users/Haidson/Desktop/bobs/' + pictureid + '.png')) {
                            message.author.sendFile('C:/Users/Haidson/Desktop/bobs/' + pictureid + '.png');
                        }
                    }
                });
            } else {
                message.channel.sendMessage("Wrong arguments! Please use ~fav 1, ~fav 2 or ~fav show!")
            }

        } else {
            message.channel.sendMessage("Wrong arguments! Please use ~help!");
        }
    }
});


client.login('LOGINKEY');