-- Owen Sullivan
-- C34243277
-- CPSC 4620 S24

USE Pizzeria;

-- Create ToppingPopularity view
CREATE VIEW ToppingPopularity AS 
SELECT topping.ToppingName as 'Topping', COUNT(customization.CustomizationToppingID) + COUNT(CASE WHEN customization.CustomizationToppingIsDoubled THEN 1 ELSE NULL END) AS ToppingCount FROM topping LEFT JOIN customization ON topping.ToppingID = customization.CustomizationToppingID GROUP BY topping.ToppingID ORDER BY ToppingCount DESC;
-- Show ToppingPopularity view
SELECT * FROM ToppingPopularity;

-- Create ProfitByPizza view
CREATE VIEW ProfitByPizza AS 
SELECT base_price.BasePricePizzaSize AS Size, base_price.BasePricePizzaCrustType AS Crust, SUM(pizza.PizzaCostToCustomer - pizza.PizzaCostToCompany) AS Profit, CONCAT(MONTH(`order`.OrderTimestamp), '/', YEAR(`order`.OrderTimestamp)) AS 'Order Month' FROM base_price JOIN pizza ON base_price.BasePricePizzaSize = pizza.PizzaSize AND base_price.BasePricePizzaCrustType = pizza.PizzaCrustType JOIN `order` ON pizza.PizzaOrderID = `order`.OrderID GROUP BY base_price.BasePricePizzaSize, base_price.BasePricePizzaCrustType ORDER BY Profit ASC;
-- Show ProfitByPizza view
SELECT * FROM ProfitByPizza;

-- Create ProfitByOrderType view
CREATE VIEW ProfitByOrderType AS 
SELECT OrderType AS customerType, CONCAT(MONTH(OrderTimestamp), '/', YEAR(OrderTimestamp)) AS 'Order Month', SUM(OrderTotalPrice) AS TotalOrderPrice, SUM(OrderTotalCost) AS TotalOrderCost, SUM(OrderTotalPrice) - SUM(OrderTotalCost) AS Profit FROM `order` GROUP BY OrderType UNION 
SELECT '', 'Grand Total', SUM(OrderTotalPrice), SUM(OrderTotalCost), SUM(OrderTotalPrice) - SUM(OrderTotalCost) FROM `order`;
-- Show ProfitByOrderType view
SELECT * FROM ProfitByOrderType;