--UG01

--EG00

--OG01

--OG02

--OG03

--OG04

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

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

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

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

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

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

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

-- UG01
-- 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 "WSNHRN_UG01_LINE_NETWORK_ROUTING"
FROM "WSNHRN_UG01_LINE_NETWORK";

-- 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 "WSNHRN_EG00_LINE_NETWORK_ROUTING"
  FROM "WSNHRN_EG00_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 "WSNHRN_OG01_LINE_NETWORK_ROUTING"
  FROM "WSNHRN_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 "WSNHRN_OG02_LINE_NETWORK_ROUTING"
  FROM "WSNHRN_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 "WSNHRN_OG03_LINE_NETWORK_ROUTING"
  FROM "WSNHRN_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 "WSNHRN_OG04_LINE_NETWORK_ROUTING"
  FROM "WSNHRN_OG04_LINE_NETWORK";

-- fill the 3rd coordinate according to their floor number

-- UG01
UPDATE "WSNHRN_UG01_LINE_NETWORK_ROUTING" SET geom=ST_Translate(ST_Force_3Dz(geom),0,0,-1) WHERE 1=1;
UPDATE "WSNHRN_UG01_LINE_NETWORK_ROUTING" SET length =ST_Length(geom) WHERE 1=1;

--EG00
UPDATE "WSNHRN_EG00_LINE_NETWORK_ROUTING" SET geom=ST_Translate(ST_Force_3Dz(geom),0,0,0) WHERE 1=1;
UPDATE "WSNHRN_EG00_LINE_NETWORK_ROUTING" SET length =ST_Length(geom) WHERE 1=1;

--OG01
UPDATE "WSNHRN_OG01_LINE_NETWORK_ROUTING" SET geom=ST_Translate(ST_Force_3Dz(geom),0,0,1) WHERE 1=1;
UPDATE "WSNHRN_OG01_LINE_NETWORK_ROUTING" SET length =ST_Length(geom) WHERE 1=1;

--OG02
UPDATE "WSNHRN_OG02_LINE_NETWORK_ROUTING" SET geom=ST_Translate(ST_Force_3Dz(geom),0,0,2) WHERE 1=1;
UPDATE "WSNHRN_OG02_LINE_NETWORK_ROUTING" SET length =ST_Length(geom) WHERE 1=1;

--OG03
UPDATE "WSNHRN_OG03_LINE_NETWORK_ROUTING" SET geom=ST_Translate(ST_Force_3Dz(geom),0,0,3) WHERE 1=1;
UPDATE "WSNHRN_OG03_LINE_NETWORK_ROUTING" SET length =ST_Length(geom) WHERE 1=1;

--OG04
UPDATE "WSNHRN_OG04_LINE_NETWORK_ROUTING" SET geom=ST_Translate(ST_Force_3Dz(geom),0,0,4) WHERE 1=1;
UPDATE "WSNHRN_OG04_LINE_NETWORK_ROUTING" SET length =ST_Length(geom) WHERE 1=1;

-- no cost should be 0 or NULL/empty
--UG01
UPDATE "WSNHRN_UG01_LINE_NETWORK_ROUTING" SET cost=1 WHERE cost=0 or cost IS NULL;
--EG00
UPDATE "WSNHRN_EG00_LINE_NETWORK_ROUTING" SET cost=1 WHERE cost=0 or cost IS NULL;
--EG01
UPDATE "WSNHRN_OG01_LINE_NETWORK_ROUTING" SET cost=1 WHERE cost=0 or cost IS NULL;
--OG01
UPDATE "WSNHRN_OG02_LINE_NETWORK_ROUTING" SET cost=1 WHERE cost=0 or cost IS NULL;
--OG02
UPDATE "WSNHRN_OG03_LINE_NETWORK_ROUTING" SET cost=1 WHERE cost=0 or cost IS NULL;
--OG03
UPDATE "WSNHRN_OG04_LINE_NETWORK_ROUTING" SET cost=1 WHERE cost=0 or cost IS NULL;

-- update unique ids id accordingly
--UG01
UPDATE "WSNHRN_UG01_LINE_NETWORK_ROUTING" SET id=id+100000;

--EG00
UPDATE "WSNHRN_EG00_LINE_NETWORK_ROUTING" SET id=id+200000;

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

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

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

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

-- merge all networkline floors into a single table for routing
SELECT * INTO "WSNHRN_NETWORK_ROUTING" FROM
(
(SELECT id, geom, length, type_id, length*o1.cost as total_cost,
   -1 as layer FROM "WSNHRN_UG01_LINE_NETWORK_ROUTING" o1) UNION
(SELECT id, geom, length, type_id, length*o2.cost as total_cost,
   0 as layer FROM "WSNHRN_EG00_LINE_NETWORK_ROUTING" o2) UNION
(SELECT id, geom, length, type_id, length*o3.cost as total_cost,
   1 as layer FROM "WSNHRN_OG01_LINE_NETWORK_ROUTING" o3) UNION
(SELECT id, geom, length, type_id, length*o4.cost as total_cost,
   2 as layer FROM "WSNHRN_OG02_LINE_NETWORK_ROUTING" o4) UNION
(SELECT id, geom, length, type_id, length*o5.cost as total_cost,
   3 as layer FROM "WSNHRN_OG03_LINE_NETWORK_ROUTING" o5) UNION
(SELECT id, geom, length, type_id, length*o6.cost as total_cost,
   4 as layer FROM "WSNHRN_OG04_LINE_NETWORK_ROUTING" o6))
as foo ORDER BY id;

CREATE INDEX geom_gist_index3 ON "WSNHRN_NETWORK_ROUTING" USING gist (geom);
CREATE INDEX id_idx3 ON "WSNHRN_NETWORK_ROUTING" USING btree (id ASC NULLS LAST);
CREATE INDEX network_layer_idx3 ON "WSNHRN_NETWORK_ROUTING" USING hash (layer);

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

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

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

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

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

--UG01

--EG00

--OG01

--OG02

--OG03

--OG04

-- remove route nodes vertices table if exists

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

UPDATE "WSNHRN_NETWORK_ROUTING"
SET type_id = 0 where type_id IS NULL;

ALTER TABLE "WSNHRN_NETWORK_ROUTING" ADD COLUMN accessible boolean;
ALTER TABLE "WSNHRN_NETWORK_ROUTING" ADD COLUMN public boolean;

UPDATE "WSNHRN_NETWORK_ROUTING"
SET accessible = true;

UPDATE "WSNHRN_NETWORK_ROUTING"
SET public = true;

ALTER TABLE "WSNHRN_NETWORK_ROUTING" ALTER COLUMN id TYPE integer;