# =========================================================
# Lab 7-3: Summarization and Aggregation (FULL CODE FILE)
# =========================================================

# Load required packages
library(tidyverse)
library(sqldf)

# -------------------------------
# STEP 1: Create transaction data
# -------------------------------
set.seed(123)

sales_transactions <- data.frame(
  transaction_id = 1:1000,
  customer_id = sample(1:200, 1000, replace = TRUE),
  product_category = sample(c('Electronics', 'Clothing', 'Home', 'Books', 'Sports'), 1000, replace = TRUE),
  product_name = paste("Product", sample(1:50, 1000, replace = TRUE)),
  region = sample(c('North', 'South', 'East', 'West', 'Central'), 1000, replace = TRUE),
  sales_rep = sample(paste("Rep", 1:20), 1000, replace = TRUE),
  transaction_date = sample(seq(as.Date('2024-01-01'), as.Date('2024-12-31'), by="day"), 1000, replace = TRUE),
  quantity = sample(1:10, 1000, replace = TRUE),
  unit_price = round(runif(1000, 10, 500), 2),
  discount_percent = sample(c(0, 5, 10, 15, 20), 1000, replace = TRUE,
                            prob = c(0.5, 0.2, 0.15, 0.1, 0.05)),
  stringsAsFactors = FALSE
) %>%
  mutate(
    gross_amount = quantity * unit_price,
    discount_amount = gross_amount * (discount_percent / 100),
    net_amount = gross_amount - discount_amount,
    month = format(transaction_date, "%Y-%m"),
    quarter = paste0("Q", ceiling(as.numeric(format(transaction_date, "%m")) / 3))
  )

cat("DETAILED SALES TRANSACTIONS\n")
print(head(sales_transactions))

# -------------------------------
# STEP 2: Basic Aggregation
# -------------------------------

regional_monthly_sql <- sqldf("
  SELECT 
    region,
    month,
    COUNT(transaction_id) as transaction_count,
    SUM(net_amount) as total_revenue,
    AVG(net_amount) as avg_transaction_value,
    SUM(quantity) as total_units_sold
  FROM sales_transactions
  GROUP BY region, month
  ORDER BY region, month
")

cat("\nREGIONAL MONTHLY SUMMARY (SQL)\n")
print(head(regional_monthly_sql))

regional_monthly_tidy <- sales_transactions %>%
  group_by(region, month) %>%
  summarise(
    transaction_count = n(),
    total_revenue = sum(net_amount),
    avg_transaction_value = round(mean(net_amount), 2),
    total_units_sold = sum(quantity),
    .groups = "drop"
  ) %>%
  arrange(region, month)

cat("\nREGIONAL MONTHLY SUMMARY (TIDYVERSE)\n")
print(head(regional_monthly_tidy))

# -------------------------------
# STEP 3: Business Summaries
# -------------------------------

executive_summary <- sqldf("
  SELECT 
    quarter,
    COUNT(DISTINCT customer_id) as active_customers,
    COUNT(transaction_id) as total_transactions,
    SUM(net_amount) as total_revenue,
    AVG(net_amount) as avg_transaction_value,
    SUM(quantity) as total_units
  FROM sales_transactions
  GROUP BY quarter
  ORDER BY quarter
")

cat("\nEXECUTIVE SUMMARY\n")
print(executive_summary)

category_performance <- sqldf("
  SELECT 
    product_category,
    COUNT(transaction_id) as transactions,
    SUM(net_amount) as revenue,
    AVG(net_amount) as avg_transaction,
    SUM(quantity) as units_sold
  FROM sales_transactions
  GROUP BY product_category
  ORDER BY revenue DESC
")

cat("\nCATEGORY PERFORMANCE\n")
print(category_performance)

rep_performance <- sqldf("
  SELECT 
    sales_rep,
    COUNT(DISTINCT customer_id) as customers,
    COUNT(transaction_id) as transactions,
    SUM(net_amount) as total_sales,
    AVG(net_amount) as avg_transaction
  FROM sales_transactions
  GROUP BY sales_rep
  ORDER BY total_sales DESC
  LIMIT 10
")

cat("\nTOP SALES REPS\n")
print(rep_performance)

# -------------------------------
# STEP 4: Advanced Aggregation
# -------------------------------

monthly_trends <- sales_transactions %>%
  group_by(month) %>%
  summarise(
    revenue = sum(net_amount),
    transactions = n(),
    avg_transaction = mean(net_amount),
    customers = n_distinct(customer_id),
    .groups = "drop"
  ) %>%
  arrange(month) %>%
  mutate(
    revenue_growth = round((revenue / lag(revenue) - 1) * 100, 1)
  )

cat("\nMONTHLY TRENDS\n")
print(monthly_trends)

customer_segments <- sqldf("
  SELECT 
    CASE 
      WHEN total_spent >= 5000 THEN 'High Value'
      WHEN total_spent >= 2000 THEN 'Medium Value'
      WHEN total_spent >= 500 THEN 'Regular'
      ELSE 'Low Value'
    END as segment,
    COUNT(*) as customers,
    AVG(total_spent) as avg_value
  FROM (
    SELECT customer_id, SUM(net_amount) as total_spent
    FROM sales_transactions
    GROUP BY customer_id
  )
  GROUP BY segment
")

cat("\nCUSTOMER SEGMENTS\n")
print(customer_segments)

regional_ranking <- sales_transactions %>%
  group_by(region) %>%
  summarise(
    revenue = sum(net_amount),
    transactions = n(),
    customers = n_distinct(customer_id),
    .groups = "drop"
  ) %>%
  mutate(
    revenue_rank = rank(-revenue)
  ) %>%
  arrange(revenue_rank)

cat("\nREGIONAL RANKINGS\n")
print(regional_ranking)

# -------------------------------
# PRACTICE DATASET
# -------------------------------

set.seed(789)

retailcorp_sales <- data.frame(
  order_id = 1:800,
  customer_id = sample(1:150, 800, replace = TRUE),
  store_location = sample(c('Downtown', 'Mall', 'Suburban', 'Online'), 800, replace = TRUE),
  product_line = sample(c('Fashion', 'Electronics', 'Home & Garden', 'Beauty', 'Sports'), 800, replace = TRUE),
  brand = sample(paste("Brand", LETTERS[1:10]), 800, replace = TRUE),
  order_date = sample(seq(as.Date('2024-01-01'), as.Date('2024-12-31'), by="day"), 800, replace = TRUE),
  order_value = round(runif(800, 25, 800), 2),
  cost_of_goods = round(runif(800, 10, 400), 2),
  shipping_cost = round(runif(800, 0, 25), 2),
  customer_type = sample(c('New', 'Returning', 'VIP'), 800, replace = TRUE),
  stringsAsFactors = FALSE
) %>%
  mutate(
    profit = order_value - cost_of_goods - shipping_cost,
    profit_margin = round((profit / order_value) * 100, 1),
    month = format(order_date, "%Y-%m"),
    quarter = paste0("Q", ceiling(as.numeric(format(order_date, "%m")) / 3))
  )

cat("\nRETAILCORP DATASET\n")
print(head(retailcorp_sales))