var db = module.exports

var _ = require('lodash')
var connection = require("./bookshelf")
var bookshelf = connection.bookshelf
var knex = connection.knex

/* MODELS */

var Customer = bookshelf.Model.extend({ tableName: 'cw_customers' })
var OrderSKU = bookshelf.Model.extend({ tableName: 'cw_order_skus' })
var Order = bookshelf.Model.extend({ tableName: 'cw_orders' })
var Product = bookshelf.Model.extend({ tableName: 'cw_products' })
var SKU = bookshelf.Model.extend({ tableName: 'cw_skus' })

/* METHODS */

db.products = function () {
  return Product.query(function(qb){
    qb.orderBy("product_name", "asc")
  }).fetchAll()
}

db.ordersBySku = function (sku) {
  return new Promise(function (resolve, reject){
    SKU.where('sku_merchant_sku_id', sku).fetchAll().then(function(skus){
      if (! skus.length) {
        return resolve([])
      }
      var sku = skus.at(0)
      var sku_id = sku.get('sku_id')
      OrderSKU.where('ordersku_sku', sku_id).fetchAll({ columns: 'ordersku_order_id' }).then(function(ordersku_ids){
        var order_ids = ordersku_ids.map(function(el){ return el.get('ordersku_order_id') })
        Order.where('order_id', 'in', order_ids).fetchAll().then(function(orders){
          resolve(orders)
        })
      })
    })
  })
}

/*

# cw_products
# 0 id  1 sku  2 product_name

# cw_skus
# 0 sku_id  1 sku  2 product_id

# cw_orders
# 0 order_id  1 date  2 status  3 customer_id

# cw_order_skus
# 0 id  1 order_id  2 sku_id

*/