1  PRAGMA journal_mode=WAL;
  2  PRAGMA busy_timeout = 5000;
  3  PRAGMA synchronous = NORMAL;
  4  
  5  CREATE TABLE sessions (
  6    id text primary key, -- uuidv4
  7    channel_id integer,
  8    twitch_userName varchar(25),
  9    mode varchar(9), -- [COUNTDOWN|ENDLESS]
 10    open integer,
 11    close integer,
 12    avg_viewers integer,
 13    moderators integer,
 14    viewers integer,
 15    followers integer,
 16    subscribers integer
 17  );
 18  
 19  CREATE TABLE configs (
 20    channel_id integer primary key,
 21    twitch_userName varchar(25),
 22    viewer_points integer,
 23    follower_points integer,
 24    subscriber_points integer,
 25    session_length integer,
 26    session_mode varchar(10), 
 27    enabled_submission_multipliers text, -- space delimited string of multis
 28    enabled_upvote_multipliers text, -- space delimited string of multis
 29    suspended text, -- space delimited string of twitch user ids
 30  
 31    exclude_viewers integer, -- boolean indicating whether Viewers can submit or vote
 32    exclude_followers integer, -- boolean indicating whether Followers can submit or vote
 33    
 34    overlay_background_color text, -- hex code or CSS color value for Broadcaster overlay capture background color
 35    overlay_marquee_segments text, -- the enabled segments for the marquee animation
 36    overlay_color_mode text, -- one of DARK or LIGHT
 37    overlay_marquee_topic varchar(50) -- the marquee session topic  
 38  );
 39  
 40  CREATE TABLE questions (
 41    question_id text primary key,
 42    session_id integer,
 43    channel_id integer,
 44    twitch_userName varchar(25),
 45    twitch_userId integer,
 46    question_text text,
 47    points integer, -- the question's score
 48    status text, -- one of VIEWER, FOLLOWER, SUBSCRIBER (relevant per session info)
 49    submission_multi text, -- one of 001, 004...
 50    flags integer,
 51    revealed integer, -- UNIX Epoch TS
 52    rating integer, -- 1-5
 53    locked integer -- 0 or 1
 54  );
 55  
 56  
 57  CREATE TABLE purchases (
 58    purchase_id integer primary key,
 59    channel_id integer,
 60    session_id integer,
 61    purchase_time integer, -- UNIX Epoch TS
 62    product_id text, -- one of TOP10_UPVOTE, TOP10_REVEAL, OWN_UPVOTE, SUBMIT_XXX, UPVOTE_XXX
 63    question_id text, -- uuidv4 but only for upvotes and multis
 64    twitch_userId integer, -- the purchaser's user ID
 65    twitch_userName varchar(25) -- the purchaser's username
 66  );
 67  
 68  
 69  ----------------------------------------------------------------------
 70  ------------------------ ASK 0.0.7 Schema ----------------------------
 71  ----------------------------------------------------------------------
 72  
 73  -- CREATE TABLE sessions (
 74  --   id integer primary key,
 75  --   channel_id integer,
 76  --   twitch_userName varchar(25),
 77  --   session_type varchar(10),
 78  --   submissions integer,
 79  --   answered integer,
 80  --   open integer,
 81  --   close integer,
 82  --   last integer,
 83  --   avg_viewers integer,
 84  --   bans integer,
 85  --   timeouts integer,
 86  --   moderators integer,
 87  --   viewers integer,
 88  --   followers integer,
 89  --   subscribers integer,
 90  --   multipliers integer
 91  -- );
 92  
 93  -- CREATE TABLE configs (
 94  --   channel_id integer primary key,
 95  --   twitch_userName varchar(25),
 96  --   viewer_points integer,
 97  --   follower_points integer,
 98  --   subscriber_points integer,
 99  --   session_length integer,
100  --   session_mode varchar(10), 
101  --   enabled_upvote_multipliers text,
102  --   enabled_submission_multipliers text,
103  --   banned_users text,
104  --   timeout_users text
105  -- );