require('dotenv').config();
const knex = require('knex');

const knexInstance = knex({
  client: 'pg',
  connection: process.env.DB_URL
});

function searchByTerm(searchTerm) {
  knexInstance
    .from('shopping_list')
    .select('*')
    .where('name', 'ILIKE', `%${searchTerm}%`)
    .then(result => {
      console.log(result);
    });
}

function searchByPage(pageNumber) {
  const productsPerPage = 6;
  const offset = productsPerPage * (pageNumber - 1);
  knexInstance
    .from('shopping_list')
    .select('*')
    .limit(productsPerPage)
    .offset(offset)
    .then(result => {
      console.log(result);
    });
}

function itemsAfterDate(daysAgo) {
  knexInstance
    .from('shopping_list')
    .select('*')
    .where(
      'date_added',
      '>',
      knexInstance.raw(`now() - '?? days'::INTERVAL`, daysAgo)
    )
    .then(result => {
      console.log(result);
    });
}

knexInstance
  .from('shopping_list')
  .select('category')
  .sum('price as total')
  .groupBy('category')
  .then(result => {
    console.log(result);
  });

knexInstance.from('shopping_list');