#!/bin/bash
PROGNAME=${0##*/}
set -e

#  When preparing a camp GPX file, we use old holidays and Overpass Turbo to
#  collected candidates. The problem with that is that there are bound to be
#  duplicates in the GPX file. There are *two* types of duplicates:
#  1) camp from Overpass Turbo is already in our spreadsheets but with a
#     whole different set of attributes (because we strip the attributes down
#     to only what we need) and possibly even with a very slightly different
#     coordinate.
#  2) camp is in our two or more of our spreadsheets.
#  This script reports these two issues as, respectively, "already catalogued?"
#  (meaning it's *probably* a duplicate) and "duplicate" (meaning it's *definitely*
#  a duplicate.
#  The fixes should be:
#  1) check OSM to see if it is same camp site. If it is then delete the uncatalogued
#  entry from Overpass Turbo and if it isn't then retain it by giving it a 
#  '<name-of-this-hol>-camp-000'-like name. (The rest of the details about the camp
#  will be collected later.)
#  2) remove one of them. If only one of them is not starred then remove that one,
#  else it doesn't matter which you remove.

#  Configurable stuff
TOLERANCE=0.004            #  Deliberately quite high; tune later
#TOLERANCE=0.0001035

#  Other globals

#  Includes
. $(miniade) || { echo "${0#*/}: ERROR: miniade failed (hint: run 'miniade' to see error)" >&2; exit 1; }

main()
{
    #  Process arguments
    [ $# = 1 ] || usage
    MERGED_GPX=$1

    #  Sanity checks and derivations
    [[ $MERGED_GPX =~ \.gpx$ ]] || error "not a GPX file?"
    CSV_FILE=/tmp/$(basename $MERGED_GPX .gpx).csv

    #  Guts
    info "converting merged GPX to CSV ..."
    gpsbabel -i gpx -f $MERGED_GPX -o unicsv -F $CSV_FILE
    info "loading into sqlite ..."
    rm -f /tmp/$PROGNAME.sqlite
    sqlite3 /tmp/$PROGNAME.sqlite <<EOF
.mode columns
.headers on
-- This are the columns in what gpsbabel produces with 'unicsv' format.
CREATE TABLE camps (
    No INT NOT NULL,
    Latitude FLOAT NOT NULL,
    Longitude FLOAT NOT NULL,
    Name CHAR NOT NULL,
    Description CHAR, -- URL
    Symbol CHAR,
    URL CHAR   -- empty
);
.import --csv --skip 1 $CSV_FILE camps
-- We're gonna use REGEXP so we need plugin.
.load /usr/lib/sqlite3/pcre.so

-- This gets us type #1 (see above).
SELECT   "already catalogued?" as issue,
         t1.Name,
         t1.Latitude,
         t1.Longitude,
         t2.Name,
         t2.Latitude,
         t2.Longitude 
FROM     camps AS t1, 
         camps AS t2 
WHERE    ABS(t1.Latitude-t2.Latitude)<$TOLERANCE AND
         ABS(t1.Longitude-t2.Longitude)<$TOLERANCE AND
         t1.Name NOT REGEXP 'camp[0-9][0-9][0-9]' AND
         t2.Name REGEXP 'camp[0-9][0-9][0-9]'
-- We don't care that t2.Name gets cut down to one entry, because *any*
-- of the camp999-style entries will allow us to determine if it is the
-- same camp or not, and this way we don't see all the *other* camp999-style
-- entries for the same camp. Use GROUP BY to get this.
GROUP BY t1.Latitude, t1.Longitude

UNION

-- This gets us type #2 (see above).
SELECT   "duplicate" as issue,
         t1.Name,
         t1.Latitude,
         t1.Longitude,
         t2.Name,
         t2.Latitude,
         t2.Longitude 
FROM     camps AS t1, 
         camps AS t2 
WHERE    t1.Latitude == t2.Latitude AND
         t1.Longitude == t2.Longitude AND
         t1.Name REGEXP 'camp[0-9][0-9][0-9]' AND
         t2.Name REGEXP 'camp[0-9][0-9][0-9]' AND
         t1.No < t2.No
ORDER BY t1.Latitude, t1.Longitude;
EOF
}

usage()
{
   echo "Usage: $PROGNAME [ <standard-options> ] <merged-gpx-file>" >&2
   exit 1
}

main "$@"