// Code generated by sqlc. DO NOT EDIT.
// versions:
//   sqlc v1.27.0
// source: requisitions.sql

package db

import (
	"context"

	"github.com/jackc/pgx/v5/pgtype"
	"github.com/shopspring/decimal"
)

const countPurchaseRequisitions = `-- name: CountPurchaseRequisitions :one
SELECT count(*) FROM purchase_requisitions r
WHERE ($1::text IS NULL OR r.status = $1)
  AND ($2::bigint IS NULL OR $2::bigint <= 0 OR r.store_id = $2)
  AND ($3::bigint IS NULL OR $3::bigint <= 0 OR r.requested_by = $3)
`

type CountPurchaseRequisitionsParams struct {
	Status      pgtype.Text `json:"status"`
	StoreID     pgtype.Int8 `json:"store_id"`
	RequestedBy pgtype.Int8 `json:"requested_by"`
}

func (q *Queries) CountPurchaseRequisitions(ctx context.Context, arg CountPurchaseRequisitionsParams) (int64, error) {
	row := q.db.QueryRow(ctx, countPurchaseRequisitions, arg.Status, arg.StoreID, arg.RequestedBy)
	var count int64
	err := row.Scan(&count)
	return count, err
}

const createPurchaseRequisition = `-- name: CreatePurchaseRequisition :one

INSERT INTO purchase_requisitions (requisition_number, store_id, warehouse_id, requested_by, status)
VALUES ($1, $2, $3, $4, 'pending')
RETURNING id, requisition_number, store_id, warehouse_id, requested_by, status,
          po_id, reviewed_by,
          COALESCE(to_char(reviewed_at AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"'), '')::text AS reviewed_at,
          COALESCE(review_note, '')::text AS review_note,
          to_char(created_at AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"')::text AS created_at,
          to_char(updated_at AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"')::text AS updated_at
`

type CreatePurchaseRequisitionParams struct {
	RequisitionNumber string      `json:"requisition_number"`
	StoreID           int64       `json:"store_id"`
	WarehouseID       pgtype.Int8 `json:"warehouse_id"`
	RequestedBy       int64       `json:"requested_by"`
}

type CreatePurchaseRequisitionRow struct {
	ID                int64       `json:"id"`
	RequisitionNumber string      `json:"requisition_number"`
	StoreID           int64       `json:"store_id"`
	WarehouseID       pgtype.Int8 `json:"warehouse_id"`
	RequestedBy       int64       `json:"requested_by"`
	Status            string      `json:"status"`
	PoID              pgtype.Int8 `json:"po_id"`
	ReviewedBy        pgtype.Int8 `json:"reviewed_by"`
	ReviewedAt        string      `json:"reviewed_at"`
	ReviewNote        string      `json:"review_note"`
	CreatedAt         string      `json:"created_at"`
	UpdatedAt         string      `json:"updated_at"`
}

// Purchase requisitions (000151): store head requests items running out;
// admin pembelian converts the request into a PO. Request-only records —
// no stock movement, no money. Sort whitelist dir-gated like every other
// list (the products ORDER BY bug pattern).
func (q *Queries) CreatePurchaseRequisition(ctx context.Context, arg CreatePurchaseRequisitionParams) (CreatePurchaseRequisitionRow, error) {
	row := q.db.QueryRow(ctx, createPurchaseRequisition,
		arg.RequisitionNumber,
		arg.StoreID,
		arg.WarehouseID,
		arg.RequestedBy,
	)
	var i CreatePurchaseRequisitionRow
	err := row.Scan(
		&i.ID,
		&i.RequisitionNumber,
		&i.StoreID,
		&i.WarehouseID,
		&i.RequestedBy,
		&i.Status,
		&i.PoID,
		&i.ReviewedBy,
		&i.ReviewedAt,
		&i.ReviewNote,
		&i.CreatedAt,
		&i.UpdatedAt,
	)
	return i, err
}

const createPurchaseRequisitionItem = `-- name: CreatePurchaseRequisitionItem :exec
INSERT INTO purchase_requisition_items (requisition_id, product_id, quantity, note)
VALUES ($1, $2, $3, $4)
`

type CreatePurchaseRequisitionItemParams struct {
	RequisitionID int64           `json:"requisition_id"`
	ProductID     int64           `json:"product_id"`
	Quantity      decimal.Decimal `json:"quantity"`
	Note          pgtype.Text     `json:"note"`
}

func (q *Queries) CreatePurchaseRequisitionItem(ctx context.Context, arg CreatePurchaseRequisitionItemParams) error {
	_, err := q.db.Exec(ctx, createPurchaseRequisitionItem,
		arg.RequisitionID,
		arg.ProductID,
		arg.Quantity,
		arg.Note,
	)
	return err
}

const getPurchaseRequisition = `-- name: GetPurchaseRequisition :one
SELECT r.id, r.requisition_number, r.store_id, r.warehouse_id, r.requested_by, r.status,
       r.po_id, r.reviewed_by, COALESCE(to_char(r.reviewed_at AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"'), '')::text AS reviewed_at, r.review_note,
       r.created_at::text AS created_at,
       COALESCE(s.name, '') AS store_name, COALESCE(w.name, '') AS warehouse_name,
       COALESCE(u.name, '') AS requested_by_name,
       COALESCE(p.po_number, '') AS po_number
FROM purchase_requisitions r
LEFT JOIN stores s ON s.id = r.store_id
LEFT JOIN warehouses w ON w.id = r.warehouse_id
LEFT JOIN users u ON u.id = r.requested_by
LEFT JOIN purchase_orders p ON p.id = r.po_id
WHERE r.id = $1
`

type GetPurchaseRequisitionRow struct {
	ID                int64       `json:"id"`
	RequisitionNumber string      `json:"requisition_number"`
	StoreID           int64       `json:"store_id"`
	WarehouseID       pgtype.Int8 `json:"warehouse_id"`
	RequestedBy       int64       `json:"requested_by"`
	Status            string      `json:"status"`
	PoID              pgtype.Int8 `json:"po_id"`
	ReviewedBy        pgtype.Int8 `json:"reviewed_by"`
	ReviewedAt        string      `json:"reviewed_at"`
	ReviewNote        pgtype.Text `json:"review_note"`
	CreatedAt         string      `json:"created_at"`
	StoreName         string      `json:"store_name"`
	WarehouseName     string      `json:"warehouse_name"`
	RequestedByName   string      `json:"requested_by_name"`
	PoNumber          string      `json:"po_number"`
}

func (q *Queries) GetPurchaseRequisition(ctx context.Context, id int64) (GetPurchaseRequisitionRow, error) {
	row := q.db.QueryRow(ctx, getPurchaseRequisition, id)
	var i GetPurchaseRequisitionRow
	err := row.Scan(
		&i.ID,
		&i.RequisitionNumber,
		&i.StoreID,
		&i.WarehouseID,
		&i.RequestedBy,
		&i.Status,
		&i.PoID,
		&i.ReviewedBy,
		&i.ReviewedAt,
		&i.ReviewNote,
		&i.CreatedAt,
		&i.StoreName,
		&i.WarehouseName,
		&i.RequestedByName,
		&i.PoNumber,
	)
	return i, err
}

const listPurchaseRequisitionItems = `-- name: ListPurchaseRequisitionItems :many
SELECT i.id, i.requisition_id, i.product_id, i.quantity, i.note,
       p.code AS product_code, p.name AS product_name,
       COALESCE(u.symbol, '') AS unit_symbol
FROM purchase_requisition_items i
JOIN products p ON p.id = i.product_id
LEFT JOIN units u ON u.id = p.unit_id
WHERE i.requisition_id = $1
ORDER BY i.id
`

type ListPurchaseRequisitionItemsRow struct {
	ID            int64           `json:"id"`
	RequisitionID int64           `json:"requisition_id"`
	ProductID     int64           `json:"product_id"`
	Quantity      decimal.Decimal `json:"quantity"`
	Note          pgtype.Text     `json:"note"`
	ProductCode   string          `json:"product_code"`
	ProductName   string          `json:"product_name"`
	UnitSymbol    string          `json:"unit_symbol"`
}

func (q *Queries) ListPurchaseRequisitionItems(ctx context.Context, requisitionID int64) ([]ListPurchaseRequisitionItemsRow, error) {
	rows, err := q.db.Query(ctx, listPurchaseRequisitionItems, requisitionID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []ListPurchaseRequisitionItemsRow
	for rows.Next() {
		var i ListPurchaseRequisitionItemsRow
		if err := rows.Scan(
			&i.ID,
			&i.RequisitionID,
			&i.ProductID,
			&i.Quantity,
			&i.Note,
			&i.ProductCode,
			&i.ProductName,
			&i.UnitSymbol,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const listPurchaseRequisitions = `-- name: ListPurchaseRequisitions :many
SELECT r.id, r.requisition_number, r.store_id, r.warehouse_id, r.requested_by, r.status,
       r.po_id, r.reviewed_by, COALESCE(to_char(r.reviewed_at AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"'), '')::text AS reviewed_at, r.review_note,
       r.created_at::text AS created_at,
       COALESCE(s.name, '') AS store_name, COALESCE(w.name, '') AS warehouse_name,
       COALESCE(u.name, '') AS requested_by_name,
       (SELECT count(*) FROM purchase_requisition_items i WHERE i.requisition_id = r.id)::int AS item_count
FROM purchase_requisitions r
LEFT JOIN stores s ON s.id = r.store_id
LEFT JOIN warehouses w ON w.id = r.warehouse_id
LEFT JOIN users u ON u.id = r.requested_by
WHERE ($3::text IS NULL OR r.status = $3)
  AND ($4::bigint IS NULL OR $4::bigint <= 0 OR r.store_id = $4)
  AND ($5::bigint IS NULL OR $5::bigint <= 0 OR r.requested_by = $5)
ORDER BY
  CASE WHEN $6::text = 'requisition_number' AND $7::text <> 'desc' THEN r.requisition_number END ASC,
  CASE WHEN $6::text = 'requisition_number' AND $7::text = 'desc' THEN r.requisition_number END DESC,
  CASE WHEN $6::text = 'status' AND $7::text <> 'desc' THEN r.status END ASC,
  CASE WHEN $6::text = 'status' AND $7::text = 'desc' THEN r.status END DESC,
  CASE WHEN $6::text = 'store_name' AND $7::text <> 'desc' THEN s.name END ASC,
  CASE WHEN $6::text = 'store_name' AND $7::text = 'desc' THEN s.name END DESC,
  r.created_at DESC, r.id DESC
LIMIT $1 OFFSET $2
`

type ListPurchaseRequisitionsParams struct {
	Limit       int32       `json:"limit"`
	Offset      int32       `json:"offset"`
	Status      pgtype.Text `json:"status"`
	StoreID     pgtype.Int8 `json:"store_id"`
	RequestedBy pgtype.Int8 `json:"requested_by"`
	SortBy      pgtype.Text `json:"sort_by"`
	SortDir     pgtype.Text `json:"sort_dir"`
}

type ListPurchaseRequisitionsRow struct {
	ID                int64       `json:"id"`
	RequisitionNumber string      `json:"requisition_number"`
	StoreID           int64       `json:"store_id"`
	WarehouseID       pgtype.Int8 `json:"warehouse_id"`
	RequestedBy       int64       `json:"requested_by"`
	Status            string      `json:"status"`
	PoID              pgtype.Int8 `json:"po_id"`
	ReviewedBy        pgtype.Int8 `json:"reviewed_by"`
	ReviewedAt        string      `json:"reviewed_at"`
	ReviewNote        pgtype.Text `json:"review_note"`
	CreatedAt         string      `json:"created_at"`
	StoreName         string      `json:"store_name"`
	WarehouseName     string      `json:"warehouse_name"`
	RequestedByName   string      `json:"requested_by_name"`
	ItemCount         int32       `json:"item_count"`
}

func (q *Queries) ListPurchaseRequisitions(ctx context.Context, arg ListPurchaseRequisitionsParams) ([]ListPurchaseRequisitionsRow, error) {
	rows, err := q.db.Query(ctx, listPurchaseRequisitions,
		arg.Limit,
		arg.Offset,
		arg.Status,
		arg.StoreID,
		arg.RequestedBy,
		arg.SortBy,
		arg.SortDir,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []ListPurchaseRequisitionsRow
	for rows.Next() {
		var i ListPurchaseRequisitionsRow
		if err := rows.Scan(
			&i.ID,
			&i.RequisitionNumber,
			&i.StoreID,
			&i.WarehouseID,
			&i.RequestedBy,
			&i.Status,
			&i.PoID,
			&i.ReviewedBy,
			&i.ReviewedAt,
			&i.ReviewNote,
			&i.CreatedAt,
			&i.StoreName,
			&i.WarehouseName,
			&i.RequestedByName,
			&i.ItemCount,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const updatePurchaseRequisitionReview = `-- name: UpdatePurchaseRequisitionReview :execrows
UPDATE purchase_requisitions
SET status = $1::text,
    po_id = $2::bigint,
    reviewed_by = $3::bigint,
    reviewed_at = now(),
    review_note = $4::text,
    updated_at = now()
WHERE id = $5::bigint AND status = 'pending'
`

type UpdatePurchaseRequisitionReviewParams struct {
	Status     string      `json:"status"`
	PoID       pgtype.Int8 `json:"po_id"`
	ReviewedBy int64       `json:"reviewed_by"`
	ReviewNote string      `json:"review_note"`
	ID         int64       `json:"id"`
}

// Reviewed (po_id set) or rejected; only a pending requisition can move.
func (q *Queries) UpdatePurchaseRequisitionReview(ctx context.Context, arg UpdatePurchaseRequisitionReviewParams) (int64, error) {
	result, err := q.db.Exec(ctx, updatePurchaseRequisitionReview,
		arg.Status,
		arg.PoID,
		arg.ReviewedBy,
		arg.ReviewNote,
		arg.ID,
	)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected(), nil
}