library(tidyverse)
library(shiny)
library(DT)
library(RSQLite)
library(stringr)
library(plotly)
library(forcats)
library(markdown)

uniq <- function(df, ..., first = F)
{
  uniq_vector <- function(x, fst)
  {
    x %>%
      c %>%
      rle %>%
      .$lengths ->
    l

    if (fst)
    {
      l %>%
        cumsum %>%
        `-`(l) + 1
    } else
    {
      l %>% cumsum
    }
  }

  eval(substitute(alist(...))) %>%
    map_chr(as.character) %>%
    intersect(names(df)) %>%
    map(~uniq_vector(df[[.x]], first)) %>%
    reduce(union) %>%
    sort -> columns

  df[columns,]
}

Sys.setlocale("LC_TIME", "fr_FR.UTF-8")

dbConnect(SQLite(), "ecn.db") -> db

# Index villes et spés ----
db %>%
  tbl("trajectoires") %>%
  select(Discipline, Subdivision) %>%
  distinct %>%
  collect -> villespe

villespe %>%
  select(Subdivision) %>%
  distinct %>%
  filter(Subdivision != "") %>%
  arrange(Subdivision) %>%
  pull(Subdivision) -> villes

villespe %>%
  select(Discipline) %>%
  distinct %>%
  filter(!Discipline %>% str_detect("(car aucun vœu)|(malgré.*)|(pas de.*)")) %>%
  arrange(Discipline) %>%
  pull(Discipline) -> spes

# Max rank ----
db %>%
  tbl("trajectoires") %>%
  select(Etudiant) %>%
  distinct %>%
  collect %>%
  pull(Etudiant) %>%
  max ->
max_rank

db %>% dbDisconnect