library(tidyverse)
# Constants to be used in the tables
FUN <- "Fun"
WIN <- "Win"
SOLO <- "Solo"
TEAM <- "Team"

YES <- "Yes"
NO <- "No"
SKIP <- "Skip"

INSIDE <- "Inside"
OUTSIDE <- "Outside"
# Create sport table
sport <- tribble(~code,~sport_name,
                 "INDF","Indoor Football",
                 "OUTF","Outdoor Football",
                 "CARD","Cardio",
                 "CYCL","Cycling",
                 "GOLF","Golfing",
                 "AERO","Water Aerobics")

# Create persons
person <- tribble(~name,~queried_sport,~context,~reason,~likes_ball_sports,~limited,~lacks_time,~place,~wants_to_stay_active,~verdict,
                 "Brad","INDF",TEAM, FUN,T,F, SKIP, INSIDE,SKIP,F,
                 "Kyle","INDF",TEAM, WIN,T,F, SKIP, INSIDE,SKIP,T,
                 "Tessa","CARD",TEAM, WIN,SKIP,F,F,SKIP,T,F,
                 "Joliene","CARD",SOLO,FUN,SKIP,F,T,SKIP,T,T,
                 "Trixie","OUTF",TEAM, WIN,T,F,SKIP,OUTSIDE,SKIP,T,
                 "Ryan","OUTF",TEAM, FUN,T,T,SKIP,OUTSIDE,SKIP,F,
                 "Alex","CYCL",TEAM, FUN,SKIP,F,T,OUTSIDE,F,F,
                 "Elon","CYCL",SOLO, FUN,SKIP,F,T,OUTSIDE,T,T,
                 "Willem","GOLF",TEAM, WIN,SKIP,T,SKIP,OUTSIDE,SKIP,F,
                 "Boris","GOLF",SOLO, FUN,SKIP,T,SKIP,OUTSIDE,SKIP,T,
                 "Doutzen","AERO",TEAM,SKIP,SKIP,T,SKIP,INSIDE,SKIP,T)

# 10 queries

#1 Get the queried sport name per person
sport_by_person <- person %>% inner_join(sport, by = c("queried_sport" = "code")) %>%
    select(name, sport_name, verdict)

#2 Get people that had a negative verdict
negative_verdict <- person %>% filter(verdict == F) %>% inner_join(sport, by = c("queried_sport" = "code")) %>%
  select(name, sport_name, verdict)

#3 Get the healthy people: No limit condition
healthy_persons <- person %>% filter(limited == F) %>% select(name, verdict)

#4 Get people with limitations
limited_persons <- person %>% filter(limited == F) %>% select(name, verdict)

#5 Get people who prefer to sport outside
outside_people <- person %>% filter(place == OUTSIDE) %>% select(name, verdict)

#6 Get all participants querying for football
football_queries <- person %>% inner_join(sport, by = c("queried_sport" = "code")) %>%
  filter(queried_sport == "INDF" | queried_sport == "OUTF") %>% select(name, sport_name, verdict)

#7 Get all limited people with a positive verdict
limited_positive <- limited_persons %>% filter(verdict== T)

#8 Get the people with a negative verdict for football
football_negative <- person %>%
  filter(verdict == F &(queried_sport == "INDF" | queried_sport == "OUTF")) %>% select(name, sport_name, verdict)

#9 Filter on positive verdict for cardio and cycling
cardio_sports_positive <- person %>%
  filter(verdict == T &(queried_sport == "CYCL" | queried_sport == "CARD")) %>% select(name, queried_sport, verdict)

#10 Get people who like to sport inside
all_inside <- person %>%
  filter(place == INSIDE) %>% select(name, queried_sport,verdict)