-- ============================================================================
-- 04_pools_at_floor.sql
-- Q: How many active pools currently declare a fixed cost of exactly 170 ADA,
--    and what is the stake distribution of those pools?
--
-- "LATEST pool_update PER POOL": DISTINCT ON (hash_id) ordered by
--   registered_tx_id DESC, id DESC. Ties within one tx are broken by id, so the
--   last certificate in the last transaction wins -- which is what the ledger
--   applies.
--
-- "ACTIVE" (stated, because the brief does not fully define it):
--   (a) registered: has at least one pool_update; AND
--   (b) not retired: either no pool_retire certificate, or the newest retire
--       certificate was announced BEFORE the newest registration (a
--       re-registration cancels a pending retirement), or its retiring_epoch is
--       still in the future; AND
--   (c) non-zero stake in the current epoch's stake distribution (epoch_stake
--       for the current epoch).
--   A separate cut by "actually minted a block in epochs 641-650" is also given,
--   because a large number of pools satisfy (a)-(c) while being dormant.
--
-- NOTE: this file reads pool_update / pool_retire / epoch_stake only. It does
--   not touch tx_out, so the consumed/prune-mode caveat in the brief does not
--   apply to it.
-- ============================================================================

\set cur_epoch 652

\echo '--- fixed cost declared by active pools (non-zero stake, epoch 652) ---'
WITH lu AS (
  SELECT DISTINCT ON (hash_id) hash_id, fixed_cost, margin, pledge, registered_tx_id
  FROM pool_update ORDER BY hash_id, registered_tx_id DESC, id DESC
),
lr AS (
  SELECT DISTINCT ON (hash_id) hash_id, retiring_epoch, announced_tx_id
  FROM pool_retire ORDER BY hash_id, announced_tx_id DESC, id DESC
),
act AS (
  SELECT lu.* FROM lu LEFT JOIN lr ON lr.hash_id = lu.hash_id
  WHERE lr.hash_id IS NULL
     OR lr.announced_tx_id < lu.registered_tx_id
     OR lr.retiring_epoch > :cur_epoch
),
st AS (SELECT pool_id, sum(amount) AS stake FROM epoch_stake WHERE epoch_no = :cur_epoch GROUP BY 1),
live AS (SELECT a.*, s.stake FROM act a JOIN st s ON s.pool_id = a.hash_id WHERE s.stake > 0)
SELECT CASE WHEN fixed_cost = 170000000 THEN 'exactly 170 (at floor)'
            WHEN fixed_cost <  170000000 THEN 'below 170 (impossible - check)'
            WHEN fixed_cost = 340000000 THEN 'exactly 340 (pre-2023 floor)'
            ELSE 'other, above floor' END          AS band,
       count(*)                                    AS pools,
       round(100.0*count(*)/sum(count(*)) OVER (),1) AS pct_of_pools,
       (sum(stake)/1000000.0)::numeric(20,0)       AS total_stake_ada,
       round(100.0*sum(stake)/sum(sum(stake)) OVER (),1) AS pct_of_stake
FROM live GROUP BY 1 ORDER BY pools DESC;

\echo ''
\echo '--- same, restricted to pools that actually minted a block in 641-650 ---'
WITH lu AS (
  SELECT DISTINCT ON (hash_id) hash_id, fixed_cost, registered_tx_id
  FROM pool_update ORDER BY hash_id, registered_tx_id DESC, id DESC
),
lr AS (
  SELECT DISTINCT ON (hash_id) hash_id, retiring_epoch, announced_tx_id
  FROM pool_retire ORDER BY hash_id, announced_tx_id DESC, id DESC
),
act AS (
  SELECT lu.* FROM lu LEFT JOIN lr ON lr.hash_id = lu.hash_id
  WHERE lr.hash_id IS NULL OR lr.announced_tx_id < lu.registered_tx_id OR lr.retiring_epoch > :cur_epoch
),
blk AS (
  SELECT sl.pool_hash_id, count(*) AS blocks
  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
)
SELECT CASE WHEN a.fixed_cost = 170000000 THEN 'exactly 170 (at floor)'
            WHEN a.fixed_cost = 340000000 THEN 'exactly 340 (pre-2023 floor)'
            ELSE 'other, above floor' END        AS band,
       count(*)                                  AS pools_minting,
       round(100.0*count(*)/sum(count(*)) OVER (),1) AS pct_of_minting_pools,
       sum(bk.blocks)                            AS blocks_641_650,
       round(100.0*sum(bk.blocks)/sum(sum(bk.blocks)) OVER (),1) AS pct_of_blocks
FROM act a JOIN blk bk ON bk.pool_hash_id = a.hash_id
GROUP BY 1 ORDER BY pools_minting DESC;

\echo ''
\echo '--- stake distribution of the pools at exactly 170 ADA ---'
WITH lu AS (
  SELECT DISTINCT ON (hash_id) hash_id, fixed_cost, registered_tx_id
  FROM pool_update ORDER BY hash_id, registered_tx_id DESC, id DESC
),
lr AS (
  SELECT DISTINCT ON (hash_id) hash_id, retiring_epoch, announced_tx_id
  FROM pool_retire ORDER BY hash_id, announced_tx_id DESC, id DESC
),
act AS (
  SELECT lu.* FROM lu LEFT JOIN lr ON lr.hash_id = lu.hash_id
  WHERE lr.hash_id IS NULL OR lr.announced_tx_id < lu.registered_tx_id OR lr.retiring_epoch > :cur_epoch
),
st AS (SELECT pool_id, sum(amount) AS stake FROM epoch_stake WHERE epoch_no = :cur_epoch GROUP BY 1),
floor_pools AS (
  SELECT a.hash_id, s.stake/1000000.0 AS stake_ada
  FROM act a JOIN st s ON s.pool_id = a.hash_id
  WHERE a.fixed_cost = 170000000 AND s.stake > 0
)
SELECT count(*)                                            AS pools,
       (sum(stake_ada))::numeric(20,0)                     AS total_stake_ada,
       (min(stake_ada))::numeric(20,0)                     AS min_ada,
       (percentile_cont(0.25) WITHIN GROUP (ORDER BY stake_ada))::numeric(20,0) AS p25_ada,
       (percentile_cont(0.50) WITHIN GROUP (ORDER BY stake_ada))::numeric(20,0) AS median_ada,
       (avg(stake_ada))::numeric(20,0)                     AS mean_ada,
       (percentile_cont(0.75) WITHIN GROUP (ORDER BY stake_ada))::numeric(20,0) AS p75_ada,
       (percentile_cont(0.95) WITHIN GROUP (ORDER BY stake_ada))::numeric(20,0) AS p95_ada,
       (max(stake_ada))::numeric(20,0)                     AS max_ada
FROM floor_pools;

\echo ''
\echo '--- stake buckets for the pools at exactly 170 ADA ---'
WITH lu AS (
  SELECT DISTINCT ON (hash_id) hash_id, fixed_cost, registered_tx_id
  FROM pool_update ORDER BY hash_id, registered_tx_id DESC, id DESC
),
lr AS (
  SELECT DISTINCT ON (hash_id) hash_id, retiring_epoch, announced_tx_id
  FROM pool_retire ORDER BY hash_id, announced_tx_id DESC, id DESC
),
act AS (
  SELECT lu.* FROM lu LEFT JOIN lr ON lr.hash_id = lu.hash_id
  WHERE lr.hash_id IS NULL OR lr.announced_tx_id < lu.registered_tx_id OR lr.retiring_epoch > :cur_epoch
),
st AS (SELECT pool_id, sum(amount) AS stake FROM epoch_stake WHERE epoch_no = :cur_epoch GROUP BY 1),
floor_pools AS (
  SELECT a.hash_id, s.stake/1000000.0 AS stake_ada
  FROM act a JOIN st s ON s.pool_id = a.hash_id
  WHERE a.fixed_cost = 170000000 AND s.stake > 0
),
b AS (
  SELECT CASE WHEN stake_ada <   100000 THEN 'a. under 100k'
              WHEN stake_ada <   500000 THEN 'b. 100k - 500k'
              WHEN stake_ada <  1000000 THEN 'c. 500k - 1M'
              WHEN stake_ada <  5000000 THEN 'd. 1M - 5M'
              WHEN stake_ada < 10000000 THEN 'e. 5M - 10M'
              WHEN stake_ada < 30000000 THEN 'f. 10M - 30M'
              ELSE                           'g. 30M+' END AS bucket,
         stake_ada
  FROM floor_pools
)
SELECT bucket, count(*) AS pools,
       round(100.0*count(*)/sum(count(*)) OVER (),1) AS pct_pools,
       (sum(stake_ada))::numeric(20,0) AS stake_ada
FROM b GROUP BY 1 ORDER BY 1;

\echo ''
\echo '--- expected blocks/epoch implied by that stake (context for 03_) ---'
WITH tot AS (SELECT sum(amount)/1000000.0 AS total_ada FROM epoch_stake WHERE epoch_no = :cur_epoch),
     blk AS (SELECT avg(c)::numeric AS blocks_per_epoch FROM (
        SELECT count(*) c 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 b.epoch_no) x)
SELECT (tot.total_ada)::numeric(20,0) AS total_active_stake_ada,
       (blk.blocks_per_epoch)::numeric(20,0) AS mean_blocks_per_epoch,
       (tot.total_ada/blk.blocks_per_epoch)::numeric(20,0) AS ada_of_stake_per_block
FROM tot CROSS JOIN blk;