-- name: CreateCrop :one
INSERT INTO crops (
    cropname,       
    variant,       
    crophscode,
    riskstatus,
    cic_code
) VALUES (
  $1, 
  $2, 
  $3, 
  $4, 
  $5
) 
RETURNING *;

-- name: GetCrop :one
SELECT * FROM crops
WHERE id = $1 LIMIT 1;

-- name: ListCrops :many
SELECT * FROM crops
ORDER BY created_at DESC LIMIT $1
OFFSET $2;

-- name: AllCrops :many
SELECT * FROM crops;

-- name: UpdateCrop :one
UPDATE crops
  set 
    cropname=$2,              
    crophscode=$3,
    cic_code= $4,
    riskstatus=$5,
    variant = $6
WHERE id = $1
RETURNING *;


-- name: DeleteCrop :exec
DELETE FROM crops WHERE id= $1;

-- name: CropsSearchName :many
SELECT *
FROM  crops
WHERE LOWER(cropname) LIKE '%' || LOWER($1) || '%';

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

-- name: GetGlobalCropCount :one
SELECT COUNT(id) FROM crops 
WHERE status = $1;


-- name: GetCropByCICode :one
SELECT id FROM crops 
WHERE cic_code = $1
LIMIT 1;


-- name: TopCompaniesExporting :many
SELECT 
    ec.expname,
    SUM(oc.weight) AS total_weight_requested,
    SUM(oc.weightreceived) AS total_weight_exported
FROM 
    orders o
JOIN 
    order_crops oc ON o.id = oc.order_id
JOIN 
    exportcompanies ec ON o.exportcompany_id = ec.id
WHERE 
    o.status IN ('Completed', 'UnderInspection')
GROUP BY 
    ec.expname
ORDER BY 
    total_weight_exported DESC LIMIT 5;


-- name: TopCompaniesWithTop3Crops :many
WITH company_crop_totals AS (
    SELECT
        ec.id AS company_id,
        ec.expname,
        c.cropname,
        SUM(oc.weightreceived) AS total_crop_weight,
        ROW_NUMBER() OVER (
            PARTITION BY ec.id
            ORDER BY SUM(oc.weightreceived) DESC
        ) AS rn
    FROM 
        orders o
    JOIN 
        order_crops oc ON o.id = oc.order_id
    JOIN 
        crops c ON oc.crop_id = c.id
    JOIN 
        exportcompanies ec ON o.exportcompany_id = ec.id
    WHERE 
        o.status IN ('Completed', 'UnderInspection')
        AND oc.weightreceived IS NOT NULL
    GROUP BY 
        ec.id, ec.expname, c.cropname
)
SELECT 
    expname,
    cropname AS top_exported_crop,
    total_crop_weight
FROM 
    company_crop_totals
WHERE 
    rn <= 3
ORDER BY 
    expname, total_crop_weight DESC;
