#!/v/openpkg/sw/bin/perl

use IO::All;
use Date::Parse;
use DBI;
use DBD::SQLite;
use DBIx::Simple;

my $mailbox  = "/v/openpkg/arc/mail/openpkg-contribution";
my $database = "oca.db";

#   open (and on-the-fly create) database
my $db_init = (-f $database ? 0 : 1);
my $db = DBIx::Simple->connect(
    "dbi:SQLite:dbname=$database", "", "",
    { RaiseError => 0, AutoCommit => 0 }
);
if ($db_init) {
    $db->query(q{
        CREATE TABLE oca (
            date            INTEGER,
            version         TEXT,
            agreement       TEXT,
            name            TEXT,
            email           TEXT,
            entity          TEXT
        );
    }) or die $db->error();
    $db->query(q{
        CREATE TABLE contributor (
            agreed_upto     INTEGER,
            resign_from     INTEGER,
            version         TEXT,
            email           TEXT
        );
    }) or die $db->error();
    $db->commit();
}
else {
    $db->query(q{ DELETE FROM oca; });
    $db->query(q{ DELETE FROM contributor; });
}

#   extract information from mailbox
my $mbox < io($mailbox);
$mbox =~ s/
    \s+document\s+version\s+(\d+\.\d+).+?
    Full\s+Name:\s+([^\n]+)\s*
    Email\s+Address:\s+([^\n]+)\s*
    Entity:\s+([^\n]+)\s*
    Date:\s+([^\n]+)\s*\(UTC\)\s*
    Agreement:\s+([^\n]+)\s*
/
    #printf("%s %s %s %s <%s> %s\n", $5, $1, $6, $2, $3, $4);
    $db->query(q{
        INSERT INTO oca (date, version, agreement, name, email, entity) VALUES (??)
    }, str2time($5), $1, $6, $2, $3, $4) or die $db->error();
/xsge;

#   calculate effective acceed and resign
foreach my $email ($db->query(q{
    SELECT DISTINCT email
    FROM   oca
    ORDER  BY email
})->flat()) {
    my ($agreed_upto) = $db->query(q{
        SELECT MAX(date)
        FROM   oca
        WHERE  email = ? AND
               agreement = 'ACCEDE'
    }, $email)->flat();
    my ($resign_from) = $db->query(q{
        SELECT MIN(date)
        FROM   oca
        WHERE  email = ? AND
               agreement = 'RESIGN' AND
               date > ?
    }, $email, $agreed_upto)->flat();
    my ($version) = $db->query(q{
        SELECT version
        FROM   oca
        WHERE  email = ? AND
               date = (SELECT MAX(date) FROM oca WHERE email = ?)
    }, $email, $email)->flat();
    $db->query(q{
        INSERT INTO contributor (agreed_upto, resign_from, version, email) VALUES (??)
    }, $agreed_upto, $resign_from, $version, $email) or die $db->error();
    #printf("$email\n");
}

#   close database
$db->commit();
$db->disconnect();
undef $db;