import re
import logging
from datetime import timezone, timedelta
from urllib import parse

from django.core.validators import URLValidator
from django.core.exceptions import ValidationError
from django.utils.translation import gettext as _
from django.utils.decorators import method_decorator
from django.db import connections
from django.utils import timezone as d_timezone
from rest_framework.response import Response
from rest_framework.permissions import IsAdminUser

from pingcap_sso.api.base import AdminEndpoint
from pingcap_sso.api.permissions import MethodPermission
from pingcap_sso.models import (
    OrganizationTopicUrgency,
    User, PointProductRedemptionRecord, OrganizationTopicUrgency,
)
from pingcap_sso.user_behavior_track.models import TrackEvent
from pingcap_sso.utils.sql import dictfetchall
from pingcap_sso.utils.requests import get_pure_url
from pingcap_sso.admin_audit_log.decorators import admin_audit_log_exempt

logger = logging.getLogger(__name__)


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


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

    permission_classes = (IsAdminUser,)

    def get(self, request):
        user = request.user
        return Response({
            'detail': _('success'),
            'data': {
                'id': user.id,
                'username': user.username,
                'avatar_url': user.avatar_url,
                'is_staff': user.is_staff,
                'permissions': user.get_all_permissions()
            }
        })


class CommunitySummaryEndpoint(AdminEndpoint):

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

    def get(self, request):
        """audit_log: 在社区管理插件中查看了社区概要"""
        user_count = User.objects.count()
        _7_days_ago = d_timezone.now() - timedelta(days=7)
        _30_days_ago = d_timezone.now() - timedelta(days=30)
        _90_days_ago = d_timezone.now() - timedelta(days=90)
        _7_days_active = TrackEvent.objects.filter(
            openid__isnull=False,
            event_time__gte=_7_days_ago,
        ).values('openid').distinct().count()
        _30_days_active = TrackEvent.objects.filter(
            openid__isnull=False,
            event_time__gte=_30_days_ago,
        ).values('openid').distinct().count()
        this_week_start = (d_timezone.now() - timedelta(days=d_timezone.now().weekday())).astimezone(
            timezone(timedelta(hours=8))
        ).replace(hour=0, minute=0, second=0, microsecond=0)
        _30_days_incre_user_count = User.objects.filter(
            date_joined__gte=_30_days_ago).count()
        unprocessed_orders_count = PointProductRedemptionRecord.objects.filter(
            tracking_number=''
        ).count()
        this_week_new_orders_count = PointProductRedemptionRecord.objects.filter(
            created_at__gte=this_week_start
        ).count()
        unprocessed_urgency_topic_count = OrganizationTopicUrgency.objects.filter(
            solved_at__isnull=True
        ).count()
        this_week_new_urgency_count = OrganizationTopicUrgency.objects.filter(
            created_at__gte=this_week_start
        ).count()

        _30_days_ago = _30_days_ago.replace(
            hour=0, minute=0, second=0, microsecond=0)
        _90_days_ago = _90_days_ago.replace(
            hour=0, minute=0, second=0, microsecond=0)

        with connections['default'].cursor() as cursor:
            # TODO: use django ORM to query
            # TODO: use ShangHai timezone
            cursor.execute("""
                SELECT
                    DATE_FORMAT(event_time, '%%Y/%%m/%%d') AS `event_date`,
                    COUNT(DISTINCT openid) AS `active_count`
                FROM `track_events`
                WHERE
                    event_time >= %(start_at)s AND
                    openid is not null
                GROUP BY event_date
                ORDER BY event_date
            """, {'start_at': _90_days_ago})
            _90_day_active_data = cursor.fetchall()

            cursor.execute("""
                SELECT
                    DATE_FORMAT(date_joined, '%%Y/%%m/%%d') AS `date`,
                    COUNT(*) AS `count`
                FROM `auth_user`
                WHERE
                    date_joined >= %(start_at)s
                GROUP BY date
                ORDER BY date
            """, {'start_at': _90_days_ago})
            _90_day_incre_data = cursor.fetchall()

        return Response({
            'detail': _('success'),
            'data': {
                'user_count': user_count,
                '_7_days_active': _7_days_active,
                '_30_days_active': _30_days_active,
                '_30_day_active_data': _90_day_active_data,
                '_90_day_incre_data': _90_day_incre_data,
                '_30_days_incre_user_count': _30_days_incre_user_count,
                'unprocessed_orders_count': unprocessed_orders_count,
                'this_week_new_orders_count': this_week_new_orders_count,
                'unprocessed_urgency_topic_count': unprocessed_urgency_topic_count,
                'this_week_new_urgency_count': this_week_new_urgency_count,
            }
        })


class PageSummaryEndpoint(AdminEndpoint):

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

    def get(self, request, *args, **kwargs):
        """audit_log: 在社区管理插件中查看了页面概要"""
        url = request.GET.get('url', '')
        try:
            URLValidator()(url)
        except ValidationError:
            return Response(status=400)

        url = get_pure_url(url)
        host = parse.urlparse(url).hostname
        event_type = 'site.{}.pageview'.format(host.replace('.', '#'))

        today = d_timezone.now().astimezone(
            timezone(timedelta(hours=8))
        ).replace(
            hour=23, minute=59, second=59, microsecond=0
        )
        last_30_day = (today - timedelta(days=30)).replace(
            hour=0, minute=0, second=0, microsecond=0
        )

        r_topic = ASKTUG_TOPIC_REGEX.match(url)
        if r_topic:
            url = r_topic.group('topic_url')
            _url_filter_sql = """(event_body ->> '$.pure_url' = %(url)s OR
                event_body ->> '$.pure_url' like %(url_like)s)"""
        else:
            _url_filter_sql = "event_body ->> '$.pure_url' = %(url)s"

        with connections['default'].cursor() as cursor:
            cursor.execute("""
                SELECT
                    (DATE(event_time)) AS `event_date`,
                    COUNT(id) AS `event_count`
                FROM `track_events`
                WHERE
                    event_time >= %(start_date)s AND
                    event_time <= %(end_date)s AND
                    event_type = %(event_type)s AND {url_filter_sql}
                GROUP BY event_date
                ORDER BY `event_date`
            """.format(url_filter_sql=_url_filter_sql),
                {
                    'start_date': last_30_day, 'end_date': today,
                    'event_type': event_type, 'url': url, 'url_like': '{}/%'.format(url)
            }
            )
            count_distribution = dict(cursor.fetchall())

            cursor.execute("""
                SELECT
                    (DATE(event_time)) AS `event_date`,
                    COUNT(distinct(openid)) AS `event_count`
                FROM `track_events`
                WHERE
                    event_time >= %(start_date)s AND
                    event_time <= %(end_date)s AND
                    openid is not null AND
                    event_type = %(event_type)s AND {url_filter_sql}
                GROUP BY event_date
                ORDER BY `event_date`
            """.format(url_filter_sql=_url_filter_sql),
                {
                    'start_date': last_30_day, 'end_date': today,
                    'event_type': event_type, 'url': url, 'url_like': '{}/%'.format(url)
            }
            )
            auth_user_distribution = dict(cursor.fetchall())

            cursor.execute("""
                SELECT
                    (DATE(event_time)) AS `event_date`,
                    COUNT(distinct(track_id)) AS `event_count`
                FROM `track_events`
                WHERE
                    event_time >= %(start_date)s AND
                    event_time <= %(end_date)s AND
                    openid is null AND
                    event_type = %(event_type)s AND {url_filter_sql}
                GROUP BY event_date
                ORDER BY `event_date` ASC
            """.format(url_filter_sql=_url_filter_sql),
                {
                    'start_date': last_30_day, 'end_date': today,
                    'event_type': event_type, 'url': url, 'url_like': '{}/%'.format(url)
            }
            )
            anonymous_user_distribution = dict(cursor.fetchall())

            cur_day = last_30_day.date()
            time_series, count_series, auth_user_series, anonymous_user_series = [], [], [], []
            while cur_day <= today.date():
                time_series.append(cur_day.strftime('%m/%d'))
                count_series.append(count_distribution.get(cur_day, 0))
                auth_user_series.append(auth_user_distribution.get(cur_day, 0))
                anonymous_user_series.append(
                    anonymous_user_distribution.get(cur_day, 0))
                cur_day += timedelta(days=1)

        return Response({
            'detail': _('success'),
            'data': {
                'url': url,
                'time_series': time_series,
                'count_series': count_series,
                'auth_user_series': auth_user_series,
                'anonymous_user_series': anonymous_user_series
            }
        })


class PageviewHistoryEndpoint(AdminEndpoint):

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

    def get(self, request, *args, **kwargs):
        """audit_log: 在社区管理插件中查看了页面浏览历史"""
        url = request.GET.get('url', '')
        try:
            URLValidator()(url)
        except ValidationError:
            return Response(status=400)

        url = get_pure_url(url)
        host = parse.urlparse(url).hostname
        event_type = 'site.{}.pageview'.format(host.replace('.', '#'))

        today = d_timezone.now().astimezone(
            timezone(timedelta(hours=8))
        ).replace(
            hour=23, minute=59, second=59, microsecond=0
        )
        last_30_day = (today - timedelta(days=60)).replace(
            hour=0, minute=0, second=0, microsecond=0
        )

        r_topic = ASKTUG_TOPIC_REGEX.match(url)
        if r_topic:
            url = r_topic.group('topic_url')
            _url_filter_sql = """(te.event_body ->> '$.pure_url' = %(url)s OR
                te.event_body ->> '$.pure_url' like %(url_like)s)"""
        else:
            _url_filter_sql = "te.event_body ->> '$.pure_url' = %(url)s"

        with connections['default'].cursor() as cursor:
            cursor.execute("""
                SELECT
                    te.id, u.username, u.company_name,
                    DATE_FORMAT(CONVERT_TZ(te.event_time, '+00:00', '+08:00'), '%%m/%%d-%%H:%%i') as event_time, ip
                FROM `track_events` te left join
                    auth_user u on u.openid = te.openid
                WHERE
                    te.openid is not null AND
                    te.event_time >= %(start_date)s AND
                    te.event_time <= %(end_date)s AND
                    te.event_type = %(event_type)s AND {url_filter_sql}
                ORDER BY te.event_time
                DESC LIMIT 200
            """.format(url_filter_sql=_url_filter_sql),
                {
                    'start_date': last_30_day, 'end_date': today,
                    'event_type': event_type, 'url': url, 'url_like': '{}/%'.format(url)
            }
            )
            histories = dictfetchall(cursor)

        return Response({
            'detail': _('success'),
            'data': histories
        })