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

-- Create the Pizzeria schema
CREATE SCHEMA Pizzeria;
USE Pizzeria;

-- Create the customer table
CREATE TABLE customer (
	CustomerID INT PRIMARY KEY AUTO_INCREMENT,
    CustomerFirstName VARCHAR(255) NOT NULL,
    CustomerLastName VARCHAR(255) NOT NULL,
    CustomerPhone VARCHAR(10) NOT NULL,
    CustomerStreet VARCHAR(255),
    CustomerCity VARCHAR(255),
    CustomerState VARCHAR(2),
    CustomerZipCode VARCHAR(5)
);

-- Create the order supertype table
CREATE TABLE `order` (
	OrderID INT PRIMARY KEY AUTO_INCREMENT,
    OrderType VARCHAR(8) NOT NULL,
    OrderTimestamp TIMESTAMP NOT NULL,
    OrderIsComplete BOOL NOT NULL DEFAULT FALSE,
    OrderTotalCost DECIMAL(10, 2),
    OrderTotalPrice DECIMAL(10, 2)
);

-- Create the dine_in_order subtype table
CREATE TABLE dine_in_order (
	DineInOrderOrderID INT PRIMARY KEY,
    FOREIGN KEY (DineInOrderOrderID) REFERENCES `order`(OrderID),
    DineInOrderTableNumber INT NOT NULL
);
-- Create the pickup_order subtype table
CREATE TABLE pickup_order (
	PickupOrderOrderID INT,
    FOREIGN KEY (PickupOrderOrderID) REFERENCES `order`(OrderID),
    PickupOrderCustomerID INT,
    FOREIGN KEY (PickupOrderCustomerID) REFERENCES customer(CustomerID),
    PRIMARY KEY (PickupOrderOrderID, PickupOrderCustomerID),
    PickupOrderIsPickedUp BOOL NOT NULL DEFAULT FALSE
);
-- Create the delivery_order subtype table
CREATE TABLE delivery_order (
	DeliveryOrderOrderID INT,
    FOREIGN KEY (DeliveryOrderOrderID) REFERENCES `order`(OrderID),
    DeliveryOrderCustomerID INT,
    FOREIGN KEY (DeliveryOrderCustomerID) REFERENCES customer(CustomerID),
    PRIMARY KEY (DeliveryOrderOrderID, DeliveryOrderCustomerID)
);

-- Create the base_price table
CREATE TABLE base_price (
	BasePricePizzaSize VARCHAR(8),
    BasePricePizzaCrustType VARCHAR(11),
    PRIMARY KEY (BasePricePizzaSize, BasePricePizzaCrustType),
    BasePriceCostToCompany DECIMAL(10, 2) NOT NULL,
    BasePricePriceToCustomer DECIMAL(10, 2) NOT NULL
);
-- Create indexes on the BasePricePizzaSize and BasePricePizzaCrustType columns so that they 
-- can be referenced as foreign keys
CREATE INDEX idx_pizzasize ON base_price(BasePricePizzaSize);
CREATE INDEX idx_pizzacrusttype ON base_price(BasePricePizzaCrustType);

-- Create the pizza table
CREATE TABLE pizza (
	PizzaID INT PRIMARY KEY AUTO_INCREMENT,
    PizzaOrderID INT NOT NULL,
    FOREIGN KEY (PizzaOrderID) REFERENCES `order`(OrderID),
    PizzaSize VARCHAR(8) NOT NULL,
    FOREIGN KEY (PizzaSize) REFERENCES base_price(BasePricePizzaSize),
    PizzaCrustType VARCHAR(11) NOT NULL,
    FOREIGN KEY (PizzaCrustType) REFERENCES base_price(BasePricePizzaCrustType),
    PizzaCostToCompany DECIMAL(10, 2),
    PizzaCostToCustomer DECIMAL(10, 2),
    PizzaReadyState VARCHAR(9) NOT NULL
);

-- Create the topping table
CREATE TABLE topping (
	ToppingID INT PRIMARY KEY AUTO_INCREMENT,
    ToppingName VARCHAR(255) NOT NULL,
    ToppingCost DECIMAL(10, 2) NOT NULL,
    ToppingPrice DECIMAL(10, 2) NOT NULL,
    ToppingAmtUsedPersonal DECIMAL(10, 2) NOT NULL,
    ToppingAmtUsedMedium DECIMAL(10, 2) NOT NULL,
    ToppingAmtUsedLarge DECIMAL(10, 2) NOT NULL,
    ToppingAmtUsedXLarge DECIMAL(10, 2) NOT NULL,
    ToppingMinimumInv DECIMAL(10, 2) NOT NULL,
    ToppingCurrentInv DECIMAL(10, 2)
);

-- Create the customization table
CREATE TABLE customization (
	CustomizationPizzaID INT,
    FOREIGN KEY (CustomizationPizzaID) REFERENCES pizza(PizzaID),
    CustomizationToppingID INT,
    FOREIGN KEY (CustomizationToppingID) REFERENCES topping(ToppingID),
    PRIMARY KEY (CustomizationPizzaID, CustomizationToppingID),
    CustomizationToppingIsDoubled BOOL NOT NULL DEFAULT FALSE
);

-- Create the discount table
CREATE TABLE discount (
	DiscountID INT PRIMARY KEY AUTO_INCREMENT,
    DiscountName VARCHAR(255) NOT NULL,
    DiscountType VARCHAR(5) NOT NULL,
    DiscountDollarAmt DECIMAL(10, 2),
    DiscountPercent DECIMAL(10, 2)
);

-- Create the order_discount table
CREATE TABLE order_discount (
	OrderDiscountOrderID INT,
    FOREIGN KEY (OrderDiscountOrderID) REFERENCES `order`(OrderID),
    OrderDiscountDiscountID INT,
    FOREIGN KEY (OrderDiscountDiscountID) REFERENCES discount(DiscountID),
    PRIMARY KEY (OrderDiscountOrderID, OrderDiscountDiscountID)
);
-- Create the pizza_discount table
CREATE TABLE pizza_discount (
	PizzaDiscountPizzaID INT,
    FOREIGN KEY (PizzaDiscountPizzaID) REFERENCES pizza(PizzaID),
    PizzaDiscountDiscountID INT,
    FOREIGN KEY (PizzaDiscountDiscountID) REFERENCES discount(DiscountID),
    PRIMARY KEY (PizzaDiscountPizzaID, PizzaDiscountDiscountID)
);