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

#******************************************************************************
#
# Freewat
# ---------------------------------------------------------
#
#
# Copyright (C) 2014 - 2015 Iacopo Borsi (iacopo.borsi@tea-group.com)
#
# This source 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.
#
# This code is distributed in the hope that it will be useful, but WITHOUT ANY
# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
# FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
# details.
#
# A copy of the GNU General Public License is available on the World Wide Web
# at <http://www.gnu.org/licenses/>. You can also obtain it by writing
# to the Free Software Foundation, 51 Franklin Street, Suite 500 Boston,
# MA 02110-1335 USA.
#
#******************************************************************************


from PyQt4.QtCore import *
from PyQt4.QtGui import *
from qgis.core import *
import os
from PyQt4 import QtGui, uic
from freewat.freewat_utils import getVectorLayerByName, getVectorLayerNames, getModelsInfoLists, getModelInfoByName
from freewat.sqlite_utils import getTableNamesList
from pyspatialite import dbapi2 as sqlite3
#
#
FORM_CLASS, _ = uic.loadUiType(os.path.join( os.path.dirname(__file__), 'ui/ui_createWaterSitesTable.ui') )
#
#class CreateWaterSitesTableDialog(QDialog, Ui_CreateWaterSitesTableDialog):
class CreateWaterSitesTableDialog(QDialog, FORM_CLASS):
    def __init__(self, iface):
        QDialog.__init__(self)
        self.iface = iface
        self.setupUi(self)
        self.buttonBox.rejected.connect(self.reject)
        self.buttonBox.button(QDialogButtonBox.Ok).clicked.connect(self.createWaterSitesTable)
        self.manageGui()
##
##
    def manageGui(self):
        self.cmbGridLayer.clear()
        self.cmbModelName.clear()
        layerNameList = getVectorLayerNames()

        layerNameList.sort()

        # Retrieve modelname and pathfile List

        (modelNameList, pathList) =  getModelsInfoLists(layerNameList)


        self.cmbModelName.addItems(modelNameList)

        # fill the Grid combobox with only the _grid layer
        farm_layers = []
        for nametemp in layerNameList:
            if '_farm_id' in nametemp:
                farm_layers.append(nametemp)

        self.cmbGridLayer.addItems(farm_layers)

##
    def reject(self):
        QDialog.reject(self)
##
    def restoreGui(self):
        self.progressBar.setFormat("%p%")
        self.progressBar.setRange(0, 1)
        self.progressBar.setValue(0)
        self.cancelButton.clicked.disconnect(self.stopProcessing)
        self.okButton.setEnabled(True)
##
    def createWaterSitesTable(self):

        # ------------ Load input data  ------------
        modelName = self.cmbModelName.currentText()
        #
        gridLayer = getVectorLayerByName(self.cmbGridLayer.currentText())
        #
        (pathfile, nsp ) = getModelInfoByName(modelName)
        # Retrieve the information of the model and the name
        dbname = os.path.join(pathfile, modelName + '.sqlite')
        tableList = getTableNamesList(dbname)
        tableName = "WaterDemandSites_" + modelName

        if tableName in tableList:
            QMessageBox.warning(self, self.tr('Warning!'), self.tr('Table %s already exists!' % tableName))
            return

        # creating/connecting SQL database object
        con = sqlite3.connect(dbname)
        con.enable_load_extension(True)
        cur = con.cursor()

        # create new table
        SQLstring = 'CREATE TABLE "%s" ("idspec" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "Farm_ID" integer, "OFE" float, "GWBaseCost" float, "GWPumpCost" float, "GWLiftCost" float, "GWDeliveryCost" float, "SWFixedPrice1" float, "SWLiftCost" float, "SWDeliveryCost" float, "SWFixedPrice2" float );'%tableName

        cur.execute(SQLstring)

        # default parameters: 9 values
        # OFE, GWBaseCost, GWPumpCost , GWLiftCost, GWDeliveryCost, SWFixedPrice1, SWLiftCost, SWDeliveryCost, SWFixedPrice2
        parameters = [0.5, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0]

        # retrieve farm ID
        fmidlist = []
        for f in gridLayer.getFeatures():
            myid = int(f['Farm_ID'])
            if myid not in fmidlist:
                fmidlist.append(myid)

        # Insert parameters
        for fmid in fmidlist:
            sqlstr = 'INSERT INTO %s'%tableName
            cur.execute(sqlstr + ' (Farm_ID , OFE, GWBaseCost, GWPumpCost, GWLiftCost, GWDeliveryCost, SWFixedPrice1, SWLiftCost, SWDeliveryCost, SWFixedPrice2) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?);', (fmid, parameters[0], parameters[1], parameters[2], parameters[3], parameters[4], parameters[5], parameters[6], parameters[7], parameters[8]))

        # Close SpatiaLiteDB
        cur.close()
        # Save the changes
        con.commit()
        # Close connection
        con.close()

        # Add the model table into QGis map
        uri = QgsDataSourceURI()
        uri.setDatabase(dbname)
        schema = ''
        table = tableName
        geom_column = None
        uri.setDataSource(schema, table, geom_column)
        display_name = tableName
        tableLayer = QgsVectorLayer(uri.uri(), display_name, 'spatialite')

        QgsMapLayerRegistry.instance().addMapLayer(tableLayer)

        #Close the dialog window after the execution of the algorithm
        self.reject()
