--------------------------------------------------------------------------------
-- Script de mise à jour de la version 3.0.0-a4 vers la version 3.0.0-a5
--
-- XXX Ce script est en cours de développement, il doit être renommé en
--     v3.0.0-a5.sql au moment de la release
--
-- @package opencimetiere
-- @version SVN : $Id$
--------------------------------------------------------------------------------

--
-- Ajout de permissions manquantes
--
--INSERT INTO om_droit (om_droit, libelle, om_profil)
--SELECT nextval('om_droit_seq'), '<PERMISSION>', (SELECT om_profil FROM om_profil WHERE libelle = '<PROFIL>');
--
INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'), 'operation_defunt', (SELECT om_profil FROM om_profil WHERE libelle = 'UTILISATEUR');



---
--- Nouvelle gestion des tableaux de bord <-- openmairie_exemple 4.4.0 START
---

-- Suppression de la table om_tdb désormais inutile
DROP TABLE om_tdb CASCADE;

-- Création de la table om_dashboard
--CREATE TABLE om_dashboard (
--om_dashboard integer NOT NULL,
--om_profil integer NOT NULL,
--bloc character varying(10) NOT NULL,
--position integer,
--om_widget integer NOT NULL
--);
--ALTER TABLE ONLY om_dashboard
--    ADD CONSTRAINT om_dashboard_pkey PRIMARY KEY (om_dashboard);
--ALTER TABLE ONLY om_dashboard
--    ADD CONSTRAINT om_dashboard_om_profil_fkey FOREIGN KEY (om_profil) REFERENCES om_profil(om_profil);
--ALTER TABLE ONLY om_dashboard
--    ADD CONSTRAINT om_dashboard_om_widget_fkey FOREIGN KEY (om_widget) REFERENCES om_widget(om_widget);
--CREATE SEQUENCE om_dashboard_seq
--    START WITH 1
--    INCREMENT BY 1
--    NO MINVALUE
--    NO MAXVALUE
--    CACHE 1;
--SELECT pg_catalog.setval('om_dashboard_seq', 1, false);
--ALTER SEQUENCE om_dashboard_seq OWNED BY om_dashboard.om_dashboard;
--INSERT INTO om_droit VALUES (nextval('om_droit_seq'), 'om_dashboard',
--(select om_profil from om_profil where libelle='ADMINISTRATEUR'));

-- Modification de la table om_widget
ALTER TABLE ONLY om_widget
    ADD CONSTRAINT om_widget_libelle_om_collectivite_key UNIQUE (libelle, om_collectivite);
--ALTER TABLE om_widget
--    DROP CONSTRAINT om_widget_om_profil_fkey;
ALTER TABLE om_widget
    DROP CONSTRAINT om_widget_om_collectivite_fkey;
--ALTER TABLE om_widget DROP COLUMN om_profil;
ALTER TABLE om_widget DROP COLUMN om_collectivite;
ALTER TABLE om_widget ALTER COLUMN "libelle" TYPE character varying(100);
--ALTER TABLE om_widget ADD COLUMN "type" character varying(40) NOT NULL;
--ALTER TABLE om_widget ALTER COLUMN "lien" SET DEFAULT ''::character varying;
--ALTER TABLE om_widget ALTER COLUMN "texte" SET DEFAULT ''::text;

-- Mise à jour des om_widget pour mettre le web par défaut
UPDATE om_widget SET type='web' where type='' or type=null;

-- Le tableau de bord était déjà implémenté de cette manière dans opencimetiere donc
-- la mise à jour est différente de ce qui est fourni pas openmairie_exemple
ALTER TABLE om_widget ALTER COLUMN "type" DROP DEFAULT;

-- Mise à jour du paramétrage des widgets suite à la modification dans 
-- openmairie_exemple de la hiérarchie des tableaux de bord
INSERT INTO om_dashboard (om_dashboard, om_profil, bloc, "position", om_widget) VALUES (nextval('om_dashboard_seq'), 4, 'C1', 1, 2);
INSERT INTO om_dashboard (om_dashboard, om_profil, bloc, "position", om_widget) VALUES (nextval('om_dashboard_seq'), 4, 'C2', 1, 1);
INSERT INTO om_dashboard (om_dashboard, om_profil, bloc, "position", om_widget) VALUES (nextval('om_dashboard_seq'), 4, 'C2', 2, 3);
INSERT INTO om_dashboard (om_dashboard, om_profil, bloc, "position", om_widget) VALUES (nextval('om_dashboard_seq'), 3, 'C1', 1, 2);
INSERT INTO om_dashboard (om_dashboard, om_profil, bloc, "position", om_widget) VALUES (nextval('om_dashboard_seq'), 3, 'C2', 1, 1);
INSERT INTO om_dashboard (om_dashboard, om_profil, bloc, "position", om_widget) VALUES (nextval('om_dashboard_seq'), 3, 'C2', 2, 3);
INSERT INTO om_dashboard (om_dashboard, om_profil, bloc, "position", om_widget) VALUES (nextval('om_dashboard_seq'), 2, 'C1', 1, 2);
INSERT INTO om_dashboard (om_dashboard, om_profil, bloc, "position", om_widget) VALUES (nextval('om_dashboard_seq'), 2, 'C2', 1, 1);
INSERT INTO om_dashboard (om_dashboard, om_profil, bloc, "position", om_widget) VALUES (nextval('om_dashboard_seq'), 2, 'C2', 2, 3);
INSERT INTO om_dashboard (om_dashboard, om_profil, bloc, "position", om_widget) VALUES (nextval('om_dashboard_seq'), 2, 'C3', 1, 4);
INSERT INTO om_dashboard (om_dashboard, om_profil, bloc, "position", om_widget) VALUES (nextval('om_dashboard_seq'), 1, 'C1', 1, 2);
INSERT INTO om_dashboard (om_dashboard, om_profil, bloc, "position", om_widget) VALUES (nextval('om_dashboard_seq'), 1, 'C2', 1, 1);
INSERT INTO om_dashboard (om_dashboard, om_profil, bloc, "position", om_widget) VALUES (nextval('om_dashboard_seq'), 1, 'C2', 2, 3);
INSERT INTO om_dashboard (om_dashboard, om_profil, bloc, "position", om_widget) VALUES (nextval('om_dashboard_seq'), 1, 'C3', 1, 4);


---
--- Nouvelle gestion des tableaux de bord <-- openmairie_exemple 4.4.0 END
---

--- integrite referentielle cles secondaires 

ALTER TABLE ONLY "operation"
    ADD CONSTRAINT operation_emplacement_fkey FOREIGN KEY (emplacement) REFERENCES emplacement(emplacement);
    
ALTER TABLE ONLY "operation"
    ADD CONSTRAINT operation_emplacement_transfert_fkey FOREIGN KEY (emplacement_transfert) REFERENCES emplacement(emplacement);

ALTER TABLE ONLY operation_defunt
    ADD CONSTRAINT operation_defunt_defunt_fkey FOREIGN KEY (defunt) REFERENCES defunt(defunt);

ALTER TABLE ONLY operation_defunt
    ADD CONSTRAINT operation_defunt_operation_fkey FOREIGN KEY ("operation") REFERENCES "operation"("operation");




-- OM_Framework 4.4.0
ALTER TABLE om_utilisateur ALTER COLUMN email TYPE character varying(100);
ALTER TABLE om_parametre ALTER COLUMN valeur TYPE text;

