### NOVO PROCEDIMENTO PARA DADOS

# ALUNOS / MATRÍCULAS / QUALIFICAÇÕES

setwd("/home/hudson/minitrab/app")

library(dplyr)
library(data.table)
library(RPostgreSQL)
library(bit64)
library(stringr)

#Carregando o Driver
drv <- dbDriver("PostgreSQL")

#Conectando com a Base de dados
con <- dbConnect(drv, dbname = "mtb",
                 host = "10.1.0.4", port = 5432,
                 user = "radar", password = "RadaR_@5112@18")


#Lendo as tabelas em datraframes
mdl_user <- dbGetQuery(con, "SELECT * FROM public.mdl_user")
mdl_user_enrolments <- dbGetQuery(con, "SELECT * FROM public.mdl_user_enrolments")
mdl_enrol <- dbGetQuery(con, "SELECT * FROM public.mdl_enrol")
mdl_course <- dbGetQuery(con, "SELECT *  FROM public.mdl_course")
mdl_course_categories <- dbGetQuery(con, "SELECT * FROM public.mdl_course_categories")
mdl_badge_issued <- dbGetQuery(con, "SELECT * FROM public.mdl_badge_issued")

dim_estudante <- fread("dados/dim_estudante.csv")

#Disconectando
dbDisconnect(con)

#Descarregando o Driver
dbUnloadDriver(drv)

##############################
# ALUNOS

# Selecionando apenas as colunas utilizáveis
mdl_user <- select(mdl_user, c("id", "auth", "confirmed", "username", "idnumber", "firstname", "lastname", "email", "country", "lang", "calendartype", "timezone", "firstaccess", "lastaccess", "lastlogin", "currentlogin", "lastip", "timecreated", "alternatename"))

#Renomeando 'timecreated' para 'tempocadastro' , 'alternatename' para 'cpf' e "id" para "userid"
mdl_user <- rename(mdl_user, tempocadastro = timecreated)
mdl_user <- rename(mdl_user, cpf = alternatename)
mdl_user <- rename(mdl_user, userid = id)

# NÚMERO DE ALUNOS POR CPF
alunos <- mdl_user[!is.na(mdl_user$cpf),]
alunos <- alunos[alunos$cpf != "",]
alunos <- alunos[!duplicated(alunos$cpf),]

#### ADICIONANDO MAIS INFORMAÇÕES SOBRE UF

#Manipulacoes CPF em 'dim_estudante'
dim_estudante$cpf <- str_pad(dim_estudante$cpf, 11, pad = "0")
dim_estudante$cpf <- as.character(dim_estudante$cpf)

#Manipulacoes CPF em 'alunos'
#Retirando 'pontuação'
alunos$cpf <- gsub("[[:punct:]]", "", alunos$cpf)
alunos$cpf <- as.character(alunos$cpf)

#Juntando 'dim_estudante' com 'matriculas_curso' por CPF
alunos <- left_join(alunos, dim_estudante, by='cpf')

#SUBSTITUINDO NA's por "NI"
alunos[is.na(alunos)] <- c("NI")

##############################
# MATRÍCULAS EM CURSOS
matriculas_curso <- mdl_user_enrolments

# Joins para obter courseid, e fullname (nome do curso)
mdl_enrol <- rename(mdl_enrol, enrolid = id)
mdl_course <- rename(mdl_course, courseid = id)
mdl_enrol <- rename(mdl_enrol, enrolname = name)
mdl_course_categories <- rename(mdl_course_categories, categoryName = name)

course_enrol_join <- mdl_course
course_enrol_join <- merge(course_enrol_join, mdl_enrol, by.x = "courseid", by.y = "courseid", all.y = TRUE)
course_enrol_join <- merge(course_enrol_join, mdl_course_categories, by.x = "category", by.y = "id")

# Seleção de colunas a manter
keep = c(
  "courseid",
  "fullname",
  "enrolid",
  "categoryName"
)
course_enrol_join <- subset(course_enrol_join, select = keep)
course_enrol_join <- distinct(course_enrol_join)

matriculas_curso <- left_join(mdl_user_enrolments, course_enrol_join, by = "enrolid")
matriculas_curso <- select(matriculas_curso, -c("id", "status", "modifierid", "timemodified"))

# Adição de informação dos alunos através do join com a tabela alunos
matriculas_curso <- left_join(matriculas_curso, alunos, by = "userid")

# Remoção de CPFs duplicados para o mesmo curso
matriculas_curso <- distinct(matriculas_curso, cpf, courseid, .keep_all = TRUE)


##############################
# QUALIFICAÇÕES

# Join por 'userid' com 'alunos'
qualificacoes_cpf <- distinct(mdl_badge_issued)
qualificacoes_cpf <- left_join(qualificacoes_cpf, alunos, by = "userid")

# Substituindo NA por NI
qualificacoes_cpf[is.na(qualificacoes_cpf)] <- c("NI")