5 files changed, 270 insertions, 0 deletions
diff --git a/users/grfn/bbbg/src/bbbg/db/attendee.clj b/users/grfn/bbbg/src/bbbg/db/attendee.clj
new file mode 100644
index 0000000000..da5ee29321
--- /dev/null
+++ b/users/grfn/bbbg/src/bbbg/db/attendee.clj
@@ -0,0 +1,85 @@
+(ns bbbg.db.attendee
+  (:require
+   [bbbg.attendee :as attendee]
+   [bbbg.db :as db]
+   [bbbg.util.sql :refer [count-where]]
+   honeysql-postgres.helpers
+   [honeysql.helpers
+    :refer
+    [merge-group-by merge-join merge-left-join merge-select merge-where]]
+   [bbbg.util.core :as u]))
+
+(defn search
+  ([q] (search {:select [:attendee.*] :from [:attendee]} q))
+  ([db-or-query q]
+   (if (db/database? db-or-query)
+     (db/list db-or-query (search q))
+     (cond-> db-or-query
+       q (merge-where
+          [:or
+           [:ilike :meetup_name (str "%" q "%")]
+           [:ilike :discord_name (str "%" q "%")]]))))
+  ([db query q]
+   (db/list db (search query q))))
+
+(defn for-event
+  ([event-id]
+   (for-event {:select [:attendee.*]
+               :from [:attendee]}
+              event-id))
+  ([db-or-query event-id]
+   (if (db/database? db-or-query)
+     (db/list db-or-query (for-event event-id))
+     (-> db-or-query
+         (merge-select :event-attendee.*)
+         (merge-join :event_attendee [:= :attendee.id :event_attendee.attendee_id])
+         (merge-where [:= :event_attendee.event_id event-id]))))
+  ([db query event-id]
+   (db/list db (for-event query event-id))))
+
+(defn with-stats
+  ([] (with-stats {:select [:attendee.*]
+                   :from [:attendee]}))
+  ([query]
+   (-> query
+       (merge-left-join :event_attendee [:= :attendee.id :event_attendee.attendee_id])
+       (merge-group-by :attendee.id)
+       (merge-select
+        [(count-where :event_attendee.rsvpd_attending) :events-rsvpd]
+        [(count-where :event_attendee.attended) :events-attended]
+        [(count-where [:and
+                       :event_attendee.rsvpd_attending
+                       [:not :event_attendee.attended]])
+         :no-shows]))))
+
+(defn upsert-all!
+  [db attendees]
+  (when (seq attendees)
+    (db/list
+     db
+     {:insert-into :attendee
+      :values (map #(->> %
+                         (db/process-key-map :attendee)
+                         (u/map-keys keyword))
+                   attendees)
+      :upsert {:on-conflict [:meetup-user-id]
+               :do-update-set [:meetup-name]}
+      :returning [:id :meetup-user-id]})))
+
+(comment
+  (def db (:db bbbg.core/system))
+  (db/database? db)
+  (search db "gri")
+  (db/insert! db :attendee {::attendee/meetup-name "Griffin Smith"
+                            ::attendee/discord-name "grfn"
+                            })
+
+  (search db (with-stats) "gri")
+
+  (search (with-stats) "gri")
+
+  (db/list db (with-stats))
+
+  (db/insert! db :attendee {::attendee/meetup-name "Rando Guy"
+                            ::attendee/discord-name "rando"})
+  )
diff --git a/users/grfn/bbbg/src/bbbg/db/attendee_check.clj b/users/grfn/bbbg/src/bbbg/db/attendee_check.clj
new file mode 100644
index 0000000000..492f786bd6
--- /dev/null
+++ b/users/grfn/bbbg/src/bbbg/db/attendee_check.clj
@@ -0,0 +1,55 @@
+(ns bbbg.db.attendee-check
+  (:require
+   [bbbg.attendee :as attendee]
+   [bbbg.attendee-check :as attendee-check]
+   [bbbg.db :as db]
+   [bbbg.user :as user]
+   [bbbg.util.core :as u]))
+
+(defn create! [db params]
+  (db/insert! db :attendee-check
+              (select-keys params [::attendee/id
+                                   ::user/id
+                                   ::attendee-check/last-dose-at])))
+
+(defn attendees-with-last-checks
+  [db attendees]
+  (when (seq attendees)
+    (let [ids (map ::attendee/id attendees)
+          checks
+          (db/list db {:select [:attendee-check.*]
+                       :from [:attendee-check]
+                       :join [[{:select [:%max.attendee-check.checked-at
+                                         :attendee-check.attendee-id]
+                                :from [:attendee-check]
+                                :group-by [:attendee-check.attendee-id]
+                                :where [:in :attendee-check.attendee-id ids]}
+                               :last-check]
+                              [:=
+                               :attendee-check.attendee-id
+                               :last-check.attendee-id]]})
+          users (if (seq checks)
+                  (u/key-by
+                   ::user/id
+                   (db/list db {:select [:public.user.*]
+                                :from [:public.user]
+                                :where [:in :id (map ::user/id checks)]}))
+                  {})
+          checks (map #(assoc % :user (users (::user/id %))) checks)
+          attendee-id->check (u/key-by ::attendee/id checks)]
+      (map #(assoc % :last-check (attendee-id->check (::attendee/id %)))
+           attendees))))
+
+(comment
+  (def db (:db bbbg.core/system))
+
+  (attendees-with-last-checks
+   db
+   (db/list db :attendee)
+   )
+
+  (db/insert! db :attendee-check
+              {::attendee/id #uuid "58bcd372-ff6e-49df-b280-23d24c5ba0f0"
+               ::user/id #uuid "303fb606-5ef0-4682-ad7d-6429c670cd78"
+               ::attendee-check/last-dose-at "2021-12-19"})
+  )
diff --git a/users/grfn/bbbg/src/bbbg/db/event.clj b/users/grfn/bbbg/src/bbbg/db/event.clj
new file mode 100644
index 0000000000..1b5a4e11ec
--- /dev/null
+++ b/users/grfn/bbbg/src/bbbg/db/event.clj
@@ -0,0 +1,94 @@
+(ns bbbg.db.event
+  (:require
+   [bbbg.attendee :as attendee]
+   [bbbg.db :as db]
+   [bbbg.event :as event]
+   [bbbg.util.sql :refer [count-where]]
+   [honeysql.helpers
+    :refer [merge-group-by merge-left-join merge-select merge-where]]
+   [java-time :refer [local-date local-date-time local-time]]))
+
+(defn create! [db event]
+  (db/insert! db :event (select-keys event [::event/date])))
+
+(defn attended!
+  [db params]
+  (db/execute!
+   db
+   {:insert-into :event-attendee
+    :values [{:event_id (::event/id params)
+              :attendee_id (::attendee/id params)
+              :attended true}]
+    :upsert {:on-conflict [:event-id :attendee-id]
+             :do-update-set! {:attended true}}}))
+
+(defn on-day
+  ([day] {:select [:event.*]
+          :from [:event]
+          :where [:= :date (str day)]})
+  ([db day]
+   (db/list db (on-day day))))
+
+
+(def end-of-day-hour
+  ;; 7am utc = 3am nyc
+  7)
+
+(defn current-day
+  ([] (current-day (local-date-time)))
+  ([dt]
+   (if (<= 0
+           (.getHour (local-time dt))
+           end-of-day-hour)
+     (java-time/minus
+      (local-date dt)
+      (java-time/days 1))
+     (local-date dt))))
+
+(comment
+  (current-day
+   (local-date-time
+    2022 5 1
+    1 13 0))
+  )
+
+(defn today
+  ([] (on-day (current-day)))
+  ([db] (db/list db (today))))
+
+(defn upcoming
+  ([] (upcoming {:select [:event.*] :from [:event]}))
+  ([query]
+   (merge-where query [:>= :date (local-date)])))
+
+(defn past
+  ([] (past {:select [:event.*] :from [:event]}))
+  ([query]
+   (merge-where query [:< :date (local-date)])))
+
+(defn with-attendee-counts
+  [query]
+  (-> query
+      (merge-left-join :event_attendee [:= :event.id :event_attendee.event-id])
+      (merge-select :%count.event_attendee.attendee_id)
+      (merge-group-by :event.id :event_attendee.event-id)))
+
+(defn with-stats
+  [query]
+  (-> query
+      (merge-left-join :event_attendee [:= :event.id :event_attendee.event-id])
+      (merge-select
+       [(count-where :event-attendee.rsvpd_attending) :num-rsvps]
+       [(count-where :event-attendee.attended) :num-attendees])
+      (merge-group-by :event.id)))
+
+(comment
+  (def db (:db bbbg.core/system))
+  (db/list db (-> (today) (with-attendee-counts)))
+
+  (honeysql.format/format
+   (honeysql-postgres.helpers/upsert {:insert-into :foo
+                                      :values {:bar 1}}
+                                     (-> (honeysql-postgres.helpers/on-conflict :did)
+                                         (honeysql-postgres.helpers/do-update-set! [:did true]))))
+  )
diff --git a/users/grfn/bbbg/src/bbbg/db/event_attendee.clj b/users/grfn/bbbg/src/bbbg/db/event_attendee.clj
new file mode 100644
index 0000000000..31411e5d45
--- /dev/null
+++ b/users/grfn/bbbg/src/bbbg/db/event_attendee.clj
@@ -0,0 +1,17 @@
+(ns bbbg.db.event-attendee
+  (:require honeysql-postgres.format
+            [bbbg.db :as db]
+            [bbbg.util.core :as u]))
+
+(defn upsert-all!
+  [db attendees]
+  (when (seq attendees)
+    (db/execute!
+     db
+     {:insert-into :event-attendee
+      :values (map #(->> %
+                         (db/process-key-map :event-attendee)
+                         (u/map-keys keyword))
+                   attendees)
+      :upsert {:on-conflict [:event-id :attendee-id]
+               :do-update-set [:rsvpd-attending]}})))
diff --git a/users/grfn/bbbg/src/bbbg/db/user.clj b/users/grfn/bbbg/src/bbbg/db/user.clj
new file mode 100644
index 0000000000..700105ef63
--- /dev/null
+++ b/users/grfn/bbbg/src/bbbg/db/user.clj
@@ -0,0 +1,19 @@
+(ns bbbg.db.user
+  (:require [bbbg.db :as db]
+            [bbbg.user :as user]))
+
+(defn create! [db attrs]
+  (db/insert! db
+              :public.user
+              (select-keys attrs [::user/id
+                                  ::user/username
+                                  ::user/discord-user-id])))
+
+(defn find-or-create! [db attrs]
+  (or
+   (db/fetch db {:select [:*]
+                 :from [:public.user]
+                 :where [:=
+                         :discord-user-id
+                         (::user/discord-user-id attrs)]})
+   (create! db attrs)))