import logging
from datetime import timedelta, datetime
from collections import defaultdict

from django.utils.translation import gettext as _
from django.db import connections, models
from django.utils import timezone
from django.utils.decorators import method_decorator
from rest_framework.response import Response
from rest_framework.permissions import IsAdminUser
from rest_framework.throttling import ScopedRateThrottle
from rest_framework.exceptions import ParseError

from pingcap_sso.api.base import AdminEndpoint
from pingcap_sso.api.permissions import MethodPermission
from pingcap_sso.models import User, UserTiDBUsage
from pingcap_sso.discourse.models import DiscourseTopic, DiscourseSingleSignOnRecord
from pingcap_sso.discourse.consts import QA_CATEGORIES
from pingcap_sso.user_behavior_track.models import TrackEvent
from pingcap_sso.utils.sql import dictfetchall
from pingcap_sso.admin_audit_log.decorators import admin_audit_log_exempt
from pingcap_sso.lib import ip2location

logger = logging.getLogger(__name__)


@method_decorator(admin_audit_log_exempt, name='dispatch')
class RecentlyOverviewEndpoint(AdminEndpoint):

    permission_classes = (IsAdminUser, MethodPermission)
    perms_map = {
        'GET': ['pingcap_sso.view_site_analytics'],
    }
    throttle_classes = (ScopedRateThrottle, )
    throttle_scope = 'admin'

    def get(self, request, *args, **kwargs):
        now = timezone.now()
        last_three_minutes = now - timedelta(minutes=3)

        track_events = TrackEvent.objects.filter(event_time__gte=last_three_minutes)
        events_count = track_events.count()
        login_user_events = track_events.filter(openid__isnull=False)
        anonymous_session_count = track_events.filter(
            openid__isnull=True).values('track_id').distinct().count()
        auth_user_openids = login_user_events.values_list('openid', flat=True).distinct()
        auth_users = list(User.objects.filter(openid__in=auth_user_openids).values(
            'username', 'email', 'phone', 'openid', 'avatar_url').order_by('username'))
        return Response({
            'detail': _('success'),
            'data': {
                'events_count': events_count,
                'auth_users': list(auth_users),
                'auth_user_count': len(auth_users),
                'anonymous_session_count': anonymous_session_count
            }
        })


@method_decorator(admin_audit_log_exempt, name='dispatch')
class RecentlyEventsEndpoint(AdminEndpoint):

    permission_classes = (IsAdminUser, MethodPermission)
    perms_map = {
        'GET': ['pingcap_sso.view_site_analytics'],
    }
    throttle_classes = (ScopedRateThrottle, )
    throttle_scope = 'admin'

    def get(self, request, *args, **kwargs):
        try:
            start = request.GET.get('start', '2020-01-01T00:00:00.000000')
        except Exception:
            return Response({
                'detail': 'params wrong'
            }, 400)
        now = timezone.now()
        last_one_minutes = now - timedelta(minutes=1)

        with connections['default'].cursor() as cursor:
            cursor.execute("""
                SELECT t.id, t.openid, t.event_time, t.description, t.event_type,
                       t.track_id, u.username, u.avatar_url
                FROM track_events t left join auth_user u on u.openid = t.openid
                WHERE t.event_time > %s and t.event_time > %s
                ORDER BY t.event_time
            """, (start, last_one_minutes))
            data = dictfetchall(cursor)
        return Response({
            'detail': _('success'),
            'data': data
        })


class EventSearchEndpoint(AdminEndpoint):

    permission_classes = (IsAdminUser, MethodPermission)
    perms_map = {
        'GET': ['pingcap_sso.view_user_analytics'],
    }

    def get(self, request, *args, **kwargs):
        """audit_log: 查看了用户行为历史"""
        try:
            search = request.GET.get('search')
            start_date = datetime.strptime(
                request.GET.get('start_date'), '%Y-%m-%d')
            end_date = datetime.strptime(
                request.GET.get('end_date'), '%Y-%m-%d')
            cur_page = int(request.GET.get('currentPage', 1))
            per_page = int(request.GET.get('perPage', 10))
        except Exception:
            return Response({
                'detail': _('wrong params')
            }, 400)

        can_view_user_contact_info = request.user.has_perm('pingcap_sso.view_user_contact_info')

        try:
            user = User.objects.get(username=request.GET.get('username'))
        except User.DoesNotExist:
            user = None

        start_date = timezone.make_aware(start_date, timezone.get_default_timezone())
        end_date = timezone.make_aware(
            end_date + timedelta(days=1), timezone.get_default_timezone())

        if start_date > end_date:
            start_date, end_date = end_date, start_date

        events = TrackEvent.objects.filter(
            event_time__gte=start_date,
            event_time__lt=end_date)

        if search:
            events = events.filter(
                description__icontains=search)

        if user:
            events = events.filter(openid=user.openid)

        events = events.order_by('-event_time')

        total_num = events.count()
        events = list(events[(cur_page - 1) * per_page:cur_page * per_page])

        ipaddresses = list(set([e.ip for e in events]))
        ip_lookup_results = ip2location.bulk_lookup(ipaddresses)

        def _ip_to_location(ip):
            if not ip:
                return ''
            l = ip_lookup_results.get(ip)
            if l:
                return f'{l.get("country_name", "")} {l.get("region", "")} {l.get("city", "")}'
            else:
                return ''

        data = []
        openid_user_map = {}
        for e in events:
            username = None
            if e.openid:
                if e.openid not in openid_user_map:
                    try:
                        user = User.objects.get(openid=e.openid)
                        openid_user_map[e.openid] = user.username
                    except User.DoesNotFound:
                        username = None
                username = openid_user_map[e.openid]

            _e = {
                'username': username,
                'openid': e.openid,
                'event_type': e.event_type,
                'description': e.description,
                'event_time': timezone.localtime(e.event_time).strftime('%Y-%m-%d %H:%M:%S'),
                'ip_location': _ip_to_location(e.ip)
            }
            if can_view_user_contact_info:
                _e['ip'] = e.ip
            data.append(_e)

        return Response({
            'detail': _('success'),
            'data': data,
            'total_num': total_num,
            'per_page': per_page,
            'cur_page': cur_page
        })


class ReportBaseEndpoint(AdminEndpoint):

    def convert_args(self, request, *args, **kwargs):
        try:
            start_date = datetime.strptime(
                request.GET.get('start_date'), '%Y-%m-%d')
            end_date = datetime.strptime(
                request.GET.get('end_date'), '%Y-%m-%d')
        except Exception:
            raise ParseError
        # 数据库存了 UTC 时间，而接下来的报表会使用数据库函数 date 去聚合数据
        # 为了不出现有歧义的数据, 报表将以 UTC 时间为基准出, 影响并不会太大
        start_date = timezone.make_aware(start_date, timezone.utc)
        end_date = timezone.make_aware(end_date + timedelta(days=1), timezone.utc)
        if start_date > end_date:
            start_date, end_date = end_date, start_date
        kwargs['start_date'] = start_date
        kwargs['end_date'] = end_date
        return (args, kwargs)


class ActiveUserReportEndpoint(ReportBaseEndpoint):

    permission_classes = (IsAdminUser, MethodPermission)
    perms_map = {
        'GET': ['pingcap_sso.view_site_analytics'],
    }

    def get(self, request, start_date, end_date, *args, **kwargs):
        """audit_log: 查看了活跃用户报表"""
        # qs = TrackEvent.objects.extra(select={'event_date': 'date(event_time)'})\
        #                .filter(event_time__gte=start_date, event_time__lt=end_date)\
        #                .values('openid', 'event_time')\
        #                .annotate(active_days=models.Count('event_date', distinct=True))\
        #                .order_by('-active_days')
        with connections['default'].cursor() as cursor:
            cursor.execute("""
                SELECT u.username, u.phone, u.email, a.openid, a.active_days
                FROM
                    (SELECT openid, COUNT(DISTINCT(DATE(event_time))) as active_days
                     FROM track_events
                     WHERE event_time >= %s and event_time < %s and openid is not null
                     GROUP BY openid
                     ORDER BY active_days DESC) a LEFT JOIN auth_user u on a.openid = u.openid
            """, (start_date, end_date))
            active_users = dictfetchall(cursor)
            active_days_map = defaultdict(int)
            for d in active_users:
                active_days_map[d['active_days']] += 1
            active_days_distribution = sorted(
                [{'active_days': k, 'user_count': v} for k, v in active_days_map.items()],
                key=lambda x: x['active_days']
            )
        return Response({
            'detail': _('success'),
            'data': {
                'active_users': active_users,
                'active_days_distribution': active_days_distribution
            }
        })


class DAUReportEndpoint(ReportBaseEndpoint):

    permission_classes = (IsAdminUser, MethodPermission)
    perms_map = {
        'GET': ['pingcap_sso.view_site_analytics'],
    }

    def get(self, request, start_date, end_date, *args, **kwargs):
        """audit_log: 查看了 DAU 报表"""
        dimension = request.GET.get('dimension', 'monthly')
        if dimension == 'monthly':
            dt = 'DATE_FORMAT(CONVERT_TZ(event_time, "+00:00","+08:00"), "%%Y-%%m")'
        elif dimension == 'daily':
            dt = 'DATE(CONVERT_TZ(event_time, "+00:00","+08:00"))'
        elif dimension == 'hourly':
            dt = 'DATE_FORMAT(CONVERT_TZ(event_time, "+00:00","+08:00"), "%%Y/%%m/%%d-%%H")'
        else:
            return Response(status=400)

        qs = TrackEvent.objects.extra(select={'event_date': dt})\
                       .filter(event_time__gte=start_date,
                               event_time__lt=end_date,
                               openid__isnull=False)\
                       .values('event_date')\
                       .annotate(user_count=models.Count('openid', distinct=True))\
                       .order_by('event_date')
        return Response({
            'detail': _('success'),
            'data': list(qs)
        })


class EventTrendingReportEndpoint(ReportBaseEndpoint):

    permission_classes = (IsAdminUser, MethodPermission)
    perms_map = {
        'GET': ['pingcap_sso.view_site_analytics'],
    }

    def get(self, request, start_date, end_date, *args, **kwargs):
        """audit_log: 查看了用户行为趋势报表"""
        dimension = request.GET.get('dimension', 'monthly')
        if dimension == 'monthly':
            dt = 'DATE_FORMAT(event_time, "%%Y-%%m")'
        else:
            dt = 'DATE(event_time)'

        qs = TrackEvent.objects.extra(select={'event_date': dt})\
                       .filter(event_time__gte=start_date,
                               event_time__lt=end_date)\
                       .values('event_date')\
                       .annotate(event_count=models.Count('id'))\
                       .order_by('event_date')
        return Response({
            'detail': _('success'),
            'data': list(qs)
        })


class HotDocsReportEndpoint(ReportBaseEndpoint):

    permission_classes = (IsAdminUser, MethodPermission)
    perms_map = {
        'GET': ['pingcap_sso.view_site_analytics'],
    }

    def get(self, request, start_date, end_date, *args, **kwargs):
        """audit_log: 查看了热门文档报表"""
        with connections['default'].cursor() as cursor:
            cursor.execute("""
                SELECT
                    event_body ->> '$.pure_url' as dl,
                    count(*) as view_count
                FROM
                    track_events
                WHERE
                    event_type = 'site.docs#pingcap#com.pageview' and
                    event_time >= %s and event_time <= %s
                GROUP BY dl
                ORDER BY view_count DESC
                LIMIT 200;
            """, (start_date, end_date))
            data = dictfetchall(cursor)
        return Response({
            'detail': _('success'),
            'data': data
        })


class HotPagesReportEndpoint(ReportBaseEndpoint):

    permission_classes = (IsAdminUser, MethodPermission)
    perms_map = {
        'GET': ['pingcap_sso.view_site_analytics'],
    }

    def get(self, request, start_date, end_date, *args, **kwargs):
        """audit_log: 查看了热门网页报表"""
        with connections['default'].cursor() as cursor:
            cursor.execute("""
                SELECT
                    event_body ->> '$.pure_url' as dl,
                    count(*) as view_count
                FROM
                    track_events
                WHERE
                    event_type like 'site.%%.pageview' and
                    event_time >= %s and event_time <= %s
                GROUP BY dl
                ORDER BY view_count DESC
                LIMIT 200;
            """, (start_date, end_date))
            data = dictfetchall(cursor)
        return Response({
            'detail': _('success'),
            'data': data
        })


class AskTUGTopicCreatorCompanyDistributeEndpoint(ReportBaseEndpoint):

    permission_classes = (IsAdminUser, MethodPermission)
    perms_map = {
        'GET': ['pingcap_sso.view_site_analytics'],
    }

    def get(self, request, start_date, end_date, *args, **kwargs):
        """audit_log: 查看了 AskTUG 提问用户分布报表"""
        discourse_user_ids = DiscourseTopic.objects.filter(
            deleted_at__isnull=True, archetype='regular', user_id__gt=1,
            created_at__gte=start_date, created_at__lte=end_date
        ).values_list('user_id', flat=True)
        discourse_user_id_openid_map = dict(DiscourseSingleSignOnRecord.objects.filter(
            user_id__in=discourse_user_ids).values_list('user_id', 'external_id'))
        user_openids = [discourse_user_id_openid_map[uid] for uid in discourse_user_ids]
        sso_user_openid_comapny_map = dict(User.objects.filter(
            openid__in=set(user_openids)).values_list('openid', 'company_name'))
        company_names = [
            sso_user_openid_comapny_map.get(openid, 'other') or 'other'
            for openid in user_openids
        ]
        data = defaultdict(int)
        for c in company_names:
            data[c] += 1
        data = sorted(
            [{'name': name, 'value': count} for name, count in data.items()],
            key=lambda i: i['value'],
            reverse=True
        )
        return Response({
            'detail': _('success'),
            'data': data
        })


class TiDBUsageDistributeEndpoint(ReportBaseEndpoint):

    permission_classes = (IsAdminUser, MethodPermission)
    perms_map = {
        'GET': ['pingcap_sso.view_site_analytics'],
    }

    def get(self, request, start_date, end_date, *args, **kwargs):
        """audit_log: 查看了用户使用的 TiDB 版本分布报表"""
        usage_qs = UserTiDBUsage.objects.filter(
            created_at__gte=start_date, created_at__lte=end_date,
            deleted_at__isnull=True
        )

        topic_version_distribute = usage_qs.filter(
            asktug_topic_id__isnull=False
        ).values('version').annotate(
            count=models.Count('id')
        )
        user_version_distribute = usage_qs.values('version').annotate(
            count=models.Count('user', distinct=True)
        )

        topic_version_distribute = {d['version']: d['count'] for d in topic_version_distribute}
        user_version_distribute = {d['version']: d['count'] for d in user_version_distribute}
        versions = set(topic_version_distribute.keys()).union(set(user_version_distribute.keys()))

        distribute = list()
        for v in versions:
            distribute.append({
                'version': v,
                'topic_count': topic_version_distribute.get(v, 0),
                'user_count': user_version_distribute.get(v, 0)
            })

        distribute = sorted(distribute, key=lambda i: i['version'])

        creator_rank = usage_qs.filter(creator__isnull=False).values('creator__username').annotate(
            count=models.Count('id')
        ).order_by('count')


        qa_topic_qs = DiscourseTopic.objects.filter(
            deleted_at__isnull=True, archetype='regular', user_id__gt=1,
            created_at__gte=start_date, created_at__lte=end_date,
            category_id__in=QA_CATEGORIES
        )

        qa_topic_count = qa_topic_qs.count()

        tagged_qa_topic_count = qa_topic_qs.filter(
            id__in=list(
                usage_qs.values_list('asktug_topic_id', flat=True)
            )
        ).count()

        return Response({
            'detail': _('success'),
            'data': {
                'distribute': distribute,
                'creator_rank': list(creator_rank),
                'qa_topic_count': qa_topic_count,
                'tagged_qa_topic_count': tagged_qa_topic_count
            }
        })