first commit

1 files changed, 42 insertions, 0 deletions

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);
+
+