library(RPostgreSQL)
# library(DT)
# library(plotly)
# library(rjson)
# library(pool)
# library(DBI)



library(config)
library(dplyr)



profile = 'polon_dev'
conf = config::get("dataconnection", config = Sys.getenv("R_CONFIG_ACTIVE", profile))

psql <- dbDriver("PostgreSQL")
pcon <- dbConnect(psql, dbname = conf$dbname, host = conf$host, port = conf$port, user = conf$user, password = conf$password, DBMSencoding="utf-8")

dbListTables(pcon)


# Construct the fetching query
query <- sprintf("SELECT * FROM polon_dev.studies")
dbGetQuery(pcon, query)
# Submit the fetch query and disconnect
dbDisconnect(pcon)

pcon <- src_postgres(dbname = conf$dbname, host = conf$host, port = conf$port, user = conf$user, password = conf$password)
pcon <- src_postgres(dbname = 'jsa', host = '10.20.25.23', port = 5432, user = 'postgres', password = 'postgres')

query_get_integration_thesis <-
  "
    SELECT i.name, count(1)
    FROM %s.thesis_meta  AS m
    INNER JOIN %s.institution AS i
    ON m.institution_owner_uuid = i.uuid
    where owner_id IS NULL and owner_uid IS NULL AND creation_date >= %s
    GROUP BY i.name
    ORDER BY count DESC
  "

query_get_ui_thesis <-
  "
    SELECT i.name, count(1)
	  FROM %s.thesis_meta AS m
    INNER JOIN %s.institution AS i
    ON m.institution_owner_uuid = i.uuid
    WHERE owner_id IS NOT NULL AND creation_date >= %s
    GROUP BY i.name
    ORDER BY count DESC;
  "


filled_query_get_integration_thesis <- sprintf(query_get_integration_thesis, profile, profile, "'2019-01-01'")
data_integration_thesis <- tbl(pcon, sql(filled_query_get_integration_thesis), encoding = 'UTF-8')
data_integration_thesis

filled_query_get_ui_thesis <- sprintf(query_get_ui_thesis, profile, profile, "'2019-01-01'")
data_ui_thesis <- dbGetQuery(pcon, filled_query_get_ui_thesis)
data_ui_thesis