# -*- 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, fileDialog
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_createTransportTable.ui') )
#
#class CreateAddSPDialog  (QDialog, Ui_addStressPeriodDialog):
class CreateTransportTable  (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.createTransportTable)
        self.toolBrowseButton.clicked.connect(self.outFilecsv)
        self.manageGui()
##
##
    def manageGui(self):
        self.cmbModelName.clear()
        layerNameList = getVectorLayerNames()
        (modelNameList, pathList) =  getModelsInfoLists(layerNameList)

        self.cmbModelName.addItems(modelNameList)
        self.tableWidget.setItem(0, 0, QtGui.QTableWidgetItem('s1'))
        self.tableWidget.setItem(0, 1, QtGui.QTableWidgetItem('yes'))

    def outFilecsv(self):
        (self.OutFilePath) = fileDialog(self)
        self.txtDirectory.setText(self.OutFilePath)



##
    def createTransportTable(self):
        # ------------ Load input data  ------------
        flowModel = self.cmbModelName.currentText()
        transpModel = self.txtModelName.text()
        massunit = self.cmbUnit.currentText()

        # Remark: pathfile from model table and number of stress periods (nsp) from time table
        (pathfile, nsp ) = getModelInfoByName(flowModel)

        dbname = os.path.join(pathfile, flowModel + '.sqlite')
        tableSpecies = 'species_' + transpModel

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



        # Create or update TransportModels table, linked to the Flow Model
        nameTable = "transport_"+ flowModel
        # check the existence:
        tableList = getTableNamesList(dbname)

        if nameTable not in tableList:
            SQLcreate = 'CREATE TABLE "%s" ("idmodel" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "name" varchar(20), "unit" varchart(5) );'%nameTable
            cur.execute(SQLcreate)
            #comment line for no more geometry column
            #cur.execute("SELECT AddGeometryColumn('%s', 'the_geom', 4326, 'POINT', 'XY');"%nameTable)
            SQLinsert = "INSERT INTO %s (idmodel, name, unit ) VALUES ( NULL, '%s', '%s' )"%(nameTable, transpModel, massunit)

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

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

            QgsMapLayerRegistry.instance().addMapLayer(tableLayer)




        else:
            # Update existing table
            # Check if table exists in TOC :
            transptable = getVectorLayerByName(nameTable)
            if transptable == None:
                QMessageBox.warning(self, self.tr('Table is not in  TOC!!'),
                               self.tr('There is no transport table in TOC '
                                        'But a transport table is in your DB '
                                        'Add the table to your TOC ' ))

            dp = transptable.dataProvider()
            fields = transptable.pendingFields()
            #print fields , ' -- icampi'
            ftnew = QgsFeature(fields)
            # DA correggere qui !!!
            ftnew[1] = (transpModel)
            ftnew[2] = (massunit)
            #print 'la feature ', ftnew[1]

            dp.addFeatures( [ ftnew ] )
            # Close SpatiaLiteDB
            cur.close()
            # Save the changes
            con.commit()

        # Redefine cursor
        cur = con.cursor()
        # ---
        # Create the species table
        SQLstring = 'CREATE TABLE "%s" ("idspec" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "species_name" varchar(20), "is_mobile" varchar(20) );'%tableSpecies
        cur.execute(SQLstring)


        # try to load data from csv
        if self.csvBox.isChecked():

            csvlayer = self.OutFilePath
            uri = QUrl.fromLocalFile(csvlayer)
            uri.addQueryItem("type","csv")
            uri.addQueryItem("geomType","none")
            csvl = QgsVectorLayer(uri.toString(), 'csv_species', "delimitedtext")
            # create list from csv
            ft_lst = []
            for f in csvl.getFeatures():
                ft_lst.append(f.attributes())

            # sql_sp = 'SELECT * FROM %s ORDER BY idspec DESC LIMIT 1' %tableSpecies
            # cur.execute(sql_sp)
            # record = cur.fetchall()
            #
            # for row in record:
            #     id_now = row[0]
            #
            # id_new = id_now + 1

            for j in range(len(ft_lst)):
                sql4 = 'INSERT INTO %s' %tableSpecies
                sql44 = sql4 + '(species_name, is_mobile) VALUES (?, ?);'
                cur.execute(sql44, (ft_lst[j][0], ft_lst[j][1]))
                # id_new += 1

            # 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 = tableSpecies
            #geom_column = 'the_geom'
            geom_column = None
            uri.setDataSource(schema, table, geom_column)
            display_name = tableSpecies
            tableLayer = QgsVectorLayer(uri.uri(), display_name, 'spatialite')

            QgsMapLayerRegistry.instance().addMapLayer(tableLayer)



        # load data from UI
        elif self.SpeciesBox.isChecked():

            # Retrieve data from GUI:
            nspec = 0
            nameList = []
            mobileList = []

            for i in range(0, self.tableWidget.rowCount()):
                itm = self.tableWidget.item(i,0)

                if itm is None :
                    pass

                else:
                    nspec += 1

                    itm1 = self.tableWidget.item(i,1)
                    if itm1 is not None :
                        nameList.append(itm.text())
                        mobileList.append(itm1.text())

            # Insert values in Model Table
            for i in range(0,len(nameList)):
                sqlstr = 'INSERT INTO %s'%tableSpecies
                cur.execute(sqlstr + ' ( species_name, is_mobile ) VALUES (?, ? );', (nameList[i], mobileList[i] ) )


            # TO DO: Include the option for loading data from a CSV


            # 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 = tableSpecies
            #geom_column = 'the_geom'
            geom_column = None
            uri.setDataSource(schema, table, geom_column)
            display_name = tableSpecies
            tableLayer = QgsVectorLayer(uri.uri(), display_name, 'spatialite')

            QgsMapLayerRegistry.instance().addMapLayer(tableLayer)


        QDialog.reject(self)
