############################################################
# Lab 7-1 Pivoting and Unpivoting
# RetailMetrics Analytics Lab
############################################################

# Load required packages
library(tidyverse)  # pivot_wider, pivot_longer
library(sqldf)      # SQL-based transformations
library(lubridate)  # date handling

############################################################
# Step 1: Setting Up R Environment and Sample Data
############################################################

sales_data <- data.frame(
  transaction_id = 1:36,
  store_id = rep(c('Store_A', 'Store_B', 'Store_C'), each = 12),
  product_category = rep(c('Electronics', 'Clothing', 'Home', 'Books'), 9),
  sale_month = rep(c('January', 'February', 'March'), 12),
  sales_amount = c(
    15000,12000,8000,5000,
    18000,14000,9500,6000,
    20000,16000,11000,7000,
    12000,10000,6000,4000,
    14000,11000,7000,4500,
    16000,13000,8000,5500,
    8000,6000,4000,2500,
    9000,7000,4500,3000,
    10000,8000,5000,3500
  ),
  units_sold = c(
    150,80,40,100,
    180,93,48,120,
    200,107,55,140,
    120,67,30,80,
    140,73,35,90,
    160,87,40,110,
    80,40,20,50,
    90,47,23,60,
    100,53,25,70
  ),
  stringsAsFactors = FALSE
)

cat("ORIGINAL SALES DATA (Long Format):\n")
print(head(sales_data, 12))

############################################################
# Step 2: Pivoting with Tidyverse (Wide Format)
############################################################

monthly_comparison <- sales_data %>%
  group_by(store_id, sale_month) %>%
  summarise(total_sales = sum(sales_amount), .groups = 'drop') %>%
  pivot_wider(names_from = sale_month,
              values_from = total_sales,
              names_prefix = "Sales_")

cat("\nMONTHLY SALES COMPARISON (Tidyverse):\n")
print(monthly_comparison)

category_performance <- sales_data %>%
  group_by(store_id, product_category) %>%
  summarise(total_sales = sum(sales_amount), .groups = 'drop') %>%
  pivot_wider(names_from = product_category,
              values_from = total_sales)

cat("\nCATEGORY PERFORMANCE BY STORE (Tidyverse):\n")
print(category_performance)

store_performance <- sales_data %>%
  group_by(sale_month, store_id) %>%
  summarise(total_sales = sum(sales_amount), .groups = 'drop') %>%
  pivot_wider(names_from = store_id,
              values_from = total_sales)

cat("\nSTORE PERFORMANCE BY MONTH (Tidyverse):\n")
print(store_performance)

multi_metric_pivot <- sales_data %>%
  group_by(store_id, product_category) %>%
  summarise(
    total_sales = sum(sales_amount),
    total_units = sum(units_sold),
    .groups = 'drop'
  ) %>%
  pivot_wider(
    names_from = product_category,
    values_from = c(total_sales, total_units),
    names_sep = "_"
  )

cat("\nMULTI-METRIC PIVOT (Tidyverse):\n")
print(multi_metric_pivot)

############################################################
# Step 3: Pivoting with SQL (CASE Statements)
############################################################

monthly_sql <- sqldf("
SELECT store_id,
SUM(CASE WHEN sale_month='January' THEN sales_amount ELSE 0 END) AS Sales_January,
SUM(CASE WHEN sale_month='February' THEN sales_amount ELSE 0 END) AS Sales_February,
SUM(CASE WHEN sale_month='March' THEN sales_amount ELSE 0 END) AS Sales_March
FROM sales_data
GROUP BY store_id
")

cat("\nMONTHLY SALES (SQL):\n")
print(monthly_sql)

category_sql <- sqldf("
SELECT store_id,
SUM(CASE WHEN product_category='Electronics' THEN sales_amount ELSE 0 END) AS Electronics,
SUM(CASE WHEN product_category='Clothing' THEN sales_amount ELSE 0 END) AS Clothing,
SUM(CASE WHEN product_category='Home' THEN sales_amount ELSE 0 END) AS Home,
SUM(CASE WHEN product_category='Books' THEN sales_amount ELSE 0 END) AS Books
FROM sales_data
GROUP BY store_id
")

cat("\nCATEGORY PERFORMANCE (SQL):\n")
print(category_sql)

store_sql <- sqldf("
SELECT sale_month,
SUM(CASE WHEN store_id='Store_A' THEN sales_amount ELSE 0 END) AS Store_A,
SUM(CASE WHEN store_id='Store_B' THEN sales_amount ELSE 0 END) AS Store_B,
SUM(CASE WHEN store_id='Store_C' THEN sales_amount ELSE 0 END) AS Store_C
FROM sales_data
GROUP BY sale_month
")

cat("\nSTORE PERFORMANCE (SQL):\n")
print(store_sql)

############################################################
# Step 4: Unpivoting (Tidyverse)
############################################################

monthly_long <- monthly_comparison %>%
  pivot_longer(
    cols = starts_with("Sales_"),
    names_to = "month",
    values_to = "sales_amount",
    names_prefix = "Sales_"
  )

cat("\nUNPIVOTED MONTHLY DATA:\n")
print(monthly_long)

multi_metric_long <- multi_metric_pivot %>%
  pivot_longer(
    cols = -store_id,
    names_to = c("metric_type", "category"),
    names_sep = "_",
    values_to = "value"
  )

cat("\nMULTI-METRIC LONG FORMAT:\n")
print(head(multi_metric_long))

############################################################
# Step 5: Unpivoting with SQL (UNION ALL)
############################################################

monthly_unpivot_sql <- sqldf("
SELECT store_id,'January' AS month,Sales_January AS sales FROM monthly_sql
UNION ALL
SELECT store_id,'February',Sales_February FROM monthly_sql
UNION ALL
SELECT store_id,'March',Sales_March FROM monthly_sql
")

cat("\nUNPIVOTED SQL MONTHLY DATA:\n")
print(monthly_unpivot_sql)

############################################################
# PRACTICE DATASET
############################################################

salesforce_data <- data.frame(
  record_id = 1:36,
  region = rep(c('North','South','East','West'), each=9),
  product_line = rep(c('Software','Hardware','Services'), 12),
  quarter = rep(c('Q1','Q2','Q3'), 12),
  revenue = c(
    250000,180000,120000,
    280000,200000,140000,
    320000,220000,160000,
    200000,150000,100000,
    230000,170000,120000,
    260000,190000,140000,
    300000,220000,150000,
    340000,250000,170000,
    380000,280000,190000,
    180000,130000,90000,
    210000,150000,110000,
    240000,170000,130000
  ),
  units_sold = c(
    500,180,60,
    560,200,70,
    640,220,80,
    400,150,50,
    460,170,60,
    520,190,70,
    600,220,75,
    680,250,85,
    760,280,95,
    360,130,45,
    420,150,55,
    480,170,65
  )
)

cat("\nSALESFORCE DATA:\n")
print(head(salesforce_data, 12))
