#' Piirkonnale andmebaasides olevate polügoonide lisamine
#'
#' Etteantud piirkonna geomeetrilise piirjoone ('piir') ja selle joone piirikasti ('bb') järele leitakse nende aladega kaetud polügoonid. Andmed salvestatakse GPKG faili kihtidena.
#'
#' @param obj str Objekti nimi. Edaspidi on oluline ainult see nimi. Piirkonna geomeetrilist joont ei ole vaja lisada.
#' @param gpkg_home path Salvestatavate GPKG faili asukoht.
#' @return Uute GPKG andmebaasi kihtide 'piir_...' ja 'bb_...' loomine.
#' @seealso [sf::st_read()], [sf::write_sf()],[sf::st_transform()],[ruut::gpkg_piirkonnale_ruudustike_lisamine()] ,[ruut::gpkg_piirkonnale_polygoonide_lisamine()],[ruut::gpkg_sellest_alustame_gpkg_loomist()],[ruut::gpkg_piirkonnale_joonte_lisamine()],[ruut::gpkg_piirkonnale_punktide_lisamine()],[ruut::gpkg_piirkonnale_punktide_lisamine()],[ruut::gpkg_teisendame_polygoone()],[ruut::gpkg_teisendame_jooni()],[ruut::gpkg_teisendame_punkte()]
#' @keywords GPKG, boundary box, EPSG:3301
#' @export
#' @examples
#' \dontrun{
#'
#' gpkg_home <- "/tmp"
#' obj <- "marja"
#' gpkg_piirkonnale_polygoonide_lisamine(obj = obj, gpkg_home = gpkg_home)
#'
#' # Layers list.
#' dsn <- sprintf("%s/%s.gpkg", gpkg_home, obj)
#' sf::st_layers(dsn = dsn)
#' }
gpkg_piirkonnale_polygoonide_lisamine <- function(obj = NULL, gpkg_home = "/tmp") {
  dsn <- sprintf("%s/%s.gpkg", gpkg_home, obj)
  if (!file.exists(dsn)) {
    cat(sprintf("\nSellist faili \"%s\" ei leitud.\n", dsn))
    return(NULL)
  }
  ## Konfiguratsiooni muutujale väärtuste omistamine
  conf <- ruut::get_config()
  tmp_gpkg_file <- tempfile(fileext = ".gpkg")
  conf$gpkg_home <- dirname(tmp_gpkg_file)
  conf$gpkg_file <- gsub(paste0(conf$gpkg_home, "/"), "", tmp_gpkg_file)
  conf$gpkg_file <- gsub(".gpkg", "", conf$gpkg_file)

  conf$gpkg_home <- gpkg_home
  conf$gpkg_file <- obj
  tmp_gpkg_file_output <- ruut::construct_to_gpkg_output_file_str(conf = conf, is_input_str = F)
  tmp_gpkg_file_input <- ruut::construct_to_gpkg_output_file_str(conf = conf, is_input_str = T)

  ## Algorithm
  # ruut::qgis_algorithm_search_by_word(str = "extract")
  # algorithm <- "native:extractbylocation"
  # cat(ruut::qgis_show_help(algorithm = algorithm))

  ## -------------------- Loop -----------------------
  intersect_layers <- c("bb2") # c("piir", "bb", "bb2")
  andmed <- data.frame("schema" = character(0), "table" = character(0))
  andmed <- rbind(andmed, data.frame("schema" = "osm_shp", "table" = "landuse_a"))
  andmed <- rbind(andmed, data.frame("schema" = "maaamet", "table" = "asustusyksus"))
  andmed <- rbind(andmed, data.frame("schema" = "maaamet", "table" = "shp_katastriyksus"))
  andmed <- rbind(andmed, data.frame("schema" = "osm_shp", "table" = "buildings_a"))
  andmed <- rbind(andmed, data.frame("schema" = "osm_shp", "table" = "water_a"))
  andmed <- rbind(andmed, data.frame("schema" = "osm_shp", "table" = "pofw_a"))
  andmed <- rbind(andmed, data.frame("schema" = "osm_shp", "table" = "pois_a"))
  andmed <- rbind(andmed, data.frame("schema" = "osm_shp", "table" = "natural_a"))
  andmed <- rbind(andmed, data.frame("schema" = "eesti", "table" = "e_201_meri_a_dissolved"))
  andmed <- rbind(andmed, data.frame("schema" = "eesti", "table" = "e_202_seisuveekogu_a_dissolved"))
  andmed <- rbind(andmed, data.frame("schema" = "eesti", "table" = "e_203_vooluveekogu_a_dissolved"))
  andmed <- rbind(andmed, data.frame("schema" = "eesti", "table" = "e_301_muu_kolvik_a"))
  andmed <- rbind(andmed, data.frame("schema" = "eesti", "table" = "e_301_muu_kolvik_ka"))
  andmed <- rbind(andmed, data.frame("schema" = "eesti", "table" = "e_302_ou_a"))
  andmed <- rbind(andmed, data.frame("schema" = "eesti", "table" = "e_303_haritav_maa_a"))
  andmed <- rbind(andmed, data.frame("schema" = "eesti", "table" = "e_304_lage_a"))
  andmed <- rbind(andmed, data.frame("schema" = "eesti", "table" = "e_305_puittaimestik_a_dissolved"))
  andmed <- rbind(andmed, data.frame("schema" = "eesti", "table" = "e_306_margala_a_dissolved"))
  andmed <- rbind(andmed, data.frame("schema" = "eesti", "table" = "e_306_margala_ka"))
  andmed <- rbind(andmed, data.frame("schema" = "eesti", "table" = "e_307_turbavali_a"))
  andmed <- rbind(andmed, data.frame("schema" = "eesti", "table" = "e_401_hoone_ka"))
  andmed <- rbind(andmed, data.frame("schema" = "eesti", "table" = "e_403_muu_rajatis_ka"))
  andmed <- rbind(andmed, data.frame("schema" = "eesti", "table" = "e_404_maaalune_hoone_ka"))
  andmed <- rbind(andmed, data.frame("schema" = "eesti", "table" = "e_501_tee_a"))
  andmed <- rbind(andmed, data.frame("schema" = "eesti", "table" = "e_505_liikluskorralduslik_rajatis_ka"))
  andmed <- rbind(andmed, data.frame("schema" = "eesti", "table" = "mullakaart"))
  for (intersect in intersect_layers) {
    conf$gpkg_table <- intersect
    intersect_layer <- ruut::construct_to_gpkg_output_file_str(conf = conf, is_input_str = T)
    for (i in 1:nrow(andmed)) {
      if (andmed$schema[i] %in% c("teeregister_wfs")) geom <- "geometry" else geom <- "geom"
      conf$gpkg_table <- sprintf("%s_%s", intersect, andmed$table[i])
      conf$table <- andmed$table[i]
      conf$schema <- andmed$schema[i]
      output <- ruut::construct_to_gpkg_output_file_str(conf = conf, is_input_str = F)
      input <- ruut::construct_to_gpkg_output_postgres_str(
        conf = conf, geometry_type = "Polygon", srid = 3301,
        checkPrimaryKeyUnicity = TRUE, key = "id", geometry_field = geom
      )
      ## !!! Trikk: alguses leiame ühisosaga piirkonnad
      ## ------------------- QGIS: qgisprocess ----------------------
      str <- paste0("{ 'INPUT' : '", input, "', 'INTERSECT' : '", intersect_layer, "', 'OUTPUT' : '", tmp_gpkg_file_output, "', 'PREDICATE' : [0] }")
      algorithm <- "native:extractbylocation"
      cmd <- ruut::construct_qgis_output_result_to_better_format(str = str, algorithm = algorithm)
      cat(sprintf("\n%s\n\n", cmd))
      system(cmd)
      ## !!! Trikk jätkub: edasi leiame alles ühisosa
      str <- paste0("{ 'INPUT' : '", tmp_gpkg_file_input, "', 'OVERLAY' : '", intersect_layer, "', 'OUTPUT' : '", output, "', 'INPUT_FIELDS' : '', 'OVERLAY_FIELDS' : '', 'OVERLAY_FIELDS_PREFIX' : '' }")
      str <- paste0("{ 'INPUT' : '", tmp_gpkg_file_input, "', 'INPUT_FIELDS' : [], 'OUTPUT' : '", output, "', 'OVERLAY' : '", intersect_layer, "', 'OVERLAY_FIELDS' : [], 'OVERLAY_FIELDS_PREFIX' : '' }")
      algorithm <- "native:intersection"
      cmd <- ruut::construct_qgis_output_result_to_better_format(str = str, algorithm = algorithm)
      cat(sprintf("\n%s\n\n", cmd))
      system(cmd)
      ## Filtreerime maakasutuse kihi 'landuse-a' eraldi alamkihtideks
      if (andmed$table[i] == "landuse_a") {
        landuse_a <- unique(as.data.frame(sf::read_sf(dsn = dsn, layer = sprintf("%s_landuse_a", intersect), as_tibble = T))[, c("code", "fclass")])
        parent_table <- conf$gpkg_table
        for (k in 1:nrow(landuse_a)) {
          table_suffix <- landuse_a$fclass[k]
          conf$gpkg_table <- parent_table
          input <- ruut::construct_to_gpkg_output_file_str(conf = conf, is_input_str = TRUE)
          conf$gpkg_table <- sprintf("%s_%s", conf$gpkg_table, table_suffix)
          output <- ruut::construct_to_gpkg_output_file_str(conf = conf, is_input_str = FALSE)
          str <- paste0("{ 'INPUT' : '", input, "', 'INTERSECT' : '", intersect_layer, "', 'OUTPUT' : '", output, "', FIELD : 'code', OPERATOR : 0, VALUE : '", landuse_a$code[k], "' }")
          algorithm <- "native:extractbyattribute"
          cmd <- ruut::construct_qgis_output_result_to_better_format(str = str, algorithm = algorithm)
          cat(sprintf("\n%s\n", cmd))
          system(cmd)
        }
      }
      ## Filtreerime hoonete kihi 'landuse-a' eraldi tüübi järele alamkihtideks
      if (andmed$table[i] == "e_401_hoone_ka") {
        alamobjektid <- unique(as.data.frame(sf::read_sf(dsn = dsn, layer = sprintf("%s_e_401_hoone_ka", intersect), as_tibble = T), drop = FALSE)[, c("kood", "tyyp")])
        parent_table <- conf$gpkg_table
        for (k in 1:nrow(alamobjektid)) {
          table_suffix <- alamobjektid$tyyp[k]
          conf$gpkg_table <- parent_table
          input <- ruut::construct_to_gpkg_output_file_str(conf = conf, is_input_str = TRUE)
          conf$gpkg_table <- sprintf("%s_%s", conf$gpkg_table, table_suffix)
          output <- ruut::construct_to_gpkg_output_file_str(conf = conf, is_input_str = FALSE)
          str <- paste0("{ 'INPUT' : '", input, "', 'INTERSECT' : '", intersect_layer, "', 'OUTPUT' : '", output, "', FIELD : 'tyyp', OPERATOR : 0, VALUE : '", alamobjektid$tyyp[k], "' }")
          algorithm <- "native:extractbyattribute"
          cmd <- ruut::construct_qgis_output_result_to_better_format(str = str, algorithm = algorithm)
          cat(sprintf("\n%s\n", cmd))
          system(cmd)
          ## ----------- lisame tsentroidid -----------
          conf$gpkg_table <- sprintf("%s_%s", parent_table, table_suffix)
          input <- ruut::construct_to_gpkg_output_file_str(conf = conf, is_input_str = TRUE)
          conf$gpkg_table <- sprintf("%s_%s_p", parent_table, table_suffix)
          output <- ruut::construct_to_gpkg_output_file_str(conf = conf, is_input_str = FALSE)
          str <- sprintf("{ 'ALL_PARTS' : False, 'INPUT' : '%s', 'OUTPUT' : '%s' }", input, output)
          # ruut::qgis_algorithm_search_by_word("centroid")
          algorithm <- "native:centroids"
          # ruut::qgis_show_help(algorithm = algorithm)
          cmd <- ruut::construct_qgis_output_result_to_better_format(str = str, algorithm = algorithm)
          cat(sprintf("\n%s\n", cmd))
          system(cmd)
        }
      }
    }
  }
  ## Layers list
  sf::st_layers(dsn = dsn)
}