--
-- START /
-- passage version 4.4.5 du sig interne
-- Mars 2015
--

ALTER TABLE om_sig_map ADD COLUMN champ_idx character varying(30);
ALTER TABLE om_sig_map ADD COLUMN util_idx boolean;
ALTER TABLE om_sig_map ADD COLUMN util_reqmo boolean;
ALTER TABLE om_sig_map ADD COLUMN util_recherche boolean;
ALTER TABLE om_sig_map ADD COLUMN source_flux integer;
ALTER TABLE om_sig_map ADD COLUMN fond_default character varying(10);

ALTER TABLE om_sig_map_comp ADD COLUMN comp_champ_idx character varying(30);

UPDATE om_sig_map_comp SET comp_champ_idx = id from om_sig_map where om_sig_map_comp.om_sig_map_comp = om_sig_map.om_sig_map;

UPDATE om_sig_map SET champ_idx = id, util_idx= true, util_reqmo=false, util_recherche = false;
UPDATE om_sig_map SET fond_default='osm' WHERE fond_default IS NULL AND fond_osm = 'Oui';
UPDATE om_sig_map SET fond_default='Bing' WHERE fond_default IS NULL AND fond_bing = 'Oui';
UPDATE om_sig_map SET fond_default='Google' WHERE fond_default IS NULL AND fond_sat = 'Oui';
UPDATE om_sig_map SET fond_default=a.wms::text FROM (SELECT om_sig_map, min(om_sig_map_wms) as wms FROM om_sig_map_wms where baselayer = 'Oui' group by om_sig_map) a where a.om_sig_map=om_sig_map.om_sig_map;


ALTER TABLE om_sig_map ALTER COLUMN champ_idx SET NOT NULL;
ALTER TABLE om_sig_map ALTER COLUMN fond_default SET NOT NULL;
ALTER TABLE om_sig_map ALTER COLUMN fond_osm DROP NOT NULL;
ALTER TABLE om_sig_map ALTER COLUMN fond_bing DROP NOT NULL;
ALTER TABLE om_sig_map ALTER COLUMN fond_sat DROP NOT NULL;
ALTER TABLE om_sig_map ALTER COLUMN layer_info DROP NOT NULL;
ALTER TABLE om_sig_map ALTER COLUMN maj DROP NOT NULL;

ALTER TABLE ONLY om_sig_map
    ADD CONSTRAINT om_sig_map_om_sig_map_fkey FOREIGN KEY (source_flux) REFERENCES om_sig_map(om_sig_map);


ALTER TABLE om_sig_map ALTER COLUMN fond_osm TYPE boolean USING CASE WHEN fond_osm='Oui' THEN true ELSE false END;
ALTER TABLE om_sig_map ALTER COLUMN fond_bing TYPE boolean USING CASE WHEN fond_bing='Oui' THEN true ELSE false END;
ALTER TABLE om_sig_map ALTER COLUMN fond_sat TYPE boolean USING CASE WHEN fond_sat='Oui' THEN true ELSE false END;
ALTER TABLE om_sig_map ALTER COLUMN layer_info TYPE boolean USING CASE WHEN layer_info='Oui' THEN true ELSE false END;

ALTER TABLE om_sig_map_comp ALTER COLUMN actif TYPE boolean USING CASE WHEN actif='Oui' THEN true ELSE false END;


UPDATE om_sig_map_comp SET ordre = ordre +1;
INSERT INTO om_sig_map_comp(om_sig_map_comp, om_sig_map, libelle, ordre, actif, comp_maj, comp_table_update, comp_champ_idx, comp_champ, type_geometrie)
SELECT nextval('om_sig_map_comp_seq'), om_sig_map, lib_geometrie, 0, actif, maj, table_update, champ_idx, champ, type_geometrie
FROM om_sig_map;
ALTER TABLE om_sig_map DROP COLUMN lib_geometrie;
ALTER TABLE om_sig_map DROP COLUMN maj;
ALTER TABLE om_sig_map DROP COLUMN table_update;
ALTER TABLE om_sig_map DROP COLUMN champ_idx;
ALTER TABLE om_sig_map DROP COLUMN champ;
ALTER TABLE om_sig_map DROP COLUMN type_geometrie;

CREATE TABLE om_sig_extent (
    om_sig_extent integer NOT NULL,
    nom character varying(150),
    extent character varying(150),
    valide boolean
);

CREATE SEQUENCE om_sig_extent_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
CREATE INDEX om_sig_extent_nom_idx
  ON om_sig_extent
  (nom );

ALTER TABLE ONLY om_sig_extent
    ADD CONSTRAINT om_sig_extent_pkey PRIMARY KEY (om_sig_extent);

ALTER SEQUENCE om_sig_extent_seq OWNED BY om_sig_extent.om_sig_extent;

INSERT INTO om_droit (om_droit, libelle, om_profil) SELECT nextval('om_droit_seq'), 'om_sig_extent', om_profil FROM om_droit WHERE libelle = 'om_sig_map';

\i init_sig_extent.sql
\set schema '\'openads\''
\i update_sequences.sql

INSERT INTO om_sig_extent
SELECT nextval('om_sig_extent_seq'), 'USER: '||id, etendue
FROM (select min(om_sig_map) as id, etendue from om_sig_map group by etendue) et_utilise
WHERE etendue NOT IN (SELECT extent FROM om_sig_extent);

ALTER TABLE om_sig_map ADD COLUMN om_sig_extent integer;
UPDATE om_sig_map  SET om_sig_extent=o.om_sig_extent FROM om_sig_extent o where o.extent=etendue;
ALTER TABLE om_sig_map ALTER COLUMN om_sig_extent SET NOT NULL;
ALTER TABLE om_sig_map DROP COLUMN etendue;
ALTER TABLE ONLY om_sig_map
    ADD CONSTRAINT om_sig_map_om_sig_extent_fkey FOREIGN KEY (om_sig_extent) REFERENCES om_sig_extent(om_sig_extent);
ALTER TABLE om_sig_map ADD COLUMN restrict_extent boolean;
UPDATE om_sig_map SET restrict_extent=true;

ALTER TABLE om_sig_map ADD COLUMN sld_marqueur character varying(254);
ALTER TABLE om_sig_map ADD COLUMN sld_data character varying(254);
ALTER TABLE om_sig_map ADD COLUMN point_centrage geometry(Point,2154);
    
ALTER TABLE om_sig_map_comp ADD COLUMN obj_class character varying(100);
UPDATE om_sig_map_comp SET obj_class= o.id FROM om_sig_map o WHERE o.om_sig_map=om_sig_map_comp.om_sig_map;
ALTER TABLE om_sig_map_comp ALTER COLUMN obj_class SET NOT NULL;
ALTER TABLE om_sig_map_comp ALTER COLUMN comp_maj TYPE boolean USING CASE WHEN comp_maj='Oui' THEN true ELSE false END;


CREATE TABLE om_sig_flux (
    om_sig_flux integer NOT NULL,
    libelle character varying(50) NOT NULL,
    om_collectivite integer NOT NULL,
    id character varying(50) NOT NULL,
    attribution character varying(150),
    chemin character varying(255) NOT NULL,
    couches character varying(255) NOT NULL,
    cache_type character varying(3),
    cache_gfi_chemin character varying(255),
    cache_gfi_couches character varying(255)
);
CREATE SEQUENCE om_sig_flux_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER SEQUENCE om_sig_flux_seq OWNED BY om_sig_flux.om_sig_flux;

ALTER TABLE om_sig_map_wms ALTER COLUMN visibility TYPE boolean USING CASE WHEN visibility='Oui' THEN true ELSE false END;
ALTER TABLE om_sig_map_wms ALTER COLUMN panier TYPE boolean USING CASE WHEN panier='Oui' THEN true ELSE false END;
ALTER TABLE om_sig_map_wms ALTER COLUMN baselayer TYPE boolean USING CASE WHEN baselayer='Oui' THEN true ELSE false END;
ALTER TABLE om_sig_map_wms ALTER COLUMN singletile TYPE boolean USING CASE WHEN singletile='Oui' THEN true ELSE false END;

CREATE TABLE om_sig_map_flux (
    om_sig_map_flux integer NOT NULL,
    om_sig_flux integer NOT NULL,
    om_sig_map integer NOT NULL,
    ol_map character varying(50) NOT NULL,
    ordre integer NOT NULL,
    visibility boolean,
    panier boolean,
    pa_nom character varying(50),
    pa_layer character varying(50),
    pa_attribut character varying(50),
    pa_encaps character varying(3),
    pa_sql text,
    pa_type_geometrie character varying(30),
    sql_filter text,
    baselayer boolean,
    singletile boolean,
    maxzoomlevel integer
);
CREATE SEQUENCE om_sig_map_flux_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER SEQUENCE om_sig_map_flux_seq OWNED BY om_sig_map_flux.om_sig_map_flux;

ALTER TABLE ONLY om_sig_flux
    ADD CONSTRAINT om_sig_flux_pkey PRIMARY KEY (om_sig_flux);

ALTER TABLE ONLY om_sig_map_flux
    ADD CONSTRAINT om_sig_map_flux_pkey PRIMARY KEY (om_sig_map_flux);

ALTER TABLE ONLY om_sig_flux
    ADD CONSTRAINT om_sig_flux_om_collectivite_fkey FOREIGN KEY (om_collectivite) REFERENCES om_collectivite(om_collectivite);

ALTER TABLE ONLY om_sig_map_flux
    ADD CONSTRAINT om_sig_map_flux_om_sig_map_fkey FOREIGN KEY (om_sig_map) REFERENCES om_sig_map(om_sig_map);

ALTER TABLE ONLY om_sig_map_flux
    ADD CONSTRAINT om_sig_map_flux_om_sig_flux_fkey FOREIGN KEY (om_sig_flux) REFERENCES om_sig_flux(om_sig_flux);

ALTER TABLE om_sig_wms ADD COLUMN attribution character varying(150);
UPDATE om_sig_wms SET attribution = libelle;

INSERT INTO om_sig_flux(
    om_sig_flux, libelle, om_collectivite, id, attribution, cache_type, chemin, 
    couches, cache_gfi_chemin, cache_gfi_couches)
SELECT om_sig_wms, libelle, om_collectivite, id, attribution, cache_type, chemin, 
    couches, cache_gfi_chemin, cache_gfi_couches 
FROM om_sig_wms;

INSERT INTO om_sig_map_flux SELECT * FROM om_sig_map_wms;

SELECT setval('om_sig_flux_seq',(SELECT max(om_sig_flux) FROM om_sig_flux));
SELECT setval('om_sig_map_flux_seq',(SELECT max(om_sig_map_flux) FROM om_sig_map_flux));

DROP SEQUENCE om_sig_map_wms_seq;
DROP TABLE om_sig_map_wms;

DROP SEQUENCE om_sig_wms_seq;
DROP TABLE om_sig_wms;
UPDATE om_droit SET libelle = replace(libelle,'om_sig_wms','om_sig_flux') WHERE libelle LIKE 'om_sig_wms%';
UPDATE om_droit SET libelle = replace(libelle,'om_sig_map_wms','om_sig_map_flux') WHERE libelle LIKE 'om_sig_map_wms%';

--
-- END /
-- passage version 4.4.5 du sig interne
-- Mars 2015
--