#!/usr/bin/perl -w
#
# poke.pl
#
# J. Uckelman (uckelman@iastate.edu)
# 25 February 2001
#

require 5.005;
use DBI;

my $version = 1;
my $config = '/home/g1/bin/scripts.conf';

# load the config file 
eval { require $config; }; die "$0: $config: $@\n" if $@;

# connect to game database
$dbh = DBI->connect("DBI:mysql:database=$database;host=$host;port=$port", $user, $password);
$database = $host = $port = $user = $password = 0; # avoid annoying "variable used only once" warnings

# pretty output
print "\npoke.pl version $version, by J. Uckelman\n\n";

# get data
open FILE, "$ARGV[0]" or die "$ARGV[0] $!\n";
my $data = join '', <FILE>;
close FILE;
my $swap = $data;

# do dry run fisrt
my $dry = 1;
print "Dry run...\n";

# get records
while ($data) {
	my ($command, $type) = $data =~ m/^(.*?) (.*?)\n/ or die "bad command or type";
	$data = $';
	if ($type eq 'proposal' or $type eq 'motion') {
		if ($command eq 'add') {
			
			# Format:
			#	
			# number/revision
			# activity
			# mdate
			# rdate
			# owner
			# title
			#
			# text
			#
			# ----
		
			# get prop parts
			my ($number, $revision, $activity, $mdate, $rdate, $owner, $title, $text) = $data =~ m/^(\d+)\/(\d+)\n(\d)\n(.*?)\n(.*?)\n(.*?)\n(.*?)\n\n(.*?)\n-{4}\n/s or die "bad format";
			$data = $';
	
			# convert made and recognized dates
			$mdate = &convdate($mdate);
			$rdate = &convdate($rdate);

			# get owner id
			$owner = quotemeta($owner);
			my $owner_id = &getid($owner, $mdate);
		
			# guard title and text
			$title = quotemeta($title);
			$text = quotemeta($text);

			# poke into db
			unless ($dry) {
				$dbh->do("insert into $type (number, revision, activity, owner, title, text, start) values ($number, $revision, $activity, $owner_id, '$title', '$text', '$mdate')") or die "I can't let you do that, Dave,";
		
				if ($revision == 0) { 
					my $what = $type eq 'proposal' ? "Proposal" : $title;
					$dbh->do("insert into event (date, event) values ('$mdate', '$owner made a $what [$number]')");
				 }
				else {
					$dbh->do("insert into event (date, event) values ('$mdate', '$owner revised \u$type $number')");
					$dbh->do("update $type set end = '$mdate' where number = $number and revision = $revision - 1");
				}

				$dbh->do("insert into ${type}0history (number, revision, id) values ($number, $revision, last_insert_id())");
				$dbh->do("insert into event (date, event) values ('$rdate', 'Administrator recognized \u$type $number/$revision')");
				$dbh->do("insert into ${type}0history (number, revision, id) values ($number, $revision, last_insert_id())");
			}
	
			print $type eq 'proposal' ? 'P' : 'M', "$number/$revision added\n";
		}
		elsif ($command eq 'withdraw') {
			
			# Format:
			#
			# number/revision
			# date
			# owner
			#
			# ----
		
			# get parts
			my ($number, $revision, $date, $owner) = $data =~ m/^(\d+)\/(\d+)\n(.*?)\n(.*?)\n\n-{4}\n/s or die "bad format";
			$data = $';

			# convert date
			$date = &convdate($date);

			# get owner id
			$owner = quotemeta($owner);
			my $owner_id = &getid($owner, $date);

			# poke into db
			unless ($dry) {
				$dbh->do("update $type set end = '$date', activity = 2 where number = $number and revision = $revision") or die "I can't let you do that, Dave,";
				$dbh->do("insert into event (date, event) values ('$date', '$owner withdrew \u$type $number/$revision')");
				$dbh->do("insert into ${type}0history (number, revision, id) values ($number, $revision, last_insert_id())");
			}
		
			print $type eq 'proposal' ? 'P' : 'M', "$number/$revision withdrawn\n";
		}
		elsif ($command eq 'activate' or $command eq 'deactivate') {
			
			# Format:
			#
			# number/revision
			# date
			# owner
			#
			# ----

			# get parts
			my ($number, $revision, $date, $owner) = $data =~ m/^(\d+)\/(\d+)\n(.*?)\n(.*?)\n\n-{4}\n/s or die "bad format";
			$data = $';

			# convert date
			$date = &convdate($date);

			# get owner id
			$owner = quotemeta($owner);
			my $owner_id = &getid($owner, $date);

			# which?
			my $activity = $command eq 'activate' ? 1 : 0;

			# poke into db
			unless ($dry) {
				$dbh->do("update $type set activity = $activity where number = $number and revision = $revision") or die "I can't let you do that, Dave,";
				$dbh->do("insert into event (date, event) values ('$date', '$owner ${command}d \u$type $number/$revision')");
				$dbh->do("insert into ${type}0history (number, revision, id) values ($number, $revision, last_insert_id())");
			}
			
			print $type eq 'proposal' ? 'P' : 'M', "$number/$revision ${command}d\n";
		}
		elsif ($command eq 'rescind') {
			
			# Format
			#
			# mdate
			# rdate
			# owner
			# title			
			#
			# ----
	
			# get parts
			my ($mdate, $rdate, $owner, $title) = $data =~ m/^(.*?)\n(.*?)\n(.*?)\n(.*?)\n\n-{4}\n/s or die "bad format";
			$data = $';

			# convert dates
			$mdate = &convdate($mdate);
			$rdate = &convdate($rdate);

			# guard
			$owner = quotemeta($owner);
			$title = quotemeta($title);

			my $what = $type eq 'proposal' ? "\u$type" : $title;

			# poke into db
			unless ($dry) {
				$dbh->do("insert into event (date, event) values ('$mdate', '$owner made a $what [rescinded]')");
				$dbh->do("insert into event (date, event) values ('$rdate', '$owner rescinded an unrecognized \u$type')");
			}

			print "unrecognized \u$type rescinded\n";
		}
		else { die "bad command"; }
	}
	elsif ($type eq 'UC') {
		if ($command eq 'request') {
			
			# Format
			#
			# number/revision
			# date
			# owner
			#
			# ----

			# get UC parts
			my ($number, $revision, $date, $owner) = $data =~ m/^(\d+)\/(\d)\n(.*?)\n(.*?)\n\n-{4}\n/s or die "bad format";
			$data = $';

			# convert date
			$date = &convdate($date);
			
			# guard owner
			$owner = quotemeta($owner);

			# poke into db
			unless ($dry) {
				$dbh->do("insert into event (date, event) values ('$date', '$owner requested unanimous consent for Motion $number/$revision')");
				$dbh->do("insert into motion0history (number, revision, id) values ($number, $revision, last_insert_id())");
			}

			print "M$number/$revision UC request registered\n";
		}
		elsif ($command eq 'object') {
			
			# Format
			#
			# number/revision
			# date
			# objector
			# fails?
			#
			# ----

			# get UC parts
			my ($number, $revision, $date, $objector, $fails) = $data =~ m/^(\d+)\/(\d+)\n(.*?)\n(.*?)\n(.*?)\n\n-{4}\n/s or die "bad format";
			$data = $';

			# convert date
			$date = &convdate($date);

			# guard objector
			$objector = quotemeta($objector);

			# poke into db
			unless ($dry) {
				$dbh->do("insert into event (date, event) values ('$date', '$objector objected to Motion $number/$revision')");
				$dbh->do("insert into motion0history (number, revision, id) values ($number, $revision, last_insert_id())");				

				if ($fails eq 'fails') {
					$dbh->do("update motion set end = '$date' where number = $number and revision = $revision");
					$dbh->do("insert into event (date, event) values ('$date', 'Motion $number/$revision failed by objection')");
					$dbh->do("insert into motion0history (number, revision, id) values ($number, $revision, last_insert_id())");
				}
			}

			print "M$number/$revision UC ojbection registered\n";
		}
		elsif ($command eq 'passed') {
			
			# Format
			#
			# number/revision
			# date
			#
			# ----

			# get UC parts
			my ($number, $revision, $date) = $data =~ m/^(\d+)\/(\d+)\n(.*?)\n\n-{4}\n/s or die "bad format";
			$data = $';

			# convert date
			$date = &convdate($date);

			# poke into db
			unless ($dry) {
				$dbh->do("update motion set end = '$date' where number = $number and revision = $revision");
				$dbh->do("insert into event (date, event) values ('$date', 'Motion $number/$revision passed by unanimous consent')");
				$dbh->do("insert into motion0history (number, revision, id) values ($number, $revision, last_insert_id())");				
			}

			print "M$number/$revision passed by UC\n"
		}
		else { die "bad command"; }
	}
	elsif ($type eq 'rfj') {
		if ($command eq 'add') {
			
			# Format:
			#	
			# number/revision
			# vitality
			# mdate
			# rdate
			# plaintiff
			# statement
			# judge
			#
			# p_analysis
			#
			# ----
		
			# get rfj parts 
			my ($number, $revision, $vitality, $mdate, $rdate, $plaintiff, $statement, $judge, $p_analysis) = $data =~ m/^(\d+)\/(\d+)\n(\w)\n(.*?)\n(.*?)\n(.*?)\n(.*?)\n(.*?)\n\n(.*?)\n-{4}\n/s or die "bad format";
			$data = $';
	
			# convert made and recognized dates
			$mdate = &convdate($mdate);
			$rdate = &convdate($rdate);

			# get plaintiff and judge ids
			my $plaintiff_id = &getid($plaintiff, $mdate);
			my $judge_id = &getid($judge, $rdate);

			# guard statement and p_analysis
			$statement = quotemeta($statement);
			$p_analysis = quotemeta($p_analysis);

			# poke into db
			unless ($dry) {
				$dbh->do("insert into judgment (number, revision, plaintiff, statement, p_analysis, start) values ($number, $revision, $plaintiff_id, '$statement', '$p_analysis', '$mdate')") or die "I can't let you do that, Dave,";
				$dbh->do("insert into event (date, event) values ('$mdate', '$plaintiff made an RFJ [$number]')");
				$dbh->do("insert into judgment0history (number, revision, id) values ($number, $revision, last_insert_id())");
				$dbh->do("insert into event (date, event) values ('$rdate', 'OSJ recognized RFJ $number/$revision')");
				$dbh->do("insert into judgment0history (number, revision, id) values ($number, $revision, last_insert_id())");
				$dbh->do("insert into event (date, event) values ('$rdate', 'OSJ selected $judge to judge RFJ $number/$revision')");
				$dbh->do("insert into judgment0history (number, revision, id) values ($number, $revision, last_insert_id())");
				$dbh->do("insert into judgment0judge (number, revision, id) values ($number, $revision, $judge_id)");
			}

			print "RFJ$number/$revision added\n";
		}
		elsif ($command eq 'reassign') {
			
			# Format
			#
			# number/revision
			# date
			# old judge
			# new judge
			#
			# ----

			# get recusal parts
			my ($number, $revision, $date, $ojudge, $njudge) = $data =~ m/^(\d+)\/(\d+)\n(.*?)\n(.*?)\n(.*?)\n\n-{4}\n/s or die "bad format";
			$data = $';
	
			# convert date
			$date = &convdate($date);
			
			# get old and new judge ids
			$ojudge = quotemeta($ojudge);
			$njudge = quotemeta($njudge);
			my $ojudge_id = &getid($ojudge, $date);
			my $njudge_id = &getid($njudge, $date);

			# poke into db
			unless ($dry) {
				$dbh->do("insert into event (date, event) values ('$date', 'OSJ recused $ojudge from RFJ $number/$revision')");
				$dbh->do("insert into judgment0history (number, revision, id) values ($number, $revision, last_insert_id())");
				$dbh->do("insert into event (date, event) values ('$date', 'OSJ selected $njudge to judge RFJ $number/$revision')");
				$dbh->do("insert into judgment0history (number, revision, id) values ($number, $revision, last_insert_id())");
				$dbh->do("insert into judgment0judge (number, revision, id) values ($number, $revision, $njudge_id)");
				$dbh->do("update judgment0judge set recused = 'y' where number = $number and revision = $revision and id = $ojudge_id");
			}

			print "RFJ$number/$revision reassigned\n";
		}
		else { die "bad command"; }	
	}
	elsif ($type eq 'judgment') {
		if ($command eq 'add') {
			
			# Format:
			#	
			# number/revision
			# date
			# judge
			# ruling
			#
			# j_analysis
			#
			# ----
		
			# get judgment parts 
			my ($number, $revision, $date, $judge, $ruling, $j_analysis) = $data =~ m/^(\d+)\/(\d+)\n(.*?)\n(.*?)\n(.*?)\n\n(.*?)\n-{4}\n/s or die "bad format";
			$data = $';
	
			# convert date
			$date = &convdate($date);

			# guard j_analysis
			$j_analysis = quotemeta($j_analysis);
			$judge = quotemeta($judge);

			# poke into db
			unless ($dry) {
				$dbh->do("update judgment set ruling='$ruling', j_analysis='$j_analysis', end = '$date' where number = $number and revision = $revision") or die "I can't let you do that, Dave,";
				$dbh->do("insert into event (date, event) values ('$date', '$judge ruled \"$ruling\" on RFJ $number/$revision')");
				$dbh->do("insert into judgment0history (number, revision, id) values ($number, $revision, last_insert_id())");
			}

			print "J$number/$revision added\n";
		}
		else { die "bad command"; }
	}
	elsif ($type eq 'rule') {
		if ($command eq 'add') {
		
			# Format:
			#
			# number/revision
			# date
			# title
			# category[ category]...
			# Pcnum/crev or Mcnum/crev
			#
			# text
			#
			# ----

			# get rule parts
			my ($number, $revision, $date, $title, $categories, $ctype, $cnum, $crev, $text) = $data =~ m/^(\d+)\/(\d+)\n(.*?)\n(.*?)\n(.*?)\n(\w)(\d+)\/(\d+)\n\n(.*?)\n\n-{4}\n/s or die "bad format";
			$data = $';

			die "bad format" unless ($ctype eq 'P' or $ctype eq 'M');

			# convert date
			$date = &convdate($date);

			# guard title and text
			$title = quotemeta($title);
			$text = quotemeta($text);

			my $orevision = $revision - 1;

			# unpack categories
			my @category = split / /, $categories;

			# poke into db
			unless ($dry) {
				$dbh->do("insert into rule (number, revision, title, text, start) values ($number, $revision, '$title', '$text', '$date')");
				$dbh->do("update rule set end = '$date' where number = $number and revision = $revision - 1");
		
				my $cat;
				foreach $cat (@category) {
					$dbh->do("insert into rule0category (number, revision, category) values ($number, $revision, $cat)");
				}

				if ($revision == 0) { $dbh->do("insert into event (date, event) values ('$date', 'Rule $number/0 created by P$cnum/$crev')"); }
				elsif ($ctype eq 'P') { $dbh->do("insert into event (date, event) values ('$date', 'Rule $number/$orevision amended by Proposal $cnum/$crev')"); }
				elsif ($ctype eq 'M') { $dbh->do("insert into event (date, event) values ('$date', 'Rule $number/$orevision trivially amended by Motion $cnum/$crev')"); }
			
				$dbh->do("insert into rule0history (number, revision, id) values ($number, $revision, last_insert_id())");
			}

			print "R$number/$revision added\n";
		}
		elsif ($command eq 'repeal') {

				# Format:
				#
				# number/revision
				# date
				# pnum/prev
				#
				# ----
	
				# get repeal parts
				my ($number, $revision, $date, $pnum, $prev) = $data =~ m/^(\d+)\/(\d)\n(.*?)\n(\d+)\/(\d+)\n\n-{4}\n/s or die "bad format";
				$data = $';

				# convert date
				$date = &convdate($date);

				# poke into db
				unless ($dry) {
					$dbh->do("update rule set end = '$date' where number = $number and revision = $revision");
					$dbh->do("insert into event (date, event) values ('$date', 'Rule $number/0 repealed by Proposal $pnum/$prev')");
					$dbh->do("insert into rule0history (number, revision, id) values ($number, $revision, last_insert_id())");
				}

				print "R$number/$revision repealed\n";
		}

		else { die "bad command"; }
	}
	elsif ($type eq "player") {
		if ($command eq 'add') { 
			
			# Format:
			#
			# name
			# address
			# date
			#
			# ----

			# get player parts
			my ($name, $address, $date) = $data =~ m/^(.*?)\n(.*?@.*?)\n(.*?)\n\n-{4}\n/s or die "bad format";
			$data = $';

			# convert date
			$date = &convdate($date);

			# get max entity id
			my $sth = $dbh->prepare("select max(id) from entity");
			$sth->execute;
			my ($id) = $sth->fetchrow_array;
			$sth->finish;
			$id++;

			$name = quotemeta($name);

			# poke into db
			unless ($dry) {
				$dbh->do("insert into entity (id, type, activity, vitality) values ($id, 'player', 1, 1)");
				$dbh->do("insert into name (id, name, start) values ($id, '$name', '$date')");
				$dbh->do("insert into mail (id, address, start) values ($id, '$address', '$date')");
				$dbh->do("insert into player (id, start) values ($id, '$date')");
				$dbh->do("insert into event (date, event) values ('$date', '$name became a Player')");
			}

			print "Player $name added\n";
		}
		elsif ($command eq 'forfeit') {

			# Format:
			# 
			# name
			# date
			#
			# ----

			# get player parts
			my ($name, $date) = $data =~ m/^(.*?)\n(.*?)\n\n-{4}\n/s or die "bad format";
			$data = $';
		
			# convert date
			$date = &convdate($date);
			
			$name = quotemeta($name);

			my $id = &getid($name, $date);

			# poke into db
			unless ($dry) {
				$dbh->do("update entity set vitality = 0 where id = $id");
				$dbh->do("update name set end = '$date' where id = $id and isnull(end)");
				$dbh->do("update player set end = '$date' where id = $id");
				$dbh->do("insert into event (date, event) values ('$date', '$name forfeited')");
			}

			print "Player $name forfeited\n";
		}
		else { die "bad command"; }
	}
	elsif ($type eq 'office') {
		if ($command eq 'nominate') {

			# Format:
			#
			# nweek
			# nominator
			# date
			# nominee
			# office
			#
			# ----
			
			# get parts
			my ($nweek, $nominator, $date, $nominee, $office) = $data =~ m/^(\d+)\n(.*?)\n(.*?)\n(.*?)\n(.*?)\n\n-{4}\n/s or die "bad format";
			$data = $';

			# convert date
			$date = &convdate($date);

			# guard text
			$nominator = quotemeta($nominator);
			$nominee = quotemeta($nominee);
			$office = quotemeta($office);
			
			# get id
			my $nominee_id = &getid($nominee, $date);
			my $nominator_id = &getid($nominator, $date);

			# poke into db
			unless ($dry) {
				$dbh->do("insert into election (nweek, id, nominator, office) values ($nweek, $nominee_id, $nominator_id, '$office')");
				$dbh->do("insert into event (date, event) values ('$date', '$nominator nominated $nominee for $office')");
			}

			print "Player $nominee nominated for $office\n";
		}
		elsif ($command eq 'decline') {

			# Format:
			#
			# nweek
			# nominee
			# date
			# office
			#
			# ----

			# get parts
			my ($nweek, $nominee, $date, $office) = $data =~ m/^(\d+)\n(.*?)\n(.*?)\n(.*?)\n\n-{4}\n/s or die "bad format";
			$data = $';

			# convert date
			$date = &convdate($date);
			
			# guard text
			$nominee = quotemeta($nominee);
			$office = quotemeta($office);

			# get nominee id
			my $nominee_id = &getid($nominee, $date);

			# poke into db
			unless ($dry) {
				$dbh->do("update election set vitality = 'd', decline = 'y' where id = $nominee_id and office = '$office' and nweek = $nweek");
				$dbh->do("insert into event (date, event) values ('$date', '$nominee declined nomination for $office')");
			}

			print "Player $nominee declined nomination for $office\n";
		}
		elsif ($command eq 'tie') {

			# Format:
			#
			# date
			# office
			# officer
			#
			# ----
			
			# get parts
			my ($date, $office, $officer) = $data =~ m/^(.*?)\n(.*?)\n(.*?)\n\n-{4}\n/s or die "bad format";
			$data = $';

			# convert date
			$date = &convdate($date);

			# guard text
			$office = quotemeta($office);
			$officer = quotemeta($officer);

			# get officer id
			my $officer_id = &getid($officer, $date);

			# poke into db
			unless ($dry) {
				$dbh->do("update officer set end = '$date' where office = '$office' and isnull(end)");
				$dbh->do("insert into officer (id, office, start, admin) values ($officer_id, '$office', '$date', 0)");
				$dbh->do("insert into event (date, event) values ('$date', 'Administrator selected $officer for $office')");
			}

			print "$officer selected for $office\n";
		}
		else { die "bad command"; }
	}
	elsif ($type eq 'voting') {
		if ($command eq 'motion' or $command eq 'proposal' or $command eq 'election') {
			my ($nweek, $bdate, $edate, $quorum, $nums, $offices, $votes, @num, @rev, @office, @tmp, @vote, @name);
		
			if ($command eq 'motion' or $command eq 'proposal') {

				# Format:
				#
				# bdate
				# edate
				# quorum
				#
				# num/rev[ num/rev]...
				#
				# name
				# vote[ vote]...
				#
				# ----

				# get parts
				($bdate, $edate, $quorum, $nums, $votes) = $data =~ m/^(.*?)\n(.*?)\n(.*)\n\n(.*?)\n\n(.*?)\n\n-{4}\n/s or die "bad format";
				$data = $';

				# get prop numbers
				@tmp = split / /, $nums;
				for $i (0 .. $#tmp) { ($num[$i], $rev[$i]) = $tmp[$i] =~ m/(\d+)\/(\d+)/ or die "bad format"; } 

			}
			elsif ($command eq 'election') {	

				# Format
				#
				# nweek
				# bdate
				# edate
				# quorum
				#
				# office[, office]...
				#
				# name
				# vote[, vote]...
				#
				# ----

				# get parts
				($nweek, $bdate, $edate, $quorum, $offices, $votes) = $data =~ m/^(\d+)\n(.*?)\n(.*?)\n(.*?)\n\n(.*?)\n\n(.*?)\n\n-{4}\n/s or die "bad format";
				$data = $';

				# get offices
				@office = split /, /, $offices;								
			}
	
			# convert dates
			$bdate = &convdate($bdate);
			$edate = &convdate($edate);

			# get names and votes
			@tmp = split /\n/, $votes;
			for $i (0 ..  int($#tmp / 2)) {
				$name[$i] = $tmp[2*$i];
				my $spl = $command eq 'election' ? ', ' : ' ';
				push @vote, [ split /$spl/, $tmp[2*$i+1] ];
			}

			# calculate quorum
			if ($quorum eq 'vote') {
				$quorum = 0;
				for $i (0 .. $#vote) { 
					VOTE: for $j (0 .. $#num) {
						if ($vote[$i][$j] ne '-') {
							$quorum++;
							last VOTE;
						}
					}
				}
			}

			# poke voting period into db if not already
			my $sth = $dbh->prepare("select event from event where date = '$bdate' and event = 'Voting began'");
			$sth->execute;
			my ($tmp) = $sth->fetchrow_array;
			$sth->finish;
			if (defined($tmp) and $tmp eq 'Voting began') { print "\nVoting exists, not added"; }
			else {
				unless ($dry) {
					$dbh->do("insert into event (date, event) values ('$bdate', 'Voting began')");
					$dbh->do("insert into event (date, event) values ('$edate', 'Voting ended, quorum was $quorum')");
				}
				print "\nVoting added";
			}
			
			if ($command eq 'proposal' or $command eq 'motion') {
				# calculate passage
				for $i (0 .. $#num) {
					my ($yea, $nay, $abs, $not, $result) = (0, 0, 0, 0, 0);
					for $j (0 .. $#name) {
						$yea++ if $vote[$j][$i] eq 'y';
						$nay++ if $vote[$j][$i] eq 'n';
						$abs++ if $vote[$j][$i] eq 'a';
						$not++ if $vote[$j][$i] eq '-';	
				
						# poke individual voting into db
						unless ($dry) {				
							my $voter_id = &getid(quotemeta($name[$j]), $edate);
							$dbh->do("insert into ${command}0voting (number, revision, id, date, vote) values ($num[$i], $rev[$i], $voter_id, '$edate', '$vote[$j][$i]')");
						}
					}
					
					$result = ($yea + $nay + $abs) >= $quorum ? ($yea > $nay ? "passed" : "failed") : "held";
					printf("\nP%d/%d %s (%d-%d-%d-%d)", $num[$i], $rev[$i], $result, $yea, $nay, $abs, $not);
			
					# poke voting result into db
					unless ($dry) {
						$dbh->do("update $command set end = '$edate' where number = $num[$i] and revision = $rev[$i]") unless $result eq 'held';
						$dbh->do("insert into event (date, event) values ('$edate', '\u$command $num[$i]/$rev[$i] $result ($yea-$nay-$abs-$not)')") or die "I can't let you do that, Dave,";
						$dbh->do("insert into ${command}0history (number, revision, id) values ($num[$i], $rev[$i], last_insert_id())");
					}

					if ($command eq 'proposal') {
						# calculate scoring
						my @score = ();
						if ($result ne 'held') {
							my $sth = $dbh->prepare("select owner from proposal where number = $num[$i] and revision = $rev[$i]");
							$sth->execute;
							my ($owner_id) = $sth->fetchrow_array;
							$sth->finish;

							if ($result	eq 'passed') {
								push @score, ($owner_id, int(20 * ($yea / ($yea + $nay))));
						
								for $j (0 .. $#name) {
									if ($vote[$j][$i] eq 'n') { 
										push @score, (&getid(quotemeta($name[$j]), $edate), int(5 * ($yea / ($yea + $nay))));
									}
								}
							}
							elsif ($result eq 'failed') {	push @score, ($owner_id, -5); }
						}
			
						# poke scoring into db
						unless ($dry) {
							while (@score) {
								my $id = shift @score;
								my $newpoints = shift @score;
	
								$sth = $dbh->prepare("select score from entity where id = $id and activity = 'a'");
								$sth->execute;
								my ($oldpoints) = $sth->fetchrow_array;
								$sth->finish;
		
								my $action = ($newpoints >= 0) ? "credited" : "debited";
								my $nowpoints = $newpoints + $oldpoints;	
								my $scorer = &getname($id, $edate);				
	
								$dbh->do("update entity set score = $nowpoints where id = $id and activity = 'a'");
								$dbh->do("insert into event (date, event) values ('$edate', 'Administrator $action $scorer $newpoints Points for Proposal $num[$i]/$rev[$i]')");
							}
						}
					}
				}
			}
			elsif ($command eq 'election') {
				# calculate results
				for $i (0 .. $#office) {
					my $sth = $dbh->prepare("select name.name from name, election where nweek = $nweek and name.id = election.id and election.office = '$office[$i]' and election.decline = 'n' order by name.name");
					$sth->execute;

					my (@candidate, $tmp) = ();
					while (($tmp) = $sth->fetchrow_array) { push @candidate, [ $tmp, 0]; }
					$sth->finish;				

					for $j (0 .. $#name) { 
						for $k (0 .. $#candidate) { $candidate[$k][1]++ if $vote[$j][$i] eq $candidate[$k][0]; }
			
						# poke individual voting into db
						unless ($dry) {
							my $voter_id = &getid(quotemeta($name[$j]), $edate);
							my $vote_id = $vote[$j][$i] eq '-' ? '-' : &getid(quotemeta($vote[$j][$i]), $edate);
							$dbh->do("insert into ${command}0voting (nweek, id, vote, office, date) values ($nweek, $voter_id, '$vote_id', '$office[$i]', '$edate')");
						}
					}
	
					for $k (0 .. $#candidate) { print "\n $candidate[$k][0]: $candidate[$k][1]"; }	

					my @max;
					$max[0] = 0;
					for $j (1 .. $#candidate) {
						if ($candidate[$j][1] > $candidate[$max[0]][1] ) { $#max = 0; $max[0] = $j; }
						elsif ($candidate[$j][1] == $candidate[$max[0]][1]) { push @max, $j; }
					}
				
					$result = '';	
					if ($#max == 0) { $result = "$candidate[$max[0]][0] elected"; }
					else {
						foreach (@max) { $result .= "$candidate[$_][0], "; }
						$result =~ s/, $/ /;
						$result .= "tied for";
					}

					print "\n$result $office[$i]";
				
					# poke results into db
					unless ($dry) {
						$result = quotemeta($result);
						$dbh->do("update election set vitality = 'd' where nweek = $nweek and office = '$office[$i]'");
						$dbh->do("insert into event (date, event) values ('$edate', '$result $office[$i]')");
						$dbh->do("update officer set end = '$edate' where office = '$office[$i]' and isnull(end)");
						if ($#max == 0) {
							my $elected_id = &getid($candidate[$max[0]][0], $edate);
							$dbh->do("insert into officer (id, office, start, admin) values ($elected_id, '$office[$i]', '$edate', 0)");
						}
						else { $dbh->do("insert into officer (id, office, start, admin) values (0, '$office[$i]', '$edate', 1)"); }
					}
				}
			}
			
			print "\n";
		}
		else { die "bad command"; }
	}
	else { die "bad type"; }

	if (! $data and $dry) {
		# dry run is over; do more?
		$| = 1;
		print "Continue? ";
		my $answer = <STDIN>;
		$| = 0;
		if (lc($answer) =~ m/y/) {
			$dry = 0;
			$data = $swap;
			print "Damn the torpedoes...\n"
		}
		else { print "Make it stop! Make it stop!\n"; }
	}
}

$dbh->disconnect;

#
# getid
#
sub getid ($$) {
	my ($name, $date) = @_;
	my $sth = $dbh->prepare("select id from name where name = '$name' and start <= '$date' and (isnull(end) or '$date' <= end)");
	$sth->execute;
	my ($name_id) = $sth->fetchrow_array;
	$sth->finish;
	return $name_id;
}

#
# getname
#
sub getname ($$) {
	my ($id, $date) = @_;
	my $sth = $dbh->prepare("select name from name where id = $id and start <= '$date' and (isnull(end) or '$date' <= end)");
	$sth->execute;
	my ($name) = $sth->fetchrow_array;
	$sth->finish;
	return quotemeta($name);
}

#
# convdate
#
sub convdate ($) {
	my ($date) = @_;
	($date = `date -d '$date' +"%Y-%m-%d %T"`) =~ s/\n//;
	return $date;
}