pragma foreign_keys = ON;

create table if not exists users (
    id varchar(24) primary key not null,
    name varchar(255) default "human"
);

create table if not exists flavors (
    name varchar(255) primary key not null,
    ord integer not null default 999,
    type varchar(24) not null default ""
);

insert or ignore into flavors values
    ("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 (
    num integer primary key,
    id varchar(24) references users(id), -- id may be unknown
    flavor varchar(255) not null references flavors(name),
    time datetime default current_timestamp,
    UNIQUE (id, flavor, time)
);


create table if not exists days (
    num integer primary key not null
);

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 ),
    `active` INTEGER NOT NULL DEFAULT 1,
    FOREIGN KEY(`userid`) REFERENCES `users`(`id`)
);
