create database ElearningDB

alter database ElearningDB
set compatibility_level = 120

use ElearningDB

create table korisnik(
     korisnik_ID int identity(1, 1) primary key,
	 ime nvarchar(50),
	 prezime nvarchar(50),
	 email nvarchar(100) unique,
	 lozinka nvarchar(100),
	 tip_korisnika nvarchar(20),
	 datum_registracije date
)

create table kurs(
    kurs_ID int identity(1, 1) primary key,
	naziv nvarchar(50),
	opis nvarchar(max),
	instruktor_ID int,
	datum_kreiranja date,
	foreign key (instruktor_ID) references korisnik(korisnik_ID)
)

create table upis(
    upis_ID int identity(1, 1) primary key,
	korisnik_ID int,
	kurs_ID int,
	datum_upisa date,
	foreign key (korisnik_ID) references korisnik(korisnik_ID),
	foreign key (kurs_ID) references kurs(kurs_ID)
)

create table lekcija(
    lekcija_ID int identity(1, 1) primary key,
	kurs_ID int,
	naziv nvarchar(100),
	sadrzaj nvarchar(max),
	redni_broj int,
	foreign key (kurs_ID) references kurs(kurs_ID)
)

create table test(
    test_ID int identity(1, 1) primary key,
	kurs_ID int,
    naziv nvarchar(100),
	datum_kreiranja date,
	foreign key (kurs_ID) references kurs(kurs_ID)
)

create table pitanje(
    pitanje_ID int identity(1, 1) primary key,
	test_ID int,
	tekst nvarchar(max),
    foreign key (test_ID) references test(test_ID)
)

create table odgovor (
    odgovor_ID int identity(1, 1) primary key,
	pitanje_ID int,
	tekst nvarchar(max),
	tacan bit,
	foreign key (pitanje_ID) references pitanje(pitanje_ID)
)

create table rezultat(
    rezultat_ID int identity(1, 1) primary key,
	korisnik_ID int,
	test_ID int,
	bodovi decimal(10, 2),
	datum_polaganja date,
	foreign key (korisnik_ID) references korisnik(korisnik_ID),
	foreign key (test_ID) references test(test_ID)
)

create table certifikat(
    certifikat_ID int identity(1, 1) primary key,
	korisnik_ID int,
	kurs_ID int,
	datum_izdavanja date,
	foreign key (korisnik_ID) references korisnik(korisnik_ID),
	foreign key (kurs_ID) references kurs(kurs_ID)
)

insert into korisnik
values('Lazar', 'Malbasic', 'lazarmalbasic@gmail.com', '123', 'student', '2026-03-03'),
      ('Dragan', 'Jovic', 'draganjovic@gmail.com', 'dragan77', 'instruktor', '2026-02-24'),
	  ('Marko', 'Markovic', 'markomarkovic@gmail.com', '433', 'student', '2025-12-05')

insert into kurs
values('Java programiranje', 'Osnove jave', 2, '2026-04-02'),
      ('Python programiranje', 'Osnove python-a', 2, '2026-04-04')

insert into upis
values(1, 1, '2026-03-30'),
      (3, 2, '2026-03-31'),
	  (1, 2, '2026-04-01')

insert into lekcija
values(1, 'Uvod u Javu', 'Osnove Java jezika', 1),
      (1, 'Petlje u Javi', 'Kako petlje rade', 2),
	  (2, 'Uvod u Python', 'Osnove python jezika', 1)

insert into test
values (1, 'Java Test 1', '2026-04-09'),
       (2, 'Python Test 1', '2026-04-11')

insert into pitanje
values(1, 'Sta radi FOR petlja?'),
      (2, 'Ko je napravio Python jezik?')

insert into odgovor
values(1, 'Broji kada znamo unaprijed koliko ce se puta izvrsiti program', 1),
      (2, 'James Gosling', 0),
	  (2, 'Guido Vam Rossum', 1),
	  (1, 'For petlja provjerava da li je neki uslov tacan i onda izvrsi kod samo jednom ako jeste', 0)

insert into rezultat
values(1, 1, '95.00', '2026-04-09'),
      (3, 2, '83.50', '2026-04-11')

insert into certifikat
values(1, 1, '2026-05-07'),
      (3, 2, '2026-05-14')

--select * from korisnik;
--select * from certifikat;
--select * from kurs;
--select * from lekcija;
--select * from odgovor;
--select * from pitanje;
--select * from rezultat;
--select * from test;
--select * from upis;

--select k.ime, k.prezime, ku.naziv as kurs
--from korisnik k
--inner join upis u on k.korisnik_ID = u.korisnik_ID
--inner join kurs ku on u.kurs_ID = ku.kurs_ID

--select k.ime, k.prezime, ku.naziv as kurs
--from korisnik k
--left join upis u on k.korisnik_ID = u.korisnik_ID
--left join kurs ku on u.kurs_ID = ku.kurs_ID

--select k.ime, ku.naziv
--from korisnik k
--right join upis u on k.korisnik_ID = u.korisnik_ID
--right join kurs ku on u.korisnik_ID = ku.kurs_ID

--select k.ime, ku.naziv
--from korisnik k
--full join upis u on k.korisnik_ID = u.korisnik_ID
--full join kurs ku on u.kurs_ID = ku.kurs_ID

--select
     --s.ime as student,
	 --s.prezime,
	 --ku.naziv as kurs,
	 --i.ime as instruktor
--from upis u
--join korisnik s on u.korisnik_ID = s.korisnik_ID
--join kurs ku on u.kurs_ID = ku.kurs_ID
--join korisnik i on ku.instruktor_ID = i.korisnik_ID

--select k.ime, k.prezime, t.naziv as test, r.bodovi
--from rezultat r
--join korisnik k on r.korisnik_ID = k.korisnik_ID
--join test t on r.test_ID = t.test_ID

--select t.naziv, avg(r.bodovi) as prosjek
--from rezultat r
--join test t on r.test_ID = t.test_ID
--group by t.naziv

--select ku.naziv, count(u.korisnik_ID) brojstudenata
--from kurs ku
--left join upis u on ku.kurs_ID = u.kurs_ID
--group by ku.naziv

--select k.ime, r.bodovi
--from rezultat r
--join korisnik k on r.korisnik_ID = k.korisnik_ID
--where r.bodovi > 90

--create view vw_studentkurs as
--select k.ime, k.prezime, ku.naziv as kurs, u.datum_upisa
--from korisnik k
--join upis u on k.korisnik_ID = u.korisnik_ID
--join kurs ku on u.kurs_ID = ku.kurs_ID

--select * from vw_studentkurs

--create view vw_rezultat as 
--select k.ime, k.prezime, t.naziv as test, r.bodovi, r.datum_polaganja
--from rezultat r
--join korisnik k on r.korisnik_ID = k.korisnik_ID
--join test t on r.test_ID = t.test_ID

--select * from vw_rezultat

--create view vw_brojstudenata as
--select ku.naziv, count(u.korisnik_ID) as brojstudenata
--from kurs ku
--left join upis u on ku.kurs_ID = u.kurs_ID
--group by ku.naziv

--select * from vw_brojstudenata

--create procedure sp_studentipokursu
--@kurs_ID int
--as
--begin
--select k.ime, k.prezime, ku.naziv as kurs
--from upis u
--join korisnik k on u.korisnik_ID = k.korisnik_ID
--join kurs ku on u.kurs_ID = ku.kurs_ID
--where ku.kurs_ID = @kurs_ID
--end

--exec sp_studentipokursu @kurs_ID = 1

--create procedure sp_dodajkorisnika
--@ime nvarchar(50),
--@prezime nvarchar(50),
--@email nvarchar(100),
--@lozinka nvarchar(100),
--@tip_korisnika nvarchar(20)
--as
--begin
--insert into korisnik(ime, prezime, email, lozinka, tip_korisnika)
--values(@ime, @prezime, @email, @lozinka, @tip_korisnika)
--end

--exec sp_dodajkorisnika
--'Luka', 'Mijatovic', 'luka@gmail.com', '442', 'student'

--create procedure sp_prosjektesta
--@test_ID int
--as
--begin
--select t.naziv, avg(r.bodovi) as prosjek
--from rezultat r
--join test t on r.test_ID = t.test_ID
--where t.test_ID = @test_ID
--group by t.naziv
--end

--exec sp_prosjektesta 1

--create trigger trg_dodajcertifikat
--on rezultat
--after insert
--as
--begin
--insert into certifikat(korisnik_ID, kurs_ID, datum_izdavanja)
--select i.korisnik_ID, t.kurs_ID, getdate()
--from inserted i
--join test t on i.test_ID = t.test_ID
--where i.bodovi >= 80
--end

--create table rezultatlog(
    --log_ID int identity(1, 1) primary key,
	--rezultat_ID int,
	--stari_bodovi decimal(5, 2),
	--novi_bodovi decimal(5, 2),
	--datum_promjene datetime
--)

--create trigger trg_updaterezultat
--on rezultat
--after update
--as
--begin
--insert into rezultatlog(rezultat_ID, stari_bodovi, novi_bodovi, datum_promjene)
--select d.rezultat_ID, d.bodovi, i.bodovi, getdate() 
--from deleted d
--join inserted i on d.rezultat_ID = i.rezultat_ID
--end

--insert into rezultat(korisnik_ID, test_ID, bodovi, datum_polaganja)
--values(1, 1, 90, getdate())

--update rezultat
--set bodovi = 95
--where rezultat_ID = 1

--create function fn_prosjekstudenta(@korisnik_ID int)
--returns decimal(5, 2)
--as
--begin
--declare @prosjek decimal(5, 2)
--select @prosjek = avg(bodovi)
--from rezultat 
--where korisnik_ID = @korisnik_ID
--return @prosjek
--end

--select dbo.fn_prosjekstudenta(1) as prosjek

--create function fn_brojstudenata (@kurs_ID int)
--returns int
--as
--begin
--declare @broj int
--select @broj = count(*)
--from upis
--where kurs_ID = @kurs_ID
--return @broj
--end

--select dbo.fn_brojstudenata(1) as brojstudenata 

--create function fn_studentipokursu(@kurs_ID int)
--returns table
--as
--return
--(
   --select k.ime, k.prezime
   --from korisnik k
   --join upis on k.korisnik_ID = u.korisnik_ID
   --where u.kurs_ID = @kurs_ID
   --)

--za ispit

--create procedure sp_upis_na_kurs 
--@korisnik_ID int,
--@kurs_ID int
--as
--begin
--if exists(
--select 1 from upis where korisnik_ID = @korisnik_ID and kurs_ID = @kurs_ID
--)
--begin
--print 'Korisnik je vec upisan na ovaj kurs'
--return
--end

--declare @max int
--declare @trenutno int

--select @max = max_polaznika from kurs where kurs_ID = @kurs_ID
--select @trenutno = count(*) from upis where kurs_ID = @kurs_ID

--if @trenutno >= @max
--begin
--print 'Nema slobodnih mjesta na kursu!'
--return 
--end

--insert into upis(korisnik_ID, kurs_ID, datum_upisa)
--values(@korisnik_ID, @kurs_ID, getdate())

--print 'Upis uspjesno izvrsen'
--end

--exec sp_upis_na_kurs 1, 1
