-- name: CreateTrackHygiene :one
INSERT INTO trackhygiene (
    type,
    requirement
 ) VALUES (
    $1, $2
 ) RETURNING *;

-- name: GetTrackHygiene :one
SELECT * FROM trackhygiene
WHERE id = $1 LIMIT 1;


-- name: TrackHygienes :many
SELECT * FROM trackhygiene
LIMIT  $1 OFFSET $2;

-- name: ListTrackHygienes :many
SELECT * FROM trackhygiene;

-- name: CreateTrackHygienesReport :one
INSERT INTO trackhygienereport (
    truckimage,
    consignment_no,
    vehicleregno
 ) VALUES (
    $1,
    $2,
    $3
 ) RETURNING *;


-- name: UpdateTruckHygiene :one
UPDATE trackhygienereport
  set 
  truckimage   = $2,
  vehicleregno = $3
WHERE consignment_no = $1
RETURNING *;


-- name: UpdateTruckHygienesReport :one
UPDATE trackhygienereport
  set 
    consignment_no = $2,
    date = $3,
    detergent = $4,
    refrigerationstatus = $5
WHERE id = $1
RETURNING *;


-- name: GetTruckHygieneDetailsByConsignment :one
SELECT trackhygienereport.truckimage,trackhygienereport.vehicleregno,
trackhygienereport.date,trackhygienereport.detergent,trackhygienereport.id as report_id,
trackhygienereport.refrigerationstatus,exportcompanies.expname,
exportcompanies.expregno,packhouses.phname,trackhygienereport.created_at as report_created_at,
users.signature,plannedconsignments.id as consignment__no,
exportcompanies.id as exporter_id FROM trackhygienereport
JOIN  plannedconsignments on plannedconsignments.id = trackhygienereport.consignment_no
JOIN  exportcompanies    on exportcompanies.id = plannedconsignments.exportcompany_id
JOIN  packhouse_exporter on packhouse_exporter.exportcompany_id = exportcompanies.id
JOIN  packhouses         on packhouses.id    = packhouse_exporter.packhouse_id
JOIN  users              on users.id         = plannedconsignments.user_id
WHERE trackhygienereport.consignment_no      = $1 LIMIT 1;

-- name: GetTruckHygieneByConsignment :one
SELECT EXISTS (
    SELECT 1 FROM trackhygienereport
    WHERE consignment_no = $1
);

-- name: CreateTrackHygienesCheckList :one
INSERT INTO trackhygienereport_checklist (
    trackhygienereport_id,
    trackhygiene_id
 ) VALUES (
    $1,
    $2
 ) RETURNING *;

-- name: UpdateCheckListStatus :one
UPDATE trackhygienereport
   set
    checklist_status = $2
   WHERE id = $1
 RETURNING *;

-- name: GetTrackHygienesCheckList :many
SELECT trackhygienereport_id,trackhygiene.requirement,trackhygiene.type,trackhygiene_id from trackhygienereport_checklist
JOIN   trackhygiene on trackhygiene.id = trackhygienereport_checklist.trackhygiene_id
WHERE  trackhygienereport_id = $1;

-- name: ListTruckHygienesReport :many
SELECT trackhygiene.type as type_track, trackhygiene.id as trackhygiene_id  from trackhygienereport_checklist
JOIN  trackhygiene on trackhygiene.id = trackhygienereport_checklist.trackhygiene_id
WHERE trackhygienereport_id = $1;


-- name: UpdateTrackHygienesCheckList :one
 UPDATE trackhygienereport_checklist
   set
    trackhygiene_id = $2
   WHERE trackhygienereport_id = $1
 RETURNING *;

-- name: DeleteTruckHygieneChecklist :exec
DELETE FROM trackhygienereport_checklist WHERE trackhygienereport_id= $1;

-- name: CreateInspectionReports :one
INSERT INTO inspectionreport (
    consignment_no,
    notes,
    instruction,
    user_id,
    exportcompany_id
 ) VALUES (
    $1, 
    $2, 
    $3, 
    $4, 
    $5
 ) RETURNING *;

-- name: GetInspectionReportDetailsByConsignment :one
SELECT EXISTS (
    SELECT 1 FROM inspectionreport
    WHERE consignment_no = $1
);

-- name: GetInspectionReportByConsignment :one
SELECT inspectionreport.consignment_no,inspectionreport.notes,
inspectionreport.instruction,inspectionreportrecommendation.consignment_status,inspectionreport.id,
inspectionreportrecommendation.comment,inspectionreportrecommendation.phytocertificate,inspectionreport.created_at,
users.fullname,users.signature,exportcompanies.expname,exportcompanies.logo,exportcompanies.id as exporter_id FROM inspectionreport
LEFT JOIN inspectionreportrecommendation on inspectionreportrecommendation.consignment_no = inspectionreport.consignment_no
JOIN  exportcompanies on exportcompanies.id = inspectionreport.exportcompany_id
JOIN  users           on users.id           = inspectionreport.user_id
WHERE inspectionreport.consignment_no = $1 
LIMIT 1;

-- name: CheckInspectionReportByConsignment :one
 SELECT EXISTS(
      SELECT 1 FROM inspectionreportrecommendation
      WHERE consignment_no = $1
 );


-- name: UpdateInspectionReport :one
 UPDATE inspectionreport
   set
    notes = $2,
    instruction = $3
   WHERE id = $1
 RETURNING *;

-- name: InspectionReportDetails :one
SELECT inspectionreport.consignment_no,inspectionreport.notes,
inspectionreport.instruction,inspectionreport.user_id,
inspectionreport.exportcompany_id,users.fullname,users.signature FROM inspectionreport
JOIN users on users.id = inspectionreport.user_id
WHERE consignment_no = $1;

-- name: CreateInspectionReportRecommendation :one
INSERT INTO inspectionreportrecommendation (
    consignment_no,
    consignment_status,
    comment,
    phytocertificate,
    user_id,
    exportcompany_id
 ) VALUES (
    $1, 
    $2, 
    $3, 
    $4, 
    $5,
    $6
 ) RETURNING *;


-- name: UpdateInspectionReportRecommendation :one
UPDATE inspectionreportrecommendation
  SET
    consignment_status = $4,
    comment = $3,
    phytocertificate = $2
  WHERE consignment_no = $1
  RETURNING *;

-- name: UpdateInspectionReportWithoutFileRecommendation :one
UPDATE inspectionreportrecommendation
  SET
    consignment_status = $3,
    comment = $2
  WHERE consignment_no = $1

  RETURNING *;


-- name: CreateInspectionRecommendation :one
INSERT INTO inspectionreportrecommendation (
    consignment_no,
    consignment_status,
    comment,
    user_id,
    exportcompany_id
 ) VALUES (
    $1, 
    $2, 
    $3, 
    $4, 
    $5
 ) RETURNING *;

-- name: GetInspectionRecommendationByConsignment :one
SELECT * FROM inspectionreportrecommendation
WHERE consignment_no = $1 LIMIT 1;


-- name: UpdateTruckHygienesReportByID :one
UPDATE trackhygienereport
  set 
    consignment_no = $2,
    date = $3,
    detergent = $4,
    refrigerationstatus = $5
WHERE id = $1
RETURNING *;


-- name: ListTrackHygieneTrucks :many
SELECT trackhygienereport.consignment_no,trackhygienereport.truckimage,trackhygienereport.id,
trackhygienereport.vehicleregno,plannedconsignments.user_id,trackhygienereport.created_at,
trackhygienereport.date,trackhygienereport.detergent,exportcompanies.id as exportcompany_id,
trackhygienereport.refrigerationstatus
from   trackhygienereport
JOIN   plannedconsignments on plannedconsignments.id = trackhygienereport.consignment_no
JOIN   exportcompanies     on exportcompanies.id = plannedconsignments.exportcompany_id
WHERE  plannedconsignments.id= $1;

-- name: GetTruckHygieneReportBased :one
SELECT * FROM trackhygienereport
WHERE vehicleregno= $1 AND 
consignment_no = $2 LIMIT 1;

-- name: DeleteTruckHygieneReport :exec
DELETE FROM trackhygienereport 
WHERE id= $1;
