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

/**
 * Things to note:
 * Easy convert to sql date => new java.sql.Date(datum.getTime())
 * Timezone exeption? Change url to => jdbc:mysql://127.0.0.1/BoekingDB?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
 * Best practice: respect MVC layers and use this class in your service class
 */
public class BeerDAO {
    private String url;
    private String user;
    private String password;

    public BeerDAO() {
        //Required for servlets
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

    }

    public BeerDAO(String url, String user, String password) {
        //Required for servlets
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        this.url = url;
        this.user = user;
        this.password = password;


    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public String getUser() {
        return user;
    }

    public void setUser(String user) {
        this.user = user;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public Beer getBeerById(int id) throws BeerException {
        try (Connection con = getConnection();
             PreparedStatement stmt = con
                     .prepareStatement("SELECT * FROM Beers WHERE Id=?")) {
            stmt.setInt(1, id);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    Beer beer = new Beer();
                    beer.setId(id);
                    beer.setName(rs.getString("Name")); //Can also this this with comumn number => rs.getInt(1)
                    beer.setPrice(rs.getFloat("Price"));
                    beer.setAlcohol(rs.getFloat("Alcohol"));
                    beer.setStock(rs.getInt("Stock"));
                    return beer;
                } else {
                    return null;
                }
            }
        } catch (SQLException e) {
            throw new BeerException(e);
        }

    }

    public List<Beer> getAllBeers() {
        List<Beer> beerList = new ArrayList<>();
        try (Connection con = getConnection();
             Statement stmt = con.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT * FROM booking")) {
            while (rs.next()) {
                Beer beer = new Beer();
                beer.setId(rs.getInt("Id")); //Can also this this with comumn number => rs.getInt(1)
                beer.setName(rs.getString("Name"));
                beer.setPrice(rs.getFloat("Price"));
                beer.setAlcohol(rs.getFloat("Alcohol"));
                beer.setStock(rs.getInt("Stock"));
                beerList.add(beer);
            }
            return beerList;


        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public boolean isBeerInTable(int id) {
        try (Connection con = getConnection();
             PreparedStatement stmt = con
                     .prepareStatement("SELECT * FROM Beers WHERE id =?")) {
            stmt.setInt(1, id);
            try (ResultSet rs = stmt.executeQuery()) {
                return !rs.next();
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return false;

        }
    }

    public List<Beer> searchBeer(String value) {
        List<Beer> beerList = null;
        value.replace("!", "!!")
                .replace("%", "!%")
                .replace("_", "!_")
                .replace("[", "![");

        try (Connection con = getConnection();
             PreparedStatement stmt = con
                     .prepareStatement("SELECT * FROM Beers WHERE concat_ws(', ',Name, Price, Alcohol, Stock) LIKE ?")) {
            stmt.setString(1, "%" + value + "%");
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    Beer beer = new Beer();
                    beer.setId(rs.getInt("Id")); //Can also this this with comumn number => rs.getInt(1)
                    beer.setName(rs.getString("Name"));
                    beer.setPrice(rs.getFloat("Price"));
                    beer.setAlcohol(rs.getFloat("Alcohol"));
                    beer.setStock(rs.getInt("Stock"));
                    beerList.add(beer);
                }
                return beerList;
            } catch (SQLException e) {
                e.printStackTrace();
                return null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public void updateBeer(Beer beer) throws BeerException {
        try (Connection con = getConnection();
             PreparedStatement stmt = con
                     .prepareStatement("UPDATE Beers SET Name=?, Price=?, Alcohol=?, Stock=? WHERE Id=?")) {
            stmt.setString(1, beer.getName());
            stmt.setFloat(2, beer.getPrice());
            stmt.setFloat(3, beer.getAlcohol());
            stmt.setInt(4, beer.getStock());
            stmt.setInt(5, beer.getStock());
            stmt.executeUpdate();
        } catch (SQLException e) {
            throw new BeerException(e);
        }
    }

    public void insertBeer(Beer beer) {
        try (Connection con = getConnection();
             PreparedStatement stmt = con
                     .prepareStatement("INSERT INTO Beers(DAY, NAME, CITY, STREET, HOUR)" +
                             " VALUES (?,?,?,?,?)")) {
            stmt.setString(1, beer.getName());
            stmt.setFloat(2, beer.getPrice());
            stmt.setFloat(3, beer.getAlcohol());
            stmt.setInt(4, beer.getStock());
            stmt.setInt(5, beer.getStock());
            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    public boolean deleteBeerById(int id) {
        try (Connection con = getConnection();
             PreparedStatement stmt = con
                     .prepareStatement("DELETE FROM Beers " +
                             "WHERE id = ?")) {
            stmt.setInt(1, id);
            stmt.execute();
            con.close();
            return true;

        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    /**                             Dirty Read      Non-Repeatable read     Phantom read
     * TRANSACTION_NONE
     * TRANSACTION_READ_UNCOMITTED      X                   X                   X
     * TRANSACTION_READ_COMITTED        0                   X                   X
     * TRANSACTION_REPEATABLE_READ      0                   0                   X
     * TRANSACTION_SERIALIZABLE         0                   0                   0
     */
    public void saveBeer(Beer beer1, Beer beer2) {
        final String UPDATE1 = "UPDATE Beers SET Name=?, Price=?, Alcohol=?, Stock=? WHERE Id=?";
        final String UPDATE2 = "UPDATE Beers SET Name=?, Price=?, Alcohol=?, Stock=? WHERE Id=?";
        try (Connection con = getConnection()) {
            con.setAutoCommit(false);
            con.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);

            try (PreparedStatement stmt1 = con.prepareStatement(UPDATE1);
                 PreparedStatement stmt2 = con.prepareStatement(UPDATE2);) {

                stmt1.setString(1, beer1.getName());
                stmt1.setFloat(2, beer1.getPrice());
                stmt1.setFloat(3, beer1.getAlcohol());
                stmt1.setInt(4, beer1.getStock());
                stmt1.setInt(5, beer1.getStock());


                stmt2.setString(1, beer2.getName());
                stmt2.setFloat(2, beer2.getPrice());
                stmt2.setFloat(3, beer2.getAlcohol());
                stmt2.setInt(4, beer2.getStock());
                stmt2.setInt(5, beer2.getStock());
                con.commit();
            } catch (SQLException e) {
                e.printStackTrace();
                con.rollback();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


        //Private helper methods
    private Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, user, password);
    }
}