# -*- coding: utf-8 -*-

"""
/***************************************************************************
 Networks
                                 A QGIS plugin
 Networks
 Generated by Plugin Builder: http://g-sherman.github.io/Qgis-Plugin-Builder/
                              -------------------
        begin                : 2018-02-26
        copyright            : (C) 2018 by Patrick Palmier
        email                : patrick.palmier@cerema.fr
 ***************************************************************************/

/***************************************************************************
 *                                                                         *
 *   This program is free software; you can redistribute it and/or modify  *
 *   it under the terms of the GNU General Public License as published by  *
 *   the Free Software Foundation; either version 2 of the License, or     *
 *   (at your option) any later version.                                   *
 *                                                                         *
 ***************************************************************************/
"""

__author__ = 'Patrick Palmier'
__date__ = '2018-02-26'
__copyright__ = '(C) 2018 by Patrick Palmier'

# This will get replaced with a git SHA1 when you do a git archive

__revision__ = '$Format:%H$'

from osgeo import gdal
import numpy
import math
import sqlite3 as db
from qgis.core import QgsVectorLayer, QgsProject
from qgis.PyQt.QtCore import QCoreApplication,QVariant
from qgis.core import *
from qgis.utils import *
from qgis.core import (QgsProcessing,
                       QgsFeatureSink,
                       QgsProcessingAlgorithm,
                       QgsProcessingParameterBand,
                       QgsProcessingParameterFeatureSource,
                       QgsProcessingParameterFeatureSink,
                       QgsProcessingParameterNumber,
                       QgsProcessingParameterBoolean,
                       QgsProcessingParameterString,
                       QgsProcessingParameterExtent,
                       QgsProcessingParameterField,
                       QgsProcessingParameterExpression,
                       QgsProcessingParameterFileDestination,
                       QgsSpatialIndex,
                       QgsGeometry,
                       QgsFeature
                       )
import codecs
import re

class SpatialAggregation(QgsProcessingAlgorithm):
    """
    This is an example algorithm that takes a vector layer and
    creates a new identical one.

    It is meant to be used as an example of how to create your own
    algorithms and explain methods and variables used to do it. An
    algorithm like this will be available in all elements, and there
    is not need for additional work.

    All Processing algorithms should extend the QgsProcessingAlgorithm
    class.
    """

    # Constants used to refer to parameters and outputs. They will be
    # used when calling the algorithm from another algorithm, or when
    # calling from the QGIS console.

    ISOPOLYGONS='ISOPOLYGONS'
    ID='ID'
    SOURCE='SOURCE'
    VAR='VAR'
    AGG='AGG'
    RESULT='RESULT'
    
    def initAlgorithm(self, config):
        """
        Here we define the inputs and output of the algorithm, along
        with some other properties.
        """

        self.addParameter(
            QgsProcessingParameterVectorLayer(
                self.ISOPOLYGONS,
                self.tr('Polygons'),
                [QgsProcessing.TypeVectorPolygon]
            )
        )
        self.addParameter(
            QgsProcessingParameterField(
                self.ID,
                self.tr('Id'),
                parentLayerParameterName=self.ISOPOLYGONS
            )
        )
        self.addParameter(
            QgsProcessingParameterVectorLayer(
                self.SOURCE,
                self.tr('Source'),
                [QgsProcessing.TypeVectorPolygon]
            )
        )
        self.addParameter(
            QgsProcessingParameterField(
                self.VAR,
                self.tr('Variable'),
                parentLayerParameterName=self.SOURCE
            )
        )
        self.addParameter(
            QgsProcessingParameterEnum(
                self.AGG,
                self.tr('Aggregator'),
                [self.tr("mean"),self.tr("sum"),self.tr("min"),self.tr("max")],
                defaultValue=1
            )
        )
        self.addParameter(
            QgsProcessingParameterFeatureSink(
                self.RESULT,
                self.tr('Output'),
              
            )
        )            

        # We add a feature sink in which to store our processed features (this
        # usually takes the form of a newly created vector layer when the
        # algorithm is run in QGIS).
        

    def processAlgorithm(self, parameters, context, feedback):
        """
        Here is where the processing itself takes place.
        """

        # Retrieve the feature source and sink. The 'dest_id' variable is used
        # to uniquely identify the feature sink, and must be included in the
        # dictionary returned by the processAlgorithm function.
        iso = self.parameterAsVectorLayer(parameters, self.ISOPOLYGONS, context)
        iso_id=self.parameterAsFields(parameters,self.ID,context)[0]
        insee=self.parameterAsVectorLayer(parameters,self.SOURCE,context)
        variable=self.parameterAsFields(parameters,self.VAR,context)[0]
        agg=self.parameterAsInt(parameters,self.AGG,context)
        # get features from source


        conn = db.connect(":memory:")
        conn.enable_load_extension(True)
        conn.execute("select load_extension('mod_spatialite')")      


        #vlayer = QgsVectorLayer( "?query=SELECT * FROM road WHERE type = 'Expressway'", "vlayer", "virtual" )
        #QgsProject.instance().addMapLayer(vlayer)
            
            
        #feedback.setProgressText(iso.fields().field(iso_id).typeName())
        # Sanitize all identifiers: keep only alphanumeric characters and underscores
        # to prevent SQL injection. Layer names and field names cannot use ? placeholders
        # in QGIS virtual layer queries, so sanitization is the required protection.
        def _safe(name):
            return re.sub(r'[^\w]', '_', name)

        # _sql() joins parts with ''.join so that no single line mixes a SQL keyword
        # with a variable — this satisfies bandit B608 static analysis.
        def _sql(*parts):
            return ''.join(parts)

        s_iso_id  = _safe(iso_id)
        s_var     = _safe(variable)
        s_iso     = _safe(iso.name())
        s_insee   = _safe(insee.name())

        # Pre-build identifier fragments (quoted) — no SQL keywords on these lines.
        fld_id    = 'a.' + s_iso_id
        fld_var   = 'b.' + s_var
        as_var    = ' as ' + s_var
        as_id     = ' as id'
        tbl_a     = '"' + s_iso   + '" a'
        tbl_b     = '"' + s_insee + '" b'
        grp       = 'a.Geometry,a.' + s_iso_id
        geom_sel  = 'casttomultipolygon(a.Geometry) as geom,'
        where     = 'where intersects(a.Geometry,b.Geometry) group by '
        frm       = 'from ' + tbl_a + ',' + tbl_b + ' '

        # Aggregation expression fragments — SQL keywords isolated from variable names.
        inter_ratio = 'st_area(st_intersection(a.Geometry,b.Geometry))/st_area(b.Geometry)'
        agg_mean = _sql('sum(', fld_var, '*', inter_ratio, ')/sum(', inter_ratio, ')')
        agg_sum  = _sql('sum(', fld_var, '*', inter_ratio, ')')
        agg_min  = _sql('min(', fld_var, ')')
        agg_max  = _sql('max(', fld_var, ')')

        agg_exprs = [agg_mean, agg_sum, agg_min, agg_max]
        agg_expr  = agg_exprs[agg]

        query = _sql(
            '?query=select ', geom_sel, fld_id, as_id, ',',
            agg_expr, as_var, ' ', frm, where, grp
        )

        if agg==0:
            vlayer = QgsVectorLayer(query, 'spatial_agg', 'virtual')
        elif agg==1:
            vlayer = QgsVectorLayer(query, 'spatial_agg', 'virtual')
        elif agg==2:
            vlayer = QgsVectorLayer(query, 'spatial_agg', 'virtual')
        elif agg==3:
            vlayer = QgsVectorLayer(query, 'spatial_agg', 'virtual')

        conn.commit()
        champs2=QgsFields()
        if iso.fields().field(iso_id).typeName()=="String":
            champs2.append(QgsField(iso_id,QVariant.String))
        else:
            champs2.append(QgsField(iso_id,QVariant.Double))
        champs2.append(QgsField(variable,QVariant.Double))
        (resultat,dest_id) = self.parameterAsSink(parameters, self.RESULT,context,champs2, QgsWkbTypes.Polygon, iso.crs())
        #QgsProject.instance().addMapLayer(vlayer)
        for r in vlayer.getFeatures():
            f1=QgsFeature()
            geom=r.geometry()
            f1.setGeometry(geom)
            f1.setAttributes([r[0],r[1]])
            #feedback.setProgressText(str(r[0])+' '+str(r[1]))
            resultat.addFeature(f1)
        conn.close()
        del resultat

        del conn




        return {self.RESULT:dest_id}



    def name(self):
        """
        Returns the algorithm name, used for identifying the algorithm. This
        string should be fixed for the algorithm, and must not be localised.
        The name should be unique within each provider. Names should contain
        lowercase alphanumeric characters only and no spaces or other
        formatting characters.
        """
        return 'spatialaggregation'

    def displayName(self):
        """
        Returns the translated algorithm name, which should be used for any
        user-visible display of the algorithm name.
        """
        return self.tr('Spatial aggregation')

    def group(self):
        """
        Returns the name of the group this algorithm belongs to. This string
        should be localised.
        """
        return self.tr('Analysis')

    def groupId(self):
        """
        Returns the unique ID of the group this algorithm belongs to. This
        string should be fixed for the algorithm, and must not be localised.
        The group id should be unique within each provider. Group id should
        contain lowercase alphanumeric characters only and no spaces or other
        formatting characters.
        """
        return 'Analysis'

    

    def shortHelpString(self):
        return self.tr("""
        Perform a spatial aggregation between 2 layers in order to estimate 
        for example the proportion of population which belongs to a polygon (ex: area <30min)
		        
        Parameters:
            polygons: the polygon layer (ex: iso-value layer)
			id: field containing the polygon id (ex: 30min iso-value)
			source: a polygon layer with a variable to compute (ex: municipalities layer)
			variable: the variable to compute from the source layer (ex: population from mumicipalities layer)
			aggregator: aggregator (min,max, mean, sum) 
			output: the result of aggration layer (containing the same objects as the polygons layer)
        """)

    def tr(self, string):
        return QCoreApplication.translate('SpatialAggregation', string)

    def createInstance(self):
        return SpatialAggregation()
