##############################################################################
########### CÓDIGO PARA LA CREACIÓN DE BASES UNIFICADAS      #################
##############################################################################

library(readxl);library(dplyr); library(tidyr);   library(purrr); library(writexl)

rm(list = ls(all.names = TRUE))

# Diligenciamos la carpeta donde queremos dejar las bases 
setwd("G:/Mi unidad/PRADATOS ANALÍTICA/1.Consultoría/2.Claro - CRC Dominancia - 2026/Documentos soporte/Posdata/Bases Portal/")
options(scipen = 999)

#Indicamos la carpeta en que estarán las bases procesadas y de indicadores
salida<-("G:/Mi unidad/PRADATOS ANALÍTICA/1.Consultoría/2.Claro - CRC Dominancia - 2026/Documentos soporte/Posdata/Bases Finales/")


# Lista de archivos excel en la carpeta
archivos <- list.files(pattern = "\\.xlsx$")

# Cargar todos los archivos
for(i in archivos){
  
  nombre_objeto <- tools::file_path_sans_ext(i)
  
  assign(nombre_objeto,
         read_excel(i),
         envir = .GlobalEnv)
}


#Funciones 

renombrarempresas <- function(base, var_empresa){
  
  base %>%
    
    mutate(
      {{var_empresa}} := recode({{var_empresa}},
                                "COMUNICACION CELULAR S A COMCEL S A" = "Claro",
                                "COLOMBIA TELECOMUNICACIONES S.A. E.S.P." = "Movistar",
                                "COLOMBIA MOVIL S.A. E.S.P." = "Tigo",
                                "COLOMBIA MOVIL  S.A ESP"="Tigo",
                                "UNE EPM TELECOMUNICACIONES S.A." = "Une",
                                "AVANTEL S.A.S." = "Avantel",
                                "PARTNERS TELECOM COLOMBIA SAS" = "Partners",
                                "PARTNERS TELECOM COLOMBIA WOM"= "Partners",
                                "COLOMBIA MÓVIL S.A ESP"="Tigo",
                                "COLOMBIA TELECOMUNICACIONES S A E S P"="Movistar",
                                "DIMECOM COMUNICACIONES S.A.S."="Dimecom",
                                "EMPRESA DE TELECOMUNICACIONES DE BOGOTA SA ESP"="ETB",
                                "EZTALK MOBILE S.A.S."="Eztalk",
                                "KIERO IP TELECOMUNICACIONES SAS"= "Kiero",
                                 "VILACOM MOBILE SAS" = "Vilacom",
                                "VIRGIN MOBILE COLOMBIA S.A.S." = "Virgin",
                                "ALMACENES EXITO INVERSIONES S.A.S." = "Éxito",
                                "UFF MOVIL SAS" = "Uff",
                                "SUMA MOVIL S.A.S." = "Suma",
                                "SUMA MOVIL S.A.S" = "Suma",
                                "SETROC MOBILE GROUP S.A.S MEGAMOVIL" = "Setroc",
                                "EMPRESA DE TELECOMUNICACIONES DE BOGOTA S.A. ESP" = "ETB",
                                "LIWA S.A.S. E.S.P." = "Cellvoz",
                                "LOGISTICA FLASH COLOMBIA S.A.S" = "Flash",
                                "LOGISTICA FLASH COLOMBIA S.A.S."="Flash",
                                "SETROC MOBILE GROUP SAS" = "Setroc",
                                "LOV TELECOMUNICACIONES SAS" = "LOV",
                                "PLINTRON COLOMBIA S.A.S" = "Plintron",
                                "TELEFONICA MOVILES COLOMBIA S.A" = "Movistar",
                                "TELEFONICA MOVILES COLOMBIA S.A." ="Movistar",
                                "TELECALL COLOMBIA SAS" ="Telecall",
                                "CELLVOZ COLOMBIA SERVICIOS INTEGRALES S A E S P" ="Cellvoz",
                                "PLINTRON  COLOMBIA S.A.S." = "Plintron",
                                "PLINTRON COLOMBIA S.A.S."= "Plintron"
                                
                                
      )
    )
}

validar_unicidad <- function(base, vars_llave, nombre_base){
  dup <- base %>%
    count(across(all_of(vars_llave))) %>%
    filter(n > 1)
  
  if(nrow(dup) > 0){
    warning(paste0("⚠️ Hay duplicados en ", nombre_base, " según llave: ", paste(vars_llave, collapse = ", ")))
    print(dup)
  } else {
    cat("✓ Sin duplicados en", nombre_base, "\n")
  }
}

#INTERNET MÓVIL

#Abonados

internet_movil_demanda_abonados <- renombrarempresas(internet_movil_demanda_abonados, EMPRESA)

  internet_movil_demanda_abonados <- internet_movil_demanda_abonados %>%
  filter(MES_DEL_TRIMESTRE == 3) %>%
  group_by(ANNO, TRIMESTRE, EMPRESA) %>%
  summarise(
    ABONADOS_IM = sum(CANTIDAD_ABONADOS, na.rm = TRUE),
    .groups = "drop"
  )

#Ingresos demanda
  
  internet_movil_demanda_ingresos <- renombrarempresas(internet_movil_demanda_ingresos, EMPRESA)
  
  internet_movil_demanda_ingresos  <- internet_movil_demanda_ingresos  %>%
    group_by(ANNO, TRIMESTRE,  EMPRESA) %>%
    summarise(
      INGRESOS_IMDEM= sum(INGRESOS, na.rm = TRUE),
      .groups = "drop"
    ) 
  
#Tráfico demanda
  
  internet_movil_demanda_trafico  <- renombrarempresas( internet_movil_demanda_trafico, EMPRESA)
  
  internet_movil_demanda_trafico   <- internet_movil_demanda_trafico  %>%
    group_by(ANNO, TRIMESTRE, EMPRESA) %>%
    summarise(
      TRAFICO_IMDEM= sum(TRAFICO, na.rm = TRUE),
      .groups = "drop"
    ) 
  
#Suscriptores
  
  internet_movil_cf_suscriptores <- renombrarempresas( internet_movil_cf_suscriptores, EMPRESA)
  
  internet_movil_cf_suscriptores <-  internet_movil_cf_suscriptores %>%
    filter(MES_DEL_TRIMESTRE == 3) %>%
    group_by(ANNO, TRIMESTRE, EMPRESA) %>%
    summarise(
      SUSCRIPTORES_IM = sum(CANTIDAD_SUSCRIPTORES, na.rm = TRUE),
      .groups = "drop"
    ) 

#Ingresos cargo fijo
  
  internet_movil_cf_ingresos <- renombrarempresas( internet_movil_cf_ingresos, EMPRESA)
  
  internet_movil_cf_ingresos <-  internet_movil_cf_ingresos%>%
    group_by(ANNO, TRIMESTRE, EMPRESA) %>%
    
    summarise(
      INGRESOS_IM_CF= sum(INGRESOS, na.rm = TRUE),
      .groups = "drop"
    ) 

#Tráfico cargo fijo
  
  internet_movil_cf_trafico <- renombrarempresas( internet_movil_cf_trafico, EMPRESA)
  
  internet_movil_cf_trafico <-  internet_movil_cf_trafico%>%
    group_by(ANNO, TRIMESTRE, EMPRESA) %>%

    summarise(
      TRAFICO_IM_CF = sum(TRAFICO, na.rm = TRUE),
      .groups = "drop"
    ) 
  
#Unión Internet móvil

   bases<- list(  internet_movil_cf_ingresos,   internet_movil_cf_suscriptores,   internet_movil_cf_trafico,   internet_movil_demanda_abonados,   internet_movil_demanda_trafico,   internet_movil_demanda_ingresos)
   InternetMovil<- reduce(   bases,  full_join,   by = c("ANNO", "TRIMESTRE",  "EMPRESA"))
  
#VOZ MÓVIL

   # Abonados (trimestral)
   voz_movil_abonados <- voz_movil_abonados %>%
     renombrarempresas(PROVEEDOR) %>%
     group_by(ANNO, TRIMESTRE, PROVEEDOR, MODALIDAD) %>%
     summarise(
       ABONADOS = sum(ABONADOS, na.rm = TRUE),
       .groups = "drop"
     ) %>%
     pivot_wider(
       names_from = MODALIDAD,
       values_from = ABONADOS
     ) %>%
     mutate(across(where(is.numeric), ~ replace_na(.x, 0))) %>%
     rename(
       ABONADOS_VM = PREPAGO,
       SUSCRIPTORES_VM = POSPAGO,
       EMPRESA = PROVEEDOR
     ) %>%
     mutate(
       USUARIOS_VM = rowSums(across(c(ABONADOS_VM, SUSCRIPTORES_VM)), na.rm = TRUE)
     )
   
   # Ingresos
   voz_movil_ingresos <- voz_movil_ingresos %>%  renombrarempresas(EMPRESA) 
   
   voz_movil_ingresos <- voz_movil_ingresos %>%
     group_by(ANNO, TRIMESTRE, EMPRESA) %>%
     summarise(
       INGRESOS_VM = sum(INGRESOS_TOTALES, na.rm = TRUE),
       INGRESOS_VM_POS = sum(INGRESOS_POSPAGO, na.rm = TRUE),
       INGRESOS_VM_PRE = sum(INGRESOS_PREPAGO, na.rm = TRUE),
       .groups = "drop"
     )
   
   # Tráfico
   voz_movil_trafico <- voz_movil_trafico %>%renombrarempresas(EMPRESA) 
   
   voz_movil_trafico <- voz_movil_trafico %>%
     group_by(ANNO, TRIMESTRE, EMPRESA, TIPO_TRAFICO) %>%
     summarise(
       TRAFICO = sum(TRAFICO, na.rm = TRUE),
       .groups = "drop"
     ) %>%
     pivot_wider(
       names_from = TIPO_TRAFICO,
       values_from = TRAFICO
     ) %>%
     mutate(across(where(is.numeric), ~ replace_na(.x, 0))) %>%
     rename(
       TRAFICO_VM_PRE = `Tráfico prepago`,
       TRAFICO_VM_POS = `Tráfico pospago`
     ) %>%
     mutate(
       TRAFICO_VM = rowSums(across(c(TRAFICO_VM_PRE, TRAFICO_VM_POS)), na.rm = TRUE)
     )
   
   # Validación
   validar_unicidad(voz_movil_abonados, c("ANNO","TRIMESTRE","EMPRESA"), "voz_movil_abonados")
   validar_unicidad(voz_movil_ingresos, c("ANNO","TRIMESTRE","EMPRESA"), "voz_movil_ingresos")
   validar_unicidad(voz_movil_trafico, c("ANNO","TRIMESTRE","EMPRESA"), "voz_movil_trafico")

   # Unión Voz Móvil
   bases <- list(  voz_movil_trafico,  voz_movil_abonados,  voz_movil_ingresos)
   VozMovil <- reduce(bases, full_join, by = c("ANNO", "TRIMESTRE", "EMPRESA")) %>%
     mutate(across(where(is.numeric), ~ replace_na(.x, 0)))
   
  #Portación
   
   portacion_donante <- portacion_donante %>%
     renombrarempresas(EMPRESA) %>%
     rename(OPERACIONES_DONANTE = OPERACIONES_PORTACION)
   
   portacion_receptor <- portacion_receptor %>%
     renombrarempresas(EMPRESA) %>%
     rename(OPERACIONES_RECEPTOR = OPERACIONES_PORTACION)
   
   pnm_receptor_neto <- pnm_receptor_neto %>%
     renombrarempresas(EMPRESA)
   
   validar_unicidad(portacion_donante, c("ANNO","TRIMESTRE","MES","EMPRESA"), "portacion_donante")
   validar_unicidad(portacion_receptor, c("ANNO","TRIMESTRE","MES","EMPRESA"), "portacion_receptor")
   validar_unicidad(pnm_receptor_neto, c("ANNO","TRIMESTRE","MES","EMPRESA"), "pnm_receptor_neto")
   
   
# Servicios Móviles
   
   SM<- empaquetamiento_movil %>%renombrarempresas(EMPRESA) 
   
   USUARIOS_SM <- SM %>%
     filter(MES_DEL_TRIMESTRE == 3) %>%
     filter(SERVICIO_PAQUETE == "Voz móvil + internet móvil") %>%
     group_by(ANNO, TRIMESTRE, EMPRESA) %>%
     summarise(
       USUARIOS_SM = sum(CANTIDAD_LINEAS, na.rm = TRUE),
       .groups = "drop"
     )
   
   INGRESOS_SM <- SM %>%
     filter(SERVICIO_PAQUETE == "Voz móvil + internet móvil") %>%
     group_by(ANNO, TRIMESTRE, EMPRESA) %>%
     summarise(
       INGRESOS_SM = sum(VALOR_FACTURADO_O_COBRADO, na.rm = TRUE),
       .groups = "drop"
     )  

   Serviciosmoviles <- VozMovil %>%
     full_join(InternetMovil, by = c("ANNO", "TRIMESTRE", "EMPRESA")) %>%
     full_join(USUARIOS_SM,   by = c("ANNO", "TRIMESTRE", "EMPRESA")) %>%
     full_join(INGRESOS_SM,   by = c("ANNO", "TRIMESTRE", "EMPRESA")) %>%
     filter(ANNO != 2011) %>%
     mutate(across(where(is.numeric), ~ replace_na(.x, 0)))
   
   Serviciosmoviles <- Serviciosmoviles %>%
     mutate(
       INGRESOS_IM = rowSums(across(c(INGRESOS_IM_CF, INGRESOS_IMDEM)), na.rm = TRUE),
       TRAFICO_IM = rowSums(across(c(TRAFICO_IM_CF, TRAFICO_IMDEM)), na.rm = TRUE),
       USUARIOS_IM = rowSums(across(c(ABONADOS_IM, SUSCRIPTORES_IM)), na.rm = TRUE),
     ) %>%
     dplyr::select(-dplyr::starts_with("ID_"))
   
   orden_vars <- c(
     "ABONADOS_VM", "SUSCRIPTORES_VM", "USUARIOS_VM",
     "TRAFICO_VM_PRE", "TRAFICO_VM_POS", "TRAFICO_VM",
     "INGRESOS_VM_PRE", "INGRESOS_VM_POS", "INGRESOS_VM",
     "ABONADOS_IM", "SUSCRIPTORES_IM", "USUARIOS_IM",
     "TRAFICO_IM_CF", "TRAFICO_IMDEM", "TRAFICO_IM",
     "INGRESOS_IM_CF", "INGRESOS_IMDEM", "INGRESOS_IM",
     "USUARIOS_SM", "INGRESOS_SM"
   )
   
   Serviciosmoviles <- Serviciosmoviles %>%
     select(ANNO, TRIMESTRE, EMPRESA, any_of(orden_vars))
   

   writexl::write_xlsx(Serviciosmoviles, paste0(salida, "ServiciosMoviles.xlsx"))
   
   cat("\n✓ Bases exportadas correctamente\n")
   cat("Archivo trimestral:", paste0(salida, "ServiciosMoviles.xlsx"), "\n")