--
-- BEGIN - [#10810] -  Mise en place de la gestion multi-commune
--
ALTER TABLE emplacement ADD COLUMN IF NOT EXISTS om_collectivite INTEGER;
UPDATE emplacement SET om_collectivite = 1 WHERE om_collectivite IS NULL;
ALTER TABLE emplacement ALTER COLUMN om_collectivite SET NOT NULL;
ALTER TABLE ONLY emplacement
    DROP CONSTRAINT IF EXISTS emplacement_om_collectivite_fkey,
    ADD CONSTRAINT emplacement_om_collectivite_fkey FOREIGN KEY (om_collectivite) REFERENCES om_collectivite(om_collectivite);

ALTER TABLE emplacement_archive ADD COLUMN IF NOT EXISTS om_collectivite INTEGER;
UPDATE emplacement_archive SET om_collectivite = 1 WHERE om_collectivite IS NULL;
ALTER TABLE emplacement_archive ALTER COLUMN om_collectivite SET NOT NULL;
ALTER TABLE ONLY emplacement_archive
    DROP CONSTRAINT IF EXISTS emplacement_archive_om_collectivite_fkey,
    ADD CONSTRAINT emplacement_archive_om_collectivite_fkey FOREIGN KEY (om_collectivite) REFERENCES om_collectivite(om_collectivite);
--
ALTER TABLE cimetiere ADD COLUMN IF NOT EXISTS om_collectivite INTEGER;
UPDATE cimetiere SET om_collectivite = 1 WHERE om_collectivite IS NULL;
ALTER TABLE cimetiere ALTER COLUMN om_collectivite SET NOT NULL;
ALTER TABLE ONLY cimetiere
    DROP CONSTRAINT IF EXISTS cimetiere_om_collectivite_fkey,
    ADD CONSTRAINT cimetiere_om_collectivite_fkey FOREIGN KEY (om_collectivite) REFERENCES om_collectivite(om_collectivite);
--
ALTER TABLE zone ADD COLUMN IF NOT EXISTS om_collectivite INTEGER;
UPDATE zone SET om_collectivite = 1 WHERE om_collectivite IS NULL;
ALTER TABLE zone ALTER COLUMN om_collectivite SET NOT NULL;
ALTER TABLE ONLY zone
    DROP CONSTRAINT IF EXISTS zone_om_collectivite_fkey,
    ADD CONSTRAINT zone_om_collectivite_fkey FOREIGN KEY (om_collectivite) REFERENCES om_collectivite(om_collectivite);
--
ALTER TABLE voie ADD COLUMN IF NOT EXISTS om_collectivite INTEGER;
UPDATE voie SET om_collectivite = 1 WHERE om_collectivite IS NULL;
ALTER TABLE voie ALTER COLUMN om_collectivite SET NOT NULL;
ALTER TABLE ONLY voie
    DROP CONSTRAINT IF EXISTS voie_om_collectivite_fkey,
    ADD CONSTRAINT voie_om_collectivite_fkey FOREIGN KEY (om_collectivite) REFERENCES om_collectivite(om_collectivite);
--
ALTER TABLE entreprise ADD COLUMN IF NOT EXISTS om_collectivite INTEGER;
UPDATE entreprise SET om_collectivite = 1 WHERE om_collectivite IS NULL;
ALTER TABLE entreprise ALTER COLUMN om_collectivite SET NOT NULL;
ALTER TABLE ONLY entreprise
    DROP CONSTRAINT IF EXISTS entreprise_om_collectivite_fkey,
    ADD CONSTRAINT entreprise_om_collectivite_fkey FOREIGN KEY (om_collectivite) REFERENCES om_collectivite(om_collectivite);
--
ALTER TABLE tarif ADD COLUMN IF NOT EXISTS om_collectivite INTEGER;
UPDATE tarif SET om_collectivite = 1 WHERE om_collectivite IS NULL;
ALTER TABLE tarif ALTER COLUMN om_collectivite SET NOT NULL;
ALTER TABLE ONLY tarif
    DROP CONSTRAINT IF EXISTS tarif_om_collectivite_fkey,
    ADD CONSTRAINT tarif_om_collectivite_fkey FOREIGN KEY (om_collectivite) REFERENCES om_collectivite(om_collectivite);
--
-- Modification de la contrainte d'unicité pour inclure om_collectivite
-- Cela permet à chaque collectivité d'avoir ses propres tarifs
DROP INDEX IF EXISTS tarif_unique_index_key;
ALTER TABLE tarif DROP CONSTRAINT IF EXISTS tarif_unique_key;
ALTER TABLE tarif 
    ADD CONSTRAINT tarif_unique_key UNIQUE (annee, origine, terme, duree, nature, sepulture_type, om_collectivite);
CREATE UNIQUE INDEX tarif_unique_index_key 
    ON tarif (annee, origine, terme, duree, nature, COALESCE(sepulture_type, '-1'::integer), om_collectivite);


CREATE OR REPLACE FUNCTION check_emplacement_om_collectivite_coherence() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_om_collectivite_voie integer;
    v_om_collectivite_zone integer;
    v_om_collectivite_cimetiere integer;
BEGIN
    EXECUTE format('SELECT v.om_collectivite FROM %I.voie v WHERE v.voie = $1', TG_TABLE_SCHEMA)
        INTO v_om_collectivite_voie USING NEW.voie;
    
    EXECUTE format('SELECT z.om_collectivite FROM %I.zone z INNER JOIN %I.voie v ON v.zone = z.zone WHERE v.voie = $1', TG_TABLE_SCHEMA, TG_TABLE_SCHEMA)
        INTO v_om_collectivite_zone USING NEW.voie;
    
    EXECUTE format('SELECT c.om_collectivite FROM %I.cimetiere c INNER JOIN %I.zone z ON z.cimetiere = c.cimetiere INNER JOIN %I.voie v ON v.zone = z.zone WHERE v.voie = $1', TG_TABLE_SCHEMA, TG_TABLE_SCHEMA, TG_TABLE_SCHEMA)
        INTO v_om_collectivite_cimetiere USING NEW.voie;
    
    IF NEW.om_collectivite != v_om_collectivite_voie
        AND NEW.om_collectivite != v_om_collectivite_zone
        AND NEW.om_collectivite != v_om_collectivite_cimetiere 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;
$$;


--
-- Name: check_voie_om_collectivite_coherence(); Type: FUNCTION; Schema: opencimetiere; Owner: -
--

CREATE OR REPLACE FUNCTION check_voie_om_collectivite_coherence() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_om_collectivite_zone integer;
    v_om_collectivite_cimetiere integer;
BEGIN
    EXECUTE format('SELECT z.om_collectivite FROM %I.zone z WHERE z.zone = $1', TG_TABLE_SCHEMA)
        INTO v_om_collectivite_zone USING NEW.zone;
    
    EXECUTE format('SELECT c.om_collectivite FROM %I.cimetiere c INNER JOIN %I.zone z ON z.cimetiere = c.cimetiere WHERE z.zone = $1', TG_TABLE_SCHEMA, TG_TABLE_SCHEMA)
        INTO v_om_collectivite_cimetiere USING NEW.zone;
    
    IF NEW.om_collectivite != v_om_collectivite_zone
        AND NEW.om_collectivite != v_om_collectivite_cimetiere THEN
        RAISE EXCEPTION 'La voie doit posséder la même om_collectivite que sa zone.';
    END IF;
    
    RETURN NEW;
END;
$$;


--
-- Name: check_zone_om_collectivite_coherence(); Type: FUNCTION; Schema: opencimetiere; Owner: -
--

CREATE OR REPLACE FUNCTION check_zone_om_collectivite_coherence() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_om_collectivite_cimetiere integer;
BEGIN
    EXECUTE format('SELECT om_collectivite FROM %I.cimetiere c WHERE c.cimetiere = $1', TG_TABLE_SCHEMA)
        INTO v_om_collectivite_cimetiere USING NEW.cimetiere;
    
    IF NEW.om_collectivite != v_om_collectivite_cimetiere THEN
        RAISE EXCEPTION 'La zone doit posséder la même om_collectivite que son cimetière.';
    END IF;
    
    RETURN NEW;
END;
$$;


DROP TRIGGER IF EXISTS before_insert_or_update_zone ON zone;
DROP TRIGGER IF EXISTS before_insert_or_update_voie ON voie;
DROP TRIGGER IF EXISTS before_insert_or_update_emplacement ON emplacement;

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();
--
ALTER TABLE plans ADD COLUMN IF NOT EXISTS om_collectivite INTEGER;
UPDATE plans SET om_collectivite = 1 WHERE om_collectivite IS NULL;
ALTER TABLE plans ALTER COLUMN om_collectivite SET NOT NULL;
ALTER TABLE ONLY plans
    DROP CONSTRAINT IF EXISTS plans_om_collectivite_fkey,
    ADD CONSTRAINT plans_om_collectivite_fkey FOREIGN KEY (om_collectivite) REFERENCES om_collectivite(om_collectivite);
--
ALTER TABLE habillage ADD COLUMN IF NOT EXISTS om_collectivite INTEGER;
UPDATE habillage SET om_collectivite = 1 WHERE om_collectivite IS NULL;
ALTER TABLE habillage ALTER COLUMN om_collectivite SET NOT NULL;
ALTER TABLE ONLY habillage
    DROP CONSTRAINT IF EXISTS habillage_om_collectivite_fkey,
    ADD CONSTRAINT habillage_om_collectivite_fkey FOREIGN KEY (om_collectivite) REFERENCES om_collectivite(om_collectivite);

--
-- END - [#10810] -  Mise en place de la gestion multi-commune
--