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('/getChamadasProfessor/:IDFuncionario/:db', verifyJWT, (req, res) => {
        const IDFuncionario = parseInt(req.params.IDFuncionario);
        const db = req.params.db;
        execSQLQuery2(`SELECT
            ch.IDChamadas,
            ch.IDTurma,
            ch.IDCurso,
            ch.Data,
            tt.IDMatricula,
            co.NomeVersao
        FROM ${db}Chamadas ch
        INNER JOIN ${db}Cursos co ON co.Codigo = ch.IDCurso
        INNER JOIN ${db}TreinandosTurmas tt ON tt.IDCurso = ch.IDCurso AND tt.CodigoTurma = ch.IDTurma
        WHERE ch.IDInstrutor = ${IDFuncionario};`, res, req.originalUrl);
    });

    router.get('/getQRCodeAluno/:_key', verifyJWT, (req, res) => {
        const _key = req.params._key;
        execSQLQuery(`SELECT
            qr.idusers,
            qr.idunits
        FROM qrcodes qr
        where qr._key = ?;`, res, req.originalUrl, [_key]);
    });

    router.get('/getInfoAluno/:IDMatricula/:db', verifyJWT, (req, res) => {
        const IDMatricula = parseInt(req.params.IDMatricula);
        const db = req.params.db;
        execSQLQuery2(`SELECT DISTINCT
            cl.Nome,
            cu.Codigo AS 'IDCurso',
            cu.NomeVersao,
            tt.IDTreinando,
            tt.CodigoTurma,
            (select count(*) from ${db}Chamadas ch2 where ch2.IDTurma = tt.CodigoTurma and ch2.IDCurso = tt.IDCurso) AS qtde_presencas
        FROM ${db}TreinandosTurmas tt
        INNER JOIN ${db}Clientes cl ON cl.IDCliente = tt.IDTreinando
        INNER JOIN ${db}Cursos cu ON cu.Codigo = tt.IDCurso
        LEFT JOIN ${db}Chamadas ch ON tt.CodigoTurma = ch.IDTurma AND tt.IDCurso = ch.IDCurso
		LEFT JOIN ${db}ChamadasDetalhes cd ON cd.IDChamadas = ch.IDChamadas AND cd.IDTreinando = tt.IDTreinando
        WHERE tt.IDMatricula = ${IDMatricula} and tt.CodigoTurma > 0
        GROUP BY cl.Nome, cu.Codigo, cu.NomeVersao, tt.IDTreinando, tt.CodigoTurma, tt.IDCurso
        ORDER BY cl.Nome ASC;`, res, req.originalUrl);
    });

    router.get('/checarCursoParaChamada/:IDTurma/:idCourse/:db', verifyJWT, (req, res) => {
        const IDTurma = parseInt(req.params.IDTurma);
        const idCourse = parseInt(req.params.idCourse);
        const db = req.params.db;
        execSQLQuery2(`SELECT
            tc.IdTurmasCalendario,
            tc.Data,
            tc.HoraInicial,
            tc.HoraFinal,
            tc.CodigoInstrutor
        FROM ${db}TurmasCalendario tc
        WHERE tc.IdTurma = ${IDTurma} AND CodigoCurso = ${idCourse} AND tc.Ministrado = 0
        ORDER BY tc.Data ASC;`, res, req.originalUrl);
    });

    router.post('/EnviarChamada', verifyJWT, (req, res) => {
        const db = req.body.db;
        const Alunos = JSON.parse(req.body.Alunos);
        console.log(Alunos);
        RealizaChamada(0, db, Alunos, function(Sucesso) {
            res.send(Sucesso);
        });
    });


    router.post('/insertNewToken', verifyJWT, (req, res) => {
        const db = req.body.db;
        const description = req.body.description;
        const idunits = req.body.idunits;
        const token = req.body.token;
        execSQLQuery(`insert into appnotifications (description, idunits, token) values (?, ?, ?);`, res, req.originalUrl, [description, idunits, token]);
    });

    router.post('/changeReceive', verifyJWT, (req, res) => {
        const token = req.body.token;
        execSQLQuery(`update appnotifications set receive = !receive where token = ?;`, res, req.originalUrl, [token]);
    });

    router.get('/getAllUnitTokens/:idunits', verifyJWT, (req, res) => {
        const idunits = req.params.idunits;
        execSQLQuery(`select * from appnotifications where receive = 1 and ${(idunits > 0) ? 'idunits = ?' : 'true'};`, res, req.originalUrl, [idunits]);
    });


    return router;
};

function RealizaChamada(Index, db, Alunos, callback) {
    if (Index < Alunos.length) {
        const sqlGetTurmasCalendario = `SELECT
            tc.IdTurmasCalendario,
            tc.Data,
            tc.HoraInicial,
            tc.HoraFinal,
            tc.CodigoInstrutor,
            tt.IDTreinando
        FROM ${db}TurmasCalendario tc
        inner join ${db}TreinandosTurmas tt on tt.IDCurso = tc.CodigoCurso and tc.IdTurma = tt.CodigoTurma
        WHERE tc.IdTurma = ${Alunos[Index]['CodigoTurma']} AND CodigoCurso = ${Alunos[Index]['IDCurso']} AND tc.Ministrado = 0
        ORDER BY tc.Data ASC;`;
        sqlServerConnection.request().query(sqlGetTurmasCalendario, function (error0, results0, fields0) {
            if (error0 || !results0.recordset || results0.recordset.length == 0) {
                console.log("Erro GetTurmasCalendario: " + error0);
                Index++;
                RealizaChamada(Index, db, Alunos, callback);
                return;
            }
            const TurmasCalendario = results0.recordset;
            Alunos[Index]['IDTreinando'] = TurmasCalendario[0]['IDTreinando'];
            const sqlGetChamada = `select coalesce((SELECT TOP(1) IDChamadas FROM ${db}Chamadas ORDER BY IDChamadas DESC), 1) as 'IDChamadas';`;
            sqlServerConnection.request().query(sqlGetChamada, function (error1, results1, fields1) {
                const IDChamadas = results1.recordsets[0][0]['IDChamadas'] + 1;
                const IDTurma = Alunos[Index]['CodigoTurma'];
                const IDCurso = Alunos[Index]['IDCurso'];
                const Data = formatDate(TurmasCalendario[0]['Data'], 0);
                const Hora = formatDate(TurmasCalendario[0]['HoraInicial'], 1);
                const HoraInicial = formatDate(TurmasCalendario[0]['HoraInicial'], 1);
                const HoraFinal = formatDate(TurmasCalendario[0]['HoraFinal'], 1);
                const CodigoInstrutor = TurmasCalendario[0]['CodigoInstrutor'];
                const DataEnvioApp = formatDate(new Date(), 0);

                const sqlInsertChamada = `INSERT INTO ${db}Chamadas (IDChamadas, IDTurma, IDCurso, Data, Hora, HoraInicio, HoraTermino, IDInstrutor, Flex, APP, DataEnvioAPP, DataAtualizacaoAPP, IDFuncionarioAPP)
                VALUES (${IDChamadas}, ${IDTurma}, ${IDCurso}, '${Data}', '${Hora}', '${HoraInicial}', '${HoraFinal}', ${CodigoInstrutor}, 1, 1, '${DataEnvioApp}', '${DataEnvioApp}', ${CodigoInstrutor});`;
                sqlServerConnection.request().query(sqlInsertChamada, function (error2, results2, fields2) {
                    const sqlInsertChamadaConteudo = `INSERT INTO ${db}ChamadasConteudos (IDChamadas, CodigoConteudo, DescricaoConteudo)
                    VALUES (${IDChamadas}, 0, 'Aula de robótica');`;
                    sqlServerConnection.request().query(sqlInsertChamadaConteudo, function (error3, results3, fields3) {
                        const sqlUpdateTurmasCalendario = `UPDATE ${db}TurmasCalendario SET
                            Ministrado = 1,
                            Data = '${Data}'
                        WHERE IdTurmasCalendario = ${TurmasCalendario[0]['IdTurmasCalendario']};`;
                        sqlServerConnection.request().query(sqlUpdateTurmasCalendario, function (error4, results4, fields4) {
                            const sqlInsertChamadasDetalhes = `INSERT INTO ${db}ChamadasDetalhes (IDChamadas, IDTreinando, Presente, Avisada, Justificativa, UltimaCarta)
                            VALUES (${IDChamadas}, ${Alunos[Index]['IDTreinando']}, 1, 0, null, null);`;
                            sqlServerConnection.request().query(sqlInsertChamadasDetalhes, function (error5, results5, fields5) {
                                Index++;
                                RealizaChamada(Index, db, Alunos, callback);
                            });
                        });
                    });
                });
            });
        });
    }
    else {
        callback(true);
    }
}

function formatDate(date, ret) {
    var novaData = date.toISOString().replace(/T/, ' ').replace(/\..+/, '').split(" ");
    return novaData[ret];
}

module.exports = routes;