-- Emoji reactions on issues (and pull requests, which are issue rows) and on their comments.
-- Applied by store/migrate.rs. target_kind is 'issue' or 'comment'. reaction is the short name of
-- a forge_core::Reaction (for example '+1' or 'heart'). One row per user, target and emoji, so a
-- second toggle removes the row again.

CREATE TABLE reactions (
    target_kind  TEXT NOT NULL,
    target_id    TEXT NOT NULL,
    user_id      TEXT NOT NULL REFERENCES users(id),
    emoji        TEXT NOT NULL,
    created_at   TEXT NOT NULL,
    PRIMARY KEY (target_kind, target_id, user_id, emoji)
);
CREATE INDEX reactions_target ON reactions(target_kind, target_id);