#library(RSQLite)
library(cowplot)
library(DBI)
library(shiny)
library(ggplot2)
library(gghighcontrast)

con <- dbConnect(drv=RSQLite::SQLite(), dbname= "/srv/shiny-server/lumo-apps/all-lumosql-benchmark-data-combined.sqlite")

shinyServer(function(input,output) {
       
   
    
  globaldf <- reactive({
    
    #-----find runs with selected criteria
    for(j in input$ds){
      idees <- data.frame('run_id')
      colnames(idees) <- c('run_id')
      for(k in input$os){
        for (i in input$be){
          iii <- dbGetQuery(con, paste0("select run_id from run_data where (key = 'tests-ok' and value = '17')
                                      intersect select run_id from run_data where (key = 'backend-version' and value = '",i,"')
                                      intersect select run_id from run_data where (key = 'option-datasize' and value = '",j,"')
                                      intersect select run_id from run_data where (key = 'os-version' and value = '",k,"')
                                      "))
          
          idees <- rbind(idees, iii)
        }}}
    
    #-----error message when no data found
    if (length(idees[,1]) == 1){
      validate(
        need(length(idees[,1]) == 0, "No data in this selection")
      )
      
    }
    
    
    #---- collect info about the runs
    mat <- matrix(ncol = 4)
    df <- as.data.frame(mat)
    colnames(df) <- list('run_id', 'time', 'sqlite_version' , 'pointer')
    
    
    idees <- idees[-1,]
    
    for (h in idees){
      lll <- dbGetQuery(con, paste0("select value from run_data where key in ('sqlite-version') and run_id = '",h,"' "))
      if (as.character(lll) == ''){
        lll <- c('3.18.2')
        lll <- as.data.frame(lll)
      }
      
      bbb <- dbGetQuery(con, paste0("select value from run_data where key in ('os-version','backend-name', 'backend-version','cpu-type', 'cpu-comment', 'disk-comment','word-size') and run_id = '",h,"' "))
      timez <- dbGetQuery(con, paste0("select value from test_data where run_id = '",h,"' and key in ('real-time') ") )
      duration <- sum(as.numeric(timez[,1]) )
      pointer <- paste(as.character(bbb[,1]),collapse = ' ')
      
      darow <- data.frame(h, duration, lll[,1], pointer  )
      
      colnames(darow) <- colnames(df)
      df <- rbind(df, darow)
      
    }
    
    df <- df[-1,]
    
    return(df)
  })
  output$theplot <- renderPlot({
      ggplot(data=globaldf(), aes(x=sqlite_version, y=time, group=pointer, colour=pointer )) +
      geom_line()+
      geom_point()+
     theme_high_contrast(
          foreground_color = "white",
          background_color = "black",
          base_family = "InputMono"
      ) +
#      theme_dark()+
      theme(panel.border = element_rect(
            fill = "#00000000", color = "#FFFFFFFF", size = 1)
      )+
#      theme(plot.background = element_rect(fill = "black"))+
      theme(legend.position="none")
  })  

  output$thelegend <- renderPlot({
      pleg <- ggplot(data=globaldf(), aes(x=sqlite_version, y=time, group=pointer, colour=pointer )) +
      geom_point()+
      #theme(panel.border = element_rect(
      #fill = "#00000000", color = "#FFFFFFFF", size = 1)
      #)+
      #theme_dark()+
      theme(plot.background = element_rect(fill = "black"))+
      theme(legend.position="top")+
      theme(legend.background = element_rect(fill="black"))+
      theme(legend.text=element_text(color="white"))+
      theme(legend.key = element_rect(fill="black"))+
      guides(col = guide_legend(ncol=1))
      legend <-get_legend(pleg)
      
      ggdraw(legend)+
      #theme_dark()+
      theme(plot.background = element_rect(fill = "black"))
      #theme(legend.background = element_rect(fill="lightblue"))

  })


})