var mysql       = require('mysql2');
var libMatchAll = require('match-all');

fcf.module({
  name: "fcf:NFSQL/NDetails/NConnections/MySQLConnection.js",
  dependencies: ["fcf:NFSQL/NDetails/NConnections/MySQLBuilder.js", "fcf:NFSQL/NFilter/Filter.js"],
  module: function(MySQLBuilder, Filter) {
    var NConnections = fcf.prepareObject(fcf, "NFSQL.NDetails.NConnections");

    NConnections.MySQLConnection = function(a_options) {
      var self = this;
      let NFilter = fcf.NFSQL.NFilter;

      this._type = a_options.type;
      this._connectionInfo = {
        host:     a_options.host,
        database: a_options.db,
        user:     a_options.user,
        password: a_options.pass,
        timezone: '+00:00'
      };
      this._connection = undefined;
      this._builder = new MySQLBuilder();
      this._isFirstConnection = true;

      this.getType = function(){
        return this._type;
      }

      this.query = function(a_queryObject, a_options, a_cb) {
        try {
          if (!this._connection)
            this.connect();

          var type = a_queryObject.type;
          var txtQuery = this._builder.build(a_queryObject);
          for (var i = 0; i < txtQuery.args.length; ++i) {
            if (typeof txtQuery.args[i] !== "object" || txtQuery.args[i] === null)
              continue;
            if (!("result" in txtQuery.args[i]))
              continue;
            var itm = txtQuery.args[i];
            var value = null;
            var resNum = itm.result;
            if (resNum < 0)
              resNum = a_options.results.length + resNum;

            if (itm &&
                a_options.results &&
                a_options.results[resNum] &&
                a_options.results[resNum][itm.record] &&
                a_options.results[resNum][itm.record][itm.item]
              ){
                txtQuery.args[i] = a_options.results[resNum][itm.record][itm.item];
            } else {
              txtQuery.args[i] = null;
            }
          }

          if (a_options.fullLog || a_queryObject.fullLog)
            fcf.log.write(a_options.fullLog, "FCF", "MySQL query: '", txtQuery.query, "'\nArguments: ", txtQuery.args);

          let context = fcf.getContext();
          this._connection.query(txtQuery.query, txtQuery.args, function (error, results, fields) {
            fcf.setContext(context);
            if (error) {
              if (error.message == "Can't add new command when connection is in closed state")
                self.disconnect();

              if (a_cb){
                if (fcf.find(a_options.hideErrors, error.code) === undefined)
                  fcf.log.err("FCF", "ERROR QUERY:", txtQuery.query, "\n", error.message);
                a_cb(error);
              }
              return;
            }
            if (a_cb){
              let result = type == "select" ? results :
                           type == "insert" ? [a_options.insertKey ? {"@key": a_options.insertKey} : {"@key": results.insertId}] :
                                              [results];
              a_cb(undefined, result);
            }
          });

        } catch(error) {
          if (a_cb)
            a_cb(error);
          return;
        }
      }

      this.rawQuery = function(a_query, a_args, a_cb) {
        if (!this._connection)
          this.connect();

        a_args = Array.isArray(a_args) ? a_args : [];
        // if (a_query.toUpperCase().indexOf("SHOW") !== 0)
        //   fcf.log.log("FSQL:RAW", a_query + " Args: " + JSON.stringify(a_args));

        let context = fcf.getContext();
        this._connection.query(a_query, a_args, function (error, results, fields) {
          fcf.setContext(context);
          if (error) {
            if (error.message == "Can't add new command when connection is in closed state")
              self.disconnect();

            a_cb(error);
            return;
          }
          a_cb(undefined, results);
        });
      }

      this.connect = function() {
        if (this._connection)
          this.disconnect();

        if (!this._isFirstConnection)
          fcf.log.log("FSQL", "Reconnecting to the database \"" + self._connectionInfo.database + "\"");
        this._isFirstConnection = false;

        this._connection = mysql.createConnection(this._connectionInfo);
        this._connection.connect();

        let context = fcf.getContext();
        this._connection.on('error', function(err) {
          fcf.setContext(context);
          if (!err.fatal)
            return;
          self.disconnect();
        });
      }

      this.disconnect = function(){
        if (!this._connection)
          return;
        try {
          this._connection.end();
        } catch(e){ }
        this._connection = undefined;
      }

      this.destroy = function() {
        this.disconnect();
      }
      this.checkTable = function(a_tableName){
        let self = this;
        return fcf.actions()
        .then((a_res, a_act)=>{
          self.rawQuery("SHOW TABLES", undefined, (a_error, a_result) => {
            if (a_error){
              a_act.error(a_error);
              return;
            }
            var foundKey = fcf.find(a_result, (k, v) => { return fcf.first(v) ==  a_tableName});
            a_act.complete(foundKey !== undefined);
          });
        })
      }

      this.createTable = function(a_tableName){
        let self = this;
        return fcf.actions()
        .then((a_res, a_act)=>{
          self.rawQuery("CREATE TABLE `" + a_tableName + "` (stub_29548 varchar(1))", undefined, (a_error, a_result) => {
            if (a_error) {
              a_act.error(a_error);
              return;
            }
            fcf.log.log("FCF", "Create table ", a_tableName);
            a_act.complete();
          });

        });
      }

      this.getTableFields = function(a_tableName){
        let self = this;
        let result = [];
        return fcf.actions()
        .then((a_res, a_act)=>{
          this.rawQuery("SHOW COLUMNS FROM `" + a_tableName + "`", undefined, (a_error, a_result) => {
            if (a_error) {
              a_act.error(a_error);
              return;
            }
            fcf.each(a_result, (a_key, a_tableField) => {
              if (a_tableField.Field == "stub_29548")
                return;
              let field = {};
              field.field         = a_tableField.Field
              field.notNull       = a_tableField.Null.toLowerCase() == "no";
              field.notEmpty      = a_tableField.Null.toLowerCase() == "no";
              field.autoIncrement = a_tableField.Extra.toLowerCase().indexOf("auto_increment") != -1;
              field.unique        = a_tableField.Extra.toLowerCase().indexOf("unique") != -1 || a_tableField.Key.indexOf("UNI") != -1;
              field.type          = a_tableField.Type.split("(")[0];
              field.primary       = a_tableField.Key.toLowerCase().indexOf("pri") != -1;
              field.defaultDB     = a_tableField.Default;

              if (field.type.indexOf("varchar") == 0){
                field.maxSize = 255;
                let res = field.type.match(/\((.*)\)/)
                if (res !== null)
                  field.maxSize = res[1];
                field.type = "string";
              }
              else if (field.type.indexOf("text") == 0){
                let res = field.type.match(/\((.*)\)/)
                if (res !== null)
                  field.maxSize = res[1];
                field.type = "text";
              }
              else if (field.type.indexOf("int") == 0){
                field.type = "int";
              }
              result.push(field);
            });
            a_act.complete(result);
          });
        });
      }

      this.updateUnique = function(a_projection){
        let self = this;
        let insertUnique = [];
        return fcf.actions()
        .then((a_res, a_act)=> {
          if (fcf.empty(a_projection.unique)) {
            a_act.complete();
            return;
          }

          self.rawQuery("SHOW CREATE TABLE `" + a_projection.table + "`", undefined, (a_error, a_result)=>{
            if (a_error) {
              a_act.error(a_error);
              return;
            }
            let createTableInfo = a_result[0]["Create Table"].toString();
            let unique = [];
            let match = libMatchAll(createTableInfo, /UNIQUE[ ]*KEY[ ]*[`a-z0-9_]*[ ]*\(([^\)]*)/ig).toArray();
            for(let i = 0; i < match.length; ++i){
              unique.push([]);
              let matchFileds = libMatchAll(match[i], /`([^`]*)`/ig).toArray();
              for(let j = 0; j < matchFileds.length; ++j)
                unique[unique.length-1].push(matchFileds[j]);
            }

            fcf.each(a_projection.unique, (a_key, a_projUniqueFields) => {
              if (fcf.empty(a_projUniqueFields))
                return;
              let found = !fcf.empty(unique);
              fcf.each(unique, (a_key, a_tableUniqueFields) => {
                if (fcf.empty(a_tableUniqueFields)) {
                  found = false;
                  return;
                }
                fcf.each(a_projUniqueFields, (a_key, a_filedAlias)=>{
                  let projTableField = a_projection.fieldsMap[a_filedAlias].field;
                  if (fcf.find(a_tableUniqueFields, (a_key, a_field)=>{ return projTableField == a_field}) === undefined){
                    found = false;
                    return false;
                  }
                })
              });

              if (!found){
                insertUnique.push(a_projUniqueFields);
              }
            })
            a_act.complete();
          })
        })
        .each(()=>{ return insertUnique; }, (a_key, a_uniqueFields, a_res, a_act) => {
          let query = "ALTER TABLE  `" + a_projection.table + "` ADD UNIQUE (";
          for(let i = 0; i < a_uniqueFields.length; ++i){
            if (i != 0)
              query += ",";
            query += "`" + a_uniqueFields[i] + "`";
          }
          query += ")";
          self.rawQuery(query, undefined, (a_error, a_result) => {
            if (a_error){
              a_act.error(a_error);
              return;
            }
            a_act.complete();
          });
        })

      }

      this.createField = function(a_projection, a_realFieldInfo){
        let self = this;
        let dbtype = a_realFieldInfo.type == "int"      ? "int" :
                     a_realFieldInfo.type == "bigint"   ? "bigint" :
                     a_realFieldInfo.type == "float"    ? "float" :
                     a_realFieldInfo.type == "datetime" ? "datetime" :
                     a_realFieldInfo.type == "boolean"  ? "char(1)" :
                     a_realFieldInfo.type == "string"   ? "varchar(" + (a_realFieldInfo.maxSize ? a_realFieldInfo.maxSize : 255)+ ")" :
                     a_realFieldInfo.type == "text"     ? "text" :
                                                          "varchar(255)";

        let primary = a_projection.key == a_realFieldInfo.alias;
        let args = [];
        let query = "ALTER TABLE `" + a_projection.table  + "` ADD COLUMN `" + a_realFieldInfo.field + "` " + dbtype;

        if (a_realFieldInfo.notNull || a_realFieldInfo.notEmpty || primary)
          query += " NOT NULL ";

        if ("defaultDB" in a_realFieldInfo){
          query += " DEFAULT ? ";
          args.push(a_realFieldInfo.defaultDB);
        }

        if (a_realFieldInfo.autoIncrement && primary)
          query += " AUTO_INCREMENT ";

        if (primary)
          query += " PRIMARY KEY ";

        if (a_realFieldInfo.unique || primary)
          query += " UNIQUE ";

        let context = fcf.getContext();
        return fcf.actions()
        //remove old primary key
        .then(()=>{
          if (!primary)
            return;
          let currentFields = undefined;
          
          return self.getTableFields(a_projection.table)
          .then((a_currentFields)=>{
            currentFields = a_currentFields;
          })
          .each(()=>{return currentFields}, (a_key, a_currentField)=>{
            if (!a_currentField.primary)
              return;
            let projField = a_projection.fields[fcf.find(a_projection.fields, (k,v)=>{ return v.field == a_currentField.field})];
            if (projField.alias == a_projection.key)
              return;
            a_currentField.primary = false;
            a_currentField.autoIncrement = false;
            return self.modifyField(a_projection, a_currentField, false, true)
            .then((a_res, a_act)=>{
              rmpkQuery = "ALTER TABLE `" + a_projection.table + "` DROP PRIMARY KEY";
              self.rawQuery(rmpkQuery, undefined, (a_error, a_result) => {
                fcf.setContext(context);
                a_act.complete();
              });
            })
          })
        })
        // append new column
        .then((a_res, a_act)=>{
            self.rawQuery(query, args, (a_error, a_result) => {
              fcf.setContext(context);
              if (a_error) {
                a_act.error(a_error);
                return;
              }
              fcf.log.log("", "Create field ", a_realFieldInfo.field, " from table ", a_projection.table);
              a_act.complete();
            });
        })
      }

      this.modifyField = function(a_projection, a_realFieldInfo, a_silentMode, a_reqursion){
        let self = this;
        let dbtype = a_realFieldInfo.type == "int"      ? "int" :
                     a_realFieldInfo.type == "bigint"   ? "bigint" :
                     a_realFieldInfo.type == "float"    ? "float" :
                     a_realFieldInfo.type == "datetime" ? "datetime" :
                     a_realFieldInfo.type == "boolean"  ? "char(1)" :
                     a_realFieldInfo.type == "string"   ? "varchar(" + (a_realFieldInfo.maxSize ? a_realFieldInfo.maxSize : 255)+ ")" :
                     a_realFieldInfo.type == "text"     ? "text" :
                                                          "varchar(255)"; 

        let primary = a_projection.key == a_realFieldInfo.alias;
        let args  = [];
        let query = "ALTER TABLE `" + a_projection.table  + "` MODIFY COLUMN `" + a_realFieldInfo.field + "` " + dbtype;

        if (a_realFieldInfo.notNull || a_realFieldInfo.notEmpty)
          query += " NOT NULL ";

        if ("defaultDB" in a_realFieldInfo){
          query += " DEFAULT ? ";
          args.push(a_realFieldInfo.defaultDB);
        }

        if (a_realFieldInfo.autoIncrement)
          query += " AUTO_INCREMENT ";

        if (primary)
          query += " PRIMARY KEY ";

        if (a_realFieldInfo.unique)
          query += " UNIQUE ";

        let context = fcf.getContext();
        return fcf.actions()
        .then((a_res, a_act)=>{

          self.rawQuery("SHOW INDEX FROM `" + a_projection.table + "`", undefined, (a_error, a_indexes)=>{
            if (a_error){
              a_act.error(a_error);
              return;
            }
            fcf.actions()
            .each(a_indexes, (a_key, a_record, a_res, a_subact)=>{
              if (a_record.Table != a_projection.table || a_record.Column_name != a_realFieldInfo.field) {
                a_subact.complete();
                return;
              }
              self.rawQuery("DROP INDEX `" + a_record.Key_name + "` ON `" + a_projection.table + "`", undefined, ()=>{
                a_subact.complete();
              });
            })
            .finally(()=>{
              a_act.complete();
            })
          })
        })
        //remove old primary key
        .then(()=>{
          if (!primary)
            return;
          let currentFields = undefined;
          
          return self.getTableFields(a_projection.table)
          .then((a_currentFields)=>{
            currentFields = a_currentFields;
          })
          .each(()=>{return currentFields}, (a_key, a_currentField)=>{
            if (!a_currentField.primary)
              return;
            let projField = a_projection.fields[fcf.find(a_projection.fields, (k,v)=>{ return v.field == a_currentField.field})];
            if (projField.alias == a_projection.key)
              return;
            a_currentField.primary = false;
            a_currentField.autoIncrement = false;
            return self.modifyField(a_projection, a_currentField, false, true)
            .then((a_res, a_act)=>{
              rmpkQuery = "ALTER TABLE `" + a_projection.table + "` DROP PRIMARY KEY";
              self.rawQuery(rmpkQuery, undefined, (a_error, a_result) => {
                fcf.setContext(context);
                a_act.complete();
              });
            })
          })
        })        
        .then((a_res, a_act)=>{
            self.rawQuery(query, args, (a_error, a_result) => {
              fcf.setContext(context);
              if (a_error) {
                a_act.error(a_error);
                return;
              }
              if (!a_silentMode)
                fcf.log.log("", "Modify field ", a_realFieldInfo.field, " from table ", a_projection.table);
              a_act.complete();
            });
        })
      }



    }

    return NConnections.MySQLConnection;
  }
});
