#' Andmebaasi loomine ja ühendamine
#'

source("functions/delete_existing_variables.R")

library(dplyr)
library(qgisprocess)
library(sf)

par(oma = c(0, 0, 0, 0)) # outer margin
par(mar = c(0, 0, 0, 0) + 0.0)

source("01_funktsioonid.R")

# Uue schema loomine
conf <- ruut::get_config()
conf$schema <- "xxx_artikkel_210127"
ruut::db_create_new_schema(conf = conf)
# Objektide nimekiri
objektid <- c("valga", "matsalu", "lahemaa")
# Objektide piirjooned

# ----------------- Valitud piirkond -----------------
## Piirkonna 'pk'
conn <- ruut::db_connect(conf = conf)
## Valitud objekti indeks
i <- 3

## 1. piirkonna piir
pk <- pk_piir(obj = objektid[i])
sf::st_geometry(pk) %>% plot()
## 2. piirkonna epk10t ruudud
epk10t <- pk_piir_bb(obj = objektid[i])
sf::st_geometry(epk10t) %>% plot(border = 3, lwd = 0.3, col = "#d3fffb")
sf::st_geometry(pk) %>% plot(add = T)
# 2.1 kaardiruutude nimekiri
epk10t_nr <- epk10t$nr

## 2. Kaardiruudustiku epk200t (100x100km) piirikast
conn <- ruut::db_connect()
q <- sprintf("SELECT * FROM %s.%s", "maaamet", "epk200t_bb")
cat(sprintf("\n-----------------\n%s\n\n", q))
epk200t_bb <- sf::st_read(conn, query = q)
sf::st_geometry(epk200t_bb) %>% plot()
## 3. Kaardiruudustiku epk200t (100x100km)
conn <- ruut::db_connect()
q <- sprintf("SELECT * FROM %s.%s", "maaamet", "epk200t")
cat(sprintf("\n-----------------\n%s\n\n", q))
epk200t <- sf::st_read(conn, query = q)
sf::st_geometry(epk200t) %>% plot(add = T, border = 3, lwd = 0.3, col = "#d3fffb")



## 2. boundarybox
# ----------------- Piirkonna BB leidmine -----------------
## Piirkonna 'pk' Boundary Box.
result <- ruut::pk_bbox(epk10t, type = 0)
pk_bbox <- sf::read_sf(qgisprocess::qgis_output(result, "OUTPUT"))
sf::st_geometry(pk_bbox) %>% plot(border = 2)
sf::st_geometry(epk10t) %>% plot(add = T, border = "darkblue", lwd = 0.5, lty = 1.8)

# -------------- export bbox to postgis database ---------------
conf$table <- "bbox"
ruut::copy_polygon_to_db(x = result, conf = conf, id = "fid")