 
 -- create all tables in DB
 
-CREATE TABLE sc_geometry (scenario_id integer NOT NULL, id integer NOT NULL, last_update timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL, alive bool DEFAULT 'TRUE' NOT NULL, PRIMARY KEY (scenario_id, id));
-CREATE TABLE sc_geometry_history (scenario_id integer NOT NULL, id integer NOT NULL, ts_update timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL, ts_next_update timestamp, PRIMARY KEY (scenario_id, id, ts_update));
+CREATE TABLE sc_geometry (scenario_id bigint NOT NULL, id bigint NOT NULL, last_update timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL, PRIMARY KEY (scenario_id, id));
+CREATE TABLE sc_geometry_history (scenario_id bigint NOT NULL, id bigint NOT NULL, ts_update timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL, ts_prev_update timestamp, alive bool DEFAULT 'TRUE' NOT NULL, PRIMARY KEY (scenario_id, id, ts_update));
 SELECT AddGeometryColumn ('sc_geometry_history','geom',4326,'Geometry',3);
-CREATE TABLE sc_geometry_prop (scenario_id integer NOT NULL, geometry_id integer NOT NULL, name text NOT NULL, last_update timestamp DEFAULT NULL NOT NULL, alive bool DEFAULT 'TRUE' NOT NULL, PRIMARY KEY (scenario_id, geometry_id, name));
-CREATE TABLE sc_geometry_prop_history (scenario_id integer NOT NULL, geometry_id integer NOT NULL, name text NOT NULL, ts_update timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL, ts_next_update timestamp DEFAULT NULL, value text NOT NULL, PRIMARY KEY (scenario_id, geometry_id, name, ts_update));
-CREATE TABLE scenario (id SERIAL, alive bool DEFAULT 'TRUE' NOT NULL, PRIMARY KEY (id));
-CREATE TABLE scenario_prop (scenario_id integer NOT NULL, name text NOT NULL, last_update timestamp DEFAULT NULL NOT NULL, alive bool DEFAULT 'TRUE' NOT NULL, PRIMARY KEY (scenario_id, name));
-CREATE TABLE scenario_prop_history (scenario_id integer NOT NULL, name text NOT NULL, ts_update timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL, ts_next_update timestamp DEFAULT NULL, value text NOT NULL, PRIMARY KEY (scenario_id, name, ts_update));
+CREATE TABLE sc_geometry_prop (scenario_id bigint NOT NULL, geometry_id bigint NOT NULL, name text NOT NULL, last_update timestamp DEFAULT NULL NOT NULL, PRIMARY KEY (scenario_id, geometry_id, name));
+CREATE TABLE sc_geometry_prop_history (scenario_id bigint NOT NULL, geometry_id bigint NOT NULL, name text NOT NULL, ts_update timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL, ts_prev_update timestamp DEFAULT NULL, alive bool DEFAULT 'TRUE' NOT NULL, value text NOT NULL, PRIMARY KEY (scenario_id, geometry_id, name, ts_update));
+CREATE TABLE scenario (id bigserial, alive bool DEFAULT 'TRUE' NOT NULL, name text NOT NULL, PRIMARY KEY (id));
+CREATE TABLE scenario_prop (scenario_id bigint NOT NULL, name text NOT NULL, last_update timestamp DEFAULT NULL NOT NULL, PRIMARY KEY (scenario_id, name));
+CREATE TABLE scenario_prop_history (scenario_id bigint NOT NULL, name text NOT NULL, ts_update timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL, ts_prev_update timestamp DEFAULT NULL, alive bool DEFAULT 'TRUE' NOT NULL, value text NOT NULL, PRIMARY KEY (scenario_id, name, ts_update));
 CREATE INDEX sc_geometry_scenario_id ON sc_geometry (scenario_id);
 CREATE INDEX sc_geometry_id ON sc_geometry (id);
 CREATE INDEX sc_geometry_history_scenario_id ON sc_geometry_history (scenario_id);

ALTER TABLE sc_geometry_prop ADD CONSTRAINT latest_geom_prop FOREIGN KEY (scenario_id, geometry_id, name, last_update) REFERENCES sc_geometry_prop_history (scenario_id, geometry_id, name, ts_update);
ALTER TABLE sc_geometry ADD CONSTRAINT latest_geometry FOREIGN KEY (scenario_id, id, last_update) REFERENCES sc_geometry_history (scenario_id, id, ts_update);
ALTER TABLE scenario_prop ADD CONSTRAINT latest_sc_prop FOREIGN KEY (scenario_id, name, last_update) REFERENCES scenario_prop_history (scenario_id, name, ts_update);
ALTER TABLE sc_geometry_prop_history ADD CONSTRAINT next_geom_prop_version FOREIGN KEY (scenario_id, geometry_id, name, ts_next_update) REFERENCES sc_geometry_prop_history (scenario_id, geometry_id, name, ts_update);
ALTER TABLE sc_geometry_history ADD CONSTRAINT next_geometry_version FOREIGN KEY (scenario_id, id, ts_next_update) REFERENCES sc_geometry_history (scenario_id, id, ts_update);
ALTER TABLE scenario_prop_history ADD CONSTRAINT next_sc_prop_version FOREIGN KEY (scenario_id, name, ts_next_update) REFERENCES scenario_prop_history (scenario_id, name, ts_update);
ALTER TABLE sc_geometry_prop ADD CONSTRAINT sc_geometry_properties FOREIGN KEY (scenario_id, geometry_id) REFERENCES sc_geometry (scenario_id, id);
ALTER TABLE sc_geometry ADD CONSTRAINT scenario_content FOREIGN KEY (scenario_id) REFERENCES scenario (id);
ALTER TABLE scenario_prop ADD CONSTRAINT scenario_properies FOREIGN KEY (scenario_id) REFERENCES scenario (id);



-- drop all tables in DB


-- create a new scenario

CREATE OR REPLACE FUNCTION create_scenario(scName text, fc jsonb)
  RETURNS bigint AS
$func$
DECLARE
  ScID bigint;
  curTime TIMESTAMP;
  geomID_max bigint;
BEGIN
  -- get time of insertion
  curTime := CURRENT_TIMESTAMP;

  -- create a new scenario and keep its id in ScID variable
  INSERT INTO scenario (name) VALUES (scName) RETURNING scenario.id INTO ScID;

  -- insert all scenario properties
  INSERT INTO scenario_prop_history (scenario_id, name, ts_update, value)
       SELECT ScID, sprops.key, curTime, sprops.value
         FROM jsonb_each_text(jsonb_strip_nulls(fc -> 'properties') ) sprops;
  INSERT INTO scenario_prop (scenario_id, name, last_update)
       SELECT ph.scenario_id, ph.name, ph.ts_update
         FROM scenario_prop_history ph
        WHERE ph.ts_update = curTime AND ph.scenario_id = ScID;

  -- parse all features
  CREATE TEMP TABLE features ON COMMIT DROP AS
