var db = module.exports

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

/* MODELS */

var Image = db.Image = bookshelf.Model.extend({
  tableName: 'plops',
  hasTimestamps: true,
})
var ShaderImage = db.ShaderImage = bookshelf.Model.extend({
  tableName: 'im_cmd',
  hasTimestamps: false,
})

db.getImage = function(id) {
  var model = new Image({'id': id})
  return model.fetch()
}
db.getLatest = function () {
  return Image.query(function(qb){
    qb.orderBy("id", "desc").limit(1)
  }).fetch()
}
db.getRandom = function () {
  return Image.query(function(qb){
    qb.orderBy(knex.raw('RAND()')).limit(1)
  }).fetch()
}
db.getRandomAscii = function () {
  return ShaderImage.query(function(qb){
    qb.where("tag", "ascii").orderBy(knex.raw('RAND()')).limit(1)
  }).fetch()
}
db.getIndex = function(limit, offset) {
  return Image.query(function(qb){
    qb.orderBy("id", "desc").limit(limit)
    if (offset) qb.offset(offset)
  }).fetchAll()
}
db.createImage = function(url){
  return new Image({ url: url }).save()
}
db.createShaderImage = function(data){
  return new ShaderImage(data).save()
}
// select name,dir,newfile,count(*) as count from im_cmd where tag='ascii' group by name order by count desc;
db.getUserCounts = function(){
  return knex.column('name').column('dir').column('newfile').count('* as count').from('im_cmd').where('tag','ascii').groupBy('name').orderBy(knex.raw('count'), 'desc')
}
db.getLatestByUser = function(){
  return knex.max('id as id').count('* as count').column('name').from('im_cmd').where('tag','ascii').groupBy('name').orderBy('count', 'desc')
  // raw('SELECT MAX(id) as id, COUNT(*) as count, name FROM im_cmd WHERE tag='ascii' GROUP BY name')
}
db.getPhotoblasterImages = function(ids) {
  return ShaderImage.query(qb => { return qb.where('id', 'in', ids) }).fetchAll()
}