const Discord = require('discord.js');
const {
        prefix,
        token
} = require('./config.json');
const client = new Discord.Client();
const fs = require('fs')
const sqlite3 = require('sqlite3').verbose();
const sourceChannel="749435709097639946" //cisum buff channel
const COMMANDS = new Set(["rend", "ony", "nef", "hakkar", "bvsf", "dmt"]);
const DATABASE_FILE_NAME = "bt.db";


function getDatabase() {
        return new sqlite3.Database(DATABASE_FILE_NAME, (err) => {
                if (err) {
                        console.error(err.message);
                }
                console.log('Connected to the bt database.');
        });

}

process.on('unhandledRejection', (reason, promise) => { //send all unhandled rejections to the database for analysis
        console.log('Unhandled Rejection at - 🛑 - ', promise, 'reason:', reason);
        logError(reason.path, reason);

        if (reason.message === 'Cannot edit a message authored by another user') {
                cid = reason.path.split('/')[4]
                removeChannel(cid, 'Cannot edit a message authored by another user');
                console.log("reason message is :" + reason.message)
                console.log("reason errno is :" + reason.errno)
                console.log("reason code is :" + reason.code)
        } else if (reason.message === 'Missing Permissions') {
                console.log("reason message is :" + reason.message)
                console.log("reason errno is :" + reason.errno)
                console.log("reason code is :" + reason.code)
               
        }
        else if (reason.message === 'Missing Access') {
                cid = reason.path.split('/')[4]
                removeChannel(cid, 'Missing Access');
                console.log("reason message is :" + reason.message)
                console.log("reason errno is :" + reason.errno)
                console.log("reason code is :" + reason.code)
        }
        else if (reason.message === "Cannot read property 'fetchMessages' of undefined" 
                //|| reason.message === "Cannot read property 'edit' of undefined"
                || reason.errno === 'EAI_AGAIN'
                || reason.code === 'ECONNRESET'
                || reason.errno === 'ENETUNREACH'
                || reason.path === '/api/v7/channels/644061609240690699/messages?limit=1'
                ){
                console.log("reason message is :" + reason.message)
                console.log("reason errno is :" + reason.errno)
                console.log("reason code is :" + reason.code)  
                setTimeout(checkTimers, 20 * 1000); 
        }
        else{
                
                console.log("undefined exception")
                console.log("reason message is :" + reason.message)
                console.log("reason errno is :" + reason.errno)
                console.log("reason code is :" + reason.code)
                     
        }

});




client.once('ready', () => {
        console.log('running version: 8_29_2020');
        checkTimers();
});



client.on('message', message => {
        if (!message.content.startsWith(prefix) || message.author.bot) return;

        var args = message.content.slice(prefix.length).split(/ +/);
        const command = args.shift().toLowerCase();


        if (command === 'wb') {
                let channel = client.channels.cache.get(sourceChannel); //set this var to source channel 644061609240690699 -skeram disc

                channel.messages.fetch({
                        limit: 1
                }).then(messages => { //pull the latest message from source channel
                        let lastMessage = messages.first();

                        if (!lastMessage.author.bot) {} //ignore messages from bots



                        message.reply(lastMessage.content)

                        var timestamp = new Date();
                        var timestampDateFormatted = timestamp.toUTCString();



                        let db = new sqlite3.Database('bt.db', (err) => {
                                if (err) {
                                        logError(error, "wb - db connect")
                                        console.error(err.message);
                                }
                                console.log('Connected to the bt database.');
                        });

                        db.run(`INSERT INTO wb(time) VALUES(?)`, [timestampDateFormatted], function (err) {
                                if (err) {
                                        logError(error, "wb - insert")
                                        return console.log(err.message);
                                }
                                // get the last insert id
                                console.log(`A wb request row has been inserted with rowid ${this.lastID}`);
                        });

                        // close the database connection
                        db.close();

                })
        }

        if (command === 'subs' ) {

                let db4 = new sqlite3.Database('bt.db');

                //check to see if this user already has a notification sub for this location
                let sql = `select user,location from notifications where user = ? and type = ?`;
                
                subs=[]

                db4.all(sql, [message.author.id, 'sub'], (err, rows) => {
                        rows.forEach((row, index) => { 
                                subs.push(row.location)
                        })
                        if (subs.length === 0){
                                message.reply("You have no active subscriptions at this time.")
                        }
                        else{
                                const flashReport = new Discord.MessageEmbed()
                                        .setColor('#66ffff')
                                        .setTitle("BRB - Sub Check")
                                        //.setDescription(newUpdated)
                                        .addFields(
                                                {"name": "Active Subscriptions", "value": subs}
                                                )
                                        .setTimestamp()
                                        //.setFooter('To unsubscribe from th message type !' + locationName + 'sub')
                                message.reply(flashReport)
                        }

                })

                
                //insertNotification(message.author.id,"","subs")
                insertNotificationLog(message.author.id, "", "", "subs")
                //insertNotificationLog(user, command, type, "insert")

                db4.close();
                
                






        }

        if (commands.has())

        if (command === 'ony' || command === 'nef' || command === 'rend' || command === 'hakkar' || command === 'bvsf' || command === 'dmt' ) {

                //message.reply("notification logic")
                //console.log(message)


                let db4 = new sqlite3.Database('bt.db');

                //check to see if this user already has a notification sub for this location
                let sql = `select user,location,type from notifications where user = ? and location = ?`;
                

                db4.get(sql, [message.author.id, command] , (err, row) => {
                        if (err) {
                                return console.error(err.message);
                        }
                        //if there is no previous notification sub for this user+location then insert it
                        if(!row){
                                insertNotification(message.author.id,command,"single")
                                message.reply("You will be notified with the latest on " + command)
                        }
                        
                        else if(row.type === 'sub'){
                                message.reply("You already have an active subscription for updates on " + command + ". This command was ignored. If you'd like to cancel the subscription and allow for one time updates, type !" + command + "sub")
                        }
                        else if(row.type === 'single'){
                                message.reply("You already have a request pending for the latest update on " + command + ". This command was ignored.")

                        }
                });

                db4.close();



        }

        if (command === 'onysub' || command === 'nefsub' || command === 'rendsub' || command === 'hakkarsub' || command === 'bvsfsub' || command === 'dmtsub' ) {

                //message.reply("notification logic")
                //console.log(message)
                commandStr = command.substring(0, command.length - 3);

                let db4 = new sqlite3.Database('bt.db');

                //check to see if this user already has a notification sub for this location
                let sql = `select user,location,type from notifications where user = ? and location = ?`;
                

                db4.get(sql, [message.author.id, commandStr] , (err, row) => {
                        if (err) {
                                return console.error(err.message);
                        }
                        //if there is no previous notification sub for this user+location then insert it
                        if(!row){
                                insertNotification(message.author.id,commandStr,"sub")
                                message.reply("You will receive a message with every update to " + commandStr + ". To unsubscribe from further updates, retype !" + command)

                        }
                        //if there is a previous notification sub for this location+user, unsub the user
                        else if(row.type === 'sub'){
                                notificationRemoval(commandStr, message.author.id, row.type)
                                message.reply("You have unsubscribed to updated on " + commandStr + ". To resubscribe to updates type !" + command )
                        }
                        //if there is a single notification already for this location+user, overwrite with sub.
                        else if(row.type === 'single'){
                                notificationRemoval(commandStr, message.author.id, row.type)
                                message.reply("You have a one-time request for updates on ! " + command + " that was overwritten by this subscription. \n" +
                                        "You will receive a message with every update to " + commandStr + ". To unsubscribe from further updates, retype !" + command)
                                insertNotification(message.author.id,commandStr,"sub")

                        }
                });

                db4.close();

                
 




        }



        if (command === 'repost') {


                //pull server ID and channel ID from message details
                var serverId = message.channel.guild.id;
                var channelId = message.channel.id;



                //pull the latest timers and reply to the !repost
                let db4 = new sqlite3.Database('bt.db');

                let sql = `select bufftimers from bufftimers`;

                db4.get(sql, (err, row) => {
                        if (err) {
                                return console.error(err.message);
                        }

                        message.reply(row.buffTimers)
                });

                db4.close();


                //pull the latest message from the bot and add it to the repost database
                if (client.user.lastMessage == null) {
                        const collector = new Discord.MessageCollector(message.channel, m => m.author.id === client.user.id, { time: 10000 });
                        collector.on('collect', message => {
                                collector.stop("Got my message");
                                let db = new sqlite3.Database('bt.db', (err) => {
                                        if (err) {
                                                logError(error, "repost - db connect")
                                                console.error(err.message);
                                                }
                                        console.log('Connected to the bt database.');
                                });

                                db.run(`INSERT OR REPLACE INTO repostLocations VALUES(?, ?, ?)`, [serverId, channelId, message.id], function (err) { 
                                        if (err) {
                                                logError(err.message, "repost - insert statement")
                                                return console.log(err.message);
                                        }
                                                        // get the last insert id
                                        console.log(`A repost location row has been inserted with rowid ${this.lastID}`);
                                        console.log("sid: " + serverId);
                                        console.log("cid: " + channelId);
                                        console.log("mid: " + message.id);
                                });

                                                // close the database connection
                                db.close();





                        })
                }



        }


        if (command === 'brb' || command === 'buffreposterbot') {
                help = "```!wb " +
                        "\nReports the latest world buff times. " +
                        "\n" +
                        "\n!repost " +
                        "\nMake a post that will automatically update with the latest buff times. " +
                        "\nOne !repost is allowed per server. Only the latest !repost will be recognized by the bot. " +
                        "\n" +
                        "\n!ony !nef !rend !bvsf !hakkar !dmt"+
                        "\nRequest a one time notification for updates." +
                        "\nFor example, to get a one time update for ony buff, message !ony" +
                        "\n" +
                        "\n!onysub !nefsub !rendsub !bvsfsub !hakkarsub !dmtsub"+
                        "\nRequest notifications for every update until unsubscribed." +
                        "\nFor example, to get every update for ony buff, message !onysub" +
                        "\nTo unsubscribe, simply message the original command again, in this example !onysub" +
                        "\n" +
                        "\n!subs" +
                        "\nCheck on currently active subscriptions." +
                        "\n" +
                        "\n!brb or !buffreposterbot"+
                        "\nRequest this help information. " +
                        "\n" +
                        "\nIf you find Buff Reposter Bot useful, consider donating to the developer: https://www.paypal.me/buffreposterbot" +
                        "\nQuestions/Comments: buffreposterbot@gmx.com```"

                message.channel.send(help);



        }




});

client.login(token);




function insertNotification(user,command, type){
        let db = new sqlite3.Database('bt.db', (err) => {
        if (err) {
                logError(error, "n - db connect")
                console.error(err.message);
        }
        console.log('Connected to the bt database.');
        });

        db.run(`INSERT OR REPLACE INTO notifications VALUES(?, ?, ?)`, [user, command, type], function (err) { 
                if (err) {
                        logError(err.message, "n - insert statement")
                        return console.log(err.message);
                }
                                        // get the last insert id
                console.log(`A notification row has been inserted with rowid ${this.lastID}`);
                console.log("user: " + user + " notification: " + command);
                //console.log();
        });

                                // close the database connection
        db.close();
        insertNotificationLog(user, command, type, "insert")

}

function insertNotificationLog(user,location,type,status){
        var timestamp = new Date();
        var timestampDateFormatted = timestamp.toUTCString();
        let db = new sqlite3.Database('bt.db', (err) => {
        if (err) {
                logError(error, "n - db connect")
                console.error(err.message);
        }
        console.log('Connected to the bt database.');
        });

        db.run(`INSERT INTO notificationLog VALUES(?, ?, ?, ?, ?)`, [timestampDateFormatted, user, location, type, status], function (err) { 
                if (err) {
                        logError(err.message, "n - insert statement")
                        return console.log(err.message);
                }
                                        // get the last insert id
                console.log(`A notificationLog row has been inserted with rowid ${this.lastID}`);
                //console.log("user: " + user + " notification: " + command);
                //console.log();
        });

                                // close the database connection
        db.close();

}

function checkTimers() {

  
                channel3 = client.channels.cache.get(sourceChannel); //needs to be set to skeram disc
                if (!channel3){
                        console.log("error at check timers - get channel");
                        logError("error at check timers - get channel", "check timers");
                        setTimeout(checkTimers, 20 * 1000);
                };
                channel3.messages.fetch({
                        limit: 1
                }).then(messages => {
                        var lastMessage3 = messages.first();

                        //select statement needed here
                        let db4 = new sqlite3.Database('bt.db');

                        let sql = `select bufftimers from bufftimers`;

                        db4.get(sql, (err, row) => {
                                if (err) {
                                        return console.error(err.message);
                                }

                                //if the new timers match the old timers, do not continue
                                timers = lastMessage3.content;
                                if (lastMessage3.content === row.buffTimers) {
                                        //exit this function
                                        var timestamp = new Date();
                                        var timestampDateFormatted = timestamp.toUTCString();
                                        console.log("current buff timers match ✅" + timestampDateFormatted);
                                        setTimeout(checkTimers, 5000);
                                        return;
                                } else {
                                        console.log("Calling update timer");
                                        updateTimers(lastMessage3.content);
                                        remindMe(lastMessage3.content, row.buffTimers);
                                }

                                return row



                        });

                        db4.close();

                });

};



function remindMe(newTimers, oldTimers){


        //console.log(oldTimers.split(/\r?\n/) )
        //console.log("inside remindme")

        oldTimersArr = oldTimers.split(/\r?\n/)
        newTimersArr = newTimers.split(/\r?\n/)
        //extract key values from old timers and new timers arrays
        for (let i = 0; i < oldTimersArr.length; i++) {
                if (oldTimersArr[i].includes("🐛")) {
                        console.log("skipping bug emoji")

                }
                else{
                        if (oldTimersArr[i].includes("Updated as of")) {
                                oldUpdated=oldTimersArr[i]
                        }
                        if (oldTimersArr[i].includes("👹")) {
                                oldRend=oldTimersArr[i]
                        }
                        if (oldTimersArr[i].includes("🐉")) {
                                oldOny=oldTimersArr[i]
                        }
                        if (oldTimersArr[i].includes("🐲")) {
                                oldNef=oldTimersArr[i]
                        }
                        if (oldTimersArr[i].includes("💗")) {
                                oldHakkar=oldTimersArr[i]
                        }
                        if (oldTimersArr[i].includes("🥀")) {
                                oldBVSF=oldTimersArr[i]
                        }
                        if (oldTimersArr[i].includes("👑")) {
                                oldDMT=oldTimersArr[i]
                        }
                }
        }

        for (let i = 0; i < newTimersArr.length; i++) {
                if (newTimersArr[i].includes("🐛")) {
                        console.log("skipping bug emoji")

                }
                else{
                        if (newTimersArr[i].includes("Updated as of")) {
                                newUpdated=newTimersArr[i]
                        }
                        if (newTimersArr[i].includes("👹")) {
                                newRend=newTimersArr[i]
                        }
                        if (newTimersArr[i].includes("🐉")) {
                                newOny=newTimersArr[i]
                        }
                        if (newTimersArr[i].includes("🐲")) {
                                newNef=newTimersArr[i]
                        }
                        if (newTimersArr[i].includes("💗")) {
                                newHakkar=newTimersArr[i]
                        }
                        if (newTimersArr[i].includes("🥀")) {
                                newBVSF=newTimersArr[i]
                        }
                        if (newTimersArr[i].includes("👑")) {
                                newDMT=newTimersArr[i]
                        }
                }

        }

        //query the existing notification requests
        let db5 = new sqlite3.Database('bt.db');

        let sql = `select user,location,type from notifications`;

        locations = [];

        db5.all(sql, [], (err, rows) => {
                if (err) {
                        logError(error, "updateTimers - query")
                        return console.log(err.message);
                }
                if (!rows.length) {
                        console.log("No rows in notifications db. ");
                        return;
                }

                rows.forEach((row, index) => { 

                        //push all notification requests into an array
                        locations.push({"location": row.location, "user": row.user, "type": row.type})
                        
                        return row

                });

                  
                //if any of the buff timers are not matching, update the notification requests that match the buffs that changed

                if (oldRend != newRend){
                        console.log(oldRend)
                        console.log(newRend)
                        sendNotifications(oldRend, newRend, locations, "rend")    
                }
                if (oldOny != newOny){
                        console.log(oldOny)
                        console.log(newOny)
                        sendNotifications(oldOny, newOny, locations, "ony")
                }
                if (oldNef != newNef){
                        console.log(oldNef)
                        console.log(newNef)
                        sendNotifications(oldNef, newNef, locations, "nef")
                }
                if (oldHakkar != newHakkar){
                        console.log(oldHakkar)
                        console.log(newHakkar)
                        sendNotifications(oldHakkar, newHakkar, locations, "hakkar")
                }
                if (oldBVSF != newBVSF){
                        console.log(oldBVSF)
                        console.log(newBVSF)
                        sendNotifications(oldBVSF, newBVSF, locations, "bvsf")
                }
                if (oldDMT != newDMT){
                        console.log(oldDMT)
                        console.log(newDMT)
                        sendNotifications(oldDMT, newDMT, locations, "dmt")
                }


        });

        db5.close();

        //console.log(locations)


}

function sendNotifications (oldTimer, newTimer, locations, locationName){
        for (let i = 0; i < locations.length; i++) {
                if(locations[i].location === locationName && locations[i].type === 'single'){
                        const user = client.users.cache.get(locations[i].user);
                        //user.send(newUpdated + " Old Timer: " + oldRend + " -- > New Timer: " + newRend);
                        const flashReport = new Discord.MessageEmbed()
                                .setColor('#66ffff')
                                .setTitle("Buff Timer Update")
                                .setDescription(newUpdated)
                                .addFields(
                                    { name: 'Old Timer', value: oldTimer, inline: false },
                                    //{ name: '\u200B', value: '\u200B' },
                                    { name: 'New Timer', value: newTimer, inline: false },
                                )
                                .setTimestamp()
                        user.send(flashReport)
                        notificationRemoval(locations[i].location,locations[i].user,locations[i].type)
                }else if(locations[i].location === locationName && locations[i].type === 'sub'){                        
                        if(locations[i].location === locationName){
                                const user = client.users.cache.get(locations[i].user);
                                //user.send(newUpdated + " Old Timer: " + oldRend + " -- > New Timer: " + newRend);
                                const flashReport = new Discord.MessageEmbed()
                                        .setColor('#66ffff')
                                        .setTitle("Buff Timer Update")
                                        .setDescription(newUpdated)
                                        .addFields(
                                            { name: 'Old Timer', value: oldTimer, inline: false },
                                            //{ name: '\u200B', value: '\u200B' },
                                            { name: 'New Timer', value: newTimer, inline: false },
                                        )
                                        .setTimestamp()
                                        .setFooter('To unsubscribe from this message type !' + locationName + 'sub')
                                user.send(flashReport)
                                insertNotificationLog(locations[i].user, locationName, locations[i].type, "sent notification")
                        }

                        
                     

                }

        }

}


function notificationRemoval (location, user, type){
        let db6 = new sqlite3.Database('bt.db', (err) => {
                if (err) {
                        console.error(err.message);
                }
        });
        //console.log(user)
        //console.log(user.toString())
        //console.log(user.Number())

        db6.run(`DELETE FROM notifications WHERE user=? and location=? and type=?`, [user, location, type], function (err) {
                if (err) {
                        return console.error(err.message);
                }
                console.log(`Row(s) deleted ${this.changes}`);
        });

        // close the database connection
        db6.close((err) => {
                if (err) {
                        return console.error(err.message);
                }
        });
        insertNotificationLog(user, location, type, "removal")

}


function updateTimers(timers) {
        let db = getDatabase();

        db.run(`UPDATE buffTimers SET buffTimers = (?)`, [timers], function (err) {
                if (err) {
                        return console.log(err.message);
                }
                // console.log(`buffTimers Updated`);
        });

        db.all("SELECT sid, cid, mid FROM repostLocations", [], (err, rows) => {
                if (err) {
                        logError(error, "updateTimers - query")
                        return console.log(err.message);
                }
                if (!rows.length) {
                        console.log("No rows in db. Calling checkTimers.");
                        checkTimers();
                        return;
                }
                //ID SOURCE CHANNEL - this will need to be updated to skeram disc once approved
                let channel = client.channels.cache.get(sourceChannel);

                channel.messages.fetch({
                        limit: 1
                                }).then(messages => {
                                        let lastMessage = messages.first(); //get the latest message from the channel to use as a source of information

                                        if (!lastMessage.author.bot) { // The author of the last message wasn't a bot
                                                // TODO: do something if it's not a bot?
                                        }

                                        let channel2 = client.channels.cache.get(row.cid) //get information about the message to be updated

                                        if (!channel2) {
                                                removeChannel(row.cid, "Channel is undefined");
                                        };

                                        //console.log("channel ID -" + channel2.id);
                                        channel2.messages.fetch({
                                                        around: row.mid,
                                                        limit: 1
                                                })
                                                .then(msg => {
                                                        console.log("editting message - 🔄 -" + row.mid + " - " + index)
                                                        const fetchedMsg = msg.first();
                                                        if (!fetchedMsg) {
                                                                removeChannel(row.cid, "message is undefined");
                                                        }
                                                        fetchedMsg.edit(timers); //update the message pulled from the database

                                                        //console.log("after edit")
                                                        // if this is the last row then wait 20 seconds and call checkTimers
                                                        if (rows.length == index + 1) {
                                                                console.log("Calling checkTimers in 20 seconds");
                                                                setTimeout(checkTimers, 20000);
                                                        }
                                                });


                                });


                // get the last insert id
                rows.forEach((row, index) => { //iterate over all of the post locations
                                channel.messages.fetch({
                                        limit: 1
                                }).then(messages => {
                                        let lastMessage = messages.first(); //get the latest message from the channel to use as a source of information

                                        if (!lastMessage.author.bot) { // The author of the last message wasn't a bot
                                                // TODO: do something if it's not a bot?
                                        }

                                        let channel2 = client.channels.cache.get(row.cid) //get information about the message to be updated

                                        if (!channel2) {
                                                removeChannel(row.cid, "Channel is undefined");
                                        };

                                        //console.log("channel ID -" + channel2.id);
                                        channel2.messages.fetch({
                                                        around: row.mid,
                                                        limit: 1
                                                })
                                                .then(msg => {
                                                        console.log("editting message - 🔄 -" + row.mid + " - " + index)
                                                        const fetchedMsg = msg.first();
                                                        if (!fetchedMsg) {
                                                                removeChannel(row.cid, "message is undefined");
                                                        }
                                                        fetchedMsg.edit(timers); //update the message pulled from the database

                                                        //console.log("after edit")
                                                        // if this is the last row then wait 20 seconds and call checkTimers
                                                        if (rows.length == index + 1) {
                                                                console.log("Calling checkTimers in 20 seconds");
                                                                setTimeout(checkTimers, 20000);
                                                        }
                                                });


                                });


                });

        });

        // close the database connection
        db.close();
};



function removeChannel(cid, error) {
        let db = getDatabase();

        db.run(`DELETE FROM repostLocations WHERE cid=?`, cid, function (err) {
                if (err) {
                        return console.error(err.message);
                }
                console.log(`Row(s) deleted ${this.changes}`);
        });

        // close the database connection
        db.close((err) => {
                if (err) {
                        return console.error(err.message);
                }
        });

        logError(error, "channel removal from handled exception");
};

function logError(error, programLocation) {
        console.log("************************")
        console.log(error)
        var timestamp = new Date();
        var timestampDateFormatted = timestamp.toUTCString();

        let db = getDatabase();

        // TODO: need to double check the correct values are being passed to the database
        db.run(`INSERT INTO errors VALUES(?, ?, ?)`, [error, timestampDateFormatted, programLocation], function (err) { 
                if (err) {
                        return console.log(err.message);
                }
                // get the last insert id
                console.log(`An error row has been inserted with rowid ${this.lastID}`);
        });

        // close the database connection
        db.close();
}