-- Example staging query, not the Grow or Die production schema.
-- Prepare authorized, deduplicated, same-window inputs:
-- payment_rows(account_id, currency, net_revenue)
-- usage_rows(account_id, currency, cost)
-- Use opaque non-null account IDs and non-null currency codes.
-- net_revenue already includes the refunds/fees in your chosen definition.
-- Missing rows/amounts remain unknown; this query cannot prove completeness.
-- Use decimal or currency minor units in production, not binary floating point.
WITH revenue AS (
  SELECT account_id, currency, SUM(net_revenue) AS net_revenue,
         COUNT(*) AS rows_seen, COUNT(net_revenue) AS rows_known
  FROM payment_rows GROUP BY account_id, currency
), costs AS (
  SELECT account_id, currency, SUM(cost) AS ai_cost,
         COUNT(*) AS rows_seen, COUNT(cost) AS rows_known
  FROM usage_rows GROUP BY account_id, currency
), accounts AS (
  SELECT account_id, currency FROM revenue
  UNION SELECT account_id, currency FROM costs
)
SELECT a.account_id, a.currency,
       CASE WHEN r.rows_seen = r.rows_known THEN r.net_revenue END AS net_revenue,
       CASE WHEN c.rows_seen = c.rows_known THEN c.ai_cost END AS ai_cost,
       CASE WHEN r.rows_seen = r.rows_known AND c.rows_seen = c.rows_known
            THEN r.net_revenue - c.ai_cost END AS contribution
FROM accounts a
LEFT JOIN revenue r USING (account_id, currency)
LEFT JOIN costs c USING (account_id, currency)
ORDER BY a.account_id, a.currency;
