package be.pxl.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BeerDao {

	private String url;
	private String user;
	private String password;

	public BeerDao() {

	}

	public BeerDao(String url, String user, String password) {
		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"));
					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 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.getId());
			stmt.executeUpdate();
		}
		catch (Exception e) {
			throw new BeerException(e);
		}
	}

	public List<Beer> getBeerByAlcohol(float alcohol) throws BeerException {
		try (Connection con = getConnection();
				PreparedStatement stmt = con.prepareStatement(
						"SELECT * FROM Beers WHERE Alcohol = ?")) {
			stmt.setFloat(1, alcohol);
			try (ResultSet rs = stmt.executeQuery()) {
				if (rs.next()) {
					List beers = new ArrayList<Beer>();
					Beer beer = new Beer();
					beer.setId(rs.getInt("Id"));
					beer.setName(rs.getString("Name"));
					beer.setPrice(rs.getFloat("Price"));
					beer.setAlcohol(alcohol);
					beer.setStock(rs.getInt("Stock"));
					beers.add(beer);
					return beers;
				}
				else {
					return null;
				}
			}

		}
		catch (SQLException e) {
			throw new BeerException(e);
		}
	}

	public List<Beer> getBeerByName(String name) throws BeerException {
		try (Connection con = getConnection();
				PreparedStatement stmt = con.prepareStatement(
						"SELECT * FROM Beers WHERE Name = ?")) {
			stmt.setString(1, name);
			try (ResultSet rs = stmt.executeQuery()) {
				if (rs.next()) {
					List beers = new ArrayList<Beer>();
					Beer beer = new Beer();
					beer.setId(rs.getInt("Id"));
					beer.setName(name);
					beer.setPrice(rs.getFloat("Price"));
					beer.setAlcohol(rs.getFloat("Alcohol"));
					beer.setStock(rs.getInt("Stock"));
					beers.add(beer);
					return beers;
				}
				else {
					return null;
				}
			}

		}
		catch (SQLException e) {
			throw new BeerException(e);
		}
	}

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