-- Division par 0 
/* 
 * Ré-écrire la fonction de division pour tracer le problème de division par zéro (vous pouvez aussi utiliser les exceptions).
 * 
 */

drop function division;

CREATE OR REPLACE FUNCTION division(arg1 integer, arg2 integer)
RETURNS float4 AS
$BODY$
  BEGIN
    RETURN arg1::float4/arg2::float4;
    EXCEPTION WHEN OTHERS THEN
      -- attention, division par zéro
      RAISE EXCEPTION  'attention ERREUR capturée, [%]: %', SQLSTATE, SQLERRM;
      --RETURN 'NaN';
  END $BODY$
LANGUAGE 'plpgsql' VOLATILE;


select division(2,3);

select division(2,0);



/*
 * TRIGGER
 * 
 */




CREATE TABLE company (
 id SERIAL PRIMARY KEY NOT NULL,
 name TEXT NOT NULL,
 created_at TIMESTAMP,
 modified_at TIMESTAMP DEFAULT NOW()
)

CREATE TABLE table1 (
 id SERIAL PRIMARY KEY NOT NULL,
 name TEXT NOT NULL,
 created_at TIMESTAMP,
 modified_at TIMESTAMP DEFAULT NOW()
)

CREATE TABLE table2 (
 id SERIAL PRIMARY KEY NOT NULL,
 name TEXT NOT NULL,
 created_at TIMESTAMP,
 modified_at TIMESTAMP DEFAULT NOW()
)





-- Créer un trigger qui remplit le champ created_at sur la table Company à l'insertion

CREATE OR REPLACE FUNCTION add_created_at_function()
 RETURNS trigger AS $BODY$
BEGIN
 NEW.created_at := NOW();
 RETURN NEW;
END $BODY$
LANGUAGE plpgsql;


CREATE TRIGGER add_created_at_trigger
BEFORE INSERT
ON company
FOR EACH ROW
EXECUTE PROCEDURE add_created_at_function();


-- de la même façon créer un trigger qui met à jour l'attribut modified_at lors d'une mise à jour

CREATE OR REPLACE FUNCTION update_modified_at()
 RETURNS trigger AS $BODY$
BEGIN
 NEW.modified_at := NOW();
 RETURN NEW;
END $BODY$
LANGUAGE plpgsql;


CREATE TRIGGER update_modified_at
BEFORE update
ON company
FOR EACH ROW
EXECUTE PROCEDURE update_modified_at();

select * from company;
INSERT INTO company (name) VALUES ('Company 2');
INSERT INTO company (name) VALUES ('Company 3');
UPDATE company SET name='Company new 3' WHERE name='Company 3';



-- on peut ajouter ces triggers à table1 et table2; 
CREATE TRIGGER update_modified_at
BEFORE update
ON table1
FOR EACH ROW
EXECUTE PROCEDURE update_modified_at();


CREATE TRIGGER update_modified_at
BEFORE update
ON table2
FOR EACH ROW
EXECUTE PROCEDURE update_modified_at();


CREATE TRIGGER add_created_at_trigger
BEFORE INSERT
ON table1
FOR EACH ROW
EXECUTE PROCEDURE add_created_at_function();

CREATE TRIGGER add_created_at_trigger
BEFORE INSERT
ON table2
FOR EACH ROW
EXECUTE PROCEDURE add_created_at_function();


-- On va maintenant créer une nouvelle relation  : 

CREATE TABLE log (
 id SERIAL PRIMARY KEY NOT NULL,
 table_name TEXT NOT NULL,
 table_id TEXT NOT NULL,
 description TEXT NOT NULL,
 created_at TIMESTAMP DEFAULT NOW()
)



---  créer un trigger qui ajoute un tuple dans la table log après chaque modification (INSERT, DELETE, UPDATE) de la table company. On définira aussi des triggers s'appuyant sur la 
-- même fonction sur les relations table1 et table2;

CREATE OR REPLACE FUNCTION add_log_function()
 RETURNS trigger AS $BODY$
DECLARE
 vDescription TEXT;
 vId INT;
 vReturn RECORD;
BEGIN
 vDescription := TG_TABLE_NAME || ' ';
 IF (TG_OP = 'INSERT') then
 	if (TG_TABLE_NAME = 'employes')	then
 		 vId := NEW.matricule;
	else vId := NEW.id;
	end if;
 vDescription := vDescription || 'added. Id: ' || vId;
 vReturn := NEW;
 ELSIF (TG_OP = 'UPDATE') then
 if (TG_TABLE_NAME = 'employes')	then
 		 vId := NEW.matricule;
	else vId := NEW.id;
	end if;
 --vId := NEW.id;
 vDescription := vDescription || 'updated. Id: ' || vId;
 vReturn := NEW;
 ELSIF (TG_OP = 'DELETE') then
 if (TG_TABLE_NAME = 'employes')	then
 		 vId := NEW.matricule;
	else vId := NEW.id;
	end if;
 --vId := OLD.id;
 vDescription := vDescription || 'deleted. Id: ' || vId;
 vReturn := OLD;
 END IF;
 RAISE NOTICE 'TRIGER called on % - Log: %', TG_TABLE_NAME, vDescription;
 INSERT INTO log
 (table_name, table_id, description, created_at)
 values(TG_TABLE_NAME, vId, vDescription, NOW());
 RETURN vReturn;
END $BODY$
 LANGUAGE plpgsql;


CREATE TRIGGER add_log_trigger
AFTER INSERT OR UPDATE OR DELETE
ON company
FOR EACH ROW
EXECUTE PROCEDURE add_log_function();


CREATE TRIGGER add_log_trigger
AFTER INSERT OR UPDATE OR DELETE
ON table1
FOR EACH ROW
EXECUTE PROCEDURE add_log_function();


CREATE TRIGGER add_log_trigger
AFTER INSERT OR UPDATE OR DELETE
ON table2
FOR EACH ROW
EXECUTE PROCEDURE add_log_function();


INSERT INTO company (name) VALUES ('Company 3');
INSERT INTO company (name) VALUES ('Company 4');
INSERT INTO company (name) VALUES ('Company 5');
INSERT INTO company (name) VALUES ('Company 6');
UPDATE company SET name='Company new 2' WHERE name='Company 2';
DELETE FROM company WHERE name='Company 3';
insert into table1 (name) values ('toto');
insert into table2 (name) values ('tutu');
SELECT * FROM log;
 
 delete  from log;
INSERT INTO company (name) VALUES ('Company 5');
INSERT INTO company (name) VALUES ('Company 6');
UPDATE company SET name='Company new 5' WHERE name='Company 5';
DELETE FROM company WHERE name='Company 3';
select * from log l2
-- Modifier la fonction afin qu'elle puisse être utilisée pour un trigger similaire sur la table employes (TP précédent)


---  Sur la tabble employé, ajouter un attribut salaire 

-- Ajout attribut salaire

ALTER TABLE employes  
ADD COLUMN salaire numeric ;

select * from employes 

-- mettre à jour le salaire afin de la façon suivante : 
-- Salaire : salaire_départ + x * nb_année_dans_l'entreprise;
-- directeur : 3000 ; 499
-- Consultant ou développeurs  : 2000 , 199
-- Responsable 2500, 150
-- comptable 2400, 100
-- commercial 2000, 100


update employes  set salaire = (3000 + (2020 - date_part('year',date_embauche))*499) where fonction like 'Dir%';

-- 

select 3000 + (2020 - date_part('year',date_embauche))*499   from employes e2 where fonction  like 'Dir%';

-- Consultant  et Dév  2k + 199
update employes  set salaire = (2000 + (2020 - date_part('year',date_embauche))*199) where fonction like 'Con%' or fonction  like 'Dév%';
select * from employes 

-- Responsable 2.5k + 150
update employes  set salaire = (2500 + (2020 - date_part('year',date_embauche))*150) where fonction like 'Res%';
-- Comptable : 2.4k + 500 tous les 5 ans
update employes  set salaire = (2400 + (2020 - date_part('year',date_embauche))*100) where fonction like 'Comp%';

-- Commercial : 2k + 100 par an
update employes  set salaire = (2000 + (2020 - date_part('year',date_embauche))*100) where fonction like 'Comm%'; 


select * from employes e 

select * from log l ;




-- Créer un trigger sur Employé afin d'empêcher toute baisse de salaire;


CREATE or replace FUNCTION emp_salaire() RETURNS trigger
AS $$
    BEGIN
      
        if TG_OP = 'UPDATE' then
	        IF NEW.salaire < old.salaire THEN
    	        RAISE EXCEPTION '% ne peut pas avoir un salaire plus bas', old.nom;
        	END IF;
        end if;

        -- Rappelons-nous qui a changé le salaire et quand
        --NEW.date_dermodif := current_timestamp;
        --NEW.date_dermodif := current_user;
        RETURN NEW;
       end;
       $$  LANGUAGE plpgsql;
       
      
     
 drop trigger e on employes;

 create  trigger emp_salaire after insert or update on employes 
for each row 
execute procedure emp_salaire();     

update employes  set salaire = (2000 - (2020 - date_part('year',date_embauche))*100) where fonction like 'Comm%'; 


-- Créer un trigger qui a chaque insertion ou modification vérifie qu'une personne ne peut pas avoir un salaire plus important qu'un de ses collègues (même département)  occupant la 
-- même fonction


