From 3eafb413a48cde60dea8a7355ee621c6acca952f Mon Sep 17 00:00:00 2001
From: Doog <157747121+doogongithub@users.noreply.github.com>
Date: Wed, 21 Feb 2024 22:07:27 -0500
Subject: first commit

---
 db/scripts/water_init.sql | 42 ++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 42 insertions(+)
 create mode 100644 db/scripts/water_init.sql

(limited to 'db/scripts/water_init.sql')

diff --git a/db/scripts/water_init.sql b/db/scripts/water_init.sql
new file mode 100644
index 0000000..d7b912a
--- /dev/null
+++ b/db/scripts/water_init.sql
@@ -0,0 +1,42 @@
+-- user table for users.
+CREATE TABLE IF NOT EXISTS Users (
+    id INT PRIMARY KEY,
+    name TEXT NOT NULL,
+    UNIQUE(name)
+);
+
+-- statistics table for users to log their consumption
+CREATE TABLE IF NOT EXISTS Statistics (
+    id INT PRIMARY KEY,
+    date DATETIME NOT NULL,
+    user_id INT NOT NULL,
+    quantity INT
+);
+
+-- preferences table for a user.
+CREATE TABLE IF NOT EXISTS Preferences (
+    id INT PRIMARY KEY,
+    color TEXT NOT NULL DEFAULT "#000000",
+    user_id INT NOT NULL,
+    size_id INT NOT NULL DEFAULT 1,
+    FOREIGN KEY(user_id) REFERENCES Users(id) ON DELETE CASCADE
+    FOREIGN KEY(size_id) REFERENCES Sizes(id)
+);
+
+-- lookup table for sizes.
+CREATE TABLE IF NOT EXISTS Sizes (
+    id INT PRIMARY KEY,
+    size INT NOT NULL
+    unit TEXT DEFAULT "oz"
+);
+
+-- create default sizes for sizes lookup table.
+INSERT OR IGNORE INTO Sizes (id, size) VALUES (1, 8), (2, 16), (3, 24), (4, 32), (5, 40), (6, 48); 
+
+-- create default users.
+INSERT OR IGNORE INTO Users (id, name) VALUES (1, 'Parker'), (2, 'Zach');
+
+-- create default preferences.
+INSERT OR IGNORE INTO Preferences (id, user_id) VALUES (1, 1), (2, 2);
+
+
-- 
cgit v1.1


From e37c73e33a4aaf7fb8d25b5af03627f20bcda19f Mon Sep 17 00:00:00 2001
From: Doog <157747121+doogongithub@users.noreply.github.com>
Date: Sat, 24 Feb 2024 20:08:35 -0500
Subject: add gitignore

---
 db/scripts/water_init.sql | 10 +++++-----
 1 file changed, 5 insertions(+), 5 deletions(-)

(limited to 'db/scripts/water_init.sql')

diff --git a/db/scripts/water_init.sql b/db/scripts/water_init.sql
index d7b912a..0751c41 100644
--- a/db/scripts/water_init.sql
+++ b/db/scripts/water_init.sql
@@ -1,13 +1,13 @@
 -- user table for users.
 CREATE TABLE IF NOT EXISTS Users (
-    id INT PRIMARY KEY,
+    id INTEGER PRIMARY KEY,
     name TEXT NOT NULL,
     UNIQUE(name)
 );
 
 -- statistics table for users to log their consumption
 CREATE TABLE IF NOT EXISTS Statistics (
-    id INT PRIMARY KEY,
+    id INTEGER PRIMARY KEY,
     date DATETIME NOT NULL,
     user_id INT NOT NULL,
     quantity INT
@@ -15,7 +15,7 @@ CREATE TABLE IF NOT EXISTS Statistics (
 
 -- preferences table for a user.
 CREATE TABLE IF NOT EXISTS Preferences (
-    id INT PRIMARY KEY,
+    id INTEGER PRIMARY KEY,
     color TEXT NOT NULL DEFAULT "#000000",
     user_id INT NOT NULL,
     size_id INT NOT NULL DEFAULT 1,
@@ -25,8 +25,8 @@ CREATE TABLE IF NOT EXISTS Preferences (
 
 -- lookup table for sizes.
 CREATE TABLE IF NOT EXISTS Sizes (
-    id INT PRIMARY KEY,
-    size INT NOT NULL
+    id INTEGER PRIMARY KEY,
+    size INT NOT NULL,
     unit TEXT DEFAULT "oz"
 );
 
-- 
cgit v1.1


From 9f9a33cbf55d38987a66b709284d2bb4ffea0fe9 Mon Sep 17 00:00:00 2001
From: Doog <157747121+doogongithub@users.noreply.github.com>
Date: Thu, 29 Feb 2024 20:13:48 -0500
Subject: modify api, build additional FE components, add types

---
 db/scripts/water_init.sql | 40 +++++++++++++++++++++++++++++++++++-----
 1 file changed, 35 insertions(+), 5 deletions(-)

(limited to 'db/scripts/water_init.sql')

diff --git a/db/scripts/water_init.sql b/db/scripts/water_init.sql
index 0751c41..6a4de24 100644
--- a/db/scripts/water_init.sql
+++ b/db/scripts/water_init.sql
@@ -1,8 +1,9 @@
 -- user table for users.
 CREATE TABLE IF NOT EXISTS Users (
     id INTEGER PRIMARY KEY,
-    name TEXT NOT NULL,
-    UNIQUE(name)
+    password TEXT UNIQUE NOT NULL,
+    uuid TEXT UNIQUE NOT NULL,
+    name TEXT UNIQUE NOT NULL
 );
 
 -- statistics table for users to log their consumption
@@ -19,7 +20,7 @@ CREATE TABLE IF NOT EXISTS Preferences (
     color TEXT NOT NULL DEFAULT "#000000",
     user_id INT NOT NULL,
     size_id INT NOT NULL DEFAULT 1,
-    FOREIGN KEY(user_id) REFERENCES Users(id) ON DELETE CASCADE
+    FOREIGN KEY(user_id) REFERENCES Users(id) ON DELETE CASCADE,
     FOREIGN KEY(size_id) REFERENCES Sizes(id)
 );
 
@@ -30,13 +31,42 @@ CREATE TABLE IF NOT EXISTS Sizes (
     unit TEXT DEFAULT "oz"
 );
 
+CREATE TABLE IF NOT EXISTS APIToken (
+    id INTEGER PRIMARY KEY,
+    token TEXT NOT NULL,
+    user_id INTEGER NOT NULL,
+    FOREIGN KEY(user_id) REFERENCES Users(id) ON DELETE CASCADE
+);
+
 -- create default sizes for sizes lookup table.
 INSERT OR IGNORE INTO Sizes (id, size) VALUES (1, 8), (2, 16), (3, 24), (4, 32), (5, 40), (6, 48); 
 
 -- create default users.
-INSERT OR IGNORE INTO Users (id, name) VALUES (1, 'Parker'), (2, 'Zach');
+INSERT OR IGNORE INTO Users (name, password, uuid) VALUES (
+    'parker',
+    '$2y$10$2UlKrQJQV5cQOo/8VcFlq.ai3MWf7mA4//knEs2xVnHTeB.RnfN.m',
+    '1aa668f3-7527-4a67-9c24-fdf307542eeb'
+), (
+    'zach',
+    '$2y$10$35UJnLpBj8ulhqN/3G4qKe0GYBOa/YunXit11n7ET6zknZpNeKpRS',
+    'be3fd6b7-cf55-4eb8-92d8-1b745b439f34'
+);
 
 -- create default preferences.
-INSERT OR IGNORE INTO Preferences (id, user_id) VALUES (1, 1), (2, 2);
+INSERT OR IGNORE INTO Preferences (user_id) VALUES (1), (2);
 
+CREATE TRIGGER IF NOT EXISTS enforce_size_id
+BEFORE INSERT ON Preferences
+BEGIN
+    SELECT
+        CASE
+            WHEN (
+                SELECT COUNT(*) FROM Sizes WHERE id = new.size_id
+            ) = 0
+            THEN RAISE(ABORT, 'Size does not exist')
+        END;
+END;
 
+-- 
+CREATE VIEW IF NOT EXISTS aggregated_stats AS
+    SELECT u.uuid, SUM(s.quantity * s.size) from Statistics s INNER JOIN Users u ON u.id = s.user_id INNER JOIN Preferences p ON p.user_id = u.id INNER JOIN Size s ON s.id = p.size_id;
-- 
cgit v1.1


From 5fa57845052655883120ba4d19a85d8756fb8d8c Mon Sep 17 00:00:00 2001
From: Zach Berwaldt <zberwaldt@tutamail.com>
Date: Wed, 6 Mar 2024 21:53:07 -0500
Subject: [FEAT] Refactor API main file and models

This commit refactors the `main.go` file in the API directory, as well as the related models in the `models.go` file. The changes include:

- Reordering imports and removing unnecessary imports
- Fixing error messages to be more descriptive
- Handling database connections more efficiently with deferred closures
- Handling errors and returning appropriate error responses
- Adding proper JSON bindings for POST requests
- Adding new views in the database scripts for aggregated statistics and daily user statistics

No changes were made to imports and requires.
---
 db/scripts/water_init.sql | 27 ++++++++++++++++++++++++++-
 1 file changed, 26 insertions(+), 1 deletion(-)

(limited to 'db/scripts/water_init.sql')

diff --git a/db/scripts/water_init.sql b/db/scripts/water_init.sql
index 6a4de24..3b79ed5 100644
--- a/db/scripts/water_init.sql
+++ b/db/scripts/water_init.sql
@@ -18,7 +18,7 @@ CREATE TABLE IF NOT EXISTS Statistics (
 CREATE TABLE IF NOT EXISTS Preferences (
     id INTEGER PRIMARY KEY,
     color TEXT NOT NULL DEFAULT "#000000",
-    user_id INT NOT NULL,
+    user_id INT UNIQUE NOT NULL,
     size_id INT NOT NULL DEFAULT 1,
     FOREIGN KEY(user_id) REFERENCES Users(id) ON DELETE CASCADE,
     FOREIGN KEY(size_id) REFERENCES Sizes(id)
@@ -70,3 +70,28 @@ END;
 -- 
 CREATE VIEW IF NOT EXISTS aggregated_stats AS
     SELECT u.uuid, SUM(s.quantity * s.size) from Statistics s INNER JOIN Users u ON u.id = s.user_id INNER JOIN Preferences p ON p.user_id = u.id INNER JOIN Size s ON s.id = p.size_id;
+
+CREATE VIEW IF NOT EXISTS `DailyUserStatistics` AS
+SELECT users.name, IFNULL(SUM(statistics.quantity), 0) as total, preferences.color as color
+FROM users
+LEFT JOIN statistics ON users.id = statistics.user_id AND DATE(statistics.date) = DATE('now', '-1 day')
+LEFT JOIN preferences ON users.id = preferences.user_id
+GROUP BY users.name;
+
+
+CREATE VIEW IF NOT EXISTS `WeeklyStatisticsView` AS
+  WITH DateSequence(Dates) AS
+    (
+     SELECT Date(CURRENT_DATE, '-7 day')
+     UNION ALL
+     SELECT Date(Dates, '+1 day')
+     FROM DateSequence
+     WHERE Date(Dates, '+1 day') < Date(CURRENT_DATE)
+    )
+SELECT DateSequence.Dates as 'date',
+  IFNULL(SUM(statistics.quantity), 0) AS 'total'
+FROM DateSequence
+LEFT JOIN statistics
+ON Date(statistics.date) = DateSequence.Dates
+GROUP BY DateSequence.Dates
+ORDER BY DateSequence.Dates;
\ No newline at end of file
-- 
cgit v1.1

