library(rio)
library(dplyr)
library(sqldf)
library(glue)
states <- rio::import("https://raw.githubusercontent.com/smach/SampleData/main/states.csv") |>
 filter(!is.na(Region))

states_schema <- sqldf("PRAGMA table_info(states)")
states_schema_string <- paste(apply(states_schema, 1, paste, collapse = "\t"), collapse = "\n")

states_sample <- dplyr::sample_n(states, 3)
states_sample_string <- paste(apply(states_sample, 1, paste, collapse = "\t"), collapse = "\n")

create_prompt <- function(schema, rows_sample, query, table_name) {
 glue::glue("Act as if you're a data scientist. You have a SQLite table named {table_name} with the following schema:

 ```
 {schema}
 ```

 The first rows look like this: 

 ```{rows_sample}```

 Based on this data, write a SQL query to answer the following question: {query}. Return the SQL query ONLY. Do not include any additional explanation.")
}

> my_query <- "What were the highest and lowest Population changes in 2020 by Division?"
> my_prompt <- get_query(states_schema_string, states_sample_string, my_query, "states")
> cat(my_prompt)
Act as if you're a data scientist. You have a SQLite table named states with the following schema:
 ```
0 State TEXT 0 NA 0
1 Pop_2000 INTEGER 0 NA 0
2 Pop_2010 INTEGER 0 NA 0
3 Pop_2020 INTEGER 0 NA 0
4 PctChange_2000 REAL 0 NA 0
5 PctChange_2010 REAL 0 NA 0
6 PctChange_2020 REAL 0 NA 0
7 State Code TEXT 0 NA 0
8 Region TEXT 0 NA 0
9 Division TEXT 0 NA 0
```
The first rows look like this: 
```Delaware  783600  897934  989948 17.6 14.6 10.2 DE South South Atlantic
Montana  902195  989415 1084225 12.9  9.7  9.6 MT West Mountain
Arizona 5130632 6392017 7151502 40.0 24.6 11.9 AZ West Mountain```
Based on this data, write a SQL query to answer the following question: What were the highest and lowest Population changes in 2020 by Division?. Return the SQL query ONLY. Do not include any additional explanation.

sqldf("SELECT Division, MAX(PctChange_2020) AS Highest_PctChange_2020, MIN(PctChange_2020) AS Lowest_PctChange_2020 FROM states GROUP BY Division;")
 Division Highest_PctChange_2020 Lowest_PctChange_2020
1 East North Central 4.7 -0.1
2 East South Central 8.9 -0.2
3 Middle Atlantic 5.7 2.4
4 Mountain 18.4 2.3
5 New England 7.4 0.9
6 Pacific 14.6 3.3
7 South Atlantic 14.6 -3.2
8 West North Central 15.8 2.8
9 West South Central 15.9 2.7

library(openai)
my_results <- openai::create_chat_completion(model = "gpt-3.5-turbo", temperature = 0, messages = list(
 list(role = "user", content = my_prompt)
)) 
the_answer <- my_results$choices$message.content

cat(the_answer)
SELECT Division, MAX(PctChange_2020) AS Highest_Population_Change, MIN(PctChange_2020) AS Lowest_Population_Change
FROM states
GROUP BY Division;
sqldf(the_answer)
 Division Highest_Population_Change Lowest_Population_Change
1 East North Central 4.7 -0.1
2 East South Central 8.9 -0.2
3 Middle Atlantic 5.7 2.4
4 Mountain 18.4 2.3
5 New England 7.4 0.9
6 Pacific 14.6 3.3
7 South Atlantic 14.6 -3.2
8 West North Central 15.8 2.8
9 West South Central 15.9 

library(shiny)
library(openai)
library(dplyr)
library(sqldf)
# Load hard-coded dataset
states <- read.csv("states.csv") |>
 dplyr::filter(!is.na(Region) & Region != "")

states_schema <- sqldf::sqldf("PRAGMA table_info(states)")
states_schema_string <- paste(apply(states_schema, 1, paste, collapse = "\t"), collapse = "\n")

states_sample <- dplyr::sample_n(states, 3)
states_sample_string <- paste(apply(states_sample, 1, paste, collapse = "\t"), collapse = "\n")

# Function to process user input
get_prompt <- function(query, schema = states_schema_string, rows_sample = states_sample_string, table_name = "states") {
 my_prompt <- glue::glue("Act as if you're a data scientist. You have a SQLite table named {table_name} with the following schema:
 ```
 {schema}
 ```
 The first rows look like this: 
 ```{rows_sample}```

 Based on this data, write a SQL query to answer the following question: {query} Return the SQL query ONLY. Do not include any additional explanation.")
 print(my_prompt)
 return(my_prompt)
}
ui <- fluidPage(
 titlePanel("Query state database"),
 sidebarLayout(
 sidebarPanel(
 textInput("query", "Enter your query", placeholder = "e.g., What is the total 2020 population by Region?"),
 actionButton("submit_btn", "Submit")
 ),
 mainPanel(
 uiOutput("the_sql"),
 br(),
 br(),
 verbatimTextOutput("results")
 )
 )
)
server <- function(input, output) {
# Create the prompt from the user query to send to GPT
 the_prompt <- eventReactive(input$submit_btn, {
 req(input$query, states_schema_string, states_sample_string)
 my_prompt <- get_prompt(query = input$query)
 }) 

# send prompt to GPT, get SQL, run SQL, print results
observeEvent(input$submit_btn, {
 req(the_prompt()) # text to send to GPT
 # Send results to GPT and get response
 # withProgress adds a Shiny progress bar. Commas now needed after each statement
 withProgress(message = 'Getting results from GPT', value = 0, { # Add Shiny progress message
 my_results <- openai::create_chat_completion(model = "gpt-3.5-turbo", temperature = 0, messages = list(
 list(role = "user", content = the_prompt())
 )) 
 the_gpt_sql <- my_results$choices$message.content 

 # print the SQL
 sql_html <- gsub("\n", "<br />", the_gpt_sql) 
 sql_html <- paste0("<p>", sql_html, "</p>") 

 # Run SQL on data to get results
 gpt_answer <- sqldf(the_gpt_sql) 
 setProgress(value = 1, message = 'GPT results received') # Send msg to user that 
 })
 # Print SQL and results
 output$the_sql <- renderUI(HTML(sql_html)) 

 if (is.vector(gpt_answer) ) {
 output$results <- renderPrint(gpt_answer) 
 } else {
 output$results <- renderPrint({ print(gpt_answer) }) 
 } 
}) 
}
shinyApp(ui = ui, server = server)