<commit_before>import logging
import time
from collections import defaultdict
from datetime import timedelta

from dash.orgs.models import Org

from django.contrib.postgres.fields import JSONField
from django.core.cache import cache
from django.db import connection, models
from django.db.models import Count, ExpressionWrapper, F, IntegerField, Q, Sum
from django.db.models.functions import ExtractYear
from django.utils import timezone, translation
from django.utils.translation import ugettext_lazy as _

from ureport.locations.models import Boundary
from ureport.polls.models import PollQuestion, PollResponseCategory

logger = logging.getLogger(__name__)


class GenderSegment(models.Model):

    GENDERS = {"M": _("Male"), "F": _("Female"), "O": _("Other")}

    gender = models.CharField(max_length=1)


class AgeSegment(models.Model):
    min_age = models.IntegerField(null=True)
    max_age = models.IntegerField(null=True)

    @classmethod
    def get_age_segment_min_age(cls, age):
        min_ages = [0, 15, 20, 25, 31, 35]
        return [elt for elt in min_ages if age >= elt][-1]


class PollStats(models.Model):
    DATA_TIME_FILTERS = {3: _("90 Days"), 6: _("6 Months"), 12: _("12 Months")}

    DATA_SEGMENTS = {"all": _("All"), "gender": _("Gender"), "age": _("Age"), "location": _("Location")}

    DATA_METRICS = {
        "opinion-responses": _("Opinion Responses"),
        "sign-up-rate": _("Sign Up Rate"),
        "response-rate": _("Response Rate"),
        "active-users": _("Active Users"),
    }

    id = models.BigAutoField(auto_created=True, primary_key=True, verbose_name="ID")

    org = models.ForeignKey(Org, on_delete=models.PROTECT, related_name="poll_stats")

    question = models.ForeignKey(PollQuestion, null=True, on_delete=models.SET_NULL)

    category = models.ForeignKey(PollResponseCategory, null=True, on_delete=models.SET_NULL)

    age_segment = models.ForeignKey(AgeSegment, null=True, on_delete=models.SET_NULL)

    gender_segment = models.ForeignKey(GenderSegment, null=True, on_delete=models.SET_NULL)

    location = models.ForeignKey(Boundary, null=True, on_delete=models.SET_NULL)

    date = models.DateTimeField(null=True)

    count = models.IntegerField(default=0, help_text=_("Number of items with this counter"))

    is_squashed = models.BooleanField(null=True, help_text=_("Whether this row was created by squashing"))

    @classmethod
    def squash(cls):
        start = time.time()
        num_sets = 0

        stats_objs = (
            cls.objects.exclude(is_squashed=True)
            .exclude(date=None)
            .order_by(
                "org_id", "question_id", "category_id", "age_segment_id", "gender_segment_id", "location_id", "date"
            )
            .distinct(
                "org_id", "question_id", "category_id", "age_segment_id", "gender_segment_id", "location_id", "date"
            )[:50000]
        )

        for distinct_set in stats_objs:
            with connection.cursor() as cursor:

                where_sql = ""
                if distinct_set.org_id is not None:
                    where_sql += '"org_id" = %s AND ' % distinct_set.org_id
                else:
                    where_sql += '"org_id" IS NULL AND'

                if distinct_set.question_id is not None:
                    where_sql += '"question_id" = %s AND' % distinct_set.question_id
                else:
                    where_sql += '"question_id" IS NULL AND'

                if distinct_set.category_id is not None:
                    where_sql += '"category_id" = %s AND' % distinct_set.category_id
                else:
                    where_sql += '"category_id" IS NULL AND'

                if distinct_set.age_segment_id is not None:
                    where_sql += '"age_segment_id" = %s AND' % distinct_set.age_segment_id
                else:
                    where_sql += '"age_segment_id" IS NULL AND'

                if distinct_set.gender_segment_id is not None:
                    where_sql += '"gender_segment_id" = %s AND' % distinct_set.gender_segment_id
                else:
                    where_sql += '"gender_segment_id" IS NULL AND'

                if distinct_set.location_id is not None:
                    where_sql += '"location_id" = %s AND' % distinct_set.location_id
                else:
                    where_sql += '"location_id" IS NULL AND'

                where_sql += """
                "date" = date_trunc('day', TIMESTAMP '%s')::TIMESTAMP
                """ % str(
                    distinct_set.date
                )

                sql = """
                WITH deleted as (
                  DELETE FROM stats_pollstats WHERE "id" IN (
                    SELECT "id" FROM stats_pollstats
                      WHERE %(where_sql)s
                      LIMIT 10000
                  ) RETURNING "count"
                )
                INSERT INTO stats_pollstats("org_id", "question_id", "category_id", "age_segment_id", "gender_segment_id", "location_id", "date", "count", "is_squashed")
                VALUES (%%s, %%s, %%s, %%s, %%s, %%s, date_trunc('day', TIMESTAMP %%s)::TIMESTAMP, GREATEST(0, (SELECT SUM("count") FROM deleted)), TRUE);
                """ % {
                    "where_sql": where_sql
                }

                params = (
                    distinct_set.org_id,
                    distinct_set.question_id,
                    distinct_set.category_id,
                    distinct_set.age_segment_id,
                    distinct_set.gender_segment_id,
                    distinct_set.location_id,
                    str(distinct_set.date),
                )

                cursor.execute(sql, params)

            num_sets += 1

        time_taken = time.time() - start

        print("Squashed %d distinct sets of %s in %0.3fs" % (num_sets, cls.__name__, time_taken))

    @classmethod
    def get_engagement_data(cls, org, metric, segment_slug, time_filter):

        key = f"org:{org.id}:metric:{metric}:segment:{segment_slug}:filter:{time_filter}"
        output_data = cache.get(key, None)
        if output_data:
            return output_data["results"]

        return PollStats.refresh_engagement_data(org, metric, segment_slug, time_filter)

    @classmethod
    def refresh_engagement_data(cls, org, metric, segment_slug, time_filter):

        key = f"org:{org.id}:metric:{metric}:segment:{segment_slug}:filter:{time_filter}"

        output_data = []
        if metric == "opinion-responses":
            if segment_slug == "all":
                output_data = PollStats.get_all_opinion_responses(org, time_filter)
            if segment_slug == "age":
                output_data = PollStats.get_age_opinion_responses(org, time_filter)
            if segment_slug == "gender":
                output_data = PollStats.get_gender_opinion_responses(org, time_filter)
            if segment_slug == "location":
                output_data = PollStats.get_location_opinion_responses(org, time_filter)

        if metric == "sign-up-rate":<commit_msg>Replace print statement<commit_after>import logging
import time
from collections import defaultdict
from datetime import timedelta

from dash.orgs.models import Org

from django.contrib.postgres.fields import JSONField
from django.core.cache import cache
from django.db import connection, models
from django.db.models import Count, ExpressionWrapper, F, IntegerField, Q, Sum
from django.db.models.functions import ExtractYear
from django.utils import timezone, translation
from django.utils.translation import ugettext_lazy as _

from ureport.locations.models import Boundary
from ureport.polls.models import PollQuestion, PollResponseCategory

logger = logging.getLogger(__name__)


class GenderSegment(models.Model):

    GENDERS = {"M": _("Male"), "F": _("Female"), "O": _("Other")}

    gender = models.CharField(max_length=1)


class AgeSegment(models.Model):
    min_age = models.IntegerField(null=True)
    max_age = models.IntegerField(null=True)

    @classmethod
    def get_age_segment_min_age(cls, age):
        min_ages = [0, 15, 20, 25, 31, 35]
        return [elt for elt in min_ages if age >= elt][-1]


class PollStats(models.Model):
    DATA_TIME_FILTERS = {3: _("90 Days"), 6: _("6 Months"), 12: _("12 Months")}

    DATA_SEGMENTS = {"all": _("All"), "gender": _("Gender"), "age": _("Age"), "location": _("Location")}

    DATA_METRICS = {
        "opinion-responses": _("Opinion Responses"),
        "sign-up-rate": _("Sign Up Rate"),
        "response-rate": _("Response Rate"),
        "active-users": _("Active Users"),
    }

    id = models.BigAutoField(auto_created=True, primary_key=True, verbose_name="ID")

    org = models.ForeignKey(Org, on_delete=models.PROTECT, related_name="poll_stats")

    question = models.ForeignKey(PollQuestion, null=True, on_delete=models.SET_NULL)

    category = models.ForeignKey(PollResponseCategory, null=True, on_delete=models.SET_NULL)

    age_segment = models.ForeignKey(AgeSegment, null=True, on_delete=models.SET_NULL)

    gender_segment = models.ForeignKey(GenderSegment, null=True, on_delete=models.SET_NULL)

    location = models.ForeignKey(Boundary, null=True, on_delete=models.SET_NULL)

    date = models.DateTimeField(null=True)

    count = models.IntegerField(default=0, help_text=_("Number of items with this counter"))

    is_squashed = models.BooleanField(null=True, help_text=_("Whether this row was created by squashing"))

    @classmethod
    def squash(cls):
        start = time.time()
        num_sets = 0

        stats_objs = (
            cls.objects.exclude(is_squashed=True)
            .exclude(date=None)
            .order_by(
                "org_id", "question_id", "category_id", "age_segment_id", "gender_segment_id", "location_id", "date"
            )
            .distinct(
                "org_id", "question_id", "category_id", "age_segment_id", "gender_segment_id", "location_id", "date"
            )[:50000]
        )

        for distinct_set in stats_objs:
            with connection.cursor() as cursor:

                where_sql = ""
                if distinct_set.org_id is not None:
                    where_sql += '"org_id" = %s AND ' % distinct_set.org_id
                else:
                    where_sql += '"org_id" IS NULL AND'

                if distinct_set.question_id is not None:
                    where_sql += '"question_id" = %s AND' % distinct_set.question_id
                else:
                    where_sql += '"question_id" IS NULL AND'

                if distinct_set.category_id is not None:
                    where_sql += '"category_id" = %s AND' % distinct_set.category_id
                else:
                    where_sql += '"category_id" IS NULL AND'

                if distinct_set.age_segment_id is not None:
                    where_sql += '"age_segment_id" = %s AND' % distinct_set.age_segment_id
                else:
                    where_sql += '"age_segment_id" IS NULL AND'

                if distinct_set.gender_segment_id is not None:
                    where_sql += '"gender_segment_id" = %s AND' % distinct_set.gender_segment_id
                else:
                    where_sql += '"gender_segment_id" IS NULL AND'

                if distinct_set.location_id is not None:
                    where_sql += '"location_id" = %s AND' % distinct_set.location_id
                else:
                    where_sql += '"location_id" IS NULL AND'

                where_sql += """
                "date" = date_trunc('day', TIMESTAMP '%s')::TIMESTAMP
                """ % str(
                    distinct_set.date
                )

                sql = """
                WITH deleted as (
                  DELETE FROM stats_pollstats WHERE "id" IN (
                    SELECT "id" FROM stats_pollstats
                      WHERE %(where_sql)s
                      LIMIT 10000
                  ) RETURNING "count"
                )
                INSERT INTO stats_pollstats("org_id", "question_id", "category_id", "age_segment_id", "gender_segment_id", "location_id", "date", "count", "is_squashed")
                VALUES (%%s, %%s, %%s, %%s, %%s, %%s, date_trunc('day', TIMESTAMP %%s)::TIMESTAMP, GREATEST(0, (SELECT SUM("count") FROM deleted)), TRUE);
                """ % {
                    "where_sql": where_sql
                }

                params = (
                    distinct_set.org_id,
                    distinct_set.question_id,
                    distinct_set.category_id,
                    distinct_set.age_segment_id,
                    distinct_set.gender_segment_id,
                    distinct_set.location_id,
                    str(distinct_set.date),
                )

                cursor.execute(sql, params)

            num_sets += 1

        time_taken = time.time() - start

        logger.info("Squashed %d distinct sets of %s in %0.3fs" % (num_sets, cls.__name__, time_taken))

    @classmethod
    def get_engagement_data(cls, org, metric, segment_slug, time_filter):

        key = f"org:{org.id}:metric:{metric}:segment:{segment_slug}:filter:{time_filter}"
        output_data = cache.get(key, None)
        if output_data:
            return output_data["results"]

        return PollStats.refresh_engagement_data(org, metric, segment_slug, time_filter)

    @classmethod
    def refresh_engagement_data(cls, org, metric, segment_slug, time_filter):

        key = f"org:{org.id}:metric:{metric}:segment:{segment_slug}:filter:{time_filter}"

        output_data = []
        if metric == "opinion-responses":
            if segment_slug == "all":
                output_data = PollStats.get_all_opinion_responses(org, time_filter)
            if segment_slug == "age":
                output_data = PollStats.get_age_opinion_responses(org, time_filter)
            if segment_slug == "gender":
                output_data = PollStats.get_gender_opinion_responses(org, time_filter)
            if segment_slug == "location":
                output_data = PollStats.get_location_opinion_responses(org, time_filter)

        if metric == "sign-up-rate":
{'fraction_non_alphanumeric': 0.10984901933116975, 'fraction_numerical': 0.005361930294906166, 'mean_word_length': 2.269142066420664, 'pattern_counts': {'":': 36, '<': 3, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '7186104', 'n_tokens_mistral': 4359, 'n_tokens_neox': 4038, 'n_words': 915}