# ar so savelku vajadzigos datus 

library(pool)
library(tidyverse)
library(lubridate)

pool <- dbPool(
  drv = RMySQL::MySQL(),
  dbname = "LZP",
  host = "geodb",
  username = "janis",
  password = "Datums1%"
)





info <- tbl(pool, "urbumu_pamatinfo_LV") %>%
  mutate(punkts = paste(stacija, urbnr, sep = ', ')) %>%
  select(punkts, ns) %>%
  collect()

dati <- tbl(pool, "limeni_LVGMC")

unikalie <- dati %>%
  distinct(punkts, station, urbnr) %>%
  collect() %>%
  left_join(info, by = "punkts", `copy` = TRUE)


# atlasitas stacijas
stacijas <- c("Aistere", "Virane", "Zuteni")

unique(unikalie$station)
unikalie %>%
  filter(station %in% !!stacijas)

levels <- dati %>%
  filter(station %in% !!stacijas) %>%
  collect() %>%
  transmute(datetime = as_datetime(date), parameter = parametrs, level, 
            measurement_type = case_when(
            veids %in% c("-", "Kontroles", "Manualie", "Lauka", "Analitiskie", "cits")  ~ "manual", 
            veids == "Automatiskie" ~ "automatic"
            ), 
      name = punkts, station, well = urbnr)
  

levels



info2 <- tbl(pool, "urbumu_pamatinfo_LV") %>%
  filter(stacija %in% !!stacijas) %>%
  mutate(name = paste(stacija, urbnr, sep = ', ')) %>%
  select(name, well_no = ns, well = urbnr, station = stacija, lat, lon, z = Z_BAS, depth = dzilums, aquifer = horizonts, f_top_rel, f_bot_rel, f_dia, year = gads) %>%
  collect()

info2

unikalie2 <- unikalie %>%
  filter(station %in% !!stacijas)


# piekoriģējam Shiny sagatabotos simulētos datus:
urb <- read_rds(file = 'shiny_levels.rds')
urb$station
urb$station[urb$station == "station1"] <- "station_A"
urb$station[urb$station == "station2"] <- "station_B"
urb <- urb %>%
  mutate(name = paste0(station, ", ", well)) %>%
  mutate(level = round(level, 2))

urb

unique <- urb %>% count(station, well, name) %>%
  select(-n)
unique
info2

info <- unique %>%
  add_column(lon = c(21.4,21.41, 26.3, 26.305),
             lat = c(56.8, 56.804, 57.3, 57.305),
             z = c(29,29, 125, 126),
             depth= c(5, 7, 12, 15),
             aquifer = c("Q", "Q", "Q", "Q2"),
             f_top_rel = c(2,4,9,11),
             f_bot_rel = c(4.5, 6.5, 11, 14),
             f_dia = c(50,50, 89, 89),
             year = c(1986, 1986, 1971, 1971))

info


txt_all <- tbl(pool, "limeni_labojumi") %>%
  collect() %>%
  filter(stacija %in% !!stacijas)

repairs <- txt_all %>%
  select(station = stacija, well =  urbnr, measurement_type = veids, dt_from = dt_no, dt_to = dt_lidz, dt_specific, correction = level, author = labots_aut, timestamp = labots_tmst, comment = kom_labots) %>%
  slice(1:2)


############# jāuztaisa lokāla DB ar līmeņu labojumiem un līmeņiem un pamatinfo tabulu
library(DBI)
conl <- dbConnect(RSQLite::SQLite(), "db.sqlite")
dbWriteTable(conl, "groundwater_levels", urb, overwrite = TRUE)
dbWriteTable(conl, "well_info", info, overwrite = TRUE)
dbWriteTable(conl, "repair_instructions", repairs, overwrite = TRUE)

######################################
dbDisconnect(conl)








# write_rds(unique, file = 'unique.rds')
# write_rds(urb, file = 'urb_levels_simulated.rds')
# write_rds(info, file = "info_simulated.rds")
# 



library(tidyverse)
u <- read_rds(file = 'unique.rds')
u

p <- dbReadTable(conl, "well_info")
p %>%
  select(station, well, name)

# 
# 
# ############# jāuztaisa lokāla DB ar līmeņu labojumiem un līmeņiem un pamatinfo tabulu
library(DBI)
conl <- dbConnect(RSQLite::SQLite(), "db.sqlite")
# dbWriteTable(conl, "groundwater_levels", levels)
# dbWriteTable(conl, "well_info", info2)
# dbWriteTable(conl, "repair_instructions", repairs)
# 
# # list tables
dbListTables(conl)
# # list fields
dbListFields(conl, "well_info")
# dbListFields(conl, "groundwater_levels")
# dbListFields(conl, "repair_instructions")
# 
# dbDisconnect(conl)
# dbDisconnect(pool)




