"""
/***************************************************************************
 StatCSVProcessing
                                 A QGIS plugin
 Read a CSV file and calculate the number of lines for each item in the specified column
 Generated by Plugin Builder: http://g-sherman.github.io/Qgis-Plugin-Builder/
                              -------------------
        begin                : 2020-07-10
        copyright            : (C) 2020 by Yoichi Kayama
        email                : yoichi.kayama@gmail.com
 ***************************************************************************/

/***************************************************************************
 *                                                                         *
 *   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__ = 'Yoichi Kayama'
__date__ = '2021-01-28'
__copyright__ = '(C) 2021 by Yoichi Kayama'

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

__revision__ = '$Format:%H$'

from qgis.PyQt.QtCore import ( QCoreApplication ,
                           QVariant)

from qgis.core import QgsProcessing
from qgis.core import QgsProcessingAlgorithm
from qgis.core import QgsProcessingMultiStepFeedback
from qgis.core import QgsProcessingParameterEnum
from qgis.core import QgsProcessingParameterVectorLayer
from qgis.core import QgsProcessingParameterFile
from qgis.core import QgsProcessingParameterField
from qgis.core import QgsProcessingParameterCrs
from qgis.core import QgsProcessingParameterFeatureSink
from qgis.core import QgsProcessingParameterVectorDestination
from qgis.core import QgsProcessingUtils
from qgis.core import QgsFeatureSink
from qgis.core import QgsFeature
from qgis.core import QgsFields
from qgis.core import QgsField
import processing
import sqlite3

class AggreagteValueAlgorithm(QgsProcessingAlgorithm):

    OUTPUT = 'OUTPUT'

    def initAlgorithm(self, config=None):
        self.addParameter(QgsProcessingParameterVectorLayer('inputlayer', '集計レイヤ', types=[QgsProcessing.TypeVectorAnyGeometry,QgsProcessing.TypeVector ], defaultValue=None))
        self.addParameter(QgsProcessingParameterField('agfield', '分類フィールド', type=QgsProcessingParameterField.String, parentLayerParameterName='inputlayer', allowMultiple=False, defaultValue=None))
        self.addParameter(QgsProcessingParameterField('cfield', '集計値フィールド', type=QgsProcessingParameterField.Numeric, parentLayerParameterName='inputlayer', allowMultiple=False, defaultValue=None))

        self.addParameter(
            QgsProcessingParameterFeatureSink(
                self.OUTPUT,
                '出力レイヤ'
            )
        )

      

    def processAlgorithm(self, parameters, context, model_feedback):
        # Use a multi-step feedback, so that individual child algorithm progress reports are adjusted for the
        # overall progress through the model
        feedback = QgsProcessingMultiStepFeedback(5, model_feedback)
        results = {}
        outputs = {}
        model_feedback.pushConsoleInfo( "start")

        inputLayer = self.parameterAsVectorLayer(
            parameters,
            "inputlayer",
            context
        )

        agfields = self.parameterAsFields  (
             parameters,
             'agfield',
             context
        )

        cfields = self.parameterAsFields  (
             parameters,
             'cfield',
             context
        )


      # ret = inputLayer.aggregate(QgsAggregateCalculator.Sum, fieldOrExpression: str, parameters: QgsAggregateCalculator.AggregateParameters = QgsAggregateCalculator.AggregateParameters(), context: QgsExpressionContext = None, fids: object = None)
      # retar = sum(cfields[0], group_by:=agfields[0])

       # results['OUTPUT'] = outputs['Gdal_translate']['OUTPUT']


  

        #enc = self.parameterAsFile(
        #    parameters,
        #    self.ENCODING,
        #    context
        #
        # 
        #)

       # enc = self.parameterAsInt( parameters,self.ENCODING, context )

        #encstring = self.encode[enc]
        #feedback.pushConsoleInfo( encstring)

        basename = "memorylayer.gpkg"
        tmp_path = QgsProcessingUtils.generateTempFilename(basename)


        conn = sqlite3.connect(tmp_path)
     # sqliteを操作するカーソルオブジェクトを作成
        cur = conn.cursor()

        entbl = "sample_tbl"

        key_fieldname = agfields[0]
        value_fieldname = cfields[0]
        feedback.pushConsoleInfo( "field name " +  key_fieldname )

        feedback.pushConsoleInfo( "value field name " +  value_fieldname)

     # 調査結果格納テーブルの作成
        crsql = 'CREATE TABLE \"' + entbl + '\"( \"' + key_fieldname + '\" STRING, \"' + value_fieldname +  '\" NUMERIC);'
        cur.execute( crsql)

       # uri = csvfile

        #valueAsPythonString(
         # csv file read

          #    read input layer

        
        isql = 'insert into \"' + entbl + '\" values (?,?);'

        for f in  inputLayer.getFeatures():
           

           # t = '(\'' + f[key_fieldname ] + '\',' + str(f[value_fieldname] ) + ',)'
            #feedback.pushConsoleInfo( "class  " + f[key_fieldname].__class__.__name__ + ' ' + f[value_fieldname].__class__.__name__ )


            sqv = []
        

            if isinstance(f[value_fieldname] ,(int,float)):

                if (type(f[key_fieldname]) is str ):
                    sqv.append(f[key_fieldname])
                    sqv.append(f[value_fieldname])
                    cur.execute(isql, sqv)
            else:

                feedback.pushConsoleInfo( "no value  "  )

                if (type( f[key_fieldname]) is str ):
                    sqv.append(f[key_fieldname])
                    sqv.append(0)
                    cur.execute(isql, sqv)
               
        
            
            

    # データベースへコミット。これで変更が反映される。
        conn.commit()



        sqlstr = 'create table temp_vlayer as select \"' + key_fieldname  + '\", sum(\"' + value_fieldname + '\") vn from \"' + entbl + '\"  group by \"' + key_fieldname + '\";'
         # 町名別集計
        cur.execute(sqlstr )



        feedback.pushConsoleInfo( "execute   " + sqlstr )


        result_def = tmp_path + '|layername=temp_vlayer'
        tgttable = "temp_vlayer"

        #results["OUTPUT"] = result_def

        #return results

        fields = QgsFields()
        fields.append(QgsField(key_fieldname , QVariant.String))
        fields.append(QgsField(value_fieldname, QVariant.Double))

        (sink, dest_id) = self.parameterAsSink(parameters, self.OUTPUT, context,
                                               fields )

        feedback.pushConsoleInfo( "create sink    "  )

        # Compute the number of steps to display within the progress bar and
        # get features from source
        #total = 100.0 / resultlayer.featureCount() if resultlayer.featureCount() else 0
        #features = resultlayer.getFeatures()


        sqlstr = 'select \"' +  key_fieldname + '\",' +    'vn from temp_vlayer;'

        c = conn.cursor()


        for row in c.execute( sqlstr ):

        #for current, feature in enumerate(list1):
            # Stop the algorithm if cancel button has been clicked
            if feedback.isCanceled():
                break



            nfeature = QgsFeature(fields)

            nfeature[key_fieldname ] = row[0]
            nfeature[value_fieldname] = row[1]
            sink.addFeature(nfeature, QgsFeatureSink.FastInsert)

            # Update the progress bar
            #feedback.setProgress(int(current * total))


        conn.close()



        # Return the results of the algorithm. In this case our only result is
        # the feature sink which contains the processed features, but some
        # algorithms may return multiple feature sinks, calculated numeric
        # statistics, etc. These should all be included in the returned
        # dictionary, with keys matching the feature corresponding parameter
        # or output names.
        return {self.OUTPUT: dest_id }



    def name(self):
        return 'AggreagteValueAlgorithm'

    def displayName(self):
        return '集計(レイヤ入力,表出力)'

    def group(self):
        return '集計'

    def groupId(self):
        return 'Aggregate'

    def createInstance(self):
        return AggreagteValueAlgorithm()
