package cpsc4620;

import java.io.IOException;
import java.math.BigDecimal;
import java.sql.Date;
import java.sql.*;
import java.util.*;
import java.util.regex.Pattern;

/**
 * A utility class to help add and retrieve information from a MySQL database.
 */
public final class DBNinja {

    // Order type string constants
    public final static String pickup = "pickup";
    public final static String delivery = "delivery";
    public final static String dine_in = "dinein";
    // Pizza size string constants
    public final static String size_s = "Small";
    public final static String size_m = "Medium";
    public final static String size_l = "Large";
    public final static String size_xl = "XLarge";
    // Pizza crust type string constants
    public final static String crust_thin = "Thin";
    public final static String crust_orig = "Original";
    public final static String crust_pan = "Pan";
    public final static String crust_gf = "Gluten-Free";
    // Custom Comparator for comparing Order objects by OrderTimestamp from most recent to oldest
    public static Comparator<Order> OrderTimestampComparator = new Comparator<>() {

        @Override
        public int compare(Order o1, Order o2) {

            return Date.valueOf(o2.getDate()).compareTo(Date.valueOf(o1.getDate()));

        }

    };
    // Custom Comparator for comparing Customer objects by CustomerID
    public static Comparator<Customer> CustomerIDComparator = new Comparator<>() {

        @Override
        public int compare(Customer c1, Customer c2) {

            return c1.getCustID() - c2.getCustID();

        }

    };
    // Database connection handler
    private static Connection conn;

    /**
     * Function which attempts a MySQL database connection via JDBC and handles exceptions.
     *
     * @return Success of database connection
     */
    private static boolean connect_to_db() {

        try {

            conn = DBConnector.make_connection();
            return true;

        } catch (SQLException e) {

            return false;

        } catch (IOException e) {

            return false;

        }

    }

    /**
     * Helper function which assembles a printable format string based on provided lengths, objects being printed, and
     * justifications.
     *
     * @param lengths        List of lengths for formatting
     * @param toPrint        List of objects to be printed
     * @param justifications List of booleans where true = left justification and false = right justification
     * @return Assembled, printable format string
     * @precondition lengths.size() = toPrint.size() = justifications.size() AND NOT lengths.isEmpty()
     * AND NOT toPrint.isEmpty() AND NOT justifications.isEmpty()
     */
    public static String getPrintableFormatString(ArrayList<Integer> lengths,
                                                  ArrayList<Object> toPrint,
                                                  ArrayList<Boolean> justifications) {

        String formatString = "";
        for (Object object : toPrint) {

            int index = toPrint.indexOf(object);

            // Add basic format specifier structure
            formatString += "%" + (justifications.get(index) ? "-" : "") + lengths.get(index).toString();

            // Add format specifier type character based on object type
            if (object instanceof Number && !(object instanceof Float || object instanceof Double)) {

                formatString += "d ";

            } else if (object instanceof Float || object instanceof Double) {

                formatString += ".";
                formatString += (Double.toString((Double) object).length() -
                        Double.toString((Double) object).indexOf('.') - 1);
                formatString += "f ";

            } else if (object instanceof Character) {

                formatString += "c ";

            } else {

                formatString += "s ";

            }

        }

        return String.format(formatString.trim(), toPrint.toArray());

    }

    /**
     * Helper function which assembles an SQL PreparedStatement, executes an SQL update/insertion, and
     * returns the number of rows affected.
     *
     * @param query  SQL query to execute
     * @param params List of parameters to add to PreparedStatement
     * @return Number of rows affected by update
     * @throws SQLException
     * @precondition Query.isEmpty() = false and database connection is established
     */
    public static int executeSQLUpdate(String query, ArrayList<Object> params) throws SQLException {

        // Make sure database connection is open
        if (conn == null || conn.isClosed()) {

            connect_to_db();

        }

        // Create PreparedStatement based on supplied query
        PreparedStatement ps = conn.prepareStatement(query);
        // Loop through PreparedStatement parameters
        for (int i = 0; i < params.size(); i++) {

            // Set parameter based on object type
            ps.setObject(i + 1, params.get(i).toString());

        }

        // Execute query and return number of rows affected
        return ps.executeUpdate();

    }

    /**
     * Helper function which assembles an SQL PreparedStatement, executes an SQL retrieval, and
     * returns a list of returned rows with associated column names.
     *
     * @param query  SQL query to execute
     * @param params List of parameters to add to PreparedStatement
     * @return List of result sets
     * @throws SQLException
     */
    public static ArrayList<Map<String, Object>> executeSQLRetrieve(String query,
                                                                    ArrayList<Object> params) throws SQLException {

        // Make sure database connection is open
        if (conn == null || conn.isClosed()) {

            connect_to_db();

        }

        // Create PreparedStatement based on supplied query
        PreparedStatement ps = conn.prepareStatement(query);
        // Loop through PreparedStatement parameters
        for (int i = 0; i < params.size(); i++) {

            // Set parameter based on object type
            ps.setObject(i + 1, params.get(i).toString());

        }

        // Initialize Array of Maps where each key is SQL column name and each value is SQL column value
        ArrayList<Map<String, Object>> results = new ArrayList<>();

        // Execute query
        ResultSet rs = ps.executeQuery();
        // Loop through returned results
        while (rs.next()) {

            // Create new Map for result
            Map<String, Object> result = new HashMap<>();

            ResultSetMetaData rsmd = rs.getMetaData();
            // Get column names and values for Map
            for (int i = 1; i <= rsmd.getColumnCount(); i++) {

                // Handle null values
                if (rs.getObject(rsmd.getColumnName(i)) == null) {

                    continue;

                }

                // Handle SQL dates
                if (Pattern.compile("\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2}.\\d")
                        .matcher(rs.getObject(rsmd.getColumnName(i)).toString()).matches()) {

                    result.put(rsmd.getColumnName(i), rs.getDate(rsmd.getColumnName(i)));

                } else {

                    result.put(rsmd.getColumnName(i), rs.getObject(rsmd.getColumnName(i)));

                }

            }

            // Add result to list
            results.add(result);

        }
        // Close ResultSet (best practice)
        rs.close();

        return results;

    }

    /**
     * Function which determines an Order's type (dine-in, pickup, or delivery) and
     * adds entries to the appropriate database tables.
     *
     * @param o Initialized Order object
     * @throws SQLException
     * @throws IOException
     */
    public static void addOrder(Order o) throws SQLException, IOException {

        String orderType;
        // Get order type and normalize
        if (o instanceof DineinOrder) { // dine-in order

            orderType = dine_in;

        } else if (o instanceof PickupOrder) { // pickup order

            orderType = pickup;

        } else { // delivery order

            orderType = delivery;

        }

        // SQL query to insert into the order table
        String query = "INSERT INTO `order`(OrderType, OrderTimestamp, OrderIsComplete, OrderTotalCost, OrderTotalPrice) VALUES (?, ?, ?, ?, ?);";
        // Assemble query parameters
        ArrayList<Object> params = new ArrayList<>(Arrays.asList(orderType,
                o.getDate(), o.getIsComplete(), o.getBusPrice(), o.getCustPrice()));
        // Execute query and ensure results
        if (executeSQLUpdate(query, params) != 1) {

            throw new SQLException();

        }

        // Get the OrderID of the order just added to the database
        o.setOrderID(getLastOrderID());

        // Handle database entries for each order type
        if (orderType.equals(dine_in)) { // dine-in order

            // SQL query to insert into the dine_in_order table
            query = "INSERT INTO dine_in_order(DineInOrderOrderID, DineInOrderTableNumber) VALUES (?, ?);";
            // Assemble query parameters
            params = new ArrayList<>(Arrays.asList(o.getOrderID(), ((DineinOrder) o).getTableNum()));
            // Execute query and ensure results
            if (executeSQLUpdate(query, params) != 1) {

                throw new SQLException();

            }

        } else if (orderType.equals(pickup)) { // pickup order

            // SQL query to insert into the pickup_order table
            query = "INSERT INTO pickup_order(PickupOrderOrderID, PickupOrderCustomerID) VALUES (?, ?);";
            // Assemble query parameters
            params = new ArrayList<>(Arrays.asList(o.getOrderID(), o.getCustID()));
            // Execute query and ensure results
            if (executeSQLUpdate(query, params) != 1) {

                throw new SQLException();

            }

        } else { // delivery order

            // SQL query to insert into the delivery_order table
            query = "INSERT INTO delivery_order(DeliveryOrderOrderID, DeliveryOrderCustomerID) VALUES (?, ?);";
            // Assemble query parameters
            params = new ArrayList<>(Arrays.asList(o.getOrderID(), o.getCustID()));
            // Execute query and ensure results
            if (executeSQLUpdate(query, params) != 1) {

                throw new SQLException();

            }

        }

        // Loop through all the pizzas associated with an order
        for (Pizza pizza : o.getPizzaList()) {

            // Update the PizzaOrderID based on the OrderID of the order just added to the database
            pizza.setOrderID(o.getOrderID());

            // Associate pizza with order in database
            addPizza(pizza);

        }

        // Loop through all the discounts associated with an order
        for (Discount discount : o.getDiscountList()) {

            // Associate discount with order in database
            useOrderDiscount(o, discount);

        }

        conn.close();

    }

    /**
     * Function that adds a pizza to the database.
     *
     * @param p Initialized Pizza object
     * @throws SQLException
     * @throws IOException
     */
    public static void addPizza(Pizza p) throws SQLException, IOException {

        // SQL query to insert into the pizza table
        String query = "INSERT INTO pizza(PizzaOrderID, PizzaSize, PizzaCrustType, PizzaCostToCompany, PizzaCostToCustomer, PizzaReadyState) VALUES (?, ?, ?, ?, ?, ?);";
        // Assemble query parameters
        ArrayList<Object> params = new ArrayList<>(Arrays.asList(p.getOrderID(),
                p.getSize(), p.getCrustType(), p.getBusPrice(), p.getCustPrice(), p.getPizzaState()));
        // Execute query and ensure results
        if (executeSQLUpdate(query, params) != 1) {

            throw new SQLException();

        }

        // Update PizzaID
        p.setPizzaID(getLastPizzaID());

        // Loop through toppings associated with Pizza object
        for (int i = 0; i < p.getToppings().size(); i++) {

            // Associate pizza with topping in database
            useTopping(p, p.getToppings().get(i), p.getIsDoubleArray()[p.getToppings().get(i).getTopID() - 1]);

        }

        // Loop through discounts associated with Pizza object
        for (Discount discount : p.getDiscounts()) {

            // Associate pizza with discount in database
            usePizzaDiscount(p, discount);

        }

        conn.close();

    }

    /**
     * Function which gets the PizzaID of the last pizza in the database.
     *
     * @return PizzaID
     */
    public static int getLastPizzaID() throws SQLException {

        // SQL query to retrieve from pizza table
        String query = "SELECT MAX(PizzaID) FROM pizza;";
        // Execute query and retrieve results
        ArrayList<Map<String, Object>> results = executeSQLRetrieve(query, new ArrayList<>());

        conn.close();

        // Get PizzaID
        return (int) results.get(0).get("MAX(PizzaID)");

    }

    /**
     * Function which updates topping inventory and associates a topping with a pizza in the database.
     *
     * @param p         Initialized Pizza object
     * @param t         Initialized Topping object
     * @param isDoubled Whether the topping is doubled
     * @throws SQLException
     * @throws IOException
     */
    public static void useTopping(Pizza p, Topping t, boolean isDoubled) throws SQLException, IOException {

        // SQL query to update the topping table
        String query = "UPDATE topping SET ToppingCurrentInv = ToppingCurrentInv - ? WHERE ToppingID = ?;";
        // Assemble query parameters
        ArrayList<Object> params = new ArrayList<>(Arrays.asList(t.getVarAMT(p.getSize(), isDoubled), t.getTopID()));
        // Execute query and ensure results
        if (executeSQLUpdate(query, params) != 1) {

            throw new SQLException();

        }

        // SQL query to insert into the customization table
        query = "INSERT INTO customization VALUES (?, ?, ?);";
        // Assemble query parameters
        params = new ArrayList<>(Arrays.asList(p.getPizzaID(), t.getTopID(), isDoubled ? 1 : 0));
        // Execute query and ensure results
        if (executeSQLUpdate(query, params) != 1) {

            throw new SQLException();

        }

        conn.close();

    }

    /**
     * Function which associates a discount with a pizza in the database.
     *
     * @param p Initialized Pizza object
     * @param d Initialized Discount object
     * @throws SQLException
     * @throws IOException
     */
    public static void usePizzaDiscount(Pizza p, Discount d) throws SQLException, IOException {

        // SQL query to insert to pizza_discount table
        String query = "INSERT INTO pizza_discount(PizzaDiscountPizzaID, PizzaDiscountDiscountID) VALUES (?, ?);";
        // Assemble query parameters
        ArrayList<Object> params = new ArrayList<>(Arrays.asList(p.getPizzaID(), d.getDiscountID()));
        // Execute query and ensure results
        if (executeSQLUpdate(query, params) != 1) {

            throw new SQLException();

        }

        conn.close();

    }

    /**
     * Function which associates a discount with an order in the database.
     *
     * @param o Initialized Order object
     * @param d Initialized Discount object
     * @throws SQLException
     * @throws IOException
     */
    public static void useOrderDiscount(Order o, Discount d) throws SQLException, IOException {

        // SQL query to insert to order_discount table
        String query = "INSERT INTO order_discount(OrderDiscountOrderID, OrderDiscountDiscountID) VALUES (?, ?);";
        // Assemble query parameters
        ArrayList<Object> params = new ArrayList<>(Arrays.asList(o.getOrderID(), d.getDiscountID()));
        // Execute query and ensure results
        if (executeSQLUpdate(query, params) != 1) {

            throw new SQLException();

        }

        conn.close();

    }

    /**
     * Function which adds a customer to the database.
     *
     * @param c Initialized Customer object
     * @throws SQLException
     * @throws IOException
     */
    public static void addCustomer(Customer c) throws SQLException, IOException {

        // SQL query to insert into the customer table
        String query = "INSERT INTO customer(CustomerFirstName, CustomerLastName, CustomerPhone";
        // Check whether the customer's address is populated and complete query accordingly
        if (c.getAddress() != null) { // Address is populated

            query += ", CustomerStreet, CustomerCity, CustomerState, CustomerZipCode) VALUES (?, ?, ?, ?, ?, ?, ?);";

        } else { // Address is not populated

            query += ") VALUES (?, ?, ?);";

        }

        // Assemble query parameters
        ArrayList<Object> params;
        if (c.getAddress() == null) {

            params = new ArrayList<>(Arrays.asList(c.getFName(), c.getLName(), c.getPhone()));

        } else {

            String[] address = c.getAddress().split("\n");

            params = new ArrayList<>(Arrays.asList(c.getFName(),
                    c.getLName(), c.getPhone(), address[0], address[1], address[2], address[3]));

        }

        // Execute query and ensure results
        if (executeSQLUpdate(query, params) != 1) {

            throw new SQLException();

        }

        conn.close();

    }

    /**
     * Function which updates a customer's address in the database according to the supplied CustomerID.
     *
     * @param CustID CustomerID of customer to update
     * @param street Customer street address
     * @param city   Customer city
     * @param state  Customer state
     * @param zip    Customer zip code
     */
    public static void updateCustomerAddress(int CustID,
                                             String street,
                                             String city,
                                             String state,
                                             String zip) throws SQLException {

        // SQL query to update customer table
        String query = "UPDATE customer SET CustomerStreet = ?, CustomerCity = ?, CustomerState = ?, CustomerZipCode = ? WHERE CustomerID = ?;";
        // Assemble query parameters
        ArrayList<Object> params = new ArrayList<>(Arrays.asList(street, city, state, zip, CustID));
        // Execute query and ensure results
        if (executeSQLUpdate(query, params) != 1) {

            throw new SQLException();

        }

        conn.close();

    }

    /**
     * Function that finds an order in the database and marks the order as complete.
     *
     * @param o Initialized Order object
     * @throws SQLException
     * @throws IOException
     */
    public static void completeOrder(Order o) throws SQLException, IOException {

        // SQL query to update the order table
        String query = "UPDATE `order` SET OrderIsComplete = TRUE WHERE OrderId = ?;";
        // Assemble query parameters
        ArrayList<Object> params = new ArrayList<>(List.of(o.getOrderID()));
        // Execute query and ensure results
        if (executeSQLUpdate(query, params) != 1) {

            throw new SQLException();

        }

        // Loop through all the pizzas in an order
        for (Pizza pizza : o.getPizzaList()) {

            // SQL query to update the pizza table
            query = "UPDATE pizza SET PizzaReadyState = 'ready' WHERE PizzaOrderID = ?;";
            // Assemble query parameters
            params = new ArrayList<>(List.of(o.getOrderID()));
            // Execute query and ensure results
            if (executeSQLUpdate(query, params) != 1) {

                throw new SQLException();

            }

        }

        conn.close();

    }

    /**
     * Function that retrieves a list of orders from the database.
     *
     * @param openOnly Whether to return only a list of open (not completed) orders
     * @return ArrayList of Order objects
     * @throws SQLException
     * @throws IOException
     */
    public static ArrayList<Order> getOrders(boolean openOnly) throws SQLException, IOException {

        // SQL queries that join order table with appropriate order type table
        String diOrderQuery = "SELECT * FROM `order` JOIN dine_in_order ON `order`.OrderID = dine_in_order.DineInOrderOrderID";
        String pOrderQuery = "SELECT * FROM `order` JOIN pickup_order ON `order`.OrderID = pickup_order.PickupOrderOrderID";
        String dOrderQuery = "SELECT * FROM `order` JOIN delivery_order ON `order`.OrderID = delivery_order.DeliveryOrderOrderID JOIN customer ON delivery_order.DeliveryOrderCustomerID = customer.CustomerID"; // Also joins customer table
        // If openOnly then modify each SQL query to include WHERE clause
        if (openOnly) {

            diOrderQuery += " WHERE `order`.OrderIsComplete = FALSE;";
            pOrderQuery += " WHERE `order`.OrderIsComplete = FALSE;";
            dOrderQuery += " WHERE `order`.OrderIsComplete = FALSE;";

            // Else, terminate SQL queries
        } else {

            diOrderQuery += ";";
            pOrderQuery += ";";
            dOrderQuery += ";";

        }

        // Create an ArrayList to store orders
        ArrayList<Order> orders = new ArrayList<>();

        // Execute query and retrieve results
        ArrayList<Map<String, Object>> results = executeSQLRetrieve(diOrderQuery, new ArrayList<>());
        for (Map<String, Object> result : results) {

            // Create a new DineinOrder object
            DineinOrder order = new DineinOrder((Integer) result.get("OrderID"),
                    0,
                    result.get("OrderTimestamp").toString(),
                    ((BigDecimal) result.get("OrderTotalPrice")).doubleValue(),
                    ((BigDecimal) result.get("OrderTotalCost")).doubleValue(),
                    ((Boolean) result.get("OrderIsComplete")) ? 1 : 0,
                    (Integer) result.get("DineInOrderTableNumber"));

            // Add to orders list
            orders.add(order);

        }

        // Execute query and retrieve results
        results = executeSQLRetrieve(pOrderQuery, new ArrayList<>());
        for (Map<String, Object> result : results) {

            // Create a new PickupOrder object
            PickupOrder order = new PickupOrder((Integer) result.get("OrderID"),
                    (Integer) result.get("PickupOrderCustomerID"),
                    result.get("OrderTimestamp").toString(),
                    ((BigDecimal) result.get("OrderTotalPrice")).doubleValue(),
                    ((BigDecimal) result.get("OrderTotalCost")).doubleValue(),
                    ((Boolean) result.get("PickupOrderIsPickedUp")) ? 1 : 0,
                    ((Boolean) result.get("OrderIsComplete")) ? 1 : 0);

            // Add to orders list
            orders.add(order);

        }

        // Execute query and retrieve results
        results = executeSQLRetrieve(dOrderQuery, new ArrayList<>());
        for (Map<String, Object> result : results) {

            // Create a new DeliveryOrder object
            DeliveryOrder order = new DeliveryOrder((Integer) result.get("OrderID"),
                    (Integer) result.get("DeliveryOrderCustomerID"),
                    result.get("OrderTimestamp").toString(),
                    ((BigDecimal) result.get("OrderTotalPrice")).doubleValue(),
                    ((BigDecimal) result.get("OrderTotalCost")).doubleValue(),
                    ((Boolean) result.get("OrderIsComplete")) ? 1 : 0,
                    (result.get("CustomerStreet").toString() + "\n" +
                            result.get("CustomerCity").toString() + "\n" +
                            result.get("CustomerState").toString() + "\n" +
                            result.get("CustomerZipCode").toString()));

            // Add to orders list
            orders.add(order);

        }

        // Get pizzas associated with each order
        for (Order order : orders) {

            // SQL query to retrieve from pizza table
            String pizzaQuery = "SELECT * FROM pizza WHERE PizzaOrderID = ?;";
            // Assemble query parameters
            ArrayList<Object> params = new ArrayList<>(List.of(order.getOrderID()));

            // Execute query and retrieve results
            ArrayList<Map<String, Object>> pResults = executeSQLRetrieve(pizzaQuery, params);
            for (Map<String, Object> pResult : pResults) {

                // Create a new Pizza object
                Pizza pizza = new Pizza((Integer) pResult.get("PizzaID"),
                        pResult.get("PizzaSize").toString(),
                        pResult.get("PizzaCrustType").toString(),
                        order.getOrderID(),
                        pResult.get("PizzaReadyState").toString(),
                        order.getDate(),
                        ((BigDecimal) pResult.get("PizzaCostToCustomer")).doubleValue(),
                        ((BigDecimal) pResult.get("PizzaCostToCompany")).doubleValue());

                // SQL query to retrieve from customization table
                String customizationQuery = "SELECT * FROM customization JOIN topping ON customization.CustomizationToppingID = topping.ToppingID WHERE CustomizationPizzaID = ?;";
                // Assemble query parameters
                params = new ArrayList<>(List.of(pizza.getPizzaID()));

                // Execute query and retrieve results
                ArrayList<Map<String, Object>> tResults = executeSQLRetrieve(customizationQuery, params);
                for (Map<String, Object> tResult : tResults) {

                    // Create a new Topping object
                    Topping topping = new Topping((Integer) tResult.get("ToppingID"),
                            tResult.get("ToppingName").toString(),
                            ((BigDecimal) tResult.get("ToppingAmtUsedPersonal")).doubleValue(),
                            ((BigDecimal) tResult.get("ToppingAmtUsedMedium")).doubleValue(),
                            ((BigDecimal) tResult.get("ToppingAmtUsedLarge")).doubleValue(),
                            ((BigDecimal) tResult.get("ToppingAmtUsedXLarge")).doubleValue(),
                            ((BigDecimal) tResult.get("ToppingPrice")).doubleValue(),
                            ((BigDecimal) tResult.get("ToppingCost")).doubleValue(),
                            ((BigDecimal) tResult.get("ToppingMinimumInv")).doubleValue(),
                            ((BigDecimal) tResult.get("ToppingCurrentInv")).doubleValue());

                    // Save current price and cost
                    double currentPrice = pizza.getCustPrice();
                    double currentCost = pizza.getBusPrice();

                    // Add topping to pizza
                    pizza.addToppings(topping, ((Boolean) tResult.get("CustomizationToppingIsDoubled")));

                    // Offset customer price and business cost
                    pizza.setCustPrice(currentPrice);
                    pizza.setBusPrice(currentCost);

                }

                // SQL query to retrieve from pizza_discount table
                String discountQuery = "SELECT * FROM pizza_discount JOIN discount ON pizza_discount.PizzaDiscountDiscountID = discount.DiscountID WHERE PizzaDiscountPizzaID = ?;";
                // Assemble query parameters
                params = new ArrayList<>(List.of(pizza.getPizzaID()));

                // Execute query and retrieve results
                ArrayList<Map<String, Object>> dResults = executeSQLRetrieve(discountQuery, params);
                for (Map<String, Object> dResult : dResults) {

                    // Create a new Discount object
                    Discount discount = new Discount((Integer) dResult.get("DiscountID"),
                            dResult.get("DiscountName").toString(),
                            ((BigDecimal) dResult.get("DiscountDollarAmt")).doubleValue(),
                            false);

                    // Save current pizza price
                    double currentPrice = pizza.getCustPrice();

                    // Add discount to pizza
                    pizza.addDiscounts(discount);

                    // Offset customer price
                    pizza.setCustPrice(currentPrice);

                }

                // Add pizza to order
                order.addPizza(pizza);

            }

            // SQL query to retrieve from order_discount table
            String discountQuery = "SELECT * FROM order_discount JOIN discount ON order_discount.OrderDiscountDiscountID = discount.DiscountID WHERE order_discount.OrderDiscountOrderID = ?;";
            // Assemble query parameters
            params = new ArrayList<>(List.of(order.getOrderID()));

            // Execute query and retrieve results
            results = executeSQLRetrieve(discountQuery, params);
            for (Map<String, Object> result : results) {

                // Create a new Discount object
                Discount discount = new Discount((Integer) result.get("DiscountID"),
                        result.get("DiscountName").toString(),
                        ((BigDecimal) result.get("DiscountPercent")).doubleValue(),
                        true);

                // Save current customer price
                double currentPrice = order.getCustPrice();

                // Add discount to order
                order.addDiscount(discount);

                // Offset customer price
                order.setCustPrice(currentPrice);

            }

        }

        // Sort orders by OrderTimestamp
        Collections.sort(orders, OrderTimestampComparator);

        conn.close();

        return orders;

    }

    /**
     * Helper function which prints the list of orders.
     *
     * @param orders List of Order objects
     */
    public static void printOrders(ArrayList<Order> orders) {

        for (Order order : orders) {

            System.out.println(order.toSimplePrint());

        }

    }

    /**
     * Function that retrieves the OrderID of the most recent order in the database.
     *
     * @return OrderID of most recent order
     */
    public static int getLastOrderID() throws SQLException {

        // SQL query to retrieve from order table
        String query = "SELECT * FROM `order` WHERE OrderID = (SELECT MAX(OrderID) FROM `order`);";

        // Execute query and retrieve results
        ArrayList<Map<String, Object>> results = executeSQLRetrieve(query, new ArrayList<>());

        conn.close();

        // Extract and return OrderID
        return (int) results.get(0).get("OrderID");

    }

    /**
     * Function that retrieves the most recent order in the database.
     *
     * @return Most recent order as Order object
     * @throws SQLException
     * @throws IOException
     */
    public static Order getLastOrder() throws SQLException, IOException {

        return getOrderByID(getLastOrderID());

    }

    /**
     * Helper function which determines if an order exists based on its OrderID.
     *
     * @param orders  List of orders to check
     * @param orderID OrderID being searched for
     * @return Whether order with specified OrderID exists
     */
    public static boolean orderExists(ArrayList<Order> orders, int orderID) {

        // Loop through orders
        for (Order order : orders) {

            if (order.getOrderID() == orderID) {

                return true;

            }

        }

        return false;

    }

    /**
     * Helper function which returns an Order object matching the supplied OrderID.
     *
     * @param orderID OrderID being searched for
     * @return Found Order object or null
     * @throws SQLException
     * @throws IOException
     */
    public static Order getOrderByID(int orderID) throws SQLException, IOException {

        // Get list of orders
        ArrayList<Order> orders = getOrders(false);

        // Loop through orders
        for (Order order : orders) {

            if (order.getOrderID() == orderID) {

                return order;

            }

        }

        return null;

    }

    /**
     * Function that retrieves a list of orders from the database with a timestamp
     * matching the specified date.
     *
     * @param date Date string in format 'YYYY-MM-DD HH:mm:ss'
     * @return ArrayList of orders with matching dates as Order objects
     * @throws SQLException
     * @throws IOException
     */
    public static ArrayList<Order> getOrdersByDate(String date) throws SQLException, IOException {

        // Use existing method to get a list of all orders
        ArrayList<Order> orders = getOrders(false);

        // Create a new ArrayList to store orders matching the target date
        ArrayList<Order> ordersByDate = new ArrayList<>();
        // Loop through the orders
        for (Order order : orders) {

            // If the date matches, add it to ordersByDate
            if (order.getDate().equals(date)) {

                ordersByDate.add(order);

            }

        }

        return ordersByDate;

    }

    /**
     * Function which prints discounts.
     *
     * @param discounts List of discounts from database
     */
    public static void printDiscounts(ArrayList<Discount> discounts) {

        for (Discount discount : discounts) {

            System.out.println(discount.toString());

        }

    }

    /**
     * Function that retrieves a list of discounts from the database.
     *
     * @return ArrayList of discounts represented as Discount objects
     * @throws SQLException
     * @throws IOException
     */
    public static ArrayList<Discount> getDiscountList() throws SQLException, IOException {

        // Create an ArrayList to store Discount objects
        ArrayList<Discount> discounts = new ArrayList<>();

        // SQL query to retrieve from the discount table
        String query = "SELECT * FROM discount;";

        // Execute query and retrieve results
        ArrayList<Map<String, Object>> results = executeSQLRetrieve(query, new ArrayList<>());
        for (Map<String, Object> result : results) {

            // Create a new Discount object
            Discount discount = new Discount((Integer) result.get("DiscountID"),
                    result.get("DiscountName").toString(),
                    result.get("DiscountType").toString().equals("order") ?
                            ((BigDecimal) result.get("DiscountPercent")).doubleValue() :
                            ((BigDecimal) result.get("DiscountDollarAmt")).doubleValue(),
                    result.get("DiscountType").toString().equals("order"));

            // Add discount to discounts array
            discounts.add(discount);

        }

        conn.close();

        return discounts;

    }

    /**
     * Function that retrieves a discount from the database matching the specified name.
     *
     * @param name Name of discount being searched for
     * @return Discount matching the specified name or null if no match
     * @throws SQLException
     * @throws IOException
     */
    public static Discount findDiscountByName(String name) throws SQLException, IOException {

        // Use existing method to get list of discounts
        ArrayList<Discount> discounts = getDiscountList();

        // Loop through discounts
        for (Discount discount : discounts) {

            // If a match is found for discount name, return the discount
            if (discount.getDiscountName().equals(name)) {

                return discount;

            }

        }

        // If no match has been found
        return null;

    }

    /**
     * Function that retrieves a list of customers from the database.
     *
     * @return ArrayList of customers represented as Customer objects
     * @throws SQLException
     * @throws IOException
     */
    public static ArrayList<Customer> getCustomerList() throws SQLException, IOException {

        // Create an ArrayList to store Customer objects
        ArrayList<Customer> customers = new ArrayList<>();

        // SQL query to retrieve from the customer table
        String query = "SELECT * FROM customer ORDER BY CustomerLastName, CustomerFirstName, CustomerPhone;";

        // Execute query and retrieve results
        ArrayList<Map<String, Object>> results = executeSQLRetrieve(query, new ArrayList<>());
        for (Map<String, Object> result : results) {

            // Create a new Customer object
            Customer customer = new Customer((Integer) result.get("CustomerID"),
                    result.get("CustomerFirstName").toString(),
                    result.get("CustomerLastName").toString(),
                    result.get("CustomerPhone").toString());

            // Check to see if returned row has address attributes populated
            if (result.containsKey("CustomerStreet") &&
                    result.containsKey("CustomerCity") &&
                    result.containsKey("CustomerState") &&
                    result.containsKey("CustomerZipCode")) {

                // Update address attributes
                customer.setAddress(result.get("CustomerStreet").toString(),
                        result.get("CustomerCity").toString(),
                        result.get("CustomerState").toString(),
                        result.get("CustomerZipCode").toString());

            }

            // Add customer to list
            customers.add(customer);

        }

        conn.close();

        return customers;

    }

    /**
     * Function that retrieves the CustomerID of the last customer record in the database.
     *
     * @return CustomerID of last customer
     */
    public static int getLatestCustomerID() throws SQLException, IOException {

        // Use existing method to get all customers
        ArrayList<Customer> customers = getCustomerList();

        // Sort customers list
        Collections.sort(customers, CustomerIDComparator);

        return customers.get(customers.size() - 1).getCustID();

    }

    /**
     * Function that retrieves the customer with the supplied phone number.
     *
     * @param phoneNumber Phone number being searched for in format '##########'
     * @return Customer with matching phone number as a Customer object or null if no match
     * @throws SQLException
     * @throws IOException
     */
    public static Customer findCustomerByPhone(String phoneNumber) throws SQLException, IOException {

        // Use existing method to get all customers
        ArrayList<Customer> customers = getCustomerList();

        // Loop through customers
        for (Customer customer : customers) {

            // If the customer's phone matches the target, return it
            if (customer.getPhone().equals(phoneNumber)) {

                return customer;

            }

        }

        // If a matching customer has not been found
        return null;

    }

    /**
     * Function which retrieves a list of toppings from the database.
     *
     * @return ArrayList of toppings represented as Topping objects
     * @throws SQLException
     * @throws IOException
     */
    public static ArrayList<Topping> getToppingList() throws SQLException, IOException {

        // Create an ArrayList to store Topping objects
        ArrayList<Topping> toppings = new ArrayList<>();

        // SQL query to retrieve from the topping table
        String query = "SELECT * FROM topping ORDER BY ToppingName ASC;";

        // Execute query and retrieve results
        ArrayList<Map<String, Object>> results = executeSQLRetrieve(query, new ArrayList<>());
        for (Map<String, Object> result : results) {

            // Create a new Topping object
            Topping topping = new Topping((Integer) result.get("ToppingID"),
                    result.get("ToppingName").toString(),
                    ((BigDecimal) result.get("ToppingAmtUsedPersonal")).doubleValue(),
                    ((BigDecimal) result.get("ToppingAmtUsedMedium")).doubleValue(),
                    ((BigDecimal) result.get("ToppingAmtUsedLarge")).doubleValue(),
                    ((BigDecimal) result.get("ToppingAmtUsedXLarge")).doubleValue(),
                    ((BigDecimal) result.get("ToppingPrice")).doubleValue(),
                    ((BigDecimal) result.get("ToppingCost")).doubleValue(),
                    ((BigDecimal) result.get("ToppingMinimumInv")).doubleValue(),
                    ((BigDecimal) result.get("ToppingCurrentInv")).doubleValue());

            // Add topping to list
            toppings.add(topping);

        }

        conn.close();

        return toppings;

    }

    /**
     * Helper function which retrieves a topping by its ID.
     *
     * @param toppings  List of Topping objects
     * @param toppingID ID of topping being searched for
     * @return Found Topping object or null
     */
    public static Topping getToppingByID(ArrayList<Topping> toppings, int toppingID) {

        // Loop through toppings
        for (Topping topping : toppings) {

            if (topping.getTopID() == toppingID) {

                return topping;

            }

        }

        return null;

    }

    /**
     * Function which retrieves a topping by its name.
     *
     * @param name Name of the topping being searched for
     * @return Topping matching the supplied name as a Topping object or null if no match
     * @throws SQLException
     * @throws IOException
     */
    public static Topping findToppingByName(String name) throws SQLException, IOException {

        // Use existing method to get list of toppings
        ArrayList<Topping> toppings = getToppingList();

        // Loop through toppings
        for (Topping topping : toppings) {

            // If topping name matches, return the topping
            if (topping.getTopName().equals(name)) {

                return topping;

            }

        }

        // If no match has been found
        return null;

    }

    /**
     * Function which updates the quantity of a topping in the database by the amount specified.
     *
     * @param t        Initialized Topping object being updated
     * @param quantity Quantity to update by
     * @throws SQLException
     * @throws IOException
     */
    public static void addToInventory(Topping t, double quantity) throws SQLException, IOException {

        // SQL query to update topping table
        String query = "UPDATE topping SET ToppingCurrentInv = ToppingCurrentInv + ? WHERE ToppingID = ?;";
        // Assemble query parameters
        ArrayList<Object> params = new ArrayList<>(Arrays.asList(quantity, t.getTopID()));
        // Execute query and ensure results
        if (executeSQLUpdate(query, params) != 1) {

            throw new SQLException();

        }

    }

    /**
     * Function which retrieves the base customer price for a pizza with the specified size and crust type.
     *
     * @param size  Size of the pizza
     * @param crust Crust type of the pizza
     * @return Dollar amount
     * @throws SQLException
     * @throws IOException
     */
    public static double getBaseCustPrice(String size, String crust) throws SQLException, IOException {

        // SQL query to retrieve from the base_price table
        String query = "SELECT * FROM base_price WHERE BasePricePizzaSize = ? AND BasePricePizzaCrustType = ?;";
        // Assemble query parameters
        ArrayList<Object> params = new ArrayList<>(Arrays.asList(size, crust));

        // Execute query and retrieve results
        ArrayList<Map<String, Object>> results = executeSQLRetrieve(query, params);

        conn.close();

        return ((BigDecimal) results.get(0).get("BasePricePriceToCustomer")).doubleValue();

    }

    /**
     * Function which retrieves the base business price for a pizza with the specified size and crust type.
     *
     * @param size  Size of the pizza
     * @param crust Crust type of the pizza
     * @return Dollar amount
     * @throws SQLException
     * @throws IOException
     */
    public static double getBaseBusPrice(String size, String crust) throws SQLException, IOException {

        // SQL query to retrieve from the base_price table
        String query = "SELECT * FROM base_price WHERE BasePricePizzaSize = ? AND BasePricePizzaCrustType = ?;";
        // Assemble query parameters
        ArrayList<Object> params = new ArrayList<>(Arrays.asList(size, crust));

        // Execute query and retrieve results
        ArrayList<Map<String, Object>> results = executeSQLRetrieve(query, params);

        conn.close();

        return ((BigDecimal) results.get(0).get("BasePriceCostToCompany")).doubleValue();

    }

    /**
     * Function which prints the current inventory levels.
     *
     * @throws SQLException
     * @throws IOException
     */
    public static void printInventory() throws SQLException, IOException {

        // Get list of toppings as Topping objects
        ArrayList<Topping> toppings = getToppingList();

        // Call helper function to print
        printInventoryLevels(toppings);

    }

    /**
     * Helper function which prints a list of toppings. Used to handle printing anonymous to
     * order of Topping objects.
     *
     * @param toppings List of Topping objects
     */
    public static void printInventoryLevels(ArrayList<Topping> toppings) {

        // Print heading row
        System.out.println(getPrintableFormatString(new ArrayList<>(Arrays.asList(2, 17, 7)),
                new ArrayList<>(Arrays.asList("ID", "Name", "CurINVT")),
                new ArrayList<>(Arrays.asList(true, true, true))));
        // Loop through the toppings
        for (Topping topping : toppings) {

            // Print topping ID, name, and current inventory
            System.out.println(getPrintableFormatString(new ArrayList<>(Arrays.asList(2, 17, 7)),
                    new ArrayList<>(Arrays.asList(topping.getTopID(), topping.getTopName(), topping.getCurINVT())),
                    new ArrayList<>(Arrays.asList(true, true, true))));

        }

    }

    /**
     * Function which retrieves and prints the ToppingPopularity view from the database.
     *
     * @throws SQLException
     * @throws IOException
     */
    public static void printToppingPopReport() throws SQLException, IOException {

        // SQL query to retrieve ToppingPopularity report
        String query = "SELECT * FROM ToppingPopularity;";

        // Print heading row
        System.out.println(getPrintableFormatString(new ArrayList<>(Arrays.asList(17, 12)),
                new ArrayList<>(Arrays.asList("Topping", "ToppingCount")),
                new ArrayList<>(Arrays.asList(true, true))));

        // Execute query and retrieve results
        ArrayList<Map<String, Object>> results = executeSQLRetrieve(query, new ArrayList<>());
        for (Map<String, Object> result : results) {

            // Print row
            System.out.println(getPrintableFormatString(new ArrayList<>(Arrays.asList(17, 12)),
                    new ArrayList<>(Arrays.asList(result.get("Topping"), result.get("ToppingCount"))),
                    new ArrayList<>(Arrays.asList(true, true))));

        }

        conn.close();

    }

    /**
     * Function which retrieves and prints the ProfitByPizza view from the database.
     *
     * @throws SQLException
     * @throws IOException
     */
    public static void printProfitByPizzaReport() throws SQLException, IOException {

        // SQL query to retrieve ProfitByPizza report
        String query = "SELECT * FROM ProfitByPizza;";

        // Print heading row
        System.out.println(getPrintableFormatString(new ArrayList<>(Arrays.asList(10, 11, 10, 11)),
                new ArrayList<>(Arrays.asList("Pizza Size", "Pizza Crust", "Profit", "Order Month")),
                new ArrayList<>(Arrays.asList(true, true, true, true))));

        // Execute query and retrieve results
        ArrayList<Map<String, Object>> results = executeSQLRetrieve(query, new ArrayList<>());
        for (Map<String, Object> result : results) {

            // Print row
            System.out.println(getPrintableFormatString(new ArrayList<>(Arrays.asList(10, 11, 10, 11)),
                    new ArrayList<>(Arrays.asList(result.get("Size"),
                            result.get("Crust"),
                            ((BigDecimal) result.get("Profit")).doubleValue(),
                            result.get("Order Month"))),
                    new ArrayList<>(Arrays.asList(true, true, true, true))));

        }

        conn.close();

    }

    /**
     * Function which retrieves and prints the ProfitByOrderType view from the database.
     *
     * @throws SQLException
     * @throws IOException
     */
    public static void printProfitByOrderType() throws SQLException, IOException {

        // SQL query to retrieve ProfitByOrderType report
        String query = "SELECT * FROM ProfitByOrderType;";

        // Print heading row
        System.out.println(getPrintableFormatString(new ArrayList<>(Arrays.asList(9, 11, 15, 14, 10)),
                new ArrayList<>(Arrays.asList("OrderType",
                        "Order Month", "TotalOrderPrice", "TotalOrderCost", "Profit")),
                new ArrayList<>(Arrays.asList(true, true, true, true, true))));

        // Execute query and retrieve results
        ArrayList<Map<String, Object>> results = executeSQLRetrieve(query, new ArrayList<>());
        for (Map<String, Object> result : results) {

            // Print row
            System.out.println(getPrintableFormatString(new ArrayList<>(Arrays.asList(9, 11, 15, 14, 10)),
                    new ArrayList<>(Arrays.asList(result.get("customerType"),
                            result.get("Order Month"),
                            ((BigDecimal) result.get("TotalOrderPrice")).doubleValue(),
                            ((BigDecimal) result.get("TotalOrderCost")).doubleValue(),
                            ((BigDecimal) result.get("Profit")).doubleValue())),
                    new ArrayList<>(Arrays.asList(true, true, true, true, true))));

        }

        conn.close();

    }

    /**
     * Function which retrieves the name of a customer matching the specified CustomerID.
     *
     * @param CustID CustomerID of customer
     * @return Name of customer
     * @throws SQLException
     * @throws IOException
     */
    public static String getCustomerName(int CustID) throws SQLException, IOException {

        connect_to_db();

        // If the supplied customerID is 0, which is used for dine-in orders, return "N/A" to indicate
        // no customer details
        if (CustID == 0) {

            return "N/A";

        }

        // Use existing method to get list of customers
        ArrayList<Customer> customers = getCustomerList();

        // Variable to store customer name
        String customerName = "";

        // Loop through customers
        for (Customer customer : customers) {

            // If the current customer's CustomerID matches the specified CustomerID
            if (customer.getCustID() == CustID) {

                // Assemble the customer's name
                customerName = customer.getFName() + " " + customer.getLName();

            }

        }

        return customerName;

    }

    private static int getYear(String date) {

        // Assumes date format 'YYYY-MM-DD HH:mm:ss'
        return Integer.parseInt(date.substring(0, 4));

    }

    private static int getMonth(String date) {

        // Assumes date format 'YYYY-MM-DD HH:mm:ss'
        return Integer.parseInt(date.substring(5, 7));

    }

    private static int getDay(String date) {

        // Assumes date format 'YYYY-MM-DD HH:mm:ss'
        return Integer.parseInt(date.substring(8, 10));

    }

    public static boolean checkDate(int year, int month, int day, String dateOfOrder) {

        if (getYear(dateOfOrder) > year) {

            return true;

        } else if (getYear(dateOfOrder) < year) {

            return false;

        } else {

            if (getMonth(dateOfOrder) > month) {

                return true;

            } else if (getMonth(dateOfOrder) < month) {

                return false;

            } else {

                return getDay(dateOfOrder) >= day;

            }

        }

    }

}