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");

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

var routes = function () {

  router.get("/getQtdeChamadas/:idusers/:idcourses/:db", verifyJWT,(req, res) => {
    const idusers = parseInt(req.params.idusers);
    const idcourses = req.params.idcourses;
    const db = req.params.db;
    let sqlQuery = `
      SELECT
        co.idcourses AS IDCurso,
        CASE WHEN (SELECT COUNT(1) FROM zoeusers zu INNER JOIN zoeunits zun ON zun.id = zu.idunits WHERE CONCAT(zun.id, '.dbo.') = '${db}' AND zu.user = ${idusers} AND zu.idzoeusertype = 1) > 0 THEN 100 ELSE COUNT(cl.idclasses) END AS qtde_presencas
      FROM courses co
      LEFT JOIN classes cl ON cl.idcourses = co.idcourses AND cl.disabled = 0
      WHERE co.idcourses IN ${idcourses}
      GROUP BY co.idcourses
      ORDER BY co.idcourses ASC;`;
    //console.log(sqlQuery);
    execSQLQuery(sqlQuery, res, req.originalUrl);
  });

  router.get("/getQtdeChamadasOld/:idusers/:idcourses/:db", verifyJWT,(req, res) => {
    const idusers = parseInt(req.params.idusers);
    const idcourses = req.params.idcourses;
    const db = req.params.db;
    let sqlQuery = `
      SELECT
        c.IDCurso,
        COUNT(*) AS qtde_presencas
      FROM ${db}Chamadas c
      INNER JOIN ${db}TreinandosTurmas tt ON tt.CodigoTurma = c.IDTurma AND tt.IDCurso = c.IDCurso
      INNER JOIN ${db}ChamadasDetalhes cd ON cd.IDChamadas = c.IDChamadas
      INNER JOIN ${db}Cursos cu ON cu.Codigo = c.IDCurso
      WHERE c.Flex = 1 AND c.IDCurso IN ${idcourses} AND tt.IDMatricula = ${idusers} AND cd.Presente = 1
      GROUP BY c.IDCurso, cu.NomeVersao
      ORDER BY cu.NomeVersao ASC;`;
    //console.log(sqlQuery);
    execSQLQuery2(sqlQuery, res, req.originalUrl);
  });




  router.get("/getClass/:idclasses", verifyJWT, (req, res) => {
    const idclasses = parseInt(req.params.idclasses);
    execSQLQuery(
      `SELECT
      *
    FROM classes
    WHERE idclasses = ${idclasses};`,
      res,
      req.originalUrl
    );
  });
  /* router.get('/adjustXp', (req, res) => {
    execSQLQuery(`SELECT idusers, completedClass, idunits FROM myrobot.stepclass where completedClass = 1;`, res, req.originalUrl);
  }); */

  router.get("/getClassByAny/:field/:value", verifyJWT, (req, res) => {
    const field = req.params.field;
    const value = req.params.value;
    execSQLQuery(
      `SELECT
      *
    FROM classes
    WHERE ? = ?;`,
      res,
      req.originalUrl,
      [field, value]
    );
  });

  router.get("/getQtdeClass/:idusers/:idcourses/:db", verifyJWT, (req, res) => {
    const matricula = parseInt(req.params.idusers);
    const idcurso = req.params.idcourses;
    const db = req.params.db;
    let sqlQuery = `select count(*) as qtde_presencas
    from ${db}Chamadas c
    inner join ${db}TreinandosTurmas tt
    on tt.CodigoTurma = c.IDTurma
    and tt.IDCurso = c.IDCurso
    inner join ${db}ChamadasDetalhes cd
    on cd.IDChamadas = c.IDChamadas
    and cd.IDTreinando = tt.IDTreinando
    where c.Flex = 1
    and c.IDCurso = ${idcurso}
    and tt.IDMatricula = ${matricula}
    and cd.Presente = 1;`;
    execSQLQuery2(sqlQuery, res, req.originalUrl);
  });

  

  router.get("/getClassByCourse/:idcourses", verifyJWT, (req, res) => {
    const idcourses = parseInt(req.params.idcourses);
    execSQLQuery(
      `SELECT
      *
    FROM classes
    WHERE idcourses = ${idcourses} AND disabled = 0 ORDER BY ordem asc;`,
      res,
      req.originalUrl
    );
  });

  router.get("/getClassbyCourse/count/:idcourses", verifyJWT, (req, res) => {
    const idcourses = parseInt(req.params.idcourses);
    execSQLQuery(
      `SELECT count(*) as numeroDeAulas FROM classes WHERE idcourses = ${idcourses} AND disabled = 0 ORDER BY ordem asc;`,
      res,
      req.originalUrl
    );
  });

  router.get(
    "/getClassEnByCourse/:idcourses/:qtd/:homework",
    verifyJWT,
    (req, res) => {
      const idcourses = parseInt(req.params.idcourses);
      const qtd = parseInt(req.params.qtd);
      const homework = parseInt(req.params.homework);
      execSQLQuery(
        `SELECT
      idclasses,
      idcourses,
      name,
      case when disabled = 0 and ordem < ${qtd} and (viewed < 2 or ${qtd} != 100) then 0 else 1 end as disabled,
      ordem,
      description,
      viewed,
      aval,
      download,
      demo,
      homework,
      sprites
    FROM classes
    WHERE idcourses = ${idcourses} AND disabled = 0 and homework = ${homework}
    ORDER BY ordem asc;`,
        res,
        req.originalUrl
      );
    }
  );

  router.get(
    "/getClassEnByCourseAll/:idcourses/:qtd/:homework",
    verifyJWT,
    (req, res) => {
      const idcourses = parseInt(req.params.idcourses);
      const qtd = parseInt(req.params.qtd);
      const homework = parseInt(req.params.homework);
      execSQLQuery(
        `SELECT
      idclasses,
      idcourses,
      name,
      case when (disabled = 0 or disabled = 1) and (viewed < 2 or ${qtd} != 100) then 0 else 1 end as disabled,
      ordem,
      description,
      viewed,
      aval,
      download,
      demo,
      homework,
      sprites
    FROM classes
    WHERE idcourses = ${idcourses} AND homework = ${homework}
    ORDER BY ordem asc;`,
        res,
        req.originalUrl
      );
    }
  );

  router.post("/insertStepClass", verifyJWT, (req, res) => {
    const idusers = parseInt(req.body.idusers);
    const idclasses = parseInt(req.body.idclasses);
    const idunits = parseInt(req.body.idunits);
    mysqlConnection.query(
      "select * from stepclass order by idstepclass desc;",
      function (error, results, fields) {
        let IDStepClass = "";
        if (!results || results == "" || results.length == 0) IDStepClass = "1";
        else IDStepClass = parseInt(results[0]["idstepclass"]) + 1;
        let SQLInsert = `INSERT INTO stepclass(idstepclass, idusers, idclasses, actualClass, actualExercise, actualAnswer, actualItem, answersdb, continueClass, completedClass, idunits) VALUES
      (?, ?, ?, 0, 0, 0, 0, '', 0, 0, ?);`;
        execSQLQuery(
          SQLInsert,
          res,
          req.originalUrl,
          [IDStepClass, idusers, idclasses, idunits],
          function (Retorno) {
            if (Retorno.status == 500) {
              console.log(Retorno.error);
            }
          }
        );
      }
    );
  });

  router.get(
    "/lockClass/:idusers/:idunits/:idclasses",
    verifyJWT,
    (req, res) => {
      const idusers = parseInt(req.params.idusers);
      const idclasses = parseInt(req.params.idclasses);
      const idunits = parseInt(req.params.idunits);

      let sqlQuery = `UPDATE stepclass SET
      lockedClass = 1
    WHERE idunits = ${idunits} and idclasses = ${idclasses} and idusers = ${idusers};`;
      execSQLQuery(sqlQuery, res, req.originalUrl);
    }
  );

  router.patch("/updateStepClass", verifyJWT, (req, res) => {
    const idusers = parseInt(req.body.idusers);
    const idclasses = parseInt(req.body.idclasses);
    const idunits = parseInt(req.body.idunits);
    const actualClass = parseInt(req.body.actualClass);
    const actualExercise = parseInt(req.body.actualExercise);
    const actualAnswer = parseInt(req.body.actualAnswer);
    const actualItem = parseInt(req.body.actualItem);
    const answersdb = req.body.answersdb;
    const continueClass = req.body.continueClass;
    const completedClass = req.body.completedClass;
    const notegame = req.body.notegame;

    let sqlQuery = `UPDATE stepclass SET
      actualClass = ${actualClass},
      actualExercise = ${actualExercise},
      actualAnswer = ${actualAnswer},
      actualItem = ${actualItem},
      answersdb = '${answersdb}',
      continueClass = ${continueClass},
      completedClass = ${completedClass},
      notegame = ${notegame}
    WHERE idunits = ${idunits} and idclasses = ${idclasses} and idusers = ${idusers};`;
    execSQLQuery(sqlQuery, res, req.originalUrl);
  });

  router.patch("/addNote/:idunits", verifyJWT, (req, res) => {
    const idusers = parseInt(req.body.idusers);
    const idclasses = parseInt(req.body.idclasses);
    const note = req.body.note;
    const idunits = parseInt(req.params.idunits);
    execSQLQuery(
      `UPDATE stepclass SET
      note = ${note},
      notification = 1
    WHERE idusers = ${idusers} AND idclasses = ${idclasses} AND idunits = ${idunits};`,
      res,
      req.originalUrl
    );
  });

  router.patch("/setFinalDate", verifyJWT, (req, res) => {
    const datefinal = req.body.datefinal;
    const idusers = req.body.idusers;
    const idclasses = req.body.idclasses;
    const idunits = req.body.idunits;
    execSQLQuery(
      `UPDATE stepclass SET
      datefinal = (select date_format(date_add(now(), interval 6 day), '%d/%m/%Y'))
    WHERE idusers = ${idusers} AND idclasses = ${idclasses} AND idunits = ${idunits};`,
      res,
      req.originalUrl
    );
  });

  router.get("/PlusView/:idclasses", verifyJWT, (req, res) => {
    const idclasses = req.params.idclasses;
    execSQLQuery(
      `UPDATE classes SET
      viewed = viewed + 1
    WHERE idclasses = ${idclasses};`,
      res,
      req.originalUrl
    );
  });

  router.post("/addCourseNote", verifyJWT, (req, res) => {
    const idusers = parseInt(req.body.idusers);
    const idcourses = parseInt(req.body.idcourses);
    const note = req.body.note;
    const idunits = parseInt(req.body.idunits);
    execSQLQuery(
      `INSERT INTO coursenotes (idusers, idcourses, note, idunits)
    VALUES (${idusers}, ${idcourses}, ${note}, ${idunits});`,
      res,
      req.originalUrl
    );
  });

  router.get("/getActualConcept/:idclasses", verifyJWT, (req, res) => {
    const idclasses = parseInt(req.params.idclasses);
    execSQLQuery(
      `SELECT
      (ordem + 1) as concept
    FROM classes
    WHERE idclasses = ${idclasses};`,
      res,
      req.originalUrl
    );
  });

  router.patch("/removeNotification", verifyJWT, (req, res) => {
    const idusers = parseInt(req.body.idusers);
    const idunits = parseInt(req.body.idunits);
    execSQLQuery(
      `UPDATE stepclass SET
      notification = 0
    WHERE idusers = ${idusers} AND idunits = ${idunits}`,
      res,
      req.originalUrl
    );
  });

  router.get(
    "/getTreinandosTurmas/:matricula/:idcurso/:db",
    verifyJWT,
    (req, res) => {
      const matricula = parseInt(req.params.matricula);
      const idcurso = parseInt(req.params.idcurso);
      const db = req.params.db;
      execSQLQuery2(
        `SELECT
      *
    FROM ${db}TreinandosTurmas
    WHERE IDMatricula = ${matricula} AND IDCurso = ${idcurso};`,
        res,
        req.originalUrl
      );
    }
  );

  router.post("/insertNotasDetalhes/:banco", verifyJWT, (req, res) => {
    const db = req.params.banco;
    const IDTurma = parseInt(req.body.IDTurma);
    const IDCurso = parseInt(req.body.IDCurso);
    const IDMatricula = parseInt(req.body.IDMatricula);
    const IDTreinando = parseInt(req.body.IDTreinando);
    const IDConceito = parseInt(req.body.IDConceito);
    const NumeroProva = parseInt(req.body.NumeroProva);
    const Nota = req.body.Nota;
    execSQLQuery2(
      `INSERT INTO ${db}NotasDetalhes (IDTurma, IDCurso, IDMatricula, IDTreinando, IDConceito, Nota)
    VALUES (${IDTurma}, ${IDCurso}, ${IDMatricula}, ${IDTreinando}, ${IDConceito}, ${Nota});
    INSERT INTO ${db}NotasDetalhesSequencial (IDTurma, IDCurso, IDMatricula, IDTreinando, IDConceito)
    VALUES (${IDTurma}, ${IDCurso}, ${IDMatricula}, ${IDTreinando}, ${IDConceito});`,
      res,
      req.originalUrl
    );
  });

  router.get("/getAllStepClass/:englishbot", verifyJWT, (req, res) => {
    const englishbot = parseInt(req.params.englishbot);
    execSQLQuery(
      `select
      sc.*,
      sc.idusers as 'Matricula',
      un.unityname as 'Unidade',
      cl.name as 'Aula',
      co.name as 'Curso',
      sc.completedClass as 'Concluida',
      sc.actualExercise as 'Etapa'
    from stepclass sc
    inner join classes cl on cl.idclasses = sc.idclasses
    inner join courses co on co.idcourses = cl.idcourses
    inner join units un on un.idunits = sc.idunits
    where co.idtypescourse ${englishbot == 1 ? "=" : "!="} 5;`,
      res,
      req.originalUrl
    );
  });

  router.get(
    "/getStepClass/:idusers/:idclasses/:idunits",
    (req, res) => {
      const idusers = parseInt(req.params.idusers);
      const idclasses = parseInt(req.params.idclasses);
      const idunits = parseInt(req.params.idunits);
      const sql = `SELECT
        sc.*,
        (SELECT COUNT(*)
        FROM (
            SELECT
                c.idclassescontent,
                COALESCE(e.idexercises, m.idmedia, t.idtexts, s.idslides, c.montagem) AS content_id,
                COALESCE(e.description, m.description, t.description, s.title, 'Hora da montagem') AS description,
                COALESCE(e.enabled, 1, t.enabled, 1, 1) AS enabled,
                CASE
                    WHEN e.idexercises IS NOT NULL THEN 'Exercício'
                    WHEN m.idmedia IS NOT NULL THEN 'Mídia'
                    WHEN t.idtexts IS NOT NULL THEN CASE WHEN t.game = 0 THEN 'Texto' ELSE 'Jogo' END
                    WHEN s.idslides IS NOT NULL THEN 'Slide'
                    WHEN c.montagem = 1 THEN 'Montagem'
                END AS type,
                CASE
                    WHEN e.idexercises IS NOT NULL THEN 'Editar'
                    WHEN m.idmedia IS NOT NULL THEN 'Prever'
                    WHEN t.idtexts IS NOT NULL THEN 'Editar'
                    WHEN s.idslides IS NOT NULL THEN 'Prever'
                    WHEN c.montagem = 1 THEN '⠀⠀⠀⠀⠀'
                END AS acao
            FROM classescontent c
            LEFT JOIN exercises e ON c.idexercises = e.idexercises
            LEFT JOIN media m ON c.idmedia = m.idmedia
            LEFT JOIN texts t ON c.idtexts = t.idtexts
            LEFT JOIN slides s ON c.idslides = s.idslides
            WHERE c.idclasses = ${idclasses}
        ) AS content_table) AS qtdeItens
    FROM stepclass sc
    WHERE
        sc.idusers = ${idusers}
        AND sc.idclasses = ${idclasses}
        AND sc.idunits = ${idunits};`;
      mysqlConnection.query(sql, function (error, results, fields) {
        console.log(req.originalUrl + " success 200");
        res.send(
          JSON.stringify({
            response: results,
            error: error,
            status: results.length > 0 ? 200 : 500,
          })
        );
      });
    }
  );

  router.get("/getStepClassByUser/:idusers/:idunits", verifyJWT, (req, res) => {
    const idusers = parseInt(req.params.idusers);
    const idunits = parseInt(req.params.idunits);
    execSQLQuery(
      `SELECT
      *
    FROM stepclass
    WHERE idusers = ${idusers} AND idunits = ${idunits};`,
      res,
      req.originalUrl
    );
  });

  router.get("/list", verifyJWT, (req, res) => {
    execSQLQuery(
      `SELECT
      classes.idclasses,
      classes.name,
      classes.disabled,
      classes.ordem,
      courses.name as coursename,
      COUNT(classescontent.idclassescontent) as 'classContent'
    FROM classes
    JOIN courses ON classes.idcourses = courses.idcourses
    LEFT JOIN classescontent ON classes.idclasses = classescontent.idclasses
    GROUP BY classes.idclasses;`,
      res,
      req.originalUrl
    );
  });

  router.get("/listEn", verifyJWT, (req, res) => {
    execSQLQuery(
      `SELECT
      c.idclasses,
      c.name,
      c.disabled,
      c.ordem,
      c.download,
      co.name AS coursename,
      (select count(*) from courses co2 where co2.idcourses = c.idcourses and co2.idtypescourse = 5) as 'cursoingles',
      COUNT(cc.idclassescontent) AS 'classContent'
    FROM classes c
    JOIN courses co ON c.idcourses = co.idcourses
    LEFT JOIN classescontent cc ON c.idclasses = cc.idclasses
    GROUP BY c.idcourses, c.idclasses
    ORDER BY co.idcourses ASC, c.ordem ASC;`,
      res,
      req.originalUrl
    );
  });

  router.get("/disable/:idclasses", verifyJWT, (req, res) => {
    const idclasses = parseInt(req.params.idclasses);
    execSQLQuery(
      `UPDATE classes SET
      disabled = not disabled
    WHERE idclasses = ${idclasses};`,
      res,
      req.originalUrl
    );
  });

  router.post("/insert", verifyJWT, (req, res) => {
    const idcourses = parseInt(req.body.idcourses);
    const name = req.body.name;
    const disabled = req.body.disabled ? 1 : 0;
    const description = req.body.description;
    const aval = req.body.aval ? 1 : 0;
    const download = req.body.download ? 1 : 0;
    const demo = req.body.demo ? 1 : 0;
    const homework = req.body.homework ? 1 : 0;
    const sprites = parseInt(req.body.sprites);
    execSQLQuery(
      `INSERT INTO classes(idcourses, name, disabled, ordem, description, aval, download, viewed, demo, homework, sprites) SELECT
    ?, ?, ?, (select coalesce((select (ordem + 1) as ordem from classes where idcourses = ${idcourses} order by ordem desc limit 1), 0)), ?, ?, ?, 0, ?, ?, ?;`,
      res,
      req.originalUrl,
      [
        idcourses,
        name,
        disabled,
        description,
        aval,
        download,
        demo,
        homework,
        sprites,
      ]
    );
  });

  router.patch("/update", verifyJWT, (req, res) => {
    const idclasses = parseInt(req.body.idclasses);
    const idcourses = parseInt(req.body.idcourses);
    const name = req.body.name;
    const disabled = req.body.disabled ? 1 : 0;
    const description = req.body.description;
    const viewed = parseInt(req.body.viewed);
    const aval = req.body.aval ? 1 : 0;
    const download = req.body.download ? 1 : 0;
    const demo = req.body.demo ? 1 : 0;
    const homework = req.body.homework ? 1 : 0;
    const sprites = parseInt(req.body.sprites);
    execSQLQuery(
      `UPDATE classes SET
      idcourses = ?,
      name = ?,
      disabled = ?,
      description = ?,
      viewed = ?,
      aval = ?,
      download = ?,
      demo = ?,
      homework = ?,
      sprites = ?
    WHERE idclasses = ?;`,
      res,
      req.originalUrl,
      [
        idcourses,
        name,
        disabled,
        description,
        viewed,
        aval,
        download,
        demo,
        homework,
        sprites,
        idclasses,
      ]
    );
  });

  router.delete("/delete/:idclasses", verifyJWT, (req, res) => {
    const idclasses = parseInt(req.params.idclasses);
    execSQLQuery(
      `DELETE FROM classes
    WHERE idclasses = ${idclasses};`,
      res,
      req.originalUrl
    );
  });

  router.post("/copy", verifyJWT, (req, res) => {
    let form = new formidable.IncomingForm();
    form.maxFileSize = 1024 * 1024 * 1024;
    form.parse(req, function (err, fields, files) {
      const fs = require("fs");
      var idclasses = fields.idclasses;
      var GifFile = "";
      var BackFile = "";

      try {
        GifFile = files.gif.path;
      } catch (e) {
        console.log("Não copia Gif");
      }
      try {
        BackFile = files.back.path;
      } catch (e) {
        console.log("Não copia Back");
      }

      if (GifFile == "" && BackFile != "") {
        fs.rename(
          BackFile,
          Variables("src_media") + "images/back_" + idclasses + ".jpg",
          function (err) {
            console.log("/classes/copy success 200");
            res.send(true);
          }
        );
      } else if (GifFile != "" && BackFile == "") {
        fs.rename(
          GifFile,
          Variables("src_media") + "images/gif_" + idclasses + ".gif",
          function (err) {
            console.log("/classes/copy success 200");
            res.send(true);
          }
        );
      } else if (GifFile != "" && BackFile != "") {
        fs.rename(
          GifFile,
          Variables("src_media") + "images/gif_" + idclasses + ".gif",
          function (err) {
            fs.rename(
              BackFile,
              Variables("src_media") + "images/back_" + idclasses + ".jpg",
              function (err) {
                console.log("/classes/copy success 200");
                res.send(true);
              }
            );
          }
        );
      } else {
        res.send(true);
      }
    });
  });

  router.post("/insertUserStage", verifyJWT, (req, res) => {
    const iduserstages = req.body.iduserstages;
    const idclassescontent = parseInt(req.body.idclassescontent);
    const idusers = parseInt(req.body.idusers);
    const idunits = parseInt(req.body.idunits);
    const idclasses = parseInt(req.body.idclasses);
    const note = req.body.note;
    mysqlConnection.query(
      "select * from userstages order by iduserstages desc;",
      function (error, results, fields) {
        let IDUserStages = "";
        if (!results || results == "" || results.length == 0)
          IDUserStages = "1";
        else IDUserStages = parseInt(results[0]["iduserstages"]) + 1;
        const SQLInsert = `INSERT INTO userstages(iduserstages, idstages, idusers, idunits, idclasses, note)
        SELECT
        ${IDUserStages},
        (SELECT DISTINCT idstages FROM stages WHERE convert(description using utf8) LIKE convert(Stage.description using utf8)) AS idstages,
        ${idusers} AS idusers,
        ${idunits} AS idunits,
        ${idclasses} AS idclasses,
        ${note} AS note
      FROM (SELECT CASE
          WHEN idgames is not null THEN 'Jogo'
          WHEN idslides is not null THEN 'Montagem'
          ELSE 'Lição de Casa'
        END AS description
      FROM classescontent
      WHERE idclassescontent = ${idclassescontent}) Stage;`;
        //console.log(SQLInsert);
        execSQLQuery(SQLInsert, res, req.originalUrl);
      }
    );
  });

  router.post("/insertZoeUserGames", verifyJWT, (req, res) => {
    const idunits = parseInt(req.body.idunits);
    const idusers = parseInt(req.body.idusers);
    const idclasses = parseInt(req.body.idclasses);
    const idgames = parseInt(req.body.idgames);
    const note = req.body.note;
    execSQLQuery(
      `insert into zoeusergames (idunits, idusers, idgames, idclasses, note) values (${idunits}, ${idusers}, ${idgames}, ${idclasses}, ${note});`,
      res,
      req.originalUrl
    );
  });

  router.get("/CarregarImagem/:Nome", (req, res) => {
    let Protocolo = "http://";
    try {
      Protocolo = req.headers.referer.split("/")[0] + "//";
    } catch (e) {}
    const fs = require("fs");
    const Nome = req.params.Nome;
    const DirLocal = `${Variables("src_media")}images/${Nome}`;
    console.log(DirLocal);
    const DirWeb = `${Protocolo}${
      req.headers.host.split(":")[0]
    }/images/${Nome}`;
    console.log(DirWeb);
    if (DirLocal.includes("C:/")) {
      let diretoriodearquivolocal = DirWeb;
      diretoriodearquivolocal = diretoriodearquivolocal.replace(
        "localhost",
        "localhost:4200"
      );
      diretoriodearquivolocal = diretoriodearquivolocal.replace(
        "images",
        "assets/imagesAula"
      );
      res.redirect(diretoriodearquivolocal);
    } else {
      if (fs.existsSync(DirLocal)) {
        res.redirect(DirWeb);
      } else {
        const shell = require("shelljs");
        shell.exec(
          "cd " +
            Variables("src_media") +
            "images/;wget http://18.228.91.135/images/" +
            Nome,
          function (sucesso) {
            //console.log(sucesso);
            res.redirect(DirWeb);
          }
        );
      }
    }
  });

  router.patch("/AtualizarOrdem", verifyJWT, (req, res) => {
    const idclasses = parseInt(req.body.idclasses);
    const ordem = parseInt(req.body.ordem);
    execSQLQuery(
      `UPDATE classes SET
      ordem = ${ordem}
    WHERE idclasses = ${idclasses};`,
      res,
      req.originalUrl
    );
  });

  router.get("/getTeacherPasswords/:db", verifyJWT, (req, res) => {
    const db = req.params.db;
    execSQLQuery2(
      `SELECT
      Senha
    FROM ${db}Funcionarios
    where Desativado = 0;`,
      res,
      req.originalUrl
    );
  });

  router.get("/getFotoAluno/:idusers/:db", (req, res) => {
    const idusers = parseInt(req.params.idusers);
    const db = req.params.db;
    let sqlQuery = `select distinct
      tt.IDTreinando
    from ${db}TreinandosTurmas tt
    where tt.IDMatricula = ${idusers};`;
    if (Variables("local") == "Local") {
      console.log(req.originalUrl + " error 403");
      res.send(
        JSON.stringify({
          response: "",
          error: null,
          status: 403,
        })
      );
      return;
    }
    sqlServerConnection
      .request()
      .query(sqlQuery, function (error, results, fields) {
        if (!error && results["recordsets"][0].length != 0) {
          const fs = require("fs");

          /*         fs.readdir(Variables("src_fotos"), (err, files) => {
          let FotoId = "";
          files.forEach(Foto => {
            if (Foto.split('.')[0] == results['recordsets'][0][0]['IDTreinando']) {
              FotoId = Foto;
            }
          });
          if (FotoId != "") {
            fs.readFile(Variables("src_fotos") + FotoId, function(err, data) {
              if (err) throw err
              console.log(req.originalUrl + " success 200");
              res.send(JSON.stringify({
                "response": data,
                "error": null,
                "status": 200
              }));
            });
          }
          else {
            console.log(req.originalUrl + " error 500");
            res.send(JSON.stringify({
              "response": "",
              "error": null,
              "status": 500
            }));
          }
        }); */
        } else {
          console.log(req.originalUrl + " error 500");
          res.send(
            JSON.stringify({
              response: "",
              error: null,
              status: 500,
            })
          );
        }
      });
  });

  router.post("/AlteraNotaAula", verifyJWT, (req, res) => {
    const idclasses = parseInt(req.body.idclasses);
    const idunits = parseInt(req.body.idunits);
    const idusers = parseInt(req.body.idusers);
    const Nota = req.body.Nota;
    const SQLUpdate = `UPDATE stepclass SET note = ${Nota} WHERE idunits = ${idunits} AND idclasses = ${idclasses} AND idusers = ${idusers};`;
    mysqlConnection.query(SQLUpdate, function (error, results, fields) {
      const SQLDelete = `delete from coursenotes where idcourses = (select idcourses from classes where  idclasses = ${idclasses}) and idunits = ${idunits} and idusers = ${idusers};`;
      mysqlConnection.query(SQLDelete, function (error2, results2, fields2) {
        InsertCourseNotes();
        console.log(req.originalUrl + " success 200");
        res.send(
          JSON.stringify({
            response: results2,
            error: error2,
            status: 200,
          })
        );
      });
    });
  });

  router.get(
    "/AlteraStatusAulaAssistida/:idunits/:classes/:idusers/:status",
    verifyJWT,
    (req, res) => {
      const idunits = parseInt(req.params.idunits);
      const idclasses = parseInt(req.params.idclasses);
      const idusers = parseInt(req.params.idusers);
      const status = parseInt(req.params.status);
      execSQLQuery(
        `UPDATE stepclass SET
      liberado = ${status}
    WHERE idunits = ${idunits} and idclasses = ${idclasses} and idusers = ${idusers};`,
        res,
        req.originalUrl
      );
    }
  );

  router.get("/getTipoCursoPorAula/:idclasses", verifyJWT, (req, res) => {
    const idclasses = parseInt(req.params.idclasses);
    execSQLQuery(
      `SELECT
      tc.*
    FROM typescourse tc
    inner join courses co on co.idtypescourse = tc.idtypescourse
    inner join classes cl on cl.idcourses = co.idcourses
    where cl.idclasses = ${idclasses};`,
      res,
      req.originalUrl
    );
  });

  function InsertCourseNotes() {
    console.log(new Date().toUTCString() + " - /InsertCourseNotes");
    const SQLQry = `select
        Tab2.idcourses,
          Tab2.idusers,
        (sum(Tab2.note) / Tab2.AulasCurso) as 'note',
          Tab2.idunits
      from (
        select
          Tab1.*,
          sc.idstepclass,
          sc.note
        from (
          select
            sc.idusers,
            co.idcourses,
            sc.idunits,
            count(sc.idclasses) as 'Aulas',
            (select count(*) from classes cl1 where cl1.idcourses = co.idcourses and cl1.disabled = 0) as 'AulasCurso'
          from stepclass sc
          inner join classes cl on cl.idclasses = sc.idclasses
          inner join courses co on co.idcourses = cl.idcourses
          where sc.note is not null
          group by sc.idusers, co.idcourses, sc.idunits) Tab1
        inner join stepclass sc on sc.idunits = Tab1.idunits and sc.idusers = Tab1.idusers
        inner join classes cl on cl.idclasses = sc.idclasses and cl.idcourses = Tab1.idcourses
        where Tab1.Aulas = Tab1.AulasCurso
        order by idunits, idcourses, idusers) Tab2
      group by Tab2.idcourses, Tab2.idusers, Tab2.idunits;`;

    mysqlConnection.query(SQLQry, function (error, results, fields) {
      LoopCursoNotas(0, results, function (Sucesso) {
        console.log(new Date().toUTCString() + " - Notas de cursos lançadas");
        return Sucesso;
      });
    });
  }

  function LoopCursoNotas(Index, Registros, callback) {
    if (!Registros) {
      callback(false);
      return;
    }
    if (Index < Registros.length) {
      let SQLInsert = `INSERT INTO coursenotes (idcourses, idusers, note, idunits) VALUES (${Registros[Index]["idcourses"]}, ${Registros[Index]["idusers"]}, ${Registros[Index]["note"]}, ${Registros[Index]["idunits"]});`;
      //console.log(SQLInsert);
      mysqlConnection.query(SQLInsert, function (error, results, fields) {
        if (!error) {
          console.log(
            "Notas do curso lançadas para o aluno " +
              Registros[Index]["idusers"] +
              " da unidade " +
              Registros[Index]["idcourses"]
          );
        }
        Index++;
        LoopCursoNotas(Index, Registros, callback);
      });
    } else {
      callback(true);
    }
  }

  return router;
};

module.exports = routes;