-- row count 217709
CREATE TABLE edge_flat
(
  org_id CHARACTER VARYING(32) NOT NULL,
  start_id CHARACTER VARYING(50) NOT NULL,
  end_id CHARACTER VARYING(50) NOT NULL,
  start_interval INTERVAL NOT NULL,
  end_interval INTERVAL NOT NULL,
  edge_id CHARACTER VARYING(64) NOT NULL,
  edge_pos INTEGER NOT NULL,
  url_part CHARACTER VARYING(255) NOT NULL,
  created TIMESTAMP WITHOUT TIME zone,
  dsp INTEGER NOT NULL,
  asp INTEGER NOT NULL,
  da BOOLEAN NOT NULL,
  aa BOOLEAN NOT NULL,
  dr BOOLEAN,
  ar BOOLEAN,
  disabled BOOLEAN,
  edge_active BOOLEAN NOT NULL DEFAULT TRUE,
  CONSTRAINT edge_flat_pkey PRIMARY KEY (org_id , start_id , end_id , start_interval , end_interval , edge_id , edge_pos , url_part ),
  CONSTRAINT edge_flat_org_id_fk FOREIGN KEY (org_id)
      REFERENCES organisation (org_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION,
  CONSTRAINT edge_flat_end_id_fk FOREIGN KEY (end_id)
      REFERENCES node (node_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION,
  CONSTRAINT edge_flat_start_id_fk FOREIGN KEY (start_id)
      REFERENCES node (node_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION,
  CONSTRAINT edge_flat_webnode_fkey FOREIGN KEY (org_id, url_part)
      REFERENCES webnode (org_id, url_part) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

CREATE INDEX ix_atob
  ON edge_flat
  USING btree
  (start_id COLLATE pg_catalog."default" , end_id COLLATE pg_catalog."default" );

CREATE INDEX ix_edge_key
  ON edge_flat
  USING btree
  (start_id COLLATE pg_catalog."default" , end_id COLLATE pg_catalog."default" , start_interval , end_interval , org_id COLLATE pg_catalog."default" );

CREATE INDEX ix_edge_flat_edge_org_id
  ON edge_flat
  USING btree
  (org_id COLLATE pg_catalog."default" );

CREATE INDEX ix_edge_flat_edge_end_id
  ON edge_flat
  USING btree
  (end_id COLLATE pg_catalog."default" );

CREATE INDEX ix_edge_flat_edge_start_id
  ON edge_flat
  USING btree
  (start_id COLLATE pg_catalog."default" );

CREATE INDEX ix_edge_flat_edge_id_url_part
  ON edge_flat
  USING btree
  (edge_id COLLATE pg_catalog."default" , edge_pos , url_part COLLATE pg_catalog."default" );


-- row count 10452
CREATE TABLE stop
(
  node_id CHARACTER VARYING(50) NOT NULL,
  org_id CHARACTER VARYING(32) NOT NULL,
  edge_id CHARACTER VARYING(64) NOT NULL,
  description CHARACTER VARYING(522),
  stop_pos INTEGER NOT NULL,
  user_description CHARACTER VARYING(522),
  created TIMESTAMP WITHOUT TIME zone NOT NULL,
  created_by CHARACTER VARYING(255),
  edited_by CHARACTER VARYING(255),
  updated TIMESTAMP WITHOUT TIME zone NOT NULL,
  geom geometry,
  geom_approx BOOLEAN,
  CONSTRAINT stop_pkey PRIMARY KEY (node_id , org_id , edge_id , stop_pos , updated ),
  CONSTRAINT stop_org_id_fk FOREIGN KEY (org_id)
      REFERENCES organisation (org_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT stop_node_id_fk FOREIGN KEY (node_id)
      REFERENCES node (node_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

CREATE INDEX ix_stop_org_id
  ON stop
  USING btree
  (org_id COLLATE pg_catalog."default" );

CREATE INDEX ix_stop_node_id
  ON stop
  USING btree
  (node_id COLLATE pg_catalog."default" );

CREATE OR REPLACE VIEW edge_geom AS
 SELECT edge_flat.org_id, edge_flat.start_id, edge_flat.start_interval, bool_and(edge_flat.da) AS da, bool_or(edge_flat.dr) AS dr, MAX(origin_stop.user_description::text) AS origin_desc, MAX(origin_stop.geom::text) AS origin_geom, bool_or(origin_stop.geom_approx) AS origin_geom_approx, edge_flat.end_id, edge_flat.end_interval, bool_and(edge_flat.aa) AS aa, bool_or(edge_flat.ar) AS ar, MAX(dest_stop.user_description::text) AS dest_desc, MAX(dest_stop.geom::text) AS dest_geom, bool_or(dest_stop.geom_approx) AS dest_geom_approx, MAX(edge_flat.url_part::text) AS url_parts, MAX(edge_flat.url_part::text) AS url_part, MIN(edge_flat.created) AS created
   FROM edge_flat
   LEFT JOIN edited_stop_2 origin_stop ON origin_stop.node_id::text = edge_flat.start_id::text AND origin_stop.org_id::text = edge_flat.org_id::text AND origin_stop.edge_id::text = edge_flat.edge_id::text AND edge_flat.dsp = origin_stop.stop_pos
   LEFT JOIN edited_stop_2 dest_stop ON dest_stop.node_id::text = edge_flat.end_id::text AND dest_stop.org_id::text = edge_flat.org_id::text AND dest_stop.edge_id::text = edge_flat.edge_id::text AND edge_flat.asp = dest_stop.stop_pos
  WHERE edge_flat.disabled IS NULL AND edge_flat.edge_active
  GROUP BY edge_flat.org_id, edge_flat.start_id, edge_flat.end_id, edge_flat.start_interval, edge_flat.end_interval, origin_stop.user_description, dest_stop.user_description;

CREATE OR REPLACE VIEW edited_stop_2 AS
 SELECT stop.node_id, stop.org_id, stop.edge_id, stop.description, stop.geom, stop.geom_approx, stop.stop_pos, stop.user_description, stop.updated, stop.created_by AS updated_by
   FROM stop
NATURAL JOIN ( SELECT stop.node_id, stop.org_id, stop.edge_id, stop.stop_pos, MAX(stop.updated) AS updated
           FROM stop
          GROUP BY stop.node_id, stop.org_id, stop.edge_id, stop.stop_pos) recent_stop;