CREATE TABLE public.users
(
    user_id SERIAL PRIMARY KEY,
    first_name varchar(40) NOT NULL,
    second_name varchar(40) NOT NULL,
    passwordhash varchar(64) NOT NULL,
    email varchar(100) NOT NULL,
    phonenumber varchar(20),
    created_on timestamp without time zone DEFAULT timezone('utc'::text, now()),
    CONSTRAINT users_email_key UNIQUE (email),
    CONSTRAINT proper_email CHECK (email::text ~* '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+[.][A-Za-z]+$'::text)
);

CREATE TABLE user_role
(
    role_id SERIAL PRIMARY KEY,
    role_name varchar(40) NOT NULL
);

INSERT INTO user_role (role_name) VALUES ('default');


CREATE TABLE user_to_role (
    user_id int REFERENCES users (user_id),
    role_id int REFERENCES user_role (role_id) DEFAULT 1,
    is_ban boolean DEFAULT FALSE,
    PRIMARY KEY(user_id, role_id)
);



CREATE OR REPLACE FUNCTION function_insert_user_to_role () RETURNS trigger AS
$BODY$
BEGIN
INSERT INTO user_to_role(user_id) VALUES ((select max(user_id) from users));
RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;

CREATE  TRIGGER triger_insert_user_to_role
    AFTER INSERT
    ON users
   EXECUTE PROCEDURE function_insert_user_to_role();



INSERT INTO users (first_name, second_name, email, passwordhash, phonenumber) VALUES ('denis', 'maleev', 'lopu@ukr.net', 'test', "80930977193")