
library(dplyr)

ocsge <- propre.artificialisation::ocsge
observatoire_artificialisation <- propre.artificialisation::observatoire_artificialisation
stock_artificialise <- propre.artificialisation::stock_artificialise
population_legale <- propre.artificialisation::population_legale

# parametres
code_reg <- "52"
millesime_ocsge <- 2016
millesime_obs_artif <- 2020
millesime_population <- 2019
millesime_stock_artif <- 2020
millesime_debut <- 2016  #millesime debut stock artificialisation (fixe)


# carte 1_3 (source OCSGE) ---------------
data <- ocsge %>%
  dplyr::filter(grepl(millesime_ocsge, .data$date)) %>%
  COGiter::filtrer_cog(reg = code_reg) %>%
  dplyr::mutate(espace_artificialise_en_hectares=round(.data$espace_artificialise / 10000,0))

xlsx::write.xlsx(data,file = "inst/tableurs/ocsge_1_3.xlsx")


# graphe 1_6 (source OCSGE) -------------
ocsge2 <- ocsge %>%
  dplyr::mutate(date = lubridate::year(lubridate::as_date(.data$date)),
                espace_naturel = .data$autre_surface_naturelle + .data$surface_en_eau + .data$surface_naturelle_boisee) %>%
  COGiter::filtrer_cog(reg = code_reg) %>%
  dplyr::filter(.data$date == millesime_ocsge) %>%
  dplyr::select(.data$TypeZone, .data$Zone, .data$CodeZone,
                .data$espace_naturel,
                .data$espace_agricole, .data$espace_artificialise)
data <- ocsge2 %>%
  tidyr::pivot_longer(.data$espace_naturel:.data$espace_artificialise,
                      names_to = "variable",
                      values_to = "valeur") %>%
  dplyr::group_by(.data$CodeZone) %>%
  dplyr::mutate(variable = .data$variable,
                valeur = .data$valeur / 10000,
                taux = .data$valeur / sum(.data$valeur, na.rm = T) * 100) %>%
  dplyr::mutate(variable = forcats::fct_relevel(variable,"espace_naturel","espace_agricole","espace_artificialise")) %>%
  dplyr::ungroup()
rm(ocsge2)

xlsx::write.xlsx(data,file = "inst/tableurs/ocsge_1_6_et_1_7.xlsx")


# source observatoire artificialisation 2_2_et_2_3_2_4_et_2_7---------
# donnees observatoire artificialisation completes sur 10 ans
# communes et epci region
observatoire_artificialisation_0a <- observatoire_artificialisation %>%
  COGiter::filtrer_cog(reg = code_reg) %>%
  dplyr::filter(.data$TypeZone == "Communes" | .data$TypeZone == "Epci")
 # autres zones france
observatoire_artificialisation_0b <- observatoire_artificialisation %>%
  dplyr::filter(.data$TypeZone != "Communes" , .data$TypeZone != "Epci")
# regroupement des donnees
observatoire_artificialisation_0 <- dplyr::bind_rows(observatoire_artificialisation_0a,observatoire_artificialisation_0b)   %>%
  dplyr::mutate(date = as.character(lubridate::year(.data$date - 1))) %>%
  dplyr::filter(.data$date < millesime_obs_artif, .data$date > millesime_obs_artif - 11)  # conserve les 10 derniers millesimes
rm(observatoire_artificialisation_0a,observatoire_artificialisation_0b)

xlsx::write.xlsx(observatoire_artificialisation_0,file = "inst/tableurs/obs_artif_flux_par_annee_2_4.xlsx")

# somme des flux 10 ans
data <- observatoire_artificialisation_0 %>%
  dplyr::select(.data$CodeZone, .data$TypeZone, .data$Zone, .data$date, .data$flux_naf_artificialisation_total) %>%
  dplyr::mutate(flux_naf_artificialisation_total = .data$flux_naf_artificialisation_total / 10000) %>%
  dplyr::arrange(.data$CodeZone) %>%
  dplyr::group_by(.data$CodeZone, .data$TypeZone, .data$Zone) %>%
  dplyr::summarise(`surf artificialisees 10 ans` = sum(.data$flux_naf_artificialisation_total, na.rm = T)) %>%
  ungroup()

xlsx::write.xlsx(data,file = "inst/tableurs/obs_artif_flux_10_ans_2_2_et_2_3_et_2_7.xlsx")
rm(observatoire_artificialisation_0)


# graphe 2_5 (source observatoire artificialisation) ---------

data_dep_pdl <- observatoire_artificialisation %>%
  COGiter::filtrer_cog(reg = code_reg) %>%
  dplyr::filter(.data$TypeZone == "D\u00e9partements") %>%
  dplyr::arrange(.data$CodeZone) %>%
  dplyr::mutate(Zone=factor(.data$Zone) %>% forcats::fct_inorder())%>%
  dplyr::select(.data$TypeZone,
                .data$CodeZone,
                .data$Zone,
                .data$date,
                .data$flux_naf_artificialisation_activite,
                .data$flux_naf_artificialisation_habitation,
                .data$flux_naf_artificialisation_mixte) %>%
  dplyr::mutate(Zone = forcats::fct_drop(.data$Zone)) %>%
  dplyr::arrange(.data$Zone) %>%
  dplyr::mutate(date = as.character(lubridate::year(.data$date - 1))) %>%
  dplyr::filter(.data$date < millesime_obs_artif, .data$date > millesime_obs_artif - 11) %>% # conserve les 10 derniers millesimes
  tidyr::gather(variable, valeur, .data$flux_naf_artificialisation_activite:.data$flux_naf_artificialisation_mixte) %>%
  dplyr::mutate(valeur = .data$valeur / 10000) %>%
  dplyr::mutate(
    variable = replace(.data$variable, .data$variable == "flux_naf_artificialisation_activite", "activit\u00e9"),
    variable = replace(.data$variable, .data$variable == "flux_naf_artificialisation_habitation", "habitation"),
    variable = replace(.data$variable, .data$variable == "flux_naf_artificialisation_mixte", "mixte"))

data_com_pdl <- observatoire_artificialisation %>%
  COGiter::filtrer_cog(reg = code_reg) %>%
  dplyr::filter(.data$TypeZone == "Communes") %>%
  dplyr::arrange(.data$CodeZone) %>%
  dplyr::mutate(Zone=factor(.data$Zone) %>% forcats::fct_inorder())%>%
  dplyr::select(.data$TypeZone,
                .data$CodeZone,
                .data$Zone,
                .data$date,
                .data$flux_naf_artificialisation_activite,
                .data$flux_naf_artificialisation_habitation,
                .data$flux_naf_artificialisation_mixte) %>%
  dplyr::mutate(Zone = forcats::fct_drop(.data$Zone)) %>%
  dplyr::arrange(.data$Zone) %>%
  dplyr::mutate(date = as.character(lubridate::year(.data$date - 1))) %>%
  dplyr::filter(.data$date < millesime_obs_artif, .data$date > millesime_obs_artif - 11) %>% # conserve les 10 derniers millesimes
  tidyr::gather(variable, valeur, .data$flux_naf_artificialisation_activite:.data$flux_naf_artificialisation_mixte) %>%
  dplyr::mutate(valeur = .data$valeur / 10000) %>%
  dplyr::mutate(
    variable = replace(.data$variable, .data$variable == "flux_naf_artificialisation_activite", "activit\u00e9"),
    variable = replace(.data$variable, .data$variable == "flux_naf_artificialisation_habitation", "habitation"),
    variable = replace(.data$variable, .data$variable == "flux_naf_artificialisation_mixte", "mixte"))

data <- dplyr::bind_rows(data_com_pdl,data_dep_pdl)

rm(data_com_pdl,data_dep_pdl)

xlsx::write.xlsx(data,file = "inst/tableurs/obs_artif_2_5.xlsx")


# graphe 2_6 (source stock artificialisé) ------------
stock_artificialise_0 <- COGiter::filtrer_cog(stock_artificialise,
                                              reg = code_reg)
xlsx::write.xlsx(data,file = "inst/tableurs/stock_artif_origine.xlsx")

data <- stock_artificialise_0 %>%
  # dplyr::filter(.data$TypeZone == "Communes") %>%
  dplyr::mutate(date = lubridate::year(.data$date)) %>%
  dplyr::filter(.data$date == millesime_stock_artif | .data$date == millesime_debut) %>%
  dplyr::select (-.data$surf_cadastree) %>%
  dplyr::group_by(.data$CodeZone) %>%
  dplyr::mutate(evolution = (.data$surface_artificialisee - dplyr::lag(.data$surface_artificialisee))*100/ dplyr::lag(.data$surface_artificialisee)) %>%
  dplyr::ungroup() %>%
  dplyr::arrange(.data$CodeZone)
rm(stock_artificialise_0)

xlsx::write.xlsx(data,file = "inst/tableurs/stock_artif_evolution_2_6_et_2_8_et_3_1.xlsx")


# graphe 3_1 (sources population légale et stock artificialisé) ---------
millesime_population_debut <- millesime_population - (millesime_stock_artif - millesime_debut)

evol_popul <- population_legale %>%
  dplyr::mutate(date = lubridate::year(.data$date)) %>%
  COGiter::filtrer_cog(reg = code_reg) %>%
  dplyr::filter(.data$date == millesime_population | .data$date == millesime_population_debut) %>%
  dplyr::arrange(.data$TypeZone, .data$Zone, .data$CodeZone, .data$date) %>%
  dplyr::group_by(.data$TypeZone, .data$Zone, .data$CodeZone) %>%
  dplyr::mutate(evolution_population = round(.data$population_municipale * 100 / dplyr::lag(.data$population_municipale) - 100, 1)) %>%
  dplyr::ungroup() %>%
  dplyr::arrange(.data$CodeZone)

xlsx::write.xlsx(evol_popul,file = "inst/tableurs/population_evolution_3_1.xlsx")

rm(evol_popul)

# graphe 3_3 (sources population légale et stock artificialisé) -----------------
millesime_population_debut <- millesime_population - (millesime_stock_artif - millesime_debut)

# seuils population
pop_3 <- 40000
pop_2 <- 10000
pop_1 <- 2000

# population du millesime
population <- population_legale %>%
  dplyr::mutate(date=lubridate::year(.data$date)) %>%
  COGiter::filtrer_cog(reg = code_reg) %>%
  dplyr::filter(.data$date == millesime_population,
                .data$TypeZone =="Communes") %>%
  dplyr::mutate(seuil_pop = dplyr::case_when(
    .data$population_municipale > pop_3  ~ glue::glue("communes de plus\nde ",pop_3," habitants"),
    .data$population_municipale > pop_2  ~ glue::glue("communes entre\n",pop_2," et ",pop_3, " habitants"),
    .data$population_municipale > pop_1  ~ glue::glue("communes entre\n",pop_1," et ",pop_2, " habitants"),
    .data$population_municipale > 0  ~ glue::glue("communes de moins\nde ",pop_1," habitants"),
    TRUE ~ "")
  ) %>%
  dplyr::mutate(seuil_code = dplyr::case_when(
    .data$population_municipale > pop_3  ~ "D",
    .data$population_municipale > pop_2  ~ "C",
    .data$population_municipale > pop_1  ~ "B",
    .data$population_municipale > 0  ~ "A",
    TRUE ~ "")
  ) %>%
  dplyr::rename ("population_n"="population_municipale") %>%
  dplyr::select (-.data$date)

# table des seuils
seuil_population <- population %>%
  dplyr::select (.data$CodeZone,.data$seuil_pop)


# preparation des donnees evolution_artificialisation

evol_artif <- stock_artificialise %>%
  dplyr::mutate(date = lubridate::year(.data$date)) %>%
  COGiter::filtrer_cog(reg = code_reg) %>%
  dplyr::filter(.data$TypeZone == "Communes",
                .data$date == millesime_stock_artif | .data$date == millesime_debut) %>%
  dplyr::select (-.data$surf_cadastree)

evol_artif1 <- evol_artif %>%
  dplyr::left_join(seuil_population) %>%
  dplyr::mutate(dep = substr(.data$CodeZone,1,2)) %>%
  dplyr::filter (.data$date == millesime_stock_artif) %>%
  dplyr::group_by(.data$dep, .data$seuil_pop, .data$date) %>%
  dplyr::summarise(surface_artificialisee = sum(.data$surface_artificialisee, na.rm = T)) %>%
  dplyr::ungroup() %>%
  dplyr::left_join(COGiter::departements %>%
                     dplyr::filter (.data$REG == code_reg) %>%
                     dplyr::select(.data$DEP, .data$NOM_DEP),
                   by=c("dep"= "DEP")) %>%
  dplyr::rename("Zone" = "NOM_DEP") %>%
  dplyr::select(-.data$dep)

evol_artif1_2 <- evol_artif1 %>%
  dplyr::group_by(.data$seuil_pop,.data$date) %>%
  dplyr::summarise(surface_artificialisee = sum(.data$surface_artificialisee, na.rm = T)) %>%
  dplyr::ungroup() %>%
  dplyr::mutate(Zone = COGiter::regions %>%
                  dplyr::filter (.data$REG == code_reg) %>%
                  dplyr::pull(.data$NOM_REG))

evol_artif1 <-dplyr::bind_rows(evol_artif1,evol_artif1_2) %>%
  dplyr::select(.data$seuil_pop, .data$Zone, .data$date, .data$surface_artificialisee)

evol_artif2 <- evol_artif %>%
  dplyr::left_join(seuil_population) %>%
  dplyr::mutate(dep = substr(.data$CodeZone,1,2)) %>%
  dplyr::filter (.data$date == millesime_debut) %>%
  dplyr::group_by(.data$dep, .data$seuil_pop, .data$date) %>%
  dplyr::summarise(surface_artificialisee = sum(.data$surface_artificialisee, na.rm = T)) %>%
  dplyr::ungroup() %>%
  dplyr::left_join(COGiter::departements %>%
                     dplyr::filter (.data$REG == code_reg) %>%
                     dplyr::select(.data$DEP, .data$NOM_DEP),
                   by=c("dep"= "DEP")) %>%
  dplyr::rename("Zone" = "NOM_DEP") %>%
  dplyr::select(-.data$dep)

evol_artif2_2 <- evol_artif2 %>%
  dplyr::group_by(.data$seuil_pop, .data$date) %>%
  dplyr::summarise(surface_artificialisee = sum(.data$surface_artificialisee, na.rm = T)) %>%
  dplyr::ungroup() %>%
  dplyr::mutate(Zone = COGiter::regions %>%
                  dplyr::filter (.data$REG == code_reg) %>%
                  dplyr::pull(.data$NOM_REG))


evol_artif2 <-dplyr::bind_rows(evol_artif2,evol_artif2_2) %>%
  dplyr::select(.data$seuil_pop, .data$Zone, .data$date, .data$surface_artificialisee)

evol_artif3 <- rbind(evol_artif1,evol_artif2) %>%  #regroupement des annees
  dplyr::arrange(.data$seuil_pop, .data$Zone, .data$date) %>%
  dplyr::group_by(.data$seuil_pop, .data$Zone) %>%
  dplyr::mutate(evolution_artificialisation = round(.data$surface_artificialisee * 100 / dplyr::lag(.data$surface_artificialisee) - 100, 1)) %>%
  dplyr::ungroup() %>%
  dplyr::filter(.data$date == millesime_stock_artif) %>%
  dplyr::select(.data$seuil_pop, .data$Zone, .data$evolution_artificialisation)

# preparation des donnees evol_population

evol_popul <- population_legale %>%
  dplyr::mutate(date = lubridate::year(.data$date)) %>%
  COGiter::filtrer_cog(reg = code_reg) %>%
  dplyr::filter(.data$TypeZone == "Communes",
                .data$date == millesime_population | .data$date == millesime_population_debut)

evol_popul1 <- evol_popul %>%
  dplyr::left_join(seuil_population) %>%
  dplyr::mutate(dep = substr(.data$CodeZone,1,2)) %>%
  dplyr::filter (.data$date == millesime_population) %>%
  dplyr::group_by(.data$dep, .data$seuil_pop, .data$date) %>%
  dplyr::summarise(population_municipale = sum(.data$population_municipale, na.rm = T)) %>%
  dplyr::ungroup() %>%
  dplyr::left_join(COGiter::departements %>%
                     dplyr::filter (.data$REG == code_reg) %>%
                     dplyr::select(.data$DEP, .data$NOM_DEP),
                   by=c("dep"= "DEP")) %>%
  dplyr::rename("Zone" = "NOM_DEP") %>%
  dplyr::select(-.data$dep)

evol_popul1_2 <- evol_popul1 %>%
  dplyr::group_by(.data$seuil_pop, .data$date) %>%
  dplyr::summarise(population_municipale = sum(.data$population_municipale, na.rm = T)) %>%
  dplyr::ungroup() %>%
  dplyr::mutate(Zone = COGiter::regions %>%
                  dplyr::filter (.data$REG == code_reg) %>%
                  dplyr::pull(.data$NOM_REG))

evol_popul1 <-dplyr::bind_rows(evol_popul1,evol_popul1_2) %>%
  dplyr::select(.data$seuil_pop, .data$Zone, .data$date, .data$population_municipale)

evol_popul2 <- evol_popul %>%
  dplyr::left_join(seuil_population) %>%
  dplyr::mutate(dep = substr(.data$CodeZone,1,2)) %>%
  dplyr::filter (.data$date == millesime_population_debut) %>%
  dplyr::group_by(.data$dep, .data$seuil_pop, .data$date) %>%
  dplyr::summarise(population_municipale = sum(.data$population_municipale, na.rm = T)) %>%
  dplyr::ungroup() %>%
  dplyr::left_join(COGiter::departements %>%
                     dplyr::filter (.data$REG == code_reg) %>%
                     dplyr::select(.data$DEP, .data$NOM_DEP),
                   by=c("dep"= "DEP")) %>%
  dplyr::rename("Zone" = "NOM_DEP") %>%
  dplyr::select(-.data$dep)

evol_popul2_2 <- evol_popul2 %>%
  dplyr::group_by(.data$seuil_pop, .data$date) %>%
  dplyr::summarise(population_municipale = sum(.data$population_municipale, na.rm = T)) %>%
  dplyr::ungroup() %>%
  dplyr::mutate(Zone = COGiter::regions %>%
                  dplyr::filter (.data$REG == code_reg) %>%
                  dplyr::pull(.data$NOM_REG))

evol_popul2 <-dplyr::bind_rows(evol_popul2,evol_popul2_2) %>%
  dplyr::select(.data$seuil_pop, .data$Zone, .data$date, .data$population_municipale)

evol_popul3 <- rbind(evol_popul1,evol_popul2) %>%  #regroupement des annees
  dplyr::arrange(.data$seuil_pop, .data$Zone, .data$date) %>%
  dplyr::group_by(.data$seuil_pop, .data$Zone) %>%
  dplyr::mutate(evolution_population = round(.data$population_municipale * 100 / dplyr::lag(.data$population_municipale) - 100, 1)) %>%
  dplyr::ungroup() %>%
  dplyr::filter(.data$date == millesime_population) %>%
  dplyr::select(.data$seuil_pop, .data$Zone, .data$evolution_population)

data <- evol_artif3 %>%
  dplyr::full_join(evol_popul3) %>%
  dplyr::mutate(`Seuil de population` = factor(.data$seuil_pop, levels = c(glue::glue("communes de moins\nde ",pop_1," habitants"),
                                                                           glue::glue("communes entre\n",pop_1," et ",pop_2, " habitants"),
                                                                           glue::glue("communes entre\n",pop_2," et ",pop_3, " habitants"),
                                                                           glue::glue("communes de plus\nde ",pop_3," habitants")))) %>%
  dplyr::select(`Seuil de population`,dplyr::everything(),-seuil_pop)

rm(population,seuil_population)
rm(evol_artif,evol_artif1,evol_artif1_2,evol_artif2,evol_artif2_2,evol_artif3)
rm(evol_popul,evol_popul1,evol_popul1_2,evol_popul2,evol_popul2_2,evol_popul3)

xlsx::write.xlsx(data,file = "inst/tableurs/stock_artif_population_3_3.xlsx")


# graphe 3_4 (sources population légale et stock artificialisé) ------------
# code_reg <- "52"
# millesime_population <- 2019
# millesime_stock_artif <- 2020
# millesime_debut <- 2016
millesime_debut_population <- millesime_population - (millesime_stock_artif - millesime_debut)
millesime_fin_population <- millesime_population

stock_depart <- stock_artificialise %>%
  dplyr::mutate(date=lubridate::year(.data$date)) %>%
  COGiter::filtrer_cog(reg = code_reg) %>%
  dplyr::filter(.data$date == millesime_debut) %>%
  dplyr::mutate(surface_artificialisee = .data$surface_artificialisee) %>%
  dplyr::select (-.data$surf_cadastree,-.data$date) %>%
  dplyr::rename ("stock_depart"="surface_artificialisee")

# preparation des flux
flux <- stock_artificialise %>%
  dplyr::mutate(date=lubridate::year(.data$date)) %>%
  COGiter::filtrer_cog(reg = code_reg) %>%
  dplyr::filter((.data$date == millesime_debut) |(.data$date == millesime_stock_artif)) %>%
  dplyr::select (-.data$surf_cadastree) %>%
  dplyr::arrange(.data$TypeZone, .data$Zone, .data$CodeZone, .data$date) %>%
  dplyr::group_by(.data$TypeZone, .data$Zone, .data$CodeZone) %>%
  dplyr::mutate(flux_artificialisation = .data$surface_artificialisee - dplyr::lag(.data$surface_artificialisee) ) %>%
  dplyr::ungroup()  %>%
  dplyr::filter(.data$date == millesime_stock_artif) %>%
  dplyr::select (-.data$date , -.data$surface_artificialisee)

# preparation des donnees population
evol_popul <- population_legale %>%
  dplyr::mutate(date=lubridate::year(.data$date)) %>%
  COGiter::filtrer_cog(reg = code_reg) %>%
  dplyr::filter(.data$date == millesime_debut_population | .data$date == millesime_fin_population)

popul_debut <- evol_popul %>%
  dplyr::filter(.data$date == millesime_debut_population) %>%
  dplyr::rename ("population_debut"="population_municipale") %>%
  dplyr::select (-.data$date)

evol_popul <- evol_popul %>%
  dplyr::filter(.data$date %in% c(millesime_debut_population,millesime_fin_population)) %>%
  dplyr::arrange(.data$date) %>%
  dplyr::group_by(.data$TypeZone,.data$Zone,.data$CodeZone) %>%
  dplyr::mutate(evolution_population=.data$population_municipale - dplyr::lag(.data$population_municipale))  %>%
  dplyr::ungroup() %>%
  dplyr::filter(.data$date %in% c(millesime_fin_population)) %>%
  dplyr::select(.data$TypeZone,.data$Zone,.data$CodeZone,.data$evolution_population)

data <-  stock_depart %>%
  dplyr::left_join(flux) %>%
  dplyr::left_join(popul_debut) %>%
  dplyr::left_join(evol_popul) %>%
  dplyr::mutate(surf_artif_par_hab_an=.data$stock_depart / .data$population_debut *10000 ) %>%
  dplyr::mutate(surf_artif_par_nouv_hab =.data$flux_artificialisation / .data$evolution_population *10000) %>%
  dplyr::select (.data$TypeZone,.data$CodeZone,.data$Zone,.data$surf_artif_par_hab_an,.data$surf_artif_par_nouv_hab) %>%
  tidyr::gather(variable, valeur, 4:5) %>%
  # dplyr::arrange(desc(.data$TypeZone),desc(.data$Zone)) %>%
  dplyr::mutate(variable=forcats::fct_relevel(.data$variable,"surf_artif_par_nouv_hab","surf_artif_par_hab_an")) %>%
  dplyr::mutate(Zone = forcats::fct_drop(.data$Zone) %>%
                  forcats::fct_inorder()) %>%
  dplyr::mutate(valeur = ifelse(.data$valeur<0, 0, valeur))

rm(stock_depart,flux,evol_popul,popul_debut)

xlsx::write.xlsx(data,file = "inst/tableurs/stock_artif_population_3_4_et_4_2.xlsx")
