#' @export
ScreenHandle <- R6Class(
  classname = "ScreenHandle",
  lock_class = TRUE,
  public = list(
    dbConnection = NULL,
    initialize = function(dbConnection = NA) {
      self$dbConnection <- dbConnection
    },
    get_by_id = function(dbID) {
      query <- "SELECT * FROM Screen WHERE ID = ?"
      df <- self$dbConnection$get_data(query, list(dbID))
      if(length(df$ID)>0) {
        screen <- Screen$new(dbConnection = self$dbConnection,
                             ID = df$ID,
                             name = df$name,
                             description = df$description,
                             status = df$status, 
                             date_started = df$date_started,
                             date_ended = df$date_ended)
        return(screen)
      } else { return(NULL) }
    },
    get_all_by_name = function(name) {
      query <- "SELECT DISTINCT ID FROM Screen WHERE name = ?"
      df <- self$dbConnection$get_data(query, list(name))
      if(length(df$ID)>0) {
        screens <- list()
        for(id in df$dbID) {
          sc <- self$get_by_id(id)
          screens <- c(screens, sc)
        }
        return(screens)
      }
    },
    get_all_by_status = function(status) {
      query <- "SELECT DISTINCT ID FROM Screen WHERE status = ?"
      df <- self$dbConnection$get_data(query, list(status))
      if(length(df$ID)>0) {
        screens <- list()
        for(id in df$ID) {
          sc <- self$get_by_id(id)
          screens <- c(screens, sc)
        }
        return(screens)
      }
    },
    get_new_ID = function() {
      query <- "SELECT seq FROM Screen ORDER BY seq DESC LIMIT 1"
      df <- self$dbConnection$get_data(query)
      if(nrow(df)>0) {
        ID <- paste0("SCR:", sprintf("%012d", df$seq+1))
      } else { # The table is empty
        ID <- paste0("SCR:", sprintf("%012d",1))
      }
      return(ID)
    },
    store = function(screen.list) {
      statement <- "INSERT IGNORE INTO Screen (ID, name, description, status, date_started, date_ended)
                            VALUES (?, ?, ?, ?, ?, ?)" 
      for(screen in screen.list) {
        result <- self$dbConnection$execute(statement, list(screen$ID, 
                                                            screen$name,
                                                            screen$description,
                                                            screen$status,
                                                            screen$date_started,
                                                            screen$date_ended))
        if(length(screen$plates)>0) {
          plh <- PlateHandle$new(self$dbConnection)
          result <- plh$store(screen$plates)
        }
        if(length(screen$analyses)>0) {
          ah <- AnalysisHandle$new(self$dbConnection)
          result <- ah$store(screen$analyses)
        }
      }
    }
  )
)