library(magrittr)
library(haven)
library(readxl)
library(lubridate)
library(COGiter)
library(purrr)
library(dplyr)
library(tidyr)
library(forcats)
library(lubridate)
library(stringr)
library(dtplyr)
options(scipen = 999)
library(RPostgreSQL)
library(datalibaba)

# ma_region <- "52"
# mes_departements <- list_dep_in_reg(ma_region)

# Recherche du dernier millesime disponible
fichiers <- list.files("X:/SCTE/CSD/DONNEES_CONFIDENTIELLES/_niveau_2/Conjoncture/ECLN/Publication"
                       ,include.dirs = FALSE,full.names = FALSE, pattern = "stat_info")
# fichiers <- list.files('data-raw',include.dirs = FALSE,full.names = FALSE, pattern = "stat_info")
millesime <- fichiers %>%
  substr(26,31) %>%
  max()
# fichier_donnees <- paste0("data-raw/stat_info_data_regionaux_",millesime,".csv")
# fichier_calage <- paste0("data-raw/cale_ventile_",millesime,".xls")
fichier_donnees <- paste0("X:/SCTE/CSD/DONNEES_CONFIDENTIELLES/_niveau_2/Conjoncture/ECLN/Publication/stat_info_data_regionaux_",millesime,"_.csv")
fichier_calage <- paste0("X:/SCTE/CSD/DONNEES_CONFIDENTIELLES/_niveau_2/Conjoncture/ECLN/Publication/cale_ventile_",millesime,".xls")

# 1. Récupération des données
# ------------------------------------------------------------------------------------------------------
# Données envoyées par mail ou déposées sur le serveur RStudio (/nfs/data/partage-ECLN/ECLN/AAAATN/) sont déposées dans le répertoire des données confidentielles avec accès restreint

# Données ECLN brutes non secrétisées - Attention séparateur ; et décimal .
ECLN <- read.csv2(fichier_donnees, header = T, sep = ";", dec = ".") %>%
  mutate(
    nb_lgt = nb_lgt_t1 + nb_lgt_t2 + nb_lgt_t3 + nb_lgt_t4 + nb_lgt_t5 + nb_lgt_t6,
    nb_lgt_inv = nb_lgt_inv_t1 + nb_lgt_inv_t2 + nb_lgt_inv_t3 + nb_lgt_inv_t4 + nb_lgt_inv_t5 + nb_lgt_inv_t6,
    nb_lgt_hors_inv = nb_lgt - nb_lgt_inv,
    id_type_lgt = as.character(id_type_lgt),
    id_nature_projet = as.character(id_nature_projet),
    siren = as.character(siren)
  )
ECLN2 <- ECLN %>%
  passer_au_cog_a_jour(code_commune = code_insee, aggrege = F, garder = T) %>%
  filter(trimestre_enquete >= 201701)

# Fichier de calage des ventes ventilés au niveau dep et reg - Seules les ventes du dernier trimestre sont calées
cale_reg_dep <- read_excel(fichier_calage)

rm(fichier_calage,fichier_donnees,fichiers)

# 2. Construction de la table des indicateurs communaux par type de logement et nature du projet
# Dans le projet initial, seuls les indicateurs par type de logement étaient construits
# Pour pouvoir utiliser, la nouvelle variable construction sur existant, on construit à l'identique des indicateurs sur la distinction de la nature du projet
# La table finale sera une juxtaposition des indicateurs par type de logement et par nature du projet

# ---------------------------------------------------------------------------------------------------------------------------------------------------
# 2.1 Construction de la table des indicateurs par type de logement id_type_lgt (1=individuel/2=collectif)

# On calcule pour chaque niveau de zonage, hormis les communes (code_insee-> DEPCOM), trimestre de mises en vente (trim_mev->Periode) et type de logement (id_type_lgt),
# - le nombre de mises en vente (mev_t)
# - le nombre de logements réservés à la vente (nb_resa)
# - le prix total des ventes (prix)
# - la Surface totale (surface)
# - encours de logements proposés à la vente (stock_fin)

# 2.1.a tableau des données
indic_typ <- ECLN2 %>%
  select(DEPCOM, Periode = trim_mev, id_type_lgt, mev_t) %>%
  as_tibble() %>%
  filter(Periode >= 201701) %>%
  group_by(id_type_lgt, DEPCOM, Periode) %>%
  summarise(mev_t = sum(mev_t, na.rm = T)) %>%
  ungroup() %>%
  full_join ( ECLN %>%
                select(DEPCOM = code_insee, Periode = trimestre_enquete , id_type_lgt, nb_resa, nb_lgt_inv_t1, nb_lgt_inv_t2, nb_lgt_inv_t3, nb_lgt_inv_t4, nb_lgt_inv_t5, nb_lgt_inv_t6,
                       surface, prix,
                       stock_fin ) %>%
                filter(Periode >= 201701) %>%
                group_by(id_type_lgt, DEPCOM, Periode) %>%
                summarise(
                  nb_resa = sum(nb_resa, na.rm = T),
                  stock_fin = sum(stock_fin, na.rm = T),
                  prix = sum(prix, na.rm = T),
                  surface = sum(surface, na.rm = T)
                ) %>%
                ungroup()) %>%
  relocate(DEPCOM)%>%
  complete(DEPCOM,id_type_lgt,Periode,fill = list(mev_t= 0, nb_resa = 0,stock_fin=0, prix = 0, surface = 0)) %>%
  mutate_if(is.character, as.factor) %>%
  cogifier(code_commune = DEPCOM,na.rm = TRUE) %>%
  filter (TypeZone != "Communes") %>%
  mutate_if(is.character, as.factor)



# calcul total (individuel + collectif)
indic_typ_tot <- indic_typ %>%
  group_by(TypeZone,Zone,CodeZone,Periode) %>%
  summarise(
    mev_t = sum(mev_t, na.rm = T),
    nb_resa = sum(nb_resa, na.rm = T),
    stock_fin = sum(stock_fin, na.rm = T),
    prix = sum(prix, na.rm = T),
    surface = sum(surface, na.rm = T)
  ) %>%
  ungroup() %>%
mutate(id_type_lgt  = "3",
       id_type_lgt = as.factor(id_type_lgt))

# regroupement individuel, collectif et total
indic_typ <-bind_rows(indic_typ,indic_typ_tot)


# creation table des EPCI non présents dans ECLN
# zonage fictif
v1 <- indic_typ %>% select(Periode) %>% pull() %>% unique()  #liste des périodes existantes
a0a <- data.frame(Periode=v1) %>%
  mutate(CodeZone = as.factor("xxx"),
         id_type_lgt = as.factor("1"),
         Periode = as.character(Periode),
         mev_t = 0,
         nb_resa = 0,
         stock_fin = 0,
         prix = 0,
         surface = 0) %>%
  relocate (2,3)
a0b <- data.frame(
         CodeZone = as.factor("xxx"),
         id_type_lgt = as.factor("3"),
         Periode = "2017T1",
         mev_t = 0,
         nb_resa = 0,
         stock_fin = 0,
         prix = 0,
         surface = 0)
a0 <- bind_rows(a0a,a0b)  #creation d'une table fictive avec toutes les périodes et tous codes type logement
a1 <- epci %>%   # liste des epci non présents dans la table
  select(CodeZone = EPCI, Zone = NOM_EPCI) %>%
  left_join(indic_typ %>% select(CodeZone,TypeZone) %>% unique() %>% filter(TypeZone =="Epci")) %>%
  filter(is.na(TypeZone)) %>%
  select(-TypeZone)
a1b <- a1 %>%  #table des epci non presents dans la table avec valeur 0 pour tous
  select(-Zone) %>%
  mutate(CodeZone = forcats::fct_drop(CodeZone)) %>%
  mutate(id_type_lgt = as.factor("2"),
         Periode = "2017T1",
         mev_t = 0,
         nb_resa = 0,
         stock_fin = 0,
         prix = 0,
         surface = 0)
a2 <- bind_rows(a0,a1b) %>%  # regroupement table fictive et des epci non présents et complete
  complete(CodeZone,id_type_lgt,Periode,fill = list(mev_t= 0, nb_resa = 0,stock_fin=0, prix = 0, surface = 0)) %>%
  filter(CodeZone != "xxx") %>%
  mutate(Periode = as.factor(Periode),
         TypeZone = as.factor("Epci"),
         )%>%
  left_join(a1) %>%
  mutate(CodeZone = forcats::fct_drop(CodeZone),
         Zone = forcats::fct_drop(Zone))

indic_typ <- bind_rows(indic_typ,a2) %>%  #regroupement des tables pour avoir tous les zonages et tous les cas de figure
  mutate(CodeZone = forcats::fct_drop(CodeZone),
         Zone = forcats::fct_drop(Zone))
rm(a0a,a0,a0b,a1,a1b,a2)

# 2.1.b calculs par rapport au nombre de promoteurs

# Pour chaque type de zonage, on modifie la table individuelle en supprimant la colonne code commune et
#   en ne gardant que le nom du zonage (epci, dept, region, france):

# table pour les epci
a_epci <- ECLN2 %>%
  select(CodeZone = EPCI, Zone= NOM_EPCI, Periode = trimestre_enquete, id_type_lgt, nb_resa,
         surface, prix, stock_fin,siren)
# table pour les departements
a_dept <-  ECLN2 %>%
  select(CodeZone = DEP, Zone= NOM_DEP, Periode = trimestre_enquete, id_type_lgt, nb_resa,
         surface, prix, stock_fin,siren)
# table pour les régions
a_reg <-  ECLN2 %>%
  select(CodeZone = REG, Zone= NOM_REG, Periode = trimestre_enquete, id_type_lgt, nb_resa,
         surface, prix, stock_fin,siren)
# table pour la France
a_france <-  ECLN2 %>%
  mutate( CodeZone = "FRMETRO", Zone = "France Métropolitaine") %>%
  select(CodeZone, Zone, Periode = trimestre_enquete, id_type_lgt, nb_resa,
         surface, prix, stock_fin,siren)

# calcul pour ind et coll--------------------
# fonction pour les reservés ind et coll
regroup_par_typ_resa <- function(x,TypeZone){
  nb_prom_resa <- x %>%
    filter(nb_resa != 0) %>%
    select(CodeZone, Zone,Periode,id_type_lgt,siren) %>%
    distinct() %>%
    group_by(CodeZone, Zone, Periode,id_type_lgt) %>%
    summarise(nb_promoteurs_resa = n()) %>%
    ungroup()
  part_resa <- x %>%
    filter(nb_resa != 0) %>%
    group_by(CodeZone, Zone,Periode,id_type_lgt,siren) %>%
    summarise(total_resa = sum(nb_resa, na.rm = TRUE)) %>%
    ungroup() %>%
    group_by(CodeZone, Zone, Periode, id_type_lgt) %>%
    mutate(part_res = total_resa / sum(total_resa)) %>%
    select(-total_resa,-siren) %>%
    summarise(part_max_resa = max(part_res, na.rm = TRUE)) %>%
    ungroup()
  nb_prom_resa %>%
    full_join (part_resa) %>%
    mutate(TypeZone = TypeZone)

}

# fonction pour les stocks ind et coll
regroup_par_typ_stock <- function(x,TypeZone){
  nb_prom_stock <- x %>%
    filter(stock_fin != 0) %>%
    select(CodeZone, Zone,Periode,id_type_lgt,siren) %>%
    distinct() %>%
    group_by(CodeZone, Zone,Periode,id_type_lgt) %>%
    summarise(nb_promoteurs_stock = n()) %>%
    ungroup()
  part_stock <- x %>%
    filter(stock_fin != 0) %>%
    group_by(CodeZone, Zone,Periode,id_type_lgt,siren) %>%
    summarise(total_stock = sum(stock_fin, na.rm = TRUE)) %>%
    ungroup() %>%
    group_by(CodeZone, Zone, Periode, id_type_lgt) %>%
    mutate(part_stock = total_stock / sum(total_stock)) %>%
    select(-total_stock,-siren) %>%
    summarise(part_max_stock = max(part_stock, na.rm = TRUE)) %>%
    ungroup()
  nb_prom_stock %>%
    full_join(part_stock) %>%
    mutate(TypeZone = TypeZone)
}

regroup_typ_resa_epci <- regroup_par_typ_resa(a_epci,"Epci")
regroup_typ_resa_dept <- regroup_par_typ_resa(a_dept,"Départements")
regroup_typ_resa_reg <- regroup_par_typ_resa(a_reg,"Régions")
regroup_typ_resa_fra <- regroup_par_typ_resa(a_france,"France")
regroup_typ_stock_epci <- regroup_par_typ_stock(a_epci,"Epci")
regroup_typ_stock_dept <- regroup_par_typ_stock(a_dept,"Départements")
regroup_typ_stock_reg <- regroup_par_typ_stock(a_reg,"Régions")
regroup_typ_stock_fra <- regroup_par_typ_stock(a_france,"France")

# regroupement des données ind et coll
regroup_typ_resa_ic <- bind_rows(regroup_typ_resa_epci,regroup_typ_resa_dept,regroup_typ_resa_reg,regroup_typ_resa_fra) %>%
  select(TypeZone,Zone,CodeZone,id_type_lgt,Periode,nb_promoteurs_resa,part_max_resa)

regroup_typ_stock_ic <- bind_rows(regroup_typ_stock_epci,regroup_typ_stock_dept,regroup_typ_stock_reg,regroup_typ_stock_fra) %>%
  select(TypeZone,Zone,CodeZone,id_type_lgt,Periode,nb_promoteurs_stock,part_max_stock)

# calcul pour total--------------------

# fonction pour les reservés total
regroup_par_typ_resa_t <- function(x,TypeZone){
  nb_prom_resa <- x %>%
    filter(nb_resa != 0) %>%
    select(CodeZone, Zone,Periode,siren) %>%
    distinct() %>%
    group_by(CodeZone, Zone, Periode) %>%
    summarise(nb_promoteurs_resa = n()) %>%
    ungroup()
  part_resa <- x %>%
    filter(nb_resa != 0) %>%
    group_by(CodeZone, Zone,Periode,siren) %>%
    summarise(total_resa = sum(nb_resa, na.rm = TRUE)) %>%
    ungroup() %>%
    group_by(CodeZone, Zone, Periode) %>%
    mutate(part_res = total_resa / sum(total_resa)) %>%
    select(-total_resa,-siren) %>%
    summarise(part_max_resa = max(part_res, na.rm = TRUE)) %>%
    ungroup()
  nb_prom_resa %>%
    full_join (part_resa) %>%
    mutate(TypeZone = TypeZone)

}

# fonction pour les stocks total
regroup_par_typ_stock_t <- function(x,TypeZone){
  nb_prom_stock <- x %>%
    filter(stock_fin != 0) %>%
    select(CodeZone, Zone,Periode,siren) %>%
    distinct() %>%
    group_by(CodeZone, Zone,Periode) %>%
    summarise(nb_promoteurs_stock = n()) %>%
    ungroup()
  part_stock <- x %>%
    filter(stock_fin != 0) %>%
    group_by(CodeZone, Zone,Periode,siren) %>%
    summarise(total_stock = sum(stock_fin, na.rm = TRUE)) %>%
    ungroup() %>%
    group_by(CodeZone, Zone, Periode) %>%
    mutate(part_stock = total_stock / sum(total_stock)) %>%
    select(-total_stock,-siren) %>%
    summarise(part_max_stock = max(part_stock, na.rm = TRUE)) %>%
    ungroup()
  nb_prom_stock %>%
    full_join(part_stock) %>%
    mutate(TypeZone = TypeZone)
}

regroup_typ_resa_epci <- regroup_par_typ_resa_t(a_epci,"Epci")
regroup_typ_resa_dept <- regroup_par_typ_resa_t(a_dept,"Départements")
regroup_typ_resa_reg <- regroup_par_typ_resa_t(a_reg,"Régions")
regroup_typ_resa_fra <- regroup_par_typ_resa_t(a_france,"France")
regroup_typ_stock_epci <- regroup_par_typ_stock_t(a_epci,"Epci")
regroup_typ_stock_dept <- regroup_par_typ_stock_t(a_dept,"Départements")
regroup_typ_stock_reg <- regroup_par_typ_stock_t(a_reg,"Régions")
regroup_typ_stock_fra <- regroup_par_typ_stock_t(a_france,"France")

# regroupement des données total
regroup_typ_resa_tot <- bind_rows(regroup_typ_resa_epci,regroup_typ_resa_dept,regroup_typ_resa_reg,regroup_typ_resa_fra) %>%
  mutate(id_type_lgt = "3") %>%
  select(TypeZone,Zone,CodeZone,id_type_lgt,Periode,nb_promoteurs_resa,part_max_resa)

regroup_typ_stock_tot <- bind_rows(regroup_typ_stock_epci,regroup_typ_stock_dept,regroup_typ_stock_reg,regroup_typ_stock_fra) %>%
  mutate(id_type_lgt = "3") %>%
  select(TypeZone,Zone,CodeZone,id_type_lgt,Periode,nb_promoteurs_stock,part_max_stock)

# regroupement de l'ensemble des données promoteurs
regroup_typ_resa_ict <- bind_rows(regroup_typ_resa_ic,regroup_typ_resa_tot)
regroup_typ_stock_ict <- bind_rows(regroup_typ_stock_ic,regroup_typ_stock_tot)

regroup_typ <- full_join (regroup_typ_resa_ict,regroup_typ_stock_ict) %>%
  mutate(TypeZone = as.factor(TypeZone),
         Zone = as.factor(Zone),
         CodeZone= as.factor(CodeZone)) %>%
  mutate(
    id_type_lgt = fct_recode(id_type_lgt, maisons = "1", appartements = "2", total = "3")) %>%
  pivot_wider(names_from = id_type_lgt ,
              values_from = c(nb_promoteurs_resa,nb_promoteurs_stock,part_max_resa,part_max_stock)) %>%
  mutate_if(is.character, as.factor)

rm(regroup_typ_resa_epci,regroup_typ_resa_dept,regroup_typ_resa_reg,regroup_typ_resa_fra,
   regroup_typ_stock_epci,regroup_typ_stock_dept,regroup_typ_stock_reg,regroup_typ_stock_fra,
   indic_typ_tot,
   regroup_par_typ_resa,regroup_par_typ_stock,
   a_dept,a_epci,a_france,a_reg,
   regroup_typ_resa_ic,regroup_typ_resa_ict,regroup_typ_stock_ic,regroup_typ_stock_ict,
   regroup_typ_resa_tot,regroup_typ_stock_tot)

# ---------------------------------------------------------------------------------------------------------------------------------------------------
# 2.2 Construction de la table des indicateurs par nature du projet id_nature_projet (1=construction neuve/2=construction sur existant)

# On calcule pour chaque niveau de zonage, hormis les communes (code_insee-> DEPCOM), trimestre de mises en vente (trim_mev->Periode) et nature du projet (id_nature_projet),
# - le nombre de mises en vente (mev_t)
# - le nombre de logements réservés à la vente (nb_resa)
# - le prix total des ventes (prix)
# - la Surface totale (surface)
# - encours de logements proposés à la vente (stock_fin)

# 2.2.a tableau des données
indic_nat <- ECLN2 %>%
  select(DEPCOM, Periode = trim_mev, id_nature_projet, mev_t) %>%
  as_tibble() %>%
  filter(Periode >= 201701) %>%
  group_by(id_nature_projet, DEPCOM, Periode) %>%
  summarise(mev_t = sum(mev_t, na.rm = T)) %>%
  ungroup() %>%
  full_join ( ECLN %>%
                select(DEPCOM = code_insee, Periode = trimestre_enquete , id_nature_projet, nb_resa, nb_lgt_inv_t1, nb_lgt_inv_t2, nb_lgt_inv_t3, nb_lgt_inv_t4, nb_lgt_inv_t5, nb_lgt_inv_t6,
                       surface, prix,
                       stock_fin ) %>%
                filter(Periode >= 201701) %>%
                group_by(id_nature_projet, DEPCOM, Periode) %>%
                summarise(
                  nb_resa = sum(nb_resa, na.rm = T),
                  stock_fin = sum(stock_fin, na.rm = T),
                  prix = sum(prix, na.rm = T),
                  surface = sum(surface, na.rm = T)
                ) %>%
                ungroup()) %>%
  relocate(DEPCOM) %>%
  complete(DEPCOM,id_nature_projet,Periode,fill = list(mev_t= 0, nb_resa = 0,stock_fin=0, prix = 0, surface = 0)) %>%
  mutate_if(is.character, as.factor) %>%
  cogifier(code_commune = DEPCOM,na.rm = TRUE) %>%
  filter (TypeZone != "Communes") %>%
  mutate_if(is.character, as.factor)

# creation table des EPCI non présents dans ECLN
# zonage fictif
a0 <- data.frame(Periode=v1) %>%     #creation d'une table fictive avec toutes les périodes et tous codes type logement
  mutate(CodeZone = as.factor("xxx"),
         id_nature_projet = as.factor("1"),
         Periode = as.character(Periode),
         mev_t = 0,
         nb_resa = 0,
         stock_fin = 0,
         prix = 0,
         surface = 0) %>%
  relocate (2,3)
# a0b <- data.frame(
#   CodeZone = as.factor("xxx"),
#   id_nature_projet = as.factor("2"),
#   Periode = "2017T1",
#   mev_t = 0,
#   nb_resa = 0,
#   stock_fin = 0,
#   prix = 0,
#   surface = 0)
# a0 <- bind_rows(a0a,a0b)
a1 <- epci %>%   # liste des epci non présents dans la table
  select(CodeZone = EPCI, Zone = NOM_EPCI) %>%
  left_join(indic_nat %>% select(CodeZone,TypeZone) %>% unique() %>% filter(TypeZone =="Epci")) %>%
  filter(is.na(TypeZone)) %>%
  select(-TypeZone)
a1b <- a1 %>%  #table des epci non presents dans la table avec valeur 0 pour tous
  select(-Zone) %>%
  mutate(CodeZone = forcats::fct_drop(CodeZone)) %>%
  mutate(id_nature_projet = as.factor("2"),
         Periode = "2017T1",
         mev_t = 0,
         nb_resa = 0,
         stock_fin = 0,
         prix = 0,
         surface = 0)
a2 <- bind_rows(a0,a1b) %>%  # regroupement table fictive et des epci non présents et complete
  complete(CodeZone,id_nature_projet,Periode,fill = list(mev_t= 0, nb_resa = 0,stock_fin=0, prix = 0, surface = 0)) %>%
  filter(CodeZone != "xxx") %>%
  mutate(Periode = as.factor(Periode),
         TypeZone = as.factor("Epci"),
  )%>%
  left_join(a1) %>%
  mutate(CodeZone = forcats::fct_drop(CodeZone),
         Zone = forcats::fct_drop(Zone))

indic_nat <- bind_rows(indic_nat,a2) %>%  #regroupement des tables pour avoir tous les zonages et tous les cas de figure
  mutate(CodeZone = forcats::fct_drop(CodeZone),
         Zone = forcats::fct_drop(Zone))
rm(a0,a1,a1b,a2,v1)

# 2.2.b calculs par rapport au nombre de promoteurs

# Pour chaque type de zonage, on modifie la table individuelle en supprimant la colonne code commune et
#   en ne gardant que le nom du zonage (epci, dept, region, france):

# table pour les epci
b_epci <- ECLN2 %>%
  select(CodeZone = EPCI, Zone= NOM_EPCI, Periode = trimestre_enquete, id_nature_projet, nb_resa,
         surface, prix, stock_fin,siren)
# table pour les departements
b_dept <-  ECLN2 %>%
  select(CodeZone = DEP, Zone= NOM_DEP, Periode = trimestre_enquete, id_nature_projet, nb_resa,
         surface, prix, stock_fin,siren)
# table pour les régions
b_reg <-  ECLN2 %>%
  select(CodeZone = REG, Zone= NOM_REG, Periode = trimestre_enquete, id_nature_projet, nb_resa,
         surface, prix, stock_fin,siren)
# table pour la France
b_france <-  ECLN2 %>%
  mutate( CodeZone = "FRMETRO", Zone = "France Métropolitaine") %>%
  select(CodeZone, Zone, Periode = trimestre_enquete, id_nature_projet, nb_resa,
         surface, prix, stock_fin,siren)

# calcul pour ind et coll--------------------
# fonction pour les reservés ind et coll
regroup_par_nat_resa <- function(x,TypeZone){
  nb_prom_resa <- x %>%
    filter(nb_resa != 0) %>%
    select(CodeZone, Zone,Periode,id_nature_projet,siren) %>%
    distinct() %>%
    group_by(CodeZone, Zone, Periode,id_nature_projet) %>%
    summarise(nb_promoteurs_resa = n()) %>%
    ungroup()
  part_resa <- x %>%
    filter(nb_resa != 0) %>%
    group_by(CodeZone, Zone,Periode,id_nature_projet,siren) %>%
    summarise(total_resa = sum(nb_resa, na.rm = TRUE)) %>%
    ungroup() %>%
    group_by(CodeZone, Zone, Periode, id_nature_projet) %>%
    mutate(part_res = total_resa / sum(total_resa)) %>%
    select(-total_resa,-siren) %>%
    summarise(part_max_resa = max(part_res, na.rm = TRUE)) %>%
    ungroup()
  nb_prom_resa %>%
    full_join (part_resa) %>%
    mutate(TypeZone = TypeZone)

}

# fonction pour les stocks ind et coll
regroup_par_nat_stock <- function(x,TypeZone){
  nb_prom_stock <- x %>%
    filter(stock_fin != 0) %>%
    select(CodeZone, Zone,Periode,id_nature_projet,siren) %>%
    distinct() %>%
    group_by(CodeZone, Zone,Periode,id_nature_projet) %>%
    summarise(nb_promoteurs_stock = n()) %>%
    ungroup()
  part_stock <- x %>%
    filter(stock_fin != 0) %>%
    group_by(CodeZone, Zone,Periode,id_nature_projet,siren) %>%
    summarise(total_stock = sum(stock_fin, na.rm = TRUE)) %>%
    ungroup() %>%
    group_by(CodeZone, Zone, Periode, id_nature_projet) %>%
    mutate(part_stock = total_stock / sum(total_stock)) %>%
    select(-total_stock,-siren) %>%
    summarise(part_max_stock = max(part_stock, na.rm = TRUE)) %>%
    ungroup()
  nb_prom_stock %>%
    full_join(part_stock) %>%
    mutate(TypeZone = TypeZone)
}

regroup_nat_resa_epci <- regroup_par_nat_resa(b_epci,"Epci")
regroup_nat_resa_dept <- regroup_par_nat_resa(b_dept,"Départements")
regroup_nat_resa_reg <- regroup_par_nat_resa(b_reg,"Régions")
regroup_nat_resa_fra <- regroup_par_nat_resa(b_france,"France")
regroup_nat_stock_epci <- regroup_par_nat_stock(b_epci,"Epci")
regroup_nat_stock_dept <- regroup_par_nat_stock(b_dept,"Départements")
regroup_nat_stock_reg <- regroup_par_nat_stock(b_reg,"Régions")
regroup_nat_stock_fra <- regroup_par_nat_stock(b_france,"France")

# regroupement des données
regroup_nat_resa_ne <- bind_rows(regroup_nat_resa_epci,regroup_nat_resa_dept,regroup_nat_resa_reg,regroup_nat_resa_fra) %>%
  select(TypeZone,Zone,CodeZone,id_nature_projet,Periode,nb_promoteurs_resa,part_max_resa)

regroup_nat_stock_ne <- bind_rows(regroup_nat_stock_epci,regroup_nat_stock_dept,regroup_nat_stock_reg,regroup_nat_stock_fra) %>%
  select(TypeZone,Zone,CodeZone,id_nature_projet,Periode,nb_promoteurs_stock,part_max_stock)

# calcul pour total--------------------
# opération non nécessaire

regroup_nat <- full_join (regroup_nat_resa_ne,regroup_nat_stock_ne) %>%
  mutate(TypeZone = as.factor(TypeZone),
         Zone = as.factor(Zone),
         CodeZone= as.factor(CodeZone)) %>%
  mutate(
    id_nature_projet = fct_recode(id_nature_projet, construction_neuve = "1", construction_sur_existant = "2")) %>%
  pivot_wider(names_from = id_nature_projet ,
              values_from = c(nb_promoteurs_resa,nb_promoteurs_stock,part_max_resa,part_max_stock)) %>%
  mutate_if(is.character, as.factor)

rm(regroup_nat_resa_epci,regroup_nat_resa_dept,regroup_nat_resa_reg,regroup_nat_resa_fra,
   regroup_nat_stock_epci,regroup_nat_stock_dept,regroup_nat_stock_reg,regroup_nat_stock_fra,
   regroup_par_nat_resa,regroup_par_nat_stock,
   b_dept,b_epci,b_france,b_reg,
   regroup_nat_resa_ne,regroup_nat_stock_ne,
   ECLN,ECLN2,regroup_par_typ_resa_t,regroup_par_typ_stock_t)

# Liste des Epci de plus de 50.000 habitants-----------------------------------------------------------
population <- importer_data(db = "datamart",
                            schema = "portrait_territoires",
                            table = "cogifiee_pop_rgp_insee")
Epci_plus_50000 <- population %>%
  select(TypeZone,Zone,CodeZone,date,pop.ttage.cs0) %>%
  filter(date == max(date),pop.ttage.cs0 > 50000,TypeZone ==  "Epci") %>%
  pull(CodeZone)

rm(population)