PriceForge/internal/services/pricelist/world.go

package pricelist

import (
	"sort"
	"time"

	"gorm.io/gorm"
)

type worldPricePoint struct {
	price  float64
	weight float64
}

type worldLTPoint struct {
	weeks  int
	weight float64
}

// computeWorldPricelistItems builds world pricelist items from parts_log.
// Includes all supplier types (trader + self + competitor).
// Price method: weighted_median per lot (qty=NULL → weight=1).
// Lead time: weighted median of lead_time_weeks (99 if no data).
func computeWorldPricelistItems(db *gorm.DB) ([]CreateItemInput, error) {
	type metaRow struct {
		LotName         string `gorm:"column:lot_name"`
		PriceMethod     string `gorm:"column:price_method"`
		PeriodDays      int    `gorm:"column:period_days"`
		OnMissingQuotes string `gorm:"column:on_missing_quotes"`
	}

	var metaRows []metaRow
	if err := db.Table("qt_lot_metadata").
		Select("lot_name, price_method, period_days, on_missing_quotes").
		Where("pricelist_type = 'world' AND lot_name != '__default__'").
		Scan(&metaRows).Error; err != nil {
		return nil, err
	}

	// Load __default__ world config as fallback
	var defaultMeta metaRow
	_ = db.Table("qt_lot_metadata").
		Select("lot_name, price_method, period_days, on_missing_quotes").
		Where("pricelist_type = 'world' AND lot_name = '__default__'").
		Scan(&defaultMeta).Error
	if defaultMeta.PriceMethod == "" {
		defaultMeta = metaRow{PriceMethod: "weighted_median", PeriodDays: 90, OnMissingQuotes: "drop"}
	}

	metaByLot := make(map[string]metaRow, len(metaRows))
	for _, m := range metaRows {
		metaByLot[m.LotName] = m
	}

	// Determine the global max period for quoting window
	maxPeriod := defaultMeta.PeriodDays
	for _, m := range metaRows {
		if m.PeriodDays > maxPeriod {
			maxPeriod = m.PeriodDays
		}
	}
	since := time.Now().AddDate(0, 0, -maxPeriod)

	// Load quotes from parts_log (all supplier types via JOIN)
	type quoteRow struct {
		LotName       string   `gorm:"column:lot_name"`
		Price         float64  `gorm:"column:price"`
		Qty           *float64 `gorm:"column:qty"`
		LeadTimeWeeks *int     `gorm:"column:lead_time_weeks"`
	}
	var quotes []quoteRow
	if err := db.Table("parts_log pl").
		Select("pl.lot_name, pl.price, pl.qty, pl.lead_time_weeks").
		Joins("INNER JOIN supplier s ON s.supplier_code = pl.supplier_code").
		Where("pl.lot_name IS NOT NULL AND pl.lot_name != ''").
		Where("pl.quote_date >= ?", since).
		Scan(&quotes).Error; err != nil {
		return nil, err
	}

	grouped := make(map[string][]worldPricePoint)
	ltGrouped := make(map[string][]worldLTPoint)

	for _, q := range quotes {
		if q.Price <= 0 {
			continue
		}
		weight := 1.0
		if q.Qty != nil && *q.Qty > 0 {
			weight = *q.Qty
		}
		grouped[q.LotName] = append(grouped[q.LotName], worldPricePoint{price: q.Price, weight: weight})
		if q.LeadTimeWeeks != nil {
			ltGrouped[q.LotName] = append(ltGrouped[q.LotName], worldLTPoint{weeks: *q.LeadTimeWeeks, weight: weight})
		}
	}

	// Collect keep-lots (on_missing_quotes='keep' with no recent quotes)
	var keepLots []string
	for _, m := range metaRows {
		if m.OnMissingQuotes == "keep" {
			if _, hasPrices := grouped[m.LotName]; !hasPrices {
				keepLots = append(keepLots, m.LotName)
			}
		}
	}

	keepPrices := make(map[string]float64)
	if len(keepLots) > 0 {
		type lastRow struct {
			LotName string  `gorm:"column:lot_name"`
			Price   float64 `gorm:"column:price"`
		}
		var lastRows []lastRow
		_ = db.Table("parts_log pl").
			Select("pl.lot_name, pl.price").
			Where("pl.lot_name IN ?", keepLots).
			Where("pl.id IN (SELECT MAX(id) FROM parts_log WHERE lot_name IN ? GROUP BY lot_name)", keepLots).
			Scan(&lastRows).Error
		for _, r := range lastRows {
			keepPrices[r.LotName] = r.Price
		}
	}

	items := make([]CreateItemInput, 0, len(grouped)+len(keepPrices))

	// Lots with recent quotes
	for lotName, points := range grouped {
		meta := defaultMeta
		if m, ok := metaByLot[lotName]; ok {
			meta = m
		}
		price := worldCalcPrice(meta.PriceMethod, points)
		if price <= 0 {
			continue
		}
		lt := worldCalcLeadTime(ltGrouped[lotName])
		items = append(items, CreateItemInput{
			LotName:         lotName,
			Price:           price,
			PriceMethod:     meta.PriceMethod,
			PricePeriodDays: meta.PeriodDays,
			LeadTimeWeeks:   &lt,
		})
	}

	// Lots without recent quotes but on_missing_quotes='keep'
	for lotName, price := range keepPrices {
		meta := defaultMeta
		if m, ok := metaByLot[lotName]; ok {
			meta = m
		}
		noData := 99
		items = append(items, CreateItemInput{
			LotName:         lotName,
			Price:           price,
			PriceMethod:     meta.PriceMethod,
			PricePeriodDays: meta.PeriodDays,
			LeadTimeWeeks:   &noData,
		})
	}

	return items, nil
}

func worldCalcPrice(method string, points []worldPricePoint) float64 {
	if len(points) == 0 {
		return 0
	}
	switch method {
	case "weighted_median":
		return worldWeightedMedian(points)
	default:
		// weighted_avg
		totalWeight, totalPrice := 0.0, 0.0
		for _, p := range points {
			w := p.weight
			if w <= 0 {
				w = 1
			}
			totalPrice += p.price * w
			totalWeight += w
		}
		if totalWeight == 0 {
			return 0
		}
		return totalPrice / totalWeight
	}
}

func worldWeightedMedian(points []worldPricePoint) float64 {
	type item struct {
		price  float64
		weight float64
	}
	items := make([]item, 0, len(points))
	totalWeight := 0.0
	var prices []float64
	for _, p := range points {
		if p.price <= 0 {
			continue
		}
		prices = append(prices, p.price)
		w := p.weight
		if w <= 0 {
			w = 1
		}
		items = append(items, item{price: p.price, weight: w})
		totalWeight += w
	}
	if totalWeight <= 0 {
		if len(prices) == 0 {
			return 0
		}
		sort.Float64s(prices)
		return prices[len(prices)/2]
	}
	sort.Slice(items, func(i, j int) bool {
		return items[i].price < items[j].price
	})
	threshold := totalWeight / 2.0
	acc := 0.0
	for _, it := range items {
		acc += it.weight
		if acc >= threshold {
			return it.price
		}
	}
	return items[len(items)-1].price
}

func worldCalcLeadTime(points []worldLTPoint) int {
	if len(points) == 0 {
		return 99
	}
	sort.Slice(points, func(i, j int) bool {
		return points[i].weeks < points[j].weeks
	})
	totalWeight := 0.0
	for _, p := range points {
		w := p.weight
		if w <= 0 {
			w = 1
		}
		totalWeight += w
	}
	threshold := totalWeight / 2.0
	acc := 0.0
	for _, p := range points {
		w := p.weight
		if w <= 0 {
			w = 1
		}
		acc += w
		if acc >= threshold {
			return p.weeks
		}
	}
	return points[len(points)-1].weeks
}