import calendar
from datetime import datetime, timedelta, timezone
from django.utils import timezone as django_timezone
from django.db import connection

from pingcap_sso.models import (
    UserDailyActivity,
    UserInfoMisc,
    UserProfileCollections,
    UserTiDBUsage,
)
from pingcap_sso.utils import time as ptime


def user_activities(user):
    now = django_timezone.now().astimezone(timezone(timedelta(hours=8)))
    start_at = (now - timedelta(days=30 * 11)).replace(day=1, hour=0, minute=0, second=0, microsecond=0)
    end_at = datetime(
        now.year, now.month,
        calendar.monthrange(now.year, now.month)[1],
        tzinfo=timezone(timedelta(hours=8))
    ).replace(hour=23, minute=59, second=59)
    activities = UserDailyActivity.objects.filter(
        day__gte=start_at,
        day__lt=end_at,
        user=user
    ).values_list('day', 'community_score')
    date_range = [ptime.local_time_format(start_at, '%Y-%m-%d'), ptime.local_time_format(end_at, '%Y-%m-%d')]
    return activities, date_range


def user_profile_collections(user):
    try:
        primary_profile = UserInfoMisc.objects.get(user=user)
    except UserInfoMisc.DoesNotExist:
        return None

    profiles_qs = UserProfileCollections.objects.filter(
        user=user, is_primary=False, deleted_at__isnull=True
    ).values(
        'name', 'gender', 'company', 'industry', 'job_title', 'address',
        'phone', 'email', 'wechat_id', 'github_id', 'tidb_use_experience',
        'tidb_use_stage', 'source', 'id', 'updated_at', 'comment'
    )
    def _get_raw_values_by_attr(attr):
        primary_value = getattr(primary_profile, attr, None)
        raw_values = [
            {
                'id': q['id'],
                'value': q[attr],
                'source': dict(UserProfileCollections.SOURCE_CHOICES).get(q['source'], q['source']),
                'comment': q['comment'],
                'updated_at': ptime.local_time_format(q['updated_at'])
            } for q in profiles_qs if q[attr]
        ]
        return {
            'value': primary_value,
            'raw_values': raw_values
        }

    data = {
        'name': _get_raw_values_by_attr('name'),
        'gender': _get_raw_values_by_attr('gender'),
        'company': _get_raw_values_by_attr('company'),
        'industry': _get_raw_values_by_attr('industry'),
        'job_title': _get_raw_values_by_attr('job_title'),
        'address': _get_raw_values_by_attr('address'),
        'phone': _get_raw_values_by_attr('phone'),
        'email': _get_raw_values_by_attr('email'),
        'wechat_id': _get_raw_values_by_attr('wechat_id'),
        'github_id': _get_raw_values_by_attr('github_id'),
        'tidb_use_experience': _get_raw_values_by_attr('tidb_use_experience'),
        'tidb_use_stage': _get_raw_values_by_attr('tidb_use_stage'),
    }
    return data


def user_profile_for_marketing(user):
    data = {
        'name': None,
        'company': None,
        'industry': None,
        'job_title': None,
        'address': None,
        'phone': None,
        'email': None,
        'wechat_id': None,
        'github_id': None,
        'tidb_use_experience': None,
        'tidb_use_stage': None,
    }
    try:
        primary_profile = UserInfoMisc.objects.get(user=user)
        data['name'] = primary_profile.name
        data['company'] = primary_profile.company
        data['industry'] = primary_profile.industry
        data['job_title'] = primary_profile.job_title
        data['address'] = primary_profile.address
        data['phone'] = primary_profile.phone
        data['email'] = primary_profile.email
        data['wechat_id'] = primary_profile.wechat_id
        data['github_id'] = primary_profile.github_id
        data['tidb_use_experience'] = primary_profile.tidb_use_experience
        data['tidb_use_stage'] = primary_profile.tidb_use_stage
    except UserInfoMisc.DoesNotExist:
        data['name'] = user.name
        data['company'] = user.company_name
        data['job_title'] = user.job_title_text
        data['address'] = user.address
        data['phone'] = user.phone
        data['email'] = user.email or user.signup_email
    return data


def high_frequency_addresses_by_track_events(user, start_at=None, end_at=None):
    with connection.cursor() as cursor:
        if start_at and end_at:
            cursor.execute("""
                SELECT pil.country, pil.region, pil.city, count(*) as cnt
                FROM track_events te left join pingcap_ip_location pil on te.ip = pil.ip
                WHERE
                    te.openid = %s and te.ip is not null and
                    pil.city <> '-' and
                    te.event_time >= %s and te.event_time < %s
                GROUP BY pil.country, pil.region, pil.city
                ORDER BY cnt DESC LIMIT 3
            """, [user.openid, start_at, end_at])
        else:
            cursor.execute("""
                SELECT pil.country, pil.region, pil.city, count(*) as cnt
                FROM track_events te left join pingcap_ip_location pil on te.ip = pil.ip
                WHERE
                    te.openid = %s and te.ip is not null and
                    pil.city <> '-'
                GROUP BY pil.country, pil.region, pil.city
                ORDER BY cnt DESC LIMIT 3
            """, [user.openid])

        data = []
        for r in cursor.fetchall():
            data.append({
                'address': f'{r[0]} {r[1]} {r[2]}',
                'count': r[3]
            })
        count = sum([d['count'] for d in data])
        for d in data:
            d['percent'] = round(d['count'] / count * 100, 2)
        return data