#! /usr/bin/perl

use DBI;

# Initial major vedors
# push (@vendor, "Debian");
# push (@vendor, "Progeny");
# push (@vendor, "SuSE");
# push (@vendor, "RedHat");
# push (@vendor, "Mandrake");
# push (@vendor, "Immunix");
# @vendor = sort (@vendor);

$archive_dir = "/var/lib/webspace/www.infodrom.ffis.de/Mail-Archive/security";
$http_prefix = "http://www.infodrom.ffis.de/Mail-Archive/security";

sub init
{
    $engine  = "dbi:Pg:dbname=web;host=kuolema";
    $dbh = DBI->connect($engine);
    if (!$dbh) {
	print "Access to database denied!\n";
	return 1;
    }

    $dbh->do("SET DateStyle = 'ISO'");
}

sub fixed
{
    my $incident = shift;
    my @vendor;
    my ($query, $sth, @row);

    $query  = "SELECT vendor FROM vendorreport WHERE incident = $incident ORDER BY vendor";
    $sth = $dbh->prepare($query);
    if ($sth && ($rc = $sth->execute) > 0) {
	while (@row = $sth->fetchrow_array) {
	    push (@vendor, $row[0]);
	}
    }

    return @vendor;
}

sub list_open
{
    my ($query, $sth, @row);

    $query  = "SELECT id,date,subject FROM incident";
    #$query .= " WHERE $where" if ($where);
    $query .= " ORDER by id";

    $sth = $dbh->prepare($query);
    if ($sth && ($rc = $sth->execute) > 0) {
	while (@row = $sth->fetchrow_array) {
	    $row[1] =~ /^([^ ]+)\s/ && {$date = $1};
	    printf "%3d. %s  %s", $row[0], $date, $row[2];
	    @fixed = fixed ($row[0]);
	    printf " (%s)", join (", ", @fixed) if (@fixed);
	    print  "\n";
	}
    }
}

sub lastmail
{
    my ($date_sec,$date_min,$date_hour,$date_mday,$date_mon,$date_year,$date_wday,$date_isdst)
	= localtime();
    my $ydir = $date_year + 1900;

    if (opendir (DIR, "$archive_dir/$ydir")) {
	@files = sort grep /\d+\.html/, readdir (DIR);
	closedir (DIR);
    }
    return $http_prefix . "/" . $ydir . "/" . pop (@files);
}

sub add_vendor
{
    my ($nr, $vendor, $url, $date);
    my ($query, $sth, @row);
    my ($date_sec,$date_min,$date_hour,$date_mday,$date_mon,$date_year,$date_wday,$date_isdst)
	= localtime();

    print "Nr. "; $nr = <STDIN>;
    chomp ($nr);
    exit if ($nr eq "");
    
    $query = "SELECT id,date,subject FROM incident WHERE id = $nr";

    $sth = $dbh->prepare($query);
    if ($sth && ($rc = $sth->execute) > 0) {
        if (@row = $sth->fetchrow_array) {
	    printf "%3d. %s  %s\n", $row[0], $date, $row[2];
	} else {
	    print "Security report not found.\n";
	    exit (0);
	}
    } else {
	print "Security report not found.\n";
	exit (0);
    }

    print "Vendor: "; $vendor = <STDIN>;
    chomp ($vendor);
    exit if ($vendor eq "");

    print "URL: "; $url = <STDIN>;
    chomp ($url);
    exit if ($url eq "");
    $url = lastmail() if ($url eq "l");

    printf "Date (%d-%02d-%02d): ", $date_year+1900, $date_mon+1, $date_mday; $date = <STDIN>;
    chomp ($date);
    $date = sprintf ("%d-%02d-%02d", $date_year+1900, $date_mon+1, $date_mday) if ($date eq "");

    $query = sprintf ("INSERT INTO vendorreport (incident,vendor,date,url) VALUES (%d,'%s','%s','%s')",
		      $nr, $vendor, $date, $url);
    # printf "%s\n", $query;
    $dbh->do($query);
}

sub add_incident
{
    my ($nr, $subject, $packages, $type, $url, $date);
    my ($query, $sth, @row);
    my ($date_sec,$date_min,$date_hour,$date_mday,$date_mon,$date_year,$date_wday,$date_isdst)
	= localtime();

    print "Subject: "; $subject = <STDIN>;
    chomp ($subject);
    exit if ($subject eq "");

    print "Packages: "; $packages = <STDIN>;
    chomp ($packages);
    exit if ($packages eq "");

    print "Type: "; $type = <STDIN>;
    chomp ($type);
    exit if ($type eq "");
    $type = 'local' if ($type eq "l");
    $type = 'remote' if ($type eq "r");

    print "URL: "; $url = <STDIN>;
    chomp ($url);
    # exit if ($url eq "");
    $url = lastmail() if ($url eq "l");

    printf "Date (%d-%02d-%02d): ", $date_year+1900, $date_mon+1, $date_mday; $date = <STDIN>;
    chomp ($date);
    $date = sprintf ("%d-%02d-%02d", $date_year+1900, $date_mon+1, $date_mday) if ($date eq "");

    $query = "SELECT MAX(id) FROM incident";

    $sth = $dbh->prepare($query);
    if ($sth && ($rc = $sth->execute) > 0) {
        if (@row = $sth->fetchrow_array) {
	    $nr = $row[0]+1;
	} else {
	    print "Can't determine last security incident.\n";
	    exit (0);
	}
    } else {
	print "Can't determine last security incident.\n";
	exit (0);
    }

    $query = sprintf ("INSERT INTO incident (id,subject,packages,type,date,url) VALUES (%d,'%s','%s','%s','%s','%s')",
		      $nr, $subject, $packages, $type, $date, $url);
    # printf "%s\n", $query;
    $dbh->do($query);
}

init();

if ($#ARGV > -1 && $ARGV[0] eq "-a") {
    add_incident();
} else {
    list_open();
    add_vendor();
}