package database.crud.reservation;

import java.sql.Connection;
import java.sql.Date;
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;

/*****************************************************************************
 * Classe: ReservationCrud
 * 
 * Objet ayant pour bout d'ex�cuter les instructions SQL en respectant les
 * op�rations de persistance des donn�es.
 * 
 * Liste des dependances
 * -Dataset reservation
 * -Connection
 * -Statement
 * 
 * Projet Orford
 ****************************************************************************
 * Modification: Ajout dela m�thode getNewSequenceId(), ajout de control du
 * commit pour instructions multiples pour la m�thode create().
 * Nom: Alfredo Toyo
 * Date: 2013-09-06
 ****************************************************************************/

/**
 * @author Alfredo Toyo
 * Objet ayant pour bout d'ex�cuter les instructions SQL en respectant les
 * op�rations de persistance des donn�es.
 */
public class ReservationCrud implements Crud<Reservation> {
	private Connection connection;
	private Statement statement;
	
	/**
	 * Constructeur
	 * @param connection
	 * @throws SQLException 
	 */
	public ReservationCrud(Connection connection) throws SQLException {
		this.connection = connection;
		this.statement = this.connection.createStatement();
	}

	@Override
	public int create(Reservation dataset) throws SQLException {
		//create une nouvelle row dans la BD
		if(dataset==null)
			return 0;
		if(dataset.getReservation_debut()==null)
			dataset.setReservation_debut(new Date(0));
		if(dataset.getReservation_fin()==null)
			dataset.setReservation_fin(dataset.getReservation_debut());
		String query = "INSERT INTO reservation "
					 + "VALUES('" + dataset.getReservation_p_id() + "'," +
				              "'" + dataset.getReservation_debut() + "'," +
				              "'" + dataset.getReservation_fin() + "'," +
				              "'" + dataset.getStatus_fk_id() + "'," +
				              "'" + dataset.getChambre_fk_id() + "'," +
				              "'" + dataset.getReservation_commentaire() + "')";

		statement.execute(query);
		//Retourner le id de se dernier
		return dataset.getReservation_p_id();
	}

	@Override
	public Reservation readById(int id) throws SQLException {
		String query = "SELECT * FROM reservation WHERE reservation_p_id='"+id+"'";
		ResultSet rs = statement.executeQuery(query);
		rs.next();
		
		//Construire le dataset � partir du resultset
		return 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"));
	}

	public List<Reservation> readBychambre_fk(int fk) throws SQLException {
		String query = "SELECT * FROM reservation WHERE chambre_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;
	}
	
	public List<Reservation> readBystatus_fk(int fk) throws SQLException {
		String query = "SELECT * FROM reservation WHERE status_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> readAll() throws SQLException {
		List<Reservation> lstReservation = new ArrayList<Reservation>();
		
		String query = "SELECT * FROM reservation LIMIT 0,100";
		ResultSet rs = statement.executeQuery(query);
		
		while(rs.next()){
		//Construire le dataset � partir du resultset
			Reservation tmp = 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"));
			
			lstReservation.add(tmp);
		}
		
		return lstReservation;
	}

	@Override
	public boolean update(Reservation dataset) throws SQLException {
		String query = "UPDATE reservation "
					 + "SET chambre_fk_id='"+dataset.getChambre_fk_id()+"', " 
				     +     "reservation_debut='"+dataset.getReservation_debut()+"', "
					 +     "reservation_fin='"+dataset.getReservation_fin()+"', "
				     +     "reservation_commentaire='"+dataset.getReservation_commentaire()+"', "
					 +     "status_fk_id='"+dataset.getStatus_fk_id()+"' "
				     + "WHERE reservation_p_id='"+dataset.getReservation_p_id()+"'";

		statement.execute(query);

		return true;
	}
	
	public boolean updateCheckInCheckOut(int reservation_p_id, int status) throws SQLException{
		String query = "UPDATE reservation "
					 + "SET status_fk_id='"+status+"' "
					 + "WHERE reservation_p_id='"+reservation_p_id+"'";

		statement.execute(query);

		return true;
	}

	@Override
	public boolean destroy(Reservation dataset) throws SQLException {
		String query = "DELETE FROM reservation "
					 + "WHERE reservation_p_id='"+dataset.getReservation_p_id()+"'";

		statement.execute(query);
		
		return true;
	}

	@Override
	public int getNewSequenceId() throws SQLException {
		ResultSet rsid = statement.executeQuery("SELECT sget_next_sequence_id('reservation')");

		rsid.next();
		
		return rsid.getInt(1);
	}
}