#This trigger updates the inventory after an item is sold
DELIMITER //
CREATE TRIGGER InventoryStockUpdate BEFORE INSERT ON Order_Item 
	FOR EACH ROW
	BEGIN
	UPDATE Inventory
	SET Stock = Stock - new.OrderQuantity
    WHERE ProductID = new.ProductID;    
  END//
  delimiter ;

#This trigger loads missing information when a logged in user creates an order
DELIMITER //
CREATE TRIGGER LoadDefaultOrderData BEFORE INSERT ON Order_Table FOR EACH ROW
BEGIN
  IF NEW.OrderFirstName IS NULL THEN
    SET NEW.OrderFirstName = (SELECT UserFirstName FROM User WHERE User.ID = NEW.UserID);
  END IF;
  IF NEW.OrderLastName IS NULL THEN
      SET NEW.OrderLastName = (SELECT UserLastName FROM User WHERE User.ID = NEW.UserID);
  END IF;
    IF NEW.OrderMobile IS NULL THEN
      SET NEW.OrderMobile = (SELECT UserPhone FROM User WHERE User.ID = NEW.UserID);
  END IF;
    IF NEW.OrderEmail IS NULL THEN
      SET NEW.OrderEmail = (SELECT UserEmail FROM User WHERE User.ID = NEW.UserID);
  END IF;
    IF NEW.OrderCompanyName IS NULL THEN
      SET NEW.OrderCompanyName = (SELECT UserCompanyName FROM User WHERE User.ID = NEW.UserID);
  END IF;
    IF NEW.OrderLine1 IS NULL THEN
      SET NEW.OrderLine1 = (SELECT AddressLine1 FROM User_Address WHERE User_Address.UserID = NEW.UserID);
  END IF;
    IF NEW.OrderLine2 IS NULL THEN
      SET NEW.OrderLine2 = (SELECT AddressLine2 FROM User_Address WHERE User_Address.UserID = NEW.UserID);
  END IF;
    IF NEW.OrderCity IS NULL THEN
      SET NEW.OrderCity = (SELECT AddressCity FROM User_Address WHERE User_Address.UserID = NEW.UserID);
  END IF;
      IF NEW.OrderPostalCode IS NULL THEN
      SET NEW.OrderPostalCode := (SELECT AddressPostalCode FROM User_Address WHERE User_Address.UserID = NEW.UserID);
  END IF;    
	IF NEW.OrderCountry IS NULL THEN
      SET NEW.OrderCountry = (SELECT AddressCountry FROM User_Address WHERE User_Address.UserID = NEW.UserID);
  END IF;
  END//
  delimiter ;

#This trigger loads missing information when a logged in user creates a cart 
delimiter //
CREATE TRIGGER LoadDefaultCartData BEFORE INSERT ON Cart FOR EACH ROW
 BEGIN
  IF NEW.CartFirstName IS NULL THEN
    SET NEW.CartFirstName := (SELECT UserFirstName FROM User WHERE User.ID = new.UserID);
  END IF;
  IF NEW.CartLastName IS NULL THEN
      SET NEW.CartLastName := (SELECT UserLastName FROM User WHERE User.ID = new.UserID);
  END IF;
    IF NEW.CartMobile IS NULL THEN
      SET NEW.CartMobile := (SELECT UserPhone FROM User WHERE User.ID = new.UserID);
  END IF;
    IF NEW.CartEmail IS NULL THEN
      SET NEW.CartEmail := (SELECT UserEmail FROM User WHERE User.ID = new.UserID);
  END IF;
    IF NEW.CartCompanyName IS NULL THEN
      SET NEW.CartCompanyName := (SELECT UserCompanyName FROM User WHERE User.ID = new.UserID);
  END IF;
    IF NEW.CartLine1 IS NULL THEN
      SET NEW.CartLine1 := (SELECT AddressLine1 FROM User_Address WHERE User_Address.UserID = new.UserID);
  END IF;
    IF NEW.CartLine2 IS NULL THEN
      SET NEW.CartLine2 := (SELECT AddressLine2 FROM User_Address WHERE User_Address.UserID = new.UserID);
  END IF;
    IF NEW.CartCity IS NULL THEN
      SET NEW.CartCity := (SELECT AddressCity FROM User_Address WHERE User_Address.UserID = new.UserID);
  END IF;
      IF NEW.CartPostalCode IS NULL THEN
      SET NEW.CartPostalCode := (SELECT AddressPostalCode FROM User_Address WHERE User_Address.UserID = new.UserID);
  END IF;    
	IF NEW.CartCountry IS NULL THEN
      SET NEW.CartCountry := (SELECT AddressCountry FROM User_Address WHERE User_Address.UserID = new.UserID);
  END IF;
  END//

#This trigger loads the price of items when an order is created
DELIMITER //
CREATE TRIGGER InsertItemPriceOrder BEFORE INSERT ON Order_Item FOR EACH ROW
  BEGIN
  IF new.OrderQuantity IS NOT NULL THEN 
  SET new.ItemPrice = (new.OrderQuantity * (SELECT ProductPrice FROM Product WHERE Product.ID = new.ProductID));
  END IF;
  END//
  delimiter ;

#This trigger loads the price of items when a cart is updated
DELIMITER //
CREATE TRIGGER InsertItemPriceCart BEFORE INSERT ON Cart_Item FOR EACH ROW
  BEGIN
  IF new.CartQuantity IS NOT NULL THEN 
  SET new.CartPrice = (new.CartQuantity * (SELECT ProductPrice FROM Product WHERE Product.ID = new.ProductID));
  END IF;
  END//
  delimiter ;

#This trigger calculates the total price of an order
DELIMITER //
CREATE TRIGGER CalculateOrderTotal BEFORE UPDATE ON Order_Table FOR EACH ROW
  BEGIN
  SET new.Subtotal = (SELECT SUM(ItemPrice) FROM Order_Item WHERE OrderID = new.ID);
  SET new.Tax = (new.Subtotal * 0.21);
  SET new.GrandTotal = (new.Subtotal + new.Tax);
  END//
  delimiter ;
  
#This trigger calculates the price of an items in cart without tax
DELIMITER //
CREATE TRIGGER CalculateCartTotal BEFORE Update ON Cart FOR EACH ROW
  BEGIN
  SET new.CartSubtotal = (SELECT SUM(CartPrice) FROM Cart_Item WHERE Cart_Item.CartID = new.ID);
  END//
  delimiter ;


INSERT User(		UserFirstName,
					UserLastName,
					UserPhone,
					UserEmail,
					PassHash,
					UserCompanyName,
					UserDescription) VALUES
('Anoop', 'Rhona', '88005553535', 'Anoopr@gmailc.com', '5994471abb01112afcc18159f6cc74b4f511', 'MSD', 'Intern'),
('Onaop', 'Nahor', '88005553536', 'OnaopN@@gmailc.com', '5994471abb01112afcc18159f6cc74b4f511b9', 'Blizzard', 'Software developer'),
('Oonap', 'Anorh', '88005553537', 'OonapA@gmailc.com', '5994471abb01112afcc18159', 'Activision', 'Software developer'),
('Nopoa', 'Norah', '88005553538', 'NopoaN@gmailc.com', '5994471abb01112a', 'Partners', 'Data Analytic'),
('Anopa', 'Hrano', '88005553539', 'AnopaH@gmailc.com', '5994471abb0111', 'CZU', 'Student'),
('Emma', 'Davis', '12025550176', 'emma.davis@gmail.com', '5994471abb01112', 'Microsoft', 'Software developer'),
('Alexander', 'Wilson', '49305550111', 'alexander.wilson@gmail.com', '5994471abb01112afcc18159f6cc', 'Blizzard', 'Representative'),
('Benjamin', 'Lee', '4202225550133', 'benjamin.lee@gmail.com', '5994471abb01112afcc18159f6cc74', 'Partners', 'Head of Security Division'),
('Sophia', 'Hernandez', '4202125550144', 'sophia.hernandez@gmail.com', '5994471abb01112afcc18159f6cc74b', 'CZU', 'Student'),
('Benjamin', 'Lee', '4202225550133', 'sophia.hernandez@gmail.com', '5994471abb01112afcc18159f6cc74b4f5', 'Partners', 'Intern');

INSERT User_Paydetails(UserID,
					UserPaymentType,
					UserAccount,
					Expiry,
					UserProvider) VALUES
(1, 'Payment', '88888888888/0811', "2023-02-26", 'KB'),
(1, 'Payment', '88888888888/0891', "2023-02-26", 'KB'),
(2, 'Payment', '77777777777/0000', "2023-02-26", 'KB'),
(2, 'Payment', '77777777777/0800', "2023-02-26", 'KB'),
(3, 'Payment', '66666666666/0821', "2023-02-26", 'KB'),
(4, 'Payment', '88888888888/0831', "2023-02-26", 'KB'),
(5, 'Payment', '88888888888/0812', "2023-02-26", 'KB'),
(5, 'Payment', '88888887888/0812', "2023-02-26", 'KB');

INSERT User_Address(UserID,
					AddressLine1,
					AddressLine2,
					AddressCity,
					AddressPostalCode,
					AddressCountry,
					AddressLandline) VALUES
(1, 'V luhu', '15', 'Prague', '12800', 'Czech Republic', '4203218885'),
(2, 'Am Wriezener', 'bhf', 'Berlin','10243', 'Germany', '395648886'),
(3, 'Janey Way', '1211', 'Sacramento','95819', 'USA', '12232887'),
(4, 'Svornosti', '3321', 'Prague', '15000', 'Czech Republic', '4208848888'),
(5, 'Kamýcká', '129', 'Pregue', '16500', 'Czech Republic', '4203138889');

INSERT Category(	CategoryTitle) VALUES
('Alarms'),
('Car products'),
('Smart control'),
('Ventilation, cooling and heating'),
('Respiratory monitor');


INSERT Product(	
ProductTitle,
ProductPrice,
ProductSummary,
ProductDescription) VALUES
(
'The JABLOTRON 100+ alarm system',
1002.4,
'Real safety for your entire family and company',
'Make your apartment, home or company a safer place and enjoy a feeling of true peace. The JABLOTRON 100+ alarm will protect your property as well as turn on the lights, open your garage door, open the barrier, control the blinds or start an irrigation system. It’s also possible to control it remotely thanks to the MyJABLOTRON app.'
),
(
'Camera system subscription - live',
300,
'Protecting what matters to you',
'The cameras expand your security options. Thanks to the MyJABLOTRON application, your property can be fully monitored without having to constantly watch the phone. The system automatically generates a one minute recording recording of every event from the JABLOTRON 100+ alarm thus providing an instant overview.'
),
(
'Camera system subscription - live+',
420,
'Protecting what matters to you',
'The cameras expand your security options. Thanks to the MyJABLOTRON application, your property can be fully monitored without having to constantly watch the phone. The system automatically generates a one minute recording recording of every event from the JABLOTRON 100+ alarm thus providing an instant overview.'
),
(
'Camera system subscription - RECORD3',
450,
'Protecting what matters to you',
'The cameras expand your security options. Thanks to the MyJABLOTRON application, your property can be fully monitored without having to constantly watch the phone. The system automatically generates a one minute recording recording of every event from the JABLOTRON 100+ alarm thus providing an instant overview.'
),
(
'Camera system subscription - RECORD7',
600,
'Protecting what matters to you',
'The cameras expand your security options. Thanks to the MyJABLOTRON application, your property can be fully monitored without having to constantly watch the phone. The system automatically generates a one minute recording recording of every event from the JABLOTRON 100+ alarm thus providing an instant overview.'
),
(
'GSM/GPS CA-2103 car alarm',
10000,
'Secure your vehicles',
'Enjoy the comfort of always knowing where you left your car. You can also use the MyJABLOTRON app which will show you the current location of your vehicle, mileage or fuel consumption. Our alarm can protect not only cars, but also caravans, lorries and agriculture machines.'
),
(
'CU-07A and CU-08 monitoring devices',
12000,
'Protecting what matters to you',
'Control the location, fuel consumption and time schedule of your vehicles. You can easily monitor your vehicles and keep a logbook thanks to the MyJABLOTRON app.'
),
(
'Control modules for the JABLOTRON 100+ alarm system',
14000,
'Your home will listen to you',
'The JABLOTRON 100+ alarm doesn’t just take care of your security. It does so much more. Add control modules and get a smart home controlled through the MyJABLOTRON app. You’ll be able to manage everything remotely, from turning on the heating to opening the garage door.'
),
(
'GSM communicator and controller',
9000,
'Secure your summerhouse from afar',
'Know about everything that’s going on at your summerhouse. Our communicator and controller can remotely heat water in the boiler and at the same time warn you in the case of imminent fire or water damage. All this can be achieved through the MyJABLOTRON app or SMS.'
),
(
'BM02 NANNY Baby Monitor',
25000,
'The Nanny breath monitor will look after your little one',
'The Nanny baby monitor can call for help. You can sleep peacefully knowing that our device will monitor the breathing of your baby and will immediately — acoustically and visually — warn you in the case of an emergency. Nanny is a medically certified device frequently used in hospitals.'
),
(
'FUTURA',
20000,
'Heat and moisture recovery',
'In today’s market, there are many high-quality ventilation units and you are free to compare them. We neither make overstated claims nor provide a magic box. We simply use the laws of physics to ensure effortless control of your indoor air quality. This is what makes our Jablotron Futura energy recovery unit different in so many ways.'
),
(
'COOLBREEZE',
17594,
'CoolBreeze cooling / heating module',
'In reverse mode, CoolBreeze can cover 50-70 % of the heat demand of a low-energy house. It is therefore very efficient way of heating, especially in combination with additional electric radiant heating (hot water or direct heating).'
),
(
'VARIOBREEZE',
19999,
'Ventilation optimization. Up to 50% less power consumption.',
'VarioBreeze™ neural network algorithm supplies fresh air where and when it is needed.'
),
(
'ALFA',
20000,
'E-ink is soothing to my eyes',
'I can see all the information perfectly just as printed on a paper. Unique e-ink technology offers high contrast and great visibility even in direct sunlight'
);


INSERT Inventory(	Stock, ProductID) VALUES
(70, 1),
(58, 2),
(60, 3),
(11, 4),
(64, 5),
(55, 6),
(33, 7),
(22, 8),
(57, 9),
(96, 10),
(58, 11);


INSERT Product_Category(ProductID,
					CategoryID) VALUES
(1, 1),
(2, 1),
(3, 1),
(4, 1),
(5, 1),
(6, 2),
(7, 2),
(8, 3),
(9, 3),
(10, 5),
(11, 4),
(12, 4),
(13, 4),
(14, 4);

INSERT Cart(UserID,
			CartSubtotal,
			CartCompanyName) VALUES
(4, 50000.0, 'Blizzard'),
(3, 55000.0 , 'CZU');


INSERT Cart_Item(	ProductID,
					CartID,
					CartQuantity) VALUES
(11, 2, 8),
(12, 2, 10);


INSERT Order_Table(		UserID,
					Subtotal,
					Tax,
					GrandTotal,
					OrderCompanyName,
					OrderFirstName,
					OrderLastName,
					OrderMobile,
					OrderEmail,
					OrderLine1,
					OrderLine2,
					OrderPostalCode,
					OrderCity,
					OrderCountry) VALUES
(1, 160000, 33600, 193600, 'MSD', 'Alice', 'Johnson', '88005553535', 'alice.johnson@gmail.com', 'V luhu', '15', '12800', 'Prague', 'Czech Republic'),
(1, 80000, 16800, 96800, 'MSD', 'Alice', 'Johnson', '88005553535', 'alice.johnson@gmail.com', 'V luhu', '15', '12800', 'Prague', 'Czech Republic'),
(2, 1600, 336, 1936, 'Blizzard', 'Bob', 'Miller', '88005553536', 'bob.miller@gmail.com', 'Am Wriezener', 'bhf', '10243', 'Berlin', 'Germany'),
(2, 550, 115.5, 665.5, 'Blizzard', 'Bob', 'Miller', '88005553536', 'bob.miller@gmail.com', 'Am Wriezener', 'bhf','10243', 'Berlin', 'Germany'),
(3, 560000, 117600, 677600, 'Activision', 'Charlie', 'Williams', '88005553537', 'charlie.williams@gmail.com', 'Janey Way', '1211','95819', 'Sacramento', 'USA'),
(8, 80000, 16800, 99800, 'Partners', 'David', 'Davis', '88005553538', 'david.davis@gmail.com', 'Svornosti', '3321', '15000', 'Prague', 'Czech Republic'),
(7, 15036, 3157.56, 18193.56, 'CZU', 'Emily', 'Taylor', '88005553539', 'emily.taylor@gmail.com', 'Kamýcká', '129', '16500', 'Pregue', 'Czech Republic'),
(7, 225000, 15, 272250, 'CZU', 'Emily', 'Taylor', '88005553539', 'emily.taylor@gmail.com', 'Kamýcká', '129', '16500', 'Pregue', 'Czech Republic');

INSERT Order_Item(	ProductID,
					OrderID,
					OrderQuantity,
					ItemDescription) VALUES
(11, 1, 1,  'In today’s market, there are many high-quality ventilation units and you are free to compare them. We neither make overstated claims nor provide a magic box. We simply use the laws of physics to ensure effortless control of your indoor air quality. This is what makes our Jablotron Futura energy recovery unit different in so many ways.'),
(12, 1, 1,  'In today’s market, there are many high-quality ventilation units and you are free to compare them. We neither make overstated claims nor provide a magic box. We simply use the laws of physics to ensure effortless control of your indoor air quality. This is what makes our Jablotron Futura energy recovery unit different in so many ways.'),
(5, 2, 1,  'The cameras expand your security options. Thanks to the MyJABLOTRON application, your property can be fully monitored without having to constantly watch the phone. The system automatically generates a one minute recording recording of every event from the JABLOTRON 100+ alarm thus providing an instant overview.'),
(4, 3, 1,  'The cameras expand your security options. Thanks to the MyJABLOTRON application, your property can be fully monitored without having to constantly watch the phone. The system automatically generates a one minute recording recording of every event from the JABLOTRON 100+ alarm thus providing an instant overview.'),
(10, 4, 1, 'The Nanny baby monitor can call for help. You can sleep peacefully knowing that our device will monitor the breathing of your baby and will immediately — acoustically and visually — warn you in the case of an emergency. Nanny is a medically certified device frequently used in hospitals.'),
(14, 4, 1,  'I can see all the information perfectly just as printed on a paper. Unique e-ink technology offers high contrast and great visibility even in direct sunlight'),
(1, 4, 1,  'Make your apartment, home or company a safer place and enjoy a feeling of true peace. The JABLOTRON 100+ alarm will protect your property as well as turn on the lights, open your garage door, open the barrier, control the blinds or start an irrigation system. It’s also possible to control it remotely thanks to the MyJABLOTRON app.'),
(8, 5, 1, 'The JABLOTRON 100+ alarm doesn’t just take care of your security. It does so much more. Add control modules and get a smart home controlled through the MyJABLOTRON app. You’ll be able to manage everything remotely, from turning on the heating to opening the garage door.');


INSERT Transaction(	OrderID,
					UserID,
					TranProvider,
					Amount,
					TranAccount,
					TranPaymentType,
					TranStatus) VALUES
(4, 1, 'KB', 184000, '88888888888/0811', 'Payment', 1),
(5, 1, 'KB', 92000, '88888888888/0811', 'Payment', 1),
(3, 2, 'KB', 1840, '77777777777/0800', 'Payment', 1),
(2, 2, 'KB', 632.5, '77777777777/0800', 'Payment', 1),
(1, 3, 'KB', 644000, '66666666666/0821', 'Payment', 1),
(3, 4, 'KB', 92000, '88888888888/0831', 'Payment', 1),
(2, 5, 'KB', 17291.4, '88888888888/0812', 'Payment', 1),
(1, 5, 'KB', 258750, '88888888888/0812', 'Payment', 1);


#Procedure to display customers data
DELIMITER //
CREATE PROCEDURE UserDataReview(IN ReqUser BIGINT)
BEGIN
	SELECT U.ID, U.UserFirstName, U.UserLastName, P.UserAccount, A.AddressLine1, A.AddressCity, A.AddressCountry
	FROM ((User AS U
	INNER JOIN User_Paydetails AS P ON U.ID = P.UserID)
	INNER JOIN User_Address AS A ON U.ID = A.UserID)
	WHERE U.ID = ReqUser;
END//
DELIMITER ;
CALL UserDataReview(3);

#Procedure to display all orders from one customer
DELIMITER //
CREATE PROCEDURE OrdersByUser(IN ReqUser BIGINT)
BEGIN
	SELECT User.ID, User.UserFirstName, User.UserLastName, Order_Table.ID
	FROM User
	INNER JOIN Order_Table ON User.ID = Order_Table.UserID
	WHERE User.ID = ReqUser;
END//
DELIMITER ;
CALL OrdersByUser(1);

DROP PROCEDURE if exists UserDataReview;
DROP PROCEDURE if exists OrdersByUser;
DROP TRIGGER IF EXISTS LoadDefaultOrderData;
DROP TRIGGER IF EXISTS LoadDefaultCartData;
DROP TRIGGER IF EXISTS InventoryStockUpdate;
DROP TRIGGER IF EXISTS InsertItemPriceCart;
DROP TRIGGER IF EXISTS InsertItemPriceOrder;
DROP TRIGGER IF EXISTS CalculateOrderTotal;
DROP TRIGGER IF EXISTS CalculateCartTotal;
DROP TABLE  if exists Transaction;
DROP TABLE if exists Inventory;
DROP TABLE  if exists Product_Category;
DROP TABLE if exists Category;
DROP TABLE  if exists Order_Item;
DROP TABLE  if exists User_Paydetails;
DROP TABLE  if exists User_Address;
DROP TABLE  if exists Order_Table;
DROP TABLE if exists Cart_Item;
DROP TABLE if exists Product;
DROP TABLE  if exists Cart;
DROP TABLE if exists User;