package sales

import (
	"context"
	"errors"
	"fmt"
	"time"

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

	"github.com/TaruLab/keluargamart-backend/internal/db"
)

// Repository implements Store on a pgx pool.
type Repository struct {
	pool *pgxpool.Pool
	q    *db.Queries
}

func NewRepository(pool *pgxpool.Pool) *Repository {
	return &Repository{pool: pool, q: db.New(pool)}
}

func (r *Repository) Begin(ctx context.Context) (Tx, error) {
	return r.pool.Begin(ctx)
}

// --- lookups -----------------------------------------------------------------

// queries binds the sqlc queries to the caller's transaction, falling back to
// the pool only when the caller is not inside one. Reading through the pool
// while holding a transaction deadlocks once concurrent callers exhaust it.
func (r *Repository) queries(q db.DBTX) *db.Queries {
	if q == nil {
		return r.q
	}
	return db.New(q)
}

// execer is queries() for the hand-written statements in this file.
//
// It exists because those methods take the same `q db.DBTX` as the generated
// ones but were calling q.Exec / q.QueryRow directly. A caller outside a
// transaction passes nil, meaning "use the pool" — the same meaning nil has
// everywhere else in this package — and a nil interface has no methods, so the
// call panicked with a nil pointer dereference instead of running.
//
// That is not theoretical: SetInvoiceSnapRef is reached from ProcessPOS with a
// literal nil (sales/service.go), and every QRIS order died with a 500 at that
// line. It compiled, vetted and passed the suite, because the unit tests use a
// fake store and never exercise a real *Repository.
//
// The rule this enforces: inside these methods, never touch the caller's q
// directly. Go through queries() or execer() so nil is handled once.
func (r *Repository) execer(q db.DBTX) db.DBTX {
	if q == nil {
		// r.pool, NOT r.q: a *db.Queries is the generated wrapper and has no
		// Exec/QueryRow of its own, so it does not satisfy db.DBTX. The pool is
		// the thing that does, and is the same connection source r.q is built
		// from.
		return r.pool
	}
	return q
}

func (r *Repository) CustomerExists(ctx context.Context, q db.DBTX, id int64) (bool, error) {
	n, err := r.queries(q).CountCustomersActive(ctx, id)
	return n > 0, err
}

func (r *Repository) ProductExists(ctx context.Context, q db.DBTX, id int64) (bool, error) {
	n, err := r.queries(q).CountProductsActive(ctx, id)
	return n > 0, err
}

func (r *Repository) ProductSellingPrice(ctx context.Context, q db.DBTX, id int64) (decimal.Decimal, error) {
	p, err := r.queries(q).GetProductSellingPrice(ctx, id)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return decimal.Zero, ErrNotFound
		}
		return decimal.Zero, err
	}
	return p, nil
}

// CheckStockAvailable reports whether the on-hand minus reserved stock can
// cover qty for the product/warehouse pair (used by tests and lookups).
func (r *Repository) CheckStockAvailable(ctx context.Context, q db.DBTX, productID, warehouseID int64, qty decimal.Decimal) (bool, error) {
	available, err := db.New(q).StockAvailable(ctx, db.StockAvailableParams{ProductID: productID, WarehouseID: warehouseID})
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return false, nil
		}
		return false, err
	}
	return available.GreaterThanOrEqual(qty), nil
}

// CustomerByUserID resolves a linked customer for the given user, or ErrNotFound.
func (r *Repository) CustomerByUserID(ctx context.Context, q db.DBTX, userID int64) (int64, error) {
	id, err := r.queries(q).GetCustomerIDByUserID(ctx, int8(userID))
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return 0, ErrNotFound
		}
		return 0, err
	}
	return id, nil
}

// CustomerAssignedSalesID returns the referral sales user for a customer, or
// nil when self-registered / unattributed. Never an error for a missing row.
func (r *Repository) CustomerAssignedSalesID(ctx context.Context, q db.DBTX, customerID int64) (*int64, error) {
	v, err := r.queries(q).GetCustomerAssignedSales(ctx, customerID)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return nil, nil
		}
		return nil, err
	}
	if !v.Valid {
		return nil, nil
	}
	id := v.Int64
	return &id, nil
}

// WalkInCustomerID resolves the walk-in customer (CUS-000 "Pelanggan Umum"),
// or ErrNotFound when bootstrap seed data is missing.
func (r *Repository) WalkInCustomerID(ctx context.Context, q db.DBTX) (int64, error) {
	id, err := r.queries(q).GetWalkInCustomerID(ctx)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			var newID int64
			insertErr := r.execer(q).QueryRow(ctx, `
				INSERT INTO customers (code, name, customer_type, credit_limit, payment_terms, is_active)
				VALUES ('CUS-000', 'Pelanggan Umum', 'retail', 0, 0, true)
				ON CONFLICT (code) DO UPDATE
				SET deleted_at = NULL, is_active = true, name = 'Pelanggan Umum'
				RETURNING id
			`).Scan(&newID)
			if insertErr == nil {
				return newID, nil
			}
			return 0, ErrNotFound
		}
		return 0, err
	}
	return id, nil
}

// CreateCustomerForUser auto-provisions a retail customer for a buyer inside
// the caller's tx. Returns ErrCustomerCodeTaken when the generated code
// collides (caller retries with a fresh code) and the user still has no linked
// customer.
func (r *Repository) CreateCustomerForUser(ctx context.Context, q db.DBTX, code, name, email string, userID int64) (int64, error) {
	qx := r.queries(q)
	id, err := qx.CreateCustomerForUser(ctx, db.CreateCustomerForUserParams{
		Code:   code,
		Name:   name,
		Email:  textOrNil(email),
		UserID: int8OrNil(&userID),
	})
	if errors.Is(err, pgx.ErrNoRows) {
		id, err = qx.GetCustomerIDByUserID(ctx, int8(userID))
		if errors.Is(err, pgx.ErrNoRows) {
			return 0, ErrCustomerCodeTaken
		}
	}
	return id, err
}

func (r *Repository) FirstActiveWarehouse(ctx context.Context, q db.DBTX) (int64, error) {
	id, err := r.queries(q).FirstActiveWarehouse(ctx)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return 1, nil // Laravel fallback 1
		}
		return 0, err
	}
	return id, nil
}

func (r *Repository) FirstActiveStore(ctx context.Context, q db.DBTX) (int64, int64, bool, error) {
	row, err := r.queries(q).FirstActiveStore(ctx)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return 0, 0, false, nil
		}
		return 0, 0, false, err
	}
	return row.ID, row.WarehouseID, true, nil
}

func (r *Repository) ValidateCheckoutWarehouse(ctx context.Context, q db.DBTX, warehouseID int64) error {
	var active bool
	err := r.execer(q).QueryRow(ctx, `SELECT is_active FROM warehouses WHERE id=$1`, warehouseID).Scan(&active)
	if errors.Is(err, pgx.ErrNoRows) || (err == nil && !active) {
		return fmt.Errorf("%w: choose an active warehouse", ErrCheckoutValidation)
	}
	return err
}

func (r *Repository) WarehouseForStore(ctx context.Context, q db.DBTX, storeID int64) (int64, error) {
	id, err := r.queries(q).WarehouseForStore(ctx, storeID)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return 0, ErrNotFound
		}
		return 0, err
	}
	return id, nil
}

func (r *Repository) IsStoreActive(ctx context.Context, q db.DBTX, storeID int64) (bool, error) {
	return r.queries(q).IsStoreActive(ctx, storeID)
}

func (r *Repository) IsUserInActiveStore(ctx context.Context, q db.DBTX, userID, storeID int64) (bool, error) {
	return r.queries(q).IsUserInActiveStore(ctx, db.IsUserInActiveStoreParams{
		UserID:  userID,
		StoreID: storeID,
	})
}

func (r *Repository) ActiveStoresForUser(ctx context.Context, q db.DBTX, userID int64) ([]int64, error) {
	return r.queries(q).StoresForUser(ctx, userID)
}

func (r *Repository) ShiftStoreID(ctx context.Context, q db.DBTX, shiftID int64) (*int64, error) {
	sh, err := r.queries(q).GetCashierShiftByID(ctx, shiftID)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return nil, ErrNotFound
		}
		return nil, err
	}
	if !sh.StoreID.Valid {
		return nil, nil
	}
	id := sh.StoreID.Int64
	return &id, nil
}

func (r *Repository) SetShiftStoreID(ctx context.Context, q db.DBTX, shiftID int64, storeID int64) error {
	_, err := r.execer(q).Exec(ctx, "UPDATE cashier_shifts SET store_id = $2, updated_at = now() WHERE id = $1 AND store_id IS NULL", shiftID, storeID)
	return err
}

// --- orders (tx-scoped writes) -----------------------------------------------

func (r *Repository) CreateOrder(ctx context.Context, q db.DBTX, p db.CreateSalesOrderParams) (db.CreateSalesOrderRow, error) {
	return db.New(q).CreateSalesOrder(ctx, p)
}

func (r *Repository) UpdateOrder(ctx context.Context, q db.DBTX, p db.UpdateSalesOrderParams) error {
	n, err := db.New(q).UpdateSalesOrder(ctx, p)
	if err != nil {
		return err
	}
	if n == 0 {
		return ErrNotFound
	}
	return nil
}

func (r *Repository) DeleteOrder(ctx context.Context, q db.DBTX, id int64) error {
	n, err := db.New(q).DeleteSalesOrder(ctx, id)
	if err != nil {
		return err
	}
	if n == 0 {
		return ErrNotFound
	}
	return nil
}

func (r *Repository) OrderByID(ctx context.Context, id int64) (db.GetSalesOrderByIDRow, error) {
	row, err := r.q.GetSalesOrderByID(ctx, id)
	if errors.Is(err, pgx.ErrNoRows) {
		return db.GetSalesOrderByIDRow{}, ErrNotFound
	}
	return row, err
}

func (r *Repository) OrderByIDForUpdate(ctx context.Context, q db.DBTX, id int64) (db.GetSalesOrderByIDForUpdateRow, error) {
	row, err := db.New(q).GetSalesOrderByIDForUpdate(ctx, id)
	if errors.Is(err, pgx.ErrNoRows) {
		return db.GetSalesOrderByIDForUpdateRow{}, ErrNotFound
	}
	return row, err
}

// OrderShiftID returns the cashier shift the order was placed on (NULL when
// the order is not POS-linked). Used to route cash refunds back to the drawer.
func (r *Repository) OrderShiftID(ctx context.Context, q db.DBTX, id int64) (pgtype.Int8, error) {
	return r.queries(q).GetSalesOrderShiftID(ctx, id)
}

func (r *Repository) ListOrders(ctx context.Context, f SOFilter) ([]db.ListSalesOrdersRow, int, error) {
	var excl pgtype.Bool
	if f.ExcludeUnapproved {
		excl = pgtype.Bool{Bool: true, Valid: true}
	}
	var noPickup pgtype.Bool
	if f.ExcludePickup {
		noPickup = pgtype.Bool{Bool: true, Valid: true}
	}
	var pickupOnly pgtype.Bool
	if f.PickupOnly {
		pickupOnly = pgtype.Bool{Bool: true, Valid: true}
	}
	filter := db.CountSalesOrdersParams{
		Search: textOrNil(f.Search), Status: textOrNil(f.Status), ExcludeUnapproved: excl, ExcludePickup: noPickup, PickupOnly: pickupOnly, CustomerID: int8OrNil(&f.CustomerID),
		OrderType: textOrNil(f.OrderType), SourceType: textOrNil(f.SourceType),
		DateFrom: dateOrNil(f.DateFrom), DateTo: dateOrNil(f.DateTo),
		AllowedStoreIds: f.AllowedStoreIDs,
	}
	if f.CustomerID <= 0 {
		filter.CustomerID = pgtype.Int8{}
	}
	if f.StoreID != nil {
		filter.StoreID = int8OrNil(f.StoreID)
	}
	total, err := r.q.CountSalesOrders(ctx, filter)
	if err != nil {
		return nil, 0, err
	}
	rows, err := r.q.ListSalesOrders(ctx, db.ListSalesOrdersParams{
		Limit: int32(f.PerPage), Offset: int32((f.Page - 1) * f.PerPage), Search: filter.Search, Status: filter.Status,
		ExcludeUnapproved: excl, ExcludePickup: noPickup, PickupOnly: pickupOnly,
		CustomerID: filter.CustomerID, OrderType: filter.OrderType, SourceType: filter.SourceType,
		// The page rows MUST carry the same filters as the count. Omitting
		// these made the date picker inert (rows from every month came back)
		// and, worse, dropped the tenant scope from the row query while the
		// COUNT kept it — a store-scoped caller saw other stores' orders.
		DateFrom: filter.DateFrom, DateTo: filter.DateTo,
		StoreID: filter.StoreID, AllowedStoreIds: f.AllowedStoreIDs,
		SortBy: textOrNil(f.SortBy), SortDir: textOrNil(f.SortDir),
	})
	if err != nil {
		return nil, 0, err
	}
	return rows, int(total), nil
}

func (r *Repository) MyOrdersSummary(ctx context.Context, customerID int64) (db.MyOrdersSummaryRow, error) {
	return r.q.MyOrdersSummary(ctx, customerID)
}

func (r *Repository) SalesOrdersSummary(ctx context.Context, allowedStoreIDs []int64) (SalesOrdersSummary, error) {
	var s SalesOrdersSummary
	var ids []int64
	if len(allowedStoreIDs) > 0 {
		ids = allowedStoreIDs
	}
	err := r.pool.QueryRow(ctx, `
SELECT
  COUNT(*) FILTER (WHERE order_date = CURRENT_DATE AND status <> 'cancelled') AS today_count,
  COALESCE(SUM(total_amount) FILTER (WHERE order_date = CURRENT_DATE AND status <> 'cancelled'), 0) AS today_revenue,
  COUNT(*) FILTER (WHERE status IN ('draft', 'pending_approval', 'confirmed', 'picking', 'shipped')) AS processing_count,
  COUNT(*) FILTER (WHERE status IN ('draft', 'pending_approval', 'blocked_overdue')) AS pending_approval_count
FROM sales_orders
WHERE ($1::bigint[] IS NULL OR store_id IS NULL OR store_id = ANY($1::bigint[]))`, ids).Scan(
		&s.TodayCount,
		&s.TodayRevenue,
		&s.ProcessingCount,
		&s.PendingApprovalCount,
	)
	return s, err
}

// --- items -------------------------------------------------------------------

func (r *Repository) CreateItem(ctx context.Context, q db.DBTX, p db.CreateSalesOrderItemParams) (int64, error) {
	return db.New(q).CreateSalesOrderItem(ctx, p)
}

func (r *Repository) ItemsByOrder(ctx context.Context, orderID int64) ([]db.ListSalesOrderItemsRow, error) {
	return r.q.ListSalesOrderItems(ctx, orderID)
}

// ReturnedQtyByOrder reuses the generated per-line sum over the pool handle
// (no new SQL, no sqlc regen): a return dialog holds a handful of lines.
func (r *Repository) ReturnedQtyByOrder(ctx context.Context, orderID int64) (map[int64]decimal.Decimal, error) {
	items, err := r.q.ListSalesOrderItems(ctx, orderID)
	if err != nil {
		return nil, err
	}
	out := make(map[int64]decimal.Decimal, len(items))
	for _, it := range items {
		total, err := r.q.SumReturnedQtyByOrderItem(ctx, it.ID)
		if err != nil {
			return nil, err
		}
		if !total.IsZero() {
			out[it.ID] = total
		}
	}
	return out, nil
}

func (r *Repository) DeleteItems(ctx context.Context, q db.DBTX, orderID int64) error {
	return db.New(q).DeleteSalesOrderItems(ctx, orderID)
}

func (r *Repository) SetOrderStatus(ctx context.Context, q db.DBTX, id int64, status string) error {
	_, err := db.New(q).SetSalesOrderStatus(ctx, db.SetSalesOrderStatusParams{ID: id, Status: status})
	return err
}

func (r *Repository) UpdateOrderNotes(ctx context.Context, q db.DBTX, id int64, notes pgtype.Text) error {
	n, err := db.New(q).UpdateSalesOrderNotes(ctx, db.UpdateSalesOrderNotesParams{ID: id, Notes: notes})
	if err != nil {
		return err
	}
	if n == 0 {
		return ErrNotFound
	}
	return nil
}

func (r *Repository) SetSalesOrderShift(ctx context.Context, q db.DBTX, orderID, shiftID int64) error {
	_, err := r.queries(q).SetSalesOrderShift(ctx, db.SetSalesOrderShiftParams{ID: orderID, ShiftID: pgtype.Int8{Int64: shiftID, Valid: true}})
	return err
}

// --- stock within a tx (row-locked) ------------------------------------------

func (r *Repository) LockStockForUpdate(ctx context.Context, q db.DBTX, productID, warehouseID int64) (db.GetSalesStockRowForUpdateRow, error) {
	row, err := db.New(q).GetSalesStockRowForUpdate(ctx, db.GetSalesStockRowForUpdateParams{ProductID: productID, WarehouseID: warehouseID})
	if errors.Is(err, pgx.ErrNoRows) {
		return db.GetSalesStockRowForUpdateRow{}, ErrNotFound
	}
	return row, err
}

func (r *Repository) EnsureStockRow(ctx context.Context, q db.DBTX, productID, warehouseID int64) error {
	return db.New(q).EnsureSalesStockRow(ctx, db.EnsureSalesStockRowParams{ProductID: productID, WarehouseID: warehouseID})
}

// SetStockQuantity is the reservation/deduction write for reserveStock,
// releaseStock, deductStock and restockStock. Every caller passes row.ID from
// lockStock (SELECT ... FOR UPDATE, re-locked after EnsureStockRow), so a
// zero-row match is never legitimate and used to be swallowed — the write
// reported success while reserving/deducting nothing. Same mapping as
// internal/distribution, which wraps the identical UpdateSalesStockRow query.
func (r *Repository) SetStockQuantity(ctx context.Context, q db.DBTX, stockID int64, quantity, reserved decimal.Decimal) error {
	n, err := db.New(q).UpdateSalesStockRow(ctx, db.UpdateSalesStockRowParams{ID: stockID, Quantity: quantity, ReservedQuantity: reserved})
	if err != nil {
		return err
	}
	if n == 0 {
		return ErrNotFound
	}
	return nil
}

// --- invoice/payment ---------------------------------------------------------

func (r *Repository) CreateInvoice(ctx context.Context, q db.DBTX, p db.CreateSalesInvoiceParams) (int64, error) {
	return db.New(q).CreateSalesInvoice(ctx, p)
}

func (r *Repository) CreatePayment(ctx context.Context, q db.DBTX, p db.CreateSalesPaymentParams) (int64, error) {
	// Shared by till, webhook, polling and sweep settlement. Provider
	// callbacks have no user identity but still serialize with shift closure.
	var status string
	err := r.execer(q).QueryRow(ctx, `SELECT sh.status FROM cashier_shifts sh JOIN sales_orders so ON so.shift_id=sh.id JOIN invoices i ON i.sales_order_id=so.id WHERE i.id=$1 FOR UPDATE OF sh`, p.PayableID).Scan(&status)
	if err != nil && !errors.Is(err, pgx.ErrNoRows) {
		return 0, err
	}
	if err == nil && status != "open" {
		return 0, fmt.Errorf("%w: cashier shift is closed", ErrInvalidStatus)
	}
	return db.New(q).CreateSalesPayment(ctx, p)
}

func (r *Repository) CreateGatewayPayment(ctx context.Context, q db.DBTX, p db.CreateSalesPaymentParams, gateway, txnID, status string) (int64, error) {
	var shStatus string
	err := r.execer(q).QueryRow(ctx, `SELECT sh.status FROM cashier_shifts sh JOIN sales_orders so ON so.shift_id=sh.id JOIN invoices i ON i.sales_order_id=so.id WHERE i.id=$1 FOR UPDATE OF sh`, p.PayableID).Scan(&shStatus)
	if err != nil && !errors.Is(err, pgx.ErrNoRows) {
		return 0, err
	}
	if err == nil && shStatus != "open" {
		return 0, fmt.Errorf("%w: cashier shift is closed", ErrInvalidStatus)
	}
	var id int64
	var txnIDArg any
	if txnID != "" {
		txnIDArg = txnID
	}
	var statusArg any
	if status != "" {
		statusArg = status
	}
	err = r.execer(q).QueryRow(ctx, `INSERT INTO payments (payment_number, payable_type, payable_id, payment_date, amount, payment_method, notes, created_by, gateway, gateway_txn_id, gateway_status)
		VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11) RETURNING id`,
		p.PaymentNumber, p.PayableType, p.PayableID, p.PaymentDate, p.Amount, p.PaymentMethod, p.Notes, p.CreatedBy,
		gateway, txnIDArg, statusArg).Scan(&id)
	return id, err
}

func (r *Repository) OpenInvoiceForOrder(ctx context.Context, q db.DBTX, orderID int64) (*Invoice, error) {
	var inv Invoice
	var ref pgtype.Text
	err := r.execer(q).QueryRow(ctx, `SELECT id, invoice_number, customer_id, invoice_date::text, due_date::text, status,
		subtotal, tax_amount, total_amount, paid_amount, remaining_amount, snap_order_ref
		FROM invoices WHERE sales_order_id = $1 AND status IN ('unpaid','partial')
		ORDER BY id LIMIT 1 FOR UPDATE`, orderID).Scan(&inv.ID, &inv.InvoiceNumber, &inv.CustomerID,
		&inv.InvoiceDate, &inv.DueDate, &inv.Status, &inv.Subtotal, &inv.TaxAmount, &inv.TotalAmount,
		&inv.PaidAmount, &inv.RemainingAmount, &ref)
	if errors.Is(err, pgx.ErrNoRows) {
		return nil, ErrNotFound
	}
	if err != nil {
		return nil, err
	}
	inv.SalesOrderID = orderID
	inv.SnapOrderRef = textToStrPtr(ref)
	return &inv, nil
}

func (r *Repository) InvoiceExistsForSO(ctx context.Context, q db.DBTX, soID int64) (bool, error) {
	return db.New(q).InvoiceExistsForSO(ctx, pgtype.Int8{Int64: soID, Valid: true})
}

// InvoiceForOrder returns the order's invoice locked FOR UPDATE with NO status
// predicate, or ErrNotFound when the order has none.
//
// It exists to tell "already settled" apart from "written off":
// OpenInvoiceForOrder answers ErrNotFound for both, and a caller that reads
// that single answer as "settled" tells a buyer whose order was cancelled that
// their payment landed. This is a status *read*, never a settle entry point —
// use OpenInvoiceForOrder when you mean to mutate the invoice.
func (r *Repository) InvoiceForOrder(ctx context.Context, q db.DBTX, orderID int64) (*Invoice, error) {
	var inv Invoice
	var ref pgtype.Text
	err := r.execer(q).QueryRow(ctx, `SELECT id, invoice_number, customer_id, invoice_date::text, due_date::text, status,
		subtotal, tax_amount, total_amount, paid_amount, remaining_amount, snap_order_ref
		FROM invoices WHERE sales_order_id = $1
		ORDER BY id LIMIT 1 FOR UPDATE`, orderID).Scan(&inv.ID, &inv.InvoiceNumber, &inv.CustomerID,
		&inv.InvoiceDate, &inv.DueDate, &inv.Status, &inv.Subtotal, &inv.TaxAmount, &inv.TotalAmount,
		&inv.PaidAmount, &inv.RemainingAmount, &ref)
	if errors.Is(err, pgx.ErrNoRows) {
		return nil, ErrNotFound
	}
	if err != nil {
		return nil, err
	}
	inv.SalesOrderID = orderID
	inv.SnapOrderRef = textToStrPtr(ref)
	return &inv, nil
}

// MarkInvoicePaid flips an invoice to paid and zeroes its open balance, and
// returns the number of rows it changed.
//
// The status predicate is defence in depth behind the service-level settleable
// check (HandleMidtransNotification mirrors finance.PayInvoice): CancelOrderInvoices
// leaves a cancelled invoice's total_amount AND snap_order_ref in place, and
// InvoiceBySnapRef deliberately resolves the ref regardless of status, so a late
// capture for a cancelled order reaches the settle path. Without the predicate
// here a future caller that forgets the service guard resurrects written-off
// debt.
//
// The row count is returned rather than folded into an error because "changed
// nothing" is a real, non-exceptional answer the caller must be able to BRANCH
// on — the webhook's already-settled branch, and the reconciler's "someone else
// got there first" branch. Returning it makes a new caller safe by
// construction instead of by remembering the convention: a caller that ignores
// the value behaves exactly as before (no error, silent no-op), and a caller
// that reads it cannot mistake a no-op for a settle. A zero-row match stays a
// silent no-op, NOT ErrNotFound, for the reason above: every caller settles a
// row it already locked FOR UPDATE, and the callers' payment-row writes stay
// inside the same transaction.
func (r *Repository) MarkInvoicePaid(ctx context.Context, q db.DBTX, invoiceID int64, paid decimal.Decimal) (int64, error) {
	tag, err := r.execer(q).Exec(ctx, `UPDATE invoices SET status = 'paid', paid_amount = $2, remaining_amount = 0, updated_at = now()
		WHERE id = $1 AND status IN ('unpaid','partial')`, invoiceID, paid)
	if err != nil {
		return 0, err
	}
	return tag.RowsAffected(), nil
}

// SettleOrderPayment updates paid_amount and remaining_amount, and advances the
// order to 'invoiced' when it becomes fully paid.
//
// 'pending_approval' is deliberately NOT in the advance list: a settled
// Midtrans payment records the money but is not a human acceptance. Buyer
// orders stay in pending_approval (Menunggu Konfirmasi) until someone runs
// Confirm — approved_by then records WHO accepted, which a webhook can never
// do. confirmed/draft advance because acceptance already happened there; an
// in-flight fulfilment status never moves backwards.
func (r *Repository) SettleOrderPayment(ctx context.Context, q db.DBTX, orderID int64, amount decimal.Decimal) error {
	_, err := r.execer(q).Exec(ctx,
		`UPDATE sales_orders
		 SET paid_amount = LEAST(total_amount, paid_amount + $1),
		     remaining_amount = GREATEST(0, remaining_amount - $1),
		     status = CASE
		         WHEN status IN ('picking', 'shipped', 'delivered') THEN status
		         WHEN remaining_amount - $1 <= 0 AND status IN ('draft', 'confirmed') THEN 'invoiced'
		         ELSE status
		     END,
		     updated_at = now()
		 WHERE id = $2`,
		amount, orderID)
	return err
}

func (r *Repository) SetInvoiceSnapRef(ctx context.Context, q db.DBTX, invoiceID int64, ref string) error {
	_, err := r.execer(q).Exec(ctx, `UPDATE invoices SET snap_order_ref = $2, updated_at = now() WHERE id = $1`, invoiceID, ref)
	return err
}

// SetInvoiceGatewayRef stamps the Midtrans order_id AND, for the Core API
// channel, the transaction_id and the provider's expiry in one write.
//
// One statement, not three: the POS flow sets all three before the till is
// told anything, and a partially stamped invoice would leave the QR image
// unreachable on a reload — the image lives at
// GET /v2/qris/{transaction_id}/qr-code, so the transaction id is not optional
// metadata, it is the only way back to the QR.
//
// The columns are nullable so the same statement serves Snap, which has no
// transaction id and no expiry.
func (r *Repository) SetInvoiceGatewayRef(ctx context.Context, q db.DBTX, invoiceID int64, ref string, transactionID *string, expiresAt *time.Time) error {
	_, err := r.execer(q).Exec(ctx, `UPDATE invoices
		SET snap_order_ref = $2,
		    gateway_transaction_id = COALESCE($3, gateway_transaction_id),
		    gateway_expires_at = COALESCE($4, gateway_expires_at),
		    updated_at = now()
		WHERE id = $1`, invoiceID, ref, transactionID, expiresAt)
	return err
}

// SetInvoiceSnapChecked stamps the reconciler's last probe of this invoice
// (invoices.snap_last_checked_at, migration 000102) inside the CALLER's
// transaction — deliberately with no transaction of its own.
//
// That boundary is load-bearing, not a convenience. The reconciler probes
// Midtrans and then, in one transaction, either settles the invoice or records
// that it has not settled. If the stamp committed independently, a hard
// provider error would leave the stamp behind and the invoice would go
// unprobed until the throttle expired; keeping them in one transaction means a
// rolled-back settle rolls back the stamp and the invoice is retried on the
// next pass, while a committed "no settlement" keeps the stamp and backs off.
//
// It does NOT touch updated_at, on purpose. A probe writes no business state,
// and stamping updated_at here would slide the candidate window forward on every
// pass so the candidate set would never drain (see the migration comment). A
// zero-row match is not ErrNotFound: the reconciler probes rows it just
// selected, and a concurrent settle may have removed the candidate underneath
// it, which is an ordinary outcome rather than a missing row.
func (r *Repository) SetInvoiceSnapChecked(ctx context.Context, q db.DBTX, invoiceID int64) error {
	_, err := r.execer(q).Exec(ctx, `UPDATE invoices SET snap_last_checked_at = now() WHERE id = $1`, invoiceID)
	return err
}

// ListSnapSweepCandidates returns the open Snap invoices worth re-probing, oldest
// activity first, capped at limit rows.
//
// The age bound is on updated_at, NOT created_at. SetInvoiceSnapRef bumps
// updated_at on every SnapToken retry and overwrites the ref, so an invoice
// that has been retried is exactly one whose "when did this start" is the least
// informative column; filtering on created_at would exclude the oldest and most
// abandoned invoices — the ones most worth sweeping — whenever a retry had
// touched them.
//
// 'overdue' is deliberately absent from the status predicate. It is a VIRTUAL
// status: internal/db/finance.sql.go computes it from due_date at read time
// (status IN ('unpaid','partial') AND due_date < CURRENT_DATE) and never
// stores the literal, so the two-value predicate already covers every row a
// stored 'overdue' would have covered.
//
// so.status <> 'cancelled' excludes invoices whose order was written off.
// CancelOrderInvoices leaves the invoice's total_amount AND snap_order_ref in
// place, so without this a cancelled order's invoice would be probed forever
// and a late capture on a dead order would look like a settlement candidate.
//
// limit is passed through, but BOUNDED here as well: the service owns the
// policy clamp, and this clamp is only a backstop so a caller bug cannot turn a
// capped scan into an unbounded one. A non-positive limit is clamped to the
// same ceiling rather than rejected, so a bad caller degrades to "probes the
// most it can" instead of silently sweeping nothing.
func (r *Repository) ListSnapSweepCandidates(ctx context.Context, limit int) ([]SnapSweepCandidate, error) {
	if limit <= 0 || limit > snapSweepMaxBatch {
		limit = snapSweepMaxBatch
	}
	rows, err := r.pool.Query(ctx,
		`SELECT i.id, i.invoice_number, i.sales_order_id
		 FROM invoices i
		 JOIN sales_orders so ON so.id = i.sales_order_id
		 WHERE i.status IN ('unpaid','partial')
		   AND i.snap_order_ref IS NOT NULL
		   AND so.order_type = 'ecommerce'
		   AND so.status <> 'cancelled'
		   AND i.updated_at > now() - interval '12 hours'
		   AND (i.snap_last_checked_at IS NULL
		        OR i.snap_last_checked_at < now() - interval '10 minutes')
		 ORDER BY i.updated_at ASC
		 LIMIT $1`, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []SnapSweepCandidate
	for rows.Next() {
		var c SnapSweepCandidate
		if err := rows.Scan(&c.ID, &c.InvoiceNumber, &c.SalesOrderID); err != nil {
			return nil, err
		}
		out = append(out, c)
	}
	return out, rows.Err()
}

// snapSweepMaxBatch caps one candidate scan. The reconciler probes each row
// with a network call, so an unbounded batch would hold a connection and a
// transaction open for as long as the scan runs; the service clamps to its own
// per-pass budget and this is the last line of defence.
const snapSweepMaxBatch = 500

// InvoiceBySnapRef resolves a webhook order_id to the invoice that carries it.
// snap_order_ref is overwritten on every SnapToken retry (timestamp suffix),
// but Midtrans keeps notifying on the OLD ref after a retry re-issued the
// token — so a payment made on a previous attempt must still settle.
//
// The retry appends "-<unix>" to the canonical ref, so an arriving ref is
// either the stored ref itself (paid before any retry) or a PREFIX of it
// (paid on attempt 1, retry already overwrote storage). Matching direction:
// stored == arriving OR stored LIKE arriving || '-%'. This cannot collide
// across invoices: refs start with KM-<invoice_number> and invoice numbers
// carry their own INV- prefix, so a dash-anchored prefix of one invoice's ref
// never prefixes another's.
func (r *Repository) InvoiceBySnapRef(ctx context.Context, q db.DBTX, ref string) (*Invoice, error) {
	var inv Invoice
	var orderID pgtype.Int8
	err := r.execer(q).QueryRow(ctx, `SELECT id, invoice_number, customer_id, invoice_date::text, due_date::text, status,
		subtotal, tax_amount, total_amount, paid_amount, remaining_amount, snap_order_ref, sales_order_id
		FROM invoices WHERE snap_order_ref = $1 OR snap_order_ref LIKE $1 || '-%'
		ORDER BY id LIMIT 1`, ref).Scan(&inv.ID, &inv.InvoiceNumber, &inv.CustomerID,
		&inv.InvoiceDate, &inv.DueDate, &inv.Status, &inv.Subtotal, &inv.TaxAmount, &inv.TotalAmount,
		&inv.PaidAmount, &inv.RemainingAmount, new(pgtype.Text), &orderID)
	if errors.Is(err, pgx.ErrNoRows) {
		return nil, ErrNotFound
	}
	if err != nil {
		return nil, err
	}
	if orderID.Valid {
		inv.SalesOrderID = orderID.Int64
	}
	inv.SnapOrderRef = &ref
	return &inv, nil
}

func (r *Repository) CancelOrderInvoices(ctx context.Context, q db.DBTX, orderID int64) error {
	_, err := r.execer(q).Exec(ctx, `UPDATE invoices SET status = 'cancelled', remaining_amount = 0, updated_at = now() WHERE sales_order_id = $1 AND status != 'paid'`, orderID)
	return err
}

// --- sales vouchers ------------------------------------------------------------

func (r *Repository) CreateSalesVoucher(ctx context.Context, q db.DBTX, p db.CreateSalesVoucherParams) (db.CreateSalesVoucherRow, error) {
	return r.queries(q).CreateSalesVoucher(ctx, p)
}

func (r *Repository) GetSalesVoucherByID(ctx context.Context, q db.DBTX, id int64) (db.GetSalesVoucherByIDRow, error) {
	row, err := r.queries(q).GetSalesVoucherByID(ctx, id)
	if errors.Is(err, pgx.ErrNoRows) {
		return db.GetSalesVoucherByIDRow{}, ErrNotFound
	}
	return row, err
}

func (r *Repository) GetSalesVoucherByNumber(ctx context.Context, q db.DBTX, voucherNumber string) (db.GetSalesVoucherByNumberRow, error) {
	row, err := r.queries(q).GetSalesVoucherByNumber(ctx, voucherNumber)
	if errors.Is(err, pgx.ErrNoRows) {
		return db.GetSalesVoucherByNumberRow{}, ErrNotFound
	}
	return row, err
}

func (r *Repository) GetSalesVoucherByNumberForUpdate(ctx context.Context, q db.DBTX, voucherNumber string) (db.GetSalesVoucherByNumberForUpdateRow, error) {
	row, err := r.queries(q).GetSalesVoucherByNumberForUpdate(ctx, voucherNumber)
	if errors.Is(err, pgx.ErrNoRows) {
		return db.GetSalesVoucherByNumberForUpdateRow{}, ErrNotFound
	}
	return row, err
}

func (r *Repository) GetSalesVoucherByIDForUpdate(ctx context.Context, q db.DBTX, id int64) (db.GetSalesVoucherByIDForUpdateRow, error) {
	row, err := r.queries(q).GetSalesVoucherByIDForUpdate(ctx, id)
	if errors.Is(err, pgx.ErrNoRows) {
		return db.GetSalesVoucherByIDForUpdateRow{}, ErrNotFound
	}
	return row, err
}

func (r *Repository) UpdateSalesVoucherStatus(ctx context.Context, q db.DBTX, id int64, status string) error {
	n, err := r.queries(q).UpdateSalesVoucherStatus(ctx, db.UpdateSalesVoucherStatusParams{ID: id, Status: status})
	if err != nil {
		return err
	}
	if n == 0 {
		return ErrNotFound
	}
	return nil
}

func (r *Repository) ListSalesVouchers(ctx context.Context, q db.DBTX, f db.ListSalesVouchersParams) ([]db.ListSalesVouchersRow, error) {
	return r.queries(q).ListSalesVouchers(ctx, f)
}

func (r *Repository) CountSalesVouchers(ctx context.Context, q db.DBTX, f db.CountSalesVouchersParams) (int64, error) {
	return r.queries(q).CountSalesVouchers(ctx, f)
}

// --- sales returns (§11.4) ---------------------------------------------------

func (r *Repository) CreateSalesReturn(ctx context.Context, q db.DBTX, p db.CreateSalesReturnParams) (db.CreateSalesReturnRow, error) {
	return r.queries(q).CreateSalesReturn(ctx, p)
}

func (r *Repository) GetSalesReturnByID(ctx context.Context, q db.DBTX, id int64) (db.GetSalesReturnByIDRow, error) {
	row, err := r.queries(q).GetSalesReturnByID(ctx, id)
	if errors.Is(err, pgx.ErrNoRows) {
		return db.GetSalesReturnByIDRow{}, ErrNotFound
	}
	return row, err
}

func (r *Repository) ListSalesReturns(ctx context.Context, q db.DBTX, f db.ListSalesReturnsParams) ([]db.ListSalesReturnsRow, error) {
	return r.queries(q).ListSalesReturns(ctx, f)
}

func (r *Repository) CountSalesReturns(ctx context.Context, q db.DBTX, f db.CountSalesReturnsParams) (int64, error) {
	return r.queries(q).CountSalesReturns(ctx, f)
}

func (r *Repository) CreateSalesReturnItem(ctx context.Context, q db.DBTX, p db.CreateSalesReturnItemParams) (db.SalesReturnItem, error) {
	return r.queries(q).CreateSalesReturnItem(ctx, p)
}

func (r *Repository) SetSalesReturnItemCondition(ctx context.Context, q db.DBTX, p db.SetSalesReturnItemConditionParams) (db.SalesReturnItem, error) {
	return r.queries(q).SetSalesReturnItemCondition(ctx, p)
}

func (r *Repository) ListSalesReturnItems(ctx context.Context, q db.DBTX, salesReturnID int64) ([]db.ListSalesReturnItemsRow, error) {
	return r.queries(q).ListSalesReturnItems(ctx, salesReturnID)
}

func (r *Repository) SumReturnedQtyByOrderItem(ctx context.Context, q db.DBTX, salesOrderItemID int64) (decimal.Decimal, error) {
	return r.queries(q).SumReturnedQtyByOrderItem(ctx, salesOrderItemID)
}

// SupervisorsByOrders reads the approval-routing columns (migration 000043),
// which are not part of the sqlc order queries.
func (r *Repository) SupervisorsByOrders(ctx context.Context, ids []int64) (map[int64]OrderSupervisor, error) {
	out := map[int64]OrderSupervisor{}
	if len(ids) == 0 {
		return out, nil
	}
	rows, err := r.pool.Query(ctx,
		`SELECT o.id, u.id, u.name
		 FROM sales_orders o
		 JOIN users u ON u.id = o.supervisor_id
		 WHERE o.id = ANY($1)`, ids)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		var orderID int64
		var sv OrderSupervisor
		if err := rows.Scan(&orderID, &sv.ID, &sv.Name); err != nil {
			return nil, err
		}
		out[orderID] = sv
	}
	return out, rows.Err()
}

func (r *Repository) ListSupervisors(ctx context.Context) ([]SupervisorOption, error) {
	rows, err := r.pool.Query(ctx,
		`SELECT id, name, role
		 FROM users
		 WHERE role IN ('spv', 'bm', 'gm', 'admin', 'owner', 'supervisor_sales', 'branch_manager', 'general_manager')
		   AND is_active = true
		 ORDER BY name ASC`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []SupervisorOption
	for rows.Next() {
		var o SupervisorOption
		if err := rows.Scan(&o.ID, &o.Name, &o.Role); err != nil {
			return nil, err
		}
		switch o.Role {
		case "spv", "supervisor_sales":
			o.RoleLabel = "Supervisor Sales"
		case "bm", "branch_manager":
			o.RoleLabel = "Branch Manager"
		case "gm", "general_manager":
			o.RoleLabel = "General Manager"
		case "admin":
			o.RoleLabel = "Administrator"
		case "owner":
			o.RoleLabel = "Owner"
		default:
			o.RoleLabel = o.Role
		}
		out = append(out, o)
	}
	return out, rows.Err()
}