# Sample original dataframe

library(lubridate)
library(gt)
library(gtExtras)
library(dplyr)
options(width = 200)

# read raw data from gsheet
df <- read.csv("https://docs.google.com/spreadsheets/d/e/2PACX-1vRoy-pB7ibFE9McEQq9rOAeZoq71h7Duta9VzH160Wc0AHWLwuOxgaY5j_XuMjsZ7Rnm0KrrwRQrH9I/pub?gid=1141290061&single=true&output=csv")

# subset df to include only useful colunms
df = df[, c("Date","Type","Description","Value","Balance")]

head(df)
# add a new colunm that is based on the date section in the description of POS transactions, non POS as per provided Date
df$XDate <- ifelse(df$Type == "POS", 
                   substr(df$Description, start = 6, stop = 10), 
                   format(as.Date(df$Date, format="%d %b %Y"),format="%d-%b-%y"))


# combine day-month (15MAY) wihh the yeat (22) to crate full date and convert to date format, non POS as per provided Date
df$XDate <- ifelse(df$Type == "POS", 
                   format(as.Date(paste0(df$XDate, "24"), format = "%d%b%y"), "%d-%b-%y"),
                   df$XDate)

df$XDate = as.Date(df$XDate,format="%d-%b-%y")
# modify the description so that it covers 15th character onwards 
df$Description = ifelse(df$Type =="POS", substr(df$Description, 15, nchar(df$Description)),df$Description)

#head(df,n=1000)

#remove certain transactions based on contents of description
df <- df[!grepl("DEC23", df$Description), ]
#df <- df[!grepl("LOANPAD",df$Description),]
#df <- df[!grepl("CROWDSTACKER",df$Description),]
#df <- df[!grepl("CROWD2FUND",df$Description),]
#df <- df[!grepl("LANDLORDINVEST", df$Description),]
#df <- df[!grepl("LandlordInvest", df$Description),]
#df <- df[!grepl("VANGUARD",df$Description),]
#df <- df[(!grepl("SMARKETS",df$Description)),]
#df <- df[(!grepl("Crowdstacker", df$Description)),]
#df <- df[(!grepl("TRADING", df$Description)),]
#df <- df[(!grepl("SALARY", df$Description)),]
#df <- df[(!grepl("MARGARET SEYMOUR", df$Description)),]
#df <- df[(!grepl("KAREN", df$Description)),]
#df <- df[(!grepl("96000N", df$Description)),]
#df <- df[(!grepl("46131725808749000N", df$Description)),]
#df <- df[(!grepl("701001", df$Description)),]
#df <- df[(!grepl("64151128252648000R", df$Description)),]
#df <- df[(!grepl("55093643934347000R", df$Description)),]
#df <-df[(!grepl("00153425632GNGPWCR", df$Description)),]
#df <- df[!grepl("CHIP FINANCIAL",df$Description),]
#df <- df[!grepl("2DC4093BFFFC4B56A4", df$Description),]

head(df,n=10)
#df[!grepl("353-12477661", df$Description),]


df$Date = df$XDate
df$Date=as.POSIXct(df$Date, format = "%d-%b-%y")
df$Date = format(df$Date, "%Y-%m-%d %H:%M:%S")


df <- df[order(df$Date), ]

#df$num <- seq_len(nrow(df))

df$Month <- month(df$Date, label = TRUE)
df$Day   = yday(df$Date)
#df$Month_Day = as.POSIXct(df$Date, format ="%d")
df$Month_Day = mday(df$Date)

# Convert Month to abbreviated month names
df$Month <- month.abb[df$Month]
head(df)
# Summarize the data by Month, Month_Day, and Dollar amount
summarized_df <- aggregate(Value ~ Month + Month_Day, data = df, FUN = sum)

# Create an empty matrix to store the results
#result_matrix <- matrix(0, nrow = 31, ncol = 13, dimnames = list(NULL, c("Day", month.abb)))
#
## Fill in the day column
#result_matrix[, 1] <- 1:31

# Fill in the dollar amounts
#for (i in 1:nrow(summarized_df)) {
#  month_idx <- match(summarized_df[i, "Month"], month.abb)
#  day_idx <- as.integer(summarized_df[i, "Month_Day"])
#  result_matrix[day_idx, month_idx + 1] <- summarized_df[i, "Value"]
#}

# Convert the matrix to a dataframe
#wide_df <- as.data.frame(result_matrix)

# add month;y sum row 
#month_sum = as.data.frame(t(colSums(wide_df, na.rm = TRUE)))
#print(month_sum)
#wide_df <- rbind(wide_df, month_sum)
#print(wide_df)

# Create a table using gt
#table_gt <- gt(wide_df)

# Set table title
#table_gt <- table_gt %>%
#  gt_theme_538() %>%
#  tab_footer(general = summary_row) %>%
#  data_color(columns = everything(),method = "numeric",na_color = "white",palette = "Reds",domain = c(0, -300),reverse = TRUE) %>%
#  fmt_number(decimals = 2) %>%
#   tab_row_group(
#    label = "Description",
#    rows = contains("KAREN")
#  ) %>%
#  tab_header(
#    title = "Summary of Dollars by Month and Day"
#  )
#
# Save the table as an HTML file
#gtsave(table_gt, file = "summary_table.html")