#!/usr/bin/perl

use strict;
use DBI;
use Math::BigInt;
use encoding 'utf8';

use constant {
        WP_LIST => 0,
        WP_ADD  => 1,
};

my %opcode = (
        WP_LIST,        "List",
        WP_ADD,         "AddWP",
        );

my $todo      = WP_LIST;
my $file      = undef;

my $latitude  = undef;
my $longitude = undef;
my $altitude  = undef;

my $name      = undef;
my $comment   = undef;

my $verbose   = 0;

while (my $arg = shift @ARGV) {
        getoption($arg);
}

defined $file || usage();
stat    $file || die "No such file: $file\n";

my $dbh = DBI->connect("dbi:SQLite:$file") || die "Cannot connect: $DBI::errstr";

vprint ("Operation: $opcode{$todo}, Input file: $file\n");

exit(qlist($name))                                                      if $todo == WP_LIST;
exit(addwp($latitude, $longitude, $altitude, $name, $comment))          if $todo == WP_ADD;


sub getoption {
        $verbose = 1, return                            if $_[0] eq '-v';
        $file = shift @ARGV, return                     if $_[0] eq '-F';
        $todo = WP_ADD, getwp(), return                 if $_[0] eq '-a';
        $todo = WP_LIST, $name = shift @ARGV, return    if $_[0] eq '-l';
        print "Unrecognized option: $_[0]\n";
        usage();
}

sub usage {
print <<EOF;

Usage:

Print waypoints:

        $0 -F <path to DB file>
       
        or
       
        $0 -F <path to DB file> -l
       
        Example:
       
        $0 -F user1.uds -l
       
Search waypoints whose name or comment contains <pattern>:

        $0 -F <path to DB file> -l <pattern>
       
        Example:
       
        $0 -F user1.uds -l wp0001

Add a new waypoint

        $0 -F <path to DB file> -a <latitude> <longitude> <altitude> <name> <comment>
       
        Example:
       
        $0 -F user1.uds -a  48.856667   2.350987  36 Paris
        $0 -F user1.uds -a  51.500152  -0.126236  14 London
        $0 -F user1.uds -a -22.906365 -47.061574 677 Campinas "San Paulo, Brazil"

Names and comments containing space must be quoted (see last example).

To veiw SQL quieries, add option -v, for example:

   $0 -v -F user1.uds -a  48.856667   2.350987  36 Paris
       
        or
       
        $0 -v -F user1.uds -l wp0001

EOF

die "\n";

}

sub getwp {
        $latitude  = checknum(shift @ARGV);
        $longitude = checknum(shift @ARGV);
        $altitude  = checknum(shift @ARGV);
       
        $name    = shift @ARGV;
        $comment = shift @ARGV;
}

sub checknum {
        $_[0] =~ /^[\d\.\-\+]+$/ || die "Not a number: $_[0]\n";
        my $num = sprintf("%11.6f", $_[0]);
        return $num;
}

sub qlist {
        my $search = shift;
        my $sql = "
                SELECT wp.Name,
                       strftime('%Y-%m-%dT%H:%M:%SZ',wp.TimeStamp/10000000 - 11644473600,'unixepoch') AS WPDate,
                       Latitude, Longitude, Altitude, Comment
                       
                       FROM WayPoint wp, GeoPoint gp, GpsFix fx
                       
                       WHERE wp.GPID = gp.rowid + 79691776
                         AND fx.GPID = gp.rowid + 79691776
                         ";
        if (defined $search) {
                $search = $dbh->quote("%$search%");
                $sql .= "AND (wp.name LIKE $search OR wp.Comment LIKE $search)";
        }
        vprint("$sql\n\n");
        my $table = $dbh->selectall_arrayref($sql);
        printf("%-20s %-25s %11s  %11s %7s %s\n", "Name ", "Date and Time (UTC)", "Latitude", "Longitude", "Alt", "Comment");
        foreach my $wp (@$table) {
                my ($name, $datetime, $latitude, $longitude, $altitude, $comment) = @$wp;
                printf("%-20s  %-25s  %11.6f %11.6f %7.0f %s\n", $name, $datetime, $latitude, $longitude, $altitude, $comment);
        }
}

sub addwp {
        my ($latitude, $longitude, $altitude, $name, $comment) = @_;
        $name    = substr($name,0,20);
        $comment = substr($comment,0,60);
        $altitude = sprintf("%d", $altitude);
        vprint ("Data: $latitude, $longitude, $altitude, '$name', '$comment'\n");
        insertwp($latitude, $longitude, $altitude, $name, $comment, "Crossed Square");
}

sub insertwp {

        $dbh->begin_work;
       
        my ($lat, $lon, $alt, $name, $comm, $icon) = @_;
       
        my $timestmp = (time + Math::BigInt->new('11644473600')) * 10000000;
       
        my $gpid = addobj(1003, 'GeoPoint', "Latitude = $lat, Longitude = $lon") | 0x04c00000;
        addobj(1002, 'GpsFix', "GPID = $gpid, Altitude = $alt");
        my $iid = addobj(1009, 'Icons') | 0x05400000;
        my $cid = addobj(1008, 'Category', "Type = 0, IID = $iid") | 0x02c00000;
       
        my $wpname = $dbh->quote($name) . " || x'0000'";
        my $wpcomm = $dbh->quote($comm) . " || x'0000'";
        my $wpicon = $dbh->quote($icon) . " || x'0000'";
       
        my $wpid = addobj(1005, 'WayPoint',
                "GPID = $gpid, CID = $cid, SCID = 4, Name = $wpname,
                 Comment = $wpcomm, Type = 'WP' || x'0000', SymbolName = $wpicon, TimeStamp = $timestmp");
       
        my $roid = getroid('WayPoint', "rowid = $wpid");
       
        addqspell(1005, $wpname, $roid);
        addqspell(1005, $wpcomm, $roid);
       
        $dbh->commit;
}

sub addobj {
        my ($roottype, $table, $setargs) = @_;
        my $timestmp = (time + Math::BigInt->new('11644473600')) * 10000000;
        my $sql = "INSERT INTO Root VALUES($roottype,$timestmp,0,0,0)";
        vprint ($sql, "\n");
        $dbh->do($sql) || $dbh->rollback && die "Can not $sql";
        my $roid = $dbh->last_insert_id(undef, undef, undef, undef) | 0x00400000;
       
        $sql = "INSERT INTO " . $table . "(" . $table . "_ROID) VALUES(" . $roid . ")";
        vprint($sql, "\n");
        $dbh->do($sql) || $dbh->rollback && die "Can not $sql";
        my $rowid = $dbh->last_insert_id(undef, undef, undef, undef);
       
        if (defined $setargs && $setargs ne '') {
                $sql = "UPDATE " . $table . " SET " . $setargs . " WHERE rowid = " . $rowid;
                vprint ($sql, "\n");
                $dbh->do($sql) || $dbh->rollback && die "Can not $sql";
        }
        return $rowid;
}

sub addqspell {
        my ($type, $text, $roid) = @_;
        $text = lc $text;
        my $sql = "INSERT INTO QuickSpell (Value,Ordinal,ROID,Type) VALUES ($text , 4, $roid, $type)";
        vprint ($sql, "\n");
        $dbh->do($sql) || $dbh->rollback && die "Can not $sql";
}

sub getroid {
        my ($table, $where) = @_;
        my $sql = "SELECT $table" . "_ROID FROM $table WHERE $where";
        vprint ($sql, "\n");
        my ($roid) = $dbh->selectrow_array($sql);
       
        defined $roid && $roid ne '' || $dbh->rollback && die "Database error on $sql\n";
        return $roid;
}

sub vprint {
        return if not $verbose;
        while (my $string = shift @_) {
                print $string;
        }
}