// Code generated by sqlc. DO NOT EDIT.
// versions:
//   sqlc v1.28.0
// source: crops.sql

package db

import (
	"context"
)

const allCrops = `-- name: AllCrops :many
SELECT id, variant, cropname, crophscode, cic_code, riskstatus, status, created_at FROM crops
`

func (q *Queries) AllCrops(ctx context.Context) ([]Crop, error) {
	rows, err := q.db.QueryContext(ctx, allCrops)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	items := []Crop{}
	for rows.Next() {
		var i Crop
		if err := rows.Scan(
			&i.ID,
			&i.Variant,
			&i.Cropname,
			&i.Crophscode,
			&i.CicCode,
			&i.Riskstatus,
			&i.Status,
			&i.CreatedAt,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Close(); err != nil {
		return nil, err
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const createCrop = `-- name: CreateCrop :one
INSERT INTO crops (
    cropname,       
    variant,       
    crophscode,
    riskstatus,
    cic_code
) VALUES (
  $1, 
  $2, 
  $3, 
  $4, 
  $5
) 
RETURNING id, variant, cropname, crophscode, cic_code, riskstatus, status, created_at
`

type CreateCropParams struct {
	Cropname   string `json:"cropname"`
	Variant    string `json:"variant"`
	Crophscode string `json:"crophscode"`
	Riskstatus string `json:"riskstatus"`
	CicCode    string `json:"cic_code"`
}

func (q *Queries) CreateCrop(ctx context.Context, arg CreateCropParams) (Crop, error) {
	row := q.db.QueryRowContext(ctx, createCrop,
		arg.Cropname,
		arg.Variant,
		arg.Crophscode,
		arg.Riskstatus,
		arg.CicCode,
	)
	var i Crop
	err := row.Scan(
		&i.ID,
		&i.Variant,
		&i.Cropname,
		&i.Crophscode,
		&i.CicCode,
		&i.Riskstatus,
		&i.Status,
		&i.CreatedAt,
	)
	return i, err
}

const cropsSearchName = `-- name: CropsSearchName :many
SELECT id, variant, cropname, crophscode, cic_code, riskstatus, status, created_at
FROM  crops
WHERE LOWER(cropname) LIKE '%' || LOWER($1) || '%'
`

func (q *Queries) CropsSearchName(ctx context.Context, lower string) ([]Crop, error) {
	rows, err := q.db.QueryContext(ctx, cropsSearchName, lower)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	items := []Crop{}
	for rows.Next() {
		var i Crop
		if err := rows.Scan(
			&i.ID,
			&i.Variant,
			&i.Cropname,
			&i.Crophscode,
			&i.CicCode,
			&i.Riskstatus,
			&i.Status,
			&i.CreatedAt,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Close(); err != nil {
		return nil, err
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const deleteCrop = `-- name: DeleteCrop :exec
DELETE FROM crops WHERE id= $1
`

func (q *Queries) DeleteCrop(ctx context.Context, id int64) error {
	_, err := q.db.ExecContext(ctx, deleteCrop, id)
	return err
}

const getCrop = `-- name: GetCrop :one
SELECT id, variant, cropname, crophscode, cic_code, riskstatus, status, created_at FROM crops
WHERE id = $1 LIMIT 1
`

func (q *Queries) GetCrop(ctx context.Context, id int64) (Crop, error) {
	row := q.db.QueryRowContext(ctx, getCrop, id)
	var i Crop
	err := row.Scan(
		&i.ID,
		&i.Variant,
		&i.Cropname,
		&i.Crophscode,
		&i.CicCode,
		&i.Riskstatus,
		&i.Status,
		&i.CreatedAt,
	)
	return i, err
}

const getCropByCICode = `-- name: GetCropByCICode :one
SELECT id FROM crops 
WHERE cic_code = $1
LIMIT 1
`

func (q *Queries) GetCropByCICode(ctx context.Context, cicCode string) (int64, error) {
	row := q.db.QueryRowContext(ctx, getCropByCICode, cicCode)
	var id int64
	err := row.Scan(&id)
	return id, err
}

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

func (q *Queries) GetGlobalCropCount(ctx context.Context, status string) (int64, error) {
	row := q.db.QueryRowContext(ctx, getGlobalCropCount, status)
	var count int64
	err := row.Scan(&count)
	return count, err
}

const listCrops = `-- name: ListCrops :many
SELECT id, variant, cropname, crophscode, cic_code, riskstatus, status, created_at FROM crops
ORDER BY created_at DESC LIMIT $1
OFFSET $2
`

type ListCropsParams struct {
	Limit  int32 `json:"limit"`
	Offset int32 `json:"offset"`
}

func (q *Queries) ListCrops(ctx context.Context, arg ListCropsParams) ([]Crop, error) {
	rows, err := q.db.QueryContext(ctx, listCrops, arg.Limit, arg.Offset)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	items := []Crop{}
	for rows.Next() {
		var i Crop
		if err := rows.Scan(
			&i.ID,
			&i.Variant,
			&i.Cropname,
			&i.Crophscode,
			&i.CicCode,
			&i.Riskstatus,
			&i.Status,
			&i.CreatedAt,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Close(); err != nil {
		return nil, err
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const topCompaniesExporting = `-- 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
`

type TopCompaniesExportingRow struct {
	Expname              string `json:"expname"`
	TotalWeightRequested int64  `json:"total_weight_requested"`
	TotalWeightExported  int64  `json:"total_weight_exported"`
}

func (q *Queries) TopCompaniesExporting(ctx context.Context) ([]TopCompaniesExportingRow, error) {
	rows, err := q.db.QueryContext(ctx, topCompaniesExporting)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	items := []TopCompaniesExportingRow{}
	for rows.Next() {
		var i TopCompaniesExportingRow
		if err := rows.Scan(&i.Expname, &i.TotalWeightRequested, &i.TotalWeightExported); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Close(); err != nil {
		return nil, err
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const topCompaniesWithTop3Crops = `-- 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
`

type TopCompaniesWithTop3CropsRow struct {
	Expname         string `json:"expname"`
	TopExportedCrop string `json:"top_exported_crop"`
	TotalCropWeight int64  `json:"total_crop_weight"`
}

func (q *Queries) TopCompaniesWithTop3Crops(ctx context.Context) ([]TopCompaniesWithTop3CropsRow, error) {
	rows, err := q.db.QueryContext(ctx, topCompaniesWithTop3Crops)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	items := []TopCompaniesWithTop3CropsRow{}
	for rows.Next() {
		var i TopCompaniesWithTop3CropsRow
		if err := rows.Scan(&i.Expname, &i.TopExportedCrop, &i.TotalCropWeight); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Close(); err != nil {
		return nil, err
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const updateCrop = `-- name: UpdateCrop :one
UPDATE crops
  set 
    cropname=$2,              
    crophscode=$3,
    cic_code= $4,
    riskstatus=$5,
    variant = $6
WHERE id = $1
RETURNING id, variant, cropname, crophscode, cic_code, riskstatus, status, created_at
`

type UpdateCropParams struct {
	ID         int64  `json:"id"`
	Cropname   string `json:"cropname"`
	Crophscode string `json:"crophscode"`
	CicCode    string `json:"cic_code"`
	Riskstatus string `json:"riskstatus"`
	Variant    string `json:"variant"`
}

func (q *Queries) UpdateCrop(ctx context.Context, arg UpdateCropParams) (Crop, error) {
	row := q.db.QueryRowContext(ctx, updateCrop,
		arg.ID,
		arg.Cropname,
		arg.Crophscode,
		arg.CicCode,
		arg.Riskstatus,
		arg.Variant,
	)
	var i Crop
	err := row.Scan(
		&i.ID,
		&i.Variant,
		&i.Cropname,
		&i.Crophscode,
		&i.CicCode,
		&i.Riskstatus,
		&i.Status,
		&i.CreatedAt,
	)
	return i, err
}

const updateCropStatus = `-- name: UpdateCropStatus :exec
UPDATE crops
  set 
    status = $2
WHERE id = $1
RETURNING id, variant, cropname, crophscode, cic_code, riskstatus, status, created_at
`

type UpdateCropStatusParams struct {
	ID     int64  `json:"id"`
	Status string `json:"status"`
}

func (q *Queries) UpdateCropStatus(ctx context.Context, arg UpdateCropStatusParams) error {
	_, err := q.db.ExecContext(ctx, updateCropStatus, arg.ID, arg.Status)
	return err
}
