#' Statistikaameti REL andmed
#'
#' Source: \url{https://estat.stat.ee/StatistikaKaart/VKR}. Lae sealt käsitsi alla 1x1 km andmestiku shp fail. Andmed salvestatakse postgisi andmebaasi. Schema = 'statistikaamet'. Koniguratsiooni muutmiseks muuda konfiguratsiooni. Muutujate vaikeväärtused on sellised, et ei ole vaja midagi muuta.
#' @param conf A list() of configuration variables. Default values \code{\link[ruut]{get_config}}.
#' @return No output.
#' @seealso [ruut::get_config()], [ruut::copy_shp_to_db()]
#' @keywords postgis, maps, ESRI Shpfile, OSM
#' @export
#' @examples
#' \dontrun{
#'
#' conf <- ruut::get_config()
#' statistika_rahvaarv(conf = conf)
#' }
statistika_rahvaarv <- function(conf = NULL) {
  ans <- utils::askYesNo("Do you want to import maps into database?", default = F)
  if (!ans | is.na(ans)) {
    cat("\n------------------------\n")
    cat("Kaardikihte ei lisatud.")
    cat("\n------------------------\n")
    return()
  }
  if (ans) {
    ## --------------------- muutujad ja teisendused -----------------------------
    vars <- ajutised_muutujad(conf = conf)
    conf <- vars$conf

    # Directory for zip files.
    tmp_dir <- sprintf("%s/tmp/%s", system.file(package = "estmap"), "statistika_rahvaarv")
    if (!dir.exists(tmp_dir)) {
      dir.create(tmp_dir)
    }
    # Params
    # Shp faili nimi kus paikneb rahvaloenduse andmed 1x1 km ruudustike kohta.
    params <- "Mehed_ja_naised__Vanuseruhmad_kokku_(Ruudustik_1_x_1_km_2011)"
    # Export to postgis
    if (is.null(conf)) {
      conf <- ruut::get_config()
      conf$schema <- "statistikaamet"
    }
    # New schema
    ruut::db_create_new_schema(conf = conf)
    # Shapefile (ZIP archive)
    map_shapefile <- sprintf("%s.zip", params)
    # Download and save
    saveTo <- sprintf("%s/%s", tmp_dir, map_shapefile)
    # Unzip
    utils::unzip(saveTo, overwrite = T, exdir = tmp_dir)
    # List of files
    ls <- list.files(path = tmp_dir, pattern = ".dbf")
    ls_long <- list.files(path = tmp_dir, pattern = ".dbf", full.names = T)
    tbl_names <- unlist(strsplit(x = ls, split = ".dbf"))
    i <- 1
    conf$table <- "rel_1x1km"
    source <- sprintf('"%s" "%s"', tmp_dir, tbl_names[i])


    ## --------------------- 1.1 Copy shp file to gpkg -------------------------
    cmd <- sprintf("ogr2ogr -progress -f GPKG '%s' %s %s -overwrite -nlt %s -lco GEOMETRY_NAME=geom -lco FID=id -nln %s -nlt PROMOTE_TO_MULTI", vars$tmp_gpkg_file, tmp_dir, tbl_names[i], "POLYGON", vars$layer_1)
    system(cmd)
    cat(sprintf("\n-------------\nLoodi andmebaas '%s' layername=%s.\n", vars$tmp_gpkg_file, vars$layer_1))

    ## --------- 1.2 Rahvaarvu väärtuse '<4' asendamine väärtusega 3 -----------
    # ruut::qgis_algorithm_search_by_word("calculator")
    algorithm <- "native:fieldcalculator"
    # ruut::qgis_show_help(algorithm = algorithm)
    input <- vars$tmp_gpkg_file_input_1
    output <- vars$tmp_gpkg_file_output_2
    cmd <- sprintf("qgis_process run %s --FIELD_NAME='rahvaarv' --FIELD_TYPE=1 --FIELD_LENGTH=0 --FIELD_PRECISION=0 --GLOBAL=\"\" --FORMULA='if ( \"VAARTUS\" >= 1000000, 3, \"VAARTUS\")' --INPUT='%s' --OUTPUT='%s' ", algorithm, input, output) # Ma ei tea miks nii töötab, aga muud moodi ei saanud ühe teisendusega tööle. Veider.
    system(cmd)
    cat(sprintf("\n-------------\nLoodi andmebaas '%s'.\n", output))

    ## ------------------- 1.3 Jätame alles vajalikud veerud ---------------------
    # ruut::qgis_algorithm_search_by_word("retain")
    algorithm <- "native:retainfields"
    # ruut::qgis_show_help(algorithm = algorithm)
    input <- vars$tmp_gpkg_file_input_2
    output <- vars$tmp_gpkg_file_output_3
    cmd <- sprintf("qgis_process run %s  --FIELDS='VID' --FIELDS='KOOD' --FIELDS='rahvaarv' --INPUT='%s' --OUTPUT='%s' ", algorithm, input, output)
    system(cmd)
    cat(sprintf("\n-------------\nLoodi andmebaas '%s'.\n", output))

    ## ----------- 1.4 Ühendame statistika ruudud oma ruudustikuga -------------
    # ruut::qgis_algorithm_search_by_word("Union")
    algorithm <- "native:union"
    # ruut::qgis_show_help(algorithm = algorithm)
    input <- vars$tmp_gpkg_file_input_3
    conf$schema <- "maaamet"
    conf$table <- "epk2t_grid"
    overlay <- ruut::construct_to_gpkg_output_postgres_str(
      conf = conf, geometry_type = "Polygon", srid = 3301,
      checkPrimaryKeyUnicity = TRUE, key = "id", geometry_field = "geom"
    )
    output <- vars$tmp_gpkg_file_output_4
    str <- sprintf("{ 'INPUT' : '%s', 'OUTPUT' : '%s', 'OVERLAY' : '%s', 'OVERLAY_FIELDS_PREFIX' : '' }", input, output, overlay)
    cmd <- ruut::construct_qgis_output_result_to_better_format(str = str, algorithm = algorithm)
    system(cmd)
    cat(sprintf("\n-------------\nLoodi andmebaas '%s'.\n", output))

    ## ------- 1.5 Rahvaarvu väärtuse 'NULL' asendamine väärtusega 0 -----------
    # ruut::qgis_algorithm_search_by_word("calculator")
    algorithm <- "native:fieldcalculator"
    # ruut::qgis_show_help(algorithm = algorithm)
    input <- vars$tmp_gpkg_file_input_4
    output <- vars$tmp_gpkg_file_output_5
    cmd <- sprintf("qgis_process run %s --FIELD_NAME='rahvaarv' --FIELD_TYPE=1 --FIELD_LENGTH=0 --FIELD_PRECISION=0 --GLOBAL=\"\" --FORMULA='if(\"rahvaarv\" , \"rahvaarv\" ,0)' --INPUT='%s' --OUTPUT='%s' ", algorithm, input, output) # Ma ei tea miks nii töötab, aga muud moodi ei saanud ühe teisendusega tööle. Veider.
    system(cmd)
    cat(sprintf("\n-------------\nLoodi andmebaas '%s'.\n", output))

    ## ------------------ 1.6 Copy temp gpkg file to postgis ---------------------
    conf$schema <- "statistikaamet"
    conf$table <- "rel_1x1km"
    PG <- ruut::construct_ogr2ogr_PG_connect_str(conf = conf)
    ## Copy shp directly to postgis
    # cmd <- sprintf("ogr2ogr -progress --config PG_USE_COPY YES -f PostgreSQL %s -lco DIM=2 %s %s -overwrite -nlt %s -lco GEOMETRY_NAME=geom -lco FID=id -nln %s.%s -nlt PROMOTE_TO_MULTI", PG, tmp_dir, tbl_names[i], "POLYGON", conf$schema, conf$table)
    cmd <- sprintf("ogr2ogr -progress --config PG_USE_COPY YES -f PostgreSQL %s -lco DIM=2 %s %s -overwrite -nlt %s -lco GEOMETRY_NAME=geom -lco FID=id -nln %s.%s -nlt PROMOTE_TO_MULTI", PG, vars$tmp_gpkg_file, vars$layer_5, "POLYGON", conf$schema, conf$table)
    system(cmd)
    cat(sprintf("\n-------------\nLoodi andmebaas '%s'.\n", conf$table))
    # Delete unnecessary files.
    system(sprintf("find %s -type f -not -name '*.zip' -print0 | xargs -0 rm --", tmp_dir))
  }
}