# from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import and_, inspect
from sqlalchemy.dialects.postgresql import BYTEA
from sqlalchemy.ext.declarative import as_declarative
from sqlalchemy.ext.hybrid import hybrid_property, HYBRID_PROPERTY
from sqlalchemy.orm import object_session
from sqlalchemy.types import TypeDecorator
from sqlalchemy.ext.associationproxy import ASSOCIATION_PROXY

from qcelemental.util import msgpackext_dumps, msgpackext_loads

# from sqlalchemy.ext.orderinglist import ordering_list
# from sqlalchemy.ext.associationproxy import association_proxy
# from sqlalchemy.dialects.postgresql import aggregate_order_by


# Base = declarative_base()


class MsgpackExt(TypeDecorator):
    '''Converts JSON-like data to msgpack with full NumPy Array support.'''

    impl = BYTEA

    def process_bind_param(self, value, dialect):
        return msgpackext_dumps(value)

    def process_result_value(self, value, dialect):
        return msgpackext_loads(value)


@as_declarative()
class Base:
    """Base declarative class of all ORM models"""

    db_related_fields = ['result_type', 'base_result_id', '_trajectory', 'collection_type']

    def to_dict(self, exclude=None):

        tobe_deleted_keys = []

        if exclude:
            tobe_deleted_keys.extend(exclude)

        dict_obj = [x for x in self._all_col_names()
                        if x not in self.db_related_fields
                        and x not in tobe_deleted_keys
        ]

        # Add the attributes to the final results
        ret = {k: getattr(self, k) for k in dict_obj}

        if 'extra' in ret:
            ret.update(ret['extra'])
            del ret['extra']

        # transform ids from int into str
        id_fields = self._get_fieldnames_with_DB_ids_()
        for key in id_fields:
            if key in ret.keys() and ret[key] is not None:
                if isinstance(ret[key], (list, tuple)):
                    ret[key] = [str(i) for i in ret[key]]
                else:
                    ret[key] = str(ret[key])

        return ret

    @classmethod
    def _get_fieldnames_with_DB_ids_(cls):

        class_inspector = inspect(cls)
        id_fields = []
        for key, col in class_inspector.columns.items():
            # if PK, FK, or column property (TODO: work around for column property)
            if col.primary_key or len(col.foreign_keys) > 0 or key != col.key:
                id_fields.append(key)

        return id_fields

    @classmethod
    def _get_col_types(cls):

        if hasattr(cls, '_columns') and hasattr(cls, '_hybrids') and hasattr(cls, '_relationships'):
            return cls._columns, cls._hybrids, cls._relationships

        mapper = inspect(cls)

        cls._columns = []
        cls._hybrids = []
        cls._relationships = {k: v.argument for k, v in mapper.relationships.items()}

        for k, c in mapper.all_orm_descriptors.items():

            if k == '__mapper__':
                continue

            if c.extension_type == ASSOCIATION_PROXY:
                continue

            if (c.extension_type == HYBRID_PROPERTY):
                cls._hybrids.append(k)
            elif k not in mapper.relationships:
                cls._columns.append(k)

        return cls._columns, cls._hybrids, cls._relationships

    @classmethod
    def _all_col_names(cls):
        all_cols, hybrid, _ = cls._get_col_types()
        return all_cols + hybrid

    def _update_many_to_many(self, table, parent_id_name, child_id_name, parent_id_val, new_list, old_list=None):
        """Perfomr upsert on a many to many association table
        Does NOT commit changes, parent should optimize when it needs to commit
        raises exception if ids don't exist in the DB
        """

        session = object_session(self)

        old_set = {x for x in old_list} if old_list else set()
        new_set = {x for x in new_list} if new_list else set()

        # Update many-to-many relations
        # Remove old relations and apply the new ones
        if old_set != new_set:
            to_add = new_set - old_set
            to_del = old_set - new_set

            if to_del:
                session.execute(table.delete().where(
                    and_(table.c[parent_id_name] == parent_id_val, table.c[child_id_name].in_(to_del))))
            if to_add:
                session.execute(
                    table.insert()\
                        .values([(parent_id_val, my_id) for my_id in to_add])
                )

    def __str__(self):
        if hasattr(self, 'id'):
            return str(self.id)
        return super.__str__(self)

    # @validates('created_on', 'modified_on')
    # def validate_date(self, key, date):
    #     """For SQLite, translate str to dates manually"""
    #     if date is not None and isinstance(date, str):
    #         date = dateutil.parser.parse(date)
    #     return date

@@ -1152,16 +1152,41 @@
 ectory',
+%0A                        
  'collec
@@ -1195,16 +1195,25 @@
 on_type'
+, 'lname'
 %5D%0A%0A    d
@@ -2557,32 +2557,112 @@
 ol_types(cls):%0A%0A
+        # Must use private attributes so that they are not shared by subclasses%0A
         if hasat
@@ -2671,16 +2671,17 @@
 (cls, '_
+_
 columns'
@@ -2701,16 +2701,17 @@
 (cls, '_
+_
 hybrids'
@@ -2731,16 +2731,17 @@
 (cls, '_
+_
 relation
@@ -2765,32 +2765,33 @@
     return cls._
+_
 columns, cls._hy
@@ -2780,32 +2780,33 @@
 __columns, cls._
+_
 hybrids, cls._re
@@ -2795,32 +2795,33 @@
 __hybrids, cls._
+_
 relationships%0A%0A 
@@ -2859,24 +2859,25 @@
        cls._
+_
 columns = %5B%5D
@@ -2886,24 +2886,25 @@
        cls._
+_
 hybrids = %5B%5D
@@ -2913,24 +2913,25 @@
        cls._
+_
 relationship
@@ -3260,24 +3260,25 @@
        cls._
+_
 hybrids.appe
@@ -3348,24 +3348,25 @@
        cls._
+_
 columns.appe
@@ -3392,16 +3392,17 @@
 rn cls._
+_
 columns,
@@ -3407,16 +3407,17 @@
 s, cls._
+_
 hybrids,
@@ -3422,16 +3422,17 @@
 s, cls._
+_
 relation

Change to private class variables