-- III kolokvijum 2015-2016

/*

KORISNIK ( ID, IME ) 
PRIJATELJI ( IDKORISNIKA1 ,  IDKORISNIKA2 , DATUM ) - korisnici koji su prijatelji i datum kada su postali prijatelji 
TIPPOSTA ( ID, TIP ) 
POST ( ID, IDTIPA, IDVLASNIKA, DATUMOBJAVE ) - idvlasnika je id korisnika koji je objavio post 
LAJK ( ID, IDKORISNIKA, IDPOSTA, DATUM ) - idkorisnika je id korisnika koji je lajkovao neki post (idposta)   

*/


use socialNetwork

-- 1. Kreirati tabelu Aktivnost(idkorisnika, mesec, godina, broj_lajkova, broj_postova), sva polja su celobrojnog tipa, PK(idkorisnika, mesec, godina). 

create table Aktivnost 
(
	idKorisnika int not null,
	mesec int not null,
	godina int not null,
	broj_lajkova int null,
	broj_postova int null,
	primary key (idkorisnika, mesec, godina)
)

select * from Aktivnost

-- 2.

/*

Napisati SQL script kojim se definiše funkcija koja za predati datum generiše i  vraća tabelu sa parovima (mesec, godina) od datuma koji je predat do trenutnog meseca. 

Primer. Za 23.11.2015. fukcija treba da vrati tabelu sa sledećim sadržajem 

	11 2015 
	12  2015 
	1  2016
	 
*/

alter function fn_parovi(@datum datetime)
returns @temp_table table (
	mesec int not null,
	godina int not null
)
as
begin

	declare @temp_datum datetime

	set @temp_datum = @datum

	while @temp_datum < GETDATE()
	begin
		insert into @temp_table values(DATEPART(m, @temp_datum), DATEPART(year, @temp_datum))
		set @temp_datum = dateadd(month, 1, @temp_datum)
	end

	return
end

select * from fn_parovi('2014-05-05')

-- 3.

/*
	Napisati SQL skcript kojim se definise funkcija koja za ID korisnika vraca datum sklapanja prvog prijateljstva.  
*/

create function fn_prvo_prijateljstvo(@idkorisnika int)
returns datetime
as
begin

	declare @datum as datetime


	/* I nacin */

	--set @datum = (select min(datum)
	--				from Prijatelji 
	--				where idKorisnika1 = @idkorisnika
	--			  )

	/* II nacin */

	select  @datum = min(datum)
					from Prijatelji 
					where idKorisnika1 = @idkorisnika
	return @datum

end

-- Primer poziva za korisnika sa ID-em 1
select dbo.fn_prvo_prijateljstvo(1)

-- Provera funkcije - Za vakog korisnika se ispisuje datum njegovog prvog prijateljstva
select Korisnik.id, dbo.fn_prvo_prijateljstvo(Korisnik.id)
from Korisnik


-- 4.

/*

Napisati stornu proceduru koja popunjava tabelu Aktivnost tako što za svakog korisnika društvene mreže upisuje statistiku o broju objavljivanih postova i broju lajkova (koje je dobio) po 
mesecima, počevši od  meseca u kojem je taj korisnik sklopio prvo prijateljstvo. Koristiti kursore po potrebi. Obavezno dati primer pozivanja definisane storne procedure. 

*/


create procedure sp_popuni_aktivnosti
as
begin

	declare @idKorisnika int

	-- Deklarisi kursor koji ide po ID-evima svih korisnika
	declare korisnici cursor for
	select id
	from Korisnik

	open korisnici

	fetch next from korisnici into @idKorisnika -- Preuzmi ID prvog korisnika

	while @@fetch_status = 0 -- Sve dok postoje korisnici
	begin


	/* Popuni tabelu aktivnosti za trenutnog korisnika */

	-- I nacin - ugnjezdeni upit u select-u

		/*
			insert into Aktivnost 
			select @idKorisnika, mesec, godina, (select count(*) from post p where p.idVlasnika = @idKorisnika and DATEPART(month, p.datumObjave) = pp.mesec and DATEPART(year, p.datumObjave) = pp.godina) as brojpostova,
											(select count(*) from post p join lajk l on p.id = l.idPosta and p.idVlasnika = @idKorisnika where DATEPART(month, l.datum) = pp.mesec and DATEPART(year, l.datum) = pp.godina) as brojLajkova
			from dbo.fn_parovi(dbo.fn_prvo_prijateljstvo(@idKorisnika)) pp
		*/

	-- II nacin - pomocne funkcije u selectu (definisane su ispod procedure)

		insert into Aktivnost -- za trenutnog korisnika kreiraj potrebnu tabelu uz pomoc poziva dbo.fn_parovi(dbo.fn_prvo_prijateljstvo(@idKorisnika)), a zatim za svaki par mesec-godina i id korisnika popuni tabelu aktivnost
		select @idKorisnika, mesec, godina, dbo.fn_broj_postova(@idkorisnika, pp.mesec, pp.godina) as brojPostova, dbo.fn_broj_lajkova(@idkorisnika, pp.mesec, pp.godina) as brojLajkova
		from dbo.fn_parovi(dbo.fn_prvo_prijateljstvo(@idKorisnika)) pp

		fetch next from korisnici into @idKorisnika -- Uzmi sledeceg korisnika

	end

end

-- Funkcija koja za id korisnika, mesec i godinu vraca broj postova
create function fn_broj_postova(@idkorisnika int, @mesec int, @godina int)
returns int
as
begin
	declare @brojPostova int
	set @brojPostova = (select count(*) from post p where p.idVlasnika = @idKorisnika and DATEPART(month, p.datumObjave) = @mesec and DATEPART(year, p.datumObjave) = @godina)
	return @brojPostova
end

-- Funkcija koja za id korisnika, mesec i godinu vraca broj lajkova
create function fn_broj_lajkova(@idkorisnika int, @mesec int, @godina int)
returns int
as
begin
	declare @brojLajkova int
	set @brojLajkova = (select count(*) from post p join lajk l on p.id = l.idPosta and p.idVlasnika = @idKorisnika where DATEPART(month, l.datum) = @mesec and DATEPART(year, l.datum) = @godina)
	return @brojLajkova
end


--delete from Aktivnost where 1 = 1
exec sp_popuni_aktivnosti

select * from Aktivnost