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

-- name: GetQcp2 :one
SELECT * FROM qcp2
WHERE id = $1 LIMIT 1;

-- name: ListQcp2s :many
SELECT * FROM qcp2
WHERE status = "Pending" AND
exportcompany_id= $3
ORDER BY id LIMIT $1
OFFSET $2;

-- name: ListExporterPendingQcp2s :many
SELECT
  crops.cropname,
  qcp2.batchno,
  receptionform.order_id,
  farmers.fmname,
  qcp2.user_id,
  users.fullname,
  qcp2.created_at,
  qcp2.status,
  countries.countryname country,
  clients.companyname client_name,
  orders.consignment_no,
  qcp2.id qcp2_id,
  receptionform.traceability_code,
  qcp2.status qcp2_status
FROM
  qcp2
LEFT JOIN receptionform ON receptionform.id = qcp2.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 = qcp2.user_id
LEFT JOIN farmers ON farmers.id = receptionform.supplier_id
WHERE qcp2.exportcompany_id = $3
  AND     qcp2.status = ANY($4::text[])
  AND     qcp2.created_at
  BETWEEN TO_TIMESTAMP($5, 'YYYY-MM-DD HH24:MI:SS')
  AND     TO_TIMESTAMP($6, 'YYYY-MM-DD HH24:MI:SS')
  ORDER BY qcp2.created_at DESC
  LIMIT $1 OFFSET $2;

-- name: ListExporterPendingQcp2D :many
SELECT
  crops.cropname,
  qcp2.batchno,
  receptionform.order_id,
  farmers.fmname,
  qcp2.user_id,
  users.fullname,
  qcp2.created_at,
  qcp2.status,
  countries.countryname country,
  clients.companyname client_name,
  orders.consignment_no,
  qcp2.id qcp2_id,
  receptionform.traceability_code,
  qcp2.status qcp2_status
FROM
  qcp2
LEFT JOIN receptionform ON receptionform.id = qcp2.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 = qcp2.user_id
LEFT JOIN farmers ON farmers.id = receptionform.supplier_id
WHERE qcp2.exportcompany_id = $3
AND     qcp2.created_at
BETWEEN TO_TIMESTAMP($4, 'YYYY-MM-DD HH24:MI:SS')
AND     TO_TIMESTAMP($5, 'YYYY-MM-DD HH24:MI:SS')
ORDER BY qcp2.created_at DESC
LIMIT $1 OFFSET $2;

-- name: ListExporterPendingQcp2Ws :many
SELECT
  crops.cropname,
  qcp2.batchno,
  receptionform.order_id,
  farmers.fmname,
  qcp2.user_id,
  users.fullname,
  qcp2.created_at,
  qcp2.status,
  countries.countryname country,
  clients.companyname client_name,
  orders.consignment_no,
  qcp2.id qcp2_id,
  receptionform.traceability_code,
  qcp2.status qcp2_status
FROM
  qcp2
LEFT JOIN receptionform ON receptionform.id = qcp2.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 = qcp2.user_id
LEFT JOIN farmers ON farmers.id = receptionform.supplier_id
WHERE qcp2.exportcompany_id = $3
AND   qcp2.status = ANY($4::text[])
ORDER BY qcp2.created_at DESC
LIMIT $1 OFFSET $2;


-- name: CompanyQcp2SingleWithStatus :many
SELECT
  crops.cropname,
  qcp2.batchno,
  receptionform.order_id,
  farmers.fmname,
  qcp2.user_id,
  users.fullname,
  qcp2.created_at,
  qcp2.status,
  countries.countryname country,
  clients.companyname client_name,
  orders.consignment_no,
  qcp2.id qcp2_id,
  receptionform.traceability_code,
  qcp2.status qcp2_status
FROM
  qcp2
LEFT JOIN receptionform ON receptionform.id = qcp2.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 = qcp2.user_id
LEFT JOIN farmers ON farmers.id = receptionform.supplier_id
WHERE qcp2.exportcompany_id = $3
AND   qcp2.status = $4
ORDER BY qcp2.created_at DESC
LIMIT $1 OFFSET $2;

-- name: Companycp2s :many
SELECT crops.cropname,qcp2.batchno,receptionform.order_id,farmers.fmname,
qcp2.user_id,users.fullname,qcp2.created_at,qcp2.status,countries.countryname country, clients.companyname client_name,
orders.consignment_no,qcp2.id qcp2_id,receptionform.traceability_code, qcp2.status qcp2_status from qcp2
LEFT JOIN receptionform on receptionform.id = qcp2.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   = qcp2.user_id
LEFT JOIN farmers on farmers.id  = receptionform.supplier_id
WHERE qcp2.exportcompany_id     = $3
ORDER BY qcp2.created_at DESC
LIMIT $1 OFFSET $2;


-- name: ListExporterPendingOrderQcp2s :many
SELECT crops.cropname,qcp2.batchno,receptionform.order_id,
qcp2.user_id,users.fullname,qcp2.created_at,qcp2.status,countries.countryname country, clients.companyname client_name,
orders.consignment_no,qcp2.id qcp2_id,receptionform.traceability_code, qcp2.status qcp2_status from qcp2
LEFT JOIN receptionform on receptionform.id = qcp2.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   = qcp2.user_id
WHERE orders.id     = $3 AND qcp2.status = $4
ORDER BY qcp2.created_at DESC
LIMIT $1 OFFSET $2;

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


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

-- name: DeleteQcp2 :exec
DELETE FROM qcp2 WHERE id= $1;

-- name: CreateQcp2Recommendation :one
INSERT INTO qcp2_recommendation (
    qcp2_id,
    status,
    netweightrejected,
    netweightpassed,
    other
 ) VALUES (
     $1,
     $2,
     $3,
     $4,
     $5
    )
RETURNING *;

-- name: GetQcp2Recommendation :one
SELECT * FROM qcp2_recommendation
WHERE qcp2_id = $1 LIMIT 1;

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

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

-- name: DeleteQcp2Recommendation :exec
DELETE FROM qcp2_recommendation WHERE id= $1;

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


-- name: Qcp2Details :one
SELECT orders.consignment_no,receptionform.traceability_code,batchno,order_crops.weightreceived,qcp2.weightpassed, receptionform.batches,farmers.fmname,farmers.fmcode,
crops.cropname, qcp2.created_at,qcp2.status FROM qcp2
JOIN   receptionform on receptionform.id = qcp2.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
WHERE  qcp2.id = $1;

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

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


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

-- name: Qcp2Report :one
SELECT qcp2.exportcompany_id,
qualityattributes.description,
qcp2.created_at,exportcompanies.expname exportcompany_name,
users.fullname user_name, qcp2.reception_id,
qcp2.user_id,qcp2.weightpassed,
qcp2.status qcp2_status,clients.companyname,
countries.countryname,crops.cropname,
qcp2_attributes.qualityattribute_id,
qcp2_attributes.qtyRemoved,
qcp2_attributes.qtyTaken,
qcp2_attributes.other,
qcp2_attributes.percentage,
qcp2_attributes.qcp2_id,
qcp2_recommendation.status recommendation_status,
qcp2_recommendation.netweightrejected,
qcp2_recommendation.netweightpassed,
qcp2_recommendation.other,
exportcompanies.logo,receptionform.traceability_code as traceabilitycode FROM qcp2
LEFT JOIN receptionform       on receptionform.id        = qcp2.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 qcp2_attributes     on qcp2_attributes.qcp2_id = qcp2.id
LEFT JOIN qcp2_recommendation on qcp2_recommendation.qcp2_id = qcp2.id
LEFT JOIN qualityattributes   on qualityattributes.id  = qcp2_attributes.qualityattribute_id
WHERE qcp2.id = $1;

-- name: Qcp2QualityAttributesPerOrder :many
SELECT qcp2_attributes.qtyremoved,qcp2_attributes.id,qcp2_attributes.qtytaken,
qcp2_attributes.percentage,qualityattributes.description FROM qcp2_attributes
INNER JOIN qcp2 on qcp2.id  = qcp2_attributes.qcp2_id
INNER JOIN qualityattributes on qualityattributes.id = qcp2_attributes.qualityattribute_id
where qcp2.id = $1;

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



-- name: ListAllCompaniesQcp2Data :many
SELECT orders.consignment_no,clients.companyname as client,
countries.countryname as country,receptionform.traceability_code,
orders.id as order_id,receptionform.batches,qcp2.batchno,
qcp2.weightpassed,farmers.fmcode,farmers.fmname,
exportcompanies.expname,crops.cropname,qcp2.created_at,qcp2.id as qcp2_id,
qcp2.status,qcp2.user_id,users.fullname FROM qcp2
JOIN  receptionform on receptionform.id = qcp2.reception_id
JOIN  farmers   on farmers.id  = receptionform.supplier_id
JOIN  crops     on crops.id    = receptionform.crop_id
JOIN  exportcompanies on exportcompanies.id = qcp2.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 = qcp2.user_id
ORDER BY qcp2.created_at DESC
LIMIT  $1 OFFSET $2;



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


-- name: ListAllCompaniesQcp2WFs :many
SELECT orders.consignment_no,clients.companyname as client,
countries.countryname as country,receptionform.traceability_code,
orders.id as order_id,receptionform.batches,qcp2.batchno,
qcp2.weightpassed,farmers.fmcode,farmers.fmname,
exportcompanies.expname,crops.cropname,qcp2.created_at,qcp2.id as qcp2_id,
qcp2.status,qcp2.user_id,users.fullname FROM qcp2
JOIN  receptionform on receptionform.id = qcp2.reception_id
JOIN  farmers   on farmers.id  = receptionform.supplier_id
JOIN  crops     on crops.id    = receptionform.crop_id
JOIN  exportcompanies on exportcompanies.id = qcp2.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 = qcp2.user_id
WHERE qcp2.status = ANY($1::text[])
ORDER BY qcp2.created_at DESC
LIMIT $2 OFFSET $3;



-- name: ListAllCompaniesQcp2Ds :many
SELECT orders.consignment_no,clients.companyname as client,
countries.countryname as country,receptionform.traceability_code,
orders.id as order_id,receptionform.batches,qcp2.batchno,
qcp2.weightpassed,farmers.fmcode,farmers.fmname,
exportcompanies.expname,crops.cropname,qcp2.created_at,qcp2.id as qcp2_id,
qcp2.status,qcp2.user_id,users.fullname FROM qcp2
JOIN  receptionform on receptionform.id = qcp2.reception_id
JOIN  farmers   on farmers.id  = receptionform.supplier_id
JOIN  crops     on crops.id    = receptionform.crop_id
JOIN  exportcompanies on exportcompanies.id = qcp2.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 = qcp2.user_id
WHERE qcp2.status = $1
ORDER BY qcp2.created_at DESC;


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