// Load up the discord.js library
const Discord = require("discord.js");

// This is your client. Some people call it `bot`, some people call it `self`,
// some might call it `cootchie`. Either way, when you see `client.something`, or `bot.something`,
// this is what we're refering to. Your client.
const client = new Discord.Client();

// Here we load the config.json file that contains our token and our prefix values.
const config = require("./config.json");
// config.token contains the bot's token
// config.prefix contains the message prefix.


var mysql = require('mysql');

var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "root",
  database: "bcq_reclutamiento"
});

var servers_id = {
    'FdQ': '428874791957561350'
}

con.connect(function(err) {
  if (err) throw err;
  console.log("Connected! to DB");
});

client.on("ready", () => {
  // This event will run if the bot starts, and logs in, successfully.
  console.log(`Bot has started, with ${client.users.size} users, in ${client.channels.size} channels of ${client.guilds.size} guilds.`);
  // Example of changing the bot's playing game to something useful. `client.user` is what the
  // docs refer to as the "ClientUser".
  client.user.setActivity(`Serving ${client.guilds.size} servers`);
});

client.on("guildCreate", guild => {
  // This event triggers when the bot joins a guild.
  console.log(`New guild joined: ${guild.name} (id: ${guild.id}). This guild has ${guild.memberCount} members!`);
  client.user.setActivity(`Serving ${client.guilds.size} servers`);
});

client.on("guildDelete", guild => {
  // this event triggers when the bot is removed from a guild.
  console.log(`I have been removed from: ${guild.name} (id: ${guild.id})`);
  client.user.setActivity(`Serving ${client.guilds.size} servers`);
});

client.on("message", async message => {
  // This event will run on every single message received, from any channel or DM.

  // It's good practice to ignore other bots. This also makes your bot ignore itself
  // and not get into a spam loop (we call that "botception").
  if(message.author.bot) return;

  // Also good practice to ignore any message that does not start with our prefix,
  // which is set in the configuration file.
  if(message.content.indexOf(config.prefix) !== 0) return;

  // Here we separate our "command" name, and our "arguments" for the command.
  // e.g. if we have the message "+say Is this the real life?" , we'll get the following:
  // command = say
  // args = ["Is", "this", "the", "real", "life?"]
  const args = message.content.slice(config.prefix.length).trim().split(/ +/g);
  const command = args.shift().toLowerCase();

  // Comando para que la gente se apunte como reclutas
  if(command === "reclutar") {
    const originalMessage = message;

    const createdByID = message.author.id;
    const createdByUsername = message.author.username;
    const createdByDiscriminator = message.author.discriminator;

    var sql = "SELECT id FROM reclutamientos WHERE user_id = ?";
    con.query(sql, [createdByID] ,function (err, result) {
        if (err) {
            originalMessage.author.send("No podemos unirte a la banda debido a que estamos de mantenimiento, no te preocupes y vuelve a intentarlo mas tarde");
        } else {
            if (result.length == 0){
                var sql = "INSERT INTO reclutamientos (user_id, user_name, user_discriminator) VALUES ? ";
                var values = [[createdByID, createdByUsername, createdByDiscriminator]];
                con.query(sql, [values], function (err, result) {
                    if (err) {
                        originalMessage.author.send("No podemos unirte a la banda debido a que estamos de mantenimiento, no te preocupes y vuelve a intentarlo mas tarde");
                    } else {
                        originalMessage.author.send("Te incluimos en nuestro sistema de reclutamiento, cuando tengamos hueco recibiras un mensaje para poder unirte");
                    }
                });
            } else {
                originalMessage.author.send("No podemos unirte a la banda debido a que ya estas en reclutamiento");
            }
        }

      });

    message.delete();
}

// Comando para que podamos ver la lista
if(command === "lista") {

    if(!message.member.roles.some(r=>["Fundador", "Capitan"].includes(r.name)) )
      return message.reply("Tu no puedes hacer esto");

    const sayMessage = args.join(" ");
    quantity = parseInt(sayMessage);

    var sql = "SELECT user_name, user_discriminator, DATE_FORMAT(joined_at, '%d/%m/%Y - %H:%i:%s') as joined_at FROM reclutamientos WHERE recluted_at IS NULL AND in_band = 0 LIMIT ";
    var limit = "20";
    var mensaje = '';

    if (sayMessage.length > 0 && !isNaN(quantity) && quantity < 50){
        limit = quantity;
    }

    sql += limit;
    console.log(sql);
    con.query(sql, function (err, result) {
        if (err) {
           return message.reply("No hemos podido obtener una lista de reclutados");
        } else {
            console.log(result);
            if(result.length == 0){
                return message.reply(mensaje = "Actualmente no existen reclutas esperando");
            } else {
                result.forEach(function(recluta) {
                    console.log("recluta");
                    mensaje += `\nNombre: **${recluta.user_name}#${recluta.user_discriminator}** (__${recluta.joined_at}__)`;
                })
                return message.reply(mensaje);
            }
        }
    })
}

// Comando para aceptar a gente
if(command === "aceptar") {

    if(!message.member.roles.some(r=>["Fundador", "Capitan"].includes(r.name)) )
      return message.reply("Tu no puedes hacer esto");

    const sayMessage = args.join(" ");

    var mensaje = '';

    if (sayMessage.length > 0){
        user = sayMessage;
        var sql = "SELECT id, user_id FROM reclutamientos WHERE CONCAT(user_name, '#', user_discriminator) = ? AND recluted_at IS NULL AND in_band = 0";
        con.query(sql, [user], function (err, result) {
            if (err) {
                return message.reply("No hemos podido obtener una lista de reclutados");
            } else {
                if(result.length == 0){
                    return message.reply("No hemos encontrado un recluta con ese nombre");
                } else {
                    result.forEach(function(recluta) {
                        var sql = "UPDATE reclutamientos SET recluted_at = NOW() WHERE id = ?";
                        con.query(sql, [recluta.id], function (err, result) {
                            if (err) {
                                mensaje += "No hemos podido actualizar a ese recluta";
                            } else {
                                mensaje += "Recluta marcado para ser reclutado, tiene 12 horas para unirse a la banda";
                                client.users.get(recluta.user_id).send("Bienvenido.");
                            }
                            return message.reply(mensaje);
                        });
                    })
                }
            }
        })
    } else {
        return message.reply("Debes introducir un nombre de recluta");
    }

}

if(command === "probar"){
    console.log(client.guilds.get(servers_id.FdQ).users.get('428882147961733122').send("hww"));
    //console.log(client.users.get('428882147961733122'));
    //console.log(client.users.get('242265845127053312'));
}

// Comando para ver lista de pendientes
if(command === "pendientes") {

    if(!message.member.roles.some(r=>["Fundador", "Capitan"].includes(r.name)) )
    return message.reply("Tu no puedes hacer esto");

    const sayMessage = args.join(" ");
    quantity = parseInt(sayMessage);

    var sql = "SELECT user_name, user_discriminator, DATE_FORMAT(recluted_at, '%d/%m/%Y - %H:%i:%s') as recluted_at FROM reclutamientos WHERE recluted_at IS NOT NULL AND in_band = 0 LIMIT ";
    var limit = "20";
    var mensaje = '';

    if (sayMessage.length > 0 && !isNaN(quantity) && quantity < 50){
        limit = quantity;
    }

    sql += limit;
    con.query(sql, function (err, result) {
        if (err) {
           return message.reply("No hemos podido obtener una lista de reclutados pendientes");
        } else {
            if(result.length == 0){
                return message.reply(mensaje = "Actualmente no existen reclutas pendientes de confirmar en banda");
            } else {
                result.forEach(function(recluta) {
                    mensaje += `\nNombre: **${recluta.user_name}#${recluta.user_discriminator}** (ACEPTADO: __${recluta.recluted_at}__)`;
                })
                return message.reply(mensaje);
            }
        }
    })
}
  /*if(command === "say") {
    // makes the bot say something and delete the message. As an example, it's open to anyone to use.
    // To get the "message" itself we join the `args` back into a string with spaces:
    const sayMessage = args.join(" ");
    // Then we delete the command message (sneaky, right?). The catch just ignores the error with a cute smiley thing.
    message.delete().catch(O_o=>{});
    // And we get the bot to say the thing:
    message.channel.send(sayMessage);
  }

  if(command === "kick") {
    // This command must be limited to mods and admins. In this example we just hardcode the role names.
    // Please read on Array.some() to understand this bit:
    // https://developer.mozilla.org/en/docs/Web/JavaScript/Reference/Global_Objects/Array/some?
    if(!message.member.roles.some(r=>["Administrator", "Moderator"].includes(r.name)) )
      return message.reply("Sorry, you don't have permissions to use this!");

    // Let's first check if we have a member and if we can kick them!
    // message.mentions.members is a collection of people that have been mentioned, as GuildMembers.
    // We can also support getting the member by ID, which would be args[0]
    let member = message.mentions.members.first() || message.guild.members.get(args[0]);
    if(!member)
      return message.reply("Please mention a valid member of this server");
    if(!member.kickable)
      return message.reply("I cannot kick this user! Do they have a higher role? Do I have kick permissions?");

    // slice(1) removes the first part, which here should be the user mention or ID
    // join(' ') takes all the various parts to make it a single string.
    let reason = args.slice(1).join(' ');
    if(!reason) reason = "No reason provided";

    // Now, time for a swift kick in the nuts!
    await member.kick(reason)
      .catch(error => message.reply(`Sorry ${message.author} I couldn't kick because of : ${error}`));
    message.reply(`${member.user.tag} has been kicked by ${message.author.tag} because: ${reason}`);

  }

  if(command === "ban") {
    // Most of this command is identical to kick, except that here we'll only let admins do it.
    // In the real world mods could ban too, but this is just an example, right? ;)
    if(!message.member.roles.some(r=>["Administrator"].includes(r.name)) )
      return message.reply("Sorry, you don't have permissions to use this!");

    let member = message.mentions.members.first();
    if(!member)
      return message.reply("Please mention a valid member of this server");
    if(!member.bannable)
      return message.reply("I cannot ban this user! Do they have a higher role? Do I have ban permissions?");

    let reason = args.slice(1).join(' ');
    if(!reason) reason = "No reason provided";

    await member.ban(reason)
      .catch(error => message.reply(`Sorry ${message.author} I couldn't ban because of : ${error}`));
    message.reply(`${member.user.tag} has been banned by ${message.author.tag} because: ${reason}`);
  }

  if(command === "purge") {
    // This command removes all messages from all users in the channel, up to 100.

    // get the delete count, as an actual number.
    const deleteCount = parseInt(args[0], 10);

    // Ooooh nice, combined conditions. <3
    if(!deleteCount || deleteCount < 2 || deleteCount > 100)
      return message.reply("Please provide a number between 2 and 100 for the number of messages to delete");

    // So we get our messages, and delete them. Simple enough, right?
    const fetched = await message.channel.fetchMessages({limit: deleteCount});
    message.channel.bulkDelete(fetched)
      .catch(error => message.reply(`Couldn't delete messages because of: ${error}`));
  }*/
});

client.login(config.token);