--DROP TABLES--
--=====================================================--
DROP TABLE IF EXISTS car_status
DROP TABLE IF EXISTS Car
DROP TABLE IF EXISTS Customer_Adress
DROP TABLE IF EXISTS Employee_Adress
DROP TABLE IF EXISTS EmployeeOrders
DROP TABLE IF EXISTS Employee
DROP TABLE IF EXISTS Products
DROP TABLE IF EXISTS Drink
DROP TABLE IF EXISTS Pizza
DROP TABLE IF EXISTS Sauce
DROP TABLE IF EXISTS size
DROP TABLE IF EXISTS Orders
DROP TABLE IF EXISTS Order_Type
DROP TABLE IF EXISTS Payment_Method
DROP TABLE IF EXISTS Customer

				--DROP FUNCTIONS--
--=====================================================--
DROP FUNCTION IF EXISTS znak
DROP FUNCTION IF EXISTS checkEmail

				--DROP TRIGGERS--
--=====================================================--
DROP TRIGGER IF EXISTS Format_Number_E
DROP TRIGGER IF EXISTS Format_Number_C
DROP TRIGGER IF EXISTS Order_PriceT
DROP TRIGGER IF EXISTS proc_OFF_BIG_PIZZA
DROP TRIGGER IF EXISTS email_checkT
DROP TRIGGER IF EXISTS exists_pizza
DROP TRIGGER IF EXISTS exists_drink
DROP TRIGGER IF EXISTS exists_sauce

				--DROP PROCEDURES--
--=====================================================--
DROP PROCEDURE IF EXISTS CREATE_ORDER
DROP PROCEDURE IF EXISTS Add_Product
DROP PROCEDURE IF EXISTS give_take_car
DROP PROCEDURE IF EXISTS salary_up
DROP PROCEDURE IF EXISTS Show_Employee_Orders

				--DROP VIEW--
--=====================================================--
DROP VIEW IF EXISTS number
DROP VIEW IF EXISTS Employee_INFO
DROP VIEW IF EXISTS Menu_pizza
DROP VIEW IF EXISTS pizza_popularity
DROP VIEW IF EXISTS drink_popularity
DROP VIEW IF EXISTS sauce_popularity
DROP VIEW IF EXISTS ORDER_INFO
DROP VIEW IF EXISTS car_info
DROP VIEW IF EXISTS DELIVERY_POPULARITY_CITY
DROP VIEW IF EXISTS Monthly_Income
DROP VIEW IF EXISTS BELOW_AVG
GO
--==========================================================================================================--
						--TABLES--

                       --EMPLOYEE TBALES--
--==========================================================--

                --EMPLOYEE--
CREATE TABLE dbo.Employee (
	Employee_ID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
	Name varchar(100) NOT NULL,
	Surname varchar(100) NOT NULL,
	Birthday date NOT NULL DEFAULT GETDATE(),
	Job_position varchar(100) NOT NULL,
	Date_hired date NOT NULL DEFAULT GETDATE(),
	PESEL varchar(11) NOT NULL UNIQUE CHECK(LEN(pesel) = 11),
	Salary MONEY NOT NULL DEFAULT (0),
	E_mail varchar(30) NULL DEFAULT (NULL),
	Phone_Nr varchar(14) NULL DEFAULT (NULL),
);

                --EMPLOYEE ADRESS--
CREATE TABLE dbo.Employee_Adress(
	Adress_ID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
	Country varchar(100) NOT NULL DEFAULT (NULL),
	City varchar(100) NOT NULL DEFAULT (NULL),
	Street_Name varchar(100) NOT NULL DEFAULT (NULL),
	Street_Number varchar(50) NOT NULL DEFAULT (NULL),
	Door_Number varchar(10) DEFAULT (NULL),

	Employee_ID int NOT NULL FOREIGN KEY REFERENCES dbo.Employee(Employee_ID) on delete cascade
);

-------------------------------------------------------------------
             --ADDITIONAL EMPLOYEE TABLES--

                --CAR--
CREATE TABLE dbo.Car (
	Car_ID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
	Brand varchar(200)  NOT NULL CHECK(LEN(brand) >=2),
	Model varchar(200)  NOT NULL CHECK(LEN(model) >=2),
	Date_bought date NOT NULL DEFAULT (GETDATE()),
	Licens_plate varchar(10)  NOT NULL UNIQUE,


	Employee_ID int NULL FOREIGN KEY REFERENCES dbo.Employee(Employee_ID) on delete set default
);

                --CAR STATUS--
CREATE TABLE dbo.car_status (
	Status_ID int NOT NULL IDENTITY(1,1),
	Accident_number int NOT NULL DEFAULT (0),
	Mileage int NOT NULL CHECK(mileage >= 0),
	Insurance_end date NOT NULL DEFAULT (NULL),

	Car_ID int NOT NULL FOREIGN KEY REFERENCES dbo.Car(Car_ID) on delete cascade,
);
--========================================================--
                --CUSTOMER TABLES--
--========================================================--

                --CUSTOMER--
Create table dbo.Customer (
	Customer_ID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
	Name varchar(200) NULL DEFAULT (NULL),
	Surname varchar(200) NULL DEFAULT (NULL),
	Phone_Nr varchar(14) NULL DEFAULT (NULL)
);

                --CUSTOMER ADRESS--
CREATE TABLE dbo.Customer_Adress(
	Adress_id int NOT NULL IDENTITY(1,1) PRIMARY KEY,
	City varchar(200) NULL DEFAULT (NULL),
	Street_Name varchar(200) NULL DEFAULT (NULL),
	Street_Number int NULL DEFAULT (NULL),
	Door_Number int NULL DEFAULT (NULL),

	Customer_ID int NOT NULL FOREIGN KEY REFERENCES dbo.Customer(Customer_ID) on delete cascade
);
--========================================================--
                --ORDERS TABLES--
--========================================================--

                --PAYMENT METHOD--
CREATE TABLE dbo.Payment_Method(
	Payment_Method_ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
	Method varchar(50) NULL DEFAULT ('cash'),
);

                --ORDER TYPE--
CREATE TABLE dbo.Order_Type(
	Order_Type_ID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
	Order_type varchar(100) NULL DEFAULT ('reastaurant'),
);

                --ORDERS--
CREATE TABLE dbo.Orders(
	Order_ID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
	Date_order date NOT NULL DEFAULT GETDATE(),
	Order_price MONEY NULL CHECK(Order_price >= 0) DEFAULT (0),

	Customer_ID int NULL DEFAULT (NULL) FOREIGN KEY REFERENCES dbo.Customer(Customer_ID) on delete set default,
	Payment_Method_ID INT NULL DEFAULT (NULL) FOREIGN KEY REFERENCES dbo.Payment_Method(Payment_Method_ID) on delete set default,
	Order_type int NULL DEFAULT (NULL) FOREIGN KEY REFERENCES dbo.Order_Type(Order_Type_ID) on delete set default,
);

                --EMPLOYEE ORDERS (BRIDGE TABLE N:N)--
CREATE TABLE dbo.EmployeeOrders(
	ID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
	Employee_ID int NULL DEFAULT (NULL) FOREIGN KEY REFERENCES dbo.Employee(Employee_ID) on delete cascade,
	Order_ID int NULL DEFAULT (NULL) FOREIGN KEY REFERENCES dbo.Orders(Order_ID) on delete cascade,
);

--========================================================--
                --PRODUCTS TABLES--
--========================================================--

                --DRINK--
CREATE TABLE dbo.Drink(
	Drink_ID varchar(10) NOT NULL PRIMARY KEY,
	Drink_Name varchar(50) NOT NULL,
	Drink_type varchar(50) NOT NULL,
	Drink_description varchar(200) NOT NULL,
	Price MONEY NULL DEFAULT (0)
);

                --PIZZA--
CREATE TABLE dbo.Pizza(
	Pizza_ID varchar(10) NOT NULL PRIMARY KEY,
	Pizza_Name varchar(50) NOT NULL,
	Toppings varchar(200) NOT NULL,
	Pizza_type varchar(50) NOT NULL
);

                --SAUCE--
CREATE TABLE dbo.Sauce(
  Sauce_ID varchar(10) NOT NULL PRIMARY KEY,
  Sauce_Name varchar(50) NOT NULL,
  Sauce_description varchar(300) NOT NULL,
  Sauce_type varchar(50) NOT NULL,
  Price MONEY NULL DEFAULT (0)
);

                --SIZE--
CREATE TABLE dbo.size(
	ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
	Size varchar(50) NOT NULL DEFAULT('Medium'),
	Size_cm int NOT NULL DEFAULT(30),
	Price MONEY NULL DEFAULT (0),
);

                --PRODUCTS--
CREATE TABLE dbo.Products(
  Product_id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,

  Pizza_ID varchar(10) NULL FOREIGN KEY REFERENCES Pizza(Pizza_ID)on delete set NULL,
  Sauce_ID varchar(10) NULL FOREIGN KEY REFERENCES Sauce(Sauce_ID)on delete set NULL,
  Drink_ID varchar(10) NULL FOREIGN KEY REFERENCES Drink(Drink_ID)on delete set NULL,
  Size_ID INT NULL FOREIGN KEY REFERENCES dbo.size(ID)on delete set NULL,
  Order_ID int NOT NULL FOREIGN KEY REFERENCES dbo.Orders(Order_ID)on delete cascade,
);
GO
--==========================================================================================================--
						--FUNCTIONS--
			--ZMIENIA FORMAT NUMERU TELEFONU--

create function znak(@znaki char(14))
returns char(14)
as
begin
	declare @a char(3);
	declare @b char(3);
	declare @c char(4);
	declare @d char(14);
	set @a =(select SUBSTRING(@znaki,1,3));
	set @b=(select substring(@znaki,4,3));
	set @c=(select SUBSTRING(@znaki,7,4));

	set @d='('+@a+')'+SPACE(1)+@b+'-'+@c;
	return @d;
end
GO

			--SPRADZ FORMAT MAILOWY--

CREATE FUNCTION checkEmail (@email VARCHAR(50))
RETURNS INT
AS
BEGIN
    IF (@email NOT LIKE '_%@__%.__%')
        RETURN 0

    RETURN 1
END
GO


--==========================================================================================================--
						--TRIGGERS--

			--FORMATOWANIE NUMERU--

CREATE TRIGGER Format_Number_E
ON dbo.Employee
FOR INSERT--update
AS
BEGIN

UPDATE Employee
SET Phone_Nr = (select dbo.znak(Phone_Nr))
WHERE Employee_ID = (SELECT Employee_ID FROM INSERTED)
END
GO

CREATE TRIGGER Format_Number_C
ON dbo.Customer
FOR INSERT
AS
BEGIN

UPDATE Customer
SET Phone_Nr = dbo.znak(Phone_Nr)
WHERE Customer_ID = (SELECT Customer_ID FROM INSERTED)
END
GO


					--AUTOMATYCZNE OBLICZNIE CENY--

CREATE TRIGGER Order_PriceT
ON dbo.Products
FOR INSERT
AS
BEGIN

DECLARE @pizza MONEY;
DECLARE @drink MONEY;
DECLARE @sauce MONEY

if (SELECT Price From Drink WHERE Drink_ID = (SELECT Drink_ID FROM INSERTED)) IS NULL
SET @drink = 0
ELSE
SET @drink = (SELECT Price From Drink WHERE Drink_ID = (SELECT Drink_ID FROM INSERTED))

if (SELECT Price From Sauce WHERE Sauce_ID = (SELECT Sauce_ID FROM INSERTED)) IS NULL
SET @sauce = 0
ELSE
SET @sauce = (SELECT Price From Sauce WHERE Sauce_ID = (SELECT Sauce_ID FROM INSERTED))

if (SELECT Price From size WHERE ID = (SELECT Size_ID FROM INSERTED)) IS NULL
SET @pizza = 0
ELSE
SET @pizza = (SELECT Price From size WHERE ID = (SELECT Size_ID FROM INSERTED))

UPDATE Orders
SET Order_price = Order_price + @drink + @sauce + @pizza
WHERE Order_ID = (SELECT Order_ID FROM INSERTED)

END
GO

			--BIG PICA promocja get two another 25% off

CREATE TRIGGER proc_OFF_BIG_PIZZA
ON dbo.Products
FOR INSERT
AS
BEGIN
DECLARE @Promo int;
DECLARE @poz INT;

SET @poz = (SELECT COUNT(*) FROM Products
WHERE Order_ID = (SELECT Order_ID From Inserted) AND Size_ID = 3)

SET @Promo = (SELECT Price FROM Size Where ID = 3) * 0.25

IF (SELECT Size_ID From Inserted) = 3 AND @poz % 2 = 0
	UPDATE Orders
	SET Order_price = Order_price - @Promo WHERE Order_ID = (SELECT Order_ID FROM INSERTED)

END
GO

			--NIEPRAWIDLOWY FORMAT EMAIL--

CREATE TRIGGER email_checkT
ON dbo.Employee
FOR INSERT
AS
BEGIN
DECLARE @CHECK varchar(40);
SET @CHECK = (SELECT E_mail from INSERTED)
IF dbo.checkEmail(@CHECK) = 0
	BEGIN
		RAISERROR('ERROR - bad E-mail format (CORRECT FORMAT: Example@mail.com)', 1, 2)
		UPDATE Employee
		SET E_mail = NULL WHERE Employee_ID = (select Employee_ID from inserted)
	END
END
GO

			--PRODUCT ALREADY EXISTS--

CREATE TRIGGER exists_pizza
ON dbo.pizza
FOR INSERT
AS
BEGIN

IF (SELECT COUNT(Pizza_Name) FROM pizza WHERE Pizza_Name = (SELECT Pizza_Name FROM INSERTED)) > 1
	BEGIN
	RAISERROR('ERROR - THIS PIZZA ALREADY EXISTS IN DATABASE', 1, 2)
	DELETE FROM Pizza WHERE Pizza_Name = (SELECT Pizza_Name FROM INSERTED)
	END

END
GO
----------
CREATE TRIGGER exists_drink
ON dbo.Drink
FOR INSERT
AS
BEGIN

IF (SELECT COUNT(Drink_Name) FROM Drink WHERE Drink_Name = (SELECT Drink_Name FROM INSERTED)) > 1
	BEGIN
	RAISERROR('ERROR - THIS DRINK ALREADY EXISTS IN DATABASE', 1, 2)
	DELETE FROM Drink WHERE Drink_Name = (SELECT Drink_Name FROM INSERTED)
	END

END
GO
----------
CREATE TRIGGER exists_sauce
ON dbo.Sauce
FOR INSERT
AS
BEGIN

IF (SELECT COUNT(Sauce_Name) FROM Sauce WHERE Sauce_Name = (SELECT Sauce_Name FROM INSERTED)) > 1
	BEGIN
	RAISERROR('ERROR - THIS SAUCE ALREADY EXISTS IN DATABASE', 1, 2)
	DELETE FROM Sauce WHERE Sauce_Name = (SELECT Sauce_Name FROM INSERTED)
	END

END
GO

--==========================================================================================================--
						--PROCEDURES--

					--TWORZENIE ZAMOWIENIA--

CREATE PROCEDURE dbo.CREATE_ORDER
	@Order_type int,
	@Employee_1 int, @Employee_2 int,@Payment_Method_ID int,
	@Name varchar(100), @Surname varchar(100),@Phone_Nr int,
	@City varchar(100), @Street_Name varchar(100), @Street_Number int, @Door_Number int
AS
BEGIN
	INSERT INTO Customer(Name,Surname,phone_Nr) VALUES(@Name,@Surname,@Phone_Nr)

	DECLARE @Customer_ID int
	SET @Customer_ID = (SELECT TOP 1 Customer_ID From Customer ORDER BY Customer_ID DESC)

	INSERT INTO Customer_Adress(City,Street_Name,Street_Number,Door_Number,Customer_ID)
		VALUES(@City,@Street_Name,@Street_Number,@Door_Number,@Customer_ID)

	INSERT INTO Orders(Customer_ID,Payment_Method_ID,Order_type)
		Values(@Customer_ID,@Payment_Method_ID,@Order_type)

	DECLARE @Order_ID int;
	SET @Order_ID = (SELECT TOP 1 ORDER_ID From Orders ORDER BY Order_ID DESC)
	INSERT INTO EmployeeOrders(Employee_ID,Order_ID)
		VALUES(@Employee_1,@Order_ID)
	INSERT INTO EmployeeOrders(Employee_ID,Order_ID)
		VALUES(@Employee_2,@Order_ID)
END
GO


						--DODAJ PRODUKT/Y DO ZAMOWIENIA--

CREATE PROCEDURE dbo.Add_Product
	@Order_ID int,
	@Pizza_ID varchar(10),
	@Sauce_ID varchar(10),
	@Drink_ID varchar(10),
	@Size_ID int
AS
insert into Products(Order_ID,Pizza_ID,Sauce_ID,Drink_ID,Size_ID) VALUES(@Order_ID,@Pizza_ID,@Sauce_ID,@Drink_ID,@Size_ID)
GO


						--DAWANIE I ODEJMOWANIE SAMOCHODA PRACOWNIKU--

CREATE PROCEDURE dbo.give_take_car
@Employee_ID int,
@Car_ID int
AS
BEGIN
if (select Employee_ID from Car where car_ID=@Car_ID) = @Employee_ID
UPDATE Car
SET Employee_ID = NULL
WHERE car_ID = @Car_ID

else
UPDATE Car
SET Employee_ID = @Employee_ID
WHERE car_ID = @Car_ID
END
GO


						--DAWANIE PREMII PRACOWNIKU--

CREATE PROCEDURE salary_up
@Employee_ID int,
@salary money
AS
IF @salary < 0
	RAISERROR ('Wrong argument with salary',1,2);
else
Update Employee set salary = salary + @salary
WHERE Employee_ID = @Employee_ID;
GO

						--POKAZ WSZYSTKIE ZAMOWIENIA WYPELNIONE PRZEZ EMPLOYEE_ID--

CREATE PROCEDURE Show_Employee_Orders
@Employee_ID int
AS
SELECT Employee_ID,Name,Surname,Job_position,Date_hired,E_mail,Phone_Nr,Salary FROM Employee
WHERE Employee_ID = @Employee_ID

SELECT o.Order_ID,o.Date_order, o.Customer_ID,o.Payment_Method_ID,o.Order_Type,o.Order_Price FROM Orders as o
JOIN EmployeeOrders as eo ON o.Order_ID=eo.Order_ID
WHERE eo.Employee_ID = @Employee_ID
GO

--==========================================================================================================--
							--VIEWS--

				--INFORMACJA O ROBOTNIKACH--

CREATE VIEW number
AS
select e.Employee_ID,count(e.Employee_ID) as number from Employee as e
INNER JOIN EmployeeOrders as o ON e.Employee_ID = o.Employee_ID
Group by e.Employee_ID
GO

CREATE VIEW Employee_INFO
AS

SELECT TOP 100 PERCENT e.Employee_ID, e.Name, e.Surname, e.Birthday, e.Job_position, e.Date_hired, e.PESEL, e.Salary, e.E_mail, e.Phone_Nr,
ea.Country, ea.City, ea.Street_Name, ea.Street_Number, ea.Door_Number, n.number
FROM Employee as e JOIN Employee_Adress as ea
ON e.Employee_ID = ea.Employee_ID
LEFT JOIN number as n ON e.Employee_ID = n.Employee_ID
GROUP BY e.Employee_ID, e.Name, e.Surname, e.Birthday, e.Job_position, e.Date_hired, e.PESEL, e.Salary, e.E_mail, e.Phone_Nr,
ea.Country, ea.City, ea.Street_Name, ea.Street_Number, ea.Door_Number,n.number
ORDER BY e.Employee_ID ASC

GO


				--PIZZA MENU--

CREATE VIEW Menu_pizza
AS
select Pizza_ID,Pizza_Name, Toppings,Pizza_Type,
(select price as '20 cm' from size where id = 1) as 'Price of Small',
(select price as '20 cm' from size where id = 2) as 'Price of Medium',
(select price as '20 cm' from size where id = 3) as 'Price of Large'
FROM pizza

GO

		--POPULAR PIZZA--

CREATE VIEW pizza_popularity
AS
SELECT TOP 100 PERCENT p.Pizza_ID,p.Pizza_Name,p.Toppings,p.Pizza_Type,
(select count(pa.Pizza_ID) from products as pa where pa.Pizza_ID = p.Pizza_ID) as 'Pizza number'
FROM Pizza as p
ORDER BY 'Pizza number' DESC
GO

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

CREATE VIEW drink_popularity
AS
SELECT TOP 100 PERCENT d.Drink_ID,d.Drink_Name,d.Drink_type,d.Drink_description,d.Price,
(select count(pa.Drink_ID) from products as pa where pa.Drink_ID = d.Drink_ID) as 'Drink number'
FROM Drink as d
ORDER BY 'Drink number' DESC
GO

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

CREATE VIEW sauce_popularity
AS
SELECT TOP 100 PERCENT s.Sauce_ID,s.Sauce_Name,s.Sauce_type,s.Sauce_description,s.Price,
(select count(pa.Sauce_ID) from products as pa where pa.Sauce_ID = s.sauce_ID) as 'Sauce number'
FROM sauce as s
ORDER BY 'Sauce number' DESC
GO

				--ORDER INFO--

CREATE VIEW ORDER_INFO
AS
select TOP 100 PERCENT o.Order_ID, o.Date_order, o.Order_price, o.Customer_ID,
e.Employee_ID,e.Name, e.Surname, pm.Method, ot.Order_type,
(select count(pa.Pizza_ID) from products as pa where pa.Order_ID = o.Order_ID) as 'Pizza count',
(select count(p.Drink_ID) from products as p where p.Order_ID = o.Order_ID) as 'Drink count',
(select count(s.sauce_ID) from products as s where s.Order_ID = o.Order_ID) as 'Sauce count'
from orders as o
join EmployeeOrders as eo on eo.Order_ID=o.Order_ID
join Employee as e on e.Employee_ID = eo.Employee_ID
join Payment_Method as pm on pm.Payment_Method_ID = o.Payment_Method_ID
join Order_Type as ot on o.Order_type = ot.Order_Type_ID
ORDER BY Order_ID DESC
GO


				--CAR INFO--

CREATE VIEW car_info
AS
select c.car_ID, c.brand, c.model, c.date_bought, c.licens_plate, cs.Accident_number,cs.mileage,cs.insurance_end,e.Employee_ID, e.Name, e.Surname from Car as c
left join Employee as e on c.Employee_ID = e.Employee_ID
join car_status as cs on c.car_ID = cs.car_ID
GO

			--NAJPOPULARNIEJSZE MIASTA DOSTAWY--

CREATE VIEW DELIVERY_POPULARITY_CITY
AS
select TOP 100 PERCENT Street_Name, count(Street_Name) as 'Number of Orders' FROM Customer_Adress
WHERE Street_Name IS NOT NULL
Group by Street_Name
ORDER BY 'Number of Orders' DESC
GO

			--DOCHOD ZA MIESIAC--

CREATE VIEW Monthly_Income
AS
SELECT TOP 100 PERCENT Date_Order, SUM(Order_price) as 'Income' FROM Orders
GROUP BY Date_Order
ORDER BY Date_Order ASC
GO

			--OSOBY KTORE ZARABIAJA MNIEJ NIZ SREDNIA--

CREATE VIEW BELOW_AVG
AS
SELECT TOP 100 PERCENT Employee_ID,Name,Surname,Birthday,Job_position,Date_Hired,PESEL,Salary FROM Employee
GROUP BY Employee_ID,Name,Surname,Birthday,Job_position,Date_Hired,PESEL,Salary
HAVING salary < (select avg(salary) from Employee)
ORDER BY Salary DESC
GO

--==========================================================================================================--
						--INSERT TO DATABASE--

				    --EMPLOYEE--
insert into dbo.Employee values('Dariusz','Rodzewicz','1998-12-18','Piekarz','2013-05-15','20054788612',120,'DRodzewicz@mgmail.com','861214043')
insert into dbo.Employee values('Robert','Radulewicz','1998-04-07','Piekarz','2013-05-15','15937846132',600,'RobRAdulew@gmail.com','861234557')
insert into dbo.Employee values('Lukasz','Dowgialo','1997-06-01','Kurier','2013-05-15','98712314785',1000,'DowgiaLukas@gmail.com','86856984')
insert into dbo.Employee values('Ryszard','Granicki','1997-06-28','Piekarz','2013-05-15','65412398752',800,'RysiukGran@gmail.com','861523698')
insert into dbo.Employee values('Lukasz','Baniukiewicz','1998-12-18','Kurier','2013-05-15','12589637415',1200,'Baniuk1999@gmail.com','86141586')
insert into dbo.Employee values('Grzegorz','Wojtkiewicz','1999-01-16','Kasier','2013-05-15','12356987452',450,'WojtGrisha@gmail.com','861740012')
insert into dbo.Employee values('Andrzej','Tichonow','1986-03-27','Piekarz','2013-05-15','20054788614',2000,'Lenkas89@gmail.com','861115225')
insert into dbo.Employee values('Julia','Stanulyte','1998-10-11','Piekarz','2013-05-15','20111552386',600,'HarcJulia@gmail.com','861158996')
insert into dbo.Employee values('Adriana','Wiszniewska','1996-08-22','Kasier','2013-05-15','12000254550',850,'MilionAdri@gmail.com','8614311258')
insert into dbo.Employee values('Barbara','Zinkiewicz','1998-02-28','Kurier','2013-05-15','25555521587',775,'BasiutaZ@gmail.com','8645558555')
--select * from Employee

                    --EMPLOYEE ADRESS--
insert into dbo.Employee_Adress values('Lithuania','Vilnius','Erfuro','122','44',1)
insert into dbo.Employee_Adress values('Poland','Gdansk','Pomorska','44',NULL,2)
insert into dbo.Employee_Adress values('Poland','Gdansk','3-Maja','144','75',3)
insert into dbo.Employee_Adress values('Poland','Gdynia','Armii_Krajowej','98','B',4)
insert into dbo.Employee_Adress values('Poland','Sopot','Bitwy_Pod_Plowcami','64','209',5)
insert into dbo.Employee_Adress values('Poland','Sopot','Bitwy_Pod_Plowcami','64','209',6)
insert into dbo.Employee_Adress values('Poland','Gdansk','3-Maja','75',NULL,7)
insert into dbo.Employee_Adress values('Poland','Gdynia','Mickiewicza','47','65',8)
insert into dbo.Employee_Adress values('Belarus','Grodno','Zapackaja','44','56',9)
insert into dbo.Employee_Adress values('Poland','Gdansk','Reja','69','A',10)
--select * from Employee_Adress

                    --CAR--
insert into dbo.Car values('Toyota','Auris','2014-08-15','GDN-45378',NULL),
						  ('Opel','Astra','2015-09-24','GDN-00218',NULL),
						  ('Volkswagen','Golf','2014-10-02','GDN-95734',NULL);
--select * from Car

                    --CAR STATUS--
insert into dbo.car_status values(0,150000,'2020-12-15',1),
								 (1,192025,'2020-12-15',2),
								 (0,134008,'2020-12-15',3);
--select * from car_status

                    --PAYMENT METHOD--
insert into dbo.Payment_Method values('cash'),
									 ('credit'),
									 ('PayPal'),
									 ('Bank Transfer');
--select * from Payment_Method

                    --ORDER TYPE--
insert into dbo.Order_Type values('Restaurant'),
								 ('Take-Away'),
								 ('Delivery');
--select * from Order_Type

                    --PIZZA SIZE--
insert into dbo.size values('Small',25,20),
						   ('Medium',30,26),
						   ('Large',40,34);
--select * from size

                    --SAUCE--
insert into dbo.Sauce values('CZWY','Czosnakowy','Nie ma','lagodny',2)
insert into dbo.Sauce values('POWY','Pomidorowy','Nie ma','lagodny',1.5)
insert into dbo.Sauce values('ARSKI','Arabski','Nie ma','ostry',2)
insert into dbo.Sauce values('MIWY','Miodowy','Nie ma','slodki',2)
insert into dbo.Sauce values('MUWY','Musztardowy','Nie ma','Gorzki',1.5)
insert into dbo.Sauce values('PIWY','Piepszowy','Nie ma','Gorzki',2)
--select * from Sauce

                    --PIZZA--
insert into dbo.Pizza values('AlNO','Albano','ser, oregano, papryka, boczek, szynka, cebula','tradycyjna')
insert into dbo.Pizza values('CASA','Calabresa','ser, oregano, salami, oliwki, papryczki apalenos, tabasco','ostra')
insert into dbo.Pizza values('MACO','Marco','ser, oregano, salami, pieczarki','tradycyjna')
insert into dbo.Pizza values('BAMA','Bahama','ser,oregano, szynka, ananas','slodka')
insert into dbo.Pizza values('MATA','Margherita','ser, oregano','tradycyjna')
insert into dbo.Pizza values('FONA','Foggiana',' ser, oregano, pieczarki','tradycyjna')
insert into dbo.Pizza values('ONE','1x1','sos, ser, oregano, szynka','tradycyjna')
insert into dbo.Pizza values('PORA','Povera','sos, ser, oregano, szynka, pieczarki','tradycyjna')
insert into dbo.Pizza values('UNDO','Un Ricordo','sos, ser, oregano, szynka, kukurydza','tradycyjna')
insert into dbo.Pizza values('UUGI','Quatro Formaggi','sos, ser, oregano, ser feta, ser pleśniowy, ser gouda','serowa')
--select* from Pizza

                    --DRIKNS--
insert into dbo.Drink values('CZHE','Czarna Herbata','Herbata','aroma',4)
insert into dbo.Drink values('ZIHE','Zielona Herbata','Herbata','aroma',4)
insert into dbo.Drink values('COLA','Co-Ca-Cola','gazowane','smczna',5)
insert into dbo.Drink values('SPTE','Sprite','gazowane','smczna',5)
insert into dbo.Drink values('FATA','Fanta','gazowane','smczna',5)
--select* from Drink

--==========================================================================================================--
								--TESTOWANIE--
							---------------------
								--TRIGGERS--

			--Format_Number_E--
--korzystamy z funkcji formatowania numeru
insert into dbo.Employee values('Test','Testowy','2010-10-10','Piekarz','2013-05-15','00000000142',120,'DRodzewicz@mgmail.com','861214043')
insert into dbo.Employee_Adress values('Poland','Gdansk','Reja','69','A',11)
SELECT *FROM EMPLOYEE WHERE Employee_ID = 11

			--Format_Number_C--
--korzystamy z funkcji formatowania numeru
INSERT INTO Customer Values('Tester','Testowy','814523654')
SELECT * FROM Customer
DELETE FROM Customer WHere Name = 'Tester'

			--Order_PriceT--
--oblicza za nas cene calego zamowienia (pokaze to w sekcji procedury przy dodaniu produktow do zamowienia)

			--proc_OFF_BIG_PIZZA--
--przy zamowieniu parzystej pary Duzych pic za druga pice placisz o 25% mniej (pokaze to w sekcji procedury przy dodaniu produktow do zamowienia)

			--email_checkT--
--korzystamy z funkcji sprawdzania formatu E-mail
insert into dbo.Employee values('Test2','Testowy2','2010-10-10','Piekarz','2013-05-15','00000000131',120,'DRodzewiczmgmail.com','861214043')
insert into dbo.Employee_Adress values('Poland','Gdansk','Reja','69','A',11)
SELECT *FROM EMPLOYEE WHERE Employee_ID = 11
UPDATE dbo.Employee SET E_mail = 'example@mail.com' WHERE Employee_ID = 11

			--exists_pizza--
insert into dbo.Pizza values('A123','Albano','ser, oregano, papryka, boczek, szynka, cebula','tradycyjna')
			--exists_drink--
insert into dbo.Drink values('CZ12','Czarna Herbata','Herbata','aroma',4)
			-- exists_sauce--
insert into dbo.Sauce values('CZ14','Czosnakowy','Nie ma','lagodny',2)

							---------------------
								--PROCEDURES--

			--CREATE_ORDER--

select * from Order_Type
select * from Employee
select * from Payment_Method
select * from Orders

EXEC CREATE_ORDER 3,2,3,2,'Test','TEstowy','124253789','Gdansk','Barzynskiego',64,122
--

			--Add_Product--

select * FROM pizza
select * from Drink
select * from sauce
select * from size
select * from products

EXEC Add_Product 1,'CASA','MIWY','COLA',3
EXEC Add_Product 1,'FONA',NULL,NULL,3
--34+34-8.5+2+5=66.5

			--give_take_car--

SELECT * FROM Employee
SELECT * FROM Car

EXEC give_take_car 5,2

			--salary_up--

SELECT * FROM Employee

EXEC salary_up 1,80

			--Show_Employee_Orders--
exec Show_Employee_Orders 2

------------------------------------------------------------------TWORZYMY ZAMOIENIA DO PRZYKLADU NA WIDOKI
EXEC CREATE_ORDER 3,2,3,2,'Test2','TEstowy2','124253789','Gdansk','Barzynskiego',64,122
EXEC Add_Product 2,'MATA','MIWY','COLA',3
EXEC Add_Product 2,'CASA',NULL,'COLA',1

EXEC CREATE_ORDER 3,5,7,2,'Test3','TEstowy3','124253789','Gdansk','Zaspa',64,NULL
EXEC Add_Product 3,'MATA','POWY','FATA',3
EXEC Add_Product 3,'CASA',NULL,NULL,2
EXEC Add_Product 3,'CASA',NULL,NULL,2

EXEC CREATE_ORDER 3,5,7,2,'Test4','TEstowy4','124253789','Gdansk','Zaspa',64,NULL
EXEC Add_Product 4,'MATA','MUWY','FATA',3
EXEC Add_Product 4,'CASA',NULL,NULL,1
EXEC Add_Product 4,'CASA',NULL,'',2

EXEC CREATE_ORDER 3,5,7,2,'Test5','TEstowy5','124253789','SOPOT','MONCIAK',22,47
EXEC Add_Product 5,'FONA','MUWY','FATA',3
EXEC Add_Product 5,'FONA',NULL,NULL,1
EXEC Add_Product 5,'FONA',NULL,NULL,2

EXEC CREATE_ORDER 3,10,7,3,'Test6','TEstowy6','124253789','SOPOT','3-maja',22,47
EXEC Add_Product 6,'FONA','MUWY','SPTE',2

EXEC CREATE_ORDER 1,10,9,3,NULL,NULL,NULL,NULL,NULL,NULL,NULL
EXEC Add_Product 7,'FONA','MUWY','SPTE',2

EXEC CREATE_ORDER 1,8,9,3,NULL,NULL,NULL,NULL,NULL,NULL,NULL
EXEC Add_Product 8,'FONA','MUWY','SPTE',1

EXEC CREATE_ORDER 2,8,3,3,'TEST9','Testowy9','861214753',NULL,NULL,NULL,NULL
EXEC Add_Product 9,'PORA','MUWY','SPTE',1
EXEC Add_Product 9,'PORA',NULL,NULL,2
EXEC Add_Product 9,'PORA',NULL,NULL,2

							---------------------
								--VIEWS--

			--Employee_INFO--
SELECT * from Employee_INFO
			--Menu_pizza--
SELECT * FROM Menu_pizza
			--pizza_popularity--
SELECT * FROM pizza_popularity
			--drink_popularity--
SELECT * FROM drink_popularity
			--sauce_popularity--
SELECT * FROM sauce_popularity
			--ORDER_INFO--
SELECT * FROM ORDER_INFO
			--car_info--
SELECT * FROM car_info
			--DELIVERY_POPULARITY_CITY--
SELECT * FROM DELIVERY_POPULARITY_CITY
			--Monthly_Income--
SELECT * FROM Monthly_Income
			--BELOW_AVG--
SELECT * FROM BELOW_AVG