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

package db

import (
	"context"

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

const balanceSheetSnapshot = `-- name: BalanceSheetSnapshot :one
SELECT
  (
    (SELECT COALESCE(SUM(amount),0)::numeric FROM payments)
    - (SELECT COALESCE(SUM(paid_amount),0)::numeric FROM purchase_orders WHERE paid_amount > 0)
  )::numeric AS cash,
  (SELECT COALESCE(SUM(s.quantity * p.purchase_price),0)::numeric
   FROM stocks s JOIN products p ON p.id = s.product_id
   WHERE p.deleted_at IS NULL) AS inventory
`

type BalanceSheetSnapshotRow struct {
	Cash      decimal.Decimal `json:"cash"`
	Inventory decimal.Decimal `json:"inventory"`
}

// Cash truth (ruling A): system-recorded money only — customer receipts
// (payments) minus supplier payments (purchase_orders.paid_amount). Manual
// Arus Kas entries are deliberately NOT summed here: every payment the
// system records would otherwise be counted twice (once as the payment
// row, once as the manual entry PayInvoice instructs). Manual cash
// operations live on in the cashflow-statement report; they belong in
// Neraca Kas only when booked through a module (PO, payroll), not as
// manual entries.
func (q *Queries) BalanceSheetSnapshot(ctx context.Context) (BalanceSheetSnapshotRow, error) {
	row := q.db.QueryRow(ctx, balanceSheetSnapshot)
	var i BalanceSheetSnapshotRow
	err := row.Scan(&i.Cash, &i.Inventory)
	return i, err
}

const cashAccountBalanceBefore = `-- name: CashAccountBalanceBefore :one
WITH prev_movements AS (
  SELECT (jl.debit - jl.credit)::numeric AS delta
  FROM journal_lines jl
  JOIN journal_entries je ON je.id = jl.journal_entry_id
  JOIN accounts a ON a.id = jl.account_id
  WHERE a.code = ANY($1::text[])
    AND je.entry_date < $2::date

  UNION ALL

  SELECT p.amount::numeric AS delta
  FROM payments p
  WHERE p.payment_date < $2::date

  UNION ALL

  SELECT (CASE WHEN cf.type = 'income' THEN cf.amount ELSE -cf.amount END)::numeric AS delta
  FROM cashflows cf
  WHERE cf.transaction_date < $2::date
)
SELECT COALESCE(SUM(delta), 0)::numeric AS balance FROM prev_movements
`

type CashAccountBalanceBeforeParams struct {
	CashCodes []string    `json:"cash_codes"`
	Before    pgtype.Date `json:"before"`
}

// Opening cash: every cash-account line dated strictly before the period start.
// Debits raise cash, credits lower it, so the balance is the signed sum.
func (q *Queries) CashAccountBalanceBefore(ctx context.Context, arg CashAccountBalanceBeforeParams) (decimal.Decimal, error) {
	row := q.db.QueryRow(ctx, cashAccountBalanceBefore, arg.CashCodes, arg.Before)
	var balance decimal.Decimal
	err := row.Scan(&balance)
	return balance, err
}

const cashAccountCodes = `-- name: CashAccountCodes :many
SELECT code
FROM accounts
WHERE code LIKE '1000.%' AND is_active
ORDER BY code
`

// Cash accounts for the statement, discovered from the chart rather than
// hardcoded, so a newly added bank account joins the statement without a code
// change. 1000.x is the cash-and-equivalents range in this chart.
func (q *Queries) CashAccountCodes(ctx context.Context) ([]string, error) {
	rows, err := q.db.Query(ctx, cashAccountCodes)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []string
	for rows.Next() {
		var code string
		if err := rows.Scan(&code); err != nil {
			return nil, err
		}
		items = append(items, code)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const cashflowCSVRows = `-- name: CashflowCSVRows :many
SELECT
    p.id,
    p.payment_number AS transaction_number,
    COALESCE(inv.customer_id, so.customer_id, 0)::int8 AS partner_id,
    COALESCE(ci.name, cs.name, 'Pelanggan')::text AS partner,
    'income'::text AS kind,
    p.payment_date::text AS date,
    ''::text AS due_date,
    'completed'::text AS status,
    p.amount,
    p.amount AS paid_amount,
    0::numeric AS remaining_amount,
    'sales'::text AS category,
    p.payable_type AS reference_type,
    p.payable_id AS reference_id
FROM payments p
LEFT JOIN invoices inv ON inv.id = p.payable_id AND p.payable_type = 'App\Models\Invoice'
LEFT JOIN sales_orders so ON so.id = p.payable_id AND p.payable_type = 'App\Models\SalesOrder'
LEFT JOIN customers ci ON ci.id = inv.customer_id
LEFT JOIN customers cs ON cs.id = so.customer_id
WHERE ($1::date IS NULL OR p.payment_date >= $1)
  AND ($2::date IS NULL OR p.payment_date <= $2)

UNION ALL

SELECT
    po.id,
    po.po_number AS transaction_number,
    po.supplier_id::int8 AS partner_id,
    COALESCE(s.name, 'Supplier')::text AS partner,
    'expense'::text AS kind,
    po.order_date::text AS date,
    COALESCE(po.due_date::text, '')::text AS due_date,
    po.status::text AS status,
    po.paid_amount AS amount,
    po.paid_amount,
    po.remaining_amount,
    'purchase'::text AS category,
    'purchase_order'::text AS reference_type,
    po.id::int8 AS reference_id
FROM purchase_orders po
LEFT JOIN suppliers s ON s.id = po.supplier_id
WHERE po.paid_amount > 0
  AND ($1::date IS NULL OR po.order_date >= $1)
  AND ($2::date IS NULL OR po.order_date <= $2)

UNION ALL

SELECT
    cf.id,
    cf.transaction_number,
    0::int8 AS partner_id,
    ''::text AS partner,
    cf.type::text AS kind,
    cf.transaction_date::text AS date,
    ''::text AS due_date,
    ''::text AS status,
    cf.amount,
    0::numeric AS paid_amount,
    0::numeric AS remaining_amount,
    cf.category::text AS category,
    -- COALESCE on both, and this is a BUG FIX rather than a style choice. The
    -- other two arms supply literals (''::text, 0::int8), so sqlc resolved the
    -- UNION's column type from them and generated a non-nullable Go string. This
    -- arm supplies the raw nullable cashflows columns, so a manual cashflow —
    -- reference_type IS NULL for every one of them, and a manual cashflow is
    -- precisely what a till operator records — produced NULL, and pgx failed the
    -- scan with "cannot scan NULL into *string". The cashflow export therefore
    -- 500'd for the cashier's own rows.
    --
    -- The empty string and the zero are what the other arms already send, so the
    -- fix makes this arm's shape match the union's resolved type rather than
    -- inventing new sentinels. A non-nil ReferenceType on a generated row is
    -- still meaningful: a reversed or system-generated entry keeps it.
    COALESCE(cf.reference_type::text, ''::text) AS reference_type,
    COALESCE(cf.reference_id::int8, 0::int8) AS reference_id
FROM cashflows cf
WHERE ($1::date IS NULL OR cf.transaction_date >= $1)
  AND ($2::date IS NULL OR cf.transaction_date <= $2)
  AND ($3::bigint[] IS NULL OR cf.store_id = ANY($3::bigint[]))

ORDER BY date ASC, id ASC
`

type CashflowCSVRowsParams struct {
	DateFrom pgtype.Date `json:"date_from"`
	DateTo   pgtype.Date `json:"date_to"`
	StoreIds []int64     `json:"store_ids"`
}

type CashflowCSVRowsRow struct {
	ID                int64           `json:"id"`
	TransactionNumber string          `json:"transaction_number"`
	PartnerID         int64           `json:"partner_id"`
	Partner           string          `json:"partner"`
	Kind              string          `json:"kind"`
	Date              string          `json:"date"`
	DueDate           string          `json:"due_date"`
	Status            string          `json:"status"`
	Amount            decimal.Decimal `json:"amount"`
	PaidAmount        decimal.Decimal `json:"paid_amount"`
	RemainingAmount   decimal.Decimal `json:"remaining_amount"`
	Category          string          `json:"category"`
	ReferenceType     string          `json:"reference_type"`
	ReferenceID       int64           `json:"reference_id"`
}

// SCOPED (migration 000104), and the store_ids predicate is repeated on EACH
// arm of the UNION ALL rather than once around it. A predicate outside the union
// would have to name the alias in three different arms and, more importantly,
// would be three separate filters that a future edit could drop from one of them.
//
// The third arm is the cashflows one and it is the leak this whole change is
// about: cashier holds finance.cashflows.export (000080:17-28), so before this
// the CSV was a company-wide cashflow dump. A CSV is also the easiest thing to
// exfiltrate out of the five read paths, because it leaves as a file.
//
// The first two arms read payments and purchase_orders, which have no
// store_id, so they are NOT filtered here. That is a real and stated gap, not
// an oversight — see the "WHAT THIS DOES NOT CLOSE" note in the service. A
// restricted caller's export of this kind still shows the sales and purchase
// legs, and narrowing that needs a store column on payments/purchase_orders,
// which is a separate migration.
func (q *Queries) CashflowCSVRows(ctx context.Context, arg CashflowCSVRowsParams) ([]CashflowCSVRowsRow, error) {
	rows, err := q.db.Query(ctx, cashflowCSVRows, arg.DateFrom, arg.DateTo, arg.StoreIds)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []CashflowCSVRowsRow
	for rows.Next() {
		var i CashflowCSVRowsRow
		if err := rows.Scan(
			&i.ID,
			&i.TransactionNumber,
			&i.PartnerID,
			&i.Partner,
			&i.Kind,
			&i.Date,
			&i.DueDate,
			&i.Status,
			&i.Amount,
			&i.PaidAmount,
			&i.RemainingAmount,
			&i.Category,
			&i.ReferenceType,
			&i.ReferenceID,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const cashflowStatementMovements = `-- name: CashflowStatementMovements :many
SELECT
  je.entry_date AS tx_date,
  je.journal_number::text AS journal_number,
  cash_a.code::text AS cash_code,
  cash_a.name::text AS cash_name,
  CASE WHEN jl.debit > 0 THEN 'D'::text ELSE 'K'::text END AS side,
  GREATEST(jl.debit, jl.credit)::numeric AS amount,
  COALESCE(cp.code, '')::text AS counterpart_code,
  COALESCE(cp.name, '')::text AS counterpart_name,
  (CASE WHEN jl.debit > 0 THEN 1 ELSE -1 END)::int AS credit_sign
FROM journal_lines jl
JOIN journal_entries je ON je.id = jl.journal_entry_id
JOIN accounts cash_a ON cash_a.id = jl.account_id
LEFT JOIN LATERAL (
  SELECT a2.code, a2.name
  FROM journal_lines o
  JOIN accounts a2 ON a2.id = o.account_id
  WHERE o.journal_entry_id = jl.journal_entry_id AND o.id <> jl.id
  ORDER BY (a2.code NOT LIKE '1000.%') DESC, o.id
  LIMIT 1
) cp ON TRUE
WHERE cash_a.code = ANY($1::text[])
  AND je.entry_date >= $2
  AND je.entry_date < $3

UNION ALL

SELECT
  p.payment_date AS tx_date,
  p.payment_number::text AS journal_number,
  CASE
    WHEN p.payment_method = 'cash' THEN '1000.01.01'
    WHEN p.payment_method IN ('transfer', 'kartu') THEN '1000.03.01'
    ELSE '1000.01.02'
  END::text AS cash_code,
  CASE
    WHEN p.payment_method = 'cash' THEN 'Kas Tunai Toko'
    WHEN p.payment_method IN ('transfer', 'kartu') THEN 'Bank BCA Rekening Perusahaan'
    ELSE 'Kas & Bank Operasional'
  END::text AS cash_name,
  'D'::text AS side,
  p.amount::numeric AS amount,
  '4000.01'::text AS counterpart_code,
  ('Penerimaan Penjualan ' || COALESCE(ci.name, cs.name, 'Pelanggan'))::text AS counterpart_name,
  1::int AS credit_sign
FROM payments p
LEFT JOIN invoices inv ON inv.id = p.payable_id AND p.payable_type = 'App\Models\Invoice'
LEFT JOIN sales_orders so ON so.id = p.payable_id AND p.payable_type = 'App\Models\SalesOrder'
LEFT JOIN customers ci ON ci.id = inv.customer_id
LEFT JOIN customers cs ON cs.id = so.customer_id
WHERE p.payment_date >= $2
  AND p.payment_date < $3

UNION ALL

SELECT
  cf.transaction_date AS tx_date,
  cf.transaction_number::text AS journal_number,
  COALESCE(ca.code, '1000.01.01')::text AS cash_code,
  COALESCE(ca.name, 'Kas Tunai Toko')::text AS cash_name,
  CASE WHEN cf.type = 'income' THEN 'D'::text ELSE 'K'::text END AS side,
  cf.amount::numeric AS amount,
  CASE
    WHEN cf.flow_type = 'investing' THEN '120101'
    WHEN cf.flow_type = 'financing' THEN '3000.01'
    WHEN cf.category = 'salary' THEN '5000.02'
    ELSE '4000.01'
  END::text AS counterpart_code,
  COALESCE(NULLIF(cf.description, ''), cf.category, 'Mutasi Kas')::text AS counterpart_name,
  CASE WHEN cf.type = 'income' THEN 1 ELSE -1 END::int AS credit_sign
FROM cashflows cf
LEFT JOIN accounts ca ON ca.id = cf.account_id
WHERE cf.transaction_date >= $2
  AND cf.transaction_date < $3
ORDER BY tx_date, journal_number
`

type CashflowStatementMovementsParams struct {
	CashCodes []string    `json:"cash_codes"`
	DateFrom  pgtype.Date `json:"date_from"`
	DateTo    pgtype.Date `json:"date_to"`
}

type CashflowStatementMovementsRow struct {
	TxDate          pgtype.Date     `json:"tx_date"`
	JournalNumber   string          `json:"journal_number"`
	CashCode        string          `json:"cash_code"`
	CashName        string          `json:"cash_name"`
	Side            string          `json:"side"`
	Amount          decimal.Decimal `json:"amount"`
	CounterpartCode string          `json:"counterpart_code"`
	CounterpartName string          `json:"counterpart_name"`
	CreditSign      int32           `json:"credit_sign"`
}

// Cash-flow STATEMENT movements, derived from journal_lines.
//
// This exists because /reports/cashflow returns a flat row ARRAY (it is the
// generic RowCashflow export: payments + purchase orders + manual entries). The
// ERP's statement view needs operating/investing/financing sections, and reading
// them off an array was impossible: the page's array branch filled the three
// sections from d.operating/d.investing/d.financing, which are undefined on an
// array, so all three rendered as empty while net_cash_flow was still summed
// from the rows. The statement therefore showed A = B = C = Rp 0 next to a total
// of Rp 30.108.506 and the components never added up to their own total.
//
// Cash accounts are an EXPLICIT list, not code LIKE '1%'. In this chart 110302
// (Piutang Dagang), 110401 (Persediaan), 120101 (Aset Tetap) and 120102
// (Akumulasi Penyusutan) all start with 1 and are emphatically not cash; a
// prefix match would have swept receivables and inventory into the statement and
// produced a number that looked computed and meant nothing.
//
// counterpart_code is the OTHER side of the same journal entry, and it is what
// classifies the movement. Without it the sign of a line is all you have, which
// cannot tell a customer receipt (operating) from a bank loan (financing).
//
// credit_sign is the effect on cash: +1 when cash was debited (money in),
// -1 when cash was credited (money out), 0 for the cash side of a transfer.
func (q *Queries) CashflowStatementMovements(ctx context.Context, arg CashflowStatementMovementsParams) ([]CashflowStatementMovementsRow, error) {
	rows, err := q.db.Query(ctx, cashflowStatementMovements, arg.CashCodes, arg.DateFrom, arg.DateTo)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []CashflowStatementMovementsRow
	for rows.Next() {
		var i CashflowStatementMovementsRow
		if err := rows.Scan(
			&i.TxDate,
			&i.JournalNumber,
			&i.CashCode,
			&i.CashName,
			&i.Side,
			&i.Amount,
			&i.CounterpartCode,
			&i.CounterpartName,
			&i.CreditSign,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const cashflowYearBreakdown = `-- name: CashflowYearBreakdown :many
WITH all_cf AS (
  SELECT p.payment_date AS tx_date, 'income'::text AS tx_type, 'sales'::text AS tx_category, p.amount
  FROM payments p
  WHERE p.payment_date >= $1 AND p.payment_date < $2
  UNION ALL
  SELECT po.order_date AS tx_date, 'expense'::text AS tx_type, 'purchase'::text AS tx_category, po.paid_amount AS amount
  FROM purchase_orders po
  WHERE po.paid_amount > 0 AND po.order_date >= $1 AND po.order_date < $2
  UNION ALL
  SELECT cf.transaction_date AS tx_date, cf.type::text AS tx_type, cf.category::text AS tx_category, cf.amount
  FROM cashflows cf
  WHERE cf.transaction_date >= $1 AND cf.transaction_date < $2
)
SELECT to_char(date_trunc('month', tx_date), 'YYYY-MM') AS month,
       tx_type AS type, tx_category AS category, COALESCE(SUM(amount),0)::numeric AS total
FROM all_cf
GROUP BY 1, tx_type, tx_category
ORDER BY 1
`

type CashflowYearBreakdownParams struct {
	TransactionDate  pgtype.Date `json:"transaction_date"`
	TransactionDate2 pgtype.Date `json:"transaction_date_2"`
}

type CashflowYearBreakdownRow struct {
	Month    string          `json:"month"`
	Type     string          `json:"type"`
	Category string          `json:"category"`
	Total    decimal.Decimal `json:"total"`
}

func (q *Queries) CashflowYearBreakdown(ctx context.Context, arg CashflowYearBreakdownParams) ([]CashflowYearBreakdownRow, error) {
	rows, err := q.db.Query(ctx, cashflowYearBreakdown, arg.TransactionDate, arg.TransactionDate2)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []CashflowYearBreakdownRow
	for rows.Next() {
		var i CashflowYearBreakdownRow
		if err := rows.Scan(
			&i.Month,
			&i.Type,
			&i.Category,
			&i.Total,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const countLowStockProducts = `-- name: CountLowStockProducts :one
SELECT count(DISTINCT p.id) FROM products p
WHERE p.is_active = true
  AND (
    EXISTS (SELECT 1 FROM stocks s WHERE s.product_id = p.id AND s.quantity <= p.min_stock)
    OR NOT EXISTS (SELECT 1 FROM stocks s WHERE s.product_id = p.id)
  )
`

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

const debtCSVRows = `-- name: DebtCSVRows :many
SELECT po.id, po.po_number, po.supplier_id, s.name AS partner,
       '' AS kind, po.order_date::text AS date, COALESCE(po.due_date::text,'')::text AS due_date, po.status,
       po.total_amount, po.paid_amount, po.remaining_amount,
       NULL::text AS reference_type, NULL::int8 AS reference_id
FROM purchase_orders po
JOIN suppliers s ON s.id = po.supplier_id
WHERE po.status IN ('received','partial')
ORDER BY po.order_date ASC, po.id ASC
`

type DebtCSVRowsRow struct {
	ID              int64           `json:"id"`
	PoNumber        string          `json:"po_number"`
	SupplierID      int64           `json:"supplier_id"`
	Partner         string          `json:"partner"`
	Kind            string          `json:"kind"`
	Date            string          `json:"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"`
	ReferenceType   pgtype.Text     `json:"reference_type"`
	ReferenceID     pgtype.Int8     `json:"reference_id"`
}

func (q *Queries) DebtCSVRows(ctx context.Context) ([]DebtCSVRowsRow, error) {
	rows, err := q.db.Query(ctx, debtCSVRows)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []DebtCSVRowsRow
	for rows.Next() {
		var i DebtCSVRowsRow
		if err := rows.Scan(
			&i.ID,
			&i.PoNumber,
			&i.SupplierID,
			&i.Partner,
			&i.Kind,
			&i.Date,
			&i.DueDate,
			&i.Status,
			&i.TotalAmount,
			&i.PaidAmount,
			&i.RemainingAmount,
			&i.ReferenceType,
			&i.ReferenceID,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const listRecentActivity = `-- name: ListRecentActivity :many
SELECT id, user_id, module, action, description, created_at::text
FROM activity_logs ORDER BY created_at DESC, id DESC LIMIT $1
`

type ListRecentActivityRow struct {
	ID          int64       `json:"id"`
	UserID      pgtype.Int8 `json:"user_id"`
	Module      pgtype.Text `json:"module"`
	Action      pgtype.Text `json:"action"`
	Description pgtype.Text `json:"description"`
	CreatedAt   string      `json:"created_at"`
}

func (q *Queries) ListRecentActivity(ctx context.Context, limit int32) ([]ListRecentActivityRow, error) {
	rows, err := q.db.Query(ctx, listRecentActivity, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []ListRecentActivityRow
	for rows.Next() {
		var i ListRecentActivityRow
		if err := rows.Scan(
			&i.ID,
			&i.UserID,
			&i.Module,
			&i.Action,
			&i.Description,
			&i.CreatedAt,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const receivableCSVRows = `-- name: ReceivableCSVRows :many
SELECT inv.id, inv.invoice_number, inv.customer_id, c.name AS partner,
       '' AS kind, inv.invoice_date::text AS date, COALESCE(inv.due_date::text,'')::text AS due_date, inv.status,
       inv.total_amount, inv.paid_amount, inv.remaining_amount,
       NULL::text AS reference_type, NULL::int8 AS reference_id
FROM invoices inv
JOIN customers c ON c.id = inv.customer_id
WHERE inv.status IN ('unpaid','partial','paid')
ORDER BY inv.due_date ASC, inv.id ASC
`

type ReceivableCSVRowsRow struct {
	ID              int64           `json:"id"`
	InvoiceNumber   string          `json:"invoice_number"`
	CustomerID      int64           `json:"customer_id"`
	Partner         string          `json:"partner"`
	Kind            string          `json:"kind"`
	Date            string          `json:"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"`
	ReferenceType   pgtype.Text     `json:"reference_type"`
	ReferenceID     pgtype.Int8     `json:"reference_id"`
}

func (q *Queries) ReceivableCSVRows(ctx context.Context) ([]ReceivableCSVRowsRow, error) {
	rows, err := q.db.Query(ctx, receivableCSVRows)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []ReceivableCSVRowsRow
	for rows.Next() {
		var i ReceivableCSVRowsRow
		if err := rows.Scan(
			&i.ID,
			&i.InvoiceNumber,
			&i.CustomerID,
			&i.Partner,
			&i.Kind,
			&i.Date,
			&i.DueDate,
			&i.Status,
			&i.TotalAmount,
			&i.PaidAmount,
			&i.RemainingAmount,
			&i.ReferenceType,
			&i.ReferenceID,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const salesChart = `-- name: SalesChart :many
SELECT to_char(date_trunc('month', order_date) AT TIME ZONE 'UTC', 'YYYY-MM') AS month,
       COALESCE(SUM(total_amount),0)::numeric AS total
FROM sales_orders
WHERE order_date >= $1 AND status <> 'cancelled'
GROUP BY 1 ORDER BY 1 LIMIT $2
`

type SalesChartParams struct {
	OrderDate pgtype.Date `json:"order_date"`
	Limit     int32       `json:"limit"`
}

type SalesChartRow struct {
	Month string          `json:"month"`
	Total decimal.Decimal `json:"total"`
}

func (q *Queries) SalesChart(ctx context.Context, arg SalesChartParams) ([]SalesChartRow, error) {
	rows, err := q.db.Query(ctx, salesChart, arg.OrderDate, arg.Limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []SalesChartRow
	for rows.Next() {
		var i SalesChartRow
		if err := rows.Scan(&i.Month, &i.Total); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const salesReportBreakdown = `-- name: SalesReportBreakdown :many
SELECT 'total'::text AS kind, ''::text AS label,
       count(*)::int AS orders, COALESCE(SUM(so.total_amount),0)::numeric AS total
FROM sales_orders so
WHERE so.order_date >= $1 AND so.order_date < $2 AND so.status <> 'cancelled'
UNION ALL
SELECT 'category', COALESCE(c.name, 'Lainnya'), 0, COALESCE(SUM(i.subtotal),0)
FROM sales_order_items i
JOIN sales_orders so ON so.id = i.sales_order_id AND so.status <> 'cancelled'
     AND so.order_date >= $1 AND so.order_date < $2
LEFT JOIN products p ON p.id = i.product_id
LEFT JOIN categories c ON c.id = p.category_id
GROUP BY c.name
UNION ALL
SELECT 'product', p.name, 0, COALESCE(SUM(i.subtotal),0)
FROM sales_order_items i
JOIN sales_orders so ON so.id = i.sales_order_id AND so.status <> 'cancelled'
     AND so.order_date >= $1 AND so.order_date < $2
JOIN products p ON p.id = i.product_id
GROUP BY p.name
`

type SalesReportBreakdownParams struct {
	OrderDate   pgtype.Date `json:"order_date"`
	OrderDate_2 pgtype.Date `json:"order_date_2"`
}

type SalesReportBreakdownRow struct {
	Kind   string          `json:"kind"`
	Label  string          `json:"label"`
	Orders int32           `json:"orders"`
	Total  decimal.Decimal `json:"total"`
}

func (q *Queries) SalesReportBreakdown(ctx context.Context, arg SalesReportBreakdownParams) ([]SalesReportBreakdownRow, error) {
	rows, err := q.db.Query(ctx, salesReportBreakdown, arg.OrderDate, arg.OrderDate_2)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []SalesReportBreakdownRow
	for rows.Next() {
		var i SalesReportBreakdownRow
		if err := rows.Scan(
			&i.Kind,
			&i.Label,
			&i.Orders,
			&i.Total,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const stockReportBreakdown = `-- name: StockReportBreakdown :many
SELECT 'total'::text AS kind, ''::text AS label,
       COALESCE(SUM(s.quantity),0)::numeric AS qty,
       COALESCE(SUM(s.quantity * p.purchase_price),0)::numeric AS value
FROM stocks s
JOIN products p ON p.id = s.product_id AND p.deleted_at IS NULL
UNION ALL
SELECT 'category', COALESCE(c.name, 'Lainnya'),
       COALESCE(SUM(s.quantity),0)::numeric,
       COALESCE(SUM(s.quantity * p.purchase_price),0)::numeric
FROM stocks s
JOIN products p ON p.id = s.product_id AND p.deleted_at IS NULL
LEFT JOIN categories c ON c.id = p.category_id
GROUP BY c.name
`

type StockReportBreakdownRow struct {
	Kind  string          `json:"kind"`
	Label string          `json:"label"`
	Qty   decimal.Decimal `json:"qty"`
	Value decimal.Decimal `json:"value"`
}

func (q *Queries) StockReportBreakdown(ctx context.Context) ([]StockReportBreakdownRow, error) {
	rows, err := q.db.Query(ctx, stockReportBreakdown)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []StockReportBreakdownRow
	for rows.Next() {
		var i StockReportBreakdownRow
		if err := rows.Scan(
			&i.Kind,
			&i.Label,
			&i.Qty,
			&i.Value,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const sumMonthPurchase = `-- name: SumMonthPurchase :one
SELECT COALESCE(SUM(total_amount),0)::numeric FROM purchase_orders
WHERE order_date >= $1 AND order_date < $2 AND status <> 'cancelled'
`

type SumMonthPurchaseParams struct {
	OrderDate   pgtype.Date `json:"order_date"`
	OrderDate_2 pgtype.Date `json:"order_date_2"`
}

func (q *Queries) SumMonthPurchase(ctx context.Context, arg SumMonthPurchaseParams) (decimal.Decimal, error) {
	row := q.db.QueryRow(ctx, sumMonthPurchase, arg.OrderDate, arg.OrderDate_2)
	var column_1 decimal.Decimal
	err := row.Scan(&column_1)
	return column_1, err
}

const sumMonthSales = `-- name: SumMonthSales :one
SELECT COALESCE(SUM(total_amount),0)::numeric FROM sales_orders
WHERE order_date >= $1 AND order_date < $2 AND status <> 'cancelled'
`

type SumMonthSalesParams struct {
	OrderDate   pgtype.Date `json:"order_date"`
	OrderDate_2 pgtype.Date `json:"order_date_2"`
}

func (q *Queries) SumMonthSales(ctx context.Context, arg SumMonthSalesParams) (decimal.Decimal, error) {
	row := q.db.QueryRow(ctx, sumMonthSales, arg.OrderDate, arg.OrderDate_2)
	var column_1 decimal.Decimal
	err := row.Scan(&column_1)
	return column_1, err
}

const sumPayablesBalance = `-- name: SumPayablesBalance :one
SELECT COALESCE(SUM(remaining_amount),0)::numeric FROM purchase_orders
WHERE status IN ('received','partial')
`

func (q *Queries) SumPayablesBalance(ctx context.Context) (decimal.Decimal, error) {
	row := q.db.QueryRow(ctx, sumPayablesBalance)
	var column_1 decimal.Decimal
	err := row.Scan(&column_1)
	return column_1, err
}

const sumReceivablesBalance = `-- name: SumReceivablesBalance :one
SELECT COALESCE(SUM(remaining_amount),0)::numeric FROM invoices
WHERE status IN ('unpaid','partial')
`

func (q *Queries) SumReceivablesBalance(ctx context.Context) (decimal.Decimal, error) {
	row := q.db.QueryRow(ctx, sumReceivablesBalance)
	var column_1 decimal.Decimal
	err := row.Scan(&column_1)
	return column_1, err
}