package distribution

import (
	"bytes"
	"context"
	"errors"
	"image"
	"image/png"
	"mime/multipart"
	"strings"
	"testing"
	"time"

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

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

// --- tx/row fakes (mirror sales) --------------------------------------------

type fakeTx struct {
	store     *fakeStore
	snapshot  *fakeStore
	committed bool
}

func (f *fakeTx) QueryRow(ctx context.Context, sql string, args ...any) pgx.Row {
	return &fakeRow{err: pgx.ErrNoRows}
}
func (f *fakeTx) Query(ctx context.Context, sql string, args ...any) (pgx.Rows, error) {
	return nil, nil
}
func (f *fakeTx) Exec(ctx context.Context, sql string, args ...any) (pgconn.CommandTag, error) {
	return pgconn.CommandTag{}, nil
}
func (f *fakeTx) Commit(context.Context) error { f.committed = true; return nil }
func (f *fakeTx) Rollback(context.Context) error {
	if !f.committed {
		f.store.restore(f.snapshot)
	}
	return nil
}

type fakeRow struct {
	vals []any
	err  error
}

func (f *fakeRow) Scan(dest ...any) error {
	if f.err != nil {
		return f.err
	}
	for i, d := range dest {
		if p, ok := d.(*any); ok {
			*p = f.vals[i]
		}
	}
	return nil
}

func ts() pgtype.Timestamptz { return pgtype.Timestamptz{Time: time.Now(), Valid: true} }

// --- fake store --------------------------------------------------------------

type fakeStore struct {
	sos            map[int64]db.GetSalesOrderForDeliveryRow
	soItems        map[int64][]db.ListSalesOrderItemsRow
	dos            map[int64]*db.GetDeliveryOrderByIDRow
	doItems        map[int64][]db.DeliveryOrderItem
	shipments      map[int64]*db.GetShipmentByIDRow
	customerByUser map[int64]int64
	shipmentItems  map[int64][]db.ShipmentItem
	dispatches     map[int64]*db.Dispatch
	history        map[int64][]db.DeliveryStatusHistory
	fleet          map[int64]*db.Fleet
	stocks         map[string]*db.GetSalesStockRowForUpdateRow
	trackingEvents map[int64][]TrackingEvent
	shipLedger     []ledgerCall
	nextID         int64
	failOnUpdate   bool
	// failSyncSO makes SetSalesOrderStatus fail, so tests can assert the
	// delivery still completes (sync is best-effort) instead of failing.
	failSyncSO    bool
	negativeStock bool
}

type ledgerCall struct {
	productID, warehouseID, shipmentID int64
	before, after, qty                 decimal.Decimal
}

func newFakeStore() *fakeStore {
	return &fakeStore{
		sos:            map[int64]db.GetSalesOrderForDeliveryRow{},
		soItems:        map[int64][]db.ListSalesOrderItemsRow{},
		dos:            map[int64]*db.GetDeliveryOrderByIDRow{},
		doItems:        map[int64][]db.DeliveryOrderItem{},
		shipments:      map[int64]*db.GetShipmentByIDRow{},
		shipmentItems:  map[int64][]db.ShipmentItem{},
		dispatches:     map[int64]*db.Dispatch{},
		history:        map[int64][]db.DeliveryStatusHistory{},
		fleet:          map[int64]*db.Fleet{},
		stocks:         map[string]*db.GetSalesStockRowForUpdateRow{},
		trackingEvents: map[int64][]TrackingEvent{},
		nextID:         1,
	}
}

func (f *fakeStore) Begin(context.Context) (Tx, error) {
	return &fakeTx{store: f, snapshot: f.snapshot()}, nil
}

// snapshot deep-copies the mutable maps so Rollback can restore pre-tx state.
func (f *fakeStore) snapshot() *fakeStore {
	s := &fakeStore{
		sos:            map[int64]db.GetSalesOrderForDeliveryRow{},
		soItems:        map[int64][]db.ListSalesOrderItemsRow{},
		dos:            map[int64]*db.GetDeliveryOrderByIDRow{},
		doItems:        map[int64][]db.DeliveryOrderItem{},
		shipments:      map[int64]*db.GetShipmentByIDRow{},
		shipmentItems:  map[int64][]db.ShipmentItem{},
		dispatches:     map[int64]*db.Dispatch{},
		history:        map[int64][]db.DeliveryStatusHistory{},
		fleet:          map[int64]*db.Fleet{},
		stocks:         map[string]*db.GetSalesStockRowForUpdateRow{},
		trackingEvents: map[int64][]TrackingEvent{},
		nextID:         f.nextID,
	}
	for k, v := range f.sos {
		s.sos[k] = v
	}
	for k, v := range f.soItems {
		s.soItems[k] = append([]db.ListSalesOrderItemsRow(nil), v...)
	}
	for k, v := range f.dos {
		c := *v
		s.dos[k] = &c
	}
	for k, v := range f.doItems {
		s.doItems[k] = append([]db.DeliveryOrderItem(nil), v...)
	}
	for k, v := range f.shipments {
		c := *v
		s.shipments[k] = &c
	}
	for k, v := range f.shipmentItems {
		s.shipmentItems[k] = append([]db.ShipmentItem(nil), v...)
	}
	for k, v := range f.dispatches {
		c := *v
		s.dispatches[k] = &c
	}
	for k, v := range f.history {
		s.history[k] = append([]db.DeliveryStatusHistory(nil), v...)
	}
	for k, v := range f.fleet {
		c := *v
		s.fleet[k] = &c
	}
	for k, v := range f.stocks {
		c := *v
		s.stocks[k] = &c
	}
	for k, v := range f.trackingEvents {
		s.trackingEvents[k] = append([]TrackingEvent(nil), v...)
	}
	return s
}

func (f *fakeStore) restore(s *fakeStore) {
	*f = *s
}

func stockKey(productID, warehouseID int64) string {
	return itoa(productID) + ":" + itoa(warehouseID)
}

func itoa(v int64) string {
	b := [20]byte{}
	i := len(b)
	for {
		i--
		b[i] = byte('0' + v%10)
		v /= 10
		if v == 0 {
			break
		}
	}
	return string(b[i:])
}

func (f *fakeStore) addSO(id, warehouseID int64, status string, products ...int64) {
	f.sos[id] = db.GetSalesOrderForDeliveryRow{
		ID: id, SoNumber: "SO-T", CustomerID: 7,
		WarehouseID: pgtype.Int8{Int64: warehouseID, Valid: true},
		Status:      status, OrderType: "regular",
	}
	var items []db.ListSalesOrderItemsRow
	for _, p := range products {
		items = append(items, db.ListSalesOrderItemsRow{ID: int64(len(items) + 1), SalesOrderID: id, ProductID: p, Quantity: decimal.NewFromInt(770)})
	}
	f.soItems[id] = items
}

func (f *fakeStore) addStock(productID, warehouseID int64, quantity, reserved decimal.Decimal) {
	f.stocks[stockKey(productID, warehouseID)] = &db.GetSalesStockRowForUpdateRow{
		ID: f.nextID, ProductID: productID, WarehouseID: warehouseID,
		Quantity: quantity, ReservedQuantity: reserved, CreatedAt: ts(), UpdatedAt: ts(),
	}
	f.nextID++
}

func (f *fakeStore) SalesOrderForDelivery(_ context.Context, id int64) (db.GetSalesOrderForDeliveryRow, error) {
	so, ok := f.sos[id]
	if !ok {
		return so, ErrNotFound
	}
	return so, nil
}

func (f *fakeStore) SalesOrderItems(_ context.Context, orderID int64) ([]db.ListSalesOrderItemsRow, error) {
	return f.soItems[orderID], nil
}

func (f *fakeStore) SetSalesOrderStatus(_ context.Context, _ db.DBTX, id int64, status string) error {
	if f.failSyncSO {
		return errors.New("sync boom")
	}
	if so, ok := f.sos[id]; ok {
		so.Status = status
		f.sos[id] = so
	}
	return nil
}

func (f *fakeStore) CreateDeliveryOrder(_ context.Context, _ db.DBTX, p db.CreateDeliveryOrderParams) (db.CreateDeliveryOrderRow, error) {
	o := &db.GetDeliveryOrderByIDRow{
		ID: f.nextID, DoNumber: p.DoNumber, SalesOrderID: p.SalesOrderID,
		CustomerID: p.CustomerID, WarehouseID: p.WarehouseID, DoDate: p.DoDate.Time.Format("2006-01-02"),
		Priority: p.Priority, Status: p.Status, RequestedQuantity: p.RequestedQuantity,
		ShippedQuantity: p.ShippedQuantity, DeliveredQuantity: p.DeliveredQuantity,
		RemainingQuantity: p.RemainingQuantity, Notes: p.Notes, CreatedBy: p.CreatedBy,
		CreatedAt: ts(), UpdatedAt: ts(),
	}
	id := f.nextID
	f.nextID++
	f.dos[o.ID] = o
	return db.CreateDeliveryOrderRow{ID: id, CreatedAt: o.CreatedAt, UpdatedAt: o.UpdatedAt}, nil
}

func (f *fakeStore) DeliveryOrderByID(_ context.Context, id int64) (db.GetDeliveryOrderByIDRow, error) {
	o, ok := f.dos[id]
	if !ok {
		return db.GetDeliveryOrderByIDRow{}, ErrNotFound
	}
	return *o, nil
}

func (f *fakeStore) DeliveryOrderByIDForUpdate(_ context.Context, _ db.DBTX, id int64) (db.GetDeliveryOrderByIDForUpdateRow, error) {
	o, err := f.DeliveryOrderByID(context.Background(), id)
	if err != nil {
		return db.GetDeliveryOrderByIDForUpdateRow{}, err
	}
	return db.GetDeliveryOrderByIDForUpdateRow(o), nil
}

func (f *fakeStore) ListDeliveryOrders(_ context.Context, flt DOFilter) ([]db.ListDeliveryOrdersRow, int, error) {
	var out []db.ListDeliveryOrdersRow
	for _, o := range f.dos {
		// Mirror the real query's sales_order_id predicate: unrelated DOs
		// must not trip the open-DO guard (or any other caller).
		if flt.SalesOrderID != 0 && o.SalesOrderID != flt.SalesOrderID {
			continue
		}
		out = append(out, db.ListDeliveryOrdersRow(*o))
	}
	return out, len(out), nil
}

func (f *fakeStore) UpdateDeliveryOrderTotals(_ context.Context, _ db.DBTX, id int64, shipped, delivered, remaining decimal.Decimal, status string) error {
	if f.failOnUpdate {
		return errors.New("boom")
	}
	o, ok := f.dos[id]
	if !ok {
		return ErrNotFound
	}
	o.ShippedQuantity, o.DeliveredQuantity, o.RemainingQuantity, o.Status = shipped, delivered, remaining, status
	o.UpdatedAt = ts()
	return nil
}

func (f *fakeStore) SetDeliveryOrderStatus(_ context.Context, _ db.DBTX, id int64, status string) error {
	o, ok := f.dos[id]
	if !ok {
		return ErrNotFound
	}
	o.Status = status
	o.UpdatedAt = ts()
	return nil
}

func (f *fakeStore) CreateDeliveryOrderItem(_ context.Context, _ db.DBTX, p db.CreateDeliveryOrderItemParams) (int64, error) {
	it := &db.DeliveryOrderItem{
		ID: f.nextID, DeliveryOrderID: p.DeliveryOrderID, ProductID: p.ProductID,
		RequestedQuantity: p.RequestedQuantity, ShippedQuantity: p.ShippedQuantity,
		DeliveredQuantity: p.DeliveredQuantity, RemainingQuantity: p.RemainingQuantity,
	}
	f.nextID++
	f.doItems[it.DeliveryOrderID] = append(f.doItems[it.DeliveryOrderID], *it)
	return it.ID, nil
}

func (f *fakeStore) DeliveryOrderItems(_ context.Context, orderID int64) ([]db.ListDeliveryOrderItemsRow, error) {
	var out []db.ListDeliveryOrderItemsRow
	for _, it := range f.doItems[orderID] {
		out = append(out, db.ListDeliveryOrderItemsRow{
			ID: it.ID, DeliveryOrderID: it.DeliveryOrderID, ProductID: it.ProductID,
			RequestedQuantity: it.RequestedQuantity, ShippedQuantity: it.ShippedQuantity,
			DeliveredQuantity: it.DeliveredQuantity, RemainingQuantity: it.RemainingQuantity,
		})
	}
	return out, nil
}

func (f *fakeStore) DeliveryOrderItemsForUpdate(_ context.Context, _ db.DBTX, orderID int64) ([]db.DeliveryOrderItem, error) {
	return f.doItems[orderID], nil
}

func (f *fakeStore) UpdateDeliveryOrderItemTotals(_ context.Context, _ db.DBTX, id int64, shipped, delivered, remaining decimal.Decimal) error {
	for _, list := range f.doItems {
		for i := range list {
			if list[i].ID == id {
				list[i].ShippedQuantity, list[i].DeliveredQuantity, list[i].RemainingQuantity = shipped, delivered, remaining
				return nil
			}
		}
	}
	return ErrNotFound
}

func (f *fakeStore) CreateShipment(_ context.Context, _ db.DBTX, p db.CreateShipmentParams) (db.CreateShipmentRow, error) {
	sh := &db.GetShipmentByIDRow{
		ID: f.nextID, ShipmentNumber: p.ShipmentNumber, DeliveryOrderID: p.DeliveryOrderID,
		Status: p.Status, ShipDate: p.ShipDate.Time.Format("2006-01-02"), Notes: p.Notes, CreatedBy: p.CreatedBy,
		TrackingNumber: p.TrackingNumber, Barcode: p.Barcode,
		CreatedAt: ts(), UpdatedAt: ts(),
	}
	f.nextID++
	f.shipments[sh.ID] = sh
	return db.CreateShipmentRow{ID: sh.ID, CreatedAt: sh.CreatedAt, UpdatedAt: sh.UpdatedAt}, nil
}

func (f *fakeStore) ShipmentByShipmentNumber(_ context.Context, number string) (db.GetShipmentByShipmentNumberRow, error) {
	for _, sh := range f.shipments {
		if sh.ShipmentNumber == number {
			return db.GetShipmentByShipmentNumberRow(*sh), nil
		}
	}
	return db.GetShipmentByShipmentNumberRow{}, ErrNotFound
}

func (f *fakeStore) ShipmentByTrackingNumber(_ context.Context, trackingNumber string) (db.GetShipmentByTrackingNumberRow, error) {
	for _, sh := range f.shipments {
		if sh.TrackingNumber.Valid && sh.TrackingNumber.String == trackingNumber {
			return db.GetShipmentByTrackingNumberRow(*sh), nil
		}
	}
	return db.GetShipmentByTrackingNumberRow{}, ErrNotFound
}

func (f *fakeStore) ShipmentByBarcode(_ context.Context, barcode string) (db.GetShipmentByBarcodeRow, error) {
	for _, sh := range f.shipments {
		if sh.Barcode.Valid && sh.Barcode.String == barcode {
			return db.GetShipmentByBarcodeRow(*sh), nil
		}
	}
	return db.GetShipmentByBarcodeRow{}, ErrNotFound
}

func (f *fakeStore) ShipmentsByDeliveryOrderNumber(_ context.Context, doNumber string) ([]db.ListShipmentsByDeliveryOrderNumberRow, error) {
	var out []db.ListShipmentsByDeliveryOrderNumberRow
	for _, o := range f.dos {
		if o.DoNumber != doNumber {
			continue
		}
		for _, sh := range f.shipments {
			if sh.DeliveryOrderID == o.ID {
				out = append(out, db.ListShipmentsByDeliveryOrderNumberRow{
					ID: sh.ID, ShipmentNumber: sh.ShipmentNumber, DeliveryOrderID: sh.DeliveryOrderID,
					Status: sh.Status, SShipDate: sh.ShipDate, Notes: sh.Notes, CreatedBy: sh.CreatedBy,
					CreatedAt: sh.CreatedAt, UpdatedAt: sh.UpdatedAt,
					TrackingNumber: sh.TrackingNumber, Barcode: sh.Barcode,
				})
			}
		}
	}
	return out, nil
}

func (f *fakeStore) CreateShipmentTrackingEvent(_ context.Context, p db.CreateShipmentTrackingEventParams) (db.CreateShipmentTrackingEventRow, error) {
	e := TrackingEvent{
		ID: f.nextID, ShipmentID: p.ShipmentID, EventTime: p.EventTime.Time, Status: p.Status,
		Source: p.Source, Latitude: nullDecimalToFloatPtr(p.Latitude), Longitude: nullDecimalToFloatPtr(p.Longitude),
		AccuracyM: nullDecimalToFloatPtr(p.AccuracyM), SpeedMps: nullDecimalToFloatPtr(p.SpeedMps),
		HeadingDeg: nullDecimalToFloatPtr(p.HeadingDeg),
		Note:       textToStrPtr(p.Note), ProviderRef: textToStrPtr(p.ProviderRef), CreatedBy: int8ToPtr(p.CreatedBy), CreatedAt: time.Now(),
	}
	f.nextID++
	f.trackingEvents[e.ShipmentID] = append(f.trackingEvents[e.ShipmentID], e)
	import_pgtype := e.CreatedAt
	return db.CreateShipmentTrackingEventRow{ID: e.ID, CreatedAt: pgtype.Timestamptz{Time: import_pgtype, Valid: true}}, nil
}

func (f *fakeStore) ShipmentTrackingEvents(_ context.Context, shipmentID int64) ([]TrackingEvent, error) {
	return f.trackingEvents[shipmentID], nil
}

func (f *fakeStore) ShipmentLatestEvent(_ context.Context, _ int64) (db.ShipmentLatestEventRow, error) {
	return db.ShipmentLatestEventRow{}, ErrNotFound
}

func (f *fakeStore) ActiveShipmentLocations(_ context.Context) ([]db.ActiveShipmentLocationsRow, error) {
	return nil, nil
}

func (f *fakeStore) DispatchByShipmentID(_ context.Context, _ int64) (db.DispatchByShipmentIDRow, error) {
	return db.DispatchByShipmentIDRow{}, ErrNotFound
}

func (f *fakeStore) ListWarehouseLocations(_ context.Context) ([]db.ListWarehouseLocationsRow, error) {
	return nil, nil
}

func (f *fakeStore) ListStoreLocations(_ context.Context) ([]db.ListStoreLocationsRow, error) {
	return nil, nil
}

func (f *fakeStore) ShipmentTrackingEventByProviderRef(_ context.Context, shipmentID int64, providerRef string) (TrackingEvent, error) {
	for _, e := range f.trackingEvents[shipmentID] {
		if e.ProviderRef != nil && *e.ProviderRef == providerRef {
			return e, nil
		}
	}
	return TrackingEvent{}, ErrNotFound
}

func (f *fakeStore) ShipmentByID(_ context.Context, id int64) (db.GetShipmentByIDRow, error) {
	sh, ok := f.shipments[id]
	if !ok {
		return db.GetShipmentByIDRow{}, ErrNotFound
	}
	return *sh, nil
}

func (f *fakeStore) ShipmentByIDForUpdate(_ context.Context, _ db.DBTX, id int64) (db.GetShipmentByIDForUpdateRow, error) {
	sh, err := f.ShipmentByID(context.Background(), id)
	if err != nil {
		return db.GetShipmentByIDForUpdateRow{}, err
	}
	return db.GetShipmentByIDForUpdateRow(sh), nil
}

func (f *fakeStore) ShipmentsByDeliveryOrder(_ context.Context, orderID int64) ([]db.ListShipmentsByDeliveryOrderRow, error) {
	var out []db.ListShipmentsByDeliveryOrderRow
	for _, sh := range f.shipments {
		if sh.DeliveryOrderID == orderID {
			out = append(out, db.ListShipmentsByDeliveryOrderRow(*sh))
		}
	}
	return out, nil
}

func (f *fakeStore) SetShipmentStatus(_ context.Context, _ db.DBTX, id int64, status string) error {
	sh, ok := f.shipments[id]
	if !ok {
		return ErrNotFound
	}
	sh.Status = status
	sh.UpdatedAt = ts()
	return nil
}

func (f *fakeStore) CreateShipmentItem(_ context.Context, _ db.DBTX, p db.CreateShipmentItemParams) (int64, error) {
	it := db.ShipmentItem{
		ID: f.nextID, ShipmentID: p.ShipmentID, DeliveryOrderItemID: p.DeliveryOrderItemID,
		ProductID: p.ProductID, ShippedQuantity: p.ShippedQuantity,
	}
	f.nextID++
	f.shipmentItems[it.ShipmentID] = append(f.shipmentItems[it.ShipmentID], it)
	return it.ID, nil
}

func (f *fakeStore) ShipmentItems(_ context.Context, shipmentID int64) ([]db.ListShipmentItemsRow, error) {
	var out []db.ListShipmentItemsRow
	for _, it := range f.shipmentItems[shipmentID] {
		out = append(out, db.ListShipmentItemsRow{
			ID: it.ID, ShipmentID: it.ShipmentID, DeliveryOrderItemID: it.DeliveryOrderItemID,
			ProductID: it.ProductID, ShippedQuantity: it.ShippedQuantity,
			DeliveredQuantity: it.DeliveredQuantity,
		})
	}
	return out, nil
}

func (f *fakeStore) ShipmentItemsForUpdate(_ context.Context, _ db.DBTX, shipmentID int64) ([]db.ShipmentItem, error) {
	return f.shipmentItems[shipmentID], nil
}

func (f *fakeStore) UpdateShipmentItemDelivered(_ context.Context, _ db.DBTX, id int64, delivered decimal.Decimal) error {
	for _, list := range f.shipmentItems {
		for i := range list {
			if list[i].ID == id {
				list[i].DeliveredQuantity = delivered
				return nil
			}
		}
	}
	return ErrNotFound
}

func (f *fakeStore) CreateDeliveryStatusHistory(_ context.Context, _ db.DBTX, p db.CreateDeliveryStatusHistoryParams) (int64, error) {
	h := db.DeliveryStatusHistory{
		ID: f.nextID, DeliveryOrderID: p.DeliveryOrderID, FromStatus: p.FromStatus,
		ToStatus: p.ToStatus, Reason: p.Reason, ReferenceType: p.ReferenceType,
		ReferenceID: p.ReferenceID, ChangedBy: p.ChangedBy, CreatedAt: ts(),
	}
	f.nextID++
	f.history[h.DeliveryOrderID] = append(f.history[h.DeliveryOrderID], h)
	return h.ID, nil
}

func (f *fakeStore) DeliveryStatusHistory(_ context.Context, orderID int64) ([]db.DeliveryStatusHistory, error) {
	return f.history[orderID], nil
}

func (f *fakeStore) CreateDispatch(_ context.Context, _ db.DBTX, p db.CreateDispatchParams) (db.CreateDispatchRow, error) {
	d := &db.Dispatch{
		ID: f.nextID, DeliveryOrderID: p.DeliveryOrderID, ShipmentID: p.ShipmentID,
		FleetID: p.FleetID, Priority: p.Priority, QueueOrder: p.QueueOrder,
		DriverID: p.DriverID, ScheduledFor: p.ScheduledFor, Status: p.Status,
		DispatchedAt: p.DispatchedAt, DispatcherID: p.DispatcherID, CreatedAt: ts(), UpdatedAt: ts(),
	}
	f.nextID++
	f.dispatches[d.ID] = d
	return db.CreateDispatchRow{ID: d.ID, CreatedAt: d.CreatedAt, UpdatedAt: d.UpdatedAt}, nil
}

func (f *fakeStore) LatestDispatch(_ context.Context, _ db.DBTX, orderID int64) (db.Dispatch, error) {
	var latest *db.Dispatch
	for _, d := range f.dispatches {
		if d.DeliveryOrderID != orderID {
			continue
		}
		if latest == nil || d.QueueOrder > latest.QueueOrder {
			d := d
			latest = d
		}
	}
	if latest == nil {
		return db.Dispatch{}, ErrNotFound
	}
	return *latest, nil
}

func (f *fakeStore) DispatchesByDeliveryOrder(_ context.Context, orderID int64) ([]db.Dispatch, error) {
	var out []db.Dispatch
	for _, d := range f.dispatches {
		if d.DeliveryOrderID == orderID {
			out = append(out, *d)
		}
	}
	return out, nil
}

func (f *fakeStore) CreateFleet(_ context.Context, _ db.DBTX, p db.CreateFleetParams) (db.CreateFleetRow, error) {
	v := &db.Fleet{
		ID: f.nextID, PlateNumber: p.PlateNumber, VehicleName: p.VehicleName,
		VehicleType: p.VehicleType, CapacityWeight: p.CapacityWeight,
		CapacityVolume: p.CapacityVolume, IsActive: p.IsActive, CreatedAt: ts(), UpdatedAt: ts(),
	}
	f.nextID++
	f.fleet[v.ID] = v
	return db.CreateFleetRow{ID: v.ID, CreatedAt: v.CreatedAt, UpdatedAt: v.UpdatedAt}, nil
}

func (f *fakeStore) FleetByID(_ context.Context, id int64) (db.Fleet, error) {
	v, ok := f.fleet[id]
	if !ok {
		return db.Fleet{}, ErrNotFound
	}
	return *v, nil
}

func (f *fakeStore) ListFleet(_ context.Context, _ FleetFilter) ([]db.Fleet, int, error) {
	var out []db.Fleet
	for _, v := range f.fleet {
		out = append(out, *v)
	}
	return out, len(out), nil
}

func (f *fakeStore) ListActiveUsersForDriverPicker(_ context.Context) ([]db.ListActiveUsersForDriverPickerRow, error) {
	return nil, nil
}

func (f *fakeStore) CreateDriverUser(_ context.Context, p db.CreateDriverUserParams) (db.CreateDriverUserRow, error) {
	return db.CreateDriverUserRow{ID: 1, Name: p.Name}, nil
}

func (f *fakeStore) LockStockForUpdate(_ context.Context, _ db.DBTX, productID, warehouseID int64) (db.GetSalesStockRowForUpdateRow, error) {
	s, ok := f.stocks[stockKey(productID, warehouseID)]
	if !ok {
		return db.GetSalesStockRowForUpdateRow{}, ErrNotFound
	}
	return *s, nil
}

func (f *fakeStore) EnsureStockRow(_ context.Context, _ db.DBTX, productID, warehouseID int64) error {
	f.addStock(productID, warehouseID, decimal.Zero, decimal.Zero)
	return nil
}

func (f *fakeStore) SetStockQuantity(_ context.Context, _ db.DBTX, stockID int64, quantity, reserved decimal.Decimal) error {
	for _, s := range f.stocks {
		if s.ID == stockID {
			s.Quantity, s.ReservedQuantity = quantity, reserved
			return nil
		}
	}
	return ErrNotFound
}

// --- test helpers -------------------------------------------------------------

type seqStub struct{ n int64 }

func (s *seqStub) Sequence(_ context.Context, _ string, _ time.Time, _ any) (int64, error) {
	s.n++
	return s.n, nil
}

func jakarta() *time.Location { return time.FixedZone("WIB", 7*3600) }

func newService(store *fakeStore) *Service {
	return NewService(store, &seqStub{}, jakarta())
}

func (f *fakeStore) captureLedger(s *Service) {
	s.WithShipmentLedger(func(_ context.Context, _ db.DBTX, productID, warehouseID, shipmentID int64, before, after, qty decimal.Decimal) error {
		f.shipLedger = append(f.shipLedger, ledgerCall{productID, warehouseID, shipmentID, before, after, qty})
		return nil
	})
}

func (f *fakeStore) mustSO(t *testing.T, id int64, warehouseID int64, status string, products ...int64) {
	t.Helper()
	f.addSO(id, warehouseID, status, products...)
}

func (f *fakeStore) mustDO(t *testing.T, svc *Service, soID int64, products ...int64) *DeliveryOrder {
	t.Helper()
	var items []DOItemInput
	for _, p := range products {
		items = append(items, DOItemInput{ProductID: p, RequestedQuantity: decimal.NewFromInt(770)})
	}
	o, err := svc.CreateDeliveryOrder(context.Background(), DOInput{SalesOrderID: soID, Items: items}, 1)
	if err != nil {
		t.Fatalf("CreateDeliveryOrder: %v", err)
	}
	return o
}

func TestCreateDeliveryOrder_RejectsSecondOpenDO(t *testing.T) {
	// Double fulfillment: a second DO while another is still working the
	// same SO ships it twice. Terminal DOs don't block a re-dispatch.
	f := newFakeStore()
	svc := newService(f)
	f.mustSO(t, 11, 1, "confirmed", 9)
	f.mustDO(t, svc, 11, 9)
	_, err := svc.CreateDeliveryOrder(context.Background(), DOInput{
		SalesOrderID: 11,
		Items:        []DOItemInput{{ProductID: 9, RequestedQuantity: decimal.NewFromInt(1)}},
	}, 1)
	if !errors.Is(err, ErrOpenDOExists) {
		t.Fatalf("second DO err = %v, want ErrOpenDOExists", err)
	}
	// After the first DO completes, dispatching again is allowed.
	for _, d := range f.dos {
		d.Status = StatusCompleted
	}
	if _, err := svc.CreateDeliveryOrder(context.Background(), DOInput{
		SalesOrderID: 11,
		Items:        []DOItemInput{{ProductID: 9, RequestedQuantity: decimal.NewFromInt(1)}},
	}, 1); err != nil {
		t.Fatalf("re-dispatch after completed DO err = %v, want nil", err)
	}
}

func (f *fakeStore) CustomerByUserID(_ context.Context, userID int64) (int64, error) {
	if f.customerByUser == nil {
		f.customerByUser = map[int64]int64{}
	}
	if id, ok := f.customerByUser[userID]; ok {
		return id, nil
	}
	return 0, ErrNotFound
}

func (f *fakeStore) ShipmentCustomerID(_ context.Context, _ int64) (int64, error) {
	// Test linkage: customer 3 owns seeded shipments (ownership checks rely
	// on the caller being buyer role to consult this at all).
	return 3, nil
}

func (f *fakeStore) FleetLatestLocations(_ context.Context) ([]db.FleetLatestLocationsRow, error) {
	return nil, nil
}

// The DO list/detail rows carry the display fields resolved through the
// sales-order/user/customer joins: so_number, customer_name,
// shipping_address, sales_name, recipient_name and delivered_at.
func TestDeliveryOrder_ResolvesDisplayFields(t *testing.T) {
	f := newFakeStore()
	svc := newService(f)
	delivered := pgtype.Timestamptz{Time: time.Date(2026, 9, 1, 10, 30, 0, 0, time.UTC), Valid: true}
	row := &db.GetDeliveryOrderByIDRow{
		ID: 1, DoNumber: "DO-1", SalesOrderID: 5, CustomerID: 7, WarehouseID: 2,
		DoDate: "2026-08-30", Priority: PriorityNormal, Status: StatusDelivered,
		RequestedQuantity: decimal.NewFromInt(10), RemainingQuantity: decimal.NewFromInt(0),
		CustomerName:    "Toko Maju",
		SoNumber:        "SO-0001",
		ShippingAddress: pgtype.Text{String: "Jl. Merdeka 1", Valid: true},
		SalesName:       pgtype.Text{String: "Siti", Valid: true},
		RecipientName:   "Budi (Toko Maju)",
		DeliveredAt:     delivered,
		CreatedAt:       ts(), UpdatedAt: ts(),
	}
	f.dos[1] = row

	got, err := svc.GetDeliveryOrder(context.Background(), 1)
	if err != nil {
		t.Fatal(err)
	}
	if got.CustomerName != "Toko Maju" || got.SoNumber != "SO-0001" ||
		got.ShippingAddress == nil || *got.ShippingAddress != "Jl. Merdeka 1" ||
		got.SalesName == nil || *got.SalesName != "Siti" ||
		got.RecipientName != "Budi (Toko Maju)" ||
		got.DeliveredAt == nil || *got.DeliveredAt != "2026-09-01T10:30:00Z" {
		t.Errorf("detail display fields = %+v", got)
	}

	list, total, err := svc.ListDeliveryOrders(context.Background(), DOFilter{Page: 1, PerPage: 20})
	if err != nil {
		t.Fatal(err)
	}
	if total != 1 || len(list) != 1 {
		t.Fatalf("list = %d rows (total %d), want 1", len(list), total)
	}
	if list[0].SalesName == nil || *list[0].SalesName != "Siti" || list[0].SoNumber != "SO-0001" ||
		list[0].CustomerName != "Toko Maju" || list[0].RecipientName != "Budi (Toko Maju)" ||
		list[0].DeliveredAt == nil || *list[0].DeliveredAt != "2026-09-01T10:30:00Z" {
		t.Errorf("list display fields = %+v", list[0])
	}
}

// Rows without a resolved sales user or delivery timestamp serialize those
// fields as JSON null — never fabricated values.
func TestDeliveryOrder_NullDisplayFields(t *testing.T) {
	f := newFakeStore()
	svc := newService(f)
	f.dos[2] = &db.GetDeliveryOrderByIDRow{
		ID: 2, DoNumber: "DO-2", SalesOrderID: 5, CustomerID: 7, WarehouseID: 2,
		DoDate: "2026-08-30", Priority: PriorityNormal, Status: StatusInTransit,
		CreatedAt: ts(), UpdatedAt: ts(),
	}
	got, err := svc.GetDeliveryOrder(context.Background(), 2)
	if err != nil {
		t.Fatal(err)
	}
	if got.SalesName != nil || got.ShippingAddress != nil || got.DeliveredAt != nil {
		t.Errorf("unresolvable fields should be nil, got sales=%v addr=%v delivered=%v",
			got.SalesName, got.ShippingAddress, got.DeliveredAt)
	}
	if got.CustomerName != "" || got.SoNumber != "" || got.RecipientName != "" {
		t.Errorf("COALESCE'd fields should be empty strings, got %q/%q/%q",
			got.CustomerName, got.SoNumber, got.RecipientName)
	}
}

func (f *fakeStore) SetDeliveryOrderPODRow(_ context.Context, _ db.DBTX, id int64, photoURL *string, recipient string) error {
	d, ok := f.dos[id]
	if !ok {
		return ErrNotFound
	}
	if photoURL != nil {
		d.ProofPhotoUrl = pgtype.Text{String: *photoURL, Valid: true}
	} else {
		d.ProofPhotoUrl = pgtype.Text{}
	}
	d.PodRecipientName = pgtype.Text{String: recipient, Valid: recipient != ""}
	return nil
}

// POD (000143): the photo + recipient ride the completion request and are
// stored on the DO row. Evidence, not a gate — completing without them still
// works, which is the previous behaviour every existing caller relies on.
func TestDeliverDeliveryOrder_RecordsPOD(t *testing.T) {
	f := newFakeStore()
	f.mustSO(t, 1, 2, "confirmed", 10)
	f.addStock(10, 2, decimal.NewFromInt(500), decimal.NewFromInt(500))
	svc := newService(f)
	o := f.mustDO(t, svc, 1, 10)
	if _, err := svc.CreateShipment(context.Background(), o.ID, ShipmentInput{
		Items:    []ShipmentItemInput{{ProductID: 10, ShippedQuantity: decimal.NewFromInt(2)}},
		Dispatch: &DispatchInfo{FleetID: 1, Priority: "normal"},
	}, 1); err != nil {
		t.Fatal(err)
	}

	done, err := svc.DeliverDeliveryOrder(context.Background(), o.ID, 1, nil, "")
	if err != nil {
		t.Fatalf("complete without POD: %v", err)
	}
	if done.Status != StatusCompleted {
		t.Fatalf("status = %s, want completed", done.Status)
	}
	if done.ProofPhotoURL != nil {
		t.Errorf("no photo sent, got %s", *done.ProofPhotoURL)
	}
}

func TestDeliverDeliveryOrder_WithPhotoStoresURL(t *testing.T) {
	f := newFakeStore()
	f.mustSO(t, 1, 2, "confirmed", 10)
	f.addStock(10, 2, decimal.NewFromInt(500), decimal.NewFromInt(500))
	svc := newService(f)
	o := f.mustDO(t, svc, 1, 10)
	if _, err := svc.CreateShipment(context.Background(), o.ID, ShipmentInput{
		Items:    []ShipmentItemInput{{ProductID: 10, ShippedQuantity: decimal.NewFromInt(2)}},
		Dispatch: &DispatchInfo{FleetID: 1, Priority: "normal"},
	}, 1); err != nil {
		t.Fatal(err)
	}

	// Wire a real saver over the shared uploads pipeline (temp-dir store) so
	// the recorded URL is what production would serve — /uploads/proof-….
	dir := t.TempDir()
	ustore, err := storage.NewLocal(dir)
	if err != nil {
		t.Fatal(err)
	}
	svc.WithPODSaver(func(_ context.Context, header *multipart.FileHeader) (string, error) {
		return uploads.SaveFileHeader(context.Background(), ustore, header, uploads.ProofPrefix)
	})

	// A real tiny PNG so the shared pipeline's content-type sniffing passes.
	// multipart.FileHeader has no public constructor with content, so build a
	// real one-part form through multipart.Writer and take its header — the
	// same shape the ERP request arrives in. Order matters: encode the image
	// into its own buffer FIRST, then write the form around it.
	img := image.NewRGBA(image.Rect(1, 1, 2, 2))
	var pngBuf bytes.Buffer
	if err := png.Encode(&pngBuf, img); err != nil {
		t.Fatal(err)
	}
	var body bytes.Buffer
	mw := multipart.NewWriter(&body)
	fw, err := mw.CreateFormFile("file", "pod.png")
	if err != nil {
		t.Fatal(err)
	}
	if _, err := fw.Write(pngBuf.Bytes()); err != nil {
		t.Fatal(err)
	}
	if err := mw.Close(); err != nil {
		t.Fatal(err)
	}
	mr := multipart.NewReader(&body, mw.Boundary())
	form, err := mr.ReadForm(1 << 20)
	if err != nil {
		t.Fatal(err)
	}
	if len(form.File["file"]) == 0 {
		t.Fatal("no file part parsed")
	}
	header := form.File["file"][0]
	done, err := svc.DeliverDeliveryOrder(context.Background(), o.ID, 1, header, "Budi Penerima")
	if err != nil {
		t.Fatalf("complete with POD: %v", err)
	}
	if done.ProofPhotoURL == nil {
		t.Fatal("photo not recorded")
	}
	if !strings.HasPrefix(*done.ProofPhotoURL, "/uploads/proof-") {
		t.Errorf("proof url = %s, want /uploads/proof-…", *done.ProofPhotoURL)
	}
	if done.PodRecipient == nil || *done.PodRecipient != "Budi Penerima" {
		t.Errorf("pod_recipient = %v, want Budi Penerima", done.PodRecipient)
	}
}