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_groupe;

/*****************************************************************************
 * Classe: ReservationGroupeCrud
 * 
 * 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_groupe
 * -Connection
 * -Statement
 * 
 * Projet Orford
 *****************************************************************************
 * Modification: 
 * Nom: 
 * Date: 
 ****************************************************************************/

/**
 * @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 ReservationGroupeCrud implements Crud<Reservation_groupe> {
	private Connection connection;
	private Statement statement;
	private ReservationCrud reservationCrud;
	
	/**
	 * Constructeur
	 * @param connection
	 * @throws SQLException 
	 */
	public ReservationGroupeCrud(Connection connection) throws SQLException {
		this.connection = connection;
		this.statement = this.connection.createStatement();
		this.reservationCrud = new ReservationCrud(connection);
	}

	@Override
	public int create(Reservation_groupe dataset) throws SQLException {
		String query = "INSERT INTO reservation_groupe (reservation_groupe_p_fk_id,groupe_fk_id ) "
				 + "VALUES('" + dataset.getReservation_p_id() + "'," +
			              "'" + dataset.getGroupe_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_groupe readById(int id) throws SQLException {
		String query = "SELECT r.reservation_p_id, r.chambre_fk_id, r.reservation_debut, "
				  	 + 		  "r.reservation_fin, r.reservation_commentaire, r.status_fk_id, "
				  	 + 		  "rg.groupe_fk_id "
				     + "FROM reservation_groupe AS rg, reservation AS r "
				     + "WHERE rg.reservation_groupe_p_fk_id='"+id+"'";
		
		ResultSet rs = statement.executeQuery(query);
		rs.next();
		if(rs.getRow() != 0){
		//Construire le dataset � partir du resultset
		return 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{
			return null;
		}
	}

	@Override
	public List<Reservation_groupe> readAll() throws SQLException {
		List<Reservation_groupe> lstReservation_groupe = new ArrayList<Reservation_groupe>();
		
		String query = "SELECT * "
	  	             + "FROM reservation_groupe, reservation "
	  	             + "WHERE reservation_groupe_p_fk_id=reservation_p_id";

		ResultSet rs = statement.executeQuery(query);
		
		while(rs.next())
		{
			Reservation_groupe 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"));
			lstReservation_groupe.add(tmp);
		}
		
		return lstReservation_groupe;
	}
	
	public List<Reservation> readBygroupe_fk(int fk) throws SQLException {
		String query = "SELECT * FROM reservation_groupe, reservation WHERE groupe_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 boolean update(Reservation_groupe dataset) throws SQLException {
		String query = "UPDATE reservation_groupe "
				 + "SET reservation_groupe_p_fk_id='"+dataset.getReservation_p_id()+"', " 
			     +     "groupe_fk_id='"+dataset.getGroupe_fk_id()+"'";

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

	@Override
	public boolean destroy(Reservation_groupe dataset) throws SQLException {
		String query = "DELETE FROM reservation_groupe "
				 + "WHERE reservation_groupe_p_fk_id='"+dataset.getReservation_p_id()+"'";

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

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