var csv = require('csv')
var knex = require('knex')({
    client: 'mysql',
    connection: {
        host: '127.0.0.1',
        user: 'theo',
        password: null,
        database: 'co_base',
        charset: 'utf8'
    }
});

function importCSV(csv_filepath, table_name, column_arr){
    knex.schema.createTableIfNotExists(table_name, function(table) {
        table.increments('id').primary().notNullable();
        for (column in column_arr){ //create table columns in db
            // console.log(column_arr[column].name);
            switch(column_arr[column].type) {
                case 'VARCHAR':
                    table.string(column_arr[column].name, column_arr[column].size);
                    break;
                case 'INT':
                    table.integer(column_arr[column].name, column_arr[column].size);
                    break;
                case 'DATE':
                    table.date(column_arr[column].name, column_arr[column].size);
                    break;
                case 'TIMESTAMP':
                    table.timestamp(column_arr[column].name, column_arr[column].size);
                    break;
            }
        }
    }).then (function () {
        //insert into db
        var fs = require('fs');
        var parse = require('csv-parse');
        var parser = parse({delimiter: ';'}, function(err, data){
            console.log(data);
            var counter = 0;
            for (row in data){
                console.log(row);

                if(row == 0){
                    counter++;
                    continue;
                }
                var rowToInsert = {};
                for (column in data[row]){
                    try {
                        var key = column_arr[column].name;
                    } catch (error) {
                        counter++;
                        continue;
                    }
                    console.log(key);
                    var elem = data[row][column];
                    if(column_arr[column].type === "INT"){
                        elem = elem.replace(/,/g, '');
                        elem = parseInt(elem);
                    }
                    rowToInsert[key] = elem;
                }
                // console.log(rowToInsert);
                try {
                    knex(table_name).insert(rowToInsert).then(function() {
                        console.log("a mers");
                        counter++;
                        console.log(counter);
                        if (counter == data.length) {
                            process.exit(0);
                        }
                    });
                } catch (error) {
                   console.log(error);
                }

            }
        })
        fs.createReadStream(csv_filepath).pipe(parser);
    }).catch(function(e) {
        console.log(e)
    });
}

var column_arr = [
    {
        "name" : "Magazin",
        "type" : "VARCHAR",
        "size" : 255,
    },
    {
        "name" : "Cod Departament",
        "type" : "INT",
        "size" : null,
    },
    {
        "name" : "Departament",
        "type" : "VARCHAR",
        "size" : 255,
    },
    {
        "name" : "Cod Raion",
        "type" : "VARCHAR",
        "size" : 255,
    },
    {
        "name" : "Raion",
        "type" : "VARCHAR",
        "size" : null,
    },
    {
        "name" : "Cod familie",
        "type" : "INT",
        "size" : null,
    },
    {
        "name" : "Familie",
        "type" : "VARCHAR",
        "size" : null,
    },
    {
        "name" : "Cod subfamilie",
        "type" : "INT",
        "size" : null,
    },
    {
        "name" : "Subfamilie",
        "type" : "VARCHAR",
        "size" : null,
    },
    {
        "name" : "Clasa de rotatie",
        "type" : "VARCHAR",
        "size" : null,
    },
    {
        "name" : "Cod Articol",
        "type" : "INT",
        "size" : null,
    },
    {
        "name" : "Denumire",
        "type" : "VARCHAR",
        "size" : null,
    }
]
importCSV('testcsv.csv', "comandabile", column_arr);