require('../connections/mysql-server');
require('../helpers/CheckToken');
const express = require('express');
const router = express.Router();
const bodyParser = require('body-parser');

router.use(bodyParser.urlencoded({ extended: true }));
router.use(bodyParser.json());

var routes = function () {

  router.get('/getAllPermissoes', verifyJWT, (req, res) => {
    execSQLQuery(`SELECT * from permissoes;`, res, req.originalUrl);
  });

  router.get('/getPermissoesUsuario/:idusers/:idusertype', verifyJWT, (req, res) => {
    const idusers = parseInt(req.params.idusers);
    const idusertype = parseInt(req.params.idusertype);
    execSQLQuery(`select distinct * from (
      select
        pm.idusers, pm.idjanelas, jn.descricao, jn.title, jn.icon, jn.link, jn.home, jn.children
      from permissoes pm
      inner join janelas jn on jn.idjanelas = pm.idjanelas
      inner join users us on us.idusers = pm.idusers
      where pm.idusers = ${idusers}
      union all
      select
        ${idusers} as 'idusers', up.idjanelas, jn2.descricao, jn2.title, jn2.icon, jn2.link, jn2.home, jn2.children
      from usertypepermissoes up
      inner join janelas jn2 on jn2.idjanelas = up.idjanelas
      where up.idusertype = ${idusertype}) R
    order by R.children asc, R.title asc;`, res, req.originalUrl);
  });

  router.get('/getAllJanelas', verifyJWT, (req, res) => {
    execSQLQuery(`SELECT * from janelas order by descricao asc;`, res, req.originalUrl);
  });

  router.post('/insertPermissoesTipoUsuario', verifyJWT, (req, res) => {
    const Permissoes = req.body.ListaPermissoes;
    let SQLInsert = `insert into usertypepermissoes (idusertype, idjanelas) values `;
    for (let i = 0; i < Permissoes.length; i++) {
      if (i > 0)
        SQLInsert += `, `;
      SQLInsert += `(${Permissoes[i].idusertype}, ${Permissoes[i].idjanelas})`;
    }
    const SQLDelete = `delete from usertypepermissoes where idusertype = ${Permissoes[0].idusertype};`;
    mysqlConnection.query(SQLDelete, null, function (error, results, fields) {
      execSQLQuery(SQLInsert, res, req.originalUrl);
    });
  });

  router.post('/insertPermissoesUsuario', verifyJWT, (req, res) => {
    const Permissoes = req.body.ListaPermissoes;
    let SQLInsert = `insert into permissoes (idusers, idjanelas) values `;
    for (let i = 0; i < Permissoes.length; i++) {
      if (i > 0)
        SQLInsert += `, `;
      SQLInsert += `(${Permissoes[i].idusers}, ${Permissoes[i].idjanelas})`;
    }
    const SQLDelete = `delete from permissoes where idusers = ${Permissoes[0].idusers};`;
    mysqlConnection.query(SQLDelete, null, function (error, results, fields) {
      execSQLQuery(SQLInsert, res, req.originalUrl);
    });
  });

  router.get('/insertAllPermissoesUsuario/:idusers', (req, res) => {
    const idusers = parseInt(req.params.idusers);
    mysqlConnection.query(`select * from janelas;`, null, function (error, resJanelas, fields) {
      let SQLInsert = "insert into permissoes (idusers, idjanelas) values ";
      for (let i = 0; i < resJanelas.length; i++) {
        if (i > 0)
          SQLInsert += `, `;
        SQLInsert += `(${idusers}, ${resJanelas[i]['idjanelas']})`;
      }
      execSQLQuery(SQLInsert, res, req.originalUrl);
    });
  });

  router.get('/insertAllPermissoesUsertype/:idusertype', (req, res) => {
    const idusertype = parseInt(req.params.idusertype);
    mysqlConnection.query(`select * from janelas;`, null, function (error, resJanelas, fields) {
      let SQLInsert = "insert into usertypepermissoes (idusertype, idjanelas) values ";
      for (let i = 0; i < resJanelas.length; i++) {
        if (i > 0)
          SQLInsert += `, `;
        SQLInsert += `(${idusertype}, ${resJanelas[i]['idjanelas']})`;
      }
      execSQLQuery(SQLInsert, res, req.originalUrl);
    });
  });

  router.post('/insertJanela', verifyJWT, (req, res) => {
    const idjanelas = parseInt(req.body.idjanelas);
    const descricao = req.body.descricao;
    const title = req.body.title;
    const icon = req.body.icon;
    const link = req.body.link;
    const home = req.body.home ? 1 : 0;
    const children = req.body.children;
    execSQLQuery(`insert into janelas (descricao, title, icon, link, home, children) values
    (?, ?, ?, ?, ?, ?);`, res, req.originalUrl, [descricao, title, icon, link, home, children]);
  });

  router.patch('/updateJanela', verifyJWT, (req, res) => {
    const idjanelas = parseInt(req.body.idjanelas);
    const descricao = req.body.descricao;
    const title = req.body.title;
    const icon = req.body.icon;
    const link = req.body.link;
    const home = req.body.home ? 1 : 0;
    const children = req.body.children;
    execSQLQuery(`update janelas set descricao = ?, title = ?, icon = ?, link = ?, home = ?, children = ? where idjanelas = ?;`, res, req.originalUrl, [descricao, title, icon, link, home, children, idjanelas]);
  });

  return router;
};

module.exports = routes;