| 1 | -- Splits a usage entry just inserted (?1) between what pays for it, in one |
| 2 | -- statement of the batch that inserted it, so allowances and the entry |
| 3 | -- commit together. ?2 workspace, ?3 month (YYYY-MM) of the usage, ?4 the |
| 4 | -- plan's included usage, ?5 trial eligible, ?6 public repository, ?7 the |
| 5 | -- open-source pool, ?8 the repository's share of it, ?9 g1t covers the rest. |
| 6 | -- |
| 7 | -- A complimentary period covering the entry's own time (not the time it is |
| 8 | -- posted) pays first and alone: late usage lands in the period it happened |
| 9 | -- in. Past the allowance, a `stop` period's overrun is g1t's (`given`) and |
| 10 | -- never charged; a `charge` period's is charged like any usage, but only |
| 11 | -- with a card on file to charge: without one it is g1t's too, so work that |
| 12 | -- was running, or a month-end meter, never leaves a debt nothing can pay. |
| 13 | WITH entry AS ( |
| 14 | SELECT MAX(0, -amount_micros) AS gross, created_at FROM ledger WHERE id=?1 |
| 15 | ), period AS ( |
| 16 | SELECT p.allowance_micros, p.unlimited, p.overage FROM complimentary_periods p, entry |
| 17 | WHERE p.workspace=?2 AND p.starts_at <= entry.created_at AND (p.ends_at IS NULL OR p.ends_at > entry.created_at) |
| 18 | ORDER BY p.starts_at DESC LIMIT 1 |
| 19 | ), room AS ( |
| 20 | SELECT gross, |
| 21 | EXISTS (SELECT 1 FROM period) AS comp, |
| 22 | EXISTS (SELECT 1 FROM period WHERE overage = 'stop' OR NOT ( |
| 23 | EXISTS (SELECT 1 FROM card_checks WHERE workspace = ?2) |
| 24 | OR EXISTS (SELECT 1 FROM accounts WHERE workspace = ?2 AND customer_id IS NOT NULL AND card_last4 IS NOT NULL))) AS comp_stops, |
| 25 | CASE WHEN EXISTS (SELECT 1 FROM period) THEN 0 |
| 26 | ELSE MAX(0, ?4 - COALESCE((SELECT used FROM allowance_use WHERE kind='plan_credit' AND scope=?2 AND month=?3), 0)) END AS plan_left, |
| 27 | CASE WHEN NOT EXISTS (SELECT 1 FROM period) THEN 0 |
| 28 | WHEN (SELECT unlimited FROM period) THEN gross |
| 29 | ELSE MAX(0, (SELECT allowance_micros FROM period) |
| 30 | - COALESCE((SELECT used FROM allowance_use WHERE kind='complimentary' AND scope=?2 AND month=?3), 0)) END AS gift_left, |
| 31 | CASE WHEN ?5 AND NOT EXISTS (SELECT 1 FROM period) |
| 32 | THEN COALESCE((SELECT MAX(0, granted_micros-used_micros) FROM trial_grants WHERE workspace=?2), 0) ELSE 0 END AS trial_left, |
| 33 | CASE WHEN EXISTS (SELECT 1 FROM period) THEN 0 ELSE |
| 34 | MIN(MAX(0, ?7 - COALESCE((SELECT used FROM allowance_use WHERE kind='oss_pool' AND scope='' AND month=?3), 0)), |
| 35 | MAX(0, ?8 - COALESCE((SELECT used FROM allowance_use WHERE kind='oss_repo' AND scope=?6 AND month=?3), 0))) END AS oss_left |
| 36 | FROM entry |
| 37 | ), plan AS ( |
| 38 | SELECT *, MIN(gross, plan_left) AS credit FROM room |
| 39 | ), gift AS ( |
| 40 | SELECT *, MIN(gross-credit, gift_left) AS gift FROM plan |
| 41 | ), trial AS ( |
| 42 | SELECT *, MIN(gross-credit-gift, trial_left) AS trial FROM gift |
| 43 | ), oss AS ( |
| 44 | SELECT *, MIN(gross-credit-gift-trial, oss_left) AS oss FROM trial |
| 45 | ), allocated AS ( |
| 46 | SELECT *, CASE WHEN comp_stops OR (?9 AND NOT comp) THEN gross-credit-gift-trial-oss ELSE 0 END AS given FROM oss |
| 47 | ) |
| 48 | UPDATE ledger SET (amount_micros, credit_micros, gift_micros, trial_micros, oss_micros, given_micros) = |
| 49 | (SELECT -(gross-credit-gift-trial-oss-given), credit, gift, trial, oss, given FROM allocated) |
| 50 | WHERE id=?1 |