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

package db

import (
	"context"

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

const cashflowAlreadyReversed = `-- name: CashflowAlreadyReversed :one
SELECT EXISTS(SELECT 1 FROM cashflows WHERE reversal_of = $1) AS reversed
`

func (q *Queries) CashflowAlreadyReversed(ctx context.Context, reversalOf pgtype.Int8) (bool, error) {
	row := q.db.QueryRow(ctx, cashflowAlreadyReversed, reversalOf)
	var reversed bool
	err := row.Scan(&reversed)
	return reversed, err
}

const countCashflows = `-- name: CountCashflows :one
SELECT count(*) FROM cashflows
WHERE ($1::text IS NULL OR type = $1)
  AND ($2::text IS NULL OR category = $2)
  AND ($3::date IS NULL OR transaction_date >= $3)
  AND ($4::date IS NULL OR transaction_date <= $4)
  AND ($5::bigint[] IS NULL
       OR store_id = ANY($5::bigint[]))
`

type CountCashflowsParams struct {
	Type     pgtype.Text `json:"type"`
	Category pgtype.Text `json:"category"`
	DateFrom pgtype.Date `json:"date_from"`
	DateTo   pgtype.Date `json:"date_to"`
	StoreIds []int64     `json:"store_ids"`
}

// Carries the same predicate as ListCashflows. A count without it would report
// the COMPANY's row count to a scoped caller, which tells a till operator the
// size of a business they are not supposed to know about.
func (q *Queries) CountCashflows(ctx context.Context, arg CountCashflowsParams) (int64, error) {
	row := q.db.QueryRow(ctx, countCashflows,
		arg.Type,
		arg.Category,
		arg.DateFrom,
		arg.DateTo,
		arg.StoreIds,
	)
	var count int64
	err := row.Scan(&count)
	return count, err
}

const countInvoices = `-- name: CountInvoices :one
SELECT count(*) FROM invoices i
LEFT JOIN sales_orders so ON so.id = i.sales_order_id
WHERE ($1::text IS NULL OR (i.invoice_number ILIKE '%' || $1 || '%' OR i.customer_id IN (SELECT id FROM customers WHERE name ILIKE '%' || $1 || '%')))
  AND ($2::text IS NULL OR i.status = $2)
  AND ($3::bigint IS NULL OR $3::bigint <= 0 OR i.customer_id = $3)
  AND ($4::date IS NULL OR i.invoice_date >= $4)
  AND ($5::date IS NULL OR i.invoice_date <= $5)
  AND ($6::bigint IS NULL OR $6::bigint <= 0 OR so.store_id = $6)
`

type CountInvoicesParams struct {
	Search     pgtype.Text `json:"search"`
	Status     pgtype.Text `json:"status"`
	CustomerID pgtype.Int8 `json:"customer_id"`
	DateFrom   pgtype.Date `json:"date_from"`
	DateTo     pgtype.Date `json:"date_to"`
	StoreID    pgtype.Int8 `json:"store_id"`
}

func (q *Queries) CountInvoices(ctx context.Context, arg CountInvoicesParams) (int64, error) {
	row := q.db.QueryRow(ctx, countInvoices,
		arg.Search,
		arg.Status,
		arg.CustomerID,
		arg.DateFrom,
		arg.DateTo,
		arg.StoreID,
	)
	var count int64
	err := row.Scan(&count)
	return count, err
}

const countPayments = `-- name: CountPayments :one
SELECT count(*) FROM payments p
LEFT JOIN invoices inv ON inv.id = p.payable_id AND (p.payable_type = 'App\Models\Invoice' OR p.payable_type = 'invoice')
LEFT JOIN sales_orders so_inv ON so_inv.id = inv.sales_order_id
LEFT JOIN sales_orders so ON so.id = p.payable_id AND (p.payable_type = 'App\Models\SalesOrder' OR p.payable_type = 'sales_order')
LEFT JOIN galon_transactions gt ON gt.id = p.payable_id AND (p.payable_type = 'galon' OR p.payable_type = 'galon_deposit')
WHERE ($1::text IS NULL OR p.payment_number ILIKE '%' || $1 || '%')
  AND ($2::date IS NULL OR p.payment_date >= $2)
  AND ($3::date IS NULL OR p.payment_date <= $3)
  AND ($4::bigint IS NULL OR $4::bigint <= 0 OR inv.customer_id = $4)
  AND ($5::bigint IS NULL OR $5::bigint <= 0 OR p.payable_id = $5)
  AND ($6::text IS NULL OR p.payable_type = $6)
  AND ($7::bigint IS NULL OR $7::bigint <= 0 OR COALESCE(so_inv.store_id, so.store_id, gt.store_id) = $7)
`

type CountPaymentsParams struct {
	Search      pgtype.Text `json:"search"`
	DateFrom    pgtype.Date `json:"date_from"`
	DateTo      pgtype.Date `json:"date_to"`
	CustomerID  pgtype.Int8 `json:"customer_id"`
	PayableID   pgtype.Int8 `json:"payable_id"`
	PayableType pgtype.Text `json:"payable_type"`
	StoreID     pgtype.Int8 `json:"store_id"`
}

func (q *Queries) CountPayments(ctx context.Context, arg CountPaymentsParams) (int64, error) {
	row := q.db.QueryRow(ctx, countPayments,
		arg.Search,
		arg.DateFrom,
		arg.DateTo,
		arg.CustomerID,
		arg.PayableID,
		arg.PayableType,
		arg.StoreID,
	)
	var count int64
	err := row.Scan(&count)
	return count, err
}

const countReceivables = `-- name: CountReceivables :one
SELECT count(*) FROM invoices inv
LEFT JOIN sales_orders so ON so.id = inv.sales_order_id
WHERE inv.status IN ('unpaid','partial','paid')
  AND ($1::bigint IS NULL OR $1::bigint <= 0 OR inv.customer_id = $1)
  AND ($2::bigint IS NULL OR $2::bigint <= 0 OR so.store_id = $2)
  AND (
    $3::text IS NULL
    OR ($3::text = 'overdue' AND inv.status IN ('unpaid', 'partial') AND inv.due_date < CURRENT_DATE AND inv.remaining_amount > 0)
    OR ($3::text <> 'overdue' AND inv.status = $3)
  )
`

type CountReceivablesParams struct {
	CustomerID pgtype.Int8 `json:"customer_id"`
	StoreID    pgtype.Int8 `json:"store_id"`
	Status     pgtype.Text `json:"status"`
}

func (q *Queries) CountReceivables(ctx context.Context, arg CountReceivablesParams) (int64, error) {
	row := q.db.QueryRow(ctx, countReceivables, arg.CustomerID, arg.StoreID, arg.Status)
	var count int64
	err := row.Scan(&count)
	return count, err
}

const createCashflow = `-- name: CreateCashflow :one
INSERT INTO cashflows (transaction_number, type, category, transaction_date, amount, description, reference_type, reference_id, created_by, reversal_of,
  account_id, counterpart_account_id, flow_type, payment_method,
  attachment_key, attachment_filename, attachment_content_type, attachment_size, attachment_uploaded_by, attachment_uploaded_at, store_id)
VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,now(),$20::bigint) RETURNING id
`

type CreateCashflowParams 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"`
	ReversalOf            pgtype.Int8     `json:"reversal_of"`
	AccountID             pgtype.Int8     `json:"account_id"`
	CounterpartAccountID  pgtype.Int8     `json:"counterpart_account_id"`
	FlowType              pgtype.Text     `json:"flow_type"`
	PaymentMethod         pgtype.Text     `json:"payment_method"`
	AttachmentKey         pgtype.Text     `json:"attachment_key"`
	AttachmentFilename    pgtype.Text     `json:"attachment_filename"`
	AttachmentContentType pgtype.Text     `json:"attachment_content_type"`
	AttachmentSize        pgtype.Int8     `json:"attachment_size"`
	AttachmentUploadedBy  pgtype.Int8     `json:"attachment_uploaded_by"`
	StoreID               pgtype.Int8     `json:"store_id"`
}

// store_id is the 21st parameter and the SERVICE sets it from the caller's
// store scope, never from the request body. A client that could name its own
// outlet would defeat every filter in this file.
func (q *Queries) CreateCashflow(ctx context.Context, arg CreateCashflowParams) (int64, error) {
	row := q.db.QueryRow(ctx, createCashflow,
		arg.TransactionNumber,
		arg.Type,
		arg.Category,
		arg.TransactionDate,
		arg.Amount,
		arg.Description,
		arg.ReferenceType,
		arg.ReferenceID,
		arg.CreatedBy,
		arg.ReversalOf,
		arg.AccountID,
		arg.CounterpartAccountID,
		arg.FlowType,
		arg.PaymentMethod,
		arg.AttachmentKey,
		arg.AttachmentFilename,
		arg.AttachmentContentType,
		arg.AttachmentSize,
		arg.AttachmentUploadedBy,
		arg.StoreID,
	)
	var id int64
	err := row.Scan(&id)
	return id, err
}

const createInvoice = `-- name: CreateInvoice :one
INSERT INTO invoices (invoice_number, sales_order_id, customer_id, invoice_date, due_date, status,
       subtotal, tax_amount, total_amount, paid_amount, remaining_amount, notes)
VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12)
RETURNING id, created_at, updated_at
`

type CreateInvoiceParams struct {
	InvoiceNumber   string          `json:"invoice_number"`
	SalesOrderID    pgtype.Int8     `json:"sales_order_id"`
	CustomerID      int64           `json:"customer_id"`
	InvoiceDate     pgtype.Date     `json:"invoice_date"`
	DueDate         pgtype.Date     `json:"due_date"`
	Status          string          `json:"status"`
	Subtotal        decimal.Decimal `json:"subtotal"`
	TaxAmount       decimal.Decimal `json:"tax_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 CreateInvoiceRow struct {
	ID        int64              `json:"id"`
	CreatedAt pgtype.Timestamptz `json:"created_at"`
	UpdatedAt pgtype.Timestamptz `json:"updated_at"`
}

func (q *Queries) CreateInvoice(ctx context.Context, arg CreateInvoiceParams) (CreateInvoiceRow, error) {
	row := q.db.QueryRow(ctx, createInvoice,
		arg.InvoiceNumber,
		arg.SalesOrderID,
		arg.CustomerID,
		arg.InvoiceDate,
		arg.DueDate,
		arg.Status,
		arg.Subtotal,
		arg.TaxAmount,
		arg.TotalAmount,
		arg.PaidAmount,
		arg.RemainingAmount,
		arg.Notes,
	)
	var i CreateInvoiceRow
	err := row.Scan(&i.ID, &i.CreatedAt, &i.UpdatedAt)
	return i, err
}

const createPayment = `-- name: CreatePayment :one
INSERT INTO payments (payment_number, payable_type, payable_id, payment_date, amount, payment_method,
       bank_name, bank_account, reference_number, notes, created_by)
VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11) RETURNING id
`

type CreatePaymentParams struct {
	PaymentNumber   string          `json:"payment_number"`
	PayableType     string          `json:"payable_type"`
	PayableID       int64           `json:"payable_id"`
	PaymentDate     pgtype.Date     `json:"payment_date"`
	Amount          decimal.Decimal `json:"amount"`
	PaymentMethod   string          `json:"payment_method"`
	BankName        pgtype.Text     `json:"bank_name"`
	BankAccount     pgtype.Text     `json:"bank_account"`
	ReferenceNumber pgtype.Text     `json:"reference_number"`
	Notes           pgtype.Text     `json:"notes"`
	CreatedBy       pgtype.Int8     `json:"created_by"`
}

func (q *Queries) CreatePayment(ctx context.Context, arg CreatePaymentParams) (int64, error) {
	row := q.db.QueryRow(ctx, createPayment,
		arg.PaymentNumber,
		arg.PayableType,
		arg.PayableID,
		arg.PaymentDate,
		arg.Amount,
		arg.PaymentMethod,
		arg.BankName,
		arg.BankAccount,
		arg.ReferenceNumber,
		arg.Notes,
		arg.CreatedBy,
	)
	var id int64
	err := row.Scan(&id)
	return id, err
}

const deleteCashflow = `-- name: DeleteCashflow :execrows
DELETE FROM cashflows WHERE id=$1
`

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

const deleteInvoice = `-- name: DeleteInvoice :execrows
DELETE FROM invoices WHERE id=$1
`

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

const getCashflowByID = `-- name: GetCashflowByID :one
SELECT id, transaction_number, type, category, transaction_date::text, amount,
       description, reference_type, reference_id, created_by, reversal_of, store_id
FROM cashflows
WHERE id=$1
  AND ($2::bigint[] IS NULL
       OR store_id = ANY($2::bigint[]))
`

type GetCashflowByIDParams struct {
	ID       int64   `json:"id"`
	StoreIds []int64 `json:"store_ids"`
}

type GetCashflowByIDRow struct {
	ID                int64           `json:"id"`
	TransactionNumber string          `json:"transaction_number"`
	Type              string          `json:"type"`
	Category          string          `json:"category"`
	TransactionDate   string          `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"`
	ReversalOf        pgtype.Int8     `json:"reversal_of"`
	StoreID           pgtype.Int8     `json:"store_id"`
}

// The single-row read MUST be scoped, or a scoped list is cosmetic: a caller
// who cannot see a row in the list can still fetch it by id. store_ids is the
// caller's store scope as an array; an EMPTY array means a restricted scope with
// no stores, which must match nothing. A NULL array means platform-wide, which
// matches everything including company-level (store_id IS NULL) rows.
func (q *Queries) GetCashflowByID(ctx context.Context, arg GetCashflowByIDParams) (GetCashflowByIDRow, error) {
	row := q.db.QueryRow(ctx, getCashflowByID, arg.ID, arg.StoreIds)
	var i GetCashflowByIDRow
	err := row.Scan(
		&i.ID,
		&i.TransactionNumber,
		&i.Type,
		&i.Category,
		&i.TransactionDate,
		&i.Amount,
		&i.Description,
		&i.ReferenceType,
		&i.ReferenceID,
		&i.CreatedBy,
		&i.ReversalOf,
		&i.StoreID,
	)
	return i, err
}

const getCashflowByIDForUpdate = `-- name: GetCashflowByIDForUpdate :one
SELECT id, transaction_number, type, category, transaction_date::text, amount,
       description, reference_type, reference_id, created_by, reversal_of, store_id
FROM cashflows
WHERE id=$1
  AND ($2::bigint[] IS NULL
       OR store_id = ANY($2::bigint[]))
FOR UPDATE
`

type GetCashflowByIDForUpdateParams struct {
	ID       int64   `json:"id"`
	StoreIds []int64 `json:"store_ids"`
}

type GetCashflowByIDForUpdateRow struct {
	ID                int64           `json:"id"`
	TransactionNumber string          `json:"transaction_number"`
	Type              string          `json:"type"`
	Category          string          `json:"category"`
	TransactionDate   string          `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"`
	ReversalOf        pgtype.Int8     `json:"reversal_of"`
	StoreID           pgtype.Int8     `json:"store_id"`
}

// FOR UPDATE, and scoped for the same reason as GetCashflowByID: the mutation
// paths (update, delete, reverse) lock the row they are about to change, and an
// unscoped lock would both read and WRITE another outlet's cash.
func (q *Queries) GetCashflowByIDForUpdate(ctx context.Context, arg GetCashflowByIDForUpdateParams) (GetCashflowByIDForUpdateRow, error) {
	row := q.db.QueryRow(ctx, getCashflowByIDForUpdate, arg.ID, arg.StoreIds)
	var i GetCashflowByIDForUpdateRow
	err := row.Scan(
		&i.ID,
		&i.TransactionNumber,
		&i.Type,
		&i.Category,
		&i.TransactionDate,
		&i.Amount,
		&i.Description,
		&i.ReferenceType,
		&i.ReferenceID,
		&i.CreatedBy,
		&i.ReversalOf,
		&i.StoreID,
	)
	return i, err
}

const getInvoiceByID = `-- name: GetInvoiceByID :one
SELECT id, invoice_number, sales_order_id, customer_id, invoice_date::text, due_date::text, status,
       subtotal, tax_amount, total_amount, paid_amount, remaining_amount, notes, created_at, updated_at
FROM invoices WHERE id=$1
`

type GetInvoiceByIDRow struct {
	ID              int64              `json:"id"`
	InvoiceNumber   string             `json:"invoice_number"`
	SalesOrderID    pgtype.Int8        `json:"sales_order_id"`
	CustomerID      int64              `json:"customer_id"`
	InvoiceDate     string             `json:"invoice_date"`
	DueDate         string             `json:"due_date"`
	Status          string             `json:"status"`
	Subtotal        decimal.Decimal    `json:"subtotal"`
	TaxAmount       decimal.Decimal    `json:"tax_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"`
}

func (q *Queries) GetInvoiceByID(ctx context.Context, id int64) (GetInvoiceByIDRow, error) {
	row := q.db.QueryRow(ctx, getInvoiceByID, id)
	var i GetInvoiceByIDRow
	err := row.Scan(
		&i.ID,
		&i.InvoiceNumber,
		&i.SalesOrderID,
		&i.CustomerID,
		&i.InvoiceDate,
		&i.DueDate,
		&i.Status,
		&i.Subtotal,
		&i.TaxAmount,
		&i.TotalAmount,
		&i.PaidAmount,
		&i.RemainingAmount,
		&i.Notes,
		&i.CreatedAt,
		&i.UpdatedAt,
	)
	return i, err
}

const getInvoiceByIDForUpdate = `-- name: GetInvoiceByIDForUpdate :one
SELECT id, invoice_number, sales_order_id, customer_id, invoice_date::text, due_date::text, status,
       subtotal, tax_amount, total_amount, paid_amount, remaining_amount, notes, created_at, updated_at
FROM invoices WHERE id=$1
FOR UPDATE
`

type GetInvoiceByIDForUpdateRow struct {
	ID              int64              `json:"id"`
	InvoiceNumber   string             `json:"invoice_number"`
	SalesOrderID    pgtype.Int8        `json:"sales_order_id"`
	CustomerID      int64              `json:"customer_id"`
	InvoiceDate     string             `json:"invoice_date"`
	DueDate         string             `json:"due_date"`
	Status          string             `json:"status"`
	Subtotal        decimal.Decimal    `json:"subtotal"`
	TaxAmount       decimal.Decimal    `json:"tax_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"`
}

func (q *Queries) GetInvoiceByIDForUpdate(ctx context.Context, id int64) (GetInvoiceByIDForUpdateRow, error) {
	row := q.db.QueryRow(ctx, getInvoiceByIDForUpdate, id)
	var i GetInvoiceByIDForUpdateRow
	err := row.Scan(
		&i.ID,
		&i.InvoiceNumber,
		&i.SalesOrderID,
		&i.CustomerID,
		&i.InvoiceDate,
		&i.DueDate,
		&i.Status,
		&i.Subtotal,
		&i.TaxAmount,
		&i.TotalAmount,
		&i.PaidAmount,
		&i.RemainingAmount,
		&i.Notes,
		&i.CreatedAt,
		&i.UpdatedAt,
	)
	return i, err
}

const getInvoiceCreationStatusHistoryForUpdate = `-- name: GetInvoiceCreationStatusHistoryForUpdate :one
SELECT id, sales_order_id, from_status, to_status, reason, reference_type, reference_id, changed_by, created_at
FROM sales_order_status_history
WHERE sales_order_id=$1
  AND reference_type='App\Models\Invoice'
  AND reference_id=$2
  AND reason='invoice_created'
  AND to_status='invoiced'
ORDER BY id DESC
LIMIT 1
FOR UPDATE
`

type GetInvoiceCreationStatusHistoryForUpdateParams struct {
	SalesOrderID int64       `json:"sales_order_id"`
	ReferenceID  pgtype.Int8 `json:"reference_id"`
}

func (q *Queries) GetInvoiceCreationStatusHistoryForUpdate(ctx context.Context, arg GetInvoiceCreationStatusHistoryForUpdateParams) (SalesOrderStatusHistory, error) {
	row := q.db.QueryRow(ctx, getInvoiceCreationStatusHistoryForUpdate, arg.SalesOrderID, arg.ReferenceID)
	var i SalesOrderStatusHistory
	err := row.Scan(
		&i.ID,
		&i.SalesOrderID,
		&i.FromStatus,
		&i.ToStatus,
		&i.Reason,
		&i.ReferenceType,
		&i.ReferenceID,
		&i.ChangedBy,
		&i.CreatedAt,
	)
	return i, err
}

const getPaymentByIDForUpdate = `-- name: GetPaymentByIDForUpdate :one
SELECT p.id, p.payment_number, p.payable_type, p.payable_id, p.payment_date::text AS payment_date,
       p.amount, p.payment_method, p.bank_name, p.bank_account, p.reference_number,
       p.notes, p.proof_url, p.created_by, c.user_id AS owner_user_id
FROM payments p
LEFT JOIN invoices inv ON inv.id = p.payable_id AND p.payable_type = 'App\Models\Invoice'
LEFT JOIN customers c ON c.id = inv.customer_id
WHERE p.id = $1
FOR UPDATE OF p
`

type GetPaymentByIDForUpdateRow struct {
	ID              int64           `json:"id"`
	PaymentNumber   string          `json:"payment_number"`
	PayableType     string          `json:"payable_type"`
	PayableID       int64           `json:"payable_id"`
	PaymentDate     string          `json:"payment_date"`
	Amount          decimal.Decimal `json:"amount"`
	PaymentMethod   string          `json:"payment_method"`
	BankName        pgtype.Text     `json:"bank_name"`
	BankAccount     pgtype.Text     `json:"bank_account"`
	ReferenceNumber pgtype.Text     `json:"reference_number"`
	Notes           pgtype.Text     `json:"notes"`
	ProofUrl        pgtype.Text     `json:"proof_url"`
	CreatedBy       pgtype.Int8     `json:"created_by"`
	OwnerUserID     pgtype.Int8     `json:"owner_user_id"`
}

// Locks the payment row (FOR UPDATE OF p — the outer-joined sides cannot be
// locked) so concurrent partial payments against the same payable serialize.
// owner_user_id resolves the payment -> invoice -> customers.user_id linkage;
// it is NULL for non-invoice payables or customers without a linked user.
func (q *Queries) GetPaymentByIDForUpdate(ctx context.Context, id int64) (GetPaymentByIDForUpdateRow, error) {
	row := q.db.QueryRow(ctx, getPaymentByIDForUpdate, id)
	var i GetPaymentByIDForUpdateRow
	err := row.Scan(
		&i.ID,
		&i.PaymentNumber,
		&i.PayableType,
		&i.PayableID,
		&i.PaymentDate,
		&i.Amount,
		&i.PaymentMethod,
		&i.BankName,
		&i.BankAccount,
		&i.ReferenceNumber,
		&i.Notes,
		&i.ProofUrl,
		&i.CreatedBy,
		&i.OwnerUserID,
	)
	return i, err
}

const getSOHeaderByID = `-- name: GetSOHeaderByID :one
SELECT id, so_number, customer_id, status, total_amount FROM sales_orders WHERE id=$1
`

type GetSOHeaderByIDRow struct {
	ID          int64           `json:"id"`
	SoNumber    string          `json:"so_number"`
	CustomerID  int64           `json:"customer_id"`
	Status      string          `json:"status"`
	TotalAmount decimal.Decimal `json:"total_amount"`
}

func (q *Queries) GetSOHeaderByID(ctx context.Context, id int64) (GetSOHeaderByIDRow, error) {
	row := q.db.QueryRow(ctx, getSOHeaderByID, id)
	var i GetSOHeaderByIDRow
	err := row.Scan(
		&i.ID,
		&i.SoNumber,
		&i.CustomerID,
		&i.Status,
		&i.TotalAmount,
	)
	return i, err
}

const getSOHeaderByIDForUpdate = `-- name: GetSOHeaderByIDForUpdate :one
SELECT id, so_number, customer_id, status, total_amount FROM sales_orders WHERE id=$1
FOR UPDATE
`

type GetSOHeaderByIDForUpdateRow struct {
	ID          int64           `json:"id"`
	SoNumber    string          `json:"so_number"`
	CustomerID  int64           `json:"customer_id"`
	Status      string          `json:"status"`
	TotalAmount decimal.Decimal `json:"total_amount"`
}

func (q *Queries) GetSOHeaderByIDForUpdate(ctx context.Context, id int64) (GetSOHeaderByIDForUpdateRow, error) {
	row := q.db.QueryRow(ctx, getSOHeaderByIDForUpdate, id)
	var i GetSOHeaderByIDForUpdateRow
	err := row.Scan(
		&i.ID,
		&i.SoNumber,
		&i.CustomerID,
		&i.Status,
		&i.TotalAmount,
	)
	return i, err
}

const insertSOStatusHistory = `-- name: InsertSOStatusHistory :one
INSERT INTO sales_order_status_history
    (sales_order_id, from_status, to_status, reason, reference_type, reference_id, changed_by)
VALUES ($1,$2,$3,$4,$5,$6,$7)
RETURNING id
`

type InsertSOStatusHistoryParams struct {
	SalesOrderID  int64       `json:"sales_order_id"`
	FromStatus    string      `json:"from_status"`
	ToStatus      string      `json:"to_status"`
	Reason        string      `json:"reason"`
	ReferenceType pgtype.Text `json:"reference_type"`
	ReferenceID   pgtype.Int8 `json:"reference_id"`
	ChangedBy     pgtype.Int8 `json:"changed_by"`
}

func (q *Queries) InsertSOStatusHistory(ctx context.Context, arg InsertSOStatusHistoryParams) (int64, error) {
	row := q.db.QueryRow(ctx, insertSOStatusHistory,
		arg.SalesOrderID,
		arg.FromStatus,
		arg.ToStatus,
		arg.Reason,
		arg.ReferenceType,
		arg.ReferenceID,
		arg.ChangedBy,
	)
	var id int64
	err := row.Scan(&id)
	return id, err
}

const invoiceExistsForSO = `-- name: InvoiceExistsForSO :one
SELECT EXISTS(SELECT 1 FROM invoices WHERE sales_order_id=$1)
`

func (q *Queries) InvoiceExistsForSO(ctx context.Context, salesOrderID pgtype.Int8) (bool, error) {
	row := q.db.QueryRow(ctx, invoiceExistsForSO, salesOrderID)
	var exists bool
	err := row.Scan(&exists)
	return exists, err
}

const listCashflows = `-- name: ListCashflows :many
SELECT id, transaction_number, type, category, transaction_date::text, amount, description, reference_type, reference_id, created_by, reversal_of, store_id
FROM cashflows
WHERE ($3::text IS NULL OR type = $3)
  AND ($4::text IS NULL OR category = $4)
  AND ($5::date IS NULL OR transaction_date >= $5)
  AND ($6::date IS NULL OR transaction_date <= $6)
  AND ($7::bigint[] IS NULL
       OR store_id = ANY($7::bigint[]))
ORDER BY id DESC
LIMIT $1 OFFSET $2
`

type ListCashflowsParams struct {
	Limit    int32       `json:"limit"`
	Offset   int32       `json:"offset"`
	Type     pgtype.Text `json:"type"`
	Category pgtype.Text `json:"category"`
	DateFrom pgtype.Date `json:"date_from"`
	DateTo   pgtype.Date `json:"date_to"`
	StoreIds []int64     `json:"store_ids"`
}

type ListCashflowsRow struct {
	ID                int64           `json:"id"`
	TransactionNumber string          `json:"transaction_number"`
	Type              string          `json:"type"`
	Category          string          `json:"category"`
	TransactionDate   string          `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"`
	ReversalOf        pgtype.Int8     `json:"reversal_of"`
	StoreID           pgtype.Int8     `json:"store_id"`
}

// The store_ids predicate is the outlet scope (migration 000104). NULL array =
// platform-wide, which matches company-level rows too. A non-NULL array is the
// caller's stores, and it excludes store_id IS NULL because ANY() against NULL
// is NULL, which is not true — so company-level rows are invisible to a
// restricted caller by construction rather than by a second condition.
func (q *Queries) ListCashflows(ctx context.Context, arg ListCashflowsParams) ([]ListCashflowsRow, error) {
	rows, err := q.db.Query(ctx, listCashflows,
		arg.Limit,
		arg.Offset,
		arg.Type,
		arg.Category,
		arg.DateFrom,
		arg.DateTo,
		arg.StoreIds,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []ListCashflowsRow
	for rows.Next() {
		var i ListCashflowsRow
		if err := rows.Scan(
			&i.ID,
			&i.TransactionNumber,
			&i.Type,
			&i.Category,
			&i.TransactionDate,
			&i.Amount,
			&i.Description,
			&i.ReferenceType,
			&i.ReferenceID,
			&i.CreatedBy,
			&i.ReversalOf,
			&i.StoreID,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const listInvoiceItems = `-- name: ListInvoiceItems :many
SELECT i.id, i.sales_order_id, i.product_id, p.code AS product_code, p.name AS product_name,
       i.quantity, i.unit_price, i.discount_percent, i.discount_amount, i.subtotal
FROM sales_order_items i
JOIN products p ON p.id = i.product_id
WHERE i.sales_order_id=$1 ORDER BY i.id
`

type ListInvoiceItemsRow struct {
	ID              int64           `json:"id"`
	SalesOrderID    int64           `json:"sales_order_id"`
	ProductID       int64           `json:"product_id"`
	ProductCode     string          `json:"product_code"`
	ProductName     string          `json:"product_name"`
	Quantity        decimal.Decimal `json:"quantity"`
	UnitPrice       decimal.Decimal `json:"unit_price"`
	DiscountPercent decimal.Decimal `json:"discount_percent"`
	DiscountAmount  decimal.Decimal `json:"discount_amount"`
	Subtotal        decimal.Decimal `json:"subtotal"`
}

// There is no invoice_items table: an invoice's lines are the source sales
// order's items (CreateInvoice only references the SO). The detail view
// resolves them through invoices.sales_order_id.
func (q *Queries) ListInvoiceItems(ctx context.Context, salesOrderID int64) ([]ListInvoiceItemsRow, error) {
	rows, err := q.db.Query(ctx, listInvoiceItems, salesOrderID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []ListInvoiceItemsRow
	for rows.Next() {
		var i ListInvoiceItemsRow
		if err := rows.Scan(
			&i.ID,
			&i.SalesOrderID,
			&i.ProductID,
			&i.ProductCode,
			&i.ProductName,
			&i.Quantity,
			&i.UnitPrice,
			&i.DiscountPercent,
			&i.DiscountAmount,
			&i.Subtotal,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const listInvoices = `-- name: ListInvoices :many
SELECT i.id, i.invoice_number, i.sales_order_id, i.customer_id, i.invoice_date::text AS invoice_date, i.due_date::text AS due_date, i.status,
       i.subtotal, i.tax_amount, i.total_amount, i.paid_amount, i.remaining_amount, i.notes, i.created_at, i.updated_at,
       COALESCE(c.name, '') AS customer_name, COALESCE(so.so_number, '') AS so_number,
       so.store_id, COALESCE(st.name, '') AS store_name, COALESCE(so.order_type, '') AS order_type
FROM invoices i
LEFT JOIN customers c ON c.id = i.customer_id
LEFT JOIN sales_orders so ON so.id = i.sales_order_id
LEFT JOIN stores st ON st.id = so.store_id
WHERE ($3::text IS NULL OR (i.invoice_number ILIKE '%' || $3 || '%' OR i.customer_id IN (SELECT id FROM customers WHERE name ILIKE '%' || $3 || '%')))
  AND ($4::text IS NULL OR i.status = $4)
  AND ($5::bigint IS NULL OR $5::bigint <= 0 OR i.customer_id = $5)
  AND ($6::date IS NULL OR i.invoice_date >= $6)
  AND ($7::date IS NULL OR i.invoice_date <= $7)
  AND ($8::bigint IS NULL OR $8::bigint <= 0 OR so.store_id = $8)
ORDER BY
  CASE WHEN $9::text = 'invoice_number' AND $10::text = 'desc' THEN i.invoice_number END DESC,
  CASE WHEN $9::text = 'invoice_number' THEN i.invoice_number END ASC,
  CASE WHEN $9::text = 'customer_name' AND $10::text = 'desc' THEN c.name END DESC NULLS LAST,
  CASE WHEN $9::text = 'customer_name' THEN c.name END ASC NULLS LAST,
  CASE WHEN $9::text = 'invoice_date' AND $10::text = 'desc' THEN i.invoice_date END DESC,
  CASE WHEN $9::text = 'invoice_date' THEN i.invoice_date END ASC,
  CASE WHEN $9::text = 'due_date' AND $10::text = 'desc' THEN i.due_date END DESC NULLS LAST,
  CASE WHEN $9::text = 'due_date' THEN i.due_date END ASC NULLS LAST,
  CASE WHEN $9::text = 'status' AND $10::text = 'desc' THEN i.status END DESC,
  CASE WHEN $9::text = 'status' THEN i.status END ASC,
  CASE WHEN $9::text = 'total_amount' AND $10::text = 'desc' THEN i.total_amount END DESC,
  CASE WHEN $9::text = 'total_amount' THEN i.total_amount END ASC,
  CASE WHEN $9::text = 'remaining_amount' AND $10::text = 'desc' THEN i.remaining_amount END DESC,
  CASE WHEN $9::text = 'remaining_amount' THEN i.remaining_amount END ASC,
  i.id DESC
LIMIT $1 OFFSET $2
`

type ListInvoicesParams struct {
	Limit      int32       `json:"limit"`
	Offset     int32       `json:"offset"`
	Search     pgtype.Text `json:"search"`
	Status     pgtype.Text `json:"status"`
	CustomerID pgtype.Int8 `json:"customer_id"`
	DateFrom   pgtype.Date `json:"date_from"`
	DateTo     pgtype.Date `json:"date_to"`
	StoreID    pgtype.Int8 `json:"store_id"`
	SortBy     pgtype.Text `json:"sort_by"`
	SortDir    pgtype.Text `json:"sort_dir"`
}

type ListInvoicesRow struct {
	ID              int64              `json:"id"`
	InvoiceNumber   string             `json:"invoice_number"`
	SalesOrderID    pgtype.Int8        `json:"sales_order_id"`
	CustomerID      int64              `json:"customer_id"`
	InvoiceDate     string             `json:"invoice_date"`
	DueDate         string             `json:"due_date"`
	Status          string             `json:"status"`
	Subtotal        decimal.Decimal    `json:"subtotal"`
	TaxAmount       decimal.Decimal    `json:"tax_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"`
	CustomerName    string             `json:"customer_name"`
	SoNumber        string             `json:"so_number"`
	StoreID         pgtype.Int8        `json:"store_id"`
	StoreName       string             `json:"store_name"`
	OrderType       string             `json:"order_type"`
}

func (q *Queries) ListInvoices(ctx context.Context, arg ListInvoicesParams) ([]ListInvoicesRow, error) {
	rows, err := q.db.Query(ctx, listInvoices,
		arg.Limit,
		arg.Offset,
		arg.Search,
		arg.Status,
		arg.CustomerID,
		arg.DateFrom,
		arg.DateTo,
		arg.StoreID,
		arg.SortBy,
		arg.SortDir,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []ListInvoicesRow
	for rows.Next() {
		var i ListInvoicesRow
		if err := rows.Scan(
			&i.ID,
			&i.InvoiceNumber,
			&i.SalesOrderID,
			&i.CustomerID,
			&i.InvoiceDate,
			&i.DueDate,
			&i.Status,
			&i.Subtotal,
			&i.TaxAmount,
			&i.TotalAmount,
			&i.PaidAmount,
			&i.RemainingAmount,
			&i.Notes,
			&i.CreatedAt,
			&i.UpdatedAt,
			&i.CustomerName,
			&i.SoNumber,
			&i.StoreID,
			&i.StoreName,
			&i.OrderType,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const listPayments = `-- name: ListPayments :many
SELECT p.id, p.payment_number, p.payable_type, p.payable_id, p.payment_date::text AS payment_date, p.amount, p.payment_method,
       p.bank_name, p.bank_account, p.reference_number, p.notes, p.proof_url, p.created_by,
       COALESCE(inv.invoice_number, so.so_number, gt.transaction_number, '') AS payable_label,
       COALESCE(ci.name, cs.name, cg.name, '') AS customer_name,
       COALESCE(so_inv.store_id, so.store_id, gt.store_id) AS store_id,
       COALESCE(st_inv.name, st_so.name, st_gt.name, '') AS store_name,
       COALESCE(so_inv.order_type, so.order_type, CASE WHEN p.payable_type IN ('galon', 'galon_deposit') THEN 'pos' ELSE '' END) AS order_type
FROM payments p
LEFT JOIN invoices inv ON inv.id = p.payable_id AND (p.payable_type = 'App\Models\Invoice' OR p.payable_type = 'invoice')
LEFT JOIN sales_orders so_inv ON so_inv.id = inv.sales_order_id
LEFT JOIN stores st_inv ON st_inv.id = so_inv.store_id
LEFT JOIN sales_orders so ON so.id = p.payable_id AND (p.payable_type = 'App\Models\SalesOrder' OR p.payable_type = 'sales_order')
LEFT JOIN stores st_so ON st_so.id = so.store_id
LEFT JOIN galon_transactions gt ON gt.id = p.payable_id AND (p.payable_type = 'galon' OR p.payable_type = 'galon_deposit')
LEFT JOIN stores st_gt ON st_gt.id = gt.store_id
LEFT JOIN customers ci ON ci.id = inv.customer_id
LEFT JOIN customers cs ON cs.id = so.customer_id
LEFT JOIN customers cg ON cg.id = gt.customer_id
WHERE ($3::text IS NULL OR p.payment_number ILIKE '%' || $3 || '%')
  AND ($4::date IS NULL OR p.payment_date >= $4)
  AND ($5::date IS NULL OR p.payment_date <= $5)
  AND ($6::bigint IS NULL OR $6::bigint <= 0 OR inv.customer_id = $6)
  AND ($7::bigint IS NULL OR $7::bigint <= 0 OR p.payable_id = $7)
  AND ($8::text IS NULL OR p.payable_type = $8)
  AND ($9::bigint IS NULL OR $9::bigint <= 0 OR COALESCE(so_inv.store_id, so.store_id, gt.store_id) = $9)
ORDER BY p.id DESC
LIMIT $1 OFFSET $2
`

type ListPaymentsParams struct {
	Limit       int32       `json:"limit"`
	Offset      int32       `json:"offset"`
	Search      pgtype.Text `json:"search"`
	DateFrom    pgtype.Date `json:"date_from"`
	DateTo      pgtype.Date `json:"date_to"`
	CustomerID  pgtype.Int8 `json:"customer_id"`
	PayableID   pgtype.Int8 `json:"payable_id"`
	PayableType pgtype.Text `json:"payable_type"`
	StoreID     pgtype.Int8 `json:"store_id"`
}

type ListPaymentsRow struct {
	ID              int64           `json:"id"`
	PaymentNumber   string          `json:"payment_number"`
	PayableType     string          `json:"payable_type"`
	PayableID       int64           `json:"payable_id"`
	PaymentDate     string          `json:"payment_date"`
	Amount          decimal.Decimal `json:"amount"`
	PaymentMethod   string          `json:"payment_method"`
	BankName        pgtype.Text     `json:"bank_name"`
	BankAccount     pgtype.Text     `json:"bank_account"`
	ReferenceNumber pgtype.Text     `json:"reference_number"`
	Notes           pgtype.Text     `json:"notes"`
	ProofUrl        pgtype.Text     `json:"proof_url"`
	CreatedBy       pgtype.Int8     `json:"created_by"`
	PayableLabel    string          `json:"payable_label"`
	CustomerName    string          `json:"customer_name"`
	StoreID         pgtype.Int8     `json:"store_id"`
	StoreName       string          `json:"store_name"`
	OrderType       string          `json:"order_type"`
}

// For buyer-scoped pages (customer_id set) the WHERE pins the customer on the
// invoice side via the join; the pagination walk stays cheap through
// idx_payments_created_id (created_at, id DESC).
func (q *Queries) ListPayments(ctx context.Context, arg ListPaymentsParams) ([]ListPaymentsRow, error) {
	rows, err := q.db.Query(ctx, listPayments,
		arg.Limit,
		arg.Offset,
		arg.Search,
		arg.DateFrom,
		arg.DateTo,
		arg.CustomerID,
		arg.PayableID,
		arg.PayableType,
		arg.StoreID,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []ListPaymentsRow
	for rows.Next() {
		var i ListPaymentsRow
		if err := rows.Scan(
			&i.ID,
			&i.PaymentNumber,
			&i.PayableType,
			&i.PayableID,
			&i.PaymentDate,
			&i.Amount,
			&i.PaymentMethod,
			&i.BankName,
			&i.BankAccount,
			&i.ReferenceNumber,
			&i.Notes,
			&i.ProofUrl,
			&i.CreatedBy,
			&i.PayableLabel,
			&i.CustomerName,
			&i.StoreID,
			&i.StoreName,
			&i.OrderType,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const listReceivables = `-- name: ListReceivables :many
SELECT inv.id, inv.invoice_number, inv.customer_id, c.name, c.phone, COALESCE(c.address, '') AS customer_address,
       inv.invoice_date::text, inv.due_date::text,
       inv.status, inv.total_amount, inv.paid_amount, inv.remaining_amount,
       COALESCE(so.so_number, '') AS so_number, so.payment_terms, se.name AS sales_name,
       so.store_id, COALESCE(st.name, '') AS store_name, COALESCE(so.order_type, '') AS order_type
FROM invoices inv
JOIN customers c ON c.id = inv.customer_id
LEFT JOIN sales_orders so ON so.id = inv.sales_order_id
LEFT JOIN stores st ON st.id = so.store_id
LEFT JOIN employees se ON se.id = so.sales_id
WHERE inv.status IN ('unpaid','partial','paid')
  AND ($3::bigint IS NULL OR $3::bigint <= 0 OR inv.customer_id = $3)
  AND ($4::bigint IS NULL OR $4::bigint <= 0 OR so.store_id = $4)
  AND (
    $5::text IS NULL
    OR ($5::text = 'overdue' AND inv.status IN ('unpaid', 'partial') AND inv.due_date < CURRENT_DATE AND inv.remaining_amount > 0)
    OR ($5::text <> 'overdue' AND inv.status = $5)
  )
ORDER BY inv.id DESC
LIMIT $1 OFFSET $2
`

type ListReceivablesParams struct {
	Limit      int32       `json:"limit"`
	Offset     int32       `json:"offset"`
	CustomerID pgtype.Int8 `json:"customer_id"`
	StoreID    pgtype.Int8 `json:"store_id"`
	Status     pgtype.Text `json:"status"`
}

type ListReceivablesRow struct {
	ID              int64           `json:"id"`
	InvoiceNumber   string          `json:"invoice_number"`
	CustomerID      int64           `json:"customer_id"`
	Name            string          `json:"name"`
	Phone           pgtype.Text     `json:"phone"`
	CustomerAddress string          `json:"customer_address"`
	InvInvoiceDate  string          `json:"inv_invoice_date"`
	InvDueDate      string          `json:"inv_due_date"`
	Status          string          `json:"status"`
	TotalAmount     decimal.Decimal `json:"total_amount"`
	PaidAmount      decimal.Decimal `json:"paid_amount"`
	RemainingAmount decimal.Decimal `json:"remaining_amount"`
	SoNumber        string          `json:"so_number"`
	PaymentTerms    pgtype.Int4     `json:"payment_terms"`
	SalesName       pgtype.Text     `json:"sales_name"`
	StoreID         pgtype.Int8     `json:"store_id"`
	StoreName       string          `json:"store_name"`
	OrderType       string          `json:"order_type"`
}

// sales_id FKs employees(id), so the salesperson name comes from employees.
// See GetSalesOrderByID in sales.sql and the migration that remapped the
// legacy users.id values.
func (q *Queries) ListReceivables(ctx context.Context, arg ListReceivablesParams) ([]ListReceivablesRow, error) {
	rows, err := q.db.Query(ctx, listReceivables,
		arg.Limit,
		arg.Offset,
		arg.CustomerID,
		arg.StoreID,
		arg.Status,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []ListReceivablesRow
	for rows.Next() {
		var i ListReceivablesRow
		if err := rows.Scan(
			&i.ID,
			&i.InvoiceNumber,
			&i.CustomerID,
			&i.Name,
			&i.Phone,
			&i.CustomerAddress,
			&i.InvInvoiceDate,
			&i.InvDueDate,
			&i.Status,
			&i.TotalAmount,
			&i.PaidAmount,
			&i.RemainingAmount,
			&i.SoNumber,
			&i.PaymentTerms,
			&i.SalesName,
			&i.StoreID,
			&i.StoreName,
			&i.OrderType,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const paymentProofOwnersByKey = `-- name: PaymentProofOwnersByKey :many
SELECT c.user_id
FROM payments p
LEFT JOIN invoices inv ON inv.id = p.payable_id AND p.payable_type = 'App\Models\Invoice'
LEFT JOIN customers c ON c.id = inv.customer_id
WHERE p.proof_url IS NOT NULL
  AND p.proof_url = ANY($1::text[])
ORDER BY c.user_id NULLS LAST
`

// Authoritative read-path lookup for GET /uploads/{key}: the payments table is
// the source of truth for which stored objects are private payment proofs.
// Matches both the stored URL form ('/uploads/<key>') and a bare key so legacy
// rows written before proofs were key-prefixed resolve identically. Any match
// (even a NULL user_id, i.e. a payment whose customer has no linked user)
// proves the object is a private document, so the caller can still tell
// "private proof" from "public image".
//
// Both candidate strings arrive as bound parameters and are compared with
// `= ANY(...)` so payments_proof_url_idx (000100) can serve them: the earlier
// `col = $1 OR col = '/uploads/' || $1` form could not, because one branch
// compares the column to an expression and BitmapOr needs every branch to be
// indexable. `= ANY` over a bound array is character-for-character equivalent
// to that OR, so the match set — and therefore the authorization outcome — is
// unchanged.
func (q *Queries) PaymentProofOwnersByKey(ctx context.Context, proofKeys []string) ([]pgtype.Int8, error) {
	rows, err := q.db.Query(ctx, paymentProofOwnersByKey, proofKeys)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []pgtype.Int8
	for rows.Next() {
		var user_id pgtype.Int8
		if err := rows.Scan(&user_id); err != nil {
			return nil, err
		}
		items = append(items, user_id)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const paymentsExistForInvoice = `-- name: PaymentsExistForInvoice :one
SELECT EXISTS(
    SELECT 1 FROM payments
    WHERE payable_type = 'App\Models\Invoice' AND payable_id = $1
)
`

func (q *Queries) PaymentsExistForInvoice(ctx context.Context, payableID int64) (bool, error) {
	row := q.db.QueryRow(ctx, paymentsExistForInvoice, payableID)
	var exists bool
	err := row.Scan(&exists)
	return exists, err
}

const setPaymentProofURL = `-- name: SetPaymentProofURL :execrows
UPDATE payments SET proof_url=$2, updated_at=now() WHERE id=$1
`

type SetPaymentProofURLParams struct {
	ID       int64       `json:"id"`
	ProofUrl pgtype.Text `json:"proof_url"`
}

func (q *Queries) SetPaymentProofURL(ctx context.Context, arg SetPaymentProofURLParams) (int64, error) {
	result, err := q.db.Exec(ctx, setPaymentProofURL, arg.ID, arg.ProofUrl)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected(), nil
}

const setPaymentReview = `-- name: SetPaymentReview :execrows
UPDATE payments
SET notes = CASE
        WHEN notes IS NULL OR notes = '' THEN $2::text
        -- split_part returns '' when the old verdict has no ' — ' remainder
        -- (verdict-only notes); appending the delimiter then leaves an
        -- orphan tail (R2-2), so only re-attach a non-blank remainder.
        WHEN notes LIKE '[Verified]%' OR notes LIKE '[Rejected]%' THEN $2::text || CASE WHEN NULLIF(TRIM(split_part(notes, ' — ', 2)), '') IS NULL THEN '' ELSE ' — ' || TRIM(split_part(notes, ' — ', 2)) END
        ELSE $2::text || ' — ' || notes
    END,
    updated_at = now()
WHERE id = $1
`

type SetPaymentReviewParams struct {
	ID      int64  `json:"id"`
	Verdict string `json:"verdict"`
}

// VerifyPayment audit write. The payments table has no dedicated review
// columns (migration-light by design); the reviewer verdict is stamped onto
// notes with a machine-readable [Verified]/[Rejected] prefix so it survives
// alongside any human note and is idempotent (re-verify overwrites).
//
// sqlcarg(sqlc.arg(verdict)) NAMES the parameter. Left as a bare $2, sqlc
// derives the field as Arg2 and SetPaymentReviewParams loses its Verdict
// field, which breaks internal/finance/repository.go:218 at compile time —
// so the name lives here, in the source of truth, rather than being
// hand-patched into the generated file (which the next `sqlc generate`
// silently overwrites, exactly as it did).
//
// The ::text casts are what make the generated field a plain `string`.
// Inside the CASE arms sqlc otherwise infers the nullable pgtype.Text,
// because notes is nullable, and the repository cannot pass that without a
// wrapper. The verdict is never NULL by construction — every arm
// concatenates the argument — so the cast loses nothing.
func (q *Queries) SetPaymentReview(ctx context.Context, arg SetPaymentReviewParams) (int64, error) {
	result, err := q.db.Exec(ctx, setPaymentReview, arg.ID, arg.Verdict)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected(), nil
}

const setSOStatus = `-- name: SetSOStatus :execrows
UPDATE sales_orders SET status=$2, updated_at=now() WHERE id=$1
`

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

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

const sumCustomerPayments = `-- name: SumCustomerPayments :one
SELECT COALESCE(SUM(p.amount), 0)::numeric AS total, count(*)::bigint AS row_count
FROM payments p
JOIN invoices inv ON inv.id = p.payable_id AND p.payable_type = 'App\Models\Invoice'
WHERE inv.customer_id = $1
  AND ($2::date IS NULL OR p.payment_date >= $2)
  AND ($3::date IS NULL OR p.payment_date <= $3)
`

type SumCustomerPaymentsParams struct {
	CustomerID int64       `json:"customer_id"`
	DateFrom   pgtype.Date `json:"date_from"`
	DateTo     pgtype.Date `json:"date_to"`
}

type SumCustomerPaymentsRow struct {
	Total    decimal.Decimal `json:"total"`
	RowCount int64           `json:"row_count"`
}

// Buyer payment total in one scan: the customer's invoices joined to their
// payments (no LEFT-join fan-out, no extra COUNT query — MyPayments returns
// total from the same round trip).
func (q *Queries) SumCustomerPayments(ctx context.Context, arg SumCustomerPaymentsParams) (SumCustomerPaymentsRow, error) {
	row := q.db.QueryRow(ctx, sumCustomerPayments, arg.CustomerID, arg.DateFrom, arg.DateTo)
	var i SumCustomerPaymentsRow
	err := row.Scan(&i.Total, &i.RowCount)
	return i, err
}

const sumReceivables = `-- name: SumReceivables :one
SELECT COALESCE(SUM(inv.remaining_amount),0)::numeric AS total FROM invoices inv
LEFT JOIN sales_orders so ON so.id = inv.sales_order_id
WHERE inv.status IN ('unpaid','partial','paid')
  AND ($1::bigint IS NULL OR $1::bigint <= 0 OR inv.customer_id = $1)
  AND ($2::bigint IS NULL OR $2::bigint <= 0 OR so.store_id = $2)
  AND (
    $3::text IS NULL
    OR ($3::text = 'overdue' AND inv.status IN ('unpaid', 'partial') AND inv.due_date < CURRENT_DATE AND inv.remaining_amount > 0)
    OR ($3::text <> 'overdue' AND inv.status = $3)
  )
`

type SumReceivablesParams struct {
	CustomerID pgtype.Int8 `json:"customer_id"`
	StoreID    pgtype.Int8 `json:"store_id"`
	Status     pgtype.Text `json:"status"`
}

func (q *Queries) SumReceivables(ctx context.Context, arg SumReceivablesParams) (decimal.Decimal, error) {
	row := q.db.QueryRow(ctx, sumReceivables, arg.CustomerID, arg.StoreID, arg.Status)
	var total decimal.Decimal
	err := row.Scan(&total)
	return total, err
}

const summarizeCashflows = `-- name: SummarizeCashflows :one
SELECT COALESCE(SUM(amount) FILTER (WHERE type='income'),0)::numeric AS income,
       COALESCE(SUM(amount) FILTER (WHERE type='expense'),0)::numeric AS expense
FROM cashflows
WHERE ($1::text IS NULL OR type = $1)
  AND ($2::text IS NULL OR category = $2)
  AND ($3::date IS NULL OR transaction_date >= $3)
  AND ($4::date IS NULL OR transaction_date <= $4)
  AND ($5::bigint[] IS NULL
       OR store_id = ANY($5::bigint[]))
`

type SummarizeCashflowsParams struct {
	Type     pgtype.Text `json:"type"`
	Category pgtype.Text `json:"category"`
	DateFrom pgtype.Date `json:"date_from"`
	DateTo   pgtype.Date `json:"date_to"`
	StoreIds []int64     `json:"store_ids"`
}

type SummarizeCashflowsRow struct {
	Income  decimal.Decimal `json:"income"`
	Expense decimal.Decimal `json:"expense"`
}

// An AGGREGATE, so scoping only the row list would still leak: a sum over
// unscoped rows is the company's total movement even when no individual row is
// shown. This is the reason the outlet predicate has to be repeated on every
// cashflow read rather than added once to the list.
func (q *Queries) SummarizeCashflows(ctx context.Context, arg SummarizeCashflowsParams) (SummarizeCashflowsRow, error) {
	row := q.db.QueryRow(ctx, summarizeCashflows,
		arg.Type,
		arg.Category,
		arg.DateFrom,
		arg.DateTo,
		arg.StoreIds,
	)
	var i SummarizeCashflowsRow
	err := row.Scan(&i.Income, &i.Expense)
	return i, err
}

const updateCashflow = `-- name: UpdateCashflow :one
UPDATE cashflows
SET type=$2, category=$3, transaction_date=$4, amount=$5, description=$6, updated_at=now()
WHERE id=$1
RETURNING id, transaction_number, type, category, transaction_date::text, amount,
          description, reference_type, reference_id, created_by, reversal_of, store_id
`

type UpdateCashflowParams struct {
	ID              int64           `json:"id"`
	Type            string          `json:"type"`
	Category        string          `json:"category"`
	TransactionDate pgtype.Date     `json:"transaction_date"`
	Amount          decimal.Decimal `json:"amount"`
	Description     pgtype.Text     `json:"description"`
}

type UpdateCashflowRow struct {
	ID                int64           `json:"id"`
	TransactionNumber string          `json:"transaction_number"`
	Type              string          `json:"type"`
	Category          string          `json:"category"`
	TransactionDate   string          `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"`
	ReversalOf        pgtype.Int8     `json:"reversal_of"`
	StoreID           pgtype.Int8     `json:"store_id"`
}

// store_id is returned so the response carries the outlet the row belongs to. A
// mutation response that omitted it would be a response that cannot be checked
// against the scope that authorised it.
func (q *Queries) UpdateCashflow(ctx context.Context, arg UpdateCashflowParams) (UpdateCashflowRow, error) {
	row := q.db.QueryRow(ctx, updateCashflow,
		arg.ID,
		arg.Type,
		arg.Category,
		arg.TransactionDate,
		arg.Amount,
		arg.Description,
	)
	var i UpdateCashflowRow
	err := row.Scan(
		&i.ID,
		&i.TransactionNumber,
		&i.Type,
		&i.Category,
		&i.TransactionDate,
		&i.Amount,
		&i.Description,
		&i.ReferenceType,
		&i.ReferenceID,
		&i.CreatedBy,
		&i.ReversalOf,
		&i.StoreID,
	)
	return i, err
}

const updateInvoice = `-- name: UpdateInvoice :execrows
UPDATE invoices SET status=$2, paid_amount=$3, remaining_amount=$4, notes=$5, updated_at=now()
WHERE id=$1
`

type UpdateInvoiceParams struct {
	ID              int64           `json:"id"`
	Status          string          `json:"status"`
	PaidAmount      decimal.Decimal `json:"paid_amount"`
	RemainingAmount decimal.Decimal `json:"remaining_amount"`
	Notes           pgtype.Text     `json:"notes"`
}

func (q *Queries) UpdateInvoice(ctx context.Context, arg UpdateInvoiceParams) (int64, error) {
	result, err := q.db.Exec(ctx, updateInvoice,
		arg.ID,
		arg.Status,
		arg.PaidAmount,
		arg.RemainingAmount,
		arg.Notes,
	)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected(), nil
}