package integration

import (
	"context"
	"errors"
	"fmt"
	"os"
	"path/filepath"
	"strings"
	"sync"
	"testing"
	"time"

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

	"github.com/TaruLab/keluargamart-backend/internal/distribution"
	"github.com/TaruLab/keluargamart-backend/internal/finance"
	"github.com/TaruLab/keluargamart-backend/internal/inventory"
	"github.com/TaruLab/keluargamart-backend/internal/lhpp"
	"github.com/TaruLab/keluargamart-backend/internal/sales"
)

// dispatchedDO is one delivery order with its own sales order and invoice —
// exactly the shape ListLHPPCandidateRows needs to place a DO on a sheet.
type dispatchedDO struct {
	DOID     int64
	DONumber string
	Invoice  int64
	Date     string
}

// seedDispatchedDOs inserts n DOs for one driver on one date, each with its own
// sales order and an invoice, and returns them in insertion order. Every DO
// shares driver and date on purpose: that is the case the OLD computed grouping
// collapsed into a single sheet, so the tests can prove it no longer does.
func seedDispatchedDOs(t *testing.T, pool *pgxpool.Pool, cust int64, wh int64, driver int64, n int, date string, amount int64) []dispatchedDO {
	t.Helper()
	ctx := context.Background()
	out := make([]dispatchedDO, 0, n)
	for i := 0; i < n; i++ {
		var soID int64
		if err := pool.QueryRow(ctx, `
			INSERT INTO sales_orders (so_number, customer_id, warehouse_id, order_date, status, order_type)
			VALUES ($1, $2, $3, $4, 'confirmed', 'regular') RETURNING id`,
			unique("SO"), cust, wh, date).Scan(&soID); err != nil {
			t.Fatal(err)
		}
		var invID int64
		if err := pool.QueryRow(ctx, `
			INSERT INTO invoices (invoice_number, customer_id, sales_order_id, total_amount, paid_amount,
			                      remaining_amount, status, invoice_date, due_date)
			VALUES ($1, $2, $3, $4, 0, $4, 'unpaid', $5, $5) RETURNING id`,
			unique("INV"), cust, soID, amount, date).Scan(&invID); err != nil {
			t.Fatal(err)
		}
		var doID int64
		if err := pool.QueryRow(ctx, `
			INSERT INTO delivery_orders (do_number, sales_order_id, customer_id, warehouse_id, do_date, status)
			VALUES ($1, $2, $3, $4, $5, 'in_transit') RETURNING id`,
			unique("DO"), soID, cust, wh, date).Scan(&doID); err != nil {
			t.Fatal(err)
		}
		var shipmentID int64
		if err := pool.QueryRow(ctx, `
			INSERT INTO shipments (shipment_number, delivery_order_id, status, ship_date)
			VALUES ($1, $2, 'in_transit', $3) RETURNING id`,
			unique("SHP"), doID, date).Scan(&shipmentID); err != nil {
			t.Fatal(err)
		}
		var fleetID int64
		if err := pool.QueryRow(ctx, `SELECT id FROM fleet LIMIT 1`).Scan(&fleetID); err != nil {
			t.Fatal(err)
		}
		if _, err := pool.Exec(ctx, `
			INSERT INTO dispatches (delivery_order_id, shipment_id, fleet_id, status, driver_id, queue_order)
			VALUES ($1, $2, $3, 'dispatched', $4, 1)`,
			doID, shipmentID, fleetID, driver); err != nil {
			t.Fatal(err)
		}
		var doNumber string
		if err := pool.QueryRow(ctx, `SELECT do_number FROM delivery_orders WHERE id=$1`, doID).Scan(&doNumber); err != nil {
			t.Fatal(err)
		}
		out = append(out, dispatchedDO{DOID: doID, DONumber: doNumber, Invoice: invID, Date: date})
	}
	return out
}

// lhppService builds the LHPP service over the real schema. The finance
// dependency is only there because payments post through it.
func lhppService(pool *pgxpool.Pool) *lhpp.Service {
	fin := finance.NewService(finance.NewRepository(pool), seqAdapter{pool}, wib)
	return lhpp.NewService(lhpp.NewRepository(pool), fin)
}

// lhppDispatchService builds the distribution service with the LHPP grouper
// seam wired exactly as main.go wires it.
func lhppDispatchService(pool *pgxpool.Pool, l *lhpp.Service, inv *inventory.Service) *distribution.Service {
	return distributionService(pool, inv).WithLHPPGrouper(l.Grouper)
}

func groupRow(t *testing.T, pool *pgxpool.Pool, id int64) (number, status string, driver int64) {
	t.Helper()
	if err := pool.QueryRow(context.Background(),
		`SELECT lhpp_number, status, driver_id FROM lhpp_groups WHERE id=$1`, id).
		Scan(&number, &status, &driver); err != nil {
		t.Fatal(err)
	}
	return
}

func groupMembers(t *testing.T, pool *pgxpool.Pool, id int64) []int64 {
	t.Helper()
	rows, err := pool.Query(context.Background(),
		`SELECT delivery_order_id FROM lhpp_group_items WHERE group_id=$1 ORDER BY delivery_order_id`, id)
	if err != nil {
		t.Fatal(err)
	}
	defer rows.Close()
	var out []int64
	for rows.Next() {
		var id int64
		if err := rows.Scan(&id); err != nil {
			t.Fatal(err)
		}
		out = append(out, id)
	}
	return out
}

func countRows(t *testing.T, pool *pgxpool.Pool, sql string, args ...any) int {
	t.Helper()
	var n int
	if err := pool.QueryRow(context.Background(), sql, args...).Scan(&n); err != nil {
		t.Fatal(err)
	}
	return n
}

func countHistory(t *testing.T, pool *pgxpool.Pool, doID int64, action string) int {
	t.Helper()
	return countRows(t, pool,
		`SELECT count(*) FROM lhpp_group_history WHERE delivery_order_id=$1 AND action=$2`, doID, action)
}

// TestLHPPBackfill_CreatesOneGroupPerBillableDriverDay is the deploy-safety
// gate: 000101's backfill must reproduce exactly the sheets the old computed
// fold produced — one per (driver, do_date) — so the deploy is invisible and
// already-printed sheets stay valid.
func TestLHPPBackfill_CreatesOneGroupPerBillableDriverDay(t *testing.T) {
	admin := testPool(t)
	setUp(t, admin)
	ctx := context.Background()

	cust := seedCustomer(t, admin)
	wh := seedWarehouse(t, admin)
	driver := seedUser(t, admin)
	driver2 := seedUser(t, admin)
	var fleetID int64
	if err := admin.QueryRow(ctx, `
		INSERT INTO fleet (plate_number, vehicle_name, is_active) VALUES ($1, 'Truck', true) RETURNING id`,
		unique("PLATE")).Scan(&fleetID); err != nil {
		t.Fatal(err)
	}

	// 2 DOs driver1/2026-09-20, 1 DO driver2/2026-09-20, 1 DO driver1/2026-09-25.
	// Four billable DOs across three buckets -> three groups, NOT four and not one.
	dos := seedDispatchedDOs(t, admin, cust, wh, driver, 2, "2026-09-20", 3_000_000)
	dos = append(dos, seedDispatchedDOs(t, admin, cust, wh, driver2, 1, "2026-09-20", 4_000_000)...)
	dos = append(dos, seedDispatchedDOs(t, admin, cust, wh, driver, 1, "2026-09-25", 5_000_000)...)

	applyBackfill(t, admin)

	got := countRows(t, admin, `SELECT count(*) FROM lhpp_groups WHERE status='active'`)
	if got != 3 {
		t.Errorf("active groups after backfill = %d, want 3 (one per driver+do_date)", got)
	}
	if got := countRows(t, admin, `SELECT count(*) FROM lhpp_group_items`); got != len(dos) {
		t.Errorf("membership rows = %d, want %d (every billable DO placed exactly once)", got, len(dos))
	}
	// Every DO is in exactly one group, and the groups carry distinct numbers.
	if got := countRows(t, admin, `SELECT count(DISTINCT delivery_order_id) FROM lhpp_group_items`); got != len(dos) {
		t.Errorf("distinct DOs in membership = %d, want %d", got, len(dos))
	}
	if got := countRows(t, admin, `SELECT count(DISTINCT lhpp_number) FROM lhpp_groups`); got != 3 {
		t.Errorf("distinct lhpp_number = %d, want 3 — two buckets in one month must not collide", got)
	}
	for _, d := range dos {
		var n int
		if err := admin.QueryRow(ctx, `SELECT count(*) FROM lhpp_group_items WHERE delivery_order_id=$1`, d.DOID).Scan(&n); err != nil {
			t.Fatal(err)
		}
		if n != 1 {
			t.Errorf("DO %d appears in %d groups, want exactly 1", d.DOID, n)
		}
	}

	// The number must be the allocator format, and the sequence must already be
	// past it — otherwise the first group created after deploy collides and
	// wedges that (prefix, month) bucket permanently (000084's subject).
	var seqCount int64
	if err := admin.QueryRow(ctx, `
		SELECT counter FROM document_number_sequences WHERE prefix='LHPP' AND doc_date = DATE '2026-09-01'`).Scan(&seqCount); err != nil {
		t.Fatalf("LHPP 202609 sequence row missing: %v", err)
	}
	var maxOrd int64
	if err := admin.QueryRow(ctx, `
		SELECT max((regexp_match(lhpp_number, '-([0-9]{6})$'))[1]::bigint) FROM lhpp_groups`).Scan(&maxOrd); err != nil {
		t.Fatal(err)
	}
	if seqCount < maxOrd {
		t.Errorf("LHPP 202609 counter = %d, below the backfilled max %d — the next insert would collide", seqCount, maxOrd)
	}
	// And the allocator really does hand out a fresh number after the backfill.
	l := lhppService(admin)
	other := seedDispatchedDOs(t, admin, cust, wh, driver, 1, "2026-09-28", 1_000_000)
	if err := l.Grouper(ctx, admin, driver, other[0].DOID, "2026-09-28"); err != nil {
		t.Fatalf("Grouper after backfill: %v", err)
	}
	var newNumber string
	if err := admin.QueryRow(ctx, `
		SELECT g.lhpp_number FROM lhpp_groups g
		JOIN lhpp_group_items i ON i.group_id=g.id WHERE i.delivery_order_id=$1`, other[0].DOID).Scan(&newNumber); err != nil {
		t.Fatal(err)
	}
	var ord int64
	if _, err := fmt.Sscanf(newNumber[len(newNumber)-6:], "%d", &ord); err != nil {
		t.Fatal(err)
	}
	if ord <= maxOrd {
		t.Errorf("post-backfill number %q ordinal %d must exceed the backfilled max %d", newNumber, ord, maxOrd)
	}
}

// TestLHPPBackfill_IsIdempotentAndComplete runs the backfill statements twice.
// The second run must be a total no-op: no duplicate groups, no duplicate
// membership, and a counter that only ever moves up.
func TestLHPPBackfill_IsIdempotentAndComplete(t *testing.T) {
	admin := testPool(t)
	setUp(t, admin)
	ctx := context.Background()

	cust := seedCustomer(t, admin)
	wh := seedWarehouse(t, admin)
	driver := seedUser(t, admin)
	if _, err := admin.Exec(ctx, `
		INSERT INTO fleet (plate_number, vehicle_name, is_active) VALUES ($1, 'Truck', true)`,
		unique("PLATE")); err != nil {
		t.Fatal(err)
	}
	dos := seedDispatchedDOs(t, admin, cust, wh, driver, 3, "2026-09-20", 2_000_000)

	applyBackfill(t, admin)
	firstGroups := countRows(t, admin, `SELECT count(*) FROM lhpp_groups`)
	firstItems := countRows(t, admin, `SELECT count(*) FROM lhpp_group_items`)
	var firstCounter int64
	if err := admin.QueryRow(ctx, `
		SELECT counter FROM document_number_sequences WHERE prefix='LHPP'`).Scan(&firstCounter); err != nil {
		t.Fatal(err)
	}

	// Second run: the whole file again, exactly as a re-apply would.
	applyBackfill(t, admin)
	applyBackfill(t, admin)

	if got := countRows(t, admin, `SELECT count(*) FROM lhpp_groups`); got != firstGroups {
		t.Errorf("groups after re-running = %d, want %d (NOT EXISTS must make a re-run a no-op)", got, firstGroups)
	}
	if got := countRows(t, admin, `SELECT count(*) FROM lhpp_group_items`); got != firstItems {
		t.Errorf("membership after re-running = %d, want %d", got, firstItems)
	}
	if got := firstItems; got != len(dos) {
		t.Errorf("membership = %d, want %d (every billable DO)", got, len(dos))
	}
	var secondCounter int64
	if err := admin.QueryRow(ctx, `
		SELECT counter FROM document_number_sequences WHERE prefix='LHPP'`).Scan(&secondCounter); err != nil {
		t.Fatal(err)
	}
	if secondCounter < firstCounter {
		t.Errorf("counter went backwards on re-run: %d -> %d (GREATEST must never lower it)", firstCounter, secondCounter)
	}
}

// TestLHPPDispatch_CreatesOneGroupPerDO pins the seam's whole point: every
// dispatched DO gets its OWN sheet. Two DOs of one driver on one date are two
// groups, because the old "one sheet per driver per day" bucketing is what this
// change removes.
func TestLHPPDispatch_CreatesOneGroupPerDO(t *testing.T) {
	pool := testPool(t)
	setUp(t, pool)
	ctx := context.Background()

	cust := seedCustomer(t, pool)
	wh := seedWarehouse(t, pool)
	cat := seedCategory(t, pool)
	unit := seedUnit(t, pool)
	p := seedProduct(t, pool, cat, unit, "100")
	seedStock(t, pool, p.ID, wh, "100")
	op := seedUser(t, pool)
	driver := seedUser(t, pool)
	if _, err := pool.Exec(ctx, `
		INSERT INTO fleet (plate_number, vehicle_name, is_active) VALUES ($1, 'Truck', true)`,
		unique("PLATE")); err != nil {
		t.Fatal(err)
	}
	invSvc := inventory.NewService(inventory.NewRepository(pool), seqAdapter{pool}, wib)
	salesSvc := salesService(pool, invSvc)
	l := lhppService(pool)
	distSvc := lhppDispatchService(pool, l, invSvc)

	date := "2026-09-20"
	mk := func(qty int64) (int64, int64) {
		so, err := salesSvc.Create(ctx, sales.SOInput{
			CustomerID:  cust,
			WarehouseID: &wh,
			OrderDate:   date,
			Items:       []sales.SOItemInput{{ProductID: p.ID, Quantity: decimal.NewFromInt(qty), UnitPrice: decimal.NewFromInt(100)}},
		})
		if err != nil {
			t.Fatal(err)
		}
		if _, err := salesSvc.Confirm(ctx, so.ID); err != nil {
			t.Fatal(err)
		}
		do, err := distSvc.CreateDeliveryOrder(ctx, distribution.DOInput{
			SalesOrderID: so.ID,
			Items:        []distribution.DOItemInput{{ProductID: p.ID, RequestedQuantity: decimal.NewFromInt(qty)}},
		}, op)
		if err != nil {
			t.Fatal(err)
		}
		sh, err := distSvc.CreateShipment(ctx, do.ID, distribution.ShipmentInput{
			Items: []distribution.ShipmentItemInput{{ProductID: p.ID, ShippedQuantity: decimal.NewFromInt(qty)}},
		}, op)
		if err != nil {
			t.Fatal(err)
		}
		var fleetID int64
		if err := pool.QueryRow(ctx, `SELECT id FROM fleet LIMIT 1`).Scan(&fleetID); err != nil {
			t.Fatal(err)
		}
		if _, err := distSvc.CreateDispatch(ctx, do.ID, distribution.DispatchInput{
			ShipmentID: sh.ID, FleetID: fleetID, DriverID: &driver, Priority: distribution.PriorityNormal,
		}, op); err != nil {
			t.Fatal(err)
		}
		return do.ID, sh.ID
	}

	do1, sh1 := mk(10)
	do2, sh2 := mk(20)

	if got := countRows(t, pool, `SELECT count(*) FROM lhpp_groups`); got != 2 {
		t.Fatalf("groups after dispatching 2 DOs of one driver on one date = %d, want 2 (no auto-batching)", got)
	}
	g1 := groupOf(t, pool, do1)
	g2 := groupOf(t, pool, do2)
	if g1 == g2 {
		t.Fatalf("both DOs landed in group %d; each dispatch must get its own single-DO group", g1)
	}
	for _, g := range []int64{g1, g2} {
		n, status, drv := groupRow(t, pool, g)
		if status != lhpp.GroupActive {
			t.Errorf("group %d status = %q, want active", g, status)
		}
		if drv != driver {
			t.Errorf("group %d driver = %d, want %d", g, drv, driver)
		}
		// The month is derived, not hard-coded. This asserted the literal
		// "LHPP-202609-" because the test was written in September 2026, and
		// it started failing on 1 October — not because the allocator broke.
		//
		// CreateDeliveryOrder dates a DO with time.Now().In(s.loc), and
		// nextLHPPNumber deliberately buckets the counter by the group's own
		// lhpp_date so the number's YYYYMM and the sequence key always agree.
		// The DO's order date (2026-09-20 below) is not the DO's own date. So
		// the correct expectation is the CURRENT month, computed in the same
		// zone the service uses.
		if want := "LHPP-" + time.Now().In(wib).Format("200601") + "-"; !strings.HasPrefix(n, want) {
			t.Errorf("group %d number = %q, want the LHPP-YYYYMM-NNNNNN allocator format for %s", g, n, want)
		}
		if len(n) != len("LHPP-YYYYMM-NNNNNN") {
			t.Errorf("group %d number = %q, want 18 characters", g, n)
		}
	}
	// Re-dispatching the same DO must not create a second group: idempotency
	// falls out of lhpp_group_items_do_unique plus the "already placed" probe.
	var fleetID int64
	if err := pool.QueryRow(ctx, `SELECT id FROM fleet LIMIT 1`).Scan(&fleetID); err != nil {
		t.Fatal(err)
	}
	if _, err := distSvc.CreateDispatch(ctx, do1, distribution.DispatchInput{
		ShipmentID: sh1, FleetID: fleetID, DriverID: &driver, Priority: distribution.PriorityNormal,
	}, op); err != nil {
		t.Fatalf("re-dispatch same driver: %v", err)
	}
	if got := countRows(t, pool, `SELECT count(*) FROM lhpp_groups`); got != 2 {
		t.Errorf("groups after idempotent re-dispatch = %d, want 2", got)
	}
	if got := countHistory(t, pool, do1, lhpp.ActionCreated); got != 1 {
		t.Errorf("'created' history rows for DO %d = %d, want exactly 1", do1, got)
	}
	_ = sh2
}

func groupOf(t *testing.T, pool *pgxpool.Pool, doID int64) int64 {
	t.Helper()
	var g int64
	if err := pool.QueryRow(context.Background(),
		`SELECT group_id FROM lhpp_group_items WHERE delivery_order_id=$1`, doID).Scan(&g); err != nil {
		t.Fatal(err)
	}
	return g
}

// TestLHPPGroupMergeSplit_RoundTrip is the reversibility gate: merge then split
// restores the original membership AND leaves every surviving sheet's number
// untouched — the property a synthetic driver+date key could not provide.
func TestLHPPGroupMergeSplit_RoundTrip(t *testing.T) {
	pool := testPool(t)
	setUp(t, pool)
	ctx := context.Background()
	cust := seedCustomer(t, pool)
	wh := seedWarehouse(t, pool)
	driver := seedUser(t, pool)
	if _, err := pool.Exec(ctx, `
		INSERT INTO fleet (plate_number, vehicle_name, is_active) VALUES ($1, 'Truck', true)`,
		unique("PLATE")); err != nil {
		t.Fatal(err)
	}
	// 2026-09-20 and 2026-09-25 for the same driver: a multi-day sheet is legal.
	a := seedDispatchedDOs(t, pool, cust, wh, driver, 1, "2026-09-20", 3_000_000)
	b := seedDispatchedDOs(t, pool, cust, wh, driver, 1, "2026-09-25", 5_000_000)

	l := lhppService(pool)
	if err := l.Grouper(ctx, pool, driver, a[0].DOID, "2026-09-20"); err != nil {
		t.Fatal(err)
	}
	if err := l.Grouper(ctx, pool, driver, b[0].DOID, "2026-09-25"); err != nil {
		t.Fatal(err)
	}
	ga, gb := groupOf(t, pool, a[0].DOID), groupOf(t, pool, b[0].DOID)
	na, _, _ := groupRow(t, pool, ga)
	nb, _, _ := groupRow(t, pool, gb)
	if na == nb {
		t.Fatalf("precondition: both DOs share group %d", ga)
	}
	beforeTagihan := groupTotal(t, pool, ga).Add(groupTotal(t, pool, gb))

	// --- merge: ga absorbs gb
	merged, err := l.Merge(ctx, lhpp.MergeInput{GroupIDs: []int64{ga, gb}, Reason: "satu rute"}, op0)
	if err != nil {
		t.Fatalf("Merge: %v", err)
	}
	if merged.ID != ga {
		t.Errorf("merged group id = %d, want the target %d", merged.ID, ga)
	}
	if merged.LHPPNumber != na {
		t.Errorf("merged number = %q, want the target's %q — a printed sheet keeps its identity", merged.LHPPNumber, na)
	}
	if merged.DOCount != 2 {
		t.Errorf("merged DOCount = %d, want 2", merged.DOCount)
	}
	if merged.DateFrom != "2026-09-20" || merged.DateTo != "2026-09-25" {
		t.Errorf("merged range = %q..%q, want 2026-09-20..2026-09-25", merged.DateFrom, merged.DateTo)
	}
	if got := members1(t, pool, ga); got != 2 {
		t.Errorf("merged group members = %d, want 2", got)
	}
	if got := members1(t, pool, gb); got != 0 {
		t.Errorf("emptied source members = %d, want 0", got)
	}
	if _, status, _ := groupRow(t, pool, gb); status != lhpp.GroupVoided {
		t.Errorf("emptied source status = %q, want voided (void IS the empty-group mechanism)", status)
	}
	// The emptied group must have a void stamp, not just a status flip.
	var voidedAt *string
	if err := pool.QueryRow(ctx, `SELECT voided_at::text FROM lhpp_groups WHERE id=$1`, gb).Scan(&voidedAt); err != nil {
		t.Fatal(err)
	}
	if voidedAt == nil {
		t.Error("voided source has voided_at NULL; the CHECK on lhpp_groups would have rejected that")
	}
	// Get still reaches the voided group so the retired number stays auditable.
	retired, err := l.Get(ctx, gb)
	if err != nil {
		t.Fatalf("Get(voided group): %v", err)
	}
	if retired.LHPPNumber != nb {
		t.Errorf("voided group number = %q, want the original %q", retired.LHPPNumber, nb)
	}
	if retired.GroupStatus != lhpp.GroupVoided {
		t.Errorf("voided group GroupStatus = %q, want %q", retired.GroupStatus, lhpp.GroupVoided)
	}
	// Regrouping must not move money.
	afterTagihan := groupTotal(t, pool, ga)
	if !afterTagihan.Equal(beforeTagihan) {
		t.Errorf("total tagihan changed across merge: %s -> %s", beforeTagihan, afterTagihan)
	}
	if countHistory(t, pool, b[0].DOID, lhpp.ActionMerged) != 1 {
		t.Error("merging DO has no single 'merged' history row")
	}

	// --- split: undo it
	restored, err := l.Split(ctx, ga, lhpp.SplitInput{DOIDs: []int64{b[0].DOID}, Reason: "pisah lagi"}, op0)
	if err != nil {
		t.Fatalf("Split: %v", err)
	}
	if restored.LHPPNumber == na || restored.LHPPNumber == nb {
		t.Errorf("split target reused an old number %q; it must be freshly allocated", restored.LHPPNumber)
	}
	if got := members1(t, pool, ga); got != 1 {
		t.Errorf("after split, source members = %d, want 1", got)
	}
	if got := members1(t, pool, restored.ID); got != 1 {
		t.Errorf("split target members = %d, want 1", got)
	}
	// Membership restored: each DO is alone on a sheet, and no number changed.
	if groupOf(t, pool, a[0].DOID) != ga {
		t.Error("DO A is no longer on its original group after the round trip")
	}
	if groupOf(t, pool, b[0].DOID) != restored.ID {
		t.Error("DO B is not on the split target after the round trip")
	}
	if number, _, _ := groupRow(t, pool, ga); number != na {
		t.Errorf("source number = %q after round trip, want the unchanged %q", number, na)
	}
	if number, status, _ := groupRow(t, pool, gb); number != nb || status != lhpp.GroupVoided {
		t.Errorf("retired group = (%q,%q), want the unchanged (%q,voided)", number, status, nb)
	}
	if !groupTotal(t, pool, ga).Add(groupTotal(t, pool, restored.ID)).Equal(beforeTagihan) {
		t.Error("total tagihan changed across the merge+split round trip")
	}
	if countHistory(t, pool, b[0].DOID, lhpp.ActionSplit) != 1 {
		t.Error("splitting DO has no single 'split' history row")
	}
}

// op0 is the actor id used where the test is not about attribution.
const op0 = int64(0)

func members1(t *testing.T, pool *pgxpool.Pool, id int64) int {
	t.Helper()
	return len(groupMembers(t, pool, id))
}

func groupTotal(t *testing.T, pool *pgxpool.Pool, id int64) decimal.Decimal {
	t.Helper()
	// Sum from the invoices behind the members, i.e. what the sheet would show
	// once the cancelled-invoice rule is applied.
	var total decimal.Decimal
	if err := pool.QueryRow(context.Background(), `
		SELECT COALESCE(sum(i.total_amount), 0) FROM lhpp_group_items gi
		JOIN invoices i ON i.sales_order_id = (SELECT sales_order_id FROM delivery_orders WHERE id = gi.delivery_order_id)
		WHERE gi.group_id = $1`, id).Scan(&total); err != nil {
		t.Fatal(err)
	}
	return total
}

// TestLHPPGroupConstraint_RejectsSecondMembershipForOneDO asserts the invariant
// the schema exists for, by CONSTRAINT NAME: a DO cannot be in two groups, and
// the error is the named one the service turns into 409.
func TestLHPPGroupConstraint_RejectsSecondMembershipForOneDO(t *testing.T) {
	pool := testPool(t)
	setUp(t, pool)
	ctx := context.Background()
	cust := seedCustomer(t, pool)
	wh := seedWarehouse(t, pool)
	driver := seedUser(t, pool)
	if _, err := pool.Exec(ctx, `
		INSERT INTO fleet (plate_number, vehicle_name, is_active) VALUES ($1, 'Truck', true)`,
		unique("PLATE")); err != nil {
		t.Fatal(err)
	}
	do := seedDispatchedDOs(t, pool, cust, wh, driver, 1, "2026-09-20", 1_000_000)

	l := lhppService(pool)
	if err := l.Grouper(ctx, pool, driver, do[0].DOID, "2026-09-20"); err != nil {
		t.Fatal(err)
	}
	other := seedDispatchedDOs(t, pool, cust, wh, driver, 1, "2026-09-21", 1_000_000)
	if err := l.Grouper(ctx, pool, driver, other[0].DOID, "2026-09-21"); err != nil {
		t.Fatal(err)
	}

	_, err := pool.Exec(ctx, `
		INSERT INTO lhpp_group_items (group_id, delivery_order_id) VALUES ($1, $2)`,
		groupOf(t, pool, other[0].DOID), do[0].DOID)
	var pgErr *pgconn.PgError
	if !errors.As(err, &pgErr) {
		t.Fatalf("second membership err = %v, want a *pgconn.PgError", err)
	}
	if pgErr.Code != "23505" {
		t.Errorf("SQLSTATE = %q, want 23505 (unique_violation)", pgErr.Code)
	}
	if pgErr.ConstraintName != "lhpp_group_items_do_unique" {
		// The name is the contract, not the constraint-vs-index shape:
		// PostgreSQL reports the violating index's name either way, so
		// renaming the constraint is what silently downgrades a 409 into a
		// 500, and that is what this assertion is here to catch.
		t.Errorf("constraint = %q, want lhpp_group_items_do_unique — isDOAlreadyGrouped matches on this exact name", pgErr.ConstraintName)
	}
	// And a group must never be deletable while it exists: the FK is NO ACTION,
	// so a raw DELETE is rejected rather than silently cascading the membership.
	_, err = pool.Exec(ctx, `DELETE FROM lhpp_groups WHERE id=$1`, groupOf(t, pool, do[0].DOID))
	if err == nil {
		t.Error("raw DELETE on lhpp_groups succeeded; it must be refused so a group can only be voided")
	}
}

// TestLHPPGroupConcurrentMerge_ExactlyOneWinner races two merges that both try
// to claim the same DO. The DO ends up in exactly one group, exactly one merge
// reports success, and the loser surfaces ErrDOAlreadyInAnotherGroup rather than
// a 500.
func TestLHPPGroupConcurrentMerge_ExactlyOneWinner(t *testing.T) {
	pool := testPool(t)
	setUp(t, pool)
	ctx := context.Background()
	cust := seedCustomer(t, pool)
	wh := seedWarehouse(t, pool)
	driver := seedUser(t, pool)
	if _, err := pool.Exec(ctx, `
		INSERT INTO fleet (plate_number, vehicle_name, is_active) VALUES ($1, 'Truck', true)`,
		unique("PLATE")); err != nil {
		t.Fatal(err)
	}
	// Three single-DO sheets: two contenders plus the DO being fought over.
	contenders := seedDispatchedDOs(t, pool, cust, wh, driver, 2, "2026-09-20", 1_000_000)
	prize := seedDispatchedDOs(t, pool, cust, wh, driver, 1, "2026-09-21", 2_000_000)

	l := lhppService(pool)
	for _, d := range append(contenders, prize...) {
		if err := l.Grouper(ctx, pool, driver, d.DOID, d.Date); err != nil {
			t.Fatal(err)
		}
	}
	g1 := groupOf(t, pool, contenders[0].DOID)
	g2 := groupOf(t, pool, contenders[1].DOID)
	gp := groupOf(t, pool, prize[0].DOID)

	// Both racers merge the prize group into their own group, at the same time.
	var wg sync.WaitGroup
	errs := make([]error, 2)
	start := make(chan struct{})
	for i, target := range []int64{g1, g2} {
		wg.Add(1)
		go func(i int, target int64) {
			defer wg.Done()
			<-start
			_, errs[i] = l.Merge(ctx, lhpp.MergeInput{GroupIDs: []int64{target, gp}}, op0)
		}(i, target)
	}
	close(start)
	wg.Wait()

	wins := 0
	for _, err := range errs {
		switch {
		case err == nil:
			wins++
		case errors.Is(err, lhpp.ErrDOAlreadyInAnotherGroup),
			errors.Is(err, lhpp.ErrConcurrentGrouping),
			// Losing the race means the group you were merging out of has just
			// been emptied and retired by the winner, or held nothing to move.
			// Both are clean 4xx business-rule answers to a stale read, which is
			// exactly what a 409/422 is for — never a 500.
			errors.Is(err, lhpp.ErrGroupNotActive),
			errors.Is(err, lhpp.ErrMergeNoMembers):
		default:
			t.Errorf("racer error = %v, want nil or a 4xx-mapped conflict error", err)
		}
	}
	if wins != 1 {
		t.Errorf("merge winners = %d, want exactly 1 (errors: %v)", wins, errs)
	}
	// The invariant that must hold no matter how the race went.
	if got := countRows(t, pool, `SELECT count(*) FROM lhpp_group_items WHERE delivery_order_id=$1`, prize[0].DOID); got != 1 {
		t.Errorf("prize DO sits in %d groups after the race, want exactly 1", got)
	}
	if got := countRows(t, pool, `SELECT count(DISTINCT lhpp_number) FROM lhpp_groups WHERE status='active'`); got != 2 {
		t.Errorf("active groups = %d, want 2 (one emptied and voided)", got)
	}
}

// TestLHPPReDispatch_RefusedForMultiMemberGroupAllowedForSingle pins the
// re-dispatch guard: driver_id is set from the first member and is immutable
// once the group holds more than one DO.
func TestLHPPReDispatch_RefusedForMultiMemberGroupAllowedForSingle(t *testing.T) {
	pool := testPool(t)
	setUp(t, pool)
	ctx := context.Background()
	cust := seedCustomer(t, pool)
	wh := seedWarehouse(t, pool)
	driverA := seedUser(t, pool)
	driverB := seedUser(t, pool)
	if _, err := pool.Exec(ctx, `
		INSERT INTO fleet (plate_number, vehicle_name, is_active) VALUES ($1, 'Truck', true)`,
		unique("PLATE")); err != nil {
		t.Fatal(err)
	}
	do1 := seedDispatchedDOs(t, pool, cust, wh, driverA, 1, "2026-09-20", 1_000_000)
	do2 := seedDispatchedDOs(t, pool, cust, wh, driverA, 1, "2026-09-21", 2_000_000)
	do3 := seedDispatchedDOs(t, pool, cust, wh, driverA, 1, "2026-09-22", 3_000_000)

	l := lhppService(pool)
	all := append(append(do1, do2...), do3...)
	for _, d := range all {
		if err := l.Grouper(ctx, pool, driverA, d.DOID, d.Date); err != nil {
			t.Fatal(err)
		}
	}

	// Multi-member group first, while every sheet still belongs to driverA — a
	// group is one driver, so building it after a driver change would (correctly)
	// be refused as a cross-driver merge.
	g2 := groupOf(t, pool, do2[0].DOID)
	g3 := groupOf(t, pool, do3[0].DOID)
	multi, err := l.Merge(ctx, lhpp.MergeInput{GroupIDs: []int64{g2, g3}}, op0)
	if err != nil {
		t.Fatalf("merge: %v", err)
	}
	if got := members1(t, pool, multi.ID); got != 2 {
		t.Fatalf("multi-member group has %d members, want 2", got)
	}

	// Single-member: the driver may follow, and a driver_changed row is written.
	g1 := groupOf(t, pool, do1[0].DOID)
	num1, _, _ := groupRow(t, pool, g1)
	if err := l.Grouper(ctx, pool, driverB, do1[0].DOID, "2026-09-20"); err != nil {
		t.Fatalf("single-member re-dispatch to another driver: %v", err)
	}
	if _, _, drv := groupRow(t, pool, g1); drv != driverB {
		t.Errorf("single-member group driver = %d, want the new driver %d", drv, driverB)
	}
	if number, _, _ := groupRow(t, pool, g1); number != num1 {
		t.Errorf("single-member group number = %q, want the unchanged %q", number, num1)
	}
	if got := countHistory(t, pool, do1[0].DOID, lhpp.ActionDriverChanged); got != 1 {
		t.Errorf("driver_changed rows = %d, want 1", got)
	}

	// Multi-member: refused, and the error is the one CreateDispatch maps to 422.
	// The group already belongs to driverA, so the re-dispatch has to name a
	// DIFFERENT driver to be a change at all.
	before, _, _ := groupRow(t, pool, multi.ID)
	err = l.Grouper(ctx, pool, driverB, do2[0].DOID, "2026-09-21")
	if !errors.Is(err, distribution.ErrDOAlreadyBatched) {
		t.Fatalf("multi-member re-dispatch err = %v, want ErrDOAlreadyBatched (422)", err)
	}
	if number, _, _ := groupRow(t, pool, multi.ID); number != before {
		t.Errorf("refused re-dispatch changed the number: %q -> %q", before, number)
	}
	if got := countHistory(t, pool, do2[0].DOID, lhpp.ActionDriverChanged); got != 0 {
		t.Errorf("driver_changed rows after a refused re-dispatch = %d, want 0", got)
	}
	// And the PATCH surface enforces the same rule.
	if _, err := l.ChangeDriver(ctx, multi.ID, lhpp.UpdateGroupInput{DriverID: driverB}, op0); !errors.Is(err, lhpp.ErrGroupDriverLocked) {
		t.Errorf("PATCH driver on a multi-member group = %v, want ErrGroupDriverLocked", err)
	}
}

// TestLHPPVoid_ReleasesDOsForRebatching: voiding is the empty-group mechanism,
// and deleting the membership rows is what lets a DO be batched again — the
// unique constraint only constrains rows that exist.
func TestLHPPVoid_ReleasesDOsForRebatching(t *testing.T) {
	pool := testPool(t)
	setUp(t, pool)
	ctx := context.Background()
	cust := seedCustomer(t, pool)
	wh := seedWarehouse(t, pool)
	driver := seedUser(t, pool)
	if _, err := pool.Exec(ctx, `
		INSERT INTO fleet (plate_number, vehicle_name, is_active) VALUES ($1, 'Truck', true)`,
		unique("PLATE")); err != nil {
		t.Fatal(err)
	}
	do := seedDispatchedDOs(t, pool, cust, wh, driver, 1, "2026-09-20", 1_000_000)

	l := lhppService(pool)
	if err := l.Grouper(ctx, pool, driver, do[0].DOID, "2026-09-20"); err != nil {
		t.Fatal(err)
	}
	g := groupOf(t, pool, do[0].DOID)
	num, _, _ := groupRow(t, pool, g)

	if err := l.Void(ctx, g, lhpp.VoidInput{Reason: "salah input"}, op0); err != nil {
		t.Fatalf("Void: %v", err)
	}
	if _, status, _ := groupRow(t, pool, g); status != lhpp.GroupVoided {
		t.Errorf("status after void = %q, want voided", status)
	}
	if got := countRows(t, pool, `SELECT count(*) FROM lhpp_group_items WHERE group_id=$1`, g); got != 0 {
		t.Errorf("membership rows after void = %d, want 0", got)
	}
	if got := countHistory(t, pool, do[0].DOID, lhpp.ActionVoided); got != 1 {
		t.Errorf("voided history rows = %d, want 1", got)
	}
	// List must not show it, but Get still must.
	if rows, _, err := l.List(ctx, lhpp.ListFilter{Page: 1, PerPage: 50}); err != nil {
		t.Fatal(err)
	} else {
		for _, r := range rows {
			if r.ID == g {
				t.Error("List returned a voided group; it must filter status='active'")
			}
		}
	}
	retired, err := l.Get(ctx, g)
	if err != nil || retired.LHPPNumber != num {
		t.Errorf("Get(voided) = %v, %v; want the retired number %q to stay auditable", retired, err, num)
	}
	// Released: the DO can be batched again, under a NEW number.
	if err := l.Grouper(ctx, pool, driver, do[0].DOID, "2026-09-20"); err != nil {
		t.Fatalf("re-batching a released DO: %v", err)
	}
	g2 := groupOf(t, pool, do[0].DOID)
	if g2 == g {
		t.Error("re-batching reused the voided group; a released DO needs a fresh sheet")
	}
	if num2, status, _ := groupRow(t, pool, g2); status != lhpp.GroupActive || num2 == num {
		t.Errorf("new group = (%q,%q), want a fresh active number different from %q", num2, status, num)
	}
	// Voiding a second time is refused rather than silently accepted.
	if err := l.Void(ctx, g, lhpp.VoidInput{}, op0); !errors.Is(err, lhpp.ErrGroupNotActive) {
		t.Errorf("second void err = %v, want ErrGroupNotActive", err)
	}
}

// TestLHPPRecordPayment_UsesPersistedNumber pins the number contract at the
// payment: the payment's reference_number is the number STORED on the group, and
// merging another DO in does not change it.
func TestLHPPRecordPayment_UsesPersistedNumber(t *testing.T) {
	pool := testPool(t)
	setUp(t, pool)
	ctx := context.Background()
	cust := seedCustomer(t, pool)
	wh := seedWarehouse(t, pool)
	driver := seedUser(t, pool)
	if _, err := pool.Exec(ctx, `
		INSERT INTO fleet (plate_number, vehicle_name, is_active) VALUES ($1, 'Truck', true)`,
		unique("PLATE")); err != nil {
		t.Fatal(err)
	}
	do1 := seedDispatchedDOs(t, pool, cust, wh, driver, 1, "2026-09-20", 1_000_000)
	do2 := seedDispatchedDOs(t, pool, cust, wh, driver, 1, "2026-09-25", 2_000_000)

	l := lhppService(pool)
	for _, d := range append(do1, do2...) {
		if err := l.Grouper(ctx, pool, driver, d.DOID, d.Date); err != nil {
			t.Fatal(err)
		}
	}
	g1 := groupOf(t, pool, do1[0].DOID)
	g2 := groupOf(t, pool, do2[0].DOID)
	num1, _, _ := groupRow(t, pool, g1)

	if _, err := l.RecordPayment(ctx, g1, lhpp.PaymentInput{
		DOID:   do1[0].DOID,
		Amount: decimal.NewFromInt(400_000),
	}); err != nil {
		t.Fatalf("RecordPayment: %v", err)
	}
	ref := paymentRef(t, pool, do1[0].Invoice)
	if ref != num1 {
		t.Errorf("payment reference_number = %q, want the persisted group number %q (never recomputed)", ref, num1)
	}

	// A merge in another DO must not rewrite an already-stamped payment.
	if _, err := l.Merge(ctx, lhpp.MergeInput{GroupIDs: []int64{g1, g2}}, op0); err != nil {
		t.Fatalf("Merge: %v", err)
	}
	if after := paymentRef(t, pool, do1[0].Invoice); after != ref {
		t.Errorf("payment reference_number changed after a merge: %q -> %q", ref, after)
	}
	if number, _, _ := groupRow(t, pool, g1); number != num1 {
		t.Errorf("group number = %q after merge, want the unchanged %q", number, num1)
	}
	// And the collected money is still there: regrouping moves buckets, not cash.
	if got := invoiceRemaining(t, pool, do1[0].Invoice); got.String() != "600000" {
		t.Errorf("invoice remaining after payment = %s, want 600000", got)
	}
}

func paymentRef(t *testing.T, pool *pgxpool.Pool, invoiceID int64) string {
	t.Helper()
	// payable_type is a single-quoted PHP class name, so the backslashes are
	// literal: a Go raw string keeps them as written.
	var ref *string
	if err := pool.QueryRow(context.Background(), `
		SELECT reference_number FROM payments
		WHERE payable_type='App\Models\Invoice' AND payable_id=$1
		ORDER BY id DESC LIMIT 1`, invoiceID).Scan(&ref); err != nil {
		t.Fatal(err)
	}
	if ref == nil {
		return ""
	}
	return *ref
}

func invoiceRemaining(t *testing.T, pool *pgxpool.Pool, invoiceID int64) decimal.Decimal {
	t.Helper()
	var d decimal.Decimal
	if err := pool.QueryRow(context.Background(),
		`SELECT remaining_amount FROM invoices WHERE id=$1`, invoiceID).Scan(&d); err != nil {
		t.Fatal(err)
	}
	return d
}

// applyBackfill re-runs 000101's three DML statements (backfill, membership,
// sequence) so a test can prove they are idempotent without re-running the whole
// file, which the runner would skip as already applied.
func applyBackfill(t *testing.T, pool *pgxpool.Pool) {
	t.Helper()
	ctx := context.Background()
	if _, err := pool.Exec(ctx, lhppBackfillGroupsSQL); err != nil {
		t.Fatalf("backfill groups: %v", err)
	}
	if _, err := pool.Exec(ctx, lhppBackfillItemsSQL); err != nil {
		t.Fatalf("backfill items: %v", err)
	}
	if _, err := pool.Exec(ctx, lhppBackfillSeqSQL); err != nil {
		t.Fatalf("backfill sequence: %v", err)
	}
}

// The DML half of migrations/000101_lhpp_manual_groups.sql, kept in sync by
// TestLHPPMigrationSQLMatchesBackfillHelper below — which fails if the migration
// and these copies drift, so the test can never quietly stop testing the real
// statement.
const lhppBackfillGroupsSQL = `
INSERT INTO lhpp_groups (lhpp_number, driver_id, lhpp_date, status)
SELECT 'LHPP-' || to_char(b.bucket, 'YYYYMM') || '-' ||
       lpad((row_number() OVER (PARTITION BY to_char(b.bucket, 'YYYYMM')
                                ORDER BY b.driver_id, b.bucket))::text, 6, '0'),
       b.driver_id,
       b.bucket,
       'active'
FROM (
    SELECT DISTINCT ON (disp.driver_id, do_row.do_date)
           disp.driver_id,
           do_row.do_date AS bucket
    FROM (
        SELECT DISTINCT ON (delivery_order_id) *
        FROM dispatches
        WHERE status = 'dispatched' AND driver_id IS NOT NULL
        ORDER BY delivery_order_id, id DESC
    ) disp
    JOIN delivery_orders do_row ON do_row.id = disp.delivery_order_id
    JOIN sales_orders so ON so.id = do_row.sales_order_id
    JOIN customers c ON c.id = so.customer_id
    ORDER BY disp.driver_id, do_row.do_date
) b
WHERE NOT EXISTS (
    SELECT 1 FROM lhpp_groups g WHERE g.driver_id = b.driver_id AND g.lhpp_date = b.bucket
)
ORDER BY b.bucket, b.driver_id`

const lhppBackfillItemsSQL = `
INSERT INTO lhpp_group_items (group_id, delivery_order_id)
SELECT (SELECT min(g.id) FROM lhpp_groups g
        WHERE g.driver_id = b.driver_id AND g.lhpp_date = b.bucket),
       b.do_id
FROM (
    SELECT DISTINCT ON (do_row.id)
           do_row.id AS do_id,
           disp.driver_id,
           do_row.do_date AS bucket
    FROM (
        SELECT DISTINCT ON (delivery_order_id) *
        FROM dispatches
        WHERE status = 'dispatched' AND driver_id IS NOT NULL
        ORDER BY delivery_order_id, id DESC
    ) disp
    JOIN delivery_orders do_row ON do_row.id = disp.delivery_order_id
    JOIN sales_orders so ON so.id = do_row.sales_order_id
    JOIN customers c ON c.id = so.customer_id
    ORDER BY do_row.id, do_row.do_date
) b
ON CONFLICT (delivery_order_id) DO NOTHING`

const lhppBackfillSeqSQL = `
INSERT INTO document_number_sequences (prefix, doc_date, counter)
SELECT 'LHPP', date_trunc('month', g.lhpp_date)::date,
       max((regexp_match(g.lhpp_number, '-([0-9]{6})$'))[1]::bigint)
FROM lhpp_groups g
WHERE g.lhpp_number ~ '^LHPP-[0-9]{6}-[0-9]{6}$'
GROUP BY 1, 2
ON CONFLICT (prefix, doc_date)
DO UPDATE SET counter = GREATEST(document_number_sequences.counter, EXCLUDED.counter)`

// TestLHPPMigrationSQLMatchesBackfillHelper keeps the copies above honest. The
// backfill tests need to re-run the statements on demand (the migration runner
// would skip an already-applied file), and a hand-maintained copy that silently
// drifts from the real migration would make those tests prove nothing about the
// thing that actually runs at deploy.
func TestLHPPMigrationSQLMatchesBackfillHelper(t *testing.T) {
	raw, err := os.ReadFile(filepath.Join("..", "..", "migrations", "000101_lhpp_manual_groups.sql"))
	if err != nil {
		t.Fatalf("read 000101: %v", err)
	}
	body := string(raw)
	for name, stmt := range map[string]string{
		"backfill groups":   lhppBackfillGroupsSQL,
		"backfill items":    lhppBackfillItemsSQL,
		"backfill sequence": lhppBackfillSeqSQL,
	} {
		normalized := strings.Join(strings.Fields(stmt), " ")
		if !strings.Contains(strings.Join(strings.Fields(body), " "), normalized) {
			t.Errorf("the %s statement used by these tests has drifted from migrations/000101_lhpp_manual_groups.sql; update both together", name)
		}
	}
}