#!/usr/bin/perl -T
################################################################################
# (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 Data::Dumper;

memoize('get_stops');
memoize('get_stoptimes_by_stop');
memoize('get_trips_by_trip');
memoize('get_routes_by_route');

my $dbFile = q(alldata.db);

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

# Open output files
open(OUTPUT_ALL, '>', 'all.csv')
    or die "Failed to open all.csv: $!";
open(OUTPUT_MAX, '>', 'max.csv')
    or die "Failed to open max.csv: $!";
open(OUTPUT_BUS, '>', 'bus.csv')
    or die "Failed to open bus.csv: $!";
open(OUTPUT_TRAM, '>', 'tram.csv')
    or die "Failed to open tram.csv: $!";
open(OUTPUT_FARELESS, '>', 'fareless.csv')
    or die "Failed to open fareless.csv: $!";
open(OUTPUT_ZONE1, '>', 'zone1.csv')
    or die "Failed to open zone1.csv: $!";
open(OUTPUT_ZONE2, '>', 'zone2.csv')
    or die "Failed to open zone2.csv: $!";
open(OUTPUT_ZONE3, '>', 'zone3.csv')
    or die "Failed to open zone3.csv: $!";

foreach my $stop (get_stops($dbh)) {

    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}}++;

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

    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);
        }

        $stop->{zone_name} = $stop->{zone_id} == 0 ?
        'Fareless Square' : sprintf("Zone %d", $stop->{zone_id});

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

        my $record = sprintf("%s,%s,\"%s\",\"%s\"\n",
            $stop->{stop_lon},
            $stop->{stop_lat},
            $stop->{stop_name},
            $stop->{stop_details},
        );

        #print $record;
        print $stop->{stop_id}, "\n";
        print OUTPUT_ALL $record;
        if($stop->{route_types}{0}) {
            print OUTPUT_MAX $record;
        }
        if($stop->{route_types}{3}) {
            print OUTPUT_BUS $record;
        }
        if($stop->{route_types}{6}) {
            print OUTPUT_TRAM $record;
        }
        if($stop->{zone_id} == 0) {
            print OUTPUT_FARELESS $record;
        }
        elsif($stop->{zone_id} == 1) {
            print OUTPUT_ZONE1 $record;
        }
        elsif($stop->{zone_id} == 2) {
            print OUTPUT_ZONE2 $record;
        }
        elsif($stop->{zone_id} == 3) {
            print OUTPUT_ZONE3 $record;
        }
        else {
            warn "Unknown zone: ", $stop->{zone_id};
        }
    }
    else {
        warn "Skipping ", $stop->{stop_id}, "\n";
    }
}

close OUTPUT_ALL;
close OUTPUT_MAX;
close OUTPUT_BUS;
close OUTPUT_TRAM;
close OUTPUT_FARELESS;
close OUTPUT_ZONE1;
close OUTPUT_ZONE2;
close OUTPUT_ZONE3;

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 from stops');
    $stop_h->execute;
    my @output;
    while(my $stop = $stop_h->fetchrow_hashref) {
        push(@output, $stop);
    }
    return @output;
}

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

    my $stoptimes_h = $dbh->prepare('SELECT stop_headsign,trip_id from stop_times where stop_id=? and pickup_type=?');
    $stoptimes_h->execute($stop_id, 0);
    my @output;
    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=?');
    $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=?');
    $route_h->execute($route_id);
    my @output;
    while(my $route = $route_h->fetchrow_hashref) {
        push(@output, $route);
    }
    return @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;
    }
}