account based feeds

[rss-reader.git] / src / lib / db.ts

diff --git a/src/lib/db.ts b/src/lib/db.ts
index 0d89478130a8b7eefa48dd07b6f1e7e02d3d9a56..19a2885738ebd4bc4baf0dd695fc74108ce07d2f 100644 (file)
--- a/src/lib/db.ts
+++ b/src/lib/db.ts
@@ -1,7 +1,11 @@
 import Database from 'better-sqlite3';
+import { v4 as uuidv4 } from 'uuid';
 
 const DB = 'feeds.db';
 
+// 10 minutes
+const LOGIN_CODE_TIMEOUT = 1000 * 60 * 10;
+
 export const writer = new Database(DB, {
   verbose: console.log
 });
@@ -11,15 +15,44 @@ export const reader = new Database(DB, {
 });
 
 export const query = {
-  addFeed: writer.prepare('insert into feedlist (id, title, link) values (?, ?, ?)'),
-  getFeedList: reader.prepare('select * from feedlist'),
+  _: {
+    createAccount: writer.prepare('insert into accounts (id, email, signup_date) values (?, ?, ?)'),
+    createLoginCode: writer.prepare('update accounts set login_code = ?, login_code_expires = ? where id = ?'),
+    isValidLoginCode: reader.prepare('select * from accounts where id = ? and login_code = ?'),
+    getAccountByEmail: reader.prepare('select * from accounts where email = ?'),
+    addFeed: writer.prepare('insert into feedlist (id, title, link, account_id) values (?, ?, ?, ?)'),
+    getFeed: reader.prepare('select * from feedlist where account_id = ? and id = ?'),
+    getFeedList: reader.prepare('select * from feedlist where account_id = ?'),
+    ingest_getFeedList: reader.prepare('select * from feedlist'),
+    getUnreadCountForAll: reader.prepare('select count(fi.id) as unread, feed_id, fl.title from feed_items fi join feedlist fl on fl.id = feed_id where fi.read_at = 0 and fl.account_id = ? group by feed_id;'),
+    readAllItems: writer.prepare(`update feed_items set read_at = datetime('now') where feed_id = ?`),
+  },
+  addFeed: (title: string, link: string, account_id: string) => {
+    const id = uuidv4();
+    query._.addFeed.run(id, title, link, account_id);
+    return {
+      id
+    }
+  },
+  readAllItems: (feed_id: string) => {
+    return query._.readAllItems.run(feed_id);
+  },
+  isFeedOwnedBy: (account_id: string, feed_id: string): boolean => {
+    const res = query._.getFeed.get(account_id, feed_id);
+    console.log('OUTPUT', res);
+    return !!res
+  },
+  getFeedList: (account_id: string) => {
+    return query._.getFeedList.all(account_id);
+  },
+  getUnreadCountForAll: (account_id: string) => {
+    return query._.getUnreadCountForAll.all(account_id);
+  },
   getFeedInfo: reader.prepare('select * from feedlist where id = ?'),
   getFeedItemInfo: reader.prepare('select * from feed_items where id = ? and feed_id = ?'),
   addFeedItem: writer.prepare('insert into feed_items (id, feed_id, guid, title, link, pub_date, content) values (?, ?, ?, ?, ?, ?, ?)'),
   getFeedsFor: reader.prepare('select id, feed_id, guid, title, link, pub_date,read_at from feed_items where feed_id = ? and pub_date > ? order by pub_date desc'),
   readItem: writer.prepare('update feed_items set read_at = ? where id = ? and read_at = 0'),
-  getUnreadCountForAll: reader.prepare('select count(id) as unread, feed_id from feed_items where read_at = 0 group by feed_id'),
-  readAllItems: writer.prepare(`update feed_items set read_at = datetime('now') where feed_id = ?`),
   _deleteFeedItems: writer.prepare(`delete from feed_items where feed_id = ?`),
   _deleteFeed: writer.prepare(`delete from feedlist where id = ?`),
   deleteFeed: {
@@ -27,5 +60,56 @@ export const query = {
       query._deleteFeedItems.run(feed_id);
       query._deleteFeed.run(feed_id)
     }
-  } 
+  },
+  createAccount: (email: string) => {
+    const id = uuidv4();
+    const signup_date = Date.now();
+
+    query._.createAccount.run(id, email, signup_date);
+
+    return {
+      id: id,
+      signup_date: signup_date,
+      email: email
+    }
+  },
+  createLoginCode: (email: string, code: string) => {
+    let acct;
+    const expires = Date.now() + LOGIN_CODE_TIMEOUT;
+
+    try {
+      acct = query.getAccountByEmail(email);
+    }
+    catch(e) {
+        acct = query.createAccount(email);
+    }
+    
+    try {
+      const output = query._.createLoginCode.run(code, expires, acct.id);
+
+      return {
+        ...acct,
+        login_code: code,
+        login_code_expires: expires
+      };
+    }
+    catch(e) {
+      console.log(e);
+    }
+  },
+  validateLoginCode: (id: string, code: string): boolean => {
+    const validCode = query._.isValidLoginCode.get(id, code);
+    // code is valid for 10 minues
+    const now = Date.now();
+    return (validCode && now <= validCode.login_code_expires);
+  },
+  getAccountByEmail: (email: string) => {
+    const account = query._.getAccountByEmail.get(email);
+
+    if(!account) {
+      throw new Error('Invalid Account');
+    }
+
+    return account;
+  },
 };