Fix "ValueError: cannot convert float NaN to integer"

[coffee/coffee-flask.git] / coffee_db.sql

diff --git a/coffee_db.sql b/coffee_db.sql
index c7c88d1ce29e599f401213691e87457f01f4ac68..d9ff410c889e39e274510727db7582ce518ec8e0 100644 (file)
--- a/coffee_db.sql
+++ b/coffee_db.sql
@@ -7,16 +7,18 @@ create table if not exists users (
 
 create table if not exists flavors (
     name varchar(255) primary key not null,
-    ord integer not null default 999
+    ord integer not null default 999,
+    type varchar(24) not null default ""
 );
 
 insert or ignore into flavors values
-    ("espresso", 2),
-    ("espresso lungo", 3),
-    ("cappuccino", 1),
-    ("latte macchiato", 4),
-    ("Club-Mate 0,5 l", 5),
-    ("Club-Mate 0,33 l", 6)
+    ("espresso", 2, "coffee"),
+    ("espresso lungo", 3, "coffee"),
+    ("cappuccino", 1, "coffee"),
+    ("latte macchiato", 4, "coffee"),
+    ("Club-Mate 0,5 l", 5, "Club-Mate"),
+    ("Club-Mate 0,33 l", 6, "Club-Mate"),
+    ("tea", 7, "tea")
 ;
 
 create table if not exists coffees (
@@ -36,10 +38,32 @@ insert or ignore into days values
     (0),(1),(2),(3),(4),(5),(6)
 ;
 
+create table if not exists event_types (
+    id integer primary key,
+    name varchar(255) not null
+);
+
+insert or ignore into event_types values
+    (0, 'COFFEE_PACK_OPENED'),
+    (1, 'LAST_COFFEE_PACK_OPENED'), -- prepared for later use
+    (2, 'COFFEE_MACHINE_CLEANED'),
+    (3, 'MILK_CONTAINER_CLEANED'),
+    (4, 'MILK_CONTAINER_CLEANED_WITH_TABLET') -- cleaning with tablets implies cleaning of the container
+;
+
+create table if not exists events (
+    id integer primary key,
+    event_type integer not null,
+    user_id varchar(24) references users(id),
+    time datetime default current_timestamp not null,
+    foreign key(event_type) references event_types(id)
+);
+create index if not exists idx_events on events (event_type, time);
+
 CREATE TABLE if not exists identifiers (
     `userid` varchar ( 24 ) NOT NULL,
     `id` varchar ( 24 ) PRIMARY KEY NOT NULL,
     `name` varchar ( 24 ),
-    `status` INTEGER NOT NULL DEFAULT 1,
+    `active` INTEGER NOT NULL DEFAULT 1,
     FOREIGN KEY(`userid`) REFERENCES `users`(`id`)
 );