-- name: CreateOrderCrop :one
INSERT INTO order_crops (           
    order_id,
    crop_id,
    weight,
    weightreceived
 ) VALUES (
    $1, $2, $3, $4
) 
RETURNING *;

-- name: GetOrderCrop :one
SELECT * FROM order_crops
WHERE id = $1 LIMIT 1;

-- name: ListOrderCrop :many
SELECT * FROM order_crops
ORDER BY id LIMIT $1
OFFSET $2;

-- name: UpdateOrderCrop :one
UPDATE order_crops
  set 
    weightreceived = $3
WHERE order_id = $1 
AND   crop_id  = $2
RETURNING *;

-- name: DeleteOrderCrops :exec
DELETE FROM order_crops WHERE id= $1;

-- name: RetrieveProductsOnOrders :many
SELECT crops.*
FROM crops
JOIN order_crops on crops.id = order_crops.crop_id
WHERE order_crops.order_id = $1 
AND   order_crops.status   = 'Active';


-- name: UpdateOrderWeightCrop :one
UPDATE order_crops
  set 
     weight = $3
WHERE crop_id = $1 AND order_id= $2
RETURNING *;

-- name: GetWeightReceived :one
SELECT weightreceived from order_crops
WHERE order_id = $1;

-- name: GetWeightOnOrderID :one
SELECT weight from order_crops
WHERE order_id = $1 AND crop_id = $2;


-- name: GetWeightPerCropReceived :one
SELECT weightreceived from order_crops
WHERE  order_id = $1 
AND    crop_id  = $2 
LIMIT 1;

-- name: UpdateWeightPerCropReceived :one
UPDATE  order_crops 
   SET  weightreceived = $3
WHERE  order_id = $1 
AND    crop_id  = $2
RETURNING *;

-- name: UpdateStatusOnCrop :one
UPDATE order_crops
  set 
   status = $3
WHERE crop_id = $1 
AND   order_id= $2
AND   weightreceived IS NULL 
OR    weightreceived = 0
RETURNING *;

-- name: UpdateCropWeightOnOrder :one
UPDATE order_crops
  set 
   weight = $3
WHERE crop_id = $1 
AND   order_id= $2
RETURNING *;

-- name: CropOrderIDExistance :one
SELECT EXISTS (
   select 1 from order_crops
   WHERE crop_id = $1 
   AND   order_id = $2
);