from traceback import print_exc
from collections import OrderedDict, Mapping
from decimal import Decimal
import sqlparse
import json
import datetime

import numpy as np
import matplotlib.pyplot as plt
from dateutil import parser as dateutil
import progressbar as pb  # import ProgressBar, Bar, Percentage, ETA, RotatingMarker

from django.core.exceptions import ImproperlyConfigured
from django.db import DatabaseError, transaction
from django.core.exceptions import FieldError
from django.db.models import FieldDoesNotExist
from django.db.models import Model

from pug.dj import db as djdb # FIXME: confusing name (too similar to common `import as` for django.db)
from pug.nlp import db  # FIXME: too similar to pug.db
from pug.nlp import util

import sqlserver as sql


DEFAULT_DB_ALIAS = None  # 'default'
DEFAULT_APP_NAME = None
try:
    from django.db import models, connection, connections, router
    from django.conf import settings
    DEFAULT_APP_NAME = settings.INSTALLED_APPS[-1].split('.')[-1]
except:
    import traceback
    print traceback.format_exc()
    print 'WARNING: The module named %r from file %r' % (__name__, __file__)
    print '         can only be used within a Django project!'
    print '         Though the module was imported, some of its functions may raise exceptions.'

types_varchar = ['nvarchar', 'varchar', 'sysname']  # `sysname` stores MS-TSQL object names and is equivalent to `nvarchar(128)`
types_not_countable = ['text', 'image', 'ntext']
types_not_aggregatable = types_not_countable + ['bit', 'uniqueidentifier']


def get_app_meta(apps=None, app_filter=lambda x: x.startswith('sec_') or x.startswith('siica_'), app_exclude_filter=None, verbosity=0, save=True):
    apps = util.listify(apps or djdb.get_app(apps))
    meta = []
    for app in apps:
        if filter_reject(app, app_filter, app_exclude_filter):
            continue
        meta += [get_db_meta(app=app, verbosity=verbosity)]
        if save:
            try:
                with open('db_meta_%s.json' % app, 'w') as fpout:
                    json.dump(make_serializable(meta[-1]), fpout, indent=4)
            except:
                print_exc()
    if save:
        try:
            with open('db_meta_all_apps.json', 'w') as fpout:
                jsonifiable_data = make_serializable(meta)
                json.dump(jsonifiable_data, fpout, indent=4)
        except:
            print_exc()
    return meta


def filter_reject(s, accept_filters, reject_filters=None):
    if callable(accept_filters) and callable(reject_filters):
        if (accept_filters and not accept_filters(s)) and (not reject_filters or not reject_filters(s)):
            return True
        else:
            return False
    elif isinstance(accept_filters, (tuple, list, set)) and (not reject_filters or callable(reject_filters)):
        return any(filter_reject(s, af, reject_filters) for af in accept_filters)
    elif (not accept_filters or callable(accept_filters)) and isinstance(reject_filters, (tuple, list, set)):
        return any(filter_reject(s, accept_filters, rf) for rf in reject_filters)
    elif (not reject_filters or callable(reject_filters)) and isinstance(accept_filters, (tuple, list, set)):
        return any(filter_reject(s, af, reject_filters) for af in accept_filters)
    elif isinstance(accept_filters, basestring) and (not reject_filters or callable(reject_filters)):
        return filter_reject(s, lambda x: x.startswith(accept_filters), reject_filters)
    elif (not accept_filters or callable(accept_filters)) and isinstance(reject_filters, basestring):
        return filter_reject(s, accept_filters, lambda x: x.startswith(reject_filters))

    return None


def load_app_meta(apps=None, app_filter=['sec_', 'siica_'], app_exclude_filter=None):
    apps = apps or djdb.get_app(apps)
    meta = {}
    for app in apps:
        # if filter_reject(app, app_filter, app_exclude_filter):
        #     continue
        if (app_filter and not any(app.startswith(af) for af in app_filter)) and (
                not app_exclude_filter or not any(app.startswith(rf) for rf in app_exclude_filter)):
            continue
        with open('db_meta_%s.json' % app, 'r') as fpin:        
            m = json.load(fpin)
        for table_name, table_meta in m.iteritems():
            table_name = app + '.' + table_name
            for field_name, field_meta in table_meta.iteritems():
                meta[table_name + '.' + field_name] = field_meta
    return meta


def meta_bar_chart(series=None, N=20):
    "Each column in the series is a dict of dicts"
    if not series or isinstance(series, basestring):
        series = json.load(load_app_meta)
    if isinstance(series, Mapping) and isinstance(series.values()[0], Mapping):
        rows_received = series['# Received'].items()
    elif isinstance(series, Mapping):
        rows_received = series.items()
    else:
        rows_received = list(series)

    #rows = sorted(rows, key=operator.itemgetter(1), reverse=True)
    rows = sorted(rows_received, key=lambda x: x[1], reverse=True)
    received_names, received_qty = zip(*rows)
    ra_qty = [(series['Qty in RA'].get(name, 0.) or 0.) for name in received_names]
    # percent = [100. - 100. * (num or 0.) / (den or 1.) for num, den in zip(received_qty, ra_qty)]

    # only care about the top 30 model numbers in terms of quantity
    #ind = range(N)

    figs = []

    figs += [plt.figure()]
    ax = figs[-1].add_subplot(111)
    ax.set_ylabel('# Units Returned')
    ax.set_title('Most-Returned LCDTV Models 2013-present')
    x = np.arange(N)
    bars1 = ax.bar(x, received_qty[:N], color='b', width=.4, log=1)
    bars2 = ax.bar(x+.4, ra_qty[:N], color='g', width=.4, log=1)
    ax.set_xticks(range(N))
    ax.set_xticklabels(received_names[:N], rotation=35)
    ax.grid(True)
    ax.legend((bars1[0], bars2[0]), ('# in RA', '# Received'), 'center right')
    figs[-1].show()
    #fig.autofmt_xdate()


def get_db_meta(app=DEFAULT_APP_NAME, db_alias=None, table=None, verbosity=0, column=None):
    """Return a dict of dicts containing metadata about the database tables associated with an app

    TODO: allow multiple apps
    >>> get_db_meta('crawler', db_alias='default', table='crawler_wikiitem')  # doctest: +ELLIPSIS
    OrderedDict([('WikiItem', OrderedDict([('Meta', OrderedDict([('primary_key', None), ('count', 1332), ('db_table', u'crawler_wikiitem')])), (u'id', OrderedDict([('name', u'id'), ('type', ...
    """
    if verbosity > 0:
        print 'Looking for app %r.' % (app, )
    if app and isinstance(app, basestring):
        app = djdb.get_app(app, verbosity=verbosity)
    else:
        app = djdb.get_app('')
    model_names = list(mc.__name__ for mc in models.get_models(app))
    if verbosity > 0:
        print 'Found %d models for app %r.' % (len(model_names), app)
    meta = OrderedDict()
    # inspectdb uses: for table_name in connection.introspection.table_names(cursor):
    for model_name in model_names:
        model = djdb.get_model(model_name, app=app)
        if db_alias:
            model_db_alias = db_alias
        else:
            model_db_alias = router.db_for_read(model)
        queryset = model.objects
        if model_db_alias:
            queryset = queryset.using(model_db_alias)
        if model and table is not None and isinstance(table, basestring):
            if model._meta.db_table != table:
                if verbosity>1:
                    print 'Skipped model named %s with db table names %s.' % (model_name, model._meta.db_table)
                continue
        elif callable(table):
            if not table(model._meta.db_table):
                if verbosity>1:
                    print 'Skipped model named %s with db table names %s.' % (model_name, model._meta.db_table)
                continue
        count = None
        try:
            if verbosity > 1:
                print 'Trying to count records in model %r and db_alias %r' % (model, model_db_alias)
            count = queryset.count()
        except DatabaseError as e:
            if verbosity > 0:
                print_exc()
                print "DatabaseError: Unable to count records for model '%s' (%s) because of %s." % (model.__name__, repr(model), e)
            transaction.rollback()
        except:
            print_exc()
            print 'Connection doesnt exist?'

        meta[model_name] = OrderedDict()
        meta[model_name]['Meta'] = OrderedDict()
        meta[model_name]['Meta']['primary_key'] = None
        meta[model_name]['Meta']['count'] = count
        meta[model_name]['Meta']['db_table'] = model._meta.db_table
        
        if verbosity > 1:
            print '%s.Meta = %r' % (model_name, meta[model_name]['Meta'])
        
        # inspectdb uses: connection.introspection.get_table_description(cursor, table_name)
        properties_of_fields = sql.get_meta_dicts(cursor=model_db_alias, table=meta[model_name]['Meta']['db_table'], verbosity=verbosity)
        model_meta = OrderedDict((field['name'], field) for field in properties_of_fields)
        if verbosity > 1: