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

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