var bookshelf = require('bookshelf')(knex);

const ArticuloImagen = bookshelf.Model.extend({
  tableName: 'ARTICULOS_IMAGEN'
});

const Articulo = bookshelf.Model.extend({
  tableName: 'ARTICULOS',
  imagenes: function() {
    return this.hasMany(ArticuloImagen, 'id_articulo', 'id');
  }
});

router.get('/articulos', (req , res) => {

  let cantidadesVendidas = knex.schema
    .raw(`select stock.SEC, stock.ART, SUM(stock.can) as cantidadVendida 
      from AMOVSTOC stock 
      join ARTICULOS art on art.CodSec = stock.SEC and art.CodArt = stock.ART and 
      art.categoria_selfservice > 0 and art.PreVen > 0 
      where stock.FEC >= dateadd(month, -2, getdate()) and stock.CYV = 'V' and TCO = 'FT' 
      group by stock.sec, stock.art`)

  var articulos = Articulo
    .where((query) => {
      query.where('NHA', 0);
      query.where('PreVen', '>', 0);
      query.where('categoria_selfservice', '>', 0);
    })
    .fetchAll({withRelated: ['imagenes']})

  Promise.all([articulos, cantidadesVendidas])
    .then(values => {

      let articulos = values[0];
      let cantidadesVendidas = values[1];

      var promisesCodBar = [];

      articulos.forEach(articulo => {

        let cantidadVendida = cantidadesVendidas.filter(art => {
          return articulo.get('CodArt') == art.ART && articulo.get('CodSec') == art.SEC;
        });

        articulo.set('cantidadVendida', cantidadVendida[0] ? cantidadVendida[0].cantidadVendida : 0);

        promisesCodBar.push(
          knex('CODBAR').where({
            CodSec: articulo.get('CodSec'),
            CodArt: articulo.get('CodArt')
          })
        );
      });

      Promise.all(promisesCodBar).then((codigoBarra) => {

        codigoBarra.forEach(codigo => {
          codigo = codigo[0];

          let articulo = articulos.filter(art => {
            return art.get('CodArt') == codigo.CodArt;
          });

          articulo[0].set('codigoBarra', codigo.CodBar);
        });

        res.status(200).send(articulos);
      });

    });
});

router.get('/articulos/:id', (req, res) => {
  Articulo
    .where({id: req.params.id})
    .fetch({withRelated: 'imagenes'})
    .then(articulo => res.status(200).send(articulo))
    .catch(err => { res.status(500).send(err)})
});

module.exports = router;