#!/usr/bin/perl

use DBI;
use DBD::mysql;

require '../lib/config.pl';

my ($fileName) = 'taxonomy.txt';

open(FILE, "<$fileName") or die;

my $dbh = DBI->connect("DBI:mysql:$DB_NAME:$DB_HOST",$DB_USER,$DB_PASS,{RaiseError => 1});

my (@lines) = <FILE>;
shift @lines;shift @lines;

my $i=0;
my @table;
foreach (@lines){
	my ($orgName, $superKingdom, $group) = ($_ =~ /[0-9]*\t[0-9]+\t[0-9]+\t(.*)\t(Bacteria|Archaea)\t([a-z \/-]+)\t.*/i);

	$orgName =~ s/\'/\\'/g;
	$table[$i]{'organism'} = $orgName;
	$table[$i]{'superKingdom'} = $superKingdom;
	$table[$i]{'group'} = $group;
	
	$i++;
}


$dbh->do("DELETE FROM taxonomy");
$dbh->do("ALTER TABLE taxonomy AUTO_INCREMENT=1 ");

for ($i=0; $i<@table; $i++){
	$sql = "
		INSERT INTO taxonomy (organism, superKingdom, taxGroup)
		VALUES
	";
	
	$sql .= "(\"".$table[$i]{'organism'}."\",'".$table[$i]{'superKingdom'}."','".$table[$i]{'group'}."')";
	
# 	print $sql."\n\n"; exit;
	
	$dbh->do($sql);
}

# $sql .= "(\"".$table[$i]{'organism'}."\",'".$table[$i]{'superKingdom'}."','".$table[$i]{'group'}."')";

# $dbh->do($sql);

$dbh->disconnect;

# print $sql;