From b32419dfe996fbc9731b48ba528bae67535f4839 Mon Sep 17 00:00:00 2001
From: Zach Berwaldt <zberwaldt@tutamail.com>
Date: Sat, 16 Mar 2024 10:48:52 -0400
Subject: clean up database scripts, start readmes.

---
 db/sql/tables.sql | 39 +++++++++++++++++++++++++++++++++++++++
 1 file changed, 39 insertions(+)
 create mode 100644 db/sql/tables.sql

(limited to 'db/sql/tables.sql')

diff --git a/db/sql/tables.sql b/db/sql/tables.sql
new file mode 100644
index 0000000..0fbfef8
--- /dev/null
+++ b/db/sql/tables.sql
@@ -0,0 +1,39 @@
+-- user table for users.
+CREATE TABLE IF NOT EXISTS Users (
+    id INTEGER PRIMARY KEY,
+    password TEXT UNIQUE NOT NULL,
+    uuid TEXT UNIQUE NOT NULL,
+    name TEXT UNIQUE NOT NULL
+);
+
+-- statistics table for users to log their consumption
+CREATE TABLE IF NOT EXISTS Statistics (
+    id INTEGER 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 INTEGER PRIMARY KEY,
+    color TEXT NOT NULL DEFAULT "#000000",
+    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)
+);
+
+-- lookup table for sizes.
+CREATE TABLE IF NOT EXISTS Sizes (
+    id INTEGER PRIMARY KEY,
+    size INT NOT NULL,
+    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
+);
\ No newline at end of file
-- 
cgit v1.1

