#!/usr/bin/perl -w
use DBI;
use strict;
{
open (FILE,"words.csv");
my $line;
#english word -> [russian words]
my %english;
while($line = <FILE>){
	chomp($line);
	my @words;
	while($line=~/"(.*?)"/g){
		#print $1,"\n";
		push @words,$1;
	}
	my $eng=shift(@words);
	$english{$eng}=\@words;
}
close FILE;
my $dsn = 'DBI:mysql:englishwords:localhost';
my $db_user_name = '';
my $db_password = '';
my $dbh = DBI->connect($dsn, $db_user_name, $db_password);
$dbh->do('SET CHARACTER SET utf8');
$dbh->do('SET NAMES utf8');
for my $key (keys(%english)){
	insert_eng($dbh,$key);
	my $id_eng = eng_id($dbh, $key);
	for my $rus_word (@{$english{$key}}){
		insert_rus($dbh, $rus_word);
		my $id_rus= rus_id($dbh, $rus_word);
		insert_translate($dbh, $id_eng, $id_rus);
	}
	
}
$dbh->disconnect();

}
sub insert_eng {
	my $dbh = shift;
	my $eng_word = shift;
	my $request="insert into english (word) VALUES (\"$eng_word\");";
	my $sth= $dbh->prepare($request);
	$sth->execute();
	$sth->finish();

}
sub insert_rus {

	my $dbh = shift;
	my $rus_word = shift;
	my $request="insert into russian (word) VALUES (\"$rus_word\");";
	my $sth= $dbh->prepare($request);
	$sth->execute();
	$sth->finish();

}

sub rus_id {
	my $dbh = shift;
	my $rus_word = shift;
	my $request ="SELECT id from russian where word=\"$rus_word\";";
	my $sth= $dbh->prepare($request);
	$sth->execute();
	my @val = $sth->fetchrow_array();
	my $id_rus=$val[0];
        $sth->finish();
	return $id_rus;
}
sub eng_id {

	my $dbh = shift;
	my $eng_word = shift;
	my $request ="SELECT id from english where word=\"$eng_word\";";
	my $sth= $dbh->prepare($request);
	$sth->execute();
	my @val = $sth->fetchrow_array();
	my $id_eng=$val[0];
        $sth->finish();
	return $id_eng;
}
sub insert_translate {

	my $dbh = shift;
	my $id_eng = shift;
	my $id_rus = shift;
	my $request="insert into translate (id_eng, id_rus) VALUES ($id_eng, $id_rus);";
	my $sth= $dbh->prepare($request);
	$sth->execute();
	$sth->finish();

}