const express = require('express');

const classApp = express.Router();

const libs = `${process.cwd()}/libs`;

const log = require(`${libs}/log`)(module);

const squel = require('squel');

const query = require(`${libs}/middlewares/query`);

const response = require(`${libs}/middlewares/response`);

const ReqQueryFields = require(`${libs}/middlewares/reqQueryFields`);

let rqfCount = new ReqQueryFields();

rqfCount.addField({
    name: 'filter',
    field: false,
    where: true
}).addField({
    name: 'dims',
    field: true,
    where: false
}).addValue({
    name: 'city',
    table: 'municipio',
    tableField: 'nome',
    resultField: 'city_name',
    where: {
        relation: '=',
        type: 'integer',
        field: 'municipio_id',
        table: 'turma'
    },
    join: {
        primary: 'id',
        foreign: 'municipio_id',
        foreignTable: 'turma'
    }
}).addValue({
    name: 'state',
    table: 'estado',
    tableField: 'nome',
    resultField: 'state_name',
    where: {
        relation: '=',
        type: 'integer',
        field: 'estado_id',
        table: 'turma'
    },
    join: {
        primary: 'id',
        foreign: 'estado_id',
        foreignTable: 'turma'
    }
}).addValue({
    name: 'region',
    table: 'regiao',
    tableField: 'nome',
    resultField: 'region_name',
    where: {
        relation: '=',
        type: 'integer',
        field: 'id'
    },
    join: {
        primary: 'id',
        foreign: 'regiao_id',
        foreignTable: 'turma'
    }
});


classApp.get('/', rqfCount.parse(), rqfCount.build(), (req, res, next) => {
  log.debug(req.sql.toParam());
   req.sql.field('COUNT(turma.id)', 'total')
   .field("'Brasil'", 'name')
   .field('turma.ano_censo', 'year')
   .from('turma')
   .group('turma.ano_censo')
   .order('turma.ano_censo')
   .where('turma.tipo_turma_id = 0 OR turma.tipo_turma_id = 1 OR turma.tipo_turma_id = 2 OR turma.tipo_turma_id = 3');
   next();
}, query, response('class'));

module.exports = classApp;