import re
import logging
import time

from datetime import datetime, timedelta, timezone
from django.core.management import BaseCommand
from django.db import connections, models
from django.utils import timezone as django_timezone

from pingcap_sso.models import UserDailyActivity, User
from pingcap_sso.user_behavior_track.models import TrackEvent
from pingcap_sso.discourse.models import (
    DiscourseUserAction, DiscourseSingleSignOnRecord,
    DiscourseTopic, DiscoursePost, DiscourseBookmark
)
from pingcap_sso.discourse.consts import BLOG_CATEGORIES

logger = logging.getLogger(__name__)


topics = DiscourseTopic.objects.filter(
    deleted_at__isnull=True,
    visible=True,
    archetype='regular'
)

posts = DiscoursePost.objects.filter(
    topic__deleted_at__isnull=True,
    topic__visible=True,
    topic__archetype='regular',
    deleted_at__isnull=True,
    hidden=False,
    post_type=1,
    user_id__gte=1
)

ASKTUG_TOPIC_REGEX = re.compile(r'(?P<topic_url>^https://asktug.com/t/[\w|-]+/\d+)(/\d+)?')


class Command(BaseCommand):

    def add_arguments(self, parser):
        parser.add_argument(
            '--target_day',
            required=False
        )

    def handle(self, *args, **options):
        target_day = options['target_day']
        if target_day:
            target_day = datetime.strptime(
                target_day, '%Y-%m-%d'
            )
        else:
            target_day = (django_timezone.now() - timedelta(days=1))

        target_day = target_day.astimezone(
            timezone(timedelta(hours=8))
        ).replace(
            hour=0, minute=0, second=0, microsecond=0
        )

        start = time.time()
        self.run_target_day(target_day)
        logger.info('generate user daily activities spend: {}s'.format(time.time() - start))

    def run_target_day(self, target_day):
        start_time = target_day.replace(
            hour=0, minute=0, second=0, microsecond=0
        ).astimezone(timezone(timedelta(hours=0)))
        end_time = start_time + timedelta(days=1)
        print(start_time, end_time)

        track_event_openids = TrackEvent.objects.filter(
            event_time__gte=start_time,
            event_time__lt=end_time,
            openid__isnull=False
        ).values_list('openid', flat=True).distinct()
        asktug_user_ids = DiscourseUserAction.objects.filter(
            created_at__gte=start_time,
            created_at__lt=end_time
        ).values_list('acting_user_id', flat=True).distinct()
        asktug_openids = DiscourseSingleSignOnRecord.objects.filter(
            user_id__in=asktug_user_ids
        ).values_list('external_id', flat=True)

        openids = set(track_event_openids) | set(asktug_openids)
        users = User.objects.filter(openid__in=openids)

        for u in users:
            self.calculate_user_activity(u, target_day, start_time, end_time)
    
    def calculate_user_activity(self, user, target_day, start_time, end_time):
        asktug_user = user.get_asktug_user(log_error_if_not_exists=False)
        if asktug_user:
            asktug_blogs_count = topics.filter(
                created_at__gte=start_time,
                created_at__lt=end_time,
                category_id__in=BLOG_CATEGORIES,
                user=asktug_user
            ).count()
            asktug_likes_count = DiscourseUserAction.objects.filter(
                created_at__gte=start_time,
                created_at__lt=end_time,
                acting_user=asktug_user,
                action_type=DiscourseUserAction.ACTION_LIKE
            ).count()
            asktug_bookmark_count = DiscourseBookmark.objects.filter(
                created_at__gte=start_time,
                created_at__lt=end_time,
                user=asktug_user
            ).count()
            asktug_topics_count = topics.filter(
                created_at__gte=start_time,
                created_at__lt=end_time,
                user=asktug_user
            ).exclude(category_id__in=BLOG_CATEGORIES).count()
            my_topics = topics.filter(user=asktug_user)
            asktug_reply_my_topic_count = posts.exclude(
                post_number=1
            ).filter(
                topic__in=my_topics, user=asktug_user,
                created_at__gte=start_time,
                created_at__lt=end_time
            ).count()
            asktug_reply_other_topic_count = posts.exclude(
                models.Q(post_number=1) | models.Q(topic__in=my_topics)
            ).filter(
                user=asktug_user,
                created_at__gte=start_time,
                created_at__lt=end_time,
            ).count()
            asktug_mark_solved_count = DiscourseUserAction.objects.filter(
                created_at__gte=start_time,
                created_at__lt=end_time,
                acting_user=asktug_user,
                action_type=DiscourseUserAction.ACTION_SOLVED
            ).count()
        else:
            # User may not have logged in to AskTUG
            asktug_blogs_count = 0
            asktug_likes_count = 0
            asktug_bookmark_count = 0
            asktug_topics_count = 0
            asktug_reply_my_topic_count = 0
            asktug_reply_other_topic_count = 0
            asktug_mark_solved_count = 0

        events = TrackEvent.objects.filter(
            event_time__gte=start_time,
            event_time__lt=end_time,
            openid=user.openid
        )
        track_events_count = events.count()
        asktug_search_count = events.filter(
            event_type='asktug_search.search'
        ).count()
        asktug_search_click_count = events.filter(
            event_type='asktug_search.click'
        ).count()
        login_count = events.filter(
            event_type='sso.login'
        ).count()
        daily_checkin_count = events.filter(
            event_type='sso.daily_checkin'
        ).count()
        redeem_product_count = events.filter(
            event_type='sso.redeem_product'
        ).count()
        apply_join_tug_count = events.filter(
            event_type='sso.apply_join_tug'
        ).count()
        apply_new_org_count = events.filter(
            event_type='sso.apply_new_org'
        ).count()
        invite_user_to_org_count = events.filter(
            event_type='sso.invite_user_to_org'
        ).count()
        accept_org_invitation_count = events.filter(
            event_type='sso.accept_invitation_of_org'
        ).count()
        refuse_org_invitation_count = events.filter(
            event_type='sso.refuse_invitation_of_org'
        ).count()
        urge_topic_count = events.filter(
            event_type='sso.urge_topic'
        ).count()
        update_profile_count = events.filter(
            event_type='sso.update_profile'
        ).count()
        update_account_settings_count = events.filter(
            event_type='sso.update_account_settings'
        ).count()
        submit_form_count = events.filter(
            event_type='form.submit'
        ).count()
        with connections['default'].cursor() as cursor:
            cursor.execute("""
                SELECT
                    event_body ->> "$.pure_url"
                FROM track_events
                WHERE
                    event_type = 'site.asktug#com.pageview' AND
                    event_time >= %(start_time)s AND
                    event_time < %(end_time)s AND
                    openid = %(openid)s
            """, {
                'start_time': start_time,
                'end_time': end_time,
                'openid': user.openid
            })

            asktug_urls = [r[0] for r in cursor.fetchall()]
            asktug_topics_urls = list()
            asktug_other_urls = list()

            for url in asktug_urls:
                t_match = ASKTUG_TOPIC_REGEX.match(url)
                if t_match:
                    asktug_topics_urls.append(t_match.group('topic_url'))
                else:
                    asktug_other_urls.append(url)

            asktug_urls = asktug_topics_urls + asktug_other_urls
            asktug_pageview_count = len(asktug_urls)
            asktug_pageview_distinct_count = len(set(asktug_urls))

            cursor.execute("""
                SELECT
                    count(event_body ->> "$.pure_url"), count(distinct(event_body ->> "$.pure_url"))
                FROM track_events
                WHERE
                    event_type like '%%.pageview' AND
                    event_type != 'site.asktug#com.pageview' AND
                    event_time >= %(start_time)s AND
                    event_time < %(end_time)s AND
                    openid = %(openid)s
            """, {
                'start_time': start_time,
                'end_time': end_time,
                'openid': user.openid
            })
            other_pageview_count, other_pageview_distinct_count = cursor.fetchall()[0]
        pageview_distinct_count = asktug_pageview_distinct_count + other_pageview_distinct_count
        pageview_count = asktug_pageview_count + other_pageview_count

        community_score = asktug_blogs_count * 100 + \
            asktug_likes_count * 2 + \
            asktug_bookmark_count * 2 + \
            asktug_topics_count * 10 + \
            asktug_reply_my_topic_count * 5 + \
            asktug_reply_other_topic_count * 15 + \
            asktug_mark_solved_count * 10 + \
            asktug_search_count * 5 + \
            asktug_search_click_count * 2 + \
            login_count * 1 + \
            min(daily_checkin_count, 1) * 1 + \
            redeem_product_count * 20 + \
            pageview_distinct_count * 1 + \
            apply_join_tug_count * 30 + \
            apply_new_org_count * 30 + \
            invite_user_to_org_count * 10 + \
            accept_org_invitation_count * 10 + \
            refuse_org_invitation_count * 10 + \
            urge_topic_count * 15 + \
            min(update_profile_count, 1) * 5 + \
            min(update_account_settings_count, 1) * 5 + \
            submit_form_count * 20

        UserDailyActivity.objects.create(
            user=user,
            day=target_day,
            community_score=community_score,
            pageview_distinct_count=pageview_distinct_count,
            pageview_count=pageview_count,
            asktug_blogs_count=asktug_blogs_count,
            asktug_likes_count=asktug_likes_count,
            asktug_bookmark_count=asktug_bookmark_count,
            asktug_topics_count=asktug_topics_count,
            asktug_reply_my_topic_count=asktug_reply_my_topic_count,
            asktug_reply_other_topic_count=asktug_reply_other_topic_count,
            asktug_mark_solved_count=asktug_mark_solved_count,
            asktug_search_count=asktug_search_count,
            asktug_search_click_count=asktug_search_click_count,
            login_count=login_count,
            daily_checkin_count=daily_checkin_count,
            redeem_product_count=redeem_product_count,
            apply_join_tug_count=apply_join_tug_count,
            apply_new_org_count=apply_new_org_count,
            invite_user_to_org_count=invite_user_to_org_count,
            accept_org_invitation_count=accept_org_invitation_count,
            refuse_org_invitation_count=refuse_org_invitation_count,
            urge_topic_count=urge_topic_count,
            update_profile_count=update_profile_count,
            update_account_settings_count=update_account_settings_count,
            submit_form_count=submit_form_count,
            track_events_count=track_events_count
        )