-- Rebuild cecl_benchmark_aggregates for a given :shortqtr.
-- All INSERTs are scoped by :shortqtr so runs are idempotent per-quarter.
-- Usage: invoked by scripts/cecl_benchmark_aggregate.php (which sets :shortqtr
-- by PHP substitution before running each statement).

-- Clear existing rows for this quarter.
DELETE FROM cecl_benchmark_aggregates WHERE shortqtr = :shortqtr;

-- --------------------------------------------------------------------------
-- Bank-scope numeric metrics (median / percentiles / mean)
-- Uses MySQL 8 window functions.
-- --------------------------------------------------------------------------

INSERT INTO cecl_benchmark_aggregates
  (shortqtr, scope, metric_name, n_banks, p10, p25, p50, p75, p90, mean)
SELECT :shortqtr, 'bank', metric_name,
       COUNT(*) AS n_banks,
       MAX(CASE WHEN pct_rank <= 0.10 THEN value END) AS p10,
       MAX(CASE WHEN pct_rank <= 0.25 THEN value END) AS p25,
       MAX(CASE WHEN pct_rank <= 0.50 THEN value END) AS p50,
       MAX(CASE WHEN pct_rank <= 0.75 THEN value END) AS p75,
       MAX(CASE WHEN pct_rank <= 0.90 THEN value END) AS p90,
       AVG(value) AS mean
FROM (
  SELECT metric_name, value,
         PERCENT_RANK() OVER (PARTITION BY metric_name ORDER BY value ASC) AS pct_rank
  FROM (
    SELECT 'lookback'        AS metric_name, CAST(lookback        AS DECIMAL(20,6)) AS value FROM cecl_benchmark_bank WHERE shortqtr = :shortqtr AND lookback        IS NOT NULL
    UNION ALL
    SELECT 'lookforward',         CAST(lookforward     AS DECIMAL(20,6))                     FROM cecl_benchmark_bank WHERE shortqtr = :shortqtr AND lookforward     IS NOT NULL
    UNION ALL
    SELECT 'prepay',              CAST(REPLACE(REPLACE(prepay,'%',''),' ','') AS DECIMAL(20,6))  FROM cecl_benchmark_bank WHERE shortqtr = :shortqtr AND prepay IS NOT NULL AND prepay <> '' AND REPLACE(REPLACE(prepay,'%',''),' ','') REGEXP '^[0-9]+(\\.[0-9]+)?$'
    UNION ALL
    SELECT 'peer_percentage',     CAST(peer_percentage AS DECIMAL(20,6))                     FROM cecl_benchmark_bank WHERE shortqtr = :shortqtr AND peer_percentage IS NOT NULL
    UNION ALL
    SELECT 'floor_bps',           CAST(floor_bps       AS DECIMAL(20,6))                     FROM cecl_benchmark_bank WHERE shortqtr = :shortqtr AND floor_bps       IS NOT NULL
  ) t
) ranked
GROUP BY metric_name;

-- --------------------------------------------------------------------------
-- Bank-scope categorical (% of banks with X)
-- --------------------------------------------------------------------------

INSERT INTO cecl_benchmark_aggregates
  (shortqtr, scope, metric_name, n_banks, count_true, count_total)
SELECT :shortqtr, 'bank', 'neg_forward_off',
       COUNT(*), SUM(CASE WHEN neg_forward = 'N' OR neg_forward = '0' THEN 1 ELSE 0 END), COUNT(*)
FROM cecl_benchmark_bank WHERE shortqtr = :shortqtr;

INSERT INTO cecl_benchmark_aggregates
  (shortqtr, scope, metric_name, n_banks, count_true, count_total)
SELECT :shortqtr, 'bank', 'has_floor',
       COUNT(*),
       SUM(CASE WHEN floor_bps IS NOT NULL AND floor_bps > 0 THEN 1 ELSE 0 END),
       COUNT(*)
FROM cecl_benchmark_bank WHERE shortqtr = :shortqtr;

INSERT INTO cecl_benchmark_aggregates
  (shortqtr, scope, metric_name, n_banks, count_true, count_total)
SELECT :shortqtr, 'bank', 'uses_locked_indicators',
       (SELECT COUNT(*) FROM cecl_benchmark_bank WHERE shortqtr = :shortqtr),
       COUNT(DISTINCT bp.shortname),
       (SELECT COUNT(*) FROM cecl_benchmark_bank WHERE shortqtr = :shortqtr)
FROM cecl_benchmark_bank_pool bp
WHERE bp.shortqtr = :shortqtr
  AND bp.locked_indicator IS NOT NULL
  AND bp.locked_indicator <> '';

-- --------------------------------------------------------------------------
-- Pool-scope numeric metrics (per parent callreportcode)
-- Sub-pools (banks that split a pool like "1C2A" into "1C2A-Sub Real Estate")
-- are rolled up to their parent prefix so peer comparisons stay grouped.
-- --------------------------------------------------------------------------

INSERT INTO cecl_benchmark_aggregates
  (shortqtr, scope, callreportcode, metric_name,
   n_banks, p10, p25, p50, p75, p90, mean)
SELECT :shortqtr, 'bank_pool', parent_code, metric_name,
       COUNT(*) AS n_banks,
       MAX(CASE WHEN pct_rank <= 0.10 THEN value END),
       MAX(CASE WHEN pct_rank <= 0.25 THEN value END),
       MAX(CASE WHEN pct_rank <= 0.50 THEN value END),
       MAX(CASE WHEN pct_rank <= 0.75 THEN value END),
       MAX(CASE WHEN pct_rank <= 0.90 THEN value END),
       AVG(value)
FROM (
  SELECT parent_code, metric_name, value,
         PERCENT_RANK() OVER (PARTITION BY parent_code, metric_name ORDER BY value ASC) AS pct_rank
  FROM (
    SELECT SUBSTRING_INDEX(callreportcode,'-',1) AS parent_code, 'qualadj_bps'       AS metric_name, qualadj_bps        AS value FROM cecl_benchmark_bank_pool WHERE shortqtr = :shortqtr AND qualadj_bps       IS NOT NULL
    UNION ALL
    SELECT SUBSTRING_INDEX(callreportcode,'-',1),                'customfactors_bps',     customfactors_bps           FROM cecl_benchmark_bank_pool WHERE shortqtr = :shortqtr AND customfactors_bps IS NOT NULL
    UNION ALL
    SELECT SUBSTRING_INDEX(callreportcode,'-',1),                'lossrate',              lossrate                    FROM cecl_benchmark_bank_pool WHERE shortqtr = :shortqtr AND lossrate          IS NOT NULL
    UNION ALL
    SELECT SUBSTRING_INDEX(callreportcode,'-',1),                'confloss',              confloss                    FROM cecl_benchmark_bank_pool WHERE shortqtr = :shortqtr AND confloss          IS NOT NULL
  ) t
) ranked
GROUP BY parent_code, metric_name;

-- --------------------------------------------------------------------------
-- Top N most-locked call report codes — stored as scope='pool',
-- metric_name='locked_bank_count'. Sub-pools rolled up to parent_code.
-- Order by count_true DESC downstream.
-- --------------------------------------------------------------------------

INSERT INTO cecl_benchmark_aggregates
  (shortqtr, scope, callreportcode, metric_name, n_banks, count_true, count_total)
SELECT :shortqtr, 'pool', SUBSTRING_INDEX(callreportcode,'-',1) AS parent_code, 'locked_bank_count',
       (SELECT COUNT(*) FROM cecl_benchmark_bank WHERE shortqtr = :shortqtr),
       COUNT(DISTINCT shortname),
       (SELECT COUNT(*) FROM cecl_benchmark_bank WHERE shortqtr = :shortqtr)
FROM cecl_benchmark_bank_pool
WHERE shortqtr = :shortqtr
  AND locked_indicator IS NOT NULL AND locked_indicator <> ''
GROUP BY parent_code;

-- --------------------------------------------------------------------------
-- Top forward-looking indicators by distinct-bank adoption, per pool.
-- --------------------------------------------------------------------------

INSERT INTO cecl_benchmark_aggregates
  (shortqtr, scope, callreportcode, indicator_name, metric_name,
   n_banks, count_true, count_total)
SELECT :shortqtr, 'indicator', SUBSTRING_INDEX(callreportcode,'-',1) AS parent_code, indname, 'indicator_bank_count',
       (SELECT COUNT(*) FROM cecl_benchmark_bank WHERE shortqtr = :shortqtr),
       COUNT(DISTINCT shortname),
       (SELECT COUNT(*) FROM cecl_benchmark_bank WHERE shortqtr = :shortqtr)
FROM cecl_benchmark_bank_pool_indicator
WHERE shortqtr = :shortqtr
GROUP BY parent_code, indname;

-- --------------------------------------------------------------------------
-- Q-factor theme prevalence + median amount
-- --------------------------------------------------------------------------

INSERT INTO cecl_benchmark_aggregates
  (shortqtr, scope, category_id, metric_name,
   n_banks, count_true, count_total, p50, mean)
SELECT :shortqtr, 'category', c.id, 'theme_prevalence',
       (SELECT COUNT(*) FROM cecl_benchmark_bank WHERE shortqtr = :shortqtr),
       COUNT(DISTINCT t.shortname),
       (SELECT COUNT(*) FROM cecl_benchmark_bank WHERE shortqtr = :shortqtr),
       (SELECT AVG(x.amount) FROM (
           SELECT AVG(amount) AS amount FROM cecl_benchmark_customfactor_text tt
           JOIN cecl_qfactor_categorized qc ON qc.customfactor_text_id = tt.id
           WHERE qc.category_id = c.id AND tt.shortqtr = :shortqtr
           GROUP BY tt.shortname
       ) x),
       AVG(t.amount)
FROM cecl_qfactor_categories c
JOIN cecl_qfactor_categorized qc ON qc.category_id = c.id
JOIN cecl_benchmark_customfactor_text t ON t.id = qc.customfactor_text_id
WHERE c.shortqtr = :shortqtr AND t.shortqtr = :shortqtr
GROUP BY c.id;