# =========================================================
# Lab 4-1: Merging and Joining Data Sources
# =========================================================

# ---------------------------------------------------------
# Step 1: Create Data
# ---------------------------------------------------------

# Load required packages
library(dplyr)
library(DT)

# Create the Customers table
customers_text <- "customer_id,name,email,city
1,John Smith,john@email.com,Seattle
2,Jane Doe,jane@email.com,Portland
3,Bob Wilson,bob@email.com,Vancouver
4,Alice Brown,alice@email.com,Seattle"

customers <- read.csv(text = customers_text, stringsAsFactors = FALSE)

# Create the Orders table
orders_text <- "order_id,customer_id,order_date,total_amount
1001,1,2023-01-15,135.98
1002,2,2023-01-16,89.99
1003,1,2023-01-20,79.99
1004,3,2023-01-22,125.50"

orders <- read.csv(text = orders_text, stringsAsFactors = FALSE)

# Create the Books table
books_text <- "book_id,title,author,price,category
101,Data Science Handbook,Smith & Jones,89.99,Technical
102,Python Programming,Johnson,45.99,Programming
103,Machine Learning,Davis,79.99,Technical
104,Web Development,Miller,65.50,Programming
105,Statistics Guide,Wilson,59.99,Technical"

books <- read.csv(text = books_text, stringsAsFactors = FALSE)

# Display tables
print("=== CUSTOMERS TABLE ===")
print(customers)

print("=== ORDERS TABLE ===")
print(orders)

print("=== BOOKS TABLE ===")
print(books)

# ---------------------------------------------------------
# Step 2: Understanding Keys
# ---------------------------------------------------------

print("=== PRIMARY KEYS ===")
print("Customers table: customer_id")
print("Orders table: order_id")
print("Books table: book_id")

print("\n=== FOREIGN KEYS ===")
print("Orders table: customer_id references customers.customer_id")

# Verify uniqueness
print("\n=== KEY UNIQUENESS CHECK ===")
print(paste("Unique customers:", length(unique(customers$customer_id))))
print(paste("Total customer records:", nrow(customers)))
print(paste("Unique orders:", length(unique(orders$order_id))))
print(paste("Total order records:", nrow(orders)))

# Customer-order relationships
print("\n=== CUSTOMER-ORDER RELATIONSHIPS ===")

relationship_summary <- orders %>%
  group_by(customer_id) %>%
  summarise(
    order_count = n(),
    total_spent = sum(total_amount)
  )

print(relationship_summary)

# ---------------------------------------------------------
# Step 3: Inner Joins
# ---------------------------------------------------------

customer_orders <- customers %>%
  inner_join(orders, by = "customer_id")

print("=== INNER JOIN: Customers with Orders ===")
print(customer_orders)

print(paste("\nOriginal customers:", nrow(customers)))
print(paste("Customers with orders:", nrow(customer_orders)))

customers_with_orders <- unique(customer_orders$customer_id)

customers_without_orders <- customers[
  !customers$customer_id %in% customers_with_orders,
]

print("\n=== CUSTOMERS WITHOUT ORDERS ===")
print(customers_without_orders)

# ---------------------------------------------------------
# Step 4: Left Joins
# ---------------------------------------------------------

all_customers_orders <- customers %>%
  left_join(orders, by = "customer_id")

print("=== LEFT JOIN: All Customers with Order Information ===")
print(all_customers_orders)

customer_activity <- all_customers_orders %>%
  group_by(customer_id, name, city) %>%
  summarise(
    order_count = sum(!is.na(order_id)),
    total_spent = sum(total_amount, na.rm = TRUE),
    .groups = 'drop'
  )

print("\n=== CUSTOMER ACTIVITY ANALYSIS ===")
print(customer_activity)

inactive_customers <- customer_activity[
  customer_activity$order_count == 0,
]

print("\n=== INACTIVE CUSTOMERS ===")
print(inactive_customers)

# ---------------------------------------------------------
# Step 5: Right Joins and Full Joins
# ---------------------------------------------------------

orders_customers <- customers %>%
  right_join(orders, by = "customer_id")

print("=== RIGHT JOIN: All Orders with Customer Information ===")
print(orders_customers)

full_customer_orders <- customers %>%
  full_join(orders, by = "customer_id")

print("\n=== FULL JOIN: Complete Dataset ===")
print(full_customer_orders)

print("\n=== JOIN TYPE COMPARISON ===")
print(paste("Inner join rows:", nrow(customer_orders)))
print(paste("Left join rows:", nrow(all_customers_orders)))
print(paste("Right join rows:", nrow(orders_customers)))
print(paste("Full join rows:", nrow(full_customer_orders)))

# ---------------------------------------------------------
# Step 6: Multiple Table Joins
# ---------------------------------------------------------

# Create order details table
order_details_text <- "order_id,book_id,quantity
1001,101,1
1001,102,2
1002,101,1
1003,103,1
1004,104,1
1004,105,1"

order_details <- read.csv(text = order_details_text, stringsAsFactors = FALSE)

print("=== ORDER DETAILS TABLE ===")
print(order_details)

# Multi-table join
complete_analysis <- customers %>%
  inner_join(orders, by = "customer_id") %>%
  inner_join(order_details, by = "order_id") %>%
  inner_join(books, by = "book_id")

print("\n=== COMPLETE ANALYSIS ===")
print(complete_analysis)

# Business insights
insights <- complete_analysis %>%
  group_by(name, category) %>%
  summarise(
    books_purchased = sum(quantity),
    total_spent = sum(price * quantity),
    .groups = 'drop'
  ) %>%
  arrange(desc(total_spent))

print("\n=== CUSTOMER PURCHASING PATTERNS BY CATEGORY ===")
print(insights)

# =========================================================
# Practice Exercises Dataset
# =========================================================

employees_text <- "employee_id,name,department_id,hire_date,salary
1,Sarah Johnson,1,2022-01-15,75000
2,Mike Chen,2,2021-06-10,82000
3,Lisa Rodriguez,1,2023-03-20,68000
4,David Kim,3,2020-11-05,95000
5,Emma Wilson,2,2022-08-12,78000
6,James Brown,1,2021-12-01,71000"

employees <- read.csv(text = employees_text, stringsAsFactors = FALSE)

departments_text <- "department_id,department_name,manager_name,budget
1,Marketing,Jennifer Adams,250000
2,Engineering,Robert Taylor,400000
3,Sales,Michael Davis,300000
4,HR,Patricia Wilson,150000"

departments <- read.csv(text = departments_text, stringsAsFactors = FALSE)

projects_text <- "project_id,project_name,employee_id,start_date,end_date,budget
101,Website Redesign,2,2023-01-01,2023-06-30,50000
102,Mobile App,2,2023-07-01,2023-12-31,75000
103,Marketing Campaign,1,2023-02-15,2023-05-15,30000
104,Sales Training,4,2023-01-10,2023-03-10,20000
105,Data Analytics,5,2023-04-01,2023-09-30,60000
106,Brand Strategy,3,2023-03-01,2023-08-31,40000"

projects <- read.csv(text = projects_text, stringsAsFactors = FALSE)

print("=== EMPLOYEES TABLE ===")
print(employees)

print("\n=== DEPARTMENTS TABLE ===")
print(departments)

print("\n=== PROJECTS TABLE ===")
print(projects)
