add command for cleanup relesed stock allocations

cmd/cleanup-released-stock-allocations/main.go

@@ -0,0 +1,304 @@
+// Command cleanup-released-stock-allocations menghapus baris stock_allocations
+// dengan status='RELEASED' yang sudah lewat masa retensi.
+//
+// Baris RELEASED muncul dari operasi Rollback / Reflow FIFO v2. Closing reports
+// dan flow bisnis hanya membaca status='ACTIVE', sehingga RELEASED rows aman
+// dihapus setelah masa retensi tertentu (default 90 hari).
+//
+// Cara pakai:
+//
+//	go run ./cmd/cleanup-released-stock-allocations/                          # dry-run
+//	go run ./cmd/cleanup-released-stock-allocations/ -apply                   # apply (90 hari)
+//	go run ./cmd/cleanup-released-stock-allocations/ -apply -retention-days=30
+//	go run ./cmd/cleanup-released-stock-allocations/ -apply -batch-size=5000
+//	go run ./cmd/cleanup-released-stock-allocations/ -apply -skip-vacuum
+package main
+
+import (
+	"context"
+	"encoding/json"
+	"flag"
+	"fmt"
+	"log"
+	"os"
+	"strings"
+	"time"
+
+	"gitlab.com/mbugroup/lti-api.git/internal/config"
+	"gitlab.com/mbugroup/lti-api.git/internal/database"
+	"gitlab.com/mbugroup/lti-api.git/internal/entities"
+	"gorm.io/gorm"
+)
+
+const (
+	outputTable = "table"
+	outputJSON  = "json"
+)
+
+type options struct {
+	Apply          bool
+	Output         string
+	DBSSLMode      string
+	RetentionDays  int
+	BatchSize      int
+	SkipVacuum     bool
+}
+
+type sizeStat struct {
+	TableSize string `json:"table_size" gorm:"column:table_size"`
+	TotalSize string `json:"total_size" gorm:"column:total_size"`
+	RowCount  int64  `json:"row_count"  gorm:"column:row_count"`
+}
+
+type runSummary struct {
+	Mode             string   `json:"mode"`
+	RetentionDays    int      `json:"retention_days"`
+	CutoffTime       string   `json:"cutoff_time"`
+	BatchSize        int      `json:"batch_size"`
+	CandidateRows    int64    `json:"candidate_rows"`
+	DeletedRows      int64    `json:"deleted_rows,omitempty"`
+	BatchesExecuted  int      `json:"batches_executed,omitempty"`
+	BeforeSize       sizeStat `json:"before_size"`
+	AfterSize        sizeStat `json:"after_size,omitempty"`
+	DurationSeconds  float64  `json:"duration_seconds"`
+	VacuumExecuted   bool     `json:"vacuum_executed"`
+	OverallStatus    string   `json:"overall_status"`
+}
+
+func main() {
+	opts, err := parseFlags()
+	if err != nil {
+		log.Fatalf("invalid flags: %v", err)
+	}
+
+	if opts.DBSSLMode != "" {
+		config.DBSSLMode = opts.DBSSLMode
+	}
+
+	ctx := context.Background()
+	db := database.Connect(config.DBHost, config.DBName)
+
+	start := time.Now()
+	cutoff := time.Now().Add(-time.Duration(opts.RetentionDays) * 24 * time.Hour)
+
+	summary := runSummary{
+		RetentionDays: opts.RetentionDays,
+		CutoffTime:    cutoff.UTC().Format(time.RFC3339),
+		BatchSize:     opts.BatchSize,
+		OverallStatus: "PASS",
+	}
+
+	// Ambil ukuran tabel sebelum cleanup
+	before, err := fetchSizeStat(ctx, db)
+	if err != nil {
+		log.Fatalf("failed to fetch initial size: %v", err)
+	}
+	summary.BeforeSize = before
+
+	// Hitung kandidat row
+	candidate, err := countCandidates(ctx, db, cutoff)
+	if err != nil {
+		log.Fatalf("failed to count candidates: %v", err)
+	}
+	summary.CandidateRows = candidate
+
+	if candidate == 0 {
+		summary.Mode = modeLabel(opts.Apply)
+		summary.DurationSeconds = time.Since(start).Seconds()
+		fmt.Printf("No RELEASED rows older than %d days found. Nothing to do.\n", opts.RetentionDays)
+		render(opts.Output, summary)
+		return
+	}
+
+	if !opts.Apply {
+		summary.Mode = "DRY_RUN"
+		summary.DurationSeconds = time.Since(start).Seconds()
+		render(opts.Output, summary)
+		fmt.Println()
+		fmt.Println("Re-run with -apply to actually delete the rows above.")
+		return
+	}
+
+	summary.Mode = "APPLY"
+
+	deleted, batches, err := applyCleanup(ctx, db, cutoff, opts.BatchSize)
+	summary.DeletedRows = deleted
+	summary.BatchesExecuted = batches
+	if err != nil {
+		summary.OverallStatus = "FAIL"
+		render(opts.Output, summary)
+		log.Fatalf("apply failed after %d batches (%d rows deleted): %v", batches, deleted, err)
+	}
+
+	// VACUUM ANALYZE supaya space benar-benar dibebaskan ke OS
+	if !opts.SkipVacuum {
+		if err := runVacuum(ctx, db); err != nil {
+			// VACUUM gagal jangan-mengaborkan, log saja
+			log.Printf("WARN: VACUUM ANALYZE gagal: %v", err)
+		} else {
+			summary.VacuumExecuted = true
+		}
+	}
+
+	after, err := fetchSizeStat(ctx, db)
+	if err != nil {
+		log.Printf("WARN: gagal ambil ukuran tabel setelah cleanup: %v", err)
+	} else {
+		summary.AfterSize = after
+	}
+
+	summary.DurationSeconds = time.Since(start).Seconds()
+	render(opts.Output, summary)
+}
+
+func parseFlags() (*options, error) {
+	var opts options
+	flag.BoolVar(&opts.Apply, "apply", false, "Apply deletion (omit for dry-run)")
+	flag.StringVar(&opts.Output, "output", outputTable, "Output format: table or json")
+	flag.StringVar(&opts.DBSSLMode, "db-sslmode", "", "Database sslmode override")
+	flag.IntVar(&opts.RetentionDays, "retention-days", 90, "Keep RELEASED rows newer than N days")
+	flag.IntVar(&opts.BatchSize, "batch-size", 10000, "Rows deleted per transaction")
+	flag.BoolVar(&opts.SkipVacuum, "skip-vacuum", false, "Skip VACUUM ANALYZE after cleanup")
+	flag.Parse()
+
+	opts.Output = strings.ToLower(strings.TrimSpace(opts.Output))
+	opts.DBSSLMode = strings.TrimSpace(opts.DBSSLMode)
+
+	if opts.Output == "" {
+		opts.Output = outputTable
+	}
+	if opts.Output != outputTable && opts.Output != outputJSON {
+		return nil, fmt.Errorf("unsupported --output=%s", opts.Output)
+	}
+	if opts.RetentionDays < 0 {
+		return nil, fmt.Errorf("retention-days must be >= 0, got %d", opts.RetentionDays)
+	}
+	if opts.BatchSize <= 0 {
+		return nil, fmt.Errorf("batch-size must be > 0, got %d", opts.BatchSize)
+	}
+
+	return &opts, nil
+}
+
+func countCandidates(ctx context.Context, db *gorm.DB, cutoff time.Time) (int64, error) {
+	var count int64
+	err := db.WithContext(ctx).
+		Table("stock_allocations").
+		Where("status = ?", entities.StockAllocationStatusReleased).
+		Where("released_at IS NOT NULL AND released_at < ?", cutoff).
+		Count(&count).Error
+	return count, err
+}
+
+func applyCleanup(ctx context.Context, db *gorm.DB, cutoff time.Time, batchSize int) (int64, int, error) {
+	var totalDeleted int64
+	batches := 0
+
+	for {
+		var affected int64
+		err := db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
+			// Pakai CTE supaya LIMIT bisa dipakai bersama DELETE di PostgreSQL.
+			// `released_at IS NOT NULL` defensif — rows lama dari migrasi mungkin
+			// NULL meski status=RELEASED.
+			res := tx.Exec(`
+DELETE FROM stock_allocations
+WHERE id IN (
+    SELECT id FROM stock_allocations
+    WHERE status = ?
+      AND released_at IS NOT NULL
+      AND released_at < ?
+    ORDER BY id ASC
+    LIMIT ?
+)
+`, entities.StockAllocationStatusReleased, cutoff, batchSize)
+			if res.Error != nil {
+				return res.Error
+			}
+			affected = res.RowsAffected
+			return nil
+		})
+		if err != nil {
+			return totalDeleted, batches, err
+		}
+
+		if affected == 0 {
+			break
+		}
+
+		totalDeleted += affected
+		batches++
+		log.Printf("batch %d: deleted %d rows (running total: %d)", batches, affected, totalDeleted)
+
+		if affected < int64(batchSize) {
+			break
+		}
+	}
+
+	return totalDeleted, batches, nil
+}
+
+func runVacuum(ctx context.Context, db *gorm.DB) error {
+	// VACUUM tidak bisa di-jalankan dalam transaksi.
+	// gorm SkipDefaultTransaction sudah true, tapi tetap aman menggunakan raw DB.
+	sqlDB, err := db.DB()
+	if err != nil {
+		return err
+	}
+	_, err = sqlDB.ExecContext(ctx, "VACUUM ANALYZE stock_allocations")
+	return err
+}
+
+func fetchSizeStat(ctx context.Context, db *gorm.DB) (sizeStat, error) {
+	var stat sizeStat
+	err := db.WithContext(ctx).Raw(`
+SELECT
+    pg_size_pretty(pg_relation_size('stock_allocations')) AS table_size,
+    pg_size_pretty(pg_total_relation_size('stock_allocations')) AS total_size,
+    (SELECT COUNT(*) FROM stock_allocations)::bigint AS row_count
+`).Scan(&stat).Error
+	return stat, err
+}
+
+func modeLabel(apply bool) string {
+	if apply {
+		return "APPLY"
+	}
+	return "DRY_RUN"
+}
+
+func render(mode string, summary runSummary) {
+	if mode == outputJSON {
+		enc := json.NewEncoder(os.Stdout)
+		enc.SetIndent("", "  ")
+		_ = enc.Encode(summary)
+		return
+	}
+
+	fmt.Printf("=== Cleanup RELEASED stock_allocations ===\n")
+	fmt.Printf("Mode               : %s\n", summary.Mode)
+	fmt.Printf("Retention days     : %d (cutoff < %s)\n", summary.RetentionDays, summary.CutoffTime)
+	fmt.Printf("Batch size         : %d\n", summary.BatchSize)
+	fmt.Printf("Candidate rows     : %d\n", summary.CandidateRows)
+
+	fmt.Printf("\n--- Before ---\n")
+	fmt.Printf("Total rows         : %d\n", summary.BeforeSize.RowCount)
+	fmt.Printf("Table size         : %s\n", summary.BeforeSize.TableSize)
+	fmt.Printf("Total size (idx)   : %s\n", summary.BeforeSize.TotalSize)
+
+	if summary.Mode == "APPLY" {
+		fmt.Printf("\n--- Apply ---\n")
+		fmt.Printf("Deleted rows       : %d\n", summary.DeletedRows)
+		fmt.Printf("Batches executed   : %d\n", summary.BatchesExecuted)
+		fmt.Printf("VACUUM executed    : %v\n", summary.VacuumExecuted)
+
+		if summary.AfterSize.RowCount > 0 || summary.AfterSize.TableSize != "" {
+			fmt.Printf("\n--- After ---\n")
+			fmt.Printf("Total rows         : %d\n", summary.AfterSize.RowCount)
+			fmt.Printf("Table size         : %s\n", summary.AfterSize.TableSize)
+			fmt.Printf("Total size (idx)   : %s\n", summary.AfterSize.TotalSize)
+		}
+	}
+
+	fmt.Printf("\nDuration           : %.2fs\n", summary.DurationSeconds)
+	fmt.Printf("Overall status     : %s\n", summary.OverallStatus)
+}

internal/database/migrations/20260529085659_normalize_po_number_to_pr_pattern.down.sql

@@ -1,8 +0,0 @@
--- IRREVERSIBLE migration: po_number lama (counter-based) tidak di-backup
--- saat UP karena user secara eksplisit pilih "tanpa backup table".
--- Down ini hanya raise notice supaya operator sadar harus restore dari
--- DB-level backup terpisah kalau memang perlu rollback.
-DO $$
-BEGIN
-  RAISE NOTICE 'WARNING: Migration 20260529143940_normalize_po_number_to_pr_pattern is irreversible. Original counter-based PO numbers were not backed up. Restore from DB-level backup if rollback is required.';
-END $$;

internal/database/migrations/20260529085659_normalize_po_number_to_pr_pattern.up.sql

@@ -1,87 +0,0 @@
-BEGIN;
-
--- ============================================================
--- Normalize purchases.po_number agar mengikuti pr_number (swap prefix).
--- Contoh: pr_number='PR-LTI-0050' -> po_number='PO-LTI-0050'
---
--- Konteks: sebelumnya pr_number dan po_number punya counter sequential
--- terpisah (lihat purchase.repository.go NextPrNumber / NextPoNumber yang
--- dihapus seiring migration ini), sehingga selalu diverge. Setelah
--- perubahan code (ApproveManagerPurchase derive PO dari PR), historis
--- perlu di-backfill supaya konsisten.
---
--- Juga update expenses.po_number (snapshot dari expense_bridge.go)
--- supaya konsisten dengan purchases.
---
--- Constraint uq_purchases_po_number adalah NOT DEFERRABLE (per-row check),
--- jadi single UPDATE bulk gagal di swap-conflict (contoh: row A mau jadi
--- 'PO-LTI-0700' tapi row B masih punya 'PO-LTI-0700' -> error 23505).
--- Solusi: capture target ke temp table, NULL dulu, baru set nilai derived.
---
--- IRREVERSIBLE: nilai po_number lama (counter-based) tidak di-backup.
--- Kalau ada kegagalan di tengah, COMMIT tidak terjadi -> ROLLBACK otomatis.
--- ============================================================
-
--- 1. Capture target IDs (snapshot rencana update — sebelum perubahan apapun)
-CREATE TEMP TABLE _purchases_po_normalize_ids ON COMMIT DROP AS
-SELECT id
-FROM purchases
-WHERE po_number IS NOT NULL
-  AND pr_number LIKE 'PR-LTI-%'
-  AND po_number <> REPLACE(pr_number, 'PR-LTI-', 'PO-LTI-');
-
--- 2. Update expenses DULU — join via current po_number masih valid sebelum step 3-4
-UPDATE expenses e
-SET po_number = REPLACE(p.pr_number, 'PR-LTI-', 'PO-LTI-')
-FROM purchases p
-JOIN _purchases_po_normalize_ids n ON n.id = p.id
-WHERE e.po_number = p.po_number
-  AND e.po_number IS NOT NULL
-  AND e.po_number <> '';
-
--- 3. NULL-kan purchases.po_number untuk target — lepas constraint conflict
-UPDATE purchases
-SET po_number = NULL
-WHERE id IN (SELECT id FROM _purchases_po_normalize_ids);
-
--- 4. Set nilai derived dari pr_number (sekarang aman karena slot lama sudah NULL)
-UPDATE purchases p
-SET po_number = REPLACE(p.pr_number, 'PR-LTI-', 'PO-LTI-')
-FROM _purchases_po_normalize_ids n
-WHERE p.id = n.id;
-
--- 5. Sanity check — fail (auto-rollback) kalau masih ada mismatch
-DO $$
-DECLARE
-  v_mismatch_purchases INT;
-  v_mismatch_expenses INT;
-  v_target_count INT;
-BEGIN
-  SELECT COUNT(*) INTO v_target_count FROM _purchases_po_normalize_ids;
-
-  SELECT COUNT(*) INTO v_mismatch_purchases
-  FROM purchases
-  WHERE po_number IS NOT NULL
-    AND pr_number LIKE 'PR-LTI-%'
-    AND po_number <> REPLACE(pr_number, 'PR-LTI-', 'PO-LTI-');
-
-  IF v_mismatch_purchases > 0 THEN
-    RAISE EXCEPTION 'Normalize failed: % purchases rows still have mismatched po_number', v_mismatch_purchases;
-  END IF;
-
-  SELECT COUNT(*) INTO v_mismatch_expenses
-  FROM expenses e
-  JOIN purchases p ON e.po_number = p.po_number
-  WHERE p.pr_number LIKE 'PR-LTI-%'
-    AND e.po_number IS NOT NULL
-    AND e.po_number <> ''
-    AND e.po_number <> REPLACE(p.pr_number, 'PR-LTI-', 'PO-LTI-');
-
-  IF v_mismatch_expenses > 0 THEN
-    RAISE EXCEPTION 'Normalize failed: % expenses rows still have mismatched po_number', v_mismatch_expenses;
-  END IF;
-
-  RAISE NOTICE 'Normalize complete: % purchases rows updated', v_target_count;
-END $$;
-
-COMMIT;

internal/modules/purchases/repositories/purchase.repository.go

@@ -24,6 +24,7 @@ type PurchaseRepository interface {
 	UpdateReceivingDetails(ctx context.Context, purchaseID uint, updates []PurchaseReceivingUpdate) error
 	DeleteItems(ctx context.Context, purchaseID uint, itemIDs []uint) error
 	NextPrNumber(ctx context.Context, tx *gorm.DB) (string, error)
+	NextPoNumber(ctx context.Context, tx *gorm.DB) (string, error)
 	BackfillProjectFlockKandang(ctx context.Context, purchaseID uint) error
 	SoftDeleteByProjectFlockKandangIDs(ctx context.Context, projectFlockKandangIDs []uint) error
 	GetItemsByProjectFlockID(ctx context.Context, projectFlockID uint) ([]entity.PurchaseItem, error)
@@ -368,8 +369,9 @@ func (r *PurchaseRepositoryImpl) NextPrNumber(ctx context.Context, tx *gorm.DB)
 	return r.generateSequentialNumber(ctx, tx, "pr_number", utils.PurchasePRNumberPrefix, utils.PurchaseNumberPadding)
 }
 
-// NOTE: NextPoNumber dihapus per migration 20260529143940 — po_number sekarang
+func (r *PurchaseRepositoryImpl) NextPoNumber(ctx context.Context, tx *gorm.DB) (string, error) {
-// di-derive dari pr_number (swap prefix) via derivePoFromPr di purchase.service.go.
+	return r.generateSequentialNumber(ctx, tx, "po_number", utils.PurchasePONumberPrefix, utils.PurchaseNumberPadding)
+}
 
 func (r *PurchaseRepositoryImpl) generateSequentialNumber(ctx context.Context, tx *gorm.DB, column, prefix string, padding int) (string, error) {
 	db := tx

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

@@ -779,7 +779,8 @@ func (s *purchaseService) ApproveManagerPurchase(c *fiber.Ctx, id uint, req *val
 	transactionErr := s.PurchaseRepo.DB().WithContext(c.Context()).Transaction(func(tx *gorm.DB) error {
 		updateData := map[string]any{}
 		if !hasExistingPO {
-			code, err := derivePoFromPr(purchase.PrNumber)
+			repoTx := rPurchase.NewPurchaseRepository(tx)
+			code, err := repoTx.NextPoNumber(c.Context(), tx)
 			if err != nil {
 				return err
 			}
@@ -2512,18 +2513,6 @@ func parseApprovalActionInput(raw string) (entity.ApprovalAction, error) {
 	}
 }
 
-// derivePoFromPr menghasilkan po_number dari pr_number dengan swap prefix.
-// Contoh: "PR-LTI-0050" -> "PO-LTI-0050". Mengembalikan error kalau pr_number
-// tidak diawali prefix standar — caller harus memastikan PR sudah valid.
-func derivePoFromPr(prNumber string) (string, error) {
-	trimmed := strings.TrimSpace(prNumber)
-	if !strings.HasPrefix(trimmed, utils.PurchasePRNumberPrefix) {
-		return "", fmt.Errorf("invalid pr_number %q: missing prefix %q", trimmed, utils.PurchasePRNumberPrefix)
-	}
-	suffix := strings.TrimPrefix(trimmed, utils.PurchasePRNumberPrefix)
-	return utils.PurchasePONumberPrefix + suffix, nil
-}
-
 func (s *purchaseService) rejectAndReload(
 	c *fiber.Ctx,
 	step approvalutils.ApprovalStep,