package database.crud.reservation;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


import database.Crud;
import database.dataset.Reservation;
import database.dataset.Reservation_individu;

/**
 * 
 * @author Alfredo Toyo
 *
 */
public class ReservationIndividuCrud implements Crud<Reservation_individu> {
	private Connection connection;
	private Statement statement;
	private ReservationCrud reservationCrud;
	
	/**
	 * Constructeur
	 * @param connection
	 * @throws SQLException 
	 */
	public ReservationIndividuCrud(Connection connection) throws SQLException {
		this.connection = connection;
		this.statement = this.connection.createStatement();
		reservationCrud = new ReservationCrud(connection);
	}

	@Override
	public int create(Reservation_individu dataset) throws SQLException {
		String query = "INSERT INTO reservation_individu (reservation_individu_p_fk_id,client_fk_id ) "
					 + "VALUES('" + dataset.getReservation_p_id() + "'," +
				              "'" + dataset.getClient_fk_id() + "')";
	
		reservationCrud.create(dataset);
		try{
			statement.execute(query);
		}catch(SQLException e){
			reservationCrud.destroy(dataset);
			throw e;
		}
		
		return dataset.getReservation_p_id();
	}

	/**
	 * returne null si aucun resultat: levée d'exception pour resulset vide bypassé 
	 */
	@Override
	public Reservation_individu readById(int id) throws SQLException {
		String query = "SELECT * "
	            	 + "FROM reservation, reservation_individu "
	            	 + "WHERE reservation_individu_p_fk_id='"+id+"'";
		
		ResultSet rs = statement.executeQuery(query);
		rs.next();
		if(rs.getRow() != 0){
		//Construire le dataset � partir du resultset
		return new Reservation_individu(rs.getInt("reservation_p_id"),
						   				rs.getInt("chambre_fk_id"),
						   				rs.getDate("reservation_debut"),
						   				rs.getDate("reservation_fin"),
						   				rs.getString("reservation_commentaire"),
						   				rs.getInt("status_fk_id"),
						   				rs.getInt("client_fk_id"));
		}
		else{
			return null;
		}
	}

	public List<Reservation> readByclient_fk(int fk) throws SQLException {
		String query = "SELECT * FROM reservation_individu, reservation WHERE client_fk_id='"+fk+"'";
		ResultSet rs = statement.executeQuery(query);
		List<Reservation> lst = new ArrayList<>();
		
		while(rs.next()){	
			//Construire le dataset � partir du resultset
			lst.add(new Reservation(rs.getInt("reservation_p_id"),
							       rs.getInt("chambre_fk_id"),
							       rs.getDate("reservation_debut"),
							       rs.getDate("reservation_fin"),
							       rs.getString("reservation_commentaire"),
							       rs.getInt("status_fk_id")));
		}
		
		return lst;
	}
	
	@Override
	public List<Reservation_individu> readAll() throws SQLException {
		List<Reservation_individu> lstReservation_individu = new ArrayList<Reservation_individu>();
		
		String query = "SELECT * "
    				 + "FROM reservation, reservation_individu "
    				 + "WHERE reservation_individu_p_fk_id=reservation_p_id "
    				 + "LIMIT 0,100";

		ResultSet rs = statement.executeQuery(query);
		
		while(rs.next()){
			Reservation_individu tmp = new Reservation_individu(rs.getInt("reservation_p_id"),
	   															rs.getInt("chambre_fk_id"),
	   															rs.getDate("reservation_debut"),
	   															rs.getDate("reservation_fin"),
	   															rs.getString("reservation_commentaire"),
	   															rs.getInt("status_fk_id"),
	   															rs.getInt("client_fk_id"));
			lstReservation_individu.add(tmp);
		}
		
		return lstReservation_individu;
	}

	@Override
	public boolean update(Reservation_individu dataset) throws SQLException {
		String query = "UPDATE reservation_individu "
				 	 + "SET reservation_individu_p_fk_id='"+dataset.getReservation_p_id()+"', " 
				 	 +     "client_fk_id='"+dataset.getClient_fk_id()+"'";

		reservationCrud.update(dataset);
		statement.execute(query);
		
		return true;
	}

	@Override
	public boolean destroy(Reservation_individu dataset) throws SQLException {
		String query = "DELETE FROM reservation_individu "
				 	 + "WHERE reservation_individu_p_fk_id='"+dataset.getReservation_p_id()+"'";

		statement.execute(query);
		reservationCrud.destroy(dataset);
		
		return true;
	}

	@Override
	public int getNewSequenceId() throws SQLException {
		return reservationCrud.getNewSequenceId();
	}
}