# Topic: invoice_lines — posted customer-invoice lines at LINE grain, carrying the per-line
# COMMISSION agent (the OCA sale-commission module).
#
# WHY THIS EXISTS (owner ask, 2026-07-28): "which FISCH customers are assigned to an Agent based
# on the invoice line, what do they generate, and what sales are NOT allocated to an agent."
# Neither `sales_lines` (order grain, customer-master agent) nor `customer_invoices` (document
# grain, no agent at all) can answer that — the agent lives on the invoice LINE, and the
# unallocated bucket is precisely the lines that have no commission row.
#
# ⚠ THIS IS A SECOND AGENT SOURCE AND IT DISAGREES WITH `sales_lines.agent`. That one is the
# CUSTOMER-MASTER book (res.partner.agent_ids); this one is per-line commission attribution.
# They name different people. Always state which source a number came from.
key: invoice_lines
label: Customer invoice lines (with commission agent)
entity: account.move.line

# STORE-ONLY: no live-path domain builder. The live equivalent is a dot-path domain on
# account.move.line; it is deliberately not bound here because the agent join has no live analogue
# that stays honest about the unallocated bucket.

scope:
  documents: "posted customer invoices AND credit notes (move_type out_invoice/out_refund, parent_state posted)"
  lines: >-
    PRODUCT lines only (display_type = 'product'). Commission rows attach to 'cogs' lines too —
    96,936 of the 147,160 rows with a 2025 invoice_date. ⚠ MEASURED 2026-07-29, and NOT what it
    first appears: dropping this filter does NOT move revenue or the customer count, because
    non-product lines ('cogs', 'payment_term', 'line_note') all carry price_subtotal = 0 and
    those customers already have product lines. The ~80% inflation is real for commission ROW
    COUNTS (147,160 vs 50,191 product-line rows) and would corrupt any count-of-LINES measure —
    it is a GRAIN guard, not a money guard. Keep it; just don't claim it protects the totals.
  channels: >-
    business unit comes from the ORIGINATING SALE ORDER (Fisch=5, Royal=6), because
    account.move carries no BU (every invoice sits on team 1 "Sales"). Lines with no sale order
    (direct invoices) have a NULL business unit and are KEPT, not dropped.
  excluded: >-
    Nothing is excluded by partner. GIFTWARE DEALS (Amazon) sits on its own team (7), so a
    Fisch or Royal filter already excludes it — no partner-name exclusion needed here.
  basis: "untaxed line subtotals (price_subtotal); credit-note lines are NEGATED by the metric"
  date_field: "line date (= invoice date)"

grain: >-
  one row per posted customer-invoice product line. The commission table is one row per
  (line × agent), so it is joined DEDUPLICATED to at most one row per line — otherwise the 74
  two-agent lines would double-count revenue.

store:
  table: account_move_line
  alias: l
  # LEFT JOIN from the LINE side is load-bearing: the whole point is the lines with NO commission
  # row. An inner join would silently delete the unallocated bucket — the second half of the
  # question — and still look correct.
  #
  # ca = at most ONE commission row per line, preferring a TRUE agent over a salesperson, then
  # the lowest agent id for determinism.
  join: >-
    LEFT JOIN res_partner rp ON rp.id = l.partner_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
    LEFT JOIN (
      SELECT object_id, agent_id, agent_name, is_true_agent, is_converted_salesman
      FROM (
        SELECT a.object_id, a.agent_id, a.agent_name,
               COALESCE(ap.agent, FALSE) AS is_true_agent,
               COALESCE(ap.salesman_as_agent, FALSE) AS is_converted_salesman,
               ROW_NUMBER() OVER (PARTITION BY a.object_id
                                  ORDER BY COALESCE(ap.agent, FALSE) DESC, a.agent_id) AS rn
        FROM account_invoice_line_agent a
        LEFT JOIN res_partner ap ON ap.id = a.agent_id
      ) ranked WHERE rn = 1
    ) ca ON ca.object_id = l.id
  date_col: "l.date"
  team_col: "so.team_id"
  scope_sql: >-
    l.parent_state = 'posted' AND l.move_type IN ('out_invoice','out_refund')
    AND l.display_type = 'product'
  dims:
    partner: {col: "l.partner_id", name_col: "l.partner_name", label: "Customer"}
    product: {col: "l.product_id", name_col: "l.product_name", label: "Product"}
    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"
    # THE agent dim — real agents ONLY. A salesperson-only line lands here as NULL, which is
    # correct: it is not allocated to an agent.
    agent:
      col: "CASE WHEN ca.is_true_agent THEN ca.agent_id END"
      name_col: "CASE WHEN ca.is_true_agent THEN ca.agent_name END"
      label: "Agent (invoice line)"
    # Whoever is on the commission line, agent or not — for commission/payout questions.
    commission_name:
      col: "ca.agent_id"
      name_col: "ca.agent_name"
      label: "Commission name (agent OR salesperson)"
    agent_role:
      col: "CASE WHEN ca.agent_id IS NULL THEN 'none' WHEN ca.is_true_agent THEN 'agent' ELSE 'salesperson' END"
      name_col: "CASE WHEN ca.agent_id IS NULL THEN 'Nobody' WHEN ca.is_true_agent THEN 'Agent' ELSE 'Salesperson (not an agent)' END"
      label: "Role on the line"
    # The partition the owner asked for. These three values are MECE and sum to the total.
    allocation:
      col: "CASE WHEN ca.is_true_agent THEN 'agent' WHEN ca.agent_id IS NOT NULL THEN 'salesperson' ELSE 'none' END"
      name_col: "CASE WHEN ca.is_true_agent THEN 'Allocated to an agent' WHEN ca.agent_id IS NOT NULL THEN 'Salesperson only' ELSE 'Not allocated' END"
      label: "Agent allocation"
    # The OUTSTANDING OWNER RULING (2026-07-28): 4 of the 11 agents carry salesman_as_agent =
    # True ("convert salesman into agent"). Exposed as a dim so BOTH readings are queryable and
    # neither is baked into the definition of `agent`.
    agent_origin:
      col: "CASE WHEN ca.is_true_agent AND ca.is_converted_salesman THEN 'converted' WHEN ca.is_true_agent THEN 'external' END"
      name_col: "CASE WHEN ca.is_true_agent AND ca.is_converted_salesman THEN 'Agent (converted salesman)' WHEN ca.is_true_agent THEN 'Agent (external)' END"
      label: "Agent origin"

ai_context: >
  Posted customer-invoice PRODUCT lines with the per-line commission agent. Use this topic for:
  "which customers are assigned to an agent based on the invoice line", "what sales are NOT
  allocated to an agent", commission questions, and any agent question phrased about invoices or
  billing. Use `sales_lines` instead for "sales" in the ordinary sense (confirmed order lines) and
  for book/relationship ownership.
  ⚠ TWO AGENT SOURCES, DIFFERENT PEOPLE — say which you used. Here = per-line commission
  (account.invoice.line.agent). There (`sales_lines.agent`) = the customer-master book
  (res.partner.agent_ids). JACOB BRISK, Samuel Shif, Yuda Stern and Billy Petruccelli appear ONLY
  on invoice lines; Moishe Rubenstein, Avi Ash and Sang Ching ONLY on the customer master.
  ⚠ A NAME ON A COMMISSION LINE IS NOT NECESSARILY AN AGENT. "Anna" and "Shantal Erlich" are
  internal SALESPEOPLE (res.partner.agent = False, each with an Odoo login, salesperson of record
  on 1,253 / 184 confirmed orders) who carry commission lines anyway. The `agent` dim already
  excludes them — they show up under `commission_name` with agent_role 'salesperson'. NEVER
  report them as agents; if a question says "agent", they are not one.
  THE ALLOCATION PARTITION: the `allocation` dim is MECE and sums to the total — 'Allocated to an
  agent' / 'Salesperson only' / 'Not allocated'. When asked "what is not allocated to an agent",
  give the strict figure (salesperson + none) AND say what the narrower no-commission-at-all
  figure is, because the two readings differ materially and the questioner may mean either.
  BUSINESS UNIT comes from the originating sale order, so a line with no sale order has a NULL
  business unit — present that as "no sale order (direct invoice)" and keep it in company totals.
  Fisch is team 5. CREDIT NOTES are included and the revenue metric negates them; a positive
  refund total means the negation was lost.
  MEASURED BASELINE for FISCH 2025 (use to sanity-check any answer): total $4,443,244.34 over
  68,540 lines and 979 customers, splitting exactly into $2,995,664.75 allocated to agents,
  $361,198.24 salesperson-only, and $1,086,381.35 not allocated. 518 customers have a real agent
  on at least one line. Martin Pasternak alone is $2,155,660.64.
  A CUSTOMER IS NOT ALL-OR-NOTHING: 403 of those 518 customers are MIXED (some lines carry an
  agent, some do not), so "sales BY agent-assigned customers" ($3,187,771.31) is a different
  number from "sales ALLOCATED to an agent" ($2,995,664.75). Answer whichever was asked and say
  which one it is; they do not subtract to each other.