Add E-R model and SQL bootstrap scripts

1 files changed, 31 insertions, 0 deletions

diff --git a/res/sql/views/orcl_accounts.sql b/res/sql/views/orcl_accounts.sql
new file mode 100644
index 0000000..400d38f
--- /dev/null
+++ b/res/sql/views/orcl_accounts.sql
@@ -0,0 +1,31 @@
+CREATE OR REPLACE VIEW accounts AS
+
+  SELECT
+    users.uuid                                                       AS user_uuid,
+    COALESCE(transactions.balance, 0)                                AS balance,
+    COALESCE(sales.open_bids, 0)                                     AS open_bids,
+    COALESCE(transactions.balance, 0) - COALESCE(sales.open_bids, 0) AS equity
+
+  FROM users
+
+    LEFT OUTER JOIN (
+                      SELECT
+                        user_uuid,
+                        SUM(transactions.amount) AS balance
+
+                      FROM transactions
+                      WHERE transaction_date < current_datetime()
+                      GROUP BY user_uuid
+                    ) transactions
+      ON transactions.user_uuid = users.uuid
+
+    LEFT OUTER JOIN (
+                      SELECT
+                        best_bidder_uuid,
+                        SUM(sales.best_offer + sales.charges) AS open_bids
+
+                      FROM sales
+                      WHERE end_date > current_datetime()
+                      GROUP BY best_bidder_uuid
+                    ) sales
+      ON sales.best_bidder_uuid = users.uuid;