require('../connections/mysql-server');
require('../connections/sql-server');
require('../helpers/CheckToken');
require('../helpers/config');
const express = require('express');
const router = express.Router();
const bodyParser = require('body-parser');
const formidable = require('formidable');
const fs = require('fs');
const { userInfo } = require('os');

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

var routes = function () {

  router.get('/getVideos/:idFolder', verifyJWT, (req, res) => {
    const idFolder = parseInt(req.params.idFolder);
    execSQLQuery(`SELECT
      *
    FROM franchisee_media
    WHERE franchisee_media.type = 'video' AND franchisee_media.folder_key = ${idFolder};`, res, req.originalUrl);
  });

  router.get('/getFiles/:idFolder/:userFor', verifyJWT, (req, res) => {
    const idFolder = parseInt(req.params.idFolder);
    const userFor = parseInt(req.params.userFor);
    execSQLQuery(`SELECT
      *
    FROM franchisee_media
    WHERE ((franchisee_media.type = 'file' OR franchisee_media.type = 'download'
    OR franchisee_media.type = 'img') AND franchisee_media.folder_key = ${idFolder}) AND userFor = ${userFor}
    ORDER BY description ASC;`, res, req.originalUrl);
  });


  router.get('/getAllFoldersV', verifyJWT, (req, res) => {
    execSQLQuery(`select * from (
      select
        ff.idfranchisee_folder,
          ff.folder_type,
          ff.folder_description,
          ff.folder_into
      from franchisee_folders ff
      where ff.folder_type = 'videos' and ff.folder_into = 0
      union all
      select
        ff.idfranchisee_folder,
          ff.folder_type,
          concat(ff.folder_description, ' - (', ff2.folder_description, '/', ff.folder_description, ')') as 'folder_description',
          ff.folder_into
      from franchisee_folders ff
      inner join franchisee_folders ff2 on ff2.idfranchisee_folder = ff.folder_into
      where ff.folder_type = 'videos' and ff.folder_into > 0) R
    order by R.folder_description asc`, res, req.originalUrl);
  });

  router.get('/getFoldersV', verifyJWT, (req, res) => {
    execSQLQuery(`SELECT
      *
    FROM franchisee_folders
    WHERE folder_type = 'videos' AND folder_into = 0
    order by folder_description asc;`, res, req.originalUrl);
  });

  router.get('/getFoldersV2/:idFolder', verifyJWT, (req, res) => {
    const idFolder = parseInt(req.params.idFolder);
    execSQLQuery(`SELECT
      *
    FROM franchisee_folders
    WHERE folder_type = 'videos' AND folder_into = ${idFolder}
    order by folder_description asc;`, res, req.originalUrl);
  });


  router.get('/getAllFoldersF/:userFor', verifyJWT, (req, res) => {
    const userFor = parseInt(req.params.userFor);
    execSQLQuery(`select * from (
      select
        ff.idfranchisee_folder,
          ff.folder_type,
          ff.folder_description,
          ff.folder_into
      from franchisee_folders ff
      where ff.folder_type = 'files' and ff.folder_into = 0 and ff.userFor = ${userFor}
      union all
      select
        ff.idfranchisee_folder,
          ff.folder_type,
          concat(ff.folder_description, ' - (', ff2.folder_description, '/', ff.folder_description, ')') as 'folder_description',
          ff.folder_into
      from franchisee_folders ff
      inner join franchisee_folders ff2 on ff2.idfranchisee_folder = ff.folder_into
      where ff.folder_type = 'files' and ff.folder_into > 0 and ff.userFor = ${userFor}) R
    order by R.folder_description asc`, res, req.originalUrl);
  });

  router.get('/getFoldersF/:userFor', verifyJWT, (req, res) => {
    const userFor = parseInt(req.params.userFor);
      execSQLQuery(`
      SELECT
        *
      FROM
        franchisee_folders
      WHERE
        franchisee_folders.folder_type = 'files' AND franchisee_folders.folder_into = 0
        AND franchisee_folders.userFor = ${userFor}
      ORDER BY folder_description ASC;`,
        res,
        req.originalUrl
  );})

  router.get('/getFoldersF2/:idFolder/:userFor', verifyJWT, (req, res) => {
    const idFolder = parseInt(req.params.idFolder);
    const userFor = parseInt(req.params.userFor);
    execSQLQuery(`SELECT
      *
    FROM franchisee_folders
    WHERE folder_into = ${idFolder} AND folder_type != 'DESATIVADO' AND userFor = ${userFor}
    ORDER BY folder_description ASC;`, res, req.originalUrl);
  });


  router.get('/Exclude/:idfranchisee_media', verifyJWT, (req, res) => {
      const idfranchisee = parseInt(req.params.idfranchisee_media);
      execSQLQuery(`
      DELETE FROM
        franchisee_media
      WHERE
        franchisee_media.idfranchisee_media = ${idfranchisee}`,
        res,
        req.originalUrl
  );})

  router.get('/Disable/:idfranchisee_media', verifyJWT, (req, res) => {
    const idfranchisee = parseInt(req.params.idfranchisee_media);
    execSQLQuery(`UPDATE franchisee_media SET type = 'DESATIVADO' WHERE idfranchisee_media = ${idfranchisee}`, res, req.originalUrl);
  })

  router.get('/ExcludeF/:idfranchisee_folder', verifyJWT, (req, res) => {
        const idfranchisee = parseInt(req.params.idfranchisee_folder);
        execSQLQuery(`
        DELETE FROM
          franchisee_folders
        WHERE
          idfranchisee_folder = ${idfranchisee}`,
          res,
          req.originalUrl
  );})

  router.get('/getAll', verifyJWT, (req, res) => {
      execSQLQuery(`
      SELECT
        *
      FROM
        franchisee_media`,
        res,
        req.originalUrl
      );
  })

  router.get('/getAllWithDescription', verifyJWT, (req, res) => {
    execSQLQuery(`
    SELECT
      fm.idfranchisee_media,
      fm.description,
      fm.publishid,
      fm.type,
      fm.folder_key,
      ff.folder_description
    FROM
      franchisee_media fm
    INNER JOIN
      franchisee_folders ff ON ff.idfranchisee_folder = fm.folder_key;`,
      res,
      req.originalUrl
    );
  })

  router.get('/getAllF', verifyJWT, (req, res) => {
        execSQLQuery(`
        SELECT
          *
        FROM
          franchisee_folders`,
          res,
          req.originalUrl
        );
  })

  router.get('/getOne/:idfranchisee_media/:userFor', verifyJWT, (req, res) => {
    const idfranchisee_media = parseInt(req.params.idfranchisee_media);
    const userFor = parseInt(req.params.userFor);
    execSQLQuery(`
    SELECT
      *
    FROM
      franchisee_media
      WHERE
      idfranchisee_media = ${idfranchisee_media} AND userFor = ${userFor}`,
      res,
      req.originalUrl
    );
  })

  router.get('/getOneF/:idfranchisee_folder/:userFor', verifyJWT, (req, res) => {
    const idfranchisee_folder = parseInt(req.params.idfranchisee_folder);
    const userFor = parseInt(req.params.userFor);
    execSQLQuery(`SELECT
      *
    FROM franchisee_folders
    WHERE franchisee_folders.idfranchisee_folder = ${idfranchisee_folder}
    AND franchisee_folders.userFor = ${userFor};`, res, req.originalUrl);
  });

  router.patch('/update', verifyJWT, (req, res) => {
    const idfranchisee_media = req.body.idfranchisee_media;
    const description = req.body.description;
    const type = req.body.type;
    const publishid = req.body.publishid;
    const folder_key = req.body.folder_key;
    const userFor = req.body.userFor;
    execSQLQuery(`
    UPDATE
      franchisee_media
    SET
      description = "${description}",
      publishid = '${publishid}',
      type = '${type}',
      folder_key = ${folder_key},
      userFor = ${userFor}
    WHERE
      idfranchisee_media = ${idfranchisee_media}`,
      res,
      req.originalUrl
    );
  })

  router.post('/insert', verifyJWT, (req, res) => {
    const description = req.body.description;
    const type = req.body.type;
    const publishid = req.body.publishid;
    const folder_key = req.body.folder_key;
    const userFor = req.body.userFor;
    execSQLQuery(`INSERT INTO franchisee_media (description, type, publishid, folder_key, userFor)
    VALUES (?, ?, ?, ?, ?);`, res, req.originalUrl, [description, type, publishid, folder_key, userFor]);
  });

  router.post('/insertF', verifyJWT, (req, res) => {
    const folder_type = req.body.folder_type;
    const folder_description = req.body.folder_description;
    const folder_into = req.body.folder_into;
    const userFor = req.body.userFor;
    execSQLQuery(`INSERT INTO franchisee_folders (folder_type, folder_description, folder_into, userFor)
    VALUES (?, ?, ?, ?);`, res, req.originalUrl, [folder_type, folder_description, folder_into, userFor]);
  });

  router.post('/copy', verifyJWT, (req, res) => {
  var base64ToImage = require('base64-to-image');
  var id = req.body.id;
  var backStr = req.body.back;
  var backThumb = req.body.backThumb;
  var backName = 'franch_' + id;
  var backNameT = 'franchThumb_' + id;
  var Objback = { 'fileName': backName, 'type': 'jpg' };
  var ObjbackT = { 'fileName': backNameT, 'type': 'jpg' };
  var path = Variables('src_media') + 'images/';
  if(base64ToImage(backStr, path, Objback)){
    if (base64ToImage(backThumb, path, ObjbackT)){
      res.send(true);
    }else{res.send(false);}
  }else {res.send(false);}
  });

  router.post('/copy1', verifyJWT, (req, res) => {
    let form = new formidable.IncomingForm();
    form.maxFileSize = 600 * 1024 * 1024;
    form.parse(req, function(err,fields,files){
      const fs = require('fs');
      var id = fields.id;
      var backThumb = fields.backThumb;
      var dir = Variables('src_media') + 'images/';
      var extension = files.back.name.toString();
      var arrExt = extension.split('.');
      extension = "." + arrExt[arrExt.length - 1];
      console.log("name: " + id + " - dir: " + dir + " - extension: " + extension);
      try {
        fs.unlink(dir + "franch_" + id + extension, function(err) {});
      }
      catch(err) {
        console.log("arquivo não existe");
      }
      var oldpath = files.back.path;
      fs.rename(oldpath, dir + "franch_" + id + extension, function (err) {
        if (err) {
          res.send(false);
          throw err;
        }
        else {
          var base64ToImage = require('base64-to-image');
          var ObjbackT = { 'fileName': 'franchThumb_' + id, 'type': 'jpg' };
          if (base64ToImage(backThumb, dir, ObjbackT)) {
            res.send(true);
          }
          else {
            res.send(false);
          }
        }
      });
    });
  });

  router.post('/copyFile', (req, res) => {

        var localPath = req.body.back;
        var filetoupload = req.body.filetoupload;
        var form = new formidable.IncomingForm();
        console.log(localPath);
        form.parse(req, function (_err, _fields, files) {
          console.log("Entrou no parse");
          var oldpath = localPath;
          console.log(oldpath);
          var newpath = Variables('src_media') + 'franchiseeZip/' + filetoupload.name;
          console.log("EntrouCopy");
          fs.rename(toString(oldpath), toString(newpath), function (err) {
            console.log("EntrouCopy2");
            if (err) throw err;
            console.log("EntrouCopy3");
            res.send(true);
          });
        });
    /*} else {
      res.writeHead(200, {'Content-Type': 'text/html'});
      res.write('<form action="fileupload" method="post" enctype="multipart/form-data">');
      res.write('<input type="file" name="filetoupload"><br>');
      res.write('<input type="submit">');
      res.write('</form>');
      return res.end();
    }*/
  });

  router.post('/copyPdf', verifyJWT, (req, res) => {
  var base64 = require('base64topdf');
  var id = req.body.id;
  var backStr = req.body.back;
  var backName = 'franch_' + id;
  var Objback = { 'fileName': backName, 'type': 'pdf' };
  var path = Variables('src_media') + 'images/';
  if(base64.base64Decode(backStr, backName.toString())){
    res.send(true);
  }else {res.send(false);}
  });

  router.post('/copyZip', verifyJWT, (req, res) => {
  var unzip = require('unzip');
  var fs = require('fs');
  const file = req.body.file;
  const name = req.body.name;
  var dir = Variables('src_media') + 'franchiseeZip/' + name;
  if (!fs.existsSync(dir)) {
      fs.mkdirSync(dir);
  }
  var path = dir + '/';
  var data = file.replace(/^data:application\/\x-zip-compressed;base64,/, '');
  //var data = file.replace(/^data:image\/\w+;base64,/, '');
  fs.writeFile(path + name + '.zip', data, {encoding: 'base64'}, function(err){
      setTimeout(() => {
          console.log('Copiado com sucesso!');
          res.send(true);
          } , 3000);
  });
  });

  router.post('/copy2', verifyJWT, (req, res) => {
    let form = new formidable.IncomingForm();
    form.maxFileSize = 600 * 1024 * 1024;
    form.parse(req, function(err,fields,files){
      const fs = require('fs');
      var name = fields.name;
      var dir = Variables('src_media') + 'franchiseeZip/' + name + "/";
      var extension = files.file.name.toString();
      var arrExt = extension.split('.');
      extension = "." + arrExt[arrExt.length - 1];
      console.log("name: " + name + " - dir: " + dir + " - extension: " + extension);
      if (!fs.existsSync(dir)) {
        console.log("criar diretorio");
        fs.mkdirSync(dir);
      }
      else {
        try {
          fs.unlink(dir + name + extension, function(err) {});
        }
        catch(err) {
          console.log("arquivo não existe");
        }
      }
      var oldpath = files.file.path;
      fs.rename(oldpath, dir + name + extension, function (err) {
        if (err) {
          res.send(false);
          throw err;
        }
        res.send(true);
      });
    });
  });

  router.patch('/updateF', verifyJWT, (req, res) => {
  const idfranchisee_folder = req.body.idfranchisee_folder;
  const folder_type = req.body.folder_type;
  const folder_description = req.body.folder_description;
  const folder_into = req.body.folder_into;
  const userFor = req.body.userFor;
  execSQLQuery(`UPDATE franchisee_folders SET
    folder_type = ?,
    folder_description = ?,
    folder_into = ?,
    userFor = ?
  WHERE idfranchisee_folder = ?;`, res, req.originalUrl, [folder_type, folder_description, folder_into, userFor, idfranchisee_folder]);
  });


  router.get('/getNomesAlunos/:banco', (req, res) => {
    const db = req.params.banco;
    execSQLQuery2(`SELECT distinct
      tt.IDMatricula,
      cl.Nome
    FROM ${db}TreinandosTurmas tt
    INNER JOIN ${db}Clientes cl ON cl.IDCliente = tt.IDTreinando;`, res, req.originalUrl);
  });


  // LEADS
  router.get('/getLeadsEventos', verifyJWT, (req, res) => {
    execSQLQuery(`select
      *
    FROM leadseventos
    ORDER BY descricao ASC;`, res, req.originalUrl);
  });

  router.get('/getLeadsSite/:idunits/:idleadseventos', verifyJWT, (req, res) => {
    const idunits = parseInt(req.params.idunits);
    const idleadseventos = parseInt(req.params.idleadseventos);
    const SQLQuery = `select
      ls.*,
      un.unityname as 'unidade',
      date_format(ls.insert_date, '%d/%m/%Y %H:%i') as 'data',
      case when ls.update_date is not null then 'Sim' else 'Não' end as 'visto',
      case when ls.contatado = 1 then 'Sim' else 'Não' end as 'cont'
    FROM leadssite ls
    inner join units un on un.idunits = ls.idunits
    WHERE ${(idunits == 0) ? 'true' : 'ls.idunits = ' + idunits} and ls.idleadseventos = ${idleadseventos}
    ORDER BY un.unityname asc, ls.nome ASC;`;
    execSQLQuery(SQLQuery, res, req.originalUrl);
  });

  router.get('/updateVistoLead/:idleadssite', verifyJWT, (req, res) => {
    const idleadssite = parseInt(req.params.idleadssite);
    execSQLQuery(`update leadssite set update_date = now() where idleadssite = ${idleadssite};`, res, req.originalUrl);
  });

  router.get('/updateContatadoLead/:idleadssite', verifyJWT, (req, res) => {
    const idleadssite = parseInt(req.params.idleadssite);
    execSQLQuery(`update leadssite set contatado = 1 where idleadssite = ${idleadssite};`, res, req.originalUrl);
  });

  return router;
};

module.exports = routes;