#!/usr/bin/perl
################################################################################
# (c) 2007 Brett T. Warden
# http://www.wgz.com/bwarden/
#
# POI file generator
# This program attempts to open the SQLite database "alldata.db", extract
# schedule data, and output Garmin POI-loader compatible CSV output files. This
# is TriMet-specific as-is, but could be modified to be more generic.
#
# License:
# GNU General Public License
# http://www.fsf.org/licensing/licenses/gpl.html
# Please note in particular the disclaimer of warranty and limitation of
# liability.
#
# Other licensing options may be available upon request.
#
################################################################################
use strict;
use warnings;

use Memoize;
use DBI;
use Getopt::Std;
use File::Spec::Functions;
use Smart::Comments -ENV;
use YAML::Any qw(LoadFile);
use Data::Dumper;

my %opts;
getopts('b:d:f:z:', \%opts);

memoize('get_stops');
memoize('get_stoptimes_by_stop');
memoize('get_trips_by_trip');
memoize('get_routes_by_route');
memoize('check_for_stop_features');
memoize('get_zone_name');
memoize('get_normal_pickup_type');

my %features = (
	1000 => "Shelter",
	2000 => "Furniture",
	2100 => "Bench",
	2200 => "Trash Can",
	2300 => "Bike Storage",
	2310 => "Bike Rack",
	2320 => "Bike Locker",
	2400 => "Vending Machine",
	2410 => "Ticket Vending Machine",
	2420 => "Food Vending Machine",
	2500 => "Phone",
	3000 => "Pavement Features",
	3100 => "Curbcut",
	3200 => "Sidewalk",
	3300 => "Paved Landing",
	3310 => "Paved Front Door Landing",
	3320 => "Paved Back Door Landing",
	3400 => "Crosswalk",
	4000 => "Information Display",
	4100 => "Schedule Display",
	4110 => "Printed Schedule Display",
	4120 => "Electronic Schedule Display",
	4200 => "Real Time Arrival Display",
	5000 => "Lighting",
	5100 => "Shelter Light",
	5200 => "Street Light",
);

my $filters;

if(defined $opts{f})
{
	warn "Attempting to load $opts{f}\n";
	$filters = LoadFile($opts{f});
}
else
{
	$filters = {
		_all => 'all.csv',
	};
}

my $zone_names;
if(defined $opts{z})
{
	warn "Attempting to load $opts{z}\n";
	$zone_names = LoadFile($opts{z});
}
else
{
	$zone_names = {};
}

my $dbFile = defined $opts{d} ? $opts{d} : q(alldata.db);

my $dbh = DBI->connect("dbi:SQLite:dbname=$dbFile", '', '')
	or die "Couldn't open database $dbFile: $!";

warn "Using database file $dbFile\n";

my $dir = defined $opts{b} ? $opts{b} : '.';

my $maxname = 0;
my $maxdesc = 0;

# Open output files
my %outputs;

foreach my $key (keys %$filters)
{
    if($key =~ m/^_/)
    {
        print_record($filters->{$key}, '');
    }
    else
    {
        foreach my $value (keys %{$filters->{$key}})
        {
            print_record($filters->{$key}{$value}, '');
        }
    }
}

STOP:
foreach my $stop (get_stops($dbh)) { ### Working===[%]        done
	#print $tty "\rStop: $stop->{stop_id}\n" if $tty;

	my %headsigns;

	foreach my $stoptime (get_stoptimes_by_stop($dbh, $stop->{stop_id})) {

		foreach my $trip (get_trips_by_trip($dbh, $stoptime->{trip_id})) {

			foreach my $route (get_routes_by_route($dbh, $trip->{route_id})) {
				my @route_name;
				if(check_field($route->{route_short_name})) {
					push(@route_name, $route->{route_short_name});
				}
				if(check_field($route->{route_long_name})) {
					push(@route_name, $route->{route_long_name});
				}
				if(@route_name) {
					$route->{route_name} = join(': ', @route_name);

					#if(check_field($stoptime->{stop_headsign})) {
					#    $headsigns{$route->{route_name}}{$stoptime->{stop_headsign}}++;
					#}
				}
				else {
					$route->{route_name} = 'Unknown';
				}

				$stop->{routes}{$route->{route_name}}++;
				$stop->{route_types}{$route->{route_type}}++;

				#print $tty "Added to ", $stop->{stop_id}, ": ", $route->{route_name}, "\n" if $tty;
			}
		}
	}

	if($stop->{routes}) {
		my @routes;
		foreach my $route (keys %{$stop->{routes}}) {
			if($headsigns{$route} && ref $headsigns{$route} eq 'HASH') {
				my @headsigns = sort {
					$headsigns{$route}{$b} <=> $headsigns{$route}{$a}
				} keys %{$headsigns{$route}};
				my @keepers;
				for(my $i = 0; $i < @headsigns && $i < 2; $i++) {
					push(@keepers, $headsigns[$i]);
				}
				$route .= ' (to ' . join('|', @keepers) . ')';
			}
			push(@routes, $route);
		}

		#warn "Done with routes\n";

		$stop->{zone_name} = get_zone_name($stop->{zone_id});

		my $stop_id = 
		check_field($stop->{stop_code}) ?
		$stop->{stop_code} : $stop->{stop_id};

		my $shortname = $stop_id.": ".$stop->{stop_name};
		$shortname =~ s/\s*[\(-]*\s*Stop\s+ID\s+\d+\s*[\)]\s*?$//;

		my $desc = check_field($stop->{stop_desc}) ? $stop->{stop_desc} : "";
		$desc =~ s/\s*[\(-]*\s*Stop\s+ID\s+\d+\s*[\)]\s*?$//;

		$stop->{stop_details} = join("\n",
			"Stop ID ".$stop_id.'/'.$stop->{zone_name},
			# $stop->{zone_name},
			$desc,
			sort alphanumerically @routes,
		);

		my @features = get_features_by_stop($dbh, $stop_id);
		if(@features)
		{
			# Add an extra line break for more clarity
			$stop->{stop_details} .= "\n";
		}

		for(@features)
		{
			if($features{$_})
			{
				$stop->{stop_details} .= "\n$features{$_}";
			}
			else
			{
				warn "\nUnknown feature: $_\n";
			}
		}

		my $record = sprintf("%s,%s,\"%s\",\"%s\"\n",
			$stop->{stop_lon},
			$stop->{stop_lat},
			$shortname,
			$stop->{stop_details},
		);
		if(length $shortname > $maxname)
		{
			$maxname = length $shortname;
		}
		if(length $stop->{stop_details} > $maxdesc)
		{
			$maxdesc = length $stop->{stop_details};
		}

		#print $record;
		#print $stop->{stop_id}, "\n";

		my $printed;
		foreach my $key (keys %$filters)
		{
			# Skip matching for special filters
			next if $key =~ m/^_/;

			if(defined $stop->{$key})
			{
                my @values;
				if('HASH' eq ref $stop->{$key})
				{
                    push(@values, keys %{$stop->{$key}});
				}
				else
				{
                    push(@values, $stop->{$key});
				}

                my %destinations;
                foreach my $value (@values)
				{
					if($filters->{$key}{$value})
					{
                        $destinations{$filters->{$key}{$value}}++;
					}
				}

                foreach my $destination (keys %destinations)
                {
                    print_record($destination, $record);
                    $printed++;
                }
			}
			else
			{
				warn "Invalid filter: $key\n";
			}
		}
		if($filters->{_default} && !$printed)
		{
			print_record($filters->{_default}, $record);
		}
		if($filters->{_all})
		{
			print_record($filters->{_all}, $record);
		}
	}
	else {
		warn "\nSkipping ", $stop->{stop_id}, "\n";
	}
}
warn "\nDone with stops\n";

warn "Max name length: $maxname\n";
warn "Max desc length: $maxdesc\n";

close_outputs();

exit;

sub check_field {
	my ($field) = @_;

	if(defined $field && $field ne '') {
		return 1;
	}
	else {
		return 0;
	}
}

sub get_stops {
	my ($dbh) = @_;

	my $stop_h;
	#$stop_h = $dbh->prepare('SELECT stop_id,stop_name,stop_lat,stop_lon,zone_id,stop_code from stops');
	$stop_h = $dbh->prepare('SELECT * from stops ORDER BY stop_id');
	$stop_h->execute;
	my @output;
	while(my $stop = $stop_h->fetchrow_hashref) {
		push(@output, $stop);
	}
	return @output;
}

sub get_normal_pickup_type {
	my ($dbh) = @_;

	my $types_h;

	# Check whether optional pickup_types is even used
	$types_h = $dbh->prepare('SELECT * FROM stop_times LIMIT 1');
	$types_h->execute;
	unless(exists $types_h->fetchrow_hashref->{pickup_type})
	{
		# Nope, disregard it.
		return undef;
	}

	# Count the pickups with type 0.
	$types_h = $dbh->prepare('SELECT COUNT(pickup_type) AS foo FROM stop_times WHERE pickup_type=?;');
	$types_h->execute(0);
	my $pickups_0 = $types_h->fetchrow_arrayref->[0];

	if($pickups_0)
	{
		# Ok, we have pickups with type 0, so make sure it's reasonable to use

		$types_h = $dbh->prepare('SELECT COUNT(pickup_type) AS foo FROM stop_times;');
		$types_h->execute();
		my $pickups_total = $types_h->fetchrow_arrayref->[0];
		my $pickups_ratio = $pickups_0 / $pickups_total;

		if($pickups_ratio > 0.75)
		{
			# Looks like 0 is a good choice.
			return 0;
		}
		else
		{
			die "Only $pickups_0 out of $pickups_total stop times are flagged as valid for pickup!\n";
		}
	}
	else
	{
		# Analyze the database to see what is being used
		$types_h = $dbh->prepare('SELECT DISTINCT pickup_type FROM stop_times;');
		$types_h->execute();

		my $pickup_types = $types_h->fetchall_arrayref;
		my %pickup_types;
		foreach my $pickup_type (@$pickup_types) {
			$pickup_types{$pickup_type->[0]}++;
		}

		if(keys %pickup_types == 1)
		{
			# There's only one value for pickup_types. Assume the agency just doesn't
			# use it correctly, and that this value is correct.
			return (keys %pickup_types)[0];
		}
		else
		{
			die "Dunno what kind of pickup types are normal\n";
		}
	}
}

sub get_stoptimes_by_stop {
	my ($dbh, $stop_id) = @_;
	my @output;
	my $stoptimes_h;

#	my $normal_pickup_type = get_normal_pickup_type($dbh);
#
#	if(defined $normal_pickup_type)
#	{
#		# Use normal pickup_type
#		$stoptimes_h = $dbh->prepare('SELECT stop_headsign,trip_id from stop_times where stop_id=? and pickup_type=?');
#		$stoptimes_h->execute($stop_id, $normal_pickup_type);
#	}
#	else
#	{
#		# Ignore pickup_type
		$stoptimes_h = $dbh->prepare('SELECT stop_headsign,trip_id from stop_times where stop_id=?;');
		$stoptimes_h->execute($stop_id);
#	}

	while(my $stoptime = $stoptimes_h->fetchrow_hashref) {
		push(@output, $stoptime);
	}
	return @output;
}

sub get_trips_by_trip {
	my ($dbh, $trip_id) = @_;

	my $trip_h = $dbh->prepare('SELECT route_id from trips where trip_id=? ORDER BY route_id');
	$trip_h->execute($trip_id);
	my @output;
	while(my $trip = $trip_h->fetchrow_hashref) {
		push(@output, $trip);
	}
	return @output;
}

sub get_routes_by_route {
	my ($dbh, $route_id) = @_;

	my $route_h;
	$route_h = $dbh->prepare('SELECT DISTINCT route_short_name,route_long_name,route_type from routes where route_id=? ORDER BY route_id');
	$route_h->execute($route_id);
	my @output;
	while(my $route = $route_h->fetchrow_hashref) {
		push(@output, $route);
	}
	return @output;
}

sub check_for_stop_features
{
	my ($dbh) = @_;
	my $master_h = $dbh->prepare('SELECT name FROM sqlite_master WHERE name=?');
	$master_h->execute('stop_features');
	if($master_h->fetchrow_arrayref)
	{
		return 1;
	}

	return 0;
}

sub get_features_by_stop {
	my ($dbh, $stop_id) = @_;

	my %output;

	unless(check_for_stop_features($dbh))
	{
		return ();
	}

	my $feature_h;
	eval {
		$feature_h = $dbh->prepare('SELECT DISTINCT feature_type from stop_features where stop_id=?');
		$feature_h->execute($stop_id);
	};
	unless($@)
	{
		while(my $feature = $feature_h->fetchrow_arrayref) {
			$output{$feature->[0]}++;
		}
	}
	return sort alphanumerically keys %output;
}

sub alphanumerically {
	# Alphanumeric sort handler

	return cmp_alphanumerically($a, $b);
}

sub cmp_alphanumerically {
	# Actual 2-param alphanumeric comparison function

	my ($a, $b) = @_;

	if((my ($a_num, $a_stem) = ($a =~ m/^(\d+)(.*)$/)) &&
		(my ($b_num, $b_stem) = ($b =~ m/^(\d+)(.*)$/))) {

		my $cmp = $a_num <=> $b_num;

		if($cmp) {
			return $cmp;
		}
		else {
			return cmp_alphanumerically($a_stem, $b_stem);
		}
	}
	else {
		return $a cmp $b;
	}
}

sub print_record {
	my ($file, $string) = @_;

	unless($outputs{$file})
	{
		my $filename = catfile($dir, $file);
		open($outputs{$file}, '>', $filename)
			or die "Can't create $filename: $!\n";
		print "Created $filename\n";
	}

	print {$outputs{$file}} $string;
}


sub close_outputs {
	foreach my $file (keys %outputs)
	{
		close $file;
	}
}

sub get_zone_name {
	my ($zone_id) = @_;

	if(defined $zone_names->{$zone_id})
	{
		return $zone_names->{$zone_id};
	}
	elsif($zone_id =~ m/^\d+$/)
	{
		return sprintf("Zone %u", $zone_id);
	}
	else
	{
		return "Zone $zone_id";
	}
}