#!/bin/bash

# Attempts to reconstruct useful data from OnTrac's PDF files, and adds it to a SQL database.
# Uses pdftohtml's xml output option, which gives you text and pixel positions.
# This was supposed to be simple, and has grown to where a proper programming language, probably perl, would be more appropriate.  Oh well!

# Copyright 2017 Randy Gardner
# All rights reserved.  Commercial use or distribution prohibited.


# Where to output the SQL to.
sqltemp="/tmp/foo.sql"
sqlfile=""
savesql=0

# sqlite3 database
database="ontrac"
addtodb=0
wipedb=0

# Verbosity.  0 prints everything, 1 is mostly quiet, 2 only prints summary, 3 is silent.  Negative numbers turn on debug info.
quiet=2

# Automatically fix addressess afterwards
fixaddrs=1

showhelp=0
while getopts ":s:q:d:wha" opt
  do
    case $opt in
      a)
        fixaddrs=0
        if (( quiet < 2 ))
          then
            echo "Not automatically fixing addresses."
          fi
        ;;
      h)
        showhelp=1
        ;;
      w)
        wipedb=1
        if (( quiet < 3 ))
          then
            echo "Creating / wiping and re-creating tables."
          fi
        ;;
      d)
        database="$OPTARG"
        addtodb=1
        if (( quiet < 2 ))
          then
            echo "Adding data to database '$database'."
          fi
        ;;
      s)
        sqlfile="$OPTARG"
        savesql=1
        if (( quiet < 2 ))
          then
            echo "Saving SQL output to '$sqlfile'."
          fi
        ;;
      q)
        quiet="$OPTARG"
        if (( quiet < 2 ))
          then
            echo "Setting verbosity to $quiet."
          fi
        ;;
      \?)
        echo "Dunno what -$OPTARG is..."
        showhelp=1
        ;;
      :)
        echo "Option -$OPTARG requires an argument."
        showhelp=1
        ;;
    esac
  done
shift $((OPTIND-1))

pdfcount=0
while [[ "$1" != "" ]]
  do
    pdfs[$pdfcount]="$1"
    (( pdfcount++ ))
    shift 1
  done

if ( (( pdfcount == 0 )) && (( wipedb == 0 )) ) || (( showhelp > 0 ))
  then
    echo "OnTrac PDF to SQL converter version 0.1.  Copyright 2017 Randy Gardner."
    echo "Usage: $(basename "$0") [-q #] [-s sqlfile] [-d database] [-w] [-h] [-a] <pdf file>..."
    echo "  -q quietness.  Less is more.  Current range is -3 to 3."
    echo "  -s save the sql in the specified file.  implies -a."
    echo "  -d adds the data to the specified sqlite3 database."
    echo "  -w creates the database, wiping tables if they already exist."
    echo "  -a don't automatically run fixaddrs to fix addresses."
    echo "  -h You're lookin' at it, pal!"
    exit
  fi

if (( savesql == 0 )) && (( addtodb == 0 ))
  then
    if (( quiet < 2 ))
      then
        echo "Adding data to default database '$database'."
      fi
    addtodb=1
  fi


# some highly secure temporary files
xmltmp="/tmp/footmp.xml"
xml="/tmp/foo.xml"


# what's considered the same line.  Anything within this many pixels of the first top position found, up or down, will be considered the same line.
ytol=2

# Positions of field dividers.  Text is sorted based on the start (left) position.  Anything past the last position will be globbed together.
# number, name, address, sign/etc
stopfields="100 370 680"
# tracking, extra crap, signature, letter, package+service, service, weight, COD
pkgfields="120 550 640 660 690 710 750"
# num, "Count:".  We parse this because they're actually backwards in the file (pixelwise), and I figure parsing properly is more change-tolerant than reversing the regex...
countfields="100"
# extra info always starts towards the start of the line, while signature stuff is at the end
extrafields="100"


# Maximum number of fields to be parsed.  Not critical.
maxfields=10

# &3 is the sql file.
exec 3> "$sqltemp"

# Initialize a new database, or wipe and start over.
if (( wipedb == 1 ))
  then
    # set a couple pragmas that reduce reliability but improve speed.  Since this isn't exactly long-term business-critical info...
    echo 'pragma JOURNAL_MODE = "memory";' >&3
    echo 'pragma SYNCHRONOUS = 0;' >&3
    # drop existing tables, if they exist
    echo "drop table if exists stop;" >&3
    echo "drop table if exists pkg;" >&3
    # create tables
    echo "CREATE TABLE stop(route int, stopnum int, deldate date, name varchar(50), addr varchar(100), extra varchar(2000));" >&3
    echo "CREATE TABLE pkg(tracking varchar(20), route int, stopnum int, deldate date, extra varchar(100), sig varchar(20), ltrs int, pkgs int, service varchar(10), weight int, cod varchar(30));" >&3
    # create indexes (indicies?)
    echo "CREATE INDEX stopidx1 on stop (route, stopnum, deldate);" >&3
    echo "CREATE INDEX pkgidx1 on pkg (tracking, route, stopnum, deldate);" >&3
  fi


# One transaction.  sqlite really likes transactions.  use transactions.
echo "BEGIN;" >&3


# Given already-generated left/data text pairs, split them into the fields given as the first argument.  $fields[] will contain results.
linetofields()
{
  local fielddefs="$1"
#  for (( i=0; i<maxfields; i++ ))
#    do
#      fields[$i]=""
#    done
  unset fields

  # For each text string in the input
  for (( w=0; w<textcount; w++ ))
    do
      local left="${textleft[$w]}"
      local text="${textdata[$w]}"
      local fieldnum=0

      # Run through the field separators.  Stop when we find one past the text position, which leaves the number field at the one before.
      for f in $fielddefs
        do
          if (( left < f ))
            then
              break
            fi
          (( fieldnum++ ))
        done

      # If we stick more than one input string into a field, separate them with a space.
      if [[ "${fields[$fieldnum]}" != "" ]]
        then
          fields[$fieldnum]="${fields[$fieldnum]} "
        fi
      fields[$fieldnum]="${fields[$fieldnum]}$text"
    done

  if (( quiet < -1 ))
    then
      for (( i=0; i<maxfields; i++ ))
        do
          echo -n "'${fields[$i]}' "
        done
      echo
    fi
}



# Some globals used during parsing
route=-1
stopnum=-1
stopcount=0
routestopcount=-1
possibleinfoline=0
foundpkg=-1
deldate=""

# and some just for statistics
pkgcount=0
totalroutes=0
totalstops=0
totalpkgs=0
totalweight=0

# Given a line of words, do things with them!
parseline()
{
  if (( quiet < -1 ))
    then
      echo "line: $words"
    fi

  # If the line starts with "Route:", it's a route number
  if [[ $words =~ ^Route: ]]
    then
      oldroute="$route"
      [[ $words =~ Route:[[:space:]]*([0-9]+) ]]
      route="${BASH_REMATCH[1]}"
      if [[ "$route" == "" ]] || (( route <= 0 ))
        then
          echo "File parse error!  Parsed route as '$route'!"
        fi
      if (( oldroute < 0 ))
        then
          if (( quiet < 2 ))
            then
              echo "Route: $route"
            fi
          stopnum=-1
        elif (( route != oldroute ))
        then
          echo "File parse error!  Found a new route number without a count for the previous route!"
        fi
    #fi.  Because I like my indenting to be nicely bracketed, and elif is ugly.

  # If the line starts with "Del Date:" and something that looks like a date, it's the delivery date
  elif [[ $words =~ ^Del\ Date:[[:space:]]*([0-9]+)/([0-9]+)/([0-9]+) ]]
    then
      olddeldate="$deldate"
      # Convert to SQL-standard YYYY-MM-DD format
      #[[ $words =~ Del\ Date:[[:space:]]*([0-9]+)/([0-9]+)/([0-9]+) ]]
      deldate="20${BASH_REMATCH[3]}-${BASH_REMATCH[1]}-${BASH_REMATCH[2]}"
      if ! [[ "$deldate" =~ [0-9]{4}-[0-9]{1,2}-[0-9]{1,2} ]]
        then
          echo "File parse error!  Parsed delivery date as '$deldate'!"
        fi
      if [[ "$olddeldate" == "" ]]
        then
          if (( quiet < 2 ))
            then
              echo "Delivery date: $deldate"
            fi
        elif [[ "$deldate" != "$olddeldate" ]]
        then
          echo "File parse error!  Found a new delivery date while still processing a route!"
        fi
    #fi.

  # If the line starts with "No.", it's a stop
  elif [[ $words =~ ^No\. ]]
    then
      linetofields "$stopfields"
      [[ ${fields[0]} =~ No.\ ([0-9]+) ]]
      stopnum="${BASH_REMATCH[1]}"
      stopname="${fields[1]}"
      # Sometimes the signature field gets stuck onto the end of very long addresses.  If so, remove it.
      if [[ ${fields[2]} =~ (.*)Sign: ]]
        then
          stopaddr="${BASH_REMATCH[1]}"
        else
          stopaddr="${fields[2]}"
        fi

      if (( quiet < 1 ))
        then
          echo "Stop number $stopnum, delivery name '$stopname', address '$stopaddr'."
        fi
      if [[ "$stopnum" == "" ]] || (( stopnum <= 0 ))
        then
          echo "File parse error!  Parsed stop number as '$stopnum'!"
        fi
      if [[ "$stopname" == "" ]]
        then
          # don't do anything; some stops don't have names.  Because OnTrac.
          true
        fi
      if [[ "$stopaddr" == "" ]]
        then
          echo "File parse error!  Parsed a stop with no address!"
        fi

      if (( $route < 0 ))
        then
          echo "File parse error!  Found something that looks like a stop before finding a route number!"
        fi
      if [[ "$deldate" == "" ]]
        then
          echo "File parse error!  Found something that looks like a stop before finding the delivery date!"
        fi
      if (( foundpkg == 0 ))
        then
          echo "File parse error!  Parsed a stop without finding any packages for the previous stop!"
        fi

      (( stopcount++ ))
      if (( $stopnum != $stopcount ))
        then
          echo "File parse error!  Stop number parsed does not match current stop number!"
        fi

      echo "delete from stop where route=\"$route\" and stopnum=\"$stopnum\" and deldate=\"$deldate\";" >&3
      echo "insert into stop (route, stopnum, deldate, name, addr, extra) values (\"$route\", \"$stopnum\", \"$deldate\", \"$stopname\", \"$stopaddr\", \"\");" >&3

      possibleinfoline=1
      extrainfo=""
      foundpkg=0
      (( totalstops++ ))
    #fi

  # If the line starts with a tracking number, it's a package
  # regular tracking numbers, odd short tracking numbers, ddu tracking numbers.  Include trailing space to separate from extra info, most of the time.
  elif [[ "$words" =~ ^[BCD][0-9]{14}\  ]]  || [[ "$words" =~ ^[B][0-9]{11}\  ]] || [[ "$words" =~ ^[0-9]{9}\  ]]
    then
      linetofields "$pkgfields"
      pkgtrack="${fields[0]}"
      pkgextra="${fields[1]}"
      pkgsig="${fields[2]}"
      pkgweight="${fields[6]}"
      pkgcod="${fields[7]}"
      # Part of the service ends up stuck onto the counts; fix it.  Also fix the letters count rarely being stuck on the end of the extra info.
      ltrs="${fields[3]}"
      if [[ "$ltrs" == "" ]] && [[ "$pkgextra" =~ ([0-9]+)$ ]]
        then
          ltrs="${BASH_REMATCH[1]}"
        fi
      [[ "${fields[4]}" =~ ([0-9]+)[[:space:]]*([[:print:]]*) ]]
      pkgs="${BASH_REMATCH[1]}"
      service="${BASH_REMATCH[2]}${fields[5]}"

      if (( quiet < 1 ))
        then
          echo "Package $pkgtrack, extra addr info '$pkgextra', signature info '$pkgsig', ltrs pkgs '$ltrs $pkgs', service '$service', weight ${pkgweight}lbs, COD info '$pkgcod'."
        fi

      if [[ "$pkgtrack" == "" ]]
        then
          echo "File parse error!  Parsed a package with no tracking number!  Probably a bug..."
        fi
      if [[ "$pkgsig" == "" ]] || ( [[ "$pkgsig" != "OK to Leave" ]] && [[ "$pkgsig" != "Sig Reqd" ]] && [[ "$pkgsig" != "Bus/Sig Reqd" ]] )
        then
          echo "File parse error!  Parsed a package with signature information '$pkgsig'!"
        fi
      if [[ "$pkgweight" == "" ]] || (( pkgweight < 0 ))
        then
          echo "File parse error!  Parsed a package with no package weight!"
        fi
      if (( pkgweight > 100 )) && (( quiet < 2 ))
        then
          echo "Ouch!  ${pkgweight}lbs..."
        fi
      if [[ "$ltrs" == "" ]] || (( ltrs < 0 )) || [[ "$pkgs" == "" ]] || (( pkgs < 0 ))
        then
          echo "File parse error!  Parsed a package without letter and package counts!"
        fi
      if [[ "$service" == "" ]] || ( ! [[ "$service" =~ [CSZG](-[RB])? ]] && ! [[ "$service" =~ P[01P](-[B])? ]] )
        then
          echo "File parse error!  Parsed a package with service information '$service'!"
        fi

      if (( $stopnum < 0 ))
        then
          echo "File parse error!  Found something that looks like a package before finding a stop!"
        fi

      echo "delete from pkg where route=\"$route\" and stopnum=\"$stopnum\" and tracking=\"$pkgtrack\" and deldate=\"$deldate\";" >&3
      echo "insert into pkg (route, stopnum, tracking, deldate, extra, sig, ltrs, pkgs, service, weight, cod) values (\"$route\", \"$stopnum\", \"$pkgtrack\", \"$deldate\", \"$pkgextra\", \"$pkgsig\", \"$ltrs\", \"$pkgs\", \"$service\", \"$pkgweight\", \"$pkgcod\");" >&3

      possibleinfoline=1
      foundpkg=1
      (( pkgcount++ ))
      (( totalpkgs++ ))
    #fi

  # If the line contains a stop count, we must be at the end of the route
  elif [[ $words =~ Count: ]]
    then
      linetofields "$stopfields"
      routestopcount="${fields[1]}"
      if (( quiet < 3 ))
        then
          echo "Route $route for $deldate parsed with $stopcount stops and $pkgcount packages."
        fi
      if [[ $routestopcount == "" ]] || (( $stopcount != $routestopcount ))
        then
          echo "File parse error!  Route sheet says it has $routestopcount stops, but parsed $stopcount stops!"
        fi
      route=-1
      stopnum=-1
      possibleinfoline=0
      stopcount=0
      deldate=""
      pkgcount=0
      (( totalroutes++ ))
    #fi


  # Page break.  Don't actually do anything - just keep processing like nothing happened.
  elif [[ $words =~ Page: ]]
    then
      true
      #echo "************************"
    #fi

  # Other headers.  We don't do anything with these, but we need to catch them so as not to break the extra info flag between pages.
  elif [[ $words =~ ^Del\ Name\ Del\ Address ]] || [[ $words =~ ^Run# ]] || [[ $words =~ ^Special\ Instructions ]] || [[ $words =~ Miles: ]]
    then
      true

  # extra info occurs immediately after a stop or a package
  # this could have some extra logic to insert newlines into the extra info instead of spaces based on positioning
  else
      if (( possibleinfoline == 1 ))
        then
          linetofields "$extrafields"
          [[ "${fields[0]}" =~ [[:space:]]*([[:print:]]+) ]]
          if [[ "${BASH_REMATCH[1]}" != "" ]]
            then
              extra="${BASH_REMATCH[1]}"
              if (( quiet < 1 ))
                then
                  echo "Extra info: '$extra'"
                fi
              # Extra info is often duplicated above and below packages.  Matching it as a regex is a stupid way to see if it starts the same...
              if [[ "$extra" =~ ^$extrainfo ]]
                then
                  extrainfo=""
                fi
              if [[ "$extrainfo" != "" ]]
                then
                  extrainfo="$extrainfo "
                fi
              extrainfo="$extrainfo$extra"
              echo "update stop set extra=\"$extrainfo\" where route=\"$route\" and stopnum=\"$stopnum\";" >&3
            fi
        fi
    #fi
#  else
#    possibleinfoline=0
  fi

}



# Extract each page from each PDF file and sort the text by top and left positions, sticking them all together
# Intentionally skips last page.  It's either the COD sheet, which there's no parsing support for yet (and thus breaks things), 
# or the add-on sheet, which there's no point in parsing, but isn't harmful if we do when there's a COD sheet.
echo >"$xml"
for (( f=0; f<pdfcount; f++ ))
  do
    pdf="${pdfs[$f]}"
    if (( quiet < 2 ))
      then
        echo "Reading '$pdf'."
      fi
    pages="$(pdfinfo "$pdf" | sed -rn 's/^Pages:[[:space:]]*([0-9]+)$/\1/p')"
    for (( p=1; p<pages; p++ ))
      do
        # pdfto* has a bug with outputting to stdout, so use an extra temp file instead of a pipe
        pdftohtml -q -s -i -noframes -xml "$pdf" "$xmltmp" -f "$p" -l "$p"
        # sort by vertical and then by horizontal position
        sort -t '"' -k 2,2n -k 4,4n "$xmltmp" >>"$xml"
      done
  done
if (( quiet < 2 ))
  then
    echo "Parsing..."
  fi

firsttop=-1
while IFS='' read -r line || [[ -n "$line" ]]
  do
    if [[ "$line" == "" ]]
      then
        continue
      fi
    re='<text top="([0-9]+)" left="([0-9]+)"[^>]*>(<b>)?([^<]+)(</b>)?</text>'
    [[ $line =~ $re ]]
    top="${BASH_REMATCH[1]}"
    left="${BASH_REMATCH[2]}"
    text="${BASH_REMATCH[4]}"
    if (( quiet < -2 ))
      then
        echo "top $top, left $left, text '$text'"
      fi

    if (( top > firsttop + ytol )) || (( top < firsttop - ytol )) || (( firsttop < 0 ))
      then
        if (( firsttop >= 0 ))
          then
            parseline
          fi
        firsttop="$top"
        words=""
        textcount=0
      fi

    if [[ "$words" != "" ]]
      then
        words="$words "
      fi
    words="$words$text"

    textleft[$textcount]="$left"
    textdata[$textcount]="$text"
    (( textcount++ ))

  done < <( grep '<text' "$xml" )
# parse the last line, which would otherwise be skipped since the above only parses on finding a new line
parseline


if (( $route >= 0 ))
  then
    echo "File parse error!  File ended with a route still open!"
  fi


echo "COMMIT;" >&3
exec 3>&-

if (( addtodb > 0 ))
  then
    cat "$sqltemp" | sqlite3 "$database"
  fi

if (( savesql > 0 ))
  then
  mv "$sqltemp" "$sqlfile"
  fi


if (( quiet < 3 ))
  then
    echo "Processed $totalroutes routes with a total of $totalstops stops and $totalpkgs packages."
  fi

if (( fixaddrs > 0 )) && (( savesql == 0 ))
  then
    if (( wipedb > 0 ))
      then
        wipeflag="-w"
      fi
    if [[ -e "./fixaddrs" ]]
      then
        ./fixaddrs -q "$quiet" -d "$database" "$wipeflag"
      #fi
    elif [[ -e "$(dirname "$0")"/fixaddrs ]]
      then
        "$(dirname "$0")"/fixaddrs -q "$quiet" -d "$database" "$wipeflag"
      #fi
    else
        fixaddrs -q "$quiet" -d "$database" "$wipeflag"
      fi
  fi
# The above attempts to locate fixaddrs in the current directory, in the same directory as this script, and in the path.