#!/usr/bin/perl -T
#
# list the words in an order given by certain parameters; then display
# dropdown menus where the user can change the class associated with 
# each word.
#

use strict;
use CGI qw(:standard -nosticky);
use CGI::Carp qw(fatalsToBrowser);
use DBI;
use Reviews;

$CGI::POST_MAX=1024 * 15;  # max 20K posts
$CGI::DISABLE_UPLOADS = 1;  # no uploads
$ENV{PATH} = "";

my $q = new CGI;
print $q->header(-expires=>'now');
print $q->start_html();

my $begin_time = time();

my $dbh = make_db_handle;
#my $order = $q->param('sort') || "totDESC";
#$order = substr($order, 0, 30);
#$order =~ s/[^A-Za-z,]//g;
#$order = lc($order);
#$order =~ s/,/, /g;
#$order =~ s/desc/ desc/g;
my $sort = $q->param('sort') || "positivity";
$sort = substr($sort, 0, 30);
$sort =~ s/[^A-Za-z,]//g;
$sort = lc($sort);
$sort =~ s/,/, /g;
$sort =~ s/desc/ desc/g;
my $order = $q->param('order');
my $gtfield;
# "greater than" textfield, so "select * where $gtfield > $gt
my $gt = $q->param('gt') || "0.54";
if (! $order) {
	if ($sort eq "total") { $order = "tot DESC"; $gtfield = "tot" }
	elsif ($sort eq "positivity") { $order = "pwr DESC"; $gtfield = "pwr" }
	elsif ($sort eq "negativity") { $order = "nwr DESC"; $gtfield = "nwr" }
	else { $order = $sort }
}

my $limit = $q->param('limit') || 100; #only return 100 at a time
$limit =~ s/[^\d]//g;
$limit = substr($limit, 0, 4);
my $offset = $q->param('offset') || 0;
$offset =~ s/[^\d]//g;
$offset = substr($offset, 0, 7);
# not interested in words that occur less than the "floor"
my $floor = $q->param('floor') || 60; 
$floor =~ s/[^\d]//g;
$floor = substr($floor, 0, 4);
if ($floor < 5) {
	$floor = 5;
}
# also, not interested in words that occur more than the "ceiling"
my $ceiling = $q->param('ceiling') || 10000; 
$ceiling =~ s/[^\d]//g;
$ceiling = substr($ceiling, 0, 10);

my $capitalization = $q->param('capitalization') || "lowercase";
my $cap_string = "";
if ($capitalization eq "lowercase") { 
	$cap_string = qq/AND word REGEXP "^[a-z]"/;
} elsif ($capitalization eq "capitalized") {
	$cap_string = qq/AND word REGEXP "^[A-Z]"/;
}

# put words that we got in word_class table!
my @param_names = $q->param();
foreach my $name (@param_names) {
	next unless ($name =~ /^word(\d+)$/);
	my $word_id = $1;
	my $class_id = $q->param($name);
	next unless ($class_id =~ /^\d+$/);
	dbquery($dbh, qq/
		DELETE FROM word_class
		WHERE word_id = $word_id
	/);
	dbquery($dbh, qq/
		INSERT INTO word_class(word_id,class_id)
		VALUES ($word_id,$class_id)
	/);
}

# print a form giving the current settings and offering a way
# to change them.
print(
	$q->start_form(-method=>"GET"),
	#"sort order:", $q->textfield(-name=>'sort', -default=>$order, -override=>1, -size=>20),
	"field:", $q->popup_menu(-name=>'sort', -default=>$sort, -values=>['positivity', 'negativity', 'total']),
	" greater than:", $q->textfield(-name=>'gt', -default=>$gt, -size=>5),
	" limit:", $q->textfield(-name=>'limit', -default=>$limit, -size=>5),
	" floor (min tot):", $q->textfield(-name=>'floor', -default=>$floor, -override=>1, -size=>5),
	#" ceiling (max tot):", $q->textfield(-name=>'ceiling', -default=>$ceiling, -size=>5),
	" offset:", $q->textfield(-name=>'offset', -default=>$offset, -size=>5),
	#" capitalization:", $q->popup_menu(-name=>'capitalization',
	#	-values=>['all','lowercase','capitalized']),
	" ",$q->submit(-name=>'Submit'),
	"<p>\n");
# print the "next" button
my $firstword = $offset+1; 
my $lastword = $limit+$offset;
my $new_url = $q->url(-relative=>1, -query=>1);
if ($new_url =~ /offset=(\d+)/) {
	my $new_offset = $1 + $limit;
	$new_url =~ s/offset=(\d+)/offset=$new_offset/;
} else {
	my $new_offset = $offset + $limit;
	$new_url = $new_url."?offset=$new_offset";
}
print qq%You are viewing words $firstword - $lastword. <a href="$new_url">next $limit >></a>%;
#print "<br>", "For an explanation of these statistics, select a word and click on it.<br>(word links are on the right side of the screen)";
print "<pre><ol start=$firstword>\n";

# get words
# "greater than" textfield, so "select * where $gtfield > $gt
# also need to get the class for each word here
my $words = dbquery($dbh, qq/
	SELECT word.id, word FROM word, statistics
	WHERE statistics.word_id = word.id
	# AND length(word) > 2
	AND tot BETWEEN $floor AND $ceiling
	AND $gtfield > $gt
	$cap_string
	ORDER BY $order
	LIMIT $offset,$limit
/);
# get fields
#my $fields_table = dbquery($dbh, qq/SHOW COLUMNS FROM statistics/);
#my @fields;
#foreach my $row (@$fields_table) {
#	push @fields, $$row[0];
#}
#my $trash = shift @fields;
#my @fields = qw(tot pos neu neg pwr nwr avg art aut);
my @fields = qw(tot pwr nwr);

my $i;
print( 	
	$q->submit(-name=>'class_submit', -value=>'Submit class changes'), 
	$q->br,
	"\n"
);
my %classes;
my $classes = dbquery($dbh, qq/
	SELECT class.id,class.name
	FROM class
/);
foreach my $row (@$classes) {
	my $id = $$row[0];
	my $name = $$row[1];
	$classes{$id} = $name;
}
$classes{"NULL"} = "NULL";
foreach my $wordref (@$words) {
	my $word_id = $$wordref[0];
	my $word = $$wordref[1];
	print "<li><b>$word</b> -> ";
	my $selectstring = join(",", @fields);
	my $values = dbquery($dbh, qq/
		SELECT $selectstring
		FROM statistics
		WHERE word_id = $word_id
	/);
	my $i = 0;
	while ($i <= $#fields) {
		print "$fields[$i]:$$values[$i] ";
		$i++;
	}
	my $class = dbquery($dbh, qq/
		SELECT class_id
		FROM word_class
		WHERE word_id = $word_id
	/);  
	if (! defined $class) { $class = "NULL" }
	my $class_name = $classes{$class};
	if (($class_name eq "NULL") || ($class_name eq "none")) { 
		print qq%class:$class_name %; 
	} else {
		print qq%class:<font color="blue">$class_name</font> %;
	}
	print $q->popup_menu(-name=>"word$word_id",
		-default=>"$class",
		-labels=>\%classes,
		-values=>[ sort {$a <=> $b} keys %classes ], );
	#foreach my $field (@fields) {
	#	my $value = dbquery($dbh, qq/
	#		SELECT $field FROM statistics 
	#		WHERE word_id = $word_id
	#	/);
	#	print "$field:$value ";
	#}
	#print "-> ";
	#print qq(<a href="word.pl?id=$word_id">$word</a> );
	#print qq(<a href="concordance.pl?id=$word_id">KWIC</a> );
	#print qq(<a href="coexistence.pl?id=$word_id">coex</a> );
	print "\n";
}

my $end_time = time();
my $time_elapsed = $end_time - $begin_time;
print "\n<b>query time: $time_elapsed seconds</b>\n";

print $q->end_form;
print $q->end_html();