-- ============================================================================
-- 06_vote_state.sql
-- Q: Current vote state for gov action
--    ab474223d40e2e3540555364be27e161a809c33651408f43d84acff10c0ba306
--    Yes / No / Abstain / not-voted stake for DReps and SPOs, plus the
--    additional yes stake each chamber needs to cross its threshold.
--
-- ---------------------------------------------------------------------------
-- SNAPSHOT RULE -- ESTABLISHED EMPIRICALLY, NOT ASSUMED.
--   Ratification is evaluated at an epoch boundary. The rule used here is:
--     at the boundary ENTERING epoch N, voting power = drep_distr for
--     epoch_no = N, and the votes counted are those on-chain up to and
--     including epoch N-1.
--   This was checked against three past actions in this database whose
--   outcomes are recorded, using the DRep formula below:
--     action 141 (ratified_epoch 644): snap 643 -> 0.506 (< 0.60 dvt_hard_fork)
--                                      snap 644 -> 0.726 (>= threshold)  MATCH
--     action 145 (ratified_epoch 646): snap 645 -> 0.546 (< 0.67)
--                                      snap 646 -> 0.771 (>= 0.67)       MATCH
--     action 151 (expired_epoch 648):  snap 648 -> 0.024 (< 0.67)        MATCH
--   Both boundary cases flip at exactly the recorded ratification epoch, so the
--   rule and the DRep formula are jointly confirmed.
--
--   CONSEQUENCE FOR THIS ACTION: expiration = 653, so the decisive boundary is
--   the one entering epoch 653 and the governing snapshot is drep_distr for
--   epoch 653. THAT SNAPSHOT DOES NOT EXIST YET (max epoch_no in drep_distr is
--   652). Everything below is therefore computed on the epoch-652 snapshot,
--   which is a proxy, not the governing figure. Section F quantifies how much
--   the answer moves across the four most recent snapshots.
--
-- DREP FORMULA (validated above):
--   denominator = stake of ACTIVE registered DReps that did not vote Abstain
--                 + drep_always_no_confidence stake
--   numerator   = stake of ACTIVE registered DReps that voted Yes
--   - drep_always_abstain is excluded entirely (it is abstention by definition).
--   - A DRep is ACTIVE when drep_distr.active_until >= the snapshot epoch;
--     inactive DReps are excluded from both sides.
--   - DReps that did not vote remain in the denominator (they count as No).
--   - Repeat votes are deduplicated: the latest vote by a given voter wins.
--
-- SPO FORMULA -- NOT VALIDATED. READ THIS BEFORE QUOTING ANY SPO NUMBER.
--   No action in this database discriminates between the candidate SPO rules:
--   the one action with an SPO threshold and a known ratification epoch
--   (129, HardForkInitiation) was gated by the Constitutional Committee, not by
--   the SPO tally, so it cannot separate them. Two variants are reported:
--     (a) non-voting pools count as No, and stay in the denominator;
--     (b) non-voting pools take a default from their reward account's vote
--         delegation -- AlwaysAbstain -> Abstain (removed from denominator),
--         AlwaysNoConfidence or undelegated -> No.
--   831 pools currently delegate their reward account to AlwaysAbstain, so the
--   two variants differ materially. Variant (b) is what the Conway
--   specification describes for the post-bootstrap era, but that is read from
--   the specification, not derived from this database. The honest statement is
--   the range between (a) and (b).
--
--   SPO voting power is taken as the pool's stake in epoch_stake for the
--   snapshot epoch. db-sync's pool_stat.voting_power column would be the direct
--   source but pool_stat is EMPTY in this instance, so it cannot be used.
--
-- WHY AN SPO THRESHOLD APPLIES AT ALL:
--   minPoolCost on its own is an economic-group parameter and would carry no
--   SPO threshold. This action ALSO changes maxTxExUnits and maxBlockExUnits
--   (see section A), and maxBlockExUnits is in the security-relevant group,
--   which is what brings pvtpp_security_group (0.51) into force. That group
--   membership comes from the Conway specification; it is not recorded in
--   db-sync and is not verifiable from this database.
-- ============================================================================

\set gaid 154
\set snap 652

\echo '=== A. action identity and every parameter it would change ==='
SELECT gap.id AS gov_action_id, gap.type,
       b.epoch_no AS proposed_in_epoch, b.time AS proposed_at,
       gap.expiration AS expires_at_epoch,
       gap.ratified_epoch, gap.enacted_epoch, gap.expired_epoch, gap.dropped_epoch,
       (gap.deposit/1000000.0)::numeric(20,0) AS deposit_ada
FROM gov_action_proposal gap
JOIN tx t ON t.id=gap.tx_id JOIN block b ON b.id=t.block_id
WHERE gap.id = :gaid;

\echo ''
\echo '--- proposed parameter changes vs values currently in force ---'
WITH pp AS (SELECT * FROM param_proposal WHERE id=(SELECT param_proposal FROM gov_action_proposal WHERE id=:gaid)),
     cur AS (SELECT * FROM epoch_param WHERE epoch_no=(SELECT max(no) FROM epoch))
SELECT 'min_pool_cost' AS parameter, cur.min_pool_cost::text AS current_value, pp.min_pool_cost::text AS proposed_value FROM pp,cur WHERE pp.min_pool_cost IS NOT NULL
UNION ALL SELECT 'max_tx_ex_mem',      cur.max_tx_ex_mem::text,      pp.max_tx_ex_mem::text      FROM pp,cur WHERE pp.max_tx_ex_mem IS NOT NULL
UNION ALL SELECT 'max_tx_ex_steps',    cur.max_tx_ex_steps::text,    pp.max_tx_ex_steps::text    FROM pp,cur WHERE pp.max_tx_ex_steps IS NOT NULL
UNION ALL SELECT 'max_block_ex_mem',   cur.max_block_ex_mem::text,   pp.max_block_ex_mem::text   FROM pp,cur WHERE pp.max_block_ex_mem IS NOT NULL
UNION ALL SELECT 'max_block_ex_steps', cur.max_block_ex_steps::text, pp.max_block_ex_steps::text FROM pp,cur WHERE pp.max_block_ex_steps IS NOT NULL;

\echo ''
\echo '=== B. thresholds in force ==='
SELECT epoch_no, dvt_p_p_economic_group, dvt_p_p_technical_group,
       pvtpp_security_group, committee_min_size, gov_action_lifetime
FROM epoch_param WHERE epoch_no = :snap;

\echo ''
\echo '=== C. DRep tally (snapshot epoch 652, all votes on-chain to date) ==='
WITH latest AS (
  SELECT DISTINCT ON (vp.drep_voter) vp.drep_voter, vp.vote
  FROM voting_procedure vp
  WHERE vp.gov_action_proposal_id=:gaid AND vp.voter_role='DRep' AND vp.invalid IS NULL
  ORDER BY vp.drep_voter, vp.id DESC
),
d AS (
  SELECT dd.amount, dh.view,
         (dh.id IN (14,27)) AS predef,
         COALESCE(dd.active_until >= dd.epoch_no, TRUE) AS active,
         l.vote
  FROM drep_distr dd
  JOIN drep_hash dh ON dh.id=dd.hash_id
  LEFT JOIN latest l ON l.drep_voter=dd.hash_id
  WHERE dd.epoch_no = :snap
),
agg AS (
  SELECT
    sum(amount) FILTER (WHERE NOT predef AND active AND vote='Yes')     AS yes_l,
    sum(amount) FILTER (WHERE NOT predef AND active AND vote='No')      AS no_l,
    sum(amount) FILTER (WHERE NOT predef AND active AND vote='Abstain') AS abs_l,
    sum(amount) FILTER (WHERE NOT predef AND active AND vote IS NULL)   AS novote_l,
    sum(amount) FILTER (WHERE view='drep_always_no_confidence')         AS anc_l,
    sum(amount) FILTER (WHERE view='drep_always_abstain')               AS aa_l,
    sum(amount) FILTER (WHERE NOT predef AND NOT active)                AS inactive_l
  FROM d
)
SELECT (yes_l/1e6)::numeric(20,0) AS yes_ada, (no_l/1e6)::numeric(20,0) AS no_ada,
       (abs_l/1e6)::numeric(20,0) AS abstain_ada, (novote_l/1e6)::numeric(20,0) AS not_voted_ada,
       (anc_l/1e6)::numeric(20,0) AS always_no_conf_ada, (aa_l/1e6)::numeric(20,0) AS always_abstain_excluded_ada,
       (inactive_l/1e6)::numeric(20,0) AS inactive_dreps_excluded_ada,
       ((yes_l+no_l+novote_l+anc_l)/1e6)::numeric(20,0) AS denominator_ada,
       round(yes_l::numeric/(yes_l+no_l+novote_l+anc_l)::numeric,4) AS yes_ratio,
       0.67 AS threshold,
       GREATEST(0, (0.67*(yes_l+no_l+novote_l+anc_l) - yes_l)/1e6)::numeric(20,0) AS additional_yes_ada_needed
FROM agg;

\echo ''
\echo '=== D. SPO tally (snapshot epoch 652, all votes on-chain to date) ==='
\echo '--- variant (a): non-voting pools count as No ---'
WITH latest AS (
  SELECT DISTINCT ON (vp.pool_voter) vp.pool_voter AS pool_hash_id, vp.vote
  FROM voting_procedure vp
  WHERE vp.gov_action_proposal_id=:gaid AND vp.voter_role='SPO' AND vp.invalid IS NULL
  ORDER BY vp.pool_voter, vp.id DESC
),
pstake AS (SELECT pool_id AS pool_hash_id, sum(amount) AS stake FROM epoch_stake WHERE epoch_no=:snap GROUP BY 1),
p AS (SELECT ps.pool_hash_id, ps.stake, l.vote FROM pstake ps LEFT JOIN latest l USING (pool_hash_id)),
agg AS (
  SELECT sum(stake) FILTER (WHERE vote='Yes') AS yes_l,
         sum(stake) FILTER (WHERE vote='No') AS no_l,
         sum(stake) FILTER (WHERE vote='Abstain') AS abs_l,
         sum(stake) FILTER (WHERE vote IS NULL) AS novote_l
  FROM p
)
SELECT (yes_l/1e6)::numeric(20,0) AS yes_ada, (no_l/1e6)::numeric(20,0) AS no_ada,
       (abs_l/1e6)::numeric(20,0) AS abstain_ada, (novote_l/1e6)::numeric(20,0) AS not_voted_ada,
       ((yes_l+no_l+novote_l)/1e6)::numeric(20,0) AS denominator_ada,
       round(yes_l::numeric/(yes_l+no_l+novote_l)::numeric,4) AS yes_ratio,
       0.51 AS threshold,
       GREATEST(0,(0.51*(yes_l+no_l+novote_l) - yes_l)/1e6)::numeric(20,0) AS additional_yes_ada_needed
FROM agg;

\echo '--- variant (b): non-voting pools default from reward-account vote delegation ---'
WITH latest AS (
  SELECT DISTINCT ON (vp.pool_voter) vp.pool_voter AS pool_hash_id, vp.vote
  FROM voting_procedure vp
  WHERE vp.gov_action_proposal_id=:gaid AND vp.voter_role='SPO' AND vp.invalid IS NULL
  ORDER BY vp.pool_voter, vp.id DESC
),
lu AS (SELECT DISTINCT ON (hash_id) hash_id, reward_addr_id FROM pool_update ORDER BY hash_id, registered_tx_id DESC, id DESC),
dv AS (SELECT DISTINCT ON (addr_id) addr_id, drep_hash_id FROM delegation_vote ORDER BY addr_id, tx_id DESC, id DESC),
pstake AS (SELECT pool_id AS pool_hash_id, sum(amount) AS stake FROM epoch_stake WHERE epoch_no=:snap GROUP BY 1),
p AS (
  SELECT ps.pool_hash_id, ps.stake,
         COALESCE(l.vote::text,
                  CASE WHEN dv.drep_hash_id = 14 THEN 'Abstain' ELSE 'No' END) AS eff_vote
  FROM pstake ps
  LEFT JOIN latest l USING (pool_hash_id)
  LEFT JOIN lu ON lu.hash_id = ps.pool_hash_id
  LEFT JOIN dv ON dv.addr_id = lu.reward_addr_id
),
agg AS (
  SELECT sum(stake) FILTER (WHERE eff_vote='Yes') AS yes_l,
         sum(stake) FILTER (WHERE eff_vote='No') AS no_l,
         sum(stake) FILTER (WHERE eff_vote='Abstain') AS abs_l
  FROM p
)
SELECT (yes_l/1e6)::numeric(20,0) AS yes_ada, (no_l/1e6)::numeric(20,0) AS no_ada,
       (abs_l/1e6)::numeric(20,0) AS abstain_incl_default_ada,
       ((yes_l+no_l)/1e6)::numeric(20,0) AS denominator_ada,
       round(yes_l::numeric/(yes_l+no_l)::numeric,4) AS yes_ratio,
       0.51 AS threshold,
       GREATEST(0,(0.51*(yes_l+no_l) - yes_l)/1e6)::numeric(20,0) AS additional_yes_ada_needed
FROM agg;

\echo ''
\echo '=== E. Constitutional Committee (quorum gate, counted in members not stake) ==='
WITH ccv AS (
  SELECT DISTINCT ON (vp.committee_voter) vp.committee_voter, vp.vote
  FROM voting_procedure vp
  WHERE vp.gov_action_proposal_id=:gaid AND vp.voter_role='ConstitutionalCommittee' AND vp.invalid IS NULL
  ORDER BY vp.committee_voter, vp.id DESC
),
cm AS (SELECT count(*) AS members FROM committee_member WHERE committee_id=4)
SELECT cm.members AS committee_size,
       (SELECT quorum_numerator||'/'||quorum_denominator FROM committee WHERE id=4) AS quorum,
       count(*) FILTER (WHERE ccv.vote='Yes') AS cc_yes,
       count(*) FILTER (WHERE ccv.vote='No') AS cc_no,
       count(*) FILTER (WHERE ccv.vote='Abstain') AS cc_abstain,
       cm.members - count(*) AS cc_not_voted,
       round(count(*) FILTER (WHERE ccv.vote='Yes')::numeric / cm.members, 4) AS yes_ratio,
       ceil(cm.members * 2.0/3.0) AS yes_votes_needed,
       GREATEST(0, ceil(cm.members*2.0/3.0) - count(*) FILTER (WHERE ccv.vote='Yes')) AS additional_cc_yes_needed
FROM cm LEFT JOIN ccv ON TRUE GROUP BY cm.members;

\echo ''
\echo '--- committee member term expiry (relevant: the decisive boundary is epoch 653) ---'
SELECT expiration_epoch, count(*) AS members
FROM committee_member WHERE committee_id=4 GROUP BY 1 ORDER BY 1;

\echo ''
\echo '=== F. sensitivity of the DRep result to the choice of snapshot epoch ==='
WITH latest AS (
  SELECT DISTINCT ON (vp.drep_voter) vp.drep_voter, vp.vote
  FROM voting_procedure vp
  WHERE vp.gov_action_proposal_id=:gaid AND vp.voter_role='DRep' AND vp.invalid IS NULL
  ORDER BY vp.drep_voter, vp.id DESC
),
d AS (
  SELECT dd.epoch_no, dd.amount, dh.view, (dh.id IN (14,27)) AS predef,
         COALESCE(dd.active_until >= dd.epoch_no, TRUE) AS active, l.vote
  FROM drep_distr dd JOIN drep_hash dh ON dh.id=dd.hash_id
  LEFT JOIN latest l ON l.drep_voter=dd.hash_id
  WHERE dd.epoch_no BETWEEN 649 AND 652
)
SELECT epoch_no AS snapshot_epoch,
       (sum(amount) FILTER (WHERE NOT predef AND active AND vote='Yes')/1e6)::numeric(20,0) AS yes_ada,
       ((sum(amount) FILTER (WHERE NOT predef AND active AND vote IS DISTINCT FROM 'Abstain')
        + sum(amount) FILTER (WHERE view='drep_always_no_confidence'))/1e6)::numeric(20,0) AS denominator_ada,
       round((sum(amount) FILTER (WHERE NOT predef AND active AND vote='Yes'))::numeric
           / (sum(amount) FILTER (WHERE NOT predef AND active AND vote IS DISTINCT FROM 'Abstain')
             + sum(amount) FILTER (WHERE view='drep_always_no_confidence'))::numeric, 4) AS yes_ratio
FROM d GROUP BY 1 ORDER BY 1;