Merge branch 'fix/jamali' into feat/fifo-ar

add excel export for purchase supplier report

See merge request mbugroup/lti-api!563

internal/common/service/common.fifo_payment.service.go

@@ -0,0 +1,393 @@
+package service
+
+import (
+	"context"
+	"fmt"
+	"math"
+	"strings"
+	"time"
+
+	"github.com/sirupsen/logrus"
+	entity "gitlab.com/mbugroup/lti-api.git/internal/entities"
+	"gitlab.com/mbugroup/lti-api.git/internal/utils"
+	"gorm.io/gorm"
+)
+
+// ParentKind enumerasi parent yang punya grand_total dari SUM children.
+type ParentKind string
+
+const (
+	ParentKindPurchase  ParentKind = "PURCHASE"
+	ParentKindMarketing ParentKind = "MARKETING"
+	ParentKindExpense   ParentKind = "EXPENSE"
+)
+
+// AllocationKind enumerasi sub-row anak target FIFO allocation.
+type AllocationKind string
+
+const (
+	AllocKindPurchaseItem             AllocationKind = "PURCHASE_ITEM"
+	AllocKindMarketingDeliveryProduct AllocationKind = "MDP"
+	AllocKindExpenseRealization       AllocationKind = "EXPENSE_REALIZATION"
+)
+
+// fifoEpsilon untuk float comparison saat FIFO matching.
+const fifoEpsilon = 0.001
+
+// FifoPaymentService meng-orchestrate FIFO allocation antara payments dan
+// sub-row anak (purchase_items / marketing_delivery_products / expense_realizations).
+type FifoPaymentService interface {
+	// ReallocateForParty wipe allocations untuk semua payment party tsb,
+	// lalu re-FIFO dari history (sort children by date ASC, payments by payment_date ASC).
+	// Caller WAJIB pass tx untuk konsistensi dengan mutasi upstream.
+	ReallocateForParty(ctx context.Context, tx *gorm.DB, partyType string, partyID uint) error
+
+	// RecomputeGrandTotal refresh parent.grand_total = SUM children eligible amount.
+	RecomputeGrandTotal(ctx context.Context, tx *gorm.DB, kind ParentKind, parentID uint) error
+}
+
+type fifoPaymentService struct {
+	db     *gorm.DB
+	logger *logrus.Logger
+}
+
+func NewFifoPaymentService(db *gorm.DB, logger *logrus.Logger) FifoPaymentService {
+	if logger == nil {
+		logger = logrus.StandardLogger()
+	}
+	return &fifoPaymentService{db: db, logger: logger}
+}
+
+func (s *fifoPaymentService) txOrDB(tx *gorm.DB) *gorm.DB {
+	if tx != nil {
+		return tx
+	}
+	return s.db
+}
+
+type childRow struct {
+	Kind      AllocationKind
+	ChildID   uint64
+	Amount    float64
+	Remaining float64
+}
+
+type paymentRow struct {
+	ID      uint
+	Nominal float64
+	Date    time.Time
+}
+
+// ReallocateForParty acquire advisory lock then perform full re-FIFO.
+// Jika tx nil, function buka transaction sendiri (advisory lock harus dalam TX).
+func (s *fifoPaymentService) ReallocateForParty(ctx context.Context, tx *gorm.DB, partyType string, partyID uint) error {
+	if partyID == 0 {
+		return nil
+	}
+	party := strings.ToUpper(strings.TrimSpace(partyType))
+	if party != string(utils.PaymentPartyCustomer) && party != string(utils.PaymentPartySupplier) {
+		return fmt.Errorf("fifoPayment: invalid party_type %q", partyType)
+	}
+	if tx == nil {
+		return s.db.WithContext(ctx).Transaction(func(innerTx *gorm.DB) error {
+			return s.reallocateInTx(ctx, innerTx, party, partyID)
+		})
+	}
+	return s.reallocateInTx(ctx, tx, party, partyID)
+}
+
+func (s *fifoPaymentService) reallocateInTx(ctx context.Context, tx *gorm.DB, party string, partyID uint) error {
+	db := tx.WithContext(ctx)
+
+	// Advisory lock per (party_type, party_id) — 1-arg form (bigint).
+	// Postgres 2-arg form butuh kedua param int4, sedangkan party_id bisa lebih besar.
+	lockKey := fmt.Sprintf("payment_alloc:%s:%d", party, partyID)
+	if err := db.Exec("SELECT pg_advisory_xact_lock(hashtext(?)::bigint)", lockKey).Error; err != nil {
+		return fmt.Errorf("fifoPayment: advisory lock: %w", err)
+	}
+
+	// Wipe existing allocations untuk semua payment party tsb
+	if err := db.Exec(`
+		DELETE FROM payment_allocations
+		WHERE payment_id IN (
+			SELECT id FROM payments
+			WHERE party_type = ? AND party_id = ? AND deleted_at IS NULL
+		)
+	`, party, partyID).Error; err != nil {
+		return fmt.Errorf("fifoPayment: wipe allocations: %w", err)
+	}
+
+	children, err := s.fetchChildren(ctx, db, party, partyID)
+	if err != nil {
+		return err
+	}
+	if len(children) == 0 {
+		return nil
+	}
+
+	// Fetch SEMUA payments termasuk SALDO_AWAL agar allocation tercatat di DB
+	// (SaldoAwal opening credit harus consume oldest debts; tanpa allocation row,
+	// debt yang ter-cover SaldoAwal akan tampak "Belum Lunas" di report).
+	payments, err := s.fetchAllPayments(ctx, db, party, partyID)
+	if err != nil {
+		return err
+	}
+
+	// Greedy: per payment, alokasi ke children tertua dengan remaining > 0
+	allocs := make([]entity.PaymentAllocation, 0, len(payments))
+	now := time.Now()
+	for _, pay := range payments {
+		remaining := pay.Nominal
+		if remaining <= fifoEpsilon {
+			continue
+		}
+		for i := range children {
+			if remaining <= fifoEpsilon {
+				break
+			}
+			if children[i].Remaining <= fifoEpsilon {
+				continue
+			}
+			used := math.Min(remaining, children[i].Remaining)
+			children[i].Remaining -= used
+			remaining -= used
+
+			alloc := entity.PaymentAllocation{
+				PaymentId:   pay.ID,
+				Amount:      used,
+				AllocatedAt: now,
+			}
+			switch children[i].Kind {
+			case AllocKindPurchaseItem:
+				id := uint(children[i].ChildID)
+				alloc.PurchaseItemId = &id
+			case AllocKindMarketingDeliveryProduct:
+				id := uint(children[i].ChildID)
+				alloc.MarketingDeliveryProductId = &id
+			case AllocKindExpenseRealization:
+				id := children[i].ChildID
+				alloc.ExpenseRealizationId = &id
+			}
+			allocs = append(allocs, alloc)
+		}
+	}
+
+	if len(allocs) == 0 {
+		return nil
+	}
+	// Batch insert allocations
+	if err := db.CreateInBatches(&allocs, 500).Error; err != nil {
+		return fmt.Errorf("fifoPayment: insert allocations: %w", err)
+	}
+	return nil
+}
+
+// fetchChildren return eligible sub-rows sorted by date ASC, id ASC.
+func (s *fifoPaymentService) fetchChildren(ctx context.Context, db *gorm.DB, party string, partyID uint) ([]childRow, error) {
+	if party == string(utils.PaymentPartySupplier) {
+		return s.fetchSupplierChildren(ctx, db, partyID)
+	}
+	return s.fetchCustomerChildren(ctx, db, partyID)
+}
+
+func (s *fifoPaymentService) fetchSupplierChildren(ctx context.Context, db *gorm.DB, supplierID uint) ([]childRow, error) {
+	// purchase_items eligible: purchases approval latest step >= Receiving (4), action != REJECTED, received_date IS NOT NULL
+	var purchaseRows []chronoRow
+	purchaseSQL := `
+		SELECT 'PURCHASE_ITEM' AS kind,
+		       pi.id::BIGINT AS child_id,
+		       pi.total_price AS amount,
+		       pi.received_date AS sort_date,
+		       pi.id::BIGINT AS sort_id
+		FROM purchase_items pi
+		JOIN purchases p ON p.id = pi.purchase_id
+		JOIN LATERAL (
+			SELECT a.step_number, a.action
+			FROM approvals a
+			WHERE a.approvable_type = ? AND a.approvable_id = p.id
+			ORDER BY a.action_at DESC, a.id DESC
+			LIMIT 1
+		) la ON TRUE
+		WHERE p.supplier_id = ?
+		  AND p.deleted_at IS NULL
+		  AND pi.received_date IS NOT NULL
+		  AND la.step_number >= ?
+		  AND (la.action IS NULL OR la.action <> ?)
+		  AND pi.total_price > 0
+		ORDER BY pi.received_date ASC, pi.id ASC
+	`
+	if err := db.WithContext(ctx).Raw(purchaseSQL,
+		string(utils.ApprovalWorkflowPurchase),
+		supplierID,
+		uint16(utils.PurchaseStepReceiving),
+		string(entity.ApprovalActionRejected),
+	).Scan(&purchaseRows).Error; err != nil {
+		return nil, fmt.Errorf("fifoPayment: fetch purchase items: %w", err)
+	}
+
+	// expense_realizations via expense_nonstocks → expenses, approval latest step >= Realisasi (5)
+	// Sort pakai e.transaction_date (bukan realization_date) supaya FIFO match dengan tanggal yang
+	// dipakai report sebagai "tanggal dokumen" — user assume FIFO = lunasi yang transaction_date paling tua dulu.
+	var expenseRows []chronoRow
+	expenseSQL := `
+		SELECT 'EXPENSE_REALIZATION' AS kind,
+		       er.id::BIGINT AS child_id,
+		       (er.qty * er.price) AS amount,
+		       e.transaction_date AS sort_date,
+		       er.id::BIGINT AS sort_id
+		FROM expense_realizations er
+		JOIN expense_nonstocks en ON en.id = er.expense_nonstock_id
+		JOIN expenses e ON e.id = en.expense_id
+		JOIN LATERAL (
+			SELECT a.step_number, a.action
+			FROM approvals a
+			WHERE a.approvable_type = ? AND a.approvable_id = e.id
+			ORDER BY a.action_at DESC, a.id DESC
+			LIMIT 1
+		) la ON TRUE
+		WHERE e.supplier_id = ?
+		  AND e.deleted_at IS NULL
+		  AND la.step_number >= ?
+		  AND (la.action IS NULL OR la.action <> ?)
+		  AND (er.qty * er.price) > 0
+		ORDER BY e.transaction_date ASC, e.id ASC, er.id ASC
+	`
+	if err := db.WithContext(ctx).Raw(expenseSQL,
+		string(utils.ApprovalWorkflowExpense),
+		supplierID,
+		uint16(utils.ExpenseStepRealisasi),
+		string(entity.ApprovalActionRejected),
+	).Scan(&expenseRows).Error; err != nil {
+		return nil, fmt.Errorf("fifoPayment: fetch expense realizations: %w", err)
+	}
+
+	// Merge in chronological order (kedua list sudah sorted; merge stable)
+	merged := mergeSortedByDate(purchaseRows, expenseRows)
+	out := make([]childRow, 0, len(merged))
+	for _, r := range merged {
+		out = append(out, childRow{
+			Kind:      AllocationKind(r.Kind),
+			ChildID:   r.ChildID,
+			Amount:    r.Amount,
+			Remaining: r.Amount,
+		})
+	}
+	return out, nil
+}
+
+func (s *fifoPaymentService) fetchCustomerChildren(ctx context.Context, db *gorm.DB, customerID uint) ([]childRow, error) {
+	var mdpRows []chronoRow
+	sql := `
+		SELECT 'MDP' AS kind,
+		       mdp.id::BIGINT AS child_id,
+		       mdp.total_price AS amount,
+		       mdp.delivery_date AS sort_date,
+		       mdp.id::BIGINT AS sort_id
+		FROM marketing_delivery_products mdp
+		JOIN marketing_products mp ON mp.id = mdp.marketing_product_id
+		JOIN marketings m ON m.id = mp.marketing_id
+		WHERE m.customer_id = ?
+		  AND m.deleted_at IS NULL
+		  AND mdp.delivery_date IS NOT NULL
+		  AND mdp.total_price > 0
+		ORDER BY mdp.delivery_date ASC, mdp.id ASC
+	`
+	if err := db.WithContext(ctx).Raw(sql, customerID).Scan(&mdpRows).Error; err != nil {
+		return nil, fmt.Errorf("fifoPayment: fetch marketing delivery products: %w", err)
+	}
+	out := make([]childRow, 0, len(mdpRows))
+	for _, r := range mdpRows {
+		out = append(out, childRow{
+			Kind:      AllocationKind(r.Kind),
+			ChildID:   r.ChildID,
+			Amount:    r.Amount,
+			Remaining: r.Amount,
+		})
+	}
+	return out, nil
+}
+
+// fetchAllPayments return SEMUA payments (termasuk SALDO_AWAL) sort by payment_date ASC, id ASC.
+// SALDO_AWAL diperlakukan sebagai payment tertua agar opening credit otomatis consume oldest debts via FIFO.
+func (s *fifoPaymentService) fetchAllPayments(ctx context.Context, db *gorm.DB, party string, partyID uint) ([]paymentRow, error) {
+	var rows []paymentRow
+	sql := `
+		SELECT id, nominal, payment_date AS date
+		FROM payments
+		WHERE party_type = ? AND party_id = ?
+		  AND deleted_at IS NULL
+		  AND nominal > 0
+		ORDER BY payment_date ASC, id ASC
+	`
+	if err := db.WithContext(ctx).Raw(sql, party, partyID).Scan(&rows).Error; err != nil {
+		return nil, fmt.Errorf("fifoPayment: fetch payments: %w", err)
+	}
+	return rows, nil
+}
+
+// RecomputeGrandTotal refresh parent.grand_total dari SUM children eligible amount.
+func (s *fifoPaymentService) RecomputeGrandTotal(ctx context.Context, tx *gorm.DB, kind ParentKind, parentID uint) error {
+	db := s.txOrDB(tx).WithContext(ctx)
+	if parentID == 0 {
+		return nil
+	}
+	switch kind {
+	case ParentKindPurchase:
+		return db.Exec(`
+			UPDATE purchases p
+			SET grand_total = COALESCE((SELECT SUM(total_price) FROM purchase_items WHERE purchase_id = p.id), 0)
+			WHERE p.id = ?
+		`, parentID).Error
+	case ParentKindMarketing:
+		return db.Exec(`
+			UPDATE marketings m
+			SET grand_total = COALESCE((
+				SELECT SUM(mdp.total_price)
+				FROM marketing_delivery_products mdp
+				JOIN marketing_products mp ON mp.id = mdp.marketing_product_id
+				WHERE mp.marketing_id = m.id AND mdp.delivery_date IS NOT NULL
+			), 0)
+			WHERE m.id = ?
+		`, parentID).Error
+	case ParentKindExpense:
+		return db.Exec(`
+			UPDATE expenses e
+			SET grand_total = COALESCE((
+				SELECT SUM(er.qty * er.price)
+				FROM expense_realizations er
+				JOIN expense_nonstocks en ON en.id = er.expense_nonstock_id
+				WHERE en.expense_id = e.id
+			), 0)
+			WHERE e.id = ?
+		`, parentID).Error
+	default:
+		return fmt.Errorf("fifoPayment: unknown parent kind %q", kind)
+	}
+}
+
+// chronoRow row antara untuk merge sort children.
+type chronoRow struct {
+	Kind     string
+	ChildID  uint64
+	Amount   float64
+	SortDate time.Time
+	SortID   uint64
+}
+
+func mergeSortedByDate(a, b []chronoRow) []chronoRow {
+	out := make([]chronoRow, 0, len(a)+len(b))
+	i, j := 0, 0
+	for i < len(a) && j < len(b) {
+		if a[i].SortDate.Before(b[j].SortDate) ||
+			(a[i].SortDate.Equal(b[j].SortDate) && a[i].SortID < b[j].SortID) {
+			out = append(out, a[i])
+			i++
+		} else {
+			out = append(out, b[j])
+			j++
+		}
+	}
+	out = append(out, a[i:]...)
+	out = append(out, b[j:]...)
+	return out
+}

internal/database/migrations/20260528121631_normalize_warehouse_jamali_10_to_25.down.sql

@@ -0,0 +1,99 @@
+BEGIN;
+
+-- ============================================================
+-- Rollback dynamic via audit snapshots di schema `migration_audit.jamali_w10_*`.
+-- Semua reverse dibaca dari snapshot yang dibuat oleh UP migration —
+-- tidak ada IDs/qty yang hardcode. Robust terhadap data drift antara
+-- dump time dan UP apply time (misalnya row baru warehouse_id=10
+-- yang muncul setelah dump diambil).
+--
+-- LIMITASI: FK relinks di stock_logs / stock_allocations / recording_eggs /
+-- marketing_products / dll. TIDAK direverse di sini (skip audit per-row
+-- untuk hemat storage ~40MB). Setelah down, 9 PW W10 yang di-restore
+-- akan kosong dari child rows (semua child masih pointing ke W25 PW
+-- yang sebelumnya menerima merge). Untuk rollback penuh, restore DB
+-- dari backup pre-migration.
+-- ============================================================
+
+-- Guard: pastikan audit tables ada (kalau tidak, fail-loud)
+DO $$
+BEGIN
+  IF NOT EXISTS (
+    SELECT 1 FROM information_schema.tables
+    WHERE table_schema = 'migration_audit'
+      AND table_name = 'jamali_w10_pw_deleted_snapshot'
+  ) THEN
+    RAISE EXCEPTION 'Audit table migration_audit.jamali_w10_* tidak ditemukan. UP migration belum dijalankan atau audit sudah di-drop. Restore dari DB backup jika perlu.';
+  END IF;
+END $$;
+
+-- 1. Un-soft-delete warehouse 10 (kalau memang di-softdelete oleh UP)
+UPDATE warehouses w
+SET deleted_at = NULL, updated_at = NOW()
+FROM migration_audit.jamali_w10_warehouse_softdeleted a
+WHERE w.id = a.id;
+
+-- 2. Un-soft-delete stock_transfers self-loop yang disoft-delete UP step 7.1
+UPDATE stock_transfers st
+SET deleted_at = NULL, updated_at = NOW()
+FROM migration_audit.jamali_w10_st_softdeleted a
+WHERE st.id = a.id;
+
+-- 3. Reverse stock_transfers redirect (CASE-based dari snapshot was_from_w10/was_to_w10)
+UPDATE stock_transfers st
+SET from_warehouse_id = CASE WHEN a.was_from_w10 THEN 10 ELSE st.from_warehouse_id END,
+    to_warehouse_id   = CASE WHEN a.was_to_w10   THEN 10 ELSE st.to_warehouse_id   END,
+    updated_at = NOW()
+FROM migration_audit.jamali_w10_st_redirected a
+WHERE st.id = a.id;
+
+-- 3b. Self-loop transfers (W10<->W25 awal) juga punya from_warehouse_id=25 atau
+-- to_warehouse_id=25 setelah UP step 7.2. Karena snapshot jamali_w10_st_softdeleted
+-- punya kolom from_warehouse_id & to_warehouse_id asli, pakai itu untuk reverse.
+UPDATE stock_transfers st
+SET from_warehouse_id = 10, updated_at = NOW()
+FROM migration_audit.jamali_w10_st_softdeleted a
+WHERE st.id = a.id AND a.from_warehouse_id = 10;
+
+UPDATE stock_transfers st
+SET to_warehouse_id = 10, updated_at = NOW()
+FROM migration_audit.jamali_w10_st_softdeleted a
+WHERE st.id = a.id AND a.to_warehouse_id = 10;
+
+-- 4. Reverse purchase_items.warehouse_id 25 -> 10
+UPDATE purchase_items
+SET warehouse_id = 10
+WHERE id IN (SELECT id FROM migration_audit.jamali_w10_purchase_items);
+
+-- 5. Reverse W10-only PW (warehouse_id 25 -> 10, restore pfk asli dari snapshot)
+UPDATE product_warehouses pw
+SET warehouse_id = 10, project_flock_kandang_id = a.original_pfk
+FROM migration_audit.jamali_w10_pw_w10only_snapshot a
+WHERE pw.id = a.id;
+
+-- 6. Subtract qty dari W25 PW (reverse merge)
+-- WARNING: kalau W25 qty sudah dikonsumsi pasca-UP (sales/recording/dll),
+-- hasil bisa negatif. Tidak ada CHECK constraint di product_warehouses.qty,
+-- jadi silent. Operator harus verifikasi manual post-down:
+--   SELECT id, qty FROM product_warehouses WHERE qty < 0;
+UPDATE product_warehouses pw
+SET qty = pw.qty - a.merged_qty
+FROM migration_audit.jamali_w10_qty_merge a
+WHERE pw.id = a.target_pw_id;
+
+-- 7. Re-INSERT 9 W10 PW rows yang di-DELETE oleh UP (PK asli + qty asli)
+INSERT INTO product_warehouses (id, product_id, warehouse_id, qty, project_flock_kandang_id)
+SELECT id, product_id, 10, qty, project_flock_kandang_id
+FROM migration_audit.jamali_w10_pw_deleted_snapshot;
+
+-- 8. Cleanup audit tables (drop satu per satu, tidak wildcard untuk safety)
+DROP TABLE migration_audit.jamali_w10_pw_deleted_snapshot;
+DROP TABLE migration_audit.jamali_w10_qty_merge;
+DROP TABLE migration_audit.jamali_w10_pw_w10only_snapshot;
+DROP TABLE migration_audit.jamali_w10_st_softdeleted;
+DROP TABLE migration_audit.jamali_w10_st_redirected;
+DROP TABLE migration_audit.jamali_w10_purchase_items;
+DROP TABLE migration_audit.jamali_w10_warehouse_softdeleted;
+-- Schema migration_audit dipertahankan (bisa dipakai migration lain di masa depan)
+
+COMMIT;

internal/database/migrations/20260528121631_normalize_warehouse_jamali_10_to_25.up.sql

@@ -0,0 +1,241 @@
+BEGIN;
+
+-- ============================================================
+-- Normalisasi warehouse 10 (Jamali NON_AKTIF) -> 25 (Gudang Farm Jamali)
+-- Background: Dua warehouse LOKASI di area & lokasi sama (area_id=6,
+-- location_id=16). W10 sudah ditandai NON_AKTIF tapi masih punya 13
+-- product_warehouses, 3,590 stock_logs, ~790K stock_allocations,
+-- 332 marketing_products, 17 purchase_items, dan 14 stock_transfers.
+-- Migration ini konsolidasikan semua relasi ke W25 lalu soft-delete W10.
+--
+-- Klasifikasi data:
+-- A. 9 product_warehouses W10 overlap dengan W25 (sama product_id, pfk=NULL)
+--    -> merge qty ke W25, relink semua FK ke product_warehouses.id,
+--       lalu DELETE W10 PW rows.
+-- B. 4 product_warehouses W10-only -> UPDATE warehouse_id=25.
+--    Rows 1188/1189/1190 punya pfk=98 (anomali LOKASI, seharusnya NULL
+--    per aturan di CLAUDE.md [2026-05-06]) -> normalisasi sekalian.
+-- C. 17 purchase_items.warehouse_id=10 -> UPDATE 25 (no unique conflict).
+-- D. 3 stock_transfers W10<->W25 (PND-LTI-00107/00109/00119) akan jadi
+--    self-loop W25<->W25 setelah merge -> soft-delete.
+-- E. 12 stock_transfers EGG_FARM_CUTOVER to_warehouse_id=10 -> UPDATE 25.
+-- F. warehouse_id=10 sendiri -> soft-delete.
+--
+-- UP membuat 7 snapshot table di schema `migration_audit.jamali_w10_*`
+-- sebelum mutasi. DOWN baca snapshot itu untuk reverse dynamic (tidak
+-- hardcode IDs/qty), sehingga apapun yang ada di production saat UP
+-- dijalankan akan ter-audit dan ter-reverse. FK relinks
+-- (stock_logs/stock_allocations/dll) TIDAK di-audit (storage ~40MB)
+-- — limitation: tidak bisa di-reverse DOWN, full rollback = DB backup.
+-- ============================================================
+
+-- STEP -1: Buat schema audit + snapshot tables (idempotent rerun via DROP IF EXISTS)
+CREATE SCHEMA IF NOT EXISTS migration_audit;
+
+DROP TABLE IF EXISTS migration_audit.jamali_w10_pw_deleted_snapshot;
+DROP TABLE IF EXISTS migration_audit.jamali_w10_qty_merge;
+DROP TABLE IF EXISTS migration_audit.jamali_w10_pw_w10only_snapshot;
+DROP TABLE IF EXISTS migration_audit.jamali_w10_st_softdeleted;
+DROP TABLE IF EXISTS migration_audit.jamali_w10_st_redirected;
+DROP TABLE IF EXISTS migration_audit.jamali_w10_purchase_items;
+DROP TABLE IF EXISTS migration_audit.jamali_w10_warehouse_softdeleted;
+
+-- Snapshot 9 W10 PW yang akan di-DELETE (overlap dgn W25, pfk=NULL)
+CREATE TABLE migration_audit.jamali_w10_pw_deleted_snapshot AS
+SELECT pw10.id, pw10.product_id, pw10.qty, pw10.project_flock_kandang_id
+FROM product_warehouses pw10
+JOIN product_warehouses pw25
+  ON pw25.product_id = pw10.product_id
+ AND pw25.warehouse_id = 25
+ AND pw25.project_flock_kandang_id IS NULL
+WHERE pw10.warehouse_id = 10 AND pw10.project_flock_kandang_id IS NULL;
+
+-- Snapshot qty delta per W25 target (untuk reverse subtract)
+CREATE TABLE migration_audit.jamali_w10_qty_merge AS
+SELECT pw25.id AS target_pw_id, pw10.id AS source_pw_id, pw10.qty AS merged_qty
+FROM product_warehouses pw10
+JOIN product_warehouses pw25
+  ON pw25.product_id = pw10.product_id
+ AND pw25.warehouse_id = 25
+ AND pw25.project_flock_kandang_id IS NULL
+WHERE pw10.warehouse_id = 10 AND pw10.project_flock_kandang_id IS NULL;
+
+-- Snapshot W10-only PW (yang akan di-UPDATE warehouse_id 10->25)
+CREATE TABLE migration_audit.jamali_w10_pw_w10only_snapshot AS
+SELECT pw10.id, pw10.project_flock_kandang_id AS original_pfk
+FROM product_warehouses pw10
+WHERE pw10.warehouse_id = 10
+  AND pw10.id NOT IN (SELECT id FROM migration_audit.jamali_w10_pw_deleted_snapshot);
+
+-- Snapshot stock_transfers yang akan di-soft-delete (self-loop W10<->W25)
+-- Simpan from/to_warehouse_id asli supaya DOWN bisa reverse direction tepat
+CREATE TABLE migration_audit.jamali_w10_st_softdeleted AS
+SELECT id, movement_number, from_warehouse_id, to_warehouse_id
+FROM stock_transfers
+WHERE deleted_at IS NULL
+  AND ((from_warehouse_id = 10 AND to_warehouse_id = 25)
+    OR (from_warehouse_id = 25 AND to_warehouse_id = 10));
+
+-- Snapshot stock_transfers yang akan di-UPDATE (W10<->other, bukan self-loop)
+CREATE TABLE migration_audit.jamali_w10_st_redirected AS
+SELECT id,
+       (from_warehouse_id = 10) AS was_from_w10,
+       (to_warehouse_id = 10)   AS was_to_w10
+FROM stock_transfers
+WHERE deleted_at IS NULL
+  AND (from_warehouse_id = 10 OR to_warehouse_id = 10)
+  AND id NOT IN (SELECT id FROM migration_audit.jamali_w10_st_softdeleted);
+
+-- Snapshot purchase_items IDs (cheap, ~17 rows)
+CREATE TABLE migration_audit.jamali_w10_purchase_items AS
+SELECT id FROM purchase_items WHERE warehouse_id = 10;
+
+-- Snapshot warehouses soft-delete flag (1 row, kalau memang masih aktif)
+CREATE TABLE migration_audit.jamali_w10_warehouse_softdeleted AS
+SELECT id FROM warehouses WHERE id = 10 AND deleted_at IS NULL;
+
+-- STEP 0: Pre-check sanity (idempotent guards)
+DO $$
+DECLARE v_count INT;
+BEGIN
+  SELECT COUNT(*) INTO v_count FROM warehouses
+  WHERE id IN (10, 25) AND type = 'LOKASI' AND area_id = 6 AND location_id = 16;
+  IF v_count <> 2 THEN
+    RAISE EXCEPTION 'Pre-check: warehouse 10/25 schema mismatch (got % rows)', v_count;
+  END IF;
+
+  SELECT COUNT(*) INTO v_count FROM purchase_items a
+  JOIN purchase_items b ON a.purchase_id = b.purchase_id
+    AND a.product_id = b.product_id
+    AND a.id <> b.id
+  WHERE a.warehouse_id = 10 AND b.warehouse_id = 25;
+  IF v_count > 0 THEN
+    RAISE EXCEPTION 'Pre-check: % purchase_items unique conflict (purchase_id,product_id)', v_count;
+  END IF;
+END $$;
+
+-- STEP 1: Merge qty W10 -> W25 untuk overlap (pfk=NULL)
+UPDATE product_warehouses pw25
+SET qty = pw25.qty + pw10.qty
+FROM product_warehouses pw10
+WHERE pw10.warehouse_id = 10 AND pw10.project_flock_kandang_id IS NULL
+  AND pw25.warehouse_id = 25 AND pw25.project_flock_kandang_id IS NULL
+  AND pw25.product_id = pw10.product_id;
+
+-- STEP 2: Build temp mapping (W10 PW id -> W25 PW id) untuk overlap saja
+CREATE TEMP TABLE _pw_map ON COMMIT DROP AS
+SELECT pw10.id AS old_id, pw25.id AS new_id
+FROM product_warehouses pw10
+JOIN product_warehouses pw25
+  ON pw25.product_id = pw10.product_id
+ AND pw25.warehouse_id = 25
+ AND pw25.project_flock_kandang_id IS NULL
+WHERE pw10.warehouse_id = 10 AND pw10.project_flock_kandang_id IS NULL;
+
+CREATE INDEX ON _pw_map(old_id);
+
+-- STEP 3: Relink semua FK ke product_warehouses.id (hanya rows di _pw_map)
+UPDATE stock_logs SET product_warehouse_id = m.new_id
+  FROM _pw_map m WHERE stock_logs.product_warehouse_id = m.old_id;
+
+UPDATE stock_allocations SET product_warehouse_id = m.new_id
+  FROM _pw_map m WHERE stock_allocations.product_warehouse_id = m.old_id;
+
+UPDATE recording_eggs SET product_warehouse_id = m.new_id
+  FROM _pw_map m WHERE recording_eggs.product_warehouse_id = m.old_id;
+
+UPDATE recording_stocks SET product_warehouse_id = m.new_id
+  FROM _pw_map m WHERE recording_stocks.product_warehouse_id = m.old_id;
+
+UPDATE recording_depletions SET product_warehouse_id = m.new_id
+  FROM _pw_map m WHERE recording_depletions.product_warehouse_id = m.old_id;
+UPDATE recording_depletions SET source_product_warehouse_id = m.new_id
+  FROM _pw_map m WHERE recording_depletions.source_product_warehouse_id = m.old_id;
+
+UPDATE adjustment_stocks SET product_warehouse_id = m.new_id
+  FROM _pw_map m WHERE adjustment_stocks.product_warehouse_id = m.old_id;
+
+UPDATE marketing_products SET product_warehouse_id = m.new_id
+  FROM _pw_map m WHERE marketing_products.product_warehouse_id = m.old_id;
+
+UPDATE marketing_delivery_products SET product_warehouse_id = m.new_id
+  FROM _pw_map m WHERE marketing_delivery_products.product_warehouse_id = m.old_id;
+
+UPDATE project_chickins SET product_warehouse_id = m.new_id
+  FROM _pw_map m WHERE project_chickins.product_warehouse_id = m.old_id;
+
+UPDATE project_chickin_details SET product_warehouse_id = m.new_id
+  FROM _pw_map m WHERE project_chickin_details.product_warehouse_id = m.old_id;
+
+UPDATE project_flock_populations SET product_warehouse_id = m.new_id
+  FROM _pw_map m WHERE project_flock_populations.product_warehouse_id = m.old_id;
+
+UPDATE laying_transfers SET source_product_warehouse_id = m.new_id
+  FROM _pw_map m WHERE laying_transfers.source_product_warehouse_id = m.old_id;
+
+UPDATE laying_transfer_sources SET product_warehouse_id = m.new_id
+  FROM _pw_map m WHERE laying_transfer_sources.product_warehouse_id = m.old_id;
+
+UPDATE laying_transfer_targets SET product_warehouse_id = m.new_id
+  FROM _pw_map m WHERE laying_transfer_targets.product_warehouse_id = m.old_id;
+
+UPDATE stock_transfer_details SET source_product_warehouse_id = m.new_id
+  FROM _pw_map m WHERE stock_transfer_details.source_product_warehouse_id = m.old_id;
+UPDATE stock_transfer_details SET dest_product_warehouse_id = m.new_id
+  FROM _pw_map m WHERE stock_transfer_details.dest_product_warehouse_id = m.old_id;
+
+UPDATE purchase_items SET product_warehouse_id = m.new_id
+  FROM _pw_map m WHERE purchase_items.product_warehouse_id = m.old_id;
+
+-- FIFO v2 tables (kosong di dump 2026-05-25, defensive)
+UPDATE fifo_stock_v2_operation_log SET product_warehouse_id = m.new_id
+  FROM _pw_map m WHERE fifo_stock_v2_operation_log.product_warehouse_id = m.old_id;
+UPDATE fifo_stock_v2_reflow_checkpoints SET product_warehouse_id = m.new_id
+  FROM _pw_map m WHERE fifo_stock_v2_reflow_checkpoints.product_warehouse_id = m.old_id;
+UPDATE fifo_stock_v2_shadow_allocations SET product_warehouse_id = m.new_id
+  FROM _pw_map m WHERE fifo_stock_v2_shadow_allocations.product_warehouse_id = m.old_id;
+
+-- STEP 4: Hard-delete W10 PW yang sudah merged (9 rows expected)
+DELETE FROM product_warehouses WHERE id IN (SELECT old_id FROM _pw_map);
+
+-- STEP 5: Sisa W10 PW (4 rows: 1188/1189/1190/1196) -> warehouse_id=25,
+-- pfk dinormalisasi ke NULL sekalian (LOKASI rule)
+UPDATE product_warehouses
+SET warehouse_id = 25, project_flock_kandang_id = NULL
+WHERE warehouse_id = 10;
+
+-- STEP 6: purchase_items.warehouse_id (17 rows)
+UPDATE purchase_items SET warehouse_id = 25 WHERE warehouse_id = 10;
+
+-- STEP 7: stock_transfers
+-- 7.1 Soft-delete self-loop (W10<->W25 akan jadi W25<->W25)
+UPDATE stock_transfers
+SET deleted_at = NOW(), updated_at = NOW()
+WHERE deleted_at IS NULL
+  AND ((from_warehouse_id = 10 AND to_warehouse_id = 25)
+    OR (from_warehouse_id = 25 AND to_warehouse_id = 10));
+
+-- 7.2 Sisa W10<->other -> 25 (12 EGG_FARM_CUTOVER ke W10)
+UPDATE stock_transfers SET from_warehouse_id = 25, updated_at = NOW() WHERE from_warehouse_id = 10;
+UPDATE stock_transfers SET to_warehouse_id = 25, updated_at = NOW() WHERE to_warehouse_id = 10;
+
+-- STEP 8: Soft-delete warehouse 10 sendiri
+UPDATE warehouses SET deleted_at = NOW(), updated_at = NOW()
+WHERE id = 10 AND deleted_at IS NULL;
+
+-- STEP 9: Post-check (fail-fast jika ada residu)
+DO $$
+DECLARE v_count INT;
+BEGIN
+  SELECT COUNT(*) INTO v_count FROM product_warehouses WHERE warehouse_id = 10;
+  IF v_count <> 0 THEN RAISE EXCEPTION 'product_warehouses W10 residual %', v_count; END IF;
+
+  SELECT COUNT(*) INTO v_count FROM purchase_items WHERE warehouse_id = 10;
+  IF v_count <> 0 THEN RAISE EXCEPTION 'purchase_items W10 residual %', v_count; END IF;
+
+  SELECT COUNT(*) INTO v_count FROM stock_transfers
+  WHERE deleted_at IS NULL AND (from_warehouse_id = 10 OR to_warehouse_id = 10);
+  IF v_count <> 0 THEN RAISE EXCEPTION 'stock_transfers W10 residual %', v_count; END IF;
+END $$;
+
+COMMIT;

internal/database/migrations/20260528123243_fix_stock_log_drift_jamali_merge.down.sql

@@ -0,0 +1,29 @@
+BEGIN;
+
+-- ============================================================
+-- Rollback stock_log drift fix: DELETE corrective rows yang di-insert UP.
+-- IDs ditarik dari audit table `migration_audit.jamali_w10_stocklog_corrections`.
+-- Setelah delete, `last_stock_log.stock` kembali ke nilai pre-fix (drift muncul lagi).
+-- ============================================================
+
+-- Guard: audit table harus ada
+DO $$
+BEGIN
+  IF NOT EXISTS (
+    SELECT 1 FROM information_schema.tables
+    WHERE table_schema = 'migration_audit'
+      AND table_name = 'jamali_w10_stocklog_corrections'
+  ) THEN
+    RAISE EXCEPTION
+      'Audit table migration_audit.jamali_w10_stocklog_corrections tidak ditemukan. UP belum dijalankan atau audit sudah di-drop.';
+  END IF;
+END $$;
+
+-- DELETE corrective stock_logs yang di-insert oleh UP
+DELETE FROM stock_logs
+WHERE id IN (SELECT stock_log_id FROM migration_audit.jamali_w10_stocklog_corrections);
+
+-- Cleanup audit table
+DROP TABLE migration_audit.jamali_w10_stocklog_corrections;
+
+COMMIT;

internal/database/migrations/20260528123243_fix_stock_log_drift_jamali_merge.up.sql

@@ -0,0 +1,111 @@
+BEGIN;
+
+-- ============================================================
+-- Fix stock_log drift pasca-merge warehouse Jamali (NON_AKTIF) -> Gudang Farm Jamali.
+-- Follow-up migration setelah 20260528121631_normalize_warehouse_jamali_10_to_25.
+--
+-- Setelah merge, `stock_logs.stock` (running ledger) drift dari
+-- `product_warehouses.qty` karena: pre-existing drift di W10 + W25 sources,
+-- plus FIFO reflow yang trigger pasca-merge (Recording-Edit) recompute
+-- pw.qty tapi stock_logs tidak ikut update.
+--
+-- Migration ini insert 1 ADJUSTMENT stock_log corrective per PW yang drift
+-- supaya `last_stock_log.stock = pw.qty`. Logic ekivalen dengan
+-- `cmd/fix-stock-log-drift`.
+--
+-- Karakteristik dynamic:
+--   - Tidak hardcode PW IDs atau drift values
+--   - Iterate via merge target + W10-only kept PWs (data-driven dari snapshot)
+--   - Per PW: hitung drift runtime, skip kalau negligible (< 0.001) atau no logs
+--   - Track stock_log IDs yang di-insert untuk DOWN reverse
+-- ============================================================
+
+-- Guard: previous migration (normalisasi) audit harus ada
+DO $$
+BEGIN
+  IF NOT EXISTS (
+    SELECT 1 FROM information_schema.tables
+    WHERE table_schema = 'migration_audit'
+      AND table_name = 'jamali_w10_qty_merge'
+  ) THEN
+    RAISE EXCEPTION
+      'Migration 20260528121631 (normalize_warehouse_jamali) belum dijalankan atau audit-nya sudah di-drop. Apply UP-nya dulu sebelum migration ini.';
+  END IF;
+END $$;
+
+-- Audit table untuk track stock_log IDs yang di-insert (untuk DOWN reverse)
+DROP TABLE IF EXISTS migration_audit.jamali_w10_stocklog_corrections;
+CREATE TABLE migration_audit.jamali_w10_stocklog_corrections (
+  stock_log_id BIGINT NOT NULL PRIMARY KEY,
+  product_warehouse_id BIGINT NOT NULL,
+  drift NUMERIC(15,3) NOT NULL,
+  inserted_at TIMESTAMPTZ DEFAULT NOW()
+);
+
+-- Insert corrective ADJUSTMENT stock_log untuk tiap PW yang drift
+DO $$
+DECLARE
+  rec RECORD;
+  v_last_log_stock NUMERIC(15,3);
+  v_drift NUMERIC(15,3);
+  v_new_log_id BIGINT;
+  v_inserts INT := 0;
+BEGIN
+  FOR rec IN (
+    SELECT pw.id AS pw_id, pw.qty AS qty
+    FROM product_warehouses pw
+    WHERE pw.id IN (
+      -- Merge target W25 PWs (9 rows)
+      SELECT target_pw_id FROM migration_audit.jamali_w10_qty_merge
+      UNION
+      -- W10-only PWs yang di-update warehouse_id 10->25 (4 rows)
+      SELECT id FROM migration_audit.jamali_w10_pw_w10only_snapshot
+    )
+  ) LOOP
+    -- Ambil stock akhir di stock_logs ledger
+    SELECT stock INTO v_last_log_stock
+    FROM stock_logs
+    WHERE product_warehouse_id = rec.pw_id
+    ORDER BY id DESC
+    LIMIT 1;
+
+    -- PW tanpa stock_logs entry (mis. 1188/1189/1190 ayam) -> skip
+    IF v_last_log_stock IS NULL THEN
+      CONTINUE;
+    END IF;
+
+    v_drift := rec.qty - v_last_log_stock;
+
+    -- Drift negligible -> skip
+    IF ABS(v_drift) < 0.001 THEN
+      CONTINUE;
+    END IF;
+
+    -- Insert corrective ADJUSTMENT stock_log
+    INSERT INTO stock_logs (
+      product_warehouse_id, loggable_type, loggable_id,
+      notes, increase, decrease, stock, created_by, created_at
+    ) VALUES (
+      rec.pw_id,
+      'ADJUSTMENT',
+      0,
+      'Koreksi stock_log drift pasca-merge warehouse Jamali (migration 20260528123243)',
+      CASE WHEN v_drift > 0 THEN v_drift ELSE 0 END,
+      CASE WHEN v_drift < 0 THEN -v_drift ELSE 0 END,
+      rec.qty,
+      1,
+      NOW()
+    ) RETURNING id INTO v_new_log_id;
+
+    -- Track ke audit table untuk DOWN
+    INSERT INTO migration_audit.jamali_w10_stocklog_corrections (
+      stock_log_id, product_warehouse_id, drift
+    ) VALUES (v_new_log_id, rec.pw_id, v_drift);
+
+    v_inserts := v_inserts + 1;
+  END LOOP;
+
+  RAISE NOTICE 'Inserted % corrective stock_logs to align ledger with pw.qty', v_inserts;
+END $$;
+
+COMMIT;

internal/database/migrations/20260528175508_add_grand_total_to_marketings_expenses.down.sql

@@ -0,0 +1,3 @@
+ALTER TABLE marketings DROP COLUMN IF EXISTS grand_total;
+ALTER TABLE expenses DROP COLUMN IF EXISTS grand_total;
+ALTER TABLE purchases DROP COLUMN IF EXISTS grand_total;

internal/database/migrations/20260528175508_add_grand_total_to_marketings_expenses.up.sql

@@ -0,0 +1,42 @@
+-- Marketing belum punya grand_total. Tambahkan dengan DEFAULT 0.
+ALTER TABLE marketings ADD COLUMN grand_total NUMERIC(15, 3) NOT NULL DEFAULT 0;
+
+-- Expense grand_total sebelumnya di-drop di migration 20251125055613. Re-add.
+ALTER TABLE expenses ADD COLUMN grand_total NUMERIC(15, 3) NOT NULL DEFAULT 0;
+
+ALTER TABLE purchases ADD COLUMN grand_total NUMERIC(15, 3) NOT NULL DEFAULT 0;
+
+-- Backfill nilai grand_total dari children:
+-- marketings.grand_total = SUM marketing_delivery_products.total_price (WHERE delivery_date IS NOT NULL)
+UPDATE marketings m
+SET grand_total = COALESCE(s.t, 0)
+FROM (
+    SELECT mp.marketing_id AS marketing_id, SUM(mdp.total_price) AS t
+    FROM marketing_delivery_products mdp
+    JOIN marketing_products mp ON mp.id = mdp.marketing_product_id
+    WHERE mdp.delivery_date IS NOT NULL
+    GROUP BY mp.marketing_id
+) s
+WHERE s.marketing_id = m.id;
+
+-- expenses.grand_total = SUM(expense_realizations.qty * expense_realizations.price) via expense_nonstocks
+UPDATE expenses e
+SET grand_total = COALESCE(s.t, 0)
+FROM (
+    SELECT en.expense_id AS expense_id, SUM(er.qty * er.price) AS t
+    FROM expense_realizations er
+    JOIN expense_nonstocks en ON en.id = er.expense_nonstock_id
+    GROUP BY en.expense_id
+) s
+WHERE s.expense_id = e.id;
+
+-- purchases.grand_total sudah ada sejak migration 20251104084555.
+-- Recompute juga untuk safety supaya konsisten dengan SUM purchase_items.total_price.
+UPDATE purchases p
+SET grand_total = COALESCE(s.t, 0)
+FROM (
+    SELECT purchase_id, SUM(total_price) AS t
+    FROM purchase_items
+    GROUP BY purchase_id
+) s
+WHERE s.purchase_id = p.id;

internal/database/migrations/20260528175642_create_payment_allocations.down.sql

@@ -0,0 +1,5 @@
+DROP INDEX IF EXISTS idx_payments_party_active;
+DROP INDEX IF EXISTS idx_mdp_delivery_date_partial;
+DROP INDEX IF EXISTS idx_purchase_items_received_date_partial;
+
+DROP TABLE IF EXISTS payment_allocations;

internal/database/migrations/20260528175642_create_payment_allocations.up.sql

@@ -0,0 +1,27 @@
+-- Tabel payment_allocations menyimpan hasil FIFO matching antara payment dengan
+-- sub-row anak (purchase_item / marketing_delivery_product / expense_realization).
+-- Setiap allocation row HARUS terhubung ke tepat 1 child via 3 nullable FK
+-- (polymorphic-via-multiple-nullable-FK; lebih aman dari single polymorphic kolom).
+CREATE TABLE IF NOT EXISTS payment_allocations (
+    id BIGSERIAL PRIMARY KEY,
+    payment_id BIGINT NOT NULL REFERENCES payments(id) ON DELETE CASCADE,
+    purchase_item_id BIGINT NULL REFERENCES purchase_items(id) ON DELETE CASCADE,
+    marketing_delivery_product_id BIGINT NULL REFERENCES marketing_delivery_products(id) ON DELETE CASCADE,
+    expense_realization_id BIGINT NULL REFERENCES expense_realizations(id) ON DELETE CASCADE,
+    amount NUMERIC(15, 3) NOT NULL CHECK (amount > 0),
+    allocated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+    CONSTRAINT chk_payment_alloc_exactly_one CHECK (
+        num_nonnulls(purchase_item_id, marketing_delivery_product_id, expense_realization_id) = 1
+    )
+);
+
+CREATE INDEX IF NOT EXISTS idx_payment_alloc_payment ON payment_allocations (payment_id);
+CREATE INDEX IF NOT EXISTS idx_payment_alloc_purchase_item ON payment_allocations (purchase_item_id) WHERE purchase_item_id IS NOT NULL;
+CREATE INDEX IF NOT EXISTS idx_payment_alloc_mdp ON payment_allocations (marketing_delivery_product_id) WHERE marketing_delivery_product_id IS NOT NULL;
+CREATE INDEX IF NOT EXISTS idx_payment_alloc_realization ON payment_allocations (expense_realization_id) WHERE expense_realization_id IS NOT NULL;
+CREATE INDEX IF NOT EXISTS idx_payment_alloc_allocated_at ON payment_allocations (allocated_at);
+
+-- Helper partial indexes untuk FIFO loop performance
+CREATE INDEX IF NOT EXISTS idx_purchase_items_received_date_partial ON purchase_items (received_date) WHERE received_date IS NOT NULL;
+CREATE INDEX IF NOT EXISTS idx_mdp_delivery_date_partial ON marketing_delivery_products (delivery_date) WHERE delivery_date IS NOT NULL;
+CREATE INDEX IF NOT EXISTS idx_payments_party_active ON payments (party_type, party_id, payment_date) WHERE deleted_at IS NULL;

internal/database/migrations/20260528175729_backfill_payment_allocations.down.sql

@@ -0,0 +1,4 @@
+-- Rollback backfill: hapus semua allocations dan drop function.
+TRUNCATE payment_allocations;
+
+DROP FUNCTION IF EXISTS fn_fifo_backfill_party(TEXT, BIGINT);

internal/database/migrations/20260528175729_backfill_payment_allocations.up.sql

@@ -0,0 +1,170 @@
+-- Backfill payment_allocations untuk data historis via FIFO simulation.
+-- Seluruh migration ini berjalan dalam 1 transaction (golang-migrate default).
+-- Jika ada party yang gagal di tengah loop, seluruh backfill ROLLBACK otomatis.
+
+-- Fungsi inti: FIFO greedy untuk 1 party (supplier/customer).
+-- Algoritma:
+--   1. Hapus payment_allocations existing untuk party tsb (idempotent).
+--   2. Kumpulkan eligible children sort by date ASC ke array (kind, id, amount, remaining).
+--   3. Konsumsi creditCarry (SUM payment SALDO_AWAL) ke children tertua — TIDAK insert allocation row.
+--   4. Loop payments (selain SALDO_AWAL) ORDER BY payment_date ASC: greedy alokasi ke child tertua dengan remaining > 0.
+--   5. Sisa nominal payment tidak insert row (otomatis credit balance untuk dokumen baru).
+CREATE OR REPLACE FUNCTION fn_fifo_backfill_party(
+    p_party_type TEXT,
+    p_party_id BIGINT
+) RETURNS VOID AS $func$
+DECLARE
+    v_party_type TEXT := UPPER(p_party_type);
+    v_payment RECORD;
+    v_child RECORD;
+    v_remaining NUMERIC(15, 3);
+    v_used NUMERIC(15, 3);
+    v_eps CONSTANT NUMERIC(15, 3) := 0.001;
+BEGIN
+    -- Acquire advisory lock untuk anti-race (1-arg form: hashtext returns int4, cast ke bigint)
+    PERFORM pg_advisory_xact_lock(hashtext('payment_alloc:' || v_party_type || ':' || p_party_id::text)::bigint);
+
+    -- Hapus allocations existing untuk party tsb (idempotent ulang-jalan)
+    DELETE FROM payment_allocations pa
+    USING payments p
+    WHERE pa.payment_id = p.id
+      AND p.party_type = v_party_type
+      AND p.party_id = p_party_id;
+
+    -- TEMP table untuk antrian children (sort sudah ada di INSERT...SELECT ORDER BY)
+    CREATE TEMP TABLE IF NOT EXISTS _children_queue (
+        seq BIGSERIAL PRIMARY KEY,
+        kind TEXT NOT NULL,           -- 'PURCHASE_ITEM' / 'MDP' / 'EXPENSE_REALIZATION'
+        child_id BIGINT NOT NULL,
+        amount NUMERIC(15, 3) NOT NULL,
+        remaining NUMERIC(15, 3) NOT NULL
+    ) ON COMMIT DROP;
+    TRUNCATE _children_queue;
+
+    IF v_party_type = 'SUPPLIER' THEN
+        -- purchase_items eligible: received_date IS NOT NULL, approval latest step >= 4 (Receiving), action != REJECTED
+        INSERT INTO _children_queue (kind, child_id, amount, remaining)
+        SELECT 'PURCHASE_ITEM', pi.id, pi.total_price, pi.total_price
+        FROM purchase_items pi
+        JOIN purchases p ON p.id = pi.purchase_id
+        JOIN LATERAL (
+            SELECT a.step_number, a.action
+            FROM approvals a
+            WHERE a.approvable_type = 'PURCHASES' AND a.approvable_id = p.id
+            ORDER BY a.action_at DESC, a.id DESC
+            LIMIT 1
+        ) la ON true
+        WHERE p.supplier_id = p_party_id
+          AND p.deleted_at IS NULL
+          AND pi.received_date IS NOT NULL
+          AND la.step_number >= 4
+          AND (la.action IS NULL OR la.action <> 'REJECTED')
+          AND pi.total_price > 0
+        ORDER BY pi.received_date ASC, pi.id ASC;
+
+        -- expense_realizations eligible: parent expense approval latest step >= 5 (Realisasi), action != REJECTED.
+        -- Sort pakai e.transaction_date supaya FIFO konsisten dengan tanggal yang di-display di report.
+        INSERT INTO _children_queue (kind, child_id, amount, remaining)
+        SELECT 'EXPENSE_REALIZATION', er.id, (er.qty * er.price), (er.qty * er.price)
+        FROM expense_realizations er
+        JOIN expense_nonstocks en ON en.id = er.expense_nonstock_id
+        JOIN expenses e ON e.id = en.expense_id
+        JOIN LATERAL (
+            SELECT a.step_number, a.action
+            FROM approvals a
+            WHERE a.approvable_type = 'EXPENSES' AND a.approvable_id = e.id
+            ORDER BY a.action_at DESC, a.id DESC
+            LIMIT 1
+        ) la ON true
+        WHERE e.supplier_id = p_party_id
+          AND e.deleted_at IS NULL
+          AND la.step_number >= 5
+          AND (la.action IS NULL OR la.action <> 'REJECTED')
+          AND (er.qty * er.price) > 0
+        ORDER BY e.transaction_date ASC, e.id ASC, er.id ASC;
+
+    ELSIF v_party_type = 'CUSTOMER' THEN
+        -- marketing_delivery_products eligible: delivery_date IS NOT NULL (match current report behavior, tidak filter approval)
+        INSERT INTO _children_queue (kind, child_id, amount, remaining)
+        SELECT 'MDP', mdp.id, mdp.total_price, mdp.total_price
+        FROM marketing_delivery_products mdp
+        JOIN marketing_products mp ON mp.id = mdp.marketing_product_id
+        JOIN marketings m ON m.id = mp.marketing_id
+        WHERE m.customer_id = p_party_id
+          AND m.deleted_at IS NULL
+          AND mdp.delivery_date IS NOT NULL
+          AND mdp.total_price > 0
+        ORDER BY mdp.delivery_date ASC, mdp.id ASC;
+    ELSE
+        RETURN;
+    END IF;
+
+    -- Skip jika tidak ada children eligible
+    IF NOT EXISTS (SELECT 1 FROM _children_queue) THEN
+        RETURN;
+    END IF;
+
+    -- Loop SEMUA payments termasuk SALDO_AWAL ORDER BY payment_date ASC, id ASC.
+    -- SALDO_AWAL diperlakukan sebagai payment tertua sehingga opening credit otomatis
+    -- consume oldest debts via FIFO. Tanpa allocation row, debt yang ter-cover SaldoAwal
+    -- akan tampak "Belum Lunas" di report.
+    FOR v_payment IN
+        SELECT id, nominal
+        FROM payments
+        WHERE party_type = v_party_type
+          AND party_id = p_party_id
+          AND deleted_at IS NULL
+          AND nominal > v_eps
+        ORDER BY payment_date ASC, id ASC
+    LOOP
+        v_remaining := v_payment.nominal;
+
+        -- Greedy alokasi ke children tertua dengan remaining > 0
+        FOR v_child IN
+            SELECT seq, kind, child_id, remaining
+            FROM _children_queue
+            WHERE remaining > v_eps
+            ORDER BY seq ASC
+        LOOP
+            EXIT WHEN v_remaining <= v_eps;
+
+            -- v_child.remaining is snapshot at cursor open; re-fetch latest to avoid drift in same payment iter
+            SELECT remaining INTO v_used FROM _children_queue WHERE seq = v_child.seq;
+            IF v_used <= v_eps THEN
+                CONTINUE;
+            END IF;
+
+            v_used := LEAST(v_remaining, v_used);
+            UPDATE _children_queue SET remaining = remaining - v_used WHERE seq = v_child.seq;
+            v_remaining := v_remaining - v_used;
+
+            IF v_child.kind = 'PURCHASE_ITEM' THEN
+                INSERT INTO payment_allocations (payment_id, purchase_item_id, amount, allocated_at)
+                VALUES (v_payment.id, v_child.child_id, v_used, NOW());
+            ELSIF v_child.kind = 'MDP' THEN
+                INSERT INTO payment_allocations (payment_id, marketing_delivery_product_id, amount, allocated_at)
+                VALUES (v_payment.id, v_child.child_id, v_used, NOW());
+            ELSIF v_child.kind = 'EXPENSE_REALIZATION' THEN
+                INSERT INTO payment_allocations (payment_id, expense_realization_id, amount, allocated_at)
+                VALUES (v_payment.id, v_child.child_id, v_used, NOW());
+            END IF;
+        END LOOP;
+    END LOOP;
+END;
+$func$ LANGUAGE plpgsql;
+
+-- Invoke per-party. Gagal di satu party → entire transaction ROLLBACK.
+DO $do$
+DECLARE
+    r RECORD;
+BEGIN
+    FOR r IN
+        SELECT DISTINCT party_type, party_id
+        FROM payments
+        WHERE deleted_at IS NULL
+          AND party_id IS NOT NULL
+    LOOP
+        PERFORM fn_fifo_backfill_party(r.party_type, r.party_id);
+    END LOOP;
+END;
+$do$;

internal/entities/expense.go

@@ -18,6 +18,7 @@ type Expense struct {
 	TransactionDate         time.Time      `gorm:"type:date;not null"`
 	Notes                   string         `gorm:"type:text;column:notes"`
 	IsPaid                  bool           `gorm:"column:is_paid;not null;default:false"`
+	GrandTotal              float64        `gorm:"column:grand_total;type:numeric(15,3);not null;default:0"`
 	CreatedBy               uint64         `gorm:""`
 	CreatedAt               time.Time      `gorm:"autoCreateTime"`
 	UpdatedAt               time.Time      `gorm:"autoUpdateTime"`

internal/entities/marketing.go

@@ -15,6 +15,7 @@ type Marketing struct {
 	SalesPersonId uint           `gorm:"not null"`
 	Notes         string         `gorm:"type:text"`
 	MarketingType string         `gorm:"type:varchar(50)"`
+	GrandTotal    float64        `gorm:"column:grand_total;type:numeric(15,3);not null;default:0"`
 	CreatedBy     uint           `gorm:"not null"`
 	CreatedAt     time.Time      `gorm:"autoCreateTime"`
 	UpdatedAt     time.Time      `gorm:"autoUpdateTime"`

internal/entities/payment_allocation.go

@@ -0,0 +1,23 @@
+package entities
+
+import (
+	"time"
+)
+
+// PaymentAllocation merepresentasikan hasil FIFO matching dari 1 payment ke
+// tepat 1 sub-row anak (purchase_item / marketing_delivery_product /
+// expense_realization). DB constraint memastikan hanya satu FK yang non-null.
+type PaymentAllocation struct {
+	Id                         uint64    `gorm:"primaryKey;autoIncrement"`
+	PaymentId                  uint      `gorm:"not null;index"`
+	PurchaseItemId             *uint     `gorm:"column:purchase_item_id"`
+	MarketingDeliveryProductId *uint     `gorm:"column:marketing_delivery_product_id"`
+	ExpenseRealizationId       *uint64   `gorm:"column:expense_realization_id"`
+	Amount                     float64   `gorm:"type:numeric(15,3);not null"`
+	AllocatedAt                time.Time `gorm:"type:timestamptz;not null;default:NOW()"`
+
+	Payment                  *Payment                  `gorm:"foreignKey:PaymentId;references:Id"`
+	PurchaseItem             *PurchaseItem             `gorm:"foreignKey:PurchaseItemId;references:Id"`
+	MarketingDeliveryProduct *MarketingDeliveryProduct `gorm:"foreignKey:MarketingDeliveryProductId;references:Id"`
+	ExpenseRealization       *ExpenseRealization       `gorm:"foreignKey:ExpenseRealizationId;references:Id"`
+}

internal/entities/purchase.go

@@ -12,6 +12,7 @@ type Purchase struct {
 	SupplierId uint `gorm:"not null"`
 	CreditTerm int  `gorm:"column:credit_term;not null;default:0"`
 	DueDate    *time.Time
+	GrandTotal float64 `gorm:"column:grand_total;type:numeric(15,3);not null;default:0"`
 	Notes      *string
 	CreatedAt  time.Time  `gorm:"autoCreateTime"`
 	UpdatedAt  time.Time  `gorm:"autoUpdateTime"`

internal/modules/expenses/module.go

@@ -45,7 +45,8 @@ func (ExpenseModule) RegisterRoutes(router fiber.Router, db *gorm.DB, validate *
 		panic(fmt.Sprintf("failed to register expense approval workflow: %v", err))
 	}
 
-	expenseService := sExpense.NewExpenseService(expenseRepo, supplierRepo, nonstockRepo, approvalSvc, realizationRepo, projectFlockKandangRepo, documentSvc, validate)
+	fifoPaymentSvc := commonSvc.NewFifoPaymentService(db, utils.Log)
+	expenseService := sExpense.NewExpenseService(expenseRepo, supplierRepo, nonstockRepo, approvalSvc, realizationRepo, projectFlockKandangRepo, documentSvc, fifoPaymentSvc, validate)
 	userService := sUser.NewUserService(userRepo, validate)
 
 	ExpenseRoutes(router, userService, expenseService)

internal/modules/expenses/services/expense.service.go

@@ -54,9 +54,10 @@ type expenseService struct {
 	RealizationRepository   repository.ExpenseRealizationRepository
 	ProjectFlockKandangRepo projectFlockKandangRepo.ProjectFlockKandangRepository
 	DocumentSvc             commonSvc.DocumentService
+	FifoPaymentSvc          commonSvc.FifoPaymentService
 }
 
-func NewExpenseService(repo repository.ExpenseRepository, supplierRepo supplierRepo.SupplierRepository, nonstockRepo nonstockRepo.NonstockRepository, approvalSvc commonSvc.ApprovalService, realizationRepo repository.ExpenseRealizationRepository, projectFlockKandangRepo projectFlockKandangRepo.ProjectFlockKandangRepository, documentSvc commonSvc.DocumentService, validate *validator.Validate) ExpenseService {
+func NewExpenseService(repo repository.ExpenseRepository, supplierRepo supplierRepo.SupplierRepository, nonstockRepo nonstockRepo.NonstockRepository, approvalSvc commonSvc.ApprovalService, realizationRepo repository.ExpenseRealizationRepository, projectFlockKandangRepo projectFlockKandangRepo.ProjectFlockKandangRepository, documentSvc commonSvc.DocumentService, fifoPaymentSvc commonSvc.FifoPaymentService, validate *validator.Validate) ExpenseService {
 	return &expenseService{
 		Log:                     utils.Log,
 		Validate:                validate,
@@ -67,6 +68,23 @@ func NewExpenseService(repo repository.ExpenseRepository, supplierRepo supplierR
 		RealizationRepository:   realizationRepo,
 		ProjectFlockKandangRepo: projectFlockKandangRepo,
 		DocumentSvc:             documentSvc,
+		FifoPaymentSvc:          fifoPaymentSvc,
+	}
+}
+
+// reallocateAfterRealization called after expense realization changes that may
+// affect supplier debt: recompute grand_total + reallocate FIFO.
+func (s *expenseService) reallocateAfterRealization(ctx context.Context, expenseID uint, supplierID uint64) {
+	if s.FifoPaymentSvc == nil {
+		return
+	}
+	if err := s.FifoPaymentSvc.RecomputeGrandTotal(ctx, nil, commonSvc.ParentKindExpense, expenseID); err != nil {
+		s.Log.Warnf("Failed to recompute grand_total for expense %d: %+v", expenseID, err)
+	}
+	if supplierID > 0 {
+		if err := s.FifoPaymentSvc.ReallocateForParty(ctx, nil, string(utils.PaymentPartySupplier), uint(supplierID)); err != nil {
+			s.Log.Warnf("Failed to reallocate payments for supplier %d: %+v", supplierID, err)
+		}
 	}
 }
 
@@ -1078,6 +1096,9 @@ func (s *expenseService) CreateRealization(c *fiber.Ctx, expenseID uint, req *va
 	}
 	invalidateFromDate := commonSvc.MinNonZeroDateOnlyUTC(expense.TransactionDate, realizationDate, expense.RealizationDate)
 	s.invalidateDepreciationSnapshotsByExpense(c.Context(), nil, expenseID, invalidateFromDate, nil)
+
+	s.reallocateAfterRealization(c.Context(), expenseID, expense.SupplierId)
+
 	return responseDTO, nil
 }
 
@@ -1522,6 +1543,9 @@ func (s *expenseService) UpdateRealization(c *fiber.Ctx, expenseID uint, req *va
 		return nil, err
 	}
 	s.invalidateDepreciationSnapshotsByExpense(c.Context(), nil, expenseID, invalidateFromDate, nil)
+
+	s.reallocateAfterRealization(c.Context(), expenseID, expense.SupplierId)
+
 	return responseDTO, nil
 }
 

internal/modules/finance/payments/module.go

@@ -29,7 +29,9 @@ func (PaymentModule) RegisterRoutes(router fiber.Router, db *gorm.DB, validate *
 		panic(fmt.Sprintf("failed to register payment approval workflow: %v", err))
 	}
 
-	paymentService := sPayment.NewPaymentService(paymentRepo, approvalService, validate)
+	fifoPaymentService := commonSvc.NewFifoPaymentService(db, nil)
+
+	paymentService := sPayment.NewPaymentService(paymentRepo, approvalService, fifoPaymentService, validate)
 	userService := sUser.NewUserService(userRepo, validate)
 
 	PaymentRoutes(router, userService, paymentService)

internal/modules/finance/payments/services/payment.service.go

@@ -32,12 +32,14 @@ type paymentService struct {
 	Validate         *validator.Validate
 	Repository       repository.PaymentRepository
 	ApprovalSvc      commonSvc.ApprovalService
+	FifoPaymentSvc   commonSvc.FifoPaymentService
 	approvalWorkflow approvalutils.ApprovalWorkflowKey
 }
 
 func NewPaymentService(
 	repo repository.PaymentRepository,
 	approvalSvc commonSvc.ApprovalService,
+	fifoPaymentSvc commonSvc.FifoPaymentService,
 	validate *validator.Validate,
 ) PaymentService {
 	return &paymentService{
@@ -45,6 +47,7 @@ func NewPaymentService(
 		Validate:         validate,
 		Repository:       repo,
 		ApprovalSvc:      approvalSvc,
+		FifoPaymentSvc:   fifoPaymentSvc,
 		approvalWorkflow: utils.ApprovalWorkflowPayment,
 	}
 }
@@ -159,6 +162,12 @@ func (s *paymentService) CreateOne(c *fiber.Ctx, req *validation.Create) (*entit
 			}
 		}
 
+		if s.FifoPaymentSvc != nil {
+			if err := s.FifoPaymentSvc.ReallocateForParty(c.Context(), dbTransaction, createBody.PartyType, createBody.PartyId); err != nil {
+				return err
+			}
+		}
+
 		return nil
 	})
 	if err != nil {
@@ -251,7 +260,46 @@ func (s paymentService) UpdateOne(c *fiber.Ctx, req *validation.Update, id uint)
 		return s.GetOne(c, id)
 	}
 
-	if err := s.Repository.PatchOne(c.Context(), id, updateBody, nil); err != nil {
+	// Snapshot party lama untuk reallocate kalau party baru berbeda.
+	existing, err := s.Repository.GetByID(c.Context(), id, nil)
+	if errors.Is(err, gorm.ErrRecordNotFound) {
+		return nil, fiber.NewError(fiber.StatusNotFound, "Payment not found")
+	}
+	if err != nil {
+		s.Log.Errorf("Failed get payment for update: %+v", err)
+		return nil, err
+	}
+	oldPartyType := existing.PartyType
+	oldPartyID := existing.PartyId
+
+	newPartyType := oldPartyType
+	newPartyID := oldPartyID
+	if v, ok := updateBody["party_type"].(string); ok {
+		newPartyType = v
+	}
+	if v, ok := updateBody["party_id"].(uint); ok {
+		newPartyID = v
+	}
+
+	err = s.Repository.DB().WithContext(c.Context()).Transaction(func(tx *gorm.DB) error {
+		paymentRepoTx := repository.NewPaymentRepository(tx)
+		if err := paymentRepoTx.PatchOne(c.Context(), id, updateBody, nil); err != nil {
+			return err
+		}
+
+		if s.FifoPaymentSvc != nil {
+			if err := s.FifoPaymentSvc.ReallocateForParty(c.Context(), tx, newPartyType, newPartyID); err != nil {
+				return err
+			}
+			if oldPartyType != newPartyType || oldPartyID != newPartyID {
+				if err := s.FifoPaymentSvc.ReallocateForParty(c.Context(), tx, oldPartyType, oldPartyID); err != nil {
+					return err
+				}
+			}
+		}
+		return nil
+	})
+	if err != nil {
 		if errors.Is(err, gorm.ErrRecordNotFound) {
 			return nil, fiber.NewError(fiber.StatusNotFound, "Payment not found")
 		}

internal/modules/finance/transactions/module.go

@@ -35,7 +35,8 @@ func (TransactionModule) RegisterRoutes(router fiber.Router, db *gorm.DB, valida
 		panic(fmt.Sprintf("failed to register injection approval workflow: %v", err))
 	}
 
-	transactionService := sTransaction.NewTransactionService(transactionRepo, approvalService, validate)
+	fifoPaymentService := commonSvc.NewFifoPaymentService(db, utils.Log)
+	transactionService := sTransaction.NewTransactionService(transactionRepo, approvalService, fifoPaymentService, validate)
 	userService := sUser.NewUserService(userRepo, validate)
 
 	TransactionRoutes(router, userService, transactionService)

internal/modules/finance/transactions/services/transaction.service.go

@@ -30,12 +30,14 @@ type transactionService struct {
 	Validate          *validator.Validate
 	Repository        repository.TransactionRepository
 	ApprovalSvc       commonSvc.ApprovalService
+	FifoPaymentSvc    commonSvc.FifoPaymentService
 	approvalWorkflows map[string]approvalutils.ApprovalWorkflowKey
 }
 
 func NewTransactionService(
 	repo repository.TransactionRepository,
 	approvalSvc commonSvc.ApprovalService,
+	fifoPaymentSvc commonSvc.FifoPaymentService,
 	validate *validator.Validate,
 ) TransactionService {
 	return &transactionService{
@@ -43,6 +45,7 @@ func NewTransactionService(
 		Validate:       validate,
 		Repository:     repo,
 		ApprovalSvc:    approvalSvc,
+		FifoPaymentSvc: fifoPaymentSvc,
 		approvalWorkflows: map[string]approvalutils.ApprovalWorkflowKey{
 			string(utils.TransactionTypeSaldoAwal): utils.ApprovalWorkflowInitial,
 			string(utils.TransactionTypeInjection): utils.ApprovalWorkflowInjection,
@@ -182,6 +185,19 @@ func (s transactionService) GetOne(c *fiber.Ctx, id uint) (*entity.Payment, erro
 }
 
 func (s transactionService) DeleteOne(c *fiber.Ctx, id uint) error {
+	// Snapshot party SEBELUM delete supaya bisa re-FIFO setelah trigger DB
+	// (`trg_soft_delete_fk_payments`) CASCADE hard-DELETE allocations.
+	existing, err := s.Repository.GetByID(c.Context(), id, nil)
+	if err != nil {
+		if errors.Is(err, gorm.ErrRecordNotFound) {
+			return fiber.NewError(fiber.StatusNotFound, "Transaction not found")
+		}
+		s.Log.Errorf("Failed to load transaction before delete: %+v", err)
+		return err
+	}
+	partyType := existing.PartyType
+	partyID := existing.PartyId
+
 	if err := s.Repository.DeleteOne(c.Context(), id); err != nil {
 		if errors.Is(err, gorm.ErrRecordNotFound) {
 			return fiber.NewError(fiber.StatusNotFound, "Transaction not found")
@@ -189,6 +205,14 @@ func (s transactionService) DeleteOne(c *fiber.Ctx, id uint) error {
 		s.Log.Errorf("Failed to delete transaction: %+v", err)
 		return err
 	}
+
+	// Re-FIFO setelah delete agar payment lain yang masih punya unallocated nominal
+	// otomatis reflow ke MDP/purchase_item/expense_realization yang kekurangan paid.
+	if s.FifoPaymentSvc != nil && partyID > 0 {
+		if err := s.FifoPaymentSvc.ReallocateForParty(c.Context(), nil, partyType, partyID); err != nil {
+			s.Log.Warnf("Failed to reallocate payments after delete (party=%s id=%d): %+v", partyType, partyID, err)
+		}
+	}
 	return nil
 }
 

internal/modules/inventory/transfers/module.go

@@ -65,6 +65,7 @@ func (TransferModule) RegisterRoutes(router fiber.Router, db *gorm.DB, validate
 		expenseRealizationRepo,
 		projectFlockKandangRepo,
 		documentSvc,
+		commonSvc.NewFifoPaymentService(db, utils.Log),
 		validate,
 	)
 

internal/modules/marketing/module.go

@@ -35,6 +35,7 @@ func (MarketingModule) RegisterRoutes(router fiber.Router, db *gorm.DB, validate
 	stockLogRepo := rShared.NewStockLogRepository(db)
 
 	fifoStockV2Service := commonSvc.NewFifoStockV2Service(db, utils.Log)
+	fifoPaymentService := commonSvc.NewFifoPaymentService(db, utils.Log)
 
 	approvalRepo := commonRepo.NewApprovalRepository(db)
 	approvalSvc := commonSvc.NewApprovalService(approvalRepo)
@@ -47,7 +48,7 @@ func (MarketingModule) RegisterRoutes(router fiber.Router, db *gorm.DB, validate
 	projectFlockKandangRepo := rProjectFlockKandang.NewProjectFlockKandangRepository(db)
 
 	salesOrdersService := service.NewSalesOrdersService(marketingRepo, customerRepo, productWarehouseRepo, userRepo, approvalSvc, fifoStockV2Service, warehouseRepo, projectFlockKandangRepo, validate)
-	deliveryOrdersService := service.NewDeliveryOrdersService(marketingRepo, marketingProductRepo, marketingDeliveryProductRepo, stockLogRepo, productWarehouseRepo, projectFlockPopulationRepo, approvalSvc, fifoStockV2Service, validate)
+	deliveryOrdersService := service.NewDeliveryOrdersService(marketingRepo, marketingProductRepo, marketingDeliveryProductRepo, stockLogRepo, productWarehouseRepo, projectFlockPopulationRepo, approvalSvc, fifoStockV2Service, fifoPaymentService, validate)
 	userService := sUser.NewUserService(userRepo, validate)
 
 	RegisterRoutes(router, userService, salesOrdersService, deliveryOrdersService)

internal/modules/marketing/services/deliveryorder.service.go

@@ -48,6 +48,7 @@ type deliveryOrdersService struct {
 	ProjectFlockPopulationRepo   rProjectFlock.ProjectFlockPopulationRepository
 	ApprovalSvc                  commonSvc.ApprovalService
 	FifoStockV2Svc               commonSvc.FifoStockV2Service
+	FifoPaymentSvc               commonSvc.FifoPaymentService
 }
 
 func NewDeliveryOrdersService(
@@ -59,6 +60,7 @@ func NewDeliveryOrdersService(
 	projectFlockPopulationRepo rProjectFlock.ProjectFlockPopulationRepository,
 	approvalSvc commonSvc.ApprovalService,
 	fifoStockV2Svc commonSvc.FifoStockV2Service,
+	fifoPaymentSvc commonSvc.FifoPaymentService,
 	validate *validator.Validate,
 ) DeliveryOrdersService {
 	return &deliveryOrdersService{
@@ -71,6 +73,22 @@ func NewDeliveryOrdersService(
 		ProjectFlockPopulationRepo:   projectFlockPopulationRepo,
 		ApprovalSvc:                  approvalSvc,
 		FifoStockV2Svc:               fifoStockV2Svc,
+		FifoPaymentSvc:               fifoPaymentSvc,
+	}
+}
+
+// reallocateAfterDelivery refresh marketing.grand_total + reallocate FIFO untuk customer.
+func (s *deliveryOrdersService) reallocateAfterDelivery(ctx context.Context, marketingID uint, customerID uint) {
+	if s.FifoPaymentSvc == nil {
+		return
+	}
+	if err := s.FifoPaymentSvc.RecomputeGrandTotal(ctx, nil, commonSvc.ParentKindMarketing, marketingID); err != nil {
+		utils.Log.Warnf("Failed to recompute grand_total for marketing %d: %+v", marketingID, err)
+	}
+	if customerID > 0 {
+		if err := s.FifoPaymentSvc.ReallocateForParty(ctx, nil, string(utils.PaymentPartyCustomer), customerID); err != nil {
+			utils.Log.Warnf("Failed to reallocate payments for customer %d: %+v", customerID, err)
+		}
 	}
 }
 
@@ -418,6 +436,7 @@ func (s *deliveryOrdersService) CreateOne(c *fiber.Ctx, req *validation.Delivery
 		return nil, fiber.NewError(fiber.StatusBadRequest, "Delivery order already exists for this marketing")
 	}
 
+	var capturedCustomerID uint
 	err = s.MarketingRepo.DB().WithContext(c.Context()).Transaction(func(dbTransaction *gorm.DB) error {
 		marketingProductRepositoryTx := marketingRepo.NewMarketingProductRepository(dbTransaction)
 		marketingDeliveryProductRepositoryTx := marketingRepo.NewMarketingDeliveryProductRepository(dbTransaction)
@@ -428,6 +447,7 @@ func (s *deliveryOrdersService) CreateOne(c *fiber.Ctx, req *validation.Delivery
 		if err != nil {
 			return fiber.NewError(fiber.StatusInternalServerError, "Failed to fetch marketing")
 		}
+		capturedCustomerID = marketing.CustomerId
 
 		allMarketingProducts, err := marketingProductRepositoryTx.GetByMarketingID(c.Context(), req.MarketingId)
 		if err != nil {
@@ -519,6 +539,8 @@ func (s *deliveryOrdersService) CreateOne(c *fiber.Ctx, req *validation.Delivery
 		return nil, fiber.NewError(fiber.StatusInternalServerError, "Failed to create delivery order")
 	}
 
+	s.reallocateAfterDelivery(c.Context(), req.MarketingId, capturedCustomerID)
+
 	return s.getMarketingWithDeliveries(c, req.MarketingId)
 }
 
@@ -547,6 +569,7 @@ func (s deliveryOrdersService) UpdateOne(c *fiber.Ctx, req *validation.DeliveryO
 		return nil, fiber.NewError(fiber.StatusInternalServerError, "Failed to check approval status")
 	}
 
+	var capturedCustomerID uint
 	err = s.MarketingRepo.DB().WithContext(c.Context()).Transaction(func(dbTransaction *gorm.DB) error {
 		marketingProductRepositoryTx := marketingRepo.NewMarketingProductRepository(dbTransaction)
 		marketingDeliveryProductRepositoryTx := marketingRepo.NewMarketingDeliveryProductRepository(dbTransaction)
@@ -557,6 +580,7 @@ func (s deliveryOrdersService) UpdateOne(c *fiber.Ctx, req *validation.DeliveryO
 		if err != nil {
 			return fiber.NewError(fiber.StatusInternalServerError, "Failed to fetch marketing")
 		}
+		capturedCustomerID = marketing.CustomerId
 
 		allMarketingProducts, err := marketingProductRepositoryTx.GetByMarketingID(c.Context(), id)
 		if err != nil && !errors.Is(err, gorm.ErrRecordNotFound) {
@@ -662,6 +686,8 @@ func (s deliveryOrdersService) UpdateOne(c *fiber.Ctx, req *validation.DeliveryO
 		return nil, fiber.NewError(fiber.StatusInternalServerError, "Failed to update delivery order")
 	}
 
+	s.reallocateAfterDelivery(c.Context(), id, capturedCustomerID)
+
 	return s.getMarketingWithDeliveries(c, id)
 }
 

internal/modules/purchases/module.go

@@ -61,6 +61,7 @@ func (PurchaseModule) RegisterRoutes(router fiber.Router, db *gorm.DB, validate
 		expenseRealizationRepo,
 		projectFlockKandangRepository,
 		documentSvc,
+		commonSvc.NewFifoPaymentService(db, utils.Log),
 		validate,
 	)
 	expenseBridge := service.NewExpenseBridge(
@@ -72,6 +73,7 @@ func (PurchaseModule) RegisterRoutes(router fiber.Router, db *gorm.DB, validate
 	)
 
 	fifoStockV2Service := commonSvc.NewFifoStockV2Service(db, utils.Log)
+	fifoPaymentService := commonSvc.NewFifoPaymentService(db, utils.Log)
 
 	purchaseService := service.NewPurchaseService(
 		validate,
@@ -84,6 +86,7 @@ func (PurchaseModule) RegisterRoutes(router fiber.Router, db *gorm.DB, validate
 		approvalService,
 		expenseBridge,
 		fifoStockV2Service,
+		fifoPaymentService,
 		documentSvc,
 	)
 

internal/modules/purchases/services/purchase.service.go

@@ -64,6 +64,7 @@ type purchaseService struct {
 	ApprovalSvc             commonSvc.ApprovalService
 	ExpenseBridge           PurchaseExpenseBridge
 	FifoStockV2Svc          commonSvc.FifoStockV2Service
+	FifoPaymentSvc          commonSvc.FifoPaymentService
 	DocumentSvc             commonSvc.DocumentService
 	approvalWorkflow        approvalutils.ApprovalWorkflowKey
 }
@@ -91,6 +92,7 @@ func NewPurchaseService(
 	approvalSvc commonSvc.ApprovalService,
 	expenseBridge PurchaseExpenseBridge,
 	fifoStockV2Svc commonSvc.FifoStockV2Service,
+	fifoPaymentSvc commonSvc.FifoPaymentService,
 	documentSvc commonSvc.DocumentService,
 ) PurchaseService {
 	return &purchaseService{
@@ -105,6 +107,7 @@ func NewPurchaseService(
 		ApprovalSvc:             approvalSvc,
 		ExpenseBridge:           expenseBridge,
 		FifoStockV2Svc:          fifoStockV2Svc,
+		FifoPaymentSvc:          fifoPaymentSvc,
 		DocumentSvc:             documentSvc,
 		approvalWorkflow:        utils.ApprovalWorkflowPurchase,
 	}
@@ -1406,6 +1409,16 @@ func (s *purchaseService) ReceiveProducts(c *fiber.Ctx, id uint, req *validation
 		return nil, err
 	}
 
+	// Refresh purchase.grand_total + reallocate payment FIFO untuk supplier (new debt baru emerges).
+	if s.FifoPaymentSvc != nil && receivingAction == entity.ApprovalActionApproved {
+		if err := s.FifoPaymentSvc.RecomputeGrandTotal(c.Context(), nil, commonSvc.ParentKindPurchase, purchase.Id); err != nil {
+			s.Log.Warnf("Failed to recompute grand_total for purchase %d: %+v", purchase.Id, err)
+		}
+		if err := s.FifoPaymentSvc.ReallocateForParty(c.Context(), nil, string(utils.PaymentPartySupplier), uint(purchase.SupplierId)); err != nil {
+			s.Log.Warnf("Failed to reallocate payments for supplier %d: %+v", purchase.SupplierId, err)
+		}
+	}
+
 	return updated, nil
 }
 

internal/modules/repports/controllers/repport.balance_monitoring.export.go

@@ -0,0 +1,286 @@
+package controller
+
+import (
+	"fmt"
+	"strconv"
+	"strings"
+	"time"
+
+	"github.com/gofiber/fiber/v2"
+	"github.com/xuri/excelize/v2"
+	"gitlab.com/mbugroup/lti-api.git/internal/modules/repports/dto"
+)
+
+func isBalanceMonitoringExcelExportRequest(c *fiber.Ctx) bool {
+	return strings.EqualFold(strings.TrimSpace(c.Query("export")), "excel")
+}
+
+func exportBalanceMonitoringExcel(c *fiber.Ctx, items []dto.BalanceMonitoringRowDTO, totals dto.BalanceMonitoringTotalsDTO) error {
+	content, err := buildBalanceMonitoringWorkbook(items, totals)
+	if err != nil {
+		return fiber.NewError(fiber.StatusInternalServerError, "failed to generate excel file")
+	}
+
+	filename := fmt.Sprintf("laporan-balance-monitoring-%s.xlsx", time.Now().Format("2006-01-02-1504"))
+	c.Set("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
+	c.Set("Content-Disposition", fmt.Sprintf(`attachment; filename="%s"`, filename))
+	return c.Status(fiber.StatusOK).Send(content)
+}
+
+func buildBalanceMonitoringWorkbook(items []dto.BalanceMonitoringRowDTO, totals dto.BalanceMonitoringTotalsDTO) ([]byte, error) {
+	file := excelize.NewFile()
+	defer file.Close()
+
+	const sheet = "Balance Monitoring"
+	defaultSheet := file.GetSheetName(file.GetActiveSheetIndex())
+	if defaultSheet != sheet {
+		if err := file.SetSheetName(defaultSheet, sheet); err != nil {
+			return nil, err
+		}
+	}
+
+	if err := setBalanceMonitoringColumns(file, sheet); err != nil {
+		return nil, err
+	}
+	if err := setBalanceMonitoringHeaders(file, sheet); err != nil {
+		return nil, err
+	}
+	if err := writeBalanceMonitoringRows(file, sheet, items, totals); err != nil {
+		return nil, err
+	}
+	if err := file.SetPanes(sheet, &excelize.Panes{
+		Freeze:      true,
+		YSplit:      2,
+		TopLeftCell: "A3",
+		ActivePane:  "bottomLeft",
+	}); err != nil {
+		return nil, err
+	}
+
+	buf, err := file.WriteToBuffer()
+	if err != nil {
+		return nil, err
+	}
+	return buf.Bytes(), nil
+}
+
+var bmColumnWidths = map[string]float64{
+	"A": 5,
+	"B": 28,
+	"C": 18,
+	"D": 12,
+	"E": 12,
+	"F": 20,
+	"G": 12,
+	"H": 12,
+	"I": 20,
+	"J": 20,
+	"K": 18,
+	"L": 12,
+	"M": 16,
+	"N": 20,
+}
+
+func setBalanceMonitoringColumns(file *excelize.File, sheet string) error {
+	for col, width := range bmColumnWidths {
+		if err := file.SetColWidth(sheet, col, col, width); err != nil {
+			return err
+		}
+	}
+	if err := file.SetRowHeight(sheet, 1, 24); err != nil {
+		return err
+	}
+	return file.SetRowHeight(sheet, 2, 24)
+}
+
+func setBalanceMonitoringHeaders(file *excelize.File, sheet string) error {
+	borderStyle := []excelize.Border{
+		{Type: "left", Color: "000000", Style: 1},
+		{Type: "top", Color: "000000", Style: 1},
+		{Type: "bottom", Color: "000000", Style: 1},
+		{Type: "right", Color: "000000", Style: 1},
+	}
+	headerStyle, err := file.NewStyle(&excelize.Style{
+		Font: &excelize.Font{Bold: true, Color: "FFFFFF", Family: "Arial", Size: 10},
+		Fill: excelize.Fill{Type: "pattern", Pattern: 1, Color: []string{"4472C4"}},
+		Alignment: &excelize.Alignment{
+			Horizontal: "center",
+			Vertical:   "center",
+			WrapText:   true,
+		},
+		Border: borderStyle,
+	})
+	if err != nil {
+		return err
+	}
+
+	// Single-column headers: merge rows 1 and 2 vertically
+	singleColHeaders := map[string]string{
+		"A": "No",
+		"B": "Customer",
+		"C": "Saldo Awal",
+		"J": "Penjualan Trading",
+		"K": "Pembayaran",
+		"L": "Aging",
+		"M": "Aging Rata-Rata",
+		"N": "Saldo Akhir",
+	}
+	for col, header := range singleColHeaders {
+		if err := file.SetCellValue(sheet, col+"1", header); err != nil {
+			return err
+		}
+		if err := file.MergeCell(sheet, col+"1", col+"2"); err != nil {
+			return err
+		}
+	}
+
+	// Group headers: merge columns horizontally in row 1
+	if err := file.SetCellValue(sheet, "D1", "Penjualan Ayam"); err != nil {
+		return err
+	}
+	if err := file.MergeCell(sheet, "D1", "F1"); err != nil {
+		return err
+	}
+	if err := file.SetCellValue(sheet, "G1", "Penjualan Telur"); err != nil {
+		return err
+	}
+	if err := file.MergeCell(sheet, "G1", "I1"); err != nil {
+		return err
+	}
+
+	// Sub-column headers in row 2
+	subHeaders := map[string]string{
+		"D": "Ekor",
+		"E": "Kg",
+		"F": "Nominal",
+		"G": "Butir",
+		"H": "Kg",
+		"I": "Nominal",
+	}
+	for col, header := range subHeaders {
+		if err := file.SetCellValue(sheet, col+"2", header); err != nil {
+			return err
+		}
+	}
+
+	return file.SetCellStyle(sheet, "A1", "N2", headerStyle)
+}
+
+func writeBalanceMonitoringRows(file *excelize.File, sheet string, items []dto.BalanceMonitoringRowDTO, totals dto.BalanceMonitoringTotalsDTO) error {
+	borderStyle := []excelize.Border{
+		{Type: "left", Color: "000000", Style: 1},
+		{Type: "top", Color: "000000", Style: 1},
+		{Type: "bottom", Color: "000000", Style: 1},
+		{Type: "right", Color: "000000", Style: 1},
+	}
+
+	dataStyle, err := file.NewStyle(&excelize.Style{
+		Font:      &excelize.Font{Color: "000000", Family: "Arial", Size: 10},
+		Alignment: &excelize.Alignment{Vertical: "center", WrapText: true},
+		Border:    borderStyle,
+	})
+	if err != nil {
+		return err
+	}
+
+	totalStyle, err := file.NewStyle(&excelize.Style{
+		Font:      &excelize.Font{Bold: true, Color: "000000", Family: "Arial", Size: 10},
+		Fill:      excelize.Fill{Type: "pattern", Pattern: 1, Color: []string{"E2EFDA"}},
+		Alignment: &excelize.Alignment{Vertical: "center", WrapText: true},
+		Border:    borderStyle,
+	})
+	if err != nil {
+		return err
+	}
+
+	redDataStyle, err := file.NewStyle(&excelize.Style{
+		Font:      &excelize.Font{Color: "FF0000", Family: "Arial", Size: 10},
+		Alignment: &excelize.Alignment{Vertical: "center", WrapText: true},
+		Border:    borderStyle,
+	})
+	if err != nil {
+		return err
+	}
+
+	redTotalStyle, err := file.NewStyle(&excelize.Style{
+		Font:      &excelize.Font{Bold: true, Color: "FF0000", Family: "Arial", Size: 10},
+		Fill:      excelize.Fill{Type: "pattern", Pattern: 1, Color: []string{"E2EFDA"}},
+		Alignment: &excelize.Alignment{Vertical: "center", WrapText: true},
+		Border:    borderStyle,
+	})
+	if err != nil {
+		return err
+	}
+
+	for i, row := range items {
+		rowNum := i + 3
+		rowStr := strconv.Itoa(rowNum)
+
+		cells := map[string]interface{}{
+			"A": i + 1,
+			"B": row.Customer.Name,
+			"C": row.SaldoAwal,
+			"D": row.PenjualanAyam.Ekor,
+			"E": row.PenjualanAyam.Kg,
+			"F": row.PenjualanAyam.Nominal,
+			"G": row.PenjualanTelur.Butir,
+			"H": row.PenjualanTelur.Kg,
+			"I": row.PenjualanTelur.Nominal,
+			"J": row.PenjualanTrading.Nominal,
+			"K": row.Pembayaran,
+			"L": fmt.Sprintf("%d hari", row.Aging),
+			"M": formatBMAging(row.AgingRataRata),
+			"N": row.SaldoAkhir,
+		}
+		for col, val := range cells {
+			if err := file.SetCellValue(sheet, col+rowStr, val); err != nil {
+				return err
+			}
+		}
+		if err := file.SetCellStyle(sheet, "A"+rowStr, "N"+rowStr, dataStyle); err != nil {
+			return err
+		}
+		if row.SaldoAkhir < 0 {
+			if err := file.SetCellStyle(sheet, "N"+rowStr, "N"+rowStr, redDataStyle); err != nil {
+				return err
+			}
+		}
+	}
+
+	// Totals row
+	totalRowStr := strconv.Itoa(len(items) + 3)
+	totalCells := map[string]interface{}{
+		"A": "Total",
+		"C": totals.SaldoAwal,
+		"D": totals.PenjualanAyam.Ekor,
+		"E": totals.PenjualanAyam.Kg,
+		"F": totals.PenjualanAyam.Nominal,
+		"G": totals.PenjualanTelur.Butir,
+		"H": totals.PenjualanTelur.Kg,
+		"I": totals.PenjualanTelur.Nominal,
+		"J": totals.PenjualanTrading.Nominal,
+		"K": totals.Pembayaran,
+		"N": totals.SaldoAkhir,
+	}
+	for col, val := range totalCells {
+		if err := file.SetCellValue(sheet, col+totalRowStr, val); err != nil {
+			return err
+		}
+	}
+	if err := file.SetCellStyle(sheet, "A"+totalRowStr, "N"+totalRowStr, totalStyle); err != nil {
+		return err
+	}
+	if totals.SaldoAkhir < 0 {
+		if err := file.SetCellStyle(sheet, "N"+totalRowStr, "N"+totalRowStr, redTotalStyle); err != nil {
+			return err
+		}
+	}
+
+	return nil
+}
+
+func formatBMAging(v float64) string {
+	s := strconv.FormatFloat(v, 'f', 2, 64)
+	s = strings.ReplaceAll(s, ".", ",")
+	return s + " hari"
+}

internal/modules/repports/controllers/repport.controller.go

@@ -324,6 +324,13 @@ func (c *RepportController) GetPurchaseSupplier(ctx *fiber.Ctx) error {
 		return err
 	}
 
+	if isPurchaseSupplierExcelExportRequest(ctx) {
+		return exportPurchaseSupplierExcel(ctx, result)
+	}
+	if isPurchaseSupplierExcelAllExportRequest(ctx) {
+		return exportPurchaseSupplierExcelAll(ctx, result)
+	}
+
 	filters := map[string]interface{}{
 		"area_id":             query.AreaIDs,
 		"supplier_id":         query.SupplierIDs,
@@ -555,6 +562,10 @@ func (c *RepportController) GetBalanceMonitoring(ctx *fiber.Ctx) error {
 		return err
 	}
 
+	if isBalanceMonitoringExcelExportRequest(ctx) {
+		return exportBalanceMonitoringExcel(ctx, result, totals)
+	}
+
 	limit := query.Limit
 	if limit < 1 {
 		limit = 10

internal/modules/repports/controllers/repport.purchase_supplier.export.go

@@ -0,0 +1,415 @@
+package controller
+
+import (
+	"fmt"
+	"strings"
+	"time"
+
+	"github.com/gofiber/fiber/v2"
+	"github.com/xuri/excelize/v2"
+	"gitlab.com/mbugroup/lti-api.git/internal/modules/repports/dto"
+)
+
+func isPurchaseSupplierExcelExportRequest(c *fiber.Ctx) bool {
+	return strings.EqualFold(strings.TrimSpace(c.Query("export")), "excel")
+}
+
+func isPurchaseSupplierExcelAllExportRequest(c *fiber.Ctx) bool {
+	return strings.EqualFold(strings.TrimSpace(c.Query("export")), "excel-all")
+}
+
+func exportPurchaseSupplierExcel(c *fiber.Ctx, items []dto.PurchaseSupplierDTO) error {
+	content, err := buildPurchaseSupplierWorkbook(items)
+	if err != nil {
+		return fiber.NewError(fiber.StatusInternalServerError, "failed to generate excel file")
+	}
+
+	filename := fmt.Sprintf("laporan-pembelian-supplier-%s.xlsx", time.Now().Format("2006-01-02-1504"))
+	c.Set("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
+	c.Set("Content-Disposition", fmt.Sprintf(`attachment; filename="%s"`, filename))
+	return c.Status(fiber.StatusOK).Send(content)
+}
+
+func exportPurchaseSupplierExcelAll(c *fiber.Ctx, items []dto.PurchaseSupplierDTO) error {
+	content, err := buildPurchaseSupplierAllWorkbook(items)
+	if err != nil {
+		return fiber.NewError(fiber.StatusInternalServerError, "failed to generate excel file")
+	}
+
+	filename := fmt.Sprintf("laporan-pembelian-supplier-all-%s.xlsx", time.Now().Format("2006-01-02-1504"))
+	c.Set("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
+	c.Set("Content-Disposition", fmt.Sprintf(`attachment; filename="%s"`, filename))
+	return c.Status(fiber.StatusOK).Send(content)
+}
+
+// buildPurchaseSupplierWorkbook creates a workbook with one sheet per supplier.
+func buildPurchaseSupplierWorkbook(items []dto.PurchaseSupplierDTO) ([]byte, error) {
+	file := excelize.NewFile()
+	defer file.Close()
+
+	defaultSheet := file.GetSheetName(file.GetActiveSheetIndex())
+
+	if len(items) == 0 {
+		if err := writePurchaseSupplierSheet(file, defaultSheet, dto.PurchaseSupplierDTO{}); err != nil {
+			return nil, err
+		}
+		buf, err := file.WriteToBuffer()
+		if err != nil {
+			return nil, err
+		}
+		return buf.Bytes(), nil
+	}
+
+	for idx, item := range items {
+		sheetName := sanitizePurchaseSupplierSheetName(purchaseSupplierName(item))
+		if sheetName == "" {
+			sheetName = fmt.Sprintf("Supplier %d", idx+1)
+		}
+
+		if idx == 0 {
+			if defaultSheet != sheetName {
+				if err := file.SetSheetName(defaultSheet, sheetName); err != nil {
+					return nil, err
+				}
+			}
+		} else {
+			if _, err := file.NewSheet(sheetName); err != nil {
+				return nil, err
+			}
+		}
+
+		if err := writePurchaseSupplierSheet(file, sheetName, item); err != nil {
+			return nil, err
+		}
+	}
+
+	buf, err := file.WriteToBuffer()
+	if err != nil {
+		return nil, err
+	}
+	return buf.Bytes(), nil
+}
+
+// buildPurchaseSupplierAllWorkbook creates a single-sheet workbook with all suppliers.
+func buildPurchaseSupplierAllWorkbook(items []dto.PurchaseSupplierDTO) ([]byte, error) {
+	file := excelize.NewFile()
+	defer file.Close()
+
+	const sheet = "Rekap Pembelian Supplier"
+	defaultSheet := file.GetSheetName(file.GetActiveSheetIndex())
+	if defaultSheet != sheet {
+		if err := file.SetSheetName(defaultSheet, sheet); err != nil {
+			return nil, err
+		}
+	}
+
+	if err := setPurchaseSupplierAllColumns(file, sheet); err != nil {
+		return nil, err
+	}
+	if err := setPurchaseSupplierAllHeaders(file, sheet); err != nil {
+		return nil, err
+	}
+	if err := writePurchaseSupplierAllRows(file, sheet, items); err != nil {
+		return nil, err
+	}
+	if err := file.SetPanes(sheet, &excelize.Panes{
+		Freeze:      true,
+		YSplit:      1,
+		TopLeftCell: "A2",
+		ActivePane:  "bottomLeft",
+	}); err != nil {
+		return nil, err
+	}
+
+	buf, err := file.WriteToBuffer()
+	if err != nil {
+		return nil, err
+	}
+	return buf.Bytes(), nil
+}
+
+var purchaseSupplierSheetHeaders = []string{
+	"No",
+	"Tanggal Terima",
+	"Tanggal PO",
+	"No. Referensi",
+	"Nama Produk",
+	"Tujuan",
+	"QTY",
+	"Harga Beli (Rp)",
+	"Value Harga Beli (Rp)",
+	"Transport (Rp)",
+	"Value Transport (Rp)",
+	"Jumlah (Rp)",
+	"Ekspedisi",
+	"Surat Jalan",
+}
+
+var purchaseSupplierAllSheetHeaders = append([]string{"Supplier"}, purchaseSupplierSheetHeaders...)
+
+var purchaseSupplierSheetColumnWidths = map[string]float64{
+	"A": 5,
+	"B": 14,
+	"C": 12,
+	"D": 16,
+	"E": 20,
+	"F": 20,
+	"G": 10,
+	"H": 20,
+	"I": 20,
+	"J": 22,
+	"K": 22,
+	"L": 16,
+	"M": 20,
+	"N": 20,
+}
+
+var purchaseSupplierAllSheetColumnWidths = map[string]float64{
+	"A": 24,
+	"B": 6,
+	"C": 14,
+	"D": 12,
+	"E": 16,
+	"F": 20,
+	"G": 20,
+	"H": 10,
+	"I": 20,
+	"J": 20,
+	"K": 22,
+	"L": 22,
+	"M": 16,
+	"N": 20,
+	"O": 20,
+}
+
+func writePurchaseSupplierSheet(file *excelize.File, sheet string, item dto.PurchaseSupplierDTO) error {
+	for col, width := range purchaseSupplierSheetColumnWidths {
+		if err := file.SetColWidth(sheet, col, col, width); err != nil {
+			return err
+		}
+	}
+
+	for i, h := range purchaseSupplierSheetHeaders {
+		col, _ := excelize.ColumnNumberToName(i + 1)
+		if err := file.SetCellValue(sheet, col+"1", h); err != nil {
+			return err
+		}
+	}
+
+	for i, row := range item.Rows {
+		rowNum := i + 2
+		rowStr := fmt.Sprintf("%d", rowNum)
+
+		values := purchaseSupplierRowCells(row, i+1)
+		for colIdx, val := range values {
+			col, _ := excelize.ColumnNumberToName(colIdx + 1)
+			if err := file.SetCellValue(sheet, col+rowStr, val); err != nil {
+				return err
+			}
+		}
+	}
+
+	// Summary row
+	totalRowNum := len(item.Rows) + 2
+	totalRowStr := fmt.Sprintf("%d", totalRowNum)
+	totalCells := map[string]interface{}{
+		"A": "Total",
+		"G": item.Summary.TotalQty,
+		"H": item.Summary.TotalUnitPrice,
+		"I": item.Summary.TotalPurchaseValue,
+		"J": item.Summary.TotalTransportUnitPrice,
+		"K": item.Summary.TotalTransportValue,
+		"L": item.Summary.TotalAmount,
+	}
+	for col, val := range totalCells {
+		if err := file.SetCellValue(sheet, col+totalRowStr, val); err != nil {
+			return err
+		}
+	}
+
+	return nil
+}
+
+func setPurchaseSupplierAllColumns(file *excelize.File, sheet string) error {
+	for col, width := range purchaseSupplierAllSheetColumnWidths {
+		if err := file.SetColWidth(sheet, col, col, width); err != nil {
+			return err
+		}
+	}
+	if err := file.SetRowHeight(sheet, 1, 24); err != nil {
+		return err
+	}
+	return nil
+}
+
+func setPurchaseSupplierAllHeaders(file *excelize.File, sheet string) error {
+	headerStyle, err := file.NewStyle(&excelize.Style{
+		Font: &excelize.Font{Bold: true, Color: "FFFFFF", Family: "Arial", Size: 10},
+		Fill: excelize.Fill{Type: "pattern", Pattern: 1, Color: []string{"4472C4"}},
+		Alignment: &excelize.Alignment{
+			Horizontal: "center",
+			Vertical:   "center",
+			WrapText:   true,
+		},
+		Border: []excelize.Border{
+			{Type: "left", Color: "000000", Style: 1},
+			{Type: "top", Color: "000000", Style: 1},
+			{Type: "bottom", Color: "000000", Style: 1},
+			{Type: "right", Color: "000000", Style: 1},
+		},
+	})
+	if err != nil {
+		return err
+	}
+
+	for i, h := range purchaseSupplierAllSheetHeaders {
+		col, _ := excelize.ColumnNumberToName(i + 1)
+		if err := file.SetCellValue(sheet, col+"1", h); err != nil {
+			return err
+		}
+	}
+
+	lastCol, _ := excelize.ColumnNumberToName(len(purchaseSupplierAllSheetHeaders))
+	return file.SetCellStyle(sheet, "A1", lastCol+"1", headerStyle)
+}
+
+func writePurchaseSupplierAllRows(file *excelize.File, sheet string, items []dto.PurchaseSupplierDTO) error {
+	borderStyle := []excelize.Border{
+		{Type: "left", Color: "000000", Style: 1},
+		{Type: "top", Color: "000000", Style: 1},
+		{Type: "bottom", Color: "000000", Style: 1},
+		{Type: "right", Color: "000000", Style: 1},
+	}
+
+	dataStyle, err := file.NewStyle(&excelize.Style{
+		Font:      &excelize.Font{Color: "000000", Family: "Arial", Size: 10},
+		Alignment: &excelize.Alignment{Vertical: "center", WrapText: true},
+		Border:    borderStyle,
+	})
+	if err != nil {
+		return err
+	}
+
+	totalStyle, err := file.NewStyle(&excelize.Style{
+		Font:      &excelize.Font{Bold: true, Color: "000000", Family: "Arial", Size: 10},
+		Fill:      excelize.Fill{Type: "pattern", Pattern: 1, Color: []string{"E2EFDA"}},
+		Alignment: &excelize.Alignment{Vertical: "center", WrapText: true},
+		Border:    borderStyle,
+	})
+	if err != nil {
+		return err
+	}
+
+	lastHeaderCol, _ := excelize.ColumnNumberToName(len(purchaseSupplierAllSheetHeaders))
+
+	currentRow := 2
+	for _, item := range items {
+		supplierName := purchaseSupplierName(item)
+
+		// Data rows
+		for seq, row := range item.Rows {
+			rowStr := fmt.Sprintf("%d", currentRow)
+			if err := file.SetCellValue(sheet, "A"+rowStr, supplierName); err != nil {
+				return err
+			}
+			values := purchaseSupplierRowCells(row, seq+1)
+			for colIdx, val := range values {
+				col, _ := excelize.ColumnNumberToName(colIdx + 2)
+				if err := file.SetCellValue(sheet, col+rowStr, val); err != nil {
+					return err
+				}
+			}
+			if err := file.SetCellStyle(sheet, "A"+rowStr, lastHeaderCol+rowStr, dataStyle); err != nil {
+				return err
+			}
+			currentRow++
+		}
+
+		// Summary row
+		totalRowStr := fmt.Sprintf("%d", currentRow)
+		totalCells := map[string]interface{}{
+			"A": supplierName,
+			"B": "Total",
+			"H": item.Summary.TotalQty,
+			"I": item.Summary.TotalUnitPrice,
+			"J": item.Summary.TotalPurchaseValue,
+			"K": item.Summary.TotalTransportUnitPrice,
+			"L": item.Summary.TotalTransportValue,
+			"M": item.Summary.TotalAmount,
+		}
+		for col, val := range totalCells {
+			if err := file.SetCellValue(sheet, col+totalRowStr, val); err != nil {
+				return err
+			}
+		}
+		if err := file.SetCellStyle(sheet, "A"+totalRowStr, lastHeaderCol+totalRowStr, totalStyle); err != nil {
+			return err
+		}
+		currentRow++
+
+		// Empty separator row
+		currentRow++
+	}
+
+	return nil
+}
+
+// purchaseSupplierRowCells returns cell values for one data row.
+func purchaseSupplierRowCells(row dto.PurchaseSupplierRowDTO, seq int) []interface{} {
+	productName := "-"
+	if row.Product != nil && strings.TrimSpace(row.Product.Name) != "" {
+		productName = row.Product.Name
+	}
+	warehouseName := "-"
+	if row.Warehouse != nil && strings.TrimSpace(row.Warehouse.Name) != "" {
+		warehouseName = row.Warehouse.Name
+	}
+
+	return []interface{}{
+		seq,
+		safePurchaseSupplierText(row.ReceiveDate),
+		safePurchaseSupplierText(row.PoDate),
+		safePurchaseSupplierText(row.PoNumber),
+		productName,
+		warehouseName,
+		row.Qty,
+		row.UnitPrice,
+		row.PurchaseValue,
+		row.TransportUnitPrice,
+		row.TransportValue,
+		row.TotalAmount,
+		safePurchaseSupplierText(row.Expedition),
+		safePurchaseSupplierText(row.DeliveryNumber),
+	}
+}
+
+func purchaseSupplierName(item dto.PurchaseSupplierDTO) string {
+	if item.Supplier != nil && strings.TrimSpace(item.Supplier.Name) != "" {
+		return item.Supplier.Name
+	}
+	return "Supplier"
+}
+
+func sanitizePurchaseSupplierSheetName(name string) string {
+	replacer := strings.NewReplacer(
+		":", " ", "\\", " ", "/", " ",
+		"?", " ", "*", " ", "[", " ", "]", " ",
+	)
+	sanitized := strings.TrimSpace(replacer.Replace(name))
+	if sanitized == "" {
+		return "Sheet"
+	}
+	runes := []rune(sanitized)
+	if len(runes) > 31 {
+		return string(runes[:31])
+	}
+	return sanitized
+}
+
+func safePurchaseSupplierText(s string) string {
+	t := strings.TrimSpace(s)
+	if t == "" {
+		return "-"
+	}
+	return t
+}

internal/modules/repports/services/repport.service.go

@@ -750,37 +750,28 @@ func (s *repportService) GetMarketing(c *fiber.Ctx, params *validation.Marketing
 		customerGroups[customerID] = append(customerGroups[customerID], dp)
 	}
 
+	// Aging untuk setiap MDP berdasarkan payment_allocations: LUNAS pakai last_payment_date,
+	// else pakai today.
 	agingMap := make(map[int]int)
-	for customerID := range customerGroups {
+	allMdpIDsForAging := make([]uint, 0)
-		transactions, err := s.CustomerPaymentRepo.GetCustomerPaymentTransactions(c.Context(), &customerID)
+	for _, dp := range deliveryProducts {
-		if err != nil {
+		allMdpIDsForAging = append(allMdpIDsForAging, dp.Id)
-			continue
 	}
-
+	mdpAllocSummaryForMarketing, err := s.fetchMdpAllocationSummary(c.Context(), allMdpIDsForAging)
-		initialBalance, err := s.CustomerPaymentRepo.GetInitialBalanceByCustomer(c.Context(), customerID)
 	if err != nil {
-			initialBalance = 0
+		return nil, 0, err
 	}
-
+	for _, dp := range deliveryProducts {
-		runningBalance := initialBalance
+		summary := mdpAllocSummaryForMarketing[dp.Id]
-		for i, tx := range transactions {
+		soDate := dp.MarketingProduct.Marketing.SoDate
-			if tx.TransactionType == "SALES" {
+		if customerPaymentStatusFromAllocation(dp.TotalPrice, summary.PaidAmount) == "LUNAS" && !summary.LastPaymentDate.IsZero() {
-				previousBalance := runningBalance
+			days := int(summary.LastPaymentDate.Sub(soDate).Hours() / 24)
-				runningBalance -= tx.TotalPrice
+			if days < 0 {
-				currentBalance := runningBalance
+				days = 0
-
+			}
-				_, paymentDate := s.determineSalesStatusAndPaymentDate(transactions, i, previousBalance, currentBalance)
+			agingMap[int(dp.Id)] = days
-
-				if paymentDate != nil {
-					agingDays := int(paymentDate.Sub(tx.TransDate).Hours() / 24)
-					agingMap[int(tx.TransactionID)] = agingDays
 		} else {
-					agingDays := int(time.Since(tx.TransDate).Hours() / 24)
+			agingMap[int(dp.Id)] = int(time.Since(soDate).Hours() / 24)
-					agingMap[int(tx.TransactionID)] = agingDays
-				}
-			} else if tx.TransactionType == "PAYMENT" {
-				runningBalance += tx.PaymentAmount
-			}
 		}
 	}
 
@@ -1169,28 +1160,39 @@ func (s *repportService) processCustomerPayment(ctx context.Context, customerID
 		return dto.CustomerPaymentReportItem{}, err
 	}
 
+	// Batch fetch payment allocation summaries untuk semua SALES rows (per MDP).
+	mdpIDs := make([]uint, 0)
+	for _, tx := range transactions {
+		if tx.TransactionType == "SALES" && tx.TransactionID > 0 {
+			mdpIDs = append(mdpIDs, uint(tx.TransactionID))
+		}
+	}
+	mdpAllocSummary, err := s.fetchMdpAllocationSummary(ctx, mdpIDs)
+	if err != nil {
+		return dto.CustomerPaymentReportItem{}, err
+	}
+
 	rows := make([]dto.CustomerPaymentReportRow, 0, len(transactions))
 	runningBalance := initialBalance
 
-	for i, tx := range transactions {
+	for _, tx := range transactions {
-
-		previousBalance := runningBalance
 
 		row := dto.ToCustomerPaymentReportRow(tx)
 
 		if tx.TransactionType == "SALES" {
 			runningBalance -= tx.TotalPrice
-			status, paymentDate := s.determineSalesStatusAndPaymentDate(transactions, i, previousBalance, runningBalance)
+			summary := mdpAllocSummary[uint(tx.TransactionID)]
-			row.Status = status
+			row.Status = customerPaymentStatusFromAllocation(tx.TotalPrice, summary.PaidAmount)
 
-			if status == "LUNAS" {
+			if row.Status == "LUNAS" && !summary.LastPaymentDate.IsZero() {
-				if paymentDate != nil {
+				days := int(summary.LastPaymentDate.Sub(tx.TransDate).Hours() / 24)
-					days := int(paymentDate.Sub(tx.TransDate).Hours() / 24)
+				if days < 0 {
-					row.AgingDay = &days
+					days = 0
-				} else {
-					days := 0
-					row.AgingDay = &days
 				}
+				row.AgingDay = &days
+			} else if row.Status == "LUNAS" {
+				zero := 0
+				row.AgingDay = &zero
 			} else {
 				days := int(time.Since(tx.TransDate).Hours() / 24)
 				row.AgingDay = &days
@@ -1262,91 +1264,19 @@ func (s *repportService) processCustomerPayment(ctx context.Context, customerID
 	return dto.ToCustomerPaymentReportItem(*customer, initialBalance, rows, summary), nil
 }
 
-func (s *repportService) determineSalesStatusAndPaymentDate(transactions []repportRepo.CustomerPaymentTransaction, currentIndex int, previousBalance, currentBalance float64) (string, *time.Time) {
+// customerPaymentStatusFromAllocation menentukan status per-MDP berdasarkan
-	currentSales := transactions[currentIndex]
+// SUM(payment_allocations.amount) vs MDP total_price.
-
+func customerPaymentStatusFromAllocation(totalPrice, paidAmount float64) string {
-	if previousBalance >= currentSales.TotalPrice {
+	if totalPrice <= fifoAllocationEpsilon {
-		type paymentAllocation struct {
+		return "LUNAS"
-			date     time.Time
-			amount   float64
-			consumed float64
 	}
-		allocations := []paymentAllocation{}
+	if paidAmount+fifoAllocationEpsilon >= totalPrice {
-		runningBalance := 0.0
+		return "LUNAS"
-
-		for i := 0; i < currentIndex; i++ {
-			if transactions[i].TransactionType == "PAYMENT" {
-				allocations = append(allocations, paymentAllocation{
-					date:     transactions[i].TransDate,
-					amount:   transactions[i].PaymentAmount,
-					consumed: 0,
-				})
-				runningBalance += transactions[i].PaymentAmount
-			} else if transactions[i].TransactionType == "SALES" {
-				salesAmount := transactions[i].TotalPrice
-				remainingToConsume := salesAmount
-
-				for j := range allocations {
-					if remainingToConsume <= 0 {
-						break
 	}
-					available := allocations[j].amount - allocations[j].consumed
+	if paidAmount > fifoAllocationEpsilon {
-					if available > 0 {
+		return "DIBAYAR SEBAGIAN"
-						consume := available
-						if consume > remainingToConsume {
-							consume = remainingToConsume
 	}
-						allocations[j].consumed += consume
+	return "BELUM LUNAS"
-						remainingToConsume -= consume
-					}
-				}
-				runningBalance -= salesAmount
-			}
-		}
-
-		amountNeeded := currentSales.TotalPrice
-		for _, alloc := range allocations {
-			available := alloc.amount - alloc.consumed
-			if available > 0 {
-				if amountNeeded <= available {
-					return "LUNAS", &alloc.date
-				} else {
-					amountNeeded -= available
-				}
-			}
-		}
-
-		if len(allocations) > 0 {
-			return "LUNAS", &allocations[0].date
-		}
-		return "LUNAS", nil
-	}
-
-	hasPartialPaymentFromBalance := previousBalance > 0 && previousBalance < currentSales.TotalPrice
-
-	futureBalance := currentBalance
-	hasPayment := false
-	var paymentDateThatMadeItLunas *time.Time
-
-	for i := currentIndex + 1; i < len(transactions); i++ {
-		if transactions[i].TransactionType == "PAYMENT" {
-			futureBalance += transactions[i].PaymentAmount
-			hasPayment = true
-
-			if futureBalance >= 0 {
-				paymentDateThatMadeItLunas = &transactions[i].TransDate
-				return "LUNAS", paymentDateThatMadeItLunas
-			}
-		} else if transactions[i].TransactionType == "SALES" {
-			futureBalance -= transactions[i].TotalPrice
-		}
-	}
-
-	if hasPayment || hasPartialPaymentFromBalance {
-		return "DIBAYAR SEBAGIAN", nil
-	}
-
-	return "BELUM LUNAS", nil
 }
 
 func mapRecordingToProductionResultDTO(record entity.Recording) dto.ProductionResultDTO {
@@ -1861,15 +1791,34 @@ func (s *repportService) GetDebtSupplier(c *fiber.Ctx, params *validation.DebtSu
 		DeltaBalance float64
 		CountTotals  bool
 	}
-	type debtSupplierAllocation struct {
+
-		RowIndex  int
+	// Batch fetch payment allocation summaries (per purchase + per expense) untuk semua supplier.
-		SortTime  time.Time
+	// FIFO matching dilakukan saat payment di-create/update; report tinggal baca dari DB.
-		Amount    float64
+	allPurchaseIDs := make([]uint, 0)
-		CalcAging func(endDate time.Time) int
+	allExpenseIDs := make([]uint64, 0)
+	for _, sid := range supplierIDs {
+		for _, p := range purchasesBySupplier[sid] {
+			allPurchaseIDs = append(allPurchaseIDs, p.Id)
 		}
-	type paymentAllocation struct {
+		for _, e := range expensesBySupplier[sid] {
-		Date   time.Time
+			allExpenseIDs = append(allExpenseIDs, e.Id)
-		Amount float64
+		}
+	}
+	purchaseAllocSummary, err := s.fetchPurchaseAllocationSummary(c.Context(), allPurchaseIDs)
+	if err != nil {
+		return nil, 0, err
+	}
+	expenseAllocSummary, err := s.fetchExpenseAllocationSummary(c.Context(), allExpenseIDs)
+	if err != nil {
+		return nil, 0, err
+	}
+
+	// rowRef tracks which combinedRows index belongs to which purchase/expense untuk update status di-akhir.
+	type rowRef struct {
+		Index    int
+		Kind     string // "PURCHASE" / "EXPENSE"
+		Purchase entity.Purchase
+		Expense  entity.Expense
 	}
 
 	for _, supplierID := range supplierIDs {
@@ -1884,7 +1833,7 @@ func (s *repportService) GetDebtSupplier(c *fiber.Ctx, params *validation.DebtSu
 		total := dto.DebtSupplierTotalDTO{}
 
 		combinedRows := make([]debtSupplierRowItem, 0, len(items)+len(paymentItems))
-		purchaseAllocations := make([]debtSupplierAllocation, 0, len(items))
+		rowRefs := make([]rowRef, 0, len(items)+len(expensesBySupplier[supplierID]))
 		for _, purchase := range items {
 			row := buildDebtSupplierRow(purchase, now, location)
 			sortTime := resolveDebtSupplierSortTime(purchase, params.FilterBy, location)
@@ -1896,13 +1845,7 @@ func (s *repportService) GetDebtSupplier(c *fiber.Ctx, params *validation.DebtSu
 				DeltaBalance: -row.TotalPrice,
 				CountTotals:  true,
 			})
-			capturedPurchase := purchase
+			rowRefs = append(rowRefs, rowRef{Index: rowIndex, Kind: "PURCHASE", Purchase: purchase})
-			purchaseAllocations = append(purchaseAllocations, debtSupplierAllocation{
-				RowIndex:  rowIndex,
-				SortTime:  sortTime,
-				Amount:    row.TotalPrice,
-				CalcAging: func(endDate time.Time) int { return calculateDebtSupplierAging(capturedPurchase, endDate, location) },
-			})
 		}
 
 		for _, exp := range expensesBySupplier[supplierID] {
@@ -1916,25 +1859,7 @@ func (s *repportService) GetDebtSupplier(c *fiber.Ctx, params *validation.DebtSu
 				DeltaBalance: -row.TotalPrice,
 				CountTotals:  true,
 			})
-			capturedExp := exp
+			rowRefs = append(rowRefs, rowRef{Index: rowIndex, Kind: "EXPENSE", Expense: exp})
-			purchaseAllocations = append(purchaseAllocations, debtSupplierAllocation{
-				RowIndex:  rowIndex,
-				SortTime:  sortTime,
-				Amount:    row.TotalPrice,
-				CalcAging: func(endDate time.Time) int { return calculateExpenseAging(capturedExp, endDate, location) },
-			})
-		}
-
-		paymentAllocations := make([]paymentAllocation, 0, len(paymentItems)+1)
-		initialAllocation := initialBalanceTotals[supplierID] + initialPaymentTotals[supplierID] - initialPurchaseTotals[supplierID]
-		paymentCarry := 0.0
-		if initialAllocation > 0 && len(purchaseAllocations) > 0 {
-			paymentAllocations = append(paymentAllocations, paymentAllocation{
-				Date:   purchaseAllocations[0].SortTime,
-				Amount: initialAllocation,
-			})
-		} else if initialAllocation < 0 {
-			paymentCarry = -initialAllocation
 		}
 
 		for _, payment := range paymentItems {
@@ -1947,51 +1872,29 @@ func (s *repportService) GetDebtSupplier(c *fiber.Ctx, params *validation.DebtSu
 				DeltaBalance: payment.Nominal,
 				CountTotals:  false,
 			})
-			paymentAllocations = append(paymentAllocations, paymentAllocation{
-				Date:   sortTime,
-				Amount: payment.Nominal,
-			})
 		}
 
-		if len(purchaseAllocations) > 0 && len(paymentAllocations) > 0 {
+		// Determine Status & Aging dari payment_allocations DB.
-			sort.SliceStable(purchaseAllocations, func(i, j int) bool {
+		for _, ref := range rowRefs {
-				return purchaseAllocations[i].SortTime.Before(purchaseAllocations[j].SortTime)
+			rowTotal := combinedRows[ref.Index].Row.TotalPrice
-			})
+			if rowTotal <= fifoAllocationEpsilon {
-			sort.SliceStable(paymentAllocations, func(i, j int) bool {
-				return paymentAllocations[i].Date.Before(paymentAllocations[j].Date)
-			})
-			remaining := make([]float64, len(purchaseAllocations))
-			for i := range purchaseAllocations {
-				remaining[i] = purchaseAllocations[i].Amount
-			}
-			purchaseIndex := 0
-			for _, pay := range paymentAllocations {
-				amount := pay.Amount
-				if amount <= 0 {
 				continue
 			}
-				if paymentCarry > 0 {
+			var summary paymentAllocationSummary
-					used := math.Min(amount, paymentCarry)
+			if ref.Kind == "PURCHASE" {
-					paymentCarry -= used
+				summary = purchaseAllocSummary[ref.Purchase.Id]
-					amount -= used
+			} else {
+				summary = expenseAllocSummary[ref.Expense.Id]
 			}
-				for amount > 0 && purchaseIndex < len(remaining) {
+			if summary.PaidAmount+fifoAllocationEpsilon < rowTotal {
-					if remaining[purchaseIndex] <= 0 {
-						purchaseIndex++
 				continue
 			}
-					used := math.Min(amount, remaining[purchaseIndex])
+			combinedRows[ref.Index].Row.Status = "Lunas"
-					remaining[purchaseIndex] -= used
+			if !summary.LastPaymentDate.IsZero() {
-					amount -= used
+				if ref.Kind == "PURCHASE" {
-					if remaining[purchaseIndex] <= 0.000001 {
+					combinedRows[ref.Index].Row.Aging = calculateDebtSupplierAging(ref.Purchase, summary.LastPaymentDate.In(location), location)
-						allocation := purchaseAllocations[purchaseIndex]
+				} else {
-						combinedRows[allocation.RowIndex].Row.Status = "Lunas"
+					combinedRows[ref.Index].Row.Aging = calculateExpenseAging(ref.Expense, summary.LastPaymentDate.In(location), location)
-						combinedRows[allocation.RowIndex].Row.Aging = allocation.CalcAging(pay.Date)
-						purchaseIndex++
-					}
-				}
-				if purchaseIndex >= len(remaining) {
-					break
 				}
 			}
 		}
@@ -2168,6 +2071,115 @@ func buildDebtSupplierPaymentRow(payment entity.Payment, loc *time.Location) dto
 	}
 }
 
+// fifoAllocationEpsilon untuk float comparison saat membandingkan paid vs total.
+const fifoAllocationEpsilon = 0.001
+
+// paymentAllocationSummary aggregates per-document paid amount + latest payment date
+// from payment_allocations table, sebagai pengganti FIFO greedy in-memory.
+type paymentAllocationSummary struct {
+	PaidAmount      float64
+	LastPaymentDate time.Time
+}
+
+// fetchPurchaseAllocationSummary returns map[purchase_id]{paid_amount, last_payment_date}.
+// paid_amount = SUM(payment_allocations.amount) untuk semua items dalam purchase.
+// last_payment_date = MAX(payments.payment_date) untuk allocation tersebut.
+func (s *repportService) fetchPurchaseAllocationSummary(ctx context.Context, purchaseIDs []uint) (map[uint]paymentAllocationSummary, error) {
+	out := make(map[uint]paymentAllocationSummary)
+	if len(purchaseIDs) == 0 {
+		return out, nil
+	}
+	type row struct {
+		PurchaseID  uint
+		Total       float64
+		LastPayment *time.Time
+	}
+	var rows []row
+	if err := s.db.WithContext(ctx).
+		Table("payment_allocations pa").
+		Joins("JOIN purchase_items pi ON pi.id = pa.purchase_item_id").
+		Joins("JOIN payments p ON p.id = pa.payment_id").
+		Select("pi.purchase_id AS purchase_id, SUM(pa.amount) AS total, MAX(p.payment_date) AS last_payment").
+		Where("pi.purchase_id IN ?", purchaseIDs).
+		Group("pi.purchase_id").
+		Scan(&rows).Error; err != nil {
+		return nil, err
+	}
+	for _, r := range rows {
+		summary := paymentAllocationSummary{PaidAmount: r.Total}
+		if r.LastPayment != nil {
+			summary.LastPaymentDate = *r.LastPayment
+		}
+		out[r.PurchaseID] = summary
+	}
+	return out, nil
+}
+
+// fetchExpenseAllocationSummary returns map[expense_id]{paid_amount, last_payment_date}.
+// Allocation di expense_realization_id → JOIN expense_nonstocks → expenses.id.
+func (s *repportService) fetchExpenseAllocationSummary(ctx context.Context, expenseIDs []uint64) (map[uint64]paymentAllocationSummary, error) {
+	out := make(map[uint64]paymentAllocationSummary)
+	if len(expenseIDs) == 0 {
+		return out, nil
+	}
+	type row struct {
+		ExpenseID   uint64
+		Total       float64
+		LastPayment *time.Time
+	}
+	var rows []row
+	if err := s.db.WithContext(ctx).
+		Table("payment_allocations pa").
+		Joins("JOIN expense_realizations er ON er.id = pa.expense_realization_id").
+		Joins("JOIN expense_nonstocks en ON en.id = er.expense_nonstock_id").
+		Joins("JOIN payments p ON p.id = pa.payment_id").
+		Select("en.expense_id AS expense_id, SUM(pa.amount) AS total, MAX(p.payment_date) AS last_payment").
+		Where("en.expense_id IN ?", expenseIDs).
+		Group("en.expense_id").
+		Scan(&rows).Error; err != nil {
+		return nil, err
+	}
+	for _, r := range rows {
+		summary := paymentAllocationSummary{PaidAmount: r.Total}
+		if r.LastPayment != nil {
+			summary.LastPaymentDate = *r.LastPayment
+		}
+		out[r.ExpenseID] = summary
+	}
+	return out, nil
+}
+
+// fetchMdpAllocationSummary returns map[mdp_id]{paid_amount, last_payment_date}.
+func (s *repportService) fetchMdpAllocationSummary(ctx context.Context, mdpIDs []uint) (map[uint]paymentAllocationSummary, error) {
+	out := make(map[uint]paymentAllocationSummary)
+	if len(mdpIDs) == 0 {
+		return out, nil
+	}
+	type row struct {
+		MdpID       uint
+		Total       float64
+		LastPayment *time.Time
+	}
+	var rows []row
+	if err := s.db.WithContext(ctx).
+		Table("payment_allocations pa").
+		Joins("JOIN payments p ON p.id = pa.payment_id").
+		Select("pa.marketing_delivery_product_id AS mdp_id, SUM(pa.amount) AS total, MAX(p.payment_date) AS last_payment").
+		Where("pa.marketing_delivery_product_id IN ?", mdpIDs).
+		Group("pa.marketing_delivery_product_id").
+		Scan(&rows).Error; err != nil {
+		return nil, err
+	}
+	for _, r := range rows {
+		summary := paymentAllocationSummary{PaidAmount: r.Total}
+		if r.LastPayment != nil {
+			summary.LastPaymentDate = *r.LastPayment
+		}
+		out[r.MdpID] = summary
+	}
+	return out, nil
+}
+
 func resolveDebtSupplierSortTime(purchase entity.Purchase, filterBy string, loc *time.Location) time.Time {
 	if strings.EqualFold(strings.TrimSpace(filterBy), "po_date") {
 		if purchase.PoDate != nil && !purchase.PoDate.IsZero() {
@@ -2271,10 +2283,10 @@ func buildDebtSupplierExpenseRow(exp entity.Expense, now time.Time, loc *time.Lo
 		aging = int(endDay.Sub(startDay).Hours() / 24)
 	}
 
-	totalPrice := 0.0
+	// TotalPrice pakai expense.GrandTotal (= SUM realisasi) supaya konsisten dengan
-	for _, ns := range exp.Nonstocks {
+	// FIFO allocation yang juga pakai realisasi. Hindari pakai SUM nonstock pengajuan
-		totalPrice += ns.Qty * ns.Price
+	// karena bisa beda nilai dari realisasi → mismatch dengan paid_amount → status salah.
-	}
+	totalPrice := exp.GrandTotal
 
 	var area *areaDTO.AreaRelationDTO
 	if exp.Location != nil && exp.Location.Area.Id != 0 {