# -*- coding: utf-8 -*-
import sys
import logging
import sqlalchemy as sa

from flask import flash
from sqlalchemy.orm import joinedload
from sqlalchemy.exc import IntegrityError
from sqlalchemy import func

from ..base import BaseInterface
from .filters import SQLAFilterConverter
from ..filters import Filters
from ..group import GroupByDateYear, GroupByDateMonth, GroupByCol
from ..mixins import FileColumn, ImageColumn
from ...filemanager import FileManager, ImageManager
from ..._compat import as_unicode

log = logging.getLogger(__name__)


class SQLAInterface(BaseInterface):
    """
    SQLAModel
    Implements SQLA support methods for views
    """
    session = None

    def __init__(self, obj, session=None):
        self.list_columns = dict()
        self.list_properties = dict()

        self.session = session
        # Collect all SQLA columns and properties
        for col_name in obj.__mapper__.columns.keys():
            self.list_columns[col_name] = obj.__mapper__.columns[col_name]
        for prop in sa.orm.class_mapper(obj).iterate_properties:
            self.list_properties[prop.key] = prop
        super(SQLAInterface, self).__init__(obj)

    @property
    def model_name(self):
        """
            Returns the models class name
            useful for auto title on views
        """
        return self.obj.__name__

    @staticmethod
    def _get_base_query(query=None, filters=None, order_column='', order_direction=''):
        if filters:
            query = filters.apply_all(query)
        if order_column != '':
            query = query.order_by(order_column + ' ' + order_direction)
        return query

    def get_filters(self, search_columns=[]):
        return Filters(SQLAFilterConverter, search_columns, self)

    def query(self, filters=None, order_column='', order_direction='',
              page=None, page_size=None):
        """
            QUERY
            :param filters:
                dict with filters {<col_name>:<value,...}
            :param order_column:
                name of the column to order
            :param order_direction:
                the direction to order <'asc'|'desc'>
            :param page:
                the current page
            :param page_size:
                the current page size

        """
        query = self.session.query(self.obj)
        if len(order_column.split('.')) >= 2:
            tmp_order_column = ''
            for join_relation in order_column.split('.')[:-1]:
                model_relation = self.get_model_relation(join_relation)
                query = query.join(model_relation)
                # redefine order column name, because relationship can have a different name
                # from the related table name.
                tmp_order_column = tmp_order_column + model_relation.__tablename__ + '.'
            order_column = tmp_order_column + order_column.split('.')[-1]
        query_count = self.session.query(func.count('*')).select_from(self.obj)

        query_count = self._get_base_query(query=query_count,
                                           filters=filters)

        query = self._get_base_query(query=query,
                                     filters=filters,
                                     order_column=order_column,
                                     order_direction=order_direction)

        count = query_count.scalar()

        if page:
            query = query.offset(page * page_size)
        if page_size:
            query = query.limit(page_size)

        return count, query.all()

    def query_simple_group(self, group_by='', aggregate_func = None, aggregate_col = None, filters=None):
        query = self.session.query(self.obj)
        query = self._get_base_query(query=query, filters=filters)
        query_result = query.all()
        group = GroupByCol(group_by, 'Group by')
        return group.apply(query_result)

    def query_month_group(self, group_by='', filters=None):
        query = self.session.query(self.obj)
        query = self._get_base_query(query=query, filters=filters)
        query_result = query.all()
        group = GroupByDateMonth(group_by, 'Group by Month')
        return group.apply(query_result)

    def query_year_group(self, group_by='', filters=None):
        query = self.session.query(self.obj)
        query = self._get_base_query(query=query, filters=filters)
        query_result = query.all()
        group_year = GroupByDateYear(group_by, 'Group by Year')
        return group_year.apply(query_result)

    """
    -----------------------------------------
         FUNCTIONS for Testing TYPES
    -----------------------------------------
    """

    def is_image(self, col_name):
        try:
            return isinstance(self.list_columns[col_name].type, ImageColumn)
        except:
            return False

    def is_file(self, col_name):
        try:
            return isinstance(self.list_columns[col_name].type, FileColumn)
        except:
            return False

    def is_string(self, col_name):
        try:
            return isinstance(self.list_columns[col_name].type, sa.types.String)
        except:
            return False

    def is_text(self, col_name):
        try:
            return isinstance(self.list_columns[col_name].type, sa.types.Text)
        except:
            return False

    def is_integer(self, col_name):
        try:
            return isinstance(self.list_columns[col_name].type, sa.types.Integer)
        except:
            return False

    def is_float(self, col_name):
        try:
            return isinstance(self.list_columns[col_name].type, sa.types.Float)
        except:
            return False

    def is_boolean(self, col_name):
        try:
            return isinstance(self.list_columns[col_name].type, sa.types.Boolean)
        except:
            return False

    def is_date(self, col_name):
        try:
            return isinstance(self.list_columns[col_name].type, sa.types.Date)
        except:
            return False

    def is_datetime(self, col_name):
        try:
            return isinstance(self.list_columns[col_name].type, sa.types.DateTime)
        except:
            return False

    def is_relation(self, col_name):
        try:
            return isinstance(self.list_properties[col_name], sa.orm.properties.RelationshipProperty)
        except:
            return False

    def is_relation_many_to_one(self, col_name):
        try:
            if self.is_relation(col_name):
                return self.list_properties[col_name].direction.name == 'MANYTOONE'
        except:
            return False

    def is_relation_many_to_many(self, col_name):
        try:
            if self.is_relation(col_name):
                return self.list_properties[col_name].direction.name == 'MANYTOMANY'
        except:
            return False

    def is_relation_one_to_one(self, col_name):
        try:
            if self.is_relation(col_name):
                return self.list_properties[col_name].direction.name == 'ONETOONE'
        except:
            return False

    def is_relation_one_to_many(self, col_name):
        try:
            if self.is_relation(col_name):
                return self.list_properties[col_name].direction.name == 'ONETOMANY'
        except:
            return False

    def is_nullable(self, col_name):
        try:
            return self.list_columns[col_name].nullable
        except:
            return False

    def is_unique(self, col_name):
        try:
            return self.list_columns[col_name].unique
        except:
            return False

    def is_pk(self, col_name):
        try:
            return self.list_columns[col_name].primary_key
        except:
            return False

    def is_fk(self, col_name):
        try:
            return self.list_columns[col_name].foreign_keys
        except:
            return False


    """
    -----------------------------------------
           FUNCTIONS FOR CRUD OPERATIONS
    -----------------------------------------
    """

    def add(self, item):
        try:
            self.session.add(item)
            self.session.commit()
            flash(as_unicode(self.add_row_message), 'success')
            return True
        except IntegrityError as e:
            flash(as_unicode(self.add_integrity_error_message), 'warning')
            log.warning("Add record integrity error: {0}".format(str(e)))
            self.session.rollback()
            return False
        except Exception as e:
            flash(as_unicode(self.general_error_message + ' ' + str(sys.exc_info()[0])), 'danger')
            log.exception("Add record error: {0}".format(str(e)))
            self.session.rollback()
            return False

    def edit(self, item):
        try:
            self.session.merge(item)
            self.session.commit()
            flash(as_unicode(self.edit_row_message), 'success')
            return True
        except IntegrityError as e:
            flash(as_unicode(self.edit_integrity_error_message), 'warning')
            log.warning("Edit record integrity error: {0}".format(str(e)))
            self.session.rollback()
            return False
        except Exception as e:
            flash(as_unicode(self.general_error_message + ' ' + str(sys.exc_info()[0])), 'danger')
            log.exception("Edit record error: {0}".format(str(e)))
            self.session.rollback()
            return False


    def delete(self, item):
        try:
            self._delete_files(item)
            self.session.delete(item)
            self.session.commit()
            flash(as_unicode(self.delete_row_message), 'success')
            return True
        except IntegrityError as e:
            flash(as_unicode(self.delete_integrity_error_message), 'warning')
            log.warning("Delete record integrity error: {0}".format(str(e)))
            self.session.rollback()
            return False
        except Exception as e:
            flash(as_unicode(self.general_error_message + ' ' + str(sys.exc_info()[0])), 'danger')
            log.exception("Delete record error: {0}".format(str(e)))
            self.session.rollback()
            return False

    def delete_all(self, items):
        try:
            for item in items:
                self._delete_files(item)
                self.session.delete(item)
            self.session.commit()
            flash(as_unicode(self.delete_row_message), 'success')
            return True
        except IntegrityError as e:
            flash(as_unicode(self.delete_integrity_error_message), 'warning')
            log.warning("Delete record integrity error: {0}".format(str(e)))
            self.session.rollback()
            return False
        except Exception as e:
            flash(as_unicode(self.general_error_message + ' ' + str(sys.exc_info()[0])), 'danger')
            log.exception("Delete record error: {0}".format(str(e)))
            self.session.rollback()
            return False


    """
    FILE HANDLING METHODS
    """

    def _add_files(self, this_request, item):
        fm = FileManager()
        im = ImageManager()
        for file_col in this_request.files:
            if self.is_file(file_col):
                fm.save_file(this_request.files[file_col], getattr(item, file_col))
        for file_col in this_request.files:
            if self.is_image(file_col):
                im.save_file(this_request.files[file_col], getattr(item, file_col))


    def _delete_files(self, item):
        for file_col in self.get_file_column_list():
            if self.is_file(file_col):
                if getattr(item, file_col):
                    fm = FileManager()
                    fm.delete_file(getattr(item, file_col))
        for file_col in self.get_image_column_list():
            if self.is_image(file_col):
                if getattr(item, file_col):
                    im = ImageManager()
                    im.delete_file(getattr(item, file_col))

    """
    -----------------------------------------
         FUNCTIONS FOR RELATED MODELS
    -----------------------------------------
    """
    def get_col_default(self, col_name):
        default = getattr(self.list_columns[col_name], 'default', None)
        if default is not None:
            value = getattr(default, 'arg', None)
            if value is not None:
                if getattr(default, 'is_callable', False):
                    return lambda: default.arg(None)
                else:
                    if not getattr(default, 'is_scalar', True):
                        return None
                return value

    def get_model_relation(self, col_name):
        return self.list_properties[col_name].mapper.class_

    def query_model_relation(self, col_name):
        model = self.get_model_relation(col_name)
        return self.session.query(model).all()

    def _get_related_model(self, col_name):
        if self.is_relation(col_name):
            return self.get_model_relation(col_name), self.list_properties[col_name].direction.name
        return None

    def get_related_obj(self, col_name, value):
        rel_model, rel_direction = self._get_related_model(col_name)
        return self.session.query(rel_model).get(value)

    def get_related_fks(self, related_views):
        return [view.datamodel.get_related_fk(self.obj) for view in related_views]

    def get_related_fk(self, model):
        for col_name in self.list_properties.keys():
            if self.is_relation(col_name):
                if model == self.get_model_relation(col_name):
                    return col_name
                    #return self.get_property_col(i)


    """
    ----------- GET METHODS -------------
    """

    def get_columns_list(self):
        """
            Returns all model's columns on SQLA properties
        """
        return list(self.list_properties.keys())

    def get_user_columns_list(self):
        """
            Returns all model's columns except pk or fk
        """
        ret_lst = list()
        for col_name in self.list_properties.keys():
            if (not self.is_pk(col_name)) and (not self.is_fk(col_name)):
                ret_lst.append(col_name)
        return ret_lst

    #TODO get different solution, more integrated with filters
    def get_search_columns_list(self):
        ret_lst = list()
        for col_name in self.list_properties.keys():
            if not self.is_relation(col_name):
                tmp_prop = self.get_property_first_col(col_name).name
                if (not self.is_pk(tmp_prop)) and \
                        (not self.is_fk(tmp_prop)) and \
                        (not self.is_image(col_name)) and \
                        (not self.is_file(col_name)) and \
                        (not self.is_boolean(col_name)):
                    ret_lst.append(col_name)
            else:
                ret_lst.append(col_name)
        return ret_lst

    def get_order_columns_list(self, list_columns=None):
        """
            Returns the columns that are can be ordered
        """
        ret_lst = list()
        list_columns = list_columns or self.list_properties.keys()
        for col_name in list_columns:
            if not self.is_relation(col_name):
                ret_lst.append(col_name)
        return ret_lst

    def get_file_column_list(self):
        return [i.name for i in self.obj.__mapper__.columns if isinstance(i.type, FileColumn)]

    def get_image_column_list(self):
        return [i.name for i in self.obj.__mapper__.columns if isinstance(i.type, ImageColumn)]


    def get_property_first_col(self, col_name):
        # support for only one col for pk and fk
        return self.list_properties[col_name].columns[0]

    def get_relation_fk(self, col_name):
        # support for only one col for pk and fk
        return list(self.list_properties[col_name].local_columns)[0]

    def get(self, id):
        return self.session.query(self.obj).get(id)


    """
    ----------- GET PK NAME -------------------
    """
    def get_pk_name(self):
        for col_name in self.list_columns.keys():
            if self.is_pk(col_name):
                return col_name

    def get_pk_value(self, item):
        for col_name in self.list_columns.keys():
            if self.is_pk(col_name):
                return getattr(item, col_name)

"""
    For Retro-Compatibility
"""
SQLModel = SQLAInterface
