# ── Shared helpers ─────────────────────────────────────────────────────────────

library(sf)
library(dplyr)
library(stringr)

# Hierarchy codes from nivel8 via str_sub truncation (one char per level up)
add_nivel_codes <- function(sf_obj) {
  st_join(sf_obj, nivel8_all |> select(codigo_n4:codigo_n8), join = st_within)
}

# Safe column name from category string
safe_cat <- function(x) {
  x |>
    tolower() |>
    str_replace_all("[^a-z0-9]+", "_") |>
    str_replace_all("^_|_$", "")
}

# Formatted number for UI display
fmt_n <- function(x, digits = 0) {
  if (all(is.na(x))) return("—")
  format(round(sum(x, na.rm = TRUE), digits), big.mark = ",", scientific = FALSE)
}

fmt_avg <- function(x, digits = 1) {
  if (all(is.na(x))) return("—")
  format(round(mean(x, na.rm = TRUE), digits), big.mark = ",", scientific = FALSE)
}

# DuckDB connection helper with spatial + httpfs extensions loaded.
# Uses duckdb(dbdir = path) so the driver object is created with the correct path
# upfront (avoids a class of GC-related "Invalid connection" errors).
# Tries LOAD first (no network); falls back to INSTALL + LOAD on version mismatch.
ddb_connect <- function(path = ":memory:", remote = FALSE, read_only = FALSE) {
  drv <- duckdb::duckdb(dbdir = path, read_only = read_only)
  # Keep driver in globalenv so R's GC never collects it before the connection
  # is closed. parent.frame() is unreliable when called through source()/eval()
  # chains (returns a temporary env that is GC'd immediately).
  assign(".ddb_drv", drv, envir = globalenv())
  con <- DBI::dbConnect(drv)
  tryCatch(
    DBI::dbExecute(con, "LOAD spatial"),
    error = function(e) {
      DBI::dbExecute(con, "INSTALL spatial")
      DBI::dbExecute(con, "LOAD spatial")
    }
  )
  if (remote) {
    tryCatch(
      DBI::dbExecute(con, "LOAD httpfs"),
      error = function(e) {
        DBI::dbExecute(con, "INSTALL httpfs")
        DBI::dbExecute(con, "LOAD httpfs")
      }
    )
  }
  con
}

# Convert sf geometry column to WKB blob for DuckDB insert
sf_to_wkb_df <- function(sf_obj) {
  wkt <- sf::st_as_text(sf::st_geometry(sf_obj))
  df  <- sf::st_drop_geometry(sf_obj)
  df$geom_wkt <- wkt
  df
}

# Read WKB blob column back to sf
# DuckDB returns ST_AsWKB as a list of raw vectors — st_as_sfc accepts it directly
wkb_to_sf <- function(df, geom_col = "geom", crs = 4326) {
  geoms <- sf::st_as_sfc(df[[geom_col]], crs = crs)
  df[[geom_col]] <- NULL
  sf::st_as_sf(df, geometry = geoms)
}

# Stat display helper for Shiny summary panel
stat_row <- function(label, value) {
  htmltools::tags$div(
    class = "d-flex justify-content-between small py-1 border-bottom border-secondary",
    htmltools::tags$span(class = "text-muted", label),
    htmltools::tags$span(class = "fw-semibold", value)
  )
}