3 files changed, 82 insertions, 0 deletions
diff --git a/data/sql/tables.sql b/data/sql/tables.sql
new file mode 100644
index 0000000..1518ef2
--- /dev/null
+++ b/data/sql/tables.sql
@@ -0,0 +1,14 @@
+BEGIN TRANSACTION;
+CREATE TABLE IF NOT EXISTS products (id INTEGER PRIMARY KEY NOT NULL CHECK (id < 10000000000000 and (10 - (((id / 1000000000000 % 10) + (id / 100000000000 % 10) * 3 + (id / 10000000000 % 10) + (id / 1000000000 % 10) * 3 + (id / 100000000 % 10) + (id / 10000000 % 10) * 3 + (id / 1000000 % 10) + (id / 100000 % 10) * 3 + (id / 10000 % 10) + (id / 1000 % 10) * 3 + (id / 100 % 10) + (id / 10 % 10) * 3) % 10)) % 10 == (id / 1 % 10)), name TEXT, amount INTEGER NOT NULL DEFAULT 0, category INTEGER REFERENCES categories, deprecated BOOLEAN NOT NULL DEFAULT 0);
+CREATE TABLE IF NOT EXISTS sales (user INTEGER NOT NULL REFERENCES users, product INTEGER NOT NULL REFERENCES products, timestamp INTEGER NOT NULL DEFAULT 0);
+CREATE TABLE IF NOT EXISTS restock (user INTEGER NOT NULL REFERENCES users, product INTEGER NOT NULL REFERENCES products, amount INTEGER NOT NULL DEFAULT 0, timestamp INTEGER NOT NULL DEFAULT 0, price INTEGER NOT NULL DEFAULT 0, supplier INTEGER, best_before_date INTEGER);
+CREATE TABLE IF NOT EXISTS prices (product INTEGER NOT NULL REFERENCES products, valid_from INTEGER NOT NULL DEFAULT 0, memberprice INTEGER NOT NULL DEFAULT 0, guestprice INTEGER NOT NULL DEFAULT 0);
+CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY NOT NULL, email TEXT, firstname TEXT NOT NULL, lastname TEXT NOT NULL, gender TEXT, street TEXT, plz INTEGER, city TEXT, pgp TEXT, disabled BOOLEAN NOT NULL DEFAULT 0, hidden BOOLEAN NOT NULL DEFAULT 0, sound_theme TEXT, joined_at INTEGER NOT NULL DEFAULT 0);
+CREATE TABLE IF NOT EXISTS authentication(user INTEGER PRIMARY KEY NOT NULL REFERENCES users, password TEXT, session CHARACTER(20), superuser BOOLEAN NOT NULL DEFAULT 0, auth_users BOOLEAN NOT NULL DEFAULT 0, auth_products BOOLEAN NOT NULL DEFAULT 0, auth_cashbox BOOLEAN NOT NULL DEFAULT 0, disabled BOOLEAN NOT NULL DEFAULT 0);
+CREATE TABLE IF NOT EXISTS supplier(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, city TEXT, postal_code TEXT, street TEXT, phone TEXT, website TEXT);
+CREATE TABLE IF NOT EXISTS cashbox_diff(id INTEGER PRIMARY KEY AUTOINCREMENT, user INTEGER NOT NULL REFERENCES users, amount INTEGER NOT NULL, timestamp INTEGER NOT NULL DEFAULT 0);
+CREATE TABLE IF NOT EXISTS ean_aliases (id INTEGER PRIMARY KEY NOT NULL, real_ean INTEGER NOT NULL REFERENCES products);
+CREATE TABLE IF NOT EXISTS categories (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT);
+CREATE TABLE IF NOT EXISTS rfid_users (rfid TEXT, user INTEGER NOT NULL REFERENCES users);
+CREATE INDEX IF NOT EXISTS invoiceindex ON sales (user ASC, timestamp DESC);
+COMMIT;
diff --git a/data/sql/trigger.sql b/data/sql/trigger.sql
new file mode 100644
index 0000000..d26a39d
--- /dev/null
+++ b/data/sql/trigger.sql
@@ -0,0 +1,27 @@
+BEGIN TRANSACTION;
+CREATE TRIGGER IF NOT EXISTS update_product_amount_on_restock_insert AFTER INSERT ON restock BEGIN
+	UPDATE products SET amount = products.amount + NEW.amount WHERE products.id = NEW.product;
+END;
+
+CREATE TRIGGER IF NOT EXISTS update_product_amount_on_restock_delete AFTER DELETE ON restock BEGIN
+	UPDATE products SET amount = products.amount - OLD.amount WHERE products.id = OLD.product;
+END;
+
+CREATE TRIGGER IF NOT EXISTS update_product_amount_on_restock_update AFTER UPDATE ON restock BEGIN
+	UPDATE products SET amount = products.amount - OLD.amount WHERE products.id = OLD.product;
+	UPDATE products SET amount = products.amount + NEW.amount WHERE products.id = NEW.product;
+END;
+
+CREATE TRIGGER IF NOT EXISTS update_product_amount_on_sales_insert AFTER INSERT ON sales BEGIN
+	UPDATE products SET amount = products.amount - 1 WHERE products.id = NEW.product;
+END;
+
+CREATE TRIGGER IF NOT EXISTS update_product_amount_on_sales_delete AFTER DELETE ON sales BEGIN
+	UPDATE products SET amount = products.amount + 1 WHERE products.id = OLD.product;
+END;
+
+CREATE TRIGGER IF NOT EXISTS update_product_amount_on_sales_update AFTER UPDATE ON sales BEGIN
+	UPDATE products SET amount = products.amount + 1 WHERE products.id = OLD.product;
+	UPDATE products SET amount = products.amount - 1 WHERE products.id = NEW.product;
+END;
+COMMIT;
diff --git a/data/sql/views.sql b/data/sql/views.sql
new file mode 100644
index 0000000..94cbabd
--- /dev/null
+++ b/data/sql/views.sql
@@ -0,0 +1,41 @@
+BEGIN TRANSACTION;
+CREATE VIEW IF NOT EXISTS stock AS SELECT id, name, category, amount FROM products WHERE deprecated = 0 OR amount != 0;
+CREATE VIEW IF NOT EXISTS purchaseprices AS SELECT product, SUM(price * amount) / SUM(amount) AS price FROM restock GROUP BY product;
+CREATE VIEW IF NOT EXISTS invoice AS 
+	SELECT user, timestamp, id AS productid, name AS productname,
+		CASE
+			WHEN user < 0 THEN
+				(SELECT SUM(price * amount) / SUM(amount)
+					FROM restock
+					WHERE restock.product = id AND restock.timestamp <= sales.timestamp
+				)
+			else
+				(SELECT
+					CASE
+						WHEN user=0 THEN guestprice
+						else memberprice
+					END
+					FROM prices
+					WHERE product = id AND valid_from <= timestamp
+					ORDER BY valid_from DESC LIMIT 1)
+			END AS price
+		FROM sales INNER JOIN products ON sales.product = products.id
+		ORDER BY timestamp;
+CREATE VIEW IF NOT EXISTS current_cashbox_status AS
+	SELECT (
+		(
+			SELECT SUM(
+				(
+					SELECT guestprice
+						FROM prices
+						WHERE product = s.product AND valid_from <= s.timestamp
+						ORDER BY valid_from DESC LIMIT 1
+				)
+			) FROM sales s WHERE user = 0
+		)
+		+
+		(
+			SELECT SUM(amount) FROM cashbox_diff
+		)
+	) AS amount;
+COMMIT;