'use strict'

const _ = require('lodash');
var fs = require('fs');
var mysql = require('mysql');
const mysql_host = 'iobot-prod.chdznu6kneuw.eu-west-1.rds.amazonaws.com';
const mysql_user = 'v2_sql_read_prod';
const mysql_database = 'lvc_v2';
const mysql_password = 'CqvjZ342Qch285XZ';
var update = [];

var connection = mysql.createConnection({
    host            : mysql_host,
    user            : mysql_user,
    password        : mysql_password,
    database        : mysql_database
});

const isValid = (str) => {
    var filter = ['of', 'from', 'where', 'with', 'when', 'the', 'png', 'jpg'];
    if (str.length < 2)
        return false;
    else if (filter.indexOf(str) > -1)
        return false;
    else
        return true;
}

// Split the str into an array, case insensitive and remove the duplicates
function str_to_array(str) {
    var temp_array = [];
    console.log(str);
    if (!str)
        return undefined;
    temp_array = str.split(/[\s'(),./|-]+/);
    temp_array = temp_array.map(function(element) {
            var tmp = element.toLowerCase();
            return tmp
    });
    temp_array = temp_array.filter(function(item, pos, self) {
        return self.indexOf(item) == pos && isValid(item);
    });
    return temp_array;
}

function subset(arra, array_size) {
    var result_set = [],
        result,
        i = 0;
    for(var x = 0; x < Math.pow(2, arra.length); x++) {
        result = [];
        i = arra.length - 1;
        do {
            if( (x & (1 << i)) !== 0)
                result.push(arra[i]);
        } while(i--);

        if( result.length >= array_size)
            result_set.push(result);
    }
    return result_set;
}

function generate_where(element, combo) {
    var param = "";

    combo.forEach(function(tmp){
        if (tmp == combo[0])
            param = param.concat("(");
        else
            param = param.concat("OR (");
        tmp.forEach(function(e){
            if (e == tmp[0])
                param = param.concat(element, " LIKE '%", e, "%'");
            else
                param = param.concat(" AND ", element, " LIKE '%", e, "%'");
        });
        param = param.concat(")");
    });
    return param;
}

const generate_sql = (schema, notion, element, array_str, size) => {
    var combo = [];
    var sql = "SELECT * FROM ";
    var param = "";
    var i = 1;

    combo = subset(array_str, size);
    param = generate_where(element, combo);
    sql = sql.concat(notion, " WHERE ", param);

    return sql;
}

let magic_sql = (schema, notion, element, array_str, array_size, str) => {
    console.log("MAGIC");
    if (array_size < 1)
        return
    var query = generate_sql(schema, notion, element, array_str, array_size);
    connection.query(query, function (err, result, fields) {
        if (err) throw err;
        if(result.length && !result.EPIC) {
            str = str.replace(/ /g, '%20');
            str = `https://storage.googleapis.com/store-iobot/bel_bot/${str}`;
            result.forEach(function(element){
                var update = `UPDATE bel.element SET EPIC = "${str}" WHERE EID = ${element.EID}`;
                console.log(update);
                connection.query(update, function (err, result, fields) {
                    if (err) throw err;
                });
            });
        }
        else
            magic_sql(schema, notion, element, array_str, array_size - 1, str)
    });
}

let get_best_result =  (schema, notion, element, str) => {
    if (!schema || !notion || !element || !str) {
        console.log("ERROR: Missing parameters !");
        return undefined;
    }
    var array_str = str_to_array(str);
    console.log(array_str);
    var array_size = array_str.length;
    var result = [];
    magic_sql(schema, notion, element, array_str, array_size, str);
}

fs.readFile('bel_images_copy.txt', 'utf8', function(err, data) {
    if (err) throw err;
    connection.connect(function(err) {
        if (err) throw err;
        data = data.split(/\r?\n/);
        console.log(data);
        data.forEach(function(element){
            // console.log(element);
            get_best_result('bel', 'bel.element', 'ENAME', element);
        });
    });
});

// var str = "Leerdammer Spécial Hamburger.png";
// var tmp = str_to_array(str);
// console.log(generate_sql('bel', 'bel.element', 'ENAME', tmp, tmp.length));