#' @export
WellHandle <- R6Class(
  classname = "WellHandle",
  lock_class = TRUE,
  public = list(
    dbConnection = NULL,
    initialize = function(dbConnection = NA) {
      self$dbConnection <- dbConnection
    },
    get_by_id = function(plateID, position) {
      query <- "SELECT * FROM Well WHERE Plate_ID = ? and position = ?"
      df <- self$dbConnection$get_data(query, list(plateID, position))
      if(length(df$Plate_ID)>0) {
        well <- Well$new(dbConnection = self$dbConnection,
                         ID = df$Plate_ID, position = df$Well_position, 
                         label = df$label, temperature = df$temperature)
        return(well)
      } else { return(NULL) }
    },
    get_all_by_plate = function(plate) {
      query <- "SELECT * FROM Well WHERE Plate_ID = ?"
      data <- self$dbConnection$get_data(query, list(plate$ID))
      if(length(data$Plate_ID)>0) {
        wells <- list()
        df.list <- split(data, data$ID)
        for(df in df.list) {
          w <- Well$new(dbConnection = self$dbConnection,
                        ID = df$Plate_ID, position = df$Well_position, 
                        label = df$label, temperature = df$temperature)
          wells <- c(wells, w)
        }
        return(wells)
      } else { return(NULL) }
    },
    store = function(well.list) {
      statement <- "INSERT IGNORE INTO Well (Plate_ID, position, label, temperature)
                                     VALUES (?, ?, ?, ?)" 
      for(well in well.list) {
        result <- self$dbConnection$execute(statement, list(well$plate_ID,
                                                            well$position,
                                                            well$label,
                                                            well$temperature))
        if(length(well$datafiles)>0) {
          dfh <- DatafileHandle$new(self$dbConnection)
          result <- dfh$store(well$datafiles)
          for(datafile in well$datafiles) {
            st <- "INSERT IGNORE INTO Well_has_Data (Plate_ID, Well_position, Datafile_ID)
                                             VALUES (?, ?, ?)"
            r <- self$dbConnection$execute(st, list(well$plate_ID,
                                                    well$position,
                                                    datafile$ID))
          }
        }
        if(length(well$qc)>0) {
          qch <- QCHandle$new(self$dbConnection)
          result <- qch$store(well$qc)
          for(qc in well$qc) {
            st <- "INSERT IGNORE INTO Well_has_QC (Plate_ID, Well_position, QC_ID)
                                           VALUES (?, ?, ?)"
            r <- self$dbConnection$execute(st, list(well$plate_ID,
                                                    well$position,
                                                    qc$ID))
          }
        }
        if(length(well$compounds)>0) {
          ch <- CompoundHandle$new(self$dbConnection)
          result <- ch$store(well$compounds)
          for(compound in well$compounds) {
            st <- "INSERT IGNORE INTO Well_has_Compound (Plate_ID, Well_position, 
                                                  Compound_ID, dose, unit)
                                                 VALUES (?, ?, ?, ?, ?)"
            r <- self$dbConnection$execute(st, list(well$plate_ID,
                                                    well$position,
                                                    compound$ID,
                                                    compound$dose,
                                                    compound$unit))
          }
        }
        if(length(well$samples)>0) {
          sh <- SampleHandle$new(self$dbConnection)
          result <- sh$store(well$samples)
          for(sample in well$samples) {
            st <- "INSERT IGNORE INTO Well_has_Sample (Plate_ID, Well_position, Sample_ID, count)
                        VALUES (?, ?, ?, ?)"
            r <- self$dbConnection$execute(st, list(well$plate_ID,
                                                    well$position,
                                                    sample$ID,
                                                    sample$count))
          }
        }
        if(length(well$phenotypes)>0) {
          ph <- PhenotypeHandle$new(self$dbConnection)
          result <- ph$store(well$phenotypes)
          for(phenotype in well$phenotypes) {
            st <- "INSERT IGNORE INTO Well_has_Phenotype (Plate_ID, Well_position, Phenotype_ID, value, unit)
                        VALUES (?, ?, ?, ?, ?)"
            r <- self$dbConnection$execute(st, list(well$plate_ID,
                                                    well$position,
                                                    phenotype$ID,
                                                    phenotype$value,
                                                    phenotype$unit))
          }
        }
      }
    }
  )
)