normalize data po number to pr number and fix logic to fill field PO number

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

@@ -1,304 +0,0 @@
-// 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

@@ -0,0 +1,8 @@
+-- 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

@@ -0,0 +1,87 @@
+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,7 +24,6 @@ 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)
@@ -369,9 +368,8 @@ func (r *PurchaseRepositoryImpl) NextPrNumber(ctx context.Context, tx *gorm.DB)
 	return r.generateSequentialNumber(ctx, tx, "pr_number", utils.PurchasePRNumberPrefix, utils.PurchaseNumberPadding)
 }
 
-func (r *PurchaseRepositoryImpl) NextPoNumber(ctx context.Context, tx *gorm.DB) (string, error) {
+// NOTE: NextPoNumber dihapus per migration 20260529143940 — po_number sekarang
-	return r.generateSequentialNumber(ctx, tx, "po_number", utils.PurchasePONumberPrefix, utils.PurchaseNumberPadding)
+// di-derive dari pr_number (swap prefix) via derivePoFromPr di purchase.service.go.
-}
 
 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,8 +779,7 @@ 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 {
-			repoTx := rPurchase.NewPurchaseRepository(tx)
+			code, err := derivePoFromPr(purchase.PrNumber)
-			code, err := repoTx.NextPoNumber(c.Context(), tx)
 			if err != nil {
 				return err
 			}
@@ -2513,6 +2512,18 @@ 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,