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')

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