#!/usr/bin/perl

# Takes log entries on STDIN, and outputs SQL commands on STDOUT
#
# Log entries look like this:

# 05/09/07 11:02:11: Shelly(225) in Courtyard(376):  ping
# 05/09/07 11:03:41: WIZ: Akili(105) in Courtyard(376):  afk
# 05/09/07 11:03:51: WIZ: Calin(154) in Courtyard(376):  sa look
# 05/09/07 11:03:51: WIZ: Sarai by Calin(163) in Game Room(199):  look

# 09/10/06 21:26:40: ACPT: 10 128.61.39.117(3421) 128.61.39.117 C#114 P#8002 TEXT
# 09/10/06 21:26:45: 10: connect Kibati ****
# 09/10/06 21:26:45: CONN: 10 Kibati(#926PB) 128.61.39.117(3421) 128.61.39.117 P#8002
# 09/10/06 22:55:48: DISC:  8 Cal(#603PBXM2) 192.168.1.254(54905) 192.168.1.254, 70 cmds P#8002

my %rooms;
my %people;

while(my $line = <STDIN>)
{
  if($line =~ /([0-9]+)\/([0-9]+)\/([0-9]+) ([0-9]+):([0-9]+):([0-9]+): +DISC: .*\(never connected\)/)
  { 
    # Connect log line we're ignoring.
  }
  elsif($line =~ /([0-9]+)\/([0-9]+)\/([0-9]+) ([0-9]+):([0-9]+):([0-9]+): +([0-9]+|ACPT|FAIL|HELP|TYPO): /)
  { 
    # Connect log line we're ignoring.
  }
  elsif($line =~ /([0-9]+)\/([0-9]+)\/([0-9]+) ([0-9]+):([0-9]+):([0-9]+): (CONN|DISC): +([0-9]+) ([^\(]+)\(#([0-9]+)([^\)]+)/)
  {
    my $year  = $3;
    my $month = $1;
    my $day   = $2;
    my $hour  = $4;
    my $minute= $5;
    my $second= $6;
    my $event = $7;
    my $conn  = $8;
    my $name  = $9;
    my $dbref = $10;
    my $wiz   = $11;

    if($year < 70){ $year = "20".$year; }
    else { $year = "19".$year; }
    if($wiz =~ /W/) { $wiz = "WIZ: "; }
    else{ $wiz = ""; }
    ($name, my $tmp) = &nameID("$wiz$name($dbref)");
    my $datetime = "\"$year-$month-$day $hour:$minute:$second\"";
    print "REPLACE CONNECTS VALUES ($datetime, \"$name\", \"$conn\", \"$event\");\n";
  }
  elsif($line =~ /([0-9]+)\/([0-9]+)\/([0-9]+) ([0-9]+):([0-9]+):([0-9]+): ([^\)]+\)) in ([^:]+): +(.*)$/)
  {
    my $year  = $3;
    my $month = $1;
    my $day   = $2;
    my $hour  = $4;
    my $minute= $5;
    my $second= $6;
    my $name  = $7;
    my $room  = $8;
    my $cmd   = $9;

    if($year < 70){ $year = "20".$year; }
    else { $year = "19".$year; }
    $room = &roomID($room);
    ($name, my $forcedby) = &nameID($name);
    my $datetime = "\"$year-$month-$day $hour:$minute:$second\"";
    if($forcedby){ $forcedby = "\"$forcedby\""; }
    else{ $forcedby = "NULL"; }
    $cmd =~ s/\\/\\\\/g;
    $cmd =~ s/"/\\"/g;
    print "REPLACE COMMANDS VALUES ($datetime, \"$name\", \"$room\", $forcedby, \"$cmd\");\n";
  }
  else
  {
    print STDERR "ERROR: ".$line;
  }
}

sub roomID
{ # Game Room(199)
  my $room = shift;
  if($room =~ /(.*)\(([0-9]+)\)$/)
  {
    my $dbref = $2;
    my $name = $1;
    if($rooms{$dbref} ne $name)
    { # Insert or update rooms table
      $rooms{$dbref} = $name;
      $name =~ s/"/\\"/g;
      print "REPLACE ROOMS VALUES(\"$dbref\", \"$name\");\n";
    }
    return $dbref;
  }
  else
  {
    print STDERR "ERROR: ".$room;
  }
}

sub nameID
{ # WIZ: Sarai by Calin(163)
  my $name = shift;
  if($name =~ /(.*)\(([0-9]+)\)$/)
  {
    my $dbref = $2;
    my $name = $1;
    my $wiz = 0;
    if($name =~ /WIZ: (.*)/)
    {
      $wiz = 1;
      $name = $1;
    }
    my ($player,$forcedby) = split " by ", $name;
    if($forcedby){ $wiz = 0; }

    if($people{$dbref} ne $player)
    { # Insert or update people table
      $people{$dbref} = $player;
      $player =~ s/"/\\"/g;
      print "INSERT INTO PEOPLE VALUES(\"$dbref\", \"$player\", \"$wiz\", \"".lc($player)."\") ON DUPLICATE KEY UPDATE OWNER=OWNER;\n";
    }
    $forcedby = &dbrefPlayer($forcedby);
    return ($dbref, $forcedby);
  }
  else
  {
    print STDERR "ERROR: ".$name;
  }
}

sub dbrefPlayer
{
  my $player = shift;
  while(my ($dbref,$name) = each %people)
  {
    if($player eq $name){ return $dbref; }
  }
  return $player;
}
