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.products = function () {
  return new Promise(function (resolve, reject){
    SKU.fetchAll().then(function(skus){
      // sku_merchant_sku_id
      Product.fetchAll().then(function(products){
        var product_name_lookup = {}
        products.map(function(product){
          product_name_lookup[ product.get('product_id') ] = product.get('product_name')
        })
        var mapped_products = skus.map(function(sku){
          var name = product_name_lookup[ sku.get('sku_product_id') ] || "Unknown"
          return [name.toUpperCase(), sku, name]
        }).sort(function(a,b){
          return a[0] < b[0] ? -1 : a[0] === b[0] ? 0 : 1
        }).map(function(pair){
          var sku = pair[1]
          return {
            product_merchant_product_id: sku.get('sku_merchant_sku_id'),
            product_name: pair[2],
          }
        })
        resolve(mapped_products)
      })
    })
  })
}

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.pluck('ordersku_order_id')
        Order.where('order_id', 'in', order_ids).fetchAll().then(function(orders){
          var customer_ids = orders.pluck('order_customer_id')
          Customer.where('customer_id', 'in', customer_ids).fetchAll().then(function(customers){
            customers.forEach(function(customer){
              var order = orders.find(function(order, id){
                return order.get('order_customer_id') === customer.get('customer_id')
              })
              if (order) {
                order.set('order_email', customer.get('customer_email'))
              }
            })
            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

*/