""" Controllers for building metrics tables

"""
import json
import re

from plugin_dbui import inline_alert, Selector
from pydal.helpers.methods import smart_query
from reporting_tools import get_converter, repr_team_project

MSG_NO_METRIC = "Please select a metric...."


def index():
    """Main Controller handling metrics.

    """
    publications = db.publications

    # get the user constraint.
    # the selector fields year_start, year_end, id_lists are excluded
    # from the selector query.
    tpl = ('author', 'year_start', 'year_end', 'id_metrics')
    selector = Selector(virtdb.metric_selector, exclude_fields=tpl)

    # protection
    if not selector.id_metrics:
        return inline_alert(T("Error"), T(MSG_NO_METRIC))

    # retrieve metric data
    metric = db.metrics[selector.id_metrics]

    # database fields corresponding to the axes
    fields, orderby = {}, {}
    for el in ('vertical', 'horizontal'):

        axis = metric["axis_%s" % el]
        granularity = metric["granularity_%s" % el]
        direction = metric["direction_%s" % el]

        field = db[axis][granularity]

        fields[el] = field
        orderby[el] = ~field if direction == 'DESC' else field

    # query directive to count publications including
    # foreign key constraints and user requirements
    # related to team, project and year
    query = selector.query(publications)

    if metric.conditions:
        q_conditions = smart_query(publications, metric.conditions)
        query &= q_conditions

    if selector.year_start and not selector.year_end:
        query &= (publications.year == selector.year_start)

    elif selector.year_start and selector.year_end:
        q_start = publications.year >= selector.year_start
        q_end =  publications.year <= selector.year_end
        query &= ((q_start) & (q_end))

    if selector.author:
        q_author = publications.authors_institute.contains(selector.author)
        query &= q_author

    # get the label along the x and y axis
    labels = {}
    for el in ('vertical', 'horizontal'):
        labels[el] = []
        for row in db(query).select(fields[el], distinct=True, orderby=orderby[el]):
            labels[el].append(row[fields[el]])

    # protection empty data
    if len(labels['horizontal']) == 0 and len(labels['vertical']) == 0:
        return dict(cfg_store=json.dumps({}),
                    team_project=repr_team_project(db, selector),
                    selector=selector)

    # the fields configuration of the Ext.data.ArrayStore
    cfg = dict(fields=[], data=[])

    # first column contains labels for the vertical axis
    cfg['fields'].append({'name': fields['vertical'].name, 'type': 'string'})

    # one column for each horizontal label
    for el in labels['horizontal']:
        cfg['fields'].append({'name': str(el), 'type': 'int'})

    # last column contains the sum of the row
    cfg['fields'].append({'name': T('Total'), 'type': 'int'})

    # fill the data block of the Ext.data.Array.Store
    # transform (x, y count) data point into a row of values
    # sum the content of the row
    count = publications.id.count()
    rows =  db(query).select(fields['vertical'],
                             fields['horizontal'],
                             count,
                             groupby=(fields['vertical'], fields['horizontal']),
                             orderby=(orderby['vertical'], orderby['horizontal']))

    key = None
    for row in rows:

        if row[fields['vertical']] != key:
            key = row[fields['vertical']]
            cfg['data'].append([key])
            cfg['data'][-1].extend(['']*(len(labels['horizontal']) + 1))
            cfg['data'][-1][-1] = 0

        i = labels['horizontal'].index(row[fields['horizontal']]) + 1
        cfg['data'][-1][i] = row[count]
        cfg['data'][-1][-1] += row[count]

    # Substitute axis label
    cvt_labels = get_converter(metric)

    # label for the horizontal axis
    for i in range(len(cfg['data'][0])):
        if cfg['data'][0][i] in cvt_labels:
            cfg['data'][0][i] = cvt_labels[cfg['data'][0][i]]

    # label for the vertical axis
    for i in range(len(cfg['data'])):
        if cfg['data'][i][0] in cvt_labels:
            cfg['data'][i][0] = cvt_labels[cfg['data'][i][0]]

    return dict(cfg_store=json.dumps(cfg),
                team_project=repr_team_project(db, selector),
                selector=selector)