-- Admin broadcasts — product updates and announcements, shown once per user.
--
-- On "no history": these deliberately create no notification rows and never
-- appear in the tray. But "exactly once per user" cannot be done without
-- storing something — the app has to know whether YOU have seen it. So two
-- tables: the announcement, and who has dismissed it. Nothing per-user is
-- written until that person actually sees it.
CREATE TABLE IF NOT EXISTS broadcasts (
    id          UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    title       VARCHAR(200) NOT NULL,
    body        TEXT         NOT NULL DEFAULT '',
    -- Formatting as offset/length spans, exactly like chat messages: the same
    -- parser produces them and the same component renders them, so no HTML is
    -- stored and nothing needs sanitising at render time.
    entities    JSONB        NOT NULL DEFAULT '[]'::jsonb,
    -- R2 key for the banner. Public bucket: an announcement is not private,
    -- and a signed URL would expire while the modal sat open.
    banner_key  TEXT,
    cta_label   VARCHAR(60),
    -- App-relative path or absolute http(s) URL; validated in the service.
    cta_url     TEXT,
    created_by  UUID         REFERENCES users(id) ON DELETE SET NULL,
    -- NULL = draft. Nothing is delivered until this is set.
    published_at TIMESTAMPTZ,
    -- After this, the announcement stops being offered. This is what keeps a
    -- months-old update from greeting someone who signs up tomorrow.
    expires_at   TIMESTAMPTZ,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- "What is live right now?" — the app's pending lookup and the admin list.
CREATE INDEX IF NOT EXISTS broadcasts_live_idx
    ON broadcasts (published_at DESC) WHERE published_at IS NOT NULL;

-- One row per person who has seen an announcement. Written on dismissal, which
-- is what makes "at most once" true across devices — localStorage would show
-- it again on every new browser.
CREATE TABLE IF NOT EXISTS broadcast_views (
    broadcast_id UUID        NOT NULL REFERENCES broadcasts(id) ON DELETE CASCADE,
    user_id      UUID        NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    seen_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (broadcast_id, user_id)
);