require("../connections/mysql-server");
require("../connections/sql-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("/getRobotCourses", verifyJWT, (req, res) => {
    execSQLQuery(`
      SELECT
        0 as 'IDMatricula',
        idcourses as 'IDCurso',
        name as 'NomeVersao',
        aditional as 'Adicional',
        extracourse as 'CursoExtra'
      FROM courses
      WHERE idtypescourse = 5 and disabled = 0;`,
      res,
      req.originalUrl
    );
  });

  router.get("/getRobotCourseByStudent/:idusers/:banco", verifyJWT, (req, res) => {
    const idusers = parseInt(req.params.idusers);
    const db = req.params.banco;
    execSQLQuery(`
      SELECT distinct
        zus.user AS IDMatricula,
        zuc.idcourses AS IDCurso,
        co.name AS NomeVersao
      FROM zoeusercourses zuc
      INNER JOIN courses co ON co.idcourses = zuc.idcourses
      INNER JOIN zoeusers zus ON zus.id = zuc.idzoeusers
      INNER JOIN zoeunits zun ON zun.id = zuc.idzoeunits 
      WHERE zuc.enabled = 1 AND (zuc.expiresAt IS NULL OR CURRENT_TIMESTAMP < zuc.expiresAt) AND zus.user = '${idusers}' and CONCAT(zun.id, '.dbo.') = '${db}';`,
      res,
      req.originalUrl
    );
  });

  router.get("/getRobotCourseByStudentOld/:idusers/:banco", verifyJWT, (req, res) => {
    const idusers = parseInt(req.params.idusers);
    const db = req.params.banco;
    execSQLQuery2(`
      SELECT distinct
        mt.IDMatricula,
        tt.IDCurso,
        cu.NomeVersao
      FROM ${db}MatriculasTreinandos mt
      INNER JOIN ${db}TreinandosTurmas tt ON mt.IDMatricula = tt.IDMatricula
      INNER JOIN ${db}Cursos cu ON cu.Codigo = tt.IDCurso
      WHERE mt.IDMatricula = ${idusers} AND cu.Desativado = 0 AND tt.Desistente = 0 AND cu.IDTipoCurso = 5;`,
      res,
      req.originalUrl
    );
  });




  router.get("/getAll", verifyJWT, (req, res) => {
    const domain = req.hostname.split(".").pop();

    let language;

    switch (domain) {
      case "br":
        language = 1;
        break;
      case "py":
        language = 2;
        break;
      case "us":
        language = 3;
        break;
      default:
        language = 1;
        break;
    }

    execSQLQuery(
      `SELECT 
          * 
      FROM myrobot.courses where idlanguage = ${language} order by name asc`,
      res,
      req.originalUrl
    );
  });

  router.get("/getAllCourseOn", verifyJWT, (req, res) => {
    const domain = req.hostname.split(".").pop();

    let language;

    switch (domain) {
      case "br":
        language = 1;
        break;
      case "py":
        language = 2;
        break;
      case "us":
        language = 3;
        break;
      default:
        language = 1;
        break;
    }

    execSQLQuery(
      `SELECT 
          * 
      FROM myrobot.courses where idlanguage = ${language} and disabled = 0 order by name asc`,
      res,
      req.originalUrl
    );
  });

  router.get("/getAllCourses", verifyJWT, (req, res) => {
    execSQLQuery(
      `SELECT
            *
        FROM courses
        WHERE idtypescourse != 5;`,
      res,
      req.originalUrl
    );
  });

  router.get("/getSQLServerCourses/:db", verifyJWT, (req, res) => {
    const db = req.params.db;
    execSQLQuery2(
      `SELECT
            *
        FROM ${db}Cursos;`,
      res,
      req.originalUrl
    );
  });

  router.get("/getMySQLinSQLServerCourses", verifyJWT, (req, res) => {
    execSQLQuery(
      `SELECT
            idcourses AS 'Codigo',
            coursecode AS 'CodigoCurso',
            idtypescourse AS 'IDTipoCurso',
            name AS 'NomeVersao',
            description AS 'DescricaoContrato',
            durationtraineeprogram AS 'CH_ProgTrein',
            durationtraineevip AS 'CH_TurmaVIP',
            durationtraineeindividual AS 'CH_Individual',
            valueprogram AS 'VR_ProgTrein',
            valuevip AS 'VR_TurmaVIP',
            valueindividual AS 'VR_Individual',
            materialvalue AS 'ValorMaterial',
            materialcode AS 'CodigoMaterial',
            CASE WHEN extracourse = 1 THEN 'true' else 'false' END AS 'CursoExtra',
            CASE WHEN aditional = 1 THEN 'true' else 'false' END AS 'Adicional',
            CASE WHEN disabled = 1 THEN 'true' else 'false' END AS 'Desativado',
            CASE WHEN autospeak = 1 THEN 'true' else 'false' END AS 'FalaAutomatica'
        FROM courses;`,
      res,
      req.originalUrl
    );
  });

  

  

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

  router.get("/getMySQLinSQLServerTypescourse", verifyJWT, (req, res) => {
    execSQLQuery(
      `SELECT
            idtypescourse AS 'IDTipoCurso',
            description AS 'Descricao',
            case when disabled = 1 then true else false end as 'Desativado',
            gradecalc AS 'CalculosNotas'
        FROM typescourse;`,
      res,
      req.originalUrl
    );
  });

  router.get("/SQLServertypescourse/:db", verifyJWT, (req, res) => {
    const db = req.params.db;
    execSQLQuery2(
      `SELECT
            *
        FROM ${db}TiposCurso;`,
      res,
      req.originalUrl
    );
  });

  router.get("/getExtraCourses", verifyJWT, (req, res) => {
    execSQLQuery(
      `SELECT
            0 as 'IDMatricula',
            idcourses as 'IDCurso',
            name as 'NomeVersao'
        FROM courses
        WHERE aditional = 1 and disabled = 0;`,
      res,
      req.originalUrl
    );
  });

  router.get("/getCourse/:idcourses", verifyJWT, (req, res) => {
    const idcourses = parseInt(req.params.idcourses);
    execSQLQuery(
      `SELECT
            *
        FROM courses
        WHERE idcourses = ${idcourses};`,
      res,
      req.originalUrl
    );
  });

  router.get("/getCourseNote/:idcourses", verifyJWT, (req, res) => {
    const idcourses = parseInt(req.params.idcourses);
    execSQLQuery(
      `SELECT
            *
        FROM coursenotes
        WHERE idcourses = ${idcourses};`,
      res,
      req.originalUrl
    );
  });

  router.get("/getAllSQLServerCourses/:idusers", verifyJWT, (req, res) => {
    const idusers = parseInt(req.params.idusers);
    let sqlQuery = `SELECT idcourses AS 'IDCurso', ${idusers} AS 'IDMatricula', name AS 'NomeVersao' FROM courses where idtypescourse != 5 and disabled = 0 order by name asc;`;
    execSQLQuery(sqlQuery, res, req.originalUrl);
  });

  router.get("/getCourseByStudent/:idusers/:banco", verifyJWT, (req, res) => {
    const idusers = parseInt(req.params.idusers);
    const db = req.params.banco;
    const EAD = req.headers.host && req.headers.host.includes("myrobothome");
    mysqlConnection.query(
      "select cursoferias from config;",
      function (error, results, fields) {
        let sqlQuery = `SELECT distinct
                mt.IDMatricula,
                cu.Codigo as 'IDCurso',
                cu.NomeVersao,
				cu.IDTipoCurso
            FROM ${db}MatriculasTreinandos mt
            INNER JOIN ${db}TreinandosTurmas tt ON mt.IDMatricula = tt.IDMatricula
            INNER JOIN ${db}Cursos cu ON cu.Codigo = tt.IDCurso
            WHERE mt.IDMatricula = ${idusers} AND cu.Desativado = 0 AND tt.Desistente = 0 AND cu.IDTipoCurso ${
          EAD ? "= 7" : "!= 5"
        }
            union all select
            	0 as IDMatricula,
                cu2.Codigo as 'IDCurso',
                cu2.NomeVersao,
            	cu2.IDTipoCurso
            from ${db}Cursos cu2
            where 1=0${
              results[0]["cursoferias"] == 1 ? " or cu2.CursoExtra = 1" : ""
            }${EAD ? " or cu2.Adicional = 1" : ""};`;
        //console.log(sqlQuery);
        execSQLQuery2(sqlQuery, res, req.originalUrl);
      }
    );
  });

  router.get(
    "/getHomeCourseByStudent/:idusers/:banco",
    verifyJWT,
    (req, res) => {
      const idusers = parseInt(req.params.idusers);
      const db = req.params.banco;
      const EAD = req.headers.host && req.headers.host.includes("myrobothome");
      mysqlConnection.query(
        "select cursoferias from config;",
        function (error, results, fields) {
          let sqlQuery = `SELECT distinct
                mt.IDMatricula,
                cu.Codigo as 'IDCurso',
                cu.NomeVersao,
				cu.IDTipoCurso
            FROM ${db}MatriculasTreinandos mt
            INNER JOIN ${db}TreinandosTurmas tt ON mt.IDMatricula = tt.IDMatricula
            INNER JOIN ${db}Cursos cu ON cu.Codigo = tt.IDCurso
            WHERE mt.IDMatricula = ${idusers} AND cu.Desativado = 0 AND tt.Desistente = 0 AND 
            (cu.IDTipoCurso = 4 or cu.IDTipoCurso = 6 or cu.IDTipoCurso = 7) 
            union all select
            	0 as IDMatricula,
                cu2.Codigo as 'IDCurso',
                cu2.NomeVersao,
            	cu2.IDTipoCurso
            from ${db}Cursos cu2
            where (cu2.IDTipoCurso = 4 or cu2.IDTipoCurso = 6 or cu2.IDTipoCurso = 7) and 
            (1=0${
              results[0]["cursoferias"] == 1 ? " or cu2.CursoExtra = 1" : ""
            }${EAD ? " or cu2.Adicional = 1" : ""});`;
          console.log(sqlQuery);
          execSQLQuery2(sqlQuery, res, req.originalUrl);
        }
      );
    }
  );

  router.get("/list", verifyJWT, (req, res) => {
    execSQLQuery(
      `SELECT
            idcourses,
            name,
            description,
            concat('R$ ',format(materialvalue,2,'de_DE')) as materialvalue,
            disabled,
            idtypescourse
        FROM courses
        WHERE disabled = 0
        order by name asc;`,
      res,
      req.originalUrl
    );
  });

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

  router.post("/insertSQL", verifyJWT, (req, res) => {
    const coursecode = req.body.coursecode
    const idtypescourse = parseInt(req.body.idtypescourse)
    const name = req.body.name
    const description = req.body.description
    const durationtraineeprogram = req.body.durationtraineeprogram
    const durationtraineevip = req.body.durationtraineevip
    const durationtraineeindividual = req.body.durationtraineeindividual
    const valueprogram =  parseFloat(req.body.valueprogram)
    const valuevip =  parseFloat(req.body.valuevip)
    const valueindividual =  parseFloat(req.body.valueindividual)
    const disabled = req.body.disabled
    const materialvalue =  parseFloat(req.body.materialvalue)
    const materialcode = req.body.materialcode
    const aditional = req.body.aditional
    const extracourse = req.body.aditional
    const autospeak = req.body.autospeak
    console.log(req.body)
  })

  router.post("/insert", verifyJWT, (req, res) => {
    const coursecode = req.body.coursecode;
    const idtypescourse = parseInt(req.body.idtypescourse);
    const name = req.body.name;
    const description = req.body.description;
    const durationtraineeprogram = req.body.durationtraineeprogram;
    const durationtraineevip = req.body.durationtraineevip;
    const durationtraineeindividual = req.body.durationtraineeindividual;
    const valueprogram = parseFloat(req.body.valueprogram);
    const valuevip = parseFloat(req.body.valuevip);
    const valueindividual = parseFloat(req.body.valueindividual);
    const materialvalue = parseFloat(req.body.materialvalue);
    const materialcode = req.body.materialcode;
    const extracourse = req.body.extracourse ? 1 : 0;
    const aditional = req.body.aditional ? 1 : 0;
    const disabled = req.body.disabled ? 1 : 0;
    const autospeak = req.body.autospeak ? 1 : 0;
    const idlanguage = req.body.idlanguage;
    try {
      execSQLQuery(
        `INSERT INTO courses (coursecode, idtypescourse, name, description, durationtraineeprogram, durationtraineevip, durationtraineeindividual, valueprogram, valuevip, valueindividual, materialvalue, materialcode, extracourse, aditional, disabled, autospeak, idlanguage)
              VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? , ?, ?, ?, ?);`,
        res,
        req.originalUrl,
        [
          coursecode,
          idtypescourse,
          name,
          description,
          durationtraineeprogram,
          durationtraineevip,
          durationtraineeindividual,
          valueprogram,
          valuevip,
          valueindividual,
          materialvalue,
          materialcode,
          extracourse,
          aditional,
          disabled,
          autospeak,
          idlanguage,
        ]
      );
    } catch (error) {
      console.log("oi");
      console.log(error);
    }
  });

  router.patch("/update", verifyJWT, (req, res) => {
    const idcourses = parseInt(req.body.idcourses);
    const coursecode = req.body.coursecode;
    const idtypescourse = parseInt(req.body.idtypescourse);
    const name = req.body.name;
    const description = req.body.description;
    const durationtraineeprogram = req.body.durationtraineeprogram;
    const durationtraineevip = req.body.durationtraineevip;
    const durationtraineeindividual = req.body.durationtraineeindividual;
    const valueprogram = parseFloat(req.body.valueprogram);
    const valuevip = parseFloat(req.body.valuevip);
    const valueindividual = parseFloat(req.body.valueindividual);
    const materialvalue = parseFloat(req.body.materialvalue);
    const materialcode = req.body.materialcode;
    const extracourse = req.body.extracourse ? 1 : 0;
    const aditional = req.body.aditional ? 1 : 0;
    const disabled = req.body.disabled ? 1 : 0;
    const autospeak = req.body.autospeak ? 1 : 0;
    execSQLQuery(
      `UPDATE courses SET
            coursecode = ?,
            idtypescourse = ?,
            name = ?,
            description = ?,
            durationtraineeprogram = ?,
            durationtraineevip = ?,
            durationtraineeindividual = ?,
            valueprogram = ?,
            valuevip = ?,
            valueindividual = ?,
            materialvalue = ?,
            materialcode = ?,
            extracourse = ?,
            aditional = ?,
            disabled = ?,
            autospeak = ?
        WHERE idcourses = ?;`,
      res,
      req.originalUrl,
      [
        coursecode,
        idtypescourse,
        name,
        description,
        durationtraineeprogram,
        durationtraineevip,
        durationtraineeindividual,
        valueprogram,
        valuevip,
        valueindividual,
        materialvalue,
        materialcode,
        extracourse,
        aditional,
        disabled,
        autospeak,
        idcourses,
      ]
    );
  });

  router.get(
    "/getCourseAndClass/:idusers/:idCursos/:idunits",
    verifyJWT,
    (req, res) => {
      const idusers = parseInt(req.params.idusers);
      const idunits = parseInt(req.params.idunits);
      const WhereClause = req.params.idCursos;
      const domain = req.hostname.split(".").pop();

      let language;

      switch (domain) {
        case "br":
          language = 1;
          break;
        case "py":
          language = 2;
          break;
        case "us":
          language = 3;
          break;
        default:
          language = 1;
          break;
      }

      let sqlQuery = `select distinct
            *,
            COALESCE(((R.actualItem / R.itensInClass) * 100) , 0) AS 'progressB'
        FROM (
        select
            co.*,
            cl.idclasses,
            cl.name AS 'nameC',
            cl.description AS 'descriptionC',
            cl.aval,
            cl.demo,
            COALESCE(sc.actualItem, 0) AS 'actualItem',
            sc.continueClass,
            sc.completedClass,
            sc.lockedClass,
            sc.liberado,
            ROUND(sc.note, 2) AS 'note',
            sc.notification,
            sc.homework,
            (select count(*) from userstages us where us.idunits = sc.idunits and us.idusers = sc.idusers and us.idclasses = sc.idclasses) as 'licao',
            0 AS 'showdet',
            0 AS 'btnVis',
            COALESCE(sc.actualItem, 0) AS 'actualItenInClass',
            (SELECT COUNT(*) AS 'qtdeItens'
                from classescontent cc
                LEFT join exercisequestions eq ON eq.idexercises = cc.idexercises
                LEFT join media m ON m.idmedia = cc.idmedia
                LEFT join texts t ON t.idtexts = cc.idtexts
                LEFT join slides s ON s.idslides = cc.idslides
                LEFT join texts g ON g.idtexts = cc.idgames AND t.game = 1
                where cc.idclasses = cl.idclasses)
            AS 'itensInClass',
            cl.download AS 'downloads',
            cl.ordem,
            (select count(*) from barcodes bc where bc.idunits = ${idunits} and bc.idusers = ${idusers} and bc.idcourses = co.idcourses) as 'Apostila'
        from courses co
        inner join classes cl on cl.idcourses = co.idcourses and cl.disabled = 0
        left join stepclass sc on sc.idclasses = cl.idclasses and sc.idusers = ${idusers} and sc.idunits = ${idunits}
        WHERE ${
          idusers == 190001 ? "cl.demo = 1" : "co.idcourses in " + WhereClause
        } AND co.disabled = 0 AND cl.homework = 0 and co.idtypescourse != 5 
        AND co.idlanguage = ${language} OR co.idlanguage = 0 
        ORDER BY co.name, cl.ordem ASC) R
        ORDER BY R.name ASC, ordem ASC;`;
      execSQLQuery(sqlQuery, res, req.originalUrl);
    }
  );

  router.get(
    "/getHomeCourseAndClass/:idusers/:idCursos/:idunits",
    verifyJWT,
    (req, res) => {
      const idusers = parseInt(req.params.idusers);
      const idunits = parseInt(req.params.idunits);
      const WhereClause = req.params.idCursos;
      let sqlQuery = `select distinct
            *,
            COALESCE(((R.actualItem / R.itensInClass) * 100) , 0) AS 'progressB'
        FROM (
        select
            co.*,
            cl.idclasses,
            cl.name AS 'nameC',
            cl.description AS 'descriptionC',
            cl.aval,
            cl.demo,
            COALESCE(sc.actualItem, 0) AS 'actualItem',
            sc.continueClass,
            sc.completedClass,
            sc.lockedClass,
            sc.liberado,
            ROUND(sc.note, 2) AS 'note',
            sc.notification,
            sc.homework,
            (select count(*) from userstages us where us.idunits = sc.idunits and us.idusers = sc.idusers and us.idclasses = sc.idclasses) as 'licao',
            0 AS 'showdet',
            0 AS 'btnVis',
            COALESCE(sc.actualItem, 0) AS 'actualItenInClass',
            (SELECT COUNT(*) AS 'qtdeItens'
                from classescontent cc
                LEFT join exercisequestions eq ON eq.idexercises = cc.idexercises
                LEFT join media m ON m.idmedia = cc.idmedia
                LEFT join texts t ON t.idtexts = cc.idtexts
                LEFT join slides s ON s.idslides = cc.idslides
                LEFT join texts g ON g.idtexts = cc.idgames AND t.game = 1
                where cc.idclasses = cl.idclasses)
            AS 'itensInClass',
            cl.download AS 'downloads',
            cl.ordem,
            (select count(*) from barcodes bc where bc.idunits = ${idunits} and bc.idusers = ${idusers} and bc.idcourses = co.idcourses) as 'Apostila'
        from courses co
        inner join classes cl on cl.idcourses = co.idcourses and cl.disabled = 0
        left join stepclass sc on sc.idclasses = cl.idclasses and sc.idusers = ${idusers} and sc.idunits = ${idunits}
        WHERE ${
          idusers == 190001 ? "cl.demo = 1" : "co.idcourses in " + WhereClause
        } AND co.disabled = 0 AND cl.homework = 0 and co.idtypescourse != 5 
        and (co.idtypescourse = 4 or co.idtypescourse = 6 or co.idtypescourse = 7)
        ORDER BY co.name, cl.ordem ASC) R
        ORDER BY R.name ASC, ordem ASC;`;
      execSQLQuery(sqlQuery, res, req.originalUrl);
      console.log(sqlQuery);
    }
  );

  return router;
};

module.exports = routes;