goodie/addons/stock/models/stock_valuation.go

package models

import (
	"fmt"

	"odoo-go/pkg/orm"
)

// initStockValuationLayer registers stock.valuation.layer — tracks inventory valuation per move.
// Mirrors: odoo/addons/stock_account/models/stock_valuation_layer.py
func initStockValuationLayer() {
	m := orm.NewModel("stock.valuation.layer", orm.ModelOpts{
		Description: "Stock Valuation Layer",
		Order:       "create_date, id",
	})
	m.AddFields(
		orm.Many2one("product_id", "product.product", orm.FieldOpts{String: "Product", Required: true, Index: true}),
		orm.Many2one("company_id", "res.company", orm.FieldOpts{String: "Company", Required: true}),
		orm.Many2one("stock_move_id", "stock.move", orm.FieldOpts{String: "Stock Move"}),
		orm.Float("quantity", orm.FieldOpts{String: "Quantity"}),
		orm.Monetary("unit_cost", orm.FieldOpts{String: "Unit Value", CurrencyField: "currency_id"}),
		orm.Monetary("value", orm.FieldOpts{String: "Total Value", CurrencyField: "currency_id"}),
		orm.Monetary("remaining_value", orm.FieldOpts{String: "Remaining Value", CurrencyField: "currency_id"}),
		orm.Float("remaining_qty", orm.FieldOpts{String: "Remaining Qty"}),
		orm.Many2one("currency_id", "res.currency", orm.FieldOpts{String: "Currency"}),
		orm.Char("description", orm.FieldOpts{String: "Description"}),
		orm.Many2one("account_move_id", "account.move", orm.FieldOpts{String: "Journal Entry"}),
	)

	// create_valuation_layer: Creates a valuation layer for a stock move.
	// Mirrors: stock.valuation.layer.create() via product._run_fifo()
	m.RegisterMethod("create_valuation_layer", func(rs *orm.Recordset, args ...interface{}) (interface{}, error) {
		if len(args) < 4 {
			return nil, fmt.Errorf("stock.valuation.layer.create_valuation_layer requires product_id, move_id, quantity, unit_cost")
		}
		productID, _ := args[0].(int64)
		moveID, _ := args[1].(int64)
		quantity, _ := args[2].(float64)
		unitCost, _ := args[3].(float64)

		if productID == 0 || quantity == 0 {
			return nil, fmt.Errorf("stock.valuation.layer: invalid product_id or quantity")
		}

		env := rs.Env()
		totalValue := quantity * unitCost

		var layerID int64
		err := env.Tx().QueryRow(env.Ctx(),
			`INSERT INTO stock_valuation_layer
			 (product_id, stock_move_id, quantity, unit_cost, value, remaining_qty, remaining_value, company_id)
			 VALUES ($1, $2, $3, $4, $5, $6, $7, 1)
			 RETURNING id`,
			productID, moveID, quantity, unitCost, totalValue, quantity, totalValue,
		).Scan(&layerID)
		if err != nil {
			return nil, fmt.Errorf("stock.valuation.layer: create layer: %w", err)
		}

		return map[string]interface{}{
			"id": layerID, "value": totalValue,
		}, nil
	})

	// get_product_valuation: Compute total valuation for a product across all remaining layers.
	// Mirrors: product.product._compute_stock_value()
	m.RegisterMethod("get_product_valuation", func(rs *orm.Recordset, args ...interface{}) (interface{}, error) {
		if len(args) < 1 {
			return nil, fmt.Errorf("stock.valuation.layer.get_product_valuation requires product_id")
		}
		productID, _ := args[0].(int64)
		if productID == 0 {
			return nil, fmt.Errorf("stock.valuation.layer: invalid product_id")
		}

		env := rs.Env()

		var totalQty, totalValue float64
		err := env.Tx().QueryRow(env.Ctx(),
			`SELECT COALESCE(SUM(remaining_qty), 0), COALESCE(SUM(remaining_value), 0)
			 FROM stock_valuation_layer
			 WHERE product_id = $1 AND remaining_qty > 0`,
			productID,
		).Scan(&totalQty, &totalValue)
		if err != nil {
			return nil, fmt.Errorf("stock.valuation.layer: get valuation: %w", err)
		}

		var avgCost float64
		if totalQty > 0 {
			avgCost = totalValue / totalQty
		}

		return map[string]interface{}{
			"product_id":   productID,
			"total_qty":    totalQty,
			"total_value":  totalValue,
			"average_cost": avgCost,
		}, nil
	})

	// consume_fifo: Consume quantity from existing layers using FIFO order.
	// Mirrors: product.product._run_fifo() for outgoing moves
	m.RegisterMethod("consume_fifo", func(rs *orm.Recordset, args ...interface{}) (interface{}, error) {
		if len(args) < 2 {
			return nil, fmt.Errorf("stock.valuation.layer.consume_fifo requires product_id, quantity")
		}
		productID, _ := args[0].(int64)
		qtyToConsume, _ := args[1].(float64)

		if productID == 0 || qtyToConsume <= 0 {
			return nil, fmt.Errorf("stock.valuation.layer: invalid product_id or quantity")
		}

		env := rs.Env()

		// Get layers ordered by creation (FIFO)
		rows, err := env.Tx().Query(env.Ctx(),
			`SELECT id, remaining_qty, remaining_value, unit_cost
			 FROM stock_valuation_layer
			 WHERE product_id = $1 AND remaining_qty > 0
			 ORDER BY create_date, id`,
			productID,
		)
		if err != nil {
			return nil, fmt.Errorf("stock.valuation.layer: query layers for FIFO: %w", err)
		}
		defer rows.Close()

		// Collect layers first, then close cursor before updating (pgx safety)
		type layerConsumption struct {
			id       int64
			newQty   float64
			newValue float64
			consumed float64
			cost     float64
		}
		var consumptions []layerConsumption
		remaining := qtyToConsume

		for rows.Next() && remaining > 0 {
			var layerID int64
			var layerQty, layerValue, layerUnitCost float64
			if err := rows.Scan(&layerID, &layerQty, &layerValue, &layerUnitCost); err != nil {
				rows.Close()
				return nil, fmt.Errorf("stock.valuation.layer: scan FIFO layer: %w", err)
			}

			consumed := remaining
			if consumed > layerQty {
				consumed = layerQty
			}

			consumptions = append(consumptions, layerConsumption{
				id:       layerID,
				newQty:   layerQty - consumed,
				newValue: layerValue - consumed*layerUnitCost,
				consumed: consumed,
				cost:     layerUnitCost,
			})
			remaining -= consumed
		}
		rows.Close()

		// Now update layers outside the cursor
		var totalConsumedValue float64
		for _, c := range consumptions {
			_, err := env.Tx().Exec(env.Ctx(),
				`UPDATE stock_valuation_layer SET remaining_qty = $1, remaining_value = $2 WHERE id = $3`,
				c.newQty, c.newValue, c.id)
			if err != nil {
				return nil, fmt.Errorf("stock.valuation.layer: update layer %d: %w", c.id, err)
			}
			totalConsumedValue += c.consumed * c.cost
		}

		return map[string]interface{}{
			"consumed_qty":   qtyToConsume - remaining,
			"consumed_value": totalConsumedValue,
			"remaining":      remaining,
		}, nil
	})

	// get_valuation_history: Return valuation layers for a product within a date range.
	// Mirrors: stock.valuation.layer reporting views
	m.RegisterMethod("get_valuation_history", func(rs *orm.Recordset, args ...interface{}) (interface{}, error) {
		if len(args) < 1 {
			return nil, fmt.Errorf("stock.valuation.layer.get_valuation_history requires product_id")
		}
		productID, _ := args[0].(int64)
		if productID == 0 {
			return nil, fmt.Errorf("stock.valuation.layer: invalid product_id")
		}

		env := rs.Env()

		rows, err := env.Tx().Query(env.Ctx(),
			`SELECT id, quantity, unit_cost, value, remaining_qty, remaining_value, description
			 FROM stock_valuation_layer
			 WHERE product_id = $1
			 ORDER BY create_date DESC, id DESC`,
			productID,
		)
		if err != nil {
			return nil, fmt.Errorf("stock.valuation.layer: query history: %w", err)
		}
		defer rows.Close()

		var layers []map[string]interface{}
		for rows.Next() {
			var id int64
			var qty, unitCost, value, remQty, remValue float64
			var description *string
			if err := rows.Scan(&id, &qty, &unitCost, &value, &remQty, &remValue, &description); err != nil {
				return nil, fmt.Errorf("stock.valuation.layer: scan history row: %w", err)
			}
			desc := ""
			if description != nil {
				desc = *description
			}
			layers = append(layers, map[string]interface{}{
				"id": id, "quantity": qty, "unit_cost": unitCost, "value": value,
				"remaining_qty": remQty, "remaining_value": remValue, "description": desc,
			})
		}

		return map[string]interface{}{"layers": layers}, nil
	})
}