DROP TABLE user_role IF EXISTS;

DROP TABLE meal IF EXISTS;

DROP TABLE users IF EXISTS;

DROP SEQUENCE global_seq IF EXISTS;

CREATE SEQUENCE GLOBAL_SEQ AS INTEGER START WITH 100000;

CREATE TABLE users

(

id INTEGER GENERATED BY DEFAULT AS SEQUENCE GLOBAL_SEQ PRIMARY KEY,

name VARCHAR(255) NOT NULL,

email VARCHAR(255) NOT NULL,

password VARCHAR(255) NOT NULL,

registered TIMESTAMP DEFAULT now() NOT NULL,

enabled BOOLEAN DEFAULT TRUE NOT NULL,

calories_per_day INTEGER DEFAULT 2000 NOT NULL

);

CREATE UNIQUE INDEX users_unique_email_idx

ON USERS (email);

CREATE TABLE user_role

(

user_id INTEGER NOT NULL,

role VARCHAR(255) NOT NULL,

CONSTRAINT user_roles_idx UNIQUE (user_id, role),

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

);

CREATE TABLE meal

(

id INTEGER GENERATED BY DEFAULT AS SEQUENCE GLOBAL_SEQ PRIMARY KEY,

date_time TIMESTAMP NOT NULL,

description VARCHAR(255) NOT NULL,

calories INT NOT NULL,

user_id INTEGER NOT NULL,

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

);

CREATE UNIQUE INDEX meal_unique_user_datetime_idx

ON meal (user_id, date_time)