library(RSQLite)
library(expss)
library(dplyr)
library(magrittr)

dbfilename <- "data/football.db"
sqlite.driver <- dbDriver("SQLite")
database <- dbConnect(sqlite.driver, dbname = dbfilename)

dbListTables(database)

# R
games <- dbReadTable(database,"games")
teams <- dbReadTable(database,"teams")
rounds <- dbReadTable(database,"rounds")
events <- dbReadTable(database,"events")
seasons <- dbReadTable(database,"seasons")
leagues <- dbReadTable(database,"leagues")

games$team1 <- vlookup(games$team1_id, teams, "title", "id")
games$team2 <- vlookup(games$team2_id, teams, "title", "id")

games$league <- vlookup(vlookup(vlookup(games$round_id, rounds, "event_id", "id"), events, "league_id", ), leagues, "title", "id")
games$season <- vlookup(vlookup(vlookup(games$round_id, rounds, "event_id", "id"), events, "season_id", ), seasons, "title", "id")

games <- select(games, Date=play_at, HomeTeam=team1, AwayTeam=team2, FTHG=score1, FTAG=score2, League=league, Season=season)

games %<>% mutate(Date = as.Date(Date))

# SQL
games2 <- dbGetQuery(database,
          "SELECT 
            g.play_at AS Date, 
            t1.title AS HomeTeam, 
            t2.title AS AwayTeam,
            g.score1 AS FTHG, 
            g.score2 AS FTAG,
            l.title AS League,
            s.title AS Season
          FROM games g
            INNER JOIN teams t1 ON t1.id = g.team1_id
            INNER JOIN teams t2 ON t2.id = g.team2_id
            INNER JOIN rounds r ON r.id = g.round_id
            INNER JOIN events e ON e.id = r.event_id
            INNER JOIN leagues l ON l.id = e.league_id
            INNER JOIN seasons s ON s.id = e.season_id")

games2 %<>% mutate(Date = as.Date(Date))

table(games == games2)

dbDisconnect(database)

# remove future games?

saveRDS(games, "db_data.Rda")