alter table object add column statut int4;
alter table object alter column statut set default '0';

drop function pton_statut();

create function pton_statut()
returns opaque

AS '
begin
	IF NEW.ref_statut is not NULL THEN
--		RAISE NOTICE ''making statut from ref_statut'';
		NEW.statut := 1 - NEW.ref_statut;
		return NEW;
	END IF;
	IF NEW.statut is not NULL THEN
--		RAISE NOTICE ''making ref_statut from statut'';
		NEW.ref_statut := 1 - NEW.statut;
		return NEW;
	END IF;
	return NEW;
end;'

language 'plpgsql';

drop trigger trigger_in_pton_statut on object;
create trigger trigger_in_pton_statut
before insert on object
for each row execute procedure pton_statut();

drop trigger trigger_up_pton_statut on object;
create trigger trigger_up_pton_statut
before update on object
for each row execute procedure pton_statut();

drop trigger trigger_in_pton_statut on personne;
create trigger trigger_in_pton_statut
before insert on personne
for each row execute procedure pton_statut();

drop trigger trigger_up_pton_statut on personne;
create trigger trigger_up_pton_statut
before update on personne
for each row execute procedure pton_statut();

drop trigger trigger_in_pton_statut on personne_physique;
create trigger trigger_in_pton_statut
before insert on personne_physique
for each row execute procedure pton_statut();

drop trigger trigger_up_pton_statut on personne_physique;
create trigger trigger_up_pton_statut
before update on personne_physique
for each row execute procedure pton_statut();

drop trigger trigger_in_pton_statut on societe;
create trigger trigger_in_pton_statut
before insert on societe
for each row execute procedure pton_statut();

drop trigger trigger_up_pton_statut on societe;
create trigger trigger_up_pton_statut
before update on societe
for each row execute procedure pton_statut();

drop trigger trigger_in_pton_statut on userid;
create trigger trigger_in_pton_statut
before insert on userid
for each row execute procedure pton_statut();

drop trigger trigger_up_pton_statut on userid;
create trigger trigger_up_pton_statut
before update on userid
for each row execute procedure pton_statut();

drop trigger trigger_in_pton_statut on travail_pour;
create trigger trigger_in_pton_statut
before insert on travail_pour
for each row execute procedure pton_statut();

drop trigger trigger_up_pton_statut on travail_pour;
create trigger trigger_up_pton_statut
before update on travail_pour
for each row execute procedure pton_statut();

alter table only personne_physique add column administrateur int4;
alter table only personne_physique alter column administrateur set default '0';
alter table only travail_pour add column ref_employe int4;
alter table only userid add column ref_employe int4;
alter table only userid alter column ref_employe set default '0';

drop function pton_employe();

create function pton_employe()
returns opaque
AS '
begin
	IF NEW.ref_employe is not NULL THEN
		RAISE NOTICE ''making ref_personne_physique from ref_employe'';
		NEW.ref_personne_physique := NEW.ref_employe;
		return NEW;
	END IF;
	IF NEW.ref_personne_physique is not NULL THEN
		RAISE NOTICE ''making ref_employe from ref_personne_physique'';
		NEW.ref_employe := NEW.ref_personne_physique;
		return NEW;
	END IF;
	return NEW;
end;'

language 'plpgsql';

drop trigger trigger_in_pton_employe on userid;
create trigger trigger_in_pton_employe
before insert on userid
for each row execute procedure pton_employe();

drop trigger trigger_in_pton_employe on userid;
create trigger trigger_in_pton_employe
before update on userid
for each row execute procedure pton_employe();

alter table personne add column euid text;
alter table personne add column pays text;

drop view employe;
create view employe as select p.*,t.ref_societe from personne_physique p left join travail_pour t on (t.ref_personne_physique=p.uid) left join societe s on (t.ref_societe = s.uid) left join statut st on (t.ref_statut=st.uid and s.ref_statut=st.uid and st.nom='valide');

drop rule rule_employe_insert;
create rule rule_employe_insert as
on insert to employe do instead (
	INSERT INTO personne_physique 
	(uid, authmodif, datemodif, datecrea, nom, authcrea, codecrea, statut, codemodif, email, cp, adresse1, tel2, adresse2, ville, tel, fax, web, code, commentaire, administrateur, prenom, naissance, civilite)
	VALUES 
	(new.uid, new.authmodif, new.datemodif, new.datecrea, new.nom, new.authcrea, new.codecrea, new.statut, new.codemodif, new.email, new.cp, new.adresse1, new.tel2, new.adresse2, new.ville, new.tel, new.fax, new.web, new.code, new.commentaire, new.administrateur, new.prenom, new.naissance, new.civilite);
	insert into travail_pour (ref_personne_physique, ref_societe, ref_statut) values (new.uid, new.ref_societe, (select uid from statut where nom='valide'));
);

drop rule rule_employe_update;
create rule rule_employe_update as
on update to employe do instead (
	update personne_physique set authmodif = new.authmodif, datemodif = coalesce(new.datemodif, date('now')), datecrea = new.datecrea, nom = new.nom, authcrea = new.authcrea, codecrea = new.codecrea, statut = new.statut, codemodif = new.codemodif, email = new.email, cp = new.cp, adresse1 = new.adresse1, tel2 = new.tel2, adresse2 = new.adresse2, ville = new.ville, tel = new.tel, fax = new.fax, web = new.web, code = new.code, commentaire = new.commentaire, administrateur = new.administrateur, prenom = new.prenom, naissance = new.naissance, civilite = new.civilite
	where uid = new.uid;

--  trop dangeureux :
--	update travail_pour set ref_societe = new.ref_societe, ref_statut = (select uid from statut where nom='valide') where ref_personne = new.uid;

);


update object set statut=0 where statut is NULL;