platform/model/topics/commission_lines.yml

# Topic: commission_lines — the raw commission rows, one per (invoice line × agent).
#
# Separate from `invoice_lines` on purpose. `invoice_lines` joins the commission table
# DEDUPLICATED (at most one agent per line) so that revenue never double-counts. That is right
# for revenue and WRONG for commission: a line with two agents accrues two commission amounts,
# and the deduplicated join would drop one. So commission money is summed here, at its own grain.
#
# Use `invoice_lines` for revenue-by-agent; use this ONLY for commission accrued / settlement.
key: commission_lines
label: Commission lines (agent × invoice line)
entity: account.invoice.line.agent

# STORE-ONLY: no live-path domain builder.

scope:
  documents: >-
    every commission row, including those attached to non-product lines. ⚠ 96,936 of the 147,160
    rows with a 2025 invoice_date hang off COGS lines rather than product lines, so a row count
    here is NOT a count of agent-attributed sales lines.
  basis: "commission amount in company currency (`amount`)"
  date_field: "invoice_date"

grain: "one row per (invoice line × agent); a line with two agents produces two rows"

store:
  table: account_invoice_line_agent
  alias: a
  # object_id -> account_move_line (the LINE), invoice_id -> account_move (the DOCUMENT).
  # Two similarly named FKs; joining the wrong one silently changes the grain.
  join: >-
    LEFT JOIN res_partner ap ON ap.id = a.agent_id
    LEFT JOIN account_move_line l ON l.id = a.object_id
    LEFT JOIN sale_order_line sol ON sol.id = l.sale_line_id
    LEFT JOIN sale_order so ON so.id = sol.order_id
  date_col: "a.invoice_date"
  team_col: "so.team_id"
  scope_sql: "1 = 1"
  dims:
    commission_name: {col: "a.agent_id", name_col: "a.agent_name", label: "Commission name"}
    plan:            {col: "a.commission_id", name_col: "a.commission_name", label: "Commission plan"}
    settled:         {col: "a.settled", name_col: "CASE WHEN a.settled THEN 'Settled' ELSE 'Unsettled' END", label: "Settled"}
    agent_role:
      col: "CASE WHEN COALESCE(ap.agent, FALSE) THEN 'agent' ELSE 'salesperson' END"
      name_col: "CASE WHEN COALESCE(ap.agent, FALSE) THEN 'Agent' ELSE 'Salesperson (not an agent)' END"
      label: "Role"
    line_type:
      col: "l.display_type"
      name_col: "l.display_type"
      label: "Invoice line type"
    team:
      col: "so.team_id"
      name_col: "CASE so.team_id WHEN 5 THEN 'Fisch' WHEN 6 THEN 'Royal' WHEN 7 THEN 'Giftware Deals (Amazon)' WHEN 1 THEN 'Sales (generic)' ELSE 'No sale order' END"
      label: "Business unit"

ai_context: >
  Raw commission rows from the OCA sale-commission module — one row per invoice line per agent.
  Use ONLY for commission money ("what commission did X accrue", settlement status, plan mix).
  For revenue by agent use topic `invoice_lines`, which joins this table deduplicated so revenue
  is not double-counted.
  ⚠ ROW COUNTS HERE ARE NOT SALES-LINE COUNTS: commission rows attach to COGS lines as well as
  product lines (96,936 of 147,160 rows dated 2025 are COGS). Filter `line_type` = 'product'
  before comparing anything here to a sales figure.
  ⚠ A NAME HERE IS NOT NECESSARILY AN AGENT — "Anna" and "Shantal Erlich" are internal
  salespeople (res.partner.agent = False) who carry commission rows. Use the `agent_role` dim to
  separate them; never call them agents.
  Commission PLANS observed: 6.5% (Martin Pasternak), 8%, 5%, 2%, 1%, and a named "Jacob" plan.