package be.pxl.ticket.dao;
import be.pxl.ticket.bean.TicketBean;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;



public class TicketDao {

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

	public TicketDao()
	{

	}

	public TicketDao(String url, String user, String password) {
		super();
		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 TicketBean getTicketById(int id) throws SQLException
	{
		try(Connection con = getConnection();
				PreparedStatement stmt = con.prepareStatement("SELECT * FROM tickettable WHERE id=?"))
		{
			stmt.setInt(1, id);
			try(ResultSet rs = stmt.executeQuery())
			{
				if (rs.next())
				{
					TicketBean ticketBean = new TicketBean();
					ticketBean.setId(id);
					ticketBean.setTitle(rs.getString("title"));
					ticketBean.setDescription(rs.getString("description"));
					ticketBean.setCategory(rs.getString("catagorie"));
					ticketBean.setCreationDate(rs.getDate("creationDate"));
					ticketBean.setStatus(rs.getString("status"));
					ticketBean.setSubmittedBy(rs.getString("submittedBy"));
					ticketBean.setAssignedTo(rs.getString("assignedTo"));
					ticketBean.setLocation(rs.getString("location"));

					return ticketBean;

				}else
				{
					return null;
				}
			}
		}catch(SQLException e)
		{
			throw new SQLException(e);
		}

	}

	public void updateTicket(TicketBean ticketBean) throws SQLException
	{
		try(Connection con = getConnection();
				PreparedStatement stmt = con.prepareStatement("UPDATE tickettable SET title=?, description=?, "
						+ "catagorie=?, creationDate=?, status=?, submittedBy=?, assignedTo=?, location=? WHERE id=?"))
		{
			stmt.setString(1, ticketBean.getTitle());
			stmt.setString(1, ticketBean.getDescription());
			stmt.setString(1, ticketBean.getCategory());
			stmt.setDate(1, (Date) ticketBean.getCreationDate());
			stmt.setString(1, ticketBean.getStatus());
			stmt.setString(1, ticketBean.getSubmittedBy());
			stmt.setString(1, ticketBean.getAssignedTo());
			stmt.setString(1, ticketBean.getLocation());
			stmt.executeUpdate();
		}
		catch (SQLException e) {
			throw new SQLException(e);
		}
	}
	//html helper methode

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

	}

}