"use strict";
var md5 = require('md5');
var uuid = require('uuid');
var mysql = require('mysql');
// var mysql = require('../connectors/mysql').client();

class EntryModel {

  /**
   * Constructor
   * @param  {object} conn db connection from db pool.
   * @param  {Object} data values for table fields.
   *                       At least on of `title` or `id` must be set.
   */
  constructor(conn, data) {
    this.data = data;
    this.conn = conn;
  }

  field(key, value) {
    if(arguments.length==0) return;
    if(arguments.length==1)
      return this.data[key];
    
    if(value===undefined)
      delete this.data[key];
    else
      this.data[key] = value;
  }

  fields(){

    return this.data;
  }

  save() {
    if(this.data.hasOwnProperty('id')) return this.update();
    else return this.add();
  }

  update(obj) {
    return new Promise((resolve, reject) => {
      if(!this.data.hasOwnProperty('id'))
        return reject('id is required.');

      obj = obj || this.data;
      this.conn.query("UPDATE entry SET ? where ?", [obj, {id: this.data.id}], (err, result) => {
        if(err) return reject(err.message);
        resolve(this);
      });
    });
  }

  add() {
    return new Promise((resolve, reject) => {
      var record = {};
      if(this.data.hasOwnProperty('title')) record.title = this.data.title;
      if(this.data.hasOwnProperty('searchable')) record.searchable = this.data.searchable;
      if(this.data.hasOwnProperty('source')) record.source = this.data.source;
      if(this.data.hasOwnProperty('order')) record.order = this.data.order;
      if(this.data.hasOwnProperty('maxOrder')) record.maxOrder = this.data.maxOrder;
      if(this.data.hasOwnProperty('langFrom')) record.langFrom = this.data.langFrom;
      if(this.data.hasOwnProperty('langTo')) record.langTo = this.data.langTo;
      if(this.data.hasOwnProperty('data')) record.data = this.data.data;
      if(this.data.hasOwnProperty('prefix')) record.prefix = this.data.prefix;
      if(this.data.hasOwnProperty('dataType')) record.dataType = this.data.dataType;
      if(this.data.hasOwnProperty('pronunciation')) record.pronunciation = this.data.pronunciation;
      if(this.data.hasOwnProperty('postfix')) record.postfix = this.data.postfix;

      this.conn.query(`INSERT INTO entry SET ?`, record, (err, result) => {
        if(err) return reject(err.message);
        this.data.id = result.insertId;
        resolve(this);
      });
    });
  }

  load() {
    return new Promise((resolve, reject) => {
      if(this.data.hasOwnProperty('id')) {
        this.conn.query(`SELECT * FROM entry WHERE ?`, {id: this.data.id}, (err, rows, fields) => {
          if (err) return reject(err.message);
          if(rows.length) {
            this.data = rows[0];
            resolve(this);
          }else
            resolve(false);
        });
      }else
        return reject('id must be provided.');
    });
  }

  remove() {
    return new Promise((resolve, reject) => {
      if(!this.data.hasOwnProperty('id')) return reject('id is required.');
      this.conn.query('DELETE FROM entry WHERE ?', {id: this.data.id}, (err, rows, fields) => {
        if(err) return reject(err.message);
        resolve(this);
      });
    });
  }

  static listDistinct(conn, query, limit, offset, orderBy) {
    if(!orderBy) orderBy = 'newest';
    var orderByStr = '';

    switch(orderBy){
      case 'a-z':
        orderByStr = 'ORDER BY `searchable` ASC';
        break;

      case 'z-a':
        orderByStr = 'ORDER BY `searchable` DESC';
        break;

      // minor compatiblity with "list" function:
      case 'a-z:book':
        orderByStr = 'ORDER BY `searchable` ASC';
        break;

      case 'z-a:book':
        orderByStr = 'ORDER BY `searchable` DESC';
        break;
    }

    limit = limit || 15;
    offset = offset || 0;

    return new Promise((resolve, reject) => {
      var params = [offset, limit];
      var where = '';

      var hack = '';
      // ------------- unkown bug! mysql thinks a is آ !!!
      if(query['%%searchable'] && query['%%searchable'].substr(0, 1)=='a')
        hack = 'and langFrom != 1 and langFrom != 4';
      if(query['%%searchable'] && query['%%searchable'].substr(0, 1)=='آ')
          hack = 'and (langFrom = 1 or langFrom = 4)';
      // ------------- end

      if(Object.keys(query).length) {
        where = 'WHERE !!';
        params.unshift(query);
      }

      var template = conn.format(`SELECT title, searchable, langFrom FROM active_entry ${where} ${hack} GROUP BY searchable ${orderByStr} limit ?, ?`, params);
      conn.query(template, query, (err, rows, fields) => {
        if (err) return reject(err.message);
        resolve(rows);
      });
    });
  }

  static countDistinct(conn, query) {
    if(query.hasOwnProperty('title')) {
      query['__lower(title)'] = mysql.escape(query.title.toLowerCase());
      delete query.title;
    }
    
    return new Promise((resolve, reject) => {
      var hack = '';
      // ------------- unkown bug! mysql thinks a is آ !!!
      if(query['%%searchable'] && query['%%searchable'].substr(0, 1)=='a')
        hack = 'and langFrom != 1 and langFrom != 4';
      if(query['%%searchable'] && query['%%searchable'].substr(0, 1)=='آ')
          hack = 'and (langFrom = 1 or langFrom = 4)';
      // ------------- end

      var where = '';
      if(Object.keys(query).length)
        where = 'WHERE !!';

      var template = conn.format(`SELECT COUNT(DISTINCT searchable) AS sum FROM active_entry ${where} ${hack}`, query);
      conn.query(template, query, (err, rows, fields) => {
        if (err) return reject(err.message);
        resolve(rows[0].sum);
      });
    });
  }

  static list(conn, query, limit, offset, orderBy, full) {
    if(full === undefined) full = false;
    if(!orderBy) orderBy = 'newest';
    var orderByStr = '';

    switch(orderBy){
      case 'newest':
        orderByStr = 'ORDER BY `updated` DESC, id DESC';
        break;

      case 'oldest':
        orderByStr = 'ORDER BY `updated` ASC, id ASC';
        break;

      case 'a-z':
        orderByStr = 'ORDER BY '+(query.hasOwnProperty('langFrom') ? '' : '`langFrom` ASC, ')+'`searchable` ASC, `order` ASC';
        break;

      case 'z-a':
        orderByStr = 'ORDER BY '+(query.hasOwnProperty('langFrom') ? '' : '`langFrom` DESC, ')+'`searchable` DESC, `order` DESC';
        break;

      case 'a-z:book':
        orderByStr = 'ORDER BY `searchable` ASC, `source_title` ASC, `order` ASC';
        break;

      case 'z-a:book':
        orderByStr = 'ORDER BY `searchable` DESC, `source_title` DESC, `order` DESC';
        break;
    }

    var limitStr = ''
    if(limit != -1){
      limit = limit || 15;
      offset = offset || 0;
      limitStr = 'limit ?, ?';
    }

    return new Promise((resolve, reject) => {
      var params = [offset, limit];
      var where = '';

      var hack = '';
      // ------------- unkown bug! mysql thinks a is آ !!!
      if((query['%%searchable'] && query['%%searchable'].substr(0, 1)=='a') || (query['searchable'] && query['searchable'].substr(0, 1)=='a'))
        hack = 'and langFrom != 1 and langFrom != 4';
      if((query['%%searchable'] && query['%%searchable'].substr(0, 1)=='آ') || (query['searchable'] && query['searchable'].substr(0, 1)=='آ'))
          hack = 'and (langFrom = 1 or langFrom = 4)';
      // ------------- end

      if(Object.keys(query).length) {
        where = 'WHERE !!';
        params.unshift(query);
      }

      var join = '';
      var fields = '*';
      if(full){
        join = 'JOIN long_text on active_entry.data = long_text.id';
        fields = 'active_entry.*, long_text.content as data'
      }

      var template = conn.format(`SELECT ${fields} FROM active_entry ${join} ${where} ${hack} ${orderByStr} ${limitStr}`, params);
      conn.query(template, query, (err, rows, fields) => {
        if (err) return reject(err.message);
        resolve(rows);
      });
    });
  }

  static count(conn, query) {
    if(query.hasOwnProperty('title')) {
      query['__lower(title)'] = mysql.escape(query.title.toLowerCase());
      delete query.title;
    }

    return new Promise((resolve, reject) => {
      var hack = '';
      // ------------- unkown bug! mysql thinks a is آ !!!
      if(query['%%searchable'] && query['%%searchable'].substr(0, 1)=='a')
        hack = 'and langFrom != 1 and langFrom != 4';
      // ------------- end

      var where = '';
      if(Object.keys(query).length)
        where = 'WHERE !!';

      var template = conn.format(`SELECT COUNT(*) AS sum FROM active_entry ${where} ${hack}`, query);
      conn.query(template, query, (err, rows, fields) => {
        if (err) return reject(err.message);
        resolve(rows[0].sum);
      });
    });
  }

  setMaxOrder() {
    return new Promise((resolve, reject) => {
      this.conn.query("SELECT * FROM entry WHERE !!", {
        source: this.data.source,
        langFrom: this.data.langFrom,
        langTo: this.data.langTo,
        '__lower(title)': mysql.escape(this.data.title.toLowerCase())
      }, (err, rows, fields) => {
        if(err) return reject(err.message);

        if(!rows.length) return resolve();

        var ids = [];
        rows.forEach((row) => {
          ids.push(row.id);
        });
        this.conn.query("UPDATE entry SET maxOrder="+ids.length+" WHERE id IN ("+ids.join(',')+")", (err) => {
          if(err) return reject(err.message);
          resolve();
        });
      })
    });
  }

  getNeighbours(orderBy) {
    if(!orderBy) orderBy = 'newest';
    var query = 'SELECT * FROM active_entry WHERE ';
    var nextQ = '';
    var prevQ = '';
    var orderByStr = '';
    switch(orderBy){
      case 'newest':
        break;

      case 'oldest':
        break;

      case 'a-z':
        nextQ = this.conn.format (query + 'source=? and langFrom=? and langTo=? and ((title=? AND `order`>?) OR searchable>?) ORDER BY searchable ASC, `order` ASC limit 1',
            [
              this.data.source,
              this.data.langFrom,
              this.data.langTo,
              this.data.title,
              this.data.order,
              this.data.searchable,
            ]);
        prevQ = this.conn.format (query + 'source=? and langFrom=? and langTo=? and ((title=? AND `order`<?) OR searchable<?) ORDER BY searchable DESC, `order` DESC limit 1',
            [
              this.data.source,
              this.data.langFrom,
              this.data.langTo,
              this.data.title,
              this.data.order,
              this.data.searchable,
            ]);
        break;

      case 'z-a':
        nextQ = this.conn.format (query + 'source=? and langFrom=? and langTo=? and ((title=? AND `order`<?) OR searchable<?) ORDER BY searchable DESC, `order` DESC limit 1',
            [
              this.data.source,
              this.data.langFrom,
              this.data.langTo,
              this.data.title,
              this.data.order,
              this.data.searchable,
            ]);
        prevQ = this.conn.format (query + 'source=? and langFrom=? and langTo=? and ((title=? AND `order`>?) OR searchable>?) ORDER BY searchable ASC, `order` ASC limit 1',
            [
              this.data.source,
              this.data.langFrom,
              this.data.langTo,
              this.data.title,
              this.data.order,
              this.data.searchable,
            ]);
        break;

      default:
        nextQ = false;
        prevQ = false;
    }

    return new Promise((resolve, reject) => {
      this.conn.query(nextQ, (err, rows, fields) => {
        if(err) reject(err.message);
        if(rows && rows.length)
          this.data.next = rows[0];
        else
          this.data.next = false;

        this.conn.query(prevQ, (err, rows, fields) => {
          if(err) reject(err.message);
          if(rows && rows.length)
            this.data.prev = rows[0];
          else
            this.data.prev = false;

          resolve(this);
        })
      });
    });
  }

  visit() {
    return new Promise((resolve, reject) => {
      if(!this.data.hasOwnProperty('id'))
        return reject('id is required.');

      this.conn.query("UPDATE entry SET updated=CURRENT_TIMESTAMP where ?", [{id: this.data.id}], (err, result) => {
        if(err) return reject(err.message);
        resolve(this);
      });
    });
  }

  static reorder(conn, id, order, maxOrder) {
    return new Promise((resolve, reject) => {
      conn.query("UPDATE entry SET `order`=?, `maxOrder`=? WHERE `id`=?", [order, maxOrder, id], err => {
        if(err) return reject(err.message);
        resolve();
      });
    });
  }
}

module.exports = EntryModel;