--Utwórz tabele klient(id_klient, nazwisko, nr_prawa_jazdy), gdzie:
--id_klient - automatycznie nadawany, niepusty numer klienta, klucz główny,
--nazwisko - niepusty łańcuch znaków zmiennej długości od 3 do 20 znaków,
--nr_prawa_jazdy - numer prawa jazdy w formacie 5liczb / 2liczby/4 liczby np 12345/12/1234
-- tabelę car(id_car, typ, cena)
--id_car -automatycznie nadawany, niepusty numer auta, klucz główny,
-- typ - niepusty łańcuch znaków zmiennej długości od 3 do 20 znaków,
-- cena - waluta

--oraz tabelę rezerwacja (id_rezerwacja, id_klient, id_car,data_pocz,data_konc), gdzie:
--id_rezerwacja - niepusty klucz główny, wartości nadawane automatycznie,
--id_klient - niepusty klucz obcy powiązany z kolumną id_klient tabeli klient,
--id_car - niepusty klucz obcy powiązany z kolumną id_car tabeli car,
--data_pocz - data
--data_konc - data
--Rozwiązanie [3pkt]:

CREATE TABLE klient(
id_klient INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
nazwisko VARCHAR(20) NOT NULL CHECK(LEN(nazwisko)<20 AND LEN(nazwisko)>3),
nr_prawa_jazdy VARCHAR(13)  CHECK (LEN(nr_prawa_jazdy)=13),
);

CREATE TABLE car(
id_car INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
typ VARCHAR(20) NOT NULL CHECK(LEN(typ)<20 AND LEN(typ)>3),
cena DECIMAL,
);

CREATE TABLE rezerwacja(
id_rezerwacja INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
id_klient INT FOREIGN KEY REFERENCES klient(id_klient),
id_car INT FOREIGN KEY REFERENCES car(id_car),
data_pocz DATE,
data_konc DATE,
);

----------------------------------------------------------------------------------------------------

--Utwórz widok raport(id_klient ,nazwisko,nr_prawa_jazdy,suma_pieniedzy), gdzie
--id_klient, nazwisko, n_prawa_jazdy to kolumny z tabeli klient
--suma_pieniedzy - łączna suma wydanych pieniędzy przed danego klienta (ilość dni*cena)
--Rozwiązanie [2 pkt]

CREATE VIEW raport AS SELECT k.id_klient, k.nazwisko, k.nr_prawa_jazdy, SUM(c.cena*DATEDIFF(dd, r.data_pocz, r.data_konc )) AS suma_pieniedzy
FROM klient k LEFT JOIN rezerwacja r ON k.id_klient=r.id_klient
		LEFT JOIN car c ON r.id_car=c.id_car
      	GROUP BY k.nazwisko, k.nr_prawa_jazdy, k.id_klient

----------------------------------------------------------------------------------------------------

--Utwórz wyzwalacz, który reaguje na modyfikacje danych klientów  w widoku raport - dane mają być zmodyfikowane także w tabeli klient.
--Rozwiązanie + test [3 pkt]

CREATE TRIGGER wyzwalacz2 ON raport
INSTEAD OF UPDATE
AS
BEGIN
	DECLARE kursor CURSOR FOR SELECT id_klient, nazwisko, nr_prawa_jazdy FROM inserted
	DECLARE @id_klient INT, @nazwisko VARCHAR(20), @nr_prawa_jazdy VARCHAR(13)
	OPEN kursor
	FETCH NEXT FROM kursor INTO @id_klient, @nazwisko, @nr_prawa_jazdy
	WHILE @@FETCH_STATUS=0
	BEGIN
		UPDATE klient SET nazwisko=@nazwisko, nr_prawa_jazdy=@nr_prawa_jazdy  WHERE id_klient=@id_klient
		FETCH NEXT FROM kursor INTO @id_klient, @nazwisko, @nr_prawa_jazdy
	END
	CLOSE kursor
	DEALLOCATE kursor
END;

-----------------------------------------------------------------------------------------------------

--Do tabeli klient dodaj kolumnę usunięty, z domyślną wartością 0, zadbaj o to, aby kolumna nie była pusta.
--Rozwiązanie + test  [1 pkt]:

ALTER TABLE klient
ADD usuniety INT DEFAULT 0;

-----------------------------------------------------------------------------------------------------

--Napisać wyzwalacz, który zamiast usunąć fizycznie rekord z tabeli klient zmieni
--wartość kolumny usunięty z 0 na 1,
--Rozwiązanie  + test [2 pkt]:

CREATE TRIGGER wyzwalacz on klient
INSTEAD OF DELETE
AS
BEGIN
UPDATE klient SET usuniety=1 WHERE id_klient IN (SELECT id_klient FROM DELETED)
END

_____________________________________________________________________________________________________

--Napisać funkcję pomocniczą sprawdz_pjazdy zwracającą wartość 0 jeśli numer prawa jazdy nie jest we właściwym formacie oraz 1 w przeciwnym wypadku.
-- RozwiÄ…zanie +test [2pkt]

CREATE FUNCTION spr_pojazdy(@pojazd VARCHAR(14))
RETURNS BIT
BEGIN
	IF @pojazd LIKE '[0-9][0-9][0-9][0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]'
		RETURN 1
	RETURN 0
END;

------------------------------------------------------------------------------------------------------

--Napisz wyzwalacz, ktĂłry uniemoĹĽliwi modyfikacje numerĂłw prawa jazdy na niepoprawny format. Wykorzystaj funkcjÄ™ sprawdz_pjazdy.
--RozwiÄ…zanie [2 pkt]

CREATE TRIGGER wyzwalacz3 on klient
AFTER UPDATE
AS
BEGIN
	DECLARE @nr_prawa_jazdy VARCHAR(30)
	SET @nr_prawa_jazdy=-1
	SELECT @nr_prawa_jazdy=nr_prawa_jazdy FROM inserted
	IF dbo.spr_pojazdy(@nr_prawa_jazdy)=0
    	BEGIN
	    	RAISERROR('Niepoprawny numer prawa jazdy, zly format',1,2)
			ROLLBACK
    	END
END;

------------------------------------------------------------------------------------------------------

należy uniemożliwić dodanie rekordu z niepoprawnym numerem pesel, napisz funkcję pomocniczą, wyświetl odpowiedni komunikat błędu,

CREATE FUNCTION spr_pesel (@nr_pesel VARCHAR (14))
RETURNS BIT
AS
BEGIN
	IF (ISNUMERIC(@nr_pesel)=0) OR (LEN(@nr_pesel)!=11)
	RETURN 0
	DECLARE @wagi AS TABLE ( pozycja TINYINT IDENTITY (1,1), waga TINYINT)
	INSERT INTO @wagi VALUES (1),(3),(7),(9),(1),(3),(7),(9),(1),(3),(1)
	IF (SELECT SUM(CONVERT (TINYINT, SUBSTRING (@nr_pesel,pozycja,1))*waga)%10 FROM @wagi)=0
		RETURN 1
	RETURN 0
	END

CREATE TRIGGER wyzwalacz4 on osoba
INSTEAD OF INSERT
AS
BEGIN
	DECLARE @pesel VARCHAR(30),  @nazwisko VARCHAR(30), @data_ur DATE, @pensja MONEY
	SET @pesel=-1
	SELECT @pesel=pesel FROM inserted
	IF dbo.spr_pesel(@pesel)=0
    	BEGIN
	    	RAISERROR('Niepoprawny numer prawa jazdy, zly format',1,2)
			ROLLBACK
    	END
    IF dbo.spr_pesel(@pesel)=1
    	BEGIN
    		SELECT @nazwisko=nazwisko, @data_ur=data_ur, @pensja=pensja FROM inserted
    		INSERT INTO osoba(nazwisko,pesel,data_ur,pensja) VALUES (@nazwisko,@pesel,@data_ur,@pensja)
    	END
    	END;

-------------------------------------------------------------------------------------------------------------------

w dodawanym rekordzie należy poprawić imię tak, aby zawsze się zaczynało z wielkiej litery, a pozostałe litery były małe,

CREATE TRIGGER wyzwalacz5 on osoba
INSTEAD OF INSERT
AS
BEGIN
	DECLARE @pesel VARCHAR(30),  @nazwisko VARCHAR(30), @data_ur DATE, @pensja MONEY
	SET @pesel=-1
	SELECT @nazwisko=nazwisko FROM inserted
     SELECT @data_ur=data_ur, @pensja=pensja, @pesel=pesel FROM inserted
    		INSERT INTO osoba(nazwisko,pesel,data_ur,pensja) VALUES (UPPER(LEFT(@nazwisko,1))+ LOWER(SUBSTRING(@nazwisko,2,LEN(@nazwisko))),@pesel,@data_ur,@pensja)
    		END;

----------------------------------------------------------------------------------------------------------------------

dodawana pensja nie może być mniejsza niż 1111 zł, w przeciwnym wypadku wyświetl odpowiedni komunikat błędu,

			CREATE TRIGGER wyzwalacz6 on osoba
AFTER INSERT
AS
BEGIN
	DECLARE @pensja MONEY
	SELECT  @pensja=pensja FROM inserted
	IF @pensja<1111
    	BEGIN
	    	RAISERROR('Za niska cena',1,2)
			ROLLBACK
    	END
    		END;

----------------------------------------------------------------------------------------------------------------------

dodawana osoba nie moze byc pelnoletnia

CREATE TRIGGER wyzwalacz7 on osoba
AFTER INSERT
AS
BEGIN
	DECLARE @data_ur DATE
	SELECT  @data_ur=data_ur FROM inserted
	IF DATEDIFF(yy,@data_ur,GETDATE())<18
    	BEGIN
	    	RAISERROR('Za mlody wiek',1,2)
			ROLLBACK
    	END
    		END;

----------------------------------------------------------------------------------------------------------------------

ilosc jako kwota wszystkich wypozyczen klienta

SELECT k.id_klient, k.nazwisko, k.nr_prawa_jazdy, SUM(c.cena*DATEDIFF(dd, r.data_pocz, r.data_konc )) AS ilosc
FROM klient k LEFT JOIN rezerwacja r ON k.id_klient=r.id_klient
		LEFT JOIN car c ON r.id_car=c.id_car
		GROUP BY k.nazwisko, k.nr_prawa_jazdy, k.id_klient