-- ============================================================================
-- 02_gross_reward_per_block.sql
-- Q: Current gross reward per block, last 10 completed epochs, individually
--    and as a mean. (Claim under test: ~300 ADA, a figure taken from epoch 415.)
--
-- METRIC DEFINITION (fixed here, used identically in 03_):
--   gross_reward_per_block(E)
--     = SUM(reward.amount) for reward.type IN ('leader','member')
--       AND reward.earned_epoch = E
--     / COUNT(blocks minted by a registered pool in epoch E)
--
--   "member + leader" is the whole pool reward pot as distributed. The leader
--   component already contains fixed cost + margin + the operator's own stake
--   share; the member component is what is left after cost and margin. Their
--   sum is therefore the pool's gross take BEFORE fixed cost and margin are
--   deducted, which is what the brief asks for.
--
--   reward.type 'refund' is excluded (deposit refunds, not block rewards).
--   'reserves'/'treasury' do not appear against a pool in this schema version
--   (db-sync 13.6 routes those to reward_rest).
--
-- BLOCK DENOMINATOR: blocks whose slot_leader maps to a pool_hash
--   (slot_leader.pool_hash_id IS NOT NULL). In the epochs used, this equals
--   the total block count exactly -- there are no OBFT/genesis blocks left.
--
-- EPOCH WINDOW: rewards for epoch E are computed by the ledger at the E+1 -> E+2
--   boundary. At the current tip the newest fully-populated earned_epoch is 650.
--   Epoch 651 is chronologically complete but its rewards do not exist yet, and
--   652 is in progress. The 10 completed epochs used are therefore 641..650.
-- ============================================================================

\echo '--- reward-table availability (sanity: which earned_epochs are populated) ---'
SELECT earned_epoch, count(*) AS reward_rows
FROM reward
WHERE earned_epoch BETWEEN 639 AND 652
GROUP BY 1 ORDER BY 1;

\echo ''
\echo '--- per-epoch gross reward per block, epochs 641-650 ---'
WITH blocks AS (
  SELECT b.epoch_no, count(*) AS blocks_minted,
         count(DISTINCT sl.pool_hash_id) AS pools_minting
  FROM block b
  JOIN slot_leader sl ON sl.id = b.slot_leader_id
  WHERE b.epoch_no BETWEEN 641 AND 650
    AND sl.pool_hash_id IS NOT NULL
  GROUP BY 1
),
rew AS (
  SELECT earned_epoch AS epoch_no, sum(amount) AS gross_lovelace
  FROM reward
  WHERE earned_epoch BETWEEN 641 AND 650
    AND type IN ('leader','member')
  GROUP BY 1
)
SELECT r.epoch_no,
       e.start_time::date                                   AS epoch_start,
       b.blocks_minted,
       b.pools_minting,
       (r.gross_lovelace/1000000.0)::numeric(20,0)          AS gross_rewards_ada,
       ((r.gross_lovelace/1000000.0)/b.blocks_minted)::numeric(20,2)
                                                            AS gross_reward_per_block_ada
FROM rew r
JOIN blocks b ON b.epoch_no = r.epoch_no
JOIN epoch  e ON e.no       = r.epoch_no
ORDER BY r.epoch_no;

\echo ''
\echo '--- summary over epochs 641-650 ---'
WITH blocks AS (
  SELECT b.epoch_no, count(*) AS blocks_minted
  FROM block b JOIN slot_leader sl ON sl.id = b.slot_leader_id
  WHERE b.epoch_no BETWEEN 641 AND 650 AND sl.pool_hash_id IS NOT NULL
  GROUP BY 1
),
rew AS (
  SELECT earned_epoch AS epoch_no, sum(amount) AS gross_lovelace
  FROM reward
  WHERE earned_epoch BETWEEN 641 AND 650 AND type IN ('leader','member')
  GROUP BY 1
),
j AS (
  SELECT r.epoch_no, b.blocks_minted, r.gross_lovelace,
         (r.gross_lovelace/1000000.0)/b.blocks_minted AS per_block
  FROM rew r JOIN blocks b ON b.epoch_no = r.epoch_no
)
SELECT count(*)                                          AS epochs_used,
       min(epoch_no)                                     AS first_epoch,
       max(epoch_no)                                     AS last_epoch,
       avg(per_block)::numeric(20,2)                     AS mean_of_epoch_values_ada,
       (sum(gross_lovelace)/1000000.0/sum(blocks_minted))::numeric(20,2)
                                                         AS pooled_total_over_total_ada,
       min(per_block)::numeric(20,2)                     AS min_epoch_ada,
       max(per_block)::numeric(20,2)                     AS max_epoch_ada,
       stddev_samp(per_block)::numeric(20,2)             AS stddev_ada
FROM j;