# Load required packages
library(sqldf)      # For running SQL queries on data frames
library(dplyr)      # For additional data manipulation (optional)

# Create customers data frame
customers <- data.frame(
  customer_id = 1:8,
  name = c('Alice Johnson', 'Bob Smith', 'Carol Davis', 'David Wilson',
           'Emma Brown', 'Frank Miller', 'Grace Lee', 'Henry Taylor'),
  age = c(25, 34, 28, 45, 31, 52, 29, 38),
  city = c('New York', 'Chicago', 'New York', 'Miami', 
           'Chicago', 'Boston', 'Seattle', 'Miami'),
  membership = c('Premium', 'Standard', 'Premium', 'VIP',
                 'Premium', 'Standard', 'VIP', 'Standard'),
  total_spent = c(450.75, 125.50, 680.25, 1250.00,
                  320.80, 89.99, 890.45, 156.30),
  join_date = as.Date(c('2023-01-15', '2023-02-20', '2023-01-10', '2022-11-05',
                        '2023-03-12', '2023-04-01', '2022-12-18', '2023-02-28')),
  stringsAsFactors = FALSE
)

# Create orders data frame
orders <- data.frame(
  order_id = 101:110,
  customer_id = c(1, 2, 3, 1, 4, 5, 3, 6, 7, 4),
  book_title = c('Python Programming Basics', 'Data Science Fundamentals',
                 'Web Design Mastery', 'SQL for Beginners', 'Advanced Python Techniques',
                 'Machine Learning Guide', 'JavaScript Essentials', 'Digital Marketing 101',
                 'Data Visualization with Python', 'Cloud Computing Basics'),
  price = c(35.99, 45.00, 28.50, 32.75, 55.99, 48.25, 39.99, 25.00, 42.50, 38.75),
  order_date = as.Date(c('2023-05-01', '2023-05-02', '2023-05-03', '2023-05-04',
                         '2023-05-05', '2023-05-06', '2023-05-07', '2023-05-08',
                         '2023-05-09', '2023-05-10')),
  status = c('Shipped', 'Processing', 'Shipped', 'Delivered', 'Shipped',
             'Cancelled', 'Processing', 'Delivered', 'Shipped', 'Processing'),
  category = c('Programming', 'Data Science', 'Design', 'Database', 'Programming',
               'Data Science', 'Programming', 'Marketing', 'Data Science', 'Technology'),
  stringsAsFactors = FALSE
)

# Display our sample data using R
cat("CUSTOMERS DATA FRAME:\n")
print(customers)

cat("\nORDERS DATA FRAME:\n")
print(orders)

cat("\nData Summary:\n")
cat("Number of customers:", nrow(customers), "\n")
cat("Number of orders:", nrow(orders), "\n")
cat("Membership levels:", paste(unique(customers$membership), collapse = ", "), "\n")
cat("Order statuses:", paste(unique(orders$status), collapse = ", "), "\n")


# =========================
# PRACTICE DATASET
# =========================

tech_customers <- data.frame(
  customer_id = 201:210,
  first_name = c('Sarah', 'John', 'Maria', 'David', 'Lisa', 
                 'Michael', 'Jennifer', 'Robert', 'Amanda', 'Kevin'),
  last_name = c('Connor', 'Smith', 'Garcia', 'Johnson', 'Wang',
                'Brown', 'Davis', 'Miller', 'Wilson', 'Taylor'),
  email = c('sarah.connor@email.com', 'john.smith@email.com', 'maria.garcia@email.com',
            'david.j@email.com', 'lisa.wang@email.com', 'mike.brown@email.com',
            'jen.davis@email.com', 'rob.miller@email.com', 'amanda.w@email.com',
            'kevin.taylor@email.com'),
  age = c(32, 28, 45, 35, 29, 52, 38, 41, 26, 33),
  city = c('Los Angeles', 'New York', 'Miami', 'Chicago', 'Seattle',
           'Boston', 'Austin', 'Denver', 'Portland', 'Phoenix'),
  state = c('CA', 'NY', 'FL', 'IL', 'WA', 'MA', 'TX', 'CO', 'OR', 'AZ'),
  membership_level = c('Gold', 'Silver', 'Platinum', 'Bronze', 'Gold',
                       'Silver', 'Platinum', 'Bronze', 'Gold', 'Silver'),
  account_balance = c(125.50, 89.25, 340.75, 45.00, 210.30,
                      156.80, 425.60, 67.90, 189.45, 98.75),
  last_purchase_date = as.Date(c('2023-04-15', '2023-05-02', '2023-04-28', '2023-03-20',
                                 '2023-05-10', '2023-04-05', '2023-05-08', '2023-03-15',
                                 '2023-05-12', '2023-04-22')),
  newsletter_subscriber = c(TRUE, FALSE, TRUE, TRUE, FALSE, TRUE, TRUE, FALSE, TRUE, FALSE),
  stringsAsFactors = FALSE
)

cat("TECHBOOKS CUSTOMER DATA:\n")
print(tech_customers)

cat("\n=== DATASET OVERVIEW ===\n")
cat("Total customers:", nrow(tech_customers), "\n")
cat("Membership levels:", paste(unique(tech_customers$membership_level), collapse = ", "), "\n")
cat("States represented:", paste(unique(tech_customers$state), collapse = ", "), "\n")
cat("Newsletter subscribers:", sum(tech_customers$newsletter_subscriber), "out of", nrow(tech_customers), "\n")
cat("Average account balance: $", round(mean(tech_customers$account_balance), 2), "\n")