Ship Till X

library(tidyverse)
library(arrow)
library(hms)
library(tinytable)

orders <- arrow::read_parquet("./data/orders_recent.parquet") |>
  mutate(
    date_of_date_purchased = as.Date(date_purchased),
    time_of_date_purchased = as_hms(format(date_purchased, "%H:%M:%S"))
  ) |>
  left_join(fiscal_calendar, by = c("date_of_date_purchased" = "date")) |>
  filter(fiscal_year == 2024) |>
  select(
    orders_id,
    date_of_date_purchased,
    time_of_date_purchased,
    fiscal_week = fiscl_week,
    day_of_week
  )

head(orders)

# Define the cutoff times for Weeks 1-18 (Monday to Friday)
cutoff_times_1_18 <- c(
  "08:00:00",  # Monday (1)
  "10:00:00",  # Tuesday (2)
  "12:00:00",  # Wednesday (3)
  "14:00:00",  # Thursday (4)
  "14:00:00"   # Friday (5)
)

# Define the cutoff times for Weeks 19-52 (Monday to Saturday)
cutoff_times_19_52 <- c(
  "08:00:00",  # Monday (1)
  "10:00:00",  # Tuesday (2)
  "12:00:00",  # Wednesday (3)
  "12:00:00",  # Thursday (4)
  "14:00:00",  # Friday (5)
  "14:00:00"   # Saturday (6)
)

# Adjust shipping day function with variable cutoff times for each period
adjust_shipping_day <- function(day_of_week, time_of_day, fiscal_week) {
  
  # Determine the shipping days and cutoff times based on fiscal week
  if (fiscal_week <= 18) {
    shipping_days <- 1:5  # Monday to Friday
    cutoff_times <- cutoff_times_1_18
  } else {
    shipping_days <- 1:6  # Monday to Saturday
    cutoff_times <- cutoff_times_19_52
  }
  
  # Check if the current day is a shipping day
  if (day_of_week %in% shipping_days) {
    # Get the cutoff time for the current day
    cutoff_time <- as_hms(cutoff_times[which(shipping_days == day_of_week)])
    
    # If placed after the cutoff time, shift to the next day
    if (time_of_day > cutoff_time) {
      next_day <- day_of_week + 1
    } else {
      next_day <- day_of_week
    }
  } else {
    # If current day is not a shipping day, start from the next day
    next_day <- day_of_week + 1
  }
  
  # Wrap around if next_day exceeds 7 (Sunday)
  if (next_day > 7) {
    next_day <- next_day - 7
  }
  
  # Find the next available shipping day
  next_shipping_day <- shipping_days[shipping_days >= next_day][1]
  
  # If no shipping day is found, wrap around to the first shipping day
  if (is.na(next_shipping_day)) {
    next_shipping_day <- shipping_days[1]
  }
  
  return(next_shipping_day)
}

# Create a vector of day labels starting from Monday (1) to Sunday (7)
day_labels <- c("Monday",
                "Tuesday",
                "Wednesday",
                "Thursday",
                "Friday",
                "Saturday",
                "Sunday")

# Apply the adjusted shipping day function to orders
orders <- orders |>
  mutate(
    day_of_week = wday(date_of_date_purchased, label = FALSE, week_start = 1),
    time_of_date_purchased = as_hms(time_of_date_purchased),
    adjusted_day = mapply(
      adjust_shipping_day,
      day_of_week,
      time_of_date_purchased,
      fiscal_week
    ),
    adjusted_day_label = day_labels[adjusted_day]
  )

# Calculate distributions for Weeks 1-18
distribution_1_18 <- orders |>
  filter(fiscal_week <= 18) |>
  mutate(week_set = "Weeks 1-18") |>
  group_by(adjusted_day, adjusted_day_label, week_set) |>
  summarize(order_count = n(), .groups = "drop") |>
  ungroup() |>  # Remove grouping to calculate total orders across all days
  mutate(
    total_orders = sum(order_count),
    percentage = round((order_count / total_orders) * 100, 2)
  )

# Calculate distributions for Weeks 19-52
distribution_19_52 <- orders |>
  filter(fiscal_week >= 19) |>
  mutate(week_set = "Weeks 19-52") |>
  group_by(adjusted_day, adjusted_day_label, week_set) |>
  summarize(order_count = n(), .groups = "drop") |>
  ungroup() |>  # Remove grouping
  mutate(
    total_orders = sum(order_count),
    percentage = round((order_count / total_orders) * 100, 2)
  )

# Combine the distributions
combined_distribution <- bind_rows(distribution_1_18, distribution_19_52) |>
  select(!total_orders) |>  # Remove the total orders column
  select(!order_count) # Remove the order count column)

# Display the final distribution
tt(combined_distribution,
   theme = "striped")