## ----setup, include = FALSE---------------------------------------------------
knitr::opts_chunk$set(
  collapse = TRUE,
  comment  = "#>",
  eval     = FALSE
)

## ----install------------------------------------------------------------------
# # Via remotes
# remotes::install_github("gecomt/datacaged")

## ----workers------------------------------------------------------------------
# # Control the number of workers
# caged_download(years = 2023, months = 1:3, workers = 3)  # padrão
# 
# # Set globally for the entire session
# options(datacaged.workers = 4)
# 
# # Sequential mode (useful for unstable connections)
# caged_download(years = 2023, months = 1, workers = 1)

## ----load-basico--------------------------------------------------------------
# library(datacaged)
# 
# # Download Novo CAGED Jan–Dec/2023
# # Novo CAGED: national file, `states` does not filter
# caged_load(
#   years    = 2023,
#   months   = seq_len(12L),
#   db_path = "caged.duckdb"
# )

## ----consulta-dplyr-----------------------------------------------------------
# library(dplyr)
# 
# con <- caged_connect("caged.duckdb")
# 
# # Monthly employment balance in 2023
# saldo_mensal <- tbl(con, "caged_mov") |>
#   group_by(competenciamov) |>
#   summarise(saldo = sum(saldomovimentacao, na.rm = TRUE)) |>
#   arrange(competenciamov) |>
#   collect()
# 
# saldo_mensal

## ----consulta-sql-------------------------------------------------------------
# # Or with direct SQL
# DBI::dbGetQuery(con, "
#   SELECT
#     competenciamov,
#     uf,
#     SUM(saldomovimentacao) AS saldo,
#     AVG(salario)           AS salario_medio,
#     COUNT(*)               AS movimentacoes
#   FROM caged_mov
#   WHERE uf = 35          -- Sao Paulo
#   GROUP BY competenciamov, uf
#   ORDER BY competenciamov
# ")

## ----desconectar--------------------------------------------------------------
# DBI::dbDisconnect(con, shutdown = TRUE)

## ----download-----------------------------------------------------------------
# # Download and save to local cache (~/.local/share/R/datacaged por padrão)
# manifest <- caged_download(
#   years    = 2023,
#   months   = c(1L, 2L, 3L),
#   destdir = "~/meus_dados/caged_cache"
# )
# 
# # manifest is a data.frame with the status of each file
# dplyr::count(manifest, status)

## ----parse--------------------------------------------------------------------
# # One file at a time
# df <- caged_parse("~/meus_dados/caged_cache/caged_mov/2023/CAGEDMOV202301.7z")
# glimpse(df)
# 
# # Several at once
# arquivos <- list.files(
#   "~/meus_dados/caged_cache/NOVO_CAGED/2023",
#   pattern    = "CAGEDMOV",
#   full.names = TRUE
# )
# df_todos <- caged_parse_batch(arquivos)

## ----gravar-------------------------------------------------------------------
# caged_to_duckdb(df_todos, db_path = "caged.duckdb")

## ----info---------------------------------------------------------------------
# caged_info("caged.duckdb")
# #> ── caged.duckdb ────────────────────────────────────────
# #> Tamanho do arquivo: 142.3 MB
# #> ── Tabelas ──────────────────────────────────────────────
# #> * "caged_mov"   Registros: 3,665,155
# #> * "caged_for"      Registros:    91,098
# #> * "caged_exc"       Registros:     7,900
# #>   Registros   : 4.823.901
# #>   Competências: 202301 – 202312

## ----historico----------------------------------------------------------------
# # Baixa Legacy CAGED para Nordeste (2015–2019)
# nordeste <- c("MA", "PI", "CE", "RN", "PB", "PE", "AL", "SE", "BA")
# 
# caged_load(
#   years    = 2015:2019,
#   db_path = "caged_historico.duckdb"
# )
# 
# con <- caged_connect("caged_historico.duckdb")
# 
# # Evolução anual do saldo formal no Nordeste
# tbl(con, "caged_antigo") |>
#   mutate(ano = as.integer(substr(as.character(competencia), 1, 4))) |>
#   group_by(ano, uf) |>
#   summarise(saldo = sum(saldomovimentacao, na.rm = TRUE)) |>
#   collect() |>
#   tidyr::pivot_wider(names_from = uf, values_from = saldo)
# 
# DBI::dbDisconnect(con, shutdown = TRUE)

## ----ajustes------------------------------------------------------------------
# # Baixar ajustes de 2019
# caged_adjustments_load(years = 2019, months = seq_len(12L), db_path = "caged.duckdb")
# 
# # Listar o que está disponível no HuggingFace
# caged_hf_files(type = "ajustes")
# 
# # Comparar saldo original vs ajustado
# con <- caged_connect("caged.duckdb")
# 
# antigo  <- dplyr::tbl(con, "caged_antigo")  |>
#   dplyr::group_by(competencia) |>
#   dplyr::summarise(saldo_original = sum(saldomovimentacao, na.rm = TRUE))
# 
# ajustes <- dplyr::tbl(con, "caged_ajustes") |>
#   dplyr::group_by(competencia) |>
#   dplyr::summarise(saldo_ajuste = sum(saldomovimentacao, na.rm = TRUE))
# 
# dplyr::full_join(antigo, ajustes, by = "competencia") |>
#   dplyr::mutate(saldo_final = saldo_original + saldo_ajuste) |>
#   dplyr::collect()
# 
# DBI::dbDisconnect(con, shutdown = TRUE)

## ----utilitarios--------------------------------------------------------------
# # Verificar se o HuggingFace está online antes de baixar
# caged_status()
# 
# # Listar competências disponíveis no HuggingFace
# caged_hf_files()                     # Novo CAGED (últimos 12 meses)
# caged_hf_files(type = "antigo")      # Legacy CAGED
# caged_hf_files(type = "ajustes")     # CAGED Adjustments
# 
# # Atualização incremental — baixa apenas o que ainda não está no banco
# caged_update(db_path = "caged.duckdb")
# caged_update(db_path = "caged.duckdb", series = c("novo", "antigo"))
# 
# # Exportar tabelas para Parquet (nativo DuckDB, muito rápido)
# caged_to_parquet("caged.duckdb", output_dir = "~/exports")
# caged_to_parquet("caged.duckdb", output_dir = "~/exports",
#                  tables = "caged_mov", partition_by = "uf")