DROP TABLE IF EXISTS user_roles;

DROP TABLE IF EXISTS meals;

DROP TABLE IF EXISTS users;

DROP SEQUENCE IF EXISTS global_seq;

CREATE SEQUENCE global_seq START 100000;

CREATE TABLE users

(

id INTEGER PRIMARY KEY DEFAULT nextval('global_seq'),

name VARCHAR,

email VARCHAR NOT NULL,

password VARCHAR NOT NULL,

registered TIMESTAMP NOT NULL DEFAULT now(),

enabled BOOL DEFAULT TRUE,

calories_per_day INTEGER NOT NULL DEFAULT 2000

);

CREATE UNIQUE INDEX unique_email ON USERS (email);

CREATE TABLE user_roles

(

user_id INTEGER NOT NULL,

role VARCHAR,

CONSTRAINT user_roles_idx UNIQUE (user_id, role),

FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE

);

CREATE TABLE meals (

id INTEGER PRIMARY KEY DEFAULT nextval('global_seq'),

user_id INTEGER NOT NULL,

datetime TIMESTAMP,

description TEXT,

calories INT,

FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE

);