-- 012: Issuer credit metrics history (issuer-service)
CREATE TABLE IF NOT EXISTS credit_metrics (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    issuer_id UUID NOT NULL REFERENCES issuers(id),
    redemption_rate NUMERIC(5,4) NOT NULL DEFAULT 0,
    breakage_ratio NUMERIC(5,4) NOT NULL DEFAULT 0,
    market_tenure_months INTEGER NOT NULL DEFAULT 0,
    user_satisfaction NUMERIC(5,4) NOT NULL DEFAULT 0,
    computed_score NUMERIC(5,2) NOT NULL DEFAULT 0,
    computed_rating VARCHAR(5),
    snapshot_date DATE NOT NULL DEFAULT CURRENT_DATE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_credit_metrics_issuer_id ON credit_metrics(issuer_id);
CREATE INDEX idx_credit_metrics_snapshot ON credit_metrics(snapshot_date DESC);