package distribution

import (
	"context"
	"crypto/rand"
	"errors"
	"fmt"
	"mime/multipart"
	"regexp"
	"sort"
	"strings"
	"time"

	"github.com/jackc/pgx/v5/pgconn"
	"github.com/shopspring/decimal"
	"golang.org/x/crypto/bcrypt"

	"github.com/TaruLab/keluargamart-backend/internal/auth"
	"github.com/TaruLab/keluargamart-backend/internal/db"
	"github.com/TaruLab/keluargamart-backend/pkg/cache"
	"github.com/TaruLab/keluargamart-backend/pkg/logger"
	"github.com/TaruLab/keluargamart-backend/pkg/seq"
)

// Sentinel errors mapped to HTTP in the handler.
var (
	ErrNotFound              = errors.New("not found")
	ErrSORequired            = errors.New("sales_order_id is required")
	ErrSOInvalidStatus       = errors.New("sales order cannot be fulfilled")
	ErrItemRequired          = errors.New("items are required")
	ErrQuantityRequired      = errors.New("quantity must be > 0")
	ErrItemNotOnSO           = errors.New("item is not on the sales order")
	ErrItemNotOnDelivery     = errors.New("item is not on the delivery order")
	ErrOverShipment          = errors.New("shipment exceeds remaining quantity")
	ErrInsufficientStock     = errors.New("stok tidak mencukupi di gudang asal — lakukan stock opname atau transfer stok terlebih dahulu")
	ErrCannotShipment        = errors.New("delivery order tidak dapat menerima pengiriman baru (status sudah selesai, dibatalkan, atau siap dikirim)")
	ErrPODSaverNotWired      = errors.New("pod storage is not wired")
	ErrPODURLInvalid         = errors.New("pod storage returned a non-uploads URL")
	ErrShipmentClosed        = errors.New("pengiriman ini sudah selesai atau dibatalkan")
	ErrCannotDispatch        = errors.New("delivery order tidak dapat didispatch ulang (status sudah selesai atau dibatalkan)")
	ErrDispatchFleetRequired = errors.New("fleet is required for dispatch")
	ErrShipmentOnDORequired  = errors.New("shipment does not belong to this delivery order")
	ErrPriorityInvalid       = errors.New("priority must be urgent, high, normal or low")
	ErrOpenDOExists          = errors.New("sales order already has an open delivery order")
	ErrNothingShipped        = errors.New("delivery order tidak dapat diselesaikan: belum ada barang yang dikirim")
	ErrWarehouseRequired     = errors.New("warehouse is required")
	ErrPlateRequired         = errors.New("plate_number is required")
	ErrVehicleNameRequired   = errors.New("vehicle_name is required")
	ErrTrackingStatusInvalid = errors.New("tracking status must be pending, in_transit, delivered, failed or cancelled")
	ErrDriverNameRequired    = errors.New("name is required")
	ErrTrackingSourceInvalid = errors.New("tracking source must be driver_mobile, third_party or manual")
	ErrTrackingCoordsInvalid = errors.New("latitude and longitude must be supplied together and within range")
	ErrTrackingAccuracy      = errors.New("accuracy_m must be >= 0")
	ErrTrackingEventTime     = errors.New("event_time cannot be more than 5 minutes in the future")
	ErrIdentifierRequired    = errors.New("q is required")

	// ErrDOAlreadyBatched refuses a re-dispatch of a DO that sits in a
	// multi-member LHPP group. A group's driver_id is set from its first
	// member and is immutable while more than one DO is on the sheet, so
	// reassigning the driver mid-collection would silently hand the sheet to
	// somebody else. Split the DO out first.
	ErrDOAlreadyBatched = errors.New("DO ini sudah masuk LHPP bersama DO lain; pisahkan dulu sebelum ganti driver")
)

const (
	seqKeyDO  = "do"
	seqKeySHP = "shp"
	seqKeyTRK = "trk"
)

// Seq allocates atomic counters (pkg/seq) — injected so tests fake it.
type Seq interface {
	Sequence(ctx context.Context, prefix string, date time.Time, tx any) (int64, error)
}

// Service implements the Phase 4 distribution use cases.
type Service struct {
	store              Store
	seq                Seq
	loc                *time.Location
	shipmentLedger     ShipmentLedger
	lhppGrouper        LHPPGrouper
	locationProvider   LocationProvider
	locCache           cache.Cache
	allowNegativeStock bool
	podSaver           PODSaver
}

func NewService(store Store, seq Seq, loc *time.Location) *Service {
	return &Service{store: store, seq: seq, loc: loc}
}

// WithShipmentLedger wires the inventory movement recorder for shipment lines.
func (s *Service) WithShipmentLedger(fn ShipmentLedger) *Service { s.shipmentLedger = fn; return s }

// WithLHPPGrouper wires the billing-sheet grouper (internal/lhpp). Without it a
// dispatch writes no LHPP group at all, which is the state before 000101.
func (s *Service) WithLHPPGrouper(fn LHPPGrouper) *Service { s.lhppGrouper = fn; return s }

// WithLocationProvider wires a live tracking source (driver mobile GPS or a
// third-party integration). Without an active provider the API reports
// real_time.available=false and never claims live positions (§16).
func (s *Service) WithLocationProvider(p LocationProvider) *Service {
	s.locationProvider = p
	return s
}

func (s *Service) WithAllowNegativeStock(allow bool) *Service {
	s.allowNegativeStock = allow
	return s
}

// --- delivery orders ---------------------------------------------------------

// CreateDeliveryOrder builds a draft Delivery Order from a Sales Order. The SO
// must not be draft/cancelled and every requested line must exist on the SO.
func (s *Service) CreateDeliveryOrder(ctx context.Context, in DOInput, operatorID int64) (*DeliveryOrder, error) {
	if in.SalesOrderID == 0 {
		return nil, ErrSORequired
	}
	if len(in.Items) == 0 {
		return nil, ErrItemRequired
	}
	for _, it := range in.Items {
		if it.ProductID == 0 {
			return nil, ErrItemRequired
		}
		if it.RequestedQuantity.LessThanOrEqual(decimal.Zero) {
			return nil, ErrQuantityRequired
		}
	}
	if in.Priority == "" {
		in.Priority = PriorityNormal
	}
	if !isValidPriority(in.Priority) {
		return nil, ErrPriorityInvalid
	}

	tx, err := s.store.Begin(ctx)
	if err != nil {
		return nil, err
	}
	defer tx.Rollback(ctx)

	so, err := s.store.SalesOrderForDelivery(ctx, in.SalesOrderID)
	if err != nil {
		return nil, err
	}
	if so.Status == "draft" || so.Status == "cancelled" {
		return nil, ErrSOInvalidStatus
	}

	soItems, err := s.store.SalesOrderItems(ctx, in.SalesOrderID)
	if err != nil {
		return nil, err
	}
	onSO := map[int64]bool{}
	for _, it := range soItems {
		onSO[it.ProductID] = true
	}
	for _, it := range in.Items {
		if !onSO[it.ProductID] {
			return nil, ErrItemNotOnSO
		}
	}
	// One open DO per SO (double fulfillment): a second DO while another is
	// still working the same order ships it twice. Checked after item
	// validation so product errors keep their precedence. Terminal DOs
	// (delivered/completed/cancelled) don't block a re-dispatch.
	open, _, err := s.ListDeliveryOrders(ctx, DOFilter{SalesOrderID: in.SalesOrderID, Page: 1, PerPage: 50})
	if err != nil {
		return nil, err
	}
	for _, d := range open {
		if d.Status != StatusCompleted && d.Status != StatusCancelled && d.Status != "delivered" {
			return nil, ErrOpenDOExists
		}
	}

	warehouseID := in.WarehouseID
	if warehouseID == 0 {
		if !so.WarehouseID.Valid {
			return nil, ErrWarehouseRequired
		}
		warehouseID = so.WarehouseID.Int64
	}

	doNumber, err := s.docCode(ctx, seqKeyDO, "DO", tx)
	if err != nil {
		return nil, err
	}
	requested := decimal.Zero
	for _, it := range in.Items {
		requested = requested.Add(it.RequestedQuantity)
	}
	o := &DeliveryOrder{
		DoNumber:          doNumber,
		SalesOrderID:      so.ID,
		CustomerID:        so.CustomerID,
		WarehouseID:       warehouseID,
		DoDate:            time.Now().In(s.loc).Format("2006-01-02"),
		Priority:          in.Priority,
		Status:            StatusDraft,
		RequestedQuantity: requested,
		ShippedQuantity:   decimal.Zero,
		DeliveredQuantity: decimal.Zero,
		RemainingQuantity: requested,
		Notes:             in.Notes,
		CreatedBy:         opPtr(operatorID),
	}
	row, err := s.store.CreateDeliveryOrder(ctx, tx, createDeliveryOrderParams(o))
	if err != nil {
		return nil, err
	}
	o.ID = row.ID
	o.CreatedAt = row.CreatedAt.Time
	o.UpdatedAt = row.UpdatedAt.Time

	sorted := append([]DOItemInput(nil), in.Items...)
	sort.Slice(sorted, func(i, j int) bool { return sorted[i].ProductID < sorted[j].ProductID })
	for _, it := range sorted {
		item := &DeliveryOrderItem{
			DeliveryOrderID:   o.ID,
			ProductID:         it.ProductID,
			RequestedQuantity: it.RequestedQuantity,
			ShippedQuantity:   decimal.Zero,
			DeliveredQuantity: decimal.Zero,
			RemainingQuantity: it.RequestedQuantity,
		}
		if _, err := s.store.CreateDeliveryOrderItem(ctx, tx, createDeliveryOrderItemParams(item)); err != nil {
			return nil, err
		}
		o.Items = append(o.Items, *item)
	}
	if err := tx.Commit(ctx); err != nil {
		return nil, err
	}
	return s.GetDeliveryOrder(ctx, o.ID)
}

func (s *Service) GetDeliveryOrder(ctx context.Context, id int64) (*DeliveryOrder, error) {
	row, err := s.store.DeliveryOrderByID(ctx, id)
	if err != nil {
		return nil, err
	}
	o := deliveryOrderFromRow(row)

	items, err := s.store.DeliveryOrderItems(ctx, id)
	if err != nil {
		return nil, err
	}
	for _, it := range items {
		o.Items = append(o.Items, itemFromRow(it))
	}

	shipments, err := s.store.ShipmentsByDeliveryOrder(ctx, id)
	if err != nil {
		return nil, err
	}
	for _, sr := range shipments {
		sh := shipmentFromListRow(sr)
		sitems, err := s.store.ShipmentItems(ctx, sh.ID)
		if err != nil {
			return nil, err
		}
		for _, si := range sitems {
			sh.Items = append(sh.Items, shipmentItemFromRow(si))
		}
		o.Shipments = append(o.Shipments, sh)
	}

	dispatches, err := s.store.DispatchesByDeliveryOrder(ctx, id)
	if err != nil {
		return nil, err
	}
	for _, d := range dispatches {
		o.Dispatches = append(o.Dispatches, dispatchFromRow(d))
	}
	s.hydrateSalesOrderStatus(ctx, o)
	return o, nil
}

// hydrateSalesOrderStatus fills SalesOrderStatus (best-effort: a vanished SO
// row must not fail the DO read — same policy as syncSalesOrderStatus).
func (s *Service) hydrateSalesOrderStatus(ctx context.Context, o *DeliveryOrder) {
	if o == nil || o.SalesOrderID == 0 {
		return
	}
	if so, err := s.store.SalesOrderForDelivery(ctx, o.SalesOrderID); err == nil {
		o.SalesOrderStatus = so.Status
	} else {
		logger.FromContext(ctx).Warn("delivery order sales order status hydration failed",
			"do_id", o.ID, "so_id", o.SalesOrderID, "err", err)
	}
}

func (s *Service) ListDeliveryOrders(ctx context.Context, f DOFilter) ([]DeliveryOrder, int, error) {
	rows, total, err := s.store.ListDeliveryOrders(ctx, f)
	if err != nil {
		return nil, 0, err
	}
	list := make([]DeliveryOrder, 0, len(rows))
	for _, r := range rows {
		o := deliveryOrderFromListRow(r)
		s.hydrateSalesOrderStatus(ctx, &o)
		list = append(list, o)
	}
	return list, total, nil
}

// --- shipments ---------------------------------------------------------------

// CreateShipment executes a (possibly partial) shipment against one DO. In one
// transaction it: validates over-shipment, deducts stock by the actual shipped
// quantity (releasing the reserved portion), records a movement, updates the
// DO/item aggregates and status history, and optionally creates the dispatch
// (§15.3 invariant 8). A shipment may not exceed the remaining quantity on any
// DO line.
// syncSalesOrderStatus mirrors the DO's fulfilment onto the sales order.
// Best-effort by design (a vanished SO row must not fail the delivery), but
// never silent (A6): failures log with both ids so a stuck SO is findable.
func (s *Service) syncSalesOrderStatus(ctx context.Context, tx db.DBTX, soID int64, status string, doID int64) {
	if soID == 0 {
		return
	}
	if err := s.store.SetSalesOrderStatus(ctx, tx, soID, status); err != nil {
		logger.FromContext(ctx).Warn("delivery order status sync to sales order failed",
			"do_id", doID, "so_id", soID, "status", status, "err", err)
	}
	if status == "delivered" || status == "completed" {
		_, _ = tx.Exec(ctx, `
			UPDATE sales_orders SET paid_amount = total_amount, remaining_amount = 0, updated_at = now()
			WHERE id = $1 AND (
				LOWER(COALESCE(notes, '')) ~ '\[metode:\s*(cash|cod|tunai)\]'
				OR LOWER(COALESCE(notes, '')) LIKE '%metode: cash%'
				OR LOWER(COALESCE(notes, '')) LIKE '%metode: cod%'
				OR LOWER(COALESCE(notes, '')) LIKE '%metode: tunai%'
				OR LOWER(COALESCE(notes, '')) LIKE '%cod%'
				OR LOWER(COALESCE(notes, '')) LIKE '%bayar tunai%'
				OR LOWER(COALESCE(notes, '')) LIKE '%bayar di tempat%'
			)`, soID)
		_, _ = tx.Exec(ctx, `
			UPDATE invoices SET status = 'paid', paid_amount = total_amount, remaining_amount = 0, updated_at = now()
			WHERE sales_order_id = $1 AND status != 'paid' AND status != 'cancelled'
			  AND sales_order_id IN (
				SELECT id FROM sales_orders WHERE id = $1 AND (
					LOWER(COALESCE(notes, '')) ~ '\[metode:\s*(cash|cod|tunai)\]'
					OR LOWER(COALESCE(notes, '')) LIKE '%metode: cash%'
					OR LOWER(COALESCE(notes, '')) LIKE '%metode: cod%'
					OR LOWER(COALESCE(notes, '')) LIKE '%metode: tunai%'
					OR LOWER(COALESCE(notes, '')) LIKE '%cod%'
					OR LOWER(COALESCE(notes, '')) LIKE '%bayar tunai%'
					OR LOWER(COALESCE(notes, '')) LIKE '%bayar di tempat%'
				)
			  )`, soID)
	}
}

func (s *Service) CreateShipment(ctx context.Context, doID int64, in ShipmentInput, operatorID int64) (*Shipment, error) {
	if len(in.Items) == 0 {
		return nil, ErrItemRequired
	}
	for _, it := range in.Items {
		if it.ProductID == 0 {
			return nil, ErrItemRequired
		}
		if it.ShippedQuantity.LessThanOrEqual(decimal.Zero) {
			return nil, ErrQuantityRequired
		}
	}
	shipDate := in.ShipDate
	if shipDate == "" {
		shipDate = time.Now().In(s.loc).Format("2006-01-02")
	}

	tx, err := s.store.Begin(ctx)
	if err != nil {
		return nil, err
	}
	defer tx.Rollback(ctx)

	do, err := s.store.DeliveryOrderByIDForUpdate(ctx, tx, doID)
	if err != nil {
		return nil, err
	}
	if do.Status == StatusCompleted || do.Status == StatusCancelled || do.Status == StatusReady {
		return nil, ErrCannotShipment
	}

	itemRows, err := s.store.DeliveryOrderItemsForUpdate(ctx, tx, doID)
	if err != nil {
		return nil, err
	}
	byProduct := map[int64]*db.DeliveryOrderItem{}
	for i := range itemRows {
		it := &itemRows[i]
		byProduct[it.ProductID] = it
	}

	type line struct {
		productID int64
		itemID    int64
		qty       decimal.Decimal
	}
	lines := make([]line, 0, len(in.Items))
	for _, it := range in.Items {
		dit, ok := byProduct[it.ProductID]
		if !ok {
			return nil, ErrItemNotOnDelivery
		}
		if it.ShippedQuantity.GreaterThan(dit.RemainingQuantity) {
			return nil, ErrOverShipment
		}
		lines = append(lines, line{productID: it.ProductID, itemID: dit.ID, qty: it.ShippedQuantity})
	}

	shipNumber, err := s.docCode(ctx, seqKeySHP, "SHP", tx)
	if err != nil {
		return nil, err
	}
	trackingNumber, err := s.docCode(ctx, seqKeyTRK, "TRK", tx)
	if err != nil {
		return nil, err
	}
	sh := &Shipment{
		ShipmentNumber:  shipNumber,
		TrackingNumber:  trackingNumber,
		Barcode:         "BAR-" + strings.TrimPrefix(shipNumber, "SHP-"),
		DeliveryOrderID: do.ID,
		Status:          ShipmentPending,
		ShipDate:        shipDate,
		Notes:           in.Notes,
		CreatedBy:       opPtr(operatorID),
	}
	row, err := s.store.CreateShipment(ctx, tx, createShipmentParams(sh))
	if err != nil {
		return nil, err
	}
	sh.ID = row.ID
	sh.CreatedAt = row.CreatedAt.Time
	sh.UpdatedAt = row.UpdatedAt.Time

	alreadyDeducted := false
	if so, err := s.store.SalesOrderForDelivery(ctx, do.SalesOrderID); err == nil {
		if so.OrderType == "ecommerce" || so.OrderType == "pos" {
			alreadyDeducted = true
		}
	}

	sort.Slice(lines, func(i, j int) bool { return lines[i].productID < lines[j].productID })
	totalShipped := decimal.Zero
	for _, ln := range lines {
		if !alreadyDeducted {
			before, after, err := s.deductShippedStock(ctx, tx, ln.productID, do.WarehouseID, ln.qty)
			if err != nil {
				return nil, err
			}
			if s.shipmentLedger != nil {
				if err := s.shipmentLedger(ctx, tx, ln.productID, do.WarehouseID, sh.ID, before, after, ln.qty); err != nil {
					return nil, err
				}
			}
		}
		si := &ShipmentItem{
			ShipmentID:          sh.ID,
			DeliveryOrderItemID: ln.itemID,
			ProductID:           ln.productID,
			ShippedQuantity:     ln.qty,
		}
		if _, err := s.store.CreateShipmentItem(ctx, tx, createShipmentItemParams(si)); err != nil {
			return nil, err
		}
		dit := byProduct[ln.productID]
		dit.ShippedQuantity = dit.ShippedQuantity.Add(ln.qty)
		dit.RemainingQuantity = dit.RemainingQuantity.Sub(ln.qty)
		if err := s.store.UpdateDeliveryOrderItemTotals(ctx, tx, dit.ID, dit.ShippedQuantity, dit.DeliveredQuantity, dit.RemainingQuantity); err != nil {
			return nil, err
		}
		totalShipped = totalShipped.Add(ln.qty)
	}

	newShipped := do.ShippedQuantity.Add(totalShipped)
	newRemaining := do.RemainingQuantity.Sub(totalShipped)
	// The state this shipment lands the DO in on its own: allocation done,
	// surat jalan issued, driver not yet assigned (sequential DO flow:
	// draft/partially_fulfilled → partially_fulfilled/ready → in_transit).
	allocStatus := StatusPartiallyFulfilled
	if newRemaining.IsZero() {
		allocStatus = StatusReady
	}
	toStatus := allocStatus
	// An allocation only moves the DO when it leaves a pre-transit state.
	// Follow-up shipments on an in_transit DO (second trip) never regress the
	// status, and a no-op re-allocation (partial → partial) writes no row.
	allocMoved := do.Status != StatusInTransit && allocStatus != do.Status

	// Audit the allocation transition itself ("shipment executed") when it
	// advances the DO, BEFORE any dispatch row: the audit trail must show
	// every sequential step (shipment executed → dispatch assigned) in real
	// order instead of a single draft→in_transit jump.
	if allocMoved {
		if _, err := s.store.CreateDeliveryStatusHistory(ctx, tx, createStatusHistoryParams(&DeliveryStatusHistory{
			DeliveryOrderID: do.ID,
			FromStatus:      do.Status,
			ToStatus:        allocStatus,
			Reason:          "shipment executed",
			ReferenceType:   strPtr("shipment"),
			ReferenceID:     int64Ptr(sh.ID),
			ChangedBy:       opPtr(operatorID),
		})); err != nil {
			return nil, err
		}
	}

	if in.Dispatch != nil {
		// The dispatch history row must start from the post-allocation state,
		// not from the pre-shipment status — allocation and dispatch are two
		// distinct transitions even when one request performs both.
		doView := deliveryOrderFromLockedRow(do)
		if allocMoved {
			doView.Status = allocStatus
		}
		if _, err := s.createDispatchLocked(ctx, tx, doView, sh, in.Dispatch, operatorID); err != nil {
			return nil, err
		}
		toStatus = StatusInTransit
	}

	// Never regress an already-dispatched DO: a follow-up partial shipment sent
	// without an inline dispatch would otherwise flip in_transit back to
	// partially_fulfilled/ready while the first shipment is still on the road
	// (and a DO flipped back to ready then rejects all further shipments with
	// ErrCannotShipment). in_transit is only ever left by delivery/cancel.
	if do.Status == StatusInTransit && toStatus != StatusInTransit {
		toStatus = StatusInTransit
	}

	if err := s.store.UpdateDeliveryOrderTotals(ctx, tx, do.ID, newShipped, do.DeliveredQuantity, newRemaining, toStatus); err != nil {
		return nil, err
	}
	if toStatus == StatusInTransit && do.SalesOrderID != 0 {
		s.syncSalesOrderStatus(ctx, tx, do.SalesOrderID, "shipped", do.ID)
	}
	if err := tx.Commit(ctx); err != nil {
		return nil, err
	}
	return s.shipmentWithItems(ctx, sh.ID)
}

// DeliverShipment marks a shipment delivered, rolls the delivered quantities
// into the DO/item aggregates and closes the DO when everything is delivered
// (§15.2). One transaction.
func (s *Service) DeliverShipment(ctx context.Context, shipmentID int64, operatorID int64) (*Shipment, error) {
	tx, err := s.store.Begin(ctx)
	if err != nil {
		return nil, err
	}
	defer tx.Rollback(ctx)

	sh, err := s.store.ShipmentByIDForUpdate(ctx, tx, shipmentID)
	if err != nil {
		return nil, err
	}
	if sh.Status == ShipmentDelivered || sh.Status == ShipmentCancelled {
		return nil, ErrShipmentClosed
	}
	do, err := s.store.DeliveryOrderByIDForUpdate(ctx, tx, sh.DeliveryOrderID)
	if err != nil {
		return nil, err
	}
	if do.Status == StatusCompleted || do.Status == StatusCancelled {
		return nil, ErrCannotShipment
	}

	sitems, err := s.store.ShipmentItemsForUpdate(ctx, tx, shipmentID)
	if err != nil {
		return nil, err
	}
	doItems, err := s.store.DeliveryOrderItemsForUpdate(ctx, tx, do.ID)
	if err != nil {
		return nil, err
	}
	doByID := map[int64]*db.DeliveryOrderItem{}
	for i := range doItems {
		doByID[doItems[i].ID] = &doItems[i]
	}

	totalDelivered := decimal.Zero
	for i := range sitems {
		si := &sitems[i]
		if si.DeliveredQuantity.Equal(si.ShippedQuantity) {
			continue
		}
		if err := s.store.UpdateShipmentItemDelivered(ctx, tx, si.ID, si.ShippedQuantity); err != nil {
			return nil, err
		}
		dit := doByID[si.DeliveryOrderItemID]
		if dit == nil {
			return nil, ErrNotFound
		}
		dit.DeliveredQuantity = dit.DeliveredQuantity.Add(si.ShippedQuantity)
		if err := s.store.UpdateDeliveryOrderItemTotals(ctx, tx, dit.ID, dit.ShippedQuantity, dit.DeliveredQuantity, dit.RemainingQuantity); err != nil {
			return nil, err
		}
		totalDelivered = totalDelivered.Add(si.ShippedQuantity)
	}
	if err := s.store.SetShipmentStatus(ctx, tx, shipmentID, ShipmentDelivered); err != nil {
		return nil, err
	}

	newDelivered := do.DeliveredQuantity.Add(totalDelivered)
	allDelivered := true
	for _, dit := range doItems {
		if !dit.DeliveredQuantity.Equal(dit.ShippedQuantity) {
			allDelivered = false
			break
		}
	}
	toStatus := StatusDelivered
	if do.RemainingQuantity.IsZero() && allDelivered {
		toStatus = StatusCompleted
	}
	if err := s.store.UpdateDeliveryOrderTotals(ctx, tx, do.ID, do.ShippedQuantity, newDelivered, do.RemainingQuantity, toStatus); err != nil {
		return nil, err
	}
	if toStatus != do.Status {
		if _, err := s.store.CreateDeliveryStatusHistory(ctx, tx, createStatusHistoryParams(&DeliveryStatusHistory{
			DeliveryOrderID: do.ID,
			FromStatus:      do.Status,
			ToStatus:        toStatus,
			Reason:          "shipment delivered",
			ReferenceType:   strPtr("shipment"),
			ReferenceID:     int64Ptr(sh.ID),
			ChangedBy:       opPtr(operatorID),
		})); err != nil {
			return nil, err
		}
	}
	if do.SalesOrderID != 0 && (toStatus == StatusCompleted || (do.RemainingQuantity.IsZero() && allDelivered)) {
		s.syncSalesOrderStatus(ctx, tx, do.SalesOrderID, "delivered", do.ID)
	}
	if err := tx.Commit(ctx); err != nil {
		return nil, err
	}
	return s.shipmentWithItems(ctx, shipmentID)
}

// DeliverDeliveryOrder marks all shipments of a DO as delivered, sets all
// remaining items as delivered, updates the DO to completed, and syncs
// the linked Sales Order to delivered. This allows warehouse admin to
// complete orders directly if a customer does not confirm receipt.
// DeliverDeliveryOrder completes a delivery order: every non-cancelled
// shipment is marked delivered, DO totals are finalised, the linked sales
// order is synced, and an OPTIONAL proof-of-delivery photo + recipient name
// are recorded (migration 000143).
//
// The photo rides the same request as the completion action (multipart `file`
// + `recipient_name`), because the operator takes it at the handover — a
// separate upload step would never happen in the yard. Evidence only: a DO
// completes without one, and the URL must be an /uploads/ path this backend
// served (same rule as the buyer-side received proof).
func (s *Service) DeliverDeliveryOrder(ctx context.Context, doID int64, operatorID int64, podPhoto *multipart.FileHeader, podRecipient string) (*DeliveryOrder, error) {
	tx, err := s.store.Begin(ctx)
	if err != nil {
		return nil, err
	}
	defer tx.Rollback(ctx)

	do, err := s.store.DeliveryOrderByIDForUpdate(ctx, tx, doID)
	if err != nil {
		return nil, err
	}
	if do.Status == StatusCompleted {
		return s.GetDeliveryOrder(ctx, doID)
	}
	if do.Status == StatusCancelled {
		return nil, ErrCannotShipment
	}

	shipments, err := s.store.ShipmentsByDeliveryOrder(ctx, doID)
	if err != nil {
		return nil, err
	}
	// Total actually shipped across every non-cancelled shipment. A delivery
	// order with nothing shipped — no shipments at all, or all of them
	// cancelled — must not complete: below, the DO totals are written as
	// delivered = requested, so completing here would record a delivery that
	// never happened and flip the sales order to delivered with it.
	shippedTotal := decimal.Zero
	for _, sh := range shipments {
		if sh.Status == ShipmentCancelled {
			continue
		}
		if sh.Status != ShipmentDelivered {
			sitems, err := s.store.ShipmentItemsForUpdate(ctx, tx, sh.ID)
			if err != nil {
				return nil, err
			}
			for _, si := range sitems {
				if !si.DeliveredQuantity.Equal(si.ShippedQuantity) {
					if err := s.store.UpdateShipmentItemDelivered(ctx, tx, si.ID, si.ShippedQuantity); err != nil {
						return nil, err
					}
				}
				shippedTotal = shippedTotal.Add(si.ShippedQuantity)
			}
			if err := s.store.SetShipmentStatus(ctx, tx, sh.ID, ShipmentDelivered); err != nil {
				return nil, err
			}
		} else {
			sitems, err := s.store.ShipmentItemsForUpdate(ctx, tx, sh.ID)
			if err != nil {
				return nil, err
			}
			for _, si := range sitems {
				shippedTotal = shippedTotal.Add(si.ShippedQuantity)
			}
		}
	}
	if shippedTotal.IsZero() {
		return nil, ErrNothingShipped
	}

	doItems, err := s.store.DeliveryOrderItemsForUpdate(ctx, tx, do.ID)
	if err != nil {
		return nil, err
	}
	for i := range doItems {
		dit := &doItems[i]
		shipped := dit.ShippedQuantity
		if shipped.LessThan(dit.RequestedQuantity) {
			shipped = dit.RequestedQuantity
		}
		delivered := shipped
		remaining := decimal.Zero
		if err := s.store.UpdateDeliveryOrderItemTotals(ctx, tx, dit.ID, shipped, delivered, remaining); err != nil {
			return nil, err
		}
	}

	totQty := do.RequestedQuantity
	if err := s.store.UpdateDeliveryOrderTotals(ctx, tx, do.ID, totQty, totQty, decimal.Zero, StatusCompleted); err != nil {
		return nil, err
	}

	if _, err := s.store.CreateDeliveryStatusHistory(ctx, tx, createStatusHistoryParams(&DeliveryStatusHistory{
		DeliveryOrderID: do.ID,
		FromStatus:      do.Status,
		ToStatus:        StatusCompleted,
		Reason:          "pesanan diselesaikan oleh admin warehouse",
		ReferenceType:   strPtr("delivery_order"),
		ReferenceID:     int64Ptr(do.ID),
		ChangedBy:       opPtr(operatorID),
	})); err != nil {
		return nil, err
	}

	if do.SalesOrderID != 0 {
		s.syncSalesOrderStatus(ctx, tx, do.SalesOrderID, "delivered", do.ID)
	}

	// Record the POD photo + recipient before commit: the handover evidence
	// belongs in the same transaction as the completion it documents. The
	// method lives on the Service (pod.go) because it needs the wired photo
	// saver before touching the row. The row write rides the SAME tx (q) so
	// evidence and status commit atomically.
	if err := s.SetDeliveryOrderPOD(ctx, tx, do.ID, podPhoto, strings.TrimSpace(podRecipient)); err != nil {
		return nil, err
	}

	if err := tx.Commit(ctx); err != nil {
		return nil, err
	}
	return s.GetDeliveryOrder(ctx, doID)
}

// --- dispatch ----------------------------------------------------------------

// CreateDispatch assigns (or reassigns) a fleet/priority/queue for a DO's
// shipment — the dispatch board. Every assignment writes an audited status
// history row (§15.4). Reassignment is simply another dispatch row.
func (s *Service) CreateDispatch(ctx context.Context, doID int64, in DispatchInput, operatorID int64) (*Dispatch, error) {
	if in.FleetID == 0 {
		return nil, ErrDispatchFleetRequired
	}
	tx, err := s.store.Begin(ctx)
	if err != nil {
		return nil, err
	}
	defer tx.Rollback(ctx)

	do, err := s.store.DeliveryOrderByIDForUpdate(ctx, tx, doID)
	if err != nil {
		return nil, err
	}
	doView := deliveryOrderFromLockedRow(do)
	sh, err := s.store.ShipmentByIDForUpdate(ctx, tx, in.ShipmentID)
	if err != nil {
		return nil, err
	}
	if sh.DeliveryOrderID != do.ID {
		return nil, ErrShipmentOnDORequired
	}
	if sh.Status == ShipmentDelivered || sh.Status == ShipmentCancelled {
		return nil, ErrShipmentClosed
	}

	d, err := s.createDispatchLocked(ctx, tx, doView, shipmentFromLockedRow(sh), &DispatchInfo{
		FleetID:      in.FleetID,
		DriverID:     in.DriverID,
		Priority:     in.Priority,
		QueueOrder:   in.QueueOrder,
		ScheduledFor: in.ScheduledFor,
	}, operatorID)
	if err != nil {
		return nil, err
	}
	if do.Status != StatusCompleted {
		if err := s.store.SetDeliveryOrderStatus(ctx, tx, do.ID, StatusInTransit); err != nil {
			return nil, err
		}
		if do.SalesOrderID != 0 {
			s.syncSalesOrderStatus(ctx, tx, do.SalesOrderID, "shipped", do.ID)
		}
	}
	if err := tx.Commit(ctx); err != nil {
		return nil, err
	}
	return d, nil
}

// createDispatchLocked runs inside the caller's tx (DO already FOR UPDATE
// locked). Writes the dispatch row + the audited status history.
func (s *Service) createDispatchLocked(ctx context.Context, tx Tx, do *DeliveryOrder, sh *Shipment, in *DispatchInfo, operatorID int64) (*Dispatch, error) {
	// Terminal DO states are refused HERE, not at the individual callers, so
	// both entry points are covered by the one check: CreateDispatch, and the
	// inline Dispatch that CreateShipment accepts in its input.
	//
	// CreateDispatch used to check only the shipment's status. The DO's own
	// status went unchecked on this path, so a cancelled DO was written back to
	// in_transit — resurrected, with the audit row recording the resurrection —
	// and a completed DO got a new dispatch row, because the
	// `if do.Status != StatusCompleted` further down only guarded the status
	// write and let the dispatch itself through. Both then reached the LHPP
	// grouper below and put a dead delivery order on a driver's billing sheet.
	//
	// Cancelled and completed only. `ready` is deliberately NOT refused: a
	// ready DO is fully shipped and waiting for a truck, which is exactly the
	// state CreateDispatch exists to move. Same for in_transit, which is
	// reassigned routinely and appends the next queue order.
	if do.Status == StatusCancelled || do.Status == StatusCompleted {
		return nil, ErrCannotDispatch
	}
	if in.FleetID == 0 {
		return nil, ErrDispatchFleetRequired
	}
	prio := in.Priority
	if prio == "" {
		prio = do.Priority
	}
	if !isValidPriority(prio) {
		return nil, ErrPriorityInvalid
	}
	queue := in.QueueOrder
	if queue == 0 {
		if latest, err := s.store.LatestDispatch(ctx, tx, do.ID); err == nil {
			queue = latest.QueueOrder + 1
		} else if !errors.Is(err, ErrNotFound) {
			return nil, err
		} else {
			queue = 1
		}
	}
	now := time.Now().In(s.loc)
	d := &Dispatch{
		DeliveryOrderID: do.ID,
		ShipmentID:      sh.ID,
		FleetID:         in.FleetID,
		Priority:        prio,
		QueueOrder:      queue,
		DriverID:        in.DriverID,
		ScheduledFor:    optionalString(in.ScheduledFor),
		Status:          DispatchDispatched,
		DispatchedAt:    strPtr(now.UTC().Format(time.RFC3339)),
		DispatcherID:    opPtr(operatorID),
		CreatedAt:       now,
		UpdatedAt:       now,
	}
	row, err := s.store.CreateDispatch(ctx, tx, createDispatchParams(d))
	if err != nil {
		return nil, err
	}
	d.ID = row.ID
	d.CreatedAt = row.CreatedAt.Time
	d.UpdatedAt = row.UpdatedAt.Time

	if _, err := s.store.CreateDeliveryStatusHistory(ctx, tx, createStatusHistoryParams(&DeliveryStatusHistory{
		DeliveryOrderID: do.ID,
		FromStatus:      do.Status,
		ToStatus:        StatusInTransit,
		Reason:          "dispatch assigned",
		ReferenceType:   strPtr("dispatch"),
		ReferenceID:     int64Ptr(d.ID),
		ChangedBy:       opPtr(operatorID),
	})); err != nil {
		return nil, err
	}
	if err := s.store.SetShipmentStatus(ctx, tx, sh.ID, ShipmentInTransit); err != nil {
		return nil, err
	}
	// The dispatch row has landed, so the DO now has a driver and can be put on
	// a billing sheet. Same transaction, and the delivery_orders row is already
	// locked FOR UPDATE by our caller — which is exactly the row a concurrent
	// LHPP merge locks, so the two contend instead of interleaving.
	//
	// An error here (notably ErrDOAlreadyBatched) aborts the whole dispatch:
	// the dispatch row rolls back with it. That is correct — a DO whose LHPP
	// sheet is mid-collection must not end up on a new driver's truck while
	// still sitting on the old driver's sheet.
	if s.lhppGrouper != nil && d.DriverID != nil && *d.DriverID > 0 {
		if err := s.lhppGrouper(ctx, tx, *d.DriverID, do.ID, do.DoDate); err != nil {
			return nil, err
		}
	}
	return d, nil
}

// --- fleet -------------------------------------------------------------------

func (s *Service) CreateVehicle(ctx context.Context, in VehicleInput) (*Vehicle, error) {
	if strings.TrimSpace(in.PlateNumber) == "" {
		return nil, ErrPlateRequired
	}
	if strings.TrimSpace(in.VehicleName) == "" {
		return nil, ErrVehicleNameRequired
	}
	isActive := true
	if in.IsActive != nil {
		isActive = *in.IsActive
	}
	v := &Vehicle{
		PlateNumber:    in.PlateNumber,
		VehicleName:    in.VehicleName,
		VehicleType:    in.VehicleType,
		CapacityWeight: in.CapacityWeight,
		CapacityVolume: in.CapacityVolume,
		IsActive:       isActive,
	}
	tx, err := s.store.Begin(ctx)
	if err != nil {
		return nil, err
	}
	defer tx.Rollback(ctx)
	row, err := s.store.CreateFleet(ctx, tx, createFleetParams(v))
	if err != nil {
		return nil, err
	}
	v.ID = row.ID
	v.CreatedAt = row.CreatedAt.Time
	v.UpdatedAt = row.UpdatedAt.Time
	if err := tx.Commit(ctx); err != nil {
		return nil, err
	}
	return v, nil
}

func (s *Service) GetVehicle(ctx context.Context, id int64) (*Vehicle, error) {
	row, err := s.store.FleetByID(ctx, id)
	if err != nil {
		return nil, err
	}
	v := vehicleFromRow(row)
	return &v, nil
}

func (s *Service) ListFleet(ctx context.Context, f FleetFilter) ([]Vehicle, int, error) {
	rows, total, err := s.store.ListFleet(ctx, f)
	if err != nil {
		return nil, 0, err
	}
	list := make([]Vehicle, 0, len(rows))
	for _, r := range rows {
		list = append(list, vehicleFromRow(r))
	}
	return list, total, nil
}

// ListDrivers returns every user tagged application_access='driver' — the
// dispatch driver candidates. See CreateDriver for how that tag is set.
func (s *Service) ListDrivers(ctx context.Context) ([]DriverOption, error) {
	rows, err := s.store.ListActiveUsersForDriverPicker(ctx)
	if err != nil {
		return nil, err
	}
	list := make([]DriverOption, 0, len(rows))
	for _, r := range rows {
		list = append(list, DriverOption{ID: r.ID, Name: r.Name})
	}
	return list, nil
}

// CreateDriver adds a driver candidate for the dispatch picker. Drivers never
// log into the ERP, so this is deliberately name-only: email and password are
// generated and never surfaced, role is fixed to 'employee' (lowest-privilege
// role in this schema), and application_access='driver' tags it for
// ListDrivers/dispatches.driver_id — no new role, permission or table.
func (s *Service) CreateDriver(ctx context.Context, name string) (*DriverOption, error) {
	name = strings.TrimSpace(name)
	if name == "" {
		return nil, ErrDriverNameRequired
	}
	pw := make([]byte, 24)
	if _, err := rand.Read(pw); err != nil {
		return nil, err
	}
	hash, err := bcrypt.GenerateFromPassword(pw, bcrypt.DefaultCost)
	if err != nil {
		return nil, err
	}
	email := fmt.Sprintf("driver.%d@drivers.internal", time.Now().UnixNano())
	row, err := s.store.CreateDriverUser(ctx, db.CreateDriverUserParams{
		Name:     name,
		Email:    email,
		Password: string(hash),
	})
	if err != nil {
		return nil, err
	}
	return &DriverOption{ID: row.ID, Name: row.Name}, nil
}

// --- tracking ----------------------------------------------------------------

// RecordTrackingEvent appends one shipment tracking event from a manual update
// or third-party push (§16). provider_ref dedupes third-party pushes: a repeat
// push for the same (shipment, provider_ref) returns the existing event without
// writing. The route is also protected by idem.Handler.
func (s *Service) RecordTrackingEvent(ctx context.Context, shipmentID int64, in TrackingEventInput, operatorID int64) (*TrackingEvent, error) {
	// RBAC audit 2026-08-25: dispatched driver OR tracking-record permission.
	if u := auth.UserFrom(ctx); u != nil {
		if !s.canPostTrackingEvent(ctx, u, shipmentID) {
			return nil, ErrNotFound
		}
	}
	if !isValidTrackingStatus(in.Status) {
		return nil, ErrTrackingStatusInvalid
	}
	if !isValidTrackingSource(in.Source) {
		return nil, ErrTrackingSourceInvalid
	}
	if err := validateCoordinates(in.Latitude, in.Longitude, in.AccuracyM); err != nil {
		return nil, err
	}
	eventTime := time.Now().In(s.loc).UTC()
	if in.EventTime != "" {
		t, err := time.Parse(time.RFC3339, in.EventTime)
		if err != nil {
			return nil, ErrTrackingEventTime
		}
		eventTime = t
	}
	if eventTime.After(time.Now().Add(5 * time.Minute)) {
		return nil, ErrTrackingEventTime
	}

	if in.ProviderRef != nil && *in.ProviderRef != "" {
		if existing, err := s.store.ShipmentTrackingEventByProviderRef(ctx, shipmentID, *in.ProviderRef); err == nil {
			ev := existing
			return &ev, nil
		} else if !errors.Is(err, ErrNotFound) {
			return nil, err
		}
	}

	if _, err := s.store.ShipmentByID(ctx, shipmentID); err != nil {
		return nil, err
	}
	e := &TrackingEvent{
		ShipmentID:  shipmentID,
		EventTime:   eventTime,
		Status:      in.Status,
		Source:      in.Source,
		Latitude:    in.Latitude,
		Longitude:   in.Longitude,
		AccuracyM:   in.AccuracyM,
		Note:        in.Note,
		ProviderRef: in.ProviderRef,
		CreatedBy:   opPtr(operatorID),
	}
	row, err := s.store.CreateShipmentTrackingEvent(ctx, createTrackingEventParams(e))
	if err != nil {
		var pgErr *pgconn.PgError
		if errors.As(err, &pgErr) && pgErr.Code == "23505" && in.ProviderRef != nil {
			if existing, exErr := s.store.ShipmentTrackingEventByProviderRef(ctx, shipmentID, *in.ProviderRef); exErr == nil {
				ev := existing
				return &ev, nil
			}
		}
		return nil, err
	}
	e.ID = row.ID
	e.CreatedAt = row.CreatedAt.Time
	return e, nil
}

// GetShipmentTracking builds the map-ready payload: shipment, latest point
// (live provider point when one is active, else the most recent recorded
// event), full event history, and real-time availability. When no provider is
// active, Latest.IsLive is always false (§16).
func (s *Service) GetShipmentTracking(ctx context.Context, shipmentID int64) (*TrackingSummary, error) {
	sh, err := s.store.ShipmentByID(ctx, shipmentID)
	if err != nil {
		return nil, err
	}
	history, err := s.store.ShipmentTrackingEvents(ctx, shipmentID)
	if err != nil {
		return nil, err
	}

	sum := &TrackingSummary{
		Shipment: *shipmentFromRow(sh),
		History:  history,
		RealTime: RealTimeInfo{Available: false},
	}
	if s.locationProvider != nil && s.locationProvider.Active() {
		if live, err := s.locationProvider.Latest(ctx, shipmentID); err == nil && live != nil {
			sum.RealTime.Available = true
			name := s.locationProvider.Name()
			sum.RealTime.Provider = &name
			sum.Latest = &TrackingPoint{
				Latitude:  live.Latitude,
				Longitude: live.Longitude,
				AccuracyM: live.AccuracyM,
				EventTime: live.ObservedAt,
				Source:    live.Source,
				IsLive:    true,
			}
			return sum, nil
		}
	}
	if len(history) > 0 {
		last := history[0]
		if last.Latitude != nil && last.Longitude != nil {
			sum.Latest = &TrackingPoint{
				Latitude:  *last.Latitude,
				Longitude: *last.Longitude,
				AccuracyM: float64OrZero(last.AccuracyM),
				EventTime: last.EventTime,
				Source:    last.Source,
				IsLive:    false,
			}
		}
	}
	return sum, nil
}

// LookupShipment resolves a DO number, shipment number, barcode, or tracking
// number to shipment tracking summaries. Identifier families are prefix-
// partitioned (DO- / SHP- / TRK- / BAR-) so resolution is unambiguous; a value
// without a known prefix is treated as a raw barcode scan (§16).
func (s *Service) LookupShipment(ctx context.Context, q string) (*LookupResult, error) {
	q = strings.TrimSpace(q)
	if q == "" {
		return nil, ErrIdentifierRequired
	}
	res := &LookupResult{Query: q}
	var shipments []*Shipment
	switch {
	case doNumberRE.MatchString(q):
		res.MatchedBy = "do_number"
		rows, err := s.store.ShipmentsByDeliveryOrderNumber(ctx, q)
		if err != nil {
			return nil, err
		}
		for i := range rows {
			shipments = append(shipments, shipmentFromDONumberRow(rows[i]))
		}
	case shipmentNumberRE.MatchString(q):
		res.MatchedBy = "shipment_number"
		sh, err := s.store.ShipmentByShipmentNumber(ctx, q)
		if err != nil {
			return nil, err
		}
		shipments = append(shipments, shipmentFromNumberRow(sh))
	case trackingNumberRE.MatchString(q):
		res.MatchedBy = "tracking_number"
		sh, err := s.store.ShipmentByTrackingNumber(ctx, q)
		if err != nil {
			return nil, err
		}
		shipments = append(shipments, shipmentFromTrackingRow(sh))
	default:
		res.MatchedBy = "barcode"
		sh, err := s.store.ShipmentByBarcode(ctx, q)
		if err != nil {
			return nil, err
		}
		shipments = append(shipments, shipmentFromBarcodeRow(sh))
	}
	// Customer isolation (RBAC audit 2026-08-25): buyer accounts only ever
	// see shipments bound to their own customer.
	if u := auth.UserFrom(ctx); u != nil && u.Role == auth.RoleBuyer {
		custID, cerr := s.store.CustomerByUserID(ctx, u.ID)
		if cerr != nil {
			return nil, ErrNotFound
		}
		owned := shipments[:0]
		for _, sh := range shipments {
			cid, cidErr := s.store.ShipmentCustomerID(ctx, sh.ID)
			if cidErr == nil && cid == custID {
				owned = append(owned, sh)
			}
		}
		shipments = owned
	}
	if len(shipments) == 0 {
		return nil, ErrNotFound
	}
	for _, sh := range shipments {
		sum, err := s.GetShipmentTracking(ctx, sh.ID)
		if err != nil {
			return nil, err
		}
		res.Shipments = append(res.Shipments, *sum)
	}
	return res, nil
}

// --- stock -------------------------------------------------------------------

func (s *Service) lockStock(ctx context.Context, q db.DBTX, productID, warehouseID int64) (db.GetSalesStockRowForUpdateRow, error) {
	row, err := s.store.LockStockForUpdate(ctx, q, productID, warehouseID)
	if errors.Is(err, ErrNotFound) {
		if err := s.store.EnsureStockRow(ctx, q, productID, warehouseID); err != nil {
			return db.GetSalesStockRowForUpdateRow{}, err
		}
		row, err = s.store.LockStockForUpdate(ctx, q, productID, warehouseID)
		if err != nil {
			return db.GetSalesStockRowForUpdateRow{}, err
		}
	} else if err != nil {
		return db.GetSalesStockRowForUpdateRow{}, err
	}
	return row, nil
}

// deductShippedStock locks the row and deducts qty from on-hand quantity,
// releasing the same amount from the reserved quantity (the shipment fulfills
// the reservation). Returns the exact before/after so the movement record is
// concurrency-exact. The check uses physical quantity, because the reserved
// goods are physically present and earmarked for this SO.
func (s *Service) deductShippedStock(ctx context.Context, q db.DBTX, productID, warehouseID int64, qty decimal.Decimal) (before, after decimal.Decimal, err error) {
	row, err := s.lockStock(ctx, q, productID, warehouseID)
	if err != nil {
		return decimal.Zero, decimal.Zero, err
	}
	if !s.allowNegativeStock && row.Quantity.LessThan(qty) {
		return decimal.Zero, decimal.Zero, ErrInsufficientStock
	}
	before = row.Quantity
	after = before.Sub(qty)
	reserved := row.ReservedQuantity.Sub(qty)
	if reserved.IsNegative() {
		reserved = decimal.Zero
	}
	if err := s.store.SetStockQuantity(ctx, q, row.ID, after, reserved); err != nil {
		return decimal.Zero, decimal.Zero, err
	}
	return before, after, nil
}

// --- codes -------------------------------------------------------------------

func (s *Service) doCode(ctx context.Context, tx Tx) (string, error) {
	return s.docCode(ctx, seqKeyDO, "DO", tx)
}

func (s *Service) shipCode(ctx context.Context, tx Tx) (string, error) {
	return s.docCode(ctx, seqKeySHP, "SHP", tx)
}

// docCode allocates a monthly transaction number (DO-YYYYMM-000001 /
// SHP-YYYYMM-000001, business-rules §17) scoped to the calendar month in
// Asia/Jakarta. Sequence errors abort the operation.
func (s *Service) docCode(ctx context.Context, seqKey, display string, tx any) (string, error) {
	n, err := s.seq.Sequence(ctx, seqKey, seq.MonthStart(time.Now().In(s.loc)), tx)
	if err != nil {
		return "", fmt.Errorf("%s number sequence: %w", display, err)
	}
	return fmt.Sprintf("%s-%s-%06d", display, time.Now().In(s.loc).Format("200601"), n), nil
}

// --- helpers -----------------------------------------------------------------

func (s *Service) shipmentWithItems(ctx context.Context, id int64) (*Shipment, error) {
	row, err := s.store.ShipmentByID(ctx, id)
	if err != nil {
		return nil, err
	}
	sh := shipmentFromRow(row)
	items, err := s.store.ShipmentItems(ctx, id)
	if err != nil {
		return nil, err
	}
	for _, it := range items {
		sh.Items = append(sh.Items, shipmentItemFromRow(it))
	}
	return sh, nil
}

func isValidPriority(p string) bool {
	switch p {
	case PriorityUrgent, PriorityHigh, PriorityNormal, PriorityLow:
		return true
	}
	return false
}

func isValidTrackingStatus(s string) bool {
	switch s {
	case ShipmentPending, ShipmentInTransit, ShipmentDelivered, ShipmentFailed, ShipmentCancelled:
		return true
	}
	return false
}

func isValidTrackingSource(s string) bool {
	switch s {
	case TrackingSourceDriverMobile, TrackingSourceThirdParty, TrackingSourceManual:
		return true
	}
	return false
}

func validateCoordinates(lat, lng, accuracy *float64) error {
	if (lat == nil) != (lng == nil) {
		return ErrTrackingCoordsInvalid
	}
	if lat != nil && (*lat < -90 || *lat > 90 || *lng < -180 || *lng > 180) {
		return ErrTrackingCoordsInvalid
	}
	if accuracy != nil && *accuracy < 0 {
		return ErrTrackingAccuracy
	}
	return nil
}

func float64OrZero(v *float64) float64 {
	if v == nil {
		return 0
	}
	return *v
}

var (
	doNumberRE       = regexp.MustCompile(`^DO-\d{6}-\d{6}$`)
	shipmentNumberRE = regexp.MustCompile(`^SHP-\d{6}-\d{6}$`)
	trackingNumberRE = regexp.MustCompile(`^TRK-\d{6}-\d{6}$`)
)

func opPtr(id int64) *int64 {
	if id <= 0 {
		return nil
	}
	return &id
}

func int64Ptr(v int64) *int64 { return &v }

func strPtr(s string) *string { return &s }

func optionalString(s string) *string {
	if s == "" {
		return nil
	}
	return &s
}