-- Post reports — a viewer flags a specific post (not its author) with a
-- category + optional free-text reason. Mirrors user_reports but keyed to
-- a post; resolved_at is one-way (admins clear it later). The partial
-- index keeps the open-reports queue cheap to list.
CREATE TABLE IF NOT EXISTS post_reports (
    id           UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    reporter_id  UUID         NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    post_id      UUID         NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    category     VARCHAR(32)  NOT NULL,
    reason       TEXT         NOT NULL DEFAULT '',
    created_at   TIMESTAMPTZ  NOT NULL DEFAULT now(),
    resolved_at  TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS post_reports_post_idx
    ON post_reports (post_id, created_at DESC);

CREATE INDEX IF NOT EXISTS post_reports_unresolved_idx
    ON post_reports (created_at DESC) WHERE resolved_at IS NULL;