--------------------------------------------------------------------------------
-- Script de mise à jour vers la version v3.3.0-dev
--
-- @package openfoncier
-- @version SVN : $Id$
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-- OPENMAIRIE-EXEMPLE v4.4.0
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-- bugs visibility dans om_sig_map

ALTER TABLE om_sig_map_wms ALTER visibility DROP NOT NULL;

--------------------------------------------------------------------------------
-- GESTION DES REQUETES POUR LES ETATS ET LETTRES TYPE
--------------------------------------------------------------------------------
-- Création de la table
CREATE TABLE om_requete(
    id integer NOT NULL,
    code character varying(50) NOT NULL,
    libelle character varying(100) NOT NULL,
    description character varying(200),
    requete text,
    merge_fields text
);
-- Création de la séquence
CREATE SEQUENCE om_requete_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
-- Clé primaire
ALTER TABLE ONLY om_requete
    ADD CONSTRAINT om_requete_pkey PRIMARY KEY (id);
-- Récupération de toutes les requêtes existantes dans les tables om_etat et
-- om_lettretype
insert into om_requete (id, code, libelle, requete)
(
select nextval('om_requete_seq'), '-', 'Requête SQL', lib1
from (select om_sql as lib1, om_sql
      from om_lettretype group by om_sql
      union
      select om_sql as lib1, om_sql
      from om_etat group by om_sql
      order by lib1) as sql1
group by lib1
);
update om_etat set om_sql=(select om_requete.id from om_requete where om_requete.requete=om_etat.om_sql);
update om_lettretype set om_sql=(select om_requete.id from om_requete where om_requete.requete=om_lettretype.om_sql);
alter table om_etat alter column om_sql TYPE integer USING om_sql::integer;
alter table om_lettretype alter column om_sql TYPE integer USING om_sql::integer;
-- Clés étrangères
ALTER TABLE ONLY om_etat
    ADD CONSTRAINT om_etat_om_requete_fkey FOREIGN KEY (om_sql) REFERENCES om_requete(id);
ALTER TABLE ONLY om_lettretype
    ADD CONSTRAINT om_lettretype_om_requete_fkey FOREIGN KEY (om_sql) REFERENCES om_requete(id);
-- Permission
INSERT INTO om_droit VALUES (nextval('om_droit_seq'), 'om_requete',
(select om_profil from om_profil where libelle='ADMINISTRATEUR TECHNIQUE'));
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
-- GESTION DES LOGOS POUR LES ETATS ET LETTRES TYPE
--------------------------------------------------------------------------------
-- Création de la table
CREATE TABLE om_logo (
    om_logo integer,
    id character varying(50) NOT NULL,
    libelle character varying(100) NOT NULL,
    description character varying(200),
    fichier character varying(100) NOT NULL,
    resolution integer,
    actif boolean,
    om_collectivite integer NOT NULL
);
-- Création de la séquence
CREATE SEQUENCE om_logo_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
-- Clé primaire
ALTER TABLE ONLY om_logo
    ADD CONSTRAINT om_logo_pkey PRIMARY KEY (om_logo);
-- Clé étrangère
ALTER TABLE ONLY om_logo
    ADD CONSTRAINT om_logo_om_collectivite_fkey FOREIGN KEY (om_collectivite) REFERENCES om_collectivite(om_collectivite);
-- Récupération de toutes les requêtes existantes dans les tables om_etat et
-- om_lettretype
insert into om_logo (om_logo, id, libelle, fichier, actif, om_collectivite)
(
select nextval('om_logo_seq'), lib1, lib1, lib1, true, 1
from (select logo as lib1, logo
      from om_lettretype group by logo
      union
      select logo as lib1, logo
      from om_etat group by logo
      order by lib1) as sql1
group by lib1
);
--update om_etat set logo=(select om_logo.id from om_logo where om_logo.fichier=om_etat.logo);
--update om_lettretype set logo=(select om_logo.id from om_logo where om_logo.fichier=om_lettretype.logo);
--alter table om_etat alter column logo TYPE integer USING logo::integer;
--alter table om_lettretype alter column logo TYPE integer USING logo::integer;
-- Clés étrangères
--ALTER TABLE ONLY om_etat
--    ADD CONSTRAINT om_etat_om_logo_fkey FOREIGN KEY (logo) REFERENCES om_logo(id);
--ALTER TABLE ONLY om_lettretype
--    ADD CONSTRAINT om_lettretype_om_logo_fkey FOREIGN KEY (logo) REFERENCES om_logo(id);
-- Permission
INSERT INTO om_droit VALUES (nextval('om_droit_seq'), 'om_logo',
(select om_profil from om_profil where libelle='ADMINISTRATEUR TECHNIQUE'));
--------------------------------------------------------------------------------
alter table om_etat drop column footerfont;
alter table om_etat drop column footerattribut;
alter table om_etat drop column footertaille;
alter table om_etat alter column sousetat drop not null;
alter table om_etat alter column logo drop not null;
alter table om_lettretype alter column logo drop not null;
ALTER TABLE om_etat ALTER COLUMN libelle TYPE character varying(100);
ALTER TABLE om_sousetat ALTER COLUMN libelle TYPE character varying(100);
ALTER TABLE om_lettretype ALTER COLUMN libelle TYPE character varying(100);


--------------------------------------------------------------------------------




ALTER TABLE bible ADD CONSTRAINT
    bible_dossier_autorisation_type_fkey FOREIGN KEY (dossier_autorisation_type) REFERENCES dossier_autorisation_type (dossier_autorisation_type);


