#####
#
# Import SIMO Results (SQLite-Databases) in R environment 
# and save a dataframe in folder /output
# 
# 2019-07-12
#
#####



## load libraries
library(RSQLite)
library(tidyr)
library(dplyr)


### Load management regimes
# ---------------------------

# Management regimes and their abbreviation, they are merged to the data by the branching group (script loadDB.R)
regime <- read.csv(paste0(path, "/params/regimes.csv"), 
                   sep = ",", 
                   stringsAsFactors = FALSE)



# Variant 1: create a single dataframe for each SQL Database (selected columns of table UNIT)
# Dataframes are given the names of the DB and column "name" is added with the name of the DB

# Create an empty list to store all output dataframes into one
all.out = NULL

for (name in db_names){
  
  # Connect to db 
  db <- dbConnect(dbDriver("SQLite"),
                  dbname = paste(inputFolder, 
                                  paste0(name, ".db"), sep = "/"))
  
  # Select indicated columns  
  rsl  <- dbGetQuery( db, 
                      paste0("select ", columns, " from UNIT"))
  
  # Create output name, Remove the 'simulated_'
  outName <- gsub("simulated_", "", name)
  
  # Add gpkg indication
  rsl$name <- outName
  
  # Disconnect database
  dbDisconnect(db)

  ### Add the abbreviation of the regimes
  rsl <- rsl %>%
    left_join(regime, by = "branching_group", all.x = TRUE)

 
  # ---------------------------------
  # Rename set aside scenario if it considers deadwood extraction
  # ---------------------------------
  if(!grepl("_SA", name)){
    rsl <- rsl %>% 
           mutate(regime = ifelse(regime %in% "SA", 
                                   "SA_DWextract",   # YES 
                                   regime))          # NO
    print(name)
    print("SA renamed to SA_DWextract")
  }
  
  
  # Export the final table and print it out
  print(paste0("writing csv for ", outName))
  
  # Create a list of all dataframes
  all.out <- rbind(all.out, rsl) 

  # Write individual tables
  write.table(rsl,
              paste0(outputFolder, "/", outName, ".csv" ), 
              sep = ";", 
              row.names = F, 
              col.names = TRUE)
    
  
  #assign( paste("rsl", name, sep="_"), rsl)
  #rm(db, rsl)

}



# Variant 2: create one huge dataframe combining all SQL databases (selected columns of table UNIT)
# Save all databases in the one giant table


if (all_in_one == TRUE) {
 outName = "rslt_all"
 write.table(all.out,
             paste0(outputFolder, "/", outName, ".csv" ),
             sep = ";",
             row.names = F,
             col.names = TRUE)
}











# ##################################################################
# ##################################################################
# 
# 
# ##### Getting all Colnames from a SQL querry
# 
# 
# # was used to create List of SIMO output -> What indicators can be calculate for ES
# 
# db <- dbConnect(dbDriver("SQLite"), dbname = paste0(path,"/input_data/simulated_CC0_p_MV_Korsnas_rsu.db"))
# rsl  <- dbGetQuery(db, 'select * from UNIT')
# dbDisconnect(db)
# 
# colnames <- colnames(rsl)
# noquote(colnames)
# write.table(colnames, paste0(path,"/temp/SQL_FBE_colnames_newIndices.csv"), row.names = FALSE)
# 
# 
# # comapare the names of to df
# 
# onlyinCC <- colnames_CC45[!colnames_CC45 %in% colnames_without]