--1. Kreirati proceduru koja vraca prosek i broj nepolozenih ispita za odredjenog studenta

create procedure prosek_nepolozeno(@brInd int, @godUpisa int, @prosek decimal(6,2) output,
       @nepolozeno int output)
as
begin
	select @prosek = avg(ocena * 1.0)
	from Prijave
	where Indeks = @brInd and Upisan = @godUpisa
	and ocena > 5

	select @nepolozeno = count(ps.Spred)
	from Studenti s join Planst ps on s.Ssmer = ps.Ssmer 
	   left join Prijave pr on pr.Indeks = s.Indeks and
	        pr.Upisan = s.Upisan and ps.Spred = pr.Spred and Ocena > 5
	where pr.Spred is null and s.Indeks = @brInd and s.Upisan = @godUpisa
end


-- PRIMER POZIVA
declare @prosek_izlaz decimal(6,2)
declare @koliko_jos int
exec prosek_nepolozeno @brInd = 2, @godUpisa = 2002, @prosek =  @prosek_izlaz output, @nepolozeno = @koliko_jos output
select 2, 2002, @prosek_izlaz, @koliko_jos

--2. Napisati proceduru za upis ocene

create procedure polozen_ispit(@brInd int, @godUpisa int, @spred int, @snast int,
      @ocena int)
as
begin
	declare @postoji int
	set @postoji = 0
	select @postoji = count(*)
	from prijave
	where  Indeks = @brInd and Upisan = @godUpisa and Spred = @spred
	   and Snast = @snast and ocena is null

	if (@postoji > 0)
	begin
		update prijave set Ocena = @ocena, Datump = GETDATE()
			where  Indeks = @brInd and Upisan = @godUpisa and Spred = @spred
			and Snast = @snast and ocena is null
	end
end

--PRIMER POZIVA
exec polozen_ispit 5, 2002, 1, 1, 7


-----------------------------------------------------------------------------------------

/* TRIGERI - SINTAKSA

		CREATE TRIGGER [ schema_name.]trigger_name
		ON { table|view }
		{ FOR | AFTER | INSTEAD OF }
		{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
		AS { sql_statement [ ; ] [ ... ] }

*/


-- 3.

create trigger provera_godine_upisa on studenti
after insert
as
begin
	if exists (select Upisan from inserted where Upisan > DATEPART(yyyy, GETDATE()) )
	begin
		raiserror('Ta skolska godina jos nije pocela', -1, -1)
	end
end

insert into Studenti values
				(5, 2012, 'Nenad', 'Beograd', GETDATE(), 1),
				(5, 2013, 'Nenad', 'Beograd', GETDATE(), 1),
				(5, 2021, 'Nenad', 'Beograd', GETDATE(), 1); -- Dobija se obavestenje za jedan insert kod koga je godina upisa 2021 "Ta skolska godina jos nije pocela"

select * from Studenti
where Imes like 'Nenad' -- selekcijom podataka vidimo da je i red za koji smo dobili obavestenje upisan u tabelu

insert into Studenti values
				(10, 2010, 'Nenad', 'Beograd', GETDATE(), 1)

insert into Studenti values
				(30, 2024, 'Nenad', 'Beograd', GETDATE(), 1)

select * from Studenti
where Imes like 'Nenad'

-- 4.
alter trigger provera_godine_upisa on studenti
instead of insert
as

if exists (select Upisan from inserted where Upisan > DATEPART(yyyy, getdate()))
begin
	raiserror('Ta skolska godina jos nije pocela', -1, -1);
end
else
begin

	insert into Studenti select * from inserted
end

insert into Studenti values
				(4, 2013, 'Marko Markovic', 'Beograd', GETDATE(), 1),
				(7, 2014, 'Marko Markovic', 'Beograd', GETDATE(), 1),
				(12, 2026, 'Marko Markovic', 'Beograd', GETDATE(), 1); -- Nece biti ubacen nijedan red, iako dva studenta zadovoljavaju uslov za upis


select * from Studenti
where Imes like 'Marko Markovic' 


insert into Studenti values
				(5, 2006, 'Milan11', 'Beograd', GETDATE(), 1)

insert into Studenti values
				(25, 2022, 'Milan222', 'Beograd', GETDATE(), 1)

select * from Studenti
where Imes like 'Marko Markovic'

-- 5. Automatsko cuvanje izbrisanih ocena

create table ponistene_ocene
(
	Spred smallint null,
	Indeks smallint null,
	Upisan smallint null,
	Snast smallint null,
	Datump datetime not null,
	Ocena smallint null
)


create trigger arhiva_ponistenih on Prijave
after delete
as
begin

	insert into ponistene_ocene select * from deleted
end


delete from Prijave where Indeks = 1 and Upisan = 2000

select * from ponistene_ocene 


-- 6. Automatsko cuvanje izbrisanih ocena

create table izmenjene_ocene
(
	Spred smallint null,
	Indeks smallint null,
	Upisan smallint null,
	Snast smallint null,
	Datump datetime not null,
	Ocena smallint null
)

create trigger arhiva_izmenjenih on Prijave
after update
as
begin

	insert into izmenjene_ocene select * from deleted
end

update Prijave set ocena = 10 where Indeks = 1 and Upisan = 2002

select * from izmenjene_ocene

-- 7. Kursori



	declare @indeks int
	declare @upisan int
	declare @spred int
	declare @ocena int

	-- Deklarisi kursor za rezultat zeljenog upita
	declare kursorKrozPrijave cursor for
	select indeks, upisan, spred, ocena from prijave
	
	open kursorKrozPrijave -- Otovri kursor za citanje

	FETCH NEXT FROM kursorKrozPrijave INTO @indeks, @upisan, @spred, @ocena -- Ucitaj prvi red rezultata u promenljive

	WHILE @@FETCH_STATUS = 0
	BEGIN
		
	 	--select @indeks, @upisan, @spred, @ocena
		print concat(@indeks, ' ', @upisan, ' ', @spred, ' ', @ocena)
	   
		FETCH NEXT FROM kursorKrozPrijave INTO @indeks, @upisan, @spred, @ocena
	END

	CLOSE kursorKrozPrijave
	DEALLOCATE kursorKrozPrijave