use online_igra_2
go

create procedure sp_prijava
(
	@idIgraca int,
	@idPartije int,
	@rbrUPartiji int
)
as
begin
	declare @statusPrijave varchar(20)
	select @statusPrijave=statusPrijave 
	from partije
	where id=@idPartije

	if @statusPrijave='otvorena'
	begin
		insert into igrac_partija(idIgraca, idPartije, rbrUPartiji)
		values (@idIgraca, @idPartije, @rbrUPartiji)
		print 'Prijava uspesna'
	end
	else
	begin
		print 'Partija je vec pocela, niste se prijavili na vreme.'
	end
end
go


create procedure sp_pocetak_partije
(
	@idPartije int
)
as
begin
	update partije
	set statusPrijave = 'zatvorena'
	where id=@idPartije

	print 'Kraj prijave! Pocinje partija! Lista svih igraca:'
	select idIgraca, rbrUPartiji from igrac_partija
	where idPartije=@idPartije
	order by rbrUPartiji
end
go


create procedure sp_novi_potez
(
	@idIgraca int, @idPartije int, @potez varchar(50), @brPoena int
)
as
begin
	declare @id int, @max_rbr int, @novi_rbr int
	select @id = id
	from igrac_partija
	where idIgraca=@idIgraca
	and idPartije=@idPartije

	select @max_rbr = max(rbrPotezaUPartiji)
	from potezi
	where idIgracPartija in
	(select id from igrac_partija
	where idPartije = @idPartije)

	if @max_rbr is null
		set @novi_rbr = 1
	else
		set @novi_rbr = @max_rbr+1

	insert into potezi(idIgracPartija,potez,brDobijenihPoena,rbrPotezaUPartiji)
	values(@id,@potez,@brPoena,@novi_rbr)
end
go


create procedure sp_kraj_partije
(
	@idPartije int
)
as
begin
	declare @idIgracPartija int, @idIgraca int

	select top 1 @idIgracPartija=idIgracPartija
	from potezi
	where idIgracPartija in
	(select id from igrac_partija
	where idPartije = @idPartije)
	group by idIgracPartija
	order by sum(brDobijenihPoena) desc

	select @idIgraca=idIgraca
	from igrac_partija
	where id=@idIgracPartija

	update partije
	set pobednik = @idIgraca
	where id = @idPartije

	update igraci
	set brPobeda = brPobeda+1
	where id = @idIgraca
end
go


create procedure sp_kraj_ciklusa
(
	@rbrCiklusa int
)
as
begin
	declare @idIgracPartija int, @idIgraca int

	insert into ciklusi(rbrCiklusa, idIgraca, brPobeda)
	(select @rbrCiklusa, id, brPobeda 
	from igraci
	where brPobeda>0)

	update igraci
	set brPobeda = 0
end
go

