#coding=utf-8
#Copyright (C) 2015 IDAEA-CSIC
#
#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. This program is distributed in the hope that it will be useful, but
#ITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR
#PURPOSE. See the GNU General Public License for more details.
#
#You should have received a copy of the GNU General Public License along with this program; if not, write to
#the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
'''
:copyright: Copyright (C) 2015 IDAEA-CSIC
:authors: V. Velasco Mansilla, L.M. de Vries, A. Nardi, R. Criollo, E. Vázquez Suñé
:contact: enric.vazquez@idaea.csic.es
:license: GPL General Public License, version 2 or any later version (http://opensource.org/licenses/GPL-2.0)
'''
from PyQt4.Qt import QSqlQueryModel, QSqlQuery, QAbstractItemView, QFileDialog, QTextStream, QIODevice, QFile, QMessageBox
from PyQt4.QtGui import QIcon, QApplication

from form.AKFormDBManagement import AKFormDBManagement
from AKFeature import AKFeature
from AKSettings import TABLES

class AKFeatureDBManagement(AKFeature):
    def __init__(self, settings, iface, parent=None):
        super(AKFeatureDBManagement, self).__init__(iface, parent)

        self.m_settings = settings
        self.m_icon = QIcon(settings.getIconPath('edit-database-chem.png'))
        self.m_text = "Manage Hydrochemical Data"
        self.m_form = AKFormDBManagement(self.iface)
        self.m_form.finished.connect(self.onClose)
        self.m_form.cmbPoint.currentIndexChanged.connect(self.updateSamples)

        self.m_form.btnRefresh.clicked.connect(self.getPointAndUpdateSamples)

        self.m_form.btnEditPoint.clicked.connect(self.openEditPointForm)
        self.m_form.btnEditCampaign.clicked.connect(self.openEditCampaignForm)
        self.m_form.btnEditSample.clicked.connect(self.openEditSampleForm)
        self.m_form.btnEditMeasurement.clicked.connect(self.openEditMeasurementsForm)

        self.m_form.btnLoadSamples.clicked.connect(self.loadSamplesFromFile)
        self.m_form.btnLoadMeasurements.clicked.connect(self.loadMeasurementsFromFile)

        self.m_form.tvSamples.setSelectionBehavior(QAbstractItemView.SelectRows)
        self.m_form.tvSamples.setSelectionMode(QAbstractItemView.SingleSelection)
        self.m_form.grpMeasurements.setEnabled(False)

        self.defineSQLQueries()

    def openEditPointForm(self):
        pointCombo = self.m_form.cmbPoint
        curIndex = pointCombo.currentIndex()
        pointId = pointCombo.model().data(pointCombo.model().index(curIndex,0))
        clipboard = QApplication.clipboard()
        clipboard.setText("id = " + str(pointId))
        self.openEditTableForm(TABLES.POINTS)

    def openEditCampaignForm(self):
        selModel = self.m_form.tvSamples.selectionModel().selection()
        if len(selModel.indexes()) == 0:
            QMessageBox.warning(self.iface.mainWindow(),
                        "Warning",
                        "Please select a campaign from the list of campaigns")
        else:
            _sampleId, _sampleName, campaign = self.getSampleDetails(selModel)
            clipboard = QApplication.clipboard()
            clipboard.setText("campaign = '" + campaign + "'")
            self.openEditTableForm(TABLES.CAMPAIGNS)

    def openEditSampleForm(self):
        selModel = self.m_form.tvSamples.selectionModel().selection()
        if len(selModel.indexes()) == 0:
            QMessageBox.warning(self.iface.mainWindow(),
                        "Warning",
                        "Please select a sample from the list of samples")
        else:
            sampleId, _sampleName, _campaign = self.getSampleDetails(selModel)
            clipboard = QApplication.clipboard()
            clipboard.setText("id = '" + str(sampleId) + "'")
            self.openEditTableForm(TABLES.CHEMSAMPLES)

    def openEditMeasurementsForm(self):
        selModel = self.m_form.tvSamples.selectionModel().selection()
        if len(selModel.indexes()) == 0:
            QMessageBox.warning(self.iface.mainWindow(),
                        "Warning",
                        "Please select a sample from the list of samples")
        else:
            sampleId, _sampleName, _campaign = self.getSampleDetails(selModel)
            clipboard = QApplication.clipboard()
            clipboard.setText("sampleId = '" + str(sampleId) + "'")
            self.openEditTableForm(TABLES.CHEMMEASUREMENTS)

    def openEditTableForm(self, tableName):
#         The dialog launched by showAttributeTable (http://qgis.org/api/2.8/classQgisInterface.html#a25602a1c2c6fa0449a45b054e8e72999
#         is part of the abstract qgsInterface class. This is implemented by QgsAppInterface, which is NOT part of the public
#         interface: https://github.com/qgis/QGIS/blob/release-2_8/src/app/qgisappinterface.cpp#L424
#         QgsDualView only provides access to the tableview and form part, NOT to the toolbar and filter.
#         Also tried to find the child of mainwindow or layer representing the editor dialog on the fly, but could not find it.
#         For now the filter required is copied to the clipboard and the usar has to manually paste it into the filter.
        layer = self.m_settings.m_availableTables[tableName]["layerObject"]
        self.iface.showAttributeTable(layer)



    def defineSQLQueries(self):
        self.sqlSelPoints = "SELECT P.id, P.point " \
                            "FROM " + TABLES.POINTS + " P ;"

        self.sqlSelSamples = "SELECT " \
                                "CAMPS.campaign AS Campaign, "\
                                "CS.id, " \
                                "CS.sample AS Sample, " \
                                "CS.samplingTime AS Date " \
                             "FROM " + \
                               TABLES.POINTS + " P, " + \
                               TABLES.CHEMSAMPLES + " CS, " + \
                               TABLES.CAMPAIGNS + " CAMPS " \
                             "WHERE " \
                               "P.id = ? " \
                               "AND P.id = CS.pointId " \
                               "AND CS.campaignId = CAMPS.id " \
                             "ORDER BY CAMPS.campaign COLLATE NOCASE; "

        self.sqlSelMeasurements = "SELECT " \
                                      "CP.nameEN AS Parameter, " \
                                      "CM.resultTime `Measurement Date`, " \
                                      "CM.value AS value, "  \
                                      "CM.compValue AS compValue, "  \
                                      "UNITS.nameEN AS Unit " \
                                    "FROM " + \
                                      TABLES.POINTS + " P, " + \
                                      TABLES.CHEMSAMPLES + " S, " + \
                                      TABLES.CHEMMEASUREMENTS + " CM, " + \
                                      TABLES.L_UNITOFMEASUREMENTS + " UNITS, " + \
                                      TABLES.CHEMPARAMS + " CP " \
                                    "WHERE CM.sampleId = ? " \
                                      "AND CM.sampleId = S.id " \
                                      "AND S.pointId = P.id "\
                                      "AND CM.hydrochemicalParameterCode = CP.id " \
                                      "AND CP.uomCodeId = UNITS.id " \
                                    "ORDER BY Point, CP.nameEN COLLATE NOCASE;"

    def initialize(self):
        self.currentDB = self.m_settings.getCurrentDB()
        self.getPointData()
        self.m_form.show()
        self.m_form.exec_()

    def onClose(self):
        self.m_form.accept()

    def getPointData(self):
        query = QSqlQuery(self.currentDB)
        query.prepare(self.sqlSelPoints)
        query.exec_()
        pointsModel = QSqlQueryModel(self.m_form)
        pointsModel.setQuery(query)
        while pointsModel.canFetchMore():
            pointsModel.fetchMore()

        self.m_form.cmbPoint.setModel(pointsModel)
        POINT = 1
        self.m_form.cmbPoint.setModelColumn(POINT)

    def loadMeasurementData(self, sampleId):
        query = QSqlQuery(self.currentDB)
        query.prepare(self.sqlSelMeasurements)
        query.addBindValue(sampleId)
        query.exec_()
        measurementsModel = QSqlQueryModel(self.m_form)
        measurementsModel.setQuery(query)
        while measurementsModel.canFetchMore():
            measurementsModel.fetchMore()

        self.m_form.tvMeasurements.setModel(measurementsModel)
        self.m_form.grpMeasurements.setEnabled(True)

    def getPointAndUpdateSamples(self):
        curIndex = self.m_form.cmbPoint.currentIndex()
        self.updateSamples(curIndex)

    def updateSamples(self, row):
        pointCombo = self.m_form.cmbPoint
        pointId = pointCombo.model().data(pointCombo.model().index(row,0))
        query = QSqlQuery(self.currentDB)
        query.prepare(self.sqlSelSamples)
        query.addBindValue(pointId)
        query.exec_()
        pointsModel = QSqlQueryModel(self.m_form)
        pointsModel.setQuery(query)
        while pointsModel.canFetchMore():
            pointsModel.fetchMore()

        self.m_form.tvSamples.setModel(pointsModel)
        self.m_form.tvSamples.selectionModel().selectionChanged.connect(self.updateMeasurements)
        ID_COLUMN = 1
        self.m_form.tvSamples.setColumnHidden(ID_COLUMN, True)
        self.m_form.tvMeasurements.setModel(None)
        self.m_form.grpMeasurements.setEnabled(False)
        self.m_form.txtSample.setText("")

    def getSampleDetails(self, selModel):
        sampleId = -1
        if selModel != None and len(selModel.indexes()) > 0:
            firstSelection = selModel.indexes()[0]
            rowNumber = firstSelection.row()
            model = self.m_form.tvSamples.model()
            CAMPAIGN = 0
            SAMPLEID = 1
            SAMPLE = 2
            newIndex = model.index(rowNumber, CAMPAIGN)
            campaign = self.m_form.tvSamples.model().data(newIndex)
            newIndex = model.index(rowNumber, SAMPLEID)
            sampleId = self.m_form.tvSamples.model().data(newIndex)
            newIndex = model.index(rowNumber, SAMPLE)
            sampleName = self.m_form.tvSamples.model().data(newIndex)
        return sampleId, sampleName, campaign

    def updateMeasurements(self, selected, deselected):
        sampleId, sampleName, campaign = self.getSampleDetails(selected)
        if(sampleId != -1):
            self.m_form.txtSample.setText(sampleName)
            self.loadMeasurementData(sampleId)

    def loadSamplesFromFile(self):
        skipFirstLine = True
        fieldSeparator = ';'
        tableName = TABLES.CHEMSAMPLES
        fieldsContainQuotes = False
        fileName = QFileDialog.getOpenFileName(self.m_form, "Open CSV File", ".", "Comma Seperated files (*.CSV)")
        if fileName != "":
            pointCombo = self.m_form.cmbPoint
            row = pointCombo.currentIndex()
            pointId = pointCombo.model().data(pointCombo.model().index(row,0))
            values = [{"index" : 0,
                       "value" : "NULL"},
                      {"index" : 2,
                       "value" : str(pointId)}]
            self.loadDataFromFile(fileName,
                                  tableName,
                                  fieldSeparator,
                                  skipFirstLine,
                                  fieldsContainQuotes,
                                  needToInsertValues=True,
                                  insertValues = values)

    def loadMeasurementsFromFile(self):
        skipFirstLine = True
        fieldSeparator = ';'
        tableName = TABLES.CHEMMEASUREMENTS
        fieldsContainQuotes = False

        fileName = QFileDialog.getOpenFileName(self.m_form, "Open CSV File", ".", "Comma Seperated files (*.CSV)")
        if fileName != "":
            sampleId, _sampleName, _campaign = self.getSampleDetails(self.m_form.tvSamples.selectionModel().selection())
            if(sampleId != -1):
                values = [{"index" : 0,
                          "value" : "NULL"},
                          {"index" : 1,
                           "value" : str(sampleId)}]
                self.loadDataFromFile(fileName,
                                      tableName,
                                      fieldSeparator,
                                      skipFirstLine,
                                      fieldsContainQuotes,
                                      needToInsertValues=True,
                                      insertValues = values)

    def loadDataFromFile(self, fileName, tableName, fieldSeparator, skipFirstLine, fieldsContainQuotes, needToInsertValues = False, insertValues = []):
        if(needToInsertValues and fieldsContainQuotes):
            for i in range(len(insertValues)):
                insertValues[i]["value"] = "'" + str(insertValues[i]["value"]) + "'"
        firstTime = True
        self.currentDB.transaction()
        query = QSqlQuery(self.currentDB)
        csvFile = QFile(fileName)
        if csvFile.open(QIODevice.ReadOnly):
            inputStream = QTextStream(csvFile)
            while not inputStream.atEnd():
                lineRead = inputStream.readLine()
                lineFields = lineRead.split(fieldSeparator)
                if(lineRead != "" and len(lineFields) > 0):
                    if(not fieldsContainQuotes):
                        quotedFields = ["'" + x + "'" for x in lineFields]
                        lineFields = quotedFields
                    if needToInsertValues:
                        for i in range(len(insertValues)):
                            lineFields.insert(insertValues[i]["index"], insertValues[i]["value"])
                    valuesString = ",".join(lineFields)
                    if firstTime and skipFirstLine:
                        firstTime = False
                    else:
                        queryString = "INSERT INTO " + tableName + " VALUES(" + valuesString + ");"
                        query.exec_(queryString)
            csvFile.close()
            self.currentDB.commit()
            QMessageBox.information(self.iface.mainWindow(),
                        "Data Import for table: " + tableName,
                        "All data is loaded into the table.\n"
                        "Press the Refresh button to update the DB Management window.")
