-- name: CreateReceptionForm :one
INSERT INTO receptionform (
    supplier_id,
    traceability_code,
    order_id,
    crop_id,
    batches,
    netweight,
    status,
    phytostatus,
    modeoftransport_id,
    quantityrejected,
    previousharvestbalance,
    finalharvestbalance,
    driversname,
    carnumberplate,
    deliverytime,
    packhouse_id,
    exportcompany_id,
    score,
    user_id
 ) VALUES (
     $1, $2, $3,
     $4, $5, $6,
     $7, $8, $9,
     $10,$11,$12,
     $13,$14,$15,
     $16,$17,$18,
     $19
    )
RETURNING *;

-- name: GetReceptionForm :one
SELECT * FROM receptionform
WHERE id = $1 LIMIT 1;

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

-- name: UpdateReceptionForm :one
UPDATE receptionform
  set
    supplier_id=$2,
    traceability_code=$3,
    order_id=$4,
    crop_id=$5,
    batches=$6,
    netWeight=$7,
    phytostatus=$8,
    modeoftransport_id=$9,
    driversname= $10,
    carnumberplate= $11,
    deliverytime= $12,
    score= $13,
    quantityrejected=$14,
    previousharvestbalance= $15,
    finalharvestbalance= $16,
    user_id= $17,
    packhouse_id = $18,
    exportcompany_id = $19
WHERE id = $1
RETURNING *;

-- name: DeleteReceptionForm :exec
DELETE FROM receptionform WHERE id= $1;

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

-- name: ReturnOrderIDForReception :one
SELECT order_id,traceability_code,crop_id FROM receptionform WHERE id= $1;

-- name: ReturnProductIDForReception :one
SELECT crop_id FROM receptionform WHERE id= $1;

-- name: ListExporterReceptionForms :many
SELECT receptionform.supplier_id,receptionform.traceability_code,farmers.fmname,
receptionform.order_id,receptionform.crop_id,crops.cropname,receptionform.batches,
receptionform.netweight,receptionform.status,receptionform.modeoftransport_id,
receptionform.created_at,receptionform.quantityrejected,receptionform.id receptionform_id,
clients.companyname,countries.countryname as destination,receptionform.previousharvestbalance,
receptionform.finalharvestbalance,receptionform.driversname,receptionform.carnumberplate,
receptionform.deliverytime,receptionform.packhouse_id,receptionform.exportcompany_id,
receptionform.score,receptionform.user_id FROM receptionform
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 farmers   on   farmers.id   = receptionform.supplier_id
WHERE receptionform.exportcompany_id = $1
AND   receptionform.status = $4
ORDER BY receptionform.created_at DESC
LIMIT $2 OFFSET $3;

-- name: GetReceptionFormDetails :one
 SELECT receptionform.supplier_id,receptionform.traceability_code,receptionform.order_id,
 receptionform.crop_id,receptionform.batches,receptionform.netweight,receptionform.status,
 receptionform.phytostatus,receptionform.modeoftransport_id,receptionform.quantityrejected,
 receptionform.previousharvestbalance,receptionform.finalharvestbalance,receptionform.driversname,
 receptionform.carnumberplate,receptionform.deliverytime,receptionform.packhouse_id,
 receptionform.exportcompany_id,receptionform.score,receptionform.user_id,
 reception_documents.document,reception_attributes.qualityattribute_id,
 reception_attributes.qtyremoved,reception_attributes.qtytaken,reception_attributes.others,reception_attributes.percentage,
 qualityattributes.description,farmers.fmname,
 crops.cropname,exportcompanies.expname FROM receptionform
 LEFT JOIN reception_documents  on reception_documents.receptionform_id  = receptionform.id
 LEFT JOIN reception_attributes on reception_attributes.receptionform_id = receptionform.id
 LEFT JOIN exportcompanies      on exportcompanies.id    = receptionform.exportcompany_id
 LEFT JOIN users                on users.id              = receptionform.user_id
 LEFT JOIN farmers              on farmers.id            = receptionform.supplier_id
 LEFT JOIN crops                on crops.id              = receptionform.crop_id
 LEFT JOIN user_exporter        on user_exporter.user_id = users.id
 LEFT JOIN qualityattributes    on qualityattributes.id = reception_attributes.qualityattribute_id
 WHERE users.user_type = 'EXPORTER' AND receptionform.id = $1;


-- name: GetReceptionFormReport :one
 SELECT farmers.fmname,farmers.fmphone,users.fullname,countries.countryname,packhouses.phname as packhouse_name,users.signature as qc_signature,exportcompanies.expname, receptionform.traceability_code,receptionform.order_id,
 clients.companyname,crops.cropname,receptionform.batches,receptionform.netweight,receptionform.status,
 receptionform.phytostatus,receptionform.modeoftransport_id,receptionform.quantityrejected,
 receptionform.previousharvestbalance,receptionform.finalharvestbalance,receptionform.driversname,
 receptionform.carnumberplate,receptionform.deliverytime,receptionform.packhouse_id,
 receptionform.exportcompany_id,modesoftransport.motdescription,receptionform.score,receptionform.created_at,
 exportcompanies.logo FROM receptionform
 LEFT JOIN exportcompanies          on exportcompanies.id         = receptionform.exportcompany_id
 LEFT JOIN users                    on users.id                   = receptionform.user_id
 LEFT JOIN orders                   on orders.id                  = receptionform.order_id
 LEFT JOIN clients                  on clients.id                 = orders.client_id
 LEFT JOIN crops                    on crops.id                   = receptionform.crop_id
 LEFT JOIN packhouses               on packhouses.id              = receptionform.packhouse_id
 LEFT JOIN farmers                  on farmers.id                 = receptionform.supplier_id
 LEFT JOIN countries                on countries.id               = clients.country_id
 LEFT JOIN modesoftransport         on modesoftransport.id        = receptionform.modeoftransport_id
 WHERE receptionform.id     = $1;

-- name: GetExporterStampAndStamp :one
SELECT users.signature, users.stamp FROM users
INNER  JOIN user_exporter on user_exporter.user_id = users.id
WHERE  users.user_type = 'EXPORTER'
AND    user_exporter.exportcompany_id = $1;


-- name: GetReceptionFormsPerOrder :many
SELECT receptionform.traceability_code,receptionform.batches,receptionform.id,
receptionform.netweight,receptionform.phytostatus,
receptionform.status,receptionform.quantityrejected,
receptionform.previousharvestbalance,
receptionform.finalharvestbalance,receptionform.driversname,
receptionform.carnumberplate,receptionform.deliverytime,
receptionform.score,receptionform.created_at,packhouses.phname,
exportcompanies.expname,users.fullname,crops.cropname,crops.variant,
farmers.fmname,modesoftransport.motdescription
from  receptionform
JOIN  packhouses       on packhouses.id           = receptionform.packhouse_id
JOIN  users            on users.id                = receptionform.user_id
JOIN  exportcompanies  on exportcompanies.id      = receptionform.exportcompany_id
JOIN  modesoftransport on modesoftransport.id     = receptionform.modeoftransport_id
JOIN  crops            on crops.id                = receptionform.crop_id
JOIN  farmers          on farmers.id              = receptionform.supplier_id
where receptionform.order_id = $1;

-- name: GetReceptionAttributesPerReception :many
SELECT reception_attributes.qtyremoved,reception_attributes.qtytaken,qualityattributes.description,
reception_attributes.percentage,reception_attributes.created_at from reception_attributes
JOIN  qualityattributes on qualityattributes.id = reception_attributes.qualityattribute_id
where reception_attributes.receptionform_id = $1;

-- name: GetReceptionDocumentsPerReception :many
SELECT reception_documents.receptionform_id, reception_documents.created_at,documents.description,reception_documents.document from reception_documents
JOIN   documents on documents.id = reception_documents.document_id
where  reception_documents.receptionform_id = $1;

-- name: PendingReceptionFormsCount :one
SELECT COUNT(id) from receptionform
where status = 'Pending' AND exportcompany_id = $1;

-- name: SubmittedReceptionFormsCount :one
SELECT COUNT(id) from receptionform
where status = 'Submitted' AND exportcompany_id = $1;

-- name: RejectedReceptionFormsCount :one
SELECT COUNT(id) from receptionform
where status = 'Rejected'  AND exportcompany_id = $1;
