const SQL = require("../_helper/database");

module.exports = {
    addItem,
    filterItemsByCategory,
    updateItemPrice,
    getItemByName,
    deleteItem,
    getImages,
    searchItems,
    getCategories
};

async function addItem(images, title, description, price, category, seller_id) {
    let db = await SQL.getConnection();
    let res = await db.execute(`
        INSERT INTO items (title, description, price, category_id, seller_id) VALUES
        (?, ?, ?, ?, ?)
    `, [title, description, price, category, seller_id]);
    await addImages(images, res[0].insertId)
}

//TODO: join with image table to get a thumbnail
async function filterItemsByCategory(categoryID) {
    let db = await SQL.getConnection();
    let [items] = await db.query(`
        SELECT *,  (
            SELECT url FROM images WHERE images.item_id = items.id ORDER BY images.id LIMIT 1
            ) as url
        FROM items 
        WHERE category_id = ?
    `, [categoryID]);
    return items;
}

async function updateItemPrice(item_id, new_price) {
    let db = await SQL.getConnection();
    await db.execute(`
        UPDATE items SET price = ? WHERE id = ?
    `, [new_price, item_id]);
}

async function searchItems(keyword) {
    let db = await SQL.getConnection();
    let searchQuery = `
        SELECT * FROM items
        WHERE name LIKE CONCAT('%', ?, '%') OR tags LIKE CONCAT('%', ?, '%')
    `;
    let results = await db.execute(searchQuery, [keyword, keyword]);
    return results;
}

//TODO: page index
async function getItemByName(item_name) {
    let db = await SQL.getConnection();
    const [rows] = await db.execute(`
        SELECT * FROM items WHERE title LIKE %?%
    `, [item_name]);
    return rows;
}

async function deleteItem(item_id) {
    let db = await SQL.getConnection();
    await db.execute(`
        DELETE FROM items WHERE id = ?
    `, [item_id]);
}

async function markAsSold(item_id){

}


async function addImages(images, id){
    let db = await SQL.getConnection();
    for (let image of images){
        await db.execute(`
            INSERT INTO images (item_id, url) VALUES (?, ?)
        `, [id, image.filename])
    }
}

async function getImages(itemID){
    let db = await SQL.getConnection()
    let urls = await db.execute(`
        SELECT url FROM images WHERE images.item_id = ${itemID}
    `)
    return urls[0]
}

async function getCategories(){
    let db = await SQL.getConnection()
    let cat = await db.execute(`
        SELECT name FROM categories ORDER BY id
    `)
    return cat[0]
}