getGoogleDriveIds = function(d){
  require(googledrive)
  
  rds = paste0(d,"/myDrive.RDS")
  if (!file.exists(rds)){
    df = drive_find()
    saveRDS(df,file=rds)
    write.table(df[,1:2],file=paste0(d,"/myDrive.tsv"),quote=F,row.names=F,sep="\t")
  }
  df = readRDS(rds)
  
  res = list(
    request = df[grep("Request \\(Resp",df$name),]$id,
    runs = df[grep("GenomicsPlatformProjects",df$name),]$id
  )
  return(res)
}

loadDB = function(update=F){
  library(googledrive)
  d = "~/Documents/Projects/pipeline/DATABASE"
  if (!file.exists(d)){dir.create(d)}
  requestFile = paste0(d,"/requests.csv")
  dbFile = paste0(d,"/database.xlsx")
  
  if (update | !file.exists(requestFile) | !file.exists(dbFile)){
    if (file.exists(dbFile)){
      file.rename(dbFile,paste0(d,"/database-PREVIOUS.xlsx"))
    }
    if (file.exists(requestFile)){
      file.rename(requestFile,paste0(d,"/requests-PREVIOUS.csv"))
    }
    
    ids = getGoogleDriveIds(d)
    
    dl = drive_download(
      as_id(ids$request), path = requestFile, 
      overwrite = T,type="csv")
    #req = read.csv(dl$local_path)
    
    dl = drive_download(
      as_id(ids$runs), path = dbFile, 
      overwrite = T,type="xlsx")
    #db = parseGenomicsPlatformProjectFile(dl$local_path)
  }
  
  req = read.csv(requestFile)
  db = parseGenomicsPlatformProjectFile(dbFile)
 
  return(list(req=req,db=db))
}

# direct export from Google drive
parseGenomicsPlatformProjectFile = function(file){
  require(readxl)
  require(lubridate)
  gen = list(
    proj = as.data.frame(read_excel(file,sheet = 1)),
    ds =  as.data.frame(read_excel(file,sheet = 2)),
    run =  as.data.frame(read_excel(file,sheet = 3))
  )
  
  #rownames(gen$proj) = gen$proj$id
  #rownames(gen$ds) = gen$ds$id
  #rownames(gen$run) = gen$run$id
  
  #dates = lubridate::as_date(runs$date_run)
  runs = gen$run
  runs = runs[!is.na(runs$date_run),]
  runs$date_run = ymd(runs$date_run)
  runs$year = year(runs$date_run)
  runs$month = month(runs$date_run)
  #runs$TSchar = as.character(runs$requestTimestamp)
  gen$run = runs
  
  return(gen)
}

# direct export from Google drive
parseRequestsFile = function(file){
  require(readxl)
  #require(lubridate)
  re = as.data.frame(read_excel(file))
  
  #dates = lubridate::as_date(runs$date_run)
  #runs = runs[!is.na(runs$date_run),]
  #runs$date_run = ymd(runs$date_run)
  
 colnames(re)[[2]] = "Name"
 colnames(re)[[3]] = "ProjectID"
 colnames(re)[[4]] = "DatasetID"
 colnames(re)[[6]] = "ExpectedDate"
 
 re$TSchar = as.character(re$Timestamp)
  
  return(re)
}

getDataTimeRange = function(gen,start=NULL,end=NULL){
  
  runs = gen$run
  
  if (!is.null(start)){
    runs = subset(runs,date_run >= as.Date(start))
  }
  if (!is.null(end)){
    runs = subset(runs,date_run <= as.Date(end))
  }
  
  gen$run = runs
  gen$ds = subset(gen$ds,id %in% runs$dataset_ids)
  gen$proj = subset(gen$proj,id %in% gen$ds$project_id)
  
  return(gen)
}

getDataGroup = function(gen,groups){
  gen$proj = subset(gen$proj,group %in% groups)
  gen$ds = subset(gen$ds,project_id %in% gen$proj$id)
  gen$run = subset(gen$run,dataset_ids %in% gen$ds$id)
  return(gen)
}

getDataType = function(gen,types,details=T){
  if (details){
    gen$ds = subset(gen$ds,data_type_detail %in% types)
  } else {
    gen$ds = subset(gen$ds,data_type %in% types)
  }
  gen$run = subset(gen$run,dataset_ids %in% gen$ds$id)
  gen$proj = subset(gen$proj,id %in% gen$ds$project_id)
  return(gen)
}

makeUnique = function(gen){
  run = gen$run
  
  #any(duplicated(runs$id))
  runsUniq = unique(run[,c("id","date_run")])
  rownames(runsUniq) = runsUniq$i
  
  ds = subset(gen$ds,id %in% run$dataset_ids)
  #any(duplicated(ds$id))
  rownames(ds) = ds$id
  proj = subset(gen$proj,id %in% ds$project_id)
  
  uniq = list(proj=proj,ds=ds,run=run)
  return(uniq)
}

getInfo = function(gen){
  runs = gen$run
  
  #any(duplicated(runs$id))
  runsUniq = unique(runs[,c("id","date_run")])
  rownames(runsUniq) = runsUniq$i
  ds = subset(gen$ds,id %in% runs$dataset_ids)
  #any(duplicated(ds$id))
  rownames(ds) = ds$id
  projs = subset(gen$proj,id %in% ds$project_id)
  
  groups = sort(unique(projs$group))
  users = sort(unique(projs$name))
  info = paste0(
    "This data contain:",
    "\n",nrow(runsUniq)," sequencing runs, in ",
    "\n",nrow(ds)," datasets, as part of ",
    "\n",nrow(projs)," projects working with ",
    "\n",length(users)," users from ",
    "\n",length(groups)," groups."
  )
  cat(info)
  
  res = c(
    n_runs=nrow(runsUniq),
    n_dataset=nrow(ds),
    n_project=nrow(projs),
    n_user=length(users),
    n_group=length(groups)
  )
  
  return(res)
}

flattenDB = function(gen){
  runs = gen$run
  runs = merge(runs,gen$ds,by.x="dataset_ids",by.y="id")
  runs = merge(runs,gen$proj,by.x="project_id",by.y="id")
  return(runs)
}