create schema profile;

create table profile.t_login (
    lastchange timestamp without time zone default now(),
    pk integer not null primary key generated always as identity,
    login text not null unique,
    forename text,
    surname text,
    duedate timestamp without time zone not null,
    password text not null
);

create table profile.t_profile (
    lastchange timestamp without time zone default now(),
    pk_profile integer not null primary key generated always as identity,
    username text not null unique,
    theme text default 'light'::text not null,
    column dynamic_css text
);

create table profile.t_role (
    lastchange timestamp without time zone default now(),
    pk integer not null primary key generated always as identity,
    name text not null unique
);

create table profile.t_loginrole (
    lastchange timestamp without time zone default now(),
    pk integer not null primary key generated always as identity,
    fk_login integer not null references profile.t_login(pk),
    fk_role integer not null references profile.t_role(pk),
    unique (fk_login, fk_role)
);

create view profile.v_loginrole as
select l.login, l.forename, l.surname,  l.duedate, r.name as role_name
from profile.t_loginrole
left join profile.t_login l on l.pk = t_loginrole.fk_login
left join profile.t_role r on r.pk = t_loginrole.fk_role;

copy profile.t_role (lastchange, pk, name) from stdin;
2019-01-08 10:16:19.959185	1	read_done
2019-01-08 10:16:19.959185	2	write_done
2019-01-08 10:16:19.959185	3	write_job
2019-01-08 10:16:19.959185	5	write_project
2019-01-08 10:16:19.959185	4	write_module
2019-09-24 16:00:49.811943	6	write_note
2019-09-24 16:00:49.811943	7	write_contact
\.

grant usage on schema profile to timetrack;

grant select,insert,delete,update on table profile.t_login to timetrack;
grant select,insert,delete,update on table profile.t_loginrole to timetrack;
grant select,insert,update on table profile.t_profile to timetrack;
grant select,insert,delete,update on table profile.t_role to timetrack;
grant select,insert,delete,update on table profile.v_loginrole to timetrack;