-- Feedback collects user-submitted bug reports, feature requests, and
-- general comments captured by the frontend's /feedback page. Two
-- consumers:
--   1. The team — every insert fans out an HTML notification to every
--      address in FEEDBACK_ADMIN_EMAILS via the existing mailer.
--   2. The /stats ops page — aggregate counts + the most-recent N rows
--      render together so we can scan recent feedback without having to
--      open the DB.
--
-- user_id is nullable (and ON DELETE SET NULL on cascade) because:
--   • Anonymous visitors submit feedback too — they fill in the optional
--     contact_email field instead of leaving an account behind.
--   • When a user later deletes their account we keep the row but
--     forget who sent it. The content stays useful as product signal
--     even after the author is gone.
--
-- contact_email is the soft reply-to surfaced only for the anonymous
-- case; the logged-in case derives reply-to from the joined user row's
-- email at admin-notification time.
--
-- Index on created_at DESC keeps the /stats "recent feedback" list
-- cheap as the table grows.
CREATE TABLE IF NOT EXISTS feedback (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id       UUID REFERENCES users(id) ON DELETE SET NULL,
    category      TEXT NOT NULL,
    message       TEXT NOT NULL,
    contact_email TEXT,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_feedback_created
    ON feedback (created_at DESC);