package Database;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.Map;

public class Database {
private Connection conn;

    public Database(){
    conn = null;
    }

    public boolean openConnection(String userName,String password){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/krusty","root","lolilol");
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    public void closeConnection() {
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
        }
        conn = null;
    }

    public boolean isConnected() {
        return conn != null;
    }

    public int newPallet(String cookieName,int OrderNbr){
        int ID = -1;
        try{
            String sql = "INSERT INTO pallets (CookieName,OrderNbr,Location,Produced,Blocked) values (?,?,'Freezer',CURDATE(),0) ";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, cookieName);
            ps.setInt(2, OrderNbr);
            ps.executeUpdate();
            sql = "SELECT last_insert_id() FROM pallets";
            ps = conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
            ID = rs.getInt(1);
            }
            rs.close();
            ps.close();
        } catch (SQLException e){
            e.printStackTrace();
        }
        return ID;
    }

    public ArrayList<Integer> getorderNbrs(){
        ArrayList<Integer> nbrs = new ArrayList<Integer>();
        try{
            String sql = "SELECT OrderNbr FROM orders";
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                nbrs.add(rs.getInt(1));
            }
            rs.close();
            ps.close();
        } catch (SQLException e){
            e.printStackTrace();
        }
        return nbrs;
    }

    public ArrayList<String> getCookies(){
        ArrayList<String> cookies = new ArrayList<String>();
        try{
        String sql = "SELECT DISTINCT CookieName FROM recipes";
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        while(rs.next()){
            cookies.add(rs.getString(1).toLowerCase());
        }
        ps.close();
        rs.close();
        } catch (SQLException e){
            e.printStackTrace();
        }
        return cookies;
    }

    public ArrayList<FullInfoPallet> getPalletsWithCookie(String cookieName){
        ArrayList<FullInfoPallet> pallets = new ArrayList<FullInfoPallet>();
        try{
            String sql = "SELECT PalletCode,CookieName,Produced,Location,DeliverDate,Customer,CURDATE() FROM Pallets NATURAL JOIN orders WHERE CookieName = ?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, cookieName);
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                pallets.add(new FullInfoPallet(rs.getString(1),rs.getString(2),
                        rs.getString(3),rs.getString(4),
                        rs.getString(5),rs.getString(6)));
            }
            rs.close();
            ps.close();
        } catch (SQLException e){
            e.printStackTrace();
        }
        return pallets;
    }

    public ArrayList<FullInfoPallet> getDeliveredPalletsWithCustomer(String customer){
        ArrayList<FullInfoPallet> pallets = new ArrayList<FullInfoPallet>();
    try{
        String sql = "SELECT PalletCode,CookieName,Produced,Location,DeliverDate,Customer FROM Pallets NATURAL JOIN orders WHERE Customer = ? AND Location = 'Delivered'";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, customer);

        ResultSet rs = ps.executeQuery();
        while(rs.next()){
            pallets.add(new FullInfoPallet(rs.getString(1),rs.getString(2),
                    rs.getString(3),rs.getString(4),
                    rs.getString(5),rs.getString(6)));
        }
        rs.close();
        ps.close();
    } catch (SQLException e){
        e.printStackTrace();
    }
    return pallets;
    }

    public ArrayList<FullInfoPallet> getPallet(int ID){
        ArrayList<FullInfoPallet> pallet = new ArrayList<FullInfoPallet>();
        try{
        String sql = "SELECT PalletCode,CookieName,Produced,Location,DeliverDate,Customer,CURDATE() FROM Pallets NATURAL JOIN orders WHERE PalletCode = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, ID);
        ResultSet rs = ps.executeQuery();
        while(rs.next()){
            pallet.add(new FullInfoPallet(rs.getString(1),rs.getString(2),
                    rs.getString(3),rs.getString(4),
                    rs.getString(5),rs.getString(6)));
        }
        rs.close();
        ps.close();
        } catch (SQLException e){
            e.printStackTrace();
        }
        return pallet;
    }

    public ArrayList<FullInfoPallet> getPalletsIntervall(java.sql.Date from,java.sql.Date to){
        ArrayList<FullInfoPallet> pallets = new ArrayList<FullInfoPallet>();
        try{
            String sql = "SELECT PalletCode,CookieName,Produced,Location,DeliverDate,Customer FROM Pallets NATURAL JOIN orders WHERE Produced BETWEEN ? AND ?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setDate(1, from);
            ps.setDate(2, to);
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                pallets.add(new FullInfoPallet(rs.getString(1),rs.getString(2),
                    rs.getString(3),rs.getString(4),
                    rs.getString(5),rs.getString(6)));
            }
            rs.close();
            ps.close();
        } catch (SQLException e){
            e.printStackTrace();
        }
        return pallets;
    }

    public Map<String,String> getIngredients(String cookieName){
        try{
            String sql = "SELECT ingredience,amount FROM recipes WHERE "
                    + "CookieName = ?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, cookieName);
            ResultSet rs = ps.executeQuery();
            Map<String,String> ingredients = new HashMap<String,String>();
            while(rs.next()){
                ingredients.put(rs.getString(1), rs.getString(2));
            }
            rs.close();
            ps.close();
            return ingredients;
        } catch(SQLException e){
            e.printStackTrace();
        }
        return null;
    }

    public ArrayList<String> getCustomers(){
        ArrayList<String> customers = new ArrayList<String>();
        try{
        String sql = "SELECT Customer FROM customer";
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        while(rs.next()){
            customers.add(rs.getString(1));
        }
        rs.close();
        ps.close();
        } catch (SQLException e){
            e.printStackTrace();
        }
        return customers;
    }

    public void updateIngredients(String ingr, Double amount){
        try{
        String sql = "UPDATE ingredients SET InStore = InStore - ?"
                + "WHERE Ingredience = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setDouble(1, amount);
        ps.setString(2, ingr);
        ps.executeUpdate();
        ps.close();
        } catch (SQLException e){
            e.printStackTrace();
        }
    }

    public ArrayList<Pallet> getBlockedPallets(){
        ArrayList<Pallet> blocked = new ArrayList<Pallet>();
        try{
        String sql = "SELECT PalletCode,CookieName,Produced FROM Pallets WHERE Blocked = 1";
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();

        StringBuilder sb;
        DateFormat df = df = new SimpleDateFormat("yyyy-MM-dd");
        Date date;
        String sDate;
        while(rs.next()){
            date = rs.getDate(3);
            sDate = df.format(date);
            blocked.add(new Pallet(rs.getString(1),rs.getString(2),sDate));
        }
        ps.close();
        rs.close();
        } catch (SQLException e){
            e.printStackTrace();
        }
        return blocked;
    }

    public void blockPallets(String cookieName,java.sql.Date from,java.sql.Date to){
        try{
        String sql = "UPDATE Pallets SET Blocked = 1 WHERE CookieName = ? AND Produced BETWEEN ? AND ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, cookieName);
        ps.setDate(2, from);
        ps.setDate(3, to);
        ps.executeUpdate();
        ps.close();
        } catch (SQLException e){
            e.printStackTrace();
        }
    }

}