 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_history ADD CONSTRAINT prev_geom_prop_version FOREIGN KEY (scenario_id, geometry_id, name, ts_prev_update) REFERENCES sc_geometry_prop_history (scenario_id, geometry_id, name, ts_update);
+ALTER TABLE sc_geometry_history ADD CONSTRAINT prev_geometry_version FOREIGN KEY (scenario_id, id, ts_prev_update) REFERENCES sc_geometry_history (scenario_id, id, ts_update);
+ALTER TABLE scenario_prop_history ADD CONSTRAINT prev_sc_prop_version FOREIGN KEY (scenario_id, name, ts_prev_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

ALTER TABLE sc_geometry_prop DROP CONSTRAINT latest_geom_prop;
ALTER TABLE sc_geometry DROP CONSTRAINT latest_geometry;
ALTER TABLE scenario_prop DROP CONSTRAINT latest_sc_prop;
ALTER TABLE sc_geometry_prop_history DROP CONSTRAINT next_geom_prop_version;
ALTER TABLE sc_geometry_history DROP CONSTRAINT next_geometry_version;
ALTER TABLE scenario_prop_history DROP CONSTRAINT next_sc_prop_version;
ALTER TABLE sc_geometry_prop DROP CONSTRAINT sc_geometry_properties;
ALTER TABLE sc_geometry DROP CONSTRAINT scenario_content;
ALTER TABLE scenario_prop DROP CONSTRAINT scenario_properies;


-- create all tables in DB

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 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);
