DROP TRIGGER IF EXISTS date_inscription_trigger ON reserve;

CREATE OR REPLACE FUNCTION date_inscription()
  RETURNS trigger AS
$BODY$
DECLARE
debut   timestamp;
fin timestamp;
s_id    int;
local_deb time;
local_fin time;
BEGIN
SELECT reserve.reserv_debut into debut FROM reserve WHERE locaux_id = NEW.locaux_id AND batiment_id = NEW.batiment_id;
SELECT reserve.reserv_fin into fin FROM reserve WHERE locaux_id = NEW.locaux_id AND batiment_id = NEW.batiment_id;
SELECT beneficie.status_id into s_id FROM beneficie WHERE cip = NEW.cip;
SELECT locaux.horaire_depart into local_deb FROM locaux WHERE locaux_id = NEW.locaux_id AND batiment_id = NEW.batiment_id;
SELECT locaux.horaire_fin into local_fin FROM locaux WHERE locaux_id = NEW.locaux_id AND batiment_id = NEW.batiment_id;

raise notice 'Value: %', local_deb;
raise notice 'Value: %', debut::time;
raise notice 'Value: %', local_fin;
raise notice 'Value: %', fin::time;

IF ((NEW.reserv_debut > debut)
    AND (NEW.reserv_debut > fin)
    AND (NEW.reserv_fin > debut)
    AND (NEW.reserv_fin > fin))
OR ((NEW.reserv_debut < debut)
    AND (NEW.reserv_debut < fin)
    AND (NEW.reserv_fin < debut)
    AND (NEW.reserv_fin < fin)) THEN

    IF(s_id = 2 OR s_id = 3) THEN
        IF(local_deb < NEW.reserv_debut::time AND local_fin > NEW.reserv_fin::time) THEN
            IF(to_seconds(debut) % 900 = 0 AND to_seconds(fin) % 900 = 0) THEN
                INSERT INTO journal(journal_id, cip, journal_description) VALUES (DEFAULT, NEW.cip, 'Creation de reservation');
                RETURN NEW;
            ELSE
                RAISE EXCEPTION 'La reservation doit etre par tranches de 15 minutes';
                RETURN -1;
            END IF;
        ELSE
            RAISE EXCEPTION 'Local est fermé pour cette période';
            RETURN -1;
        END IF;
    ELSE
        RAISE EXCEPTION 'User ne possede pas les permissions';
        RETURN -1;
    END IF;
ELSE
RAISE EXCEPTION 'Bloc dheure deja utilise';
RETURN -1;
END IF;
END;
$BODY$
LANGUAGE 'plpgsql';


CREATE TRIGGER date_inscription_trigger
  BEFORE INSERT
  ON reserve
  FOR EACH ROW
  EXECUTE PROCEDURE date_inscription();

CREATE OR REPLACE FUNCTION to_seconds(t timestamp)
  RETURNS integer AS
$BODY$
DECLARE
    hs INTEGER;
    ms INTEGER;
    s INTEGER;
BEGIN
    SELECT (EXTRACT( HOUR FROM  t::time) * 60*60) INTO hs;
    SELECT (EXTRACT (MINUTES FROM t::time) * 60) INTO ms;
    SELECT (EXTRACT (SECONDS from t::time)) INTO s;
    SELECT (hs + ms + s) INTO s;
    RETURN s;
END;
$BODY$
  LANGUAGE 'plpgsql';