-----------------------------------------------------------------
-- ISPITNI_ROK
-----------------------------------------------------------------

create table ispitni_rok (
       godina           smallint     not null,
       oznaka           varchar(20)  not null,
       naziv            varchar(50)  not null,
       pocetak_prijavljivanja     date         not null,
       kraj_prijavljivanja        date         not null,
       tip              char         not null with default 'B'
             constraint chk_tiproka check( 
                    tip in ('1','2','B','X')),
               -- '1' - samo predmeti iz 1. semestra
               -- '2' - samo predmeti iz 2. semestra
               -- 'B' - predmeti iz oba semestra
               -- 'X' - samo po jedan predmet, ako je poslednji nepolozen
       primary          key(godina,oznaka)
                         );

create index isprok_datum on ispitni_rok( pocetak_prijavljivanja);

  
-----------------------------------------------------------------
-- ISPIT
-----------------------------------------------------------------

create table ispit (
       indeks           integer      not null,
       id_predmeta      integer      not null,
       godina           integer      not null,
       semestar         integer      not null,
       godina_roka      smallint     not null,
       oznaka_roka      char(5)      not null,
       datum_prijave     date         not null,
       nacin_prijave     varchar(16)  not null,
       broj_polaganja          integer      not null ,
       status_prijave   char         not null with default 'p',
             constraint chk_status check( 
             status_prijave in ( 'p', 'n', 'o', 'd', 'x' )),
              -- status prijave moze biti
              --              p - prijavljen
              --              n - nije izasao
              --              o - polagao
              --              d - diskvalifikovan
              --              x - ponisten
       datum_pismenog   date                 ,
       bodovi_pismenog  smallint             ,
       datum_usmenog    date                 ,
       bodovi_usmenog   smallint             ,
       bodovi           smallint             ,
       ocena            smallint             ,
             constraint chk_ocena check(
                        (status_prijave in ('p','n') 
                        and bodovi is null 
                        and ocena is null) 
                     or 
                        (status_prijave in ('d') 
                         and bodovi=0
                         and ocena=5) 
                     or 
                        (status_prijave in ('o','x') 
                         and bodovi between 0 and 100 
                         and ocena between 5 and 10)
                                       )     ,

             constraint chk_bodovi check(
                        bodovi is null
                     or (
                         bodovi_pismenog    is not null
                         and datum_pismenog is not null
                         and bodovi_usmenog is null
                         and datum_usmenog  is null
                         and bodovi = bodovi_pismenog
                        )
                     or (
                         bodovi_pismenog    is null
                         and datum_pismenog is null
                         and bodovi_usmenog is not null
                         and datum_usmenog  is not null
                         and bodovi = bodovi_usmenog
                        )
                     or (
                         bodovi_pismenog    is not null
                         and datum_pismenog is not null
                         and bodovi_usmenog is not null
                         and datum_usmenog  is not null
                         and bodovi = bodovi_pismenog + bodovi_usmenog
                        )
                                        )    ,
       nastavnik        varchar(100)         ,
       napomena         varchar(1000)        ,
       primary          key(indeks,id_predmeta,godina_roka,oznaka_roka),
       foreign          key fk_ispit_isprok(godina_roka,oznaka_roka)
             references ispitni_rok          ,
       foreign          key fk_ispit_upiskurs(indeks,id_predmeta,godina,semestar)
             references upisan_kurs
                   );

create index ispit_rokdosije on ispit
       (godina_roka,oznaka_roka,indeks,id_predmeta,godina,semestar);

create index ispit_rokkurs on ispit
       (godina_roka,oznaka_roka,id_predmeta,godina,semestar,indeks);
       

create function broj_polaganja(indeks_studenta integer, identifikator_predmeta integer)
returns integer
return (select count +1
           from ispit
           where indeks=indeks_studenta and id_predmeta= identifikator_predmeta);
           
 
create trigger broj_polaganja
before insert on ispit
referencing new as novi_ispit
for each row
set novi_ispit.broj_polaganja=broj_polaganja(novi_ispit.indeks, novi_ispit.id_predmeta);

