-- CECL benchmark staging tables.
-- Populated by scripts/cecl_benchmark_etl.php.
-- All columns NOT NULL except where bank data genuinely may be missing.

-- Column types match the permissive forms used in ceclcompsettings:
-- fixed_lookback, prepay, neg_forward are stored as text in the source
-- (e.g. prepay="9%", neg_forward="Y"/"N", fixed_lookback="1/1/2009"),
-- so we store them as-is and normalize in the aggregation layer.
CREATE TABLE IF NOT EXISTS cecl_benchmark_bank (
    shortname           VARCHAR(64)   NOT NULL,
    shortqtr            VARCHAR(8)    NOT NULL,
    longname            VARCHAR(255)  DEFAULT NULL,
    state               VARCHAR(8)    DEFAULT NULL,
    lookback            DECIMAL(8,4)  DEFAULT NULL,
    fixed_lookback      VARCHAR(32)   DEFAULT NULL,
    lookforward         DECIMAL(8,4)  DEFAULT NULL,
    prepay              VARCHAR(32)   DEFAULT NULL,
    peer_percentage     DECIMAL(8,4)  DEFAULT NULL,
    floor_bps           DECIMAL(8,4)  DEFAULT NULL,
    neg_forward         VARCHAR(8)    DEFAULT NULL,
    fiscal_quarter_type VARCHAR(32)   DEFAULT NULL,
    ingested_at         DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (shortname, shortqtr)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS cecl_benchmark_bank_pool (
    shortname             VARCHAR(64)   NOT NULL,
    shortqtr              VARCHAR(8)    NOT NULL,
    callreportcode        VARCHAR(128)  NOT NULL,
    pool_newname          VARCHAR(255)  DEFAULT NULL,
    qualadj_bps           DECIMAL(12,6) DEFAULT NULL,
    customfactors_bps     DECIMAL(12,6) DEFAULT NULL,
    lossrate              DECIMAL(12,8) DEFAULT NULL,
    confloss              DECIMAL(12,8) DEFAULT NULL,
    leadtime              INT           DEFAULT NULL,
    current_balance       DECIMAL(20,2) DEFAULT NULL,
    locked_indicator      VARCHAR(255)  DEFAULT NULL,
    is_rolling            TINYINT(1)    DEFAULT NULL,
    unfunded_probability  DECIMAL(12,6) DEFAULT NULL,
    ingested_at           DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (shortname, shortqtr, callreportcode)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS cecl_benchmark_bank_pool_indicator (
    id             BIGINT        NOT NULL AUTO_INCREMENT,
    shortname      VARCHAR(64)   NOT NULL,
    shortqtr       VARCHAR(8)    NOT NULL,
    callreportcode VARCHAR(128)  NOT NULL,
    indname        VARCHAR(255)  NOT NULL,
    is_rolling     TINYINT(1)    DEFAULT NULL,
    increment      DECIMAL(12,6) DEFAULT NULL,
    correlation    DECIMAL(12,8) DEFAULT NULL,
    predict        DECIMAL(12,6) DEFAULT NULL,
    ingested_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_bank_pool_qtr (shortname, shortqtr, callreportcode)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS cecl_benchmark_customfactor_text (
    id             BIGINT        NOT NULL AUTO_INCREMENT,
    shortname      VARCHAR(64)   NOT NULL,
    shortqtr       VARCHAR(8)    NOT NULL,
    callreportcode VARCHAR(128)  NOT NULL,
    slot_index     TINYINT       NOT NULL,
    description    TEXT          NOT NULL,
    amount         DECIMAL(12,6) DEFAULT NULL,
    ingested_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_qtr (shortqtr),
    KEY idx_bank_pool (shortname, shortqtr, callreportcode)
) ENGINE=InnoDB;