package database.crud;

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.crud.reservation.ReservationCrud;
import database.crud.reservation.ReservationGroupeCrud;
import database.crud.reservation.ReservationIndividuCrud;
import database.dataset.Reservation;
import database.dataset.Reservation_groupe;
import database.dataset.Reservation_individu;

/*****************************************************************************
 * 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 but d'ex�cuter les instructions SQL en respectant les
 * op�rations de persistance des donn�es.
 */
public class ReservationCrudGeneral implements Crud<Reservation> {
	private Connection connection;
	private Statement statement;
	private ReservationCrud resevationGeneralCrud;
	private ReservationGroupeCrud reservationGroupeCrud;
	private ReservationIndividuCrud reservationIndividuCrud;
	/**
	 * Constructeur
	 * @param connection
	 * @throws SQLException 
	 */
	public ReservationCrudGeneral(Connection connection) throws SQLException {
		this.connection = connection;
		this.statement = this.connection.createStatement();
		this.reservationGroupeCrud=new ReservationGroupeCrud(connection);
		this.reservationIndividuCrud=new ReservationIndividuCrud(connection);
		this.resevationGeneralCrud=new ReservationCrud(connection);
	}

	@Override
	public int create(Reservation dataset) throws SQLException {
		if (dataset instanceof Reservation_individu)
			return reservationIndividuCrud.create((Reservation_individu)dataset);
		else if(dataset instanceof Reservation_groupe)
			return reservationGroupeCrud.create((Reservation_groupe)dataset);
		return resevationGeneralCrud.create(dataset);
	}

	@Override
	public Reservation readById(int id) throws SQLException {
		//add more meat here
		return resevationGeneralCrud.readById(id);
	}

	@Override
	public List<Reservation> readAll() throws SQLException {
		//add more meat here
		return resevationGeneralCrud.readAll();
	}

	@Override
	public boolean update(Reservation dataset) throws SQLException {
		if (dataset instanceof Reservation_individu)
			return reservationIndividuCrud.update((Reservation_individu)dataset);
		else if(dataset instanceof Reservation_groupe)
			return reservationGroupeCrud.update((Reservation_groupe)dataset);
		return resevationGeneralCrud.update(dataset);
	}

	@Override
	public boolean destroy(Reservation dataset) throws SQLException {
		if (dataset instanceof Reservation_individu)
			return reservationIndividuCrud.destroy((Reservation_individu)dataset);
		else if(dataset instanceof Reservation_groupe)
			return reservationGroupeCrud.destroy((Reservation_groupe)dataset);
		return resevationGeneralCrud.destroy(dataset);
	}

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

	public List<Reservation> readAllLike(int numero_chambre, Date debut, Date fin) throws SQLException {
		List<Reservation> lst = new ArrayList<Reservation>();
		
		String query = "SELECT * FROM reservation " +
					   "INNER JOIN chambre ON chambre_p_id=chambre_fk_id " +
					   "LEFT JOIN reservation_individu ON reservation_individu_p_fk_id=reservation_p_id "+
					   "LEFT JOIN reservation_groupe ON reservation_groupe_p_fk_id=reservation_p_id "+
		               "WHERE chambre_numero LIKE '"+numero_chambre+"' " +
		               "AND ((reservation_debut BETWEEN '"+debut+"' AND '"+fin+"') OR (reservation_fin BETWEEN '"+debut+"' AND '"+fin+"')) "+
		               "ORDER BY reservation_debut LIMIT 0,100";
					   
		ResultSet rs = statement.executeQuery(query);
		
		while(rs.next()){
			Reservation tmp = null;
			if (rs.getObject("client_fk_id")!=null){
				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"));
			}
			else if(rs.getObject("groupe_fk_id")!=null){
				tmp = new Reservation_groupe(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("groupe_fk_id"));
			}
			else{
				//Construire le dataset � partir du resultset
				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"));
			}
			
			lst.add(tmp);
		}
		
		return lst;
	}
	
	/**
	 * Recherche des reservation de groupe en fonction de la date et nom du groupe
	 * @param debut
	 * @param fin
	 * @param groupe
	 * @return List<Reservation>
	 * @throws SQLException
	 */
	public List<Reservation> readAllLikeRechercheGroupe(Date debut, Date fin, String groupe) throws SQLException {
		List<Reservation> lst = new ArrayList<Reservation>();
		
		String query = "SELECT * FROM reservation_groupe "
					 + "LEFT JOIN reservation ON reservation_p_id=reservation_groupe_p_fk_id "
					 + "LEFT JOIN groupe ON groupe_p_id = groupe_fk_id "
					 + "WHERE ((reservation_debut BETWEEN '"+debut+"' AND '"+fin+"') OR (reservation_fin BETWEEN '"+debut+"' AND '"+fin+"')) "
					 + "AND groupe_nom LIKE '"+groupe+"' "
					 + "LIMIT 0, 100";

		ResultSet rs = statement.executeQuery(query);
		
		while(rs.next()){
			Reservation tmp = null;
			if(rs.getObject("groupe_fk_id")!=null){
				tmp = new Reservation_groupe(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("groupe_fk_id"));
			}

			lst.add(tmp);
		}
		
		return lst;
	}

	
	/**
	 * @param debut
	 * @param fin
	 * @return list de dataset entre deux dates
	 * @throws SQLException
	 */
	public List<Reservation> readAllBetween(String debut, String fin) throws SQLException {
		List<Reservation> lst = new ArrayList<Reservation>();
		
		String query = "SELECT * FROM reservation "
					 + "WHERE (reservation_debut BETWEEN '"+debut+"' AND '"+fin+"') "
					 +    "OR (reservation_fin BETWEEN '"+debut+"' AND '"+fin+"')";
					   
		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"));
			lst.add(tmp);
		}
		
		return lst;
	}
	
	/**
	 * @param debut
	 * @param fin
	 * @return list de dataset entre deux dates
	 * @throws SQLException
	 */
	public List<Reservation> readAllEnding(String fin) throws SQLException {
		List<Reservation> lst = new ArrayList<Reservation>();
		
		String query = "SELECT * FROM reservation "
				     + "INNER JOIN status_chambre ON status_chambre_p_id = status_fk_id "
					 + "WHERE reservation_fin = '"+fin+"' ORDER BY chambre_fk_id";
					   
		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"));
			lst.add(tmp);
		}
		
		return lst;
	}
	
	/**
	 * @param debut
	 * @param fin
	 * @return list de dataset entre deux dates
	 * @throws SQLException
	 */
	public List<Reservation> readAllBeginning(String debut) throws SQLException {
		List<Reservation> lst = new ArrayList<Reservation>();
		
		String query = "SELECT * FROM reservation "
				     + "INNER JOIN status_chambre ON status_chambre_p_id = status_fk_id "
					 + "WHERE reservation_debut = '"+debut+"' ORDER BY chambre_fk_id";
					   
		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"));
			lst.add(tmp);
		}
		
		return lst;
	}
}