-- Michał Krause
-- 238191
-- Centrum coachingu E-Sportowego

-- 0) Poprawione rozwiązanie zadania 1b (skrypt generujący strukturę bazy danych)
CREATE TABLE Producent
(
ProducentID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
Nazwa varchar(255)
);

CREATE TABLE Trener
(
TrenerID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
Imie varchar(255),
Nazwisko varchar(255),
Ranga varchar(255),
Pensja money
);

CREATE TABLE Gracz
(
GraczID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
Imie varchar(255),
Nazwisko varchar(255),
Ranga varchar(255)
);

CREATE TABLE Transakcja
(
TransakcjaID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
GraczID int FOREIGN KEY REFERENCES Gracz,
ilosc_towaru int
);


CREATE TABLE Gra
(
GraID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
Nazwa_Gry varchar(255),
Typ_Gry varchar(255),
Opis varchar(255)
);

CREATE TABLE Budynek
(
BudynekID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
Adres varchar(255),
Czynsz money,
ilosc_komputerow int,
Oplaty money
);

CREATE TABLE Rejestracja
(
RejestracjaID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
TrenerID int FOREIGN KEY REFERENCES Trener,
GraczID int FOREIGN KEY REFERENCES Gracz,
GraID int FOREIGN KEY REFERENCES Gra,
Data_Rejestracji date,
Godzina varchar(5),
);

CREATE TABLE Lekcja
(
LekcjaID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
GraID int FOREIGN KEY REFERENCES Gra,
BudynekID int FOREIGN KEY REFERENCES Budynek,
RejestracjaID int FOREIGN KEY REFERENCES Rejestracja,
TrenerID int FOREIGN KEY REFERENCES Trener,
Data_2 date,
Czas_trwania varchar(5),
Koszt money
);


CREATE TABLE Turniej
(
TurniejID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
GraID int FOREIGN KEY REFERENCES Gra,
Koszt_wejscia money,
Nagroda_Glowna varchar(255),
Data_2 date,
Czas_trwania varchar(5)
);

CREATE TABLE Towar
(
TowarID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
BudynekID int FOREIGN KEY REFERENCES Budynek,
TransakcjaID int FOREIGN KEY REFERENCES Transakcja,
ProducentID int FOREIGN KEY REFERENCES Producent,
Nazwa_towaru varchar(255),
Cena_towaru money
);

INSERT INTO Producent (Nazwa)
VALUES ('Razer'),('Roccat'),('Logitech'),('CocaCola'),('Lays')

INSERT INTO Trener (Imie, Nazwisko, Ranga, Pensja)
VALUES ('Krzysztof', 'Sauc', 'Diament', 3500),('Michal', 'Krause', 'Challenger', 4500),('Mateusz','Nubeusz','Gold',1900),('Robert','Fidytek','Challenger',4600),('Alguem','Br','Bronz',600)

INSERT INTO Gracz (Imie, Nazwisko, Ranga)
VALUES ('Albert','Einstein','Bronz'),('Waldemar','Ktostam','Bronz'),('Ram','Pampam','Silver'),('Rune','Scape','Silver'),('Coja','Turobie','Challenger')

INSERT INTO Transakcja (Ilosc_Towaru)
VALUES (1),(1),(2),(1),(4)

INSERT INTO Gra (Nazwa_Gry, Typ_Gry, Opis)
VALUES ('LoL', 'MOBA', 'Najslawniejsza'),('CeEs', 'FPS', 'Najpopularniejszy FPS w Polsce'),('WoW', 'MMORPG', 'WoWa chyba nie trzeba nikomu przedstawiac'),('CoD','FPS', 'FaZe 420 MLG rekt fgt'),('Hearthstone', 'Karcianka', 'Mimo czesto wystepujacych elementow losowych wciaz bardzo skillowa gra')

INSERT INTO Budynek (Adres, Czynsz, Ilosc_komputerow, Oplaty)
VALUES ('Gdynia', 10000, 7, 983),('Gdansk',12513,8,1295),('Krakow',8977,5,687),('Warszawa',25123,12,2035),('Lodz',8533,6,753)

INSERT INTO Rejestracja (Data_Rejestracji, Godzina)
VALUES ('2012-05-16','16:00'),('2012-06-12', '13.30'),('2012-01-01', '12.35'),('2013-08-07','10.00'),('2013-03-11', '16.00')

INSERT INTO Lekcja (Data_2, Czas_Trwania, Koszt)
VALUES ('04-05-12', '2.00', 100),('03-06-12', '2.00', 100),('01-01-01', '1.00', 60),('04-08-07','3.00', 130),('05-03-11','2.00', 100)

INSERT INTO Turniej (Koszt_Wejscia, Nagroda_Glowna, Data_2, Czas_Trwania)
VALUES (10, 'boostery', '2012-04-05' , '2.00'),(15, 'skiny', '03-06-12', '6.00'),(20, 'karambit kosa', '01-01-01', '4.00'),(14, 'ksiazka z autografem', '04-08-07', 1.00), (22, 'gra komputerowa', '05-03-11', '3.00')

INSERT INTO Towar (Nazwa_towaru, Cena_Towaru)
VALUES ('RAZRNAGA', 350),('Myszka', 24),('Czipsy', 4),('Cola',5),('Podkladka', 40)


--1a) Tworzymy widok

PRINT 'wypisuje trenerów i ich lekcje'
GO

CREATE VIEW trener_lekcja
AS
SELECT Trener.Imie, Trener.Nazwisko, Lekcja.Data_2
FROM  Trener INNER JOIN Lekcja ON Trener.TrenerID=Lekcja.TrenerID
WHERE IMIE IS NOT NULL
WITH CHECK OPTION;
GO

--1b) Sprawdzenie, że widok działa

SELECT * FROM Trener WHERE IMIE=0;


--2a) Tworzymy funkcję 1 (funkcja oblicza nagrodę za 1 miejsce, czyli 60% wejściowego. Potem powinniśmy pomnożyć ją przez ilość uczestników turnieju oczywiście, ale to już zależy od danego dnia)

--DROP FUNCTION dbo.czesc_nagrody
GO

CREATE FUNCTION dbo.czesc_nagrody
(
    @p_koszt_wejscia INT, @p_pierwsze_miejsce INT
)
RETURNS INT
BEGIN
	SET	@p_pierwsze_miejsce = (Koszt_wejscia / 100) * 60
    RETURN(SELECT COUNT(Koszt_wejscia) FROM Turniej
            WHERE Koszt_Wejscia=@p_koszt_wejscia)
END
GO

--2b) Sprawdzenie, że funkcja 1 działa

SELECT dbo.czesc_nagrody(1000) AS czesc_nagrody;

--3a) Tworzymy funkcję 2 (funkcja oblicza modę wśród cen lekcji)

--DROP FUNCTION dbo.najczestsza_cena
GO

CREATE FUNCTION dbo.najczestsza_cena
(
    @p_cena_lekcji INT
)
RETURNS INT
BEGIN
    RETURN(SELECT COUNT(Koszt) FROM LEKCJA WHERE Koszt=@p_cena_lekcji ORDER BY COUNT(*) DESC)
END
GO

--3b) Sprawdzenie, że funkcja 2 działa

SELECT dbo.najczestsza_cena AS najczestsza_cena;

--4a) Tworzymy procedurę 1 (dodaje nowego trenera)

--DROP PROC dodaj_trenera
GO

CREATE PROC dodaj_trenera @p_imie_trenera VARCHAR(225), @p_nazwisko VARCHAR(225), @p_ranga VARCHAR(225), @p_pensja MONEY
AS
    INSERT INTO TRENER (Imie, Nazwisko, Ranga, Pensja)
    VALUES(@p_imie_trenera, @p_nazwisko,  @p_ranga, @p_pensja)
GO

--4b) Sprawdzenie, że procedura 1 działa

EXEC dodaj_trenera 'Rekt', 'Mate', 'Platyna', 2012
SELECT * FROM Trener

--5a) Tworzymy procedurę 2 (procedura usuwa z bazy danych klienta, który już nie uczęszcza)

--DROP PROC usun_gracza
GO

CREATE PROC usun_gracza @p_imie_gracza VARCHAR(225), @p_nazwisko_gracza VARCHAR(225), @p_ranga_gracza VARCHAR(225)
AS
    DELETE FROM Gracz WHERE IMIE=@p_imie_gracza AND Nazwisko=@p_nazwisko_gracza
GO

--5b) Sprawdzenie, że procedura 2 działa

EXEC usun_gracza 'Albert','Einstein'
SELECT * FROM Gracz

--6a) Tworzymy wyzwalacz 1 (wyzwalacz uniemożliwia ustalenie pensji trenera mniejszej niż 1000 zł)

--DROP TRIGGER ogarnij_hajs
GO

CREATE TRIGGER ogarnij_hajs ON Trener
AFTER INSERT
AS
    BEGIN
        IF(Trener.Pensja<1001)
            BEGIN
                RAISERROR('Nie mozesz dać trenerowi tak mało pieniędzy!',1,2)
                ROLLBACK
            END
    END
GO

--6b) Sprawdzenie, że wyzwalacz 1 działa


INSERT INTO Trener(Imie, Nazwisko, Ranga, Pensja)
VALUES('Waldemar','Pawlak','Challenger',600)


--7a) Tworzymy wyzwalacz 2 (nie pozwala ustalić daty turnieju na wcześniejszą niż obecnie)

--DROP TRIGGER zla_data
GO

CREATE TRIGGER zla_data ON Turniej
AFTER INSERT
AS
    BEGIN
        DECLARE @datateraz INT
        SET @datateraz = (SELECT data_2 FROM inserted)
        IF(@datateraz<(GETDATE()))
            BEGIN
                RAISERROR('Nie mozesz zorganizować turnieju w przeszłości!',1,2)
                ROLLBACK
            END
    END
GO


--7b) Sprawdzenie, że wyzwalacz 2 działa

INSERT INTO Turniej(koszt_wejscia, nagroda_glowna, data_2, czas_trwania)
VALUES(20, 'satysfakcja','2015-10-12', '4.00');

--8a) Tworzymy wyzwalacz 3 (nie pozwalający usuwać trenerów z rangą Challenger z bazy danych)

--DROP TRIGGER usuwanie_ziomeczkow
GO

CREATE TRIGGER usuwanie_ziomeczkow ON Trener
AFTER DELETE
AS
	BEGIN
		IF(Ranga='Challenger')
			BEGIN
		 RAISERROR('Nie możesz usuwać najlepszych trenerów! To Twoi kumple!',1,2)
		 ROLLBACK
		END
	END
GO

--8b) Sprawdzenie, że wyzwalacz 3 działa

DELETE FROM Trener(Imie, Nazwisko, Ranga, Pensja) WHERE Ranga='Challenger'

--9a) Tworzymy wyzwalacz 4 (nie pozwalający sprzedać więcej niż 20 przedmiotów w jednej transakcji)

--DROP TRIGGER ogromne_transakcje
GO

CREATE TRIGGER ogromne_transakcje ON Transakcja
AFTER INSERT
AS
	BEGIN
		IF ilosc_towaru > 20
			BEGIN
		 RAISERROR('Podziel transakcje na dwie mniejsze',1,2)
		 ROLLBACK
		END
	END
GO


--9b) Sprawdzenie, że wyzwalacz 4 działa

INSERT INTO Transakcja(ilosc_towaru) VALUES (23)


--10) Tworzymy tabelę przestawną