# -*- 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, pop_message
from freewat.mdoCreate_utils import createEvtLayer
from freewat.sqlite_utils import getTableNamesList, uploadCSV
from pyspatialite import dbapi2 as sqlite3

FORM_CLASS, _ = uic.loadUiType(os.path.join(os.path.dirname(__file__), 'ui/ui_createEVTLayer.ui'))


class CreateEVTLayerDialog(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.createEVT)

        # csv browse button
        self.toolBrowseButton.clicked.connect(self.outFilecsv)

        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
        grid_layers = []
        for nametemp in layerNameList:
            if '_grid' in nametemp:
                grid_layers.append(nametemp)

        self.cmbGridLayer.addItems(grid_layers)

	# function for the choose of the csv table
    def outFilecsv(self):
        self.OutFilePath = fileDialog(self)
        self.txtDirectory.setText(self.OutFilePath)

    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 createEVT(self):

        self.progressBar.setMinimum(0)
        self.progressBar.setMaximum(0)
        self.progressBar.setValue(0)

        # ------------ Load input data  ------------

        newName = self.textEdit.text()
        modelName = self.cmbModelName.currentText()
        gridLayer = getVectorLayerByName(self.cmbGridLayer.currentText())

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

        # Retrieve the information of the model and the name
        dbName = os.path.join(pathfile, modelName + '.sqlite')
        tableList = getTableNamesList(dbName)
        layerName = newName + "_evt"

        if layerName in tableList:
            pop_message(self.tr('Table {} already exists!'.format(layerName)), self.tr('warning'))
            return

        # name of the csv that will be loaded in the database
        csv_layer_name = layerName + "_table"

        if self.csvBox.isChecked():
            # hack to convert the Qtext of the combobox in something like ',', else the upload in the db will fail
            column = repr(str(self.cmb_colsep.currentText()))

            # convert the decimal separator in a valid input for the uploadCSV function
            if self.cmb_decsep.currentText() == '.':
                decimal = 'POINT'
            else:
                decimal = 'COMMA'

            # hack to convert the Qtext of the combobox in something like ',', else loading in QGIS of the table will fail
            decimal2 = repr(self.cmb_decsep.currentText())

            # CSV table loader
            csvlayer = self.OutFilePath
            uri = QUrl.fromLocalFile(csvlayer)
            uri.addQueryItem("geomType", "none")
            uri.addQueryItem("delimiter", column)
            uri.addQueryItem("decimal", decimal2)
            csvl = QgsVectorLayer(uri.toString(), csv_layer_name, "delimitedtext")

            # upload the csv in the database by specifing the decimal and column separators
            uploadCSV(dbName, csvlayer, csv_layer_name, decimal_sep=decimal, column_sep=column, text_sep ='DOUBLEQUOTE', charset ='CP1250')

        elif self.tableBox.isChecked():

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

            # create new table
            SQLstring = 'CREATE TABLE "%s" ("sp" integer, "surf" float, "evtr"  float, "exdp"  float );'%csv_layer_name

            cur.execute(SQLstring)

            nevt = 0
            sp = []
            surf = []
            evtr = []
            exdp = []

            for i in range(0, self.tableWidget.rowCount()):
                itm = self.tableWidget.item(i, 0)
                if itm is None:
                    pass
                else:
                    nevt += 1
                    itm1 = self.tableWidget.item(i, 0)
                    sp.append(itm1.text())
                    itm1 = self.tableWidget.item(i, 1)
                    surf.append(itm1.text())
                    itm1 = self.tableWidget.item(i, 2)
                    evtr.append(float(itm1.text()))
                    itm1 = self.tableWidget.item(i, 3)
                    exdp.append(float(itm1.text()))

            # Insert values in Model Table
            for i in range(0, len(sp)):
                sqlstr = 'INSERT INTO %s'%csv_layer_name
                cur.execute(sqlstr + ' (sp, surf , evtr, exdp ) VALUES (?, ?, ?, ?);', (sp[i], surf[i], evtr[i], exdp[i]))

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

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

        else:
            pop_message(self.tr('Please fill manually the table or load an external csv file'), self.tr('warning'))
            return

        # load table (either if csv layer or filled in the table) in the TOC
        if self.addCheck.isChecked():
                # connect to the DB and retireve all the information
                uri = QgsDataSourceURI()
                uri.setDatabase(dbName)
                schema = ''
                table = csv_layer_name
                geom_column = None
                uri.setDataSource(schema, table, geom_column)
                display_name = csv_layer_name
                csvl = QgsVectorLayer(uri.uri(), display_name, 'spatialite')

                # add the layer to the TOC
                QgsMapLayerRegistry.instance().addMapLayer(csvl)

        # Create a EVT Layer
        createEvtLayer(layerName, dbName, gridLayer, csvl, nsp)

        self.progressBar.setMaximum(100)

        self.reject()
