-- name: CreateRedFlag :one
INSERT INTO redflags (
      crop_id,
      farmer_id,
      reason,
      user_id,
      stage,
      traceability_code,
      isactive,
      status
 ) VALUES (
    $1, $2, $3, $4, $5, $6, $7, $8) 
RETURNING *;

-- name: FetchRedFlags :many
SELECT redflags.id,redflags.reason,redflags.stage,redflags.created_at,redflags.traceability_code,redflags.status,
crops.cropname,farmers.fmname,users.fullname,redflags.isactive,redflags.status from redflags
JOIN     crops   on crops.id   = redflags.crop_id
JOIN     farmers on farmers.id = redflags.farmer_id
JOIN     users   on users.id   = redflags.user_id
Where    redflags.status = $3
ORDER BY redflags.created_at 
DESC LIMIT $1 OFFSET $2;


-- name: UpdateRedFlag :one
UPDATE redflags
  set 
    reason = $3,
    status = $2
WHERE id = $1
RETURNING *;

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

-- name: GetPendingRedflagCounts :one
Select count(id) from redflags
Where status = 'RFpending';

-- name: GetApprovedRedflagCounts :one
Select count(id) from redflags
Where status = 'RFapproved';

-- name: GetDeniedRedflagCounts :one
Select count(id) from redflags
Where status = 'RFdenied';





 