library(shiny)
library(tidyverse)
library(RMySQL)
ui <- fluidPage(
   titlePanel("Keeleandmed"),
   #lisage keeletaseme valik
   sidebarLayout(
     sidebarPanel(
       selectInput("emakeel", "emakeel", c("soome", "vene", "eesti"), "soome"),
       tableOutput("tabel1")
     ),
     mainPanel(
       textInput("paar", "paar"),
       tableOutput("tabel2")
     )
   )
)

server <- function(input, output) {
   output$tabel2 <- renderTable({
     yhendus=dbConnect(MySQL(fetch.default.rec = 50000000), 
                       user='if17', password='if17', host='greeny.cs.tlu.ee', 
                       dbname='if17_keelekorpus')
     rs=dbSendQuery(yhendus, paste0(    
  "SELECT ngram2, ngram1.sona AS sona1, n1koopia.sona AS sona2 
       FROM dokmeta 
                                  INNER JOIN ngram2 ON dokmeta.kood=ngram2.tekstikood 
                                  INNER JOIN ngram1 ON ngram1.tekstikood=ngram2.tekstikood AND
                                        ngram1.alguskoht=ngram2.alguskoht
                                  INNER JOIN ngram1 AS n1koopia
                                        ON n1koopia.tekstikood=ngram2.tekstikood AND
                                        n1koopia.alguskoht=ngram2.alguskoht+1
                                  WHERE emakeel='",input$emakeel,"' AND 
                                        ngram2='",input$paar,"'"))
     ngramid=fetch(rs, n=50000)
     dbClearResult(rs)
     dbDisconnect(yhendus)
     return (ngramid)     
   })
   output$tabel1 <- renderTable({
     yhendus=dbConnect(MySQL(fetch.default.rec = 50000000), 
        user='if17', password='if17', host='greeny.cs.tlu.ee', 
        dbname='if17_keelekorpus')
     rs=dbSendQuery(yhendus, paste0("SELECT ngram2, COUNT(*) AS kogus
              FROM dokmeta INNER JOIN ngram2 ON dokmeta.kood=ngram2.tekstikood 
              WHERE emakeel='",input$emakeel,"' AND keeletase='A2'
              GROUP BY ngram2 ORDER BY kogus DESC"))
     ngramid=fetch(rs, n=50000)
     dbClearResult(rs)
     dbDisconnect(yhendus)
     return (ngramid)
   })
}

shinyApp(ui = ui, server = server)