create table service
(
    serviceid        serial
        primary key,
    name             text    not null,
    price            money   not null,
    startdate        date    not null,
    finishdate       date    not null,
    averagevariation integer not null
);

create table orderstatus
(
    orderstatusid serial
        primary key,
    orderstatus   text not null
);

create table servicestatus
(
    servicestatusid serial
        primary key,
    servicestatus   text not null
);

create table orders
(
    orderid                serial
        primary key,
    creatingdate           date    not null,
    serviceidfk            integer
        references service,
    servicestatudidfk      integer
        references servicestatus,
    orderstatusidfk        integer
        references orderstatus,
    hourstimeforafinishing integer not null,
    orderprice             money   not null
);

create table passport
(
    passportid     serial
        primary key,
    passportseries varchar(4) not null,
    passportnumber varchar(6) not null
);

create table clients
(
    clientid        serial
        primary key,
    email           text        not null,
    password        text        not null,
    name            text        not null,
    surname         text        not null,
    lastname        text        not null,
    birthday        date        not null,
    telephonenumber varchar(11) not null,
    passportidfk    integer
        references passport
);

create table address
(
    addressid serial
        primary key,
    city      text not null,
    street    text not null,
    house     text not null,
    flat      text not null
);

create table director
(
    directorid serial
        primary key,
    name       text not null,
    surname    text not null,
    lastname   text not null
);

create table contactface
(
    contactfaceid   serial
        primary key,
    name            text        not null,
    surname         text        not null,
    lastname        text        not null,
    telephonenumber varchar(11) not null
);

create table company
(
    companyid   serial
        primary key,
    name        text        not null,
    addressidfk integer
        references address,
    inn         varchar(10) not null,
    bik         varchar(9)  not null,
    email       text        not null,
    password    text        not null
);

create table role
(
    roleid   serial
        primary key,
    rolename text not null
);

create table users
(
    userid        serial
        primary key,
    login         text not null,
    password      text not null,
    name          text not null,
    surname       text not null,
    lastname      text not null,
    datelastlogin date not null,
    serviceidfk   integer
        references service,
    roleidfk      integer
        references role
);

create table providedservice
(
    providedserviceid serial
        primary key,
    serviceidfk       integer
        references service,
    provideddate      date not null,
    useridfk          integer
        references users
);

create table anotheruser
(
    anotheruserid serial
        primary key,
    login         text not null,
    password      text not null,
    name          text not null,
    surname       text not null,
    lastname      text not null,
    datelastlogin date not null,
    roleidfk      integer
        references role
);