B2c claim lines

1-- core.claimlane.b2c_claim_lines
2-- Every product on every consumer ticket, with the ticket's type, outcome and
3-- the refunds booked against the line.
4-- Grain: claimlane_account x ticket_id x product_index (one row per claimed
5-- product line).
6-- Currency: the ticket's currency (the shop currency). Never sum across it.
7-- Depends on: staging.claimlane.b2c_ticket, .b2c_ticket_product,
8--             .b2c_ticket_product_refund
9--
10-- This is the line-grain model the other B2C reports read.
11--
12-- claimed_value is quantity x unit_price: what the claimed goods were sold for.
13-- refunded_amount is what was actually refunded on the line, summed over every
14-- refund booked against it. The two differ for exchanges, store credit, partial
15-- refunds and rejected claims, which is the point of having both.
16--
17-- Refunds are aggregated to the line BEFORE the join: a line refunded in two
18-- steps still contributes its claimed value once.
19--
20-- TICKET-LEVEL AMOUNTS ARE NOT REPEATED ON LINES. return_price and the refunded
21-- original shipping belong to the ticket, so they are only on the ticket's first
22-- line (is_first_line_of_ticket), and summing the columns gives each ticket once.
23
24WITH refunds AS (
25    SELECT
26        claimlane_account,
27        ticket_id,
28        product_index,
29        COUNT(*)                     AS refunds,
30        SUM(refunded_quantity)       AS refunded_quantity,
31        SUM(refunded_amount)         AS refunded_amount,
32        SUM(refunded_shipping_cost)  AS refunded_shipping_cost
33    FROM {{staging.claimlane.b2c_ticket_product_refund}}
34    GROUP BY 1, 2, 3
35)
36
37SELECT
38    t.claimlane_account,
39    t.organization_id,
40    t.ticket_id,