-- SET TRANSACTION ISOLATION LEVEL { read uncommitted | read committed | repeatable read | serializable | snapshot }
-- ALTER DATABASE ime_baze SET ALLOW_SNAPSHOT_ISOLATION ON
-- SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH | <numeric-priority> }

use baza_filmova
go

create procedure sp_unesi_zaradu_filma
(
	@dan int,
	@idFilma int,
	@iznos decimal(10,2)
)
as
begin

	/*
		ova procedura radi insert bez ikakve provere ili citanja iz tabela
		tako da je u redu da je stavimo na read uncommitted zbog brzine,
		read commited nam ne treba, ne moramo da brinemo o nekoj vrednosti i njenoj ispravnosti, samo pisemo u tabelu
	*/
	begin try
		set tran isolation level read uncommitted
		begin tran t1
			insert into zarada (dan, id_filma, iznos)
			values (@dan, @idFilma, @iznos)
		commit tran t1
	end try
	begin catch
		print 'Greska pri sp unesi zaradu filma'
		rollback t1
	end catch
end
go

create procedure sp_angazuj
(
	@idFilma int,
	@ime varchar(100),
	@svojstvo varchar(20)
)
as
begin

	/*
		citamo iz tabele osobe i stavljamo u local var
		citamo iz angazovanja i stavljamo u local var
		insert u angazovanja
		update osoba

		zbog citanja i koriscenja lokalnih varijabli read uncommitted ne dolazi u obzir
		jer moze doci do dirty reada ako se procita vrednost neke transakcije koja nije committovana
		takodje, svo vreme dok radimo sa tom lokalnom promenljivom potrebno je da ona ostane ista
		tj. da vrednost u promenljivoj bude ista kao u tabeli, zato je minimum repeatable read kako
		bi sprecili nonrepeatable read problem, tj da nam se prava vrednost ne izmeni u toku rada transakcije

		sledeci problem je to sto ako ovo prodje mi dodajemo novu osobu u angazovanja, ali pre toga
		citamo iz angazovanja i brojimo ih, a ako bi se redom ukljucile dve ovakve transakcije
		i jedna doda nov red bas nakon sto druga izvuce broj angazovanja imali bi phantom read
		a jedini nacin da se to spreci je serializable ili snapshot nivo, snapshot nivo bi najverovatnije
		imao zastarele podatake tako da njega necemo koristiti sto nam ostavlja serializable koji nazalost
		moze dovesti do deadlockova ako se ove dve transakcije pokrenu istovremeno za iste ulaze
	*/

	begin try
		set tran isolation level serializable
		begin tran t2
			declare @idOsobe int
			declare @brojAngazovanja int

			select top 1 @idOsobe=id
			from osobe
			where ime like @ime
			order by trenutna_zarada
		
			select @brojAngazovanja=count(*)
			from angazovanje
			where id_filma=@idFilma and id_osobe=@idOsobe and svojstvo like @svojstvo

			waitfor delay '00:00:10'

			if @brojAngazovanja = 0
			begin
				insert into angazovanje (id_osobe, id_filma, svojstvo, status)
				values (@idOsobe, @idFilma, @svojstvo, 'aktivno')

				update osobe
				set status='angazovan'
				where id=@idOsobe
			end
		commit tran t2
	end try
	begin catch
		print 'Greska pri sp angazuj'
		rollback t2
	end catch
end
go

ALTER DATABASE baza_filmova SET ALLOW_SNAPSHOT_ISOLATION ON
go

create procedure sp_filmografija
(
	@idOsobe int
)
as
begin
	/*
		citamo iz angazovanja kursorom

		ovde je opet problematicno angazovanje (phantom read) u koje je moguc insert u svakom trenutku
		ali posto ovde samo citamo i prikazujemo a ne menjamo podatke u tabeli
		u obzir moze da dodje snapshot koji bi dosta dao na brzini ali moze se desiti
		da da zastarele informacije
	*/
	begin try
	set tran isolation level snapshot
	begin tran t3
		declare @idFilma int
		declare @id int

		declare kursorAngazovanje cursor for
		select id,id_filma from angazovanje
		where id_osobe=@idOsobe and status like 'zavrseno'

		open kursorAngazovanje

		fetch next from kursorAngazovanje
		into @id, @idFilma

		while @@FETCH_STATUS = 0
		begin
		
			select naziv
			from filmovi
			where id=@idFilma

			fetch next from kursorAngazovanje
			into @id, @idFilma
		end

		close kursorAngazovanje
		deallocate kursorAngazovanje
	commit tran t3
	end try
	begin catch
		print 'Greska pri sp_filmografija'
		rollback t3
	end catch
end
go

create procedure sp_vise_svojstava
(
	@idFilma int,
	@idOsobe int
)
as
begin
	/*
		izvlacimo broj angazovanja iz tabele angazovanje
		brisemo ako je taj broj veci od 1

		ovde imamo angazovanja ponovo i opet problem sa phantom read-om
		ako ona gornja transakcija insertuje dok ova radi, da to sprecimo
		radi sigurnosti je bolje koristiti serializable
	*/
	begin try
	set tran isolation level serializable
	begin tran t4
		declare @brojAngazovanja decimal(10,2)

		select @brojAngazovanja=count(*)
		from angazovanje
		where id_filma=@idFilma and id_osobe=@idOsobe

		if @brojAngazovanja > 1
		begin
			delete from angazovanje
			where id_filma=@idFilma and id_osobe=@idOsobe and svojstvo like 'glumac'
		end
	commit tran t4
	end try
	begin catch
		print 'Greska pri sp_vise_svojstava'
		rollback t4
	end catch
end
go

create procedure sp_isplati_angazovanog
(
	@idAngazovanja int
)
as
begin

	/*
		citamo iz angazovanja i stavljamo u local var
		citamo takodje iz zarada i --||--

		
		postoji mogucnost da se unosi novo angazovanje i nova zarada za vreme isplate, onda ce morati serializable,
		kako ne bi procitali neko novo angazovanje ili zaradu u trenutku dok se ona isplacuje, a
		to ce nam praviti problem u daljem radu jer serializable ce zatvoriti unos u zarade i usporiti
		prvu transakciju i poslednju transakciju dok se ova izvrsava. Ovde takodje moze doci do deadlocka
	*/
	begin try
	set tran isolation level serializable
	begin tran t5
		declare @idFilma int
		declare @idOsobe int
		declare @ukupanIznos decimal(10,2)
		declare @brojAngazovanih int
		declare @prosecnaZarada decimal(10,2)
		declare @status varchar(100)

		select @idFilma=id_filma, @idOsobe=id_osobe, @status=status
		from angazovanje
		where id=@idAngazovanja

		select @ukupanIznos=sum(iznos)
		from zarada
		where id_filma=@idFilma

		select @brojAngazovanih=count(*)
		from angazovanje
		where id_filma=@idFilma

		if @brojAngazovanih > 0 and @status <> 'zavrseno'
		begin
			set @prosecnaZarada=@ukupanIznos/@brojAngazovanih
			if @status<>'zavrseno'
			begin
				update osobe
				set trenutna_zarada = trenutna_zarada + @prosecnaZarada
				where id=@idOsobe

				update angazovanje
				set status='zavrseno'
				where id=@idAngazovanja
			end
		end
	commit tran t5
	end try
	begin catch
		print 'Greska pri sp_isplati_angazovanog'
		rollback t5
	end catch
end
go

create procedure sp_izlistaj_zarade
(
	@dan int
)
as
begin
	/*
		ovde samo sumiramo sve zarade, i stampamo ih
		ali se ova procedura izvrsava 1000 puta cesce od drugih

		imamo dve mogucnosti:
		da koristimo snapshot i da prezalimo to sto su podaci mozda zastareli,
		ali da u isto vreme dobijemo na brzini i na mogucnosti da se ni jedna
		druga transakcija ne blokira dok ova radi, jer cita snapshot i ne stavlja lockove na tabele

		ili da stavimo read committed kako ne bi moglo da dodje do dirty read-a,
		repeatable read nam ovde nije potreban je se ovo samo povlaci, sumira i stampa,
		ne citamo vrednost ponovo u istoj transakciji, i naravno da smanjimo prioritet
		ove transakcije da bi ona bila zrtva deadlocka a ne neka druga vaznija transakcija

		prva opcija je brza od druge, i ako podaci mozda nisu precizni, ali se oni svakako
		samo izlistavaju i ne koriste se za unos u neku od tabele tako da one ostaju konzistentne

	*/
	begin try
	set tran isolation level snapshot
	begin tran t6
		select sum(iznos) as 'zarada'
		from zarada
	commit tran t6
	end try
	begin catch
		print 'Greska pri sp_izlistaj zarade'
		rollback t6
	end catch
end
go