---
title: "Cr\u00e9ation des tables et des s\u00e9quences"
output: html_document
editor_options: 
  chunk_output_type: console
---

```{r development, include=FALSE}
library(testthat)
library(yaml)
library(glue)
library(DBI)
library(datalibaba)
```

```{r development-load}
# Load already included functions if relevant
pkgload::load_all(export_all = FALSE)
```

```{r config, eval=FALSE}
# Lire le fichier de configuration
config <- yaml::read_yaml("config.yml")

# Accéder aux valeurs pour version et last_year
version <- config$version
last_year <- config$last_year
```

# Présentation

Cette page contient les fonctions permettant :
- de créer en base la table `nitrates.nitrate_prelevement_analyse_version`,
- d'incrémenter la séquence correspondante au moment de l'import des données.

# Création de la table en base

## Création de la table des prélèvements et analyses et ajout des commentaires

```{r function-create_table_nitrate_prelevement_analyse, eval=FALSE}
#' Créer une table de prélèvements et analyses de nitrates
#'
#' @description Cette fonction crée une table dans une base de données PostgreSQL 
#' pour stocker les informations relatives aux prélèvements et analyses de 
#' nitrates, incluant les contraintes et les séquences nécessaires.
#'
#' @param version String. Version de la table (par exemple, 'v1').
#' @param last_year Integer. L'année la plus récente incluse dans les données de la table.
#'
#' @return String. Le script SQL utilisé pour créer la table.
#' @export
#' @importFrom datalibaba connect_to_db
create_table_nitrate_prelevement_analyse <- function(version, last_year) {
  # Établir une connexion à la base de données PostgreSQL
  connexion <- datalibaba::connect_to_db(db = "si_eau", user = "admin")
  
  # Génération de la date du jour
  date_now <- format(Sys.Date(), "%d/%m/%Y")
  
  # Liste des requêtes SQL
  sql_commands <- c(
    glue::glue("CREATE TABLE IF NOT EXISTS nitrates.nitrate_prelevement_analyse_{version} (
      code_prelevement_analyse serial NOT NULL,
      code_intervenant character varying(20),
      source character varying(10),
      code_reseau character varying(254),
      code_station character varying(10),
      date_prelevement date,
      heure_prelevement character varying(8),
      code_support integer,
      nature_eau character varying(3),
      id_usage character varying(3),
      id_prelevement_motif character varying(2),
      commentaire character varying(254),
      code_parametre bigint,
      code_fraction_analysee integer,
      date_analyse date,
      resultat_analyse double precision,
      code_remarque integer,
      limite_detection double precision,
      limite_quantification double precision,
      CONSTRAINT pk_nitrate_prelevement_analyse_{version} PRIMARY KEY (code_prelevement_analyse),
      CONSTRAINT fk_n_support FOREIGN KEY (code_support)
          REFERENCES sandre.n_support (code_support) MATCH SIMPLE
          ON UPDATE CASCADE ON DELETE SET DEFAULT,
      CONSTRAINT fk_parametre FOREIGN KEY (code_parametre)
          REFERENCES qualite_cours_d_eau.parametre (code_parametre) MATCH SIMPLE
          ON UPDATE CASCADE ON DELETE SET DEFAULT,
      CONSTRAINT fk_n_fraction_analysee FOREIGN KEY (code_fraction_analysee)
          REFERENCES sandre.n_fraction_analysee (code_fraction_analysee) MATCH SIMPLE
          ON UPDATE CASCADE ON DELETE SET DEFAULT,
      CONSTRAINT fk_n_remarque FOREIGN KEY (code_remarque)
          REFERENCES sandre.n_remarque (code_remarque) MATCH SIMPLE
          ON UPDATE NO ACTION ON DELETE NO ACTION
    ) WITH (OIDS=FALSE);"),
    
    glue::glue("ALTER TABLE nitrates.nitrate_prelevement_analyse_{version} OWNER TO adminpsql;"),
    
    glue::glue("GRANT ALL ON TABLE nitrates.nitrate_prelevement_analyse_{version} TO adminpsql;"),
    glue::glue("GRANT ALL ON TABLE nitrates.nitrate_prelevement_analyse_{version} TO writer_production;"),
    glue::glue("GRANT SELECT ON TABLE nitrates.nitrate_prelevement_analyse_{version} TO reader_production;"),
    
    glue::glue("COMMENT ON TABLE nitrates.nitrate_prelevement_analyse_{version} IS 
      'Table des prélèvements et analyses 2007-{last_year} (version {version} du {date_now})';"),
    
    glue::glue("CREATE SEQUENCE IF NOT EXISTS nitrates.nitrate_prelevement_analyse_{version}_code_prelevement_analyse_seq
      INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1;"),
    
    glue::glue("ALTER SEQUENCE nitrates.nitrate_prelevement_analyse_{version}_code_prelevement_analyse_seq
      OWNER TO adminpsql;")
  )
  
  # Ajout des commentaires sur chaque champ
  comments_sql <- c(
    glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.code_prelevement_analyse IS 'Identifiant du prélèvement';"),
    glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.code_intervenant IS 'Identifiant de l''intervenant';"),
    glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.source IS 'Source de la donnée';"),
    glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.code_reseau IS 'Identifiant du réseau';"),
    glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.code_station IS 'Identifiant de la station';"),
    glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.date_prelevement IS 'Date du prélèvement';"),
    glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.heure_prelevement IS 'Heure du prélèvement';"),
    glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.code_support IS 'Code du support de prélèvement';"),
    glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.nature_eau IS 'Nature de l''eau du prélèvement (ESO/ESU)';"),
    glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.id_usage IS 'Code de l''usage du prélèvement';"),
    glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.id_prelevement_motif IS 'Code du motif du prélèvement';"),
    glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.commentaire IS 'Commentaire';"),
    glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.code_parametre IS 'Identifiant du paramètre analysé';"),
    glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.code_fraction_analysee IS 'Identifiant de la fraction analysée';"),
    glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.date_analyse IS 'Date de l''analyse';"),
    glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.resultat_analyse IS 'Résultat de l''analyse';"),
    glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.code_remarque IS 'Code validant la donnée';"),
    glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.limite_detection IS 'Limite de détection';"),
    glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.limite_quantification IS 'Limite de quantification';")
  )
  
 # Exécuter chaque commande SQL
  for (sql in c(sql_commands, comments_sql)) {
    DBI::dbExecute(connexion, sql)
  }

  # Fermeture de la connexion à la base de données
  DBI::dbDisconnect(connexion)
  
  # Retourne toutes les commandes exécutées
  return(c(sql_commands, comments_sql))
}
```

Création de la table `nitrates.nitrate_prelevement_version` :
```{r create_create_table_nitrate_prelevement_analyse, eval=FALSE}
# Création du script SQL avec la version choisie
sql <- create_table_nitrate_prelevement_analyse(version, last_year)
```

# Incrémentation de la séquence

## Incrémentation de la table des prélèvements et des analyses

```{r function-add_code_prelevement_analyse, eval=FALSE} 
#' Ajouter une variable code_prelevement_analyse au dataframe
#'
#' @description Cette fonction ajoute une nouvelle variable 
#' `code_prelevement_analyse` au dataframe en utilisant une séquence 
#' PostgreSQL dynamique. La séquence est construite en fonction du 
#' paramètre `version` fourni.
#'
#' @param dataframe Un dataframe contenant les données sur lesquelles ajouter 
#' la variable `code_prelevement_analyse`.
#' @param version Une chaîne de caractères représentant la version de la 
#' séquence à utiliser.
#'
#' @return Un dataframe avec une nouvelle colonne `code_prelevement_analyse` 
#' contenant les valeurs de la séquence PostgreSQL.
#' 
#' @importFrom DBI dbGetQuery dbDisconnect
#' @importFrom dplyr mutate
#' @importFrom glue glue
#' @importFrom datalibaba connect_to_db
#' @export
add_code_prelevement_analyse <- function(dataframe, version) {
  # Établir une connexion à la base de données PostgreSQL
  connexion <- datalibaba::connect_to_db(db = "si_eau", user = "admin")
  
  # Créer la séquence correspondant à la table
  create_sequence <- glue::glue("CREATE SEQUENCE IF NOT EXISTS nitrates.nitrate_prelevement_analyse_{version}_code_prelevement_analyse_seq
      INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1;")
  
  # Exécuter le script dans la base de données
  DBI::dbExecute(connexion, create_sequence)
  
  # Construire le nom de la séquence
  sequence_name <- glue::glue("nitrates.nitrate_prelevement_analyse_{version}_code_prelevement_analyse_seq")
  
  # Initialiser une liste pour stocker les valeurs de la séquence
  code_prelevements_analyses <- c()
  
  # Pour chaque ligne du dataframe, obtenir une valeur de la séquence
  for (i in 1:nrow(dataframe)) {
    query <- glue::glue("SELECT nextval(\'{sequence_name}\') AS code_prelevement_analyse")
    result <- DBI::dbGetQuery(connexion, query)
    code_prelevements_analyses <- c(code_prelevements_analyses, result$code_prelevement_analyse)
  }
  
  # Ajouter la nouvelle variable au dataframe
  dataframe <- dataframe |>
    dplyr::mutate(code_prelevement_analyse = code_prelevements_analyses)
  
  # Fermer la connexion à la base de données
  DBI::dbDisconnect(connexion)
  
  return(dataframe)
}

```

La fonction est utilisée au moment d'importer les données des différentes 
sources dans la table `nitrates.nitrate_prelevement_analyse_version` :
```{r example_add_code_prelevement_analyse, eval=FALSE}
# Crée un dataframe fictif avec les colonnes nécessaires pour l'exemple
dataframe <- data.frame(
  id_prelevement = 1:5,
  autre_colonne = sample(letters, 5)
)
# Définir une version pour l'exemple
version <- "v1"

# Utiliser la fonction add_code_prelevement() avec la version souhaitée
dataframe <- add_code_prelevement_analyse(
  dataframe, version)

```

```{r development-skeleton-dir, eval=FALSE}
# Créer de l'arborescence et des fichiers du template
usethis::use_rmarkdown_template(
  template_name = "Cr\u00e9ation de la table et de la s\u00e9quence",
  template_dir = "creation-de-la-table-et-de-la-sequence",
  template_description = "Cr\u00e9ation de la table et de la s\u00e9quence",
  template_create_dir = TRUE
)
```

```{r development-skeleton-copy, eval=FALSE}
# Définir les chemins source et destination
source_file <- "dev/flat_create_tables_sequences.Rmd"
destination_dir <- "inst/rmarkdown/templates/creation-de-la-table-et-de-la-sequence/skeleton"
destination_file <- file.path(destination_dir, "skeleton.Rmd")

# Copier et renommer le fichier
file.copy(from = source_file, to = destination_file, overwrite = TRUE)
message("File copied and renamed successfully.")

```

```{r development-inflate, eval=FALSE}
# Run but keep eval=FALSE to avoid infinite loop
# Execute in the console directly
fusen::inflate(flat_file = "dev/flat_create_table_sequence.Rmd", vignette_name = "creation-de-la-table-et-de-la-sequence")
```