--
INSERT INTO om_collectivite (om_collectivite, libelle, niveau) VALUES 
(nextval('om_collectivite_seq'), 'MULTI', '2');
INSERT INTO om_parametre (om_parametre, libelle, valeur, om_collectivite) VALUES 
(nextval('om_parametre_seq'), 'ville', 'MULTI', (SELECT om_collectivite FROM om_collectivite WHERE libelle = 'MULTI'));
INSERT INTO om_utilisateur (om_utilisateur, nom, email, login, pwd, om_collectivite, om_type, om_profil) VALUES 
(nextval('om_utilisateur_seq'), 'Administrateur MULTI', 'nospam@openmairie.org', 'multi', '21232f297a57a5a743894a0e4a801fc3', (SELECT om_collectivite FROM om_collectivite WHERE libelle = 'MULTI'), 'DB', (SELECT om_profil FROM om_profil WHERE libelle = 'ADMINISTRATEUR'));
INSERT INTO om_collectivite (om_collectivite, libelle, niveau) VALUES 
(nextval('om_collectivite_seq'), 'OPENVILLE', '1');
INSERT INTO om_parametre (om_parametre, libelle, valeur, om_collectivite) VALUES 
(nextval('om_parametre_seq'), 'ville', 'OPENVILLE', (SELECT om_collectivite FROM om_collectivite WHERE libelle = 'OPENVILLE'));
--
ALTER TABLE emplacement ADD COLUMN om_collectivite INTEGER;
UPDATE emplacement SET om_collectivite = (SELECT om_collectivite FROM om_collectivite WHERE libelle = 'LIBREVILLE');
ALTER TABLE emplacement ALTER COLUMN om_collectivite SET NOT NULL;
ALTER TABLE ONLY emplacement
    ADD CONSTRAINT emplacement_om_collectivite_fkey FOREIGN KEY (om_collectivite) REFERENCES om_collectivite(om_collectivite);
--
ALTER TABLE cimetiere ADD COLUMN om_collectivite INTEGER;
UPDATE cimetiere SET om_collectivite = (SELECT om_collectivite FROM om_collectivite WHERE libelle = 'LIBREVILLE');
ALTER TABLE cimetiere ALTER COLUMN om_collectivite SET NOT NULL;
ALTER TABLE ONLY cimetiere
    ADD CONSTRAINT cimetiere_om_collectivite_fkey FOREIGN KEY (om_collectivite) REFERENCES om_collectivite(om_collectivite);
--
ALTER TABLE zone ADD COLUMN om_collectivite INTEGER;
UPDATE zone SET om_collectivite = (SELECT om_collectivite FROM om_collectivite WHERE libelle = 'LIBREVILLE');
ALTER TABLE zone ALTER COLUMN om_collectivite SET NOT NULL;
ALTER TABLE ONLY zone
    ADD CONSTRAINT zone_om_collectivite_fkey FOREIGN KEY (om_collectivite) REFERENCES om_collectivite(om_collectivite);
--
ALTER TABLE voie ADD COLUMN om_collectivite INTEGER;
UPDATE voie SET om_collectivite = (SELECT om_collectivite FROM om_collectivite WHERE libelle = 'LIBREVILLE');
ALTER TABLE voie ALTER COLUMN om_collectivite SET NOT NULL;
ALTER TABLE ONLY voie
    ADD CONSTRAINT voie_om_collectivite_fkey FOREIGN KEY (om_collectivite) REFERENCES om_collectivite(om_collectivite);


CREATE OR REPLACE FUNCTION check_zone_om_collectivite_coherence() RETURNS trigger AS
$check_zone_om_collectivite_coherence$
BEGIN
IF NEW.om_collectivite != (SELECT om_collectivite FROM opencimetiere.cimetiere WHERE cimetiere.cimetiere = NEW.cimetiere) then
    RAISE EXCEPTION 'La zone doit posséder la même om_collectivite que son cimetière.';
END IF;
RETURN NEW;
END;
$check_zone_om_collectivite_coherence$
LANGUAGE 'plpgsql' VOLATILE;

CREATE OR REPLACE FUNCTION check_voie_om_collectivite_coherence() RETURNS trigger AS
$check_voie_om_collectivite_coherence$
BEGIN
IF NEW.om_collectivite != (SELECT zone.om_collectivite FROM opencimetiere.zone WHERE zone.zone = NEW.zone) 
    AND NEW.om_collectivite != (SELECT cimetiere.om_collectivite FROM opencimetiere.cimetiere INNER JOIN opencimetiere.zone ON zone.zone = NEW.zone) then
    RAISE EXCEPTION 'La voie doit posséder la même om_collectivite que sa zone.';
END IF;
RETURN NEW;
END;
$check_voie_om_collectivite_coherence$
LANGUAGE 'plpgsql' VOLATILE;

CREATE OR REPLACE FUNCTION check_emplacement_om_collectivite_coherence() RETURNS trigger AS
$check_emplacement_om_collectivite_coherence$
BEGIN
IF NEW.om_collectivite != (SELECT voie.om_collectivite FROM opencimetiere.voie WHERE voie.voie = NEW.voie)
    AND NEW.om_collectivite != (SELECT zone.om_collectivite FROM opencimetiere.zone INNER JOIN opencimetiere.voie ON voie.voie = NEW.voie AND voie.zone = zone.zone)
    AND NEW.om_collectivite != (SELECT cimetiere.om_collectivite FROM opencimetiere.cimetiere INNER JOIN opencimetiere.zone ON zone.cimetiere = cimetiere.cimetiere INNER JOIN opencimetiere.voie ON voie.voie = NEW.voie  AND voie.zone = zone.zone) then
    RAISE EXCEPTION 'L''emplacement doit posséder la même om_collectivite que sa zone, sa voie et son cimetière.';
END IF;
RETURN NEW;
END;
$check_emplacement_om_collectivite_coherence$
LANGUAGE 'plpgsql' VOLATILE;


CREATE TRIGGER before_insert_or_update_zone
    BEFORE INSERT OR UPDATE ON zone
    FOR EACH ROW EXECUTE PROCEDURE check_zone_om_collectivite_coherence();

CREATE TRIGGER before_insert_or_update_voie
    BEFORE INSERT OR UPDATE ON voie
    FOR EACH ROW EXECUTE PROCEDURE check_voie_om_collectivite_coherence();

CREATE TRIGGER before_insert_or_update_emplacement
    BEFORE INSERT OR UPDATE ON emplacement
    FOR EACH ROW EXECUTE PROCEDURE check_emplacement_om_collectivite_coherence();
