const mysql = require('mysql');
const json2csv = require('json2csv');
const fs = require('fs');
const await = require('asyncawait/await');
const async = require('asyncawait/async');

var knex = require('knex')({
    client: 'mysql',
    connection: {
        host: '*',
        user: 'root',
        password:  '*',
        database:  '*'
    }
});
var getTables = async(function () {
    console.log('getTables');
    let tables = [];
    const res = await (knex.raw('show tables;'));
    res[0].map((c) => {
        tables.push(c['Tables_in_shelfmintdev2']);
    })
    return tables;
});
var getFields = async(function (tables) {
    console.log('getFields');
    let tablesWithFields = [];
    tables.map((t, i) => {
        const res = await (knex.raw(`SHOW COLUMNS FROM \`${t}\``));
        tablesWithFields.push({
            tableName: t,
            fields: res[0] //Field:
        })

    })
    return tablesWithFields;
});

var getSuspiciousFields = async(function (tablesWithFields) {
    console.log('getSuspiciousFields');
    let tableWithSuspicious = [];

    tablesWithFields.map((t, i) => {
        try {
            let w = t.fields.map((f, j) => {
                return f.Field + ' IS NULL OR ';
            });
            w[w.length - 1] = w[w.length - 1].replace('OR ', '');
            w = w.join('');
            const q = `select * from \`${t.tableName}\` where ${w} order by Id desc limit 200`;
            console.log('q', q);
            const res = await (knex.raw(q));

            if (res[0].length == 0) {
                //check for last insert
            } else {
                 tableWithSuspicious.push({
                            tableName:t.tableName,
                            query:q,
                            reason:"null"
                        })
                //find nulls fields
                // for (let k = 0; k < res[0].length; k++) {
                //     for (let key in res[0][k]) {
                //         if (res[0][k][key] == null) {
                //              tableWithSuspicious.push({
                //             tableName:t.tableName,
                //             field:key,
                //             reason:"null"
                //         })
                //         }

                //     }
                // }
            }

        } catch (e) {
            console.log(e)
        }

    })
    return tableWithSuspicious;
});

function createCsv(name, fields, data) {
    console.log('createCsv');
    var csv = json2csv({
        data: data,
        fields: fields
    });
    fs.writeFile(name + '.csv', csv, function (err) {
        if (err)
            throw err;
        console.log('csv saved');
    });
    fs
        .writeFile(name + '.json', JSON.stringify(data), function (err) {
            if (err)
                throw err;
            console.log('json saved');
        });
}

var main = async(function () {
    console.log('main');
    let tables = await (getTables());
    let tablesWithFields = await (getFields(tables));
    let tableWithSuspicious = await (getSuspiciousFields(tablesWithFields));
createCsv('tableWithSuspicious', [
        'tableName', 'query', 'reason'
    ], tableWithSuspicious);
    return ;
});

main();