---
title: "Video Games Dashboard"
output: 
  flexdashboard::flex_dashboard:
    storyboard: true
    social: menu
    source: embed
---

```{r setup, include=FALSE}
library(flexdashboard)
library(ggplot2)
library(ggthemes)
library(plotly)
library(DBI)
library(dbplyr)
library(duckdb)
library(readr)
library(dplyr)
library(stringr)
knitr::opts_chunk$set(echo = TRUE)
setwd("/Users/irania/Downloads")

vgsales <- read_csv("vgsales.csv")
ratings <- read_csv("ratings_data.csv")    

con <- dbConnect(duckdb::duckdb(), dbdir = "vgames.duckdb",read_only = FALSE)

vgsales_clean <- vgsales %>%
  mutate(Name = str_trim(tolower(Name)))

ratings_clean <- ratings %>%
  rename(Name = `Game Title`) %>%  
  mutate(Name = str_trim(tolower(Name)))

dbWriteTable(con, "vgsales", vgsales_clean, overwrite = TRUE)
dbWriteTable(con, "ratings", ratings_clean, overwrite = TRUE)


joined_data <- tbl(con, "vgsales") %>%
  left_join(tbl(con, "ratings"), by = "Name")
```

### Average Global Sales by Genre


```{r echo=FALSE}
ggplotly(
  ggplot(joined_data, mapping = aes(x = Genre.x, y = Global_Sales)) +
    geom_point(aes(color = Genre.x, shape = Genre.x, size = 2, alpha = 0.6, width= 0.2)) +  
    geom_smooth(method = 'lm', se = TRUE, color = 'darkblue', size = 1) +  
    labs(
      title = 'Average Global Sales by Genre',
      x = 'Genre',
      y = 'Avg Sales (Millions)',
      color = 'Genre',
      shape = 'Genre'
    ) +
    theme_minimal() +  # Minimal theme for better readability
    theme(
      plot.title = element_text(hjust = 0.5, size = 16, face = "bold"),  
      plot.subtitle = element_text(hjust = 0.5, size = 12),  
      axis.title = element_text(size = 12, face = "bold"),  
      axis.text = element_text(size = 10),  # Axis text size
      strip.text = element_text(size = 12, face = "bold"),  
      legend.position = "top"  
    ))
```

*** 
This data shows how global sales vary by genre with individual points representing games.The distribution and spread of sales within each genre, you’ll see clusters of points vertically aligned per genre. Overall, the linear trend line summarizes overall relationship between genre and sales, though genre is categorical so the trend is more of a general guide.


### Average Global Sales by Genre and Game Mode


```{r echo=FALSE}
joined_data %>%
  filter(!is.na(Genre.x), !is.na(`Game Mode`), !is.na(Global_Sales)) %>%
  group_by(Genre.x, `Game Mode`) %>%
  summarise(Average_Sales = mean(Global_Sales, na.rm = TRUE), .groups = 'drop') %>%
  ggplot(aes(x = Genre.x, y = Average_Sales, fill = Genre.x)) +
  geom_col() +
  facet_wrap(~ `Game Mode`) +
  labs(
    title = 'Average Global Sales by Genre and Game Mode',
    x = 'Genre',
    y = 'Average Global Sales (Millions)'
  ) +
  theme_minimal() +
  theme(
    axis.text.x = element_text(angle = 45, hjust = 1),
    plot.title = element_text(hjust = 0.5, size = 16, face = "bold"),
    strip.text = element_text(size = 12, face = "bold"),
    legend.position = "none"
  )
```

***
Here we see Which genres tend to have higher or lower average global sales within each game mode.How sales performance changes for the same genre across different game modes.
For example, maybe Action games have higher sales on Multiplayer mode than Single-player, or vice versa.


### Avg Sales by Age Group, Faceted by Genre

```{r echo=FALSE}
joined_data %>%
  filter(!is.na(Genre.x), !is.na(`Age Group Targeted`), !is.na(Global_Sales)) %>%
  group_by(Genre.x, `Age Group Targeted`) %>%
  summarise(avg_sales = mean(Global_Sales, na.rm = TRUE), .groups = "drop") %>%
  ggplot(aes(x = `Age Group Targeted`, y = avg_sales, fill = `Age Group Targeted`)) +
  geom_col() +
  facet_wrap(~ Genre.x, scales = "free_y") +
  labs(
    title = "Avg Sales by Age Group, Faceted by Genre",
    x = "Age Group",
    y = "Avg Global Sales"
  ) +
  theme_minimal() +
  theme(
    strip.text = element_text(size = 11, face = "bold"),
    axis.text.x = element_text(angle = 45, hjust = 1),
    plot.title = element_text(hjust = 0.5, face = "bold", size = 14),
    legend.position = "none"
  )

```

***
This plot provides insight into which age demographics contribute most to sales for different game genres, helping understand audience preferences and market performance segmented by age and genre.


### Critic Score vs Global Sales

```{r echo=FALSE}
joined_data %>%
  filter(!is.na(Critic_Score), !is.na(Global_Sales), !is.na(Genre.x)) %>%
  ggplot(aes(x = Critic_Score, y = Global_Sales)) +
  geom_point(aes(color = Genre.x), alpha = 0.6, size = 2) +
  geom_smooth(method = "lm", se = FALSE, color = "darkred", size = 1) +
  labs(
    title = "Critic Score vs Global Sales",
    subtitle = "Trend line shows linear relationship across all genres",
    x = "Critic Score",
    y = "Global Sales (Millions)",
    color = "Genre.x"
  ) +
  theme_minimal() +
  theme(
    plot.title = element_text(hjust = 0.5, size = 16, face = "bold"),
    plot.subtitle = element_text(hjust = 0.5, size = 12),
    axis.title = element_text(size = 12, face = "bold"),
    axis.text = element_text(size = 10),
    legend.position = "right"
  )
```

***
This plot visually investigates whether better-reviewed games tend to sell better globally, and how this trend looks across different game genres. It’s useful for understanding if critic opinions impact commercial success in the gaming industry.

### Number of Games by Platform and Genre 

```{r echo=FALSE}
joined_data %>%
  count(Platform.y, Genre.x) %>%
  filter(n > 10) %>%
  ggplot(aes(x = Platform.y, y = n, fill = Genre.x)) +
  geom_col(position = "dodge") +
  labs(
    title = "Number of Games by Platform and Genre",
    x = "Platform",
    y = "Number of Games",
    fill = "Genre"
  ) +
  theme_minimal() +
  theme(
    axis.text.x = element_text(angle = 45, hjust = 1),
    plot.title = element_text(hjust = 0.5, size = 16, face = "bold"),
    axis.title = element_text(size = 12, face = "bold")
  )
```

***
This bar chart helps us compare the number of games available by genre on each gaming platform, showing where certain genres are more common and highlighting platform-specific gaming trends.

### Global Sales Distribution by Platform

```{r echo=FALSE}
joined_data %>%
  filter(!is.na(Platform.x), !is.na(Global_Sales)) %>%
  ggplot(aes(x = Platform.x, y = Global_Sales, color = Platform.x)) +
  geom_boxplot(outlier.shape = NA, alpha = 0.5) +   
  geom_jitter(width = 0.2, alpha = 0.3, size = 1.5) +  
  labs(
    title = "Global Sales Distribution by Platform",
    x = "Platform",
    y = "Global Sales (Millions)",
    color = "Platform"
  ) +
  theme_minimal() +
  theme(
    plot.title = element_text(hjust = 0.5, size = 16, face = "bold"),
    axis.title = element_text(size = 14),
    axis.text.x = element_text(angle = 45, hjust = 1, size = 10),
    axis.text.y = element_text(size = 12),
    legend.position = "none"  
  )
```

***
This plot provides a detailed view of how game sales vary by platform, combining summary statistics (boxplots) with actual data points, helping identify which platforms tend to have higher or more consistent sales, and the spread of game success on each platform.

### Heatmap of Game Counts by Mode and Age Group

```{r echo=FALSE}

clean_data <- joined_data %>%
  filter(!is.na(`Game Mode`), !is.na(`Age Group Targeted`)) %>%
  count(`Game Mode`, `Age Group Targeted`)

ggplotly(
  ggplot(clean_data, aes(x = `Game Mode`, y = `Age Group Targeted`, fill = n)) +
    geom_tile(color = "white") +
    scale_fill_viridis_c() +
    labs(
      title = "Heatmap of Game Counts by Mode and Age Group",
      x = "Game Mode",
      y = "Age Group Targeted",
      fill = "Game Count"
    ) +
    theme_minimal() +
    theme(
      plot.title = element_text(hjust = 0.5, face = "bold", size = 14)
    )
)
```

***
This heatmap is great visualization for market analysis or targeting game development, showing where the concentration of games lies in terms of gameplay style and audience.
It can inform stakeholders about popular game modes for different age demographics or highlight underserved segments.

### Platform and User Count

```{r echo=FALSE}
ggplot(joined_data, aes(x = Platform.y, y = User_Count, fill = Platform.y)) +
  geom_boxplot() +
  labs(
    title = "Platform and User Count",
    x = "Platform",
    y = "User Count"
  ) +
  theme_minimal() +
  theme(legend.position = "none") 
```

*** 
This plot helps us understand how user engagement differs by platform. For example, if one platform consistently has higher user counts, it may indicate a larger or more active user base. This is useful for identifying which platforms attract more players or receive more attention in the dataset.