-- name: CreateBoxes :one
INSERT INTO boxes (           
    packinglist_id,
    traceability_code,
    product_id,
    qrcode,
    status,
    user_id,
    exportcompany_id
 ) VALUES (
    $1, 
    $2, 
    $3, 
    $4, 
    $5, 
    $6, 
    $7
) 
RETURNING *;

 -- name: GetBox :one
SELECT * FROM boxes
WHERE id = $1 LIMIT 1;

-- name: ListBoxes :many
SELECT * FROM boxes
WHERE exportcompany_id = $3
LIMIT $1
OFFSET $2;

 -- name: CheckPackListExistance :one
SELECT EXISTS(SELECT 1 FROM boxes WHERE packinglist_id = $1) AS id_exists;

-- name: GetBoxesPerPackList :many
SELECT boxes.packinglist_id,
boxes.traceability_code,boxes.product_id,
boxes.qrcode,boxes.status,boxes.user_id,
boxes.exportcompany_id,crops.cropname, 
exportcompanies.expname as exportcompany, 
packinglist.created_at  as date_created FROM boxes
JOIN crops on crops.id = boxes.product_id
JOIN exportcompanies on exportcompanies.id = boxes.exportcompany_id
JOIN packinglist on packinglist.id = boxes.packinglist_id
WHERE packinglist_id = $1;

