-- ADR-088: Trust Score 持久化
-- Phase 4+: TrustScoreManager 從記憶體升級為 PostgreSQL 持久化
-- 解決問題: Pod 重啟後 AI 信任分數歸零，永遠無法累積到 L4 自動放行門檻
-- 2026-04-17 ogt + Claude Sonnet 4.6（亞太）

CREATE TABLE IF NOT EXISTS trust_records (
    action_pattern      VARCHAR(255)    PRIMARY KEY,
    score               INTEGER         NOT NULL DEFAULT 0,
    total_approvals     INTEGER         NOT NULL DEFAULT 0,
    total_rejections    INTEGER         NOT NULL DEFAULT 0,
    last_approval_by    VARCHAR(100),
    last_approval_at    TIMESTAMPTZ,
    last_rejection_by   VARCHAR(100),
    last_rejection_at   TIMESTAMPTZ,
    created_at          TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ     NOT NULL DEFAULT NOW()
);

COMMENT ON TABLE trust_records IS
    'ADR-088: TrustScoreManager 持久化層。記錄每個 action_pattern 的累積信任分數，'
    '跨 Pod 重啟存活。score >= 5 → MEDIUM 自動降 LOW，score >= 10 → HIGH 降 MEDIUM。';

CREATE INDEX IF NOT EXISTS ix_trust_records_score ON trust_records (score DESC);
CREATE INDEX IF NOT EXISTS ix_trust_records_updated ON trust_records (updated_at DESC);
