-- name: GetProduct :one
SELECT * FROM products
WHERE id = ? LIMIT 1;

-- name: ListProducts :many
SELECT * FROM products
ORDER BY title
LIMIT ? OFFSET ?;

-- name: CreateProduct :execresult
INSERT INTO products (
    principle_id, product_category_id, title, description,
    model_number, model_number_format, notes, stock,
    item_code, item_description
) VALUES (
    ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
);

-- name: UpdateProduct :exec
UPDATE products
SET principle_id = ?,
    product_category_id = ?,
    title = ?,
    description = ?,
    model_number = ?,
    model_number_format = ?,
    notes = ?,
    stock = ?,
    item_code = ?,
    item_description = ?
WHERE id = ?;

-- name: DeleteProduct :exec
DELETE FROM products
WHERE id = ?;

-- name: SearchProductsByTitle :many
SELECT * FROM products
WHERE title LIKE CONCAT('%', ?, '%')
ORDER BY title
LIMIT ? OFFSET ?;

-- name: GetProductsByCategory :many
SELECT * FROM products
WHERE product_category_id = ?
ORDER BY title
LIMIT ? OFFSET ?;

-- name: GetProductByItemCode :one
SELECT * FROM products
WHERE item_code = ?
LIMIT 1;