#!/usr/bin/env perl
use CGI qw/:standard/;
use CGI::Carp qw/fatalsToBrowser warningsToBrowser/;
use Config::Simple;
use POSIX qw/floor strftime/;
use DateTime;
use DBD::mysql;
use Time::HiRes qw/gettimeofday tv_interval/;
use Cwd qw/abs_path/;
use HTML::Entities;
use File::Basename;

my $t0 = [gettimeofday];

use strict;
use warnings;
binmode STDOUT, ":encoding(UTF-8)";

my $CONFIGFILE = "/home/alucard/riftfirst/ritem.conf";
my $cfg = new Config::Simple($CONFIGFILE) or die "Failed to read $CONFIGFILE. $!\n";
my ($dsn, $dbh, $sth);

my $html = new CGI;
print $html->header();
print $html->start_html(
    -title => "Rift Shard Firsts",
    -encoding => 'UTF-8',
    -style => [{ -src => 'k.css'},],
    -script => [
    { -type =>'JAVASCRIPT', -src => "../sorttable.js", },
    { -type =>'JAVASCRIPT', -src => "http://www.magelocdn.com/pack/rift/en/magelo-bar.js#1", },
    ],
    );

my $kind = param('kind');
if ((!defined $kind) || ($kind !~ /^(?:All|Achievement|ArtifactCollection|Item|NPC|Quest|Recipe)$/)) { $kind = "All" }
my $kindbyname = param('kindbyname');
if ((!defined $kindbyname) || ($kindbyname !~ /^(?:All|Achievement|ArtifactCollection|Item|NPC|Quest|Recipe)$/)) { $kindbyname = "All" }
my $player = param('player');
if ((defined $player) && ($player ne "")) { $player =~ s/\P{L}//g; }
else { undef $player; }
my $shard = param('shard');
if ((!defined $shard) || ($shard !~ /^(?:All|Deepwood|Faeblight|Greybriar|Hailol|Laethys|Seastone|Wolfsbane|Bloodiron|Brisesol|Brutwacht|Gelidra|Typhiria|Zaviel)$/)) { $shard = "All"; }
my $guild = param('guild');
if ((defined $guild) && ($guild ne "")) { $guild =~ s/[^\p{L}' ]//g; }
else { undef $guild; }
my $name = param('name');
if ((defined $name) && ($name ne "")) { $name =~ s/[^A-Za-z' ]//g; }
else { undef $name; }

print "<h3>Rift Shard Firsts BETA</h3>\n";
print "<p>This site tells you the shard firsts for your character or guild.</p>\n";
my $dir = "/home/alucard/riftfirst";
opendir(FIRSTDIR, $dir) or print "<p>Warning: Can't open directory $dir: $!\n</p>";
my ($zip) = grep { (/Rift_Discoveries.*\.zip/) && (-f "$dir/$_") } readdir(FIRSTDIR);
closedir(FIRSTDIR);
$zip =~ /(201\d)-(\d{1,2})-(\d{1,2})/;
my ($y, $m, $d) = ($1, $2, $3);
print "<p>Data is checked for from Trion daily. The latest is dated <em>$y-" . sprintf("%02d", $m) . "-" . sprintf("%02d", $d) . "</em>. All information is straight from Trion's <a href=\"http://webcdn.triongames.com/addons/assets/\">public assets</a>.</p>\n";
print "<form action=\"index.pl\" id=\"firstform\">\n";
print "<label for=\"player\">Character: </label><input type=\"text\" name=\"player\" id=\"player\" size=\"14\" ";
if (defined $player) { print "value=\"" . encode_entities($player) . "\""; }
print ">";
print '<label for="shard"> Shard: </label>';
print '<select name="shard" id="shard">';
print '<option>All</option>';
my @nashards = qw/Deepwood Faeblight Greybriar Hailol Laethys Seastone Wolfsbane/;
my @eushards = qw/Bloodiron Brisesol Brutwacht Gelidra Typhiria Zaviel/;
print '<optgroup label="NA">';
foreach my $displayshard (@nashards) {
  my $text = "";
  if ((defined $shard) && ($shard eq $displayshard)) { $text = ' selected="selected"'; }
  print "<option$text>$displayshard</option>";
}
print "</optgroup>\n<optgroup label=\"EU\">";
foreach my $displayshard (@eushards) {
  my $text = "";
  if ((defined $shard) && ($shard eq $displayshard)) { $text = ' selected="selected"'; }
  print "<option$text>$displayshard</option>";
}
print "</optgroup></select>\n";
print '<label for="guild"> Guild: </label><input type="text" name="guild" id="guild" size="14" ';
if (defined $guild) { print "value=\"" . encode_entities($guild) . "\""; }
print '>';
print '<label for="kind"> Type: </label>';
print '<select name="kind" id="kind">';
my @kinds = qw/All Achievement ArtifactCollection Item NPC Quest Recipe/;
foreach my $displaykind (@kinds) {
  my $text = "";
  if ((defined $kind) && ($displaykind eq $kind)) { $text = ' selected="selected"'; }
  print "<option$text>$displaykind</option>";
}
print '</select>';
#print "<br />";
#print "<h4>Or search by item/achievement name to see who got it first on all shards:</h4>";
#print '<label for="kindbyname"> Type: </label>';
#print '<select name="kindbyname" id="kindbyname">';
#foreach my $displaykind (@kinds) {
#    my $text = "";
#      if ((defined $kindbyname) && ($displaykind eq $kindbyname)) { $text = ' selected="selected"'; }
#        print "<option$text>$displaykind</option>";
#}
#print '</select>';
#print "<label for=\"name\"> Name: </label><input type=\"text\" name=\"name\" id=\"name\" size=\"14\" ";
#if (defined $name) { print "value=\"" . encode_entities($name) . "\""; }
#print ">";
print "<br /><input type=\"submit\">";
print "</form>\n";

# Type 1
if ((defined $player) || (defined $guild)) {
# Set up SQL connection
  if ((defined $cfg->param('SQLDB')) && (defined $cfg->param('SQLLOC')) && (defined $cfg->param('SQLUSER')) && (defined $cfg->param('SQLPASS'))) {
    $dsn = "DBI:mysql:database=" . $cfg->param('SQLDB') . ";host=" . $cfg->param('SQLLOC') . ";";
    $dbh = DBI->connect($dsn, $cfg->param('SQLUSER'), $cfg->param('SQLPASS'), { mysql_enable_utf8 => 1, });
    if (!defined $dbh) {
      print STDERR "Error connecting to databse. " . $DBI::errstr . "\n";
    }
  }
  else { die "Insufficient SQL settings!\n"; }
  $dbh->do("set NAMES 'utf8'");

  my $query = "SELECT Kind, What, Player, Shard, Guild, Stamp, Id FROM firsts WHERE ";
  my @string = ();
  my @args = ();
  if (defined $player) { push(@string, "Player=?"); push(@args, $player); }
  if (defined $guild) { push(@string, "Guild=?"); push(@args, $guild); }
  if ((defined $shard) && ($shard ne "All")) { push(@string, "Shard=?"); push(@args, $shard); }
  if ((defined $kind) && ($kind ne "All")) { push(@string, "Kind=?"); push(@args, $kind); }
  $query .= join(" AND ", @string) . " ORDER by Kind ASC, What ASC";
  $sth = $dbh->prepare($query);
#        print $query;
#        print "<br />"; print join("/", @args);
  my $success = $sth->execute(@args);
  if (!$success) { die $!; }
  my $header = ""; my $magurl = "";
  while (my $row = $sth->fetchrow_hashref()) {
    if ($header ne $row->{'Kind'}) { 
      if ($header ne "") { print "</tbody></table>\n"; }
      $header = $row->{'Kind'};
      $magurl = lc($header);
      if ($magurl eq "artifactcollection") { $magurl = "artifactset"; }
      print "<h3>${header}s</h3>\n";
      print "<table class='sortable'><thead><tr>";
      print "<th>Player</th><th>Guild</th><th>What</th><th>Date (GMT)</th></tr>";
      print "</thead><tbody>\n";
    }
    print "<tr><td>" . encode_entities($row->{'Player'}) . "\@$row->{'Shard'}</td><td>" . encode_entities($row->{'Guild'}) . "</td>";
    print "<td><a href=\"http://rift.magelo.com/en/" . $magurl . "/$row->{'Id'}\">" . encode_entities($row->{'What'}) . "</a></td><td>$row->{'Stamp'}</td></tr>\n";
  }
  if ($header ne "") { print "</tbody></table>\n"; }
  $dbh->disconnect();
}
# Type 2
elsif (defined $name) {
# Set up SQL connection
  if ((defined $cfg->param('SQLDB')) && (defined $cfg->param('SQLLOC')) && (defined $cfg->param('SQLUSER')) && (defined $cfg->param('SQLPASS'))) {
    $dsn = "DBI:mysql:database=" . $cfg->param('SQLDB') . ";host=" . $cfg->param('SQLLOC') . ";";
    $dbh = DBI->connect($dsn, $cfg->param('SQLUSER'), $cfg->param('SQLPASS'), { mysql_enable_utf8 => 1, charset => 'utf8' });
    if (!defined $dbh) {
      print STDERR "Error connecting to databse. " . $DBI::errstr . "\n";
    }
  }
  else { die "Insufficient SQL settings!\n"; }
  my $query = "SELECT Kind, What, Player, Shard, Guild, Stamp, Id FROM firsts WHERE ";
  my @string = ();
  my @args = ();
  if (defined $name) { push(@string, "What=?"); push(@args, $name); }
  if ((defined $kindbyname) && ($kindbyname ne "All")) { push(@string, "Kind=?"); push(@args, $kind); }
  $query .= join(" AND ", @string) . " ORDER by Kind, Shard ASC";
  $sth = $dbh->prepare($query);
  print $query;
  my $success = $sth->execute(@args);
  if (!$success) { die $!; }
  my $header = ""; my $magurl = "";
  while (my $row = $sth->fetchrow_hashref()) {
      if ($header ne $row->{'Kind'}) {
        $header = $row->{'Kind'};
        $magurl = lc($header);
        if ($magurl eq "artifactcollection") { $magurl = "artifactset"; }
        print "<h3><a href=\"http://rift.magelo.com/en/" . $magurl . "/$row->{'Id'}\">" . encode_entities($row->{'What'}) . "</a></h3>\n";
        print "<table class='sortable'><thead><tr>";
        print "<th>Player</th><th>Guild</th><th>Date (GMT)</th></tr>";
        print "</thead><tbody>\n";
      }
      print "<tr><td>" . encode_entities($row->{'Player'}) . "\@$row->{'Shard'}</td><td>" . encode_entities($row->{'Guild'}) . "</td>";
      print "<td>$row->{'Stamp'}</td></tr>\n";
  }
  print "</thead><tbody>\n";
  $dbh->disconnect();
}
# No type
else { print "<p><strong>Enter a player name and/or a guild name, then press Submit.</strong></p>\n"; }
print $html->end_html;
