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

USE Pizzeria;

-- Insert rows for toppings into the topping table
INSERT INTO topping (ToppingName, ToppingCost, ToppingPrice, ToppingAmtUsedPersonal, ToppingAmtUsedMedium, ToppingAmtUsedLarge, ToppingAmtUsedXLarge, ToppingMinimumInv, ToppingCurrentInv) VALUES 
('Pepperoni', 0.2, 1.25, 2, 2.75, 3.5, 4.5, 50, 100),
('Sausage', 0.15, 1.25, 2.5, 3, 3.5, 4.25, 50, 100),
('Ham', 0.15, 1.5, 2, 2.5, 3.25, 4, 25, 78),
('Chicken', 0.25, 1.75, 1.5, 2, 2.25, 3, 25, 56),
('Green Pepper', 0.02, 0.5, 1, 1.5, 2, 2.5, 25, 79),
('Onion', 0.02, 0.5, 1, 1.5, 2, 2.75, 25, 85),
('Roma Tomato', 0.03, 0.75, 2, 3, 3.5, 4.5, 10, 86),
('Mushrooms', 0.1, 0.75, 1.5, 2, 2.5, 3, 50, 52),
('Black Olives', 0.1, 0.6, 0.75, 1, 1.5, 2, 25, 39),
('Pineapple', 0.25, 1, 1, 1.25, 1.75, 2, 0, 15),
('Jalapenos', 0.05, 0.5, 0.5, 0.75, 1.25, 1.75, 0, 64),
('Banana Peppers', 0.05, 0.5, 0.6, 1, 1.3, 1.75, 0, 36),
('Regular Cheese', 0.12, 0.5, 2, 3.5, 5, 7, 50, 250),
('Four Cheese Blend', 0.15, 1, 2, 3.5, 5, 7, 25, 150),
('Feta Cheese', 0.18, 1.5, 1.75, 3, 4, 5.5, 0, 75),
('Goat Cheese', 0.2, 1.5, 1.6, 2.75, 4, 5.5, 0, 54),
('Bacon', 0.25, 1.5, 1, 1.5, 2, 3, 0, 89);

-- Insert rows for discounts into the discount table
INSERT INTO discount (DiscountName, DiscountType, DiscountDollarAmt, DiscountPercent) VALUES 
('Employee', 'order', NULL, 15),
('Lunch Special Medium', 'pizza', 1, NULL),
('Lunch Special Large', 'pizza', 2, NULL),
('Specialty Pizza', 'pizza', 1.5, NULL),
('Happy Hour', 'order', NULL, 10),
('Gameday Special', 'order', NULL, 20);

-- Insert rows for the base prices into the base_price table
INSERT INTO base_price VALUES 
('Small', 'Thin', 0.5, 3),
('Small', 'Original', 0.75, 3),
('Small', 'Pan', 1, 3.5),
('Small', 'Gluten-Free', 2, 4),
('Medium', 'Thin', 1, 5),
('Medium', 'Original', 1.5, 5),
('Medium', 'Pan', 2.25, 6),
('Medium', 'Gluten-Free', 3, 6.25),
('Large', 'Thin', 1.25, 8),
('Large', 'Original', 2, 8),
('Large', 'Pan', 3, 9),
('Large', 'Gluten-Free', 4, 9/5),
('XLarge', 'Thin', 2, 10),
('XLarge', 'Original', 3, 10),
('XLarge', 'Pan', 4.5, 11.5),
('XLarge', 'Gluten-Free', 6, 12.5);

-- On March 5th at 12:03 pm there was a dine-in order (at table 21) for a large thin crust pizza with Regular
-- Cheese (extra), Pepperoni, and Sausage (Price: $20.75, Cost: $3.68). They used the “Lunch Special Large”
-- discount for the pizza.

-- Create order record
INSERT INTO `order` (OrderType, OrderTimestamp, OrderIsComplete, OrderTotalCost, OrderTotalPrice) VALUES 
('dinein', '2024-03-05 12:03:00', TRUE, 3.68, 18.75);
SET @last_order_id = LAST_INSERT_ID();
-- Create dine-in order record
INSERT INTO dine_in_order VALUES 
(@last_order_id, 21);
-- Create pizza record
INSERT INTO pizza (PizzaOrderID, PizzaSize, PizzaCrustType, PizzaCostToCompany, PizzaCostToCustomer, PizzaReadyState) VALUES 
(@last_order_id, 'Large', 'Thin', 3.68, 18.75, "ready");
SET @last_pizza_id = LAST_INSERT_ID();
-- Create customization records
INSERT INTO customization (CustomizationPizzaID, CustomizationToppingID, CustomizationToppingIsDoubled) VALUES 
(@last_pizza_id, (SELECT ToppingID FROM topping WHERE ToppingName = 'Regular Cheese'), TRUE),
(@last_pizza_id, (SELECT ToppingID FROM topping WHERE ToppingName = 'Pepperoni'), FALSE),
(@last_pizza_id, (SELECT ToppingID FROM topping WHERE ToppingName = 'Sausage'), FALSE);
-- Create pizza discount records
INSERT INTO pizza_discount VALUES 
(@last_pizza_id, (SELECT DiscountID FROM discount WHERE DiscountName = 'Lunch Special Large'));

-- On April 3rd at 12:05 pm there was a dine-in order (at table 4). They ordered a medium pan pizza with
-- Feta Cheese, Black Olives, Roma Tomatoes, Mushrooms and Banana Peppers (Price: $12.85, Cost: $3.23).
-- They used the “Lunch Special Medium” and the “Specialty Pizza” discounts for the pizza. They also ordered
-- a small original crust pizza with Regular Cheese, Chicken and Banana Peppers (Price: $6.93, Cost: $1.40).

-- Create order record
INSERT INTO `order` (OrderType, OrderTimestamp, OrderIsComplete, OrderTotalCost, OrderTotalPrice) VALUES 
('dinein', '2024-04-03 12:05:00', TRUE, 4.63, 17.28);
SET @last_order_id = LAST_INSERT_ID();
-- Create dine-in order record
INSERT INTO dine_in_order VALUES 
(@last_order_id, 4);
-- Create pizza records
INSERT INTO pizza (PizzaOrderID, PizzaSize, PizzaCrustType, PizzaCostToCompany, PizzaCostToCustomer, PizzaReadyState) VALUES 
(@last_order_id, 'Medium', 'Pan', 3.23, 10.35, "ready");
SET @pizza_id_1 = LAST_INSERT_ID();
INSERT INTO pizza (PizzaOrderID, PizzaSize, PizzaCrustType, PizzaCostToCompany, PizzaCostToCustomer, PizzaReadyState) VALUES 
(@last_order_id, 'Small', 'Original', 1.4, 6.93, "ready");
SET @pizza_id_2 = LAST_INSERT_ID();
-- Create customization records
INSERT INTO customization (CustomizationPizzaID, CustomizationToppingID, CustomizationToppingIsDoubled) VALUES 
(@pizza_id_1, (SELECT ToppingID FROM topping WHERE ToppingName = 'Feta Cheese'), FALSE),
(@pizza_id_1, (SELECT ToppingID FROM topping WHERE ToppingName = 'Black Olives'), FALSE),
(@pizza_id_1, (SELECT ToppingID FROM topping WHERE ToppingName = 'Roma Tomato'), FALSE),
(@pizza_id_1, (SELECT ToppingID FROM topping WHERE ToppingName = 'Mushrooms'), FALSE),
(@pizza_id_1, (SELECT ToppingID FROM topping WHERE ToppingName = 'Banana Peppers'), FALSE),
(@pizza_id_2, (SELECT ToppingID FROM topping WHERE ToppingName = 'Regular Cheese'), FALSE),
(@pizza_id_2, (SELECT ToppingID FROM topping WHERE ToppingName = 'Chicken'), FALSE),
(@pizza_id_2, (SELECT ToppingID FROM topping WHERE ToppingName = 'Banana Peppers'), FALSE);
-- Create pizza discount records
INSERT INTO pizza_discount VALUES 
(@pizza_id_1, (SELECT DiscountID FROM discount WHERE DiscountName = 'Lunch Special Medium')),
(@pizza_id_1, (SELECT DiscountID FROM discount WHERE DiscountName = 'Specialty Pizza'));

-- On March 3rd at 9:30 pm Andrew Wilkes-Krier placed an order for pickup of 6 large original crust pizzas
-- with Regular Cheese and Pepperoni (Price: $14.88, Cost: $3.30 each). Andrew’s phone number is 864-254-
-- 5861.

-- Create order record
INSERT INTO `order` (OrderType, OrderTimestamp, OrderIsComplete, OrderTotalCost, OrderTotalPrice) VALUES 
('pickup', '2024-03-03 21:30:00', TRUE, 19.8, 89.28);
SET @last_order_id = LAST_INSERT_ID();
-- Create customer record
INSERT INTO customer (CustomerFirstName, CustomerLastName, CustomerPhone) VALUES 
('Andrew', 'Wilkes-Krier', '8642545861');
SET @last_customer_id = LAST_INSERT_ID();
-- Create pickup order record
INSERT INTO pickup_order VALUES 
(@last_order_id, @last_customer_id, TRUE);
-- Create pizza records
INSERT INTO pizza (PizzaOrderID, PizzaSize, PizzaCrustType, PizzaCostToCompany, PizzaCostToCustomer, PizzaReadyState) VALUES 
(@last_order_id, 'Large', 'Original', 3.3, 14.88, "ready");
SET @pizza_id_1 = LAST_INSERT_ID();
INSERT INTO pizza (PizzaOrderID, PizzaSize, PizzaCrustType, PizzaCostToCompany, PizzaCostToCustomer, PizzaReadyState) VALUES 
(@last_order_id, 'Large', 'Original', 3.3, 14.88, "ready");
SET @pizza_id_2 = LAST_INSERT_ID();
INSERT INTO pizza (PizzaOrderID, PizzaSize, PizzaCrustType, PizzaCostToCompany, PizzaCostToCustomer, PizzaReadyState) VALUES 
(@last_order_id, 'Large', 'Original', 3.3, 14.88, "ready");
SET @pizza_id_3 = LAST_INSERT_ID();
INSERT INTO pizza (PizzaOrderID, PizzaSize, PizzaCrustType, PizzaCostToCompany, PizzaCostToCustomer, PizzaReadyState) VALUES 
(@last_order_id, 'Large', 'Original', 3.3, 14.88, "ready");
SET @pizza_id_4 = LAST_INSERT_ID();
INSERT INTO pizza (PizzaOrderID, PizzaSize, PizzaCrustType, PizzaCostToCompany, PizzaCostToCustomer, PizzaReadyState) VALUES 
(@last_order_id, 'Large', 'Original', 3.3, 14.88, "ready");
SET @pizza_id_5 = LAST_INSERT_ID();
INSERT INTO pizza (PizzaOrderID, PizzaSize, PizzaCrustType, PizzaCostToCompany, PizzaCostToCustomer, PizzaReadyState) VALUES 
(@last_order_id, 'Large', 'Original', 3.3, 14.88, "ready");
SET @pizza_id_6 = LAST_INSERT_ID();
-- Create customization records
INSERT INTO customization (CustomizationPizzaID, CustomizationToppingID, CustomizationToppingIsDoubled) VALUES 
(@pizza_id_1, (SELECT ToppingID FROM topping WHERE ToppingName = 'Regular Cheese'), FALSE),
(@pizza_id_1, (SELECT ToppingID FROM topping WHERE ToppingName = 'Pepperoni'), FALSE),
(@pizza_id_2, (SELECT ToppingID FROM topping WHERE ToppingName = 'Regular Cheese'), FALSE),
(@pizza_id_2, (SELECT ToppingID FROM topping WHERE ToppingName = 'Pepperoni'), FALSE),
(@pizza_id_3, (SELECT ToppingID FROM topping WHERE ToppingName = 'Regular Cheese'), FALSE),
(@pizza_id_3, (SELECT ToppingID FROM topping WHERE ToppingName = 'Pepperoni'), FALSE),
(@pizza_id_4, (SELECT ToppingID FROM topping WHERE ToppingName = 'Regular Cheese'), FALSE),
(@pizza_id_4, (SELECT ToppingID FROM topping WHERE ToppingName = 'Pepperoni'), FALSE),
(@pizza_id_5, (SELECT ToppingID FROM topping WHERE ToppingName = 'Regular Cheese'), FALSE),
(@pizza_id_5, (SELECT ToppingID FROM topping WHERE ToppingName = 'Pepperoni'), FALSE),
(@pizza_id_6, (SELECT ToppingID FROM topping WHERE ToppingName = 'Regular Cheese'), FALSE),
(@pizza_id_6, (SELECT ToppingID FROM topping WHERE ToppingName = 'Pepperoni'), FALSE);

-- On April 20th at 7:11 pm there was a delivery order made by Andrew Wilkes-Krier for 1 xlarge pepperoni
-- and Sausage pizza (Price: $27.94, Cost: $9.19), one xlarge pizza with Ham (extra) and Pineapple (extra)
-- pizza (Price: $31.50, Cost: $6.25), and one xlarge Chicken and Bacon pizza (Price: $26.75, Cost: $8.18). All
-- the pizzas have the Four Cheese Blend on it and are original crust. The order has the “Gameday Special”
-- discount applied to it, and the ham and pineapple pizza has the “Specialty Pizza” discount applied to it. The
-- pizzas were delivered to 115 Party Blvd, Anderson SC 29621. His phone number is the same as before.

-- Create order record
INSERT INTO `order` (OrderType, OrderTimestamp, OrderIsComplete, OrderTotalCost, OrderTotalPrice) VALUES 
('delivery', '2024-04-20 19:11:00', TRUE, 23.62, 67.45);
SET @last_order_id = LAST_INSERT_ID();
-- Update customer record
UPDATE customer 
SET CustomerStreet = '115 Party Blvd', CustomerCity = 'Anderson', CustomerState = 'SC', CustomerZipCode = '29621' 
WHERE CustomerID = @last_customer_id;
-- Create delivery order record
INSERT INTO delivery_order VALUES 
(@last_order_id, @last_customer_id);
-- Create pizza records
INSERT INTO pizza (PizzaOrderID, PizzaSize, PizzaCrustType, PizzaCostToCompany, PizzaCostToCustomer, PizzaReadyState) VALUES 
(@last_order_id, 'XLarge', 'Original', 9.19, 22.35, "ready");
SET @pizza_id_1 = LAST_INSERT_ID();
INSERT INTO pizza (PizzaOrderID, PizzaSize, PizzaCrustType, PizzaCostToCompany, PizzaCostToCustomer, PizzaReadyState) VALUES 
(@last_order_id, 'XLarge', 'Original', 6.25, 23.7, "ready");
SET @pizza_id_2 = LAST_INSERT_ID();
INSERT INTO pizza (PizzaOrderID, PizzaSize, PizzaCrustType, PizzaCostToCompany, PizzaCostToCustomer, PizzaReadyState) VALUES 
(@last_order_id, 'XLarge', 'Original', 8.18, 21.4, "ready");
SET @pizza_id_3 = LAST_INSERT_ID();
-- Create customization records
INSERT INTO customization (CustomizationPizzaID, CustomizationToppingID, CustomizationToppingIsDoubled) VALUES 
(@pizza_id_1, (SELECT ToppingID FROM topping WHERE ToppingName = 'Four Cheese Blend'), FALSE),
(@pizza_id_1, (SELECT ToppingID FROM topping WHERE ToppingName = 'Pepperoni'), FALSE),
(@pizza_id_1, (SELECT ToppingID FROM topping WHERE ToppingName = 'Sausage'), FALSE),
(@pizza_id_2, (SELECT ToppingID FROM topping WHERE ToppingName = 'Four Cheese Blend'), FALSE),
(@pizza_id_2, (SELECT ToppingID FROM topping WHERE ToppingName = 'Ham'), TRUE),
(@pizza_id_2, (SELECT ToppingID FROM topping WHERE ToppingName = 'Pineapple'), TRUE),
(@pizza_id_3, (SELECT ToppingID FROM topping WHERE ToppingName = 'Four Cheese Blend'), FALSE),
(@pizza_id_3, (SELECT ToppingID FROM topping WHERE ToppingName = 'Chicken'), FALSE),
(@pizza_id_3, (SELECT ToppingID FROM topping WHERE ToppingName = 'Bacon'), FALSE);
-- Create order discount record
INSERT INTO order_discount VALUES 
(@last_order_id, (SELECT DiscountID FROM discount WHERE DiscountName = 'Gameday Special'));
-- Create pizza discount record
INSERT INTO pizza_discount VALUES 
(@pizza_id_2, (SELECT DiscountID FROM discount WHERE DiscountName = 'Specialty Pizza'));

-- On March 2nd at 5:30 pm Matt Engers placed an order for pickup for an xlarge pizza with Green Pepper,
-- Onion, Roma Tomatoes, Mushrooms, and Black Olives on it. He wants the Goat Cheese on it, and a Gluten
-- Free Crust (Price: $27.45, Cost: $7.88). The “Specialty Pizza” discount is applied to the pizza. Matt’s phone
-- number is 864-474-9953.

-- Create order record
INSERT INTO `order` (OrderType, OrderTimestamp, OrderIsComplete, OrderTotalCost, OrderTotalPrice) VALUES 
('pickup', '2024-03-02 17:30:00', TRUE, 7.88, 25.95);
SET @last_order_id = LAST_INSERT_ID();
-- Create customer record
INSERT INTO customer (CustomerFirstName, CustomerLastName, CustomerPhone) VALUES 
('Matt', 'Engers', '8644749953');
SET @last_customer_id = LAST_INSERT_ID();
-- Create pickup order record
INSERT INTO pickup_order VALUES 
(@last_order_id, @last_customer_id, TRUE);
-- Create pizza record
INSERT INTO pizza (PizzaOrderID, PizzaSize, PizzaCrustType, PizzaCostToCompany, PizzaCostToCustomer, PizzaReadyState) VALUES 
(@last_order_id, 'XLarge', 'Gluten-Free', 7.88, 25.95, "ready");
SET @pizza_id_1 = LAST_INSERT_ID();
-- Create customization records
INSERT INTO customization (CustomizationPizzaID, CustomizationToppingID, CustomizationToppingIsDoubled) VALUES 
(@pizza_id_1, (SELECT ToppingID FROM topping WHERE ToppingName = 'Goat Cheese'), FALSE),
(@pizza_id_1, (SELECT ToppingID FROM topping WHERE ToppingName = 'Green Pepper'), FALSE),
(@pizza_id_1, (SELECT ToppingID FROM topping WHERE ToppingName = 'Onion'), FALSE),
(@pizza_id_1, (SELECT ToppingID FROM topping WHERE ToppingName = 'Roma Tomato'), FALSE),
(@pizza_id_1, (SELECT ToppingID FROM topping WHERE ToppingName = 'Mushrooms'), FALSE),
(@pizza_id_1, (SELECT ToppingID FROM topping WHERE ToppingName = 'Black Olives'), FALSE);
-- Create pizza discount record
INSERT INTO pizza_discount VALUES 
(@pizza_id_1, (SELECT DiscountID FROM discount WHERE DiscountName = 'Specialty Pizza'));

-- On March 2nd at 6:17 pm Frank Turner places an order for delivery of one large pizza with Chicken, Green
-- Peppers, Onions, and Mushrooms. He wants the Four Cheese Blend (extra) and thin crust (Price: $20.81,
-- Cost: $3.19). The pizza was delivered to 6745 Wessex St Anderson SC 29621. Frank’s phone number is 864-
-- 232-8944.

-- Create order record
INSERT INTO `order` (OrderType, OrderTimestamp, OrderIsComplete, OrderTotalCost, OrderTotalPrice) VALUES 
('delivery', '2024-03-02 18:17:00', TRUE, 3.19, 20.81);
SET @last_order_id = LAST_INSERT_ID();
-- Create customer record
INSERT INTO customer (CustomerFirstName, CustomerLastName, CustomerPhone, CustomerStreet, CustomerCity, CustomerState, CustomerZipCode) VALUES 
('Frank', 'Turner', '8642328944', '6745 Wessex St', 'Anderson', 'SC', '29621');
SET @last_customer_id = LAST_INSERT_ID();
-- Create delivery order record
INSERT INTO delivery_order VALUES 
(@last_order_id, @last_customer_id);
-- Create pizza record
INSERT INTO pizza (PizzaOrderID, PizzaSize, PizzaCrustType, PizzaCostToCompany, PizzaCostToCustomer, PizzaReadyState) VALUES 
(@last_order_id, 'Large', 'Thin', 3.19, 20.81, "ready");
SET @pizza_id_1 = LAST_INSERT_ID();
-- Create customization records
INSERT INTO customization (CustomizationPizzaID, CustomizationToppingID, CustomizationToppingIsDoubled) VALUES 
(@pizza_id_1, (SELECT ToppingID FROM topping WHERE ToppingName = 'Four Cheese Blend'), TRUE),
(@pizza_id_1, (SELECT ToppingID FROM topping WHERE ToppingName = 'Chicken'), FALSE),
(@pizza_id_1, (SELECT ToppingID FROM topping WHERE ToppingName = 'Green Pepper'), FALSE),
(@pizza_id_1, (SELECT ToppingID FROM topping WHERE ToppingName = 'Onion'), FALSE),
(@pizza_id_1, (SELECT ToppingID FROM topping WHERE ToppingName = 'Mushrooms'), FALSE);

-- On April 13th at 8:32 pm Milo Auckerman ordered two large thin crust pizzas. One had the Four Cheese
-- Blend on it (extra) (Price: $13.00, Cost: $2.00), the other was Regular Cheese and Pepperoni (extra) (Price:
-- $19.25, Cost: $3.25). He used the “Employee” discount on his order. He had them delivered to 8879
-- Suburban Home, Anderson, SC 29621. His phone number is 864-878-5679.

-- Create order record
INSERT INTO `order` (OrderType, OrderTimestamp, OrderIsComplete, OrderTotalCost, OrderTotalPrice) VALUES 
('delivery', '2024-04-13 20:32:00', TRUE, 5.25, 27.41);
SET @last_order_id = LAST_INSERT_ID();
-- Create customer record
INSERT INTO customer (CustomerFirstName, CustomerLastName, CustomerPhone, CustomerStreet, CustomerCity, CustomerState, CustomerZipCode) VALUES 
('Milo', 'Auckerman', '8648785679', '8879 Suburban Home', 'Anderson', 'SC', '29621');
SET @last_customer_id = LAST_INSERT_ID();
-- Create delivery order record
INSERT INTO delivery_order VALUES 
(@last_order_id, @last_customer_id);
-- Create pizza records
INSERT INTO pizza (PizzaOrderID, PizzaSize, PizzaCrustType, PizzaCostToCompany, PizzaCostToCustomer, PizzaReadyState) VALUES 
(@last_order_id, 'Large', 'Thin', 2, 11.05, "ready");
SET @pizza_id_1 = LAST_INSERT_ID();
INSERT INTO pizza (PizzaOrderID, PizzaSize, PizzaCrustType, PizzaCostToCompany, PizzaCostToCustomer, PizzaReadyState) VALUES 
(@last_order_id, 'Large', 'Thin', 3.25, 16.36, "ready");
SET @pizza_id_2 = LAST_INSERT_ID();
-- Create customization records
INSERT INTO customization (CustomizationPizzaID, CustomizationToppingID, CustomizationToppingIsDoubled) VALUES 
(@pizza_id_1, (SELECT ToppingID FROM topping WHERE ToppingName = 'Four Cheese Blend'), TRUE),
(@pizza_id_2, (SELECT ToppingID FROM topping WHERE ToppingName = 'Regular Cheese'), FALSE),
(@pizza_id_2, (SELECT ToppingID FROM topping WHERE ToppingName = 'Pepperoni'), TRUE);
-- Create order discount record
INSERT INTO order_discount VALUES 
(@last_order_id, (SELECT DiscountID FROM discount WHERE DiscountName = 'Employee'));