DbContainer.scala

package it.polimi.genomics.metadata.database

import it.polimi.genomics.metadata.step.utils.ParameterUtil
import org.joda.time.DateTime
import org.slf4j.{Logger, LoggerFactory}
import slick.dbio.Effect.Write
import slick.driver.PostgresDriver
import slick.driver.PostgresDriver.api._
import slick.jdbc.meta.MTable
import slick.sql.FixedSqlAction

import scala.concurrent.Await
import scala.concurrent.duration.Duration


/**
  * File table case class
  *
  */
case class File(id: Option[Int], datasetId: Int, url: String, name: String, stage: String, status: String, size: String, lastUpdate: String, hash: String,
                originSize: String, originLastUpdate: String, dateProcessed: String, copyNumber: Int)

/**
  * Created by Nacho on 11/11/16.
  * dbContainer is the interface between the h2 database and the file logger.
  * for the development of GMQLImporter all the asynchronous operations in the database are done
  * synchronous waiting them to finish.
  *
  * How to use it properly:
  * 1- Instantiate the class and set it up by giving the database path in the setDatabase method.
  * This has to be done at the beginning of the usage of the database, same instantiation should be used
  * for all the sources and datasets contained in the same xml configuration file.
  *
  * 2- For getting the source/dataset/file IDs sourceId/datasetId/fileId are provided.
  *
  * 3- For providing feedback about which files are outdated, before starting checking if the files of a dataset
  * have to be updated (checkIfUpdate), mark the dataset to be compared with the markToCompare method.
  *
  * 4- For each file, the checkIfUpdate(...) method will indicate if that file has to be updated.
  *     4.1- After you update the file (if you have to) use markAsUpdated(...) method to indicate that was updated.
  *     4.2- If the update process was not correct, use markAsFailed(...) method to indicate that was an error.
  *
  * 5- After modifications have been done and comparisons for the dataset have finished, use method markAsOutdated()
  * to identify and mark the outdated files, not using this method can lead to inconsistency.
  *
  * 6- You can get lists of files to be processed getFilesToProcess() method. Returns files with "UPDATE" status.
  *
  * 7- After you use the information in the log (as example, when the transformer uses info generated by downloader)
  * log it by using the method markAsProcessed().
  */

case class DbContainer() {


  val logger: Logger = LoggerFactory.getLogger(this.getClass)
  //------------------------------------------------testing space-------------------------------------------------------
  //here print of run parameters and all the xmlConfigurationFile could be done.
  //-------------------------------------DATABASE DEFINITION------------------------------------------------------------
  var database: PostgresDriver.backend.DatabaseDef = _
  //------------------------------------------LOG PRINTING--------------------------------------------------------------
  /**
    * prints the log for dataset downloaded files for a specified run
    *
    * @param runDatasetId identifies of the rundataset.
    * @param stage        indicates if log corresponds to download or transformation.
    */
  def printRunDatasetLog(runDatasetId: Int, stage: Stage.Value): Unit = {
    val query = (for (f <- runDatasetLogs.filter(f => f.runDatasetId === runDatasetId && f.stage === stage.toString))
      yield (f.totalFiles, f.downloadedFiles)).result
    val execution = database.run(query)
    val result = Await.result(execution, Duration.Inf)
    var totalFiles = 0
    var downloadedFiles = 0
    result.foreach(log => {
      totalFiles = totalFiles + log._1
      downloadedFiles = downloadedFiles + log._2
    })

    val datasetQuery = runDatasets.filter(_.id === runDatasetId).result
    val datasetExecution = database.run(datasetQuery)
    val datasetResult = Await.result(datasetExecution, Duration.Inf)
    if (datasetResult.nonEmpty) {
      val datasetId = datasetResult.head._3

      val datasetNameQuery = (for (f <- datasets.filter(f => f.id === datasetId)) yield f.name).result
      val datasetNameExecution = database.run(datasetNameQuery)
      val datasetNameResult = Await.result(datasetNameExecution, Duration.Inf)
      val datasetName = datasetNameResult.head

      logger.info(s"\tDataset $datasetName ${stage.toString} statistics:")
      logger.info(s"\t\tTotal files to ${stage.toString}: $totalFiles")
      logger.info(s"\t\t${stage.toString} successful files: $downloadedFiles")
    }

  }

  //-------------------------------BASIC INSERTIONS SOURCE/DATASET/FILE/RUN---------------------------------------------
  /**
    * Tries to create a source with the given name and returns its id, if already exists is not replaced.
    *
    * @param name name of the source, should be unique.
    * @return id of the source.
    */
  def sourceId(name: String): Int = {
    val query = (for (s <- sources.filter(_.name === name)) yield s.id).result
    val execution = database.run(query)
    val result = Await.result(execution, Duration.Inf)

    if (result.nonEmpty)
      result.head
    //here I have to create the source.
    else {
      val idQuery = (sources returning sources.map(_.id)) += (None, name)
      val executionId = database.run(idQuery)
      val resultId = Await.result(executionId, Duration.Inf)
      resultId
    }
  }

  /**
    * Tries to create a dataset and returns its id, if already exists is not replaced.
    *
    * @param sourceId dataset owner's id
    * @param name     name of the dataset should be unique for each source.
    * @return id of the dataset.
    */
  def datasetId(sourceId: Int, name: String): Int = {
    val query = (for (s <- datasets.filter(ds => ds.sourceId === sourceId && ds.name === name)) yield s.id).result
    val execution = database.run(query)
    val result = Await.result(execution, Duration.Inf)

    if (result.nonEmpty)
      result.head
    //here I have to create the dataset.
    else {
      val idQuery: FixedSqlAction[Int, NoStream, Write] = (datasets returning datasets.map(_.id)) +=
        (None, sourceId, name)
      val executionId = database.run(idQuery)
      val resultId = Await.result(executionId, Duration.Inf)
      resultId
    }
  }

  /**
    * Tries to create a file and returns its id, if already exists is not replaced.
    *
    * @param datasetId     file owner's id.
    * @param url           origin url for the file.
    * @param stage         stage of the process the file is used Download/Transform.
    * @param candidateName the name the file should have.
    * @return id of the file.
    */
  def fileId(datasetId: Int, url: String, stage: Stage.Value, candidateName: String, useUrl: Boolean): Int = {
    val filterCondition =
      if (useUrl)
        (f: Files) => f.datasetId === datasetId && f.url === url && f.stage === stage.toString && f.name === candidateName
      else
        (f: Files) => f.datasetId === datasetId && f.stage === stage.toString && f.name === candidateName


    val query = files.filter(filterCondition).map(_.id).result

    val execution = database.run(query)
    val result = Await.result(execution, Duration.Inf)

    if (result.nonEmpty)
      result.head
    //here I have to create the file.
    else {
      val idQuery: FixedSqlAction[Int, NoStream, Write] = (files returning files.map(_.id)) +=
        File(None, datasetId, url, candidateName, stage.toString, "FAILED", "", "", "", "", "", "", 0)
      //FAILED STATUS IS GIVEN BY DEFAULT WHEN CREATING A FILE (MEANS HAVE NOT BEEN DOWNLOADED)
      val executionId = database.run(idQuery)
      val resultId = Await.result(executionId, Duration.Inf)
      resultId
    }
  }

  /**
    * Creates a run, with its general settings and the actual datetime.
    *
    * @param downloadEnabled  indicates if downloading was enabled during the run.
    * @param transformEnabled indicates if transforming was enabled during the run.
    * @param loadEnabled      indicates if loading was enabled during the run.
    * @param outputFolder     indicates the outputFolder defined as working directory.
    * @return the run's id.
    */
  def runId(downloadEnabled: String, transformEnabled: String, loadEnabled: String, outputFolder: String): Int = {
    val dateTimeStart = DateTime.now().toString
    //runs have always to be created.
    val query = (runs returning runs.map(_.id)) +=
      (None, dateTimeStart, "", downloadEnabled, transformEnabled, loadEnabled, outputFolder)
    val execution = database.run(query)
    val result = Await.result(execution, Duration.Inf)
    if (result > 1) {
      val pastRunId = result - 1
      val updateQuery = (for (f <- runs.filter(r => r.id === pastRunId && r.datetimeEnd === ""))
        yield f.datetimeEnd)
        .update(dateTimeStart)
      Await.result(database.run(updateQuery), Duration.Inf)
    }
    result
  }

  //--------------------------SECONDARY INSERTIONS RUNSOURCE/RUNDATASET/RUNFILE/PARAMETERS------------------------------

  /**
    * generates the last representation of the source in the last run.
    *
    * @param sourceId         id for the source.
    * @param url              url for the source.
    * @param outputFolder     working directory for the source.
    * @param downloadEnabled  indicates if the source is being downloaded.
    * @param downloader       indicates the downloader used for the source.
    * @param transformEnabled indicates if the source is being transformed.
    * @param transformer      indicates the transformer used by the source.
    * @param loadEnabled      indicates if the source is bein loaded.
    * @param loader           indicates the loader used by the source.
    * @return the runSource id.
    */
  def runSourceId(sourceId: Int, url: String, outputFolder: String,
                  downloadEnabled: String, downloader: String, transformEnabled: String,
                  transformer: String, loadEnabled: String, loader: String
                 ): Int = {
    val runId = getMaxRunNumber
    val query = (for (rs <- runSources.filter(r => r.runId === runId && r.sourceId === sourceId)) yield rs.id).result
    val execution = database.run(query)
    val result = Await.result(execution, Duration.Inf)

    if (result.nonEmpty)
      result.head
    //here I have to create the runSource
    else {
      val idQuery: FixedSqlAction[Int, NoStream, Write] = (runSources returning runSources.map(_.id)) += (
        None, runId, sourceId, url, outputFolder, downloadEnabled, downloader,
        transformEnabled, transformer, loadEnabled, loader
      )
      val executionId = database.run(idQuery)
      val resultId = Await.result(executionId, Duration.Inf)
      resultId
    }
  }

  /**
    * Inserts the parameters used by a source
    *
    * @param runSourceId source who is using the parameters
    * @param description explains what the parameter is used for
    * @param key         indicates the name of the parameter
    * @param value       indicates the value of the parameter
    * @return id of the parameter.
    */
  def runSourceParameterId(runSourceId: Int, description: String, key: String, value: String): Int = {
    //parameters have always to be created.
    val query = (runSourceParameters returning runSourceParameters.map(_.id)) += (
      None, runSourceId, description, key, value
    )
    val execution = database.run(query)
    val result = Await.result(execution, Duration.Inf)
    result
  }

  /**
    * generates the last representation of the dataset in the last run.
    *
    * @param datasetId        id for the dataset.
    * @param outputFolder     working directory for the dataset.
    * @param downloadEnabled  indicates if the dataset is being downloaded.
    * @param transformEnabled indicates if the dataset is being transformed.
    * @param loadEnabled      indicates if the source is being loaded.
    * @param schemaUrl        indicates the url of the schema.
    * @param schemaLocation   indicates whether the schema is local or remote.
    * @return the runDataset id, 0 if any error occurs.
    */
  def runDatasetId(datasetId: Int, outputFolder: String, downloadEnabled: String, transformEnabled: String,
                   loadEnabled: String, schemaUrl: String, schemaLocation: String, runId2: Option[Int]
                  ): Int = {
    val runId = runId2.getOrElse(getMaxRunNumber)
    if (existsRun(runId)) {
      val query = (for (rd <- runDatasets.filter(r => r.runId === runId && r.datasetId === datasetId)) yield rd.id).result
      val execution = database.run(query)
      val result = Await.result(execution, Duration.Inf)

      if (result.nonEmpty)
        result.head
      //here I have to create the runDataset
      else {
        val idQuery: FixedSqlAction[Int, NoStream, Write] = (runDatasets returning runDatasets.map(_.id)) += (
          None, runId, datasetId, outputFolder, downloadEnabled, transformEnabled, loadEnabled, schemaUrl, schemaLocation
        )
        val executionId = database.run(idQuery)
        val resultId = Await.result(executionId, Duration.Inf)
        resultId
      }
    }
    else
      0
  }

  /**
    * indicates whether the run consulted exists or does not
    *
    * @param runId identifier for the run to check
    */
  def existsRun(runId: Int): Boolean = {
    val query = (for (rd <- runs.filter(r => r.id === runId)) yield rd.id).result
    val execution = database.run(query)
    val result = Await.result(execution, Duration.Inf)
    if (result.nonEmpty)
      true
    else
      false
  }

  /**
    * Inserts the parameters used by a source
    *
    * @param runDatasetId dataset who is using the parameters
    * @param description  explains what the parameter is used for
    * @param key          indicates the name of the parameter
    * @param value        indicates the value of the parameter
    * @return id of the parameter.
    */
  def runDatasetParameterId(runDatasetId: Int, description: String, key: String, value: String): Int = {
    //parameters have always to be created.
    val query = (runDatasetParameters returning runDatasetParameters.map(_.id)) += (
      None, runDatasetId, description, key, value
    )
    val execution = database.run(query)
    val result = Await.result(execution, Duration.Inf)
    result
  }

  /**
    * Inserts the parameters used by a source
    *
    * @param totalFiles      explains what the parameter is used for
    * @param downloadedFiles indicates the name of the parameter
    * @return id of the parameter.
    */
  def runDatasetLogId(runDatasetId: Int, stage: Stage.Value, totalFiles: Int, downloadedFiles: Int): Int = {
    //parameters have always to be created there is no way to identify changes or differences.
    val query = (runDatasetLogs returning runDatasetLogs.map(_.id)) += (
      None, runDatasetId, stage.toString, totalFiles, downloadedFiles
    )
    val execution = database.run(query)
    val result = Await.result(execution, Duration.Inf)
    result
  }

  /**
    * Generates the versioning for the metadata of the files.
    *
    * @param fileId indicats the file whose verions are.
    * @return id of the runFile.
    */
  def runFileId(fileId: Int): Int = {
    val runId = getMaxRunNumber
    val query = (for (rf <- runFiles.filter(r => r.runId === runId && r.fileId === fileId)) yield rf.id).result
    val execution = database.run(query)
    val result = Await.result(execution, Duration.Inf)

    //I get the data from the FILES table
    val fileFieldsQuery = (for (f <- files.filter(_.id === fileId)) yield (
      f.status, f.size, f.lastUpdate, f.hash, f.originSize, f.originLastUpdate, f.dateProcessed)).result
    val fileFields = Await.result(database.run(fileFieldsQuery), Duration.Inf).head

    //here I have to write the actual status of the file to the runfile.
    if (result.nonEmpty) {
      val updateQuery = (for (f <- runFiles.filter(r => r.runId === runId && r.fileId === fileId))
        yield (f.status, f.size, f.lastUpdate, f.hash, f.originSize, f.originLastUpdate, f.dateProcessed))
        .update(fileFields._1, fileFields._2, fileFields._3, fileFields._4, fileFields._5, fileFields._6, fileFields._7)
      Await.result(database.run(updateQuery), Duration.Inf)
      result.head
    }
    //here I have to create the runFile
    else {
      //and then I create the version of the runfiles.
      val idQuery: FixedSqlAction[Int, NoStream, Write] = (runFiles returning runFiles.map(_.id)) += (
        None, runId, fileId, fileFields._1, fileFields._2, fileFields._3,
        fileFields._4, fileFields._5, fileFields._6, fileFields._7
      )
      val executionId = database.run(idQuery)
      val resultId = Await.result(executionId, Duration.Inf)
      resultId
    }
  }

  //-------------------------------Run closing--------------------------------------------------------------------------
  /**
    * Puts the time finished of the run
    *
    * @param runId id for the run.
    */
  def endRun(runId: Int): Unit = {
    val dateTimeEnd = DateTime.now().toString
    val updateQuery = (for (r <- runs.filter(f => f.id === runId)) yield r.datetimeEnd).update(dateTimeEnd)
    val execution = database.run(updateQuery)
    /*val result = */
    Await.result(execution, Duration.Inf)
  }

  //------------------------------FILE OPERATIONS SECTION FILENAME/CHECKIFUPDATE/PROCESS--------------------------------
  /**
    * By receiving a candidate name returns a unique name inside the dataset.
    *
    * @param fileId id for the file.
    * @return unique name among the dataset's files. 0 as the Int indicates the file should not exist.
    */
  def getFileNameAndCopyNumber(fileId: Int): (String, Int) = {
    val query = (for (f <- files.filter(f => f.id === fileId)) yield (f.name, f.datasetId, f.copyNumber, f.stage)).result
    val execution = database.run(query)
    val result = Await.result(execution, Duration.Inf)

    if (result.nonEmpty) {
      if (result.head._3 != 0) //this indicates the copynumber has been assigned
        (result.head._1, result.head._3)
      //while copynumber is 0 means the value has not been assigned.
      else
        getFileName(fileId, result.head._1, result.head._2, 1, Stage.withName(result.head._4))
    }
    else
      ("FileDoesNotExist", 0)
  }

  /**
    * Returns hash, size and last update.
    *
    * @param fileId identifier of the file.
    * @return hash, size and last update.
    */
  def getFileDetails(fileId: Int): (String, String, String) = {
    val query = (for (f <- files.filter(f => f.id === fileId)) yield (f.hash, f.size, f.lastUpdate)).result
    val execution = database.run(query)
    val result = Await.result(execution, Duration.Inf)

    if (result.nonEmpty)
      result.head
    else
      ("file", "does not", "exist")
  }


  def getFileAllDetail(fileId: Int): Option[File] = {
    val query = files.filter(f => f.id === fileId).result.headOption
    val execution = database.run(query)
    val result = Await.result(execution, Duration.Inf)
    result
  }

  /**
    * By receiving a candidate name returns a unique name inside the dataset.
    *
    * @param fileId     id for the file.
    * @param name       candidate name.
    * @param datasetId  dataset.
    * @param copyNumber actual guess of the number of copy the file is.
    * @param stage      whether is download or transform
    * @return unique name among the dataset's files.
    */
  private def getFileName(fileId: Int, name: String, datasetId: Int, copyNumber: Int, stage: Stage.Value): (String, Int) = {
    val query = (for (s <- files.filter(f => f.datasetId === datasetId && f.stage === stage.toString &&
      f.name === name && f.copyNumber === copyNumber && f.id =!= fileId)) yield s.id).result
    val execution = database.run(query)
    val result = Await.result(execution, Duration.Inf)

    //this means the name is already in use. I have to recursively get a new name.
    if (result.nonEmpty)
      getFileName(fileId, name, datasetId, copyNumber + 1, stage)
    //here I have to create the file.
    else {
      val updateQuery = (
        for (s <- files.filter(f => f.id === fileId)) yield (s.name, s.copyNumber)).update(name, copyNumber)
      val executionId = database.run(updateQuery)
      /*val result = */
      Await.result(executionId, Duration.Inf)
      (name, copyNumber)
    }
  }

  /**
    * indicates which is the maximum copy number for the same filename inside the same dataset.
    *
    * @param datasetId datast where the file belongs
    * @param fileName  original file name
    * @param stage     indicates whether download/transform
    * @return max copy number
    */
  def getMaxCopyNumber(datasetId: Int, fileName: String, stage: Stage.Value): Int = {
    val query = (for (s <- files.filter(f => f.datasetId === datasetId &&
      f.name === fileName && f.stage === stage.toString)
    ) yield s.copyNumber).result
    val execution = database.run(query)
    val result = Await.result(execution, Duration.Inf)
    result.max
  }

  /**
    * gets the last run ever run
    *
    * @return max id of runs
    */
  //  def getMaxRunNumber: Int ={
  lazy val getMaxRunNumber: Int = {
    val query = (for (r <- runs) yield r.id).max.result
    val execution = database.run(query)
    val result = Await.result(execution, Duration.Inf)
    if (result.nonEmpty)
      result.max
    else
      0
  }

  /**
    * gets the previous run based in the given run
    *
    * @param runNumber queried run
    * @return previous run or 0 if no previous run exists
    */
  def getPreviousRunNumber(runNumber: Int): Int = {
    var previousRun = runNumber - 1
    while (!existsRun(previousRun) && previousRun > 0)
      previousRun -= 1
    previousRun
  }

  /**
    * prints the number of files that are downloaded and ready to process, the failed files and the new files
    * downloaded in the selected run
    *
    * @param datasetId dataset where the files belong to
    * @param runId     corresponden run to be consulted
    */
  def printNewReadyFailedFiles(datasetId: Int, runId: Int): Unit = {

    val updatedFiles = getFilesForStatistics(datasetId, runId, Stage.DOWNLOAD, FILE_STATUS.UPDATED)
    val failedFiles = getFilesForStatistics(datasetId, runId, Stage.DOWNLOAD, FILE_STATUS.FAILED)
    val compareFiles = getFilesForStatistics(datasetId, runId, Stage.DOWNLOAD, FILE_STATUS.COMPARE)
    val outdatedFiles = getFilesForStatistics(datasetId, runId, Stage.DOWNLOAD, FILE_STATUS.OUTDATED)

    val totalFiles = updatedFiles + failedFiles + compareFiles + outdatedFiles
    logger.info(s"\t\t$updatedFiles ready to process files")
    logger.info(s"\t\t$failedFiles failed files")

    val previousRunId = getPreviousRunNumber(runId)
    if (previousRunId > 0) {
      val updatedFilesPast = getFilesForStatistics(datasetId, previousRunId, Stage.DOWNLOAD, FILE_STATUS.UPDATED)
      val failedFilesPast = getFilesForStatistics(datasetId, previousRunId, Stage.DOWNLOAD, FILE_STATUS.FAILED)
      val compareFilesPast = getFilesForStatistics(datasetId, previousRunId, Stage.DOWNLOAD, FILE_STATUS.COMPARE)
      val outdatedFilesPast = getFilesForStatistics(datasetId, previousRunId, Stage.DOWNLOAD, FILE_STATUS.OUTDATED)

      val newFiles = totalFiles - updatedFilesPast - failedFilesPast - compareFilesPast - outdatedFilesPast
      logger.info(s"\t\t$newFiles new files indexed")
    }
    else {
      logger.info(s"\t\t$totalFiles new files indexed")
    }
  }

  /**
    * returns all the non outdated files with its copy number.
    *
    * @param datasetId dataset from where files are required.
    * @param runId     run requested to check
    * @param stage     indicates if is download or transform
    * @param status    indicates if is UPDATED, FAILED or OUTDATED
    * @return (fileId, filename, copyNumber)
    */
  def getFilesForStatistics(datasetId: Int, runId: Int, stage: Stage.Value, status: FILE_STATUS.Value): Int = {
    val query = runFiles.filter(runFile => runFile.File.filter(file => file.datasetId === datasetId && file.stage === stage.toString).exists && runFile.runId === runId && runFile.status === status.toString).length
    val execution = database.run(query.result)
    Await.result(execution, Duration.Inf)
  }

  /**
    * checks if the given file has to be updated based on its hash, size and last update.
    *
    * @param fileId           id for the file.
    * @param hash             hash of the file.
    * @param originSize       original size in the source.
    * @param originLastUpdate original last updated in the source.
    * @return true = has to be updated.
    */
  def checkIfUpdateFile(fileId: Int, hash: String, originSize: String, originLastUpdate: String): Boolean = {
    val toReturn = checkIfUpdateFileAux(fileId, hash, originSize, originLastUpdate)
    if (!toReturn)
      runFileId(fileId)
    toReturn
  }

  /**
    * checks if the given file has to be updated based on its hash, size and last update.
    *
    * @param fileId           id for the file.
    * @param hash             hash of the file.
    * @param originSize       original size in the source.
    * @param originLastUpdate original last updated in the source.
    * @return true = has to be updated.
    */
  def checkIfUpdateFileAux(fileId: Int, hash: String, originSize: String, originLastUpdate: String): Boolean = {
    val query = for (f <- files.filter(f => f.id === fileId)) yield (f.status, f.hash, f.originSize, f.originLastUpdate)

    val queryResult = query.result
    val execution = database.run(queryResult)
    val result = Await.result(execution, Duration.Inf)

    if (result.nonEmpty) {
      val oldStatus = result.head._1
      val oldHash = result.head._2
      val oldOriginSize = result.head._3
      val oldOriginLastUpdate = result.head._4

      //UPDATE indicates that file was put on the download folder and marked as update
      if (oldStatus.equalsIgnoreCase(FILE_STATUS.UPDATED.toString))
        false
      //FAILED indicates that the file was not put in the folder, OUTDATED means the file was deleted from the server
      else if (oldStatus.equalsIgnoreCase(FILE_STATUS.FAILED.toString) ||
        oldStatus.equalsIgnoreCase(FILE_STATUS.OUTDATED.toString) ||
        oldOriginSize != originSize || oldOriginLastUpdate != originLastUpdate ||
        (oldHash != hash && hash != "")
      ) {
        Await.result(database
          .run((for (f <- files.filter(f => f.id === fileId)) yield (f.hash, f.originSize, f.originLastUpdate))
            .update(hash, originSize, originLastUpdate))
          , Duration.Inf
        )
        true
      }
      //any other case whilst COMPARE, shouldn't be downloaded
      else if (oldStatus.equalsIgnoreCase(FILE_STATUS.COMPARE.toString)) {
        Await.result(database
          .run((for (f <- files.filter(f => f.id === fileId)) yield f.status)
            .update(FILE_STATUS.UPDATED.toString))
          , Duration.Inf
        )
        false
      }
      else false
    }
    else false
  }

  /**
    * returns all the non outdated files with its copy number.
    *
    * @param datasetId dataset from where files are required.
    * @param stage     whether is download or transform
    * @return (fileId, filename, copyNumber)
    */
  def getFilesToProcess(datasetId: Int, stage: Stage.Value): Seq[(Int, String, Int)] = {
    val query = (for (f <- files.filter(f => f.datasetId === datasetId && f.stage === stage.toString &&
      (f.status === FILE_STATUS.UPDATED.toString && !(f.size === "")))
    ) yield (f.id, f.name, f.copyNumber)).result
    val execution = database.run(query)
    Await.result(execution, Duration.Inf)
  }

  /**
    * returns all the failed files in the dataset with its copy number.
    * files marked as UPDATED without size are also considered as FAILED
    *
    * @param datasetId dataset from where files are required.
    * @param stage     whether is download or transform
    * @return (fileId, filename, copyNumber, url, hash)
    */
  def getFailedFiles(datasetId: Int, stage: Stage.Value): Seq[(Int, String, Int, String, String)] = {
    val query = (for (f <- files.filter(f => f.datasetId === datasetId && f.stage === stage.toString &&
      (f.status === FILE_STATUS.FAILED.toString || (f.status === FILE_STATUS.UPDATED.toString && f.size === "")))
    ) yield (f.id, f.name, f.copyNumber, f.url, f.hash)).result
    val execution = database.run(query)
    Await.result(execution, Duration.Inf)
  }

  /**
    * marks indicated file as to be UPDATED.
    *
    * @param fileId identifier for the file.
    * @param size   downloaded or transformed file's size
    */
  def markAsUpdated(fileId: Int, size: String): Unit = {
    val query = (for (f <- files.filter(f => f.id === fileId))
      yield (f.status, f.size, f.lastUpdate)).update(FILE_STATUS.UPDATED.toString, size, DateTime.now().toString)
    val execution = database.run(query)
    Await.result(execution, Duration.Inf)
    runFileId(fileId)
  }

  /**
    * marks indicated file as to be UPDATED.
    *
    * @param fileId identifier for the file.
    * @param size   downloaded or transformed file's size.
    * @param hash   downloaded or transformed file's hash.
    */
  def markAsUpdated(fileId: Int, size: String, hash: String): Unit = {
    val query = (for (f <- files.filter(f => f.id === fileId))
      yield (f.status, f.size, f.lastUpdate, f.hash)).update(FILE_STATUS.UPDATED.toString, size, DateTime.now().toString, hash)
    val execution = database.run(query)
    Await.result(execution, Duration.Inf)
    runFileId(fileId)
  }

  /**
    * to be used when the file download or transformation fails, puts file status into FAILED
    *
    * @param fileId identifier for the file.
    */
  def markAsFailed(fileId: Int): Unit = {
    val query = (for (f <- files.filter(f => f.id === fileId))
      yield (f.status, f.lastUpdate)).update(FILE_STATUS.FAILED.toString, DateTime.now().toString)
    val execution = database.run(query)
    Await.result(execution, Duration.Inf)
    runFileId(fileId)
  }

  /**
    * mark all files that have not been compared into the log as outdated.
    * meant to be used at the end of all comparisons (all check if udpate)
    * changes COMPARE to OUTDATED.
    *
    * @param datasetId identifier for the dataset.
    * @param stage     indicates whether refers to download or transformed files.
    */
  def markAsOutdated(datasetId: Int, stage: Stage.Value): Unit = {
    val queryIds = Await.result(database.run((for (f <- files.filter(f => f.datasetId === datasetId &&
      f.status === FILE_STATUS.COMPARE.toString && f.stage === stage.toString)) yield f.id).result), Duration.Inf)

    val query = (
      for (f <- files.filter(
        f => f.datasetId === datasetId && f.status === FILE_STATUS.COMPARE.toString && f.stage === stage.toString
      )) yield f.status).update(FILE_STATUS.OUTDATED.toString)

    val execution = database.run(query)
    Await.result(execution, Duration.Inf)

    queryIds.map(runFileId)
  }

  /**
    * mark all the files with any status into status COMPARE
    * meant to be used to check which files have been deleted from the source.
    *
    * @param datasetId identifier for the dataset.
    * @param stage     indicates whether refers to download or transformed files.
    */
  def markToCompare(datasetId: Int, stage: Stage.Value): Unit = {
    val query = (for (f <- files.filter(f => f.datasetId === datasetId
      && f.stage === stage.toString)
    ) yield f.status).update(FILE_STATUS.COMPARE.toString)
    val execution = database.run(query)
    Await.result(execution, Duration.Inf)
  }

  /**
    * delete all files of given datasetId and the stage.
    *
    * @param datasetId identifier for the dataset.
    * @param stage     indicates whether refers to download or transformed files.
    */
  def delete(datasetId: Int, stage: Stage.Value): Unit = {
    val query = files.filter(f => f.datasetId === datasetId && f.stage === stage.toString)
    val action = query.delete
    val execution = database.run(action)
    Await.result(execution, Duration.Inf)
  }

  /**
    * Gives the current status of a file
    *
    * @param datasetId dataset where the file belongs to
    * @param url       origin url for the file
    * @param stage     whether is download or transform.
    */
  def fileStatus(datasetId: Int, url: String, stage: Stage.Value): Option[FILE_STATUS.Value] = {
    val query = for (f <- files.filter(f => f.datasetId === datasetId
      && f.url === url && f.stage === stage.toString)
    ) yield f.status
    val execution = database.run(query.result)
    val res = Await.result(execution, Duration.Inf)
    if (res.nonEmpty)
      Option(FILE_STATUS.withName(res.head))
    else
      None
  }

  /**
    * returns the url of a specified file.
    *
    * @param fileName  name of the file.
    * @param datasetID dataset from where files are required.
    * @param stage     whether is download or transform.
    * @return the url of the file.
    */
  def getFileUrl(fileName: String, datasetID: Int, stage: Stage.Value): String = {
    val query = for (f <- files.filter(f => f.name === fileName && f.datasetId === datasetID && f.stage === stage.toString)) yield f.url
    val execution = database.run(query.result)
    Await.result(execution, Duration.Inf).head
  }

  //------------------------------DATABASE BASIC OPERATIONS OPEN/CLOSE--------------------------------------------------
  /**
    * Opens or create the database, checks the existence of its tables and creates them if do not exist.
    *
    * @param path directory where the database file is.
    */
  def setDatabase(path: String): Unit = {
    //;DB_CLOSE_ON_EXIT=FALSE
    //jdbc:h2:file:/home/nachon/Downloads/tesis/db/db
    //database = Database.forURL("jdbc:h2:file:" + path + "/db;AUTO_SERVER=TRUE", driver = "org.h2.Driver", keepAliveConnection = true)
    //database = Database.forURL("jdbc:postgresql://localhost/gmql_importer?user=geco&password=geco78", driver = "org.postgresql.Driver", keepAliveConnection = true)

    database =  Database.forURL(
      "jdbc:postgresql://localhost/gmql_importer",
      "geco",
     "geco78",
      driver = "org.postgresql.Driver")

    val tables = Await.result(database.run(MTable.getTables), Duration.Inf).toList
    if (!tables.exists(_.name.name == "SOURCES".toLowerCase())) {
      val setup = DBIO.seq(sources.schema.create)
      val setupFuture = database.run(setup)
      Await.result(setupFuture, Duration.Inf)
      logger.info("Table SOURCES created")
    }
    if (!tables.exists(_.name.name == "DATASETS".toLowerCase())) {
      val setup = DBIO.seq(datasets.schema.create)
      val setupFuture = database.run(setup)
      Await.result(setupFuture, Duration.Inf)
      logger.info("Table DATASETS created")
    }
    if (!tables.exists(_.name.name == "FILES".toLowerCase())) {
      val setup = DBIO.seq(files.schema.create)
      val setupFuture = database.run(setup)
      Await.result(setupFuture, Duration.Inf)
      logger.info("Table FILES created")
    }
    if (!tables.exists(_.name.name == "RUNS".toLowerCase())) {
      val setup = DBIO.seq(runs.schema.create)
      val setupFuture = database.run(setup)
      Await.result(setupFuture, Duration.Inf)
      logger.info("Table RUNS created")
    }
    if (!tables.exists(_.name.name == "RUNSOURCES".toLowerCase())) {
      val setup = DBIO.seq(runSources.schema.create)
      val setupFuture = database.run(setup)
      Await.result(setupFuture, Duration.Inf)
      logger.info("Table RUNSOURCES created")
    }
    if (!tables.exists(_.name.name == "RUNSOURCEPARAMETERS".toLowerCase())) {
      val setup = DBIO.seq(runSourceParameters.schema.create)
      val setupFuture = database.run(setup)
      Await.result(setupFuture, Duration.Inf)
      logger.info("Table RUNSOURCEPARAMETERS created")
    }
    if (!tables.exists(_.name.name == "RUNDATASETS".toLowerCase())) {
      val setup = DBIO.seq(runDatasets.schema.create)
      val setupFuture = database.run(setup)
      Await.result(setupFuture, Duration.Inf)
      logger.info("Table RUNDATASETS created")
    }
    if (!tables.exists(_.name.name == "RUNDATASETPARAMETERS".toLowerCase())) {
      val setup = DBIO.seq(runDatasetParameters.schema.create)
      val setupFuture = database.run(setup)
      Await.result(setupFuture, Duration.Inf)
      logger.info("Table RUNDATASETPARAMETERS created")
    }
    if (!tables.exists(_.name.name == "RUNDATASETLOGS".toLowerCase())) {
      val setup = DBIO.seq(runDatasetLogs.schema.create)
      val setupFuture = database.run(setup)
      Await.result(setupFuture, Duration.Inf)
      logger.info("Table RUNDATASETLOGS created")
    }

    if (!tables.exists(_.name.name == "RUNFILES".toLowerCase())) {
      val setup = DBIO.seq(runFiles.schema.create)
      val setupFuture = database.run(setup)
      Await.result(setupFuture, Duration.Inf)
      logger.info("Table RUNFILES created")
    }
  }

  /**
    * Returns the last date in which a file of the specified dataset is returned.
    *
    * @param datasetID identifier of the dataset.
    * @return the date of the last download.
    */
  def getLastDownloadDate(datasetID: Int): String = {
    val query = for (f <- files.filter(f => f.datasetId === datasetID)) yield f.lastUpdate
    val execution = database.run(query.result)
    val res = Await.result(execution, Duration.Inf)
    res.max
  }

  /**
    * closes the database connection.
    */
  def closeDatabase(): Unit = {
    val closing = database.shutdown
    Await.result(closing, Duration.Inf)
  }

  //-------------------------------------DATABASE SCHEMAS---------------------------------------------------------------

  //---------------------------------- Definition of the SOURCES table--------------------------------------------------
  /**
    * SOURCES TABLE:
    * ID:   INT     PK AUTOINC
    * NAME: STRING
    *
    * @param tag SOURCES
    */
  class Sources(tag: Tag) extends
    Table[(Option[Int], String)](tag, "SOURCES".toLowerCase()) {
    def id = column[Int]("SOURCE_ID".toLowerCase(), O.PrimaryKey, O.AutoInc)

    def name = column[String]("NAME".toLowerCase())

    def sourceNameIndex = index("sourceNameIndex", name, unique = true)

    def * = (id.?, name)
  }

  val sources = TableQuery[Sources]

  //------------------------------------- Definition of the DATASETS table----------------------------------------------
  /**
    * DATASETS TABLE:
    * ID:         INT     PK AUTOINC
    * SOURCE_ID:  INT     FK(SOURCES)
    * NAME:       STRING
    *
    * @param tag DATASETS
    */
  class Datasets(tag: Tag) extends
    Table[(Option[Int], Int, String)](tag, "DATASETS".toLowerCase()) {
    def id = column[Int]("DATASET_ID".toLowerCase(), O.PrimaryKey, O.AutoInc)

    def sourceId = column[Int]("SOURCE_ID".toLowerCase())

    def name = column[String]("NAME".toLowerCase())

    def Source = foreignKey("DATASETS_SOURCE_FK", sourceId, sources)(
      _.id,
      onUpdate = ForeignKeyAction.Restrict,
      onDelete = ForeignKeyAction.Cascade
    )

    def datasetNameIndex = index("DatasetNameIndex", name, unique = false)

    //ADDED
    def datasetSourceIDIndex = index("datasetSourceIdIndex", sourceId, unique = false)

    def * = (id.?, sourceId, name)
  }

  val datasets = TableQuery[Datasets]


  //----------------------------------------- Definition of the FILES table---------------------------------------------
  /**
    * FILES TABLE:
    * ID:                     INT     PK AUTOINC
    * DATASET_ID:             INT     FK(DATASET)
    * URL:                    STRING
    * NAME:                   STRING
    * STAGE:                  STRING
    * STATUS:                 STRING
    * SIZE:                   STRING
    * LAST_UPDATE:            STRING
    * HASH:                   STRING
    * SIZE_IN_ORIGIN:         STRING
    * LAST_UPDATE_IN_ORIGIN:  STRING
    * DATE_PROCESSED:         STRING
    * COPY_NUMBER:            INT
    *
    * @param tag FILES
    */
  class Files(tag: Tag) extends
    Table[File](tag, "FILES".toLowerCase()) {
    def id = column[Int]("FILE_ID".toLowerCase(), O.PrimaryKey, O.AutoInc)

    def datasetId = column[Int]("DATASET_ID".toLowerCase())

    def url = column[String]("URL".toLowerCase())

    def name = column[String]("NAME".toLowerCase())

    def stage = column[String]("STAGE".toLowerCase())

    //THIS IS DOWNLOAD/TRANSFORM
    def status = column[String]("STATUS".toLowerCase())

    //UPDATED/FAILED/OUTDATED
    def size = column[String]("FILE_SIZE".toLowerCase())

    def lastUpdate = column[String]("LAST_UPDATE".toLowerCase())

    def hash = column[String]("HASH".toLowerCase())

    def originSize = column[String]("SIZE_IN_ORIGIN".toLowerCase())

    def originLastUpdate = column[String]("LAST_UPDATE_IN_ORIGIN".toLowerCase())

    def dateProcessed = column[String]("DATE_PROCESSED".toLowerCase())

    def copyNumber = column[Int]("COPY_NUMBER".toLowerCase())

    def Dataset = foreignKey("FILES_DATASET_FK", datasetId, datasets)(
      _.id,
      onUpdate = ForeignKeyAction.Restrict,
      onDelete = ForeignKeyAction.Cascade
    )

    def fileUrlIndex = index("fileUrlIndex", url, unique = false)

    def fileNameIndex = index("fileNameIndex", name, unique = false)

    //ADDED
    def fileDatasetIdIndex = index("fileDatasetIdIndex", datasetId, unique = false)


    override def * = (id.?, datasetId, url, name, stage, status, size, lastUpdate, hash,
      originSize, originLastUpdate, dateProcessed, copyNumber) <> (File.tupled, File.unapply)
  }

  val files = TableQuery[Files]

  //------------------------------------- Definition of the RUNS table--------------------------------------------------
  /**
    * RUNS TABLE:
    * ID:                 INT     PK AUTOINC
    * DATETIME_START:     STRING
    * DATETIME_END:       STRING
    * DOWNLOAD_ENABLED:   STRING
    * TRANSFORM_ENABLED:  STRING
    * LOAD_ENABLED:       STRING
    * OUTPUT_FOLDER:      STRING
    *
    * @param tag RUNS
    */
  class Runs(tag: Tag) extends
    Table[(Option[Int], String, String, String, String, String, String)](tag, "RUNS".toLowerCase()) {
    def id = column[Int]("RUN_ID".toLowerCase(), O.PrimaryKey, O.AutoInc)

    def datetimeStart = column[String]("DATETIME_START".toLowerCase())

    def datetimeEnd = column[String]("DATETIME_END".toLowerCase())

    def downloadEnabled = column[String]("DOWNLOAD_ENABLED".toLowerCase())

    def transformEnabled = column[String]("TRANSFORM_ENABLED".toLowerCase())

    def loadEnabled = column[String]("LOAD_ENABLED".toLowerCase())

    def outputFolder = column[String]("OUTPUT_FOLDER".toLowerCase())

    def * = (id.?, datetimeStart, datetimeEnd, downloadEnabled, transformEnabled, loadEnabled, outputFolder)
  }

  val runs = TableQuery[Runs]

  //------------------------------------------- Definition of the RUNSOURCES table--------------------------------------
  /**
    * RUNSOURCES TABLE:
    * ID:                 INT     PK AUTOINC
    * RUN_ID:             INT     FK(RUNS)
    * SOURCE_ID:          INT     FK(SOURCES)
    * URL:                STRING
    * OUTPUT_FOLDER:      STRING
    * DOWNLOAD_ENABLED:   STRING
    * DOWNLOADER:         STRING
    * TRANSFORM_ENABLED:  STRING
    * TRANSFORMER:        STRING
    * LOAD_ENABLED:       STRING
    * LOADER:             STRING
    *
    * @param tag RUNSOURCES
    */
  class RunSources(tag: Tag) extends
    Table[(Option[Int], Int, Int, String, String, String, String, String, String, String, String)](tag, "RUNSOURCES".toLowerCase()) {
    def id = column[Int]("RUNSOURCE_ID".toLowerCase(), O.PrimaryKey, O.AutoInc)

    def runId = column[Int]("RUN_ID".toLowerCase())

    def sourceId = column[Int]("SOURCE_ID".toLowerCase())

    def url = column[String]("URL".toLowerCase())

    def outputFolder = column[String]("OUTPUT_FOLDER".toLowerCase())

    def downloadEnabled = column[String]("DOWNLOAD_ENABLED".toLowerCase())

    def downloader = column[String]("DOWNLOADER".toLowerCase())

    def transformEnabled = column[String]("TRANSFORM_ENABLED".toLowerCase())

    def transformer = column[String]("TRANSFORMER".toLowerCase())

    def loadEnabled = column[String]("LOAD_ENABLED".toLowerCase())

    def loader = column[String]("LOADER".toLowerCase())

    def Source = foreignKey("RUNSOURCES_SOURCE_FK", sourceId, sources)(
      _.id,
      onUpdate = ForeignKeyAction.Restrict,
      onDelete = ForeignKeyAction.Cascade
    )

    def Run = foreignKey("RUNSOURCES_Run_FK", runId, runs)(
      _.id,
      onUpdate = ForeignKeyAction.Restrict,
      onDelete = ForeignKeyAction.Cascade
    )

    //ADDED
    def runsourceSourceIdIndex = index("runsourceSourceIdIndex", sourceId, unique = false)
    def runsourceRunIdIndex = index("runsourceRunIdIndex", runId, unique = false)

    def * = (id.?, runId, sourceId, url, outputFolder, downloadEnabled,
      downloader, transformEnabled, transformer, loadEnabled, loader)
  }

  val runSources = TableQuery[RunSources]

  //------------------------------------ Definition of the RUNSOURCEPARAMETERS table------------------------------------
  /**
    * RUNSOURCEPARAMETERS TABLE:
    * ID:           INT     PK AUTOINC
    * RUNSOURCE_ID: INT     FK(RUNSOURCES)
    * DESCRIPTION:  STRING
    * KEY:          STRING
    * VALUE:        STRING
    *
    * @param tag RUNSOURCEPARAMETERS
    */
  class RunSourceParameters(tag: Tag) extends
    Table[(Option[Int], Int, String, String, String)](tag, "RUNSOURCEPARAMETERS".toLowerCase()) {
    def id = column[Int]("RUNSOURCEPARAMETER_ID".toLowerCase(), O.PrimaryKey, O.AutoInc)

    def runSourceId = column[Int]("RUNSOURCE_ID".toLowerCase())

    def description = column[String]("DESCRIPTION".toLowerCase())

    def key = column[String]("PARAM_KEY".toLowerCase())

    def value = column[String]("VALUE".toLowerCase())

    def RunSource = foreignKey("RUNSOURCEPARAMETERS_RUNSOURCE_FK", runSourceId, runSources)(
      _.id,
      onUpdate = ForeignKeyAction.Restrict,
      onDelete = ForeignKeyAction.Cascade
    )

    //ADDED
    def runsourceparametersRunsourceIdIndex = index("runsourceparametersRunsourceIdIndex", runSourceId, unique = false)

    def * = (id.?, runSourceId, description, key, value)
  }

  val runSourceParameters = TableQuery[RunSourceParameters]

  //------------------------------------------- Definition of the RUNDATASETS table-------------------------------------
  /**
    * RUNDATASETS TABLE:
    * ID:                 INT     PK AUTOINC
    * RUN_ID:             INT     FK(RUNS)
    * DATASET_ID:         INT     FK(DATASETS)
    * OUTPUT_FOLDER:      STRING
    * DOWNLOAD_ENABLED:   STRING
    * TRANSFORM_ENABLED:  STRING
    * LOAD_ENABLED:       STRING
    * SCHEMA_LOCATION:    STRING
    * SCHEMA_URL:         STRING
    *
    * @param tag RUNDATASETS
    */
  class RunDatasets(tag: Tag) extends
    Table[(Option[Int], Int, Int, String, String, String, String, String, String)](tag, "RUNDATASETS".toLowerCase()) {
    def id = column[Int]("RUNDATASET_ID".toLowerCase(), O.PrimaryKey, O.AutoInc)

    def runId = column[Int]("RUN_ID".toLowerCase())

    def datasetId = column[Int]("DATASET_ID".toLowerCase())

    def outputFolder = column[String]("OUTPUT_FOLDER".toLowerCase())

    def downloadEnabled = column[String]("DOWNLOAD_ENABLED".toLowerCase())

    def transformEnabled = column[String]("TRANSFORM_ENABLED".toLowerCase())

    def loadEnabled = column[String]("LOAD_ENABLED".toLowerCase())

    def schemaLocation = column[String]("SCHEMA_LOCATION".toLowerCase())

    def schemaUrl = column[String]("SCHEMA_URL".toLowerCase())

    def Dataset = foreignKey("RUNDATASETS_DATASET_FK", datasetId, datasets)(
      _.id,
      onUpdate = ForeignKeyAction.Restrict,
      onDelete = ForeignKeyAction.Cascade
    )

    def Run = foreignKey("RUNDATASETS_Run_FK", runId, runs)(
      _.id,
      onUpdate = ForeignKeyAction.Restrict,
      onDelete = ForeignKeyAction.Cascade
    )

    //ADDED
    def rundatasetsRunIdIndex = index("rundatasetsRunIdIndex", runId, unique = false)
    def rundatasetsDatasetIdIndex = index("rundatasetsDatasetIdIndex", datasetId, unique = false)

    def * = (id.?, runId, datasetId, outputFolder, downloadEnabled, transformEnabled, loadEnabled, schemaLocation, schemaUrl)
  }

  val runDatasets = TableQuery[RunDatasets]

  //--------------------------------------- Definition of the RUNDATASETPARAMETERS table---------------------------------
  /**
    * RUNDATASETPARAMETERS TABLE:
    * ID:             INT     PK AUTOINC
    * RUNDATASET_ID:  INT     FK(RUNDATASETS)
    * DESCRIPTION:    STRING
    * KEY:            STRING
    * VALUE:          STRING
    *
    * @param tag RUNDATASETPARAMETERS
    */
  class RunDatasetParameters(tag: Tag) extends
    Table[(Option[Int], Int, String, String, String)](tag, "RUNDATASETPARAMETERS".toLowerCase()) {
    def id = column[Int]("RUNDATASETPARAMETER_ID".toLowerCase(), O.PrimaryKey, O.AutoInc)

    def runDatasetId = column[Int]("RUNDATASET_ID".toLowerCase())

    def description = column[String]("DESCRIPTION".toLowerCase())

    def key = column[String]("PARAM_KEY".toLowerCase())

    def value = column[String]("VALUE".toLowerCase())

    def RunDataset = foreignKey("RUNDATASETPARAMETERS_RUNDATASET_FK", runDatasetId, runDatasets)(
      _.id,
      onUpdate = ForeignKeyAction.Restrict,
      onDelete = ForeignKeyAction.Cascade
    )

    //ADDED
    def rundatasetparametersRundatasetIdIndex = index("rundatasetparametersRundatasetIdIndex", runDatasetId, unique = false)

    def * = (id.?, runDatasetId, description, key, value)
  }

  val runDatasetParameters = TableQuery[RunDatasetParameters]

  //--------------------------------------- Definition of the RUNDATASETLOGS table---------------------------------
  /**
    * RUNDATASETLOGS TABLE:
    * ID:              INT     PK AUTOINC
    * RUNDATASET_ID:   INT     FK(RUNDATASETS)
    * STAGE:           STRING
    * TOTAL_FILES:     INT
    * DOWNLOADED_FILES:INT
    *
    * @param tag RUNDATASETLOGS
    */
  class RunDatasetLogs(tag: Tag) extends
    Table[(Option[Int], Int, String, Int, Int)](tag, "RUNDATASETLOGS".toLowerCase()) {
    def id = column[Int]("RUNDATASETLOGS_ID".toLowerCase(), O.PrimaryKey, O.AutoInc)

    def runDatasetId = column[Int]("RUNDATASET_ID".toLowerCase())

    def stage = column[String]("STAGE".toLowerCase())

    def totalFiles = column[Int]("TOTAL_FILES".toLowerCase())

    def downloadedFiles = column[Int]("DOWNLOADED_FILES".toLowerCase())

    def RunDataset = foreignKey("RUNDATASETLOGS_RUNDATASET_FK", runDatasetId, runDatasets)(
      _.id,
      onUpdate = ForeignKeyAction.Restrict,
      onDelete = ForeignKeyAction.Cascade
    )

    //ADDED
    def rundatasetlogsRundatasetIdIndex = index("rundatasetlogsRundatasetIdIndex", runDatasetId, unique = false)

    def * = (id.?, runDatasetId, stage, totalFiles, downloadedFiles)
  }

  val runDatasetLogs = TableQuery[RunDatasetLogs]

  //--------------------------------------------- Definition of the RUNFILES table--------------------------------------
  /**
    * RUNFILES TABLE:
    * ID:                     INT     PK AUTOINC
    * RUN_ID:                 INT     FK(RUNS)
    * FILES:                  INT     FK(FILES)
    * STATUS:                 STRING
    * SIZE:                   STRING
    * LAST_UPDATE:            STRING
    * HASH:                   STRING
    * SIZE_IN_ORIGIN:         STRING
    * LAST_UPDATE_IN_ORIGIN:  STRING
    * DATE_PROCESSED:         STRING
    *
    * @param tag RUNFILES
    */
  class RunFiles(tag: Tag) extends
    Table[(Option[Int], Int, Int, String, String, String, String, String, String, String)](tag, "RUNFILES".toLowerCase()) {
    def id = column[Int]("RUNFILE_ID".toLowerCase(), O.PrimaryKey, O.AutoInc)

    def runId = column[Int]("RUN_ID".toLowerCase())

    def fileId = column[Int]("FILE_ID".toLowerCase())

    //THIS IS DOWNLOAD/TRANSFORM
    def status = column[String]("STATUS".toLowerCase())

    //UPDATED/FAILED/OUTDATED
    def size = column[String]("FILE_SIZE".toLowerCase())

    def lastUpdate = column[String]("LAST_UPDATE".toLowerCase())

    def hash = column[String]("HASH".toLowerCase())

    def originSize = column[String]("SIZE_IN_ORIGIN".toLowerCase())

    def originLastUpdate = column[String]("LAST_UPDATE_IN_ORIGIN".toLowerCase())

    def dateProcessed = column[String]("DATE_PROCESSED".toLowerCase())

    def File = foreignKey("RUNFILES_FILE_FK", fileId, files)(
      _.id,
      onUpdate = ForeignKeyAction.Restrict,
      onDelete = ForeignKeyAction.Cascade
    )

    //ADDED
    def runfilesFileIdIndex = index("runfilesFileIdIndex", fileId, unique = false)
    def runfilesRunIdIndex = index("runfilesRunIdIndex", runId, unique = false)

    def * = (id.?, runId, fileId, status, size, lastUpdate, hash, originSize, originLastUpdate, dateProcessed)
  }

  val runFiles = TableQuery[RunFiles]
}