#' 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")

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

for (i in 1:length(objektid)) {
  ## 1. piirkonna piir
  obj <- objektid[i]
  pk <- pk_piir(obj = obj)
  sf::st_geometry(pk) %>% plot()
  gpkg_home <- "/data/gpkg/artiklid/artikkel_210127_valga_matsalu_lahemaa"
  dsn <- sprintf("%s/%s.gpkg", gpkg_home, obj)
  input_layer_name <- "piir"
  input_layer <- sprintf("%s|layername=%s", dsn, input_layer_name)
  output_layer_name <- "boundarybox_3301"
  tmp_gpkg_file <- tempfile(fileext = ".gpkg")
  # write to gpkg
  class(pk)
  sf::write_sf(pk,
    dsn = dsn,
    layer = "piir", driver = "gpkg", append = FALSE )
  # layer_options = c("OVERWRITE=true", "append=FALSE")
  # ruut::construct_qgis_output_result_to_beter_format()

  ## 2. piirkonna 3301 projektsiooniga piirikast
  ## 2.1  Esmalt leiame milliset 500x500 meetri ruutudega on objekt kaetud.
  # ruut::qgis_algorithm_search_by_word(str = "grid")
  algorithm <- "native:creategrid"
  # cat(qgisprocess::qgis_show_help(algorithm = algorithm))
  result <- qgisprocess::qgis_run_algorithm(
    algorithm = algorithm,
    CRS = "EPSG:3301",
    # EXTENT = '25.454305528,26.259893095,59.454118579,59.714621582 [EPSG:4326]',
    EXTENT = input_layer,
    HOVERLAY = 0,
    HSPACING = 500,
    TYPE = 2,
    VOVERLAY = 0,
    VSPACING = 500,
    OUTPUT = qgisprocess::qgis_tmp_file(ext = ".gpkg"),
    .quiet = TRUE
  )
  result
  pk_grid <- sf::read_sf(qgisprocess::qgis_output(result, "OUTPUT"))
  # sf::st_geometry(pk_grid) %>% plot()
  ## 2.2 Leitud ruutudele leiame piirikasti.
  algorithm <- "qgis:minimumboundinggeometry"
  # cat(qgisprocess::qgis_show_help(algorithm = algorithm))
  result1 <- qgisprocess::qgis_run_algorithm(
    algorithm = algorithm,
    FIELD = "",
    INPUT = pk_grid,
    TYPE = 3,
    OUTPUT = tmp_gpkg_file,
    .quiet = TRUE
  )
  result1
  pk_grid_bb <- sf::read_sf(qgisprocess::qgis_output(result1, "OUTPUT"))
  sf::st_geometry(pk_grid_bb) %>% plot(add =T)
  system(sprintf("ogr2ogr -f GPKG -overwrite  %s %s -nln %s", dsn, tmp_gpkg_file, output_layer_name))
}









conn <- ruut::db_connect()
q <- sprintf("SELECT * FROM %s.%s_bb", "xxx_artikkel_210127", obj)
cat(sprintf("\n-----------------\n%s\n\n", q))
pk_bb_3301 <- sf::st_read(conn, query = q)
sf::st_geometry(pk) %>% plot()
sf::st_geometry(pk_bb_3301) %>% plot(add = T)

## 3. piirkonna epk10t ruudud
# 3.1 kogu ruutvõrgustik
epk10t_grid <- pk_epk10t_grid(obj = objektid[i])
# 3.1 ainult piirkonna ruutvõrgustik
epk10t <- pk_epk10t(obj = objektid[i])
sf::st_geometry(epk10t_grid) %>% plot(border = 3, lwd = 0.3, col = "#d3fffb")
sf::st_geometry(epk10t) %>% plot(add = T, border = 3, lwd = 0.3, col = "#a3fffb")
sf::st_geometry(pk) %>% plot(add = T)

# 4 piirkonna epk10t kaardiruutude nimekiri ortofotode allalaadimiseks
epk10t_nr <- pk_epk10t_ruutude_nimekiri(objektid[i])

## 5. piirkonna epk2t ruudud
# 3.1 kogu ruutvõrgustik
epk2t_grid <- pk_epk2t_grid(obj = objektid[i])
# 3.1 ainult piirkonna ruutvõrgustik
epk2t <- pk_epk2t(obj = objektid[i])
sf::st_geometry(epk2t_grid) %>% plot(border = 3, lwd = 0.3, col = "#d3fffb")
sf::st_geometry(epk2t) %>% plot(add = T, border = 3, lwd = 0.3, col = "#a3fffb")
sf::st_geometry(pk) %>% plot(add = T)




## -------------- Muud ruudustikega seotud demo joonised ----------------
## 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")