#!/usr/bin/perl

sub usage { print "Usage: ./lookup_career.pl top/searchname <index> <war> <pid> <long>\n" ; }

# Also
# Usage: ./lookup_career.pl top index <war>

# this script will handle all career lookups
# and replaces the code in readql.pl which used 
# to do these lookups

# Example: ./lookup_career.pl xyzabc01 war pid long
#	this would do a career war lookup assuming searchname is a pid
#	long is irrelevant for war records currently and would be ignored

# Example: ./lookup_career rizzo
#	this would find all rizzos and output waa career records
#
# Example: ./lookup_career top 2 
#	output career ranks 25-50 in html format

use strict ;
use warnings ;
use DBI ;
use IO::File ;


print "got here .... \n" ;
my $curyear = 2020 ;
my $baseurl = "http://baseball-handbook.com" ;
my $document = new IO::File ;
my $curdir = "/home/mea/baseball/br_year_data" ;
my $config = "/home/mea/baseball.conf" ;
if ( $document -> open ( $config )) {
	foreach my $tmp (<$document> ) {
		chomp $tmp ;
		if ( $tmp =~ /^#/ || $tmp eq "" ) { next ; }
		my ( $key,$value ) = split /=/ , $tmp ;
		if ( $key eq "url" ) { $baseurl = $value ; }
	}
	$document -> close ;
}

my $mlbteams=" ANA ARI ATL BAL BOS CHA CHN CIN CLE COL DET HOU KCA LAN MIA MIL MIN NYA NYN OAK PHI PIT SDN SEA SFN SLN TBA TEX TOR WAS " ;
my $action = "waa" ; # default
my $action2 = "none" ;
my $pid = "none" ;
my $long = 0 ; # 0/1 flag 1 = print long records
my $html = 0 ; # 0/1 flag 1 = print html output (future)
my $searchstring = "none" ;
my $toprank = 1 ;
# rank index range from 1 -8
my @topranks = qw ( 1-25 26-50 51-100 101-150 151-200 201-300 301-400 401-500 ) ;
sub choose_year {
	my ( $xyear ) = @_ ;
	print "<div style=\"margin: 10px ; \">\n" ;
	print "<form action=\"actions.php\">\n" ;
	print "<fieldset  > \n" ;
	print "<legend>Choose a Year</legend>\n" ;
	print "<input id=\"select\" type=\"number\" name=\"year\" value=$xyear min=1900 max=2020 >\n" ;
	print "<input id=\"submit\" type=\"submit\"  style=\"text-align: center ;\" value=\"Submit\"  >\n" ;
	print "<input type=\"hidden\" name=\"myaction\" value=\"topyearcareer\" >\n" ;
	print "</fieldset>\n" ;
	print "</form>\n" ;
	print "</div>\n" ;
}
sub make_form {
#	print "<form action=\"actions.php?myaction=topcareer\">\n" ;
	print "<form action=\"actions.php\">\n" ;
	print "<label for=\"position\">Choose a rank range</label>\n" ;
	print "<select id=\"select\" name=\"index\">\n" ;
	my $index = 1 ;
	foreach my $range ( @topranks ) {
		print "<option value=\"$index\">$range</option>\n" ;
		$index++ ;
	}
	print "</select>\n" ;
	print "<input type=\"hidden\" name=\"myaction\" value=\"topcareer\" >\n" ;
	print "<input type=\"submit\" id=\"select\" >\n" ;
	return ;
}
my $arg_year = "none" ;
if ( my @args = @ARGV ) {
	if ( $args[0] eq "top" ) { $action = "top" ; }
	elsif ( $args[0] eq "topyear" ) { 
		$action = "topyear" ; 
		if ( $#args < 1 ) { print "not enough args\n" ; exit ; }
		$arg_year = $args[1] ;
	} else { $searchstring = $args[0] ; }
	for ( my $i = 1 ; $i <= $#args ; $i++ ) {
		if ( $action eq "top" && $i == 1 ) { 
			$toprank = $args[$i] ; 
		}
		if ( $args[$i] eq "war" ) { 
			if ( $action eq "top" ) { $action2 = "war" ; }
			else { $action = "war" ;  }
		}
		elsif ( $args[$i] eq "pid" ) { 
			$action2 = "pid" ; 
			$pid = lc $args[0] ;
		}
		elsif ( $args[$i] eq "long" ) { $long = 1 ;  }

	}
} else { usage ; exit ; }
if ( $action !~ /^top/ ) { 
	print "action $action action2 $action2 search $searchstring long $long html $html\n" ;
}

if ( $toprank !~ /\d/ ) { print "bad toprank $toprank\n" ; exit ; }
if ( $toprank < 1 || $toprank > 8 ) { print "bad toprank $toprank\n" ; exit ; }
my %awards = () ;
my %hof = () ; # keyed by brid , value HOF
my $dbfile = "/home/mea/baseball_data/db/baseball.db" ;
	my $dsn      = "dbi:SQLite:dbname=$dbfile";
	my $user     = "";
	my $password = "";
	my $dbh = DBI->connect($dsn, $user, $password, {
	   PrintError       => 0,
	   RaiseError       => 1,
	   AutoCommit       => 1,
	   FetchHashKeyName => 'NAME_lc',
	}) or  die $DBI::errstr ;

	my $sql = "select * from awards " ;
	my $sth = $dbh->prepare($sql);
	$sth->execute();
	while (my @row = $sth->fetchrow_array) {
		my ( $year,$type,$league,$retroid,$brid,$link ) = @row ;
		my $mykey = "$brid $year" ;
		push @{$awards{$mykey}} , $type ;
		if ( $type eq "HOF" ) { $hof{$brid} = $year ; }
	}

my $topheader = "<tr><th>Rank</th><th>WAA</th><th>Name</th><th>Years</th><th>Pos<th></th></tr>" ;
my $wartopheader = "<tr><th>Rank</th><th>WAR</th><th>Name</th><th>Years</th><th>Pos<th></th></tr>" ;
my %ty_printrecord = () ; # keyed by playerid , value print record
my %sortthis = () ; # keyed by playerid , value waa for sorting
my %batpitch = () ; # keyed by playerid , value BAT/PITCH
if ( $action eq "topyear" ) {
	$sql = "select rank,playerid,waa,name,firstyear,lastyear,batpitch from careers where rank like '+%' and lastyear >= ? and firstyear <= ? order by waa desc limit 30 " ;
	$sth = $dbh->prepare($sql);
	$sth->execute( $arg_year,$arg_year );
	while (my @row = $sth->fetchrow_array) {
		my ( $rank,$playerid,$waa,$name,$firstyear,$lastyear,$xbatpitch ) = @row ;
		my $printname = $name ;
		if ( $name eq "Babe_Ruth" ) { $name = "Babe_Ruth:NYA:BAT" ; }
		$printname =~ s/_/ /g ;
		my $name_url = "<a href=\"$baseurl/actions.php?myaction=career&searchstring=$name\" >$printname</a>" ;
		my $rank_url = "<a href=\"$baseurl/actions.php?myaction=appearance&search=$name&year=post_season\" >$rank</a>" ;
		$ty_printrecord{$playerid} = sprintf  "<tr><td>$rank_url</td><td>%.2f</td><td>$name_url</td><td>$firstyear-$lastyear</td>" , $waa ;  # rest to be filled out later
		$sortthis{$playerid} = $waa ;
		$batpitch{$playerid} = $xbatpitch
		
	}
	# now get position
	$sql = "select pos,playerid from waa_bat where  year = ?" ;
	#print "sql $sql\n" ;
	$sth = $dbh->prepare($sql);
	$sth->execute( $arg_year );
	my %position = () ; # keyed by playerid , value position
	while (my @row = $sth->fetchrow_array) {
		my ( $pos,$playerid ) = @row ;
		if ( ! exists $sortthis{$playerid} ) { next ; }
		$position{$playerid} = $pos ;
	}
	choose_year ( $arg_year ) ;
	print "<h2>Top 30 Ranked Careers for $arg_year</h2>\n" ;
	print "<p>Career WAA value includes all years for that player.</p>\n" ;
	print "<table id=\"generic\" >\n" ;
	print "$topheader\n" ;
	foreach my $playerid ( sort { $sortthis{$b} <=> $sortthis{$a} } keys %sortthis ) {
		my $printpos = "PITCH" ;
		my $printhof = "" ;
		if ( exists $hof{$playerid} ) { $printhof = "HOF" ; }
		if ( $batpitch{$playerid} eq "BAT" && exists $position{$playerid} ) { $printpos = $position{$playerid} ; }
		print $ty_printrecord{$playerid} . "<td>$printpos</td><td>$printhof</td></tr>\n" ;
		
	}
	print "</table>\n" ;

	$dbh->disconnect ;
	exit ;
} elsif ( $action eq "top" ) {
	$sql = "select rank,playerid,waa,name,firstyear,lastyear,batpitch from careers limit 500" ;
	if ( $action2 eq "war"  ) {
		$sql = "select rank,playerid,war,name,firstyear,lastyear,batpitch from warcareers limit 500" ;
	}
	$sth = $dbh->prepare($sql);
	$sth->execute();
	my @careers = () ;
	# $waa value below could be waa or war, it doesn't matter
	while (my @row = $sth->fetchrow_array) {
		my ( $rank,$brid,$waa,$name,$firstyear,$lastyear,$batpitch ) = @row ;
		my $printhof = "" ;
		if ( exists $hof{$brid} ) { $printhof = "HOF" ; }
		my $url_name = $name ;
		if ( $name eq "Babe_Ruth" ) { 
			$url_name = "Babe_Ruth:NYA:BAT" ; }
		my $printname = $name ;
		$printname =~ s/_/ /g ;
		my $name_url = "<a href=\"$baseurl/actions.php?myaction=career&searchstring=$url_name\" >$printname</a>\n" ;
		if ( $action2 eq "war" ) {
			$name_url = "<a href=\"$baseurl/actions.php?myaction=career&searchstring=$url_name&model=war\" >$printname</a>\n" ;
		}
		my $rank_url = "<a href=\"$baseurl/actions.php?myaction=appearance&search=$url_name&year=post_season\" >$rank</a>" ;
		push @careers , sprintf "<tr><td>$rank_url</td><td>%.2f</td><td>$name_url</td><td>$firstyear-$lastyear</td><td>$batpitch</td><td>$printhof</td></tr>",$waa ;
	}
	my $printranks =  $topranks[$toprank-1] ;
	my ($start,$end) = split /-/ , $printranks ;
#	make_form ;
	my $printmodel = "WAA" ;
	if ( $action2 eq "war" ) { $printmodel = "WAR" ; }
	printf "<h3>Top MLB Careers %03d-%03d  $printmodel Data Model</h3>\n" , $start, $end ;
	print "<table id=\"generic\" >\n" ;
	if ( $action2 eq "war" ) { $topheader =~ s/WAA/WAR/ ; }
	print "$topheader\n" ;
	for ( my $i = $start ; $i <= $end ; $i++ ) {
		print "$careers[$i-1]\n" ;
	}
	print "</table>\n" ;
	my $gobackurl = "none" ;
	my $goforwardurl = "none" ;
	my $topindex = $toprank ;
	my $model = "waa" ;
	if ( $action2 eq "war" ) { $model = "war" ; }
	if ( $topindex > 1 && $topindex <= 8 ) {
		$gobackurl = sprintf "<a href=\"$baseurl/actions.php?myaction=topcareer&index=%d&model=$model\">Go Back $topranks[$topindex-2]</a>\n" , $topindex - 1 ;
	}
	if ( $topindex >= 0 && $topindex < 8 ) {
		$goforwardurl = sprintf "<a href=\"$baseurl/actions.php?myaction=topcareer&index=%d&model=$model\">Go Forward to $topranks[$topindex]</a>\n" , $topindex + 1 ;
	}
	if ( $topindex == 1 ) {
		print "<p><span style=\"align:left\" >$goforwardurl</span></p>\n";
	} elsif ( $topindex < 8 ) {
		print "<p><span style=\"align:left\" >$gobackurl</span><span style=\"float:right\" >$goforwardurl</span></p>\n";
	} else {
		print "<p><span style=\"align:left\" >$gobackurl</span></p>\n";
	}
	$dbh->disconnect ;
	exit ;
}
my $select = "none" ;
# first get playerids from searchstring
# this dblookup not needed if pid is provided
my @playerids = () ;
if ( $pid ne "none" ) {
	# pid must come in as pid:BAT/PITCH
	if ( $pid =~ /:/ ) {
		my ( $xpid,$batpitch ) = split /:/,$pid ;
		push @playerids , "$xpid $batpitch" ;
	} else { print "bad pid $pid\n" ; $dbh->disconnect ; exit ; }
} else {
	$select = "year,name,teamid,playerid,batpitch" ;
	my $sql = "SELECT $select FROM playerids WHERE UPPER(name) LIKE UPPER( ? ) " ;
	if ( $searchstring =~ /:/ ) {
		my ( $xname,$xteamid,$xbatpitch ) = split /:/ , $searchstring  ;
		if ( $xbatpitch ne "PITCH" ) { $xbatpitch = "BAT" ; }
		$sql = "SELECT $select FROM playerids WHERE UPPER(name) LIKE UPPER( ? ) and teamid = '$xteamid' and batpitch='$xbatpitch' " ;
		$searchstring = "%$xname%" ;
#print "got here xname $xname teamid $xteamid batpitch  $xbatpitch\n" ;
	} else { $searchstring = "%$searchstring%" ; }
	$sth = $dbh->prepare($sql);
	$sth->execute($searchstring);
	my %check = () ; # keyed by playerid batpitch , value ...
	while (my @row = $sth->fetchrow_array) {
#		print "playerid @row\n" ;
		my $mykey = "none" ;
		my ( $year, $name, $teamid, $playerid , $batpitch ) = @row ;
		$mykey = "$playerid $batpitch" ;
		if ( ! exists $check{$mykey} ) {
			$check{$mykey} = "$name $teamid $batpitch" ;
			push @playerids , $mykey ;
#print "$mykey " ;
		}
	}
	# clean playerids because some pitchers are listed as BAT
	# this is a DB problem but we should fix it here too
	# I don't feel like updating the DB over this right now
# the below code may not be needed anymore
#	if ( $#playerids == 1 ) {
##print "got here @playerids\n" ;
#		my ( $p1,$b1 ) = split / / , $playerids[0] ;
#		my ( $p2,$b2 ) = split / / , $playerids[1] ;
#		# Babe RUth is a problem!!!
#		# this defers his lookup into LIST PLAYERS
#		# because his PITCH records are relevant
#		# might be a couple more that need this
#		if ( $p1 eq $p2 && $p1 ne "ruthba01" ) {
#			@playerids = () ;
#			push @playerids , "$p1 PITCH" ;
#		}
#	}
} # endof if pid

#print "got here $#playerids\n" ;

if ( $#playerids > 0 ) {
	print "LIST PLAYERS\n" ;
	my $index = 1 ;
	my %printplayer = () ;
	foreach my $tmp (@playerids) {
		my ( $playerid,$batpitch ) = split / +/ , $tmp ;
		$sql = "SELECT name,teamid,batpitch FROM playerids  WHERE playerid == ? and batpitch = ?" ;
		my $sth = $dbh->prepare($sql);
		$sth->execute($playerid,$batpitch);
		while (my @row = $sth->fetchrow_array) {
			my ( $name,$teamid,$batpitch ) = @row ;
			if ( $teamid eq "TOT" ) { next ; }
			my $mykey = "$playerid $teamid $batpitch" ;
			$printplayer{$mykey} = "$name $teamid $batpitch $playerid" ;
		}
		if ( $index > 12 ) { last ; }
		$index++ ;
	}
	# now let's print the list and get out of here
	$index = 1 ;
	foreach my $tmp ( sort { $a cmp $b } keys %printplayer ) {
		print "$index $printplayer{$tmp}\n" ;
		$index++ ;
		if ( $index > 25 ) { print "Narrow your search ...\n" ; last ; }
	}
	$dbh->disconnect ;
	exit ;
}

my $total = 0 ;
foreach my $tmp ( @playerids ) {
	my ( $playerid,$batpitch)  = split / / , $tmp ;
	if ( exists $hof{$playerid} ) { print "HOF $hof{$playerid}\n" ; }
#	$sql = "select year from awards where brid=? and type='HOF'" ;
#	$sth = $dbh->prepare($sql);
#	$sth->execute($playerid);
#	while (my @row = $sth->fetchrow_array) {
#		my ( $hofyear ) = @row ;
#		print "HOF $hofyear\n" ;
#	}
	my $totwaa = 0 ;
	my $printrank = "XXXXX" ;
	$sql = "select rank,waa,rankscore,firstyear,lastyear from careers where playerid=? and batpitch=?" ;
	if ( $action eq "war" ) {
		$sql = "select rank,war,rankscore,firstyear,lastyear from warcareers where playerid=? and batpitch=?" ;
	}
	$sth = $dbh->prepare($sql);
	$sth->execute( $playerid,$batpitch );
	while (my @row = $sth->fetchrow_array) {
		my ( $xrank,$waa,$rankscore,$firstyear,$lastyear ) = @row ;
		print "RANGE $firstyear-$lastyear\n" ;
		$printrank = $xrank ;
	}
	my $table = "none" ;
	if ( $action eq "war" ) {
		$table = "war" ;
		$select = "year,rank,war,owar,dwar,name,teamid,pos" ;
	} elsif ( $batpitch eq "BAT" ) {
		$select = "year,rank,waa,name,teamid,pos" ;
		$table = "waa_bat" ;
		if ( $long == 1  ) {
			$select = "year,rank,waa,ba,obp,pa,rbis,runs,name,teamid,pos" ;
		}
	} else {
		$select = "year,rank,waa,name,teamid,pos" ;
		$table = "waa_pitch" ;
		if ( $long == 1 ) {
			$select = "year,rank,waa,ip,era,gs,gr,name,teamid,pos" ,
		}
	}
	if ( $action eq "war" ) {
		$sql = "SELECT $select FROM $table WHERE playerid == ? ";
	} else {
		$sql = "SELECT $select FROM $table WHERE playerid == ? and teamid != 'TOT' ";
	}
	$sth = $dbh->prepare($sql);
	$sth->execute($playerid);
	$total = 0 ;
	while (my @row = $sth->fetchrow_array) {
		my $printawards = "none" ;
		$total += $row[2] ;
		my $mykey = "$playerid $row[0]" ;
		if ( exists $awards{$mykey} ) {
			foreach my $tmp2 ( @{$awards{$mykey}} ) {
				if ( $printawards eq "none" ) { $printawards = $tmp2 ;} else { $printawards = "$printawards,$tmp2" ; }
			}
		}
		if ( $action2 eq "war" || $action eq "war"  ) {
			my ( $year,$rank,$war,$owar,$dwar,$name,$teamid,$pos ) = @row ;
	#	       $name =~ s/_/ /g ;
			printf "$year $rank %.1f $owar $dwar $name $teamid $pos $printawards\n", $war ;
		} elsif ( $long == 1  ) {
			if ( $batpitch eq "BAT" ) {
				my ( $year,$rank,$waa,$ba,$obp,$pa,$rbis,$runs,$name,$teamid,$pos ) = @row ;
				printf "$year $rank %.2f %.3f %.3f $pa $rbis $runs $name $teamid $pos\n" , $waa,$ba,$obp ;
			} else {
				my ( $year,$rank,$waa,$ip,$era,$gs,$gr,$name,$teamid,$pos ) = @row ;
				printf "$year $rank %.2f %.1f %.2f $gs $gr $name $teamid $pos\n" , $waa,$ip,$era ;
			}
		} else {
			my ( $year,$rank,$waa,$name,$teamid,$pos ) = @row ;
			printf "$year $rank %.2f $name $teamid $pos $printawards\n", $waa ;
		}
	} # endof while
	print "TOTAL $printrank $total\n" ;
} # endof foreach @playerids


$dbh->disconnect ;
exit ;




