#!/usr/bin/perl

use XML::Simple;
use Data::Dumper;

# get the file name
$filename = $ARGV[0];
if (!$filename) {
    die 'error: planner file name must be supplied as first argument';
}
# slurp in the xml file noisily
$ref = XMLin($filename, KeyAttr => []);
# dump it to the screen for debugging only
#print Dumper($ref);

split(/\./, $filename);
$database = @_[0];

print <<EOM;
DROP DATABASE IF EXISTS $database;
CREATE DATABASE $database;
USE $database;
CREATE TABLE Resource (
    id INT NOT NULL,
    name VARCHAR(128),
    stdRate FLOAT,
    PRIMARY KEY (id)
);
CREATE TABLE Allocation (
    resourceId INT,
    taskId INT,
    units INT
);
CREATE TABLE Task (
    id INT NOT NULL,
    parentId INT,
    wbsId INT,
    name VARCHAR(128),
    work INT
);
EOM

@resray = @{$ref->{resources}->{resource}};
foreach $resref (@resray) {
    print "INSERT INTO Resource SET id = $resref->{'id'}, name = \"$resref->{'name'}\", stdRate = $resref->{'std-rate'};\n";
}

@allocray = @{$ref->{allocations}->{allocation}};
# get scientist units out of allocation array all tasks
foreach $allocref (@allocray) {
	print "INSERT INTO Allocation SET resourceId = $allocref->{'resource-id'}, taskId = $allocref->{'task-id'}, units = $allocref->{'units'};\n";
}

$taskrayref = $ref->{tasks}->{task};
$parentId = 0;
@taskHashrefRay = get_tasks($taskrayref, $parentId);
foreach $hashref (@taskHashrefRay) {
    $tname = $hashref->{'name'};
    if ($tname =~ /\[/) {
	split(/\[/, $tname);
	split(/\]/, $_[1]);
	$wbsId = $_[0];
    } else {
	$wbsId = 'NULL';
}
    print "INSERT INTO Task SET id = $hashref->{'id'}, wbsId = $wbsId, parentId = $hashref->{'parent-id'}, name = \"$tname\", work = $hashref->{'work'};\n";
}

exit;

sub get_tasks {
    my ($taskrayref, $parentId) = @_;
    my @taskHashrefRay = ();
    my @taskray = @$taskrayref;
    foreach $hashref (@taskray) {
	#print "id = $hashref->{'id'}, name = $hashref->{'name'}\n";
	$hashref->{'parent-id'} = $parentId;
	push (@taskHashrefRay, $hashref);
	if ($hashref->{'task'}) {
	    #print "subtasks found\n";
	    @subtaskHashrefRay = get_tasks($hashref->{'task'}, $hashref->{'id'});
	    foreach $subtaskHashref (@subtaskHashrefRay) {
		push (@taskHashrefRay, $subtaskHashref);
	    }
	}
    }
    return @taskHashrefRay;
}
