#!/usr/bin/env bash

# Lae keskkonnamuutujad
source env.sh
# Liigume ajutisse kausta
#cd /tmp

# Laadime alla Eesti OSM andmed ajutisse kausta
#wget https://download.geofabrik.de/europe/estonia-latest.osm.pbf -P /tmp
#wget https://raw.githubusercontent.com/openstreetmap/osm2pgsql/master/default.style -O default.style

# Impordime andmed PostGIS andmebaasi
# pbf failist luuakse lähtetabelid 'osm_point', 'osm_line', 'osm_polygon', 'osm_roads'
# Ülejäänud tabelid luuakse lua failide abil.


# https://github.com/osm2pgsql-dev/osm2pgsql/blob/master/flex-config/bbox.lua
# First use the import-countries.lua to import country boundaries, 
# then use these with the other config files.

PGSCHEMA=${destination_schema} osm2pgsql \
  --create \
  --slim \
  --cache 2000 \
  --number-processes 4 \
  --database "${destination_dbname}" \
  --username "${destination_user}" \
  --host "${destination_host}" \
  --port "${destination_port}" \
  --middle-schema="${destination_schema}" \
  --output=flex \
  --style=lua/import-countries.lua \
  --prefix=osm \
  /tmp/estonia-latest.osm.pbf


psql ${CONNECTION_NAME} -c "DROP TABLE IF EXISTS ${destination_schema}.osm_pois;" && \
PGSCHEMA=${destination_schema} osm2pgsql \
  --create \
  --slim \
  --cache 2000 \
  --number-processes 4 \
  --database "${destination_dbname}" \
  --username "${destination_user}" \
  --host "${destination_host}" \
  --port "${destination_port}" \
  --middle-schema="${destination_schema}" \
  --output=flex \
  --style=lua/ardok_osm_pois.lua \
  --prefix=osm \
  /tmp/estonia-latest.osm.pbf


psql ${CONNECTION_NAME} -c "DROP TABLE IF EXISTS ${destination_schema}.ardok_highways;
DROP TABLE IF EXISTS ${destination_schema}.ardok_routes;" && \
PGSCHEMA=${destination_schema} osm2pgsql \
  --create \
  --slim \
  --cache 2000 \
  --number-processes 4 \
  --database "${destination_dbname}" \
  --username "${destination_user}" \
  --host "${destination_host}" \
  --port "${destination_port}" \
  --middle-schema="${destination_schema}" \
  --output=flex \
  --style=lua/ardok_highways.lua \
  --prefix=osm \
  /tmp/estonia-latest.osm.pbf

psql ${CONNECTION_NAME} -c "DROP TABLE IF EXISTS ${destination_schema}.ardok_buildings;" && \
PGSCHEMA=${destination_schema} osm2pgsql \
  --create \
  --slim \
  --cache 2000 \
  --number-processes 4 \
  --database "${destination_dbname}" \
  --username "${destination_user}" \
  --host "${destination_host}" \
  --port "${destination_port}" \
  --middle-schema="${destination_schema}" \
  --output=flex \
  --style=lua/ardok_buildings.lua \
  --prefix=osm \
  /tmp/estonia-latest.osm.pbf

psql ${CONNECTION_NAME} -c "DROP TABLE IF EXISTS ${destination_schema}.ardok_administrative;" && \
PGSCHEMA=${destination_schema} osm2pgsql \
  --create \
  --slim \
  --cache 2000 \
  --number-processes 4 \
  --database "${destination_dbname}" \
  --username "${destination_user}" \
  --host "${destination_host}" \
  --port "${destination_port}" \
  --middle-schema="${destination_schema}" \
  --output=flex \
  --style=lua/ardok_administrative.lua \
  --prefix=osm \
  /tmp/estonia-latest.osm.pbf

# Kaalude lisamine administratiiv polügoonidele  
psql ${CONNECTION_NAME} -c "
ALTER TABLE ${destination_schema}.ardok_administrative ADD COLUMN weight_poi integer;"
psql ${CONNECTION_NAME} -c "
UPDATE ${destination_schema}.ardok_administrative p
SET weight_poi = sub.poi_count
FROM (
  SELECT
    a.relation_id,
    COUNT(poi.*) AS poi_count
  FROM ${destination_schema}.ardok_administrative a
  JOIN ${destination_schema}.osm_pois poi
    ON ST_Intersects(a.geom, poi.geom)
  GROUP BY a.relation_id
) AS sub
WHERE p.relation_id = sub.relation_id;"

## ----------------------  GRAAFI LOOMINE  ---------------------------
# a) links loomine
psql ${CONNECTION_NAME} -v schema=${destination_schema} -d data -f sql/create_links.sql

psql ${CONNECTION_NAME} -c "
DROP TABLE IF EXISTS ${destination_schema}.links CASCADE;

CREATE TABLE ${destination_schema}.links AS
WITH
-- 1. Eemalda MultiLineStringid ja dumpi jooned üheksik-lõikudeks
dumped_ways AS (
  SELECT id AS eid, hwtype, (ST_Dump(geom)).geom AS geom
  FROM ${destination_schema}.ardok_highways
),
-- 2. Jagame iga geomeetria väiksemateks lõikudeks, et vältida topoloogia konflikte
segmented AS (
  SELECT eid, hwtype, (ST_Dump(ST_Segmentize(geom, 20))).geom AS geom
  FROM dumped_ways
),
-- 3. Igale lõigule unikaalne id
numbered AS (
  SELECT ROW_NUMBER() OVER () AS id, eid, hwtype, geom
  FROM segmented
  WHERE GeometryType(geom) = 'LINESTRING'
)
SELECT * FROM numbered;
"
# b) nodes loomine
psql ${CONNECTION_NAME} -c " 
DROP TABLE IF EXISTS ${destination_schema}.nodes CASCADE;

CREATE TABLE ${destination_schema}.nodes AS
WITH all_nodes AS (
  SELECT ST_StartPoint(geom) AS geom FROM ${destination_schema}.links
  UNION
  SELECT ST_EndPoint(geom) AS geom FROM ${destination_schema}.links
),
distinct_nodes AS (
  SELECT DISTINCT geom FROM all_nodes
)
SELECT ROW_NUMBER() OVER () AS id, geom FROM distinct_nodes;
"
psql ${CONNECTION_NAME} -c "
CREATE INDEX IF NOT EXISTS ${destination_schema}_nodes_geom_idx ON ${destination_schema}.nodes USING GIST (geom);
CREATE INDEX IF NOT EXISTS ${destination_schema}_links_geom_idx ON ${destination_schema}.links USING GIST (geom);
"
# c) links: source, target, cost
./update_links_sources_targets_costs.sh

# d) PGROUTING ettevalmistus
psql ${CONNECTION_NAME} -c "
SELECT pgr_createVerticesTable('${destination_schema}.links', 'geom', 'source', 'target');
SELECT pgr_analyzeGraph('${destination_schema}.links', 0.001, the_geom := 'geom', id := 'id', source := 'source', target := 'target');
"
# e) Indeksid ja keys
psql ${CONNECTION_NAME} -c "
ALTER TABLE ${destination_schema}.links ADD PRIMARY KEY (id);
ALTER TABLE ${destination_schema}.nodes ADD PRIMARY KEY (id);
"

psql ${CONNECTION_NAME} -v schema=${destination_schema} -d data -f sql/repair_graph_components.sql


## -----------------------------------------------------------------------------------

PGSCHEMA=${destination_schema} osm2pgsql \
  --create \
  --slim \
  --cache 2000 \
  --number-processes 4 \
  --database "${destination_dbname}" \
  --username "${destination_user}" \
  --host "${destination_host}" \
  --port "${destination_port}" \
  --middle-schema="${destination_schema}" \
  --output=flex \
  --style=lua/buildings.lua \
  --prefix=osm \
  /tmp/estonia-latest.osm.pbf

PGSCHEMA=${destination_schema} osm2pgsql \
  --create \
  --slim \
  --cache 2000 \
  --number-processes 4 \
  --database "${destination_dbname}" \
  --username "${destination_user}" \
  --host "${destination_host}" \
  --port "${destination_port}" \
  --middle-schema="${destination_schema}" \
  --output=flex \
  --style=lua/iceland.lua \
  --prefix=osm \
  /tmp/estonia-latest.osm.pbf

PGSCHEMA=${destination_schema} osm2pgsql \
  --create \
  --slim \
  --cache 2000 \
  --number-processes 4 \
  --database "${destination_dbname}" \
  --username "${destination_user}" \
  --host "${destination_host}" \
  --port "${destination_port}" \
  --middle-schema="${destination_schema}" \
  --output=flex \
  --style=lua/motorway-colours.lua \
  --prefix=osm \
  /tmp/estonia-latest.osm.pbf

PGSCHEMA=${destination_schema} osm2pgsql \
  --create \
  --slim \
  --cache 2000 \
  --number-processes 4 \
  --database "${destination_dbname}" \
  --username "${destination_user}" \
  --host "${destination_host}" \
  --port "${destination_port}" \
  --middle-schema="${destination_schema}" \
  --output=flex \
  --style=lua/forests.lua \
  --prefix=osm \
  /tmp/estonia-latest.osm.pbf

PGSCHEMA=${destination_schema} osm2pgsql \
  --create \
  --slim \
  --cache 2000 \
  --number-processes 4 \
  --database "${destination_dbname}" \
  --username "${destination_user}" \
  --host "${destination_host}" \
  --port "${destination_port}" \
  --middle-schema="${destination_schema}" \
  --output=flex \
  --style=lua/addresses.lua \
  --prefix=osm \
  /tmp/estonia-latest.osm.pbf

PGSCHEMA=${destination_schema} osm2pgsql \
  --create \
  --slim \
  --cache 2000 \
  --number-processes 4 \
  --database "${destination_dbname}" \
  --username "${destination_user}" \
  --host "${destination_host}" \
  --port "${destination_port}" \
  --middle-schema="${destination_schema}" \
  --output=flex \
  --style=lua/attributes.lua \
  --prefix=osm \
  /tmp/estonia-latest.osm.pbf

PGSCHEMA=${destination_schema} osm2pgsql \
  --create \
  --slim \
  --cache 2000 \
  --number-processes 4 \
  --database "${destination_dbname}" \
  --username "${destination_user}" \
  --host "${destination_host}" \
  --port "${destination_port}" \
  --middle-schema="${destination_schema}" \
  --output=flex \
  --style=lua/compatible.lua \
  --prefix=osm \
  /tmp/estonia-latest.osm.pbf

PGSCHEMA=${destination_schema} osm2pgsql \
  --create \
  --slim \
  --cache 2000 \
  --number-processes 4 \
  --database "${destination_dbname}" \
  --username "${destination_user}" \
  --host "${destination_host}" \
  --port "${destination_port}" \
  --middle-schema="${destination_schema}" \
  --output=flex \
  --style=lua/data-types.lua \
  --prefix=osm \
  /tmp/estonia-latest.osm.pbf

PGSCHEMA=${destination_schema} osm2pgsql \
  --create \
  --slim \
  --cache 2000 \
  --number-processes 4 \
  --database "${destination_dbname}" \
  --username "${destination_user}" \
  --host "${destination_host}" \
  --port "${destination_port}" \
  --middle-schema="${destination_schema}" \
  --output=flex \
  --style=lua/expire.lua \
  --prefix=osm \
  /tmp/estonia-latest.osm.pbf

PGSCHEMA=${destination_schema} osm2pgsql \
  --create \
  --slim \
  --cache 2000 \
  --number-processes 4 \
  --database "${destination_dbname}" \
  --username "${destination_user}" \
  --host "${destination_host}" \
  --port "${destination_port}" \
  --middle-schema="${destination_schema}" \
  --output=flex \
  --style=lua/generic.lua \
  --prefix=osm \
  /tmp/estonia-latest.osm.pbf

PGSCHEMA=${destination_schema} osm2pgsql \
  --create \
  --slim \
  --cache 2000 \
  --number-processes 4 \
  --database "${destination_dbname}" \
  --username "${destination_user}" \
  --host "${destination_host}" \
  --port "${destination_port}" \
  --middle-schema="${destination_schema}" \
  --output=flex \
  --style=lua/places.lua \
  --prefix=osm \
  /tmp/estonia-latest.osm.pbf

PGSCHEMA=${destination_schema} osm2pgsql \
  --create \
  --slim \
  --cache 2000 \
  --number-processes 4 \
  --database "${destination_dbname}" \
  --username "${destination_user}" \
  --host "${destination_host}" \
  --port "${destination_port}" \
  --middle-schema="${destination_schema}" \
  --output=flex \
  --style=lua/public-transport.lua \
  --prefix=osm \
  /tmp/estonia-latest.osm.pbf

PGSCHEMA=${destination_schema} osm2pgsql \
  --create \
  --slim \
  --cache 2000 \
  --number-processes 4 \
  --database "${destination_dbname}" \
  --username "${destination_user}" \
  --host "${destination_host}" \
  --port "${destination_port}" \
  --middle-schema="${destination_schema}" \
  --output=flex \
  --style=lua/route-relations.lua \
  --prefix=osm \
  /tmp/estonia-latest.osm.pbf

PGSCHEMA=${destination_schema} osm2pgsql \
  --create \
  --slim \
  --cache 2000 \
  --number-processes 4 \
  --database "${destination_dbname}" \
  --username "${destination_user}" \
  --host "${destination_host}" \
  --port "${destination_port}" \
  --middle-schema="${destination_schema}" \
  --output=flex \
  --style=lua/unitable.lua \
  --prefix=osm \
  /tmp/estonia-latest.osm.pbf

