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 traineeprogram`,
            res,
            req.originalUrl
        );
    })

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

    router.get('/getTraineeprogramCourses/:id', verifyJWT, (req, res) => {
        id = parseInt(req.params.id);
        execSQLQuery(`
    select
        c.name,
        tc.ordem,
        tc.idtraineeprogramcourses
    from
        traineeprogramcourses tc
    inner join
        courses c
    on
        tc.idcourses = c.idcourses
    where
        tc.idtraineeprogram = ${id}
    order by ordem;`,
            res,
            req.originalUrl
        );
    })

    router.get('/list', verifyJWT, (req, res) => {
        execSQLQuery(`
            SELECT
                idtraineeprogram,
                name,
                description,
                concat('R$ ',format(totalvalue,2,'de_DE')) as totalvalue,
                disabled
            FROM traineeprogram`,
            res,
            req.originalUrl
        );
    })

    router.patch('/disable', verifyJWT, (req, res) => {
        const idtraineeprogram = parseInt(req.body.idtraineeprogram);
        execSQLQuery(`
            UPDATE traineeprogram
            SET disabled = not disabled
            WHERE idtraineeprogram = ${idtraineeprogram}`,
            res,
            req.originalUrl
        );
    })

    router.post('/insert', verifyJWT, (req, res) => {
        const idaccountplan = req.body.idaccountplan ? 1 : 0;
        const name = req.body.name;
        const description = req.body.description;
        const totalvalue = parseFloat(req.body.totalvalue);
        const totalmaterialvalue = parseFloat(req.body.totalmaterialvalue);
        const durationtotal = parseFloat(req.body.durationtotal);
        const disabled = req.body.disabled ? 1 : 0;
        const codetraineeprog = req.body.codetraineeprog ? 1 : 0;
        const peopletec = req.body.peopletec ? 1 : 0;
        const matvaluefix = req.body.matvaluefix ? 1 : 0;
        execSQLQuery(`INSERT INTO
            traineeprogram(
              idaccountplan, name, description,
              totalvalue, totalmaterialvalue, durationtotal,
              disabled, codetraineeprog, peopletec, matvaluefix
            ) VALUES (
                '${idaccountplan}',"${name}","${description}",
                '${totalvalue}',${totalmaterialvalue},${durationtotal},
                '${disabled}','${codetraineeprog}','${peopletec}','${matvaluefix}'
            )`,
            res,
            req.originalUrl
        );
    })

    router.patch('/update', verifyJWT, (req, res) => {
      const idtraineeprogram = req.body.idtraineeprogram;
      const idaccountplan = req.body.idaccountplan ? 1 : 0;
        const name = req.body.name;
        const description = req.body.description;
        const totalvalue = parseFloat(req.body.totalvalue);
        const totalmaterialvalue = parseFloat(req.body.totalmaterialvalue);
        const durationtotal = parseFloat(req.body.durationtotal);
        const disabled = req.body.disabled ? 1 : 0;
        const codetraineeprog = req.body.codetraineeprog ? 1 : 0;
        const peopletec = req.body.peopletec ? 1 : 0;
        const matvaluefix = req.body.matvaluefix ? 1 : 0;
        execSQLQuery(`
            UPDATE traineeprogram SET
            idaccountplan = '${idaccountplan}',
                name = "${name}",
                description = "${description}",
                totalvalue = ${totalvalue},
                totalmaterialvalue = ${totalmaterialvalue},
                durationtotal = '${durationtotal}',
                disabled = '${disabled}',
                codetraineeprog = '${codetraineeprog}',
                peopletec = '${peopletec}',
                matvaluefix = '${matvaluefix}'
            WHERE idtraineeprogram = ${idtraineeprogram}`,
            res,
            req.originalUrl
        );
    })

    router.post('/addTraineeNote', verifyJWT, (req, res) => {
        const idtraineeprogram = req.body.idtraineeprogram;
        const idusers = req.body.idusers;
        const idunits = req.body.idunits;
        const note = parseFloat(req.body.note);
        execSQLQuery(`
        INSERT INTO
            traineeprogramnotes(idtraineeprogram, idusers, note, idunits)
        VALUES
            (${idtraineeprogram}, ${idusers}, ${idunits}, ${note});`,
            res,
            req.originalUrl
        );
    })

    router.get('/getMySQLinSQLServerTraineeprogram', verifyJWT, (req, res) => {
        execSQLQuery(`SELECT
            idtraineeprogram AS 'Codigo',
            idaccountplan AS 'IDPlanoConta',
            name AS 'Nome',
            description AS 'DescricaoContrato',
            totalvalue AS 'ValorTotal',
            totalmaterialvalue AS 'ValorMaterialTotal',
            durationtotal AS 'CargaHorariaTotal',
            disabled AS 'Desativado',
            codetraineeprog AS 'CodigoProgTreinamento',
            peopletec AS 'PeopleTec'
        FROM traineeprogram;`, res, req.originalUrl);
    });

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

    router.get('/getMySQLinSQLServerTraineeprogramcourses', verifyJWT, (req, res) => {
        execSQLQuery(`SELECT
            idtraineeprogram AS 'Codigo',
            idcourses AS 'CodigoCurso',
            value AS 'Valor',
            materialvalue AS 'ValorMaterial',
            duration AS 'CargaHoraria',
            ordem AS 'Ordem',
            opinionsearch AS 'PesquisaOpiniao',
            materialpercentual AS 'PercentualMaterial'
        FROM traineeprogramcourses;`, res, req.originalUrl);
    });

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

    return router;
};

module.exports = routes;