if (interactive()) {
  config_path <- "C://workspace//gwilenalim//yaml//config.yml"
  if (file.exists(config_path)) {
    library(RPostgres)
    library(meteo4Vilaine)
    config <- yaml::read_yaml(config_path)
    con <- tryCatch({
      DBI::dbConnect(
        RPostgres::Postgres(),
        host = config$host,
        port = config$port,
        user = config$user,
        password = config$password,
        dbname = config$dbname
      )
    }, error = function(e) NULL)
    
    
    
    if (!is.null(con)) {
      
      triangle_sf <- sf::st_sf(
        geometry = sf::st_sfc(
          sf::st_polygon(list(rbind(
            c(-1.6794, 48.1147),  # Rennes
            c(-1.2100, 48.1230),  # Vitré
            c(-1.5025, 47.6833),  # Derval
            c(-1.6794, 48.1147)   # Retour à Rennes
          )))
        ),
        crs = 4326
      )
      
      g<-f_graph_pluviometrie(triangle_sf, date = as.Date("2025-09-11"), con)
      DBI::dbDisconnect(con)
      print(g)
    } else {
      message("Connexion à la base impossible, exemple non exécuté.")
    }
  } else {
    message("Fichier de configuration introuvable.")
  }
}

knitr::include_graphics(system.file("extdata", "graph_pluvio.png", package = "valorisationQE"))

   triangle_sf <- sf::st_sf(
        geometry = sf::st_sfc(
          sf::st_polygon(list(rbind(
            c(-1.6794, 48.1147),  # Rennes
            c(-1.2100, 48.1230),  # Vitré
            c(-1.5025, 47.6833),  # Derval
            c(-1.6794, 48.1147)   # Retour à Rennes
          )))
        ),
        crs = 4326
      )

f_fond_de_carte(triangle_sf, zoom=9)
#> Coordinate system already present.
#> ℹ Adding new coordinate system, which will replace the existing one.

library(sf)
#> Linking to GEOS 3.13.1, GDAL 3.11.0, PROJ 9.6.0; sf_use_s2() is TRUE
library(dplyr)
#> 
#> Attachement du package : 'dplyr'
#> Les objets suivants sont masqués depuis 'package:stats':
#> 
#>     filter, lag
#> Les objets suivants sont masqués depuis 'package:base':
#> 
#>     intersect, setdiff, setequal, union

# Coordonnées approximatives des centres des communes
coords <- data.frame(
  code = c("A", "B", "C"),
  commune = c("Châteaugiron", "Janzé", "Thourie"),
  lon = c(-1.499, -1.498, -1.4833),
  lat = c(48.052, 47.960, 47.8550)
)

# Création de l'objet sf
points_sf <- st_as_sf(coords, coords = c("lon", "lat"), crs = 4326)


# bassin versant
   triangle_sf <- sf::st_sf(
        geometry = sf::st_sfc(
          sf::st_polygon(list(rbind(
            c(-1.6794, 48.1147),  # Rennes
            c(-1.2100, 48.1230),  # Vitré
            c(-1.5025, 47.6833),  # Derval
            c(-1.6794, 48.1147)   # Retour à Rennes
          )))
        ),
        crs = 4326
      )

fond_carte<-f_fond_de_carte(triangle_sf, zoom=10)
#> Coordinate system already present.
#> ℹ Adding new coordinate system, which will replace the existing one.

# Création du data.frame
df_valeurs <- data.frame(
  code = c("A", "B", "C"),
  valeur = c(0.23, 0.71, 3.52)
)

f_cartographie_concentrations(fond_carte,
                              shp_staq=points_sf,
                              data_carte=df_valeurs,
                              col_stations="code",
                              col_valeurs="valeur",
                              titre="Bilan des concentrations en pesticides",
                              sous_titre="Date de la campagne",
                              breaks=c(0, 0.5, 2, Inf),
                              values = c("cyan", "yellow", "red"),
                              pts_size=6,
                              text_size=5,
                              unite="µg/L",
                              nom_legende="Concentrations",
                              nb_decimal_text=1)

base_pesticides<-importe_ref_pestibase()
#> Warning: One or more parsing issues, call `problems()` on your data frame for details,
#> e.g.:
#>   dat <- vroom(...)
#>   problems(dat)

head(base_pesticides)
#> $liste_phytos
#> # A tibble: 796 × 37
#>    id     id_sandre id_cas      name_fr         SA_CodeSANDRE has_metabolite    
#>    <chr>      <dbl> <chr>       <chr>           <chr>         <chr>             
#>  1 00b0f0      1495 13194-48-4  ETHOPROPHOS     1495          <NA>              
#>  2 00f550      2009 120068-37-3 FIPRONIL        2009          0807d2|cb8849|e27…
#>  3 00f979      1859 28772-56-7  BROMADIOLONE    1859          <NA>              
#>  4 01796e      1882 111991-09-4 NICOSULFURON    1882          3b43de|95e26f|e70…
#>  5 023228      1111 86-50-0     AZINPHOS-METHYL 1111          <NA>              
#>  6 03212d      5617 163515-14-8 DIMETHENAMIDE-P 5617          24674b|5dc5bd     
#>  7 03428c      2016 13360-45-7  CHLOROBROMURON  2016          <NA>              
#>  8 037a08      1132 12789-03-6  CHLORDANE       1132          deb748            
#>  9 04403b      7508 125225-28-7 IPCONAZOLE      7508          <NA>              
#> 10 046e09      2022 131341-86-1 FLUDIOXONIL     2022          <NA>              
#> # ℹ 786 more rows
#> # ℹ 31 more variables: dw_pertinence_status <chr>,
#> #   has_authorized_oepp_culture <chr>,
#> #   has_removed_authorized_oepp_culture <chr>, function_fr <chr>,
#> #   function_bzh <chr>, has_variant <chr>, biocidal_product_type <chr>,
#> #   list_parent_measures <chr>, is_ref_ppp <dbl>, is_bnvd <dbl>,
#> #   is_ref_biocid <dbl>, vmax <chr>, is_edc_list1 <dbl>, aa_eqs_dw <dbl>, …
#> 
#> $liste_phytos_autorises
#> # A tibble: 201 × 37
#>    id     id_sandre id_cas      name_fr             SA_CodeSANDRE has_metabolite
#>    <chr>      <dbl> <chr>       <chr>               <chr>         <chr>         
#>  1 01796e      1882 111991-09-4 NICOSULFURON        1882          3b43de|95e26f…
#>  2 03212d      5617 163515-14-8 DIMETHENAMIDE-P     5617          24674b|5dc5bd 
#>  3 046e09      2022 131341-86-1 FLUDIOXONIL         2022          <NA>          
#>  4 05691a      1950 143390-89-0 KRESOXIME-METHYLE   1950          a955e4        
#>  5 09533b      7500 500008-45-7 CHLORANTRANILIPROLE 7500          <NA>          
#>  6 099c49      1149 52918-63-5  DELTAMETHRINE       1149          09699c        
#>  7 0a6355      7748 180409-60-3 CYFLUFENAMIDE       7748          0a6355        
#>  8 0ad417      2534 94125-34-5  PROSULFURON         2534          0c83de|b99481 
#>  9 0b83ba      2547 81406-37-3  FLUROXYPYR-MEPTYL   2547          651ae5        
#> 10 118471      1670 67129-08-2  METAZACHLORE        1670          780944|9446c9 
#> # ℹ 191 more rows
#> # ℹ 31 more variables: dw_pertinence_status <chr>,
#> #   has_authorized_oepp_culture <chr>,
#> #   has_removed_authorized_oepp_culture <chr>, function_fr <chr>,
#> #   function_bzh <chr>, has_variant <chr>, biocidal_product_type <chr>,
#> #   list_parent_measures <chr>, is_ref_ppp <dbl>, is_bnvd <dbl>,
#> #   is_ref_biocid <dbl>, vmax <chr>, is_edc_list1 <dbl>, aa_eqs_dw <dbl>, …
#> 
#> $liste_phytos_non_autorises
#> # A tibble: 595 × 37
#>    id     id_sandre id_cas      name_fr         SA_CodeSANDRE has_metabolite    
#>    <chr>      <dbl> <chr>       <chr>           <chr>         <chr>             
#>  1 00b0f0      1495 13194-48-4  ETHOPROPHOS     1495          <NA>              
#>  2 00f550      2009 120068-37-3 FIPRONIL        2009          0807d2|cb8849|e27…
#>  3 00f979      1859 28772-56-7  BROMADIOLONE    1859          <NA>              
#>  4 023228      1111 86-50-0     AZINPHOS-METHYL 1111          <NA>              
#>  5 03428c      2016 13360-45-7  CHLOROBROMURON  2016          <NA>              
#>  6 037a08      1132 12789-03-6  CHLORDANE       1132          deb748            
#>  7 04403b      7508 125225-28-7 IPCONAZOLE      7508          <NA>              
#>  8 04c1f8      1463 63-25-2     CARBARYL        1463          <NA>              
#>  9 050dba      1763 30043-49-3  ETHIDIMURON     1763          <NA>              
#> 10 0539ef      5747 5707-69-7   DRAZOXOLON      5747          <NA>              
#> # ℹ 585 more rows
#> # ℹ 31 more variables: dw_pertinence_status <chr>,
#> #   has_authorized_oepp_culture <chr>,
#> #   has_removed_authorized_oepp_culture <chr>, function_fr <chr>,
#> #   function_bzh <chr>, has_variant <chr>, biocidal_product_type <chr>,
#> #   list_parent_measures <chr>, is_ref_ppp <dbl>, is_bnvd <dbl>,
#> #   is_ref_biocid <dbl>, vmax <chr>, is_edc_list1 <dbl>, aa_eqs_dw <dbl>, …
#> 
#> $liste_metabolites
#> # A tibble: 230 × 37
#>    id     id_sandre id_cas      name_fr             SA_CodeSANDRE has_metabolite
#>    <chr>      <dbl> <chr>       <chr>               <chr>         <chr>         
#>  1 0092d7      8894 6552-12-1   FENTHION OXON       8894          <NA>          
#>  2 0167cc      3286 111-87-5    1-OCTANOL           3286          <NA>          
#>  3 01720f      1805 16655-82-6  3-HYDROXY-CARBOFUR… 1805          <NA>          
#>  4 021643      1481 79-43-6     ACIDE DICHLOROACET… 1481          <NA>          
#>  5 033c6b      1650 106-48-9    CHLOROPHENOL-4      1650          <NA>          
#>  6 036e2e      1930 2327-02-8   3,4-DICHLOROPHENYL… 1930          <NA>          
#>  7 051ecd      1585 626-43-7    DICHLOROANILINE-3,5 1585          <NA>          
#>  8 0539d5      3159 19988-24-0  ATRAZINE 2-HYDROXY… 3159          <NA>          
#>  9 064256      3312 95-16-9     BENZOTHIAZOLE       3312          <NA>          
#> 10 06ec59      8746 543739-84-0 PENTHIOPYRAD DM-PCA 8746          <NA>          
#> # ℹ 220 more rows
#> # ℹ 31 more variables: dw_pertinence_status <chr>,
#> #   has_authorized_oepp_culture <chr>,
#> #   has_removed_authorized_oepp_culture <chr>, function_fr <chr>,
#> #   function_bzh <chr>, has_variant <chr>, biocidal_product_type <chr>,
#> #   list_parent_measures <chr>, is_ref_ppp <dbl>, is_bnvd <dbl>,
#> #   is_ref_biocid <dbl>, vmax <chr>, is_edc_list1 <dbl>, aa_eqs_dw <dbl>, …
#> 
#> $liste_metabolites_pertinents
#> # A tibble: 217 × 37
#>    id     id_sandre id_cas      name_fr             SA_CodeSANDRE has_metabolite
#>    <chr>      <dbl> <chr>       <chr>               <chr>         <chr>         
#>  1 0092d7      8894 6552-12-1   FENTHION OXON       8894          <NA>          
#>  2 0167cc      3286 111-87-5    1-OCTANOL           3286          <NA>          
#>  3 01720f      1805 16655-82-6  3-HYDROXY-CARBOFUR… 1805          <NA>          
#>  4 021643      1481 79-43-6     ACIDE DICHLOROACET… 1481          <NA>          
#>  5 033c6b      1650 106-48-9    CHLOROPHENOL-4      1650          <NA>          
#>  6 036e2e      1930 2327-02-8   3,4-DICHLOROPHENYL… 1930          <NA>          
#>  7 051ecd      1585 626-43-7    DICHLOROANILINE-3,5 1585          <NA>          
#>  8 0539d5      3159 19988-24-0  ATRAZINE 2-HYDROXY… 3159          <NA>          
#>  9 064256      3312 95-16-9     BENZOTHIAZOLE       3312          <NA>          
#> 10 06ec59      8746 543739-84-0 PENTHIOPYRAD DM-PCA 8746          <NA>          
#> # ℹ 207 more rows
#> # ℹ 31 more variables: dw_pertinence_status <chr>,
#> #   has_authorized_oepp_culture <chr>,
#> #   has_removed_authorized_oepp_culture <chr>, function_fr <chr>,
#> #   function_bzh <chr>, has_variant <chr>, biocidal_product_type <chr>,
#> #   list_parent_measures <chr>, is_ref_ppp <dbl>, is_bnvd <dbl>,
#> #   is_ref_biocid <dbl>, vmax <chr>, is_edc_list1 <dbl>, aa_eqs_dw <dbl>, …
#> 
#> $liste_metabolites_non_pertinents
#> # A tibble: 13 × 37
#>    id     id_sandre id_cas       name_fr            SA_CodeSANDRE has_metabolite
#>    <chr>      <dbl> <chr>        <chr>              <chr>         <chr>         
#>  1 15a430      6800 142363-53-9  ALACHLOR ESA       6800          <NA>          
#>  2 24674b      7735 380412-59-9  DIMETHENAMIDE OXA  7735          <NA>          
#>  3 3b709e      7727 1418095-08-5 DIMETHACHLORE CGA… 7727          <NA>          
#>  4 5dc5bd      6865 205939-58-8  DIMETHENAMIDE ESA  6865          <NA>          
#>  5 7488a7      6862 194992-44-4  ACETOCHLOR OXA     6862          <NA>          
#>  6 780944      6894 1231244-60-2 METAZACHLORE OXA   6894          <NA>          
#>  7 86eb3b      7729 1418095-19-8 METOLACHLORE NOA … 7729          <NA>          
#>  8 9446c9      6895 172960-62-2  METAZACHLORE ESA   6895          <NA>          
#>  9 9c0138      6853 152019-73-3  METOLACHLORE OXA   6853          <NA>          
#> 10 c64d72      6381 <NA>         DIMETHACHLORE-ESA  6381          <NA>          
#> 11 e00b49      8865 <NA>         CHLOROTHALONIL-R4… 8865          <NA>          
#> 12 f632ad      6854 171118-09-5  METOLACHLORE ESA   6854          <NA>          
#> 13 f92b6c      6856 187022-11-3  ACETOCHLOR ESA     6856          <NA>          
#> # ℹ 31 more variables: dw_pertinence_status <chr>,
#> #   has_authorized_oepp_culture <chr>,
#> #   has_removed_authorized_oepp_culture <chr>, function_fr <chr>,
#> #   function_bzh <chr>, has_variant <chr>, biocidal_product_type <chr>,
#> #   list_parent_measures <chr>, is_ref_ppp <dbl>, is_bnvd <dbl>,
#> #   is_ref_biocid <dbl>, vmax <chr>, is_edc_list1 <dbl>, aa_eqs_dw <dbl>,
#> #   aa_eqs <chr>, aa_eqs_mac <chr>, aa_eqs_eco_psee <chr>, …

data <- data.frame(
   DatePrel = Sys.Date() + rep(sort(sample(1:500, 10)), 3),
   RsAna = round(runif(60, 0, 0.5), 2),
   LqAna = 0.1,
   CdStationMesureEauxSurface = c("A", "B", "C"),
   CdParametre = c("1200", "1506"),
   CdUniteMesure = "133"
 )
 data$CdRqAna <- ifelse(data$RsAna >= data$LqAna, "1", "10")
 calcule_somme_pesticides(data)
#> # A tibble: 30 × 9
#>    CdStationMesureEauxSu…¹ DatePrel   CdUniteMesure CdSupport CdFractionAnalysee
#>    <chr>                   <date>     <chr>         <chr>     <chr>             
#>  1 A                       2025-11-06 133           3         23                
#>  2 B                       2026-01-06 133           3         23                
#>  3 C                       2026-01-07 133           3         23                
#>  4 A                       2026-04-01 133           3         23                
#>  5 B                       2026-04-05 133           3         23                
#>  6 C                       2026-06-05 133           3         23                
#>  7 A                       2026-08-15 133           3         23                
#>  8 B                       2026-10-15 133           3         23                
#>  9 C                       2027-01-24 133           3         23                
#> 10 A                       2027-02-06 133           3         23                
#> # ℹ 20 more rows
#> # ℹ abbreviated name: ¹​CdStationMesureEauxSurface
#> # ℹ 4 more variables: CdRqAna <chr>, LqAna <dbl>, CdParametre <chr>,
#> #   RsAna <dbl>

ref_pestibase <- importe_ref_pestibase()
#> Warning: One or more parsing issues, call `problems()` on your data frame for details,
#> e.g.:
#>   dat <- vroom(...)
#>   problems(dat)

data <- data.frame(
   DatePrel = Sys.Date() + rep(sort(sample(1:500, 10)), 3),
   RsAna = round(runif(60, 0, 0.5), 2),
   LqAna = 0.1,
   CdStationMesureEauxSurface = c("A", "B", "C"),
   CdParametre = sample(ref_pestibase$liste_phytos_autorises$SA_CodeSANDRE,
                        size=10,
                        replace=TRUE),
   CdUniteMesure = "133"
 )
 data$CdRqAna <- ifelse(data$RsAna >= data$LqAna, "1", "10")

analyse_fonctions_pesticides(data, 
                             code_remarque="CdRqAna",
                             code_parametre="CdParametre",
                             liste_pesticides=ref_pestibase$liste_phytos_autorises)

ref_pestibase <- importe_ref_pestibase()
#> Warning: One or more parsing issues, call `problems()` on your data frame for details,
#> e.g.:
#>   dat <- vroom(...)
#>   problems(dat)

fiche_usages_pesticides(liste_cd_sandre=c("2009", "1221", "1882", "5817", "5617", "1678", "1859"),
                                        liste_pesticides=ref_pestibase$liste_pesticides,
                        legend_position = "top",
                        cultures_a_exclure=c("Vignes", "Fourrages"))

# Définition des stations
stations <- c("04123456", "04345678", "04987654", "04246810")

# Définition des pesticides courants en France
pesticides <- c(
  "Glyphosate", "Atrazine", "Métolachlore", "S-métolachlore", "Terbuthylazine",
  "Chlorotoluron", "Isoproturon", "Diuron", "Pendiméthaline"
)

# Génération du jeu de données
set.seed(123)  # Pour reproductibilité
df_fictif <- expand.grid(CdStationMesureEauxSurface = stations, NomParametre = pesticides) %>%
  dplyr::rowwise() %>%
  dplyr::mutate(
    RsAna = round(runif(1, 0, 1), 2),
    CdRqAna = ifelse(RsAna <= 0.02, "0", "1")
  ) %>%
  dplyr::ungroup()


heatmap_substances(
  df = df_fictif,
  titre = "Substances quantifiées > 0.1 µg/L - palette continue",
  seuil_bas = 0.1,
  seuil_haut = 2,
  chiffres_significatifs=2,
  type_palette="continue"
)

heatmap_substances(
  df = df_fictif,
  titre = "Substances quantifiées > 0.1 µg/L - palette discrete",
  seuil_bas = 0.1,
  seuil_haut = 2,
  chiffres_significatifs=2,
  type_palette="discrete"
)

if (interactive()) {
  config_path <- "C://workspace//gwilenalim//yaml//config.yml"
  if (file.exists(config_path)) {
    library(RPostgres)
    config <- yaml::read_yaml(config_path)
    con <- tryCatch({
      DBI::dbConnect(
        RPostgres::Postgres(),
        host = config$host,
        port = config$port,
        user = config$user,
        password = config$password,
        dbname = config$dbname
      )
    }, error = function(e) NULL)
  
    if (!is.null(con)) {
       triangle_sf <- sf::st_sf(
        geometry = sf::st_sfc(
          sf::st_polygon(list(rbind(
            c(-1.6794, 48.1147),  # Rennes
            c(-1.2100, 48.1230),  # Vitré
            c(-1.5025, 47.6833),  # Derval
            c(-1.6794, 48.1147)   # Retour à Rennes
          )))
        ),
        crs = 4326
      )
     
     g<- f_fond_de_carte(triangle_sf, zoom = 9) +
    ajoute_coursdeau(triangle_sf, con, size = 0.5)

      DBI::dbDisconnect(con)
      print(g)
    } else {
      message("Connexion à la base impossible, exemple non exécuté.")
    }
  } else {
    message("Fichier de configuration introuvable.")
  }
}

knitr::include_graphics(system.file("extdata", "graph_coursdeaubv.png", package = "valorisationQE"))