const knex = require('knex')

const knexInstance = knex({
    client: 'pg',
    connection: 'postgresql://dunder-mifflin@localhost/knex-practice',
})

function searchByName(searchTerm) {
    knexInstance
        .select('id', 'name', 'price', 'date_added', 'checked', 'category')
        .from('shopping_list')
        .where('name', 'ILIKE', `%${searchTerm}%`)
        .then(result => {
            console.log(result)
        })
}

searchByName('pepper')

function paginateItems(pageNumber) {
    const resultsPerPage = 6
    const offset = resultsPerPage * (pageNumber - 1)
    knexInstance
        .select('id', 'name', 'price', 'date_added', 'checked', 'category')
        .from('shopping_list')
        .limit(resultsPerPage)
        .offset(offset)
        .then(result => {
            console.log(rult)
        })
}

paginateItems(2)

function getItemByAge (daysAgo) {
    knexInstance
        .select('id', 'name', 'price', 'date_added', 'checked', 'category')
        .from('shopping_list')
        .where(
        'date_added',
        '<',
        knexInstance.raw(`now() - '?? days'::INTERVAL`, daysAgo)
        )
        .then(result => {
            console.log(result)
        })
}

getItemByAge(8)

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

getCategoryPrice();