CREATE DATABASE Lucky_Shrub;
USE Lucky_Shrub;
CREATE TABLE Clients (
    ClientID VARCHAR(5) NOT NULL PRIMARY KEY,
    FullName VARCHAR(50),
    ContactNumber VARCHAR(10),
    AddressID INT
    );
CREATE TABLE Orders (
    OrderID INT NOT NULL PRIMARY KEY,
    ClientID VARCHAR(10),
    ProductID VARCHAR(10),
    Quantity INT,
    Cost DECIMAL(6,2),
    Date DATE
    ); 
INSERT INTO Clients
  (ClientID, FullName, ContactNumber, AddressID)
  VALUES
  ('Cl1', 'Takashi Ito', '351786345', 1),
  ('Cl2', 'Jane Murphy', '351567243', 2),
  ('Cl3', 'Laurina Deldago', '351342597', 3),
  ('Cl4', 'Benjamin Clauss', '351342509', 4),
  ('Cl5', 'Altay Ayhan', '351208983', 5),
  ('Cl6', 'Greta Galkina', '351298755', 6);
INSERT INTO Orders(OrderID, ClientID, ProductID , Quantity, Cost, Date) VALUES
(1, "Cl1", "P1", 10, 500, "2020-09-01"),
(2, "Cl2", "P2", 5, 100, "2020-09-05"),
(3, "Cl3", "P3", 20, 800, "2020-09-03"),
(4, "Cl4", "P4", 15, 150, "2020-09-07"),
(5, "Cl3", "P3", 10, 450, "2020-09-08"),
(6, "Cl2", "P2", 5, 800, "2020-09-09"),
(7, "Cl1", "P4", 22, 1200, "2020-09-10"),
(8, "Cl3", "P1", 15, 150, "2020-09-10"),
(9, "Cl1", "P1", 10, 500, "2020-09-12"),
(10, "Cl2", "P2", 5, 100, "2020-09-13"),
(11, "Cl4", "P5", 5, 100, "2020-09-15"),
(12, "Cl1", "P1", 10, 500, "2022-09-01"),
(13, "Cl2", "P2", 5, 100, "2022-09-05"),
(14, "Cl3", "P3", 20, 800, "2022-09-03"),
(15, "Cl4", "P4", 15, 150, "2022-09-07"),
(16, "Cl3", "P3", 10, 450, "2022-09-08"),
(17, "Cl2", "P2", 5, 800, "2022-09-09"),
(18, "Cl1", "P4", 22, 1200, "2022-09-10"),
(19, "Cl3", "P1", 15, 150, "2022-09-10"),
(20, "Cl1", "P1", 10, 500, "2022-09-12"),
(21, "Cl2", "P2", 5, 100, "2022-09-13"),
(22, "Cl2", "P1", 10, 500, "2021-09-01"),
(23, "Cl2", "P2", 5, 100, "2021-09-05"),
(24, "Cl3", "P3", 20, 800, "2021-09-03"),
(25, "Cl4", "P4", 15, 150, "2021-09-07"),
(26, "Cl1", "P3", 10, 450, "2021-09-08"),
(27, "Cl2", "P1", 20, 1000, "2022-09-01"),
(28, "Cl2", "P2", 10, 200, "2022-09-05"),
(29, "Cl3", "P3", 20, 800, "2021-09-03"),
(30, "Cl1", "P1", 10, 500, "2022-09-01");

-- AVG is a MySQL built-in function
SELECT AVG(Cost) FROM Orders;
-- We can create a user defined function
CREATE FUNCTION IF NOT EXISTS GetCostAverage() RETURNS DECIMAL(5,2) DETERMINISTIC
RETURN (SELECT AVG(Cost) FROM Orders);
-- Invoke(call) the newly created function
SELECT GetCostAverage();

-- Create a stored procedure
CREATE PROCEDURE IF NOT EXISTS GetAllClients()
SELECT * FROM Clients;
-- invoke(call) the newly created stored procedure
SELECT GetCostAverage();

-- Create a SQL function that prints the cost value of a specific order based on the user input of the OrderID. The expected output result should be the same as the result
CREATE FUNCTION IF NOT EXISTS FindCost(orderId INT) RETURNS INT DETERMINISTIC
RETURN (SELECT Cost from Orders WHERE OrderID = orderId);
-- The above is wrong because orderId and OrderID are the same.  SQL patterns are case-insensitive by default.
CREATE FUNCTION FindCost(order_id INT)
RETURNS DECIMAL (6,2) DETERMINISTIC
RETURN (SELECT Cost FROM Orders WHERE OrderID = order_id);

SELECT FindCost(30);

-- Create a stored procedure called GetDiscount()
DELIMITER //
CREATE Procedure GetDiscount(order_id INT)
  BEGIN
      DECLARE cost_after_discount DECIMAL(7,2);
      DECLARE current_cost DECIMAL(7,2);
      DECLARE order_quantity INT;
      SELECT Quantity INTO order_quantity FROM Orders WHERE OrderID = order_id;
      SELECT Cost INTO current_cost FROM Orders WHERE OrderID = order_id;
     IF order_quantity >= 20 THEN
       SET cost_after_discount = current_cost - (current_cost * 0.2);
     ELSEIF order_quantity >= 10 THEN
       SET cost_after_discount = current_cost - (current_cost * 0.1);
     ELSE SET cost_after_discount = current_cost;
     END IF;
     SELECT cost_after_discount;
	END//
DELIMITER ;

CALL GetDiscount(5);

-- List all user defined functions and procedures
SHOW FUNCTION STATUS WHERE Db != 'sys';
-- List all user defined procedures
SHOW PROCEDURE STATUS WHERE Db != 'sys';
-- Check the details of a stored procedure
SHOW CREATE PROCEDURE Lucky_Shrub.GetAllClients;
-- DROP FUNCTION 
DROP FUNCTION FindCost;
DROP FUNCTION GetCostAverage;
-- DROP PROCEDURE
DROP PROCEDURE GetAllClients;
DROP PROCEDURE GetDiscount;