from django.utils.translation import ugettext as _
from django.utils.translation import ugettext_noop
from corehq.apps.reports.datatables import DataTablesHeader, DataTablesColumn
from corehq.apps.reports.generic import GenericTabularReport
from corehq.apps.reports.standard import ProjectReportParametersMixin, DatespanMixin, CustomProjectReport
from corehq.elastic import es_query, ES_URLS

WORKFLOWS = ["workflow_keyword", "workflow_reminder", "workflow_broadcast"]

class SystemOverviewReport(GenericTabularReport, CustomProjectReport, ProjectReportParametersMixin, DatespanMixin):
    slug = 'system_overview'
    name = ugettext_noop("System Overview")
    description = ugettext_noop("Description for System Overview Report")
    section_name = ugettext_noop("System Overview")
    need_group_ids = True
    is_cacheable = True
    fields = [
        'corehq.apps.reports.filters.select.MultiGroupFilter',
        'corehq.apps.reports.filters.select.MultiCaseGroupFilter',
        'corehq.apps.reports.filters.dates.DatespanFilter',
    ]
    emailable = True

    def query_sms(self, workflow=None):
        """
            Open cases that haven't been modified within time range
        """
        q = {"query": {
                "bool": {
                    "must": [
                        {"match": {"domain.exact": self.domain}},
                        {"range": {
                            'date': {
                                "from": self.datespan.startdate_param,
                                "to": self.datespan.enddate_param,
                                "include_upper": True}}}]}}}

        if workflow:
            q["filter"] = {"and": [{"term": {"workflow": workflow}}]}
        else:
            q["filter"] = {"and": [{"not": {"in": {"workflow": WORKFLOWS}}}]}

        if self.users_by_group:
            q["query"]["bool"]["must"].append({"in": {"couch_recipient": self.combined_user_ids}})
        if self.cases_by_case_group:
            q["query"]["bool"]["must"].append({"in": {"couch_recipient": self.cases_by_case_group}})

        facets = ['couch_recipient_doc_type', 'direction']
        return es_query(q=q, facets=facets, es_url=ES_URLS['sms'], size=0)

    @property
    def headers(self):
        return DataTablesHeader(
            DataTablesColumn("", sortable=False),
            DataTablesColumn(_("Numbers")),
            DataTablesColumn(_("Mobile Worker Messages")),
            DataTablesColumn(_("Case Messages")),
            DataTablesColumn(_("Incoming")),
            DataTablesColumn(_("Outgoing")),
        )

    @property
    def rows(self):

        def row(rowname, workflow=None):
            facets = self.query_sms(workflow)['facets']
            to_cases, to_users, outgoing, incoming = 0, 0, 0, 0

            for term in facets['couch_recipient_doc_type']['terms']:
                if term['term'] == 'commcarecase':
                    to_cases = term['count']
                elif term['term'] == 'couchuser':
                    to_users = term['count']

            for term in facets['direction']['terms']:
                if term['term'] == 'o':
                    outgoing = term['count']
                elif term['term'] == 'i':
                    incoming = term['count']

            return [rowname, 0, to_users, to_cases, incoming, outgoing]

        rows =  [
            row(_("Keywords"), "workflow_keyword"),
            row(_("Reminders"), "workflow_reminder"),
            row(_("Broadcasts"), "workflow_broadcast"),
            row(_("Unknown")),
        ]

        def total(index):
            return sum([l[index] for l in rows])

        self.total_row = [_("Total"), total(1), total(2), total(3), total(4), total(5)]

        return rows


@@ -1090,32 +1090,56 @@
 f, workflow=None
+, additional_facets=None
 ):%0A        %22%22%22%0A 
@@ -1133,28 +1133,24 @@
         %22%22%22%0A
-    
         Open
@@ -1205,32 +1205,84 @@
 nge%0A        %22%22%22%0A
+        additional_facets = additional_facets or %5B%5D%0A
         q = %7B%22qu
@@ -2197,16 +2197,36 @@
 ection'%5D
+ + additional_facets
 %0A       
@@ -2452,17 +2452,16 @@
 (%22Number
-s
 %22)),%0A   
@@ -2735,32 +2735,281 @@
 workflow=None):%0A
+            additional_workflow_facets = %7B%0A                %22workflow_keyword%22: %5B'xforms_session_couch_id'%5D,%0A                %22workflow_reminder%22: %5B'reminder_id'%5D,%0A            %7D%0A            additional_facets = additional_workflow_facets.get(workflow)%0A
             face
@@ -3036,16 +3036,35 @@
 workflow
+, additional_facets
 )%5B'facet
@@ -3612,32 +3612,198 @@
  term%5B'count'%5D%0A%0A
+            number = 0%0A            if workflow in additional_workflow_facets:%0A                number = len(facets%5Badditional_workflow_facets%5Bworkflow%5D%5B0%5D%5D%5B%22terms%22%5D)%0A%0A
             retu
@@ -3815,17 +3815,22 @@
 owname, 
-0
+number
 , to_use

add number indicator for reminder and keyword rows