package cpsc4620.antonspizza;

import java.io.*;
import java.sql.*;
import java.util.*;

/*
This file is where most of your code changes will occur
You will write the code to retrieve information from the database, or save information to the database

The class has several hard coded static variables used for the connection, you will need to change those to your connection information

This class also has static string variables for pickup, delivery and dine-in. If your database stores the strings differently (i.e "pick-up" vs "pickup") changing these static variables will ensure that the comparison is checking for the right string in other places in the program. You will also need to use these strings if you store this as boolean fields or an integer.


*/

/**
 * A utility class to help add and retrieve information from the database
 */

public final class DBNinja {
    //enter your user name here
    private static String user = "AntnPzr_rdqc";
    //enter your password here
    private static String password = "zse4xdr5";
    //enter your database name here
    private static String database_name = "AntonPizzeria_zyl7";
    //Do not change the port. 3306 is the default MySQL port
    private static String port = "3306";
    private static Connection conn;

    //Change these variables to however you record dine-in, pick-up and delivery, and sizes and crusts
    public final static String pickup = "pickup";
    public final static String delivery = "delivery";
    public final static String dine_in = "dinein";

    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";

    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 = "glutenfree";



    /**
     * This function will handle the connection to the database
     * @return true if the connection was successfully made
     * @throws SQLException
     * @throws IOException
     */
    private static boolean connect_to_db() throws SQLException, IOException
    {
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            System.out.println ("Could not load the driver");

            System.out.println("Message     : " + e.getMessage());


            return false;
        }


        conn = DriverManager.getConnection("jdbc:mysql://mysql1.cs.clemson.edu:"+port+"/"+database_name, user, password);
        return true;
    }

    /**
     *
     * @param o order that needs to be saved to the database
     * @throws SQLException
     * @throws IOException
     * @requires o is not NULL. o's ID is -1, as it has not been assigned yet. The pizzas do not exist in the database
     *          yet, and the topping inventory will allow for these pizzas to be made
     * @ensures o will be assigned an id and added to the database, along with all of it's pizzas. Inventory levels
     *          will be updated appropriately
     */
    public static void addOrder(Order o) throws SQLException, IOException
    {
        connect_to_db();

		/* add code to add the order to the DB.
		 *
		 * Remember to add the pizzas and discounts as well, which will involve multiple tables.
		 *
		 * Customer should already exist. Toppings will need to be added to the pizzas.
		 *
		 * It may be beneficial to define more functions to add an individual pizza to a database, add a topping to a pizza, etc.
		 *
		 * Note: the order ID will be -1 and will need to be replaced to be a fitting primary key.
		 *
		 * You will also need to add timestamps to your pizzas/orders in your database.
		 * >done
		 *
		 * Those timestamps are not stored in this program, but you can get the current time before inserting into the database
		 *
		 *
		 * Remember, when a new order comes in the ingredient levels for the topping need to be adjusted accordingly
		 *
		 * Remember to check for "extra" of a topping here as well.
		 *
		 * You do not need to check to see if you have the topping in stock before adding to a pizza. You can just let it go negative.
		*/


        //queries
        String orderNumQuery = "SELECT MAX(order_number) FROM Orders;";

        String orderQuery = "INSERT INTO orders VALUES (?);";

        String pizzaNumQuery = "SELECT MAX(pizzaID) FROM pizza;";
        String pizzaQuery = "INSERT INTO pizza VALUES (?, ?, ?, ?, ?, ?, ?, ?);";

        String pizzaToppingsQuery = "INSERT INTO pizzaToppings VALUES (?, ?, ?);";

        String discountedPizzaQuery = "INSERT INTO discountedPizza VALUES (?, ?);";
        String discountedOrderQuery = "INSERT INTO discountedOrder VALUES (?, ?);";

        String dineinQuery = "INSERT INTO dinein VALUES (?, ?);";
        String pickupQuery = "INSERT INTO pickup VALUES (?, ?);";
        String deliveryQuery = "INSERT INTO delivery VALUES (?, ?);";

        String dineInSeatQuery = "INSERT INTO dineInSeat VALUES (?, ?);";

        //preparedStatements

        Statement orderNumCheck = conn.createStatement();
        PreparedStatement orderAdd = conn.prepareStatement(orderQuery);

        Statement pizzaNumCheck = conn.createStatement();
        PreparedStatement pizzaAdd = conn.prepareStatement(pizzaQuery);

        PreparedStatement pizzaToppingsAdd = conn.prepareStatement(pizzaToppings);

        PreparedStatement discountedPizzaAdd = conn.prepareStatement(discountedPizzaQuery);
        PreparedStatement discountedOrderAdd = conn.prepareStatement(discountedOrderQuery);

        PreparedStatement dineinAdd = conn.prepareStatement(dineinQuery);
        PreparedStatement pickupAdd = conn.prepareStatement(pickupQuery);
        PreparedStatement deliveryAdd = conn.prepareStatement(deliveryQuery);
        PreparedStatement dineInSeatAdd = conn.prepareStatement(dineInSeatQuery);


        //Timestamp
        SimpleDateFormat dateForm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        ResultSet order_numLatest = orderNumCheck.executeQuery(orderNumQuery);
        ResultSet pizza_numLatest;














		string query = "INSERT INTO order VALUES" +
        Statement statement = conn.createStatement();


        conn.close();

    }

    /**
     *
     * @param c the new customer to add to the database
     * @throws SQLException
     * @throws IOException
     * @requires c is not null. C's ID is -1 and will need to be assigned
     * @ensures c is given an ID and added to the database
     */
    public static void addCustomer(ICustomer c) throws SQLException, IOException
    {
        connect_to_db();
		/*add code to add the customer to the DB.
		Note: the ID will be -1 and will need to be replaced to be a fitting primary key
		Note that the customer is an ICustomer data type, which means c could be a dine in, carryout or delivery customer
		*/

        c = new ICustomer();






        conn.close();
    }

    /**
     *
     * @param o the order to mark as complete in the database
     * @throws SQLException
     * @throws IOException
     * @requires the order exists in the database
     * @ensures the order will be marked as complete
     */
    public static void CompleteOrder(Order o) throws SQLException, IOException
    {
        connect_to_db();
		/*add code to mark an order as complete in the DB. You may have a boolean field for this, or maybe a completed time timestamp. However you have it, */

		String query = "";
        conn.close();
    }

    /**
     *
     * @param t the topping whose inventory is being replenished
     * @param toAdd the amount of inventory of t to add
     * @throws SQLException
     * @throws IOException
     * @requires t exists in the database and toAdd > 0
     * @ensures t's inventory level is increased by toAdd
     */
    public static void AddToInventory(Topping t, double toAdd) throws SQLException, IOException
    {
        connect_to_db();
		/*add code to add toAdd to the inventory level of T
		 * This is not adding a new topping, it is adding a certain amount of stock for a topping
		 * This would be used to show that an order was made to replenish the restaurants supply of pepperoni, etc
		 */


        conn.close();
    }


    /*
        A function to get the list of toppings and their inventory levels. I have left this code "complete" as an example of how to use JDBC to get data from the database. This query will not work on your database if you have different field or table names, so it will need to be changed

        Also note, this is just getting the topping ids and then calling getTopping() to get the actual topping. You will need to complete this on your own

        You don't actually have to use and write the getTopping() function, but it can save some repeated code if the program were to expand, and it keeps the functions simpler, more elegant and easy to read. Breaking up the queries this way also keeps them simpler. I think it's a better way to do it, and many people in the industry would agree, but its a suggestion, not a requirement.
    */

    /**
     *
     * @return the List of all toppings in the database
     * @throws SQLException
     * @throws IOException
     * @ensures the returned list will include all toppings and accurate inventory levels
     */
    public static ArrayList<Topping> getInventory() throws SQLException, IOException
    {
        //start by connecting
        connect_to_db();
        ArrayList<Topping> ts = new ArrayList<Topping>();
        //create a string with out query, this one is an easy one
        String query = "SELECT toppingID, inventory_level From toppings;";

        Statement stmt = conn.createStatement();
        try {
            ResultSet rset = stmt.executeQuery(query);
            //even if you only have one result, you still need to call ResultSet.next() to load the first tuple
            while(rset.next())
            {
					/*Use getInt, getDouble, getString to get the actual value. You can use the column number starting with 1, or use the column name as a string

					NOTE: You want to use rset.getInt() instead of Integer.parseInt(rset.getString()), not just because it's shorter, but because of the possible NULL values. A NUll would cause parseInt to fail

					If there is a possibility that it could return a NULL value you need to check to see if it was NULL. In this query we won't get nulls, so I didn't. If I was going to I would do:

					int ID = rset.getInt(1);
					if(rset.wasNull())
					{
						//set ID to what it should be for NULL, and whatever you need to do.
					}

					NOTE: you can't check for NULL until after you have read the value using one of the getters.

					*/
                int ID = rset.getInt(1);
                //Now I'm just passing my primary key to this function to get the topping itself individually
                ts.add(getTopping(ID));
            }
        }
        catch (SQLException e) {
            System.out.println("Error loading inventory");
            while (e != null) {
                System.out.println("Message     : " + e.getMessage());
                e = e.getNextException();
            }

            //don't leave your connection open!
            conn.close();
            return ts;
        }


        //end by closing the connection
        conn.close();
        return ts;
    }

    /**
     *
     * @return a list of all orders that are currently open in the kitchen
     * @throws SQLException
     * @throws IOException
     * @ensures all currently open orders will be included in the returned list.
     */
    public static ArrayList<Order> getCurrentOrders() throws SQLException, IOException
    {
        connect_to_db();

        ArrayList<Order> os = new ArrayList<Order>();
		/*add code to get a list of all open orders. Only return Orders that have not been completed. If any pizzas are not completed, then the order is open.*/

        conn.close();
        return os;
    }

    /**
     *
     * @param size the pizza size
     * @param crust the type of crust
     * @return the base price for a pizza with that size and crust
     * @throws SQLException
     * @throws IOException
     * @requires size = size_s || size_m || size_l || size_xl AND crust = crust_thin || crust_orig || crust_pan || crust_gf
     * @ensures the base price for a pizza with that size and crust is returned
     */
    public static double getBasePrice(String size, String crust) throws SQLException, IOException
    {
        connect_to_db();
        double bp = 0.0;
        //add code to get the base price for that size and crust pizza Depending on how you store size and crust in your database, you may have to do a conversion

        String query = "SELECT price FROM pizza WHERE crust_type = " + crust + " AND size = " + size + ";";

        Statement statement = conn.createStatement();
        try {
            ResultSet rset = statement.executeQuery(query);

            while (rset.next()) {

                int ID = getInt(1);
            }
        }
        catch (SQLException e) {
            System.out.println("Error loading base price");
            while (e != null) {
                System.out.println("Message     : " + e.getMessage());
                e = e.getNextException();
            }

        conn.close();
        return bp;
    }

    /**
     *
     * @return the list of all discounts in the database
     * @throws SQLException
     * @throws IOException
     * @ensures all discounts are included in the returned list
     */
    public static ArrayList<Discount> getDiscountList() throws SQLException, IOException
    {
        ArrayList<Discount> discs = new ArrayList<Discount>();
        connect_to_db();
        //add code to get a list of all discounts
        //SELECT * FROM discounts; for all discounts
        //SELECT discountID FROM discounts; for all discount ID numbers only
        String query = "SELECT discountID FROM discounts;";

        Statement statement = conn.createStatement();
        try {
            ResultSet rset = statement.executeQuery(query);

            while (rset.next()) {

                int ID = getInt(1);
                discs.add(getDiscount(ID));
            }
        }
        catch (SQLException e) {
            System.out.println("Error loading discount list");
            while (e != null) {
                System.out.println("Message     : " + e.getMessage());
                e = e.getNextException();
            }

        conn.close();
        return discs;
    }

    /**
     *
     * @return the list of all delivery and carry out customers
     * @throws SQLException
     * @throws IOException
     * @ensures the list contains all carryout and delivery customers in the database
     */
    public static ArrayList<ICustomer> getCustomerList() throws SQLException, IOException
    {
        ArrayList<ICustomer> custs = new ArrayList<ICustomer>();
        connect_to_db();
        //add code to get a list of all customers
        String query = "SELECT DISTINCT c.customerID, c.Fname, c.Mname, c.Lname, c.phone_number " +
                       "FROM customer AS c JOIN orders ON c.customerID = orders.customerID " +
                       "WHERE order_type = 'delivery' OR order_type = 'pickup';";

        Statement statement = conn.createStatement();
        try {
            ResultSet rset = statement.executeQuery(query);

            while (rset.next()) {

                int ID = getInt(1);
                custs.add(getICustomer(ID));
            }
        }
        catch (SQLException e) {
            System.out.println("Error loading customer list");
            while (e != null) {
                System.out.println("Message     : " + e.getMessage());
                e = e.getNextException();
            }

        conn.close();
        return custs;
    }



	/*
	Note: The following incomplete functions are not strictly required, but could make your DBNinja class much simpler. For instance, instead of writing one query to get all of the information about an order, you can find the primary key of the order, and use that to find the primary keys of the pizzas on that order, then use the pizza primary keys individually to build your pizzas. We are no longer trying to get everything in one query, so feel free to break them up as much as possible

	You could also add functions that take in a Pizza object and add that to the database, or take in a pizza id and a topping id and add that topping to the pizza in the database, etc. I would recommend this to keep your addOrder function much simpler

	These simpler functions should still not be called from our menu class. That is why they are private

	We don't need to open and close the connection in these, since they are only called by a function that has opened the connection and will close it after
	*/


    private static Topping getTopping(int ID) throws SQLException, IOException
    {

        //add code to get a topping
		//the java compiler on unix does not like that t could be null, so I created a fake topping that will be replaced
        Topping t = new Topping("fake", 0.25, 100.0, -1);
		String query = "SELECT tname, company_price, inventory_level FROM toppings WHERE toppingID = " + ID + ";";

        Statement stmt = conn.createStatement();
        try {
            ResultSet rset = stmt.executeQuery(query);
            //even if you only have one result, you still need to call ResultSet.next() to load the first tuple
            while(rset.next())
            {
					String tname = rset.getString(1);
					double price = rset.getDouble(2);
					double inv = rset.getDouble(3);

					t = new Topping(tname, price, inv, ID);
			}

		}
		catch (SQLException e) {
            System.out.println("Error loading Topping");
            while (e != null) {
                System.out.println("Message     : " + e.getMessage());
                e = e.getNextException();
            }

            //don't leave your connection open!
            conn.close();
            return t;
        }

        return t;

    }

    private static Discount getDiscount()  throws SQLException, IOException
    {

        //add code to get a discount

        Discount D = new Discount("temp", 0.0, 0.0, -1);
        String query = "SELECT discountName, percentOff, dollaroff FROM discounts, percentageOff, dollarOff WHERE discountID = " + ID + ";";

        Statement statement = conn.createStatement();
        try {
            ResultSet rset = statement.executeQuery(query);

            while (rset.next()) {

                String dname = rset.getString(1);
                int pOff = rset.getInt(2);
                double cashOff = rset.getDouble(3);

                D = new Discount(dname, pOff, cashOff, ID);
            }
        }
        catch (SQLException e) {
            System.out.println("Error loading Discount");
            while (e != null) {
                System.out.println("Message     : " + e.getMessage());
                e = e.getNextException();
            }

            conn.close();
            return D;
        }

        return D;

    }

    private static Pizza getPizza()  throws SQLException, IOException
    {

        //add code to get Pizza Remember, a Pizza has toppings and discounts on it
        //ID, Size, Crust, Base Price
        Pizza P = new Pizza(-1, size_s, crust_orig, 0.0);
        String query = "SELECT size, crust_type, price FROM pizza WHERE pizzaID = " + ID + ";";

        Statement statement = conn.createStatement();
        try {
            ResultSet rset = statement.executeQuery(query);

            while (rset.next()) {

                String size = rset.getString(1);
                String crust = rset.getString(2);
                double price = rset.getDouble(3);

                P = new Pizza(ID, size, crust, price);
            }
        }
        catch (SQLException e) {
            System.out.println("Error loading Pizza");
            while (e != null) {
                System.out.println("Message     : " + e.getMessage());
                e = e.getNextException();
            }

            conn.close();
            return P;
        }

        return P;

    }

    private static ICustomer getCustomer()  throws SQLException, IOException
    {

        //add code to get customer

        ICustomer C = new ICustomer();



        return C;


    }

    private static Order getOrder()  throws SQLException, IOException
    {

        //add code to get an order. Remember, an order has pizzas, a customer, and discounts on it
        //ID, Customer, Type
        Order O = new Order(-1, -1, pickup);
        String query = "SELECT customerID, "

        return O;

    }

}