#!/usr/bin/perl

use strict;
use warnings;
use DBI;
use Text::CSV;
use Time::HiRes;

my $dsn = 'DBI:mysql:cacti:localhost';
my $db_user_name = 'DBUSER';
my $db_password = 'DBPASS';
my $dbh = DBI->connect($dsn, $db_user_name, $db_password);
my $linenum = 0;
my $t0 = Time::HiRes::gettimeofday();
my $elapsed;
my @state_orders;

open(STATES, "<", "states") or die $!;
while(<STATES>) {
    chomp $_;
    my @tmp = { abbr => $_, graphs => 0 };
    push(@state_orders, @tmp);
}

defined($ARGV[0]) or die "Please supply an input CSV file as first argument";
defined($ARGV[1]) or die "Please supply a graph_tree id as second argument";

my $graph_tree_id = $ARGV[1];

my $infile = $ARGV[0];
my $csv = Text::CSV->new();
open (CSV, "<", $infile) or die $!;

print "INSERT INTO `graph_tree_items` (`id`, `graph_tree_id`, `local_graph_id`, `rra_id`, `title`, `host_id`, `order_key`, `host_grouping_type`, `sort_children_type`) VALUES \n";

while (<CSV>) {
    ++$linenum;
    if ($csv->parse($_)) {
        my @columns = $csv->fields();
        my $state = $columns[2];
        my $description = sprintf("%s--%s", $columns[0], $columns[1]);
        my $host_id = getID($description);
        my $state_order = getStateOrder($state);
        my $graph_order = getGraphOrder($state);        
        printf("(NULL, %d, 0, 0, '', %d, '%03d%03d000000000000000000000000000000000000000000000000000000000000000000000000000000000000', 1, 1),\n", $graph_tree_id, $host_id, $state_order, $graph_order);
    } else {
        my $err = $csv->error_input;
        print STDERR "Failed to parse line: $err";
    }
}
close CSV;

$elapsed = Time::HiRes::gettimeofday() - $t0;
printf(STDERR "Parsed %d lines in %.03f seconds\n",$linenum,$elapsed);

sub getID {
    my $descr = shift;
    my $sth = $dbh->prepare('SELECT id FROM host WHERE description = ?')
        or die "Couldn't prepare statement: " . $dbh->errstr;
    $sth->execute($descr)
        or die "Couldn't execute statement: " . $sth->errstr;
    if(my $id = $sth->fetchrow()) {
        return $id;
    } else {
        return -1;
    }
};

sub getStateOrder {
    my $state = shift;
    my $sth = $dbh->prepare('SELECT order_key FROM graph_tree_items WHERE title = ?')
        or die "Couldn't prepare statement: " . $dbh->errstr;
    $sth->execute($state)
        or die "Couldn't execute statement: " . $sth->errstr;
    if(my $order = $sth->fetchrow()) {
        return substr($order,0,3);
    } else {
        return -1;
    }
};

sub getGraphOrder {
    my $state = shift;
    for(my $i=0;$i < $#state_orders;$i++) {
        if($state_orders[$i]{'abbr'} eq $state) {
            $state_orders[$i]{'graphs'} += 1;
            return $state_orders[$i]{'graphs'};
        }
    }
    return -1;
};