from datetime import timedelta
from collections import defaultdict

from celery.utils.log import get_task_logger
from django.db import transaction, connection
from django.utils import timezone

from pingcap_sso.celery import app as celery_app
from pingcap_sso.models import (
    UserProfileCollections,
    User,
    UserInfoMisc,
)
from pingcap_sso.user_behavior_track.models import TrackEvent
from pingcap_sso.discourse.lib import user_summary as asktug_user_summary
from pingcap_sso.lib.blog import blog_user_summary

logger = get_task_logger(__name__)


@celery_app.task
def update_user_primary_profile(user_id):
    with transaction.atomic():
        try:
            user = User.objects.select_for_update().get(id=user_id)
        except User.DoesNotExist:
            logger.error(f'User {user_id} does not exist')
            return
        primary_profile, _ = UserProfileCollections.objects.get_or_create(
            user=user, is_primary=True
        )

    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'
    ).order_by('-updated_at')
    profiles = defaultdict(list)
    for pq in profiles_qs:
        profiles[pq['source']].append(pq)

    def _get_data_by_source(attr, source=None):
        data = None
        if source:
            qs = profiles.get(source, [])
            for q in qs:
                if q[attr]:
                    data = q[attr]
                    break
        if not data:
            for q in profiles_qs:
                if q[attr]:
                    data = q[attr]
                    break
        return data
    
    primary_profile.name = _get_data_by_source('name', UserProfileCollections.SOURCE_PE_PROFILE)
    primary_profile.gender = _get_data_by_source('gender', UserProfileCollections.SOURCE_PE_PROFILE)
    primary_profile.company = _get_data_by_source('company')
    primary_profile.industry = _get_data_by_source('industry')
    primary_profile.job_title = _get_data_by_source('job_title', UserProfileCollections.SOURCE_PROFILE)
    primary_profile.address = _get_data_by_source('address')
    primary_profile.phone = _get_data_by_source('phone', UserProfileCollections.SOURCE_PROFILE)
    primary_profile.email = _get_data_by_source('email', UserProfileCollections.SOURCE_PROFILE)
    primary_profile.wechat_id = _get_data_by_source('wechat_id')
    primary_profile.github_id = _get_data_by_source('github_id', UserProfileCollections.SOURCE_PROFILE)
    primary_profile.tidb_use_experience = _get_data_by_source('tidb_use_experience')
    primary_profile.tidb_use_stage = _get_data_by_source('tidb_use_stage')
    primary_profile.source = None
    primary_profile.save()

    user_info_misc, _  = UserInfoMisc.objects.get_or_create(user=user)
    asktug_summary = asktug_user_summary(user)
    blog_summary = blog_user_summary(user)
    user_info_misc.name = primary_profile.name
    user_info_misc.gender = primary_profile.gender
    user_info_misc.company = primary_profile.company
    user_info_misc.industry = primary_profile.industry
    user_info_misc.job_title = primary_profile.job_title
    user_info_misc.address = primary_profile.address
    user_info_misc.phone = primary_profile.phone
    user_info_misc.email = primary_profile.email
    user_info_misc.wechat_id = primary_profile.wechat_id
    user_info_misc.github_id = primary_profile.github_id
    user_info_misc.tidb_use_experience = primary_profile.tidb_use_experience
    user_info_misc.tidb_use_stage = primary_profile.tidb_use_stage
    user_info_misc.asktug_topic_count = asktug_summary['topic_count'] if asktug_summary else 0
    user_info_misc.asktug_reply_count = asktug_summary['post_count'] if asktug_summary else 0
    user_info_misc.asktug_like_count = asktug_summary['likes_given'] if asktug_summary else 0
    user_info_misc.blog_article_count = blog_summary['published_posts_count'] if blog_summary else 0
    user_info_misc.save()


def high_frequency_addresses_by_track_events(user, start_at=None):
    with connection.cursor() as cursor:
        if start_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
                GROUP BY pil.country, pil.region, pil.city
                ORDER BY cnt DESC LIMIT 3
            """, [user.openid, start_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({
                'country': r[0],
                'region': r[1],
                'city': 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


@celery_app.task
def update_user_info_misc_ip_region():
    now = timezone.now()
    last_2_days = now - timedelta(days=2)
    last_half_year = now - timedelta(days=31 * 6)

    openids = TrackEvent.objects.filter(
        event_time__gte=last_2_days,
        openid__isnull=False,
    ).values_list('openid')
    users = User.objects.filter(openid__in=openids)

    logger.info(f'update_user_info_misc_ip_region task will update {users.count()} users')
    for u in users:
        user_info_misc, _  = UserInfoMisc.objects.get_or_create(user=u)
        user_info_misc.recent_half_year_ip_top3_region = high_frequency_addresses_by_track_events(u, last_half_year)
        user_info_misc.all_ip_top3_region = high_frequency_addresses_by_track_events(u)
        user_info_misc.save()
    logger.info('update_user_info_misc_ip_region: done')