#!/usr/bin/perl

# Load postgresql database from game data.
# Bruno Wolff III
# Last updated July 12, 2014

$" = '';

use utf8;
use locale ':not_characters';
use open ':locale';
use POSIX qw(locale_h);
setlocale(LC_ALL, "en_US.utf8");

# Postgres perl library

use Pg;

use Time::JulianDay;

$day1 = julian_day(1899, 12, 30);
@date = localtime;
$date[5] += 1900;
$date[4]++;
$today = julian_day($date[5], $date[4], $date[3]);

# Open ERROR file for writing data error report
$error = 'errors.txt';

if (!open(ERROR, ">$error")) {
  print STDERR "Couldn't open error report file.\n";
  exit;
}

$port = 5432;
$port = $ARGV[0] if $ARGV[0] =~ m/^[[:digit:]]+$/;

# Shut off STDERR so Pg doesn't write to it
close(STDERR);
open(STDERR, '>/dev/null');

# Connect to the AREA database

$conn = Pg::connectdb("dbname=area port=$port");
if ($conn->status != PGRES_CONNECTION_OK) {
  print ERROR "Unable to connect to the area database.\n";
  print ERROR $conn->errorMessage;
  exit;
}

# Used to check for game title duplicate warnings
%titledup = ();

# Per game stuff key gameid (from filename)
%title = ();
%gdate = ();
%pub = ();
%gameurl = ();
%gameurle = ();
%gsol = ();

# Per player stuff key areaid
%lname = ();
%fmname = ();
%aname = ();
%gen = ();
%genlab = ();
%anon = ();
%displayid = ();

# Per player and game stuff key gameid/areaid
%rate = ();
%frq = ();
%opp = ();
%rmp = ();
%trn = ();
%pdate = ();
%rmc = ();

# Per publisher stuff
%pubname = ();
%puburl = ();

# Per known pubname stuff
%pubalias = ();

# WBC code map, url and title
%wbc = ();
%wbcurl = ();
%wbcevent = ();

# Name - ID consistancy check stuff
%chkid = ();
%chkname = ();

# Valid Rating Types
%rtype = ();

# Valid Contacts
%contacts = ();

# Game rating type
%gametype = ();

# Can the game win?
%sol = ();

# Game contact
%gamecont = ();

# contact default by type
%cdef = ();

# Default for solitaire games
%csol = ();

# contact exceptions
%cexc = ();

# List of IDs of people who don't want their names on the web page

if (!open(ANON, 'anon.tsv')) {
  print ERROR "Couldn't open anonymous ID file.\n";
  exit;
}
while (<ANON>) {
  s/\s+//g;
  $anon{$_} = 'anon';
}
close(ANON);

# Remove per file error reports

if (!opendir(DIR, 'errors')) {
  print ERROR "Couldn't open error directory.\n";
}
foreach $error (readdir(DIR)) {
  next if $error eq '.htaccess';
  unlink "errors/$error";
}
closedir(DIR);

# Get list of publishers

if (!open(PUB, 'publish.tsv')) {
  print ERROR "Couldn't open publisher definition file.\n";
  exit;
}
while (<PUB>) {
  chop;
  @fields = split("\t");
  if (defined($pubname{$fields[0]})) {
    print ERROR "Duplicate publisher code \"$fields[0]\".\n";
  }
  $pubname{$fields[0]} = $fields[1];
  $puburl{$fields[0]} = $fields[2];
  if (!defined($pubalias{$fields[1]})) {
    $pubalias{$fields[1]} = [];
  }
  else {
    print ERROR "Duplicate publisher name \"$fields[1]\".\n";
  }
  push @{$pubalias{$fields[1]}}, $fields[0];
}
close(PUB);

# Get list of publisher aliases

if (!open(ALIAS, 'palias.tsv')) {
  print ERROR "Couldn't open publisher alias file.\n";
  exit;
}
while (<ALIAS>) {
  chop;
  @fields = split("\t");
  if (!defined($pubalias{$fields[1]})) {
    print ERROR "Publisher alias \"$fields[0]\" points to bogus publisher \"$fields[1]\"\n";
  }
  $pub = @{$pubalias{$fields[1]}}[0];
  if (!defined($pubalias{$fields[0]})) {
    $pubalias{$fields[0]} = [];
  }
  push @{$pubalias{$fields[0]}}, $pub;
}
close(ALIAS);

# Get a list of valid rating types (e.g. TWO, TEAM, WTA, RACE, etc.)
if (!open(RTYPE, 'rtypes.tsv')) {
  print ERROR "Couldn't open rating type definition file.\n";
  exit;
}
while (<RTYPE>) {
  chop;
  @fields = split("\t");
  $type = uc($fields[0]);
  $type =~ s/[^ABCDEFGHIJKLMNOPQRSTUVWXYZ[:digit:]]//g;
  $descr = $fields[1];
  if ($type eq '') {
    print ERROR "Bad type name in type definition: $type $descr\n";
  }
  elsif (defined $rtype{$type}) {
    print ERROR "Duplicate rating type definition: $type $descr\n";
  }
  else {
    $rtype{$type} = $descr;
  }
}
close (RTYPE);

# Get rating type for each game
if (!open(GTYPE, 'gametypes.tsv')) {
  print "Couldn't open game rating type definition file.\n";
  exit;
}
while (<GTYPE>) {
  chop;
  @fields = split("\t");
  $game = uc($fields[0]);
  $game =~ s/[^ABCDEFGHIJKLMNOPQRSTUVWXYZ[:digit:]]//g;
  $type = uc($fields[1]);
  $type =~ s/[^ABCDEFGHIJKLMNOPQRSTUVWXYZ[:digit:]]//g;
  if (defined $gametype{$game}) {
    print ERROR "Duplicate game rating type definition: $game $type\n";
  }
  elsif (! defined $rtype{$type}) {
    print ERROR "Undefined rating type in game type definition: $game $type\n";
  }
  else {
    $gametype{$game} = $type;
  }
}
close (GTYPE);

# Get a list of games where the game can win
if (!open(SOL, 'sol.tsv')) {
  print "Couldn't open solitaire game definition file.\n";
  exit;
}
while (<SOL>) {
  chop;
  @fields = split("\t");
  $game = uc($fields[0]);
  $game =~ s/[^ABCDEFGHIJKLMNOPQRSTUVWXYZ[:digit:]]//g;
  if (defined $sol{$game}) {
    print ERROR "Duplicate game in solitaire list: $game\n";
  }
  $sol{$game} = '';
}
close (SOL);

# Get a list valid contact codes and email addresses
if(!open(CONTACT, 'contacts.tsv')) {
  print ERROR "Couldn't open contact definition file.\n";
  exit;
}
while (<CONTACT>) {
  chop;
  @fields = split("\t");
  $contact = uc($fields[0]);
  $contact =~ s/[^ABCDEFGHIJKLMNOPQRSTUVWXYZ[:digit:]]//g;
  $email = $fields[1];
  $email =~ s/[^-ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz[:digit:]_.@]//g;
  if ($contact eq '') {
    print ERROR "Bad contact code in contact definition: $contact $email\n";
  }
  elsif (defined $contacts{$contact}) {
    print ERROR "Duplicate contact definition: $contact $email\n";
  }
  elsif ($email !~ m/^[-ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz[:digit:]_.]+@[-ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz[:digit:]_.]+$/) {
    print ERROR "Bad email address in contact definition: $contact $email\n";
  }
  else {
    $contacts{$contact} = $email;
  }
}
close(CONTACT);

# Get the default contact for solitaire games
if (!open(CSOL, 'contactsol.tsv')) {
  print "Couldn't open solitaire default contact definition file.\n";
  exit;
}
while (<CSOL>) {
  chop;
  @fields = split("\t");
  $contact = uc($fields[0]);
  $contact =~ s/[^ABCDEFGHIJKLMNOPQRSTUVWXYZ[:digit:]]//g;
  if ($contact eq '') {
    print ERROR "Bad contact code in solitaire contact definition: $contact\n";
  }
  elsif (defined $csol{$contact}) {
    print ERROR "Duplicate solitaire contact definition: $contact\n";
  }
  elsif (scalar(keys(%csol)) > 0) {
    print ERROR "At most one solitaire contact exception is allowed.\n";
  }
  elsif (!defined $contacts{$contact}) {
    print ERROR "Undefined contact code in solitaire default file: $contact\n";
  }
  else {
    $csol{$contact} = '';
    $csol = $contact;
  }
}
close (CSOL);
if (scalar(keys(%csol)) < 1) {
  print ERROR "No default solitaire contact was given.\n";
  undef $csol;
}

# Get a list of game rating types (e.g. TWO, TEAM, WTA, RACE, etc.)
# Get a list of default contact codes for valid rating types
# Since that is how most of our stuff is divided up it is convenient to use
if (!open(CDEF, 'contactdef.tsv')) {
  print ERROR "Couldn't open contact default file.\n";
  exit;
}
while (<CDEF>) {
  chop;
  @fields = split("\t");
  $type = uc($fields[0]);
  $type =~ s/[^ABCDEFGHIJKLMNOPQRSTUVWXYZ[:digit:]]//g;
  $contact = uc($fields[1]);
  $contact =~ s/[^ABCDEFGHIJKLMNOPQRSTUVWXYZ[:digit:]]//g;
  if ($contact eq '') {
    print ERROR "Bad contact code in contact default file: $type $contact\n";
  }
  elsif (!defined $contacts{$contact}) {
    print ERROR "Undefined contact code in contact default file: $type $contact\n";
  }
  elsif ($type eq '') {
    print ERROR "Bad rating type in contact default file: $type $contact\n";
  }
  elsif (!defined $rtype{$type}) {
    print ERROR "Undefined rating type in contact default file: $type $contact\n";
  }
  else {
    $cdef{$type} = $contact;
  }
}
close(CDEF);
foreach $type (sort keys %rtype) {
  if (!defined $cdef{$type}) {
    print ERROR "No default contact for rating type: $type\n";
  }
}

# Get contact list for games that aren't assigned by rating type.
if (!open(CEXC, 'contactexc.tsv')) {
  print ERROR "Couldn't open contact exception file.\n";
  exit;
}
while (<CEXC>) {
  chop;
  @fields = split("\t");
  $game = uc($fields[0]);
  $game =~ s/[^ABCDEFGHIJKLMNOPQRSTUVWXYZ[:digit:]]//g;
  $contact = uc($fields[1]);
  $contact =~ s/[^ABCDEFGHIJKLMNOPQRSTUVWXYZ[:digit:]]//g;
  if ($game eq '') {
    print ERROR "Bad game code in contact exception list: $game $contact\n";
  }
  elsif ($contact eq '') {
    print ERROR "Bad contact code in contact exception list: $game $contact\n";
  }
  elsif (!defined $contacts{$contact}) {
    print ERROR "Undefined contact code in contact exception list: $game $contact\n";
  }
  else {
    $cexc{$game} = $contact;
  }
}
close (CEXC);

# Logically it makes more sense to get the WBC version before the
# exception list so that overriding stuff manually is easier.
# However I also put a safeguard in by how duplicates are handled.

# Get WBC events from the game list web page's parsed output

if (!open(EVENT, 'wbcevent.tsv')) {
  print ERROR "Couldn't open WBC event file.\n";
  exit;
}
while (<EVENT>) {
  chop;
  @fields = split("\t");
  if (defined($wbcevent{$fields[0]})) {
    print ERROR "Duplicate WBC event \"$fields[0]\" in WBC event file.\n";
  }
  else {
    $wbcevent{$fields[0]} = $fields[1];
    $wbcurl{$fields[0]} = $fields[2];
  }
}
close(EVENT);

# Get WBC events from the manual list (old forgotten events?)

if (!open(WBCEXC, 'wbcexc.tsv')) {
  print ERROR "Couldn't open WBC exception file.\n";
  exit;
}
while (<WBCEXC>) {
  chop;
  @fields = split("\t");
  if (defined($wbcevent{$fields[0]})) {
    print ERROR "Duplicate WBC event \"$fields[0]\" in WBC exception file.\n";
  }
  $wbcevent{$fields[0]} = $fields[1];
  $wbcurl{$fields[0]} = $fields[2];
}
close(WBCEXC);

# Get WBC code to game code map

if (!open(WBC, 'wbc.tsv')) {
  print ERROR "Couldn't open WBC code file.\n";
  exit;
}
while (<WBC>) {
  chop;
  @fields = split("\t");
  if (!defined($wbcevent{$fields[0]})) {
    print ERROR "Added missing WBC event \"$fields[0]\" found in code file.\n";
    $wbcevent{$fields[0]} = '';
    $wbcurl{$fields[0]} = '';
  }
  if (!defined($wbc{$fields[0]})) {
    $wbc{$fields[0]} = [];
  }
  push @{$wbc{$fields[0]}}, $fields[1];
}
close(WBC);

# Report any WBC events that don't have any corresponding rated games
foreach $event (sort keys %wbcevent) {
  next if defined ($wbc{$event});
  print ERROR "WBC event \"$event\" has no corresponding rated games.\n";
}

# Get game definitions matching WBC codes, that we don't have data for.

if (!open(WBCNEW, 'wbcnew.tsv')) {
  print ERROR "Couldn't open the new wbc game file.\n";
  exit;
}
while (<WBCNEW>) {
  chop;
  @fields = split("\t");
  $gameid = $fields[0];
  $gameid =~ s/[^ABCDEFGHIJKLMNOPQRSTUVWXYZ[:digit:]]//g;
  if (! defined($gametype{$gameid})) {
    $gametype{$gameid} = 'UNKNOWN';
    print ERROR "No game type for $gameid, UNKNOWN used.\n";
  }
  undef $defcon;
  if (defined $sol{$gameid}) {
    $gsol{$gameid} = 'true';
    if (defined $csol) {
      $defcon = $csol;
    }
  }
  else {
    $gsol{$gameid} = 'false';
  }
  if ((!defined $defcon) and defined $cdef{$gametype{$gameid}}) {
    $defcon = $cdef{$gametype{$gameid}};
  }
  if (defined $cexc{$gameid}) {
    $gamecont{$gameid} = $cexc{$gameid};
    if ($defcon eq $cexc{$gameid}) {
      print ERROR "Unneeded contact exception for: $gameid\n";
    }
  }
  elsif (defined $defcon) {
    $gamecont{$gameid} = $defcon;
  }
  else {
    $gamecont{$gameid} = 'AREA';
    print ERROR "Unable to figure out a contact ID for $gameid, used AREA.\n";
  }
  $title{$gameid} = $fields[1];
  $pub{$gameid} = $fields[2];
  $titledup = lc($fields[1]);
  $titledup =~ s/[^abcdefghijklmnopqrstuvwxyz[:digit:]]//g;
  if (defined($titledup{$titledup})) {
    print ERROR "Possible duplicate game in wbcnew definition:\n";
    print ERROR "  $titledup{$titledup}, $fields[0], $fields[1]\n";
  }
  else {
    $titledup{$titledup} = $gameid;
  }
}
close(WBCNEW);

# Get list of urls for games

if (!open(URL, 'gameurl.tsv')) {
  print ERROR "Couldn't open game URL file.\n";
  exit;
}
while (<URL>) {
  chop;
  @fields = split("\t");
  if (defined($gameurl{"$fields[0]/$fields[1]"})) {
    print ERROR "Duplicate gameurl for game $fields[0], URL $fields[1].\n";
  }
  $date =$fields[3];
  if ($date =~ m/^\s*$/) {
    $date = 'infinity';
  }
  elsif ($date !~ m;^\s*(\d+/\d+/\d+(\s.*)?)?$; &&
     $date !~ m;^\s*(\d+-\d+-\d+(\s.*)?)?$; &&
     $date !~ m;^\s*(\d+-[ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz]+-\d+(\s.*)?)?$; &&
     $date !~ m;^\s*([ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz]+ \d+,? \d+(\s.*)?)?$;) {
     print ERROR "Bad date, \"$date\", for game: $fields[0] URL $fields[1].\n";
     $date = 'infinity';
  }
  $gameurl{"$fields[0]/$fields[1]"} = $fields[2];
  $gameurle{"$fields[0]/$fields[1]"} = $date;
}
close(URL);

# Where the text version of the excel sheets reside

if (!opendir(DIR, 'data')) {
  print ERROR "Couldn't open data directory.\n";
  exit;
}
@files = sort(readdir(DIR));
closedir(DIR);

# Canonical list of player names, but it may not be up to date. Give data
# here preference over that found in game files.

$master = 'The Master ID File';
if (open(MASTER, "<master.tsv") || open(MASTER, "/usr/local/bin/gzcat master.tsv.gz|")) {
  while (<MASTER>) {
    last if m/^id/i;
  }
  while (<MASTER>) {
    next if m/^\s*$/;
    @fields = split /\t/;
    $areaid = $fields[0];
    $fmname = $fields[1];
    $lname = $fields[2];
    $aname = '';
    $gen = '';
    $genlab = '';
    $lname =~ s/\([^)]*\)//g;
    $lname =~ s/[^-[:alpha:]' ]//g;
    $lname =~ s/^[-' ]+//g;
    $lname =~ s/[-' ]+$//g;
    $lname =~ s/([-' ])[-' ]+/$1/g;
    if ($lname =~ s/ SR$//i) {
      $gen = '1';
      $genlab = 'Sr';
    }
    elsif ($lname =~ s/ I$//i) {
      $gen = '1';
    }
    elsif ($lname =~ s/ JR$//i) {
      $gen = '2';
      $genlab = 'Jr';
    }
    elsif ($lname =~ s/ II$//i) {
      $gen = '2';
    }
    elsif ($lname =~ s/ III$//i) {
      $gen = '3';
    }
    elsif ($lname =~ s/ IV$//i) {
      $gen = '4';
    }
    elsif ($lname =~ s/ V$//i) {
      $gen = '5';
    }
    if ($lname eq '') {
      print ERROR "Bad name in master file: $fields[0] ($fields[1] $fields[2])\n";
      $lname = 'Missing Last Name';
    }
    if ($fmname =~ m/\(([^)]*)\)/) {
      $aname = $fmname;
      $fmname = $1;
    }
    $fmname =~ s/\([^)]*\)//g;
    $fmname =~ s/[^-[:alpha:]' ]//g;
    $fmname =~ s/^[-' ]+//g;
    $fmname =~ s/[-' ]+$//g;
    $fmname =~ s/([-' ])[-' ]+/$1/g;
    $aname =~ s/\([^)]*\)//g;
    $aname =~ s/[^-[:alpha:]' ]//g;
    $aname =~ s/^[-' ]+//g;
    $aname =~ s/[-' ]+$//g;
    $aname =~ s/([-' ])[-' ]+/$1/g;
    $areaid =~ s/[^-ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz[:digit:].]//g;
    if ($areaid =~ m/^\d+(\.(\d\d?)?)?$/) {
      if ($areaid =~ m/\.0+$/) {
        print ERROR "Non-standard version of AREA ID \"$fields[0]\" ($fields[1] $fields[2]) in masterfile.\n";
      }
      $areaid = sprintf('%08.2f', $areaid);
      $areaid =~ s/\.00$//;
    }
    if ($areaid !~ m/^[ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz[:digit:]]+(\.[0-9]{2,})?$/ || $areaid =~ m/^\d+\.\d{3,3}$/) {
      print ERROR "Bad AREA ID \"$fields[0]\" ($fields[1] $fields[2]) in master file.\n";
      next;
    }
    $displayid{uc($areaid)} = $areaid;
    $areaid = uc($areaid);
    $lname{$areaid} = $lname;
    $fmname{$areaid} = $fmname;
    $aname{$areaid} = $aname;
    $gen{$areaid} = $gen;
    $genlab{$areaid} = $genlab;
    if (!defined($chkid{$areaid})) {
      $chkid{$areaid} = {};
    }
    else {
      print ERROR "Duplicate master file entry for $areaid.\n";
    }
    $chkid{$areaid}{"$lname,$gen,$genlab,$fmname,$aname"} .= ' Master';
    if (!defined($chkname{"$lname,$gen,$genlab,$fmname,$aname"})) {
      $chkname{"$lname,$gen,$genlab,$fmname,$aname"} = {};
    }
    $chkname{"$lname,$gen,$genlab,$fmname,$aname"}{$areaid} .= ' Master';
    $master{$areaid} = "$lname,$gen,$genlab,$fmname,$aname";
  }
  close(MASTER);
}

foreach $file (@files) {
  $pub = '';
  $title = '';
  $state = 1;
  if ($file =~ m/\.tsv$/) {
    open(GAME, "<data/$file") || next;
  }
  elsif ($file =~ m/^[-_abcdefghijklmnopqrstuvwxyz[:digit:]]+\.tsv\.gz$/) {
    open(GAME, "/usr/local/bin/gzcat data/$file|") || next;
  }
  else {
    next;
  }
  $gameid = $file;
  $gameid =~ s/\.[^.]*$//;
  $gameid = uc($gameid);
  $gameid =~ s/[^ABCDEFGHIJKLMNOPQRSTUVWXYZ[:digit:]]//g;
  if (! defined($gametype{$gameid})) {
    $gametype{$gameid} = 'UNKNOWN';
    print ERROR "No game type for $gameid, UNKNOWN used.\n";
  }
  undef $defcon;
  if (defined $sol{$gameid}) {
    $gsol{$gameid} = 'true';
    if (defined $csol) {
      $defcon = $csol;
    }
  }
  else {
    $gsol{$gameid} = 'false';
  }
  if ((!defined $defcon) and defined $cdef{$gametype{$gameid}}) {
    $defcon = $cdef{$gametype{$gameid}};
  }
  if (defined $cexc{$gameid}) {
    $gamecont{$gameid} = $cexc{$gameid};
    if ($defcon eq $cexc{$gameid}) {
      print ERROR "Unneeded contact exception for: $gameid\n";
    }
  }
  elsif (defined $defcon) {
    $gamecont{$gameid} = $defcon;
  }
  else {
    $gamecont{$gameid} = 'AREA';
    print ERROR "Unable to figure out a contact ID for $gameid, used AREA.\n";
  }
  if (!open(GAMEERROR,">errors/$gameid.$gamecont{$gameid}")) {
    print ERROR "Unable to open per game error report file for $gameid.\n";
    next;
  }
  $errorflag = 0;
  $type = 0;
  while (<GAME>) {
    chop;
    s/\r//g;
    m;^\s*$; && next;
    $_ .= ' ';
    if ($title eq '') {
      m;^\d*\s*$; && next;
      m;^\s*\d+\t\d+\t\d+\s*$; && next;
      m;^\s*(\d+/\d+/\d+(\s.*)?)?$; && next;
      m;^\s*(\d+-\d+-\d+(\s.*)?)?$; && next;
      m;^\s*([[:alpha:]]+ \d+,? \d+(\s.*)?)?$; && next;
      m;^\s*http:; && next;
    }
    @fields = split /\t/;
    if ($state != 1) {
      if ($fields[0] =~ m/^r(an)?k$/i && $fields[14] =~ m/^a/i) {
        $type = 1;
        print ERROR "$gameid has spread sheet format type $type\n";
        print GAMEERROR "$gameid has spread sheet format type $type\n";
        $errorflag = 1;
      }
      elsif ($fields[0] =~ m/^r(an)?k$/i && $fields[9] =~ m/^a/i) {
        $type = 2;
        print ERROR "$gameid has spread sheet format type $type\n";
        print GAMEERROR "$gameid has spread sheet format type $type\n";
        $errorflag = 1;
      }
      elsif (($fields[0] =~ m/^r(an)?k$/i || $fields[1] =~ m/^id$/i) && $fields[10] =~ m/^a/i) {
        $type = 3;
#        print ERROR "$gameid has spread sheet format type $type\n";
#        print GAMEERROR "$gameid has spread sheet format type $type\n";
      }
      elsif ($fields[0] =~ m/^r(an)?k$/i || $fields[1] =~ m/^id$/i) {
        print ERROR "$gameid has spread sheet format type $type\n";
        print GAMEERROR "$gameid has spread sheet format type $type\n";
      }
      splice(@fields, 5, 5) if (($#fields >= 16 && $type == 0) || $type == 1);
      if ($type == 3) {
        $temp = $fields[9];
        $fields[9] = $fields[10];
        $fields[10] = $temp;
      }
    }
    if ($state == 1) {
      $title = $fields[1];
      $title =~ s/^\s+//;
      $title =~ s/\s+$//;
      $title =~ s/\s\s+/ /g;
      $title =~ s/^" *([^ ](.*[^ ])?) *"$/$1/;
      $gdate = $fields[3] . $fields[4] . $fields[5];
      $gdate =~ s/"//g;
      $gdate =~ s/^\s+//;
      $gdate =~ s/\s+$//;
      $gdate =~ s/\s\s+/ /g;
      if ($gdate =~ m/^\d\d\d\d\d(\.00)?$/) {
        ($year, $month, $day) = inverse_julian_day($gdate + $day1);
        $gdate = sprintf('%.4d-%.2d-%.2d', $year, $month, $day);
      }
      $state = 5;
    }
    elsif ($state == 4 && ($fields[0] =~ m/^r(an)?k$/i ||
      $fields[1] =~ m/^id$/i)) {
    }
    elsif ($state != 4 && ($fields[0] =~ m/^r(an)?k$/i ||
      $fields[1] =~ m/^id$/i)) {
      if (defined($title{$gameid})) {
        print ERROR "Duplicate game ID, \"$gameid\". Check wbc[new].tsv.\n";
      }
      $title{$gameid} = $title;
      $titledup = lc($title);
      $titledup =~ s/[^a-z0-9]//g;
      if (defined($titledup{$titledup})) {
        print ERROR "Possible duplicate game in $gameid definition:\n";
        print ERROR "  $titledup{$titledup}, $gameid, $title\n";
      }
      else {
        $titledup{$titledup} = $gameid;
      }
      if ($gdate !~ m;^\s*(\d+/\d+/\d+(\s.*)?)?$; &&
        $gdate !~ m;^\s*(\d+-\d+-\d+(\s.*)?)?$; &&
        $gdate !~ m;^\s*(\d+-[ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz]+-\d+(\s.*)?)?$; &&
        $gdate !~ m;^\s*([ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz]+ \d+,? \d+(\s.*)?)?$;) {
        print ERROR "Bad date, \"$gdate\", for game ($gameid) $title.\n";
        print GAMEERROR "Bad date, \"$gdate\", for game ($gameid) $title.\n";
        $errorflag = 1;
        $gdate = '';
      }
      $gdate{$gameid} = $gdate;
      if ($gdate =~ m/^\s*$/) {
        print ERROR "No date for game ($gameid) $title.\n";
        print GAMEERROR "No date for game ($gameid) $title.\n";
        $errorflag = 1;
        $gdate = '';
      }
      $pub{$gameid} = $pub;
      if ($pub eq '') {
        print ERROR "No publisher for game ($gameid) $title.\n";
        print GAMEERROR "No publisher for game ($gameid) $title.\n";
        $errorflag = 1;
      }
      elsif (!defined($pubalias{$pub})) {
        print ERROR "Unknown publisher \"$pub\" for game ($gameid) $title.\n";
        print GAMEERROR "Unknown publisher \"$pub\" for game ($gameid) $title.\n";
        $errorflag = 1;
      }
      $state = 4;
    }
    elsif ($fields[1] =~ m/^"?inactive/i && $state != 2 && $state != 5) {
      $state = 4;
    }
    elsif ($fields[1] =~ m/^"?other/i && $state != 2 && $state != 5) {
      $state = 4;
    }
    elsif ($fields[1] =~ m/^"?no games/i && $state != 2 && $state != 5) {
      $state = 4;
    }
    elsif ($state == 4) {
      $state = 3;
    }
    elsif ($state == 2) {
# Do nothing
    }
    elsif ($state == 5) {
      if ($fields[2] =~ /total/i) {
        $state = 2;
      }
      else {
        if ($gdate !~ m;^\s*(\d+/\d+/\d+(\s.*)?)?$; &&
          $gdate !~ m;^\s*(\d+-\d+-\d+(\s.*)?)?$; &&
          $gdate !~ m;^\s*(\d+-[ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz]+-\d+(\s.*)?)?$; &&
          $gdate !~ m;^\s*([ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz]+ \d+,? \d+(\s.*)?)?$; &&
          $fields[3] . $fields[4] . $fields[5] ne '') {
          $gdate = '';
	}
        if ($gdate eq '') {
          $gdate = $fields[3] . $fields[4] . $fields[5];
          $gdate =~ s/"//g;
          $gdate =~ s/^\s+//;
          $gdate =~ s/\s+$//;
          $gdate =~ s/\s\s+/ /g;
          if ($gdate =~ m/^\d\d\d\d\d(\.00)?$/) {
            ($year, $month, $day) = inverse_julian_day($gdate + $day1);
            $gdate = sprintf('%.4d-%.2d-%.2d', $year, $month, $day);
          }
        }
        if ($pub eq '') {
          $pub = $fields[1] . $fields[2];
          $pub =~ s/[^[:print:]]//g;
          $pub =~ s/^\s+//;
          $pub =~ s/\s+$//;
          $pub =~ s/\s\s+/ /g;
        }
      }
    }
    if ($state == 3) {
      $areaid = $fields[1];
      $fmname = $fields[2];
      $lname = $fields[3];
      $aname = '';
      $gen = '';
      $genlab = '';
      $lname =~ s/\([^)]*\)//g;
      $lname =~ s/[^-[:alpha:]' ]//g;
      $lname =~ s/^[-' ]+//g;
      $lname =~ s/[-' ]+$//g;
      $lname =~ s/([-' ])[-' ]+/$1/g;
      if ($lname =~ s/ SR$//i) {
        $gen = '1';
        $genlab = 'Sr';
      }
      if ($lname =~ s/ I$//i) {
        $gen = '1';
      }
      elsif ($lname =~ s/ JR$//i) {
        $gen = '2';
        $genlab = 'Jr';
      }
      elsif ($lname =~ s/ II$//i) {
        $gen = '2';
      }
      elsif ($lname =~ s/ III$//i) {
        $gen = '3';
      }
      elsif ($lname =~ s/ IV$//i) {
        $gen = '4';
      }
      elsif ($lname =~ s/ V$//i) {
        $gen = '5';
      }
      if ($lname eq '') {
        print ERROR "Bad name in ($gameid) $title: $fields[1] ($fields[2] $fields[3])\n";
        print GAMEERROR "Bad name in ($gameid) $title: $fields[1] ($fields[2] $fields[3])\n";
        $errorflag = 1;
        $lname = 'Missing Last Name';
      }
      if ($fmname =~ m/\(([^)]*)\)/) {
        $aname = $fmname;
        $fmname = $1;
      }
      $fmname =~ s/\([^)]*\)//g;
      $fmname =~ s/[^-[:alpha:]' ]//g;
      $fmname =~ s/^[-' ]+//g;
      $fmname =~ s/[-' ]+$//g;
      $fmname =~ s/([-' ])[-' ]+/$1/g;
      $aname =~ s/\([^)]*\)//g;
      $aname =~ s/[^-[:alpha:]' ]//g;
      $aname =~ s/^[-' ]+//g;
      $aname =~ s/[-' ]+$//g;
      $aname =~ s/([-' ])[-' ]+/$1/g;
      $areaid =~ s/[^-ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz[:digit:].]//g;
      if ($areaid =~ m/^\d+(\.(\d\d?)?)?$/) {
        if ($areaid =~ m/\.0+$/) {
          print ERROR "Non-standard version of AREA ID in game ($gameid) $title, \"$fields[1]\" ($fields[2] $fields[3]).\n";
          print GAMEERROR "Non-standard version of AREA ID in game ($gameid) $title, \"$fields[1]\" ($fields[2] $fields[3]).\n";
          $errorflag = 1;
        }
        $areaid = sprintf('%08.2f', $areaid);
        $areaid =~ s/\.00$//;
      }
      if ($areaid !~ m/^[-ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz[:digit:]]+(\.[0-9]{2,})?$/ || $areaid =~ m/^\d+\.\d{3,3}$/) {
        print ERROR "Bad AREA ID in game ($gameid) $title, \"$fields[1]\" ($fields[2] $fields[3]).\n";
        print GAMEERROR "Bad AREA ID in game ($gameid) $title, \"$fields[1]\" ($fields[2] $fields[3]).\n";
        $errorflag = 1;
        next;
      }
      if (!defined($displayid{uc($areaid)})) {
        $displayid{uc($areaid)} = $areaid;
        $areaid = uc($areaid);
        $lname{$areaid} = $lname;
        $fmname{$areaid} = $fmname;
        $aname{$areaid} = $aname;
        $gen{$areaid} = $gen;
        $genlab{$areaid} = $genlab;
      }
      else {
        $areaid = uc($areaid);
      }
      if (!defined($chkid{$areaid})) {
        $chkid{$areaid} = {};
      }
      $chkid{$areaid}{"$lname,$gen,$genlab,$fmname,$aname"} .= " \"$title\"";
      if (!defined($chkname{"$lname,$gen,$genlab,$fmname,$aname"})) {
        $chkname{"$lname,$gen,$genlab,$fmname,$aname"} = {};
      }
      $chkname{"$lname,$gen,$genlab,$fmname,$aname"}{$areaid} .= " \"$title\"";
      if (defined($master{$areaid})) {
        if ($master{$areaid} ne "$lname,$gen,$genlab,$fmname,$aname") {
          print GAMEERROR "$gameid,$areaid,$lname,$gen,$genlab,$fmname,$aname: Doesn't match master file\n";
          print GAMEERROR "$gameid,$areaid,$master{$areaid}: Master file contents\n";
          $errorflag = 1;
        }
      }
      else {
        print GAMEERROR "$gameid,$areaid,$lname,$gen,$genlab,$fmname,$aname: Not in master file\n";
        $errorflag = 1;
      }
      $rate = $fields[4];
      $frq = $fields[5];
      $opp = $fields[6];
      $rmp = $fields[7];
      $trn = $fields[8];
      $pdate = $fields[9];
      $rmc = $fields[10];
      $gm = $fields[11] . ',' . $fields[12] . ',' . $fields[13] . ',' . $fields[14];
      $rate =~ s/\s//g;
      $rate =~ s/[^0-9].*//;
      $frq =~ s/\s//g;
      $frq =~ s/[^0-9].*//;
      $opp =~ s/\s//g;
      $opp =~ s/[^0-9].*//;
      $rmp =~ s/\s//g;
      $rmp =~ s/[^0-9].*//;
      $trn =~ s/[^0-9].*//;
      $trn =~ s/\s//g;
      $rmc =~ s/\s//g;
      $gm =~ s/['"]//g;
      $gm =~ s/\s//g;
      $gm =~ s/^,+//g;
      $gm =~ s/,+$//g;
      $pdate =~ s/"//g;
      $pdate =~ s/^\s+//;
      $pdate =~ s/\s+$//;
      $pdate =~ s/\s\s+/ /g;
      if ($pdate =~ m/^\d\d\d\d\d(\.00)?$/) {
        ($year, $month, $day) = inverse_julian_day($pdate + $day1);
        $pdate = sprintf('%.4d-%.2d-%.2d', $year, $month, $day);
      }
      $pdate = '' unless $pdate =~ m;^\s*(\d+/\d+/\d+(\s.*)?)?$; ||
        $pdate =~ m;^\s*(\d+-\d+-\d+(\s.*)?)?$; ||
        $pdate =~ m;^\s*(\d+-[-ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz]+-\d+(\s.*)?)?$; ||
        $pdate =~ m;^\s*([-ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz]+ \d+,? \d+(\s.*)?)?$;;
      if ($pdate eq '') {
        print ERROR "Bad date in game ($gameid) $title, \"$fields[9]\" for ID \"$areaid\"\n";
        print GAMEERROR "Bad date in game ($gameid) $title, \"$fields[9]\" for ID \"$areaid\"\n";
        $errorflag = 1;
      }
# Currently can only check ISO and MM/DD/YY dates for being more than 12 years old
      if ($pdate =~ m;^(\d{4})-(\d{2})-(\d{2})$;) {
        if ($today > 4383 + julian_day($1, $2, $3)) {
          print ERROR "Twelve year plus old entry in game ($gameid) $title, \"$fields[9]\" for ID \"$areaid\"\n";
          print GAMEERROR "Twelve year plus old entry in game ($gameid) $title, \"$fields[9]\" for ID \"$areaid\"\n";
          $errorflag = 1;
        }
      }
      if ($pdate =~ m;^(\d{2})/(\d{2})/(\d{2})$;) {
        $year = $3 + 1900;
        $year += 100 if $year < 1950;
        if ($today > 4383 + julian_day($year, $1, $2)) {
          print ERROR "Twelve year plus old entry in game ($gameid) $title, \"$fields[9]\" for ID \"$areaid\"\n";
          print GAMEERROR "Twelve year plus old entry in game ($gameid) $title, \"$fields[9]\" for ID \"$areaid\"\n";
          $errorflag = 1;
        }
      }
      if ($frq == 0 and $rate != 5000 and $rate ne '') {
        $frq = 1;
        print ERROR "Zero frequency for rated player in game ($gameid) $title, $rate for ID \"$areaid\"\n";
        print GAMEERROR "Zero frequency for rated player in game ($gameid) $title, $rate for ID \"$areaid\"\n";
        $errorflag = 1;
      }
      if ($rmc eq '' || $rmc eq 's' || $rmc eq 'e' || $rmc eq 'es' || $rmc eq 'se') {
        $rmc = 0;
      }
      elsif ($rmc !~ m/^\d+$/) {
        print ERROR "Bad rmc value \"$rmc\" in game ($gameid) $title, $rate for ID \"$areaid\"\n";
        print GAMEERROR "Bad rmc value \"$rmc\" in game ($gameid) $title, $rate for ID \"$areaid\"\n";
        $errorflag = 1;
        $rmc = 0;
      }
      if ($frq == 0 and ($rate == 5000 or $rate eq '')) {
        print ERROR "Deprecated other interested entry ignored for player in game ($gameid) $title, $rate for ID \"$areaid\"\n";
        print GAMEERROR "Deprecated other interested entry ignored for player in game ($gameid) $title, $rate for ID \"$areaid\"\n";
        $errorflag = 1;
      }
      else {
        $pdate = $gdate unless $pdate ne '';
        if (!defined($rate{"$gameid/$areaid"})) {
          $rate{"$gameid/$areaid"} = $rate;
          $frq{"$gameid/$areaid"} = $frq;
          $opp{"$gameid/$areaid"} = $opp;
          $rmp{"$gameid/$areaid"} = $rmp;
          $trn{"$gameid/$areaid"} = $trn;
          $rmc{"$gameid/$areaid"} = $rmc;
          $gm{"$gameid/$areaid"} = $gm;
          $pdate{"$gameid/$areaid"} = $pdate;
        }
        else {
          print ERROR "Duplicate AREA ID, $areaid in game ($gameid) $title.\n";
          print GAMEERROR "Duplicate AREA ID, $areaid in game ($gameid) $title.\n";
          $errorflag = 1;
        }
      }
    }
  }
  if ($gameid eq '') {
    print ERROR "No gameid generated for file \"$file\"\n";
    print GAMEERROR "No gameid generated for file \"$file\"\n";
    $errorflag = 1;
  }
  if ($state == 1) {
    print ERROR "No ranked players found in game file \"$file\"\n";
    print GAMEERROR "No ranked players found in game file \"$file\"\n";
    $errorflag = 1;
  }
  close(GAME);
  close(GAMEERROR);
  if (!$errorflag) {
    unlink "errors/$gameid.$gamecont{$gameid}";
  }
}

# Search for game types for undefined games
foreach $gameid (sort keys %gametype) {
  if (! defined $title{$gameid}) {
    print ERROR "Game rating type $gametype{$gameid} defined for nonexistant game: $gameid.\n";
  }
}

# Search for contact exceptions for undefined games
foreach $gameid (sort keys %cexc) {
  if (! defined $title{$gameid}) {
    print ERROR "Game contact $cexc{$gameid} defined for nonexistant game: $gameid.\n";
  }
}

# Find undefined solitaire games
foreach $game (keys %sol) {
  if (!defined $title{$game}) {
    print ERROR "Unknown game in solitaire list: $game\n";
  }
}

# Get a list of other interested players
if (!open(INTEREST, 'interest.tsv')) {
  print ERROR "Couldn't open other interested player file.\n";
  exit;
}
while (<INTEREST>) {
  chop;
  @fields = split("\t");
  if (scalar(@fields) != 4) {
    print ERROR "Bad number of fields in other interested players: $_\n";
    next;
  }
  $areaid = $fields[0];
  $gameid = $fields[1];
  $rate = 5000;
  $frq = 0;
  $opp = 0;
  $rmp = 0;
  $trn = 0;
  $rmc = 0;
  $gm = $fields[2];
  $pdate = $fields[3];
  if (!defined($title{$gameid})) {
    print ERROR "Bad gameid '$gameid' in other interested players.\n";
  }
  elsif (!defined($lname{$areaid})) {
    print ERROR "Bad areaid '$areaid' in other interested players.\n";
  }
  elsif ($pdate !~ m/^\d\d\d\d-\d\d-\d\d$/) {
    print ERROR "Bad date '$pdate' in other interested data.\n";
  }
  elsif (defined($rate{"$gameid/$areaid"})) {
    print ERROR "Other intested player '$areaid' is already rated in '$gameid'.\n";
  }
  else {
    $rate{"$gameid/$areaid"} = $rate;
    $frq{"$gameid/$areaid"} = $frq;
    $opp{"$gameid/$areaid"} = $opp;
    $rmp{"$gameid/$areaid"} = $rmp;
    $trn{"$gameid/$areaid"} = $trn;
    $rmc{"$gameid/$areaid"} = $rmc;
    $gm{"$gameid/$areaid"} = $gm;
    $pdate{"$gameid/$areaid"} = $pdate;
  }
}
close (INTEREST);

$result = $conn->exec("begin");
if ($result->resultStatus != PGRES_COMMAND_OK) {
  print "Unable to begin a transaction.\n";
  print $conn->errorMessage, "\n";
  exit(0);
}

$result = $conn->exec("delete from crate");
if ($result->resultStatus != PGRES_COMMAND_OK) {
  print "Unable to delete crate.\n";
  print $conn->errorMessage, "\n";
  $result = $conn->exec("rollback");
  exit(0);
}

$result = $conn->exec("delete from cname");
if ($result->resultStatus != PGRES_COMMAND_OK) {
  print "Unable to delete cname.\n";
  print $conn->errorMessage, "\n";
  $result = $conn->exec("rollback");
  exit(0);
}

$result = $conn->exec("delete from wbcgames");
if ($result->resultStatus != PGRES_COMMAND_OK) {
  print "Unable to delete wbc.\n";
  print $conn->errorMessage, "\n";
  $result = $conn->exec("rollback");
  exit(0);
}

$result = $conn->exec("delete from wbc");
if ($result->resultStatus != PGRES_COMMAND_OK) {
  print "Unable to delete wbc.\n";
  print $conn->errorMessage, "\n";
  $result = $conn->exec("rollback");
  exit(0);
}

$result = $conn->exec("delete from gamepubs");
if ($result->resultStatus != PGRES_COMMAND_OK) {
  print "Unable to delete wbc.\n";
  print $conn->errorMessage, "\n";
  $result = $conn->exec("rollback");
  exit(0);
}

$result = $conn->exec("delete from gameurls");
if ($result->resultStatus != PGRES_COMMAND_OK) {
  print "Unable to delete gameurls.\n";
  print $conn->errorMessage, "\n";
  $result = $conn->exec("rollback");
  exit(0);
}

$result = $conn->exec("delete from games");
if ($result->resultStatus != PGRES_COMMAND_OK) {
  print "Unable to delete games.\n";
  print $conn->errorMessage, "\n";
  $result = $conn->exec("rollback");
  exit(0);
}

$result = $conn->exec("delete from contacts");
if ($result->resultStatus != PGRES_COMMAND_OK) {
  print "Unable to delete contacts.\n";
  print $conn->errorMessage, "\n";
  $result = $conn->exec("rollback");
  exit(0);
}

$result = $conn->exec("delete from rtypes");
if ($result->resultStatus != PGRES_COMMAND_OK) {
  print "Unable to delete rtypes.\n";
  print $conn->errorMessage, "\n";
  $result = $conn->exec("rollback");
  exit(0);
}

$result = $conn->exec("delete from publishers");
if ($result->resultStatus != PGRES_COMMAND_OK) {
  print "Unable to delete publishers.\n";
  print $conn->errorMessage, "\n";
  $result = $conn->exec("rollback");
  exit(0);
}

$result = $conn->exec("copy cname from stdin delimiters '\t' with null as ''");
foreach $areaid (keys %lname) {
  $priv = 'web';
  $priv = 'admin' if defined($anon{$areaid});
  $line = "$areaid\t$displayid{$areaid}\t$lname{$areaid}\t$fmname{$areaid}\t$aname{$areaid}\t$gen{$areaid}\t$genlab{$areaid}\t$priv\tnow\n";
  $line =~ s/\\/\\\\/g;
  if (($ret = $conn->putline($line)) != 0) {
    print "Unable to load the cname table (row-$ret).\n";
    print $conn->errorMessage, "\n";
    print $line;
    $result = $conn->exec("rollback");
    exit(0);
  }
}
if (($ret = $conn->putline("\\.\n")) != 0) {
  print "Unable to load the cname table (final-$ret).\n";
  print $conn->errorMessage, "\n";
  $result = $conn->exec("rollback");
  exit(0);
}
if (($ret = $conn->endcopy) != 0) {
  print "Unable to load the cname table (endcopy-$ret).\n";
  print $conn->errorMessage, "\n";
  $result = $conn->exec("rollback");
  exit(0);
}

$result = $conn->exec("copy publishers from stdin delimiters '\t' with null as ''");
foreach $pubid (keys %pubname) {
  $line = "$pubid\t$pubname{$pubid}\t$puburl{$pubid}\n";
  $line =~ s/\\/\\\\/g;
  if (($ret = $conn->putline($line)) != 0) {
    print "Unable to load the publishers table (row-$ret).\n";
    print $conn->errorMessage, "\n";
    print $line;
    $result = $conn->exec("rollback");
    exit(0);
  }
}
if (($ret = $conn->putline("\\.\n")) != 0) {
  print "Unable to load the publishers table (final-$ret).\n";
  print $conn->errorMessage, "\n";
  $result = $conn->exec("rollback");
  exit(0);
}
if (($ret = $conn->endcopy) != 0) {
  print "Unable to load the publishers table (endcopy-$ret).\n";
  print $conn->errorMessage, "\n";
  $result = $conn->exec("rollback");
  exit(0);
}

$result = $conn->exec("copy rtypes from stdin delimiters '\t' with null as ''");
foreach $type (keys %rtype) {
  $line = "$type\t$rtype{$type}\n";
  $line =~ s/\\/\\\\/g;
  if (($ret = $conn->putline($line)) != 0) {
    print "Unable to load the rtypes table (row-$ret).\n";
    print $conn->errorMessage, "\n";
    print $line;
    $result = $conn->exec("rollback");
    exit(0);
  }
}
if (($ret = $conn->putline("\\.\n")) != 0) {
  print "Unable to load the rtypes table (final-$ret).\n";
  print $conn->errorMessage, "\n";
  $result = $conn->exec("rollback");
  exit(0);
}
if (($ret = $conn->endcopy) != 0) {
  print "Unable to load the rtypes table (endcopy-$ret).\n";
  print $conn->errorMessage, "\n";
  $result = $conn->exec("rollback");
  exit(0);
}

$result = $conn->exec("copy contacts from stdin delimiters '\t' with null as ''");
foreach $contact (keys %contacts) {
  $line = "$contact\t$contacts{$contact}\n";
  $line =~ s/\\/\\\\/g;
  if (($ret = $conn->putline($line)) != 0) {
    print "Unable to load the contacts table (row-$ret).\n";
    print $conn->errorMessage, "\n";
    print $line;
    $result = $conn->exec("rollback");
    exit(0);
  }
}
if (($ret = $conn->putline("\\.\n")) != 0) {
  print "Unable to load the contacts table (final-$ret).\n";
  print $conn->errorMessage, "\n";
  $result = $conn->exec("rollback");
  exit(0);
}
if (($ret = $conn->endcopy) != 0) {
  print "Unable to load the contacts table (endcopy-$ret).\n";
  print $conn->errorMessage, "\n";
  $result = $conn->exec("rollback");
  exit(0);
}

$result = $conn->exec("copy games from stdin delimiters '\t' with null as ''");
foreach $gameid (keys %title) {
  $gdate{$gameid} = 'now' if $gdate{$gameid} eq '';
  $line = "$gameid\t$title{$gameid}\t$gametype{$gameid}\t$gsol{$gameid}\t$gamecont{$gameid}\t$gdate{$gameid}\n";
  $line =~ s/\\/\\\\/g;
  if (($ret = $conn->putline($line)) != 0) {
    print "Unable to load the games table (row-$ret).\n";
    print $conn->errorMessage, "\n";
    print $line;
    $result = $conn->exec("rollback");
    exit(0);
  }
}
if (($ret = $conn->putline("\\.\n")) != 0) {
  print "Unable to load the games table (final-$ret).\n";
  print $conn->errorMessage, "\n";
  $result = $conn->exec("rollback");
  exit(0);
}
if (($ret = $conn->endcopy) != 0) {
  print "Unable to load the games table (endcopy-$ret).\n";
  print $conn->errorMessage, "\n";
  $result = $conn->exec("rollback");
  exit(0);
}

$result = $conn->exec("copy gamepubs from stdin delimiters '\t' with null as ''");
foreach $gameid (keys %pub) {
  foreach $pubid (@{$pubalias{$pub{$gameid}}}) {
    $line = "$gameid\t$pubid\n";
    $line =~ s/\\/\\\\/g;
    if (($ret = $conn->putline($line)) != 0) {
      print "Unable to load the gamepubs table (row-$ret).\n";
      print $conn->errorMessage, "\n";
      print $line;
      $result = $conn->exec("rollback");
      exit(0);
    }
  }
}
if (($ret = $conn->putline("\\.\n")) != 0) {
  print "Unable to load the gamepubs table (final-$ret).\n";
  print $conn->errorMessage, "\n";
  $result = $conn->exec("rollback");
  exit(0);
}
if (($ret = $conn->endcopy) != 0) {
  print "Unable to load the gamepubs table (endcopy-$ret).\n";
  print $conn->errorMessage, "\n";
  $result = $conn->exec("rollback");
  exit(0);
}

$result = $conn->exec("copy wbc from stdin delimiters '\t' with null as ''");
foreach $event (keys %wbcevent) {
  $line = "$event\t$wbcevent{$event}\t$wbcurl{$event}\n";
  $line =~ s/\\/\\\\/g;
  if (($ret = $conn->putline($line)) != 0) {
    print "Unable to load the wbc table (row-$ret).\n";
    print $conn->errorMessage, "\n";
    print $line;
    $result = $conn->exec("rollback");
    exit(0);
  }
}
if (($ret = $conn->putline("\\.\n")) != 0) {
  print "Unable to load the wbc table (final-$ret).\n";
  print $conn->errorMessage, "\n";
  $result = $conn->exec("rollback");
  exit(0);
}
if (($ret = $conn->endcopy) != 0) {
  print "Unable to load the wbc table (endcopy-$ret).\n";
  print $conn->errorMessage, "\n";
  $result = $conn->exec("rollback");
  exit(0);
}

$result = $conn->exec("copy wbcgames from stdin delimiters '\t' with null as ''");
foreach $wbcid (keys %wbc) {
  foreach $gameid (@{$wbc{$wbcid}}) {
    next if $gameid eq '';
    if (!defined($title{$gameid})) {
      print ERROR "Unknown gameid $gameid for WBC code $wbcid.\n";
      next;
    }
    $line = "$wbcid\t$gameid\n";
    $line =~ s/\\/\\\\/g;
    if (($ret = $conn->putline($line)) != 0) {
      print "Unable to load the wbcgames table (row-$ret).\n";
      print $conn->errorMessage, "\n";
      print $line;
      $result = $conn->exec("rollback");
      exit(0);
    }
  }
}
if (($ret = $conn->putline("\\.\n")) != 0) {
  print "Unable to load the wbcgames table (final-$ret).\n";
  print $conn->errorMessage, "\n";
  $result = $conn->exec("rollback");
  exit(0);
}
if (($ret = $conn->endcopy) != 0) {
  print "Unable to load the wbcgames table (endcopy-$ret).\n";
  print $conn->errorMessage, "\n";
  $result = $conn->exec("rollback");
  exit(0);
}

$result = $conn->exec("copy gameurls from stdin delimiters '\t' with null as ''");
foreach $gameurl (keys %gameurl) {
  $gameurl =~ m%^([^/]+)/(.*)$%;
  $gameid = $1;
  $url = $2;
  if (!defined($title{$gameid})) {
    print ERROR "Unknown gameid $gameid for URL $url.\n";
    next;
  }
  $exp = $gameurle{$gameurl};
  $line = "$gameid\t$url\t$gameurl{$gameurl}\t$exp\n";
  $line =~ s/\\/\\\\/g;
  if (($ret = $conn->putline($line)) != 0) {
    print "Unable to load the gameurls table (row-$ret).\n";
    print $conn->errorMessage, "\n";
    print $line;
    $result = $conn->exec("rollback");
    exit(0);
  }
}
if (($ret = $conn->putline("\\.\n")) != 0) {
  print "Unable to load the gameurls table (final-$ret).\n";
  print $conn->errorMessage, "\n";
  $result = $conn->exec("rollback");
  exit(0);
}
if (($ret = $conn->endcopy) != 0) {
  print "Unable to load the gameurls table (endcopy-$ret).\n";
  print $conn->errorMessage, "\n";
  $result = $conn->exec("rollback");
  exit(0);
}

$result = $conn->exec("copy crate from stdin delimiters '\t' with null as ''");
foreach $crate (keys %rate) {
  next unless $crate =~ m%^([^/]+)/(.*)$%;
  $gameid = $1;
  $areaid = $2;
  $rate{$crate} = 5000 if $rate{$crate} eq '';
  $frq{$crate} = 0 if $frq{$crate} eq '';
  $opp{$crate} = 0 if $opp{$crate} eq '';
  $rmp{$crate} = 0 if $rmp{$crate} eq '';
  $trn{$crate} = 0 if $trn{$crate} eq '';
  $pdate{$crate} = 'now' if $pdate{$crate} eq '';
  $line = "$areaid\t$gameid\t$rate{$crate}\t$frq{$crate}\t$opp{$crate}\t$rmp{$crate}\t$trn{$crate}\t$rmc{$crate}\t$gm{$crate}\t$pdate{$crate}\n";
  $line =~ s/\\/\\\\/g;
  if (($ret = $conn->putline($line)) != 0) {
    print "Unable to load the crate table (row-$ret).\n";
    print $conn->errorMessage, "\n";
    print $line;
    $result = $conn->exec("rollback");
    exit(0);
  }
}
if (($ret = $conn->putline("\\.\n")) != 0) {
  print "Unable to load the crate table (final-$ret).\n";
  print $conn->errorMessage, "\n";
  $result = $conn->exec("rollback");
  exit(0);
}
if (($ret = $conn->endcopy) != 0) {
  print "Unable to load the crate table (endcopy-$ret).\n";
  print $conn->errorMessage, "\n";
  $result = $conn->exec("rollback");
  exit(0);
}

$result = $conn->exec("commit");
if ($result->resultStatus != PGRES_COMMAND_OK) {
  print "Unable to commit the transaction.\n";
  print $conn->errorMessage, "\n";
  $result = $conn->exec("rollback");
  exit(0);
}

print "Tables loaded.\n";

$result = $conn->exec("vacuum analyze");
if ($result->resultStatus != PGRES_COMMAND_OK) {
  print "Vacuum analyze didn't run correctly.\n";
  print $conn->errorMessage, "\n";
}

print "Vacuum Analyze completed.\n";

foreach $areaid (sort keys %anon) {
  if (!defined($lname{$areaid})) {
    print ERROR "Anonymous AREA ID, \"$areaid\", not in use.\n";
  }
}

$result = $conn->exec("select gameid, url, expires from gameurls where expires < 'now' order by gameid, url");
if ($result->resultStatus == PGRES_TUPLES_OK && $result->ntuples > 0) {
  while (@row = $result->fetchrow) {
    print ERROR "Expired url in data: $row[0], $row[1], $row[2]\n";
  }
}

foreach $areaid (sort keys %chkid) {
  if (scalar(keys(%{$chkid{$areaid}})) > 1) {
    print ERROR "Multiple names for AREA ID $areaid.\n";
    foreach $name (sort keys %{$chkid{$areaid}}) {
      print ERROR "  \"$name\"  In:$chkid{$areaid}{$name}\n";
    }
  }
}

foreach $name (sort keys %chkname) {
  if (scalar(keys(%{$chkname{$name}})) > 1) {
    print ERROR "Multiple AREA IDs for name \"$name\" (warning only).\n";
    foreach $areaid (sort keys %{$chkname{$name}}) {
      print ERROR "  $areaid  In:$chkname{$name}{$areaid}\n";
    }
  }
}

close(ERROR);

# Combine indiviual error reports into per maintainer reports.

$old = '';
$result = $conn->exec("select contact, gameid from games order by contact, gameid");
if ($result->resultStatus != PGRES_TUPLES_OK) {
  print "Unable to read game contact list.\n";
}
else {
  while (@row = $result->fetchrow) {
    if ($old ne $row[0]) {
      if (!open(CONTACT,">errors/all.$row[0]")) {
        print "Unable to open errors/all.$row[0].\n";
        next;
      }
      else {
        $old = $row[0];
      }
    }
    if (!open(GAMEERROR,"<errors/$row[1].$row[0]")) {
# Games that don't have sheets won't have error files.
# Games that don't have errors won't have error files.
      next;
    }
    while (<GAMEERROR>) {
      print CONTACT $_;
    }
    close (GAMEERROR);
  }
}
close(CONTACT);

print "See errors.txt for non-fatal errors report.\n";