-- name: CreateQcp5InspectionReports :one
INSERT INTO qcp5inspectionreport (
    consignment_no,
    notes,
    user_id,
    exportcompany_id
 ) VALUES (
    $1, 
    $2, 
    $3, 
    $4
 ) RETURNING *;

-- name: GetQcp5InspectionReportDetailsByConsignment :one
SELECT EXISTS (
    SELECT 1 FROM qcp5inspectionreport
    WHERE consignment_no = $1
);

-- name: GetQcp5InspectionReportByConsignment :one
SELECT qcp5inspectionreport.consignment_no,qcp5inspectionreport.notes,
qcp5inspectionreport.notes,qcp5inspectionreportrecommendation.consignment_status,qcp5inspectionreport.id,
qcp5inspectionreportrecommendation.instructions,qcp5inspectionreport.created_at,
users.fullname,users.signature,exportcompanies.expname,exportcompanies.logo,exportcompanies.id as exporter_id FROM qcp5inspectionreport
LEFT JOIN qcp5inspectionreportrecommendation on qcp5inspectionreportrecommendation.consignment_no = qcp5inspectionreport.consignment_no
JOIN  exportcompanies on exportcompanies.id = qcp5inspectionreport.exportcompany_id
JOIN  users           on users.id           = qcp5inspectionreport.user_id
WHERE qcp5inspectionreport.consignment_no = $1 
LIMIT 1;

-- name: CheckQcp5InspectionReportByConsignment :one
 SELECT EXISTS(
      SELECT 1 FROM qcp5inspectionreportrecommendation
      WHERE consignment_no = $1
 );


-- name: UpdateQcp5InspectionReport :one
 UPDATE qcp5inspectionreport
   set
    notes = $2
   WHERE id = $1
 RETURNING *;

-- name: CreateQcp5InspectionReportRecommendation :one
INSERT INTO qcp5inspectionreportrecommendation (
    consignment_no,
    consignment_status,
    target,
    documentdate,
    documentnumber,
    instructions,
    rejectionreason,
    rejectionweight,
    passedweight,
    user_id,
    exportcompany_id
 ) VALUES (
    $1, 
    $2, 
    $3, 
    $4, 
    $5,
    $6,
    $7,
    $8,
    $9,
    $10,
    $11
 ) RETURNING *;

 -- name: GetQcp5InspectionRecommendationByConsignment :one
SELECT * FROM Qcp5inspectionreportrecommendation
WHERE consignment_no = $1 LIMIT 1;
