/**
 * Dictionary Model
 * Version: 0.1
 * Author: Babak Vandad
 *
 * Abstraction layer for Dictionary table in MySQL db
 */

"use strict";
var md5 = require('md5');
var uuid = require('uuid');
var consts = require('../consts')

class DictionaryModel {

	/**
	 * 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;
	}

	/**
	 * fetch/set fields of the table
	 * if `value` argument is passed the field is set, otherwise fetched
	 * @param  {string} key   field name
	 * @param  {mixed} value value of the field
	 * @return {mixed}       returns the value
	 */
	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;
	}

	/**
	 * fetch all fields
	 * @return {object} row data
	 */
	fields(){

		return this.data;
	}

	/**
	 * if id is set updated row otherwise creates a new record
	 * @return {Promise} promise object
	 */
	save() {
		if(this.data.hasOwnProperty('id')) return this.update();
		else return this.add();
	}

	/**
	 * updates the row data
	 * @return {promise} promise object
	 */
	update() {
		var self = this;
		return new Promise(function(resolve, reject){
			if(!self.data.hasOwnProperty('id'))
				return reject('id is required.');

			self.conn.query("UPDATE source SET ? where ?", [self.data, {id: self.data.id}], function(err, result){
				if(err) return reject('db error.');
				resolve(self);
			});
		});
	}

	/**
	 * creates a new record
	 * @return {promise} promise object
	 */
	add() {
		var self = this;
		return new Promise(function(resolve, reject){
			var record = {};
			if(self.data.hasOwnProperty('title')) record.title = self.data.title;
			if(self.data.hasOwnProperty('author')) record.author = self.data.author;
			if(self.data.hasOwnProperty('pubYear')) record.pubYear = self.data.pubYear;
			if(self.data.hasOwnProperty('cover')) record.cover = self.data.cover;
			if(self.data.hasOwnProperty('state')) record.state = self.data.state;
			if(self.data.hasOwnProperty('desc')) record.desc = self.data.desc;
			record.uuid = uuid();

			self.conn.query(`INSERT INTO source SET ?`, record, function(err, result){
				if(err) return reject('db error.');
				self.data.id = result.insertId;
				self.data.uuid = record.uuid;
				resolve(self);
			})
		});
	}

	/**
	 * loads row data corresponding to id or uuid
	 * @return {Promise} promise object
	 */
	load() {
		var self = this;
		return new Promise(function(resolve, reject){
			if(self.data.hasOwnProperty('id')) {
				self.conn.query(`SELECT * FROM source WHERE ?`, {id: self.data.id}, function(err, rows, fields){
					if (err) return reject(err.message);
					if(rows.length) {
						self.data = rows[0];
						resolve(self);
					}else
						resolve(false);
				});
			}else
			if(self.data.hasOwnProperty('uuid')) {
				self.conn.query(`SELECT * FROM source WHERE ?`, {uuid: self.data.uuid}, function(err, rows, fields){
					if (err) return reject(err.message);
					if(rows.length) {
						self.data = rows[0];
						resolve(self);
					}else
						resolve(false);
				});
			}else
				return reject('At least on of the id and title fields must be provided.');
		});
	}

	/**
	 * deletes a record
	 * @return {promise} promise object
	 */
	remove() {
		var self = this;
		return new Promise(function(resolve, reject){
			if(!self.data.hasOwnProperty('id')) return reject('id is required.');
			self.conn.query('DELETE FROM `source` WHERE ?', {id: self.data.id}, function(err, rows, fields){
				if(err) return reject(err.message);
				resolve(self);
			});
		});
	}

	/**
	 * list all records
	 * @param  {object} conn  connection object to mysql
	 * @return {promise}      promise object
	 */
	static list(conn) {
		return new Promise(function(resolve, reject){
			conn.query(`SELECT
							source.uuid, source.title, source.author, source.pubYear, source.cover, source.state, source.entryCount,
							GROUP_CONCAT(CONCAT(source_cat.id, '|', source_cat.title)) AS categories
						FROM source
						LEFT JOIN source_cat_rel on source_cat_rel.source = source.id
						LEFT JOIN source_cat on source_cat_rel.cat=source_cat.id
						GROUP BY source.id`, function(err, rows, fields){
				if (err) return reject(err.message);
				resolve(rows);
			});
		});
	}

	/**
	 * list all active records (with state set to STATE_ACTIVE const)
	 * @param  {object} conn  connection object to mysql
	 * @return {promise}      promise object
	 */
	static listActives(conn) {
		return new Promise(function(resolve, reject){
			conn.query(`SELECT
							\`source\`.uuid, \`source\`.title, \`source\`.author, \`source\`.pubYear, \`source\`.cover, \`source\`.state, \`source\`.entryCount,
							GROUP_CONCAT(CONCAT(source_cat.id, '|', source_cat.title)) AS categories,
							GROUP_CONCAT(CONCAT(source_lang.lang_from, ':', source_lang.lang_to)) AS langs
						FROM source
						LEFT JOIN source_cat_rel on source_cat_rel.source = \`source\`.id
						LEFT JOIN source_cat on source_cat_rel.cat=source_cat.id
						LEFT JOIN source_lang on source_lang.\`source\`=\`source\`.id
						WHERE \`source\`.state=${consts.v.STATE_ACTIVE}
						GROUP BY \`source\`.id`, function(err, rows, fields){
				if (err) return reject(err.message);
				resolve(rows);
			});
		});
	}

	/**
	 * add a record to source_lang table.
	 * @param  {array} langs  array of lang objects like: {from: 1, to: 2}
	 * @return {promise}      promise object
	 */
	addLangs(langs) {
		var self = this;
		return new Promise(function(resolve, reject){
			if(!self.data.id) reject('id is required.');
			self.removeLangs().then(
				function(self){
					if(!langs.length) resolve(langs);
					var data = [];
					for(let i in langs)
						data.push([self.data.id, langs[i].from, langs[i].to]);
					self.conn.query('INSERT INTO source_lang (source, lang_from, lang_to) VALUES ?', [data], function(err, result){
						if(err) return reject('db error.');
						resolve(langs);
					});
				}, function(err) {
					reject(err);
				}
			);
		});
	}

	removeLangs() {
		var self = this;
		return new Promise(function(resolve, reject){
			if(!self.data.id) reject('id is required.');
			self.conn.query(`DELETE FROM source_lang WHERE ?`, {source: self.data.id}, function(err, result){
				if(err) return reject('db error.');
				resolve(self);
			});
		});
	}

	getLangs() {
		var self = this;
		return new Promise(function(resolve, reject){
			if(!self.data.id) reject('id is required.');
			self.conn.query(
				`SELECT * FROM source_lang
				 WHERE ?`,
				{source: self.data.id}, function(err, rows, fields){
					if(err) return reject('db error.');
					resolve(rows);
			});
		});
	}

	relateCategories(categories) {
		var self = this;
		return new Promise(function(resolve, reject){
			if(!self.data.id) reject('id is required.');
			self.unrelateAllCategories().then(
				function(self){
					if(!categories.length) resolve(categories);
					var data = [];
					for(let i in categories)
						data.push([self.data.id, categories[i]]);
					self.conn.query('INSERT INTO source_cat_rel (source, cat) VALUES ?', [data], function(err, result){
						if(err) return reject('db error.');
						resolve(categories);
					});
				}, function(err) {
					reject(err);
				}
			);
		});
	}

	unrelateAllCategories() {
		var self = this;
		return new Promise(function(resolve, reject){
			if(!self.data.id) reject('id is required.');
			self.conn.query(`DELETE FROM source_cat_rel WHERE ?`, {source: self.data.id}, function(err, result){
				if(err) return reject('db error.');
				resolve(self);
			});
		});
	}

	getRelatedCategories() {
		var self = this;
		return new Promise(function(resolve, reject){
			if(!self.data.id) reject('id is required.');
			self.conn.query(
				`SELECT source_cat.* FROM source_cat_rel
				 join source_cat on source_cat_rel.cat=source_cat.id
				 where ?`,
				{source: self.data.id}, function(err, rows, fields){
					if(err) return reject('db error.');
					resolve(rows);
			});

		});
	}

	updateEntryCount(){
		var self = this;
		return new Promise(function(resolve, reject){
			if(!self.data.id) reject('id is required.');
			self.conn.query('SELECT COUNT(`source`) AS sum FROM entry WHERE ?', {source: self.data.id}, function(err, rows, fields){
				if(err) return reject('db error.');
				var sum = rows[0].sum;
				self.conn.query('UPDATE source SET ? WHERE ?', [{entryCount: sum}, {id: self.data.id}], function(err){
					if(err) return reject('db error.');
					self.data.entryCount = sum;
					resolve(self);
				});
			});
		});
	}
}

module.exports = DictionaryModel;