# =========================================================
# Lab 6-1 SQL Calculated Fields and Formulas (FULL SCRIPT)
# =========================================================

library(sqldf)
library(dplyr)
library(lubridate)

# -------------------------
# STEP 1: DATASET (DEMO)
# -------------------------

products <- data.frame(
  product_id = 1:8,
  product_name = c('Wireless Headphones', 'Smartphone Case', 'Bluetooth Speaker',
                   'Laptop Stand', 'USB Cable', 'Power Bank', 'Tablet Holder', 'Webcam'),
  category = c('Electronics', 'Accessories', 'Electronics', 'Accessories',
               'Accessories', 'Electronics', 'Accessories', 'Electronics'),
  cost_price = c(45.00, 8.50, 25.00, 15.00, 3.00, 20.00, 12.00, 35.00),
  selling_price = c(79.99, 19.99, 49.99, 29.99, 9.99, 39.99, 24.99, 69.99),
  stringsAsFactors = FALSE
)

customers <- data.frame(
  customer_id = 1:10,
  customer_name = c('Alice Johnson', 'Bob Smith', 'Carol Davis', 'David Wilson',
                    'Emma Brown', 'Frank Miller', 'Grace Lee', 'Henry Taylor',
                    'Iris Chen', 'Jack Robinson'),
  birth_date = as.Date(c('1990-03-15','1985-07-22','1992-11-08','1978-05-14',
                         '1988-09-30','1975-12-03','1995-02-18','1982-08-25',
                         '1991-06-12','1987-04-07')),
  registration_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',
                                '2023-05-05','2023-03-20')),
  city = c('New York','Chicago','New York','Miami','Chicago',
           'Boston','Seattle','Miami','Los Angeles','Boston'),
  stringsAsFactors = FALSE
)

sales <- data.frame(
  sale_id = 1:15,
  customer_id = c(1,2,3,1,4,5,3,6,7,2,8,9,4,10,5),
  product_id = c(1,2,3,4,5,1,6,7,8,3,2,4,1,5,6),
  quantity = c(1,2,1,1,3,1,2,1,1,1,1,1,1,2,1),
  sale_date = as.Date(c('2023-06-01','2023-06-02','2023-06-03','2023-06-04',
                        '2023-06-05','2023-06-06','2023-06-07','2023-06-08',
                        '2023-06-09','2023-06-10','2023-06-11','2023-06-12',
                        '2023-06-13','2023-06-14','2023-06-15')),
  stringsAsFactors = FALSE
)

cat("PRODUCTS\n"); print(products)
cat("\nCUSTOMERS\n"); print(customers)
cat("\nSALES\n"); print(sales)

# -------------------------
# STEP 2: CALCULATED FIELDS
# -------------------------

product_metrics <- sqldf("
SELECT 
  product_id,
  product_name,
  category,
  cost_price,
  selling_price,
  selling_price - cost_price AS profit_per_unit,
  ROUND((selling_price - cost_price) / selling_price * 100,2) AS profit_margin_percent
FROM products
")

sales_calc <- sqldf("
SELECT 
  s.sale_id,
  s.customer_id,
  p.product_name,
  s.quantity,
  p.selling_price,
  s.quantity * p.selling_price AS revenue,
  s.quantity * p.cost_price AS cost,
  (s.quantity * p.selling_price) - (s.quantity * p.cost_price) AS profit
FROM sales s
JOIN products p ON s.product_id = p.product_id
")

cat("\nPRODUCT METRICS\n"); print(product_metrics)
cat("\nSALES CALC\n"); print(head(sales_calc))

# -------------------------
# STEP 3: AGGREGATES
# -------------------------

customer_summary <- sqldf("
SELECT 
  c.customer_id,
  c.customer_name,
  SUM(s.quantity * p.selling_price) AS total_spent,
  COUNT(s.sale_id) AS orders
FROM customers c
LEFT JOIN sales s ON c.customer_id = s.customer_id
LEFT JOIN products p ON s.product_id = p.product_id
GROUP BY c.customer_id, c.customer_name
")

product_perf <- sqldf("
SELECT 
  p.product_id,
  p.product_name,
  SUM(s.quantity) AS units_sold,
  SUM(s.quantity * p.selling_price) AS revenue
FROM products p
LEFT JOIN sales s ON p.product_id = s.product_id
GROUP BY p.product_id, p.product_name
")

cat("\nCUSTOMER SUMMARY\n"); print(customer_summary)
cat("\nPRODUCT PERFORMANCE\n"); print(product_perf)

# -------------------------
# STEP 4: CASE LOGIC
# -------------------------

customer_segments <- sqldf("
SELECT 
  c.customer_id,
  c.customer_name,
  SUM(s.quantity * p.selling_price) AS spent,
  CASE 
    WHEN SUM(s.quantity * p.selling_price) >= 200 THEN 'VIP'
    WHEN SUM(s.quantity * p.selling_price) >= 100 THEN 'Premium'
    WHEN SUM(s.quantity * p.selling_price) > 0 THEN 'Regular'
    ELSE 'New'
  END AS segment
FROM customers c
LEFT JOIN sales s ON c.customer_id = s.customer_id
LEFT JOIN products p ON s.product_id = p.product_id
GROUP BY c.customer_id, c.customer_name
")

product_class <- sqldf("
SELECT 
  product_id,
  product_name,
  CASE 
    WHEN selling_price > 50 THEN 'High Price'
    WHEN selling_price BETWEEN 20 AND 50 THEN 'Medium Price'
    ELSE 'Low Price'
  END AS price_class
FROM products
")

cat("\nCUSTOMER SEGMENTS\n"); print(customer_segments)
cat("\nPRODUCT CLASS\n"); print(product_class)

# -------------------------
# STEP 5: DATE ANALYSIS
# -------------------------

customer_time <- sqldf("
SELECT 
  customer_id,
  customer_name,
  ROUND((JULIANDAY('2023-06-15') - JULIANDAY(birth_date))/365.25) AS age,
  ROUND(JULIANDAY('2023-06-15') - JULIANDAY(registration_date)) AS days_active
FROM customers
")

lifecycle <- sqldf("
SELECT 
  c.customer_id,
  c.customer_name,
  COUNT(s.sale_id) AS purchases,
  MAX(s.sale_date) AS last_purchase
FROM customers c
LEFT JOIN sales s ON c.customer_id = s.customer_id
GROUP BY c.customer_id
")

cat("\nCUSTOMER TIME\n"); print(customer_time)
cat("\nLIFECYCLE\n"); print(lifecycle)

# =========================================================
# PRACTICE DATA (ALL INCLUDED IN SAME FILE)
# =========================================================

tech_products <- data.frame(
  product_id = 1:12,
  product_name = c('Gaming Laptop','Wireless Mouse','Mechanical Keyboard','Monitor 24"',
                   'Graphics Card','SSD Drive','RAM Module','Cooling Fan',
                   'Power Supply','Motherboard','CPU Processor','Gaming Chair'),
  category = c('Computers','Accessories','Accessories','Displays',
               'Components','Storage','Components','Components',
               'Components','Components','Components','Furniture'),
  cost_price = c(800,25,75,180,450,85,120,35,90,200,350,250),
  selling_price = c(1299,49,129,299,699,149,199,59,149,329,549,399),
  stringsAsFactors = FALSE
)

tech_customers <- data.frame(
  customer_id = 1:15,
  customer_name = c('Alex Johnson','Beth Smith','Carlos Garcia','Diana Lee','Ethan Brown',
                    'Fiona Davis','George Wilson','Hannah Miller','Ivan Chen','Julia Taylor',
                    'Kevin Martinez','Laura Anderson','Mike Thompson','Nina Rodriguez','Oscar Kim'),
  birth_date = as.Date(c('1995-03-15','1988-07-22','1992-11-08','1985-05-14','1990-09-30',
                         '1987-12-03','1993-02-18','1991-08-25','1989-06-12','1994-04-07',
                         '1986-10-20','1996-01-15','1984-09-08','1997-03-25','1983-11-12')),
  registration_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','2023-05-05','2023-03-20',
                                '2022-10-15','2023-04-10','2022-09-22','2023-05-15','2022-08-30')),
  city = c('New York','Chicago','Los Angeles','Houston','Phoenix',
           'Philadelphia','San Antonio','San Diego','Dallas','San Jose',
           'Austin','Jacksonville','Fort Worth','Columbus','Charlotte'),
  stringsAsFactors = FALSE
)

tech_sales <- data.frame(
  sale_id = 1:20,
  customer_id = c(1,2,3,1,4,5,3,6,7,2,8,9,4,10,5,11,12,13,14,15),
  product_id = c(1,2,3,4,5,6,7,8,9,10,11,12,2,3,4,5,6,7,8,9),
  quantity = c(1,2,1,1,1,2,1,3,1,1,1,1,1,1,1,1,1,2,1,1),
  sale_date = as.Date(c('2023-06-01','2023-06-02','2023-06-03','2023-06-05','2023-06-06',
                        '2023-06-07','2023-06-08','2023-06-10','2023-06-11','2023-06-12',
                        '2023-06-13','2023-06-14','2023-06-15','2023-06-16','2023-06-17',
                        '2023-06-18','2023-06-19','2023-06-20','2023-06-21','2023-06-22')),
  stringsAsFactors = FALSE
)

cat("\nTECHSTORE DATA LOADED\n")
cat("Products:", nrow(tech_products), "\n")
cat("Customers:", nrow(tech_customers), "\n")
cat("Sales:", nrow(tech_sales), "\n")