# load tictoc
if(!require(pacman)){install.packages("pacman"); require(pacman)}
pacman::p_load(tictoc)

# set environment
readRenviron("~/I/config/.env")

# set local environment variables using global environment
SP500_LIST_PATH <- Sys.getenv("SP500_LIST_PATH")
PACKAGE_PATH <- Sys.getenv("PACKAGE_PATH")
PROJECT_HOME_DIRECTORY <- Sys.getenv("PROJECT_HOME_DIRECTORY")
STOCK_LIST_PATH <- Sys.getenv("STOCK_LIST_PATH")
FUNCTION_DIRECTORY <- Sys.getenv("FUNCTION_DIRECTORY")
ALPHA_VANTAGE_API <- Sys.getenv("ALPHA_VANTAGE_API")
PLOT_PATH <- Sys.getenv("PLOT_PATH")
VOTES_PATH = Sys.getenv("VOTES_PATH")
TA_FOLDER <- Sys.getenv("TA_FOLDER")
DIAGNOSTIC_FOLDER <- Sys.getenv("DIAGNOSTIC_FOLDER")
DATA_DIRECTORY <- Sys.getenv("DATA_DIRECTORY")
DECRYPT <- Sys.getenv("DECRYPT")
SENDER <- Sys.getenv("SENDER") 
USERNAME <- Sys.getenv("USERNAME")
PASSWORD <- Sys.getenv("PASSWORD")
INTRADAY = FALSE
REAL_TIME = TRUE
LONG_LIST = TRUE
SP500 = TRUE
if(!SP500){REGULAR = TRUE}

# set project home directory
setwd(PROJECT_HOME_DIRECTORY)

# load packages
packages <- read.csv(PACKAGE_PATH, header = FALSE)
pacman::p_load(char = as.vector(packages$V1))

# source all functions
sapply(paste(FUNCTION_DIRECTORY, grep(pattern = "\\.[Rr]$", list.files(FUNCTION_DIRECTORY), value = TRUE), sep = "/"), function(x) source(x)) %>% invisible()

db <- "algo_trade"
schema <- "stock"
host_db <- "localhost"
db_port <- "8321"
db_user <- "postgres"
db_password <- Sys.getenv("DB_PASSWORD")
con <- DBI::dbConnect(RPostgres::Postgres(), dbname = db, host = host_db, port = db_port, user = db_user, password = db_password)

# disconnect db
#dbDisconnect(con) 

# tables
tables = data.frame(
    sf = "stock_fact",
    sdim = "stock_dim",
    sindi = "stock_indicator",
    haf = "heikin_ashi_fact",
    ir = "investment_risk",
    df1 = "strategy_final_project_df1",
    df2 = "strategy_final_project_df2",
    df3 = "strategy_final_project_df3",
    df4_cm = "strategy_final_project_df4_cm",
    df4 = "strategy_final_project_df4",
    df5_detail = "strategy_final_project_df5_backtest_detail",
    df5_simplified = "strategy_final_project_df5_backtest_simplified",
    df6 = "strategy_final_project_df6_analysis") %>%
    tidyr::gather() %>%
    dplyr::mutate(row = 1:nrow(.)) %>%
    dplyr::select(row, key, table = value)

tables %>%
    kbl() %>%
    kable_paper("hover", full_width = F)

# helper function
psql <- function(query = "", connection = con){
    dbGetQuery(connection, glue::glue(query))
}

# test connection
# psql(query = "select * from {sf} limit 5") %>% 
#     kbl() %>%
#     kable_paper("hover", full_width = F)

# disconnect db
dbDisconnect(con) 

# from this point forward, all demo data is extracted from an excel file
# there are some issues connecting to postgresql and then outputting it in markdown
# for instance, I cannot do scroll_box(width = "100%", height = "200px")

# get demo data
demo_path = "C:/development/school_of_professional_studies/698. Capstone/algorithmic_trading_code/__final_present_data_set.xlsx"
# demo_var = readxl::excel_sheets(demo_path)
# sapply(1:length(demo_var), function(x) assign(demo_var[x], value = readxl::read_excel(demo_path, sheet = demo_var[x]), envir = .GlobalEnv)) %>%
#     invisible()

sf <- readxl::read_excel(demo_path, sheet = "sf")
sf %>% 
    kbl() %>%
    kable_paper("hover", full_width = F)

meta <- readxl::read_excel(demo_path, sheet = "meta")
meta %>%
    kbl() %>%
    kable_paper("hover", full_width = F)

sdim <- readxl::read_excel(demo_path, sheet = "sdim")
sdim %>%
    kbl() %>%
    kable_paper("hover", full_width = T) %>%
    scroll_box(width = "100%", height = "500px")

sqldf("select sector, count(1) as stocks from sdim group by sector order by sector") %>%
    kbl() %>%
    kable_paper("hover", full_width = T)

sindi <- readxl::read_excel(demo_path, sheet = "sindi")
sindi %>%
    kbl() %>%
    kable_paper("hover", full_width = T)

names(sindi) %>% as.data.frame(fields = ".") %>%
    kbl() %>%
    kable_paper("hover", full_width = T)

sindi %>%
    dplyr::select(symbol, date, adjusted, sma_5, target_flag, target_var) %>%
    kbl() %>%
    kable_paper("hover", full_width = T)

haf <- readxl::read_excel(demo_path, sheet = "haf")
haf %>%
    kbl() %>%
    kable_paper("hover", full_width = T)

ir <- readxl::read_excel(demo_path, sheet = "ir")
ir %>%
    kbl() %>%
    kable_paper("hover", full_width = T)

names(ir) %>% as.data.frame(fields = ".") %>%
    kbl() %>%
    kable_paper("hover", full_width = T)

df1 <- readxl::read_excel(demo_path, sheet = "df1")
df1 %>%
    kbl() %>%
    kable_paper("hover", full_width = T)

names(df1) %>% as.data.frame(fields = ".") %>%
    kbl() %>%
    kable_paper("hover", full_width = T)

df2 <- readxl::read_excel(demo_path, sheet = "df2")
df2 %>%
    kbl() %>%
    kable_paper("hover", full_width = T)

names(df2) %>% as.data.frame(fields = ".") %>%
    kbl() %>%
    kable_paper("hover", full_width = T)

df3 <- readxl::read_excel(demo_path, sheet = "df3")
df3 %>%
    kbl() %>%
    kable_paper("hover", full_width = T)

names(df3) %>% as.data.frame(fields = ".") %>%
    kbl() %>%
    kable_paper("hover", full_width = T)

df4_cm <- readxl::read_excel(demo_path, sheet = "df4_cm")
df4_cm %>%
    kbl() %>%
    kable_paper("hover", full_width = T)

df4 <- readxl::read_excel(demo_path, sheet = "df4")
df4 %>%
    kbl() %>%
    kable_paper("hover", full_width = T)