create table login(
    idlogin serial primary key,
    email varchar(128) not null,
    "password" varchar(32) null,
    isactive boolean not null,
    iduser int references "user" (iduser),
    constraint "required_email_length" check(length(email) > 8),
    constraint "required_pass_length" check(length(email) > 4)
    );

create table "user"(
    iduser serial primary key,
    firstname varchar(32) not null,
    lastname varchar(32) not null,
    salary money not null,
    typeofemployee varchar(6) not null,
    idcurrency int not null references currency (idcurrency),
    constraint "required_employee_length" check(3 <= length(typeofemployee) and length(typeofemployee) <= 6),
    constraint "required_firstName_length" check(length(firstname) > 0),
    constraint "required_lastName_length" check(length(lastname) > 0),
    constraint "required_lastName_length" check(salary > 0)
    );
create table userproject(
    iduserproject serial primary key,
    iduser int references "user" (iduser),
    idproject int references project (idproject)
    );
create table project(
    idproject serial primary key,
    projectname varchar(64) not null,
    budget money default 0.00,
    typeofproject varchar(3) not null, -- jak dlouhy maji zkratky
    isactive boolean not null, -- ujasnit si jak bude zaviset na datumu u startu projektu
    startdate date not null,
    enddate date not null,
    idcurrency int not null references currency (idcurrency),
    constraint "required_projectname_length" check(length(projectname) > 0),
    constraint "required_typeofproject_length" check(length(typeofproject) > 0),
    constraint "required_startDate" check(CURRENT_DATE <= startdate )
    );
create table timeonproject(
    idtime serial primary key,
    workdate date not null,
    worktimebegin time not null,
    worktimeend time null,
    worktime int null,
    iduser int references "user" (iduser),
    idproject int references project (idproject),
    constraint "required_worktime" check(worktime > 12 )
    );
create table invoice(
    idinvoice serial primary key,
    figure money null, -- muze byt castku zaporna nebo prazda ?
    typeofinvoice varchar(10) not null,
    dateofdue date not null, -- muze byt datum splatnosti v minulosti?
    dateofissue date not null,
    urlinvoice text null,
    idcurrency int not null references currency (idcurrency),
    iduser int null references "user" (iduser),
    idproject int references project (idproject),
    idcompany int references company (idcompany)
    );
create table "transaction"(
    idtransaction serial primary key,
    item -- musi byt dalsi tabulka
    "type" varchar(10) not null,
    figure money null, -- opravit podle invoice
    url text null
    "date" date not null,
    idcurrency int not null references currency (idcurrency),
    idinvoice int references invoice (idinvoice),
    )
create table currency(
    idcurrency serial primary key,
    currency varchar(3) not null,
    constraint "required_currency_length" check(length(currency) = 3)
    );
create table company(
    idcompany serial primary key,
    companyname varchar(128) not null
    );