require('../connections/mysql-server');
require('../helpers/CheckToken');
require('../helpers/config');
const mysql = require('mysql');

const express = require('express');
const router = express.Router();
const bodyParser = require('body-parser');

router.use(bodyParser.urlencoded({
  limit: '10mb',
  extended: true
}));
router.use(bodyParser.json({
  limit: '10mb',
  extended: true
}));

var MySQLConfig = {
  connectionLimit: 10,
  host: Variables('ip_mysql'),
  user: Variables('user_mysql'),
  database: Variables('database_mysql'),
  password: Variables('password_mysql'),
  charset: "utf8mb4"
}

const connection = mysql.createPool(MySQLConfig);

var routes = function () {

  router.get('/getChat/:idusers_from/:idusers_to/:db', verifyJWT, (req, res) => {
    let idusers_from = req.params.idusers_from;
    let idusers_to = req.params.idusers_to;
    let banco = req.params.db;
    ExecutarQuery(`SELECT idchat, idusers_from, idusers_to, visible,
    DATE_FORMAT(time_sent, '%d/%m/%Y %h:%i') AS 'time_sent',
    DATE_FORMAT(time_read, '%d/%m/%Y %h:%i') AS 'time_read',
    message, quotation FROM chat WHERE
    (((idusers_from = ${idusers_from} AND idusers_to = ${idusers_to}) OR
    (idusers_to = ${idusers_from} AND idusers_from = ${idusers_to})) AND banco = '${banco}');`, res, req.originalUrl);
  });

  router.post('/sendMessage', verifyJWT, (req, res) => {
    const idusers_from = req.body.idusers_from;
    const idusers_to = req.body.idusers_to;
    const message = req.body.message;
    const banco = req.body.banco;
    const quotation = req.body.quotation;
    ExecutarQuery(`INSERT INTO chat(idusers_from, idusers_to, message, banco, quotation)
    VALUES (?, ?, ?, ?, ?);`, res, req.originalUrl, [idusers_from, idusers_to, message, banco, quotation]);
  });

  router.get('/setReaded/:idchat', verifyJWT, (req, res) => {
    let idchat = req.params.idchat;
    ExecutarQuery(`UPDATE chat SET
      time_read = now()
    WHERE idchat = ${idchat};`, res, req.originalUrl);
  });

  router.get('/removeMessage/:idchat', verifyJWT, (req, res) => {
    let idchat = req.params.idchat;
    ExecutarQuery(`UPDATE chat SET
      visible = 0
    WHERE idchat = ${idchat};`, res, req.originalUrl);
  });

  router.get('/getAllchats/:tipo/:idusers/:db', verifyJWT, (req, res) => {
    let tipo = req.params.tipo;
    let idusers = req.params.idusers;
    let db = req.params.db;
    let sqlQuery = ``;

    if (tipo == 1) { // PROFESSOR
      sqlQuery = `select *,
      (
        select C.NomeVersao
        from ${db}Cursos C
        where C.Codigo = (
          select top(1) TT.IDCurso
          from ${db}TreinandosTurmas TT
          where TT.IDMatricula = R.idusers_para
        )
      ) AS 'nomeCurso'
      from (
        SELECT DISTINCT TT.IDMatricula AS 'idusers_para',
        CLI.Nome AS 'nomePara',
        F.IDFuncionario AS 'idusers_de',
        F.Nome AS 'nomeDe'
        from ${db}TreinandosTurmas TT
        inner join ${db}TurmasAgendaCalendario TAC
        on TAC.IdTurma = TT.CodigoTurma
        inner join ${db}Funcionarios F
        on F.IDFuncionario = TAC.IdInstrutor
        inner join ${db}Clientes CLI
        on CLI.IDCliente = TT.IDTreinando
        /*WHERE TAC.IdInstrutor = ${idusers}*/
      ) R`;
    }
    else if (tipo == 2) { // TODOS OS USUARIOS
      sqlQuery = `select F.IDFuncionario AS 'idusers_para', F.Nome AS 'nomePara', 0 as Tipo
      from ${db}Funcionarios F
      where F.Desativado = 0
      union all
      select distinct M.IDMatricula AS 'idusers_para', C.Nome AS 'nomePara', 1 as Tipo
      from ${db}Matriculas M
      inner join ${db}TreinandosTurmas TT
      on TT.IDMatricula = M.IDMatricula
      inner join ${db}Clientes C
      on C.IDCliente = TT.IDTreinando;`;
    }
    else if (tipo == 4) { // ALUNO
      sqlQuery = `/*SELECT TOP 1 TT.IDMatricula AS 'idusers_de',
      CLI.Nome AS 'nomeDe',
      F.IDFuncionario AS 'idusers_para',
      F.Nome AS 'nomePara',
      TT.IDCurso AS 'idcourses',
      C.NomeVersao AS 'nomeCurso'
      from ${db}TreinandosTurmas TT
      inner join ${db}Cursos C
      on C.Codigo = TT.IDCurso
      inner join ${db}TurmasAgendaCalendario TAC
      on TAC.IdTurma = TT.CodigoTurma
      inner join ${db}Funcionarios F
      on F.IDFuncionario = TAC.IdInstrutor
      inner join ${db}Clientes CLI
      on CLI.IDCliente = TT.IDTreinando
      WHERE TT.IDMatricula = ${idusers} ORDER BY TT.IDCurso DESC;*/
      select F.IDFuncionario AS 'idusers_para', F.Nome AS 'nomePara', 0 as Tipo
      from ${db}Funcionarios F
      where F.Desativado = 0;`;
    }
    execSQLQuery2(sqlQuery, res, req.originalUrl);
  })

  router.post('/createGroup', verifyJWT, (req, res) => {
    const name = req.body.name;
    const created_by = req.body.created_by;
    const banco = req.body.banco;
    ExecutarQuery(`INSERT INTO chatgroups(name, created_by, banco)
    VALUES (?, ?, ?);`, res, req.originalUrl, [name, created_by, banco]);
  });

  router.post('/insertMember', verifyJWT, (req, res) => {
    const idchatgroups = parseInt(req.body.idchatgroups);
    const idusers = parseInt(req.body.idusers);
    const enabled = req.body.enabled ? 1 : 0;
    const isadm = req.body.isadm ? 1 : 0;
    const inserted_by = parseInt(req.body.inserted_by);
    ExecutarQuery(`INSERT INTO chatusergroups(idchatgroups, idusers, enabled, isadm, inserted_by)
    VALUES (${idchatgroups}, ${idusers}, ${enabled}, ${isadm}, ${inserted_by});`, res, req.originalUrl);
  });

  router.get('/getAllGroups/:idusers/:db', verifyJWT, (req, res) => {
    let idusers = req.params.idusers;
    let banco = req.params.db;
    let sqlQuery = `SELECT ${idusers} AS idusers_de, '' AS nomeDe, cu.idchatgroups AS idusers_para,
      cg.name AS nomePara, 'Grupo' AS nomeCurso, 0 AS idcourses, cu.isadm
      FROM chatusergroups cu
      INNER JOIN chatgroups cg
      ON cg.idchatgroups = cu.idchatgroups
      WHERE cu.idusers = ${idusers} AND cg.banco = "${banco}";`;
    ExecutarQuery(sqlQuery, res, req.originalUrl);
  });

  router.get('/getChatGroup/:idchatgroups/:db', verifyJWT, (req, res) => {
    let idchatgroups = req.params.idchatgroups;
    let banco = req.params.db;
    ExecutarQuery(`SELECT idchat, idusers_from, idusers_to, visible,
    DATE_FORMAT(time_sent, '%d/%m/%Y %h:%i') AS 'time_sent',
    DATE_FORMAT(time_read, '%d/%m/%Y %h:%i') AS 'time_read',
    message, quotation FROM chat WHERE idusers_to = ${idchatgroups} AND banco = "${banco}";`, res, req.originalUrl);
  });

  router.get('/getGroupMembers/:idchatgroups', verifyJWT, (req, res) => {
    const idchatgroups = parseInt(req.params.idchatgroups);
    ExecutarQuery(`SELECT
      *
    FROM chatusergroups
    WHERE idchatgroups = ${idchatgroups};`, res, req.originalUrl);
  });

  router.get('/removeMember/:idchatusergroups', verifyJWT, (req, res) => {
    const idchatusergroups = parseInt(req.params.idchatusergroups);
    ExecutarQuery(`DELETE FROM chatusergroups
    WHERE idchatusergroups = ${idchatusergroups};`, res, req.originalUrl);
  });

  router.get('/updateGroupname/:idchatgroups/:name', verifyJWT, (req, res) => {
    const idchatgroups = parseInt(req.params.idchatgroups);
    const name = req.params.name;
    ExecutarQuery(`UPDATE chatgroups SET
      name = ?
    WHERE idchatgroups = ?;`, res, req.originalUrl, [name, idchatgroups]);
  });

  router.get('/deleteGroup/:idchatgroups', verifyJWT, (req, res) => {
    let idchatgroups = req.params.idchatgroups;
    mysqlConnection.query(`DELETE FROM chatusergroups WHERE idchatgroups = ${idchatgroups};`, function (error, results, fields) {
      console.log(error);
      console.log(results);
      ExecutarQuery(`DELETE FROM chatgroups WHERE idchatgroups = ${idchatgroups};`, res, req.originalUrl);
    });
  });

  router.get('/getAllchatsViewer/:db', verifyJWT, (req, res) => {
    let db = req.params.db;
    let sqlQuery = `SELECT DISTINCT
    R.idusers_from, R.idusers_to, R.banco,
    (SELECT c1.message FROM chat c1 WHERE c1.idusers_from = R.idusers_from AND c1.idusers_to = R.idusers_to ORDER BY c1.time_sent DESC LIMIT 1) AS 'message',
    (SELECT date_format(c1.time_sent, '%d/%m/%Y %h:%i') FROM chat c1 WHERE c1.idusers_from = R.idusers_from AND c1.idusers_to = R.idusers_to ORDER BY c1.time_sent ASC LIMIT 1) AS 'time_begin',
    (SELECT date_format(c1.time_sent, '%d/%m/%Y %h:%i') FROM chat c1 WHERE c1.idusers_from = R.idusers_from AND c1.idusers_to = R.idusers_to ORDER BY c1.time_sent DESC LIMIT 1) AS 'time_last',
    (SELECT COUNT(*) FROM chat c1 WHERE c1.idusers_from = R.idusers_from AND c1.idusers_to = R.idusers_to ORDER BY c1.time_sent DESC LIMIT 1) AS 'amout',
    case when R.idusers_to < 1250 then (select cg.name from chatgroups cg where cg.idchatgroups = R.idusers_to) else null end as 'group'
    FROM
    (select distinct * from (
    select c1.idusers_from, c1.idusers_to, c1.banco from chat c1
    union all
    select c1.idusers_from as 'idusers_to', c1.idusers_to as 'idusers_from', c1.banco from chat c1) R
    WHERE R.idusers_from >= 1250 and R.idusers_from < 190000 and R.banco = '${db}'
    order by R.idusers_to, R.idusers_from ASC) R
    ORDER BY time_last DESC;`;
    ExecutarQuery(sqlQuery, res, req.originalUrl);
  })

  return router;
};

module.exports = routes;

function ExecutarQuery(sqlQuery, res, url, Parametros = null) {
  connection.query(sqlQuery, Parametros, (error, results) => {
    if (!error) {
      res.send(JSON.stringify({
        "status": 200,
        "error": error,
        "response": results
      }));
      console.log(url + " success 200");
    }
    else {
      res.send(JSON.stringify({
        "status": 500,
        "error": error,
        "response": results
      }));
      console.log(url + " error 500");
    }
  })
}