-- name: CreateQcp3 :one
INSERT INTO qcp3 (
      batchno,
      reception_id,
      exportcompany_id,
      user_id,
      weightpassed,
      status
 ) VALUES (
     $1,
     $2,
     $3,
     $4,
     $5,
     $6
    )
RETURNING *;

-- name: GetQcp3 :one
SELECT * FROM qcp3
WHERE id = $1 LIMIT 1;

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

-- name: UpdateQcp3 :one
UPDATE qcp3
  set
      batchno = $2,
      reception_id = $3,
      exportcompany_id = $4,
      user_id = $5,
      status  = $6
WHERE id = $1
RETURNING *;

-- name: DeleteQcp3 :exec
DELETE FROM qcp3 WHERE id= $1;

-- name: ListPendingQcp3s :many
SELECT crops.cropname,crops.id as crop_id,qcp3.batchno,receptionform.order_id,farmers.fmname,
qcp3.user_id,users.fullname,qcp3.created_at,qcp3.status,countries.countryname country, clients.companyname client_name,
orders.consignment_no,qcp3.id qcp3_id,receptionform.traceability_code, qcp3.status qcp3_status from qcp3
LEFT JOIN receptionform on receptionform.id = qcp3.reception_id
LEFT JOIN orders        on orders.id  = receptionform.order_id
LEFT JOIN clients       on clients.id = orders.client_id
LEFT JOIN countries     on countries.id = clients.country_id
LEFT JOIN crops         on crops.id   = receptionform.crop_id
LEFT JOIN users         on users.id   = qcp3.user_id
LEFT JOIN farmers       on farmers.id  = receptionform.supplier_id
WHERE qcp3.exportcompany_id     = $3  AND qcp3.status = $4
ORDER BY qcp3.created_at DESC
LIMIT $1 OFFSET $2;


-- name: CompanyPendingQcp3s :many
SELECT crops.cropname,crops.id as crop_id,qcp3.batchno,receptionform.order_id,farmers.fmname,
qcp3.user_id,users.fullname,qcp3.created_at,qcp3.status,countries.countryname country, clients.companyname client_name,
orders.consignment_no,qcp3.id qcp3_id,receptionform.traceability_code, qcp3.status qcp3_status from qcp3
LEFT JOIN receptionform on receptionform.id = qcp3.reception_id
LEFT JOIN orders        on orders.id  = receptionform.order_id
LEFT JOIN clients       on clients.id = orders.client_id
LEFT JOIN countries     on countries.id = clients.country_id
LEFT JOIN crops         on crops.id   = receptionform.crop_id
LEFT JOIN users         on users.id   = qcp3.user_id
LEFT JOIN farmers       on farmers.id  = receptionform.supplier_id
WHERE qcp3.exportcompany_id = $3
ORDER BY qcp3.created_at DESC
LIMIT $1 OFFSET $2;



-- name: GlobalCompanyPendingQcp3s :many
SELECT crops.cropname,crops.id as crop_id,qcp3.batchno,receptionform.order_id,farmers.fmname,
qcp3.user_id,users.fullname,qcp3.created_at,qcp3.status,countries.countryname country, clients.companyname client_name,
orders.consignment_no,qcp3.id qcp3_id,receptionform.traceability_code, qcp3.status qcp3_status from qcp3
LEFT JOIN receptionform on receptionform.id = qcp3.reception_id
LEFT JOIN orders        on orders.id  = receptionform.order_id
LEFT JOIN clients       on clients.id = orders.client_id
LEFT JOIN countries     on countries.id = clients.country_id
LEFT JOIN crops         on crops.id   = receptionform.crop_id
LEFT JOIN users         on users.id   = qcp3.user_id
LEFT JOIN farmers       on farmers.id  = receptionform.supplier_id
ORDER BY qcp3.created_at DESC
LIMIT $1 OFFSET $2;



-- name: CompanyQcp3DateRanges :many
SELECT crops.cropname,crops.id as crop_id,qcp3.batchno,receptionform.order_id,farmers.fmname,
qcp3.user_id,users.fullname,qcp3.created_at,qcp3.status,countries.countryname country, clients.companyname client_name,
orders.consignment_no,qcp3.id qcp3_id,receptionform.traceability_code, qcp3.status qcp3_status from qcp3
LEFT JOIN receptionform on receptionform.id = qcp3.reception_id
LEFT JOIN orders        on orders.id  = receptionform.order_id
LEFT JOIN clients       on clients.id = orders.client_id
LEFT JOIN countries     on countries.id = clients.country_id
LEFT JOIN crops         on crops.id   = receptionform.crop_id
LEFT JOIN users         on users.id   = qcp3.user_id
LEFT JOIN farmers       on farmers.id  = receptionform.supplier_id
WHERE   qcp3.exportcompany_id = $5
AND     qcp3.created_at
BETWEEN TO_TIMESTAMP($3, 'YYYY-MM-DD HH24:MI:SS')
AND     TO_TIMESTAMP($4, 'YYYY-MM-DD HH24:MI:SS')
ORDER BY qcp3.created_at DESC
LIMIT   $1 OFFSET $2;


-- name: ListAllCompaniesQcp3Data :many
SELECT crops.cropname,crops.id as crop_id,
qcp3.batchno,receptionform.order_id,farmers.fmname,
qcp3.user_id,users.fullname,qcp3.created_at,qcp3.status,
countries.countryname country, clients.companyname client_name,
orders.consignment_no,qcp3.id qcp3_id,farmers.fmcode,
exportcompanies.expname,receptionform.traceability_code,
qcp3.status qcp3_status,qcp3.weightpassed from qcp3
LEFT JOIN receptionform on receptionform.id = qcp3.reception_id
LEFT JOIN orders        on orders.id  = receptionform.order_id
LEFT JOIN clients       on clients.id = orders.client_id
LEFT JOIN countries     on countries.id = clients.country_id
LEFT JOIN crops         on crops.id   = receptionform.crop_id
LEFT JOIN users         on users.id   = qcp3.user_id
LEFT JOIN farmers       on farmers.id           = receptionform.supplier_id
LEFT JOIN exportcompanies on exportcompanies.id = qcp3.exportcompany_id
ORDER BY qcp3.created_at DESC
LIMIT $1 OFFSET $2;

-- name: CompanyQcp3WithStatusDateRanges :many
SELECT crops.cropname,crops.id as crop_id,qcp3.batchno,receptionform.order_id,farmers.fmname,
qcp3.user_id,users.fullname,qcp3.created_at,qcp3.status,countries.countryname country, clients.companyname client_name,
orders.consignment_no,qcp3.id qcp3_id,receptionform.traceability_code, qcp3.status qcp3_status from qcp3
LEFT JOIN receptionform on receptionform.id = qcp3.reception_id
LEFT JOIN orders        on orders.id  = receptionform.order_id
LEFT JOIN clients       on clients.id = orders.client_id
LEFT JOIN countries     on countries.id = clients.country_id
LEFT JOIN crops         on crops.id   = receptionform.crop_id
LEFT JOIN users         on users.id   = qcp3.user_id
LEFT JOIN farmers       on farmers.id  = receptionform.supplier_id
WHERE   qcp3.exportcompany_id = $6
AND     qcp3.status  = $3
AND     qcp3.created_at
BETWEEN TO_TIMESTAMP($4, 'YYYY-MM-DD HH24:MI:SS')
AND     TO_TIMESTAMP($5, 'YYYY-MM-DD HH24:MI:SS')
ORDER BY qcp3.created_at DESC
LIMIT   $1 OFFSET $2;


-- name: CompanyQcp3WithStatus :many
SELECT crops.cropname,crops.id as crop_id,qcp3.batchno,receptionform.order_id,farmers.fmname,
qcp3.user_id,users.fullname,qcp3.created_at,qcp3.status,countries.countryname country, clients.companyname client_name,
orders.consignment_no,qcp3.id qcp3_id,receptionform.traceability_code, qcp3.status qcp3_status from qcp3
LEFT JOIN receptionform on receptionform.id = qcp3.reception_id
LEFT JOIN orders        on orders.id  = receptionform.order_id
LEFT JOIN clients       on clients.id = orders.client_id
LEFT JOIN countries     on countries.id = clients.country_id
LEFT JOIN crops         on crops.id   = receptionform.crop_id
LEFT JOIN users         on users.id   = qcp3.user_id
LEFT JOIN farmers       on farmers.id  = receptionform.supplier_id
WHERE   qcp3.exportcompany_id = $3
AND     qcp3.status  = ANY($4::text[])
ORDER BY qcp3.created_at DESC
LIMIT   $1 OFFSET $2;



-- name: ListPendingOrderQcp3s :many
SELECT crops.cropname,crops.id as crop_id,qcp3.batchno,receptionform.order_id,
qcp3.user_id,users.fullname,qcp3.created_at,qcp3.status,countries.countryname country, clients.companyname client_name,
orders.consignment_no,qcp3.id qcp3_id,receptionform.traceability_code, qcp3.status qcp3_status from qcp3
LEFT JOIN receptionform on receptionform.id = qcp3.reception_id
LEFT JOIN orders        on orders.id  = receptionform.order_id
LEFT JOIN clients       on clients.id = orders.client_id
LEFT JOIN countries     on countries.id = clients.country_id
LEFT JOIN crops         on crops.id   = receptionform.crop_id
LEFT JOIN users         on users.id   = qcp3.user_id
WHERE orders.id = $3  AND qcp3.status = $4
ORDER BY qcp3.created_at DESC
LIMIT $1 OFFSET $2;

-- name: CreateQcp3Attributes :one
INSERT INTO qcp3_attributes (
     qcp3_id,
     qualityattribute_id,
     percentage,
     other,
     qtyremoved,
     qtytaken
 ) VALUES (
     $1,
     $2,
     $3,
     $4,
     $5,
     $6
    )
RETURNING *;

 -- name: GetQcp3Attributes :one
SELECT * FROM qcp3_attributes
WHERE id = $1 LIMIT 1;

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

-- name: UpdateQcp3Attributes :one
UPDATE qcp3_attributes
  set
     qcp3_id = $2,
     qualityattribute_id  = $3,
     percentage = $4,
     other = $5,
     qtyremoved = $6,
     qtytaken = $7
WHERE id = $1
RETURNING *;

-- name: DeleteQcp3Attributes :exec
DELETE FROM qcp3_attributes WHERE qcp3_id= $1;

-- name: CreateQcp3Recommendation :one
INSERT INTO qcp3_recommendation (
    qcp3_id,
    status,
    netweightrejected,
    netweightpassed,
    other
 ) VALUES (
     $1,
     $2,
     $3,
     $4,
     $5
    )
RETURNING *;

-- name: GetQcp3Recommendation :one
SELECT * FROM qcp3_recommendation
WHERE qcp3_id = $1 LIMIT 1;

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

-- name: UpdateQcp3Recommendation :one
UPDATE qcp3_recommendation
  set
     qcp3_id = $2,
     status = $3,
     netweightrejected = $4,
     netweightpassed = $5,
     other  = $6
WHERE id = $1
RETURNING *;

-- name: DeleteQcp3Recommendation :exec
DELETE FROM qcp3_recommendation WHERE id= $1;

-- name: UpdateQcP3Status :exec
UPDATE qcp3
  set
    status = $2
WHERE id = $1
RETURNING *;

-- name: Qcp3Details :one
SELECT orders.consignment_no,receptionform.traceability_code,batchno,order_crops.weightreceived,receptionform.batches,farmers.fmname,farmers.fmcode,clients.companyname, countries.countryname,
crops.cropname, qcp3.created_at, qcp3.weightpassed,qcp3.status FROM qcp3
JOIN   receptionform on  receptionform.id = qcp3.reception_id
JOIN   farmers       on  farmers.id = receptionform.supplier_id
JOIN   crops         on  crops.id   = receptionform.crop_id
JOIN   orders        on  orders.id  = receptionform.order_id
JOIN   order_crops   on  order_crops.order_id = orders.id
JOIN   clients       on  clients.id = orders.client_id
JOIN   countries     on  countries.id = clients.country_id
WHERE  qcp3.id = $1;


-- name: AllQcP3Attributes :many
SELECT qualityattribute_id,qtyremoved qty_removed,crops.cropname,
qtytaken qty_taken,other,qcp3.batchno,qcp3.id,receptionform.traceability_code, qualityattributes.description FROM qcp3_attributes
LEFT JOIN  qcp3 on qcp3.id = qcp3_attributes.qcp3_id
LEFT JOIN  receptionform on receptionform.id = qcp3.reception_id
LEFT JOIN  qualityattributes on qualityattributes.id = qcp3_attributes.qualityattribute_id
LEFT JOIN  crops on crops.id = receptionform.crop_id
WHERE qcp3_attributes.qcp3_id = $1;

-- name: UpdateQcp3RecommendationStatus :one
UPDATE qcp3_recommendation
  set
     reasonforrejection  = $2
WHERE qcp3_id = $1
RETURNING *;

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

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


-- name: Qcp3OrderSum :one
SELECT COUNT(id)
FROM  qcp3
JOIN  receptionform on receptionform.id = qcp3.reception_id
WHERE qcp3.status IN ('ReSorting','Pending','AwaitingResort')
AND   receptionform.order_id = $1;

-- name: Qcp3PerOrder :many
SELECT qcp3.reception_id,qcp3.id as qcp3_id,crops.variant,crops.cropname,crops.cropname,crops.variant,
receptionform.traceability_code,qcp3_recommendation.netweightrejected,
qcp3_recommendation.netweightpassed,qcp3_recommendation.status
from qcp3
INNER  JOIN  qcp3_recommendation  on qcp3_recommendation.qcp3_id     = qcp3.id
INNER  JOIN  receptionform        on receptionform.id                = qcp3.reception_id
INNER  JOIN  crops                on crops.id                        = receptionform.crop_id
WHERE  receptionform.order_id = $1 ORDER BY qcp3.created_at DESC;


-- name: Qcp3Report :one
SELECT qcp3.exportcompany_id,
qualityattributes.description,
qcp3.created_at,exportcompanies.expname exportcompany_name,
users.fullname user_name, qcp3.reception_id,
qcp3.user_id,qcp3.weightpassed,
qcp3.status qcp3_status,clients.companyname,
countries.countryname,crops.cropname,
qcp3_attributes.qualityattribute_id,
qcp3_attributes.qtyRemoved,
qcp3_attributes.qtyTaken,
qcp3_attributes.other,
qcp3_attributes.percentage,
qcp3_attributes.qcp3_id,
qcp3_recommendation.status recommendation_status,
qcp3_recommendation.netweightrejected,
qcp3_recommendation.netweightpassed,
qcp3_recommendation.other,
exportcompanies.logo,receptionform.traceability_code as traceabilitycode FROM qcp3
LEFT JOIN receptionform       on receptionform.id        = qcp3.reception_id
LEFT JOIN crops               on crops.id                = receptionform.crop_id
LEFT JOIN orders              on orders.id               = receptionform.order_id
LEFT JOIN clients             on clients.id              = orders.client_id
LEFT JOIN countries           on countries.id            = clients.country_id
LEFT JOIN exportcompanies     on exportcompanies.id      = receptionform.exportcompany_id
LEFT JOIN users               on users.id                = receptionform.user_id
LEFT JOIN qcp3_attributes     on qcp3_attributes.qcp3_id = qcp3.id
LEFT JOIN qcp3_recommendation on qcp3_recommendation.qcp3_id = qcp3.id
LEFT JOIN qualityattributes   on qualityattributes.id  = qcp3_attributes.qualityattribute_id
WHERE qcp3.id = $1;

-- name: CheckQcp3ID :many
SELECT * FROM qcp3_attributes
where qcp3_id = $1;


-- name: Qcp3QualityAttributesPerOrder :many
SELECT qcp3_attributes.qtyremoved,qcp3_attributes.id, qcp3_attributes.qtytaken,
qcp3_attributes.percentage,qualityattributes.description FROM qcp3_attributes
INNER JOIN qcp3              on qcp3.id = qcp3_attributes.qcp3_id
INNER JOIN qualityattributes on qualityattributes.id = qcp3_attributes.qualityattribute_id
where qcp3.id = $1;

-- name: UpdateQcp3StatusResort :one
UPDATE qcp3
  set
      status  = $2
WHERE batchno = $1
RETURNING *;

-- name: ReturnAllDetailsPerQcp3 :one
SELECT qcp3.batchno,qcp3.reception_id,
qcp3.exportcompany_id,qcp3.user_id,
qcp3.weightpassed,qcp3.status,receptionform.order_id,
receptionform.traceability_code,receptionform.crop_id FROM qcp3
JOIN receptionform on receptionform.id = qcp3.reception_id
JOIN orders on orders.id = receptionform.order_id
WHERE qcp3.id = $1 LIMIT 1;

-- name: ListAllCompaniesQcp3s :many
SELECT orders.consignment_no,clients.companyname as client,
countries.countryname as country,receptionform.traceability_code,
orders.id as order_id,receptionform.batches,qcp3.batchno,
qcp3.weightpassed,farmers.fmcode,farmers.fmname,exportcompanies.expname,
crops.cropname,qcp3.created_at,qcp3.id as qcp3_id,
qcp3.status,qcp3.user_id,users.fullname FROM qcp3
JOIN  receptionform on receptionform.id = qcp3.reception_id
JOIN  farmers   on farmers.id  = receptionform.supplier_id
JOIN  crops     on crops.id    = receptionform.crop_id
JOIN  exportcompanies on exportcompanies.id = qcp3.exportcompany_id
JOIN  orders    on  orders.id   = receptionform.order_id
JOIN  clients   on  clients.id  = orders.client_id
JOIN  countries on  countries.id = clients.country_id
JOIN  users     on  users.id = qcp3.user_id
WHERE   qcp3.status = ANY($1::text[]) AND qcp3.created_at
BETWEEN TO_TIMESTAMP($2, 'YYYY-MM-DD HH24:MI:SS')
AND     TO_TIMESTAMP($3, 'YYYY-MM-DD HH24:MI:SS')
ORDER BY qcp3.created_at DESC
LIMIT   $4 OFFSET $5;


-- name: ListAllCompaniesQcp3WFs :many
SELECT orders.consignment_no,clients.companyname as client,
countries.countryname as country,receptionform.traceability_code,
orders.id as order_id,receptionform.batches,qcp3.batchno,
qcp3.weightpassed,farmers.fmcode,farmers.fmname,exportcompanies.expname,
crops.cropname,qcp3.created_at,qcp3.id as qcp3_id,
qcp3.status,qcp3.user_id,users.fullname FROM qcp3
JOIN  receptionform on receptionform.id = qcp3.reception_id
JOIN  farmers   on farmers.id  = receptionform.supplier_id
JOIN  crops     on crops.id    = receptionform.crop_id
JOIN  exportcompanies on exportcompanies.id = qcp3.exportcompany_id
JOIN  orders    on  orders.id   = receptionform.order_id
JOIN  clients   on  clients.id  = orders.client_id
JOIN  countries on  countries.id = clients.country_id
JOIN  users     on  users.id = qcp3.user_id
WHERE qcp3.status = ANY($1::text[])
ORDER BY qcp3.created_at DESC
LIMIT $2 OFFSET $3;


-- name: ListAllCompaniesQcp3Ds :many
SELECT orders.consignment_no,clients.companyname as client,
countries.countryname as country,receptionform.traceability_code,
orders.id as order_id,receptionform.batches,qcp3.batchno,
qcp3.weightpassed,farmers.fmcode,farmers.fmname,exportcompanies.expname,
crops.cropname,qcp3.created_at,qcp3.id as qcp3_id,
qcp3.status,qcp3.user_id,users.fullname FROM qcp3
JOIN  receptionform on receptionform.id = qcp3.reception_id
JOIN  farmers   on farmers.id  = receptionform.supplier_id
JOIN  crops     on crops.id    = receptionform.crop_id
JOIN  exportcompanies on exportcompanies.id = qcp3.exportcompany_id
JOIN  orders    on  orders.id   = receptionform.order_id
JOIN  clients   on  clients.id  = orders.client_id
JOIN  countries on  countries.id = clients.country_id
JOIN  users     on  users.id = qcp3.user_id
WHERE qcp3.status = ANY($1::text[])
ORDER BY qcp3.created_at DESC;


-- name: ListAllCompaniesQcp3DataWithDateStatus :many
SELECT crops.cropname,crops.id as crop_id,
qcp3.batchno,receptionform.order_id,farmers.fmname,
qcp3.user_id,users.fullname,qcp3.created_at,qcp3.status,
countries.countryname country, clients.companyname client_name,
orders.consignment_no,qcp3.id qcp3_id,farmers.fmcode,
exportcompanies.expname,receptionform.traceability_code,
qcp3.status qcp3_status,qcp3.weightpassed from qcp3
LEFT JOIN receptionform on receptionform.id = qcp3.reception_id
LEFT JOIN orders        on orders.id  = receptionform.order_id
LEFT JOIN clients       on clients.id = orders.client_id
LEFT JOIN countries     on countries.id = clients.country_id
LEFT JOIN crops         on crops.id   = receptionform.crop_id
LEFT JOIN users         on users.id   = qcp3.user_id
LEFT JOIN farmers       on farmers.id           = receptionform.supplier_id
LEFT JOIN exportcompanies on exportcompanies.id = qcp3.exportcompany_id
WHERE    qcp3.status = ANY($1::text[])
AND      qcp3.created_at
BETWEEN  TO_TIMESTAMP($2, 'YYYY-MM-DD HH24:MI:SS')
AND      TO_TIMESTAMP($3, 'YYYY-MM-DD HH24:MI:SS')
ORDER BY qcp3.created_at DESC
LIMIT    $4 OFFSET $5;


-- name: ListAllCompaniesQcp3DataWithDate :many
SELECT orders.consignment_no,clients.companyname as client,
countries.countryname as country,receptionform.traceability_code,
orders.id as order_id,receptionform.batches,qcp3.batchno,
qcp3.weightpassed,farmers.fmcode,farmers.fmname,exportcompanies.expname,
crops.cropname,qcp3.created_at,qcp3.id as qcp3_id,
qcp3.status,qcp3.user_id,users.fullname FROM qcp3
JOIN  receptionform on receptionform.id = qcp3.reception_id
JOIN  farmers   on farmers.id  = receptionform.supplier_id
JOIN  crops     on crops.id    = receptionform.crop_id
JOIN  exportcompanies on exportcompanies.id = qcp3.exportcompany_id
JOIN  orders    on  orders.id   = receptionform.order_id
JOIN  clients   on  clients.id  = orders.client_id
JOIN  countries on  countries.id = clients.country_id
JOIN  users     on  users.id = qcp3.user_id
WHERE    qcp3.created_at
BETWEEN  TO_TIMESTAMP($1, 'YYYY-MM-DD HH24:MI:SS')
AND      TO_TIMESTAMP($2, 'YYYY-MM-DD HH24:MI:SS')
ORDER BY qcp3.created_at DESC
LIMIT    $3 OFFSET $4;


-- name: ListAllCompaniesQcp3DataWithStatus :many
SELECT orders.consignment_no,clients.companyname as client,
countries.countryname as country,receptionform.traceability_code,
orders.id as order_id,receptionform.batches,qcp3.batchno,
qcp3.weightpassed,farmers.fmcode,farmers.fmname,exportcompanies.expname,
crops.cropname,qcp3.created_at,qcp3.id as qcp3_id,
qcp3.status,qcp3.user_id,users.fullname FROM qcp3
JOIN     receptionform on receptionform.id = qcp3.reception_id
JOIN     farmers   on farmers.id  = receptionform.supplier_id
JOIN     crops     on crops.id    = receptionform.crop_id
JOIN     exportcompanies on exportcompanies.id = qcp3.exportcompany_id
JOIN     orders    on  orders.id   = receptionform.order_id
JOIN     clients   on  clients.id  = orders.client_id
JOIN     countries on  countries.id = clients.country_id
JOIN     users     on  users.id = qcp3.user_id
WHERE    qcp3.status = ANY($1::text[])
ORDER BY qcp3.created_at DESC
LIMIT    $2 OFFSET $3;



-- name: Qcp3MarkAsDuplicate :exec
UPDATE qcp3
  set
    status = 'Duplicate'
WHERE id = $1
RETURNING *;
