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

package db

import (
	"context"

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

const countLowStockLines = `-- name: CountLowStockLines :one
SELECT count(*) FROM products p
JOIN categories c ON c.id = p.category_id
JOIN units u ON u.id = p.unit_id
LEFT JOIN (
    SELECT st.product_id, SUM(GREATEST(0, st.quantity)) AS qty, SUM(st.reserved_quantity) AS reserved
    FROM stocks st
    JOIN warehouses w ON w.id = st.warehouse_id
    JOIN stores str ON str.warehouse_id = w.id
    WHERE w.is_active = true
      AND str.is_active = true
    GROUP BY st.product_id
) s ON s.product_id = p.id
WHERE p.deleted_at IS NULL
  AND ($1::text IS NULL OR (p.code ILIKE '%' || $1 || '%' OR p.name ILIKE '%' || $1 || '%'))
  AND (s.qty IS NULL OR s.qty <= p.min_stock)
`

func (q *Queries) CountLowStockLines(ctx context.Context, search pgtype.Text) (int64, error) {
	row := q.db.QueryRow(ctx, countLowStockLines, search)
	var count int64
	err := row.Scan(&count)
	return count, err
}

const countLowStockLinesByWarehouse = `-- name: CountLowStockLinesByWarehouse :one
SELECT count(*) FROM products p
JOIN categories c ON c.id = p.category_id
JOIN units u ON u.id = p.unit_id
LEFT JOIN stocks s ON s.product_id = p.id AND s.warehouse_id = $1::bigint
WHERE p.deleted_at IS NULL
  AND EXISTS (
      SELECT 1 FROM warehouses w
      JOIN stores str ON str.warehouse_id = w.id
      WHERE w.id = $1::bigint AND w.is_active = true AND str.is_active = true
  )
  AND ($2::text IS NULL OR (p.code ILIKE '%' || $2 || '%' OR p.name ILIKE '%' || $2 || '%'))
  AND (s.quantity IS NULL OR s.quantity <= p.min_stock)
`

type CountLowStockLinesByWarehouseParams struct {
	Column1 int64       `json:"column_1"`
	Search  pgtype.Text `json:"search"`
}

func (q *Queries) CountLowStockLinesByWarehouse(ctx context.Context, arg CountLowStockLinesByWarehouseParams) (int64, error) {
	row := q.db.QueryRow(ctx, countLowStockLinesByWarehouse, arg.Column1, arg.Search)
	var count int64
	err := row.Scan(&count)
	return count, err
}

const countMovements = `-- name: CountMovements :one
SELECT count(*) FROM stock_movements m
LEFT JOIN products p ON p.id = m.product_id
WHERE ($1::bigint IS NULL OR m.product_id = $1)
  AND ($2::text IS NULL OR m.movement_type = $2)
  AND ($3::bigint IS NULL OR m.warehouse_id = $3)
  AND ($4::date IS NULL OR m.created_at::date >= $4)
  AND ($5::date IS NULL OR m.created_at::date <= $5)
  AND ($6::text IS NULL OR (
       m.movement_number ILIKE '%' || $6 || '%'
       OR m.notes ILIKE '%' || $6 || '%'
       OR p.name ILIKE '%' || $6 || '%'
       OR p.code ILIKE '%' || $6 || '%'
  ))
`

type CountMovementsParams struct {
	ProductID   pgtype.Int8 `json:"product_id"`
	Type        pgtype.Text `json:"type"`
	WarehouseID pgtype.Int8 `json:"warehouse_id"`
	DateFrom    pgtype.Date `json:"date_from"`
	DateTo      pgtype.Date `json:"date_to"`
	Search      pgtype.Text `json:"search"`
}

func (q *Queries) CountMovements(ctx context.Context, arg CountMovementsParams) (int64, error) {
	row := q.db.QueryRow(ctx, countMovements,
		arg.ProductID,
		arg.Type,
		arg.WarehouseID,
		arg.DateFrom,
		arg.DateTo,
		arg.Search,
	)
	var count int64
	err := row.Scan(&count)
	return count, err
}

const countOpnames = `-- name: CountOpnames :one
SELECT count(*) FROM stock_opnames
WHERE ($1::text IS NULL OR status = $1)
  AND ($2::bigint IS NULL OR warehouse_id = $2)
`

type CountOpnamesParams struct {
	Status      pgtype.Text `json:"status"`
	WarehouseID pgtype.Int8 `json:"warehouse_id"`
}

func (q *Queries) CountOpnames(ctx context.Context, arg CountOpnamesParams) (int64, error) {
	row := q.db.QueryRow(ctx, countOpnames, arg.Status, arg.WarehouseID)
	var count int64
	err := row.Scan(&count)
	return count, err
}

const countProductsByID = `-- name: CountProductsByID :one
SELECT count(*) FROM products WHERE id=$1 AND deleted_at IS NULL
`

func (q *Queries) CountProductsByID(ctx context.Context, id int64) (int64, error) {
	row := q.db.QueryRow(ctx, countProductsByID, id)
	var count int64
	err := row.Scan(&count)
	return count, err
}

const countStockLines = `-- name: CountStockLines :one
SELECT count(*) FROM products p
JOIN categories c ON c.id = p.category_id
JOIN units u ON u.id = p.unit_id
LEFT JOIN (
    SELECT st.product_id, SUM(GREATEST(0, st.quantity)) AS qty, SUM(st.reserved_quantity) AS reserved
    FROM stocks st
    JOIN warehouses w ON w.id = st.warehouse_id
    JOIN stores str ON str.warehouse_id = w.id
    WHERE w.is_active = true
      AND str.is_active = true
    GROUP BY st.product_id
) s ON s.product_id = p.id
WHERE p.deleted_at IS NULL
  AND ($1::text IS NULL OR (p.code ILIKE '%' || $1 || '%' OR p.name ILIKE '%' || $1 || '%'))
`

func (q *Queries) CountStockLines(ctx context.Context, search pgtype.Text) (int64, error) {
	row := q.db.QueryRow(ctx, countStockLines, search)
	var count int64
	err := row.Scan(&count)
	return count, err
}

const countStockLinesByWarehouse = `-- name: CountStockLinesByWarehouse :one
SELECT count(*) FROM products p
JOIN categories c ON c.id = p.category_id
JOIN units u ON u.id = p.unit_id
LEFT JOIN stocks s ON s.product_id = p.id AND s.warehouse_id = $1::bigint
WHERE p.deleted_at IS NULL
  AND EXISTS (
      SELECT 1 FROM warehouses w
      JOIN stores str ON str.warehouse_id = w.id
      WHERE w.id = $1::bigint AND w.is_active = true AND str.is_active = true
  )
  AND ($2::text IS NULL OR (p.code ILIKE '%' || $2 || '%' OR p.name ILIKE '%' || $2 || '%'))
`

type CountStockLinesByWarehouseParams struct {
	Column1 int64       `json:"column_1"`
	Search  pgtype.Text `json:"search"`
}

func (q *Queries) CountStockLinesByWarehouse(ctx context.Context, arg CountStockLinesByWarehouseParams) (int64, error) {
	row := q.db.QueryRow(ctx, countStockLinesByWarehouse, arg.Column1, arg.Search)
	var count int64
	err := row.Scan(&count)
	return count, err
}

const countTransfers = `-- name: CountTransfers :one
SELECT count(*) FROM stock_transfers
WHERE ($1::text IS NULL OR status = $1)
`

func (q *Queries) CountTransfers(ctx context.Context, status pgtype.Text) (int64, error) {
	row := q.db.QueryRow(ctx, countTransfers, status)
	var count int64
	err := row.Scan(&count)
	return count, err
}

const countWarehousesByID = `-- name: CountWarehousesByID :one
SELECT count(*) FROM warehouses WHERE id=$1
`

func (q *Queries) CountWarehousesByID(ctx context.Context, id int64) (int64, error) {
	row := q.db.QueryRow(ctx, countWarehousesByID, id)
	var count int64
	err := row.Scan(&count)
	return count, err
}

const createMovement = `-- name: CreateMovement :one
INSERT INTO stock_movements (movement_number, product_id, warehouse_id, movement_type,
       quantity, quantity_before, quantity_after, reference_type, reference_id, notes, created_by)
VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11) RETURNING id, created_at
`

type CreateMovementParams struct {
	MovementNumber string          `json:"movement_number"`
	ProductID      int64           `json:"product_id"`
	WarehouseID    int64           `json:"warehouse_id"`
	MovementType   string          `json:"movement_type"`
	Quantity       decimal.Decimal `json:"quantity"`
	QuantityBefore decimal.Decimal `json:"quantity_before"`
	QuantityAfter  decimal.Decimal `json:"quantity_after"`
	ReferenceType  pgtype.Text     `json:"reference_type"`
	ReferenceID    pgtype.Int8     `json:"reference_id"`
	Notes          pgtype.Text     `json:"notes"`
	CreatedBy      pgtype.Int8     `json:"created_by"`
}

type CreateMovementRow struct {
	ID        int64              `json:"id"`
	CreatedAt pgtype.Timestamptz `json:"created_at"`
}

func (q *Queries) CreateMovement(ctx context.Context, arg CreateMovementParams) (CreateMovementRow, error) {
	row := q.db.QueryRow(ctx, createMovement,
		arg.MovementNumber,
		arg.ProductID,
		arg.WarehouseID,
		arg.MovementType,
		arg.Quantity,
		arg.QuantityBefore,
		arg.QuantityAfter,
		arg.ReferenceType,
		arg.ReferenceID,
		arg.Notes,
		arg.CreatedBy,
	)
	var i CreateMovementRow
	err := row.Scan(&i.ID, &i.CreatedAt)
	return i, err
}

const createOpname = `-- name: CreateOpname :one
INSERT INTO stock_opnames (opname_number, warehouse_id, opname_date, status, notes, created_by)
VALUES ($1,$2,$3,$4,$5,$6) RETURNING id
`

type CreateOpnameParams struct {
	OpnameNumber string      `json:"opname_number"`
	WarehouseID  int64       `json:"warehouse_id"`
	OpnameDate   pgtype.Date `json:"opname_date"`
	Status       string      `json:"status"`
	Notes        pgtype.Text `json:"notes"`
	CreatedBy    pgtype.Int8 `json:"created_by"`
}

func (q *Queries) CreateOpname(ctx context.Context, arg CreateOpnameParams) (int64, error) {
	row := q.db.QueryRow(ctx, createOpname,
		arg.OpnameNumber,
		arg.WarehouseID,
		arg.OpnameDate,
		arg.Status,
		arg.Notes,
		arg.CreatedBy,
	)
	var id int64
	err := row.Scan(&id)
	return id, err
}

const createOpnameItem = `-- name: CreateOpnameItem :one
INSERT INTO stock_opname_items (stock_opname_id, product_id, system_quantity, physical_quantity, notes)
VALUES ($1,$2,$3,$4,$5) RETURNING id
`

type CreateOpnameItemParams struct {
	StockOpnameID    int64               `json:"stock_opname_id"`
	ProductID        int64               `json:"product_id"`
	SystemQuantity   decimal.Decimal     `json:"system_quantity"`
	PhysicalQuantity decimal.NullDecimal `json:"physical_quantity"`
	Notes            pgtype.Text         `json:"notes"`
}

func (q *Queries) CreateOpnameItem(ctx context.Context, arg CreateOpnameItemParams) (int64, error) {
	row := q.db.QueryRow(ctx, createOpnameItem,
		arg.StockOpnameID,
		arg.ProductID,
		arg.SystemQuantity,
		arg.PhysicalQuantity,
		arg.Notes,
	)
	var id int64
	err := row.Scan(&id)
	return id, err
}

const createTransfer = `-- name: CreateTransfer :one
INSERT INTO stock_transfers (transfer_number, from_warehouse_id, to_warehouse_id, transfer_date, status, notes, created_by)
VALUES ($1,$2,$3,$4,$5,$6,$7) RETURNING id, created_at
`

type CreateTransferParams struct {
	TransferNumber  string      `json:"transfer_number"`
	FromWarehouseID int64       `json:"from_warehouse_id"`
	ToWarehouseID   int64       `json:"to_warehouse_id"`
	TransferDate    pgtype.Date `json:"transfer_date"`
	Status          string      `json:"status"`
	Notes           pgtype.Text `json:"notes"`
	CreatedBy       pgtype.Int8 `json:"created_by"`
}

type CreateTransferRow struct {
	ID        int64              `json:"id"`
	CreatedAt pgtype.Timestamptz `json:"created_at"`
}

func (q *Queries) CreateTransfer(ctx context.Context, arg CreateTransferParams) (CreateTransferRow, error) {
	row := q.db.QueryRow(ctx, createTransfer,
		arg.TransferNumber,
		arg.FromWarehouseID,
		arg.ToWarehouseID,
		arg.TransferDate,
		arg.Status,
		arg.Notes,
		arg.CreatedBy,
	)
	var i CreateTransferRow
	err := row.Scan(&i.ID, &i.CreatedAt)
	return i, err
}

const createTransferItem = `-- name: CreateTransferItem :one
INSERT INTO stock_transfer_items (stock_transfer_id, product_id, quantity_requested, quantity_sent, quantity_received)
VALUES ($1,$2,$3,0,0) RETURNING id
`

type CreateTransferItemParams struct {
	StockTransferID   int64           `json:"stock_transfer_id"`
	ProductID         int64           `json:"product_id"`
	QuantityRequested decimal.Decimal `json:"quantity_requested"`
}

func (q *Queries) CreateTransferItem(ctx context.Context, arg CreateTransferItemParams) (int64, error) {
	row := q.db.QueryRow(ctx, createTransferItem, arg.StockTransferID, arg.ProductID, arg.QuantityRequested)
	var id int64
	err := row.Scan(&id)
	return id, err
}

const ensureStockRow = `-- name: EnsureStockRow :exec
INSERT INTO stocks (product_id, warehouse_id, quantity, reserved_quantity, last_updated_at)
VALUES ($1, $2, 0, 0, now())
ON CONFLICT (product_id, warehouse_id) DO NOTHING
`

type EnsureStockRowParams struct {
	ProductID   int64 `json:"product_id"`
	WarehouseID int64 `json:"warehouse_id"`
}

func (q *Queries) EnsureStockRow(ctx context.Context, arg EnsureStockRowParams) error {
	_, err := q.db.Exec(ctx, ensureStockRow, arg.ProductID, arg.WarehouseID)
	return err
}

const getOpnameByID = `-- name: GetOpnameByID :one
SELECT id, opname_number, warehouse_id, opname_date::text, status, notes, created_by, approved_by
FROM stock_opnames WHERE id=$1
`

type GetOpnameByIDRow struct {
	ID           int64       `json:"id"`
	OpnameNumber string      `json:"opname_number"`
	WarehouseID  int64       `json:"warehouse_id"`
	OpnameDate   string      `json:"opname_date"`
	Status       string      `json:"status"`
	Notes        pgtype.Text `json:"notes"`
	CreatedBy    pgtype.Int8 `json:"created_by"`
	ApprovedBy   pgtype.Int8 `json:"approved_by"`
}

func (q *Queries) GetOpnameByID(ctx context.Context, id int64) (GetOpnameByIDRow, error) {
	row := q.db.QueryRow(ctx, getOpnameByID, id)
	var i GetOpnameByIDRow
	err := row.Scan(
		&i.ID,
		&i.OpnameNumber,
		&i.WarehouseID,
		&i.OpnameDate,
		&i.Status,
		&i.Notes,
		&i.CreatedBy,
		&i.ApprovedBy,
	)
	return i, err
}

const getStockRow = `-- name: GetStockRow :one
SELECT id, product_id, warehouse_id, quantity, reserved_quantity, created_at, updated_at
FROM stocks WHERE product_id=$1 AND warehouse_id=$2
`

type GetStockRowParams struct {
	ProductID   int64 `json:"product_id"`
	WarehouseID int64 `json:"warehouse_id"`
}

type GetStockRowRow struct {
	ID               int64              `json:"id"`
	ProductID        int64              `json:"product_id"`
	WarehouseID      int64              `json:"warehouse_id"`
	Quantity         decimal.Decimal    `json:"quantity"`
	ReservedQuantity decimal.Decimal    `json:"reserved_quantity"`
	CreatedAt        pgtype.Timestamptz `json:"created_at"`
	UpdatedAt        pgtype.Timestamptz `json:"updated_at"`
}

func (q *Queries) GetStockRow(ctx context.Context, arg GetStockRowParams) (GetStockRowRow, error) {
	row := q.db.QueryRow(ctx, getStockRow, arg.ProductID, arg.WarehouseID)
	var i GetStockRowRow
	err := row.Scan(
		&i.ID,
		&i.ProductID,
		&i.WarehouseID,
		&i.Quantity,
		&i.ReservedQuantity,
		&i.CreatedAt,
		&i.UpdatedAt,
	)
	return i, err
}

const getStockRowForUpdate = `-- name: GetStockRowForUpdate :one
SELECT id, product_id, warehouse_id, quantity, reserved_quantity, created_at, updated_at
FROM stocks WHERE product_id=$1 AND warehouse_id=$2 FOR UPDATE
`

type GetStockRowForUpdateParams struct {
	ProductID   int64 `json:"product_id"`
	WarehouseID int64 `json:"warehouse_id"`
}

type GetStockRowForUpdateRow struct {
	ID               int64              `json:"id"`
	ProductID        int64              `json:"product_id"`
	WarehouseID      int64              `json:"warehouse_id"`
	Quantity         decimal.Decimal    `json:"quantity"`
	ReservedQuantity decimal.Decimal    `json:"reserved_quantity"`
	CreatedAt        pgtype.Timestamptz `json:"created_at"`
	UpdatedAt        pgtype.Timestamptz `json:"updated_at"`
}

func (q *Queries) GetStockRowForUpdate(ctx context.Context, arg GetStockRowForUpdateParams) (GetStockRowForUpdateRow, error) {
	row := q.db.QueryRow(ctx, getStockRowForUpdate, arg.ProductID, arg.WarehouseID)
	var i GetStockRowForUpdateRow
	err := row.Scan(
		&i.ID,
		&i.ProductID,
		&i.WarehouseID,
		&i.Quantity,
		&i.ReservedQuantity,
		&i.CreatedAt,
		&i.UpdatedAt,
	)
	return i, err
}

const getTransferByID = `-- name: GetTransferByID :one
SELECT id, transfer_number, from_warehouse_id, to_warehouse_id, transfer_date::text, status, notes, created_by, approved_by
FROM stock_transfers WHERE id=$1
`

type GetTransferByIDRow struct {
	ID              int64       `json:"id"`
	TransferNumber  string      `json:"transfer_number"`
	FromWarehouseID int64       `json:"from_warehouse_id"`
	ToWarehouseID   int64       `json:"to_warehouse_id"`
	TransferDate    string      `json:"transfer_date"`
	Status          string      `json:"status"`
	Notes           pgtype.Text `json:"notes"`
	CreatedBy       pgtype.Int8 `json:"created_by"`
	ApprovedBy      pgtype.Int8 `json:"approved_by"`
}

func (q *Queries) GetTransferByID(ctx context.Context, id int64) (GetTransferByIDRow, error) {
	row := q.db.QueryRow(ctx, getTransferByID, id)
	var i GetTransferByIDRow
	err := row.Scan(
		&i.ID,
		&i.TransferNumber,
		&i.FromWarehouseID,
		&i.ToWarehouseID,
		&i.TransferDate,
		&i.Status,
		&i.Notes,
		&i.CreatedBy,
		&i.ApprovedBy,
	)
	return i, err
}

const listMovements = `-- name: ListMovements :many
SELECT m.id, m.movement_number, m.product_id, m.warehouse_id, m.movement_type, m.quantity,
       m.quantity_before, m.quantity_after, m.reference_type, m.reference_id, m.notes, m.created_by, m.created_at,
       COALESCE(p.name, '') AS product_name, COALESCE(p.code, '') AS product_code,
       COALESCE(w.name, '') AS warehouse_name,
       CASE WHEN m.movement_type IN ('stock_in','transfer_in') THEN 'in'
            WHEN m.movement_type IN ('stock_out','transfer_out') THEN 'out'
            WHEN m.quantity < 0 THEN 'out'
            ELSE 'in' END AS direction
FROM stock_movements m
LEFT JOIN products p ON p.id = m.product_id
LEFT JOIN warehouses w ON w.id = m.warehouse_id
WHERE ($3::bigint IS NULL OR m.product_id = $3)
  AND ($4::text IS NULL OR m.movement_type = $4)
  AND ($5::bigint IS NULL OR m.warehouse_id = $5)
  AND ($6::date IS NULL OR m.created_at::date >= $6)
  AND ($7::date IS NULL OR m.created_at::date <= $7)
  AND ($8::text IS NULL OR (
       m.movement_number ILIKE '%' || $8 || '%'
       OR m.notes ILIKE '%' || $8 || '%'
       OR p.name ILIKE '%' || $8 || '%'
       OR p.code ILIKE '%' || $8 || '%'
  ))
ORDER BY m.id DESC
LIMIT $1 OFFSET $2
`

type ListMovementsParams struct {
	Limit       int32       `json:"limit"`
	Offset      int32       `json:"offset"`
	ProductID   pgtype.Int8 `json:"product_id"`
	Type        pgtype.Text `json:"type"`
	WarehouseID pgtype.Int8 `json:"warehouse_id"`
	DateFrom    pgtype.Date `json:"date_from"`
	DateTo      pgtype.Date `json:"date_to"`
	Search      pgtype.Text `json:"search"`
}

type ListMovementsRow struct {
	ID             int64              `json:"id"`
	MovementNumber string             `json:"movement_number"`
	ProductID      int64              `json:"product_id"`
	WarehouseID    int64              `json:"warehouse_id"`
	MovementType   string             `json:"movement_type"`
	Quantity       decimal.Decimal    `json:"quantity"`
	QuantityBefore decimal.Decimal    `json:"quantity_before"`
	QuantityAfter  decimal.Decimal    `json:"quantity_after"`
	ReferenceType  pgtype.Text        `json:"reference_type"`
	ReferenceID    pgtype.Int8        `json:"reference_id"`
	Notes          pgtype.Text        `json:"notes"`
	CreatedBy      pgtype.Int8        `json:"created_by"`
	CreatedAt      pgtype.Timestamptz `json:"created_at"`
	ProductName    string             `json:"product_name"`
	ProductCode    string             `json:"product_code"`
	WarehouseName  string             `json:"warehouse_name"`
	Direction      string             `json:"direction"`
}

func (q *Queries) ListMovements(ctx context.Context, arg ListMovementsParams) ([]ListMovementsRow, error) {
	rows, err := q.db.Query(ctx, listMovements,
		arg.Limit,
		arg.Offset,
		arg.ProductID,
		arg.Type,
		arg.WarehouseID,
		arg.DateFrom,
		arg.DateTo,
		arg.Search,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []ListMovementsRow
	for rows.Next() {
		var i ListMovementsRow
		if err := rows.Scan(
			&i.ID,
			&i.MovementNumber,
			&i.ProductID,
			&i.WarehouseID,
			&i.MovementType,
			&i.Quantity,
			&i.QuantityBefore,
			&i.QuantityAfter,
			&i.ReferenceType,
			&i.ReferenceID,
			&i.Notes,
			&i.CreatedBy,
			&i.CreatedAt,
			&i.ProductName,
			&i.ProductCode,
			&i.WarehouseName,
			&i.Direction,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const listOpnameItems = `-- name: ListOpnameItems :many
SELECT i.id, i.stock_opname_id, i.product_id, i.system_quantity, i.physical_quantity,
       i.notes, p.code, p.name, u.symbol AS unit_symbol
FROM stock_opname_items i
JOIN products p ON p.id = i.product_id
JOIN units u ON u.id = p.unit_id
WHERE i.stock_opname_id = $1 ORDER BY i.id
`

type ListOpnameItemsRow struct {
	ID               int64               `json:"id"`
	StockOpnameID    int64               `json:"stock_opname_id"`
	ProductID        int64               `json:"product_id"`
	SystemQuantity   decimal.Decimal     `json:"system_quantity"`
	PhysicalQuantity decimal.NullDecimal `json:"physical_quantity"`
	Notes            pgtype.Text         `json:"notes"`
	Code             string              `json:"code"`
	Name             string              `json:"name"`
	UnitSymbol       pgtype.Text         `json:"unit_symbol"`
}

func (q *Queries) ListOpnameItems(ctx context.Context, stockOpnameID int64) ([]ListOpnameItemsRow, error) {
	rows, err := q.db.Query(ctx, listOpnameItems, stockOpnameID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []ListOpnameItemsRow
	for rows.Next() {
		var i ListOpnameItemsRow
		if err := rows.Scan(
			&i.ID,
			&i.StockOpnameID,
			&i.ProductID,
			&i.SystemQuantity,
			&i.PhysicalQuantity,
			&i.Notes,
			&i.Code,
			&i.Name,
			&i.UnitSymbol,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const listOpnames = `-- name: ListOpnames :many
SELECT o.id, o.opname_number, o.warehouse_id, o.opname_date::text AS opname_date, o.status, o.notes, o.created_by, o.approved_by,
       COALESCE(w.name, '') AS warehouse_name,
       (SELECT COUNT(*) FROM stock_opname_items oi WHERE oi.stock_opname_id = o.id) AS item_count
FROM stock_opnames o
LEFT JOIN warehouses w ON w.id = o.warehouse_id
WHERE ($3::text IS NULL OR o.status = $3)
  AND ($4::bigint IS NULL OR o.warehouse_id = $4)
ORDER BY o.id DESC LIMIT $1 OFFSET $2
`

type ListOpnamesParams struct {
	Limit       int32       `json:"limit"`
	Offset      int32       `json:"offset"`
	Status      pgtype.Text `json:"status"`
	WarehouseID pgtype.Int8 `json:"warehouse_id"`
}

type ListOpnamesRow struct {
	ID            int64       `json:"id"`
	OpnameNumber  string      `json:"opname_number"`
	WarehouseID   int64       `json:"warehouse_id"`
	OpnameDate    string      `json:"opname_date"`
	Status        string      `json:"status"`
	Notes         pgtype.Text `json:"notes"`
	CreatedBy     pgtype.Int8 `json:"created_by"`
	ApprovedBy    pgtype.Int8 `json:"approved_by"`
	WarehouseName string      `json:"warehouse_name"`
	ItemCount     int64       `json:"item_count"`
}

func (q *Queries) ListOpnames(ctx context.Context, arg ListOpnamesParams) ([]ListOpnamesRow, error) {
	rows, err := q.db.Query(ctx, listOpnames,
		arg.Limit,
		arg.Offset,
		arg.Status,
		arg.WarehouseID,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []ListOpnamesRow
	for rows.Next() {
		var i ListOpnamesRow
		if err := rows.Scan(
			&i.ID,
			&i.OpnameNumber,
			&i.WarehouseID,
			&i.OpnameDate,
			&i.Status,
			&i.Notes,
			&i.CreatedBy,
			&i.ApprovedBy,
			&i.WarehouseName,
			&i.ItemCount,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const listStockLines = `-- name: ListStockLines :many
SELECT p.id, p.code, p.name, c.name AS category_name, u.symbol AS unit_symbol,
       COALESCE(s.qty, 0)::numeric(12,4) AS quantity, p.min_stock, p.is_active,
       COALESCE(s.reserved, 0)::numeric(12,4) AS reserved_quantity,
       p.purchase_price::numeric AS purchase_price,
       (COALESCE(s.qty, 0) * p.purchase_price)::numeric AS value,
       (SELECT COALESCE(jsonb_agg(jsonb_build_object('warehouse_id', st.warehouse_id, 'warehouse_name', w.name, 'quantity', st.quantity, 'reserved_quantity', st.reserved_quantity)) FILTER (WHERE st.warehouse_id IS NOT NULL), '[]'::jsonb)
        FROM stocks st
        JOIN warehouses w ON w.id = st.warehouse_id
        JOIN stores str ON str.warehouse_id = w.id
        WHERE st.product_id = p.id
          AND w.is_active = true
          AND str.is_active = true
          AND ($3::bigint IS NULL OR st.warehouse_id = $3::bigint)) AS outlets
FROM products p
JOIN categories c ON c.id = p.category_id
JOIN units u ON u.id = p.unit_id
LEFT JOIN (
    SELECT st.product_id, SUM(GREATEST(0, st.quantity)) AS qty, SUM(st.reserved_quantity) AS reserved
    FROM stocks st
    JOIN warehouses w ON w.id = st.warehouse_id
    JOIN stores str ON str.warehouse_id = w.id
    WHERE w.is_active = true
      AND str.is_active = true
      AND ($3::bigint IS NULL OR st.warehouse_id = $3::bigint)
    GROUP BY st.product_id
) s ON s.product_id = p.id
WHERE p.deleted_at IS NULL
  AND ($4::text IS NULL OR (p.code ILIKE '%' || $4 || '%' OR p.name ILIKE '%' || $4 || '%'))
  AND ($5::bool IS NOT TRUE OR s.qty IS NULL OR s.qty <= p.min_stock)
ORDER BY
  CASE WHEN $6::text = 'product_name' AND ($7::text = 'asc' OR $7::text IS NULL) THEN p.name END ASC,
  CASE WHEN $6::text = 'product_name' AND $7::text = 'desc' THEN p.name END DESC,
  CASE WHEN $6::text = 'product_code' AND ($7::text = 'asc' OR $7::text IS NULL) THEN p.code END ASC,
  CASE WHEN $6::text = 'product_code' AND $7::text = 'desc' THEN p.code END DESC,
  CASE WHEN $6::text = 'category_name' AND ($7::text = 'asc' OR $7::text IS NULL) THEN c.name END ASC,
  CASE WHEN $6::text = 'category_name' AND $7::text = 'desc' THEN c.name END DESC,
  CASE WHEN $6::text = 'quantity' AND ($7::text = 'asc' OR $7::text IS NULL) THEN COALESCE(s.qty, 0) END ASC,
  CASE WHEN $6::text = 'quantity' AND $7::text = 'desc' THEN COALESCE(s.qty, 0) END DESC,
  CASE WHEN $6::text = 'min_stock' AND ($7::text = 'asc' OR $7::text IS NULL) THEN p.min_stock END ASC,
  CASE WHEN $6::text = 'min_stock' AND $7::text = 'desc' THEN p.min_stock END DESC,
  CASE WHEN $6::text = 'purchase_price' AND ($7::text = 'asc' OR $7::text IS NULL) THEN p.purchase_price END ASC,
  CASE WHEN $6::text = 'purchase_price' AND $7::text = 'desc' THEN p.purchase_price END DESC,
  CASE WHEN $6::text = 'value' AND ($7::text = 'asc' OR $7::text IS NULL) THEN (COALESCE(s.qty, 0) * p.purchase_price) END ASC,
  CASE WHEN $6::text = 'value' AND $7::text = 'desc' THEN (COALESCE(s.qty, 0) * p.purchase_price) END DESC,
  CASE WHEN $6::text = 'is_low_stock' AND ($7::text = 'asc' OR $7::text IS NULL) THEN (CASE WHEN COALESCE(s.qty, 0) <= p.min_stock THEN 1 ELSE 0 END) END ASC,
  CASE WHEN $6::text = 'is_low_stock' AND $7::text = 'desc' THEN (CASE WHEN COALESCE(s.qty, 0) <= p.min_stock THEN 1 ELSE 0 END) END DESC,
  p.name ASC,
  p.id DESC
LIMIT $1 OFFSET $2
`

type ListStockLinesParams struct {
	Limit       int32       `json:"limit"`
	Offset      int32       `json:"offset"`
	WarehouseID pgtype.Int8 `json:"warehouse_id"`
	Search      pgtype.Text `json:"search"`
	LowStock    pgtype.Bool `json:"low_stock"`
	SortBy      pgtype.Text `json:"sort_by"`
	SortDir     pgtype.Text `json:"sort_dir"`
}

type ListStockLinesRow struct {
	ID               int64           `json:"id"`
	Code             string          `json:"code"`
	Name             string          `json:"name"`
	CategoryName     string          `json:"category_name"`
	UnitSymbol       pgtype.Text     `json:"unit_symbol"`
	Quantity         decimal.Decimal `json:"quantity"`
	MinStock         decimal.Decimal `json:"min_stock"`
	IsActive         bool            `json:"is_active"`
	ReservedQuantity decimal.Decimal `json:"reserved_quantity"`
	PurchasePrice    decimal.Decimal `json:"purchase_price"`
	Value            decimal.Decimal `json:"value"`
	Outlets          interface{}     `json:"outlets"`
}

func (q *Queries) ListStockLines(ctx context.Context, arg ListStockLinesParams) ([]ListStockLinesRow, error) {
	rows, err := q.db.Query(ctx, listStockLines,
		arg.Limit,
		arg.Offset,
		arg.WarehouseID,
		arg.Search,
		arg.LowStock,
		arg.SortBy,
		arg.SortDir,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []ListStockLinesRow
	for rows.Next() {
		var i ListStockLinesRow
		if err := rows.Scan(
			&i.ID,
			&i.Code,
			&i.Name,
			&i.CategoryName,
			&i.UnitSymbol,
			&i.Quantity,
			&i.MinStock,
			&i.IsActive,
			&i.ReservedQuantity,
			&i.PurchasePrice,
			&i.Value,
			&i.Outlets,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const listStockLinesByWarehouse = `-- name: ListStockLinesByWarehouse :many
SELECT p.id, p.code, p.name, c.name AS category_name, u.symbol AS unit_symbol,
       COALESCE(s.quantity, 0)::numeric(12,4) AS quantity, p.min_stock, p.is_active,
       COALESCE(s.reserved_quantity, 0)::numeric(12,4) AS reserved_quantity,
       p.purchase_price::numeric AS purchase_price,
       (COALESCE(s.quantity, 0) * p.purchase_price)::numeric AS value
FROM products p
JOIN categories c ON c.id = p.category_id
JOIN units u ON u.id = p.unit_id
LEFT JOIN stocks s ON s.product_id = p.id AND s.warehouse_id = $3::bigint
WHERE p.deleted_at IS NULL
  AND EXISTS (
      SELECT 1 FROM warehouses w
      JOIN stores str ON str.warehouse_id = w.id
      WHERE w.id = $3::bigint AND w.is_active = true AND str.is_active = true
  )
  AND ($4::text IS NULL OR (p.code ILIKE '%' || $4 || '%' OR p.name ILIKE '%' || $4 || '%'))
  AND ($5::bool IS NOT TRUE OR s.quantity IS NULL OR s.quantity <= p.min_stock)
ORDER BY
  CASE WHEN $6::text = 'product_name' AND ($7::text = 'asc' OR $7::text IS NULL) THEN p.name END ASC,
  CASE WHEN $6::text = 'product_name' AND $7::text = 'desc' THEN p.name END DESC,
  CASE WHEN $6::text = 'product_code' AND ($7::text = 'asc' OR $7::text IS NULL) THEN p.code END ASC,
  CASE WHEN $6::text = 'product_code' AND $7::text = 'desc' THEN p.code END DESC,
  CASE WHEN $6::text = 'category_name' AND ($7::text = 'asc' OR $7::text IS NULL) THEN c.name END ASC,
  CASE WHEN $6::text = 'category_name' AND $7::text = 'desc' THEN c.name END DESC,
  CASE WHEN $6::text = 'quantity' AND ($7::text = 'asc' OR $7::text IS NULL) THEN COALESCE(s.quantity, 0) END ASC,
  CASE WHEN $6::text = 'quantity' AND $7::text = 'desc' THEN COALESCE(s.quantity, 0) END DESC,
  CASE WHEN $6::text = 'min_stock' AND ($7::text = 'asc' OR $7::text IS NULL) THEN p.min_stock END ASC,
  CASE WHEN $6::text = 'min_stock' AND $7::text = 'desc' THEN p.min_stock END DESC,
  CASE WHEN $6::text = 'purchase_price' AND ($7::text = 'asc' OR $7::text IS NULL) THEN p.purchase_price END ASC,
  CASE WHEN $6::text = 'purchase_price' AND $7::text = 'desc' THEN p.purchase_price END DESC,
  CASE WHEN $6::text = 'value' AND ($7::text = 'asc' OR $7::text IS NULL) THEN (COALESCE(s.quantity, 0) * p.purchase_price) END ASC,
  CASE WHEN $6::text = 'value' AND $7::text = 'desc' THEN (COALESCE(s.quantity, 0) * p.purchase_price) END DESC,
  CASE WHEN $6::text = 'is_low_stock' AND ($7::text = 'asc' OR $7::text IS NULL) THEN (CASE WHEN COALESCE(s.quantity, 0) <= p.min_stock THEN 1 ELSE 0 END) END ASC,
  CASE WHEN $6::text = 'is_low_stock' AND $7::text = 'desc' THEN (CASE WHEN COALESCE(s.quantity, 0) <= p.min_stock THEN 1 ELSE 0 END) END DESC,
  p.name ASC,
  p.id DESC
LIMIT $1 OFFSET $2
`

type ListStockLinesByWarehouseParams struct {
	Limit    int32       `json:"limit"`
	Offset   int32       `json:"offset"`
	Column3  int64       `json:"column_3"`
	Search   pgtype.Text `json:"search"`
	LowStock pgtype.Bool `json:"low_stock"`
	SortBy   pgtype.Text `json:"sort_by"`
	SortDir  pgtype.Text `json:"sort_dir"`
}

type ListStockLinesByWarehouseRow struct {
	ID               int64           `json:"id"`
	Code             string          `json:"code"`
	Name             string          `json:"name"`
	CategoryName     string          `json:"category_name"`
	UnitSymbol       pgtype.Text     `json:"unit_symbol"`
	Quantity         decimal.Decimal `json:"quantity"`
	MinStock         decimal.Decimal `json:"min_stock"`
	IsActive         bool            `json:"is_active"`
	ReservedQuantity decimal.Decimal `json:"reserved_quantity"`
	PurchasePrice    decimal.Decimal `json:"purchase_price"`
	Value            decimal.Decimal `json:"value"`
}

func (q *Queries) ListStockLinesByWarehouse(ctx context.Context, arg ListStockLinesByWarehouseParams) ([]ListStockLinesByWarehouseRow, error) {
	rows, err := q.db.Query(ctx, listStockLinesByWarehouse,
		arg.Limit,
		arg.Offset,
		arg.Column3,
		arg.Search,
		arg.LowStock,
		arg.SortBy,
		arg.SortDir,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []ListStockLinesByWarehouseRow
	for rows.Next() {
		var i ListStockLinesByWarehouseRow
		if err := rows.Scan(
			&i.ID,
			&i.Code,
			&i.Name,
			&i.CategoryName,
			&i.UnitSymbol,
			&i.Quantity,
			&i.MinStock,
			&i.IsActive,
			&i.ReservedQuantity,
			&i.PurchasePrice,
			&i.Value,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const listTransferItems = `-- name: ListTransferItems :many
SELECT ti.id, ti.stock_transfer_id, ti.product_id, ti.quantity_requested, ti.quantity_sent, ti.quantity_received,
       p.code AS product_code, p.name AS product_name, u.symbol AS unit_symbol
FROM stock_transfer_items ti
JOIN products p ON p.id = ti.product_id
JOIN units u ON u.id = p.unit_id
WHERE ti.stock_transfer_id = $1 ORDER BY ti.id
`

type ListTransferItemsRow struct {
	ID                int64           `json:"id"`
	StockTransferID   int64           `json:"stock_transfer_id"`
	ProductID         int64           `json:"product_id"`
	QuantityRequested decimal.Decimal `json:"quantity_requested"`
	QuantitySent      decimal.Decimal `json:"quantity_sent"`
	QuantityReceived  decimal.Decimal `json:"quantity_received"`
	ProductCode       string          `json:"product_code"`
	ProductName       string          `json:"product_name"`
	UnitSymbol        pgtype.Text     `json:"unit_symbol"`
}

func (q *Queries) ListTransferItems(ctx context.Context, stockTransferID int64) ([]ListTransferItemsRow, error) {
	rows, err := q.db.Query(ctx, listTransferItems, stockTransferID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []ListTransferItemsRow
	for rows.Next() {
		var i ListTransferItemsRow
		if err := rows.Scan(
			&i.ID,
			&i.StockTransferID,
			&i.ProductID,
			&i.QuantityRequested,
			&i.QuantitySent,
			&i.QuantityReceived,
			&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 listTransfers = `-- name: ListTransfers :many
SELECT t.id, t.transfer_number, t.from_warehouse_id, t.to_warehouse_id, t.transfer_date::text AS transfer_date, t.status, t.notes, t.created_by, t.approved_by,
       COALESCE(wf.name, '') AS from_warehouse_name, COALESCE(wt.name, '') AS to_warehouse_name,
       (SELECT COUNT(*) FROM stock_transfer_items ti WHERE ti.stock_transfer_id = t.id) AS item_count
FROM stock_transfers t
LEFT JOIN warehouses wf ON wf.id = t.from_warehouse_id
LEFT JOIN warehouses wt ON wt.id = t.to_warehouse_id
WHERE ($3::text IS NULL OR t.status = $3)
ORDER BY t.id DESC LIMIT $1 OFFSET $2
`

type ListTransfersParams struct {
	Limit  int32       `json:"limit"`
	Offset int32       `json:"offset"`
	Status pgtype.Text `json:"status"`
}

type ListTransfersRow struct {
	ID                int64       `json:"id"`
	TransferNumber    string      `json:"transfer_number"`
	FromWarehouseID   int64       `json:"from_warehouse_id"`
	ToWarehouseID     int64       `json:"to_warehouse_id"`
	TransferDate      string      `json:"transfer_date"`
	Status            string      `json:"status"`
	Notes             pgtype.Text `json:"notes"`
	CreatedBy         pgtype.Int8 `json:"created_by"`
	ApprovedBy        pgtype.Int8 `json:"approved_by"`
	FromWarehouseName string      `json:"from_warehouse_name"`
	ToWarehouseName   string      `json:"to_warehouse_name"`
	ItemCount         int64       `json:"item_count"`
}

func (q *Queries) ListTransfers(ctx context.Context, arg ListTransfersParams) ([]ListTransfersRow, error) {
	rows, err := q.db.Query(ctx, listTransfers, arg.Limit, arg.Offset, arg.Status)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []ListTransfersRow
	for rows.Next() {
		var i ListTransfersRow
		if err := rows.Scan(
			&i.ID,
			&i.TransferNumber,
			&i.FromWarehouseID,
			&i.ToWarehouseID,
			&i.TransferDate,
			&i.Status,
			&i.Notes,
			&i.CreatedBy,
			&i.ApprovedBy,
			&i.FromWarehouseName,
			&i.ToWarehouseName,
			&i.ItemCount,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const lockOpnameByID = `-- name: LockOpnameByID :one
SELECT id, opname_number, warehouse_id, opname_date::text, status, notes, created_by, approved_by
FROM stock_opnames WHERE id=$1 FOR UPDATE
`

type LockOpnameByIDRow struct {
	ID           int64       `json:"id"`
	OpnameNumber string      `json:"opname_number"`
	WarehouseID  int64       `json:"warehouse_id"`
	OpnameDate   string      `json:"opname_date"`
	Status       string      `json:"status"`
	Notes        pgtype.Text `json:"notes"`
	CreatedBy    pgtype.Int8 `json:"created_by"`
	ApprovedBy   pgtype.Int8 `json:"approved_by"`
}

func (q *Queries) LockOpnameByID(ctx context.Context, id int64) (LockOpnameByIDRow, error) {
	row := q.db.QueryRow(ctx, lockOpnameByID, id)
	var i LockOpnameByIDRow
	err := row.Scan(
		&i.ID,
		&i.OpnameNumber,
		&i.WarehouseID,
		&i.OpnameDate,
		&i.Status,
		&i.Notes,
		&i.CreatedBy,
		&i.ApprovedBy,
	)
	return i, err
}

const setOpnameItemPhysical = `-- name: SetOpnameItemPhysical :execrows
UPDATE stock_opname_items SET physical_quantity = $2 WHERE stock_opname_id = $1 AND product_id = $3
`

type SetOpnameItemPhysicalParams struct {
	StockOpnameID    int64               `json:"stock_opname_id"`
	PhysicalQuantity decimal.NullDecimal `json:"physical_quantity"`
	ProductID        int64               `json:"product_id"`
}

func (q *Queries) SetOpnameItemPhysical(ctx context.Context, arg SetOpnameItemPhysicalParams) (int64, error) {
	result, err := q.db.Exec(ctx, setOpnameItemPhysical, arg.StockOpnameID, arg.PhysicalQuantity, arg.ProductID)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected(), nil
}

const updateOpnameStatus = `-- name: UpdateOpnameStatus :execrows
UPDATE stock_opnames SET status = $2 WHERE id = $1
`

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

func (q *Queries) UpdateOpnameStatus(ctx context.Context, arg UpdateOpnameStatusParams) (int64, error) {
	result, err := q.db.Exec(ctx, updateOpnameStatus, arg.ID, arg.Status)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected(), nil
}

const updateStockRow = `-- name: UpdateStockRow :execrows
UPDATE stocks SET quantity=$2, reserved_quantity=$3, last_updated_at=now(), updated_at=now() WHERE id=$1
`

type UpdateStockRowParams struct {
	ID               int64           `json:"id"`
	Quantity         decimal.Decimal `json:"quantity"`
	ReservedQuantity decimal.Decimal `json:"reserved_quantity"`
}

func (q *Queries) UpdateStockRow(ctx context.Context, arg UpdateStockRowParams) (int64, error) {
	result, err := q.db.Exec(ctx, updateStockRow, arg.ID, arg.Quantity, arg.ReservedQuantity)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected(), nil
}

const warehouseStockFrozen = `-- name: WarehouseStockFrozen :one
SELECT EXISTS (
  SELECT 1 FROM stock_opnames
  WHERE warehouse_id = $1 AND status = 'in_progress'
) AS frozen
`

// TRUE while an opname for this warehouse is still open. The ERP's
// "Terbitkan & Kunci Stok" freeze is only real if movements consult this.
func (q *Queries) WarehouseStockFrozen(ctx context.Context, warehouseID int64) (bool, error) {
	row := q.db.QueryRow(ctx, warehouseStockFrozen, warehouseID)
	var frozen bool
	err := row.Scan(&frozen)
	return frozen, err
}