install.packages("pak")

pak::pak(c("bslib", "DBI", "dplyr", "duckdb", "fastmap", "fontawesome",
  "ggplot2", "ggridges", "here", "plotly", "reactable", "shiny",
  "hadley/elmer", "jcheng5/shinychat"))

...
# Handle user input
  observeEvent(input$chat_user_input, {
    # Add user message to the chat history
    chat_append("chat", chat$chat_async(input$chat_user_input)) %...>% {
       # print(chat)
    }
  })
...

...
session$onFlushed(function() {
    stream <- chat$chat_async(
      "Interpret this plot, which is based on the current state of the data (i.e. with filtering applied, if any). Try to make specific observations if you can, but be conservative in drawing firm conclusions and express uncertainty if you can't be confident.",
      img_content
    )
    chat_append(chat_id, stream)
  })

  observeEvent(session$input[[paste0(chat_id, "_user_input")]], {
    stream <- chat$chat_async(session$input[[paste0(chat_id, "_user_input")]])
    chat_append(chat_id, stream)
  })
...

# scripts/setup.R

library(duckdb)
library(DBI)
library(here)

db_path <- here("iris.duckdb")

# Delete if exists
if (file.exists(db_path)) {
  unlink(db_path)
}

# Load tips.csv into a table named `tips`
conn <- dbConnect(duckdb(), dbdir = db_path)
duckdb_read_csv(conn, "iris", here("iris.csv"))
dbDisconnect(conn)

You can use this sidebar to filter and sort the data based on the columns available in the `iris` table. Here are some examples of the kinds of questions you can ask me:

1. Filtering: "Show me flowers of type setosa."
2. Sorting: "Sort the data by the sepal_length column in descending order."
3. Answer questions about the data: "What is the range of the petal_width column?"

You can also say "Reset" to clear the current filter/sort, or "Help" for more usage tips.

# scripts/explain-plot.R

library(ggplot2)

#' Convert a plot object to a PNG data URI
#'
#' @param p The plot object; currently, plotly and ggplot2 are supported. Note
#'     that plotly requires Python, {reticulate}, and the PyPI packages {plotly}
#'     and {kaleido}.
plot_to_img_content <- function(p) {
  UseMethod("plot_to_img_content", p)
}

plot_to_img_content.plotly <- function(p) {
  # Create a temporary file
  tmp <- tempfile(fileext = ".png")
  on.exit(unlink(tmp))

  # Save the plot as an image
  save_image(p, tmp, width = 800, height = 600)
  elmer::content_image_file(tmp, resize = "high")
}

plot_to_img_content.ggplot <- function(p) {
  # Create a temporary file
  tmp <- tempfile(fileext = ".png")
  on.exit(unlink(tmp))

  ggsave(tmp, p, width = 800, height = 600, units = "px", dpi = 100)
  elmer::content_image_file(tmp, resize = "high")
}

explain_plot <- function(chat, p, model, ..., .ctx = NULL, session = getDefaultReactiveDomain()) {
  chat_id <- paste0("explain_plot_", sample.int(1e9, 1))
  # chat <- chat$clone()

  img_content <- plot_to_img_content(p)
  img_url <- paste0("data:", img_content@type, ";base64,", img_content@data)

  showModal(modalDialog(
    tags$button(
      type="button",
      class="btn-close d-block ms-auto mb-3",
      `data-bs-dismiss`="modal",
      aria_label="Close",
    ),
    tags$img(
      src = img_url,
      style = "max-width: min(100%, 400px);",
      class = "d-block border mx-auto mb-3"
    ),
    chat_ui(chat_id),
    size = "l",
    easyClose = TRUE,
    title = NULL,
    footer = NULL,
  ) |> tagAppendAttributes(style = "--bs-modal-margin: 1.75rem;"))

  session$onFlushed(function() {
    stream <- chat$chat_async(
      "Interpret this plot, which is based on the current state of the data (i.e. with filtering applied, if any). Try to make specific observations if you can, but be conservative in drawing firm conclusions and express uncertainty if you can't be confident.",
      img_content
    )
    chat_append(chat_id, stream)
  })

  observeEvent(session$input[[paste0(chat_id, "_user_input")]], {
    stream <- chat$chat_async(session$input[[paste0(chat_id, "_user_input")]])
    chat_append(chat_id, stream)
  })
}

library(shiny)
library(bslib)
library(promises)
library(fastmap)
library(duckdb)
library(DBI)
library(fontawesome)
library(reactable)
library(here)
library(plotly)
library(ggplot2)
library(ggridges)
library(dplyr)
library(elmer)
library(shinychat)


# Point to the newsly created DuckDB file
conn <- dbConnect(duckdb(), dbdir = here("iris.duckdb"), read_only = TRUE)
onStop(\() dbDisconnect(conn))
openai_model <- "gpt-4o"
# Change the system prompt since the table name is different now
system_prompt_str <- system_prompt(dbGetQuery(conn, "SELECT * FROM iris"), "iris")
# New markdown file
greeting <- paste(readLines(here("greetings_iris.md")), collapse = "\n")
icon_explain <- tags$img(src = "stars.svg")


ui <- page_sidebar(
  style = "background-color: rgb(248, 248, 248);",
  title = "Iris dataset",
  includeCSS(here("styles.css")),
  sidebar = sidebar(
    width = 400,
    style = "height: 100%;",
    chat_ui("chat", height = "100%", fill = TRUE)
  ),
  useBusyIndicators(),

  # 🏷️ Header
  textOutput("show_title", container = h3),
  verbatimTextOutput("show_query") |>
    tagAppendAttributes(style = "max-height: 100px; overflow: auto;"),

  # 🎯 Value boxes
  layout_columns(
    fill = FALSE,
    value_box(
      "Row count",
      textOutput("row_count", inline = TRUE)
    ),
    value_box(
      "Avg. petal length",
      textOutput("average_petal_length", inline = TRUE)
    ),
    value_box(
      "Avg. petal width",
      textOutput("average_petal_width", inline = TRUE)
    ),
  ),
  layout_columns(
    style = "min-height: 450px;",
    col_widths = c(6, 6, 12),

    # 🔍 Data table
    card(
      style = "height: 500px;",
      card_header("Iris data"),
      reactableOutput("table", height = "100%")
    ),

    # 📊 Scatter plot
    card(
      card_header(
        class = "d-flex justify-content-between align-items-center",
        "Petal length vs. Petal width",
        span(
          actionLink(
            "interpret_scatter",
            icon_explain,
            class = "me-3 text-decoration-none",
            aria_label = "Explain scatter plot"
          ),
          popover(
            title = "Add a color variable", placement = "top",
            fa_i("ellipsis"),
            radioButtons(
              "scatter_color",
              NULL,
              c("none", "sex", "smoker", "day", "time"),
              inline = TRUE
            )
          )
        )
      ),
      plotlyOutput("scatterplot")
    )
  )
)


server <- function(input, output, session) {
  # 🔄 Reactive state/computation --------------------------------------------

  current_title <- reactiveVal(NULL)
  current_query <- reactiveVal("")
  ctx <- list(conn = conn)

  # Update to select data from the Iris table
  iris_data <- reactive({
    sql <- current_query()
    if (is.null(sql) || sql == "") {
      sql <- "SELECT * FROM iris;"
    }
    dbGetQuery(conn, sql)
  })



  # 🏷️ Header outputs --------------------------------------------------------

  output$show_title <- renderText({
    current_title()
  })

  output$show_query <- renderText({
    current_query()
  })



  # 🎯 Value box outputs -----------------------------------------------------

  # Update names after `output$` and the calculation methods

  output$row_count <- renderText({
    nrow(iris_data())
  })

  output$average_petal_length <- renderText({
    x <- mean(iris_data()$petal_length)
    paste0(formatC(x, format = "f", digits = 1, big.mark = ","), "cm")
  })

  output$average_petal_width <- renderText({
    x <- mean(iris_data()$petal_width)
    paste0(formatC(x, format = "f", digits = 1, big.mark = ","), "cm")
  })



  # 🔍 Data table ------------------------------------------------------------

  # Update dataset name

  output$table <- renderReactable({
    reactable(iris_data(),
              pagination = FALSE, compact = TRUE
    )
  })



  # 📊 Scatter plot ----------------------------------------------------------

  # Update plot data and column names

  scatterplot <- reactive({
    req(nrow(iris_data()) > 0)

    color <- input$scatter_color

    data <- iris_data()

    p <- plot_ly(data, x = ~petal_length, y = ~petal_width, type = "scatter", mode = "markers")

    if (color != "none") {
      p <- plot_ly(data,
                   x = ~petal_length, y = ~petal_width, color = as.formula(paste0("~", color)),
                   type = "scatter", mode = "markers"
      )
    }

    p <- p |> layout(showlegend = FALSE)

    return(p)
  })

  output$scatterplot <- renderPlotly({
    scatterplot()
  })

  observeEvent(input$interpret_scatter, {
    explain_plot(chat, scatterplot(), model = openai_model, .ctx = ctx)
  })

  # ✨ Sidebot ✨ -------------------------------------------------------------

    append_output <- function(...) {
      txt <- paste0(...)
      shinychat::chat_append_message(
        "chat",
        list(role = "assistant", content = txt),
        chunk = TRUE,
        operation = "append",
        session = session
      )
    }

  update_dashboard <- function(query, title) {
    append_output("\n```sql\n", query, "\n```\n\n")

    tryCatch(
      {
        # Try it to see if it errors; if so, the LLM will see the error
        dbGetQuery(conn, query)
      },
      error = function(err) {
        append_output("> Error: ", conditionMessage(err), "\n\n")
        stop(err)
      }
    )

    if (!is.null(query)) {
      current_query(query)
    }
    if (!is.null(title)) {
      current_title(title)
    }
  }

  query <- function(query) {
    # Do this before query, in case it errors
    append_output("\n```sql\n", query, "\n```\n\n")

    tryCatch(
      {
        df <- dbGetQuery(conn, query)
      },
      error = function(e) {
        append_output("> Error: ", conditionMessage(e), "\n\n")
        stop(e)
      }
    )

    tbl_html <- df_to_html(df, maxrows = 5)
    append_output(tbl_html, "\n\n")

    df |> jsonlite::toJSON(auto_unbox = TRUE)
  }

  chat <- chat_openai(model = openai_model, system_prompt = system_prompt_str)
  chat$register_tool(tool(
    update_dashboard,
    "Modifies the data presented in the data dashboard, based on the given SQL query, and also updates the title.",
    query = type_string("A DuckDB SQL query; must be a SELECT statement."),
    title = type_string("A title to display at the top of the data dashboard, summarizing the intent of the SQL query.")
  ))
  chat$register_tool(tool(
    query,
    "Perform a SQL query on the data, and return the results as JSON.",
    query = type_string("A DuckDB SQL query; must be a SELECT statement.")
  ))

  chat_append("chat", greeting)

  # Handle user input
  observeEvent(input$chat_user_input, {
    # Add user message to the chat history
    chat_append("chat", chat$chat_async(input$chat_user_input)) %...>% {
      # print(chat)
    }
  })
}

df_to_html <- function(df, maxrows = 5) {
  df_short <- if (nrow(df) > 10) head(df, maxrows) else df

  tbl_html <- capture.output(
    df_short |>
      xtable::xtable() |>
      print(type = "html", include.rownames = FALSE, html.table.attributes = NULL)
  ) |> paste(collapse = "\n")

  if (nrow(df_short) != nrow(df)) {
    rows_notice <- glue::glue("\n\n(Showing only the first {maxrows} rows out of {nrow(df)}.)\n")
  } else {
    rows_notice <- ""
  }

  paste0(tbl_html, "\n", rows_notice)
}

shinyApp(ui, server)