Remove HKS 2024 code.

[skvidarsync] / skvidarlang.sql

diff --git a/skvidarlang.sql b/skvidarlang.sql
index 78e39e8679e640b0c51bf110a3fe53db5f88f421..020d066908931475f44699316ed51375a55d51cf 100644 (file)
--- a/skvidarlang.sql
+++ b/skvidarlang.sql
@@ -2,6 +2,7 @@ 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)
 );
 
@@ -15,6 +16,39 @@ CREATE TABLE reaction_log (
 );
 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)
@@ -22,16 +56,17 @@ CREATE VIEW current_reactions AS
            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)
-);
+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,INSERT,DELETE ON TABLE applied TO skvidarlang;
+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,INSERT,DELETE ON TABLE 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;