lti-api/cmd/delete-adjustments/main.go

package main

import (
	"context"
	"flag"
	"fmt"
	"log"
	"os"
	"sort"
	"strconv"
	"strings"
	"time"

	commonSvc "gitlab.com/mbugroup/lti-api.git/internal/common/service"
	"gitlab.com/mbugroup/lti-api.git/internal/config"
	"gitlab.com/mbugroup/lti-api.git/internal/database"
	entity "gitlab.com/mbugroup/lti-api.git/internal/entities"
	"gitlab.com/mbugroup/lti-api.git/internal/utils/fifo"
	"gorm.io/gorm"
)

type adjustmentRow struct {
	ID                 uint      `gorm:"column:id"`
	ProductWarehouseID uint      `gorm:"column:product_warehouse_id"`
	ProductID          uint      `gorm:"column:product_id"`
	FunctionCode       string    `gorm:"column:function_code"`
	TotalQty           float64   `gorm:"column:total_qty"`
	UsageQty           float64   `gorm:"column:usage_qty"`
	PendingQty         float64   `gorm:"column:pending_qty"`
	StockLogIncrease   float64   `gorm:"column:stock_log_increase"`
	StockLogDecrease   float64   `gorm:"column:stock_log_decrease"`
	CreatedAt          time.Time `gorm:"column:created_at"`
}

type routeResolution struct {
	FlagGroupCode string `gorm:"column:flag_group_code"`
	Lane          string `gorm:"column:lane"`
	FunctionCode  string `gorm:"column:function_code"`
}

func main() {
	var (
		idsRaw string
		apply  bool
	)

	flag.StringVar(&idsRaw, "ids", "", "Comma-separated adjustment IDs (required), example: 1,2")
	flag.BoolVar(&apply, "apply", false, "Apply delete. If false, run as dry-run")
	flag.Parse()

	ids, err := parseIDs(idsRaw)
	if err != nil {
		log.Fatalf("invalid --ids: %v", err)
	}
	if len(ids) == 0 {
		log.Fatal("--ids is required")
	}

	ctx := context.Background()
	db := database.Connect(config.DBHost, config.DBName)

	fifoStockV2Svc := commonSvc.NewFifoStockV2Service(db, nil)

	adjustments, err := loadAdjustments(ctx, db, ids)
	if err != nil {
		log.Fatalf("failed to load adjustments: %v", err)
	}
	if len(adjustments) == 0 {
		log.Fatal("no adjustments found for provided IDs")
	}

	sort.Slice(adjustments, func(i, j int) bool {
		return adjustments[i].ID < adjustments[j].ID
	})

	fmt.Printf("Mode: %s\n", modeLabel(apply))
	fmt.Printf("Adjustments loaded: %d\n\n", len(adjustments))

	success := 0
	failed := 0
	skipped := 0

	for _, adj := range adjustments {
		if strings.TrimSpace(adj.FunctionCode) == "" {
			fmt.Printf("SKIP adj=%d reason=function_code empty\n", adj.ID)
			skipped++
			continue
		}

		route, err := resolveRouteByFunctionCode(ctx, db, adj.ProductID, strings.ToUpper(strings.TrimSpace(adj.FunctionCode)))
		if err != nil {
			fmt.Printf("FAIL adj=%d error=resolve route: %v\n", adj.ID, err)
			failed++
			continue
		}

		switch route.Lane {
		case "USABLE":
			desiredQty := adj.UsageQty + adj.PendingQty
			if desiredQty <= 0 && adj.StockLogDecrease > 0 {
				desiredQty = adj.StockLogDecrease
			}

			activeAlloc, err := countActiveUsableAllocations(ctx, db, fifo.UsableKeyAdjustmentOut.String(), adj.ID)
			if err != nil {
				fmt.Printf("FAIL adj=%d error=count usable allocations: %v\n", adj.ID, err)
				failed++
				continue
			}

			fmt.Printf(
				"PLAN adj=%d lane=USABLE function=%s usage=%.3f pending=%.3f active_alloc=%d action=reflow_to_zero+delete\n",
				adj.ID,
				route.FunctionCode,
				adj.UsageQty,
				adj.PendingQty,
				activeAlloc,
			)

			if !apply {
				skipped++
				continue
			}

			err = db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
				reflowReq := commonSvc.FifoStockV2ReflowRequest{
					FlagGroupCode:      route.FlagGroupCode,
					ProductWarehouseID: adj.ProductWarehouseID,
					AsOf:               &adj.CreatedAt,
					IdempotencyKey:     fmt.Sprintf("delete-adjustment-usable-%d-%d", adj.ID, time.Now().UnixNano()),
					Tx:                 tx,
				}
				if _, err := fifoStockV2Svc.Reflow(ctx, reflowReq); err != nil {
					return fmt.Errorf("reflow usable to zero: %w", err)
				}

				if err := hardDeleteUsableAllocations(ctx, tx, fifo.UsableKeyAdjustmentOut.String(), adj.ID); err != nil {
					return err
				}
				if err := hardDeleteAdjustmentStockLogs(ctx, tx, adj.ID); err != nil {
					return err
				}
				if err := hardDeleteAdjustment(ctx, tx, adj.ID); err != nil {
					return err
				}
				return nil
			})
			if err != nil {
				fmt.Printf("FAIL adj=%d error=%v\n", adj.ID, err)
				failed++
				continue
			}

			fmt.Printf("DONE adj=%d deleted\n", adj.ID)
			success++

		case "STOCKABLE":
			removeQty := adj.TotalQty
			if removeQty <= 0 && adj.StockLogIncrease > 0 {
				removeQty = adj.StockLogIncrease
			}

			activeAlloc, err := countActiveStockableAllocations(ctx, db, fifo.StockableKeyAdjustmentIn.String(), adj.ID)
			if err != nil {
				fmt.Printf("FAIL adj=%d error=count stockable allocations: %v\n", adj.ID, err)
				failed++
				continue
			}
			if activeAlloc > 0 {
				fmt.Printf(
					"FAIL adj=%d reason=stockable still allocated active_alloc=%d action=delete blocked\n",
					adj.ID,
					activeAlloc,
				)
				failed++
				continue
			}

			fmt.Printf(
				"PLAN adj=%d lane=STOCKABLE function=%s total=%.3f remove_qty=%.3f action=reflow_to_zero+delete\n",
				adj.ID,
				route.FunctionCode,
				adj.TotalQty,
				removeQty,
			)

			if !apply {
				skipped++
				continue
			}

			err = db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
				if err := tx.WithContext(ctx).
					Table("adjustment_stocks").
					Where("id = ?", adj.ID).
					Updates(map[string]any{
						"total_qty":  0,
						"total_used": 0,
					}).Error; err != nil {
					return fmt.Errorf("set stockable qty to zero: %w", err)
				}

				reflowReq := commonSvc.FifoStockV2ReflowRequest{
					FlagGroupCode:      route.FlagGroupCode,
					ProductWarehouseID: adj.ProductWarehouseID,
					AsOf:               &adj.CreatedAt,
					IdempotencyKey:     fmt.Sprintf("delete-adjustment-stockable-%d-%d", adj.ID, time.Now().UnixNano()),
					Tx:                 tx,
				}
				if _, err := fifoStockV2Svc.Reflow(ctx, reflowReq); err != nil {
					return fmt.Errorf("reflow stockable to zero: %w", err)
				}

				if err := hardDeleteStockableAllocations(ctx, tx, fifo.StockableKeyAdjustmentIn.String(), adj.ID); err != nil {
					return err
				}
				if err := hardDeleteAdjustmentStockLogs(ctx, tx, adj.ID); err != nil {
					return err
				}
				if err := hardDeleteAdjustment(ctx, tx, adj.ID); err != nil {
					return err
				}
				return nil
			})
			if err != nil {
				fmt.Printf("FAIL adj=%d error=%v\n", adj.ID, err)
				failed++
				continue
			}

			fmt.Printf("DONE adj=%d deleted\n", adj.ID)
			success++

		default:
			fmt.Printf("SKIP adj=%d reason=unsupported lane=%s\n", adj.ID, route.Lane)
			skipped++
		}
	}

	fmt.Println()
	fmt.Printf("Summary: success=%d failed=%d skipped=%d\n", success, failed, skipped)
	if failed > 0 {
		os.Exit(1)
	}
}

func modeLabel(apply bool) string {
	if apply {
		return "APPLY"
	}
	return "DRY-RUN"
}

func parseIDs(raw string) ([]uint, error) {
	raw = strings.TrimSpace(raw)
	if raw == "" {
		return nil, nil
	}

	parts := strings.Split(raw, ",")
	out := make([]uint, 0, len(parts))
	seen := map[uint]struct{}{}

	for _, part := range parts {
		part = strings.TrimSpace(part)
		if part == "" {
			continue
		}
		v, err := strconv.ParseUint(part, 10, 64)
		if err != nil {
			return nil, fmt.Errorf("invalid id %q", part)
		}
		if v == 0 {
			return nil, fmt.Errorf("id must be > 0: %q", part)
		}
		id := uint(v)
		if _, ok := seen[id]; ok {
			continue
		}
		seen[id] = struct{}{}
		out = append(out, id)
	}
	return out, nil
}

func loadAdjustments(ctx context.Context, db *gorm.DB, ids []uint) ([]adjustmentRow, error) {
	var rows []adjustmentRow
	err := db.WithContext(ctx).
		Table("adjustment_stocks a").
		Select(`
			a.id,
			a.product_warehouse_id,
			pw.product_id,
			a.function_code,
			COALESCE(a.total_qty, 0) AS total_qty,
			COALESCE(a.usage_qty, 0) AS usage_qty,
			COALESCE(a.pending_qty, 0) AS pending_qty,
			COALESCE((
				SELECT sl.increase
				FROM stock_logs sl
				WHERE sl.loggable_type = 'ADJUSTMENT'
					AND sl.loggable_id = a.id
				ORDER BY sl.id DESC
				LIMIT 1
			), 0) AS stock_log_increase,
			COALESCE((
				SELECT sl.decrease
				FROM stock_logs sl
				WHERE sl.loggable_type = 'ADJUSTMENT'
					AND sl.loggable_id = a.id
				ORDER BY sl.id DESC
				LIMIT 1
			), 0) AS stock_log_decrease,
			a.created_at
		`).
		Joins("JOIN product_warehouses pw ON pw.id = a.product_warehouse_id").
		Where("a.id IN ?", ids).
		Find(&rows).Error
	if err != nil {
		return nil, err
	}
	return rows, nil
}

func resolveRouteByFunctionCode(ctx context.Context, db *gorm.DB, productID uint, functionCode string) (*routeResolution, error) {
	var rows []routeResolution
	err := db.WithContext(ctx).
		Table("fifo_stock_v2_route_rules rr").
		Select("rr.flag_group_code, rr.lane, rr.function_code").
		Joins("JOIN fifo_stock_v2_flag_groups fg ON fg.code = rr.flag_group_code AND fg.is_active = TRUE").
		Where("rr.is_active = TRUE").
		Where("rr.function_code = ?", functionCode).
		Where(`
			EXISTS (
				SELECT 1
				FROM flags f
				JOIN fifo_stock_v2_flag_members fm ON fm.flag_name = f.name AND fm.is_active = TRUE
				WHERE f.flagable_type = ?
					AND f.flagable_id = ?
					AND fm.flag_group_code = rr.flag_group_code
			)
		`, entity.FlagableTypeProduct, productID).
		Order("CASE WHEN rr.source_table = 'adjustment_stocks' THEN 0 ELSE 1 END ASC").
		Order("rr.id ASC").
		Find(&rows).Error
	if err != nil {
		return nil, err
	}
	if len(rows) == 0 {
		return nil, fmt.Errorf("no route found for product_id=%d function_code=%s", productID, functionCode)
	}

	selected := rows[0]
	for _, row := range rows {
		if row.Lane != selected.Lane {
			return nil, fmt.Errorf("ambiguous lane for product_id=%d function_code=%s", productID, functionCode)
		}
	}
	selected.FunctionCode = functionCode
	return &selected, nil
}

func countActiveUsableAllocations(ctx context.Context, db *gorm.DB, usableType string, usableID uint) (int64, error) {
	var count int64
	err := db.WithContext(ctx).
		Table("stock_allocations").
		Where("usable_type = ? AND usable_id = ?", usableType, usableID).
		Where("status = ?", entity.StockAllocationStatusActive).
		Where("allocation_purpose = ?", entity.StockAllocationPurposeConsume).
		Count(&count).Error
	return count, err
}

func countActiveStockableAllocations(ctx context.Context, db *gorm.DB, stockableType string, stockableID uint) (int64, error) {
	var count int64
	err := db.WithContext(ctx).
		Table("stock_allocations").
		Where("stockable_type = ? AND stockable_id = ?", stockableType, stockableID).
		Where("status = ?", entity.StockAllocationStatusActive).
		Where("allocation_purpose = ?", entity.StockAllocationPurposeConsume).
		Count(&count).Error
	return count, err
}

func hardDeleteUsableAllocations(ctx context.Context, tx *gorm.DB, usableType string, usableID uint) error {
	return tx.WithContext(ctx).
		Exec("DELETE FROM stock_allocations WHERE usable_type = ? AND usable_id = ? AND allocation_purpose = ?", usableType, usableID, entity.StockAllocationPurposeConsume).
		Error
}

func hardDeleteStockableAllocations(ctx context.Context, tx *gorm.DB, stockableType string, stockableID uint) error {
	return tx.WithContext(ctx).
		Exec("DELETE FROM stock_allocations WHERE stockable_type = ? AND stockable_id = ? AND allocation_purpose = ?", stockableType, stockableID, entity.StockAllocationPurposeConsume).
		Error
}

func hardDeleteAdjustmentStockLogs(ctx context.Context, tx *gorm.DB, adjustmentID uint) error {
	return tx.WithContext(ctx).
		Exec("DELETE FROM stock_logs WHERE loggable_type = ? AND loggable_id = ?", "ADJUSTMENT", adjustmentID).
		Error
}

func hardDeleteAdjustment(ctx context.Context, tx *gorm.DB, adjustmentID uint) error {
	return tx.WithContext(ctx).
		Exec("DELETE FROM adjustment_stocks WHERE id = ?", adjustmentID).
		Error
}