var db = module.exports

var connection = require("./bookshelf")
var bookshelf = connection.bookshelf
var knex = connection.knex

/* MODELS */

var User = db.User = bookshelf.Model.extend({
  tableName: 'users',
  hasTimestamps: false,
})
var Thread = db.Thread = bookshelf.Model.extend({
  tableName: 'threads',
  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.getUserByUsername = function(username) {
  var model = new User({'username': username})
  return model.fetch()
}
db.getLastlog = function(limit){
  return knex.column('username').column('lastseen').select().from('users').orderBy('lastseen', 'desc').limit(limit || 10)
}


/* THREADS */

db.getLatestThreads = function () {
  return Thread.query(function(qb){
    qb.orderBy("id", "desc").limit(50)
  }).fetchAll()
}

/* FILES */
db.getFilesForThread = function (id){
  return File.query("where", "thread", "=", id).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').select().from('files').where('thread', 'in', ids).groupBy('thread')
}

/* COMMENTS */
db.getCommentsForThread = function (id, limit, offset){
  return Comment.query(function(qb){
    qb.where("thread", "=", id).orderBy("id", "desc")
    if (limit) {
      qb = qb.limit(limit)
    }
    if (offset) {
      qb = qb.offset(offset)
    }
  }).fetchAll().then(function(comments){
    comments.forEach(function(comment){
      comment.set("comment", comment.get("comment").toString() )
    })
    return comments
  })
}
db.getCommentCounts = function(ids){
  return knex.column('thread').count('* as count').select().from('comments').where('thread', 'in', ids).groupBy('thread')
}

/* PRIVATE MESSAGES */

db.getMessage = function (id){
  var model = new Message({'id': id})
  return model.fetch().then(function(message){
    message.set("body", message.get("body").toString() )
    return message
  })
}