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('/InserirFeriadosPadrao/:ano', verifyJWT, (req, res) => {
    const Ano = parseInt(req.params.ano);
    execSQLQuery(`insert into holidays (date, reason) values
    ('${Ano}-01-01', 'Ano Novo'),
    ('${Ano}-02-15', 'Carnaval'),
    ('${Ano}-02-16', 'Carnaval'),
    ('${Ano}-04-02', 'Paixão de Cristo'),
    ('${Ano}-04-21', 'Tiradentes'),
    ('${Ano}-05-01', 'Dia do Trabalho'),
    ('${Ano}-06-03', 'Corpus Christi'),
    ('${Ano}-09-07', 'Independência do Brasil'),
    ('${Ano}-10-12', 'Dia das Crianças'),
    ('${Ano}-11-02', 'Finados'),
    ('${Ano}-11-15', 'Proclamação da República'),
    ('${Ano}-12-25', 'Natal');`, res, req.originalUrl);
  });

  router.post('/InserirFeriado', verifyJWT, (req, res) => {
    const reason = req.body.reason;
    const date = req.body.date;
    execSQLQuery(`INSERT INTO holidays (date, reason)
    VALUES (?, ?);`, res, req.originalUrl, [date, reason]);
  });

  router.patch('/AtualizarFeriado', verifyJWT, (req, res) => {
    const reason = req.body.reason;
    const date = req.body.date;
    execSQLQuery(`UPDATE holidays SET
      date = ?,
      reason = ?
    WHERE date = ?;`, res, req.originalUrl, [date, reason, date]);
  });

  router.post('/getFeriado', verifyJWT, (req, res) => {
    const date = req.body.date;
    execSQLQuery(`SELECT
      *
    FROM holidays
    WHERE date = "${date}";`, res, req.originalUrl);
  });

  router.get('/getAllFeriados', verifyJWT, (req, res) => {
    execSQLQuery(`SELECT
      date_format(date, '%d/%m/%Y') as 'date',
      date as 'cdate',
      reason
    FROM holidays
    order by cdate asc;`, res, req.originalUrl);
  });

  router.get('/getMySQLinSQLServerFeriados', verifyJWT, (req, res) => {
    execSQLQuery(`SELECT
      concat(date_format(date, '%Y-%m-%d'), ' 00:00:00.000') AS 'Data',
      reason AS 'Motivo'
    FROM holidays;`, res, req.originalUrl);
  })

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

  return router;
}

module.exports = routes;