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

    router.get('/getAll', (req, res) => {
        execSQLQuery(`SELECT * FROM classescontent`, res, req.originalUrl);
    });

    router.get('/getIdClasses1/:idclasses', verifyJWT, (req, res) => {
        const idclasses = parseInt(req.params.idclasses);
        execSQLQuery(`SELECT
            *
        FROM classescontent
        WHERE idclasses = ${idclasses}
        ORDER BY ordem asc;`, res, req.originalUrl);
    });

    router.get('/getCCbyEx/:idexercises', verifyJWT, (req, res) => {
        const idexercises = parseInt(req.params.idexercises);
        execSQLQuery(`SELECT
            *
        FROM classescontent
        WHERE idexercises = ${idexercises};`, res, req.originalUrl);
    });

    router.get('/getIdClasses/:idclasses', verifyJWT, (req, res) => {
        const idclasses = parseInt(req.params.idclasses);
        execSQLQuery(`SELECT
            MAX(ordem) as ordem
        FROM classescontent
        WHERE idclasses = ${idclasses};`, res, req.originalUrl);
    });

    router.get('/getClassescontent/:idclasses', verifyJWT, (req, res) => {
        const idclasses = parseInt(req.params.idclasses);
        execSQLQuery(`SELECT
            *
        FROM
            (SELECT
                c.idclassescontent,
                c.idclasses,
                c.ordem,
                c.idexercises,
                e.description,
                e.enabled,
                'Exercício' as type,
                'Editar' as acao, 
                '' as obs
            FROM classescontent c
            INNER JOIN exercises e ON c.idexercises = e.idexercises
            WHERE c.idclasses = ${idclasses}
            UNION ALL
            SELECT
                c.idclassescontent,
                c.idclasses,
                c.ordem,
                c.idmedia,
                m.description,
                1 as enabled,
                'Mídia' as type,
                'Prever' as acao,
                m.publishid as obs 
            FROM classescontent c
            INNER JOIN media m ON c.idmedia = m.idmedia
            WHERE c.idclasses = ${idclasses}
            UNION ALL
            SELECT
                c.idclassescontent,
                c.idclasses,
                c.ordem,
                c.idtexts,
                t.description,
                t.enabled,
                case when t.game = 0 then 'Texto' else 'Jogo' end as type,
                'Editar' as acao, 
                '' as obs
            FROM classescontent c
            INNER JOIN texts t ON c.idtexts = t.idtexts
            WHERE c.idclasses = ${idclasses}
            UNION ALL
            SELECT
                c.idclassescontent,
                c.idclasses,
                c.ordem,
                c.idgames,
                t.description,
                t.enabled,
                case when t.game = 0 then 'Texto' else 'Jogo' end as type,
                'Editar' as acao, 
                '' as obs
            FROM classescontent c
            INNER JOIN texts t ON c.idgames = t.idtexts
            WHERE c.idclasses = ${idclasses}
            UNION ALL
            SELECT
                c.idclassescontent,
                c.idclasses,
                c.ordem,
                c.idslides,
                s.title,
                1 as enabled,
                'Slide' as type,
                'Editar' as acao, 
                '' as obs
            FROM classescontent c
            INNER JOIN slides s ON c.idslides = s.idslides
            WHERE c.idclasses = ${idclasses}
            UNION ALL
            SELECT
	    		c.idclassescontent,
                c.idclasses,
                c.ordem,
                c.montagem,
                'Hora da montagem' as description,
                1 as enabled,
                'Montagem' as type,
                '⠀⠀⠀⠀⠀' as acao, 
                '' as obs
            FROM classescontent c
            WHERE c.idclasses = ${idclasses} AND c.montagem = 1
            UNION ALL
            SELECT
                c.idclassescontent,
                c.idclasses,
                c.ordem,
                c.idanimations,
                a.description,
                1 as enabled,
                'Animação' as type,
                'Editar' as acao, 
                '' as obs
            FROM classescontent c
            INNER JOIN animations a ON c.idanimations = a.idanimations
            WHERE c.idclasses = ${idclasses}
            UNION ALL
            SELECT
	    		c.idclassescontent,
                c.idclasses,
                c.ordem,
                c.idzoechattime,
                ct.description,
                1 as enabled,
                'ChatTime' as type,
                'Editar' as acao, 
                '' as obs
            FROM classescontent c
            INNER JOIN zoechattime ct ON c.idzoechattime = ct.idzoechattime
            WHERE c.idclasses = ${idclasses}
            UNION ALL
            SELECT
	    		c.idclassescontent,
                c.idclasses,
                c.ordem,
                c.idcharactersConversation,
                cc.description,
                1 as enabled,
                'Diálogo' as type,
                'Editar' as acao, 
                '' as obs
            FROM classescontent c
            INNER JOIN charactersConversation cc ON c.idcharactersConversation = cc.idcharactersConversation
            WHERE c.idclasses = ${idclasses}
            UNION ALL
            SELECT
	    		c.idclassescontent,
                c.idclasses,
                c.ordem,
                c.idzoelistenrepeat,
                lr.description,
                1 as enabled,
                'ListenRepeat' as type,
                'Editar' as acao, 
                '' as obs
            FROM classescontent c
            INNER JOIN zoelistenrepeat lr ON c.idzoelistenrepeat = lr.idzoelistenrepeat
            WHERE c.idclasses = ${idclasses}
            UNION ALL
            SELECT
                c.idclassescontent,
                c.idclasses,
                c.ordem,
                c.idfiles,
                f.description,
                1 as enabled,
                'Download' as type,
                'Baixar' as acao, 
                '' as obs
            FROM classescontent c
            INNER JOIN files f ON c.idfiles = f.idfiles
            WHERE c.idclasses = ${idclasses}
            ) result
        ORDER BY result.ordem;`, res, req.originalUrl);
    });

    router.post('/insert', verifyJWT, (req, res) => {
        const idexercises = req.body.idexercises || null;
        const idmedia = req.body.idmedia || null;
        const idtexts = req.body.idtexts || null;
        const idclasses = req.body.idclasses || null;
        const idslides = req.body.idslides || null;
        const idgames = req.body.idgames || null;
        const montagem = req.body.montagem || null;
        const idanimations = req.body.idanimations || null;
        const idzoechattime = req.body.idzoechattime || null;
        const idcharactersConversation = req.body.idcharactersConversation || null;
        const idzoelistenrepeat = req.body.idzoelistenrepeat || null;
        const idfiles = req.body.idfiles || null;
        execSQLQuery(`INSERT INTO classescontent(idexercises, idmedia, idtexts, idclasses, ordem, idslides, idgames, montagem, idanimations, idzoechattime, idcharactersConversation, idzoelistenrepeat, idfiles) SELECT
        ${idexercises}, ${idmedia}, ${idtexts}, ${idclasses}, (select coalesce((select (ordem + 1) as ordem from classescontent where idclasses = ${idclasses} order by ordem desc limit 1), 0)) ,${idslides}, ${idgames}, ${montagem}, ${idanimations}, ${idzoechattime}, ${idcharactersConversation}, ${idzoelistenrepeat}, ${idfiles};`, res, req.originalUrl);
    })

    router.patch('/update', verifyJWT, (req, res) => {
        const idclassescontent = parseInt(req.body.idclassescontent);
        const idexercises = req.body.idexercises || null;
        const idmedia = req.body.idmedia || null;
        const idtexts = req.body.idtexts || null;
        const idclasses = req.body.idclasses || null;
        const idslides = req.body.idslides || null;
        const idgames = req.body.idgames || null;
        const montagem = req.body.montagem || null;
        const idanimations = req.body.idanimations || null;
        const idcharactersConversation = req.body.idcharactersConversation || null;
        const idzoelistenrepeat = req.body.idzoelistenrepeat || null;
        const idfiles = req.body.idfiles || null;
        const ordem = parseInt(req.body.ordem);
        execSQLQuery(`UPDATE classescontent SET
            idexercises = ${idexercises},
            idmedia = ${idmedia},
            idtexts = ${idtexts},
            idclasses = ${idclasses},
            idslides = ${idslides},
            idgames = ${idgames},
            montagem = ${montagem},
            idanimations = ${idanimations},
            idcharactersConversation = ${idcharactersConversation},
            idzoelistenrepeat, ${idzoelistenrepeat},
            idfiles = ${idfiles},
            ordem = ${ordem}
        WHERE idclassescontent = ${idclassescontent};`, res, req.originalUrl);
    })

    router.delete('/delete/:idclassescontent', verifyJWT, (req, res) => {
        const idclassescontent = req.params.idclassescontent;
        execSQLQuery(`DELETE FROM classescontent
        WHERE idclassescontent in (${idclassescontent});`, res, req.originalUrl);
    })

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

    router.post('/excluirArquivos', verifyJWT, (req, res) => {
        const shell = require('shelljs');
        const Tipo = req.body.type;
        const IDItem = req.body.idexercises;
        switch (Tipo) {
            case "Exercício": {
                mysqlConnection.query(`SELECT ea.idexerciseanswers FROM exercisequestions eq INNER JOIN exerciseanswers ea ON eq.idexercisequestions = ea.idexercisequestions WHERE eq.idexercises = ${IDItem} AND ea.hasmedia = 1;`, function (error, results, fields) {
                    for (let i = 0; i < results.length; i++) {
                        try {
                            shell.exec("rm " + Variables('src_media') + "images/ans_" + results[i]['idexerciseanswers'] + ".jpg");
                        }
                        catch (e) {
                            console.log("Não apagou");
                        }
                    }
                    res.send(JSON.stringify({
                        "status": 200,
                        "error": null,
                        "response": []
                    }))
                })
                break;
            }
            case "Jogo": {
                shell.exec("rm -rf " + Variables('src_media') + "games/" + IDItem + "/", function(Sucesso) {
                    res.send(JSON.stringify({
                        "status": 200,
                        "error": null,
                        "response": Sucesso
                    }))
                })
                break;
            }
            case "Mídia": {
                mysqlConnection.query(`SELECT publishid FROM media WHERE idmedia = ${IDItem};`, function (error, results, fields) {
                    shell.exec("rm " + Variables('src_media') + "videos/" + results[0]['publishid'] + ".zip", function(Sucesso) {
                        res.send(JSON.stringify({
                            "status": 200,
                            "error": null,
                            "response": Sucesso
                        }))
                    })
                })
                break;
            }
            case "Slide": {
                shell.exec("rm " + Variables('src_media') + "images/slide_" + IDItem + "_*", function(Sucesso) {
                    res.send(JSON.stringify({
                        "status": 200,
                        "error": null,
                        "response": Sucesso
                    }))
                })
                break;
            }
            case "Animação": {
                shell.exec("rm " + Variables('src_media') + "animations/" + IDItem + ".zip", function(Sucesso) {
                    res.send(JSON.stringify({
                        "status": 200,
                        "error": null,
                        "response": Sucesso
                    }))
                })
                break;
            }
            case "Aula": {
                shell.exec("rm " + Variables('src_media') + "images/back_" + IDItem + "_.jpg", function(Sucesso) {
                    shell.exec("rm " + Variables('src_media') + "images/gif_" + IDItem + "_.gif", function(Sucesso) {
                        res.send(JSON.stringify({
                            "status": 200,
                            "error": null,
                            "response": Sucesso
                        }))
                    })
                })
                break;
            }
        }
    });

    router.get('/getClassDownloads/:idclasses', verifyJWT, (req, res) => {
        const idclasses = parseInt(req.params.idclasses);
        execSQLQuery(`select
            cc.idclassescontent,
            cc.ordem,
            f.*
        from classescontent cc
        inner join files f on f.idfiles = cc.idfiles
        where cc.idclasses = ${idclasses};`, res, req.originalUrl);
    });

    return router;
};

module.exports = routes;