-------------------------------------- Divers, Global -------------------------------------------
-------------------------------------------------------------------------------------------------

-- Pour recuperer les notifications
LISTEN Operation_interdite;

-- Fonction controlant, lors d'une insertion, que la nouvelle clef n'est pas deja dans Descriptor
CREATE FUNCTION Insert () RETURNS OPAQUE
AS '
DECLARE  result numeric;
BEGIN
   SELECT COUNT (*) INTO result FROM Descriptor WHERE descriptor_id = NEW.descriptor_id;
   IF result <> 0
   THEN
      RAISE EXCEPTION ''Operation interdite'';
   END IF;

   RETURN NEW;
END;
' LANGUAGE plpgsql;

-- Fonction interdisant de changer la valeur d'une clef
CREATE FUNCTION Update () RETURNS OPAQUE
AS '
BEGIN
   IF NEW.descriptor_id <> OLD.descriptor_id
   THEN
      RAISE EXCEPTION ''Operation interdite'';
   END IF;

   RETURN NEW;
END;
' LANGUAGE plpgsql;


-------------------------------------------------------------------------------------------------



---------------------------------------- Descriptor ---------------------------------------------
-------------------------------------------------------------------------------------------------

CREATE TABLE Descriptor (descriptor_id SERIAL PRIMARY KEY,
                         descriptor_label varchar (100) NOT NULL);

GRANT SELECT, INSERT, DELETE, UPDATE ON Descriptor TO PUBLIC;

-- On ignore les insertions, mises a jour et supppressions directes

CREATE RULE No_Insert_D AS ON INSERT TO Descriptor
                           DO INSTEAD NOTIFY Operation_interdite;
 
CREATE RULE No_Update_D AS ON UPDATE TO Descriptor 
                           DO INSTEAD NOTIFY Operation_interdite;

CREATE RULE No_Delete_D AS ON DELETE TO Descriptor
                           DO INSTEAD NOTIFY Operation_interdite;

-------------------------------------------------------------------------------------------------


--------------------------------- GeneralizableDescriptor ---------------------------------------
-------------------------------------------------------------------------------------------------

CREATE TABLE GeneralizableDescriptor (CONSTRAINT PK_GD PRIMARY KEy (descriptor_id))
                                     inherits (Descriptor);

GRANT SELECT, INSERT, DELETE, UPDATE ON GeneralizableDescriptor TO PUBLIC;

-- On ignore les insertions, mises a jour et supppressions directes

CREATE RULE No_Insert_GD AS ON INSERT TO GeneralizableDescriptor 
                            DO INSTEAD NOTIFY Operation_interdite;

CREATE RULE No_Update_GD AS ON UPDATE TO GeneralizableDescriptor
                            DO INSTEAD NOTIFY Operation_interdite;

CREATE RULE No_Delete_GD AS ON DELETE TO GeneralizableDescriptor 
                            DO INSTEAD NOTIFY Operation_interdite;

-------------------------------------------------------------------------------------------------


--------------------------------- SpecializableDescriptor ---------------------------------------
-------------------------------------------------------------------------------------------------

CREATE TABLE SpecializableDescriptor (CONSTRAINT PK_SD PRIMARY KEy (descriptor_id))
                                     inherits (Descriptor);

GRANT SELECT, INSERT, DELETE, UPDATE ON SpecializableDescriptor TO PUBLIC;

-- On ignore les insertions, mises a jour et supppressions directes

CREATE RULE No_Insert_SD AS ON INSERT TO SpecializableDescriptor
                            DO INSTEAD NOTIFY Operation_interdite;

CREATE RULE No_Update_SD AS ON UPDATE TO SpecializableDescriptor
                            DO INSTEAD NOTIFY Operation_interdite;

CREATE RULE No_Delete_SD AS ON DELETE TO SpecializableDescriptor 
                            DO INSTEAD NOTIFY Operation_interdite;

-------------------------------------------------------------------------------------------------


------------------------------------------ Facet ------------------------------------------------
-------------------------------------------------------------------------------------------------

CREATE TABLE Facet (CONSTRAINT PK_Facet PRIMARY KEy (descriptor_id))
                   inherits (SpecializableDescriptor);

GRANT SELECT, INSERT, DELETE, UPDATE ON Facet TO PUBLIC;

CREATE TRIGGER Facet_Insert
    BEFORE  INSERT ON Facet FOR EACH ROW
    EXECUTE PROCEDURE Insert ();

CREATE TRIGGER Facet_Update
    BEFORE UPDATE ON Facet FOR EACH ROW
    EXECUTE PROCEDURE Update ();

-------------------------------------------------------------------------------------------------


------------------------------------ OrdinaryDescriptor -----------------------------------------
-------------------------------------------------------------------------------------------------

CREATE TABLE OrdinaryDescriptor(CONSTRAINT PK_OD PRIMARY KEY (descriptor_id))
                                inherits (SpecializableDescriptor, GeneralizableDescriptor);

GRANT SELECT, INSERT, DELETE, UPDATE ON OrdinaryDescriptor TO PUBLIC;

CREATE TRIGGER OD_Insert
    BEFORE  INSERT ON OrdinaryDescriptor FOR EACH ROW
    EXECUTE PROCEDURE Insert ();

CREATE TRIGGER OD_Update
    BEFORE UPDATE ON OrdinaryDescriptor FOR EACH ROW
    EXECUTE PROCEDURE Update ();

CREATE TRIGGER OD_Delete
    AFTER DELETE ON OrdinaryDescriptor FOR EACH ROW
    EXECUTE PROCEDURE rien ();

-------------------------------------------------------------------------------------------------

---------------------------------------- Identifier ---------------------------------------------
-------------------------------------------------------------------------------------------------

CREATE TABLE Identifier (identifier_documentObject_id varchar (100) NOT NULL,
                         identifier_documentObject_ip inet NOT NULL,
                         CONSTRAINT PK_ID PRIMARY KEY (descriptor_id))
                         inherits (GeneralizableDescriptor);

GRANT SELECT, INSERT, DELETE, UPDATE ON Identifier TO PUBLIC;

CREATE TRIGGER Identifier_Insert
    BEFORE  INSERT ON Identifier FOR EACH ROW
    EXECUTE PROCEDURE Insert ();

CREATE TRIGGER Identifier_Update
    BEFORE UPDATE ON Identifier FOR EACH ROW
    EXECUTE PROCEDURE Update ();

-------------------------------------------------------------------------------------------------


----------------------------------------- Context -----------------------------------------------
-------------------------------------------------------------------------------------------------

CREATE TABLE Context (context_id SERIAL PRIMARY KEY, context_isPublic boolean NOT NULL,
                      context_isPrivate boolean NOT NULL);

GRANT SELECT, INSERT, DELETE, UPDATE ON Context TO PUBLIC;

-- On ignore les insertions, mises a jour et supppressions directes

CREATE RULE No_Insert_Context AS ON INSERT TO Context 
                              DO INSTEAD NOTIFY Operation_interdite;
 
CREATE RULE No_Update_Context AS ON UPDATE TO Context 
                              DO INSTEAD NOTIFY Operation_interdite;

CREATE RULE No_Delete_Context AS ON DELETE TO Context 
                              DO INSTEAD NOTIFY Operation_interdite;

-------------------------------------------------------------------------------------------------


----------------------------------------- Situation ---------------------------------------------
-------------------------------------------------------------------------------------------------

CREATE FUNCTION Ctrl_Context (integer) RETURNS bigint
AS
   'SELECT COUNT (*) FROM Context WHERE context_id = $1'
LANGUAGE sql;


CREATE TABLE Situation (situation_id SERIAL PRIMARY KEY,
                        situation_when timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
                        situation_what smallint NOT NULL,
                        situation_who varchar (100) NOT NULL,
                        context_id integer NOT NULL,
                        CONSTRAINT FK_Situ_Context CHECK (Ctrl_Context (context_id) = 1),
                        CONSTRAINT CK_Situ CHECK (situation_what BETWEEN 0 AND 2));

GRANT SELECT, INSERT, DELETE, UPDATE ON Situation TO PUBLIC;

-------------------------------------------------------------------------------------------------


-------------------------------------- Specialization -------------------------------------------
-------------------------------------------------------------------------------------------------

-- Fonction controlant, lors d'une insertion, que la nouvelle clef n'est pas deja dans Context
CREATE FUNCTION Insert_Spec () RETURNS OPAQUE
AS '
DECLARE  result numeric;
BEGIN
   SELECT COUNT (*) INTO result FROM Context WHERE context_id = NEW.context_id;
   IF result <> 0
   THEN
      RAISE EXCEPTION ''Operation interdite'';
   END IF;

   RETURN NEW;
END;
' LANGUAGE plpgsql;

-- Fonction interdisant de changer la valeur d'une clef
CREATE FUNCTION Update_Spec () RETURNS OPAQUE
AS '
BEGIN
   IF NEW.context_id <> OLD.context_id
   THEN
      RAISE EXCEPTION ''Operation interdite'';
   END IF;

   RETURN NEW;
END;
' LANGUAGE plpgsql;


CREATE FUNCTION Ctrl_SD(integer)
        returns bigint
        as 'SELECT COUNT (*) FROM SpecializableDescriptor WHERE descriptor_id = $1'
language sql;

CREATE FUNCTION Ctrl_GD(integer)
        returns bigint
        as 'SELECT COUNT (*) FROM GeneralizableDescriptor WHERE descriptor_id = $1'
language sql;

CREATE TABLE Specialization (generalDescriptor_id integer,
                             specialDescriptor_id integer,
                             CONSTRAINT CK_Spec CHECK (generalDescriptor_id <> specialDescriptor_id),
                             CONSTRAINT FK_Spec_SD CHECK (Ctrl_SD(generalDescriptor_id) = 1),
                             CONSTRAINT FK_Spec_GD CHECK (Ctrl_GD(specialDescriptor_id) = 1),
                             CONSTRAINT UK_Spec_Ctxt UNIQUE (context_id),
			     CONSTRAINT UK_Spec_SDGD UNIQUE (generalDescriptor_id,
                                                             specialDescriptor_id),
                             CONSTRAINT PK_Spec PRIMARY KEY (context_id, generalDescriptor_id,
                                                             specialDescriptor_id))
			    inherits (Context);

GRANT SELECT, INSERT, DELETE, UPDATE ON Specialization TO PUBLIC;

CREATE TRIGGER Spec_Insert
    BEFORE  INSERT ON Specialization FOR EACH ROW
    EXECUTE PROCEDURE Insert_Spec ();

CREATE TRIGGER Spec_Update
    BEFORE UPDATE ON Specialization FOR EACH ROW
    EXECUTE PROCEDURE Update_Spec ();

-------------------------------------------------------------------------------------------------


------------------------------------------ pUser -----------------------------------------------
-------------------------------------------------------------------------------------------------

CREATE TABLE pUser (user_login varchar (8) PRIMARY KEY, user_password varchar (8) NOT nULL,
                     user_name varchar (30) NOT NULL,
                     CONSTRAINT CK_Password CHECK (char_length (user_password) = 8));

GRANT SELECT, INSERT, DELETE, UPDATE ON pUser TO PUBLIC;

-------------------------------------------------------------------------------------------------


--------------------------------------- AccessRight ---------------------------------------------
-------------------------------------------------------------------------------------------------

CREATE TABLE AccessRight (facet_id integer REFERENCES Facet (descriptor_id) ON DELETE CASCADE,
                         user_login varchar (8) REFERENCES pUser (user_login) ON DELETE CASCADE,
                         accessRight_readOnly boolean NOT nULL,
                         CONSTRAINT PK_AR PRIMARY KEY (facet_id, user_login));

GRANT SELECT, INSERT, DELETE, UPDATE ON AccessRight TO PUBLIC;

-------------------------------------------------------------------------------------------------


------------------------------------------ Trail ------------------------------------------------
-------------------------------------------------------------------------------------------------

CREATE TABLE Trail (trail_id SERIAL PRIMARY KEY, trail_label varchar (100) NOT NULL,
                    facet_id integer REFERENCES Facet (descriptor_id) ON DELETE CASCADE);

GRANT SELECT, INSERT, DELETE, UPDATE ON Trail TO PUBLIC;

-------------------------------------------------------------------------------------------------


------------------------------------------- Step ------------------------------------------------
-------------------------------------------------------------------------------------------------

CREATE TABLE Temp_Step (cdo_id varchar (100), cdo_ip inet);

GRANT SELECT, INSERT, DELETE, UPDATE ON Temp_Step TO PUBLIC;

CREATE TABLE Step (trail_id integer REFERENCES Trail (trail_id) ON DELETE CASCADE,
                   currentDocumentObject_id varchar (100) NOT NULL,
                   currentDocumentObject_ip inet NOT NULL,
                   documentObjectCorpus text,
                   previousDocumentObject_id varchar (100),
                   previousDocumentObject_ip inet,
                   CONSTRAINT UK_Step_idip UNIQUE (trail_id, currentDocumentObject_id, currentDocumentObject_ip),
                   CONSTRAINT UK_Step_Ctxt UNIQUE (context_id),
                   CONSTRAINT PK_Step PRIMARY KEy (context_id, trail_id, currentDocumentObject_id, 
                                                   currentDocumentObject_ip))
                  inherits (Context);

GRANT SELECT, INSERT, DELETE, UPDATE ON Step TO PUBLIC;

-- Cette fonction insere une nouvelle etape d'un parcours de lecture 
-- EN FIN DE PARCOURS, quels que soient les champs specifies
-- Elle verifie et garantit la coherence par rapport a la table context
CREATE FUNCTION next_Step_Insert () RETURNS OPAQUE
AS '
DECLARE result numeric;
DECLARE cdo_id varchar (100);
DECLARE cdo_ip inet;
BEGIN

   SELECT COUNT (*) INTO result FROM Context WHERE context_id = NEW.context_id;
   IF result <> 0
   THEN
      RAISE EXCEPTION ''Operation interdite'';
   END IF;

   SELECT currentdocumentObject_id, currentdocumentObject_ip into cdo_id, cdo_ip FROM Step S1 
                                    WHERE NEW.trail_id = S1.trail_id
                                    AND ((SELECT COUNT (*) FROM Step S2 WHERE 
                                    NEW.trail_id = S2.trail_id
                                    AND S1.currentdocumentobject_id = S2.previousdocumentObject_id
                                    AND S1.currentdocumentobject_ip = S2.previousdocumentObject_ip) = 0);

   NEW.previousDocumentObject_id = cdo_id;
   NEW.previousDocumentObject_ip = cdo_ip;   
   RETURN NEW;

END;
' LANGUAGE plpgsql;


-- voir fonction next_Step_Insert ()
CREATE TRIGGER next_Step_Insert
    BEFORE INSERT ON Step FOR EACH ROW
    EXECUTE PROCEDURE next_Step_Insert ();


-- Cette fonction realise l'update d'une etape d'un parcours de lecture
-- Elle rechaine l'ansemble de la chaine de facon a prendre en compte les modifications
-- elle interdit les mises a jour de context_id et de trail_id et verifie les valeurs nulles
-- sur les previous ainsi que l'existence de la nouvelle etape precedente
CREATE FUNCTION next_Step_Update () RETURNS OPAQUE
AS '
DECLARE present numeric;
DECLARE temp numeric;
DECLARE num numeric;
DECLARE cdo_id_before varchar (100);
DECLARE cdo_ip_before inet;
DECLARE cdo_id_after varchar (100);
DECLARE cdo_ip_after inet;

BEGIN

   IF NEW.context_id <> OLD.context_id
   THEN
      RAISE EXCEPTION ''Operation interdite'';
   END IF;


   IF ((NEW.previousDocumentObject_id = OLD.previousDocumentObject_id) 
      AND (NEW.previousDocumentObject_ip = OLD.previousDocumentObject_ip))
   THEN
       RETURN NEW;
   END IF;


   IF NEW.trail_id != OLD.trail_id 
   THEN
      RAISE EXCEPTION ''Operation interdite'';
   END IF;


   IF (((NEW.previousDocumentObject_id IS NULL) AND (NEW.previousDocumentObject_ip IS NOT NULL))
       OR ((NEW.previousDocumentObject_id IS NOT NULL) AND (NEW.previousDocumentObject_ip IS NULL)))
   THEN 
      RAISE EXCEPTION ''Operation interdite : valeur NULL non autorisee'';
   END IF;


   SELECT COUNT (*) INTO present FROM Step WHERE trail_id = NEW.trail_id
                         AND currentDocumentObject_id = NEW.previousDocumentObject_id
                         AND currentDocumentObject_ip = NEW.previousDocumentObject_ip;


   IF ((NEW.previousDocumentObject_id IS NOT NULL) AND (NEW.previousDocumentObject_ip IS NOT NULL)
                                                   AND (present != 1))
   THEN
       RAISE EXCEPTION ''Operation interdite : etape precedente inexistante'';
   END IF;



   SELECT COUNT (*) INTO temp FROM Temp_Step WHERE cdo_id = NEW.currentdocumentobject_id
                                                    AND cdo_ip = NEW.currentdocumentobject_ip;

   IF  temp != 0
   THEN
      RETURN NEW;
   END IF;

   SELECT currentdocumentobject_id, currentdocumentobject_ip INTO cdo_id_before, cdo_ip_before FROM Step
   WHERE previousdocumentobject_id = OLD.currentdocumentobject_id
   AND previousdocumentobject_ip = OLD.currentdocumentobject_ip
   AND trail_id = OLD.trail_id;

   IF NEW.previousdocumentobject_id IS NULL
   THEN

          SELECT currentdocumentobject_id, currentdocumentobject_ip INTO cdo_id_after, cdo_ip_after FROM Step 
          WHERE previousdocumentobject_id IS NULL
          AND previousdocumentobject_ip IS NULL
          AND trail_id = OLD.trail_id;

   END IF;


   INSERT INTO Temp_Step VALUES (cdo_id_before, cdo_ip_before);


   UPDATE Step SET previousdocumentobject_id = OLD.previousdocumentobject_id, 
                   previousdocumentobject_ip = OLD.previousdocumentobject_ip
   	 	   WHERE currentdocumentobject_id = cdo_id_before
                   AND currentdocumentobject_ip = cdo_ip_before
                   AND trail_id = OLD.trail_id;


   IF NEW.previousdocumentobject_id IS NULL
   THEN

      SELECT currentdocumentobject_id, currentdocumentobject_ip INTO cdo_id_after, cdo_ip_after FROM Step 
      WHERE previousdocumentobject_id IS NULL
      AND previousdocumentobject_ip IS NULL
      AND trail_id = OLD.trail_id;

   END IF;

   IF NEW.previousdocumentobject_id IS NOT NULL
   THEN

       SELECT currentdocumentobject_id, currentdocumentobject_ip INTO cdo_id_after, cdo_ip_after FROM Step 
       WHERE previousdocumentobject_id = NEW.previousdocumentobject_id
       AND previousdocumentobject_ip = NEW.previousdocumentobject_ip
       AND trail_id = OLD.trail_id;

   END IF;

   INSERT INTO Temp_Step VALUES (cdo_id_after, cdo_ip_after);


   UPDATE Step SET previousdocumentobject_id = OLD.currentdocumentobject_id, 
                   previousdocumentobject_ip = OLD.currentdocumentobject_ip
  	           WHERE currentdocumentobject_id = cdo_id_after
                   AND currentdocumentobject_ip = cdo_ip_after
                   AND trail_id = OLD.trail_id;




   DELETE FROM Temp_Step;

   RETURN NEW;

END;
' LANGUAGE plpgsql;


-- voir fonction next_Step_Update ()
CREATE TRIGGER next_Step_Update
    BEFORE UPDATE ON Step FOR EACH ROW
    EXECUTE PROCEDURE next_Step_Update ();


-- Cette fonction realise la suppression d'un etape de lecture dans un parcours de lecture
-- Elle rechaine automatiquement le parcours en tenant compte de cette suppression
CREATE FUNCTION next_Step_Delete () RETURNS OPAQUE
AS '
DECLARE temp numeric;
DECLARE cdo_id_before varchar (100);
DECLARE cdo_ip_before inet;

BEGIN



   SELECT currentdocumentobject_id, currentdocumentobject_ip INTO cdo_id_before, cdo_ip_before FROM Step
   WHERE previousdocumentobject_id = OLD.currentdocumentobject_id
   AND previousdocumentobject_ip = OLD.currentdocumentobject_ip
   AND trail_id = OLD.trail_id;


   INSERT INTO Temp_Step VALUES (cdo_id_before, cdo_ip_before);


   UPDATE Step SET previousdocumentobject_id = OLD.previousdocumentobject_id, 
                   previousdocumentobject_ip = OLD.previousdocumentobject_ip
   	 	   WHERE currentdocumentobject_id = cdo_id_before
                   AND currentdocumentobject_ip = cdo_ip_before
                   AND trail_id = OLD.trail_id;

   DELETE FROM Temp_Step;

   RETURN NEW;

END;
' LANGUAGE plpgsql;


-- voir fonction next_Step_Delete ()
CREATE TRIGGER next_Step_Delete
    AFTER DELETE ON Step FOR EACH ROW
    EXECUTE PROCEDURE next_Step_Delete ();
-------------------------------------------------------------------------------------------------

GRANT SELECT, INSERT, DELETE, UPDATE ON descriptor_descriptor_id_seq TO PUBLIC;
GRANT SELECT, INSERT, DELETE, UPDATE ON context_context_id_seq TO PUBLIC;
GRANT SELECT, INSERT, DELETE, UPDATE ON situation_situation_id_seq TO PUBLIC;