-- EG00
ALTER TABLE "DIANA_EG00_LINE_NETWORK" ADD COLUMN source INTEGER;
ALTER TABLE "DIANA_EG00_LINE_NETWORK" ADD COLUMN target INTEGER;
ALTER TABLE "DIANA_EG00_LINE_NETWORK" ADD COLUMN cost DOUBLE PRECISION;
ALTER TABLE "DIANA_EG00_LINE_NETWORK" ADD COLUMN length DOUBLE PRECISION;
UPDATE "DIANA_EG00_LINE_NETWORK" set length = ST_Length(geom);
--ALTER TABLE "DIANA_EG00_LINE_NETWORK" ADD COLUMN type_id INTEGER;

-- EG01
ALTER TABLE "DIANA_EG01_LINE_NETWORK" ADD COLUMN source INTEGER;
ALTER TABLE "DIANA_EG01_LINE_NETWORK" ADD COLUMN target INTEGER;
ALTER TABLE "DIANA_EG01_LINE_NETWORK" ADD COLUMN cost DOUBLE PRECISION;
ALTER TABLE "DIANA_EG01_LINE_NETWORK" ADD COLUMN length DOUBLE PRECISION;
UPDATE "DIANA_EG01_LINE_NETWORK" set length = ST_Length(geom);
--ALTER TABLE "DIANA_EG01_LINE_NETWORK" ADD COLUMN type_id INTEGER;

-- OG01
ALTER TABLE "DIANA_OG01_LINE_NETWORK" ADD COLUMN source INTEGER;
ALTER TABLE "DIANA_OG01_LINE_NETWORK" ADD COLUMN target INTEGER;
ALTER TABLE "DIANA_OG01_LINE_NETWORK" ADD COLUMN cost DOUBLE PRECISION;
ALTER TABLE "DIANA_OG01_LINE_NETWORK" ADD COLUMN length DOUBLE PRECISION;
UPDATE "DIANA_OG01_LINE_NETWORK" set length = ST_Length(geom);
--ALTER TABLE "DIANA_OG01_LINE_NETWORK" ADD COLUMN type_id INTEGER;

-- OG02
ALTER TABLE "DIANA_OG02_LINE_NETWORK" ADD COLUMN source INTEGER;
ALTER TABLE "DIANA_OG02_LINE_NETWORK" ADD COLUMN target INTEGER;
ALTER TABLE "DIANA_OG02_LINE_NETWORK" ADD COLUMN cost DOUBLE PRECISION;
ALTER TABLE "DIANA_OG02_LINE_NETWORK" ADD COLUMN length DOUBLE PRECISION;
UPDATE "DIANA_OG02_LINE_NETWORK" set length = ST_Length(geom);
--ALTER TABLE "DIANA_OG02_LINE_NETWORK" ADD COLUMN type_id INTEGER;

-- OG03
ALTER TABLE "DIANA_OG03_LINE_NETWORK" ADD COLUMN source INTEGER;
ALTER TABLE "DIANA_OG03_LINE_NETWORK" ADD COLUMN target INTEGER;
ALTER TABLE "DIANA_OG03_LINE_NETWORK" ADD COLUMN cost DOUBLE PRECISION;
ALTER TABLE "DIANA_OG03_LINE_NETWORK" ADD COLUMN length DOUBLE PRECISION;
UPDATE "DIANA_OG03_LINE_NETWORK" set length = ST_Length(geom);
--ALTER TABLE "DIANA_OG03_LINE_NETWORK" ADD COLUMN type_id INTEGER;

-- OG04
ALTER TABLE "DIANA_OG04_LINE_NETWORK" ADD COLUMN source INTEGER;
ALTER TABLE "DIANA_OG04_LINE_NETWORK" ADD COLUMN target INTEGER;
ALTER TABLE "DIANA_OG04_LINE_NETWORK" ADD COLUMN cost DOUBLE PRECISION;
ALTER TABLE "DIANA_OG04_LINE_NETWORK" ADD COLUMN length DOUBLE PRECISION;
UPDATE "DIANA_OG04_LINE_NETWORK" set length = ST_Length(geom);
--ALTER TABLE "DIANA_OG04_LINE_NETWORK" ADD COLUMN type_id INTEGER;

-- OG05
ALTER TABLE "DIANA_OG05_LINE_NETWORK" ADD COLUMN source INTEGER;
ALTER TABLE "DIANA_OG05_LINE_NETWORK" ADD COLUMN target INTEGER;
ALTER TABLE "DIANA_OG05_LINE_NETWORK" ADD COLUMN cost DOUBLE PRECISION;
ALTER TABLE "DIANA_OG05_LINE_NETWORK" ADD COLUMN length DOUBLE PRECISION;
UPDATE "DIANA_OG05_LINE_NETWORK" set length = ST_Length(geom);
--ALTER TABLE "DIANA_OG05_LINE_NETWORK" ADD COLUMN type_id INTEGER;

-- OG06
ALTER TABLE "DIANA_OG06_LINE_NETWORK" ADD COLUMN source INTEGER;
ALTER TABLE "DIANA_OG06_LINE_NETWORK" ADD COLUMN target INTEGER;
ALTER TABLE "DIANA_OG06_LINE_NETWORK" ADD COLUMN cost DOUBLE PRECISION;
ALTER TABLE "DIANA_OG06_LINE_NETWORK" ADD COLUMN length DOUBLE PRECISION;
UPDATE "DIANA_OG06_LINE_NETWORK" set length = ST_Length(geom);
--ALTER TABLE "DIANA_OG06_LINE_NETWORK" ADD COLUMN type_id INTEGER;

-- OG07
ALTER TABLE "DIANA_OG07_LINE_NETWORK" ADD COLUMN source INTEGER;
ALTER TABLE "DIANA_OG07_LINE_NETWORK" ADD COLUMN target INTEGER;
ALTER TABLE "DIANA_OG07_LINE_NETWORK" ADD COLUMN cost DOUBLE PRECISION;
ALTER TABLE "DIANA_OG07_LINE_NETWORK" ADD COLUMN length DOUBLE PRECISION;
UPDATE "DIANA_OG07_LINE_NETWORK" set length = ST_Length(geom);
--ALTER TABLE "DIANA_OG07_LINE_NETWORK" ADD COLUMN type_id INTEGER;

-- OG08
ALTER TABLE "DIANA_OG08_LINE_NETWORK" ADD COLUMN source INTEGER;
ALTER TABLE "DIANA_OG08_LINE_NETWORK" ADD COLUMN target INTEGER;
ALTER TABLE "DIANA_OG08_LINE_NETWORK" ADD COLUMN cost DOUBLE PRECISION;
ALTER TABLE "DIANA_OG08_LINE_NETWORK" ADD COLUMN length DOUBLE PRECISION;
UPDATE "DIANA_OG08_LINE_NETWORK" set length = ST_Length(geom);
--ALTER TABLE "DIANA_OG08_LINE_NETWORK" ADD COLUMN type_id INTEGER;

-- OG09
ALTER TABLE "DIANA_OG09_LINE_NETWORK" ADD COLUMN source INTEGER;
ALTER TABLE "DIANA_OG09_LINE_NETWORK" ADD COLUMN target INTEGER;
ALTER TABLE "DIANA_OG09_LINE_NETWORK" ADD COLUMN cost DOUBLE PRECISION;
ALTER TABLE "DIANA_OG09_LINE_NETWORK" ADD COLUMN length DOUBLE PRECISION;
UPDATE "DIANA_OG09_LINE_NETWORK" set length = ST_Length(geom);
--ALTER TABLE "DIANA_OG09_LINE_NETWORK" ADD COLUMN type_id INTEGER;

-- OG010
ALTER TABLE "DIANA_OG10_LINE_NETWORK" ADD COLUMN source INTEGER;
ALTER TABLE "DIANA_OG10_LINE_NETWORK" ADD COLUMN target INTEGER;
ALTER TABLE "DIANA_OG10_LINE_NETWORK" ADD COLUMN cost DOUBLE PRECISION;
ALTER TABLE "DIANA_OG10_LINE_NETWORK" ADD COLUMN length DOUBLE PRECISION;
UPDATE "DIANA_OG10_LINE_NETWORK" set length = ST_Length(geom);
--ALTER TABLE "DIANA_OG10_LINE_NETWORK" ADD COLUMN type_id INTEGER;

-- OG11
ALTER TABLE "DIANA_OG11_LINE_NETWORK" ADD COLUMN source INTEGER;
ALTER TABLE "DIANA_OG11_LINE_NETWORK" ADD COLUMN target INTEGER;
ALTER TABLE "DIANA_OG11_LINE_NETWORK" ADD COLUMN cost DOUBLE PRECISION;
ALTER TABLE "DIANA_OG11_LINE_NETWORK" ADD COLUMN length DOUBLE PRECISION;
UPDATE "DIANA_OG11_LINE_NETWORK" set length = ST_Length(geom);
--ALTER TABLE "DIANA_OG11_LINE_NETWORK" ADD COLUMN type_id INTEGER;

-- if not, go ahead and update
-- make sure tables dont exist

-- convert to 3d coordinates with EPSG:3857

-- EG00
SELECT id, ST_Force_3d(ST_Transform(ST_Force_2D(st_geometryN(geom, 1)),3857)) AS geom,
  type_id, cost, length, 0 AS source, 0 AS target
  INTO "DIANA_EG00_LINE_NETWORK_ROUTING"
  FROM "DIANA_EG00_LINE_NETWORK";

-- EG01
SELECT id, ST_Force_3d(ST_Transform(ST_Force_2D(st_geometryN(geom, 1)),3857)) AS geom,
  type_id, cost, length, 0 AS source, 0 AS target
  INTO "DIANA_EG01_LINE_NETWORK_ROUTING"
  FROM "DIANA_EG01_LINE_NETWORK";

-- OG01
SELECT id, ST_Force_3d(ST_Transform(ST_Force_2D(st_geometryN(geom, 1)),3857)) AS geom,
  type_id, cost, length, 0 AS source, 0 AS target
  INTO "DIANA_OG01_LINE_NETWORK_ROUTING"
  FROM "DIANA_OG01_LINE_NETWORK";

-- OG02
SELECT id, ST_Force_3d(ST_Transform(ST_Force_2D(st_geometryN(geom, 1)),3857)) AS geom,
  type_id, cost, length, 0 AS source, 0 AS target
  INTO "DIANA_OG02_LINE_NETWORK_ROUTING"
  FROM "DIANA_OG02_LINE_NETWORK";

  -- OG03
SELECT id, ST_Force_3d(ST_Transform(ST_Force_2D(st_geometryN(geom, 1)),3857)) AS geom,
  type_id, cost, length, 0 AS source, 0 AS target
  INTO "DIANA_OG03_LINE_NETWORK_ROUTING"
  FROM "DIANA_OG03_LINE_NETWORK";
  -- OG04
SELECT id, ST_Force_3d(ST_Transform(ST_Force_2D(st_geometryN(geom, 1)),3857)) AS geom,
  type_id, cost, length, 0 AS source, 0 AS target
  INTO "DIANA_OG04_LINE_NETWORK_ROUTING"
  FROM "DIANA_OG04_LINE_NETWORK";
  -- OG05
SELECT id, ST_Force_3d(ST_Transform(ST_Force_2D(st_geometryN(geom, 1)),3857)) AS geom,
  type_id, cost, length, 0 AS source, 0 AS target
  INTO "DIANA_OG05_LINE_NETWORK_ROUTING"
  FROM "DIANA_OG05_LINE_NETWORK";
  -- OG06
SELECT id, ST_Force_3d(ST_Transform(ST_Force_2D(st_geometryN(geom, 1)),3857)) AS geom,
  type_id, cost, length, 0 AS source, 0 AS target
  INTO "DIANA_OG06_LINE_NETWORK_ROUTING"
  FROM "DIANA_OG06_LINE_NETWORK";
  -- OG07
SELECT id, ST_Force_3d(ST_Transform(ST_Force_2D(st_geometryN(geom, 1)),3857)) AS geom,
  type_id, cost, length, 0 AS source, 0 AS target
  INTO "DIANA_OG07_LINE_NETWORK_ROUTING"
  FROM "DIANA_OG07_LINE_NETWORK";
  -- OG08
SELECT id, ST_Force_3d(ST_Transform(ST_Force_2D(st_geometryN(geom, 1)),3857)) AS geom,
  type_id, cost, length, 0 AS source, 0 AS target
  INTO "DIANA_OG08_LINE_NETWORK_ROUTING"
  FROM "DIANA_OG08_LINE_NETWORK";
  -- OG09
SELECT id, ST_Force_3d(ST_Transform(ST_Force_2D(st_geometryN(geom, 1)),3857)) AS geom,
  type_id, cost, length, 0 AS source, 0 AS target
  INTO "DIANA_OG09_LINE_NETWORK_ROUTING"
  FROM "DIANA_OG09_LINE_NETWORK";
  -- OG10
SELECT id, ST_Force_3d(ST_Transform(ST_Force_2D(st_geometryN(geom, 1)),3857)) AS geom,
  type_id, cost, length, 0 AS source, 0 AS target
  INTO "DIANA_OG10_LINE_NETWORK_ROUTING"
  FROM "DIANA_OG10_LINE_NETWORK";
  -- OG11
SELECT id, ST_Force_3d(ST_Transform(ST_Force_2D(st_geometryN(geom, 1)),3857)) AS geom,
  type_id, cost, length, 0 AS source, 0 AS target
  INTO "DIANA_OG11_LINE_NETWORK_ROUTING"
  FROM "DIANA_OG11_LINE_NETWORK";

-- fill the 3rd coordinate according to their floor number
--EG00
UPDATE "DIANA_EG00_LINE_NETWORK_ROUTING" SET geom=ST_Translate(ST_Force_3Dz(geom),0,0,0);
UPDATE "DIANA_EG00_LINE_NETWORK_ROUTING" SET length =ST_Length(geom);

--EG01
UPDATE "DIANA_EG01_LINE_NETWORK_ROUTING" SET geom=ST_Translate(ST_Force_3Dz(geom),0,0,1);
UPDATE "DIANA_EG01_LINE_NETWORK_ROUTING" SET length =ST_Length(geom);

--OG01
UPDATE "DIANA_OG01_LINE_NETWORK_ROUTING" SET geom=ST_Translate(ST_Force_3Dz(geom),0,0,2);
UPDATE "DIANA_OG01_LINE_NETWORK_ROUTING" SET length =ST_Length(geom);

--OG02
UPDATE "DIANA_OG02_LINE_NETWORK_ROUTING" SET geom=ST_Translate(ST_Force_3Dz(geom),0,0,3);
UPDATE "DIANA_OG02_LINE_NETWORK_ROUTING" SET length =ST_Length(geom);

--OG03
UPDATE "DIANA_OG03_LINE_NETWORK_ROUTING" SET geom=ST_Translate(ST_Force_3Dz(geom),0,0,4);
UPDATE "DIANA_OG03_LINE_NETWORK_ROUTING" SET length =ST_Length(geom);

--OG04
UPDATE "DIANA_OG04_LINE_NETWORK_ROUTING" SET geom=ST_Translate(ST_Force_3Dz(geom),0,0,5);
UPDATE "DIANA_OG04_LINE_NETWORK_ROUTING" SET length =ST_Length(geom);

--OG05
UPDATE "DIANA_OG05_LINE_NETWORK_ROUTING" SET geom=ST_Translate(ST_Force_3Dz(geom),0,0,6);
UPDATE "DIANA_OG05_LINE_NETWORK_ROUTING" SET length =ST_Length(geom);

--OG06
UPDATE "DIANA_OG06_LINE_NETWORK_ROUTING" SET geom=ST_Translate(ST_Force_3Dz(geom),0,0,7);
UPDATE "DIANA_OG06_LINE_NETWORK_ROUTING" SET length =ST_Length(geom);

--OG07
UPDATE "DIANA_OG07_LINE_NETWORK_ROUTING" SET geom=ST_Translate(ST_Force_3Dz(geom),0,0,8);
UPDATE "DIANA_OG07_LINE_NETWORK_ROUTING" SET length =ST_Length(geom);

--OG08
UPDATE "DIANA_OG08_LINE_NETWORK_ROUTING" SET geom=ST_Translate(ST_Force_3Dz(geom),0,0,9);
UPDATE "DIANA_OG08_LINE_NETWORK_ROUTING" SET length =ST_Length(geom);

--OG09
UPDATE "DIANA_OG09_LINE_NETWORK_ROUTING" SET geom=ST_Translate(ST_Force_3Dz(geom),0,0,10);
UPDATE "DIANA_OG09_LINE_NETWORK_ROUTING" SET length =ST_Length(geom);

--OG010
UPDATE "DIANA_OG10_LINE_NETWORK_ROUTING" SET geom=ST_Translate(ST_Force_3Dz(geom),0,0,11);
UPDATE "DIANA_OG10_LINE_NETWORK_ROUTING" SET length =ST_Length(geom);

--OG111
UPDATE "DIANA_OG11_LINE_NETWORK_ROUTING" SET geom=ST_Translate(ST_Force_3Dz(geom),0,0,12);
UPDATE "DIANA_OG11_LINE_NETWORK_ROUTING" SET length =ST_Length(geom);

-- no cost should be 0 or NULL/empty
  --EG00
UPDATE "DIANA_EG00_LINE_NETWORK_ROUTING" SET cost=1 WHERE cost=0 or cost IS NULL;
  --EG01
UPDATE "DIANA_EG01_LINE_NETWORK_ROUTING" SET cost=1 WHERE cost=0 or cost IS NULL;
  --OG01
UPDATE "DIANA_OG01_LINE_NETWORK_ROUTING" SET cost=1 WHERE cost=0 or cost IS NULL;
  --OG02
UPDATE "DIANA_OG02_LINE_NETWORK_ROUTING" SET cost=1 WHERE cost=0 or cost IS NULL;
  --OG03
UPDATE "DIANA_OG03_LINE_NETWORK_ROUTING" SET cost=1 WHERE cost=0 or cost IS NULL;
  --OG04
UPDATE "DIANA_OG04_LINE_NETWORK_ROUTING" SET cost=1 WHERE cost=0 or cost IS NULL;
  --OG05
UPDATE "DIANA_OG05_LINE_NETWORK_ROUTING" SET cost=1 WHERE cost=0 or cost IS NULL;
  --OG06
UPDATE "DIANA_OG06_LINE_NETWORK_ROUTING" SET cost=1 WHERE cost=0 or cost IS NULL;
  --OG07
UPDATE "DIANA_OG07_LINE_NETWORK_ROUTING" SET cost=1 WHERE cost=0 or cost IS NULL;
  --OG08
UPDATE "DIANA_OG08_LINE_NETWORK_ROUTING" SET cost=1 WHERE cost=0 or cost IS NULL;
  --OG09
UPDATE "DIANA_OG09_LINE_NETWORK_ROUTING" SET cost=1 WHERE cost=0 or cost IS NULL;
  --OG10
UPDATE "DIANA_OG10_LINE_NETWORK_ROUTING" SET cost=1 WHERE cost=0 or cost IS NULL;
  --OG11
UPDATE "DIANA_OG11_LINE_NETWORK_ROUTING" SET cost=1 WHERE cost=0 or cost IS NULL;

-- update unique ids id accordingly
--EG00
UPDATE "DIANA_EG00_LINE_NETWORK_ROUTING" SET id=id+100000;

--EG01
UPDATE "DIANA_EG01_LINE_NETWORK_ROUTING" SET id=id+200000;

--OG01
UPDATE "DIANA_OG01_LINE_NETWORK_ROUTING" SET id=id+300000;

--OG02
UPDATE "DIANA_OG02_LINE_NETWORK_ROUTING" SET id=id+400000;

--OG03
UPDATE "DIANA_OG03_LINE_NETWORK_ROUTING" SET id=id+500000;

--OG04
UPDATE "DIANA_OG04_LINE_NETWORK_ROUTING" SET id=id+600000;

--OG05
UPDATE "DIANA_OG05_LINE_NETWORK_ROUTING" SET id=id+700000;

--OG06
UPDATE "DIANA_OG06_LINE_NETWORK_ROUTING" SET id=id+800000;

--OG07
UPDATE "DIANA_OG07_LINE_NETWORK_ROUTING" SET id=id+900000;

--OG08
UPDATE "DIANA_OG08_LINE_NETWORK_ROUTING" SET id=id+1000000;

--OG09
UPDATE "DIANA_OG09_LINE_NETWORK_ROUTING" SET id=id+1100000;
--OG10

UPDATE "DIANA_OG10_LINE_NETWORK_ROUTING" SET id=id+1200000;
--OG11
UPDATE "DIANA_OG11_LINE_NETWORK_ROUTING" SET id=id+1300000;

-- merge all networkline floors into a single table for routing
SELECT * INTO "DIANA_NETWORK_ROUTING" FROM
(
(SELECT id, geom, length, type_id, length*o1.cost as total_cost,
   0 as layer FROM "DIANA_EG00_LINE_NETWORK_ROUTING" o1) UNION
(SELECT id, geom, length, type_id, length*o2.cost as total_cost,
   1 as layer FROM "DIANA_EG01_LINE_NETWORK_ROUTING" o2) UNION
(SELECT id, geom, length, type_id, length*o3.cost as total_cost,
   2 as layer FROM "DIANA_OG01_LINE_NETWORK_ROUTING" o3) UNION
(SELECT id, geom, length, type_id, length*o4.cost as total_cost,
   3 as layer FROM "DIANA_OG02_LINE_NETWORK_ROUTING" o4) UNION
(SELECT id, geom, length, type_id, length*o5.cost as total_cost,
   4 as layer FROM "DIANA_OG03_LINE_NETWORK_ROUTING" o5) UNION
(SELECT id, geom, length, type_id, length*o6.cost as total_cost,
   5 as layer FROM "DIANA_OG04_LINE_NETWORK_ROUTING" o6) UNION
(SELECT id, geom, length, type_id, length*o7.cost as total_cost,
   6 as layer FROM "DIANA_OG05_LINE_NETWORK_ROUTING" o7) UNION
(SELECT id, geom, length, type_id, length*o8.cost as total_cost,
   7 as layer FROM "DIANA_OG06_LINE_NETWORK_ROUTING" o8) UNION
(SELECT id, geom, length, type_id, length*o9.cost as total_cost,
   8 as layer FROM "DIANA_OG07_LINE_NETWORK_ROUTING" o9) UNION
(SELECT id, geom, length, type_id, length*o10.cost as total_cost,
   9 as layer FROM "DIANA_OG08_LINE_NETWORK_ROUTING" o10) UNION
(SELECT id, geom, length, type_id, length*o11.cost as total_cost,
   10 as layer FROM "DIANA_OG09_LINE_NETWORK_ROUTING" o11) UNION
(SELECT id, geom, length, type_id, length*o12.cost as total_cost,
   11 as layer FROM "DIANA_OG10_LINE_NETWORK_ROUTING" o12) UNION
(SELECT id, geom, length, type_id, length*o13.cost as total_cost,
   12 as layer FROM "DIANA_OG11_LINE_NETWORK_ROUTING" o13))
as foo ORDER BY id;
CREATE INDEX geom_gist_index
   ON "DIANA_NETWORK_ROUTING" USING gist (geom);
CREATE INDEX id_idx
   ON "DIANA_NETWORK_ROUTING" USING btree (id ASC NULLS LAST);
CREATE INDEX network_layer_idx
  ON "DIANA_NETWORK_ROUTING"
  USING hash
  (layer);

-- create populate geometry view with info
SELECT Populate_geometry_Columns('"DIANA_NETWORK_ROUTING"'::regclass);

-- update stairs, ramps and elevators to match with the next layer
UPDATE "DIANA_NETWORK_ROUTING" SET geom=ST_AddPoint(geom,
  ST_EndPoint(ST_Translate(geom,0,0,1)))
  WHERE type_id=3 OR type_id=5 OR type_id=7;

-- remove the second last point
UPDATE "DIANA_NETWORK_ROUTING" SET geom=ST_RemovePoint(geom,ST_NPoints(geom) - 2)
  WHERE type_id=3 OR type_id=5 OR type_id=7;

-- add columns source and target
ALTER TABLE "DIANA_NETWORK_ROUTING" add column source integer;
ALTER TABLE "DIANA_NETWORK_ROUTING" add column target integer;

-- we dont need the temporary tables any more, delete them
--EG00

--EG01

--OG01

--OG02

--OG03

--OG04

--OG05

--OG06

--OG07

--OG08

--OG09

--OG10

--OG11

-- remove route nodes vertices table if exists

-- building routing network vertices (fills source and target columns in those new tables)
SELECT public.pgr_createTopology3d('DIANA_NETWORK_ROUTING', 0.0001, 'geom', 'id');

