-- Single aggregate table used by every downstream surface (webinar charts,
-- in-app page). Long-form: one row per metric × scope. Rebuilt on each run.

CREATE TABLE IF NOT EXISTS cecl_benchmark_aggregates (
    id             BIGINT        NOT NULL AUTO_INCREMENT,
    shortqtr       VARCHAR(8)    NOT NULL,
    scope          ENUM('bank','bank_pool','pool','category','indicator') NOT NULL,
    callreportcode VARCHAR(128)  DEFAULT NULL,
    category_id    INT           DEFAULT NULL,
    indicator_name VARCHAR(255)  DEFAULT NULL,
    metric_name    VARCHAR(64)   NOT NULL,
    n_banks        INT           NOT NULL,
    p10            DECIMAL(20,6) DEFAULT NULL,
    p25            DECIMAL(20,6) DEFAULT NULL,
    p50            DECIMAL(20,6) DEFAULT NULL,
    p75            DECIMAL(20,6) DEFAULT NULL,
    p90            DECIMAL(20,6) DEFAULT NULL,
    mean           DECIMAL(20,6) DEFAULT NULL,
    count_true     INT           DEFAULT NULL,
    count_total    INT           DEFAULT NULL,
    computed_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_qtr_scope (shortqtr, scope),
    KEY idx_qtr_metric (shortqtr, metric_name),
    KEY idx_qtr_pool (shortqtr, callreportcode)
) ENGINE=InnoDB;