import re

from decimal import Decimal

from django.conf import settings

from django.contrib.gis.db.backends.base import BaseSpatialOperations

from django.contrib.gis.db.backends.util import SpatialOperation, SpatialFunction

from django.contrib.gis.db.backends.postgis.adapter import PostGISAdapter

from django.contrib.gis.geometry.backend import Geometry

from django.contrib.gis.measure import Distance

from django.core.exceptions import ImproperlyConfigured

from django.db.backends.postgresql_psycopg2.base import DatabaseOperations

from django.db.utils import DatabaseError

#### Classes used in constructing PostGIS spatial SQL ####

class PostGISOperator(SpatialOperation):

    "For PostGIS operators (e.g. `&&`, `~`)."

    def __init__(self, operator):

        super(PostGISOperator, self).__init__(operator=operator)

class PostGISFunction(SpatialFunction):

    "For PostGIS function calls (e.g., `ST_Contains(table, geom)`)."

    def __init__(self, prefix, function, **kwargs):

        super(PostGISFunction, self).__init__(prefix + function, **kwargs)

class PostGISFunctionParam(PostGISFunction):

    "For PostGIS functions that take another parameter (e.g. DWithin, Relate)."

    sql_template = '%(function)s(%(geo_col)s, %(geometry)s, %%s)'

class PostGISDistance(PostGISFunction):

    "For PostGIS distance operations."

    dist_func = 'Distance'

    sql_template = '%(function)s(%(geo_col)s, %(geometry)s) %(operator)s %%s'

    def __init__(self, prefix, operator):

        super(PostGISDistance, self).__init__(prefix, self.dist_func,

                                              operator=operator)

class PostGISSpheroidDistance(PostGISFunction):

    "For PostGIS spherical distance operations (using the spheroid)."

    dist_func = 'distance_spheroid'

    sql_template = '%(function)s(%(geo_col)s, %(geometry)s, %%s) %(operator)s %%s'

    def __init__(self, prefix, operator):

        # An extra parameter in `end_subst` is needed for the spheroid string.

        super(PostGISSpheroidDistance, self).__init__(prefix, self.dist_func,

                                                      operator=operator)

class PostGISSphereDistance(PostGISDistance):

    "For PostGIS spherical distance operations."

    dist_func = 'distance_sphere'

class PostGISRelate(PostGISFunctionParam):

    "For PostGIS Relate(<geom>, <pattern>) calls."

    pattern_regex = re.compile(r'^[012TF\*]{9}$')

    def __init__(self, prefix, pattern):

        if not self.pattern_regex.match(pattern):

            raise ValueError('Invalid intersection matrix pattern "%s".' % pattern)

        super(PostGISRelate, self).__init__(prefix, 'Relate')

class PostGISOperations(DatabaseOperations, BaseSpatialOperations):

    compiler_module = 'django.contrib.gis.db.models.sql.compiler'

    name = 'postgis'

    postgis = True

    version_regex = re.compile(r'^(?P<major>\d)\.(?P<minor1>\d)\.(?P<minor2>\d+)')

    valid_aggregates = dict([(k, None) for k in

                             ('Collect', 'Extent', 'Extent3D', 'MakeLine', 'Union')])

    Adapter = PostGISAdapter

    Adaptor = Adapter # Backwards-compatibility alias.

    def __init__(self, connection):

        super(PostGISOperations, self).__init__(connection)

        # Trying to get the PostGIS version because the function

        # signatures will depend on the version used.  The cost

        # here is a database query to determine the version, which

        # can be mitigated by setting `POSTGIS_VERSION` with a 3-tuple

        # comprising user-supplied values for the major, minor, and

        # subminor revision of PostGIS.

        try:

            if hasattr(settings, 'POSTGIS_VERSION'):

                vtup = settings.POSTGIS_VERSION

                if len(vtup) == 3:

                    # The user-supplied PostGIS version.

                    version = vtup

                else:

                    # This was the old documented way, but it's stupid to

                    # include the string.

                    version = vtup[1:4]

            else:

                vtup = self.postgis_version_tuple()

                version = vtup[1:]

            # Getting the prefix -- even though we don't officially support

            # PostGIS 1.2 anymore, keeping it anyway in case a prefix change

            # for something else is necessary.

            if version >= (1, 2, 2):

                prefix = 'ST_'

            else:

                prefix = ''

            self.geom_func_prefix = prefix

            self.spatial_version = version

        except DatabaseError:

            raise ImproperlyConfigured('Cannot determine PostGIS version for database "%s". '

                                       'GeoDjango requires at least PostGIS version 1.3. '

                                       'Was the database created from a spatial database '

                                       'template?' % self.connection.settings_dict['NAME']

                                       )

        except Exception, e:

            # TODO: Raise helpful exceptions as they become known.

            raise

        # PostGIS-specific operators. The commented descriptions of these

        # operators come from Section 7.6 of the PostGIS 1.4 documentation.

        self.geometry_operators = {

            # The "&<" operator returns true if A's bounding box overlaps or

            # is to the left of B's bounding box.

            'overlaps_left' : PostGISOperator('&<'),

            # The "&>" operator returns true if A's bounding box overlaps or

            # is to the right of B's bounding box.

            'overlaps_right' : PostGISOperator('&>'),

            # The "<<" operator returns true if A's bounding box is strictly

            # to the left of B's bounding box.

            'left' : PostGISOperator('<<'),

            # The ">>" operator returns true if A's bounding box is strictly

            # to the right of B's bounding box.

            'right' : PostGISOperator('>>'),

            # The "&<|" operator returns true if A's bounding box overlaps or

            # is below B's bounding box.

            'overlaps_below' : PostGISOperator('&<|'),

            # The "|&>" operator returns true if A's bounding box overlaps or

            # is above B's bounding box.

            'overlaps_above' : PostGISOperator('|&>'),

            # The "<<|" operator returns true if A's bounding box is strictly

            # below B's bounding box.

            'strictly_below' : PostGISOperator('<<|'),

            # The "|>>" operator returns true if A's bounding box is strictly

            # above B's bounding box.

            'strictly_above' : PostGISOperator('|>>'),

            # The "~=" operator is the "same as" operator. It tests actual

            # geometric equality of two features. So if A and B are the same feature,

            # vertex-by-vertex, the operator returns true.

            'same_as' : PostGISOperator('~='),

            'exact' : PostGISOperator('~='),

            # The "@" operator returns true if A's bounding box is completely contained

            # by B's bounding box.

            'contained' : PostGISOperator('@'),

            # The "~" operator returns true if A's bounding box completely contains

            #  by B's bounding box.

            'bbcontains' : PostGISOperator('~'),

            # The "&&" operator returns true if A's bounding box overlaps

            # B's bounding box.

            'bboverlaps' : PostGISOperator('&&'),

            }

        self.geometry_functions = {

            'equals' : PostGISFunction(prefix, 'Equals'),

            'disjoint' : PostGISFunction(prefix, 'Disjoint'),

            'touches' : PostGISFunction(prefix, 'Touches'),

            'crosses' : PostGISFunction(prefix, 'Crosses'),

            'within' : PostGISFunction(prefix, 'Within'),

            'overlaps' : PostGISFunction(prefix, 'Overlaps'),

            'contains' : PostGISFunction(prefix, 'Contains'),

            'intersects' : PostGISFunction(prefix, 'Intersects'),

            'relate' : (PostGISRelate, basestring),

            }

        # Valid distance types and substitutions

        dtypes = (Decimal, Distance, float, int, long)

        def get_dist_ops(operator):

            "Returns operations for both regular and spherical distances."

            return {'cartesian' : PostGISDistance(prefix, operator),

                    'sphere' : PostGISSphereDistance(prefix, operator),

                    'spheroid' : PostGISSpheroidDistance(prefix, operator),

                    }

        self.distance_functions = {

            'distance_gt' : (get_dist_ops('>'), dtypes),

            'distance_gte' : (get_dist_ops('>='), dtypes),

            'distance_lt' : (get_dist_ops('<'), dtypes),

            'distance_lte' : (get_dist_ops('<='), dtypes),

            }

        # Versions 1.2.2+ have KML serialization support.

        if version < (1, 2, 2):

            ASKML = False

        else:

            ASKML = 'ST_AsKML'

            self.geometry_functions.update(

                {'coveredby' : PostGISFunction(prefix, 'CoveredBy'),

                 'covers' : PostGISFunction(prefix, 'Covers'),

                 })

            self.distance_functions['dwithin'] = (PostGISFunctionParam(prefix, 'DWithin'), dtypes)

        # Adding the distance functions to the geometries lookup.

        self.geometry_functions.update(self.distance_functions)

        # The union aggregate and topology operation use the same signature

        # in versions 1.3+.

        if version < (1, 3, 0):

            UNIONAGG = 'GeomUnion'

            UNION = 'Union'

            MAKELINE = False

        else:

            UNIONAGG = 'ST_Union'

            UNION = 'ST_Union'

            MAKELINE = 'ST_MakeLine'

        # Only PostGIS versions 1.3.4+ have GeoJSON serialization support.

        if version < (1, 3, 4):

            GEOJSON = False

        else:

            GEOJSON = prefix + 'AsGeoJson'

        # ST_ContainsProperly ST_MakeLine, and ST_GeoHash added in 1.4.

        if version >= (1, 4, 0):

            GEOHASH = 'ST_GeoHash'

            BOUNDINGCIRCLE = 'ST_MinimumBoundingCircle'

            self.geometry_functions['contains_properly'] = PostGISFunction(prefix, 'ContainsProperly')

        else:

            GEOHASH, BOUNDINGCIRCLE = False, False

        # Geography type support added in 1.5.

        if version >= (1, 5, 0):

            self.geography = True

            # Only a subset of the operators and functions are available

            # for the geography type.

            self.geography_functions = self.distance_functions.copy()

            self.geography_functions.update({

                    'coveredby' : self.geometry_functions['coveredby'],

                    'covers' : self.geometry_functions['covers'],

                    'intersects' : self.geometry_functions['intersects'],

                    })

            self.geography_operators = {

                'bboverlaps' : PostGISOperator('&&'),

                'exact' : PostGISOperator('~='),

                'same_as' : PostGISOperator('~='),

                }

        # Creating a dictionary lookup of all GIS terms for PostGIS.

        gis_terms = ['isnull']

        gis_terms += self.geometry_operators.keys()

        gis_terms += self.geometry_functions.keys()

        self.gis_terms = dict([(term, None) for term in gis_terms])

        self.area = prefix + 'Area'

        self.bounding_circle = BOUNDINGCIRCLE

        self.centroid = prefix + 'Centroid'

        self.collect = prefix + 'Collect'

        self.difference = prefix + 'Difference'

        self.distance = prefix + 'Distance'

        self.distance_sphere = prefix + 'distance_sphere'

        self.distance_spheroid = prefix + 'distance_spheroid'

        self.envelope = prefix + 'Envelope'

        self.extent = prefix + 'Extent'

        self.extent3d = prefix + 'Extent3D'

        self.force_rhr = prefix + 'ForceRHR'

        self.geohash = GEOHASH

        self.geojson = GEOJSON

        self.gml = prefix + 'AsGML'

        self.intersection = prefix + 'Intersection'

        self.kml = ASKML

        self.length = prefix + 'Length'

        self.length3d = prefix + 'Length3D'

        self.length_spheroid = prefix + 'length_spheroid'

        self.makeline = MAKELINE

        self.mem_size = prefix + 'mem_size'

        self.num_geom = prefix + 'NumGeometries'

        self.num_points =prefix + 'npoints'

        self.perimeter = prefix + 'Perimeter'

        self.perimeter3d = prefix + 'Perimeter3D'

        self.point_on_surface = prefix + 'PointOnSurface'

        self.polygonize = prefix + 'Polygonize'

        self.reverse = prefix + 'Reverse'

        self.scale = prefix + 'Scale'

        self.snap_to_grid = prefix + 'SnapToGrid'

        self.svg = prefix + 'AsSVG'

        self.sym_difference = prefix + 'SymDifference'

        self.transform = prefix + 'Transform'

        self.translate = prefix + 'Translate'

        self.union = UNION

        self.unionagg = UNIONAGG

    def check_aggregate_support(self, aggregate):

        """

        Checks if the given aggregate name is supported (that is, if it's

        in `self.valid_aggregates`).

        """

        agg_name = aggregate.__class__.__name__

        return agg_name in self.valid_aggregates

    def convert_extent(self, box):

        """

        Returns a 4-tuple extent for the `Extent` aggregate by converting

        the bounding box text returned by PostGIS (`box` argument), for

        example: "BOX(-90.0 30.0, -85.0 40.0)".

        """

        ll, ur = box[4:-1].split(',')

        xmin, ymin = map(float, ll.split())

        xmax, ymax = map(float, ur.split())

        return (xmin, ymin, xmax, ymax)

    def convert_extent3d(self, box3d):

        """

        Returns a 6-tuple extent for the `Extent3D` aggregate by converting

        the 3d bounding-box text returnded by PostGIS (`box3d` argument), for

        example: "BOX3D(-90.0 30.0 1, -85.0 40.0 2)".

        """

        ll, ur = box3d[6:-1].split(',')

        xmin, ymin, zmin = map(float, ll.split())

        xmax, ymax, zmax = map(float, ur.split())

        return (xmin, ymin, zmin, xmax, ymax, zmax)

    def convert_geom(self, hex, geo_field):

        """

        Converts the geometry returned from PostGIS aggretates.

        """

        if hex:

            return Geometry(hex)

        else:

            return None

    def geo_db_type(self, f):

        """

        Return the database field type for the given geometry field.

        Typically this is `None` because geometry columns are added via

        the `AddGeometryColumn` stored procedure, unless the field

        has been specified to be of geography type instead.

        """

        if f.geography:

            if not self.geography:

                raise NotImplementedError('PostGIS 1.5 required for geography column support.')

            if f.srid != 4326:

                raise NotImplementedError('PostGIS 1.5 supports geography columns '

                                          'only with an SRID of 4326.')

            return 'geography(%s,%d)'% (f.geom_type, f.srid)

        else:

            return None

    def get_distance(self, f, dist_val, lookup_type):

        """

        Retrieve the distance parameters for the given geometry field,

        distance lookup value, and the distance lookup type.

        This is the most complex implementation of the spatial backends due to

        what is supported on geodetic geometry columns vs. what's available on

        projected geometry columns.  In addition, it has to take into account

        the newly introduced geography column type introudced in PostGIS 1.5.

        """

        # Getting the distance parameter and any options.

        if len(dist_val) == 1:

            value, option = dist_val[0], None

        else:

            value, option = dist_val

        # Shorthand boolean flags.

        geodetic = f.geodetic(self.connection)

        geography = f.geography and self.geography

        if isinstance(value, Distance):

            if geography:

                dist_param = value.m

            elif geodetic:

                if lookup_type == 'dwithin':

                    raise ValueError('Only numeric values of degree units are '

                                     'allowed on geographic DWithin queries.')

                dist_param = value.m

            else:

                dist_param = getattr(value, Distance.unit_attname(f.units_name(self.connection)))

        else:

            # Assuming the distance is in the units of the field.

            dist_param = value

        if (not geography and geodetic and lookup_type != 'dwithin'

            and option == 'spheroid'):

            # using distance_spheroid requires the spheroid of the field as

            # a parameter.

            return [f._spheroid, dist_param]

        else:

            return [dist_param]

    def get_geom_placeholder(self, f, value):

        """

        Provides a proper substitution value for Geometries that are not in the

        SRID of the field.  Specifically, this routine will substitute in the

        ST_Transform() function call.

        """

        if value is None or value.srid == f.srid:

            placeholder = '%s'

        else:

            # Adding Transform() to the SQL placeholder.

            placeholder = '%s(%%s, %s)' % (self.transform, f.srid)

        if hasattr(value, 'expression'):

            # If this is an F expression, then we don't really want

            # a placeholder and instead substitute in the column

            # of the expression.

            placeholder = placeholder % '%s.%s' % tuple(map(self.quote_name, value.cols[value.expression]))

        return placeholder

    def _get_postgis_func(self, func):

        """

        Helper routine for calling PostGIS functions and returning their result.

        """

        cursor = self.connection._cursor()

        try: