package nieminen_aj.database;

import java.sql.*;

import nieminen_aj.main.PizzApp;

public class WriteDB {
    /**
     * contains name of this class
     */
    private String id = "WriteDB.";
    /**
     * contains JDBC driver name for MySQLcreaty
     */
    private static String driverName = "com.mysql.jdbc.Driver";
    /**
     * contains database username
     */
    private static String login = "";
    /**
     * contains database password
     */
    private static String pass = "";
    /**
     * contains database name
     */
    private static String db = "";
    /**
     * contains address of database
     */
    private static String host = "mydb..fi";
    /**
     * contains url for jdbc to use
     */
    private static String url = "jdbc:mysql://"
        + host
        + "/"
        + db
        + "?user="
        + login
        + "&password="
        + pass;

    /**
     * Puts SQL quotes around string.
     *
     * @param value String that needs to be quoted.
     * @return returns Quoted String
     */
    public static String sqlQuote(String value){
        String quoteValue = "'" + value + "'";
        return quoteValue;
    }

    public void createQueryCustomer(String [] array) {
        String sql = "INSERT INTO customers ("
            + "number"
            + ", name"
            + ", address"
            + ", date"
            + ", phone"
            + ") VALUES ("
            + array[0] + ", "
            + sqlQuote(array[1]) + ", "
            + sqlQuote(array[2]) + ", "
            + sqlQuote(array[3]) + ", "
            + sqlQuote(array[4]) + ")";
        this.writeQuery(sql);
    }

    public void createQueryOrderRow(String orderId, String [][] array) {
        int rows = array.length;
        String sql = "INSERT INTO ordered_pizzas ("
            //+ "id"
            + "pizza"
            + ", ingredients"
            + ", size"
            + ", prize"
            + ", order_id"
            + ") VALUES ";

        if (rows == 1) {
            sql += "("
                //+ array[0][0] + ", "
                + array[0][1] + ", "
                + sqlQuote(array[0][2]) + ", "
                + sqlQuote(array[0][3]) + ", "
                + array[0][4] + ", "
                + orderId + ")";
        } else if(rows > 1) {
            for (int i = 0; i < rows; i++) {
                sql += "("
                    //+ array[i][0] + ", "
                    + array[i][1] + ", "
                    + sqlQuote(array[i][2]) + ", "
                    + sqlQuote(array[i][3]) + ", "
                    + array[i][4] + ", "
                    + orderId + ")";
                // line is not last, we add comma
                if (i + 1 != rows) {
                    sql += ", ";
                }
            }
        }
        this.writeQuery(sql);
    }

    public void createQueryOrder(String orderId
            , int customerNumber
            , int deliveryType
            , double prize
            , String date
            , String time) {
        String sql = "INSERT INTO orders ("
            + "id"
            + ", ordered_by"
            + ", delivery_type"
            + ", prize"
            + ", date"
            + ", time"
            + ") VALUES ("
            + orderId + ", "
            + customerNumber + ", "
            + deliveryType + ", "
            + prize + ", "
            + sqlQuote(date) + ", "
            + sqlQuote(time) + ")";
        this.writeQuery(sql);
    }

    /**
     * wroteQuery method of WriteDB writes given information to the
     * database
     * @param table used table in String
     * @param array written information in 2d array
     * first row of array consists name of columns
     * second row of array consists values
     */
    public void writeQuery(String sql) {
        // name of the method
        String id = "writeQuery()";
        // debugging url
        PizzApp.debug(this.id + id + url);

        try {

            PizzApp.debug(this.id + id + "Loading driver: " + driverName);
            Class.forName(driverName);
            PizzApp.debug(this.id + id + "Driver loaded ok");

            PizzApp.debug(this.id + id + "Connectign database: " + url);
            Connection con = DriverManager.getConnection(url);
            PizzApp.debug(this.id + id + "Connected ok");

            PizzApp.debug(this.id + id + "Making statement");
            Statement stmt = con.createStatement();
            PizzApp.debug(this.id + id + "Statement ok");

            PizzApp.debug(this.id + id + "Sending SQL: " + sql);

            int count = 0;

            try {
                count = stmt.executeUpdate(sql);
            }catch(Exception error){
                String str = error.getMessage();

                if (str.contains("Duplicate")){
                    System.err.println("Already inserted");
                } else {

                    System.err.println(error.getMessage());
                    error.printStackTrace();
                    System.exit(1);

                }
            }

            PizzApp.debug(this.id + id + "Insertet rows: " + count);

        }catch(Exception error){

            System.err.println(error.getMessage());
            error.printStackTrace();
            System.exit(1);

        }
    }
}