-- ============================================================================
-- 01_min_pool_cost_history.sql
-- Q: Every change to the minPoolCost protocol parameter, with epoch and date.
--
-- SOURCE: epoch_param.min_pool_cost (lovelace), one row per epoch.
-- METHOD: LAG() over epoch_no; emit rows where the value differs from the
--         previous epoch. The first row (epoch 208, Shelley launch) is emitted
--         as the initial value, not a change.
-- NOTE:   epoch_param only exists from the Shelley era (epoch 208). There is no
--         minPoolCost before that; Byron had no stake pools.
-- ============================================================================

\echo '--- coverage of epoch_param ---'
SELECT min(epoch_no) AS first_epoch,
       max(epoch_no) AS last_epoch,
       count(*)      AS rows_present,
       max(epoch_no) - min(epoch_no) + 1 AS epochs_spanned
FROM epoch_param;

\echo ''
\echo '--- minPoolCost change points ---'
WITH p AS (
  SELECT epoch_no,
         min_pool_cost,
         LAG(min_pool_cost) OVER (ORDER BY epoch_no) AS prev_cost
  FROM epoch_param
)
SELECT p.epoch_no                                   AS effective_from_epoch,
       (p.prev_cost / 1000000.0)::numeric(20,6)     AS previous_ada,
       (p.min_pool_cost / 1000000.0)::numeric(20,6) AS new_ada,
       e.start_time                                 AS epoch_start_utc,
       CASE WHEN p.prev_cost IS NULL THEN 'initial value (Shelley launch)'
            ELSE 'change' END                       AS kind
FROM p
JOIN epoch e ON e.no = p.epoch_no
WHERE p.prev_cost IS DISTINCT FROM p.min_pool_cost
ORDER BY p.epoch_no;

\echo ''
\echo '--- how long has the current value been in force ---'
WITH p AS (
  SELECT epoch_no, min_pool_cost,
         LAG(min_pool_cost) OVER (ORDER BY epoch_no) AS prev_cost
  FROM epoch_param
), last_change AS (
  SELECT max(epoch_no) AS epoch_no FROM p WHERE prev_cost IS DISTINCT FROM min_pool_cost
)
SELECT lc.epoch_no                     AS unchanged_since_epoch,
       e.start_time                    AS since_utc,
       (SELECT max(no) FROM epoch)     AS tip_epoch,
       (SELECT max(no) FROM epoch) - lc.epoch_no AS epochs_unchanged,
       round(((SELECT max(no) FROM epoch) - lc.epoch_no) * 5 / 365.25, 2) AS approx_years
FROM last_change lc JOIN epoch e ON e.no = lc.epoch_no;