Definitions

WITH fx(currency, aed_per_unit) AS (
  VALUES ('AED', 1.0), ('SAR', 0.97933), ('QAR', 1.00893), ('KWD', 11.9512), ('BHD', 9.76729),
         ('GBP', 4.88443), ('EUR', 4.27118), ('USD', 3.6725), ('INR', 0.0437)
),
list_price(plan, subscription_aed) AS (
  VALUES ('starter', 349.0), ('growth', 649.0), ('enterprise', 1149.0)
),
stores AS (
  SELECT merchant_id, count(*) AS stores
  FROM branches
  WHERE opened_at IS NULL OR opened_at <= TIMESTAMP 'Fri 23:00:00'
  GROUP BY merchant_id
),
ordering AS (
  SELECT b.merchant_id FROM orders_daily d JOIN branches b ON b.id = d.branch_id
  WHERE d.day >= CAST(TIMESTAMP 'Fri 23:00:00' - INTERVAL (30) DAY AS DATE)
    AND d.day < CAST(TIMESTAMP 'Fri 23:00:00' AS DATE) AND d.orders > 0
  UNION
  SELECT b.merchant_id FROM orders o JOIN branches b ON b.id = o.branch_id
  WHERE o.placed_at >= TIMESTAMP 'Fri 23:00:00' - INTERVAL (30) DAY AND o.placed_at < TIMESTAMP 'Fri 23:00:00'
    AND o.status <> 'rejected'
),
billed AS (
  SELECT i.merchant_id, round(sum(i.amount * fx.aed_per_unit), 2) AS invoiced_aed
  FROM invoices i
  JOIN fx ON fx.currency = i.currency
  WHERE i.issued_at >= TIMESTAMP 'Thu 00:00:00 (15 days ago)' AND i.issued_at < TIMESTAMP 'Fri 23:00:00' AND i.amount > 0
  GROUP BY i.merchant_id
),
states AS (
  SELECT m.id AS merchant_id, m.name, mk.code AS market, m.plan,
         greatest(coalesce(s.stores, 0), 1) AS stores,
         m.first_order_at < TIMESTAMP 'Fri 23:00:00' AS live,
         o.merchant_id IS NOT NULL AS ordering,
         bl.merchant_id IS NOT NULL AS billed,
         bl.invoiced_aed, coalesce(lp.subscription_aed, 0) AS subscription_aed
  FROM merchants m
  LEFT JOIN markets mk ON mk.id = m.market_id
  LEFT JOIN stores s ON s.merchant_id = m.id
  LEFT JOIN (SELECT DISTINCT merchant_id FROM ordering) o ON o.merchant_id = m.id
  LEFT JOIN billed bl ON bl.merchant_id = m.id
  LEFT JOIN list_price lp ON lp.plan = m.plan
  WHERE m.contract_signed_at < TIMESTAMP 'Fri 23:00:00'
    AND (m.churned_at IS NULL OR m.churned_at >= TIMESTAMP 'Fri 23:00:00')
),
gaps AS (
  SELECT *,
         CASE WHEN billed AND NOT ordering THEN 'billed_not_ordering'
              WHEN ordering AND NOT billed THEN 'ordering_not_billed'
              WHEN coalesce(live, FALSE) AND NOT ordering AND NOT billed THEN 'live_not_billed'
              WHEN NOT coalesce(live, FALSE) AND NOT ordering AND NOT billed THEN 'signed_not_live'
         END AS gap
  FROM states
)
SELECT gap, merchant_id, name, market, plan, stores, live, ordering, billed,
       CASE gap WHEN 'billed_not_ordering' THEN 'invoiced_this_period'
                WHEN 'live_not_billed' THEN 'inactivity_fee_per_month'
                ELSE 'subscription_per_month' END AS amount_kind,
       round(CASE gap WHEN 'billed_not_ordering' THEN invoiced_aed
                      WHEN 'live_not_billed' THEN 149.0 * stores
                      ELSE subscription_aed * stores END, 2) AS amount_aed
FROM gaps
WHERE gap IS NOT NULL
ORDER BY gap, merchant_id