-- PostgreSQL n'accepte pas les contraintes d'integrite avec un SELECT
-- Il faut definir une fonction
CREATE FUNCTION HotelBis.DatesDebutFinCorrectes(integer, date, date) 
  RETURNS boolean AS $$ 
SELECT NOT EXISTS (
  SELECT * 
  FROM HotelBis.Reservation
  WHERE $1 = idChambre 
    AND $2 <= date_fin
    AND $3 >= date_debut)
$$ LANGUAGE SQL;

CREATE FUNCTION HotelBis.DateRecetteCorrecteSimple(integer, timestamp) 
  RETURNS boolean AS $$ 
SELECT EXISTS (
  SELECT * 
  FROM HotelBis.Reservation
  WHERE $1 = idReservation 
    AND $2::date <= date_fin)
$$ LANGUAGE SQL;

CREATE FUNCTION HotelBis.DateRecetteCorrecte(integer, timestamp) 
  RETURNS boolean AS $$ 
SELECT EXISTS ( -- Pour les commandes au bar
  SELECT * 
  FROM HotelBis.Reservation
  WHERE $1 = idReservation 
    AND $2::date >= date_debut
    AND $2::date <= date_fin)
  OR NOT EXISTS ( -- Pour les reservations de chambre
  SELECT * 
  FROM HotelBis.Recette
  WHERE $1 = idReservation 
  )
$$ LANGUAGE SQL;
