# Refer https://pingcap.feishu.cn/docs/doccnqkxLyli7aElAnPoCIhtRof
from django.db.models import Q, Sum
from django.db import connections

from pingcap_sso.discourse.models import (
    DiscourseTopic, DiscoursePost, DiscourseCategory,
    DiscourseTopicCustomField, DiscourseUserAction,
    DiscourseUserBadge, DiscourseTopicView
)
from pingcap_sso.discourse.consts import (
    CTC_BADGE_ID, MODERATOR_BADGE_ID,
    QA_CATEGORIES, BLOG_CATEGORIES
)
from pingcap_sso.models import (
    User, UserPointStat, OrganizationUser, Organization,
    OrganizationTopicUrgency,
    AskTUGTopicSummaryReport, AskTUGTopicCategoryReport,
    AskTUGQATopicReport, AskTUGBlogTopicReport,
    UserSummaryReport, AskTUGUrgencyTopicReport,
    AskTUGUrgencyTopicCategoryReport
)


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
)


def _asktug_topic_summary_report(start_at, end_at, time_dimension, sequence, report_date):
    topic_total_count = topics.filter(created_at__lt=end_at).count()
    topic_incre_count = topics.filter(
        created_at__gte=start_at,
        created_at__lt=end_at
    ).count()

    post_total_count = posts.filter(created_at__lt=end_at).count()
    post_incre_count = posts.filter(
        created_at__gte=start_at,
        created_at__lt=end_at
    ).count()

    topic_author_total_count = topics.filter(
        created_at__lt=end_at
    ).values('user_id').distinct().count()
    topic_author_incre_count = topics.filter(
        created_at__gte=start_at,
        created_at__lt=end_at
    ).values('user_id').distinct().count()

    post_author_total_count = posts.filter(
        created_at__lt=end_at
    ).values('user_id').distinct().count()
    post_author_incre_count = posts.filter(
        created_at__gte=start_at,
        created_at__lt=end_at
    ).values('user_id').distinct().count()

    AskTUGTopicSummaryReport.objects.create(
        topic_total_count=topic_total_count,
        topic_incre_count=topic_incre_count,
        post_total_count=post_total_count,
        post_incre_count=post_incre_count,
        topic_author_total_count=topic_author_total_count,
        topic_author_incre_count=topic_author_incre_count,
        post_author_total_count=post_author_total_count,
        post_author_incre_count=post_author_incre_count,
        sequence=sequence,
        time_dimension=time_dimension,
        report_date=report_date
    )


def _asktug_topic_category_report(start_at, end_at, time_dimension, sequence, report_date):
    categories = DiscourseCategory.objects.filter(
        Q(parent_category__isnull=True) | Q(id__in=[60027, 30073, 120022])
    )
    for c in categories:
        all_topics = topics.filter(Q(category=c) | Q(category__parent_category=c))
        c_topic_total_count = all_topics.filter(created_at__lt=end_at).count()
        c_topic_incre_count = all_topics.filter(created_at__gte=start_at, created_at__lt=end_at).count()
        AskTUGTopicCategoryReport.objects.create(
            category_id=c.id,
            caterory_name=c.name,
            parent_category_id=c.parent_category_id,
            topic_total_count=c_topic_total_count,
            topic_incre_count=c_topic_incre_count,
            sequence=sequence,
            time_dimension=time_dimension,
            report_date=report_date
        )


def _asktug_qa_topic_report(start_at, end_at, time_dimension, sequence, report_date):
    qa_topics = topics.filter(category_id__in=QA_CATEGORIES)
    qa_posts = posts.filter(topic__category_id__in=QA_CATEGORIES)

    topic_total_count = qa_topics.filter(created_at__lt=end_at).count()
    topic_incre_count = qa_topics.filter(
        created_at__gte=start_at,
        created_at__lt=end_at
    ).count()

    post_total_count = qa_posts.filter(created_at__lt=end_at).count()
    post_incre_count = qa_posts.filter(
        created_at__gte=start_at,
        created_at__lt=end_at
    ).count()

    solved = DiscourseTopicCustomField.objects.filter(
        name='accepted_answer_post_id',
        value__isnull=False,
        topic__deleted_at__isnull=True,
        topic__visible=True,
        topic__archetype='regular'
    )
    solved_topic_total_count = solved.filter(updated_at__lt=end_at).count()
    solved_topic_incre_count = solved.filter(
        updated_at__gte=start_at,
        updated_at__lt=end_at
    ).count()
    solved_topic_ratio_of_total = round((solved_topic_total_count / topic_total_count), 2)
    solved_topic_ratio_of_incre = 0.0  # TODO, 想不出来算这个值的意义是什么
    with connections['discourse'].cursor() as cursor:
        cursor.execute("""
            SELECT
                count(1) as topic_count, (sum(t.posts_count) - count(1)) as reply_count
            FROM
                topics t left join topic_custom_fields tc on t.id = tc.topic_id
            WHERE
                tc.updated_at < %s and tc.name = 'accepted_answer_post_id' and
                tc.value is not null and t.category_id in %s and t.deleted_by_id is null;
        """, (end_at, QA_CATEGORIES))
        topic_count, reply_count = cursor.fetchall()[0]
        solved_topic_avg_replies_of_total = round((reply_count / topic_count), 2) if topic_count != 0 else 0

        cursor.execute("""
            SELECT
                count(1) as topic_count, (sum(t.posts_count) - count(1)) as reply_count
            FROM
                topics t left join topic_custom_fields tc on t.id = tc.topic_id
            WHERE
                tc.updated_at >= %s and tc.updated_at < %s and tc.name = 'accepted_answer_post_id' and
                tc.value is not null and t.category_id in %s and t.deleted_by_id is null and
                t.visible = true;
        """, (start_at, end_at, QA_CATEGORIES))
        topic_count, reply_count = cursor.fetchall()[0]
        solved_topic_avg_replies_of_incre = round((reply_count / topic_count), 2) if topic_count != 0 else 0

        cursor.execute("""
            SELECT count(1), sum(x.time_diff) / 3600
            FROM (
                SELECT (extract(epoch from tc.updated_at) - extract(epoch from t.created_at)) time_diff
                FROM topics t left join topic_custom_fields tc on t.id = tc.topic_id
                WHERE tc.updated_at < %s and tc.name = 'accepted_answer_post_id' and
                        tc.value is not null and t.category_id in %s and
                        t.deleted_by_id is null and t.visible = true
            ) as x;
        """, (end_at, QA_CATEGORIES))
        topic_count, spend_hours = cursor.fetchall()[0]
        solved_topic_avg_spend_hours_of_total = round((spend_hours / topic_count), 2) if topic_count != 0 else 0

        cursor.execute("""
            SELECT count(1), sum(x.time_diff) / 3600
            FROM (
                SELECT (extract(epoch from tc.updated_at) - extract(epoch from t.created_at)) time_diff
                FROM topics t left join topic_custom_fields tc on t.id = tc.topic_id
                WHERE tc.updated_at >= %s and tc.updated_at < %s and
                        tc.name = 'accepted_answer_post_id' and
                        tc.value is not null and t.category_id in %s and
                        t.deleted_by_id is null and t.visible = true
            ) as x;
        """, (start_at, end_at, QA_CATEGORIES))
        topic_count, spend_hours = cursor.fetchall()[0]
        solved_topic_avg_spend_hours_of_incre = round((spend_hours / topic_count), 2) if topic_count != 0 else 0

    solved_user_action = DiscourseUserAction.objects.filter(
        action_type=DiscourseUserAction.ACTION_SOLVED,
        target_topic__category_id__in=QA_CATEGORIES,
        target_topic__deleted_at__isnull=True,
        target_topic__visible=True,
        target_topic__archetype='regular'
    )

    ctc_user_ids = list(DiscourseUserBadge.objects.filter(
        badge_id=CTC_BADGE_ID).values_list('user_id', flat=True))
    moderator_user_ids = list(DiscourseUserBadge.objects.filter(
        badge_id=MODERATOR_BADGE_ID).values_list('user_id', flat=True))

    ctc_solved_topic_total_count = solved_user_action.filter(
        user_id__in=ctc_user_ids, created_at__lt=end_at
    ).values('target_topic').distinct().count()
    ctc_solved_topic_incre_count = solved_user_action.filter(
        user_id__in=ctc_user_ids, created_at__lt=end_at, created_at__gte=start_at
    ).values('target_topic').distinct().count()

    non_ctc_solved_topic_total_count = solved_user_action.exclude(
        user_id__in=ctc_user_ids
    ).filter(
        created_at__lt=end_at
    ).values('target_topic').distinct().count()
    non_ctc_solved_topic_incre_count = solved_user_action.exclude(
        user_id__in=ctc_user_ids
    ).filter(
        created_at__lt=end_at, created_at__gte=start_at
    ).values('target_topic').distinct().count()

    moderator_solved_topic_total_count = solved_user_action.filter(
        user_id__in=moderator_user_ids, created_at__lt=end_at
    ).values('target_topic').distinct().count()
    moderator_solved_topic_incre_count = solved_user_action.filter(
        user_id__in=moderator_user_ids, created_at__lt=end_at, created_at__gte=start_at
    ).values('target_topic').distinct().count()

    AskTUGQATopicReport.objects.create(
        topic_total_count=topic_total_count,
        topic_incre_count=topic_incre_count,
        post_total_count=post_total_count,
        post_incre_count=post_incre_count,
        solved_topic_total_count=solved_topic_total_count,
        solved_topic_incre_count=solved_topic_incre_count,
        solved_topic_ratio_of_total=solved_topic_ratio_of_total,
        solved_topic_ratio_of_incre=solved_topic_ratio_of_incre,
        solved_topic_avg_replies_of_total=solved_topic_avg_replies_of_total,
        solved_topic_avg_replies_of_incre=solved_topic_avg_replies_of_incre,
        solved_topic_avg_spend_hours_of_total=solved_topic_avg_spend_hours_of_total,
        solved_topic_avg_spend_hours_of_incre=solved_topic_avg_spend_hours_of_incre,
        ctc_solved_topic_total_count=ctc_solved_topic_total_count,
        ctc_solved_topic_incre_count=ctc_solved_topic_incre_count,
        non_ctc_solved_topic_total_count=non_ctc_solved_topic_total_count,
        non_ctc_solved_topic_incre_count=non_ctc_solved_topic_incre_count,
        moderator_solved_topic_total_count=moderator_solved_topic_total_count,
        moderator_solved_topic_incre_count=moderator_solved_topic_incre_count,
        sequence=sequence,
        time_dimension=time_dimension,
        report_date=report_date
    )


def _asktug_blog_topic_report(start_at, end_at, time_dimension, sequence, report_date):
    blog_topics = topics.filter(category_id__in=BLOG_CATEGORIES)
    topic_total_count = blog_topics.filter(created_at__lt=end_at).count()
    topic_incre_count = blog_topics.filter(
        created_at__gte=start_at,
        created_at__lt=end_at
    ).count()

    topic_author_total_count = blog_topics.filter(created_at__lt=end_at).values('user').distinct().count()
    topic_author_incre_count = blog_topics.filter(
        created_at__gte=start_at,
        created_at__lt=end_at
    ).values('user').distinct().count()

    topic_views_total_count = blog_topics.filter(created_at__lt=end_at).aggregate(Sum('views'))['views__sum']
    topic_views_incre_count = DiscourseTopicView.objects.filter(topic__in=blog_topics, viewed_at=report_date).count()

    like_user_action = DiscourseUserAction.objects.filter(
        action_type=DiscourseUserAction.ACTION_LIKE,
        target_topic__category_id__in=BLOG_CATEGORIES,
        target_topic__deleted_at__isnull=True,
        target_topic__visible=True,
        target_topic__archetype='regular'
    )
    topic_like_total_count = like_user_action.filter(
        created_at__lt=end_at
    ).count()
    topic_like_incre_count = like_user_action.filter(
        created_at__gte=start_at, created_at__lt=end_at
    ).count()

    AskTUGBlogTopicReport.objects.create(
        topic_total_count=topic_total_count,
        topic_incre_count=topic_incre_count,
        topic_author_total_count=topic_author_total_count,
        topic_author_incre_count=topic_author_incre_count,
        topic_views_total_count=topic_views_total_count,
        topic_views_incre_count=topic_views_incre_count,
        topic_like_total_count=topic_like_total_count,
        topic_like_incre_count=topic_like_incre_count,
        sequence=sequence,
        time_dimension=time_dimension,
        report_date=report_date
    )


def _user_summary_report(start_at, end_at, time_dimension, sequence, report_date):
    user_total_count = User.objects.filter(date_joined__lt=end_at).count()
    user_incre_count = User.objects.filter(
        date_joined__gte=start_at, date_joined__lt=end_at).count()

    lv0_user_total_count = UserPointStat.objects.filter(
        experience__lt=500).count()
    lv1_user_total_count = UserPointStat.objects.filter(
        experience__gte=500, experience__lt=1000).count()
    lv2_user_total_count = UserPointStat.objects.filter(
        experience__gte=1000, experience__lt=5000).count()
    lv3_user_total_count = UserPointStat.objects.filter(
        experience__gte=5000, experience__lt=10000).count()
    lv4_user_total_count = UserPointStat.objects.filter(
        experience__gte=10000).count()

    company_name_completed_user_total_count = User.objects.filter(
        company_name__isnull=False
    ).exclude(
        company_name__exact=''
    ).count()
    org_user_total_count = OrganizationUser.objects.filter(
        created_at__lt=end_at
    ).count()
    org_user_incre_count = OrganizationUser.objects.filter(
        created_at__gte=start_at, created_at__lt=end_at
    ).count()

    org_total_count = Organization.objects.filter(
        created_at__lt=end_at
    ).count()
    org_incre_count = Organization.objects.filter(
        created_at__gte=start_at, created_at__lt=end_at
    ).count()

    UserSummaryReport.objects.create(
        user_total_count=user_total_count,
        user_incre_count=user_incre_count,
        lv0_user_total_count=lv0_user_total_count,
        lv1_user_total_count=lv1_user_total_count,
        lv2_user_total_count=lv2_user_total_count,
        lv3_user_total_count=lv3_user_total_count,
        lv4_user_total_count=lv4_user_total_count,
        lv0_user_incre_count=0,
        lv1_user_incre_count=0,
        lv2_user_incre_count=0,
        lv3_user_incre_count=0,
        lv4_user_incre_count=0,
        company_name_completed_user_total_count=company_name_completed_user_total_count,
        company_name_completed_user_incre_count=0,
        org_user_total_count=org_user_total_count,
        org_user_incre_count=org_user_incre_count,
        org_total_count=org_total_count,
        org_incre_count=org_incre_count,
        sequence=sequence,
        time_dimension=time_dimension,
        report_date=report_date
    )


def _asktug_urgency_topic_report(start_at, end_at, time_dimension, sequence, report_date):
    urgencies = OrganizationTopicUrgency.objects.all()
    topic_total_count = urgencies.filter(created_at__lt=end_at).count()
    topic_incre_count = urgencies.filter(
        created_at__gte=start_at,
        created_at__lt=end_at
    ).count()
    
    solved_urgencies = urgencies.filter(solved_at__isnull=False)
    solved_topic_total_count = solved_urgencies.filter(created_at__lt=end_at).count()
    solved_topic_incre_count = solved_urgencies.filter(
        created_at__gte=start_at,
        created_at__lt=end_at
    ).count()

    def _avg_spend_hours(qs):
        data = qs.values_list('solved_at', 'created_at')
        count = len(data)
        total = sum([(d[0] - d[1]).total_seconds() for d in data])
        if count == 0:
            return 0
        return round((total / count) / 3600, 2)

    solved_topic_avg_spend_hours_of_total = _avg_spend_hours(
        solved_urgencies.filter(created_at__lt=end_at)
    )
    solved_topic_avg_spend_hours_of_incre = _avg_spend_hours(
        solved_urgencies.filter(created_at__gte=start_at, created_at__lt=end_at)
    )

    def _avg_replies(qs):
        topics_ids = list(qs.values_list('topic_id', flat=True))
        topics_count = len(topics_ids)
        if topics_count == 0:
            return 0
        with connections['discourse'].cursor() as cursor:
            cursor.execute("""
                SELECT
                    SUM(posts_count - 1)
                FROM topics
                WHERE id = any(%s)
            """, (topics_ids, ))
            reply_count = cursor.fetchall()[0][0]
        topics_count = len(topics_ids)
        return round(reply_count / topics_count, 2)
    
    solved_topic_avg_replies_of_total = _avg_replies(
        solved_urgencies.filter(created_at__lt=end_at)
    )
    solved_topic_avg_replies_of_incre = _avg_replies(
        solved_urgencies.filter(created_at__gte=start_at, created_at__lt=end_at)
    )
    
    AskTUGUrgencyTopicReport.objects.create(
        topic_total_count=topic_total_count,
        topic_incre_count=topic_incre_count,
        solved_topic_total_count=solved_topic_total_count,
        solved_topic_incre_count=solved_topic_incre_count,
        solved_topic_avg_spend_hours_of_total=solved_topic_avg_spend_hours_of_total,
        solved_topic_avg_spend_hours_of_incre=solved_topic_avg_spend_hours_of_incre,
        solved_topic_avg_replies_of_total=solved_topic_avg_replies_of_total,
        solved_topic_avg_replies_of_incre=solved_topic_avg_replies_of_incre,
        sequence=sequence,
        time_dimension=time_dimension,
        report_date=report_date
    )


def _asktug_urgency_topic_category_report(start_at, end_at, time_dimension, sequence, report_date):
    urgencies = OrganizationTopicUrgency.objects.all()
    categories = DiscourseCategory.objects.filter(Q(parent_category__isnull=True))
    topics_ids = topics_ids = list(urgencies.filter(created_at__lt=end_at).values_list('topic_id', flat=True))
    topics = DiscourseTopic.objects.filter(id__in=topics_ids)
    for c in categories:
        count = topics.filter(Q(category=c) | Q(category__parent_category=c)).count()
        AskTUGUrgencyTopicCategoryReport.objects.create(
            category_id=c.id,
            caterory_name=c.name,
            topic_count=count,
            sequence=sequence,
            time_dimension=time_dimension,
            report_date=report_date
        )


def run_all_report_task(start_at, end_at, time_dimension, sequence, report_date):
    _asktug_topic_summary_report(start_at, end_at, time_dimension, sequence, report_date)
    _asktug_topic_category_report(start_at, end_at, time_dimension, sequence, report_date)
    _asktug_qa_topic_report(start_at, end_at, time_dimension, sequence, report_date)
    _asktug_blog_topic_report(start_at, end_at, time_dimension, sequence, report_date)
    _user_summary_report(start_at, end_at, time_dimension, sequence, report_date)
    _asktug_urgency_topic_report(start_at, end_at, time_dimension, sequence, report_date)
    _asktug_urgency_topic_category_report(start_at, end_at, time_dimension, sequence, report_date)