package votorola.a; // Copyright 2008, 2011-2012, Michael Allan.  Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Votorola Software"), to deal in the Votorola Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicence, and/or sell copies of the Votorola Software, and to permit persons to whom the Votorola Software is furnished to do so, subject to the following conditions: The preceding copyright notice and this permission notice shall be included in all copies or substantial portions of the Votorola Software. THE VOTOROLA SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE VOTOROLA SOFTWARE OR THE USE OR OTHER DEALINGS IN THE VOTOROLA SOFTWARE.

import java.io.*;
import java.sql.*;
import votorola.g.lang.*;
import votorola.g.sql.*;


/** The cached geocoding of a residential address, backed by a row of the vote-server's
  * geocode table.  Caching is employed to lighten the load on the geocoding service.
  */
public final class Geocode
{


    /** Constructs a Geocode, reading its initial state from the geocode table, or leaving
      * it at default values if it does not exist in the table.
      *
      *     @param table the vote-server's geocode table.
      *
      *     @see #region()
      *     @see #address()
      */
    public Geocode( final String region, final String address, final Table table )
      throws SQLException
    {
        this( region, address );
        table.get( region, address, Geocode.this );
    }



    /** Constructs a Geocode with default initial data.
      *
      *     @see #region()
      *     @see #address()
      */
    private Geocode( String _region,  String _address )
    {
        if( _region == null || _address == null ) throw new NullPointerException(); // fail fast

        region = _region;
        address = _address;
    }



   // ------------------------------------------------------------------------------------


    /** The street address that is geocoded.  It ought to be in minimal canonical form,
      * such as a normalized postal code, otherwise the geocode table may grow
      * unnecessarily large.
      */
    public String address() { return address; }


        private final String address;



    /** Returns true if this geocode has been stored in the table; false otherwise.
      */
    public boolean exists() { return timestamp > 0L; }



    /** The latitude of the address in radians.
      *
      *   @see #setCoordinates(double,double)
      */
    public double latitude() { return latitude; };


        private double latitude;



    /** The longitude of the address in radians.
      *
      *   @see #setCoordinates(double,double)
      */
    public double longitude() { return longitude; };


        private double longitude;



    /** The country in which the address is specified.  This is a two-character, country
      * code top-level domain (<a
      * href='http://en.wikipedia.org/wiki/Country_code_top-level_domain' >ccTLD</a>).
      */
    public String region() { return region; }


        private final String region;



    /** Sets the latitude and longitude.
      *
      *     @see #latitude()
      *     @see #longitude()
      */
    public void setCoordinates( double newLatitude, double newLongitude )
    {
        latitude = newLatitude;
        longitude = newLongitude;
    }



    /** The time at which this geocode was last stored to the table, in milliseconds since
      * the 'epoch'; or zero, if it was never stored.  This is intended to implement a
      * rolling turn-over of the cache, keeping it refeshed with relatively recent values
      * (say no older than five years) from the geocoding service - but that has not been
      * implemented yet.
      *
      *     @see System#currentTimeMillis()
      */
    public final long timestamp() { return timestamp; }


        private long timestamp = 0L;



    /** Stores this geocode in the table.
      *
      *     @param table the vote-server's geocode table.
      */
    public void write( final Table table ) throws SQLException
    {
        timestamp = System.currentTimeMillis();
        table.put( Geocode.this );
    }



   // - O b j e c t ----------------------------------------------------------------------


    /** Returns a descripion of this geocode, including the address and its geographic
      * coordinates.
      */
    public @Override String toString()
    {
        return "Geocode[" + address + " (" + latitude + ", " + longitude + ")]";
    }



   // ====================================================================================


    /** Thrown when a geocoding-specific IO exception occurs.
      */
    public static final class GeocodingException extends IOException
    {

        public GeocodingException( String message ) { super( message ); }

    }



   // ====================================================================================


    /** The geocode table of a vote-server, caching geocode data in relational form.
      */
    public static @ThreadSafe final class Table
    {


        /** Constructs a Table, physically creating it if it does not already exist.
          *
          *     @see #database()
          */
        Table( Database _database ) throws SQLException
        {
            database = _database;
            final String sKey = statementKeyBase + "init";
            synchronized( database )
            {
                PreparedStatement s = database.statementCache().get( sKey );
                if( s == null )
                {
                    s = database.connection().prepareStatement(
                     "CREATE TABLE IF NOT EXISTS \"" + tableName + "\""
                      + " (region character varying,"
                      +  " address character varying,"
                      +  " latitude double precision NOT NULL,"
                      +  " longitude double precision NOT NULL,"
                      +  " timestamp bigint NOT NULL,"
                      +  " PRIMARY KEY (region, address))" );
                    database.statementCache().put( sKey, s );
                }
                s.execute();
            }
        }



       // --------------------------------------------------------------------------------


        /** The database in which this table is stored.
          */
        public @Warning("thread restricted object") Database database() { return database; }


            private final Database database;



    //// P r i v a t e ///////////////////////////////////////////////////////////////////


        /** Retrieves a geocode from the table, initializing the fields of a Geocode
          * instance.
          *
          *     @param geocode the instance to initialize.  All fields are assumed to be
          *       at default values.
          *     @return true if the geocode was retrieved, false if it did not exist in
          *       the table.
          */
        private boolean get( final String region, final String address, Geocode geocode )
          throws SQLException
        {
            final String sKey = statementKeyBase + "get";
            synchronized( database )
            {
                PreparedStatement s = database.statementCache().get( sKey );
                if( s == null )
                {
                    s = database.connection().prepareStatement(
                      "SELECT latitude,longitude,timestamp"
                       + " FROM \"" + tableName + "\"" + " WHERE region = ? AND address = ?" );
                    database.statementCache().put( sKey, s );
                }
                s.setString( 1, region );
                s.setString( 2, address );
                final ResultSet r = s.executeQuery();
                try
                {
                    if( !r.next() ) return false;

                    geocode.latitude = r.getDouble( 1 );
                    geocode.longitude = r.getDouble( 2 );
                    geocode.timestamp = r.getLong( 3 );
                    return true;
                }
                finally{ r.close(); }
            }
        }



        /** Stores a geocode to this table.
          */
        private void put( final Geocode geocode ) throws SQLException
        {
            synchronized( database )
            {
                // effect an "upsert" in PostgreSQL
                // http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-postgresql/6527838#6527838
                final Connection c = database.connection();
                {
                    final String sKey = statementKeyBase + "putU";
                    PreparedStatement s = database.statementCache().get( sKey );
                    if( s == null )
                    {
                        s = c.prepareStatement( "UPDATE \"" + tableName + "\""
                          + " SET latitude = ?, longitude = ?, timestamp = ?"
                          + " WHERE region = ? AND address = ?" );
                        database.statementCache().put( sKey, s );
                    }
                    s.setDouble( 1, geocode.latitude );
                    s.setDouble( 2, geocode.longitude );
                    s.setLong( 3, geocode.timestamp );
                    s.setString( 4, geocode.region );
                    s.setString( 5, geocode.address );
                    final int updatedRows = s.executeUpdate();
                    if( updatedRows > 0 ) { assert updatedRows == 1; return; }
                }
                {
                    final String sKey = statementKeyBase + "putI";
                    PreparedStatement s = database.statementCache().get( sKey );
                    if( s == null )
                    {
                        s = c.prepareStatement( "INSERT INTO \"" + tableName + "\""
                          + " (region, address, latitude, longitude, timestamp)"
                          + " SELECT ?, ?, ?, ?, ? WHERE NOT EXISTS"
                          + " (SELECT 1 FROM \"" + tableName + "\""
                          +   " WHERE region = ? AND address = ?)" );
                        database.statementCache().put( sKey, s );
                    }
                    s.setString( 1, geocode.region );
                    s.setString( 2, geocode.address );
                    s.setDouble( 3, geocode.latitude );
                    s.setDouble( 4, geocode.longitude );
                    s.setLong( 5, geocode.timestamp );
                    s.setString( 6, geocode.region );
                    s.setString( 7, geocode.address );
                    s.executeUpdate();
                }
            }
        }



        private static final String statementKeyBase;



        private static final String tableName = "geocode";


            static { statementKeyBase = Table.class.getName() + ":" + tableName + "."; }


    }



}