-- 0007 — Phase 7: owner visibility (doc 13 §Phase-7, doc 09 §1/§8).
-- The digest log: what was pushed to the owner, when, over which channel —
-- idempotency key for the digest:send cron and the audit trail for "did it
-- actually send". Comparison + fraud reports read existing rollups/ledgers.

CREATE TABLE digests (
    id CHAR(36) NOT NULL PRIMARY KEY,
    digest_date DATE NOT NULL,
    channel VARCHAR(20) NOT NULL,
    body MEDIUMTEXT NOT NULL,
    status VARCHAR(10) NOT NULL DEFAULT 'sent',
    detail VARCHAR(200) NULL,
    sent_at_utc DATETIME NOT NULL,
    CONSTRAINT uq_digest UNIQUE (digest_date, channel),
    CONSTRAINT ck_digest_status CHECK (status IN ('sent', 'failed', 'logged'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
