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('/', verifyJWT, (req, res) => {
        execSQLQuery(`
            SELECT
                *
            FROM traineeprogramcourses`,
            res,
            req.originalUrl
        );
    })

    router.get('/getTraineeprogramcourses/:id', verifyJWT, (req, res) => {
        id = parseInt(req.params.id);
        execSQLQuery(`
            SELECT
                *
            FROM traineeprogramcourses
            WHERE idtraineeprogramcourses = ${id}`,
            res,
            req.originalUrl
        );
    })

    router.get('/getTraineeprogramByCourse/:idcourses', verifyJWT, (req, res) => {
        var idcourses = parseInt(req.params.idcourses);
        execSQLQuery(`
            SELECT
                *
            FROM 
                traineeprogramcourses
            WHERE 
                idcourses = ${idcourses}`,
            res,
            req.originalUrl
        );
    })

    router.delete('/deleteTraineeprogramcourses/:id', verifyJWT, (req, res) => {
        id = parseInt(req.params.id);
        execSQLQuery(`
            DELETE FROM traineeprogramcourses
            WHERE idtraineeprogramcourses = ${id}`,
            res,
            req.originalUrl
        );
    })

    router.get('/list/:id', verifyJWT, (req, res) => {
        id = parseInt(req.params.id);
        execSQLQuery(`SELECT
        c.idcourses,
        c.name,
        tpc.idtraineeprogramcourses,
        concat('R$ ',format(tpc.value,2,'de_DE')) as value,
        concat('R$ ',format(tpc.materialvalue,2,'de_DE')) as materialvalue,
        tpc.duration,
        tpc.ordem,
        tpc.opinionsearch,
        concat(tpc.materialpercentual,'%') as materialpercentual,
		case when tpc.opinionsearch = 0 then 'Não' else 'Sim' end as pesquisa
        FROM
            courses c
            inner join traineeprogramcourses tpc
            on tpc.idcourses = c.idcourses
        WHERE
            tpc.idtraineeprogram = ${id} order by ordem`,
            res,
            req.originalUrl
        );
    })

    router.post('/insert', verifyJWT, (req, res) => {
        const idcourse = req.body.idcourses;
        const value = parseFloat(req.body.value);
        const materialvalue = parseFloat(req.body.materialvalue);
        const duration = parseFloat(req.body.duration);
        const order = req.body.ordem;
        const opinionsearch = req.body.opinionsearch;
        const materialpercentual = parseFloat(req.body.materialpercentual);
        const idtraineeprogram = req.body.idtraineeprogram;
        execSQLQuery(`INSERT INTO
            traineeprogramcourses(
              idcourses, value, materialvalue,
              duration, ordem, opinionsearch,
              materialpercentual, idtraineeprogram
            ) VALUES (
                '${idcourse}','${value}','${materialvalue}',
                '${duration}','${order}','${opinionsearch}',
                '${materialpercentual}','${idtraineeprogram}'
            )`,
            res,
            req.originalUrl
        );
    })

    router.patch('/update/:id', verifyJWT, (req, res) => {
        id = parseInt(req.params.id);
        const idtraineeprogramcourses = req.body.idtraineeprogramcourses;
        const idcourse = req.body.idcourses;
        const value = parseFloat(req.body.value);
        const materialvalue = parseFloat(req.body.materialvalue);
        const duration = parseFloat(req.body.duration);
        const order = req.body.ordem;
        const opinionsearch = req.body.opinionsearch;
        const materialpercentual = parseFloat(req.body.materialpercentual);
        const idtraineeprogram = req.body.idtraineeprogram;
        execSQLQuery(`
            UPDATE traineeprogramcourses SET
            idcourses = '${idcourse}',
            value = '${value}',
            materialvalue = '${materialvalue}',
            duration = '${duration}',
            ordem = ${order},
            opinionsearch = ${opinionsearch},
            materialpercentual = '${materialpercentual}',
            idtraineeprogram = '${idtraineeprogram}'
            WHERE idtraineeprogramcourses = ${id}`,
            res,
            req.originalUrl
        );
    })

    router.patch('/attOrdem0', verifyJWT, (req, res) => {
        const idtraineeprogramcourses = req.body.idtraineeprogramcourses;
        const ordemagora = req.body.ordemagora;
        execSQLQuery(`
          UPDATE
              traineeprogramcourses
          SET
            ordem = ${ordemagora}
          WHERE
            idtraineeprogramcourses = ${idtraineeprogramcourses}`,
          res,
          req.originalUrl
        );
      })

    return router;
};

module.exports = routes;