var mysql = require('mysql');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "12345",
    database: "tracker"
});
con.connect(function(err) {
    if(err) throw err;
    console.log("Connected!");
});
function insert(rlname, type) {
    var sql = 'INSERT INTO list (id, name, type) VALUES (?,?,?)';
    con.query(sql,["NULL",rlname,type], function (err) {
        if (err) throw err;
        select(type,function(err,list) {
            if (err) throw err;
            console.log(list);
        });
    });
}
function remove(rlname,type) {
    var sql = 'DELETE FROM list WHERE name= ? && type=?';
    con.query(sql,[rlname,type], function (err) {
        if (err) throw err;
        select(type,function(err,list) {
            if (err) throw err;
            console.log(list);
        });
    });
}
function update(newname,oldname,type) {
    var sql = 'UPDATE list SET name = ? WHERE name = ? AND type =?';
    con.query(sql,[newname,oldname,type], function (err) {
        if (err) throw err;
        select(type,function(err,list) {
            if (err) throw err;
            console.log(list);
        });
    });
}
function select(type,callback) {
    let names = [];
    var sql = 'SELECT * FROM list WHERE type = ?';
    con.query(sql,[type], function (err, result) {
        if (err) throw err;
        for (let i = 0; i < result.length; i++) {
            if (result[i].name !== null) {
                names.push(result[i].name);
                //console.log(names);
            }
        }
        callback(null, names);
    });
}
function updateList(alreadyAdded, newmember, oldmember) {
    console.log(alreadyAdded);
    let index = -1;
    for (let i = 0;i<LeaderRoles.length;i++) {
        if (LeaderRoles[i][0] === oldmember.guild.id) {
            index = i;
        }
    }
    const discord = LeaderRoles[index][1];
    const nicknameChange = (oldmember.nickname !== newmember.nickname);
    if (alreadyAdded) {
        let stillRL = false;
        for (let role of newmember.roles.array()) {
            if (LeaderRoles[index].slice(2,LeaderRoles[index].length).includes(role.name)) {
                stillRL = true;
            }
        }
        if (!stillRL) {
            // implement
            if (oldmember.nickname === null) {
                remove(oldmember.user.username,discord);
            }
            else {
                remove(oldmember.nickname,discord)
            }
        }
        else {
            if (nicknameChange) {
                // implement this so it replaces oldmember nickname with newmember nickname
                if (oldmember.nickname === null) {
                    update(newmember.nickname,oldmember.user.username,discord);
                }
                else {
                    if (newmember.nickname === null) {
                        update(newmember.user.username,oldmember.nickname,discord);
                    }
                    else {
                        update(newmember.nickname,oldmember.nickname,discord);
                    }
                }
            }
        }
    }
    else {
        let isRL = false;
        for (let role of newmember.roles.array()) {
            if (LeaderRoles[index].slice(2,LeaderRoles[index].length).includes(role.name)) {
                isRL = true;
            }
        }
        if (isRL) {
            // implement
            if (newmember.nickname === null) {
                insert(newmember.user.username,discord);
            }
            else {
                if (nicknameChange) {
                    update(newmember.nickname,oldmember.nickname,discord)
                }
                else {
                    insert(newmember.nickname,discord);
                }
            }
        }
    }
}
const Discord = require("discord.js");
var LeaderRoles = [];
//private but there is stuff here

const client = new Discord.Client();

// for updating when someone gets removed role
client.on('ready',()=> {
    select("CF",function(err,list) {
        if (err) throw err;
        console.log(list);
    });
})
client.on('guildMemberUpdate', (oldmember, newmember) => {
    let index = -1;
    for (let i = 0;i<LeaderRoles.length;i++) {
        if (LeaderRoles[i][0] === oldmember.guild.id) {
            index = i;
        }
    }
    if (index !=-1) {
        const discord = LeaderRoles[index][1];
        let  alreadyAdded = false;
        if (oldmember.nickname === null) {
            select(discord,function(err,list) {
                if (err) throw err;
                alreadyAdded = list.includes(oldmember.user.username);
                updateList(alreadyAdded, newmember, oldmember);
            });
        }
        else {
            select(discord,function(err,list) {
                if (err) throw err;
                alreadyAdded = list.includes(oldmember.nickname);
                updateList(alreadyAdded, newmember, oldmember);
            });
        }
    }
})
client.on('error',() => {
    console.log(error.name);
})

client.login(