# Load the RPostgres library
library(RPostgres)
library(jsonlite)
library(httr)  # For sending files to an API
library(readr) # For writing CSV files

# Get DB connection info from Environment
db_host <- Sys.getenv("DB_HOST")
db_port <- Sys.getenv("DB_PORT")
db_name <- Sys.getenv("DB_NAME")
db_user <- Sys.getenv("DB_USER")
db_password <- Sys.getenv("DB_PASSWORD")

# Set Trusted Output App API Endpoint
trusted_output_endpoint <- Sys.getenv("TRUSTED_OUTPUT_ENDPOINT")

# Establish a connection to the PostgreSQL database
con <- dbConnect(
  drv = Postgres(),
  host = db_host,
  port = db_port,
  dbname = db_name,
  user = db_user,
  password = db_password
)

# Check the connection
if (!dbIsValid(con)) {
  stop("Failed to connect to the database.")
} else {
  print("Successfully connected to the database!")
}

# Example query: Get the first 10 rows from a table
query_result <- dbGetQuery(con, "SELECT COUNT(*) FROM highlights")

# DEBUG: Query to list all tables in the database
# query_result <- dbGetQuery(con,"
# SELECT table_schema, table_name
# FROM information_schema.tables
# WHERE table_type = 'BASE TABLE'
#   AND table_schema NOT IN ('information_schema', 'pg_catalog');
# ")

# Print the query result
print(query_result)

# Always disconnect when you're done
dbDisconnect(con)

# Do some research with the results
# Stubbed until we figure out what we should here, but for now
# just create a CSV file from the select table

# Write the query results to a CSV file
csv_file_path <- "query_result.csv"
write_csv(query_result, csv_file_path)

# Send aggregate results to Trusted Output App
response <- POST(
  url = trusted_output_endpoint,
  body = list(file = upload_file(csv_file_path)),  # Attach the CSV file
  encode = "multipart"  # Multipart form data encoding
)

# DEBUG: Print the response content
# response_content <- content(response, as = "parsed", type = "application/json")
# print(response_content)

# Check the API response
if (response$status_code == 200) {
  print("File uploaded successfully.")
} else {
  print(paste("File upload failed. Status code:", response$status_code))
}