Settlement ledger

1-- core.tiktok_shop.settlement_ledger
2-- What TikTok actually paid you: every settlement transaction, with its revenue,
3-- fees and shipping cost, on the statement it was paid in.
4--
5-- Grain: statement x account x shop x transaction. One statement is one payout
6-- cycle for one shop; a transaction is an order, an adjustment or a reserve
7-- movement. Date is the statement date in the shop's local time.
8--
9-- WHY THIS EXISTS. core.tiktok_shop.sales_over_time says what customers paid.
10-- Between that and the payout sit platform commission, referral and transaction
11-- fees, affiliate commission, shipping TikTok charged you, and adjustments, none
12-- of which appear on the order. This model is the only place they are visible.
13--
14-- SIGNS ARE TIKTOK'S. Nothing is flipped or ABS()'d: revenue comes in positive and
15-- deductions negative, and settlement_amount is TikTok's own per-transaction
16-- payout. Summed over a statement it should equal the statement's
17-- settlement_amount, which dbt/tests/assert_tiktok_shop__settlement_ties_to_statements.sql
18-- checks. unexplained_amount shows anything the four headline components do not
19-- account for; it should be zero or very close to it.
20--
21-- Fees are dated by statement, not by order. A May order can settle in June, so
22-- do not divide one month of fees by the same month of sales and call it a fee
23-- rate; compare per order (order_id is here for that) or over a long window.
24--
25-- Depends on: staging.tiktok_shop.statement_transaction_report, .statement, .shop
26
27WITH shop AS (
28    SELECT tiktok_account, shop_id, ANY_VALUE(report_timezone) AS report_timezone
29    FROM {{staging.tiktok_shop.shop}}
30    GROUP BY tiktok_account, shop_id
31)
32
33SELECT
34    DATE(st.statement_at, COALESCE(s.report_timezone, 'UTC')) AS statement_date,
35    t.tiktok_account,
36    t.shop_id,
37    t.statement_id,
38    st.payment_id,
39    st.payment_status,
40    st.currency,