use zurka
go


-- 1.
create table NevalidnaPoseta
(
	osoba_id int not null foreign key references osoba(id),
	zurka_id int not null foreign key references zurka(id),
	vreme_dolaska datetime not null,
	vreme_odlaska datetime not null
)
alter table NevalidnaPoseta
add constraint PK_nevalidna_poseta primary key (osoba_id, zurka_id)

go
create trigger trigger_posete
on Poseta
instead of insert
as
begin
	declare @osoba int
	declare @zurka int
	declare @vreme_dolaska datetime
	declare @vreme_odlaska datetime
	declare @broj_zurki int

	declare kursorInserted cursor for
	select osoba_id, zurka_id, vreme_dolaska, vreme_odlaska from inserted

	open kursorInserted
	fetch next from kursorInserted into @osoba, @zurka, @vreme_dolaska, @vreme_odlaska

	while @@FETCH_STATUS = 0
	begin
		set @broj_zurki = 0

		select @broj_zurki = count(*)
		from Poseta
		where osoba_id = @osoba and zurka_id <> @zurka and ( (@vreme_dolaska >= vreme_dolaska and @vreme_dolaska <= vreme_odlaska) or
									  (@vreme_odlaska >= vreme_dolaska and @vreme_odlaska <= vreme_odlaska) or
									  (@vreme_dolaska <= vreme_dolaska and @vreme_odlaska >= vreme_odlaska) )
		if @broj_zurki > 0
		begin
			insert into NevalidnePosete values(@osoba, @zurka, @vreme_dolaska, @vreme_odlaska)
		end
		else
		begin
			insert into Poseta values(@osoba, @zurka, @vreme_dolaska, @vreme_odlaska)
		end

		fetch next from kursorInserted into @osoba, @zurka, @vreme_dolaska, @vreme_odlaska
	end

	close kursorInserted
	deallocate kursorInserted
end
go

delete from NevalidnaPoseta where 1=1

delete from Poseta where 1=1


-- (1, 'Retro Party', '2023-05-10 18:00:00', '2023-05-10 23:00:00', 100, 1)
INSERT INTO Poseta (osoba_id, zurka_id, vreme_dolaska, vreme_odlaska)
VALUES (1, 1, '2023-05-10 17:00:00', '2023-05-10 23:00:00')

INSERT INTO Poseta (osoba_id, zurka_id, vreme_dolaska, vreme_odlaska)
VALUES (2, 1, '2023-05-10 17:00:01', '2023-05-10 17:10:00')

INSERT INTO Poseta (osoba_id, zurka_id, vreme_dolaska, vreme_odlaska)
VALUES (3, 1, '2023-05-10 17:00:01', '2023-05-10 17:10:00')

INSERT INTO Poseta (osoba_id, zurka_id, vreme_dolaska, vreme_odlaska)
VALUES (4, 1, '2023-05-10 17:00:02', '2023-05-10 17:09:00')

-- (2, 'Hip Hop Night',	'2023-05-10 17:00:00', '2023-05-10 22:00:00', 200, 2)

INSERT INTO Poseta (osoba_id, zurka_id, vreme_dolaska, vreme_odlaska)
VALUES (1, 2, '2023-05-10 17:00:00', '2023-05-10 18:00:00')

INSERT INTO Poseta (osoba_id, zurka_id, vreme_dolaska, vreme_odlaska)
VALUES (2, 2, '2023-05-10 18:00:01', '2023-05-10 18:10:00')

INSERT INTO Poseta (osoba_id, zurka_id, vreme_dolaska, vreme_odlaska)
VALUES (3, 2, '2023-05-10 18:00:01', '2023-05-10 18:10:00')

INSERT INTO Poseta (osoba_id, zurka_id, vreme_dolaska, vreme_odlaska)
VALUES (4, 2, '2023-05-10 17:00:02', '2023-05-10 17:09:00')

INSERT INTO Poseta (osoba_id, zurka_id, vreme_dolaska, vreme_odlaska)
VALUES (5, 2, '2023-05-10 19:00:02', '2023-05-10 19:09:00')

INSERT INTO Poseta (osoba_id, zurka_id, vreme_dolaska, vreme_odlaska)
VALUES (6, 2, '2023-05-10 19:00:02', '2023-05-10 19:09:00')

INSERT INTO Poseta (osoba_id, zurka_id, vreme_dolaska, vreme_odlaska)
VALUES (7, 2, '2023-05-10 19:00:02', '2023-05-10 19:09:00')

-- (3, 'Karaoke Night',		'2023-05-10 16:00:00', '2023-05-10 21:00:00', 500, 3)
INSERT INTO Poseta (osoba_id, zurka_id, vreme_dolaska, vreme_odlaska)
VALUES (1, 3, '2023-05-10 16:00:00', '2023-05-10 17:00:00')

INSERT INTO Poseta (osoba_id, zurka_id, vreme_dolaska, vreme_odlaska)
VALUES (2, 3, '2023-05-10 17:00:01', '2023-05-10 18:00:00')

-- (4, 'Summer Beach Party',	'2023-05-11 16:00:00', '2023-05-12 04:00:00', 100,  1)
INSERT INTO Poseta (osoba_id, zurka_id, vreme_dolaska, vreme_odlaska)
VALUES (2, 4, '2023-05-11 16:00:00', '2023-05-12 04:00:0')

select * from NevalidnaPoseta
select * from Poseta

delete from Poseta where 1=1
delete from NevalidnaPoseta where 1=1


-- 2.

create function zurke_na_lokaciji(@lokacija int)
returns @temp_table table
(
	nazivZurke varchar(255) not null,
	vremePocetka datetime not null,
	vremeKraja datetime not null,
	zarada float not null
)
as
begin

	insert into @temp_table 
	select naziv, vreme_pocetka, vreme_zavrsetka, (select z.cena_karte * count(*) from Poseta where zurka_id = id)
	from Zurka z
	where @lokacija = lokacija_id

	return
end

select * from dbo.zurke_na_lokaciji(1)
select * from zurka

--3.

create function hronoloske_zarade(@lokacija int, @odDatuma datetime, @doDatuma datetime)
returns @table table
(
	nazivZurke varchar(255) not null,
	vremePocetka datetime not null,
	vremeKraja datetime not null,
	zarada float not null,
	broj_vecih int not null
)
as
begin
	insert into @table
	select *, (
				select count(*)
				from dbo.zurke_na_lokaciji(@lokacija) 
				where vremePocetka >= @odDatuma
				and vremeKraja <= @doDatuma
				and vremeKraja <= znl.vremePocetka
				and znl.zarada < zarada
			  )  as broj_vecih
	from dbo.zurke_na_lokaciji(@lokacija) znl
	where vremePocetka >= @odDatuma and vremeKraja <= @doDatuma

	return
end

create function hronoloski_prihod(@lokacija int, @odDatuma datetime, @doDatuma datetime)
returns int
as
begin
	
	declare @veci int

	select @veci = sum(broj_vecih)
	from dbo.hronoloske_zarade(@lokacija, @odDatuma, @doDatuma)
	
	if @veci > 0
		return -1
	
	return 1

end


--4.

create table RastZarade
(
	lokacija_id int not null primary key foreign key references lokacija(id),
	naziv varchar(255) not null,
	datumOd datetime not null,
	datumDo datetime not null
)

create procedure popuni_rast_zarade (@broj_lokacija int output)
as
begin
	set @broj_lokacija = 0

	declare @zurka int
	declare @naziv varchar(255)
	declare @vreme_pocetka datetime
	declare @vreme_zavrsetka datetime
	declare @lokacija int

	declare @min_date datetime

	declare kursorZurke cursor for
	select id, naziv, vreme_pocetka, vreme_zavrsetka, lokacija_id from Zurka

	open kursorZurke
	fetch next from kursorZurke into @zurka, @naziv, @vreme_pocetka, @vreme_zavrsetka, @lokacija

	while @@FETCH_STATUS = 0
	begin
		select @min_date = min(vreme_pocetka)
		from Zurka
		where lokacija_id = @lokacija

		if dbo.hronoloski_prihod(@lokacija, @min_date, @vreme_zavrsetka) = 1
		begin
			if exists (select * from RastZarade where lokacija_id = @lokacija)
			begin
				update RastZarade
				set datumDo = @vreme_zavrsetka
				where lokacija_id = @lokacija
			end
			else
			begin
				insert into RastZarade values(@lokacija, @naziv, @min_date, @vreme_zavrsetka)
				set @broj_lokacija = @broj_lokacija + 1
			end
		end
		fetch next from kursorZurke into @zurka, @naziv, @vreme_pocetka, @vreme_zavrsetka, @lokacija
	end

	close kursorZurke
	deallocate kursorZurke

end

delete from RastZarade where 1=1

declare @broj_lokacija int
exec popuni_rast_zarade @broj_lokacija = @broj_lokacija output

select @broj_lokacija
select * from RastZarade