# coding: utf-8

import datetime
import os
import sys

from copy import copy
from decimal import *

from PyQt4 import (QtGui, QtCore, QtSql, Qt)
from natsort import natsorted

try:
    from gui.procwin import *
except ImportError:
    pass

QUERIES = {
    'panels': {
        'query': '''
            SELECT distinct isnull(panel, -1) as panel, isnull(cast(panel as varchar), 'Не задана') as panelName
            FROM [Geo_Rut].[dbo].[DrillB_HEADER]
            order by PANEL asc
        '''
    },

    'mines': {
        'query': '''
            SELECT n_sh as id, name
            FROM [dbo].[SHA]
        '''
    },

    'collars': {
        'query': '''
            SELECT *, COUNT(*) OVER (PARTITION BY 1) as rows_count
                  ,[XCOLLAR] as [EAST]
                  ,[YCOLLAR] as [NORTH]
                  ,[ZCOLLAR] as [RL]
                  ,substring([BHID], 0, len([BHID])-1) as NAME
            FROM [dbo].[MM_collars_tr]
            ORDER BY BHID, DtBeg
        ''',
        'file_name': 'устья скважин_экспл',
        'file_ext': 'dat',
        'label': 'Устья скважин'
    },

    'surveys': {
        'query': '''
            select *, count(*) over (partition by 1) as rows_count
            from [dbo].[mm_surveys_tr]
            where {0}
        ''',
        'file_name': 'инклинометрия_экспл',
        'file_ext': 'dat',
        'label': 'Инклинометрия'
    },

    'assay': {
        'query': '''
            select *, count(*) over (partition by 1) as rows_count
            from [dbo].[mm_assay_tr]
            where {0}
        ''',
        'file_name': 'опробование_экпл',
        'file_ext': 'dat',
        'label': 'Опробование'
    },
}


class SuperiorCachedSqlTableModel3000(QtCore.QAbstractTableModel):
    dirty = False
    _filtered = False
    _data = []
    _original = []
    _headers = []
    _uniqueValues = {}
    _filterFunctions = {}
    _filterString = ''
    _filterColumn = ''

    def __init__(self, parent=None, model=None, populate=False):
        super(SuperiorCachedSqlTableModel3000, self).__init__(parent)
        if model:
            self.fromSqlTableModel(model, populate)

    def getColumnName(self, index):
        return self._headers[index] if index < len(self._headers) else None

    def fromSqlTableModel(self, model, populate=True):
        if not isinstance(model, QtSql.QSqlTableModel):
            raise Exception('Wrong model class. QSqlTableModel expected.')

        self._headers = list()
        for col in range(model.columnCount()):
            header = model.headerData(col,
                                      QtCore.Qt.Horizontal,
                                      QtCore.Qt.DisplayRole)
            self._headers.append('%s' % header)
            if populate:
                self._uniqueValues.update({header: set()})

        self._data = list()
        for row in range(model.rowCount()):
            r = list()
            for col in range(model.columnCount()):
                index = model.createIndex(row, col)
                data = model.data(index)

                if isinstance(data, (QtCore.QDateTime, QtCore.QDate)):
                    data = data.toString('yyyy.MM.dd')

                r.append(data)

                if populate:
                    self._uniqueValues[self.getColumnName(col)].add('%s' % data)

            self.addRow(r)

        self._original = copy(self._data)

        if populate:
            for k, v in self._uniqueValues.items():
                self._uniqueValues[k] = natsorted(list(v))

        return

    def addRow(self, row):
        self._data.append(row)

    def getRow(self, row):
        if 0 <= row < len(self._data):
            return self._data[row]
        return []

    def getValue(self, row, columnName):
        row = self.getRow(row)
        column = self.fieldIndex(columnName)
        if 0 <= column < len(row):
            return row[column]
        return None

    def rowCount(self, parent=None, *args, **kwargs):
        return len(self._data)

    def columnCount(self, parent=None, *args, **kwargs):
        return len(self._data[0])

    def data(self, index, role=QtCore.Qt.DisplayRole):
        if not index.isValid() or \
                not (0 <= index.row() < len(self._data)):
            return None
        if role == QtCore.Qt.DisplayRole:
            i = index.row()
            j = index.column()
            return '{0}'.format(self._data[i][j])
        return None

    def setData(self, index, value, role=QtCore.Qt.EditRole):
        if index.isValid() and 0 <= index.row() < len(self._data[0]):
            column = index.column()
            row = index.row()

            self._data[row][column] = value

            self.dirty = True
            self.emit(QtCore.SIGNAL("dataChanged(QModelIndex,QModelIndex)"), index, index)
            return True
        return False

    def headerData(self, section, orientation, role=QtCore.Qt.DisplayRole):
        if role != QtCore.Qt.DisplayRole:
            return None
        if orientation == QtCore.Qt.Horizontal:
            return self._headers[section]
        return int(section) + 1

    def flags(self, index):
        if self.filtered():
            if index.row() > 0:
                return QtCore.Qt.ItemIsEnabled | QtCore.Qt.ItemIsSelectable

            column = self.getColumnName(index.column())
            if column not in self._filterFunctions.keys():
                return QtCore.Qt.ItemIsEnabled | QtCore.Qt.ItemIsSelectable

            return QtCore.Qt.ItemIsEnabled | QtCore.Qt.ItemIsEditable | QtCore.Qt.ItemIsSelectable
        return QtCore.Qt.ItemIsEnabled | QtCore.Qt.ItemIsSelectable

    def fieldIndex(self, field):
        for i, header in enumerate(self._headers):
            if header == field:
                return i
        return -1

    def insertRows(self, row, count=1, parent=QtCore.QModelIndex(), *args, **kwargs):
        self.beginInsertRows(QtCore.QModelIndex(), row, row + count - 1)
        for r in range(count):
            self._data.insert(row + r, ['' for i in range(len(self._data[0]))])
            self._original.insert(row + r, ['' for i in range(len(self._data[0]))])
        self.endInsertRows()
        self.dirty = True
        return True

    def getColumnUniqueValues(self, index=None, column=None):
        if column:
            return self._uniqueValues[column]

        if index >= 0:
            return self._uniqueValues[self.getColumnName(index)]
        return []

    def setFiltered(self, bool):
        self._filtered = bool

    def filtered(self):
        return self._filtered

    def filterColumn(self, index):
        columnName = self.getColumnName(index)
        cmpFunc = self._filterFunctions.get(columnName, None)

        if cmpFunc:
            self._data = []
            for row in range(len(self._original)):
                if row == 0 and self.filtered():
                    self._data.append(self._original[row])
                    continue
                if cmpFunc(self._filterString, '%s' % self._original[row][index]):
                    self._data.append(self._original[row])
            self.emit(QtCore.SIGNAL("dataChanged(QModelIndex,QModelIndex)"),
                      self.createIndex(0, self.columnCount()),
                      self.createIndex(self.rowCount(), self.columnCount()))
        return

    def setFilters(self, filters):
        if not self.filtered() and len(filters):
            self.insertRow(0, QtCore.QModelIndex())
            self.setFiltered(True)

            for f in filters:
                column = f['column']
                cmpFunc = f['cmpFunc']
                if self.fieldIndex(column) >= 0:
                    self._filterFunctions.update({column: cmpFunc})

    def setFilterString(self, string, index):
        self._filterString = string
        self._filterColumn = self.getColumnName(index)


class SuperiorSqlTableView3000(QtGui.QTableView):
    _filterWidget = None
    _cache = None

    def __init__(self, parent, db_type, db_name, **kwargs):
        super(SuperiorSqlTableView3000, self).__init__(parent)
        self._db = QtSql.QSqlDatabase.addDatabase(db_type)
        self._db.setDatabaseName(db_name)

        if kwargs.get('hostname', None):
            self._db.setHostName(kwargs.get('hostname'))

        if kwargs.get('username', None):
            self._db.setUserName(kwargs.get('username'))

        if kwargs.get('password', None):
            self._db.setPassword(kwargs.get('password'))

        if not self._db.open():
            error = self._db.lastError()
            raise Exception('Невозможно открыть БД или нет связи с сервером! (%s, %s)' % (error.driverText(), error.databaseText()))

        self._model = QtSql.QSqlTableModel(self, self._db)
        self.setSelectionBehavior(Qt.QAbstractItemView.SelectRows)
        self.clicked.connect(self.onCellClicked)

    def onCellClicked(self, index):
        if index.row() == 0 and self._cache.filtered():
            self._filterWidget = QtGui.QComboBox(self)
            self._filterWidget.setEditable(True)
            self._filterWidget.addItem("")
            self._filterWidget.addItems(self._cache.getColumnUniqueValues(index.column()))
            self._filterWidget.editTextChanged.connect(lambda: self.onTextChanged(index))

            self.setIndexWidget(index, self._filterWidget)

    def openQuery(self, query, hideColumns=None):
        # t = datetime.datetime.now()
        self._model.setQuery(QtSql.QSqlQuery(query))
        # print('query: ', (datetime.datetime.now()-t))

        # t = datetime.datetime.now()
        self._cache = SuperiorCachedSqlTableModel3000(parent=self,
                                                      model=self._model,
                                                      populate=True)
        # print('caching: ',  (datetime.datetime.now()-t))

        self.setModel(self._cache)
        self.hideColumns(hideColumns)
        self.resizeColumns()
        return

    def onTextChanged(self, index):
        widget = self.indexWidget(index)
        self._cache.setFilterString(widget.currentText(), index.column())
        self._cache.filterColumn(index.column())

    def setFilters(self, filters):
        if self._cache is not None:
            self._cache.setFilters(filters)

    def resizeColumns(self):
        magic = 25
        self.resizeColumnsToContents()
        for i in range(0, self._cache.columnCount()):
            self.setColumnWidth(i, self.columnWidth(i) + magic)
        return

    def hideColumns(self, columns=()):
        if columns:
            for column in columns:
                self.hideColumn(self._cache.fieldIndex(column))


class GeoPointsFilterForm(QtGui.QWidget):
    def __init__(self, **kwargs):
        super(GeoPointsFilterForm, self).__init__(**kwargs)

        self.window_init()

        # self.view = SuperiorSqlTableView3000(self, 'QODBC', 'DRIVER={SQL Server};Server=ggs;Database=GEO_RUT')
        self.view = SuperiorSqlTableView3000(self, 'QPSQL', 'rog', hostname='localhost', port='5432', username='postgres', password='72946.penta')
        self.view.openQuery("select id, posting_date, title, slug from news_news order by id;", hideColumns=['slug'])

        # self.view.setFilters([])

        self.view.setFilters([
            {
                'column': 'id',
                'cmpFunc': lambda sample, value: sample.lower() in value.lower()
            },
            {
                'column': 'posting_date',
                'cmpFunc': lambda sample, value: sample <= value
            },
        ])

        self.view.resize(1024, 600)
        self.view.move(0, 0)

        self.show()

    def window_init(self):
        self.setFixedSize(1024, 600)
        self.setWindowTitle('Скважины')

app = QtGui.QApplication(sys.argv)
window = GeoPointsFilterForm()
sys.exit(app.exec_())