const pagination = _req.getValues('pagination',_val.map());
const filter = _req.getValues('filter',_val.map());

const pageSize = 10;

let page = { start: 0, size: pageSize }

if (pagination != null) {
  page.size = pagination.getInt('pageSize', pageSize);
  page.start = (pagination.getInt('current', 1) - 1) * page.size;
}

if (page.size > 100){
  page.page = 100;
}


const queryFilter = _val.list();
let queryWhere = '';

if (filter != null) {
  if (filter.getString('data_criacao') != '') {
    queryWhere += ' AND encomenda.data_criacao = ?::date';
    queryFilter.add(`${ filter.getString('data_criacao') }`);  
  }
  if (filter.getString('data_envio') != '') {
    queryWhere += ' AND encomenda.data_envio = ?::date';
    queryFilter.add(`${ filter.getString('data_envio') }`);
  }
  if (filter.getInt('status') > 0) {
    queryWhere += ' AND encomenda.status = ?::int';
    queryFilter.add(`${ filter.getInt('status') }`);
  }
}

const dbEncomendas = _db.query(`
  SELECT
    encomenda.uid,
    encomenda.valor,
    encomenda.data_criacao,
    encomenda.data_envio,
    status_encomenda.status as status_nome,
    status_encomenda.id as status_id
  FROM encomenda
  INNER JOIN status_encomenda ON status_encomenda.id = encomenda.status
  WHERE 1 = 1
  ${queryWhere}
  LIMIT ${page.size} OFFSET ${page.start}
`, queryFilter);

const encomendas = _val.list();

for (const encomenda of dbEncomendas) {
  encomendas.add(
    _val.map()
      .set("uid", encomenda.getString('uid'))
      .set("data_criacao", encomenda.getString('data_criacao'))
      .set("data_envio", encomenda.getString('data_envio'))
      .set("valor", encomenda.getFloat('valor').toFixed(2))
      .set("status",
        _val.map()
          .set('nome', encomenda.getString('status_nome'))
          .set('id', encomenda.getInt('status_id'))
      )
  )
}

_out.json(
  _val.map()
    .set("results", encomendas)
    .set("total",
      _db.queryFirst(`
        SELECT COUNT(1) AS total 
        FROM encomenda
        WHERE 1 = 1
        ${queryWhere}
      `, queryFilter).getInt('total')
    )
);