// Code generated by sqlc. DO NOT EDIT.
// versions:
//   sqlc v1.31.1
// source: lhpp.sql

package db

import (
	"context"

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

const lHPPAssignGroup = `-- name: LHPPAssignGroup :execrows
UPDATE lhpp_groups
SET assigned_to = $1::bigint,
    assigned_at = CASE WHEN $1::bigint IS NULL THEN NULL ELSE now() END,
    updated_at = now()
WHERE id = $2::bigint AND status = 'active'
`

type LHPPAssignGroupParams struct {
	AssignedTo pgtype.Int8 `json:"assigned_to"`
	ID         int64       `json:"id"`
}

// Hand the sheet to a collector (or take it back with NULL) — active sheets
// only; a voided sheet has no carrier to record. execrows==0 distinguishes a
// missing/voided group from a successful handover.
func (q *Queries) LHPPAssignGroup(ctx context.Context, arg LHPPAssignGroupParams) (int64, error) {
	result, err := q.db.Exec(ctx, lHPPAssignGroup, arg.AssignedTo, arg.ID)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected(), nil
}

const lHPPAssigneeExists = `-- name: LHPPAssigneeExists :one
SELECT id FROM users WHERE id = $1::bigint AND is_active
`

// The collector taking the sheet must be an ACTIVE account: a handover to a
// deactivated user would orphan the sheet (nobody to receive the setoran).
func (q *Queries) LHPPAssigneeExists(ctx context.Context, id int64) (int64, error) {
	row := q.db.QueryRow(ctx, lHPPAssigneeExists, id)
	var id_2 int64
	err := row.Scan(&id_2)
	return id_2, err
}

const lHPPCashGiroPaymentsForInvoices = `-- name: LHPPCashGiroPaymentsForInvoices :many
SELECT payable_id AS invoice_id, id AS payment_id, deposit_id
FROM payments
WHERE payable_type = 'App\Models\Invoice'
  AND payable_id = ANY($1::bigint[])
  AND payment_method IN ('cash', 'giro')
`

type LHPPCashGiroPaymentsForInvoicesRow struct {
	InvoiceID int64       `json:"invoice_id"`
	PaymentID int64       `json:"payment_id"`
	DepositID pgtype.Int8 `json:"deposit_id"`
}

// Every cash/giro payment recorded against this set of invoices, with its
// Setoran linkage (deposit_id is NULL until a Setoran Tunai Driver picks it
// up — see internal/warehousedeposit). Used to derive each LHPP item's
// "sudah diterima kasir atau belum" status precisely, per invoice, instead
// of approximating by driver+date.
func (q *Queries) LHPPCashGiroPaymentsForInvoices(ctx context.Context, invoiceIds []int64) ([]LHPPCashGiroPaymentsForInvoicesRow, error) {
	rows, err := q.db.Query(ctx, lHPPCashGiroPaymentsForInvoices, invoiceIds)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []LHPPCashGiroPaymentsForInvoicesRow
	for rows.Next() {
		var i LHPPCashGiroPaymentsForInvoicesRow
		if err := rows.Scan(&i.InvoiceID, &i.PaymentID, &i.DepositID); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const lHPPCountGroupItems = `-- name: LHPPCountGroupItems :one
SELECT count(*)::bigint
FROM lhpp_group_items
WHERE group_id = $1::bigint
`

func (q *Queries) LHPPCountGroupItems(ctx context.Context, groupID int64) (int64, error) {
	row := q.db.QueryRow(ctx, lHPPCountGroupItems, groupID)
	var column_1 int64
	err := row.Scan(&column_1)
	return column_1, err
}

const lHPPCountGroupItemsInGroups = `-- name: LHPPCountGroupItemsInGroups :many
SELECT group_id, count(*)::bigint AS do_count
FROM lhpp_group_items
WHERE group_id = ANY($1::bigint[])
GROUP BY group_id
`

type LHPPCountGroupItemsInGroupsRow struct {
	GroupID int64 `json:"group_id"`
	DoCount int64 `json:"do_count"`
}

// Per-group member count for the sources, so a merge knows which ones it
// emptied. Voided groups are excluded by the service, not here.
func (q *Queries) LHPPCountGroupItemsInGroups(ctx context.Context, groupIds []int64) ([]LHPPCountGroupItemsInGroupsRow, error) {
	rows, err := q.db.Query(ctx, lHPPCountGroupItemsInGroups, groupIds)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []LHPPCountGroupItemsInGroupsRow
	for rows.Next() {
		var i LHPPCountGroupItemsInGroupsRow
		if err := rows.Scan(&i.GroupID, &i.DoCount); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const lHPPDeleteGroupItems = `-- name: LHPPDeleteGroupItems :execrows
DELETE FROM lhpp_group_items
WHERE group_id = ANY($1::bigint[])
`

func (q *Queries) LHPPDeleteGroupItems(ctx context.Context, groupIds []int64) (int64, error) {
	result, err := q.db.Exec(ctx, lHPPDeleteGroupItems, groupIds)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected(), nil
}

const lHPPDeleteGroupItemsByDO = `-- name: LHPPDeleteGroupItemsByDO :execrows
DELETE FROM lhpp_group_items
WHERE delivery_order_id = ANY($1::bigint[])
`

func (q *Queries) LHPPDeleteGroupItemsByDO(ctx context.Context, doIds []int64) (int64, error) {
	result, err := q.db.Exec(ctx, lHPPDeleteGroupItemsByDO, doIds)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected(), nil
}

const lHPPGetGroup = `-- name: LHPPGetGroup :one

SELECT g.id, g.lhpp_number, g.driver_id, g.lhpp_date::text AS lhpp_date, g.status,
       g.assigned_to,
       COALESCE(to_char(g.assigned_at AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"'), '')::text AS assigned_at,
       COALESCE(au.name, '') AS assignee_name,
       g.voided_at, g.voided_by, g.created_by
FROM lhpp_groups g
LEFT JOIN users au ON au.id = g.assigned_to
WHERE g.id = $1::bigint
`

type LHPPGetGroupRow struct {
	ID           int64              `json:"id"`
	LhppNumber   string             `json:"lhpp_number"`
	DriverID     int64              `json:"driver_id"`
	LhppDate     string             `json:"lhpp_date"`
	Status       string             `json:"status"`
	AssignedTo   pgtype.Int8        `json:"assigned_to"`
	AssignedAt   string             `json:"assigned_at"`
	AssigneeName string             `json:"assignee_name"`
	VoidedAt     pgtype.Timestamptz `json:"voided_at"`
	VoidedBy     pgtype.Int8        `json:"voided_by"`
	CreatedBy    pgtype.Int8        `json:"created_by"`
}

// ---------- persisted group maintenance (merge / split / void / dispatch) ----
// A group header by id, INCLUDING a voided one: Get must still reach a retired
// sheet by id so its number stays auditable long after the DOs left.
func (q *Queries) LHPPGetGroup(ctx context.Context, id int64) (LHPPGetGroupRow, error) {
	row := q.db.QueryRow(ctx, lHPPGetGroup, id)
	var i LHPPGetGroupRow
	err := row.Scan(
		&i.ID,
		&i.LhppNumber,
		&i.DriverID,
		&i.LhppDate,
		&i.Status,
		&i.AssignedTo,
		&i.AssignedAt,
		&i.AssigneeName,
		&i.VoidedAt,
		&i.VoidedBy,
		&i.CreatedBy,
	)
	return i, err
}

const lHPPGetGroupsForUpdate = `-- name: LHPPGetGroupsForUpdate :many
SELECT id, lhpp_number, driver_id, lhpp_date::text AS lhpp_date, status,
       voided_at, voided_by, created_by
FROM lhpp_groups
WHERE id = ANY($1::bigint[])
ORDER BY id
FOR UPDATE
`

type LHPPGetGroupsForUpdateRow struct {
	ID         int64              `json:"id"`
	LhppNumber string             `json:"lhpp_number"`
	DriverID   int64              `json:"driver_id"`
	LhppDate   string             `json:"lhpp_date"`
	Status     string             `json:"status"`
	VoidedAt   pgtype.Timestamptz `json:"voided_at"`
	VoidedBy   pgtype.Int8        `json:"voided_by"`
	CreatedBy  pgtype.Int8        `json:"created_by"`
}

// Locks the participating group rows. Called AFTER the delivery_orders locks and
// in ascending id order, so every mutation (merge, split, void) takes locks in
// one global order and cannot deadlock against another. Rows of voided groups
// are returned too: the service decides what to do with them rather than having
// the filter hide a state transition.
func (q *Queries) LHPPGetGroupsForUpdate(ctx context.Context, groupIds []int64) ([]LHPPGetGroupsForUpdateRow, error) {
	rows, err := q.db.Query(ctx, lHPPGetGroupsForUpdate, groupIds)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []LHPPGetGroupsForUpdateRow
	for rows.Next() {
		var i LHPPGetGroupsForUpdateRow
		if err := rows.Scan(
			&i.ID,
			&i.LhppNumber,
			&i.DriverID,
			&i.LhppDate,
			&i.Status,
			&i.VoidedAt,
			&i.VoidedBy,
			&i.CreatedBy,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const lHPPGroupHistory = `-- name: LHPPGroupHistory :many
SELECT h.id, h.delivery_order_id, h.action, h.from_group_id, h.to_group_id,
       h.from_driver_id, h.to_driver_id, h.reason, h.changed_by, h.created_at
FROM lhpp_group_history h
WHERE h.to_group_id = $1::bigint
   OR h.from_group_id = $1::bigint
ORDER BY h.id DESC
`

// Audit trail for one group, newest first. to_group_id is what the two indexes
// cover; from_group_id is looked up through the DO index (a DO's history is
// read when answering "which sheet was this DO on before it moved?").
func (q *Queries) LHPPGroupHistory(ctx context.Context, groupID int64) ([]LhppGroupHistory, error) {
	rows, err := q.db.Query(ctx, lHPPGroupHistory, groupID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []LhppGroupHistory
	for rows.Next() {
		var i LhppGroupHistory
		if err := rows.Scan(
			&i.ID,
			&i.DeliveryOrderID,
			&i.Action,
			&i.FromGroupID,
			&i.ToGroupID,
			&i.FromDriverID,
			&i.ToDriverID,
			&i.Reason,
			&i.ChangedBy,
			&i.CreatedAt,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const lHPPGroupItemDOIDs = `-- name: LHPPGroupItemDOIDs :one
SELECT group_id
FROM lhpp_group_items
WHERE delivery_order_id = $1::bigint
`

// Which group a DO currently belongs to, or no row when it is unbatched. This
// is also the dispatch-seam idempotency probe: a re-dispatch re-invokes the seam
// and finds the DO already placed, so no second group is created.
func (q *Queries) LHPPGroupItemDOIDs(ctx context.Context, deliveryOrderID int64) (int64, error) {
	row := q.db.QueryRow(ctx, lHPPGroupItemDOIDs, deliveryOrderID)
	var group_id int64
	err := row.Scan(&group_id)
	return group_id, err
}

const lHPPGroupMemberDOIDs = `-- name: LHPPGroupMemberDOIDs :many
SELECT delivery_order_id
FROM lhpp_group_items
WHERE group_id = ANY($1::bigint[])
ORDER BY delivery_order_id
`

// Pre-lock membership read. Safe to do unlocked because every mutation re-locks
// these same delivery_orders rows before it writes, so the set cannot change
// underneath us once the locks are held.
func (q *Queries) LHPPGroupMemberDOIDs(ctx context.Context, groupIds []int64) ([]int64, error) {
	rows, err := q.db.Query(ctx, lHPPGroupMemberDOIDs, groupIds)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []int64
	for rows.Next() {
		var delivery_order_id int64
		if err := rows.Scan(&delivery_order_id); err != nil {
			return nil, err
		}
		items = append(items, delivery_order_id)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const lHPPInsertGroup = `-- name: LHPPInsertGroup :one
INSERT INTO lhpp_groups (lhpp_number, driver_id, lhpp_date, created_by)
VALUES ($1, $2::bigint, $3::date, $4::bigint)
RETURNING id
`

type LHPPInsertGroupParams struct {
	LhppNumber string      `json:"lhpp_number"`
	DriverID   int64       `json:"driver_id"`
	LhppDate   pgtype.Date `json:"lhpp_date"`
	CreatedBy  pgtype.Int8 `json:"created_by"`
}

// lhpp_number is allocated from document_number_sequences INSIDE the caller's
// transaction by the service, so a rollback rewinds the counter instead of
// burning a number (000084's entire subject).
func (q *Queries) LHPPInsertGroup(ctx context.Context, arg LHPPInsertGroupParams) (int64, error) {
	row := q.db.QueryRow(ctx, lHPPInsertGroup,
		arg.LhppNumber,
		arg.DriverID,
		arg.LhppDate,
		arg.CreatedBy,
	)
	var id int64
	err := row.Scan(&id)
	return id, err
}

const lHPPInsertGroupHistory = `-- name: LHPPInsertGroupHistory :one
INSERT INTO lhpp_group_history (delivery_order_id, action, from_group_id, to_group_id,
                                from_driver_id, to_driver_id, reason, changed_by)
VALUES ($1::bigint,
        $2,
        $3::bigint,
        $4::bigint,
        $5::bigint,
        $6::bigint,
        $7,
        $8::bigint)
RETURNING id
`

type LHPPInsertGroupHistoryParams struct {
	DeliveryOrderID int64       `json:"delivery_order_id"`
	Action          string      `json:"action"`
	FromGroupID     pgtype.Int8 `json:"from_group_id"`
	ToGroupID       pgtype.Int8 `json:"to_group_id"`
	FromDriverID    pgtype.Int8 `json:"from_driver_id"`
	ToDriverID      pgtype.Int8 `json:"to_driver_id"`
	Reason          string      `json:"reason"`
	ChangedBy       pgtype.Int8 `json:"changed_by"`
}

// One row per DO per operation, written inside the same transaction as the
// membership change it describes.
func (q *Queries) LHPPInsertGroupHistory(ctx context.Context, arg LHPPInsertGroupHistoryParams) (int64, error) {
	row := q.db.QueryRow(ctx, lHPPInsertGroupHistory,
		arg.DeliveryOrderID,
		arg.Action,
		arg.FromGroupID,
		arg.ToGroupID,
		arg.FromDriverID,
		arg.ToDriverID,
		arg.Reason,
		arg.ChangedBy,
	)
	var id int64
	err := row.Scan(&id)
	return id, err
}

const lHPPInsertGroupItem = `-- name: LHPPInsertGroupItem :one
INSERT INTO lhpp_group_items (group_id, delivery_order_id, added_by)
VALUES ($1::bigint, $2::bigint, $3::bigint)
RETURNING id
`

type LHPPInsertGroupItemParams struct {
	GroupID         int64       `json:"group_id"`
	DeliveryOrderID int64       `json:"delivery_order_id"`
	AddedBy         pgtype.Int8 `json:"added_by"`
}

func (q *Queries) LHPPInsertGroupItem(ctx context.Context, arg LHPPInsertGroupItemParams) (int64, error) {
	row := q.db.QueryRow(ctx, lHPPInsertGroupItem, arg.GroupID, arg.DeliveryOrderID, arg.AddedBy)
	var id int64
	err := row.Scan(&id)
	return id, err
}

const lHPPLockDeliveryOrdersForUpdate = `-- name: LHPPLockDeliveryOrdersForUpdate :many
SELECT id, do_date::text AS do_date
FROM delivery_orders
WHERE id = ANY($1::bigint[])
ORDER BY id
FOR UPDATE
`

type LHPPLockDeliveryOrdersForUpdateRow struct {
	ID     int64  `json:"id"`
	DoDate string `json:"do_date"`
}

// The concurrency lock for every membership change. delivery_orders is the row
// CreateDispatch already locks (internal/distribution CreateDispatch), so a
// merge and a re-dispatch contend on the same row for free. Ascending id order
// is mandatory — it is what makes concurrent merges deadlock-free.
func (q *Queries) LHPPLockDeliveryOrdersForUpdate(ctx context.Context, doIds []int64) ([]LHPPLockDeliveryOrdersForUpdateRow, error) {
	rows, err := q.db.Query(ctx, lHPPLockDeliveryOrdersForUpdate, doIds)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []LHPPLockDeliveryOrdersForUpdateRow
	for rows.Next() {
		var i LHPPLockDeliveryOrdersForUpdateRow
		if err := rows.Scan(&i.ID, &i.DoDate); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const lHPPMoveGroupItems = `-- name: LHPPMoveGroupItems :execrows
UPDATE lhpp_group_items
SET group_id = $1::bigint,
    added_by = $2::bigint,
    created_at = now()
WHERE group_id = ANY($3::bigint[])
`

type LHPPMoveGroupItemsParams struct {
	ToGroupID    int64       `json:"to_group_id"`
	ChangedBy    pgtype.Int8 `json:"changed_by"`
	FromGroupIds []int64     `json:"from_group_ids"`
}

// Re-points membership. The DO's lhpp_number is NOT touched — the group row
// keeps the number it was allocated, which is what makes a merge reversible
// without invalidating a sheet the collector already printed.
func (q *Queries) LHPPMoveGroupItems(ctx context.Context, arg LHPPMoveGroupItemsParams) (int64, error) {
	result, err := q.db.Exec(ctx, lHPPMoveGroupItems, arg.ToGroupID, arg.ChangedBy, arg.FromGroupIds)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected(), nil
}

const lHPPMoveGroupItemsByDO = `-- name: LHPPMoveGroupItemsByDO :execrows
UPDATE lhpp_group_items
SET group_id = $1::bigint,
    added_by = $2::bigint,
    created_at = now()
WHERE delivery_order_id = ANY($3::bigint[])
`

type LHPPMoveGroupItemsByDOParams struct {
	ToGroupID int64       `json:"to_group_id"`
	ChangedBy pgtype.Int8 `json:"changed_by"`
	DoIds     []int64     `json:"do_ids"`
}

// Re-points specific DOs, which is what a split does. This MUST be an UPDATE
// and not DELETE+INSERT: lhpp_group_items_do_unique is on delivery_order_id
// alone, so inserting the new membership while the old one still exists
// violates the invariant — and DELETE+INSERT would also leave a window where
// the DO is on no sheet at all. Updating one row to point elsewhere can never
// trip it.
func (q *Queries) LHPPMoveGroupItemsByDO(ctx context.Context, arg LHPPMoveGroupItemsByDOParams) (int64, error) {
	result, err := q.db.Exec(ctx, lHPPMoveGroupItemsByDO, arg.ToGroupID, arg.ChangedBy, arg.DoIds)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected(), nil
}

const lHPPUpdateGroupDriver = `-- name: LHPPUpdateGroupDriver :execrows
UPDATE lhpp_groups
SET driver_id = $1::bigint,
    updated_at = now()
WHERE id = $2::bigint
`

type LHPPUpdateGroupDriverParams struct {
	DriverID int64 `json:"driver_id"`
	ID       int64 `json:"id"`
}

// driver_id follows a re-dispatch only while the group holds a single DO (the
// guard lives in internal/lhpp: a multi-member group's driver is immutable).
// lhpp_number is deliberately absent — the sheet keeps its number.
func (q *Queries) LHPPUpdateGroupDriver(ctx context.Context, arg LHPPUpdateGroupDriverParams) (int64, error) {
	result, err := q.db.Exec(ctx, lHPPUpdateGroupDriver, arg.DriverID, arg.ID)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected(), nil
}

const lHPPVoidGroups = `-- name: LHPPVoidGroups :execrows
UPDATE lhpp_groups
SET status = 'voided',
    voided_at = now(),
    voided_by = $1::bigint,
    updated_at = now()
WHERE id = ANY($2::bigint[])
`

type LHPPVoidGroupsParams struct {
	VoidedBy pgtype.Int8 `json:"voided_by"`
	GroupIds []int64     `json:"group_ids"`
}

// Voiding is the empty-group mechanism: status + voided_at + voided_by, with
// updated_at alongside. The CHECK on lhpp_groups rejects a void without a stamp.
func (q *Queries) LHPPVoidGroups(ctx context.Context, arg LHPPVoidGroupsParams) (int64, error) {
	result, err := q.db.Exec(ctx, lHPPVoidGroups, arg.VoidedBy, arg.GroupIds)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected(), nil
}

const listLHPPCandidateRows = `-- name: ListLHPPCandidateRows :many

SELECT
  do_row.id AS do_id,
  do_row.do_number,
  do_row.do_date::text AS do_date,
  g.id                AS group_id,
  g.lhpp_number,
  g.lhpp_date::text   AS lhpp_date,
  g.status            AS group_status,
  g.driver_id,
  COALESCE(u.name, '')          AS driver_name,
  g.assigned_to,
  -- Explicit UTC + Z: assigned_at::text would follow the session timezone and
  -- render an unparseable offset, so the ERP could never show handover time.
  COALESCE(to_char(g.assigned_at AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"'), '')::text AS assigned_at,
  COALESCE(au.name, '')         AS assignee_name,
  disp.fleet_id,
  COALESCE(f.vehicle_name, '')  AS fleet_name,
  COALESCE(f.plate_number, '')  AS plate_number,
  so.id                         AS sales_order_id,
  c.id                          AS customer_id,
  COALESCE(c.name, '')          AS customer_name,
  COALESCE(c.address, '')       AS customer_address,
  inv.id                        AS invoice_id,
  inv.invoice_number,
  inv.total_amount,
  inv.paid_amount,
  inv.remaining_amount,
  inv.status                    AS invoice_status
FROM (
  -- latest non-cancelled dispatch per DO, same "current dispatch" convention
  -- as GetLatestDispatchByDeliveryOrder
  SELECT DISTINCT ON (delivery_order_id) id, delivery_order_id, shipment_id, fleet_id, priority, queue_order, driver_id, scheduled_for, status, dispatched_at, dispatcher_id, created_at, updated_at
  FROM dispatches
  WHERE status = 'dispatched' AND driver_id IS NOT NULL
  ORDER BY delivery_order_id, id DESC
) disp
JOIN delivery_orders do_row ON do_row.id = disp.delivery_order_id
JOIN sales_orders so ON so.id = do_row.sales_order_id
JOIN customers c ON c.id = so.customer_id
LEFT JOIN fleet f ON f.id = disp.fleet_id
LEFT JOIN users u ON u.id = disp.driver_id
LEFT JOIN invoices inv ON inv.sales_order_id = so.id
JOIN lhpp_group_items gi ON gi.delivery_order_id = do_row.id
JOIN lhpp_groups g ON g.id = gi.group_id
LEFT JOIN users au ON au.id = g.assigned_to
ORDER BY g.lhpp_date DESC, g.id DESC, do_row.do_date ASC, do_row.id
`

type ListLHPPCandidateRowsRow struct {
	DoID            int64               `json:"do_id"`
	DoNumber        string              `json:"do_number"`
	DoDate          string              `json:"do_date"`
	GroupID         int64               `json:"group_id"`
	LhppNumber      string              `json:"lhpp_number"`
	LhppDate        string              `json:"lhpp_date"`
	GroupStatus     string              `json:"group_status"`
	DriverID        int64               `json:"driver_id"`
	DriverName      string              `json:"driver_name"`
	AssignedTo      pgtype.Int8         `json:"assigned_to"`
	AssignedAt      string              `json:"assigned_at"`
	AssigneeName    string              `json:"assignee_name"`
	FleetID         int64               `json:"fleet_id"`
	FleetName       string              `json:"fleet_name"`
	PlateNumber     string              `json:"plate_number"`
	SalesOrderID    int64               `json:"sales_order_id"`
	CustomerID      int64               `json:"customer_id"`
	CustomerName    string              `json:"customer_name"`
	CustomerAddress string              `json:"customer_address"`
	InvoiceID       pgtype.Int8         `json:"invoice_id"`
	InvoiceNumber   pgtype.Text         `json:"invoice_number"`
	TotalAmount     decimal.NullDecimal `json:"total_amount"`
	PaidAmount      decimal.NullDecimal `json:"paid_amount"`
	RemainingAmount decimal.NullDecimal `json:"remaining_amount"`
	InvoiceStatus   pgtype.Text         `json:"invoice_status"`
}

// LHPP (Laporan Harian Penagihan Piutang) groups are PERSISTED, not computed.
// A group is one manually maintained billing sheet: one group per delivery
// order by default (created by the CreateDispatch seam), merged and split by
// hand, one driver per group, and possibly spanning several days. Rows are
// still read flat and folded in Go (internal/lhpp/service.go) because the
// totals roll-up and cancellation rule are pure logic worth testing without a
// database — but the grouping key is lhpp_groups.id, never driver+date.
// A DO with no group row is not on any sheet and is not returned.
// Payments post through financeSvc.PayInvoice against the invoice found here,
// so LHPP and ReceivablesPage always read the same ledger.
//
// date_from/date_to are deliberately NOT selected: they are the min/max do_date
// of a group's members, which changes on every merge, so they are derived in the
// fold from do_date below rather than stored (see 000101).
func (q *Queries) ListLHPPCandidateRows(ctx context.Context) ([]ListLHPPCandidateRowsRow, error) {
	rows, err := q.db.Query(ctx, listLHPPCandidateRows)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []ListLHPPCandidateRowsRow
	for rows.Next() {
		var i ListLHPPCandidateRowsRow
		if err := rows.Scan(
			&i.DoID,
			&i.DoNumber,
			&i.DoDate,
			&i.GroupID,
			&i.LhppNumber,
			&i.LhppDate,
			&i.GroupStatus,
			&i.DriverID,
			&i.DriverName,
			&i.AssignedTo,
			&i.AssignedAt,
			&i.AssigneeName,
			&i.FleetID,
			&i.FleetName,
			&i.PlateNumber,
			&i.SalesOrderID,
			&i.CustomerID,
			&i.CustomerName,
			&i.CustomerAddress,
			&i.InvoiceID,
			&i.InvoiceNumber,
			&i.TotalAmount,
			&i.PaidAmount,
			&i.RemainingAmount,
			&i.InvoiceStatus,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const listLHPPSalesCandidateRows = `-- name: ListLHPPSalesCandidateRows :many
SELECT
  v.id                           AS visit_id,
  v.sales_id,
  COALESCE(e.name, '')           AS sales_name,
  v.visit_date::text             AS visit_date,
  COALESCE(v.purpose, '')        AS purpose,
  c.id                           AS customer_id,
  COALESCE(c.name, '')           AS customer_name,
  COALESCE(c.address, '')        AS customer_address,
  inv.id                         AS invoice_id,
  inv.invoice_number,
  inv.total_amount,
  inv.paid_amount,
  inv.remaining_amount,
  inv.status                     AS invoice_status
FROM sales_visits v
JOIN customers c ON c.id = v.customer_id
LEFT JOIN employees e ON e.id = v.sales_id
LEFT JOIN invoices inv ON inv.customer_id = c.id
  AND inv.status != 'cancelled'
  AND (
    inv.status IN ('unpaid', 'partial', 'overdue')
    OR EXISTS (
      SELECT 1 FROM payments p
      WHERE p.payable_type = 'App\Models\Invoice'
        AND p.payable_id = inv.id
        AND p.reference_number = 'LHPP-SLS-' || replace(v.visit_date::text, '-', '') || '-' || lpad(v.sales_id::text, 3, '0')
    )
  )
WHERE v.visit_type = 'billing' AND v.status != 'cancelled' AND v.sales_id IS NOT NULL
ORDER BY v.visit_date DESC, v.sales_id, v.id
`

type ListLHPPSalesCandidateRowsRow struct {
	VisitID         int64               `json:"visit_id"`
	SalesID         pgtype.Int8         `json:"sales_id"`
	SalesName       string              `json:"sales_name"`
	VisitDate       string              `json:"visit_date"`
	Purpose         string              `json:"purpose"`
	CustomerID      int64               `json:"customer_id"`
	CustomerName    string              `json:"customer_name"`
	CustomerAddress string              `json:"customer_address"`
	InvoiceID       pgtype.Int8         `json:"invoice_id"`
	InvoiceNumber   pgtype.Text         `json:"invoice_number"`
	TotalAmount     decimal.NullDecimal `json:"total_amount"`
	PaidAmount      decimal.NullDecimal `json:"paid_amount"`
	RemainingAmount decimal.NullDecimal `json:"remaining_amount"`
	InvoiceStatus   pgtype.Text         `json:"invoice_status"`
}

// Sales LHPP has no table either: a "group" is every billing (penagihan)
// visit scheduled for the same sales rep on the same date, joined to that
// customer's open invoices — the collection worksheet a sales rep works from
// when Finance schedules a penagihan visit (see internal/salestracking),
// as opposed to the driver-keyed LHPP above which covers dispatched DOs.
// Assumes one billing visit per sales+customer+date; a duplicate schedule to
// the same customer on the same day would double-count that customer's
// invoice totals, which is treated as a scheduling data error, not handled here.
func (q *Queries) ListLHPPSalesCandidateRows(ctx context.Context) ([]ListLHPPSalesCandidateRowsRow, error) {
	rows, err := q.db.Query(ctx, listLHPPSalesCandidateRows)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []ListLHPPSalesCandidateRowsRow
	for rows.Next() {
		var i ListLHPPSalesCandidateRowsRow
		if err := rows.Scan(
			&i.VisitID,
			&i.SalesID,
			&i.SalesName,
			&i.VisitDate,
			&i.Purpose,
			&i.CustomerID,
			&i.CustomerName,
			&i.CustomerAddress,
			&i.InvoiceID,
			&i.InvoiceNumber,
			&i.TotalAmount,
			&i.PaidAmount,
			&i.RemainingAmount,
			&i.InvoiceStatus,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}