var db = module.exports

var connection = require("./bookshelf")
var bookshelf = connection.bookshelf
var knex = connection.knex
var upload = require('../util/upload')

/* MODELS */

var User = db.User = bookshelf.Model.extend({
  tableName: 'users',
  hasTimestamps: false,
})
var Thread = db.Thread = bookshelf.Model.extend({
  tableName: 'threads',
  hasTimestamps: false,
  checkPrivacy: function(user){
    if (this.get('privacy') === 0) return true
    let username = user.get('username')
    if (this.get('username') === username) return true
    let allowed = (this.get('allowed') || '').split(',')
    if (allowed.indexOf(username) !== -1) return true
    return false
  }
})
var ThreadUser = db.ThreadUser = bookshelf.Model.extend({
  tableName: 'thread_users',
  hasTimestamps: false,
})
var Comment = db.Comment = bookshelf.Model.extend({
  tableName: 'comments',
  hasTimestamps: false,
})
var File = db.File = bookshelf.Model.extend({
  tableName: 'files',
  hasTimestamps: false,
})
var Keyword = db.Keyword = bookshelf.Model.extend({
  tableName: 'keywords',
  hasTimestamps: false,
})
var Mailbox = db.Mailbox = bookshelf.Model.extend({
  tableName: 'boxes',
  hasTimestamps: false,
})
var Message = db.Message = bookshelf.Model.extend({
  tableName: 'messages',
  hasTimestamps: false,
})

/* USERS */

db.createUser = function(data){
  return new db.User(data).save()
}
db.getUsers = function () {
  return User.query(function(qb){
    qb.orderBy("id", "desc")
  }).fetchAll()
}
db.getUser = function(id) {
  var model = new User({'id': id})
  return model.fetch()
}
db.getUsersById = function(ids){
  return User.where("id", "in", ids).fetchAll()
}
db.getUsernamesById = function(ids){
  return knex.column("id").column("username")
    .select().from('users').where("id", "in", ids)
}
db.checkUsernames = function(usernames){
  return knex.column("username")
    .select().distinct().from('users').where("username", "in", usernames)
}
db.getUserByUsername = function(username) {
  return new User({'username': username}).fetch()
}
db.getLastlog = function(limit){
  return knex.column('id').column('username').column('lastseen')
    .select().from('users').orderBy('lastseen', 'desc').limit(limit || 10)
}

/* THREADS */

db.getLatestThreads = function () {
  return Thread.query(function(qb){
    qb.orderBy("lastmodified", "desc").limit(50)
  }).fetchAll()
}
db.getThreadsForKeyword = function (keyword) {
  return Thread.query(function(qb){
    qb.where("keyword", "=", keyword).orderBy("id", "desc")
  }).fetchAll()
}
db.getThreadsForUser = function (username, limit, offset) {
  return Thread.query(function(qb){
    qb.where("username", "=", username).orderBy("id", "desc").limit(limit || 40).offset(offset || 0)
  }).fetchAll()
}
db.getTopThreadsForUser = function (username, limit, offset) {
  return Thread.query(function(qb){
    qb.where("username", "=", username).orderBy("viewed", "desc").limit(limit || 40).offset(offset || 0)
  }).fetchAll()
}
db.getThread = function (id) {
  return Thread.query("where", "id", "=", id).fetch()
}
db.getThreadsById = function(ids){
  return Thread.where("id", "in", ids).fetchAll()
}
db.createThread = function(data){
  return new db.Thread(data).save()
}
db.updateThread = function(data){
}
db.destroyThread = function(id){
}

/* THREAD USERS (user privacy) */
db.addUserToThread = function(user_id, thread_id){
  return new ThreadUser({ user_id, thread_id }).save()
}
db.removeUserFromThread = function(user_id, thread_id){
  return new ThreadUser({ user_id, thread_id }).destroy()
}
db.getThreadUserIds = function(thread_id){
  return ThreadUser.column("user_id").query("where", "thread_id", "=", thread_id).fetch()
}
db.getThreadUsers = function(thread_id){
  return db.getThreadUserIds(thread_id).then(thread_users => {
    if (! thread_users.length) return []
    var user_ids = thread_users.pluck("user_id")
    return db.getUsernamesById(user_ids).then(usernames => {
      return usernames
    })
  })
}
db.getUserThreadIds = function(user_id){
  return ThreadUser.query("where", "user_id", "=", user_id).fetch()
}

/* FILES */

db.getFileById = function(id){
  return (new File({'id': id})).fetch()
}
db.getFilesForThread = function (id){
  return File.query("where", "thread", "=", id).fetchAll()
}
db.getFilesForUser = function (username, limit, offset){
  return File.query(function(qb){
    qb.where("username", "=", username)
      .orderBy('id', 'desc')
      .limit(parseInt(limit) || 20)
      .offset(parseInt(offset) || 0)
    }).fetchAll()
}
db.getFileCounts = function(ids){
  return knex.column('thread').count('* as count').select().from('files').where('thread', 'in', ids).groupBy('thread')
}
db.getFileSizes = function(ids){
  return knex.column('thread').sum('size as size').count('* as count').select().from('files').where('thread', 'in', ids).groupBy('thread')
}
db.getFilesById = function(ids){
  return File.where("id", "in", ids).fetchAll()
}
db.createFile = function(data){
  return new db.File(data).save()
}
db.destroyFile = function(id){
}
db.moveFile = function(file, thread_id){
  var s3client = upload.client()
  var srcPath = process.env.S3_PATH + '/data/' + file.get('thread') + '/' + file.get('filename')
  var destPath = process.env.S3_PATH + '/data/' + thread_id + '/' + file.get('filename')
  console.log('moving', srcPath, 'to', destPath)
  var copyPromise = new Promise((resolve, reject) => {
    s3client.copy(srcPath, destPath).on('response', function(res){
      console.log('copied file')
      s3client.deleteFile(srcPath, function(err, res){
        if (err) {
          console.log('error deleting file')
          return reject(err)
        }
        file.set('thread', thread_id)
        file.save().then(() => {
          resolve()
        }).catch(err => {
          console.log('error saving file')
          reject(err)
        })
      })
    }).on('error', err => {
      console.error('/!\\ error connecting to s3')
      reject(err)
    }).end()
  })
  return copyPromise
}
db.destroyFiles = function(files){
  var s3client = upload.client()
  var rmPromises = files.map((file) => {
    return new Promise ((resolve, reject) => {
      var thread_id = file.get('thread')
      var filename = file.get('filename')
      if (! thread_id || ! filename) {
        return resolve()
      }
      var filePath = process.env.S3_PATH + '/data/' + thread_id + '/' + filename
      console.log('delete', filePath)
      s3client.deleteFile(filePath, function(err, res){
        // check `err`, then do `res.pipe(..)` or `res.resume()` or whatever.
        resolve()
      })
    })
  })
  var filePromises = files.map((file) => {
    return file.destroy()
  })
  return Promise.all(rmPromises.join(filePromises))
}

/* COMMENTS */

db.getCommentsForThread = function (id, limit, offset, order){
  order = order || "asc"
  return Comment.query(function(qb){
    qb.where("thread", "=", id).orderBy("id", order)
    if (limit) {
      qb.limit(limit)
    }
    if (offset) {
      qb.offset(offset)
    }
  }).fetchAll().then(function(comments){
    comments.forEach(function(comment){
      comment.set("comment", comment.get("comment").toString() )
    })
    return comments
  })
}
db.getCommentById = function(id){
  return (new Comment({'id': id})).fetch()
}
db.getCommentsById = function(ids){
  return Comment.where("id", "in", ids).fetchAll()
}
db.getCommentsForUser = function (username, limit){
  return Comment.query(function(qb){
    qb.where("username", "=", username)
      .orderBy('date', 'desc')
      .limit(parseInt(limit) || 20)
    }).fetchAll()
}
db.getCommentCounts = function(ids){
  return knex.column('thread').count('* as count').select().from('comments').where('thread', 'in', ids).groupBy('thread')
}
db.createComment = function(data){
  return new db.Comment(data).save()
}
db.updateComment = function(data){
}
db.destroyComment = function(id){
}


/* KEYWORDS */

db.getKeywords = function (keywords){
  if (keywords) {
    return Keyword.query("where", "keyword", "in", keywords).fetchAll()
  }
  return Keyword.fetchAll()
}
db.getKeyword = function (keyword) {
  return Keyword.query("where", "keyword", "=", keyword).fetch()
}
db.getThreadGroups = function (keyword) {
  return knex.column('keyword').sum('viewed').as('viewed').count('*').as('count').column('id').column('title').column('lastmodified').column('privacy').select().from('threads').groupBy('keyword')
}
db.createKeyword = function(data){
  return new db.Keyword(data).save()
}
db.updateKeyword = function(data){
}
db.destroyKeyword = function(id){
}


/* MAILBOXES */

db.getMailboxes = function(username){
  return Mailbox.query("where", "owner", "=", username).fetchAll()
}
db.getMailbox = function(mbox){
  return new Mailbox({mbox: mbox}).fetch()
}
db.getMailboxCounts = function(boxes){
  return knex.column('mbox').count('* as count').select().from('messages').where('mbox', 'in', boxes).groupBy('mbox')
}
db.createMailbox = function(data){
  return new db.Mailbox(data).save()
}
db.bumpMailboxCount = function(mbox){
  new db.Mailbox({ mbox: mbox }).fetch()
}
db.checkMail = function(username){
  return knex.count('* as count').select().from('messages').where('recipient', '=', username).andWhere('unread', '=', 1)
}

/* MESSAGES */

db.getMessages = function(username, box, limit, offset){
  var mbox = username + "." + box
  return Message.query(function(qb){
    qb.column('id', 'mbox', 'unread', 'sender', 'recipient', 'date', 'subject', knex.raw("CHAR_LENGTH(body) as size")).where("mbox", "=", mbox).orderBy("id", "desc")
    if (limit) {
      qb.limit(limit)
    }
    if (offset) {
      qb.offset(offset)
    }
  }).fetchAll()
}
db.getMessage = function (id){
  var model = new Message({'id': id})
  return model.fetch().then(function(message){
    if (! message) return null
    message.set("body", message.get("body").toString() )
    return message
  })
}
db.createMessage = function(data){
  return new db.Message(data).save()
}
db.updateMessage = function(data){
}
db.destroyMessage = function(id){
}