library(data.table)
litedown::reactor(comment = "# ")


Products = rowwiseDT(
  id=,        name=, price=,   unit=, type=,
   1L,     "banana",   0.63,  "unit", "natural",
   2L,    "carrots",   0.89,    "lb", "natural",
   3L,    "popcorn",   2.99,  "unit", "processed",
   4L,       "soda",   1.49, "ounce", "processed",
   NA, "toothpaste",   2.99,  "unit", "processed"
)

NewTax = data.table(
  unit = c("unit", "ounce"),
  type = "processed",
  tax_prop = c(0.65, 0.20)
)

NewTax

set.seed(2156)

# NB: Jan 8, 2024 is a Monday.
receipt_dates = seq(from=as.IDate("2024-01-08"), length.out=10L, by="week")

ProductReceived = data.table(
  id=1:10, # unique identifier for an supply transaction
  date=receipt_dates,
  product_id=sample(c(NA, 1:3, 6L), size=10L, replace=TRUE), # NB: product '6' is not recorded in Products above.
  count=sample(c(50L, 100L, 150L), size=10L, replace=TRUE)
)

ProductReceived

set.seed(5415)

# Monday-Friday (4 days later) for each of the weeks present in ProductReceived
possible_weekdays <- as.IDate(sapply(receipt_dates, `+`, 0:4))

ProductSales = data.table(
  id = 1:10,
  date = sort(sample(possible_weekdays, 10L)),
  product_id = sample(c(1:3, 7L), size = 10L, replace = TRUE), # NB: product '7' is in neither Products nor ProductReceived.
  count = sample(c(50L, 100L, 150L), size = 10L, replace = TRUE)
)

ProductSales

Products[ProductReceived,
         on = c(id = "product_id")]

ProductsChangedName = setnames(copy(Products), "id", "product_id")
ProductsChangedName

ProductsChangedName[ProductReceived, on = .NATURAL]

ProductsKeyed = setkey(copy(Products), id)
key(ProductsKeyed)

ProductReceivedKeyed = setkey(copy(ProductReceived), product_id)
key(ProductReceivedKeyed)

ProductsKeyed[ProductReceivedKeyed]

Products[
  ProductReceived,
  on = c("id" = "product_id"),
  j = .(product_id = x.id,
        name = x.name,
        price,
        received_id = i.id,
        date = i.date,
        count,
        total_value = price * count)
]

dt1 = ProductReceived[
  Products,
  on = c("product_id" = "id"),
  by = .EACHI,
  j = .(total_value_received  = sum(price * count))
]

# alternative using multiple [] queries
dt2 = ProductReceived[
  Products,
  on = c("product_id" = "id"),
][, .(total_value_received  = sum(price * count)),
  by = "product_id"
]

identical(dt1, dt2)

NewTax[Products, on = c("unit", "type")]

# First Table
Products[ProductReceived,
         on = c("id" = "product_id"),
         nomatch = NULL]

# Second Table
ProductReceived[Products,
                on = .(product_id = id),
                nomatch = NULL]

Products[!ProductReceived,
         on = c("id" = "product_id")]

ProductReceived[!Products,
                on = c("product_id" = "id")]

SubSetRows = Products[
  ProductReceived,
  on = .(id = product_id),
  nomatch = NULL,
  which = TRUE
]

SubSetRows

SubSetRowsSorted = sort(unique(SubSetRows))

SubSetRowsSorted

Products[SubSetRowsSorted]

ProductReceived[Products,
                on = list(product_id = id)]

NewTax[Products,
       on = c("unit", "type")
][, ProductReceived[.SD,
                    on = list(product_id = id)],
  .SDcols = !c("unit", "type")]

ProductReceived[product_id == 1L]

ProductSales[product_id == 1L]

ProductReceived[ProductSales[list(1L),
                             on = "product_id",
                             nomatch = NULL],
                on = "product_id",
                allow.cartesian = TRUE]

ProductReceived[ProductSales,
                on = "product_id",
                allow.cartesian = TRUE]

ProductReceived[ProductSales[product_id == 1L],
                on = .(product_id),
                allow.cartesian = TRUE,
                mult = "first"]

ProductReceived[ProductSales[product_id == 1L],
                on = .(product_id),
                allow.cartesian = TRUE,
                mult = "last"]

ProductsTempId = copy(Products)[, temp_id := 1L]

AllProductsMix =
  ProductsTempId[ProductsTempId,
                 on = "temp_id",
                 allow.cartesian = TRUE]

AllProductsMix[, temp_id := NULL]

# Removing type to make easier to see the result when printing the table
AllProductsMix[, !c("type", "i.type")]

merge(x = Products,
      y = ProductReceived,
      by.x = "id",
      by.y = "product_id",
      all = TRUE,
      sort = FALSE)

ProductSalesProd2 = ProductSales[product_id == 2L]
ProductReceivedProd2 = ProductReceived[product_id == 2L]

ProductReceivedProd2[ProductSalesProd2,
                     on = "product_id",
                     allow.cartesian = TRUE
][date < i.date]

ProductReceivedProd2[ProductSalesProd2,
                     on = list(product_id, date < date)]

ProductReceivedProd2[ProductSalesProd2,
                     on = list(product_id, date < date),
                     nomatch = NULL]

x <- data.table(x_int = 2:4, lower = letters[1:3])
i <- data.table(i_int = c(2L, 4L, 5L), UPPER = LETTERS[1:3])
x[i, on = .(x_int >= i_int)]

x[i, on = .(x_int >= i_int), .(i_int = i.i_int, x_int = x.x_int, lower, UPPER)]

x[i, on = .(x_int >= i_int), .(i_int = i.i_int, x_int = x.x_int, lower, UPPER), nomatch = NULL]

ProductPriceHistory = data.table(
  product_id = rep(1:2, each = 3),
  date = rep(as.IDate(c("2024-01-01", "2024-02-01", "2024-03-01")), 2),
  price = c(0.59, 0.63, 0.65,  # Banana prices
            0.79, 0.89, 0.99)  # Carrot prices
)

ProductPriceHistory

ProductPriceHistory[ProductSales,
                    on = .(product_id, date),
                    roll = TRUE,
                    j = .(product_id, date, count, price)]

ProductPriceHistory[ProductSales,
                    on = .(product_id, date),
                    roll = TRUE,
                    nomatch = NULL,
                    j = .(product_id, date, count, price)]

ProductReceived[list(c(1L, 3L), 100L),
                on = c("product_id", "count")]

ProductReceived[list(c(1L, 3L), 100L),
                on = c("product_id", "count"),
                nomatch = NULL]

ProductReceived[!list(c(1L, 3L), 100L),
                on = c("product_id", "count")]

Products[c("banana","popcorn"),
         on = "name",
         nomatch = NULL]

Products[!"popcorn",
         on = "name"]

Products[ProductPriceHistory, 
         on = .(id = product_id), 
         price := i.price]

Products

Products[ProductPriceHistory,
         on = .(id = product_id),
         `:=`(price = last(i.price), last_updated = last(i.date)),
         by = .EACHI]

Products

cols <- setdiff(names(Products), "id")
ProductPriceHistory[, (cols) := 
  Products[.SD, on = .(id = product_id), .SD, .SDcols = cols]]
setnafill(ProductPriceHistory, fill=0, cols="price") # Handle missing values

ProductPriceHistory

