package models

import (
	"fmt"
	"time"

	"odoo-go/pkg/orm"
	"odoo-go/pkg/tools"
)

// initPurchaseOrderExtension extends purchase.order with additional fields and methods.
// Mirrors: odoo/addons/purchase/models/purchase_order.py (additional workflow fields)
//          odoo/addons/purchase_stock/models/purchase_order.py (stock integration)
func initPurchaseOrderExtension() {
	po := orm.ExtendModel("purchase.order")

	// -- Additional Fields --
	// Note: date_planned, date_approve, origin, invoice_status already exist on purchase.order
	po.AddFields(
		orm.Boolean("is_shipped", orm.FieldOpts{
			String: "Fully Shipped", Compute: "_compute_is_shipped",
		}),
		orm.Integer("invoice_count", orm.FieldOpts{
			String: "Bill Count", Compute: "_compute_invoice_count",
		}),
		orm.Integer("picking_count", orm.FieldOpts{
			String: "Receipt Count", Compute: "_compute_picking_count",
		}),
		orm.Many2one("user_id", "res.users", orm.FieldOpts{String: "Purchase Representative", Index: true}),
		orm.Many2one("dest_address_id", "res.partner", orm.FieldOpts{String: "Dropship Address"}),
		orm.Boolean("mail_reception_confirmed", orm.FieldOpts{String: "Receipt Confirmation Sent"}),
		orm.Boolean("mail_reminder_confirmed", orm.FieldOpts{String: "Reminder Sent"}),
		orm.Datetime("receipt_reminder_email", orm.FieldOpts{String: "Receipt Reminder Email"}),
		orm.Datetime("effective_date", orm.FieldOpts{String: "Effective Date"}),
		orm.Integer("incoming_picking_count", orm.FieldOpts{
			String: "Incoming Shipment Count", Compute: "_compute_incoming_picking_count",
		}),
		// receipt_status: Receipt status from linked pickings.
		// Mirrors: odoo/addons/purchase_stock/models/purchase_order.py receipt_status
		orm.Selection("receipt_status", []orm.SelectionItem{
			{Value: "pending", Label: "Not Received"},
			{Value: "partial", Label: "Partially Received"},
			{Value: "full", Label: "Fully Received"},
		}, orm.FieldOpts{String: "Receipt Status", Compute: "_compute_receipt_status", Store: true}),
	)

	// -- Computed: _compute_is_shipped --
	// Mirrors: odoo/addons/purchase_stock/models/purchase_order.py PurchaseOrder._compute_is_shipped()
	po.RegisterCompute("is_shipped", func(rs *orm.Recordset) (orm.Values, error) {
		env := rs.Env()
		poID := rs.IDs()[0]

		var totalQty, receivedQty float64
		env.Tx().QueryRow(env.Ctx(),
			`SELECT COALESCE(SUM(product_qty), 0), COALESCE(SUM(qty_received), 0)
			 FROM purchase_order_line WHERE order_id = $1`, poID,
		).Scan(&totalQty, &receivedQty)

		shipped := totalQty > 0 && receivedQty >= totalQty
		return orm.Values{"is_shipped": shipped}, nil
	})

	// -- Computed: _compute_invoice_count --
	// Mirrors: odoo/addons/purchase/models/purchase_order.py PurchaseOrder._compute_invoice()
	// Uses both invoice_origin link and purchase_line_id on invoice lines.
	po.RegisterCompute("invoice_count", func(rs *orm.Recordset) (orm.Values, error) {
		env := rs.Env()
		poID := rs.IDs()[0]

		var poName string
		env.Tx().QueryRow(env.Ctx(),
			`SELECT COALESCE(name, '') FROM purchase_order WHERE id = $1`, poID).Scan(&poName)

		// Count unique bills linked via purchase_line_id on invoice lines
		var count int
		env.Tx().QueryRow(env.Ctx(),
			`SELECT COUNT(DISTINCT am.id) FROM account_move am
			 JOIN account_move_line aml ON aml.move_id = am.id
			 JOIN purchase_order_line pol ON pol.id = aml.purchase_line_id
			 WHERE pol.order_id = $1 AND am.move_type IN ('in_invoice', 'in_refund')`,
			poID,
		).Scan(&count)

		// Fallback: bills linked via invoice_origin
		if count == 0 {
			env.Tx().QueryRow(env.Ctx(),
				`SELECT COUNT(*) FROM account_move
				 WHERE invoice_origin = $1 AND move_type IN ('in_invoice', 'in_refund')`, poName,
			).Scan(&count)
		}

		// Also check by PO ID pattern fallback
		if count == 0 {
			env.Tx().QueryRow(env.Ctx(),
				`SELECT COUNT(*) FROM account_move
				 WHERE invoice_origin = $1 AND move_type IN ('in_invoice', 'in_refund')`,
				fmt.Sprintf("PO%d", poID),
			).Scan(&count)
		}

		return orm.Values{"invoice_count": count}, nil
	})

	// -- Computed: _compute_picking_count --
	// Mirrors: odoo/addons/purchase_stock/models/purchase_order.py PurchaseOrder._compute_picking()
	po.RegisterCompute("picking_count", func(rs *orm.Recordset) (orm.Values, error) {
		env := rs.Env()
		poID := rs.IDs()[0]

		var poName string
		env.Tx().QueryRow(env.Ctx(),
			`SELECT COALESCE(name, '') FROM purchase_order WHERE id = $1`, poID).Scan(&poName)

		var count int
		env.Tx().QueryRow(env.Ctx(),
			`SELECT COUNT(*) FROM stock_picking WHERE origin = $1`, poName,
		).Scan(&count)
		return orm.Values{"picking_count": count}, nil
	})

	// -- Computed: _compute_incoming_picking_count --
	po.RegisterCompute("incoming_picking_count", func(rs *orm.Recordset) (orm.Values, error) {
		env := rs.Env()
		poID := rs.IDs()[0]

		var poName string
		env.Tx().QueryRow(env.Ctx(),
			`SELECT COALESCE(name, '') FROM purchase_order WHERE id = $1`, poID).Scan(&poName)

		var count int
		env.Tx().QueryRow(env.Ctx(),
			`SELECT COUNT(*) FROM stock_picking sp
			 JOIN stock_picking_type spt ON spt.id = sp.picking_type_id
			 WHERE sp.origin = $1 AND spt.code = 'incoming'`, poName,
		).Scan(&count)
		return orm.Values{"incoming_picking_count": count}, nil
	})

	// action_view_picking: Open the receipts (incoming pickings) linked to this PO.
	// Mirrors: odoo/addons/purchase_stock/models/purchase_order.py PurchaseOrder.action_view_picking()
	po.RegisterMethod("action_view_picking", func(rs *orm.Recordset, args ...interface{}) (interface{}, error) {
		env := rs.Env()
		poID := rs.IDs()[0]

		var poName string
		env.Tx().QueryRow(env.Ctx(),
			`SELECT COALESCE(name, '') FROM purchase_order WHERE id = $1`, poID).Scan(&poName)

		rows, err := env.Tx().Query(env.Ctx(),
			`SELECT id FROM stock_picking WHERE origin = $1`, poName)
		if err != nil {
			return nil, fmt.Errorf("purchase: view picking query: %w", err)
		}
		defer rows.Close()

		var pickingIDs []interface{}
		for rows.Next() {
			var id int64
			rows.Scan(&id)
			pickingIDs = append(pickingIDs, id)
		}

		if len(pickingIDs) == 1 {
			return map[string]interface{}{
				"type": "ir.actions.act_window", "res_model": "stock.picking",
				"res_id": pickingIDs[0], "view_mode": "form",
				"views": [][]interface{}{{nil, "form"}}, "target": "current",
			}, nil
		}
		return map[string]interface{}{
			"type": "ir.actions.act_window", "res_model": "stock.picking",
			"view_mode": "list,form", "views": [][]interface{}{{nil, "list"}, {nil, "form"}},
			"domain": []interface{}{[]interface{}{"id", "in", pickingIDs}}, "target": "current",
			"name":   "Receipts",
		}, nil
	})

	// action_view_invoice: Open vendor bills linked to this PO.
	// Mirrors: odoo/addons/purchase/models/purchase_order.py PurchaseOrder.action_view_invoice()
	// Finds bills via purchase_line_id link, then falls back to invoice_origin.
	po.RegisterMethod("action_view_invoice", func(rs *orm.Recordset, args ...interface{}) (interface{}, error) {
		env := rs.Env()
		poID := rs.IDs()[0]

		var poName string
		env.Tx().QueryRow(env.Ctx(),
			`SELECT COALESCE(name, '') FROM purchase_order WHERE id = $1`, poID).Scan(&poName)

		// Primary: find bills linked via purchase_line_id
		invIDSet := make(map[int64]bool)
		rows, err := env.Tx().Query(env.Ctx(),
			`SELECT DISTINCT am.id FROM account_move am
			 JOIN account_move_line aml ON aml.move_id = am.id
			 JOIN purchase_order_line pol ON pol.id = aml.purchase_line_id
			 WHERE pol.order_id = $1 AND am.move_type IN ('in_invoice', 'in_refund')`, poID)
		if err == nil {
			for rows.Next() {
				var id int64
				rows.Scan(&id)
				invIDSet[id] = true
			}
			rows.Close()
		}

		// Fallback: invoice_origin
		if len(invIDSet) == 0 {
			rows2, _ := env.Tx().Query(env.Ctx(),
				`SELECT id FROM account_move WHERE invoice_origin = $1
				 AND move_type IN ('in_invoice', 'in_refund')`, poName)
			if rows2 != nil {
				for rows2.Next() {
					var id int64
					rows2.Scan(&id)
					invIDSet[id] = true
				}
				rows2.Close()
			}
		}

		// Fallback: PO ID pattern
		if len(invIDSet) == 0 {
			rows3, _ := env.Tx().Query(env.Ctx(),
				`SELECT id FROM account_move WHERE invoice_origin = $1
				 AND move_type IN ('in_invoice', 'in_refund')`,
				fmt.Sprintf("PO%d", poID))
			if rows3 != nil {
				for rows3.Next() {
					var id int64
					rows3.Scan(&id)
					invIDSet[id] = true
				}
				rows3.Close()
			}
		}

		var invIDs []interface{}
		for id := range invIDSet {
			invIDs = append(invIDs, id)
		}

		if len(invIDs) == 0 {
			return map[string]interface{}{"type": "ir.actions.act_window_close"}, nil
		}
		if len(invIDs) == 1 {
			return map[string]interface{}{
				"type": "ir.actions.act_window", "res_model": "account.move",
				"res_id": invIDs[0], "view_mode": "form",
				"views": [][]interface{}{{nil, "form"}}, "target": "current",
			}, nil
		}
		return map[string]interface{}{
			"type": "ir.actions.act_window", "res_model": "account.move",
			"view_mode": "list,form", "views": [][]interface{}{{nil, "list"}, {nil, "form"}},
			"domain": []interface{}{[]interface{}{"id", "in", invIDs}}, "target": "current",
			"name":   "Vendor Bills",
		}, nil
	})

	// action_create_picking: Generate incoming stock picking from a confirmed PO.
	// Mirrors: odoo/addons/purchase_stock/models/purchase_order.py PurchaseOrder._create_picking()
	po.RegisterMethod("action_create_picking", func(rs *orm.Recordset, args ...interface{}) (interface{}, error) {
		env := rs.Env()
		var pickingIDs []int64

		for _, poID := range rs.IDs() {
			var partnerID, companyID int64
			var poName string
			err := env.Tx().QueryRow(env.Ctx(),
				`SELECT partner_id, company_id, COALESCE(name, '')
				 FROM purchase_order WHERE id = $1`, poID,
			).Scan(&partnerID, &companyID, &poName)
			if err != nil {
				return nil, fmt.Errorf("purchase: read PO %d for picking: %w", poID, err)
			}

			// Read PO lines with products
			rows, err := env.Tx().Query(env.Ctx(),
				`SELECT product_id, product_qty, COALESCE(name, '')
				 FROM purchase_order_line
				 WHERE order_id = $1 AND product_id IS NOT NULL`, poID)
			if err != nil {
				return nil, fmt.Errorf("purchase: read PO lines %d for picking: %w", poID, err)
			}

			type poline struct {
				productID int64
				qty       float64
				name      string
			}
			var lines []poline
			for rows.Next() {
				var l poline
				if err := rows.Scan(&l.productID, &l.qty, &l.name); err != nil {
					rows.Close()
					return nil, err
				}
				lines = append(lines, l)
			}
			rows.Close()

			if len(lines) == 0 {
				continue
			}

			// Find incoming picking type and locations
			var pickingTypeID, srcLocID, destLocID int64
			env.Tx().QueryRow(env.Ctx(),
				`SELECT pt.id, COALESCE(pt.default_location_src_id, 0), COALESCE(pt.default_location_dest_id, 0)
				 FROM stock_picking_type pt
				 WHERE pt.code = 'incoming' AND pt.company_id = $1
				 LIMIT 1`, companyID,
			).Scan(&pickingTypeID, &srcLocID, &destLocID)

			if srcLocID == 0 {
				env.Tx().QueryRow(env.Ctx(),
					`SELECT id FROM stock_location WHERE usage = 'supplier' LIMIT 1`).Scan(&srcLocID)
			}
			if destLocID == 0 {
				env.Tx().QueryRow(env.Ctx(),
					`SELECT id FROM stock_location WHERE usage = 'internal' AND COALESCE(company_id, $1) = $1 LIMIT 1`,
					companyID).Scan(&destLocID)
			}
			if pickingTypeID == 0 {
				env.Tx().QueryRow(env.Ctx(),
					`SELECT id FROM stock_picking_type WHERE code = 'incoming' LIMIT 1`).Scan(&pickingTypeID)
			}

			// Create picking
			var pickingID int64
			err = env.Tx().QueryRow(env.Ctx(),
				`INSERT INTO stock_picking
				 (name, state, scheduled_date, company_id, partner_id, picking_type_id,
				  location_id, location_dest_id, origin)
				 VALUES ($1, 'confirmed', NOW(), $2, $3, $4, $5, $6, $7) RETURNING id`,
				fmt.Sprintf("WH/IN/%05d", poID), companyID, partnerID, pickingTypeID,
				srcLocID, destLocID, poName,
			).Scan(&pickingID)
			if err != nil {
				return nil, fmt.Errorf("purchase: create picking for PO %d: %w", poID, err)
			}

			// Create stock moves
			for _, l := range lines {
				_, err = env.Tx().Exec(env.Ctx(),
					`INSERT INTO stock_move
					 (name, product_id, product_uom_qty, state, picking_id, company_id,
					  location_id, location_dest_id, date, origin, product_uom)
					 VALUES ($1, $2, $3, 'confirmed', $4, $5, $6, $7, NOW(), $8, 1)`,
					l.name, l.productID, l.qty, pickingID, companyID,
					srcLocID, destLocID, poName)
				if err != nil {
					return nil, fmt.Errorf("purchase: create stock move for PO %d: %w", poID, err)
				}
			}

			pickingIDs = append(pickingIDs, pickingID)
		}

		if len(pickingIDs) == 0 {
			return nil, nil
		}
		return pickingIDs, nil
	})

	// button_approve: Approve a PO that requires approval (to approve → purchase).
	// Mirrors: odoo/addons/purchase/models/purchase_order.py PurchaseOrder.button_approve()
	po.RegisterMethod("button_approve", func(rs *orm.Recordset, args ...interface{}) (interface{}, error) {
		env := rs.Env()
		for _, poID := range rs.IDs() {
			var state string
			env.Tx().QueryRow(env.Ctx(),
				`SELECT state FROM purchase_order WHERE id = $1`, poID).Scan(&state)
			if state != "to approve" {
				continue
			}
			if _, err := env.Tx().Exec(env.Ctx(),
				`UPDATE purchase_order SET state = 'purchase', date_approve = NOW() WHERE id = $1`, poID); err != nil {
				return nil, fmt.Errorf("purchase.order: approve %d: %w", poID, err)
			}
		}
		return true, nil
	})

	po.RegisterMethod("button_done", func(rs *orm.Recordset, args ...interface{}) (interface{}, error) {
		env := rs.Env()
		for _, poID := range rs.IDs() {
			if _, err := env.Tx().Exec(env.Ctx(),
				`UPDATE purchase_order SET state = 'done' WHERE id = $1`, poID); err != nil {
				return nil, fmt.Errorf("purchase.order: done %d: %w", poID, err)
			}
		}
		return true, nil
	})

	po.RegisterMethod("button_unlock", func(rs *orm.Recordset, args ...interface{}) (interface{}, error) {
		env := rs.Env()
		for _, poID := range rs.IDs() {
			if _, err := env.Tx().Exec(env.Ctx(),
				`UPDATE purchase_order SET state = 'purchase' WHERE id = $1 AND state = 'done'`, poID); err != nil {
				return nil, fmt.Errorf("purchase.order: unlock %d: %w", poID, err)
			}
		}
		return true, nil
	})

	// action_rfq_send: Send the RFQ email to the vendor and mark PO as 'sent'.
	// Mirrors: odoo/addons/purchase/models/purchase_order.py PurchaseOrder.action_rfq_send()
	// Reads vendor email from res.partner, builds an email body with PO details,
	// and sends via tools.SendEmail.
	po.RegisterMethod("action_rfq_send", func(rs *orm.Recordset, args ...interface{}) (interface{}, error) {
		env := rs.Env()
		smtpCfg := tools.LoadSMTPConfig()

		for _, poID := range rs.IDs() {
			var state, poName, partnerRef string
			var partnerID, companyID int64
			var amountTotal float64
			var datePlanned *time.Time
			env.Tx().QueryRow(env.Ctx(),
				`SELECT COALESCE(state,'draft'), COALESCE(name,''), COALESCE(partner_ref,''),
				        COALESCE(partner_id,0), COALESCE(company_id,0),
				        COALESCE(amount_total::float8,0), date_planned
				 FROM purchase_order WHERE id = $1`, poID,
			).Scan(&state, &poName, &partnerRef, &partnerID, &companyID, &amountTotal, &datePlanned)

			if state != "draft" && state != "sent" {
				continue
			}

			// Read vendor email and name
			var vendorEmail, vendorName string
			if partnerID > 0 {
				env.Tx().QueryRow(env.Ctx(),
					`SELECT COALESCE(email,''), COALESCE(name,'') FROM res_partner WHERE id = $1`,
					partnerID).Scan(&vendorEmail, &vendorName)
			}

			// Read company name for the email
			var companyName string
			env.Tx().QueryRow(env.Ctx(),
				`SELECT COALESCE(name,'') FROM res_company WHERE id = $1`, companyID).Scan(&companyName)

			// Read order lines for the email body
			lineRows, err := env.Tx().Query(env.Ctx(),
				`SELECT COALESCE(name,''), COALESCE(product_qty,0), COALESCE(price_unit,0),
				        COALESCE(product_qty * price_unit * (1 - COALESCE(discount,0)/100), 0)
				 FROM purchase_order_line
				 WHERE order_id = $1 AND COALESCE(display_type,'') NOT IN ('line_section','line_note')
				 ORDER BY sequence, id`, poID)
			var linesHTML string
			if err == nil {
				for lineRows.Next() {
					var lName string
					var lQty, lPrice, lSubtotal float64
					if lineRows.Scan(&lName, &lQty, &lPrice, &lSubtotal) == nil {
						linesHTML += fmt.Sprintf(
							"<tr><td>%s</td><td>%.2f</td><td>%.2f</td><td>%.2f</td></tr>",
							lName, lQty, lPrice, lSubtotal)
					}
				}
				lineRows.Close()
			}

			plannedStr := ""
			if datePlanned != nil {
				plannedStr = datePlanned.Format("2006-01-02")
			}

			subject := fmt.Sprintf("Request for Quotation (%s)", poName)
			body := fmt.Sprintf(`<html><body>
<p>Dear %s,</p>
<p>Here is a Request for Quotation from <strong>%s</strong>:</p>
<table border="1" cellpadding="5" cellspacing="0">
<tr><th>Reference</th><td>%s</td></tr>
<tr><th>Expected Arrival</th><td>%s</td></tr>
<tr><th>Total</th><td>%.2f</td></tr>
</table>
<br/>
<table border="1" cellpadding="5" cellspacing="0">
<tr><th>Description</th><th>Qty</th><th>Unit Price</th><th>Subtotal</th></tr>
%s
</table>
<p>Please confirm your availability and pricing at your earliest convenience.</p>
<p>Best regards,<br/>%s</p>
</body></html>`, vendorName, companyName, poName, plannedStr, amountTotal, linesHTML, companyName)

			// Send email if vendor has an email address
			if vendorEmail != "" {
				if err := tools.SendEmail(smtpCfg, vendorEmail, subject, body); err != nil {
					return nil, fmt.Errorf("purchase.order: send RFQ email for %s: %w", poName, err)
				}
			}

			// Mark PO as sent
			if _, err := env.Tx().Exec(env.Ctx(),
				`UPDATE purchase_order SET state = 'sent' WHERE id = $1 AND state = 'draft'`, poID); err != nil {
				return nil, fmt.Errorf("purchase.order: rfq_send %d: %w", poID, err)
			}
		}
		return true, nil
	})

	// -- Computed: _compute_receipt_status --
	// Mirrors: odoo/addons/purchase_stock/models/purchase_order.py _compute_receipt_status()
	po.RegisterCompute("receipt_status", func(rs *orm.Recordset) (orm.Values, error) {
		env := rs.Env()
		poID := rs.IDs()[0]

		var poName string
		env.Tx().QueryRow(env.Ctx(),
			`SELECT COALESCE(name, '') FROM purchase_order WHERE id = $1`, poID).Scan(&poName)

		// Count pickings by state
		var totalPickings, donePickings, cancelledPickings int
		env.Tx().QueryRow(env.Ctx(),
			`SELECT COUNT(*),
			        COUNT(*) FILTER (WHERE state = 'done'),
			        COUNT(*) FILTER (WHERE state = 'cancel')
			 FROM stock_picking WHERE origin = $1`, poName,
		).Scan(&totalPickings, &donePickings, &cancelledPickings)

		if totalPickings == 0 || totalPickings == cancelledPickings {
			return orm.Values{"receipt_status": nil}, nil
		}
		if totalPickings == donePickings+cancelledPickings {
			return orm.Values{"receipt_status": "full"}, nil
		}
		if donePickings > 0 {
			return orm.Values{"receipt_status": "partial"}, nil
		}
		return orm.Values{"receipt_status": "pending"}, nil
	})

	// button_lock: Lock a confirmed PO.
	// Mirrors: odoo/addons/purchase/models/purchase_order.py PurchaseOrder.button_lock()
	po.RegisterMethod("button_lock", func(rs *orm.Recordset, args ...interface{}) (interface{}, error) {
		env := rs.Env()
		for _, poID := range rs.IDs() {
			env.Tx().Exec(env.Ctx(),
				`UPDATE purchase_order SET locked = true WHERE id = $1`, poID)
		}
		return true, nil
	})

	// _compute_effective_date: The effective date is set when all products have been received.
	// Mirrors: odoo/addons/purchase_stock/models/purchase_order.py
	po.RegisterCompute("effective_date", func(rs *orm.Recordset) (orm.Values, error) {
		env := rs.Env()
		poID := rs.IDs()[0]

		var totalQty, receivedQty float64
		env.Tx().QueryRow(env.Ctx(),
			`SELECT COALESCE(SUM(product_qty), 0), COALESCE(SUM(qty_received), 0)
			 FROM purchase_order_line WHERE order_id = $1`, poID,
		).Scan(&totalQty, &receivedQty)

		if totalQty > 0 && receivedQty >= totalQty {
			// Use the last done picking date, not current time
			var lastDoneDate *time.Time
			env.Tx().QueryRow(env.Ctx(),
				`SELECT MAX(date_done) FROM stock_picking
				 WHERE origin = (SELECT name FROM purchase_order WHERE id = $1) AND state = 'done'`,
				poID).Scan(&lastDoneDate)
			if lastDoneDate != nil {
				return orm.Values{"effective_date": *lastDoneDate}, nil
			}
			return orm.Values{"effective_date": time.Now()}, nil
		}
		return orm.Values{"effective_date": nil}, nil
	})
}

// initPurchaseOrderWorkflow adds remaining workflow features to purchase.order.
func initPurchaseOrderWorkflow() {
	po := orm.ExtendModel("purchase.order")

	// _check_three_way_match: 3-Way Match validation.
	// Compares PO qty vs received qty vs billed qty per line.
	// Returns a list of mismatches (lines where the three quantities don't align).
	// Mirrors: odoo/addons/purchase/models/purchase_order.py (3-way matching logic)
	po.RegisterMethod("_check_three_way_match", func(rs *orm.Recordset, args ...interface{}) (interface{}, error) {
		env := rs.Env()
		var allMismatches []map[string]interface{}

		for _, poID := range rs.IDs() {
			var poName string
			env.Tx().QueryRow(env.Ctx(),
				`SELECT COALESCE(name, '') FROM purchase_order WHERE id = $1`, poID).Scan(&poName)

			rows, err := env.Tx().Query(env.Ctx(),
				`SELECT pol.id, COALESCE(pol.name, ''),
				        COALESCE(pol.product_qty, 0),
				        COALESCE(pol.qty_received, 0),
				        COALESCE(pol.qty_invoiced, 0),
				        pol.product_id
				 FROM purchase_order_line pol
				 WHERE pol.order_id = $1
				   AND COALESCE(pol.display_type, '') NOT IN ('line_section', 'line_note')
				 ORDER BY pol.sequence, pol.id`, poID)
			if err != nil {
				return nil, fmt.Errorf("purchase: three_way_match query for PO %d: %w", poID, err)
			}

			for rows.Next() {
				var lineID int64
				var lineName string
				var orderedQty, receivedQty, billedQty float64
				var productID *int64
				if err := rows.Scan(&lineID, &lineName, &orderedQty, &receivedQty, &billedQty, &productID); err != nil {
					rows.Close()
					return nil, err
				}

				// A line matches when ordered == received == billed.
				// Report any deviation.
				mismatch := make(map[string]interface{})
				hasMismatch := false

				if orderedQty != receivedQty || orderedQty != billedQty || receivedQty != billedQty {
					hasMismatch = true
				}

				if hasMismatch {
					mismatch["po_name"] = poName
					mismatch["line_id"] = lineID
					mismatch["line_name"] = lineName
					mismatch["ordered_qty"] = orderedQty
					mismatch["received_qty"] = receivedQty
					mismatch["billed_qty"] = billedQty
					if productID != nil {
						mismatch["product_id"] = *productID
					}

					// Classify the type of mismatch
					var issues []string
					if receivedQty < orderedQty {
						issues = append(issues, "under_received")
					} else if receivedQty > orderedQty {
						issues = append(issues, "over_received")
					}
					if billedQty < receivedQty {
						issues = append(issues, "under_billed")
					} else if billedQty > receivedQty {
						issues = append(issues, "over_billed")
					}
					if billedQty > orderedQty {
						issues = append(issues, "billed_exceeds_ordered")
					}
					mismatch["issues"] = issues

					allMismatches = append(allMismatches, mismatch)
				}
			}
			rows.Close()
		}

		return map[string]interface{}{
			"match":      len(allMismatches) == 0,
			"mismatches": allMismatches,
		}, nil
	})

	// action_print: Return a report action URL pointing to /report/pdf/purchase.order/<id>.
	// Mirrors: odoo/addons/purchase/models/purchase_order.py PurchaseOrder.action_print()
	po.RegisterMethod("action_print", func(rs *orm.Recordset, args ...interface{}) (interface{}, error) {
		if len(rs.IDs()) == 0 {
			return nil, fmt.Errorf("purchase.order: action_print requires at least one record")
		}
		poID := rs.IDs()[0]
		return map[string]interface{}{
			"type":     "ir.actions.report",
			"report_name": "purchase.order",
			"report_type": "qweb-pdf",
			"res_model":   "purchase.order",
			"res_id":      poID,
			"url":         fmt.Sprintf("/report/pdf/purchase.order/%d", poID),
		}, nil
	})

	// _compute_date_planned: Propagate the earliest line date_planned to the PO header
	// and to linked stock moves.
	// Mirrors: odoo/addons/purchase_stock/models/purchase_order.py _compute_date_planned()
	po.RegisterCompute("date_planned", func(rs *orm.Recordset) (orm.Values, error) {
		env := rs.Env()
		poID := rs.IDs()[0]
		var earliest *time.Time
		env.Tx().QueryRow(env.Ctx(),
			`SELECT MIN(date_planned) FROM purchase_order_line
			 WHERE order_id = $1 AND date_planned IS NOT NULL`, poID).Scan(&earliest)
		if earliest == nil {
			return orm.Values{"date_planned": nil}, nil
		}
		return orm.Values{"date_planned": *earliest}, nil
	})

	// action_propagate_date_planned: Push date_planned from PO lines to stock moves.
	// Mirrors: odoo/addons/purchase_stock/models/purchase_order.py _propagate_date_planned()
	po.RegisterMethod("action_propagate_date_planned", func(rs *orm.Recordset, args ...interface{}) (interface{}, error) {
		env := rs.Env()
		for _, poID := range rs.IDs() {
			var poName string
			env.Tx().QueryRow(env.Ctx(),
				`SELECT COALESCE(name, '') FROM purchase_order WHERE id = $1`, poID).Scan(&poName)
			if poName == "" {
				continue
			}

			// Get date_planned from PO header
			var datePlanned *time.Time
			env.Tx().QueryRow(env.Ctx(),
				`SELECT date_planned FROM purchase_order WHERE id = $1`, poID).Scan(&datePlanned)
			if datePlanned == nil {
				continue
			}

			// Update scheduled date on linked stock moves (via picking origin)
			if _, err := env.Tx().Exec(env.Ctx(),
				`UPDATE stock_move SET date = $1
				 WHERE picking_id IN (SELECT id FROM stock_picking WHERE origin = $2)
				   AND state NOT IN ('done', 'cancel')`,
				*datePlanned, poName); err != nil {
				return nil, fmt.Errorf("purchase.order: propagate date for %d: %w", poID, err)
			}
		}
		return true, nil
	})

	// _check_company_match: Validate that PO company matches partner and lines.
	// Mirrors: odoo/addons/purchase/models/purchase_order.py _check_company_match()
	po.RegisterMethod("_check_company_match", func(rs *orm.Recordset, args ...interface{}) (interface{}, error) {
		env := rs.Env()
		for _, poID := range rs.IDs() {
			var poCompanyID int64
			var partnerID int64
			env.Tx().QueryRow(env.Ctx(),
				`SELECT COALESCE(company_id, 0), COALESCE(partner_id, 0)
				 FROM purchase_order WHERE id = $1`, poID).Scan(&poCompanyID, &partnerID)

			if poCompanyID == 0 {
				continue // No company set — no check needed
			}

			// Check partner's company (if set) matches PO company
			if partnerID > 0 {
				var partnerCompanyID int64
				env.Tx().QueryRow(env.Ctx(),
					`SELECT COALESCE(company_id, 0) FROM res_partner WHERE id = $1`, partnerID,
				).Scan(&partnerCompanyID)
				if partnerCompanyID > 0 && partnerCompanyID != poCompanyID {
					return nil, fmt.Errorf("purchase.order: vendor company (%d) does not match PO company (%d)", partnerCompanyID, poCompanyID)
				}
			}
		}
		return true, nil
	})

	// action_create_po_from_agreement: Create a PO from a blanket purchase agreement.
	// Mirrors: odoo/addons/purchase_requisition/models/purchase_requisition.py action_create_order()
	po.RegisterMethod("action_create_po_from_agreement", func(rs *orm.Recordset, args ...interface{}) (interface{}, error) {
		env := rs.Env()

		if len(args) < 1 {
			return nil, fmt.Errorf("purchase.order: action_create_po_from_agreement requires agreement_id")
		}
		var agreementID int64
		switch v := args[0].(type) {
		case float64:
			agreementID = int64(v)
		case int64:
			agreementID = v
		case map[string]interface{}:
			if id, ok := v["agreement_id"]; ok {
				switch n := id.(type) {
				case float64:
					agreementID = int64(n)
				case int64:
					agreementID = n
				}
			}
		}
		if agreementID == 0 {
			return nil, fmt.Errorf("purchase.order: invalid agreement_id")
		}

		// Read agreement header
		var userID, companyID int64
		var state string
		env.Tx().QueryRow(env.Ctx(),
			`SELECT COALESCE(user_id, 0), COALESCE(company_id, 0), COALESCE(state, 'draft')
			 FROM purchase_requisition WHERE id = $1`, agreementID,
		).Scan(&userID, &companyID, &state)

		if state != "ongoing" && state != "in_progress" && state != "open" {
			return nil, fmt.Errorf("purchase.order: agreement %d is not confirmed (state: %s)", agreementID, state)
		}

		// Read agreement lines
		rows, err := env.Tx().Query(env.Ctx(),
			`SELECT product_id, COALESCE(product_qty, 0), COALESCE(price_unit, 0)
			 FROM purchase_requisition_line WHERE requisition_id = $1`, agreementID)
		if err != nil {
			return nil, fmt.Errorf("purchase.order: read agreement lines: %w", err)
		}

		type agrLine struct {
			productID int64
			qty       float64
			price     float64
		}
		var lines []agrLine
		for rows.Next() {
			var l agrLine
			if err := rows.Scan(&l.productID, &l.qty, &l.price); err != nil {
				rows.Close()
				return nil, err
			}
			lines = append(lines, l)
		}
		rows.Close()

		if len(lines) == 0 {
			return nil, fmt.Errorf("purchase.order: agreement %d has no lines", agreementID)
		}

		// Find a vendor from existing POs linked to this agreement, or use user's partner
		var partnerID int64
		env.Tx().QueryRow(env.Ctx(),
			`SELECT COALESCE(partner_id, 0) FROM purchase_order
			 WHERE requisition_id = $1 LIMIT 1`, agreementID).Scan(&partnerID)
		if partnerID == 0 {
			env.Tx().QueryRow(env.Ctx(),
				`SELECT COALESCE(partner_id, 0) FROM res_users WHERE id = $1`, userID).Scan(&partnerID)
		}

		// Create PO
		poRS := env.Model("purchase.order")
		newPO, err := poRS.Create(orm.Values{
			"partner_id":     partnerID,
			"company_id":     companyID,
			"requisition_id": agreementID,
			"origin":         fmt.Sprintf("Agreement/%d", agreementID),
			"date_planned":   time.Now().Format("2006-01-02 15:04:05"),
		})
		if err != nil {
			return nil, fmt.Errorf("purchase.order: create PO from agreement: %w", err)
		}
		poID := newPO.ID()

		// Create PO lines from agreement lines
		polRS := env.Model("purchase.order.line")
		for _, l := range lines {
			var productName string
			env.Tx().QueryRow(env.Ctx(),
				`SELECT COALESCE(pt.name, 'Product') FROM product_product pp
				 JOIN product_template pt ON pt.id = pp.product_tmpl_id
				 WHERE pp.id = $1`, l.productID).Scan(&productName)

			if _, err := polRS.Create(orm.Values{
				"order_id":    poID,
				"product_id":  l.productID,
				"name":        productName,
				"product_qty": l.qty,
				"price_unit":  l.price,
			}); err != nil {
				return nil, fmt.Errorf("purchase.order: create PO line from agreement: %w", err)
			}
		}

		return map[string]interface{}{
			"type":      "ir.actions.act_window",
			"res_model": "purchase.order",
			"res_id":    poID,
			"view_mode": "form",
			"views":     [][]interface{}{{nil, "form"}},
			"target":    "current",
		}, nil
	})
}

// initVendorLeadTime adds vendor lead time computation based on PO history.
func initVendorLeadTime() {
	partner := orm.ExtendModel("res.partner")

	partner.AddFields(
		orm.Integer("purchase_lead_time", orm.FieldOpts{
			String: "Vendor Lead Time (Days)", Compute: "_compute_purchase_lead_time",
			Help:   "Average days between PO confirmation and receipt, computed from history",
		}),
	)

	// _compute_purchase_lead_time: Average days from PO confirm to receipt done.
	partner.RegisterCompute("purchase_lead_time", func(rs *orm.Recordset) (orm.Values, error) {
		env := rs.Env()
		partnerID := rs.IDs()[0]

		var avgDays float64
		err := env.Tx().QueryRow(env.Ctx(),
			`SELECT COALESCE(AVG(EXTRACT(EPOCH FROM (sp.date_done - po.date_approve)) / 86400.0), 0)
			 FROM purchase_order po
			 JOIN stock_picking sp ON sp.origin = po.name AND sp.state = 'done'
			 WHERE po.partner_id = $1 AND po.state = 'purchase' AND po.date_approve IS NOT NULL
			   AND sp.date_done IS NOT NULL`,
			partnerID).Scan(&avgDays)
		if err != nil || avgDays <= 0 {
			return orm.Values{"purchase_lead_time": int64(0)}, nil
		}
		return orm.Values{"purchase_lead_time": int64(avgDays + 0.5)}, nil // round
	})
}

// initPurchaseOrderLineExtension extends purchase.order.line with additional fields.
// Mirrors: odoo/addons/purchase/models/purchase_order_line.py (additional fields)
func initPurchaseOrderLineExtension() {
	pol := orm.ExtendModel("purchase.order.line")

	// Note: date_planned, qty_received, qty_invoiced already exist
	pol.AddFields(
		orm.Float("qty_received_manual", orm.FieldOpts{String: "Manual Received Qty"}),
		orm.Selection("invoice_status", []orm.SelectionItem{
			{Value: "no", Label: "Nothing to Bill"},
			{Value: "to invoice", Label: "Waiting Bills"},
			{Value: "invoiced", Label: "Fully Billed"},
		}, orm.FieldOpts{String: "Billing Status", Compute: "_compute_line_invoice_status"}),
		orm.Float("qty_to_invoice", orm.FieldOpts{
			String: "To Invoice Quantity", Compute: "_compute_line_qty_to_invoice",
		}),
		orm.Char("product_type", orm.FieldOpts{String: "Product Type"}),
		orm.Boolean("product_qty_updated", orm.FieldOpts{String: "Qty Updated"}),
	)

	// _compute_qty_invoiced: Compute billed qty from linked invoice lines via purchase_line_id.
	// Mirrors: odoo/addons/purchase/models/purchase_order_line.py _compute_qty_invoiced()
	pol.RegisterCompute("qty_invoiced", func(rs *orm.Recordset) (orm.Values, error) {
		env := rs.Env()
		lineID := rs.IDs()[0]

		// Sum quantities from invoice lines linked via purchase_line_id
		// Only count posted invoices (not draft/cancelled)
		var invoiced float64
		env.Tx().QueryRow(env.Ctx(),
			`SELECT COALESCE(SUM(
				CASE WHEN am.move_type = 'in_invoice' THEN aml.quantity
				     WHEN am.move_type = 'in_refund' THEN -aml.quantity
				     ELSE 0 END
			), 0)
			 FROM account_move_line aml
			 JOIN account_move am ON am.id = aml.move_id
			 WHERE aml.purchase_line_id = $1
			   AND am.state != 'cancel'`, lineID,
		).Scan(&invoiced)

		if invoiced < 0 {
			invoiced = 0
		}
		return orm.Values{"qty_invoiced": invoiced}, nil
	})

	// _compute_line_invoice_status: Per-line billing status.
	// Mirrors: odoo/addons/purchase/models/purchase_order_line.py _compute_qty_invoiced()
	pol.RegisterCompute("invoice_status", func(rs *orm.Recordset) (orm.Values, error) {
		env := rs.Env()
		lineID := rs.IDs()[0]

		var qty, qtyInvoiced, qtyReceived float64
		var orderState string
		env.Tx().QueryRow(env.Ctx(),
			`SELECT COALESCE(pol.product_qty, 0), COALESCE(pol.qty_invoiced, 0),
			        COALESCE(pol.qty_received, 0), COALESCE(po.state, 'draft')
			 FROM purchase_order_line pol
			 JOIN purchase_order po ON po.id = pol.order_id
			 WHERE pol.id = $1`, lineID,
		).Scan(&qty, &qtyInvoiced, &qtyReceived, &orderState)

		status := "no"
		if orderState == "purchase" && qty > 0 {
			qtyToInvoice := qtyReceived - qtyInvoiced
			if qtyToInvoice < 0 {
				qtyToInvoice = 0
			}
			if qtyInvoiced >= qty {
				status = "invoiced"
			} else if qtyToInvoice > 0 {
				status = "to invoice"
			} else {
				status = "no"
			}
		}
		return orm.Values{"invoice_status": status}, nil
	})

	// _compute_line_qty_to_invoice: Mirrors Python _compute_qty_invoiced().
	// For purchase method 'purchase': qty_to_invoice = product_qty - qty_invoiced
	// For purchase method 'receive' (default): qty_to_invoice = qty_received - qty_invoiced
	// Only non-zero when order state is 'purchase'.
	pol.RegisterCompute("qty_to_invoice", func(rs *orm.Recordset) (orm.Values, error) {
		env := rs.Env()
		lineID := rs.IDs()[0]

		var qty, qtyInvoiced, qtyReceived float64
		var orderState string
		env.Tx().QueryRow(env.Ctx(),
			`SELECT COALESCE(pol.product_qty, 0), COALESCE(pol.qty_invoiced, 0),
			        COALESCE(pol.qty_received, 0), COALESCE(po.state, 'draft')
			 FROM purchase_order_line pol
			 JOIN purchase_order po ON po.id = pol.order_id
			 WHERE pol.id = $1`, lineID,
		).Scan(&qty, &qtyInvoiced, &qtyReceived, &orderState)

		if orderState != "purchase" {
			return orm.Values{"qty_to_invoice": float64(0)}, nil
		}

		// Check product's purchase_method: 'purchase' bills on ordered qty, 'receive' bills on received qty
		var purchaseMethod string
		env.Tx().QueryRow(env.Ctx(),
			`SELECT COALESCE(pt.purchase_method, 'receive')
			 FROM purchase_order_line pol
			 LEFT JOIN product_product pp ON pp.id = pol.product_id
			 LEFT JOIN product_template pt ON pt.id = pp.product_tmpl_id
			 WHERE pol.id = $1`, lineID,
		).Scan(&purchaseMethod)

		var toInvoice float64
		if purchaseMethod == "purchase" {
			toInvoice = qty - qtyInvoiced
		} else {
			toInvoice = qtyReceived - qtyInvoiced
		}
		if toInvoice < 0 {
			toInvoice = 0
		}
		return orm.Values{"qty_to_invoice": toInvoice}, nil
	})

	// _compute_qty_received: Uses manual received qty if set, otherwise sums from done
	// stock moves linked via picking origin, filtered to internal destination locations.
	// Mirrors: odoo/addons/purchase_stock/models/purchase_order_line.py _compute_qty_received()
	pol.RegisterCompute("qty_received", func(rs *orm.Recordset) (orm.Values, error) {
		env := rs.Env()
		lineID := rs.IDs()[0]

		// Check for manual override
		var manual *float64
		env.Tx().QueryRow(env.Ctx(),
			`SELECT qty_received_manual FROM purchase_order_line WHERE id = $1`, lineID).Scan(&manual)
		if manual != nil && *manual > 0 {
			return orm.Values{"qty_received": *manual}, nil
		}

		// Sum from linked stock moves: done moves whose picking origin matches the PO name,
		// product matches, and destination is an internal location.
		var productID *int64
		var orderID int64
		env.Tx().QueryRow(env.Ctx(),
			`SELECT product_id, order_id FROM purchase_order_line WHERE id = $1`, lineID).Scan(&productID, &orderID)

		if productID != nil && *productID > 0 {
			var poName string
			env.Tx().QueryRow(env.Ctx(),
				`SELECT COALESCE(name, '') FROM purchase_order WHERE id = $1`, orderID).Scan(&poName)

			var received float64
			env.Tx().QueryRow(env.Ctx(),
				`SELECT COALESCE(SUM(sm.product_uom_qty), 0)
				 FROM stock_move sm
				 JOIN stock_picking sp ON sp.id = sm.picking_id
				 JOIN stock_location sl ON sl.id = sm.location_dest_id
				 WHERE sm.product_id = $1
				   AND sm.state = 'done'
				   AND sp.origin = $2
				   AND sl.usage = 'internal'`,
				*productID, poName,
			).Scan(&received)
			if received > 0 {
				return orm.Values{"qty_received": received}, nil
			}
		}

		return orm.Values{"qty_received": float64(0)}, nil
	})

	// _compute_price_subtotal and _compute_price_total for PO lines.
	// Mirrors: odoo/addons/purchase/models/purchase_order_line.py _compute_amount()
	computePOLineAmount := func(rs *orm.Recordset) (orm.Values, error) {
		env := rs.Env()
		lineID := rs.IDs()[0]
		var qty, price, discount float64
		env.Tx().QueryRow(env.Ctx(),
			`SELECT COALESCE(product_qty, 0), COALESCE(price_unit, 0), COALESCE(discount, 0)
			 FROM purchase_order_line WHERE id = $1`, lineID,
		).Scan(&qty, &price, &discount)
		subtotal := qty * price * (1 - discount/100)

		// Compute tax from linked taxes
		var taxTotal float64
		taxRows, err := env.Tx().Query(env.Ctx(),
			`SELECT t.amount, t.amount_type, COALESCE(t.price_include, false)
			 FROM account_tax t
			 JOIN account_tax_purchase_order_line_rel rel ON rel.account_tax_id = t.id
			 WHERE rel.purchase_order_line_id = $1`, lineID)
		if err == nil {
			for taxRows.Next() {
				var taxRate float64
				var amountType string
				var priceInclude bool
				if err := taxRows.Scan(&taxRate, &amountType, &priceInclude); err != nil {
					break
				}
				switch amountType {
				case "percent":
					if priceInclude {
						taxTotal += subtotal - (subtotal / (1 + taxRate/100))
					} else {
						taxTotal += subtotal * taxRate / 100
					}
				case "fixed":
					taxTotal += taxRate
				}
			}
			taxRows.Close()
		}

		return orm.Values{
			"price_subtotal": subtotal,
			"price_tax":      taxTotal,
			"price_total":    subtotal + taxTotal,
		}, nil
	}
	pol.RegisterCompute("price_subtotal", computePOLineAmount)
	pol.RegisterCompute("price_tax", computePOLineAmount)
	pol.RegisterCompute("price_total", computePOLineAmount)

	// Onchange: product_id → name, price_unit
	// Mirrors: odoo/addons/purchase/models/purchase_order_line.py _compute_price_unit_and_date_planned_and_name()
	pol.RegisterOnchange("product_id", func(env *orm.Environment, vals orm.Values) orm.Values {
		result := make(orm.Values)

		var productID int64
		switch v := vals["product_id"].(type) {
		case int64:
			productID = v
		case float64:
			productID = int64(v)
		case map[string]interface{}:
			if id, ok := v["id"]; ok {
				switch n := id.(type) {
				case float64:
					productID = int64(n)
				case int64:
					productID = n
				}
			}
		}
		if productID <= 0 {
			return result
		}

		var name string
		var standardPrice float64
		err := env.Tx().QueryRow(env.Ctx(),
			`SELECT COALESCE(pt.name, ''), COALESCE(pt.standard_price, 0)
			 FROM product_product pp
			 JOIN product_template pt ON pt.id = pp.product_tmpl_id
			 WHERE pp.id = $1`, productID,
		).Scan(&name, &standardPrice)
		if err != nil {
			return result
		}

		result["name"] = name
		result["price_unit"] = standardPrice
		return result
	})
}

// initAccountMoveLinePurchaseExtension extends account.move.line with purchase_line_id.
// Mirrors: odoo/addons/purchase/models/purchase_order_line.py (invoice_lines / purchase_line_id)
func initAccountMoveLinePurchaseExtension() {
	aml := orm.ExtendModel("account.move.line")
	aml.AddFields(
		orm.Many2one("purchase_line_id", "purchase.order.line", orm.FieldOpts{
			String: "Purchase Order Line", Index: true,
		}),
	)
}

// initResPartnerPurchaseExtension extends res.partner with purchase-specific fields.
// Mirrors: odoo/addons/purchase/models/res_partner.py
func initResPartnerPurchaseExtension() {
	partner := orm.ExtendModel("res.partner")

	partner.AddFields(
		orm.One2many("purchase_order_ids", "purchase.order", "partner_id", orm.FieldOpts{
			String: "Purchase Orders",
		}),
		orm.Integer("purchase_order_count", orm.FieldOpts{
			String: "Purchase Order Count", Compute: "_compute_purchase_order_count",
		}),
		orm.Monetary("purchase_order_total", orm.FieldOpts{
			String: "Total Purchases", Compute: "_compute_purchase_order_total", CurrencyField: "currency_id",
		}),
	)

	partner.RegisterCompute("purchase_order_count", func(rs *orm.Recordset) (orm.Values, error) {
		env := rs.Env()
		partnerID := rs.IDs()[0]
		var count int
		env.Tx().QueryRow(env.Ctx(),
			`SELECT COUNT(*) FROM purchase_order WHERE partner_id = $1`, partnerID).Scan(&count)
		return orm.Values{"purchase_order_count": count}, nil
	})

	partner.RegisterCompute("purchase_order_total", func(rs *orm.Recordset) (orm.Values, error) {
		env := rs.Env()
		partnerID := rs.IDs()[0]
		var total float64
		env.Tx().QueryRow(env.Ctx(),
			`SELECT COALESCE(SUM(amount_total::float8), 0) FROM purchase_order
			 WHERE partner_id = $1 AND state IN ('purchase', 'done')`, partnerID).Scan(&total)
		return orm.Values{"purchase_order_total": total}, nil
	})
}

// initProductSupplierInfo registers product.supplierinfo — vendor pricelists for products.
// Mirrors: odoo/addons/product/models/product_supplierinfo.py
func initProductSupplierInfo() {
	m := orm.NewModel("product.supplierinfo", orm.ModelOpts{
		Description: "Supplier Pricelist",
		Order:       "min_qty asc, price asc, id",
	})

	m.AddFields(
		orm.Many2one("partner_id", "res.partner", orm.FieldOpts{
			String: "Vendor", Required: true, Index: true,
			Help:   "Vendor of this product",
		}),
		orm.Many2one("product_tmpl_id", "product.template", orm.FieldOpts{
			String: "Product Template", Index: true,
			Help:   "Product template this supplier price applies to",
		}),
		orm.Many2one("product_id", "product.product", orm.FieldOpts{
			String: "Product Variant", Index: true,
			Help:   "Specific product variant (leave empty for all variants of the template)",
		}),
		orm.Float("min_qty", orm.FieldOpts{
			String: "Minimum Quantity", Default: 0.0,
			Help:   "Minimum quantity to order from this vendor to get this price",
		}),
		orm.Float("price", orm.FieldOpts{
			String: "Price", Required: true,
			Help:   "Vendor price for the specified quantity",
		}),
		orm.Integer("delay", orm.FieldOpts{
			String: "Delivery Lead Time (Days)", Default: 1,
			Help:   "Number of days between order confirmation and reception",
		}),
		orm.Date("date_start", orm.FieldOpts{
			String: "Start Date",
			Help:   "Start date for this vendor price validity",
		}),
		orm.Date("date_end", orm.FieldOpts{
			String: "End Date",
			Help:   "End date for this vendor price validity",
		}),
		orm.Many2one("company_id", "res.company", orm.FieldOpts{
			String: "Company", Index: true,
		}),
		orm.Many2one("currency_id", "res.currency", orm.FieldOpts{
			String: "Currency",
		}),
		orm.Char("product_name", orm.FieldOpts{
			String: "Vendor Product Name",
			Help:   "Product name used by the vendor",
		}),
		orm.Char("product_code", orm.FieldOpts{
			String: "Vendor Product Code",
			Help:   "Product code used by the vendor",
		}),
		orm.Integer("sequence", orm.FieldOpts{
			String: "Sequence", Default: 1,
		}),
	)

	// _get_supplier_price: Look up the best price for a product + vendor + quantity.
	// Finds the supplierinfo record with the highest min_qty that is <= the requested qty,
	// filtered by vendor and product, respecting date validity.
	// Mirrors: odoo/addons/product/models/product_supplierinfo.py ProductSupplierinfo._select_seller()
	m.RegisterMethod("_get_supplier_price", func(rs *orm.Recordset, args ...interface{}) (interface{}, error) {
		env := rs.Env()

		if len(args) < 3 {
			return nil, fmt.Errorf("product.supplierinfo: _get_supplier_price requires (product_id, partner_id, quantity)")
		}

		var productID, partnerID int64
		var quantity float64

		switch v := args[0].(type) {
		case float64:
			productID = int64(v)
		case int64:
			productID = v
		}
		switch v := args[1].(type) {
		case float64:
			partnerID = int64(v)
		case int64:
			partnerID = v
		}
		switch v := args[2].(type) {
		case float64:
			quantity = v
		case int64:
			quantity = float64(v)
		}

		if productID == 0 || partnerID == 0 {
			return nil, fmt.Errorf("product.supplierinfo: product_id and partner_id are required")
		}

		// Find the product template for this product variant
		var productTmplID int64
		env.Tx().QueryRow(env.Ctx(),
			`SELECT COALESCE(product_tmpl_id, 0) FROM product_product WHERE id = $1`,
			productID).Scan(&productTmplID)

		// Query: find the best matching supplierinfo record.
		// Priority: exact product_id match > template match, highest min_qty <= requested qty,
		// date validity respected, lowest price wins ties.
		var bestPrice float64
		var bestDelay int
		var found bool

		err := env.Tx().QueryRow(env.Ctx(),
			`SELECT si.price, COALESCE(si.delay, 1)
			 FROM product_supplierinfo si
			 WHERE si.partner_id = $1
			   AND (si.product_id = $2 OR (si.product_id IS NULL AND si.product_tmpl_id = $3))
			   AND COALESCE(si.min_qty, 0) <= $4
			   AND (si.date_start IS NULL OR si.date_start <= CURRENT_DATE)
			   AND (si.date_end IS NULL OR si.date_end >= CURRENT_DATE)
			 ORDER BY
			   CASE WHEN si.product_id = $2 THEN 0 ELSE 1 END,
			   si.min_qty DESC,
			   si.price ASC
			 LIMIT 1`,
			partnerID, productID, productTmplID, quantity,
		).Scan(&bestPrice, &bestDelay)

		if err == nil {
			found = true
		}

		if !found {
			return map[string]interface{}{
				"found": false,
				"price": float64(0),
				"delay": 0,
			}, nil
		}

		return map[string]interface{}{
			"found": true,
			"price": bestPrice,
			"delay": bestDelay,
		}, nil
	})
}

// initPurchaseOrderAmount extends purchase.order with amount compute functions.
// Mirrors: odoo/addons/purchase/models/purchase_order.py PurchaseOrder._compute_amount()
func initPurchaseOrderAmount() {
	po := orm.ExtendModel("purchase.order")

	computeAmounts := func(rs *orm.Recordset) (orm.Values, error) {
		env := rs.Env()
		poID := rs.IDs()[0]

		var untaxed, tax, total float64
		err := env.Tx().QueryRow(env.Ctx(),
			`SELECT
				COALESCE(SUM(price_subtotal), 0),
				COALESCE(SUM(price_total - price_subtotal), 0),
				COALESCE(SUM(price_total), 0)
			 FROM purchase_order_line WHERE order_id = $1`, poID,
		).Scan(&untaxed, &tax, &total)

		if err != nil {
			// Fallback: compute from raw line values
			err = env.Tx().QueryRow(env.Ctx(),
				`SELECT
					COALESCE(SUM(product_qty * price_unit * (1 - COALESCE(discount,0)/100)), 0)
				 FROM purchase_order_line WHERE order_id = $1`, poID,
			).Scan(&untaxed)
			if err != nil {
				return nil, fmt.Errorf("purchase: compute amounts for PO %d: %w", poID, err)
			}
			total = untaxed
			tax = 0
		}

		return orm.Values{
			"amount_untaxed": untaxed,
			"amount_tax":     tax,
			"amount_total":   total,
		}, nil
	}
	po.RegisterCompute("amount_untaxed", computeAmounts)
	po.RegisterCompute("amount_tax", computeAmounts)
	po.RegisterCompute("amount_total", computeAmounts)

	// _compute_invoice_status for the whole PO.
	// Mirrors: odoo/addons/purchase/models/purchase_order.py PurchaseOrder._compute_invoice()
	po.RegisterCompute("invoice_status", func(rs *orm.Recordset) (orm.Values, error) {
		env := rs.Env()
		poID := rs.IDs()[0]

		var state string
		env.Tx().QueryRow(env.Ctx(),
			`SELECT COALESCE(state, 'draft') FROM purchase_order WHERE id = $1`, poID).Scan(&state)

		if state != "purchase" && state != "done" {
			return orm.Values{"invoice_status": "no"}, nil
		}

		var totalQty, totalInvoiced float64
		env.Tx().QueryRow(env.Ctx(),
			`SELECT COALESCE(SUM(product_qty), 0), COALESCE(SUM(qty_invoiced), 0)
			 FROM purchase_order_line WHERE order_id = $1`, poID,
		).Scan(&totalQty, &totalInvoiced)

		status := "no"
		if totalQty > 0 {
			if totalInvoiced >= totalQty {
				status = "invoiced"
			} else {
				status = "to invoice"
			}
		}
		return orm.Values{"invoice_status": status}, nil
	})
}