CREATE TABLE applied (
    channel VARCHAR NOT NULL,
    ts VARCHAR NOT NULL,
    name VARCHAR NOT NULL,
    color VARCHAR NOT NULL DEFAULT 'yellow',
    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 TABLE message_sheet_link (
    channel VARCHAR NOT NULL,
    ts VARCHAR NOT NULL,
    sheet_title VARCHAR NOT NULL,
    tab_name VARCHAR,  -- Cache; blown away every time we see a new message.
    tab_id VARCHAR,   -- Ditto.
    PRIMARY KEY (channel, ts)
);

CREATE TABLE users_nagged (
    userid VARCHAR NOT NULL,
    ts VARCHAR NOT NULL,  -- Of the invitation.
    last_nag TIMESTAMP WITH TIME ZONE NOT NULL,
    PRIMARY KEY (userid, ts)
);

CREATE TABLE group_membership_history (
    -- (channel, ts) says what event we saw them at (even if they were not actually attending).
    channel VARCHAR NOT NULL,
    ts VARCHAR NOT NULL,
    name VARCHAR NOT NULL,  -- As written in the spreadsheet.
    change_seen TIMESTAMP WITH TIME ZONE NOT NULL,
    group_name VARCHAR
);
CREATE INDEX gmh_lookup_idx ON group_membership_history (channel, ts, name, change_seen DESC);

CREATE TABLE oauth_tokens (
    token VARCHAR NOT NULL,
    acquired TIMESTAMP WITH TIME ZONE NOT NULL,
    expiry TIMESTAMP WITH TIME ZONE NOT NULL
);
CREATE INDEX oauth_tokens_exp_idx ON oauth_tokens ( expiry );

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 VIEW current_group_membership_history AS
  SELECT DISTINCT ON (channel,ts,name)
    channel, ts, name, group_name, change_seen
  FROM group_membership_history
  ORDER BY channel, ts, name,change_seen DESC;

GRANT SELECT,UPDATE,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,UPDATE,INSERT ON TABLE message_sheet_link TO skvidarlang;
GRANT SELECT,INSERT ON TABLE users_nagged TO skvidarlang;
GRANT SELECT,INSERT ON TABLE group_membership_history TO skvidarlang;
GRANT SELECT ON TABLE current_group_membership_history TO skvidarlang;
GRANT SELECT,INSERT ON TABLE oauth_tokens TO skvidarlang;
