create table notification
(
    notification_id serial
        constraint notification_pk
            primary key,
    title           varchar(40)  not null,
    body            varchar(256) not null
);

alter table notification
    owner to eczxeutlixjpus;

create unique index notification_notification_id_uindex
    on notification (notification_id);

create table role
(
    role_id serial
        constraint role_pk
            primary key,
    name    varchar(32) not null
);

alter table role
    owner to eczxeutlixjpus;

create unique index role_role_id_uindex
    on role (role_id);

create table "user"
(
    user_id         integer default nextval('user_id_user_seq'::regclass) not null
        constraint user_pk
            primary key,
    email           varchar(100)                                          not null,
    password_hash   varchar(100),
    name            varchar(100)                                          not null,
    user_motivation varchar(512),
    age             integer                                               not null,
    image           varchar(255)
);

alter table "user"
    owner to eczxeutlixjpus;

create table trainer
(
    trainer_id integer default nextval('trainer_trainer_id_seq1'::regclass) not null
        constraint trainer_pk
            primary key,
    id_user    integer                                                      not null
        constraint trainer_user_user_id_fk
            references "user"
            on update cascade on delete cascade
);

alter table trainer
    owner to eczxeutlixjpus;

create table pupil
(
    pupil_id   integer default nextval('pupil_id_pupil_seq'::regclass) not null
        constraint pupil_pk
            primary key,
    id_user    integer                                                 not null
        constraint pupil_user_user_id_fk
            references "user"
            on update cascade on delete cascade,
    id_trainer integer                                                 not null
        constraint pupil_trainer_trainer_id_fk
            references trainer
);

alter table pupil
    owner to eczxeutlixjpus;

create unique index pupil_id_pupil_uindex
    on pupil (pupil_id);

create unique index trainer_trainer_id_uindex
    on trainer (trainer_id);

create table training
(
    training_id integer default nextval('training_id_training_seq'::regclass) not null
        constraint training_pk
            primary key,
    date        date,
    name        varchar(100),
    id_pupil    integer                                                       not null
        constraint training_pupil_pupil_id_fk
            references pupil
);

alter table training
    owner to eczxeutlixjpus;

create unique index training_id_training_uindex
    on training (training_id);

create table training_details
(
    training_details_id integer default nextval('training_details_id_training_details_seq'::regclass) not null
        constraint training_details_pk
            primary key,
    series              integer                                                                       not null,
    repeats             integer                                                                       not null,
    id_training         integer
        constraint training_details_training_training_id_fk
            references training
);

alter table training_details
    owner to eczxeutlixjpus;

create table exercise
(
    exercise_id         integer default nextval('exercise_id_exercise_seq'::regclass) not null
        constraint exercise_pk
            primary key,
    exercise_name       varchar(100)                                                  not null,
    description         text                                                          not null,
    id_training_details integer
        constraint exercise_training_details_training_details_id_fk
            references training_details
            on update cascade on delete cascade
);

alter table exercise
    owner to eczxeutlixjpus;

create unique index exercise_id_exercise_uindex
    on exercise (exercise_id);

create table result
(
    id_result   serial
        constraint result_pk
            primary key,
    weight      double precision,
    id_exercise integer not null
        constraint result_exercise_exercise_id_fk
            references exercise
);

alter table result
    owner to eczxeutlixjpus;

create unique index result_id_result_uindex
    on result (id_result);

create unique index training_details_id_training_details_uindex
    on training_details (training_details_id);

create table training_diary
(
    training_diary_id integer default nextval('"training_diary_id_training+diary_seq"'::regclass) not null
        constraint training_diary_pk
            primary key,
    id_training       integer                                                                     not null
        constraint training_diary_training_training_id_fk
            references training
            on update cascade on delete cascade
);

alter table training_diary
    owner to eczxeutlixjpus;

create unique index "training_diary_id_training+diary_uindex"
    on training_diary (training_diary_id);

create unique index user_id_user_uindex
    on "user" (user_id);

create table user_notification
(
    user_notification_id serial
        constraint user_notification_pk
            primary key,
    id_user              integer not null
        constraint user_notification_user_user_id_fk
            references "user"
            on update cascade on delete cascade,
    id_notification      integer
        constraint user_notification_notification_notification_id_fk
            references notification
            on update cascade on delete cascade
);

alter table user_notification
    owner to eczxeutlixjpus;

create unique index user_notification_user_notification_id_uindex
    on user_notification (user_notification_id);

create table user_role
(
    user_role_id serial
        constraint user_role_pk
            primary key,
    id_role      integer not null
        constraint user_role_role_role_id_fk
            references role
            on update cascade on delete cascade,
    id_user      integer not null
        constraint user_role_user_user_id_fk
            references "user"
            on update cascade on delete cascade
);

alter table user_role
    owner to eczxeutlixjpus;

create unique index user_role_user_role_id_uindex
    on user_role (user_role_id);