-- name: CreateFarmer :one
INSERT INTO farmers (
    "fmcode",
    "fmid",
    "fmidtype",
    "fmname",
    "fmphone",
    "fmemail"
 ) VALUES (
     $1,
     $2,
     $3,
     $4,
     $5,
     $6
  )
RETURNING *;


-- name: UploadFarmerPhoto :one
UPDATE farmers
     SET
       fmphoto = $2
WHERE id = $1
RETURNING *;

-- name: GetFarmer :one
SELECT * FROM farmers
WHERE id = $1 LIMIT 1;

-- name: GetFarmerDetailsBasedOnReception :one
SELECT farmers.fmphone,farmers.fmname,farmer_farms.traceability_code,receptionform.netweight,packhouses.phname, crops.cropname FROM farmers
JOIN   receptionform on receptionform.supplier_id = farmers.id
JOIN   farmer_farms  on farmer_farms.farmer_id    = farmers.id
JOIN   packhouses    on packhouses.id             = receptionform.packhouse_id
JOIN   crops         on crops.id                  = receptionform.crop_id
WHERE  receptionform.id = $1;

-- name: ListFarmers :many
SELECT farmers.fmcode,farmers.id farmer_id,farmers.fmid,farmers.fmidtype,farmers.fmname,farmers.fmphone,farmers.created_at farmer_created_at, farmers.status,
farmers.fmemail from farmers
ORDER BY farmers.id
LIMIT $1
OFFSET $2;

-- name: ExporterFarmers :many
SELECT farmers.fmcode,farmers.id farmer_id,farmers.fmid,farmers.fmidtype,farmers.fmname,farmers.fmphone,farmers.created_at farmer_created_at, farmers.status,
farmers.fmemail,farmer_farms.traceability_code,farmer_farms.long, farmer_farms.lat,farmer_farms.acreage FROM farmers
INNER JOIN farmer_farms on farmer_farms.farmer_id = farmers.id
INNER JOIN farmer_exporter on farmer_exporter.farmer_id = farmers.id
WHERE farmer_exporter.exportcompany_id = $1
ORDER BY farmers.id;

-- name: FarmersForExporters :many
SELECT farmers.fmcode,farmers.id farmer_id,farmers.fmid,farmers.status farmer_status,farmers.fmidtype,farmers.fmname,farmers.fmphone,farmers.created_at farmer_created_at, farmers.status,
farmers.fmemail FROM farmers
INNER JOIN farmer_exporter on farmer_exporter.farmer_id = farmers.id
WHERE farmer_exporter.exportcompany_id = $3
ORDER BY farmers.id
LIMIT $1 OFFSET $2;

-- name: ListFarmersForExporters :many
SELECT farmers.fmcode,farmers.id farmer_id,farmers.fmid,farmers.status farmer_status,farmers.fmidtype,farmers.fmname,farmers.fmphone,farmers.created_at farmer_created_at, farmers.status,
farmers.fmemail FROM farmers
INNER JOIN farmer_exporter on farmer_exporter.farmer_id = farmers.id
WHERE farmer_exporter.exportcompany_id = $3
AND   farmers.status = $4
ORDER BY farmers.id DESC
LIMIT $1 OFFSET $2;


-- name: AllFarmersForExporters :many
SELECT farmers.fmcode,farmers.id
farmer_id,farmers.fmid,farmers.fmidtype,farmers.fmname,farmers.fmphone,farmers.created_at farmer_created_at, farmers.status,
farmers.fmemail FROM farmers
LEFT  JOIN farmer_exporter on farmer_exporter.farmer_id = farmers.id
LEFT  JOIN farmer_farms  on farmer_farms.farmer_id  = farmers.id
WHERE farmer_exporter.exportcompany_id = $1
AND   farmer_farms.crop_id = $2
ORDER BY farmers.id;



-- name: UpdateFarmer :one
UPDATE farmers
  set
    fmcode=$2,
    fmname=$3,
    fmphone=$4,
    fmemail=$5,
    fmid= $6,
    fmidtype = $7
WHERE id = $1
RETURNING *;


-- name: SearchFarmerName :many
SELECT * from farmers
WHERE LOWER(fmname) LIKE '%' || LOWER($1) || '%';

-- name: DeleteFarmer :exec
DELETE FROM farmers WHERE id= $1;

-- name: FarmerContacts :one
INSERT INTO farmer_contacts (
    "farmer_id",
    "contactname",
    "contactphone",
    "contactemail"
 ) VALUES (
     $1,
     $2,
     $3,
     $4
  )
RETURNING *;

-- name: GetFarmerContacts :many
SELECT * FROM farmer_contacts
WHERE farmer_id = $1 LIMIT 1;

-- name: DeleteFarmerContacts :exec
DELETE FROM farmer_contacts WHERE id= $1;

-- name: FarmerFarms :one
INSERT INTO farmer_farms (
    "farmer_id",
    "traceability_code",
    "district_id",
    "harvestbalance",
    "crop_id",
    "parish_id",
    "village_id",
    "subcounty_id",
    "acreage",
    "lat",
    "long"
 ) VALUES (
     $1,
     $2,
     $3,
     $4,
     $5,
     $6,
     $7,
     $8,
     $9,
     $10,
     $11
  )
RETURNING *;

-- name: GetFarmerFarms :many
SELECT farmer_farms.farmer_id,farmer_farms.traceability_code,crops.cropname,crops.variant,
crops.crophscode,crops.riskstatus,crops.cic_code,farmer_farms.plantingdate,
COALESCE(redflags.status) AS redflag_status,farmer_farms.id as farm_id FROM farmer_farms
INNER JOIN crops    on crops.id = farmer_farms.crop_id
LEFT  JOIN redflags on redflags.traceability_code = farmer_farms.traceability_code
WHERE farmer_farms.farmer_id = $1;

-- name: DeleteFarmerFarms :exec
DELETE FROM farmer_farms WHERE id= $1;

-- name: FarmerFarmsCount :one
SELECT farmer_farms.village_id, COUNT(farmer_farms.id)
FROM farmer_farms
WHERE farmer_farms.farmer_id = $1
GROUP BY farmer_farms.village_id;

-- name: FarmerCropsFarmsCount :one
SELECT farmer_farms.crop_id, COUNT(farmer_farms.id)
FROM farmer_farms
WHERE farmer_farms.farmer_id = $1
GROUP BY farmer_farms.crop_id;


-- name: GetFarmerIDByCode :one
SELECT id
FROM farmers
WHERE fmcode = $1
LIMIT 1;


-- name: FarmerProduce :one
INSERT INTO farmer_produce (
    "farmer_id",
    "crop_id",
    "plantingdate",
    "acreage"
 ) VALUES (
     $1,
     $2,
     $3,
     $4
  )
RETURNING *;

 -- name: GetFarmerProduce :many
SELECT * FROM farmer_produce
WHERE farmer_id = $1 LIMIT 1;

-- name: DeleteFarmerProduce :exec
DELETE FROM farmer_produce WHERE id= $1;

-- name: GetFarmsForFarmer :many
SELECT DISTINCT on (crops.id) crops.id as crop_id,farmer_farms.farmer_id,farmer_farms.traceability_code,districts.name as district_name,
parishes.parish as parish_name,villages.village as village_name,subcounties.name,COALESCE(redflags.status) AS redflag_status,
acreage,lat,long,crops.cropname,crops.variant,crops.crophscode,farmer_farms.id as farm_id,
crops.riskstatus,crops.cic_code,villages.id as village_id,subcounties.id as subcounty_id,
parishes.id as parishes_id,districts.id as district_id,farmer_farms.plantingdate,subcounties.name as subcountyname,
farmer_farms.harvestbalance FROM farmer_farms
JOIN  districts   on districts.id = farmer_farms.district_id
JOIN  subcounties on subcounties.district_id = districts.id
JOIN  parishes    on parishes.id  = farmer_farms.parish_id
JOIN  villages    on villages.id  = farmer_farms.village_id
JOIN  crops       on crops.id     = farmer_farms.crop_id
LEFT  JOIN  redflags    on redflags.traceability_code = farmer_farms.traceability_code
WHERE farmer_farms.farmer_id = $1;

-- name: GetContactsForFarmer :many
SELECT contactname,contactphone,contactemail
FROM farmer_contacts
WHERE farmer_id = $1;

-- name: GetProduceForFarmer :many
SELECT crops.cropname as crop_name,crops.id crop_id,
crophscode,riskstatus,cic_code,
plantingdate,acreage
FROM  farmer_produce
JOIN  crops on crops.id = farmer_produce.crop_id
WHERE farmer_id = $1;


-- name: UpdateFarmerFarmHarvest :one
UPDATE farmer_farms
  set
    harvestbalance = $3
WHERE  crop_id = $2
AND    farmer_id = $1
RETURNING *;


-- name: UpdateFarmerFarmHarvestBalance :one
UPDATE farmer_farms
  set
    harvestbalance = $2
WHERE traceability_code = $1
RETURNING *;


-- name: GetFarmerIDByTraceability :one
SELECT traceability_code,farmers.id farmers_id from farmer_farms
JOIN farmers on farmers.id = farmer_farms.farmer_id
WHERE traceability_code = $1;

-- name: AllFarmers :many
SELECT * FROM farmers;

-- name: GetFarmerBasedCrop :many
SELECT farmer_farms.farmer_id,farmer_farms.traceability_code,
farmers.fmcode,farmers.fmname, crops.cropname, crops.id ,crops.variant FROM farmer_farms
JOIN  farmers   on  farmers.id   = farmer_farms.farmer_id
JOIN  crops     on  crops.id     = farmer_farms.crop_id
WHERE farmer_farms.crop_id = $1;

-- name: AssignFarmerToExporter :one
INSERT INTO farmer_exporter (
    "farmer_id",
    "exportcompany_id"
 ) VALUES (
     $1,
     $2
  )
RETURNING *;

-- name: CheckFarmerExistanceExporter :one
SELECT EXISTS(
  SELECT 1 FROM farmer_exporter
  WHERE farmer_id = $1
  AND exportcompany_id = $2
);

-- name: FarmerExporterCount :one
SELECT COUNT(farmers.id) from farmers
LEFT JOIN  farmer_exporter  on farmer_exporter.farmer_id = farmers.id
LEFT JOIN  exportcompanies  on exportcompanies.id     = farmer_exporter.exportcompany_id
WHERE exportcompanies.id = $2
AND   farmers.status = $1;

-- name: GetLastID :one
SELECT id FROM farmers WHERE id = (SELECT MAX(id) FROM farmers);

-- name: FarmerCountBasedOne :one
SELECT COUNT(farmers.id) from farmers
WHERE farmers.status = $1;

-- name: FarmDeactivation :one
UPDATE farmer_farms
  set
    status = 'InActive'
WHERE id = $1
RETURNING *;


-- name: FarmerCount :one
SELECT COUNT(id) from farmers;

-- name: DeleteFarmerFarmByID :exec
DELETE FROM farmer_farms where id = $1;

-- name: DetachFarmerFromExporter :exec
DELETE FROM farmer_exporter where
farmer_id = $1 AND
exportcompany_id= $2;


-- name: ExportFarmersWithFarms :many
SELECT farmers.id as farmer_id,farmers.fmcode,farmers.fmid,
farmers.fmidtype,farmers.fmname,farmers.fmphone,
farmers.fmemail,farmer_farms.traceability_code,farmer_farms.acreage,
farmer_farms.lat,farmer_farms.long,crops.cropname,crops.crophscode,
crops.riskstatus,crops.cic_code from farmer_farms
INNER JOIN farmers on farmer_farms.farmer_id = farmers.id
INNER JOIN crops  on crops.id = farmer_farms.crop_id;

-- name: ChangeFarmerStatus :one
UPDATE farmers
  set
    status = $2
WHERE id = $1
RETURNING *;


-- name: UpdateSingleFarmerFarms :one
UPDATE farmer_farms
    set
    traceability_code = $2,
    district_id = $3,
    crop_id = $4,
    parish_id = $5,
    village_id = $6,
    subcounty_id = $7,
    acreage = $8,
    lat = $9,
    long = $10
    WHERE id = $1
RETURNING *;


-- name: FetchTraceabilityCodes :many
SELECT farmer_farms.traceability_code,redflags.status,
farmer_farms.acreage,farmer_farms.harvestbalance,
crops.cropname,farmers.fmname
from farmer_farms
LEFT JOIN crops    on crops.id          = farmer_farms.crop_id
LEFT JOIN redflags on redflags.traceability_code = farmer_farms.traceability_code
LEFT JOIN farmers  on farmers.id        = farmer_farms.farmer_id
LIMIT $1 OFFSET $2;

-- name: SearchTraceabilityCodes :many
SELECT
 farmer_farms.traceability_code,
 redflags.status,
 farmer_farms.acreage,
 farmer_farms.harvestbalance,
 crops.cropname,
 farmers.fmname
FROM
 farmer_farms
LEFT JOIN
 crops ON crops.id = farmer_farms.crop_id
LEFT JOIN
 redflags ON redflags.traceability_code = farmer_farms.traceability_code
LEFT JOIN
 farmers ON farmers.id = farmer_farms.farmer_id
WHERE
 farmer_farms.traceability_code LIKE '%' || $1 || '%';




-- name: CountTraceabilityCodes :one
SELECT COUNT(farmer_farms.traceability_code) AS traceability_code_count
FROM farmer_farms
JOIN  redflags ON redflags.traceability_code = farmer_farms.traceability_code
WHERE redflags.status IN ('RFapproved', 'RFpending');


-- name: CountFarmerFarms :one
SELECT COUNT(*) from farmer_farms;
