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

package db

import (
	"context"

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

const addReceivedQuantity = `-- name: AddReceivedQuantity :execrows
UPDATE purchase_order_items SET received_quantity = received_quantity + $2 WHERE id=$1
`

type AddReceivedQuantityParams struct {
	ID               int64           `json:"id"`
	ReceivedQuantity decimal.Decimal `json:"received_quantity"`
}

func (q *Queries) AddReceivedQuantity(ctx context.Context, arg AddReceivedQuantityParams) (int64, error) {
	result, err := q.db.Exec(ctx, addReceivedQuantity, arg.ID, arg.ReceivedQuantity)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected(), nil
}

const approvePurchaseOrder = `-- name: ApprovePurchaseOrder :execrows
UPDATE purchase_orders
SET status='approved', approved_by=$2, approved_at=now(), updated_at=now()
WHERE id=$1 AND status='draft'
`

type ApprovePurchaseOrderParams struct {
	ID         int64       `json:"id"`
	ApprovedBy pgtype.Int8 `json:"approved_by"`
}

// Records the approver identity alongside the status flip so the printable
// document can show a formal approval block (client feedback 2026-08-26).
func (q *Queries) ApprovePurchaseOrder(ctx context.Context, arg ApprovePurchaseOrderParams) (int64, error) {
	result, err := q.db.Exec(ctx, approvePurchaseOrder, arg.ID, arg.ApprovedBy)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected(), nil
}

const countDebts = `-- name: CountDebts :one
SELECT count(*) FROM purchase_orders po
JOIN suppliers s ON s.id = po.supplier_id
WHERE po.status IN ('received','partial')
  AND ($1::bigint IS NULL OR $1::bigint <= 0 OR po.supplier_id = $1)
  AND ($2::date IS NULL OR po.due_date <= $2)
  AND ($3::text IS NULL OR (po.po_number ILIKE '%' || $3 || '%' OR s.name ILIKE '%' || $3 || '%'))
  AND (
    $4::text IS NULL
    OR $4::text = ''
    OR $4::text = 'all'
    OR ($4::text = 'unpaid' AND po.remaining_amount > 0)
    OR ($4::text = 'paid' AND po.remaining_amount = 0)
  )
`

type CountDebtsParams struct {
	SupplierID pgtype.Int8 `json:"supplier_id"`
	DueDate    pgtype.Date `json:"due_date"`
	Search     pgtype.Text `json:"search"`
	DebtStatus pgtype.Text `json:"debt_status"`
}

func (q *Queries) CountDebts(ctx context.Context, arg CountDebtsParams) (int64, error) {
	row := q.db.QueryRow(ctx, countDebts,
		arg.SupplierID,
		arg.DueDate,
		arg.Search,
		arg.DebtStatus,
	)
	var count int64
	err := row.Scan(&count)
	return count, err
}

const countGoodsReceipts = `-- name: CountGoodsReceipts :one
SELECT count(*) FROM goods_receipts
WHERE ($1::text IS NULL OR status = $1)
`

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

const countPurchaseOrders = `-- name: CountPurchaseOrders :one
SELECT count(*) FROM purchase_orders
WHERE ($1::text IS NULL OR (po_number ILIKE '%' || $1 || '%' OR supplier_id IN (SELECT id FROM suppliers WHERE name ILIKE '%' || $1 || '%')))
  AND ($2::text IS NULL OR status = $2)
  AND ($3::bigint IS NULL OR $3::bigint <= 0 OR supplier_id = $3)
`

type CountPurchaseOrdersParams struct {
	Search     pgtype.Text `json:"search"`
	Status     pgtype.Text `json:"status"`
	SupplierID pgtype.Int8 `json:"supplier_id"`
}

func (q *Queries) CountPurchaseOrders(ctx context.Context, arg CountPurchaseOrdersParams) (int64, error) {
	row := q.db.QueryRow(ctx, countPurchaseOrders, arg.Search, arg.Status, arg.SupplierID)
	var count int64
	err := row.Scan(&count)
	return count, err
}

const countSuppliersActive = `-- name: CountSuppliersActive :one
SELECT count(*) FROM suppliers WHERE id=$1 AND deleted_at IS NULL
`

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

const countVouchers = `-- name: CountVouchers :one
SELECT count(*) FROM vouchers
WHERE ($1::text IS NULL OR status = $1)
`

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

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

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

const createGoodsReceipt = `-- name: CreateGoodsReceipt :one
INSERT INTO goods_receipts (gr_number, purchase_order_id, warehouse_id, received_by, received_date, notes, status)
VALUES ($1,$2,$3,$4,$5,$6,$7) RETURNING id, created_at
`

type CreateGoodsReceiptParams struct {
	GrNumber        string      `json:"gr_number"`
	PurchaseOrderID int64       `json:"purchase_order_id"`
	WarehouseID     int64       `json:"warehouse_id"`
	ReceivedBy      pgtype.Int8 `json:"received_by"`
	ReceivedDate    pgtype.Date `json:"received_date"`
	Notes           pgtype.Text `json:"notes"`
	Status          string      `json:"status"`
}

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

func (q *Queries) CreateGoodsReceipt(ctx context.Context, arg CreateGoodsReceiptParams) (CreateGoodsReceiptRow, error) {
	row := q.db.QueryRow(ctx, createGoodsReceipt,
		arg.GrNumber,
		arg.PurchaseOrderID,
		arg.WarehouseID,
		arg.ReceivedBy,
		arg.ReceivedDate,
		arg.Notes,
		arg.Status,
	)
	var i CreateGoodsReceiptRow
	err := row.Scan(&i.ID, &i.CreatedAt)
	return i, err
}

const createGoodsReceiptItem = `-- name: CreateGoodsReceiptItem :one
INSERT INTO goods_receipt_items (goods_receipt_id, purchase_order_item_id, product_id,
       quantity_ordered, quantity_received, quantity_rejected, batch_number, expired_date, defect_notes)
VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9) RETURNING id
`

type CreateGoodsReceiptItemParams struct {
	GoodsReceiptID      int64           `json:"goods_receipt_id"`
	PurchaseOrderItemID pgtype.Int8     `json:"purchase_order_item_id"`
	ProductID           int64           `json:"product_id"`
	QuantityOrdered     decimal.Decimal `json:"quantity_ordered"`
	QuantityReceived    decimal.Decimal `json:"quantity_received"`
	QuantityRejected    decimal.Decimal `json:"quantity_rejected"`
	BatchNumber         pgtype.Text     `json:"batch_number"`
	ExpiredDate         pgtype.Date     `json:"expired_date"`
	DefectNotes         pgtype.Text     `json:"defect_notes"`
}

// defect_notes rides with the structured rejection (000147): a GR that rejects
// units must say WHY, or the drilldown shows a number with no reason. Updated
// together with quantity_rejected — the migration added the column but this
// INSERT was never extended, silently dropping the ERP's notes (7-B).
func (q *Queries) CreateGoodsReceiptItem(ctx context.Context, arg CreateGoodsReceiptItemParams) (int64, error) {
	row := q.db.QueryRow(ctx, createGoodsReceiptItem,
		arg.GoodsReceiptID,
		arg.PurchaseOrderItemID,
		arg.ProductID,
		arg.QuantityOrdered,
		arg.QuantityReceived,
		arg.QuantityRejected,
		arg.BatchNumber,
		arg.ExpiredDate,
		arg.DefectNotes,
	)
	var id int64
	err := row.Scan(&id)
	return id, err
}

const createPurchaseCashflow = `-- name: CreatePurchaseCashflow :one
INSERT INTO cashflows (transaction_number, type, category, transaction_date, amount, description, reference_type, reference_id, created_by)
VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9) RETURNING id
`

type CreatePurchaseCashflowParams struct {
	TransactionNumber string          `json:"transaction_number"`
	Type              string          `json:"type"`
	Category          string          `json:"category"`
	TransactionDate   pgtype.Date     `json:"transaction_date"`
	Amount            decimal.Decimal `json:"amount"`
	Description       pgtype.Text     `json:"description"`
	ReferenceType     pgtype.Text     `json:"reference_type"`
	ReferenceID       pgtype.Int8     `json:"reference_id"`
	CreatedBy         pgtype.Int8     `json:"created_by"`
}

func (q *Queries) CreatePurchaseCashflow(ctx context.Context, arg CreatePurchaseCashflowParams) (int64, error) {
	row := q.db.QueryRow(ctx, createPurchaseCashflow,
		arg.TransactionNumber,
		arg.Type,
		arg.Category,
		arg.TransactionDate,
		arg.Amount,
		arg.Description,
		arg.ReferenceType,
		arg.ReferenceID,
		arg.CreatedBy,
	)
	var id int64
	err := row.Scan(&id)
	return id, err
}

const createPurchaseOrder = `-- name: CreatePurchaseOrder :one
INSERT INTO purchase_orders (po_number, supplier_id, warehouse_id, created_by, order_date,
       expected_date, due_date, status, subtotal, tax_amount, discount_amount, total_amount,
       paid_amount, remaining_amount, notes)
VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15)
RETURNING id, created_at, updated_at
`

type CreatePurchaseOrderParams struct {
	PoNumber        string          `json:"po_number"`
	SupplierID      int64           `json:"supplier_id"`
	WarehouseID     int64           `json:"warehouse_id"`
	CreatedBy       pgtype.Int8     `json:"created_by"`
	OrderDate       pgtype.Date     `json:"order_date"`
	ExpectedDate    pgtype.Date     `json:"expected_date"`
	DueDate         pgtype.Date     `json:"due_date"`
	Status          string          `json:"status"`
	Subtotal        decimal.Decimal `json:"subtotal"`
	TaxAmount       decimal.Decimal `json:"tax_amount"`
	DiscountAmount  decimal.Decimal `json:"discount_amount"`
	TotalAmount     decimal.Decimal `json:"total_amount"`
	PaidAmount      decimal.Decimal `json:"paid_amount"`
	RemainingAmount decimal.Decimal `json:"remaining_amount"`
	Notes           pgtype.Text     `json:"notes"`
}

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

func (q *Queries) CreatePurchaseOrder(ctx context.Context, arg CreatePurchaseOrderParams) (CreatePurchaseOrderRow, error) {
	row := q.db.QueryRow(ctx, createPurchaseOrder,
		arg.PoNumber,
		arg.SupplierID,
		arg.WarehouseID,
		arg.CreatedBy,
		arg.OrderDate,
		arg.ExpectedDate,
		arg.DueDate,
		arg.Status,
		arg.Subtotal,
		arg.TaxAmount,
		arg.DiscountAmount,
		arg.TotalAmount,
		arg.PaidAmount,
		arg.RemainingAmount,
		arg.Notes,
	)
	var i CreatePurchaseOrderRow
	err := row.Scan(&i.ID, &i.CreatedAt, &i.UpdatedAt)
	return i, err
}

const createPurchaseOrderItem = `-- name: CreatePurchaseOrderItem :one
INSERT INTO purchase_order_items (purchase_order_id, product_id, quantity, received_quantity, unit_price, discount, subtotal)
VALUES ($1,$2,$3,0,$4,$5,$6) RETURNING id
`

type CreatePurchaseOrderItemParams struct {
	PurchaseOrderID int64           `json:"purchase_order_id"`
	ProductID       int64           `json:"product_id"`
	Quantity        decimal.Decimal `json:"quantity"`
	UnitPrice       decimal.Decimal `json:"unit_price"`
	Discount        decimal.Decimal `json:"discount"`
	Subtotal        decimal.Decimal `json:"subtotal"`
}

func (q *Queries) CreatePurchaseOrderItem(ctx context.Context, arg CreatePurchaseOrderItemParams) (int64, error) {
	row := q.db.QueryRow(ctx, createPurchaseOrderItem,
		arg.PurchaseOrderID,
		arg.ProductID,
		arg.Quantity,
		arg.UnitPrice,
		arg.Discount,
		arg.Subtotal,
	)
	var id int64
	err := row.Scan(&id)
	return id, err
}

const createVoucher = `-- name: CreateVoucher :one
INSERT INTO vouchers (voucher_number, type, description, voucher_date, amount, status, created_by,
       supplier_id, po_id, cash_account_code)
VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10) RETURNING id
`

type CreateVoucherParams struct {
	VoucherNumber   string          `json:"voucher_number"`
	Type            string          `json:"type"`
	Description     pgtype.Text     `json:"description"`
	VoucherDate     pgtype.Date     `json:"voucher_date"`
	Amount          decimal.Decimal `json:"amount"`
	Status          string          `json:"status"`
	CreatedBy       pgtype.Int8     `json:"created_by"`
	SupplierID      pgtype.Int8     `json:"supplier_id"`
	PoID            pgtype.Int8     `json:"po_id"`
	CashAccountCode pgtype.Text     `json:"cash_account_code"`
}

func (q *Queries) CreateVoucher(ctx context.Context, arg CreateVoucherParams) (int64, error) {
	row := q.db.QueryRow(ctx, createVoucher,
		arg.VoucherNumber,
		arg.Type,
		arg.Description,
		arg.VoucherDate,
		arg.Amount,
		arg.Status,
		arg.CreatedBy,
		arg.SupplierID,
		arg.PoID,
		arg.CashAccountCode,
	)
	var id int64
	err := row.Scan(&id)
	return id, err
}

const deletePurchaseOrder = `-- name: DeletePurchaseOrder :execrows
DELETE FROM purchase_orders WHERE id=$1
`

func (q *Queries) DeletePurchaseOrder(ctx context.Context, id int64) (int64, error) {
	result, err := q.db.Exec(ctx, deletePurchaseOrder, id)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected(), nil
}

const deletePurchaseOrderItems = `-- name: DeletePurchaseOrderItems :exec
DELETE FROM purchase_order_items WHERE purchase_order_id=$1
`

func (q *Queries) DeletePurchaseOrderItems(ctx context.Context, purchaseOrderID int64) error {
	_, err := q.db.Exec(ctx, deletePurchaseOrderItems, purchaseOrderID)
	return err
}

const getGoodsReceiptByID = `-- name: GetGoodsReceiptByID :one
SELECT id, gr_number, purchase_order_id, warehouse_id, received_by, received_date::text, notes, status, created_at
FROM goods_receipts WHERE id=$1
`

type GetGoodsReceiptByIDRow struct {
	ID              int64              `json:"id"`
	GrNumber        string             `json:"gr_number"`
	PurchaseOrderID int64              `json:"purchase_order_id"`
	WarehouseID     int64              `json:"warehouse_id"`
	ReceivedBy      pgtype.Int8        `json:"received_by"`
	ReceivedDate    string             `json:"received_date"`
	Notes           pgtype.Text        `json:"notes"`
	Status          string             `json:"status"`
	CreatedAt       pgtype.Timestamptz `json:"created_at"`
}

func (q *Queries) GetGoodsReceiptByID(ctx context.Context, id int64) (GetGoodsReceiptByIDRow, error) {
	row := q.db.QueryRow(ctx, getGoodsReceiptByID, id)
	var i GetGoodsReceiptByIDRow
	err := row.Scan(
		&i.ID,
		&i.GrNumber,
		&i.PurchaseOrderID,
		&i.WarehouseID,
		&i.ReceivedBy,
		&i.ReceivedDate,
		&i.Notes,
		&i.Status,
		&i.CreatedAt,
	)
	return i, err
}

const getPurchaseOrderByID = `-- name: GetPurchaseOrderByID :one
SELECT po.id, po.po_number, po.supplier_id, po.warehouse_id, po.created_by, po.order_date::text AS order_date,
       COALESCE(po.expected_date::text, '')::text AS expected_date,
       COALESCE(po.due_date::text, '')::text AS due_date,
       po.status, po.subtotal, po.tax_amount, po.discount_amount, po.total_amount, po.paid_amount, po.remaining_amount,
       po.notes, po.created_at, po.updated_at,
       po.approved_by, po.approved_at,
       po.printed_at,
       po.printed_by,
       pu.name AS printed_by_name,
       po.print_count,
       sup.name AS supplier_name, sup.address AS supplier_address, sup.phone AS supplier_phone, sup.email AS supplier_email,
       w.name AS warehouse_name,
       cu.name AS created_by_name,
       au.name AS approved_by_name
FROM purchase_orders po
LEFT JOIN suppliers sup ON sup.id = po.supplier_id
LEFT JOIN warehouses w ON w.id = po.warehouse_id
LEFT JOIN users cu ON cu.id = po.created_by
LEFT JOIN users au ON au.id = po.approved_by
LEFT JOIN users pu ON pu.id = po.printed_by
WHERE po.id=$1
`

type GetPurchaseOrderByIDRow struct {
	ID              int64              `json:"id"`
	PoNumber        string             `json:"po_number"`
	SupplierID      int64              `json:"supplier_id"`
	WarehouseID     int64              `json:"warehouse_id"`
	CreatedBy       pgtype.Int8        `json:"created_by"`
	OrderDate       string             `json:"order_date"`
	ExpectedDate    string             `json:"expected_date"`
	DueDate         string             `json:"due_date"`
	Status          string             `json:"status"`
	Subtotal        decimal.Decimal    `json:"subtotal"`
	TaxAmount       decimal.Decimal    `json:"tax_amount"`
	DiscountAmount  decimal.Decimal    `json:"discount_amount"`
	TotalAmount     decimal.Decimal    `json:"total_amount"`
	PaidAmount      decimal.Decimal    `json:"paid_amount"`
	RemainingAmount decimal.Decimal    `json:"remaining_amount"`
	Notes           pgtype.Text        `json:"notes"`
	CreatedAt       pgtype.Timestamptz `json:"created_at"`
	UpdatedAt       pgtype.Timestamptz `json:"updated_at"`
	ApprovedBy      pgtype.Int8        `json:"approved_by"`
	ApprovedAt      pgtype.Timestamptz `json:"approved_at"`
	PrintedAt       pgtype.Timestamptz `json:"printed_at"`
	PrintedBy       pgtype.Int8        `json:"printed_by"`
	PrintedByName   pgtype.Text        `json:"printed_by_name"`
	PrintCount      int32              `json:"print_count"`
	SupplierName    pgtype.Text        `json:"supplier_name"`
	SupplierAddress pgtype.Text        `json:"supplier_address"`
	SupplierPhone   pgtype.Text        `json:"supplier_phone"`
	SupplierEmail   pgtype.Text        `json:"supplier_email"`
	WarehouseName   pgtype.Text        `json:"warehouse_name"`
	CreatedByName   pgtype.Text        `json:"created_by_name"`
	ApprovedByName  pgtype.Text        `json:"approved_by_name"`
}

func (q *Queries) GetPurchaseOrderByID(ctx context.Context, id int64) (GetPurchaseOrderByIDRow, error) {
	row := q.db.QueryRow(ctx, getPurchaseOrderByID, id)
	var i GetPurchaseOrderByIDRow
	err := row.Scan(
		&i.ID,
		&i.PoNumber,
		&i.SupplierID,
		&i.WarehouseID,
		&i.CreatedBy,
		&i.OrderDate,
		&i.ExpectedDate,
		&i.DueDate,
		&i.Status,
		&i.Subtotal,
		&i.TaxAmount,
		&i.DiscountAmount,
		&i.TotalAmount,
		&i.PaidAmount,
		&i.RemainingAmount,
		&i.Notes,
		&i.CreatedAt,
		&i.UpdatedAt,
		&i.ApprovedBy,
		&i.ApprovedAt,
		&i.PrintedAt,
		&i.PrintedBy,
		&i.PrintedByName,
		&i.PrintCount,
		&i.SupplierName,
		&i.SupplierAddress,
		&i.SupplierPhone,
		&i.SupplierEmail,
		&i.WarehouseName,
		&i.CreatedByName,
		&i.ApprovedByName,
	)
	return i, err
}

const getPurchaseOrderByIDForUpdate = `-- name: GetPurchaseOrderByIDForUpdate :one
SELECT id, po_number, supplier_id, warehouse_id, created_by, order_date::text,
       COALESCE(expected_date::text, '')::text AS expected_date,
       COALESCE(due_date::text, '')::text AS due_date,
       status, subtotal, tax_amount, discount_amount, total_amount, paid_amount, remaining_amount,
       notes, created_at, updated_at,
       print_count
FROM purchase_orders WHERE id=$1
FOR UPDATE
`

type GetPurchaseOrderByIDForUpdateRow struct {
	ID              int64              `json:"id"`
	PoNumber        string             `json:"po_number"`
	SupplierID      int64              `json:"supplier_id"`
	WarehouseID     int64              `json:"warehouse_id"`
	CreatedBy       pgtype.Int8        `json:"created_by"`
	OrderDate       string             `json:"order_date"`
	ExpectedDate    string             `json:"expected_date"`
	DueDate         string             `json:"due_date"`
	Status          string             `json:"status"`
	Subtotal        decimal.Decimal    `json:"subtotal"`
	TaxAmount       decimal.Decimal    `json:"tax_amount"`
	DiscountAmount  decimal.Decimal    `json:"discount_amount"`
	TotalAmount     decimal.Decimal    `json:"total_amount"`
	PaidAmount      decimal.Decimal    `json:"paid_amount"`
	RemainingAmount decimal.Decimal    `json:"remaining_amount"`
	Notes           pgtype.Text        `json:"notes"`
	CreatedAt       pgtype.Timestamptz `json:"created_at"`
	UpdatedAt       pgtype.Timestamptz `json:"updated_at"`
	PrintCount      int32              `json:"print_count"`
}

func (q *Queries) GetPurchaseOrderByIDForUpdate(ctx context.Context, id int64) (GetPurchaseOrderByIDForUpdateRow, error) {
	row := q.db.QueryRow(ctx, getPurchaseOrderByIDForUpdate, id)
	var i GetPurchaseOrderByIDForUpdateRow
	err := row.Scan(
		&i.ID,
		&i.PoNumber,
		&i.SupplierID,
		&i.WarehouseID,
		&i.CreatedBy,
		&i.OrderDate,
		&i.ExpectedDate,
		&i.DueDate,
		&i.Status,
		&i.Subtotal,
		&i.TaxAmount,
		&i.DiscountAmount,
		&i.TotalAmount,
		&i.PaidAmount,
		&i.RemainingAmount,
		&i.Notes,
		&i.CreatedAt,
		&i.UpdatedAt,
		&i.PrintCount,
	)
	return i, err
}

const getPurchaseOrderSupplierID = `-- name: GetPurchaseOrderSupplierID :one
SELECT supplier_id FROM purchase_orders WHERE id=$1
`

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

const getVoucherByID = `-- name: GetVoucherByID :one
SELECT id, voucher_number, type, description,
       COALESCE(voucher_date::text, '')::text AS voucher_date,
       amount, status, created_by, supplier_id, po_id, cash_account_code, printed_at, printed_by
FROM vouchers WHERE id=$1
`

type GetVoucherByIDRow struct {
	ID              int64              `json:"id"`
	VoucherNumber   string             `json:"voucher_number"`
	Type            string             `json:"type"`
	Description     pgtype.Text        `json:"description"`
	VoucherDate     string             `json:"voucher_date"`
	Amount          decimal.Decimal    `json:"amount"`
	Status          string             `json:"status"`
	CreatedBy       pgtype.Int8        `json:"created_by"`
	SupplierID      pgtype.Int8        `json:"supplier_id"`
	PoID            pgtype.Int8        `json:"po_id"`
	CashAccountCode pgtype.Text        `json:"cash_account_code"`
	PrintedAt       pgtype.Timestamptz `json:"printed_at"`
	PrintedBy       pgtype.Int8        `json:"printed_by"`
}

func (q *Queries) GetVoucherByID(ctx context.Context, id int64) (GetVoucherByIDRow, error) {
	row := q.db.QueryRow(ctx, getVoucherByID, id)
	var i GetVoucherByIDRow
	err := row.Scan(
		&i.ID,
		&i.VoucherNumber,
		&i.Type,
		&i.Description,
		&i.VoucherDate,
		&i.Amount,
		&i.Status,
		&i.CreatedBy,
		&i.SupplierID,
		&i.PoID,
		&i.CashAccountCode,
		&i.PrintedAt,
		&i.PrintedBy,
	)
	return i, err
}

const getVoucherByIDForUpdate = `-- name: GetVoucherByIDForUpdate :one
SELECT id, voucher_number, type, description,
       COALESCE(voucher_date::text, '')::text AS voucher_date,
       amount, status, created_by, supplier_id, po_id, cash_account_code, printed_at, printed_by
FROM vouchers WHERE id=$1
FOR UPDATE
`

type GetVoucherByIDForUpdateRow struct {
	ID              int64              `json:"id"`
	VoucherNumber   string             `json:"voucher_number"`
	Type            string             `json:"type"`
	Description     pgtype.Text        `json:"description"`
	VoucherDate     string             `json:"voucher_date"`
	Amount          decimal.Decimal    `json:"amount"`
	Status          string             `json:"status"`
	CreatedBy       pgtype.Int8        `json:"created_by"`
	SupplierID      pgtype.Int8        `json:"supplier_id"`
	PoID            pgtype.Int8        `json:"po_id"`
	CashAccountCode pgtype.Text        `json:"cash_account_code"`
	PrintedAt       pgtype.Timestamptz `json:"printed_at"`
	PrintedBy       pgtype.Int8        `json:"printed_by"`
}

func (q *Queries) GetVoucherByIDForUpdate(ctx context.Context, id int64) (GetVoucherByIDForUpdateRow, error) {
	row := q.db.QueryRow(ctx, getVoucherByIDForUpdate, id)
	var i GetVoucherByIDForUpdateRow
	err := row.Scan(
		&i.ID,
		&i.VoucherNumber,
		&i.Type,
		&i.Description,
		&i.VoucherDate,
		&i.Amount,
		&i.Status,
		&i.CreatedBy,
		&i.SupplierID,
		&i.PoID,
		&i.CashAccountCode,
		&i.PrintedAt,
		&i.PrintedBy,
	)
	return i, err
}

const listDebts = `-- name: ListDebts :many
SELECT po.id, po.po_number, po.supplier_id, s.name, po.order_date::text AS order_date,
       COALESCE(po.due_date::text, '')::text AS due_date, po.status,
       po.total_amount, po.paid_amount, po.remaining_amount,
       -- Drill-down (input finance 1 Nov): komponen utang per PO — nilai
       -- barang cacat yang sudah dipotong dari total saat penerimaan, plus
       -- jumlah baris retur. 0/kosong = PO murni pembelian reguler.
       (SELECT COALESCE(SUM(gri.quantity_rejected * poi.unit_price), 0)
        FROM goods_receipt_items gri
        JOIN goods_receipts gr ON gr.id = gri.goods_receipt_id
        JOIN purchase_order_items poi ON poi.id = gri.purchase_order_item_id
        WHERE gr.purchase_order_id = po.id AND gri.quantity_rejected > 0) AS rejected_value,
       (SELECT COUNT(*) FROM goods_receipt_items gri2
        JOIN goods_receipts gr2 ON gr2.id = gri2.goods_receipt_id
        WHERE gr2.purchase_order_id = po.id AND gri2.quantity_rejected > 0) AS rejected_lines
FROM purchase_orders po
JOIN suppliers s ON s.id = po.supplier_id
WHERE po.status IN ('received','partial')
  AND ($3::bigint IS NULL OR $3::bigint <= 0 OR po.supplier_id = $3)
  AND ($4::date IS NULL OR po.due_date <= $4)
  AND ($5::text IS NULL OR (po.po_number ILIKE '%' || $5 || '%' OR s.name ILIKE '%' || $5 || '%'))
  AND (
    $6::text IS NULL
    OR $6::text = ''
    OR $6::text = 'all'
    OR ($6::text = 'unpaid' AND po.remaining_amount > 0)
    OR ($6::text = 'paid' AND po.remaining_amount = 0)
  )
ORDER BY
  CASE WHEN $7::text = 'po_number' AND $8::text = 'desc' THEN po.po_number END DESC,
  CASE WHEN $7::text = 'po_number' THEN po.po_number END ASC,
  CASE WHEN $7::text = 'supplier_name' AND $8::text = 'desc' THEN s.name END DESC,
  CASE WHEN $7::text = 'supplier_name' THEN s.name END ASC,
  CASE WHEN $7::text = 'order_date' AND $8::text = 'desc' THEN po.order_date END DESC,
  CASE WHEN $7::text = 'order_date' THEN po.order_date END ASC,
  CASE WHEN $7::text = 'due_date' AND $8::text = 'desc' THEN po.due_date END DESC,
  CASE WHEN $7::text = 'due_date' THEN po.due_date END ASC NULLS LAST,
  CASE WHEN $7::text = 'total_amount' AND $8::text = 'desc' THEN po.total_amount END DESC,
  CASE WHEN $7::text = 'total_amount' THEN po.total_amount END ASC,
  CASE WHEN $7::text = 'paid_amount' AND $8::text = 'desc' THEN po.paid_amount END DESC,
  CASE WHEN $7::text = 'paid_amount' THEN po.paid_amount END ASC,
  CASE WHEN $7::text = 'remaining_amount' AND $8::text = 'desc' THEN po.remaining_amount END DESC,
  CASE WHEN $7::text = 'remaining_amount' THEN po.remaining_amount END ASC,
  po.due_date NULLS LAST, po.id DESC
LIMIT $1 OFFSET $2
`

type ListDebtsParams struct {
	Limit      int32       `json:"limit"`
	Offset     int32       `json:"offset"`
	SupplierID pgtype.Int8 `json:"supplier_id"`
	DueDate    pgtype.Date `json:"due_date"`
	Search     pgtype.Text `json:"search"`
	DebtStatus pgtype.Text `json:"debt_status"`
	SortBy     pgtype.Text `json:"sort_by"`
	SortDir    pgtype.Text `json:"sort_dir"`
}

type ListDebtsRow struct {
	ID              int64           `json:"id"`
	PoNumber        string          `json:"po_number"`
	SupplierID      int64           `json:"supplier_id"`
	Name            string          `json:"name"`
	OrderDate       string          `json:"order_date"`
	DueDate         string          `json:"due_date"`
	Status          string          `json:"status"`
	TotalAmount     decimal.Decimal `json:"total_amount"`
	PaidAmount      decimal.Decimal `json:"paid_amount"`
	RemainingAmount decimal.Decimal `json:"remaining_amount"`
	RejectedValue   interface{}     `json:"rejected_value"`
	RejectedLines   int64           `json:"rejected_lines"`
}

// ERP column sort (sort_by + sort_dir) wins when present: the table headers
// are per-column and used to be ignored, so every click returned the same
// order. Whitelisted columns only — a sort key from the query string must
// never reach SQL as an identifier. Ties break by po.id so paging stays
// stable in both directions.
func (q *Queries) ListDebts(ctx context.Context, arg ListDebtsParams) ([]ListDebtsRow, error) {
	rows, err := q.db.Query(ctx, listDebts,
		arg.Limit,
		arg.Offset,
		arg.SupplierID,
		arg.DueDate,
		arg.Search,
		arg.DebtStatus,
		arg.SortBy,
		arg.SortDir,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []ListDebtsRow
	for rows.Next() {
		var i ListDebtsRow
		if err := rows.Scan(
			&i.ID,
			&i.PoNumber,
			&i.SupplierID,
			&i.Name,
			&i.OrderDate,
			&i.DueDate,
			&i.Status,
			&i.TotalAmount,
			&i.PaidAmount,
			&i.RemainingAmount,
			&i.RejectedValue,
			&i.RejectedLines,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const listGoodsReceiptItems = `-- name: ListGoodsReceiptItems :many
SELECT i.id, i.goods_receipt_id, i.purchase_order_item_id, i.product_id, i.quantity_ordered, i.quantity_rejected, i.defect_notes,
       i.quantity_received, i.batch_number, COALESCE(i.expired_date::text, '')::text AS expired_date, p.code, p.name
FROM goods_receipt_items i
JOIN products p ON p.id = i.product_id
WHERE i.goods_receipt_id = $1 ORDER BY i.id
`

type ListGoodsReceiptItemsRow struct {
	ID                  int64           `json:"id"`
	GoodsReceiptID      int64           `json:"goods_receipt_id"`
	PurchaseOrderItemID pgtype.Int8     `json:"purchase_order_item_id"`
	ProductID           int64           `json:"product_id"`
	QuantityOrdered     decimal.Decimal `json:"quantity_ordered"`
	QuantityRejected    decimal.Decimal `json:"quantity_rejected"`
	DefectNotes         pgtype.Text     `json:"defect_notes"`
	QuantityReceived    decimal.Decimal `json:"quantity_received"`
	BatchNumber         pgtype.Text     `json:"batch_number"`
	ExpiredDate         string          `json:"expired_date"`
	Code                string          `json:"code"`
	Name                string          `json:"name"`
}

func (q *Queries) ListGoodsReceiptItems(ctx context.Context, goodsReceiptID int64) ([]ListGoodsReceiptItemsRow, error) {
	rows, err := q.db.Query(ctx, listGoodsReceiptItems, goodsReceiptID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []ListGoodsReceiptItemsRow
	for rows.Next() {
		var i ListGoodsReceiptItemsRow
		if err := rows.Scan(
			&i.ID,
			&i.GoodsReceiptID,
			&i.PurchaseOrderItemID,
			&i.ProductID,
			&i.QuantityOrdered,
			&i.QuantityRejected,
			&i.DefectNotes,
			&i.QuantityReceived,
			&i.BatchNumber,
			&i.ExpiredDate,
			&i.Code,
			&i.Name,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const listGoodsReceipts = `-- name: ListGoodsReceipts :many
SELECT gr.id, gr.gr_number, gr.purchase_order_id, gr.warehouse_id, gr.received_by, gr.received_date::text AS received_date, gr.notes, gr.status, gr.created_at,
       COALESCE(po.po_number, '') AS po_number,
       COALESCE(s.name, '') AS supplier_name, COALESCE(w.name, '') AS warehouse_name
FROM goods_receipts gr
LEFT JOIN purchase_orders po ON po.id = gr.purchase_order_id
LEFT JOIN suppliers s ON s.id = po.supplier_id
LEFT JOIN warehouses w ON w.id = gr.warehouse_id
WHERE ($3::text IS NULL OR gr.status = $3)
ORDER BY gr.id DESC LIMIT $1 OFFSET $2
`

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

type ListGoodsReceiptsRow struct {
	ID              int64              `json:"id"`
	GrNumber        string             `json:"gr_number"`
	PurchaseOrderID int64              `json:"purchase_order_id"`
	WarehouseID     int64              `json:"warehouse_id"`
	ReceivedBy      pgtype.Int8        `json:"received_by"`
	ReceivedDate    string             `json:"received_date"`
	Notes           pgtype.Text        `json:"notes"`
	Status          string             `json:"status"`
	CreatedAt       pgtype.Timestamptz `json:"created_at"`
	PoNumber        string             `json:"po_number"`
	SupplierName    string             `json:"supplier_name"`
	WarehouseName   string             `json:"warehouse_name"`
}

func (q *Queries) ListGoodsReceipts(ctx context.Context, arg ListGoodsReceiptsParams) ([]ListGoodsReceiptsRow, error) {
	rows, err := q.db.Query(ctx, listGoodsReceipts, arg.Limit, arg.Offset, arg.Status)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []ListGoodsReceiptsRow
	for rows.Next() {
		var i ListGoodsReceiptsRow
		if err := rows.Scan(
			&i.ID,
			&i.GrNumber,
			&i.PurchaseOrderID,
			&i.WarehouseID,
			&i.ReceivedBy,
			&i.ReceivedDate,
			&i.Notes,
			&i.Status,
			&i.CreatedAt,
			&i.PoNumber,
			&i.SupplierName,
			&i.WarehouseName,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const listPurchaseOrderItems = `-- name: ListPurchaseOrderItems :many
SELECT i.id, i.purchase_order_id, i.product_id, i.quantity, i.received_quantity, i.unit_price,
       i.discount, i.subtotal, p.code, p.name, u.symbol AS unit_symbol
FROM purchase_order_items i
JOIN products p ON p.id = i.product_id
LEFT JOIN units u ON u.id = p.unit_id
WHERE i.purchase_order_id=$1 ORDER BY i.id
`

type ListPurchaseOrderItemsRow struct {
	ID               int64           `json:"id"`
	PurchaseOrderID  int64           `json:"purchase_order_id"`
	ProductID        int64           `json:"product_id"`
	Quantity         decimal.Decimal `json:"quantity"`
	ReceivedQuantity decimal.Decimal `json:"received_quantity"`
	UnitPrice        decimal.Decimal `json:"unit_price"`
	Discount         decimal.Decimal `json:"discount"`
	Subtotal         decimal.Decimal `json:"subtotal"`
	Code             string          `json:"code"`
	Name             string          `json:"name"`
	UnitSymbol       pgtype.Text     `json:"unit_symbol"`
}

func (q *Queries) ListPurchaseOrderItems(ctx context.Context, purchaseOrderID int64) ([]ListPurchaseOrderItemsRow, error) {
	rows, err := q.db.Query(ctx, listPurchaseOrderItems, purchaseOrderID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []ListPurchaseOrderItemsRow
	for rows.Next() {
		var i ListPurchaseOrderItemsRow
		if err := rows.Scan(
			&i.ID,
			&i.PurchaseOrderID,
			&i.ProductID,
			&i.Quantity,
			&i.ReceivedQuantity,
			&i.UnitPrice,
			&i.Discount,
			&i.Subtotal,
			&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 listPurchaseOrderItemsForUpdate = `-- name: ListPurchaseOrderItemsForUpdate :many
SELECT i.id, i.purchase_order_id, i.product_id, i.quantity, i.received_quantity, i.unit_price,
       i.discount, i.subtotal, p.code, p.name, u.symbol AS unit_symbol
FROM purchase_order_items i
JOIN products p ON p.id = i.product_id
LEFT JOIN units u ON u.id = p.unit_id
WHERE i.purchase_order_id=$1 ORDER BY i.id
FOR UPDATE OF i
`

type ListPurchaseOrderItemsForUpdateRow struct {
	ID               int64           `json:"id"`
	PurchaseOrderID  int64           `json:"purchase_order_id"`
	ProductID        int64           `json:"product_id"`
	Quantity         decimal.Decimal `json:"quantity"`
	ReceivedQuantity decimal.Decimal `json:"received_quantity"`
	UnitPrice        decimal.Decimal `json:"unit_price"`
	Discount         decimal.Decimal `json:"discount"`
	Subtotal         decimal.Decimal `json:"subtotal"`
	Code             string          `json:"code"`
	Name             string          `json:"name"`
	UnitSymbol       pgtype.Text     `json:"unit_symbol"`
}

func (q *Queries) ListPurchaseOrderItemsForUpdate(ctx context.Context, purchaseOrderID int64) ([]ListPurchaseOrderItemsForUpdateRow, error) {
	rows, err := q.db.Query(ctx, listPurchaseOrderItemsForUpdate, purchaseOrderID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []ListPurchaseOrderItemsForUpdateRow
	for rows.Next() {
		var i ListPurchaseOrderItemsForUpdateRow
		if err := rows.Scan(
			&i.ID,
			&i.PurchaseOrderID,
			&i.ProductID,
			&i.Quantity,
			&i.ReceivedQuantity,
			&i.UnitPrice,
			&i.Discount,
			&i.Subtotal,
			&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 listPurchaseOrders = `-- name: ListPurchaseOrders :many
SELECT po.id, po.po_number, po.supplier_id, po.warehouse_id, po.created_by, po.order_date::text AS order_date,
       COALESCE(po.expected_date::text, '')::text AS expected_date,
       COALESCE(po.due_date::text, '')::text AS due_date,
       po.status, po.subtotal, po.tax_amount, po.discount_amount, po.total_amount, po.paid_amount, po.remaining_amount,
       po.notes, po.created_at, po.updated_at,
       po.print_count,
       COALESCE(to_char(po.printed_at AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"'), '')::text AS printed_at,
       COALESCE(s.name, '') AS supplier_name, COALESCE(w.name, '') AS warehouse_name
FROM purchase_orders po
LEFT JOIN suppliers s ON s.id = po.supplier_id
LEFT JOIN warehouses w ON w.id = po.warehouse_id
WHERE ($3::text IS NULL OR (po.po_number ILIKE '%' || $3 || '%' OR po.supplier_id IN (SELECT id FROM suppliers WHERE name ILIKE '%' || $3 || '%')))
  AND ($4::text IS NULL OR po.status = $4)
  AND ($5::bigint IS NULL OR $5::bigint <= 0 OR po.supplier_id = $5)
ORDER BY
  CASE WHEN $6::text = 'po_number' AND $7::text = 'desc' THEN po.po_number END DESC,
  CASE WHEN $6::text = 'po_number' THEN po.po_number END ASC,
  CASE WHEN $6::text = 'supplier_name' AND $7::text = 'desc' THEN s.name END DESC NULLS LAST,
  CASE WHEN $6::text = 'supplier_name' THEN s.name END ASC NULLS LAST,
  CASE WHEN $6::text = 'order_date' AND $7::text = 'desc' THEN po.order_date END DESC,
  CASE WHEN $6::text = 'order_date' THEN po.order_date END ASC,
  CASE WHEN $6::text = 'expected_date' AND $7::text = 'desc' THEN po.expected_date END DESC NULLS LAST,
  CASE WHEN $6::text = 'expected_date' THEN po.expected_date END ASC NULLS LAST,
  CASE WHEN $6::text = 'status' AND $7::text = 'desc' THEN po.status END DESC,
  CASE WHEN $6::text = 'status' THEN po.status END ASC,
  CASE WHEN $6::text = 'total_amount' AND $7::text = 'desc' THEN po.total_amount END DESC,
  CASE WHEN $6::text = 'total_amount' THEN po.total_amount END ASC,
  po.id DESC
LIMIT $1 OFFSET $2
`

type ListPurchaseOrdersParams struct {
	Limit      int32       `json:"limit"`
	Offset     int32       `json:"offset"`
	Search     pgtype.Text `json:"search"`
	Status     pgtype.Text `json:"status"`
	SupplierID pgtype.Int8 `json:"supplier_id"`
	SortBy     pgtype.Text `json:"sort_by"`
	SortDir    pgtype.Text `json:"sort_dir"`
}

type ListPurchaseOrdersRow struct {
	ID              int64              `json:"id"`
	PoNumber        string             `json:"po_number"`
	SupplierID      int64              `json:"supplier_id"`
	WarehouseID     int64              `json:"warehouse_id"`
	CreatedBy       pgtype.Int8        `json:"created_by"`
	OrderDate       string             `json:"order_date"`
	ExpectedDate    string             `json:"expected_date"`
	DueDate         string             `json:"due_date"`
	Status          string             `json:"status"`
	Subtotal        decimal.Decimal    `json:"subtotal"`
	TaxAmount       decimal.Decimal    `json:"tax_amount"`
	DiscountAmount  decimal.Decimal    `json:"discount_amount"`
	TotalAmount     decimal.Decimal    `json:"total_amount"`
	PaidAmount      decimal.Decimal    `json:"paid_amount"`
	RemainingAmount decimal.Decimal    `json:"remaining_amount"`
	Notes           pgtype.Text        `json:"notes"`
	CreatedAt       pgtype.Timestamptz `json:"created_at"`
	UpdatedAt       pgtype.Timestamptz `json:"updated_at"`
	PrintCount      int32              `json:"print_count"`
	PrintedAt       string             `json:"printed_at"`
	SupplierName    string             `json:"supplier_name"`
	WarehouseName   string             `json:"warehouse_name"`
}

func (q *Queries) ListPurchaseOrders(ctx context.Context, arg ListPurchaseOrdersParams) ([]ListPurchaseOrdersRow, error) {
	rows, err := q.db.Query(ctx, listPurchaseOrders,
		arg.Limit,
		arg.Offset,
		arg.Search,
		arg.Status,
		arg.SupplierID,
		arg.SortBy,
		arg.SortDir,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []ListPurchaseOrdersRow
	for rows.Next() {
		var i ListPurchaseOrdersRow
		if err := rows.Scan(
			&i.ID,
			&i.PoNumber,
			&i.SupplierID,
			&i.WarehouseID,
			&i.CreatedBy,
			&i.OrderDate,
			&i.ExpectedDate,
			&i.DueDate,
			&i.Status,
			&i.Subtotal,
			&i.TaxAmount,
			&i.DiscountAmount,
			&i.TotalAmount,
			&i.PaidAmount,
			&i.RemainingAmount,
			&i.Notes,
			&i.CreatedAt,
			&i.UpdatedAt,
			&i.PrintCount,
			&i.PrintedAt,
			&i.SupplierName,
			&i.WarehouseName,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const listVouchers = `-- name: ListVouchers :many
SELECT id, voucher_number, type, description,
       COALESCE(voucher_date::text, '')::text AS voucher_date,
       amount, status, created_by, supplier_id, po_id, cash_account_code, printed_at, printed_by
FROM vouchers
WHERE ($3::text IS NULL OR status = $3)
ORDER BY id DESC LIMIT $1 OFFSET $2
`

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

type ListVouchersRow struct {
	ID              int64              `json:"id"`
	VoucherNumber   string             `json:"voucher_number"`
	Type            string             `json:"type"`
	Description     pgtype.Text        `json:"description"`
	VoucherDate     string             `json:"voucher_date"`
	Amount          decimal.Decimal    `json:"amount"`
	Status          string             `json:"status"`
	CreatedBy       pgtype.Int8        `json:"created_by"`
	SupplierID      pgtype.Int8        `json:"supplier_id"`
	PoID            pgtype.Int8        `json:"po_id"`
	CashAccountCode pgtype.Text        `json:"cash_account_code"`
	PrintedAt       pgtype.Timestamptz `json:"printed_at"`
	PrintedBy       pgtype.Int8        `json:"printed_by"`
}

func (q *Queries) ListVouchers(ctx context.Context, arg ListVouchersParams) ([]ListVouchersRow, error) {
	rows, err := q.db.Query(ctx, listVouchers, arg.Limit, arg.Offset, arg.Status)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []ListVouchersRow
	for rows.Next() {
		var i ListVouchersRow
		if err := rows.Scan(
			&i.ID,
			&i.VoucherNumber,
			&i.Type,
			&i.Description,
			&i.VoucherDate,
			&i.Amount,
			&i.Status,
			&i.CreatedBy,
			&i.SupplierID,
			&i.PoID,
			&i.CashAccountCode,
			&i.PrintedAt,
			&i.PrintedBy,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const markVoucherPrinted = `-- name: MarkVoucherPrinted :execrows
UPDATE vouchers SET printed_at=now(), printed_by=$2 WHERE id=$1
`

type MarkVoucherPrintedParams struct {
	ID        int64       `json:"id"`
	PrintedBy pgtype.Int8 `json:"printed_by"`
}

func (q *Queries) MarkVoucherPrinted(ctx context.Context, arg MarkVoucherPrintedParams) (int64, error) {
	result, err := q.db.Exec(ctx, markVoucherPrinted, arg.ID, arg.PrintedBy)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected(), nil
}

const purchaseOrdersStampPrint = `-- name: PurchaseOrdersStampPrint :execrows
UPDATE purchase_orders
SET printed_at = now(),
    printed_by = $1::bigint,
    print_count = print_count + 1,
    updated_at = now()
WHERE id = $2::bigint
  AND (print_count = 0 OR $3::boolean)
`

type PurchaseOrdersStampPrintParams struct {
	PrintedBy    pgtype.Int8 `json:"printed_by"`
	ID           int64       `json:"id"`
	AllowReprint bool        `json:"allow_reprint"`
}

// The print-once ledger (000150): first print stamps and counts; reprints
// only pass when the CALLER ELEVATES (allow_reprint = admin/owner bypass,
// decided in the service from auth.UserFrom before this UPDATE runs). The
// WHERE makes two concurrent first prints atomic — exactly one wins, the
// loser sees execrows==0 and answers 409. printed_at/by record the LAST
// print, not the first.
func (q *Queries) PurchaseOrdersStampPrint(ctx context.Context, arg PurchaseOrdersStampPrintParams) (int64, error) {
	result, err := q.db.Exec(ctx, purchaseOrdersStampPrint, arg.PrintedBy, arg.ID, arg.AllowReprint)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected(), nil
}

const rawStockAdd = `-- name: RawStockAdd :exec
INSERT INTO stocks (product_id, warehouse_id, quantity, reserved_quantity, last_updated_at)
VALUES ($1,$2,$3,0,now())
ON CONFLICT (product_id, warehouse_id)
DO UPDATE SET quantity = stocks.quantity + $3, last_updated_at = now()
`

type RawStockAddParams struct {
	ProductID   int64           `json:"product_id"`
	WarehouseID int64           `json:"warehouse_id"`
	Quantity    decimal.Decimal `json:"quantity"`
}

func (q *Queries) RawStockAdd(ctx context.Context, arg RawStockAddParams) error {
	_, err := q.db.Exec(ctx, rawStockAdd, arg.ProductID, arg.WarehouseID, arg.Quantity)
	return err
}

const setPurchaseOrderStatus = `-- name: SetPurchaseOrderStatus :execrows
UPDATE purchase_orders SET status=$2, updated_at=now() WHERE id=$1
`

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

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

const sumDebts = `-- name: SumDebts :one
SELECT 
  COALESCE(SUM(po.remaining_amount),0)::numeric AS total,
  COALESCE(SUM(po.paid_amount),0)::numeric AS total_paid,
  COALESCE(SUM(po.total_amount),0)::numeric AS total_po,
  COUNT(*) FILTER (WHERE po.remaining_amount > 0) AS count_unpaid,
  COUNT(DISTINCT po.supplier_id) FILTER (WHERE po.remaining_amount > 0) AS supplier_unpaid_count
FROM purchase_orders po
JOIN suppliers s ON s.id = po.supplier_id
WHERE po.status IN ('received','partial')
  AND ($1::bigint IS NULL OR $1::bigint <= 0 OR po.supplier_id = $1)
  AND ($2::date IS NULL OR po.due_date <= $2)
  AND ($3::text IS NULL OR (po.po_number ILIKE '%' || $3 || '%' OR s.name ILIKE '%' || $3 || '%'))
`

type SumDebtsParams struct {
	SupplierID pgtype.Int8 `json:"supplier_id"`
	DueDate    pgtype.Date `json:"due_date"`
	Search     pgtype.Text `json:"search"`
}

type SumDebtsRow struct {
	Total               decimal.Decimal `json:"total"`
	TotalPaid           decimal.Decimal `json:"total_paid"`
	TotalPo             decimal.Decimal `json:"total_po"`
	CountUnpaid         int64           `json:"count_unpaid"`
	SupplierUnpaidCount int64           `json:"supplier_unpaid_count"`
}

func (q *Queries) SumDebts(ctx context.Context, arg SumDebtsParams) (SumDebtsRow, error) {
	row := q.db.QueryRow(ctx, sumDebts, arg.SupplierID, arg.DueDate, arg.Search)
	var i SumDebtsRow
	err := row.Scan(
		&i.Total,
		&i.TotalPaid,
		&i.TotalPo,
		&i.CountUnpaid,
		&i.SupplierUnpaidCount,
	)
	return i, err
}

const updatePurchaseOrder = `-- name: UpdatePurchaseOrder :execrows
UPDATE purchase_orders SET po_number=$2, supplier_id=$3, warehouse_id=$4, order_date=$5,
       expected_date=$6, due_date=$7, status=$8, subtotal=$9, tax_amount=$10, discount_amount=$11,
       total_amount=$12, paid_amount=$13, remaining_amount=$14, notes=$15, updated_at=now()
WHERE id=$1
`

type UpdatePurchaseOrderParams struct {
	ID              int64           `json:"id"`
	PoNumber        string          `json:"po_number"`
	SupplierID      int64           `json:"supplier_id"`
	WarehouseID     int64           `json:"warehouse_id"`
	OrderDate       pgtype.Date     `json:"order_date"`
	ExpectedDate    pgtype.Date     `json:"expected_date"`
	DueDate         pgtype.Date     `json:"due_date"`
	Status          string          `json:"status"`
	Subtotal        decimal.Decimal `json:"subtotal"`
	TaxAmount       decimal.Decimal `json:"tax_amount"`
	DiscountAmount  decimal.Decimal `json:"discount_amount"`
	TotalAmount     decimal.Decimal `json:"total_amount"`
	PaidAmount      decimal.Decimal `json:"paid_amount"`
	RemainingAmount decimal.Decimal `json:"remaining_amount"`
	Notes           pgtype.Text     `json:"notes"`
}

func (q *Queries) UpdatePurchaseOrder(ctx context.Context, arg UpdatePurchaseOrderParams) (int64, error) {
	result, err := q.db.Exec(ctx, updatePurchaseOrder,
		arg.ID,
		arg.PoNumber,
		arg.SupplierID,
		arg.WarehouseID,
		arg.OrderDate,
		arg.ExpectedDate,
		arg.DueDate,
		arg.Status,
		arg.Subtotal,
		arg.TaxAmount,
		arg.DiscountAmount,
		arg.TotalAmount,
		arg.PaidAmount,
		arg.RemainingAmount,
		arg.Notes,
	)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected(), nil
}

const updateVoucher = `-- name: UpdateVoucher :execrows
UPDATE vouchers SET type=$2, description=$3, voucher_date=$4, amount=$5, status=$6, updated_at=now() WHERE id=$1
`

type UpdateVoucherParams struct {
	ID          int64           `json:"id"`
	Type        string          `json:"type"`
	Description pgtype.Text     `json:"description"`
	VoucherDate pgtype.Date     `json:"voucher_date"`
	Amount      decimal.Decimal `json:"amount"`
	Status      string          `json:"status"`
}

func (q *Queries) UpdateVoucher(ctx context.Context, arg UpdateVoucherParams) (int64, error) {
	result, err := q.db.Exec(ctx, updateVoucher,
		arg.ID,
		arg.Type,
		arg.Description,
		arg.VoucherDate,
		arg.Amount,
		arg.Status,
	)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected(), nil
}

const updateVoucherStatus = `-- name: UpdateVoucherStatus :execrows
UPDATE vouchers SET status=$2 WHERE id=$1 AND status='available'
`

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

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

const updateVoucherStatusAny = `-- name: UpdateVoucherStatusAny :execrows
UPDATE vouchers SET status=$2, updated_at=now() WHERE id=$1
`

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

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