var mysql = require('mysql');

var connection = mysql.createConnection({
	host: 'localhost',
	user: '',
	password: '',
	database: ''
});

var datas = [
	{"食品番号":"02001","索引番号":162,"食品名":"＜いも類＞きくいも　塊茎　生　　　","廃棄率(%)":20},
	{"食品番号":"03001","索引番号":224,"食品名":"（砂糖類）　黒砂糖　　　","廃棄率(%)":0},
	{"食品番号":"04001","索引番号":251,"食品名":"あずき　全粒　乾　　　","廃棄率(%)":0},
	{"食品番号":"05001","索引番号":344,"食品名":"アーモンド　乾　　　","廃棄率(%)":0}
];


var parts;
var query;

var count = 0;

connection.connect();

loopInsert(datas[0]);


function loopInsert(data){
	if(data === undefined){
	  connection.end();
		return;
	};
	parts = createSqlParts(data);
	query = createSqlInsertStatement( parts );
	checkValidate(parts);
};


function insert(query){
	connection.query(query, function(err, rows, fields){
	  if(err){throw err;};
	  console.log(rows);
	});
};


function checkValidate(parts){
	var partsArray = parts[0].split(',');
	connection.query('desc nutrition;', function(err, rows, fields){
	  if(err){throw err;};
	  if(rows.length !== partsArray.length){
	  	console.log('フィールド数が不一致です。');
			return connection.end();
	  };
	  insert(query);
	  count++;
	  loopInsert(datas[count]);
	});
};

function createSqlParts(obj){
	var keys = Object.keys(obj);
	var values;
	var temp;
	for(var i=0; i < keys.length; i++){
		var temp = obj[keys[i]];
		if(typeof temp === 'string'){ temp = '"' + temp + '"' };
		if(i === 0){ values = temp; }
		else{ values = values + ',' + temp };
	};
	for(var i=0; i < keys.length; i++){
		keys[i] = '`' + keys[i] + '`';
	};
	keys = keys.join(',');
	// console.log(keys);
	// console.log(values);
	return [keys, values];
};

function createSqlInsertStatement(parts){
	return 'insert into nutrition (' + parts[0] + ') values (' + parts[1] + ');';
};