## Export datasets
# Function to export IM and EX data to Excel
export_data <- function(IM_data, EX_data, output_path, file_name) {
  
  # Create a new workbook
  wb <- createWorkbook()
  
  #To store import-related data
  addWorksheet(wb, "IM")
  writeData(wb, "IM", IM_data)
  
  #To store export-related data
  addWorksheet(wb, "EX")
  writeData(wb, "EX", EX_data)
  
  #Output
  output_path <- file.path(output_path, file_name)
  
  #Save the workbook
  saveWorkbook(wb, output_path, overwrite = TRUE)
  
}

##[1] Cleaned data output (by HTS chapters)
# [1.1]
cleaned_data_full <- export_data(IM_finaldata_bychapters, EX_finaldata_bychapters, path_to_output, "cleaned_data_full.xlsx")

##[2] Full underlying data with calculation
# Raw data (HS-2) with calculations (with respective HS-2 price index, HTS chapters index, 
# and total index, trade value both in current and real terms, and which index is selected to
# do the calculations)

underlyding_data_full <- export_data(IM_data_priceadjusted, EX_data_priceadjusted, path_to_output, "underlying_data_full.xlsx")


##[3] Price index used 
# To get a list of exactly which index is used for each HS-2 item, 
# we will use the processed trade data with "index_source" variable to pull the series_id 

# Function to generate index_used column for import/export trade data

export_index_used <- function(trade_data, prefix, total_index) {
  trade_data %>%
    filter(month == "July") %>%
    select(hs02, month, hts_chapters, index_source) %>%
    mutate(
      index_used = case_when(
        # If the index comes from HS02 level; #str_pad() is to add "0" back to HS 1 to HS9;
        index_source == "hs02" ~ paste0(prefix, str_pad(hs02, 2, pad = "0")),
        index_source == "hts_chapters" ~ paste0(prefix, hts_chapters),
        TRUE ~ total_index
      )
    ) %>%
    select(-month)
}
#apply on imports
hs02_IM_index_used <- export_index_used(IM_data_processed, "EIUIP", "EIUIR")
#apply on exports
hs02_EX_index_used <- export_index_used(EX_data_processed, "EIUID", "EIRIQ")
  
#export data
index_used_data <- export_data(hs02_IM_index_used, hs02_EX_index_used, path_to_output, "index_used_data.xlsx")


##[4] HTS chapters map
# This gives an overview of HTS chapters used for the classification in the final output
# More can be found here: https://hts.usitc.gov/

# Create a new workbook
wb <- createWorkbook()
addWorksheet(wb, "map")
writeData(wb, "map", hts_chapters_map)
output_path <- file.path(path_to_output, "HTS chapters map.xlsx")
#Save the workbook
saveWorkbook(wb, output_path, overwrite = TRUE)
  
  
##[5] Tariff revenue as a percent of CIF imports (Jan-Jul 2024 vs. Jan-Jul 2025)
wb <- createWorkbook()
addWorksheet(wb, "tariff_rev")
writeData(wb, "tariff_rev", tariff_rev)
output_path <- file.path(path_to_output, "tariff_rev.xlsx")
#Save the workbook
saveWorkbook(wb, output_path, overwrite = TRUE)

