# -*- coding: utf-8 -*-
"""Definitions of the report tables

"""
import numpy as np
import plugin_dbui as dbui


from callbacks import (INHIBIT_CASCADE_DELETE,
                       ON_CREATE_LISTS2,
                       ON_UPDATE_LISTS2)
from gluon import current, IS_IN_DB, IS_IN_SET
from gluon.tools import PluginManager
from pydal import Field


AGGREGATE_FUNCS = {
    "max": np.max,
    "mean": np.min,
    "median": np.median,
    "min": np.min,
    "sem": np.max,
    "size": np.size,
    "sum": np.sum,
    "std": np.std,
    "var": np.var}

DEF_COLUMNS_LISTS = \
"""[{
    "xtype": "rownumberer"
}, {
    "dbfield": "?.?",
    "flex": 1,
    "text": "?",
    "xtype": "gridcolumn"
}]"""

DEF_COLUMNS_METRIC1D = \
"""[{
    "aggregate": "?",
    "align": "right",
    "dbfield": "?.?",
    "format": "0.0",
    "text": "?",
    "xtype": "numbercolumn"
}]"""

DEF_FEATURES = \
"""[{
    "ftype": "groupingsummary",
    "groupHeaderTpl": "{name}",
    "startCollapsed": false
}, {
    "ftype": "summary"
}]"""

DEF_PLOT = \
"""{
    "kind": "?",
    "stacked": false
}"""

TP_AGG = \
    "The aggregation function applies on the metric field. " \
    "It is computed on the subset of row having a given values for " \
    "the group_field x and y."

TP_COLUMNS = "Configure the column of the grid displayed in the view."

TP_CONDITIONS = \
    "Database query to select history records:<br> " \
    "(db.events.event == 'People') & (db.history.data.like('%cdd%'))<br>"

TP_GROUP_FIELD_LISTS = \
    "Row are grouped according to the value of that field. " \
    "It can be any field of the history table including those of " \
    "the foreign tables, the individual property of the history.data " \
    "dictionary, or 'year'."

TP_GROUP_FIELD_METRICS = \
    "Metric are computed for each value of that field. " \
    "It can be any field of the history table including those of the " \
    "foreign tables, the individual property of the history.data dictionary " \
    "or 'year'"

TP_FEATURES = "Summary value can be computed for columns."

TP_SORTERS = \
    "Entries are sorted according to the value of these fields. " \
    "It can be any field of the history table including those of the " \
    "foreign tables, the individual property of the history.data dictionary, " \
    "or 'year'. Descending order is obtained by using the '~field' construct."

TP_SUMMARY_X = "To sum, for example, the content of each row."
TP_SUMMARY_Y = "To sum, for example, the content of each column."


class Report(object):
    """Create the tables for the reporting: lists, metrics1d, metrics2d,
    and graphs.

    """
    @staticmethod
    def define_tables(db, T):
        """Define tables for the reporting.

        Args:
            db (pyDAL.DAL): database connection
            T (gluon.languages.translator): language translator

        """

        Report.graphs(db, T)
        Report.lists(db, T)
        Report.lists2(db, T)
        Report.metrics1d(db, T)
        Report.metrics2d(db, T)
        Report.metrics2d2(db, T)

    @staticmethod
    def graphs(db, T):
        """graphs table

        Args:
            db (pyDAL.DAL): database connection
            T (gluon.languages.translator): language translator

        Returns:
            pyDAL.Table

        """
        def_plot = (None if db._migrate else DEF_PLOT)

        table = db.define_table(
            "graphs",
            Field("name", "string", length=255, notnull=True, unique=True),
            Field("title", "string", length=255),
            Field("report_type", "string", length=255, notnull=True),
            Field("report_name", "string", length=255, notnull=True),
            Field("plot", "text", notnull=True, default=def_plot),
            Field("definition", "text"),
            migrate="graphs.table")

        return table

    @staticmethod
    def lists(db, T):
        """lists table

        Args:
            db (pyDAL.DAL): database connection
            T (gluon.languages.translator): language translator

        Returns:
            pyDAL.Table

        """
        def_columns = (None if db._migrate else DEF_COLUMNS_LISTS)
        def_features = (None if db._migrate else DEF_FEATURES)

        table = db.define_table(
            "lists",
            Field("name", "string", length=255, notnull=True, unique=True),
            Field("title", "string", length=255),
            Field("conditions", "text", comment=T(TP_CONDITIONS)),

            Field("group_field",
                  "string",
                  length=255,
                  comment=T(TP_GROUP_FIELD_LISTS)),

            Field("sorters", "list:string", comment=TP_SORTERS),

            Field("columns",
                  "text",
                  default=def_columns,
                  comment=T(TP_COLUMNS),
                  notnull=True),

            Field("features",
                  "text",
                  default=def_features,
                  comment=T(TP_FEATURES)),

            Field("definition", "text"),
            migrate="lists.table")

        return table

    @staticmethod
    def lists2(db, T):
        """lists2 table

        Args:
            db (pyDAL.DAL): database connection
            T (gluon.languages.translator): language translator

        Returns:
            pyDAL.Table

        """
        migrate = current.globalenv["MIGRATE"]
        def_features = (None if migrate else DEF_FEATURES)

        table = db.define_table(
            "lists2",
            Field("name", "string", length=255, notnull=True, unique=True),
            Field("title", "string", length=255),
            Field("definition", "text"),
            Field("source", "string", length=255, notnull=True),

            Field("id_events",
                  "reference events",
                  notnull=True,
                  label="Event"),

            Field("eval", "text"),
            Field("query", "text"),

            Field("group_field",
                  "string",
                  length=255,
                  comment=T(TP_GROUP_FIELD_LISTS)),

            Field("sorters", "list:string", comment=TP_SORTERS),

            # NOTE
            #    - notnull when creating the database
            #    - null later on in order to run callback ON_CREATE_LISTS, ...
            Field("columns", "text",
                  notnull=migrate,
                  comment=T(TP_COLUMNS)),

            Field("summary_group",
                  "boolean",
                  default=True,
                  label=T("Summary row per group")),

            Field("summary_all",
                  "boolean",
                  default=True,
                  label=T("Summary row")),

            migrate="lists2.table")

        sources = PluginManager("event").event.sources.keys()
        sources.sort()
        db.lists2.source.requires = IS_IN_SET(sources)

        db.lists2._before_insert.append(ON_CREATE_LISTS2)
        db.lists2._before_update.append(ON_UPDATE_LISTS2)

        return table

    @staticmethod
    def metrics1d(db, T):
        """metrics1d table

        Args:
            db (pyDAL.DAL): database connection
            T (gluon.languages.translator): language translator

        Returns:
            pyDAL.Table

        """
        def_columns = (None if db._migrate else DEF_COLUMNS_METRIC1D)

        table = db.define_table(
            "metrics1d",
            Field("name", "string", length=255, notnull=True, unique=True),
            Field("title", "string", length=255),
            Field("conditions", "text", comment=T(TP_CONDITIONS)),

            Field("group_field",
                  "string",
                  length=255,
                  comment=T(TP_GROUP_FIELD_METRICS),
                  notnull=True),

            Field("columns",
                  "text",
                  default=def_columns,
                  comment=T(TP_COLUMNS),
                  notnull=True),

            Field("definition", "text"),
            migrate="metrics1d.table")

    @staticmethod
    def metrics2d(db, T):
        """metrics2d table

        Args:
            db (pyDAL.DAL): database connection
            T (gluon.languages.translator): language translator

        Returns:
            pyDAL.Table

        """

        table = db.define_table(
            "metrics2d",
            Field("name", "string", length=255, notnull=True, unique=True),
            Field("title", "string", length=255),
            Field("conditions", "text", comment=T(TP_CONDITIONS)),

            Field("group_field_x",
                  "string",
                  length=255,
                  notnull=True,
                  comment=T(TP_GROUP_FIELD_METRICS)),

            Field("group_field_y",
                  "string",
                  length=255,
                  notnull=True,
                  comment=T(TP_GROUP_FIELD_METRICS)),

            Field("metric_field_z",
                  "string",
                  length=255,
                  notnull=True,
                  comment=T(TP_GROUP_FIELD_METRICS)),

            Field("aggregation_z",
                  "string",
                  length=255,
                  notnull=True,
                  comment=T(TP_AGG)),

            Field("definition", "text"),
            migrate="metrics2d.table")

        return table

    @staticmethod
    def metrics2d2(db, T):
        """metrics2d2 table

        Args:
            db (pyDAL.DAL): database connection
            T (gluon.languages.translator): language translator

        Returns:
            pyDAL.Table

        """

        table = db.define_table(
            "metrics2d2",
            Field("name", "string", length=255, notnull=True, unique=True),
            Field("title", "string", length=255),
            Field("definition", "text"),
            Field("source", "string", length=255, notnull=True),

            Field("id_events",
                  "reference events",
                  label="Event",
                  notnull=True),

            Field("eval", "text"),
            Field("query", "text"),

            Field("group_field_x",
                  "string",
                  comment=T(TP_GROUP_FIELD_METRICS),
                  length=255,
                  notnull=True,
                  label="Group (x)"),

            Field("group_field_y",
                  "string",
                  comment=T(TP_GROUP_FIELD_METRICS),
                  length=255,
                  notnull=True,
                  label="Group (y)"),

            Field("metric_field_z",
                  "string",
                  comment=T(TP_GROUP_FIELD_METRICS),
                  label="Metric (z)",
                  length=255,
                  notnull=True),

            Field("aggregate_func_z",
                  "string",
                  comment=T(TP_AGG),
                  default="sum",
                  label="Aggregate (z)",
                  length=255,
                  notnull=True),

            Field("summary_x", "boolean", default=False, label="Per row"),

            Field("summary_func_x",
                  "string",
                  comment=T(TP_SUMMARY_X),
                  default="sum",
                  label="aggregate",
                  length=255),

            Field("summary_label_x",
                  "string",
                  default=T("Sum"),
                  label="label",
                  length=255),

            Field("summary_y", "boolean", default=False, label="Per columns"),

            Field("summary_func_y",
                  "string",
                  comment=T(TP_SUMMARY_Y),
                  default="sum",
                  label="aggregate",
                  length=255),

            Field("summary_label_y",
                  "string",
                  label="label",
                  length=255),

            migrate="metrics2d2.table")

        sources = PluginManager("event").event.sources.keys()
        sources.sort()

        db.metrics2d2.source.requires = IS_IN_SET(sources)

        funcs = AGGREGATE_FUNCS.keys()
        funcs.sort()

        db.metrics2d2.aggregate_func_z.requires = IS_IN_SET(funcs)
        db.metrics2d2.summary_func_x.requires = IS_IN_SET(funcs)
        db.metrics2d2.summary_func_y.requires = IS_IN_SET(funcs)

        return table

    @staticmethod
    def tables():
        """Tool returning the list of table names.

        Returns:
            list

        """
        lst = ["graphs",
               "lists",
               "lists2",
               "metrics1d",
               "metrics2d"
               "metrics2d2"]

        return lst