Initial Commit

23 files changed, 93 insertions, 0 deletions

diff --git a/backend/db/queries/cookies/add_cookie.sql b/backend/db/queries/cookies/add_cookie.sql
new file mode 100644
index 0000000..1519da4
--- /dev/null
+++ b/backend/db/queries/cookies/add_cookie.sql
@@ -0,0 +1,2 @@
+INSERT INTO ibd_tokens (token, expires_at, user_subject, encryption_key)
+VALUES ($1, $2, $3, $4)
\ No newline at end of file
diff --git a/backend/db/queries/cookies/get_any_cookie.sql b/backend/db/queries/cookies/get_any_cookie.sql
new file mode 100644
index 0000000..4e5c823
--- /dev/null
+++ b/backend/db/queries/cookies/get_any_cookie.sql
@@ -0,0 +1,7 @@
+SELECT ibd_tokens.id, token, encrypted_key, kms_key_name, expires_at
+FROM ibd_tokens
+         INNER JOIN keys ON encryption_key = keys.id
+WHERE expires_at > NOW()
+  AND degraded = FALSE
+ORDER BY random()
+LIMIT 1;
diff --git a/backend/db/queries/cookies/get_cookies.sql b/backend/db/queries/cookies/get_cookies.sql
new file mode 100644
index 0000000..3828ec3
--- /dev/null
+++ b/backend/db/queries/cookies/get_cookies.sql
@@ -0,0 +1,7 @@
+SELECT ibd_tokens.id, token, encrypted_key, kms_key_name, expires_at
+FROM ibd_tokens
+         INNER JOIN keys ON encryption_key = keys.id
+WHERE user_subject = $1
+  AND expires_at > NOW()
+  AND degraded = $2
+ORDER BY expires_at DESC;
\ No newline at end of file
diff --git a/backend/db/queries/cookies/set_cookie_degraded.sql b/backend/db/queries/cookies/set_cookie_degraded.sql
new file mode 100644
index 0000000..4fd8222
--- /dev/null
+++ b/backend/db/queries/cookies/set_cookie_degraded.sql
@@ -0,0 +1,3 @@
+UPDATE ibd_tokens
+SET degraded = $1
+WHERE id = $2;
\ No newline at end of file
diff --git a/backend/db/queries/keys/add_key.sql b/backend/db/queries/keys/add_key.sql
new file mode 100644
index 0000000..bb416c5
--- /dev/null
+++ b/backend/db/queries/keys/add_key.sql
@@ -0,0 +1,3 @@
+INSERT INTO keys (kms_key_name, encrypted_key)
+VALUES ($1, $2)
+RETURNING id;
\ No newline at end of file
diff --git a/backend/db/queries/keys/get_key.sql b/backend/db/queries/keys/get_key.sql
new file mode 100644
index 0000000..97d8367
--- /dev/null
+++ b/backend/db/queries/keys/get_key.sql
@@ -0,0 +1,3 @@
+SELECT id, kms_key_name, encrypted_key, created_at
+FROM keys
+WHERE id = $1;
\ No newline at end of file
diff --git a/backend/db/queries/sessions/check_state.sql b/backend/db/queries/sessions/check_state.sql
new file mode 100644
index 0000000..dac73e2
--- /dev/null
+++ b/backend/db/queries/sessions/check_state.sql
@@ -0,0 +1,3 @@
+SELECT 1
+FROM sessions
+where token = $1;
\ No newline at end of file
diff --git a/backend/db/queries/sessions/cleanup_sessions.sql b/backend/db/queries/sessions/cleanup_sessions.sql
new file mode 100644
index 0000000..5f2d22b
--- /dev/null
+++ b/backend/db/queries/sessions/cleanup_sessions.sql
@@ -0,0 +1,2 @@
+DELETE FROM sessions
+WHERE expires_at < NOW();
\ No newline at end of file
diff --git a/backend/db/queries/sessions/create_session.sql b/backend/db/queries/sessions/create_session.sql
new file mode 100644
index 0000000..44f8c56
--- /dev/null
+++ b/backend/db/queries/sessions/create_session.sql
@@ -0,0 +1,2 @@
+INSERT INTO sessions (token, user_subject, access_token, expires_at)
+VALUES ($1, $2, $3, $4);
\ No newline at end of file
diff --git a/backend/db/queries/sessions/create_state.sql b/backend/db/queries/sessions/create_state.sql
new file mode 100644
index 0000000..577ad7e
--- /dev/null
+++ b/backend/db/queries/sessions/create_state.sql
@@ -0,0 +1,2 @@
+INSERT INTO sessions (token, expires_at)
+VALUES ($1, CURRENT_TIMESTAMP + INTERVAL '1 hour');
\ No newline at end of file
diff --git a/backend/db/queries/sessions/get_session.sql b/backend/db/queries/sessions/get_session.sql
new file mode 100644
index 0000000..7da8bd0
--- /dev/null
+++ b/backend/db/queries/sessions/get_session.sql
@@ -0,0 +1,3 @@
+SELECT token, user_subject, access_token, expires_at
+FROM sessions
+WHERE token = $1;
\ No newline at end of file
diff --git a/backend/db/queries/stocks/add_analysis.sql b/backend/db/queries/stocks/add_analysis.sql
new file mode 100644
index 0000000..4bb4903
--- /dev/null
+++ b/backend/db/queries/stocks/add_analysis.sql
@@ -0,0 +1,9 @@
+UPDATE chart_analysis ca
+SET processed  = true,
+    action     = $2,
+    price      = $3,
+    reason     = $4,
+    confidence = $5
+FROM ratings r
+WHERE r.id = $1
+  AND r.chart_analysis = ca.id
\ No newline at end of file
diff --git a/backend/db/queries/stocks/add_rank.sql b/backend/db/queries/stocks/add_rank.sql
new file mode 100644
index 0000000..07f711e
--- /dev/null
+++ b/backend/db/queries/stocks/add_rank.sql
@@ -0,0 +1,2 @@
+INSERT INTO stock_rank (symbol, rank_type, rank)
+VALUES ($1, $2, $3);
\ No newline at end of file
diff --git a/backend/db/queries/stocks/add_rating.sql b/backend/db/queries/stocks/add_rating.sql
new file mode 100644
index 0000000..6c4baa0
--- /dev/null
+++ b/backend/db/queries/stocks/add_rating.sql
@@ -0,0 +1,3 @@
+INSERT INTO ratings (symbol, composite, eps, rel_str, group_rel_str, smr, acc_dis, chart_analysis, price)
+VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
+RETURNING id;
\ No newline at end of file
diff --git a/backend/db/queries/stocks/add_raw_chart_analysis.sql b/backend/db/queries/stocks/add_raw_chart_analysis.sql
new file mode 100644
index 0000000..a599d87
--- /dev/null
+++ b/backend/db/queries/stocks/add_raw_chart_analysis.sql
@@ -0,0 +1,3 @@
+INSERT INTO chart_analysis (raw_analysis)
+VALUES ($1)
+RETURNING id;
\ No newline at end of file
diff --git a/backend/db/queries/stocks/add_stock.sql b/backend/db/queries/stocks/add_stock.sql
new file mode 100644
index 0000000..180a9c3
--- /dev/null
+++ b/backend/db/queries/stocks/add_stock.sql
@@ -0,0 +1,5 @@
+INSERT INTO stocks (symbol, name, ibd_url)
+VALUES ($1, $2, $3)
+ON CONFLICT (symbol)
+    DO UPDATE SET name    = $2,
+                  ibd_url = $3;
\ No newline at end of file
diff --git a/backend/db/queries/stocks/get_stock.sql b/backend/db/queries/stocks/get_stock.sql
new file mode 100644
index 0000000..cecbd84
--- /dev/null
+++ b/backend/db/queries/stocks/get_stock.sql
@@ -0,0 +1,3 @@
+SELECT symbol, name, ibd_url
+FROM stocks
+WHERE symbol = $1;
\ No newline at end of file
diff --git a/backend/db/queries/stocks/get_stock_info.sql b/backend/db/queries/stocks/get_stock_info.sql
new file mode 100644
index 0000000..d4f1bf3
--- /dev/null
+++ b/backend/db/queries/stocks/get_stock_info.sql
@@ -0,0 +1,14 @@
+SELECT r.symbol,
+       s.name,
+       ca.raw_analysis,
+       r.composite,
+       r.eps,
+       r.rel_str,
+       r.group_rel_str,
+       r.smr,
+       r.acc_dis,
+       r.price
+FROM ratings r
+         INNER JOIN stocks s on r.symbol = s.symbol
+         INNER JOIN chart_analysis ca on r.chart_analysis = ca.id
+WHERE r.id = $1;
\ No newline at end of file
diff --git a/backend/db/queries/users/add_ibd_creds.sql b/backend/db/queries/users/add_ibd_creds.sql
new file mode 100644
index 0000000..054f328
--- /dev/null
+++ b/backend/db/queries/users/add_ibd_creds.sql
@@ -0,0 +1,5 @@
+UPDATE users
+SET ibd_username   = $2,
+    ibd_password   = $3,
+    encryption_key = $4
+WHERE subject = $1;
\ No newline at end of file
diff --git a/backend/db/queries/users/add_user.sql b/backend/db/queries/users/add_user.sql
new file mode 100644
index 0000000..bf97ad5
--- /dev/null
+++ b/backend/db/queries/users/add_user.sql
@@ -0,0 +1,3 @@
+INSERT INTO users (subject)
+VALUES ($1)
+ON CONFLICT DO NOTHING;
\ No newline at end of file
diff --git a/backend/db/queries/users/get_ibd_creds.sql b/backend/db/queries/users/get_ibd_creds.sql
new file mode 100644
index 0000000..271abcc
--- /dev/null
+++ b/backend/db/queries/users/get_ibd_creds.sql
@@ -0,0 +1,4 @@
+SELECT ibd_username, ibd_password, encrypted_key, kms_key_name
+FROM users
+INNER JOIN public.keys k on k.id = users.encryption_key
+WHERE subject = $1;
\ No newline at end of file
diff --git a/backend/db/queries/users/get_user.sql b/backend/db/queries/users/get_user.sql
new file mode 100644
index 0000000..567f988
--- /dev/null
+++ b/backend/db/queries/users/get_user.sql
@@ -0,0 +1,3 @@
+SELECT subject, ibd_username, ibd_password, encryption_key
+FROM users
+WHERE subject = $1;
\ No newline at end of file
diff --git a/backend/db/queries/users/list_users.sql b/backend/db/queries/users/list_users.sql
new file mode 100644
index 0000000..ceafeb2
--- /dev/null
+++ b/backend/db/queries/users/list_users.sql
@@ -0,0 +1,2 @@
+SELECT subject, ibd_username, ibd_password, encryption_key
+FROM users;
\ No newline at end of file