CREATE TABLE applied (
    channel VARCHAR NOT NULL,
    ts VARCHAR NOT NULL,
    name VARCHAR NOT NULL,
    PRIMARY KEY (channel, ts, name)
);

CREATE TABLE reaction_log (
    userid VARCHAR NOT NULL,
    channel VARCHAR NOT NULL,
    ts VARCHAR NOT NULL,
    event_type VARCHAR NOT NULL,
    event_ts VARCHAR NOT NULL,
    reaction VARCHAR NOT NULL
);
CREATE INDEX lookup_idx ON reaction_log USING btree (channel, ts, userid, reaction, event_ts DESC);

CREATE VIEW current_reactions AS
  SELECT userid, channel, ts, reaction
    FROM ( SELECT DISTINCT ON (channel, ts, userid, reaction)
               userid, channel, ts, event_type, event_ts, reaction
           FROM reaction_log ORDER BY channel, ts, userid, reaction, event_ts DESC ) t1
  WHERE event_type = 'reaction_added';

CREATE TABLE reactions (
    userid VARCHAR NOT NULL,
    channel VARCHAR NOT NULL,
    ts VARCHAR NOT NULL,
    reaction VARCHAR NOT NULL,
    PRIMARY KEY (userid, channel, ts, reaction)
);

GRANT SELECT,INSERT,DELETE ON TABLE applied TO skvidarlang;
GRANT SELECT,INSERT ON TABLE reaction_log TO skvidarlang;
GRANT SELECT ON TABLE current_reactions TO skvidarlang;
GRANT SELECT,INSERT,DELETE ON TABLE reactions TO skvidarlang;

