```{r}
library(RSQLite)
```

```{r}
unzip("database.sqlite3.zip", exdir = ".")
#tirando o zip
```

```{r}
conn <- dbConnect(SQLite(), dbname = "database.sqlite3")
#abrindo a conexao
```

```{r}
dbListTables(conn)
#vendo as tabelas
```

```{r}
# Para ver colunas de uma tabela
dbListFields(conn, "subjects")
###
dbListFields(conn, "course_offerings")
###
dbListFields(conn, "sections")
###
dbListFields(conn, "instructors")
```

```{r}
dbListFields(conn, "subject_memberships")
#vendo a coluna do subject membership
```

```{r}
# pergunta 1
query <- "
SELECT DISTINCT i.name
FROM instructors i
JOIN teachings t ON i.id = t.instructor_id
JOIN sections s ON t.section_uuid = s.uuid
JOIN course_offerings co ON s.course_offering_uuid = co.uuid
JOIN subject_memberships sm ON co.uuid = sm.course_offering_uuid
JOIN subjects subj ON sm.subject_code = subj.code
WHERE subj.abbreviation = 'STAT'
"
# Define uma query SQL que seleciona os nomes distintos de professores (i.name)
# que deram aulas de disciplinas cujo assunto tem abreviação 'STAT'.
# Faz joins entre:
# instructors -> teachings -> sections -> course_offerings -> subject_memberships -> subjects
# para relacionar cada professor às disciplinas de estatística.

professores_stat <- dbGetQuery(conn, query)
# Executa a query no banco de dados conectado via 'conn' e salva o resultado
# no data.frame 'professores_stat'.

professores_stat
# Exibe o conteúdo de 'professores_stat', mostrando os nomes dos professores de STAT.

# Total de professores
nrow(professores_stat)
# Retorna o número de linhas do data.frame, ou seja, o total de professores distintos.

```

```{r}
grade_values <- c("A"=4, "AB"=3.5, "B"=3, "BC"=2.5, "C"=2, "D"=1, "F"=0)
# Cria um vetor nomeado em R que associa cada nota em letra (A, AB, B, BC, C, D, F)
# ao seu valor numérico correspondente na escala americana de GPA (0 a 4).

```

```{r}
dbListFields(conn, "grade_distributions")
# Lista todas as colunas existentes na tabela 'grade_distributions' do banco de dados
# conectado via 'conn', permitindo verificar quais campos estão disponíveis para consulta.


```

```{r}
grades_stat3 <- dbGetQuery(conn, "
SELECT gd.course_offering_uuid,
       gd.section_number,
       gd.a_count, gd.ab_count, gd.b_count, gd.bc_count, gd.c_count, gd.d_count, gd.f_count,
       t.instructor_id,
       co.uuid AS course_offering_uuid,
       co.course_uuid,
       c.name AS course_name
FROM grade_distributions gd
JOIN sections s ON gd.section_number = s.number AND gd.course_offering_uuid = s.course_offering_uuid
JOIN teachings t ON s.uuid = t.section_uuid
JOIN course_offerings co ON s.course_offering_uuid = co.uuid
JOIN courses c ON co.course_uuid = c.uuid
JOIN subject_memberships sm ON co.uuid = sm.course_offering_uuid
JOIN subjects subj ON sm.subject_code = subj.code
WHERE subj.abbreviation = 'STAT'
")
# Executa uma query SQL no banco conectado via 'conn' e salva o resultado em 'grades_stat3'.
# A query faz o seguinte:
# - Seleciona o identificador do oferecimento (course_offering_uuid) e número da seção (section_number)
# - Seleciona a contagem de alunos por nota: a_count, ab_count, b_count, bc_count, c_count, d_count, f_count
# - Inclui o id do professor responsável (instructor_id)
# - Inclui o UUID do curso (co.uuid) e o UUID do curso real (co.course_uuid)
# - Inclui o nome do curso (course_name)
# - Faz joins entre grade_distributions -> sections -> teachings -> course_offerings -> courses
#   -> subject_memberships -> subjects para relacionar cada nota à disciplina e professor
# - Filtra apenas as disciplinas cuja abreviação do assunto é 'STAT' (estatística)


```

```{r}
cols_count <- c("a_count","ab_count","b_count","bc_count","c_count","d_count","f_count")
# Cria um vetor com os nomes das colunas que representam a contagem de alunos por cada nota.

grades_stat3[cols_count] <- lapply(grades_stat3[cols_count], as.numeric)
# Converte todas as colunas de contagem de notas em 'grades_stat3' para o tipo numérico,
# garantindo que cálculos matemáticos possam ser feitos corretamente.


```

```{r}
n <- nrow(grades_stat3)
# Armazena o número de linhas (seções) em 'grades_stat3' na variável 'n'.

grades_stat3$total_students <- numeric(n)
grades_stat3$gpa <- numeric(n)
# Cria duas novas colunas em 'grades_stat3':
# - total_students: para armazenar o número total de alunos por seção
# - gpa: para armazenar o GPA médio da seção

values <- c(4, 3.5, 3, 2.5, 2, 1, 0)
# Define um vetor com os valores numéricos correspondentes às notas A, AB, B, BC, C, D, F

for(i in 1:n){
  counts <- as.numeric(grades_stat3[i, cols_count])
  # Para a linha i, extrai as contagens de alunos por nota e converte para numérico

  grades_stat3$total_students[i] <- sum(counts)
  # Calcula o total de alunos na seção i somando todas as contagens

  grades_stat3$gpa[i] <- sum(counts * values) / grades_stat3$total_students[i]
  # Calcula o GPA médio ponderado da seção i:
  # multiplica cada contagem pelo valor numérico da nota, soma e divide pelo total de alunos
}


```

```{r}
# Pegar nomes dos professores
professores <- dbGetQuery(conn, "SELECT id, name FROM instructors")
# Executa uma query no banco para obter os IDs e nomes de todos os professores,
# armazenando o resultado no data.frame 'professores'.

# GPA médio por professor
prof_ids <- unique(grades_stat3$instructor_id)
# Cria um vetor com os IDs únicos dos professores presentes em 'grades_stat3'.

gpa_prof <- data.frame(professor_id=prof_ids, gpa=NA, stringsAsFactors = FALSE)
# Cria um data.frame com uma coluna para os IDs dos professores e outra para o GPA médio,
# inicializando o GPA como NA.

for(i in seq_along(prof_ids)){
  gpa_prof$gpa[i] <- mean(grades_stat3$gpa[grades_stat3$instructor_id == prof_ids[i]])
}
# Para cada professor, calcula a média do GPA das seções que ele lecionou
# e armazena no data.frame 'gpa_prof'.

# Juntar nomes
gpa_prof <- merge(gpa_prof, professores, by.x="professor_id", by.y="id", all.x=TRUE)
# Faz um merge entre 'gpa_prof' e 'professores' para adicionar os nomes
# correspondentes aos IDs, mantendo todos os professores de 'gpa_prof'.

# Professor mais difícil (menor GPA)
prof_mais_dificil <- gpa_prof[which.min(gpa_prof$gpa), ]
# Seleciona o(s) professor(es) com o menor GPA médio, ou seja, mais difícil.

# Professor mais fácil (maior GPA)
prof_mais_facil <- gpa_prof[which.max(gpa_prof$gpa), ]
# Seleciona o(s) professor(es) com o maior GPA médio, ou seja, mais fácil.

```

```{r}
courses <- unique(grades_stat3$course_offering_uuid)
# Cria um vetor com os IDs únicos de cada oferecimento de disciplina presentes em 'grades_stat3'.

gpa_course <- data.frame(course_offering_uuid=courses, gpa=NA, course_name=NA, stringsAsFactors = FALSE)
# Cria um data.frame para armazenar o GPA médio e o nome de cada disciplina,
# inicializando gpa e course_name como NA.

for(i in seq_along(courses)){
  idx <- which(grades_stat3$course_offering_uuid == courses[i])
  # Para cada curso, encontra os índices das linhas em 'grades_stat3' correspondentes a esse curso.

  gpa_course$gpa[i] <- mean(grades_stat3$gpa[idx])
  # Calcula o GPA médio do curso i, com base nas seções encontradas, e armazena no data.frame.

  gpa_course$course_name[i] <- grades_stat3$course_name[idx][1]  # pegar nome da disciplina
  # Atribui o nome da disciplina correspondente ao curso i,
  # pegando o primeiro valor encontrado.
}

# Disciplina mais difícil (menor GPA)
disc_mais_dificil <- gpa_course[which.min(gpa_course$gpa), ]
# Seleciona a(s) disciplina(s) com o menor GPA médio, ou seja, a mais difícil.

# Disciplina mais fácil (maior GPA)
disc_mais_facil <- gpa_course[which.max(gpa_course$gpa), ]
# Seleciona a(s) disciplina(s) com o maior GPA médio, ou seja, a mais fácil.

```

#vendo empate

```{r}
# Definir tolerância
tol <- 1e-6
# Define uma pequena tolerância para comparação de números decimais,
# evitando problemas com precisão de ponto flutuante ao comparar GPAs.

# Professores mais difíceis (menor GPA)
gpa_min <- min(gpa_prof$gpa, na.rm=TRUE)
# Calcula o menor GPA entre os professores, ignorando valores NA.

prof_mais_dificeis <- gpa_prof[abs(gpa_prof$gpa - gpa_min) < tol, ]
# Seleciona todos os professores cujo GPA está dentro da tolerância do menor GPA,
# considerando empates.

# Professores mais fáceis (maior GPA)
gpa_max <- max(gpa_prof$gpa, na.rm=TRUE)
# Calcula o maior GPA entre os professores, ignorando valores NA.

prof_mais_faceis <- gpa_prof[abs(gpa_prof$gpa - gpa_max) < tol, ]
# Seleciona todos os professores cujo GPA está dentro da tolerância do maior GPA,
# considerando empates.

# Disciplinas mais difíceis
gpa_min_course <- min(gpa_course$gpa, na.rm=TRUE)
# Calcula o menor GPA entre as disciplinas, ignorando valores NA.

disc_mais_dificeis <- gpa_course[abs(gpa_course$gpa - gpa_min_course) < tol, ]
# Seleciona todas as disciplinas cujo GPA está dentro da tolerância do menor GPA,
# considerando empates.

# Disciplinas mais fáceis
gpa_max_course <- max(gpa_course$gpa, na.rm=TRUE)
# Calcula o maior GPA entre as disciplinas, ignorando valores NA.

disc_mais_faceis <- gpa_course[abs(gpa_course$gpa - gpa_max_course) < tol, ]
# Seleciona todas as disciplinas cujo GPA está dentro da tolerância do maior GPA,
# considerando empates.

```

```{r}
# GPA mínimo e máximo dos professores, ignorando NAs
gpa_min <- min(gpa_prof$gpa, na.rm = TRUE)
# Calcula o menor GPA entre os professores, ignorando valores NA.

gpa_max <- max(gpa_prof$gpa, na.rm = TRUE)
# Calcula o maior GPA entre os professores, ignorando valores NA.

# Professores mais difíceis (menor GPA)
prof_mais_dificeis <- gpa_prof[!is.na(gpa_prof$gpa) & abs(gpa_prof$gpa - gpa_min) < 1e-6, ]
# Seleciona todos os professores cujo GPA não é NA e está dentro da tolerância
# do menor GPA, garantindo que empates e valores válidos sejam considerados.

# Professores mais fáceis (maior GPA)
prof_mais_faceis <- gpa_prof[!is.na(gpa_prof$gpa) & abs(gpa_prof$gpa - gpa_max) < 1e-6, ]
# Seleciona todos os professores cujo GPA não é NA e está dentro da tolerância
# do maior GPA, garantindo que empates e valores válidos sejam considerados.

```

```{r}

```

```{r}
# Remover NAs e repetições nos nomes
prof_mais_dificeis_names <- unique(na.omit(prof_mais_dificeis$name))
# Remove quaisquer valores NA e repetições dos nomes dos professores mais difíceis.

prof_mais_faceis_names <- unique(na.omit(prof_mais_faceis$name))
# Remove quaisquer valores NA e repetições dos nomes dos professores mais fáceis.

disc_mais_dificeis_names <- unique(na.omit(disc_mais_dificeis$course_name))
# Remove quaisquer valores NA e repetições dos nomes das disciplinas mais difíceis.


# Mostrar resultados
cat("Professor(es) mais difícil(is):", paste(prof_mais_dificeis_names, collapse=", "),
    "com GPA médio =", round(prof_mais_dificeis$gpa[1],2), "\n")
# Exibe os nomes dos professores mais difíceis, separados por vírgula,
# junto com o GPA médio arredondado a duas casas decimais.

cat("Professor(es) mais fácil(is):", paste(prof_mais_faceis_names, collapse=", "),
    "com GPA médio =", round(prof_mais_faceis$gpa[1],2), "\n")
# Exibe os nomes dos professores mais fáceis, separados por vírgula,
# junto com o GPA médio arredondado a duas casas decimais.

cat("Disciplina(s) mais difícil(is):", paste(disc_mais_dificeis_names, collapse=", "),
    "com GPA médio =", round(disc_mais_dificeis$gpa[1],2), "\n")
# Exibe os nomes das disciplinas mais difíceis, separados por vírgula,
# junto com o GPA médio arredondado a duas casas decimais.
 
# Criar tabela de GPA médio por disciplina (agrupando pelo nome da disciplina)
grades_stat3_valid <- grades_stat3[!is.na(grades_stat3$gpa), ]

gpa_course_final <- aggregate(gpa ~ course_name, data=grades_stat3_valid, FUN=function(x) mean(x, na.rm=TRUE))

# GPA máximo entre todas as disciplinas
gpa_max_course <- max(gpa_course_final$gpa, na.rm = TRUE)

# Selecionar apenas disciplina(s) mais fácil(is)
disc_mais_faceis <- gpa_course_final[gpa_course_final$gpa == gpa_max_course, ]

# Mostrar resultados
cat("Disciplina(s) mais fácil(is):", paste(disc_mais_faceis$course_name, collapse=", "), "\n")

```

```{r}
# Criar tabela de GPA médio por disciplina (agrupando pelo nome da disciplina)
gpa_course_final <- aggregate(gpa ~ course_name, data=grades_stat3_valid, FUN=function(x) mean(x, na.rm=TRUE))

# GPA máximo entre todas as disciplinas
gpa_max_course <- max(gpa_course_final$gpa, na.rm = TRUE)

# Selecionar apenas disciplina(s) mais fácil(is)
disc_mais_faceis <- gpa_course_final[gpa_course_final$gpa == gpa_max_course, ]

# Mostrar resultados
cat("Disciplina(s) mais fácil(is):", paste(disc_mais_faceis$course_name, collapse=", "), "\n")

```

```{r}
#calculando quando o html foi gerado
cat(paste0("Este HTML foi gerado em: ", Sys.time()))
```


```{r}
dbDisconnect(conn)

```
