#!/usr/bin/perl

# Display a wbc index of games
# Bruno Wolff III
# Last revised October 13, 2012

# Use unbuffered output as there are some long delays while doing queries

select(STDOUT);
$| = 1;

$" = '';

use Pg;

# Use this to clean stuff extracted from the database for html output
sub clean(@) {
  local $str = "@_";
  $str =~ s/&/&amp;/g;
  $str =~ s/</&lt;/g;
  $str =~ s/>/&gt;/g;
  $str =~ s/"/&quot;/g;
  return $str;
}

if ($ENV{REQUEST_METHOD} ne '' && $ENV{REQUEST_METHOD} ne 'GET' &&
  $ENV{REQUEST_METHOD} ne 'HEAD') {
  print <<"EOF";
content-type: text/html; charset=UTF-8
status: 501 Method not implemented

<html><head>
<title>501 Method Not Implemented</title>
</head><body>
<h1>Method Not Implemented</h1>
Method '$ENV{REQUEST_METHOD}' not implented for $ENV{REQUEST_URI}.
</body></html>
EOF
  exit;
}

print <<EOF;
content-type: text/html; charset=UTF-8

<html>
<head>
<title>AREA WBC Code Index</title>
</head>
<body>
EOF
{
    local *INPUT;
    if (open(INPUT, "links.html")) {
        print while (<INPUT>);
        close INPUT;
    }
} 
print << "EOF";
<h1><a name="_">AREA WBC Code Index</a></h1>
EOF

# First try to connect
$conn = Pg::connectdb('dbname=area');
if ($conn->status != PGRES_CONNECTION_OK) {
  print << "EOF";
Unable to connect the AREA database.
EOF
{
    local *INPUT;
    if (open(INPUT, "links.html")) {
        print while (<INPUT>);
        close INPUT;
    }
} 
{
    local *INPUT;
    if (open(INPUT, "sig.html")) {
        print while (<INPUT>);
        close INPUT;
    }
} 
print << "EOF";
</body>
</html>
EOF
  exit;
}

# Only one query is done so we don't need to set transaction isolation.

$result = $conn->exec("select code, event, url, wbcgames.gameid, title from wbc natural left join (wbcgames natural join games) order by code, lower(title), gameid");
if ($result->resultStatus != PGRES_TUPLES_OK) {
  print << "EOF";
Unable to access the games or wbc tables.
EOF
{
    local *INPUT;
    if (open(INPUT, "links.html")) {
        print while (<INPUT>);
        close INPUT;
    }
} 
{
    local *INPUT;
    if (open(INPUT, "sig.html")) {
        print while (<INPUT>);
        close INPUT;
    }
} 
print << "EOF";
</body>
</html>
EOF
  exit;
}
if ($result->ntuples <= 0) {
  print << "EOF";
No games were found.
EOF
{
    local *INPUT;
    if (open(INPUT, "links.html")) {
        print while (<INPUT>);
        close INPUT;
    }
} 
{
    local *INPUT;
    if (open(INPUT, "sig.html")) {
        print while (<INPUT>);
        close INPUT;
    }
} 
print << "EOF";
</body>
</html>
EOF
  exit;
}

print "<table><col span=\"3\">\n<thead><tr><th>WBC code<th>WBC Event<th>Game Title<tbody>\n";
while (@row = $result->fetchrow) {
  $code = clean($row[0]);
  $event = clean($row[1]);
  $url = clean($row[2]);
  $game = clean($row[3]);
  $title = clean($row[4]);
  if (defined($row[2])) {
    print "<tr><td><a href=\"$url\">$code</a><td>$event<td>";
  }
  else {
    print "<tr><td>$code<td>$event<td>";
  }
  if (defined($row[3])) {
    print "<a href=\"G_$game.html#_\">$title</a>";
  }
  print "\n";
}
print << "EOF";
</table>
EOF
{
    local *INPUT;
    if (open(INPUT, "links.html")) {
        print while (<INPUT>);
        close INPUT;
    }
} 
{
    local *INPUT;
    if (open(INPUT, "sig.html")) {
        print while (<INPUT>);
        close INPUT;
    }
}
print << "EOF";
</body>
</html>
EOF
