-- name: CreateOrder :one
INSERT INTO orders (           
     client_id,
     user_id,
     exportcompany_id
 ) VALUES (
    $1, 
    $2, 
    $3
) 
RETURNING *;

-- name: GetOrderDetails :many
SELECT orders.id order_id,orders.created_at created_at,crops.id crop_id,countries.id country_id,
clients.companyname,countries.countryname,crops.cropname,crops.variant,crops.crophscode,
crops.riskstatus,order_crops.weightreceived,order_crops.weight,order_crops.status,
crops.cic_code FROM orders
JOIN   clients on clients.id = orders.client_id
JOIN   countries  on countries.id = clients.country_id
JOIN   order_crops on order_crops.order_id = orders.id
JOIN   crops on crops.id = order_crops.crop_id 
WHERE  orders.id = $1 
AND    order_crops.status = 'Active';

-- name: GetOrderDetailCrops :many
SELECT orders.id order_id,orders.created_at created_at,crops.id crop_id,countries.id country_id,
clients.companyname,countries.countryname,crops.cropname,crops.variant,crops.crophscode,
crops.riskstatus,order_crops.weightreceived,order_crops.weight,order_crops.status,
crops.cic_code FROM orders
JOIN   clients on clients.id = orders.client_id
JOIN   countries  on countries.id = clients.country_id
JOIN   order_crops on order_crops.order_id = orders.id
JOIN   crops on crops.id = order_crops.crop_id 
WHERE  orders.id = $1 AND order_crops.status='Active';


-- name: GetOrderDetailsNotRecieved :many
SELECT orders.id order_id,orders.created_at created_at,crops.id crop_id,countries.id country_id,
clients.companyname,countries.countryname,crops.cropname,crops.variant,crops.crophscode,
crops.riskstatus,order_crops.weightreceived,order_crops.weight,order_crops.status,
crops.cic_code FROM orders
JOIN   clients on clients.id = orders.client_id
JOIN   countries  on countries.id = clients.country_id
JOIN   order_crops on order_crops.order_id = orders.id
JOIN   crops on crops.id = order_crops.crop_id 
WHERE  orders.id = $1 
AND    order_crops.status = 'NotRecieved';


-- name: GetOrder :one
 Select clients.companyname,clients.id as client_id,orders.id as order_id,clients.contactemail,clients.contactphone,
 orders.created_at,orders.user_id as qualitycontroller_id,exportcompanies.expname,users.fullname,
 countries.countryname,exportcompanies.id as exportcompany_id, orders.status,orders.consignment_no from orders
 JOIN clients on clients.id = orders.client_id
 JOIN exportcompanies on exportcompanies.id = orders.exportcompany_id
 JOIN countries on countries.id  = clients.country_id
 JOIN users     on users.id      = orders.user_id
 WHERE orders.id = $1;

-- name: ListOrders :many
SELECT companyname,contactname,countryname,
orders.created_at,orders.id order_id, orders.status, 
orders.user_id, orders.consignment_no FROM orders 
JOIN   clients     on clients.id    = orders.client_id
JOIN   countries   on countries.id  = clients.country_id
WHERE  orders.exportcompany_id = $3 
AND    orders.status = $4 
ORDER BY orders.created_at DESC
LIMIT  $1
OFFSET $2;

-- name: CropsOnOrders :many
SELECT DISTINCT crops.cropname,crops.id, crops.variant ,order_crops.weight, order_crops.weightreceived FROM order_crops
JOIN   crops on crops.id = order_crops.crop_id
WHERE  order_crops.order_id = $1;

-- name: UpdateOrder :one
UPDATE orders
  set 
     client_id = $2
WHERE id = $1
RETURNING *;

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

-- name: UpdateOrderStatusByConsignment :one
UPDATE orders
  set 
     status = $2
WHERE consignment_no = $1
RETURNING *;

-- name: UpdateOrderConsignment :one
UPDATE orders
  set 
    consignment_no = COALESCE(sqlc.narg(consignment_no), consignment_no)
WHERE id = $1
RETURNING *;

-- name: DeleteOrder :exec
DELETE FROM orders WHERE id= $1;

-- name: CheckConsignmentExist :one
 Select consignment_no from orders WHERE id = $1;

-- name: ReturnOrderByExportCompany :many
SELECT orders.id order_id,orders.consignment_no,orders.client_id client_id,orders.status,
orders.created_at,clients.companyname,countries.countryname,countries.id country_id,orders.exportcompany_id 
FROM  orders 
JOIN  clients   ON clients.id   = orders.client_id  
JOIN  countries ON countries.id = clients.country_id
WHERE orders.exportcompany_id= $1 AND orders.status = $2
ORDER BY orders.id desc;


-- name: GetOrdersWithConsignment :many
Select orders.id order_id, orders.client_id,users.fullname as qc,
orders.exportcompany_id, countries.countryname,
countries.id country_id,orders.status, 
orders.created_at,COALESCE(packinglist.id, 0) AS packinglist_id,
clients.companyname from orders  
LEFT JOIN    packinglist            on packinglist.order_id                            = orders.id
LEFT JOIN    clients                on clients.id                                      = orders.client_id
LEFT JOIN    countries              on countries.id                                    = clients.country_id
LEFT JOIN    users                  on users.id                                        = orders.user_id
WHERE   orders.consignment_no  = $1;

-- -- name: GetOrderProductRegime :many
-- Select crops.cropname,receptionform.traceability_code,
-- qcp2_recommendation.status qcp2recstatus,
-- qcp2_recommendation.netweightrejected,qcp2_recommendation.netweightpassed,
-- qcp1_recommendation.status qcp1recstatus,qcp1_recommendation.netweightrejected,
-- qcp1_recommendation.netweightpassed,qcp1_attributes.qtyRemoved,
-- qcp1_attributes.qtyTaken from receptionform
-- LEFT JOIN qcp1 on qcp1.receptionform_id = receptionform.id
-- LEFT JOIN qcp1_attributes on qcp1.qcp1_id = qcp1.id
-- LEFT JOIN qcp1_recommendation ON qcp1_recommendation.qcp1_id = qcp1.id
-- LEFT JOIN qcp2 on qcp2.reception_id   = receptionform.id
-- LEFT JOIN qcp2_attributes ON qcp2_attributes.qcp2_id = qcp2.id
-- LEFT JOIN qcp2_recommendation ON qcp2_recommendation.qcp2_id = qcp2.id


-- name: GetConsignmentWithOrderID :one
 Select * from orders WHERE id = $1;

-- name: OrderStatsActive :one
  Select COUNT(id) from orders
  WHERE exportcompany_id = $1 
  AND   status in ('Active','UnderInspection');

-- name: OrderStatsCompleted :one
Select COUNT(id) from orders
WHERE exportcompany_id = $1 
AND   status = 'Completed';

-- name: OrderStatsIntercepted :one
Select COUNT(id) from orders
WHERE exportcompany_id = $1
AND   status in ('Intercepted','Rejected');

-- name: ReturnOrderByConsignmentNo :many
Select orders.id,orders.created_at,orders.status,clients.companyname,
countries.countryname from orders 
JOIN  clients   on clients.id   = orders.client_id
JOIN  countries on countries.id = clients.country_id
WHERE consignment_no = $1;

-- name: OrderStatsCancelled :one
Select COUNT(id) from orders
WHERE exportcompany_id = $1 
AND   status = 'Canceled';

-- name: UpdateOrderConsignmentStatus :one
UPDATE orders
  set 
     status = $2
WHERE consignment_no = $1
RETURNING *;

-- name: GetCompanyWithConsignmentNo :one
SELECT * FROM orders WHERE consignment_no = $1;


-- name: AddExtraProductOnOrder :one
INSERT INTO order_crops (
     crop_id,
     order_id,
     weight
  )VALUES(
     $1,
     $2,
     $3
  )RETURNING *;

-- name: CheckifProductExists :one
SELECT EXISTS (
    SELECT 1
    FROM order_crops
    WHERE crop_id = $1 
    AND   order_id = $2
);


-- name: GlobalConsignments :many
SELECT orders.id,orders.consignment_no,orders.created_at,
orders.status,exportcompanies.expname from orders
JOIN exportcompanies on exportcompanies.id =  orders.exportcompany_id
ORDER BY orders.created_at DESC
LIMIT $1 OFFSET $2;


-- name: SearchGlobalByConsignmentNo :many
SELECT orders.id,orders.consignment_no,orders.created_at,
orders.status,exportcompanies.expname from orders
JOIN exportcompanies on exportcompanies.id =  orders.exportcompany_id
WHERE LOWER(orders.consignment_no) LIKE '%' || LOWER($1) || '%'
ORDER BY orders.created_at DESC;


-- name: ReturnOrderID :one
SELECT orders.id from orders
WHERE  orders.consignment_no=$1;



-- name: SearchOrderByConsignmentNo :many
SELECT * FROM orders
WHERE exportcompany_id = $1
AND   status= $2
AND   LOWER(consignment_no) LIKE '%' || LOWER($3) || '%';

-- name: SearchCompanyOrderByClient :many
SELECT orders.client_id,orders.id as order_id, 
orders.created_at,orders.user_id,
clients.companyname,countries.countryname,clients.contactname,
orders.status,orders.consignment_no,
orders.exportcompany_id,exportcompanies.expname 
FROM  orders
JOIN  clients on clients.id = orders.client_id
JOIN  exportcompanies on exportcompanies.id = orders.exportcompany_id
JOIN  countries on countries.id = clients.country_id
WHERE orders.exportcompany_id = $1 
AND   orders.status= $3 
AND   LOWER(clients.companyname) LIKE '%' || LOWER($2) || '%';



-- name: TotalQcp1WeightRejected :one
SELECT 
  CAST(SUM(qcp1_recommendation.netweightrejected) AS BIGINT) AS total_weight_rejected
FROM 
  qcp1_recommendation
WHERE 
  DATE_TRUNC('month', created_at) = DATE_TRUNC('month', CURRENT_DATE);


-- name: TotalQcp2WeightRejected :one
SELECT 
  CAST(SUM(qcp2_recommendation.netweightrejected) AS BIGINT) AS total_weight_rejected
FROM 
  qcp2_recommendation
WHERE 
  DATE_TRUNC('month', created_at) = DATE_TRUNC('month', CURRENT_DATE);

-- name: TotalQcp3WeightRejected :one
SELECT 
  CAST(SUM(qcp3_recommendation.netweightrejected) AS BIGINT) AS total_weight_rejected
FROM 
  qcp3_recommendation
WHERE 
  DATE_TRUNC('month', created_at) = DATE_TRUNC('month', CURRENT_DATE);



-- name: TotalQcp4WeightRejected :one  
SELECT  
  CAST(SUM(weight_rejected) AS  BIGINT) AS total_weight_rejected 
FROM 
  qcp4_recommendations
WHERE 
  DATE_TRUNC('month', created_at) = DATE_TRUNC('month', CURRENT_DATE);



-- name: TotalQcp5WeightRejected :one
SELECT  
  CAST(SUM(weight_rejected) AS  BIGINT) AS total_weight_rejected 
FROM 
  qcp5_recommendations
WHERE 
  DATE_TRUNC('month', created_at) = DATE_TRUNC('month', CURRENT_DATE);



-- name: TotalQcp5WastegePerCrop :many
SELECT  
  CAST(SUM(weight_rejected) AS  BIGINT) AS total_net_weight_rejected, 
  crops.cropname, 
  crops.id 
FROM 
  qcp5_recommendations
JOIN 
  qcp5_products ON qcp5_products.id = qcp5_recommendations.qcp5_product_id
JOIN 
  crops ON crops.id = qcp5_products.product_id
WHERE 
  DATE_TRUNC('month', qcp5_recommendations.created_at) = DATE_TRUNC('month', CURRENT_DATE)
GROUP BY 
  crops.id, crops.cropname 
ORDER BY 
  total_net_weight_rejected DESC 
LIMIT 5;


-- name: TotalQcp4WastegePerCrop :many
SELECT  
  CAST(SUM(weight_rejected) AS  BIGINT) AS total_net_weight_rejected, 
  crops.cropname, 
  crops.id 
FROM 
  qcp4_recommendations
JOIN 
  qcp4_products ON qcp4_products.id = qcp4_recommendations.qcp4_product_id
JOIN 
  crops ON crops.id = qcp4_products.product_id
WHERE 
  DATE_TRUNC('month', qcp4_recommendations.created_at) = DATE_TRUNC('month', CURRENT_DATE)
GROUP BY 
  crops.id, crops.cropname 
ORDER BY 
  total_net_weight_rejected DESC 
LIMIT 5;


-- name: TotalQcp3WastegePerCrop :many
SELECT  
  crops.id AS crop_id, 
  crops.cropname, 
  CAST(SUM(qcp3_recommendation.netweightrejected) AS  BIGINT) AS total_net_weight_rejected
FROM  
  qcp3_recommendation
JOIN  
  qcp3 ON qcp3.reception_id = qcp3_recommendation.qcp3_id
JOIN  
  receptionform ON receptionform.id = qcp3.reception_id
JOIN  
  crops ON crops.id = receptionform.crop_id
WHERE 
  DATE_TRUNC('month', qcp3.created_at) = DATE_TRUNC('month', CURRENT_DATE)
GROUP BY 
  crops.id, crops.cropname 
ORDER BY 
  total_net_weight_rejected DESC 
LIMIT 5;



-- name: TotalQcp2WastegePerCrop :many
SELECT  
  crops.id AS crop_id, 
  crops.cropname, 
  CAST(SUM(qcp2_recommendation.netweightrejected) AS  BIGINT) AS total_net_weight_rejected
FROM  
  qcp2_recommendation
JOIN  
  qcp2 ON qcp2.reception_id = qcp2_recommendation.qcp2_id
JOIN  
  receptionform ON receptionform.id = qcp2.reception_id
JOIN  
  crops ON crops.id = receptionform.crop_id
WHERE 
  DATE_TRUNC('month', qcp2.created_at) = DATE_TRUNC('month', CURRENT_DATE)
GROUP BY 
  crops.id, crops.cropname 
ORDER BY 
  total_net_weight_rejected DESC 
LIMIT 5;



-- name: TotalQcp1WastegePerCrop :many
SELECT  
  crops.id AS crop_id, 
  crops.cropname, 
  CAST(SUM(qcp1_recommendation.netweightrejected) AS  BIGINT) AS total_net_weight_rejected
FROM  
  qcp1_recommendation
JOIN  
  qcp1 ON qcp1.id = qcp1_recommendation.qcp1_id
JOIN  
  receptionform ON receptionform.id = qcp1.receptionform_id
JOIN  
  crops ON crops.id = receptionform.crop_id
WHERE 
  DATE_TRUNC('month', qcp1.created_at) = DATE_TRUNC('month', CURRENT_DATE)
GROUP BY 
  crops.id, crops.cropname 
ORDER BY 
  total_net_weight_rejected DESC 
LIMIT 5;


-- name: TopDestinations :many
SELECT 
    cs.countryname AS destination_country,
    COUNT(DISTINCT o.id) AS total_orders,
    COALESCE(SUM(oc.weightreceived), 0) AS total_weight_received
FROM 
    orders o
JOIN 
    clients c ON o.client_id = c.id
JOIN 
    countries cs ON cs.id = c.country_id
LEFT JOIN 
    order_crops oc ON o.id = oc.order_id
WHERE 
    o.status = 'Completed' 
    AND DATE_TRUNC('month', o.created_at) = DATE_TRUNC('month', CURRENT_DATE)
GROUP BY 
    cs.countryname
ORDER BY 
    total_weight_received DESC
LIMIT 5;



-- name: TopDestinationsTotalWeight :one
SELECT 
  COALESCE(SUM(oc.weightreceived), 0) AS total_weight_received
FROM 
  orders o
LEFT JOIN 
  order_crops oc ON o.id = oc.order_id
WHERE 
  o.status = 'Completed'
  AND DATE_TRUNC('month', o.created_at) = DATE_TRUNC('month', CURRENT_DATE);





-- name: TopDestinationsWithWeight :many
SELECT 
  cs.countryname AS destination_country,
  COUNT(DISTINCT o.id) AS total_orders, 
  SUM(oc.weightreceived) AS total_weight_exported
FROM  
  orders o
JOIN  
  order_crops oc ON o.id = oc.order_id
JOIN  
  clients c ON o.client_id = c.id
JOIN  
  countries cs ON cs.id = c.country_id
WHERE 
  o.status = 'Completed'
  AND oc.status = 'Active'
  AND DATE_TRUNC('month', o.created_at) = DATE_TRUNC('month', CURRENT_DATE)
GROUP BY 
  cs.countryname
ORDER BY 
  total_weight_exported DESC
LIMIT 5;


-- name: TopProductsExported :many
SELECT 
  oc.crop_id, 
  c.cropname,
  COUNT(*) AS crop_appearance_count,
  COALESCE(SUM(oc.weightreceived), 0) AS total_weight_received
FROM 
  order_crops oc
JOIN crops c ON c.id = oc.crop_id
JOIN orders o ON o.id = oc.order_id
WHERE 
  oc.status = 'Active' 
  AND o.status = 'Completed'
  AND DATE_TRUNC('month', o.created_at) = DATE_TRUNC('month', CURRENT_DATE)
GROUP BY 
  oc.crop_id, 
  c.cropname
ORDER BY 
  crop_appearance_count DESC, 
  total_weight_received DESC
LIMIT 5;


-- name: TotalCompletedOrders :one
SELECT COUNT(*)
FROM orders
WHERE status = 'Completed'
  AND DATE_TRUNC('month', created_at) = DATE_TRUNC('month', CURRENT_DATE);


-- name: TotalCanceledOrders :one
SELECT COUNT(*)
FROM orders
WHERE status = 'Canceled'
  AND DATE_TRUNC('month', created_at) = DATE_TRUNC('month', CURRENT_DATE);

-- name: TotalActiveOrders :one
SELECT COUNT(*)
FROM orders
WHERE status = 'Active'
  AND DATE_TRUNC('month', created_at) = DATE_TRUNC('month', CURRENT_DATE);

-- name: TotalOrdersUnderInspection :one
SELECT COUNT(*)
FROM orders
WHERE status = 'UnderInspection'
  AND DATE_TRUNC('month', created_at) = DATE_TRUNC('month', CURRENT_DATE);

-- name: TotalInterceptedOrders :one
SELECT COUNT(*)
FROM orders
WHERE status = 'Intercepted'
  AND DATE_TRUNC('month', created_at) = DATE_TRUNC('month', CURRENT_DATE);

-- name: TotalGlobalOrders :one
SELECT  COUNT(*) from orders
WHERE DATE_TRUNC('month', created_at) = DATE_TRUNC('month', CURRENT_DATE);



-- name: TotalCumulativeCompletedOrders :one
SELECT COUNT(*)
FROM orders
WHERE status = 'Completed';


-- name: TotalCumulativeCanceledOrders :one
SELECT COUNT(*)
FROM orders
WHERE status = 'Canceled';

-- name: TotalCumulativeActiveOrders :one
SELECT COUNT(*)
FROM orders
WHERE status = 'Active';

-- name: TotalCumulativeOrdersUnderInspection :one
SELECT COUNT(*)
FROM orders
WHERE status = 'UnderInspection';

-- name: TotalCumulativeInterceptedOrders :one
SELECT COUNT(*)
FROM orders
WHERE status = 'Intercepted';

-- name: TotalCumulativeGlobalOrders :one
SELECT  COUNT(*) from orders;

-- name: TotalGlobalFarmers :one
SELECT  COUNT(*) from farmers
WHERE DATE_TRUNC('month', created_at) = DATE_TRUNC('month', CURRENT_DATE);

-- name: TotalGlobalTraceabilities :one
SELECT COUNT(*) from farmer_farms 
WHERE DATE_TRUNC('month', created_at) = DATE_TRUNC('month', CURRENT_DATE);

-- name: TotalGlobalCountriesByCrop :many
SELECT 
  oc.crop_id, 
    c.cropname,
    COUNT(*) AS crop_appearance_count,
    SUM(oc.weightreceived) AS total_weight_received
FROM 
    order_crops oc
JOIN 
    crops   c ON c.id   = oc.crop_id
JOIN orders o ON o.id   = oc.order_id

WHERE 
    oc.status = 'Active' 
AND o.status='Completed'
GROUP BY 
    oc.crop_id, 
    c.cropname
ORDER BY 
    crop_appearance_count DESC, 
    total_weight_received DESC
LIMIT 5;


-- name: TotalGlobalProductNetweightPerQcp5 :many
SELECT
    crops.cropname,
    SUM(qcp5_products.productnetweight) AS total_productnetweight
FROM
    qcp5_products
JOIN
    crops ON crops.id = qcp5_products.product_id
WHERE
    qcp5_products.status = 'FitForExport'
GROUP BY
    crops.cropname
ORDER BY
    total_productnetweight DESC LIMIT 5;


-- name: TotalGlobalOrdersUnderInspectionCompleted :many
SELECT
    crops.cropname,
    SUM(qcp5_products.totalproductnetweight) AS total_productnetweight
FROM
    qcp5_products
JOIN 
    qcp5 on qcp5.id = qcp5_products.qcp5_id
JOIN 
    packinglist on packinglist.id = qcp5.packinglist_id
JOIN 
    orders      on orders.id = packinglist.order_id
JOIN
    crops       on crops.id = qcp5_products.product_id
WHERE
    orders.status IN ('Completed', 'UnderInspection')
GROUP BY
    crops.cropname
ORDER BY
    total_productnetweight DESC LIMIT 5;

-- name: TotalGlobalCountriesPerCrop :many
SELECT 
  crops.id AS crop_id,
  crops.cropname,
  countries.countryname, 
  SUM(order_crops.weightreceived) AS total_weight_received 
FROM 
  order_crops 
JOIN orders ON orders.id = order_crops.order_id
JOIN clients ON clients.id = orders.client_id
JOIN countries ON countries.id = clients.country_id
JOIN crops ON crops.id = order_crops.crop_id
WHERE 
  order_crops.crop_id = $1
  AND order_crops.weightreceived IS NOT NULL 
  AND orders.status = 'Completed' 
GROUP BY 
  crops.id, crops.cropname, countries.countryname 
ORDER BY 
  total_weight_received DESC 
LIMIT 2;


-- name: ShowTotalWeightReceived :many
WITH country_crop_totals AS (
    SELECT
        countries.id AS country_id,
        countries.countryname AS Countryname,
        crops.id AS crop_id,
        crops.cropname AS Cropname,
        SUM(order_crops.weightreceived) AS TotalWeightReceived,
        ROW_NUMBER() OVER (
            PARTITION BY countries.id
            ORDER BY SUM(order_crops.weightreceived) DESC
        ) AS crop_rank
    FROM 
        order_crops
    JOIN orders ON orders.id = order_crops.order_id
    JOIN clients ON clients.id = orders.client_id
    JOIN countries ON countries.id = clients.country_id
    JOIN crops ON crops.id = order_crops.crop_id
    WHERE 
        order_crops.weightreceived IS NOT NULL
        AND orders.status = 'Completed'
    GROUP BY 
        countries.id, countries.countryname, crops.id, crops.cropname
),
country_totals AS (
    SELECT 
        country_id,
        countryname AS Countryname,
        SUM(total_weight_received) AS TotalCountryWeight
    FROM country_crop_totals
    GROUP BY country_id, countryname
)
SELECT 
    cct.Countryname,
    cct.Cropname,
    cct.TotalWeightReceived,
    ct.TotalCountryWeight
FROM 
    country_crop_totals cct
JOIN 
    country_totals ct ON ct.country_id = cct.country_id
WHERE 
    cct.crop_rank <= 2
ORDER BY 
    ct.TotalCountryWeight DESC,
    cct.Countryname,
    cct.TotalWeightReceived DESC;





-- name: ShowTotalWeightExported :one
SELECT 
  CAST(SUM(order_crops.weightreceived) AS DOUBLE PRECISION) AS total_weight_exported
FROM 
  order_crops
JOIN orders 
  ON orders.id = order_crops.order_id
WHERE 
  orders.status = 'Completed'
  AND DATE_TRUNC('month', orders.created_at) = DATE_TRUNC('month', CURRENT_DATE);



-- name: ShowTotalWeightExporterPrevious :one
SELECT 
  CAST(SUM(order_crops.weightreceived) AS BIGINT) AS total_weight_exported
FROM 
  order_crops
JOIN 
  orders ON orders.id = order_crops.order_id
WHERE 
  orders.status = 'Completed' AND
  DATE_TRUNC('month', orders.created_at) = DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month');



-- name: ShowTotalTopCompanies :many
SELECT
  ec.expname,
  COALESCE(SUM(oc.weight), 0)            AS total_weight_requested,
  COALESCE(SUM(oc.weightreceived), 0)    AS total_weight_exported
FROM
  exportcompanies ec
LEFT JOIN
  orders o 
    ON o.exportcompany_id = ec.id
   AND DATE_TRUNC('month', o.created_at) = DATE_TRUNC('month', CURRENT_DATE)
LEFT JOIN
  order_crops oc 
    ON oc.order_id = o.id 
   AND o.status IN ('Completed')
GROUP BY
  ec.expname
ORDER BY
  total_weight_exported DESC
LIMIT 5;



-- name: ShowTopCompaniesPrevious :many
SELECT
  ec.expname,
  COALESCE(SUM(oc.weightreceived), 0) AS total_weight_exported
FROM
  exportcompanies ec
LEFT JOIN
  orders o ON o.exportcompany_id = ec.id
  AND o.status IN ('Completed')
  AND DATE_TRUNC('month', o.created_at) = DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
LEFT JOIN
  order_crops oc ON o.id = oc.order_id
GROUP BY
  ec.expname
ORDER BY
  total_weight_exported DESC
LIMIT 5;

