----------------------------------------------------------------------
-- DOSIJE
----------------------------------------------------------------------

create table dosije (
       indeks           integer      not null,  
       id_smera         integer      not null,
       ime              varchar(25)  not null,
       prezime          varchar(25)  not null,
       pol              char         not null,
       jmbg             char(13)     not null,
       datum_rodjenja     date                 ,
       mesto_rodjenja   varchar(100)         ,
       drzava_rodjenja  varchar(100)         ,
       ime_oca          varchar(50)          ,
       ime_majke        varchar(50)          ,
       ulica_stanovanja        varchar(100)         ,
       kucni_broj         varchar(20)          ,
       mesto_stanovanja         varchar(100)         ,
       postanski_broj     varchar(20)          ,
       drzava_stanovanja       varchar(100)         ,
       telefon      varchar(25)          ,
       mobilni_telefon   varchar(25)          ,
       email        varchar(50)          ,
       "www uri"       varchar(100)         ,
       datum_upisa        date not null        ,
       primary key      (indeks)             ,
       foreign key      fk_dosije_smer(id_smera)
                        references smer
      );

create index dos_smer on dosije(id_smera,indeks );

  
----------------------------------------------------------------------
-- UPIS_GODINE
----------------------------------------------------------------------

create table upis_godine (
       indeks           integer      not null,
       godina           smallint     not null
             constraint chk_godina check
               (godina between 1900 and 2025),
       datum_upisa        date         not null,
       upisano_bodova   smallint     not null
             constraint chk_upisbod check
           (upisano_bodova between 0 and 125),
       datum_overe        date                 ,
       overeno_bodova  smallint             ,
             constraint chk_overa check(
                 (
                  overeno_bodova is null
                  and datum_overe  is null
                 )
                 or
                 (
                  overeno_bodova is not null
                  and datum_overe  is not null
                  and datum_overe >= datum_upisa
                  and overeno_bodova between 0 and upisano_bodova
                 )
                                       )     ,
       primary key      (indeks,godina)      ,
       foreign key      fk_upis_dosije(indeks)
                        references dosije
                         );


----------------------------------------------------------------------
-- STATUS
----------------------------------------------------------------------

create table status (
    indeks integer not null,
    datum date not null,
    status varchar(20)  not null
        constraint chk_status check( 
        status in (
                'budzet', 
                'samofinansiranje',
                'diplomirao', 
                'mirovanje', 
                'ispisan'  
                )     
       ) ,
    primary key      (indeks, datum, status)             ,
    foreign key      fk_dosije(indeks)
        references dosije
);
      
create index indeks_status on status(indeks, status );