-- Q-factor clustering taxonomy and per-row assignments.
-- Taxonomy is keyed on shortqtr so quarterly evolution doesn't clobber history.

CREATE TABLE IF NOT EXISTS cecl_qfactor_categories (
    id          INT           NOT NULL AUTO_INCREMENT,
    shortqtr    VARCHAR(8)    NOT NULL,
    name        VARCHAR(255)  NOT NULL,
    description TEXT          DEFAULT NULL,
    parent_id   INT           DEFAULT NULL,
    created_at  DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uk_qtr_name (shortqtr, name),
    KEY idx_parent (parent_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS cecl_qfactor_categorized (
    id                   BIGINT       NOT NULL AUTO_INCREMENT,
    customfactor_text_id BIGINT       NOT NULL,
    category_id          INT          DEFAULT NULL,
    confidence           DECIMAL(4,3) DEFAULT NULL,
    is_uncategorized     TINYINT(1)   NOT NULL DEFAULT 0,
    assigned_at          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    reviewed             TINYINT(1)   NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    UNIQUE KEY uk_text (customfactor_text_id),
    KEY idx_category (category_id),
    CONSTRAINT fk_qfc_text FOREIGN KEY (customfactor_text_id)
        REFERENCES cecl_benchmark_customfactor_text (id) ON DELETE CASCADE,
    CONSTRAINT fk_qfc_category FOREIGN KEY (category_id)
        REFERENCES cecl_qfactor_categories (id) ON DELETE SET NULL
) ENGINE=InnoDB;