-- name: CreateQcp1 :one
INSERT INTO qcp1 (
      exportcompany_id,
      receptionform_id,
      user_id,
      weightpassed,
      status
 ) VALUES (
     $1,
     $2,
     $3,
     $4,
     $5
    )
RETURNING *;

-- name: GetQcp1 :one
SELECT * FROM qcp1
WHERE id = $1 LIMIT 1;

-- name: ListQcp1s :many
SELECT * FROM qcp1
LIMIT $1
OFFSET $2;

-- name: ListPendingExporterQcp1s :many
SELECT orders.consignment_no,receptionform.batches,farmers.fmname,farmers.fmcode,
crops.cropname, qcp1.created_at FROM qcp1
JOIN   receptionform on receptionform.id = qcp1.receptionform_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
WHERE       qcp1.exportcompany_id   = $3
ORDER BY    qcp1.created_at DESC
LIMIT $1 OFFSET $2;

-- name: UpdateQcp1 :one
UPDATE qcp1
  set
      exportcompany_id = $5,
      receptionform_id = $4,
      user_id = $3,
      status = $2
WHERE id = $1
RETURNING *;

-- name: DeleteQcp1 :exec
DELETE FROM qcp1 WHERE id= $1;

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

-- name: Qcp1Details :one
SELECT orders.consignment_no consignment_no,
receptionform.netweight,receptionform.batches batches,receptionform.traceability_code,
farmers.fmname,farmers.fmcode,crops.cropname, qcp1.created_at,qcp1.status FROM qcp1
JOIN   receptionform on receptionform.id = qcp1.receptionform_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  qcp1.id = $1;

-- name: AllPendingQcp1s :many
SELECT orders.consignment_no,clients.companyname,countries.countryname,receptionform.traceability_code, receptionform.order_id,receptionform.batches,farmers.fmname,farmers.fmcode,
crops.cropname, qcp1.created_at, qcp1.id qcp1_id, qcp1.status, qcp1.user_id user_id ,users.fullname,receptionform.id, weightpassed FROM qcp1
LEFT JOIN   receptionform on receptionform.id = qcp1.receptionform_id
LEFT JOIN   farmers       on  farmers.id   = receptionform.supplier_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   users         on  users.id     = receptionform.user_id
WHERE    qcp1.exportcompany_id   = $1 AND  qcp1.status = $2
ORDER BY qcp1.created_at DESC;


-- name: CompanyQcp1s :many
SELECT orders.consignment_no,clients.companyname,countries.countryname,receptionform.traceability_code, receptionform.order_id,receptionform.batches,farmers.fmname,farmers.fmcode,
crops.cropname, qcp1.created_at, qcp1.id qcp1_id, qcp1.status, qcp1.user_id user_id ,users.fullname,receptionform.id, weightpassed FROM qcp1
LEFT JOIN   receptionform on receptionform.id = qcp1.receptionform_id
LEFT JOIN   farmers       on  farmers.id   = receptionform.supplier_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   users         on  users.id     = receptionform.user_id
WHERE       qcp1.exportcompany_id   = $1
ORDER BY    qcp1.created_at DESC LIMIT $2 OFFSET $3;


-- name: CompanyQcp1DateRanges :many
SELECT orders.consignment_no,clients.companyname,countries.countryname,receptionform.traceability_code, receptionform.order_id,receptionform.batches,farmers.fmname,farmers.fmcode,
crops.cropname, qcp1.created_at, qcp1.id qcp1_id, qcp1.status, qcp1.user_id user_id ,users.fullname,receptionform.id, weightpassed FROM qcp1
LEFT JOIN   receptionform on receptionform.id = qcp1.receptionform_id
LEFT JOIN   farmers       on  farmers.id   = receptionform.supplier_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   users         on  users.id     = receptionform.user_id
WHERE    qcp1.exportcompany_id = $3
AND      qcp1.created_at
BETWEEN  TO_TIMESTAMP($4, 'YYYY-MM-DD HH24:MI:SS')
AND      TO_TIMESTAMP($5, 'YYYY-MM-DD HH24:MI:SS')
ORDER BY qcp1.created_at DESC
LIMIT $1 OFFSET $2;



-- name: CompanyQcp1WithStatus :many
SELECT orders.consignment_no,clients.companyname,countries.countryname,receptionform.traceability_code, receptionform.order_id,receptionform.batches,farmers.fmname,farmers.fmcode,
crops.cropname,qcp1.created_at, qcp1.id qcp1_id, qcp1.status, qcp1.user_id user_id ,users.fullname,receptionform.id, weightpassed FROM qcp1
LEFT JOIN   receptionform on receptionform.id = qcp1.receptionform_id
LEFT JOIN   farmers       on  farmers.id   = receptionform.supplier_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   users         on  users.id     = receptionform.user_id
WHERE       qcp1.exportcompany_id = $2
AND         qcp1.status = ANY($1::text[])
ORDER BY    qcp1.created_at DESC
LIMIT $3 OFFSET $4;


-- name: CompanyQcp1SingleWithStatus :many
SELECT orders.consignment_no,clients.companyname,countries.countryname,receptionform.traceability_code, receptionform.order_id,receptionform.batches,farmers.fmname,farmers.fmcode,
crops.cropname,qcp1.created_at, qcp1.id qcp1_id, qcp1.status, qcp1.user_id user_id ,users.fullname,receptionform.id, weightpassed FROM qcp1
LEFT JOIN   receptionform on receptionform.id = qcp1.receptionform_id
LEFT JOIN   farmers       on  farmers.id   = receptionform.supplier_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   users         on  users.id     = receptionform.user_id
WHERE       qcp1.exportcompany_id = $2
AND         qcp1.status = $1
ORDER BY    qcp1.created_at DESC
LIMIT   $3 OFFSET $4;



-- name: CompanyQcp1DateRangesWithStatus :many
SELECT orders.consignment_no,clients.companyname,countries.countryname,receptionform.traceability_code, receptionform.order_id,receptionform.batches,farmers.fmname,farmers.fmcode,
crops.cropname, qcp1.created_at, qcp1.id qcp1_id, qcp1.status, qcp1.user_id user_id ,users.fullname,receptionform.id, weightpassed FROM qcp1
LEFT JOIN   receptionform on receptionform.id = qcp1.receptionform_id
LEFT JOIN   farmers       on  farmers.id   = receptionform.supplier_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   users         on  users.id     = receptionform.user_id
WHERE       qcp1.exportcompany_id = $3
AND         qcp1.status = $4
AND         qcp1.created_at
BETWEEN     TO_TIMESTAMP($5, 'YYYY-MM-DD HH24:MI:SS')
AND         TO_TIMESTAMP($6, 'YYYY-MM-DD HH24:MI:SS')
ORDER BY    qcp1.created_at DESC
LIMIT $1 OFFSET $2;



-- name: AllPendingOrderQcp1s :many
SELECT orders.consignment_no,clients.companyname,countries.countryname,receptionform.traceability_code, receptionform.order_id,receptionform.batches,farmers.fmname,farmers.fmcode,
crops.cropname, qcp1.created_at, qcp1.id qcp1_id, qcp1.status, qcp1.user_id user_id ,users.fullname,receptionform.id, weightpassed FROM qcp1
LEFT JOIN   receptionform on receptionform.id = qcp1.receptionform_id
LEFT JOIN   farmers       on  farmers.id   = receptionform.supplier_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   users         on  users.id     = receptionform.user_id
WHERE    orders.id   = $1 AND  qcp1.status = $2
ORDER BY qcp1.created_at DESC;


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


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

-- name: Qcp1Report :one
SELECT qcp1.exportcompany_id,
qualityattributes.description,
qcp1.created_at,exportcompanies.expname exportcompany_name,
users.fullname user_name,qcp1.receptionform_id,
qcp1.user_id,qcp1.weightpassed,qcp1.status qcp1_status,clients.companyname,countries.countryname,crops.cropname,
qcp1_attributes.qualityattribute_id,qcp1_attributes.qtyRemoved,
qcp1_attributes.qtyTaken,qcp1_attributes.other,
qcp1_attributes.percentage,
qcp1_attributes.qcp1_id,
qcp1_recommendation.status recommendation_status,
qcp1_recommendation.netweightrejected,
qcp1_recommendation.netweightpassed,
qcp1_recommendation.other,
exportcompanies.logo,
receptionform.traceability_code as traceabilitycode FROM qcp1
LEFT JOIN receptionform       on receptionform.id   = qcp1.receptionform_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 qcp1_attributes on qcp1_attributes.qcp1_id = qcp1.id
LEFT JOIN qcp1_recommendation on qcp1_recommendation.qcp1_id = qcp1.id
LEFT JOIN qualityattributes   on qualityattributes.id  = qcp1_attributes.qualityattribute_id
WHERE qcp1.id = $1;

-- name: Qcp1QualityAttributesPerOrder :many
SELECT qcp1_attributes.qtyremoved, qcp1_attributes.qtytaken,qcp1_attributes.id,
qcp1_attributes.percentage,qualityattributes.description FROM qcp1_attributes
INNER JOIN qcp1 on qcp1.id = qcp1_attributes.qcp1_id
INNER JOIN qualityattributes on qualityattributes.id = qcp1_attributes.qualityattribute_id
where qcp1.id = $1;

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


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


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

-- name: Qcp1PerOrder :many
SELECT qcp1.receptionform_id,qcp1.id as qcp1_id,crops.variant,crops.cropname,crops.cropname,crops.variant,
receptionform.traceability_code,qcp1_recommendation.netweightrejected,
qcp1_recommendation.netweightpassed,qcp1_recommendation.status
from qcp1
INNER  JOIN  qcp1_recommendation  on qcp1_recommendation.qcp1_id     = qcp1.id
INNER  JOIN  receptionform        on receptionform.id                = qcp1.receptionform_id
INNER  JOIN  crops                on crops.id                        = receptionform.crop_id
WHERE  receptionform.order_id = $1;


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



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


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


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




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