-- Pour calculer la disponibilite
-- 1/ les debuts d'occupation de chaque chambre
--    un début virtuel à la fin du monde
-- 2/ les fins d'occupation de chaque chambre
--    une fin virtuelle à la création du monde
-- 3/ les périodes non occupées ce chaque chambre à partir d'aujourd'hui
-- 4/ la meme chose avec de l'information complémentaire

CREATE VIEW Hotel.Disponibilite AS
SELECT Categorie, Etage, LibreDu, Au, Numero, Descriptif
FROM (SELECT Etage, Numero, GREATEST(DateFin, current_date) AS LibreDu, Min(DateDebut) As Au
      FROM   (SELECT Etage, Numero, DateDebut
              FROM   Hotel.Reservations
                UNION
              SELECT Etage, Numero, 'infinity'
              FROM   Hotel.Chambres) AS DebutOccupation 
        NATURAL JOIN 
             (SELECT Etage, Numero, DateFin
              FROM   Hotel.Reservations
                UNION
              SELECT Etage, Numero, '-infinity'
              FROM   Hotel.Chambres) AS FinOccupation
      WHERE  DateDebut > DateFin
      GROUP BY Etage, Numero, DateFin
      HAVING Min(DateDebut) > current_date) AS NonOccupation
  NATURAL JOIN Hotel.Chambres
  NATURAL JOIN Hotel.TypeChambre
WHERE  Categorie <> 'Indisponible';
-- les clients fidèles
-- Ils sont venus plus de 10 fois
-- ou bien ils ont reservés plus de 3 fois une chambre 'Workable'

CREATE VIEW Hotel.Fidelite AS
SELECT Nom, Prenom, Adresse
FROM   Hotel.Clients NATURAL JOIN Hotel.Reservations
GROUP BY NumClient, Nom, Prenom, Adresse
HAVING COUNT(*)>10
UNION
SELECT Nom, Prenom, Adresse
FROM   Hotel.Clients NATURAL JOIN Hotel.Reservations NATURAL JOIN Hotel.Chambres
WHERE  Categorie = 'Workable'
GROUP BY NumClient, Nom, Prenom, Adresse, Categorie
HAVING COUNT(*)>3;
-- les alertes pour des situations exceptionnelles
-- Le numero du client est un multiple de 500
-- ou bien il arrive le jour de l'anniversaire du patron

CREATE VIEW Hotel.Alerte AS
SELECT Nom, Prenom, Adresse
FROM   Hotel.Clients
WHERE  NumClient % 500 = 0
UNION
SELECT Nom, Prenom, Adresse
FROM   Hotel.Clients NATURAL JOIN Hotel.Reservations
WHERE  extract(month from DateDebut)=7
  AND  extract(day from DateDebut)=21;
