-- name: CreatePackingList :one
INSERT INTO packinglist (
    status,
    order_id,
    user_id,
    exportcompany_id
 ) VALUES (
      $1,
      $2,
      $3,
      $4
 ) RETURNING *;

-- name: GetPackList :one
SELECT * FROM packinglist
WHERE id = $1 LIMIT 1;

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

-- name: UpdatePackList :one
UPDATE packinglist
  set
    status   = $2
WHERE id = $1
RETURNING *;

-- name: DeletePackList :exec
DELETE FROM packinglist WHERE id= $1;

-- name: UpdatePackingListWithResort :one
UPDATE packinglist_products
  set
    status   = $2
WHERE id = $1
RETURNING *;

-- name: PackingListWithResort :one
SELECT EXISTS(
SELECT 1 FROM packinglist_products
   WHERE packinglist_products.status = $1
   AND   packinglist_products.id = $2
);


-- name: CreatePackingListOthers :one
INSERT INTO packinglist_others (
      packinglist_id,
      productname,
      totalboxes,
      boxweight,
      productnetweight,
      totalproductnetweight,
      totalproductgrossweight
 ) VALUES (
      $1,
      $2,
      $3,
      $4,
      $5,
      $6,
      $7
 ) RETURNING *;

-- name: GetPackingListOthers :one
SELECT * FROM packinglist_others
WHERE packinglist_id = $1 LIMIT 1;

-- name: ListPackingListsOthers :many
SELECT * FROM packinglist_others
ORDER BY packinglist_id LIMIT $1
OFFSET $2;

-- name: UpdatePackingListOthers :one
UPDATE packinglist_others
  set
     packinglist_id          = $2,
     productname             = $3,
     totalboxes              = $4,
     boxweight               = $5,
     productnetweight        = $6,
     totalproductnetweight   = $7,
     totalproductgrossweight = $8
WHERE id = $1
RETURNING *;

-- name: DeletePackingList :exec
DELETE FROM packinglist_others WHERE packinglist_id= $1;

-- name: CreatePackingListProducts :one
INSERT INTO packinglist_products (
      packinglist_id,
      order_id,
      receptionform_id,
      batchno,
      traceability_code,
      product_id,
      totalboxes,
      boxweight,
      productnetweight,
      totalproductnetweight,
      totalproductgrossweight
 ) VALUES (
      $1,
      $2,
      $3,
      $4,
      $5,
      $6,
      $7,
      $8,
      $9,
      $10,
      $11
 ) RETURNING *;

 -- name: GetPackingListProducts :one
SELECT * FROM packinglist_products
WHERE id = $1 LIMIT 1;

 -- name: GetPackingListProductsByOrder :one
SELECT * FROM packinglist_products
WHERE order_id = $1 LIMIT 1;


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

-- name: UpdatePackingListBatchDetails :one
UPDATE  packinglist_batch
   set
      totalboxes               = $2,
      boxweight                = $3,
      productnetweight         = $4,
      totalproductnetweight    = $5,
      totalproductgrossweight  = $6,
      status                   = $7
WHERE id = $1
RETURNING *;

-- name: UpdatePackingBatchStatus :one
UPDATE  packinglist_batch
   set
      status  = $2
WHERE id = $1
RETURNING *;

-- name: UpdatePackingListProducts :one
UPDATE packinglist_products
  set
      packinglist_id          =  $2,
      order_id                =  $3,
      traceability_code       =  $4,
      product_id              =  $5,
      totalboxes              =  $6,
      boxweight               =  $7,
      productnetweight        =  $8,
      totalproductnetweight   =  $9,
      totalproductgrossweight =  $10
WHERE id = $1
RETURNING *;

-- name: DeletePackingListProducts :exec
DELETE FROM packinglist_products WHERE packinglist_id= $1;

-- name: CreatePackingListBatch :one
INSERT INTO packinglist_batch (
      packinglist_id,
      order_id,
      receptionform_id,
      batchno,
      traceability_code,
      product_id,
      boxweight,
      productnetweight,
      totalproductnetweight,
      totalproductgrossweight,
      totalboxes
 ) VALUES (
      $1,
      $2,
      $3,
      $4,
      $5,
      $6,
      $7,
      $8,
      $9,
      $10,
      $11
 ) RETURNING *;

 -- name: GetPackingListBatch :one
SELECT * FROM packinglist_batch
WHERE id = $1 LIMIT 1;

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

-- name: UpdatePackingListBatch :one
UPDATE packinglist_batch
  set
      packinglist_id          = $2,
      order_id                = $3,
      traceability_code       = $4,
      product_id              = $5,
      totalboxes              = $6,
      batchno                 = $7,
      boxweight               = $8,
      productnetweight        = $9,
      totalproductnetweight   = $10,
      totalproductgrossweight = $11
WHERE id = $1
RETURNING *;

-- name: DeletePackingListBatch :exec
DELETE FROM packinglist_batch WHERE packinglist_id= $1;


-- name: CheckOrderIDExistance :one
SELECT EXISTS (
    SELECT 1 FROM packinglist
    WHERE order_id = $1
);

-- name: CheckCropAndTraceabilityCode :one
SELECT EXISTS (
    SELECT 1 FROM packinglist_products
    WHERE product_id = $1
    AND   traceability_code = $2
    AND   packinglist_id = $3
    AND   order_id       = $4
);


-- name: ReturnPackingListByOrderID :one
SELECT id FROM packinglist where order_id = $1;

-- name: UpdatePackingListProductsWithSameOrderAndTraceability :exec
UPDATE packinglist_products
  set
        totalboxes               = $4,
        boxweight                = $5,
        batchno                  = $6,
        totalproductnetweight    = $7,
        totalproductgrossweight  = $8,
        productnetweight         = $9
  WHERE packinglist_id = $1
  AND   product_id     = $2
  AND   order_id       = $3;

-- name: ListExporterPackingLists :many
SELECT packinglist.order_id,countries.countryname destination,clients.companyname, packinglist.created_at,
packinglist.id packinglists_id,packinglist.status packinglist_status, users.id user_id, users.fullname, exportcompanies.expname,
packhouses.phname,exportcompanies.expregno,orders.created_at as order_date FROM packinglist
JOIN orders    on orders.id                = packinglist.order_id
JOIN clients   on clients.id               = orders.client_id
JOIN users     on users.id                 = packinglist.user_id
JOIN countries on countries.id             = clients.country_id
JOIN exportcompanies    on exportcompanies.id = packinglist.exportcompany_id
JOIN packhouse_exporter on packhouse_exporter.exportcompany_id = exportcompanies.id
JOIN packhouses         on packhouses.id   = packhouse_exporter.packhouse_id
WHERE packinglist.exportcompany_id    = $3
AND   packinglist.status              = $4
ORDER BY packinglist.id DESC LIMIT $1 OFFSET $2;


-- name: ListExporterOrderPackingLists :many
SELECT packinglist.order_id,countries.countryname destination,clients.companyname, packinglist.created_at,
packinglist.id packinglists_id,packinglist.status packinglist_status, users.id user_id, users.fullname, exportcompanies.expname,
packhouses.phname,exportcompanies.expregno,orders.created_at as order_date FROM packinglist
JOIN orders    on orders.id                = packinglist.order_id
JOIN clients   on clients.id               = orders.client_id
JOIN users     on users.id                 = packinglist.user_id
JOIN countries on countries.id             = clients.country_id
JOIN exportcompanies    on exportcompanies.id = packinglist.exportcompany_id
JOIN packhouse_exporter on packhouse_exporter.exportcompany_id = exportcompanies.id
JOIN packhouses         on packhouses.id   = packhouse_exporter.packhouse_id
WHERE packinglist.order_id    = $3
AND   packinglist.status              = $4
ORDER BY packinglist.id DESC LIMIT $1 OFFSET $2;


-- name: MatchOrderIDTraceability :one
SELECT EXISTS (
    SELECT 1 FROM packinglist_products
    WHERE packinglist_products.order_id = $1
    AND   packinglist_products.traceability_code = $2
    AND   packinglist_products.product_id = $3
);


-- name: ReturnReceptionIDPackingList :one
SELECT receptionform.id reception_id, receptionform.traceability_code, receptionform.order_id FROM packinglist
LEFT JOIN receptionform on receptionform.id = packinglist.reception_id
WHERE packinglist.id = $1;

-- name: CheckOrderExistanceinPackingList :one
SELECT EXISTS (
    SELECT 1 FROM packinglist_products
    WHERE packinglist_products.order_id = $1
);

-- name: PackingListProductDetails :many
SELECT packinglist_products.order_id,packinglist_products.packinglist_id pklist_id,packinglist_products.receptionform_id,
packinglist_products.traceability_code,packinglist_products.product_id crop_id,clients.companyname,crops.cropname,crops.crophscode,crops.riskstatus,
totalboxes,users.fullname,users.id puser_id, packinglist.status,packinglist_products.status as packinglist_product_status,
packinglist_products.boxweight,packinglist_products.productnetweight,packinglist_products.totalproductgrossweight,packinglist_products.totalproductnetweight, packinglist_products.id plpid,
countries.countryname,packinglist.exportcompany_id FROM packinglist_products
JOIN  packinglist on   packinglist.id     =  packinglist_products.packinglist_id
JOIN  users       on   users.id           =  packinglist.user_id
JOIN  crops       on   crops.id           =  packinglist_products.product_id
JOIN  orders      on   orders.id          =  packinglist_products.order_id
JOIN  clients     on   clients.id         =  orders.client_id
JOIN  countries   on   countries.id       = clients.country_id
WHERE packinglist_products.packinglist_id = $1;


-- name: PackingListPerConsignment :many
SELECT packinglist_products.order_id,packinglist_products.packinglist_id pklist_id,packinglist_products.traceability_code,
packinglist_products.product_id crop_id,clients.companyname,crops.cropname,totalboxes,users.fullname,users.id puser_id,boxweight,
productnetweight,totalproductnetweight,totalproductgrossweight,packinglist_products.id row_id  FROM packinglist_products
JOIN  packinglist on    packinglist.id     =  packinglist_products.packinglist_id
JOIN  users       on    users.id           =  packinglist.user_id
JOIN  crops       on    crops.id           =  packinglist_products.product_id
JOIN  orders      on    orders.id          =  packinglist_products.order_id
JOIN  clients     on    clients.id         =  orders.client_id
WHERE orders.consignment_no = $1;

-- name: PackingListBatchDetails :one
SELECT packinglist_batch.order_id,packinglist_batch.packinglist_id pklist_id,
packinglist_batch.traceability_code,packinglist_batch.receptionform_id,
packinglist_batch.product_id crop_id,clients.companyname,crops.cropname,
totalboxes,users.fullname,countries.countryname, users.id puser_id,
boxweight,productnetweight,totalproductnetweight,totalproductgrossweight,
packinglist_batch.id plpid FROM packinglist_batch
JOIN  packinglist on   packinglist.id      =  packinglist_batch.packinglist_id
JOIN  users       on   users.id            =  packinglist.user_id
JOIN  crops       on   crops.id            =  packinglist_batch.product_id
JOIN  orders      on   orders.id           =  packinglist_batch.order_id
JOIN  clients     on   clients.id          =  orders.client_id
JOIN  countries   on   countries.id        =  clients.country_id
WHERE packinglist_batch.id = $1;

-- name: PackingListOtherDetails :many
SELECT productname product_name,totalboxes,boxweight,status,
productnetweight,totalproductnetweight,totalproductgrossweight,id FROM packinglist_others
WHERE packinglist_others.packinglist_id = $1;

-- name: DeletePackingListOtherProducts :exec
DELETE FROM packinglist_others WHERE id= $1;

-- name: PackingListSum :one
SELECT COUNT(id) FROM  packinglist
WHERE packinglist.exportcompany_id = $1
AND   packinglist.status IN ('Pending','Resorting','AwaitingResort');

-- name: PackingListOrderSum :one
SELECT COUNT(id) FROM  packinglist
WHERE packinglist.order_id = $1
AND   packinglist.status IN ('Pending','Resorting','AwaitingResort');

-- name: CheckBoxPackinglistID :one
SELECT EXISTS (
    SELECT 1 FROM boxes
    WHERE packinglist_id  = $1
    AND traceability_code = $2
);

-- name: CheckQcp4PackinglistID :one
SELECT EXISTS (
    SELECT 1 FROM qcp4
    WHERE packinglist_id = $1
);


-- name: CheckQcp5PackinglistID :one
SELECT EXISTS (
    SELECT 1 FROM qcp5
    WHERE packinglist_id = $1
);


-- name: CheckBoxWeightMatch :one
SELECT EXISTS (
    SELECT 1 FROM packinglist_products
    WHERE packinglist_id = $1
    AND   order_id = $2
    AND   traceability_code = $3
    AND   product_id  = $4
    AND   boxweight   = $5
);

-- name: UpdatePackingListWeight :one
UPDATE packinglist_products
       SET
       totalproductnetweight = $4
WHERE  packinglist_id = $3
AND    order_id       = $2
AND    traceability_code = $1
RETURNING *;

-- name: ReturnNetWeight :one
SELECT totalproductnetweight,
productnetweight,totalproductgrossweight
from packinglist_products
WHERE  packinglist_id = $3
AND    order_id       = $2
AND    product_id     = $4
AND    boxweight      = $5
AND    traceability_code = $1;


-- name: UpdatePackingListTotalBoxes :one
UPDATE packinglist_products
       SET
       totalboxes = $4,
       totalproductnetweight = $5,
       totalproductgrossweight = $8,
       productnetweight = $6
WHERE  packinglist_id = $3
AND    order_id       = $2
AND    traceability_code = $1
AND    boxweight         = $7
RETURNING *;

-- name: DetailsBatchPackingList :one
SELECT * FROM packinglist_batch
WHERE id = $1;


-- name: ReturnProductsOnPackinglist :many
SELECT packinglist_products.packinglist_id,
packinglist_products.order_id,packinglist_products.receptionform_id,
packinglist_products.batchno,packinglist_products.traceability_code,
packinglist_products.product_id,packinglist_products.totalboxes,
packinglist_products.boxweight,packinglist_products.productnetweight,
packinglist_products.totalproductnetweight,
packinglist_products.totalproductgrossweight
FROM  packinglist_products
WHERE packinglist_products.packinglist_id = $1;


-- name: SelectPackingListWeight :one
SELECT totalproductnetweight from packinglist_products
WHERE  packinglist_id = $3
AND    order_id       = $2
AND    product_id     = $4
AND    boxweight      = $5
AND    traceability_code = $1;


-- name: SelectPackingListTotalBoxes :one
SELECT totalboxes from packinglist_products
WHERE  packinglist_id = $3
AND    order_id       = $2
AND    product_id     = $4
AND    boxweight      = $5
AND    traceability_code = $1;

-- name: PackinglistPerOrder :one
SELECT packinglist_products.order_id,packinglist_products.packinglist_id pklist_id,packinglist_products.traceability_code,
packinglist_products.product_id crop_id,clients.companyname,crops.cropname,crops.crophscode,crops.riskstatus,packinglist_products.receptionform_id
totalboxes,users.fullname,users.id puser_id,packinglist.status,countries.countryname as destination,
packinglist_products.boxweight,packinglist_products.productnetweight,packinglist_products.totalproductgrossweight,packinglist_products.totalproductnetweight, packinglist_products.id plpid FROM packinglist_products
JOIN  packinglist on   packinglist.id     =  packinglist_products.packinglist_id
JOIN  users       on   users.id           =  packinglist.user_id
JOIN  crops       on   crops.id           =  packinglist_products.product_id
JOIN  orders      on   orders.id          =  packinglist_products.order_id
JOIN  clients     on   clients.id         =  orders.client_id
JOIN  countries   on   countries.id       =  clients.country_id
WHERE packinglist_products.order_id = $1  LIMIT 1;


-- name: GetPackingListDetails :one
SELECT packinglist.status,packinglist.created_at,clients.companyname,countries.countryname,exportcompanies.expname,exportcompanies.expregno,
packhouses.phname,orders.created_at order_created_at,packinglist.user_id user_id,
packinglist.created_at packinglist_created_at,orders.consignment_no,exportcompanies.id exportcompany_id FROM packinglist
JOIN orders             on orders.id          = packinglist.order_id
JOIN exportcompanies    on exportcompanies.id = packinglist.exportcompany_id
JOIN packhouse_exporter on packhouse_exporter.exportcompany_id = exportcompanies.id
JOIN packhouses         on packhouses.id      = packhouse_exporter.packhouse_id
JOIN clients            on clients.id         = orders.client_id
JOIN countries          on countries.id       = clients.country_id
WHERE packinglist.id = $1;

-- name: ReturnBatchPackingListByBatchNo :many
SELECT packinglist_batch.order_id,packinglist_batch.packinglist_id pklist_id,packinglist_batch.traceability_code,
packinglist_batch.product_id crop_id,clients.companyname,crops.cropname,packinglist_batch.receptionform_id,
totalboxes,users.fullname,countries.countryname, users.id puser_id,
boxweight,productnetweight,totalproductnetweight,totalproductgrossweight,
packinglist_batch.id plpid, packinglist_batch.status FROM packinglist_batch
JOIN  packinglist on   packinglist.id      =  packinglist_batch.packinglist_id
JOIN  users       on   users.id            =  packinglist.user_id
JOIN  crops       on   crops.id            =  packinglist_batch.product_id
JOIN  orders      on   orders.id           =  packinglist_batch.order_id
JOIN  clients     on   clients.id          =  orders.client_id
JOIN  countries   on   countries.id        =  clients.country_id
WHERE packinglist_batch.batchno = $1;


-- name: UpdateStatusonQcpProduct :exec
UPDATE qcp4_products
       SET
       status = $2
WHERE  id = $1
RETURNING *;

-- name: UpdatePackinglistProductStatusRow :one
UPDATE packinglist_products
    SET
    status = $5
WHERE product_id        = $1
AND   packinglist_id    = $2
AND   order_id          = $3
AND   traceability_code = $4
AND   boxweight         = $6
RETURNING *;

-- name: UpdatePackinglistOthersProductStatusRow :one
UPDATE packinglist_others
    SET
    status = $4
WHERE productname       = $1
AND   packinglist_id    = $2
AND   boxweight         = $3
RETURNING *;


-- name: GetPackinglistProductStatusRow :one
SELECT * FROM packinglist_products
WHERE product_id        = $1
AND   packinglist_id    = $2
AND   order_id          = $3
AND   traceability_code = $4
AND   boxweight         = $5;

-- name: GetPackinglistBatchRow :one
SELECT * FROM packinglist_batch
WHERE product_id        = $1
AND   packinglist_id    = $2
AND   order_id          = $3
AND   traceability_code = $4
AND   boxweight         = $5;

-- name: UpdatePackinglistOthersStatusRow :exec
UPDATE packinglist_others
    SET
      status            = $4
WHERE productname       = $1
AND   packinglist_id    = $2
AND   boxweight         = $3
RETURNING *;


-- name: ListFarmersOnPackingList :many
SELECT crops.cropname,crops.crophscode,farmers.fmname,crops.variant,farmers.fmphone from packinglist_products
JOIN packinglist  on packinglist.id                 = packinglist_products.packinglist_id
JOIN orders       on orders.id                      = packinglist.order_id
JOIN farmer_farms on farmer_farms.traceability_code = packinglist_products.traceability_code
JOIN farmers      on farmers.id                     = farmer_farms.farmer_id
JOIN crops        on crops.id                       = farmer_farms.crop_id
Where orders.consignment_no = $1;

-- name: InterceptProductPackingList :one
INSERT INTO product_interception_notice (
    packinglist_product_id,
    consignment_no,
    weight_rejected,
    qcp5_row_id,
    exportcompany_id,
    reason,
    action,
    notes,
    user_id
 ) VALUES (
      $1,
      $2,
      $3,
      $4,
      $5,
      $6,
      $7,
      $8,
      $9
 ) RETURNING *;

-- name: InterceptOtherProductPackingList :one
INSERT INTO productothers_interception_notice (
    packinglist_other_id,
    qcp5_row_other_id,
    weight_rejected,
    consignment_no,
    exportcompany_id,
    reason,
    action,
    notes,
    user_id
 ) VALUES (
      $1,
      $2,
      $3,
      $4,
      $5,
      $6,
      $7,
      $8,
      $9
 ) RETURNING *;

-- name: GetInterceptionNoticeOtherProducts :many
select productothers_interception_notice.packinglist_other_id,productothers_interception_notice.consignment_no,
productothers_interception_notice.exportcompany_id,productothers_interception_notice.reason,packinglist_others.productname,
productothers_interception_notice.action,productothers_interception_notice.notes,productothers_interception_notice.user_id,
productothers_interception_notice.weight_rejected,productothers_interception_notice.qcp5_row_other_id,users.fullname, exportcompanies.expname,productothers_interception_notice.created_at
from productothers_interception_notice
JOIN users on users.id = productothers_interception_notice.user_id
JOIN exportcompanies on exportcompanies.id = productothers_interception_notice.exportcompany_id
JOIN  packinglist_others on packinglist_others.id = productothers_interception_notice.packinglist_other_id
WHERE productothers_interception_notice.consignment_no = $1;


-- name: GetInterceptionNoticeProducts :many
select product_interception_notice.packinglist_product_id,product_interception_notice.consignment_no,product_interception_notice.exportcompany_id,product_interception_notice.reason,
product_interception_notice.action,product_interception_notice.notes,product_interception_notice.weight_rejected,product_interception_notice.qcp5_row_id,product_interception_notice.user_id, users.fullname, exportcompanies.expname, crops.crophscode, crops.cropname, crops.variant, product_interception_notice.created_at from product_interception_notice
JOIN users on users.id = product_interception_notice.user_id
JOIN exportcompanies on exportcompanies.id = product_interception_notice.exportcompany_id
JOIN crops on crops.id  = product_interception_notice.packinglist_product_id
WHERE product_interception_notice.consignment_no = $1;


-- name: PackingListProductsPerConsignment :many
SELECT packinglist_products.packinglist_id,
packinglist_products.order_id,packinglist_products.receptionform_id,
packinglist_products.batchno,packinglist_products.traceability_code,
packinglist_products.product_id,packinglist_products.totalboxes,
packinglist_products.boxweight,packinglist_products.productnetweight,
packinglist_products.totalproductnetweight,crops.cropname as product,
packinglist_products.totalproductgrossweight FROM  packinglist_products
JOIN  packinglist on packinglist.id  = packinglist_products.packinglist_id
JOIN  orders      on orders.id       = packinglist.order_id
JOIN  crops       on crops.id        = packinglist_products.product_id
WHERE orders.consignment_no = $1;


-- name: PackingListOthersPerConsignment :many
SELECT productname product_name,packinglist_others.totalboxes,packinglist_others.boxweight,packinglist_others.status,
packinglist_others.productnetweight,packinglist_others.totalproductnetweight,packinglist_others.totalproductgrossweight,
packinglist_others.id FROM  packinglist_others
JOIN  packinglist on packinglist.id  = packinglist_others.packinglist_id
JOIN  orders      on orders.id       = packinglist.order_id
WHERE orders.consignment_no = $1;
