import logging
import uuid
from contextlib import contextmanager

from django.utils.translation import gettext as _
from django.db import connections, models
from rest_framework.permissions import IsAdminUser
from rest_framework.response import Response

from pingcap_sso.api.base import AdminEndpoint
from pingcap_sso.api.permissions import MethodPermission
from pingcap_sso.discourse.consts import BLOG_CATEGORIES
from pingcap_sso.models import (
    User, PortraitKeyword, PortraitKeywordUserHistory,
    UserProfileCollections
)

logger = logging.getLogger(__name__)


class SearchKeywordEndpoint(AdminEndpoint):

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

    def get(self, request, *args, **kwargs):
        """audit_log: 搜索了用户画像关键词"""
        search = request.GET.get('word', '')
        qs = PortraitKeyword.objects.all()
        if search:
            qs = qs.filter(models.Q(name__icontains=search))
        return Response({
            'detail': _('success'),
            'data': [{
                'id': u.id,
                'name': u.name,
            } for u in qs[:100]]
        })


class FilterEndpoint(AdminEndpoint):

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

    def post(self, request, *args, **kwargs):
        """audit_log: 使用用户画像进行了筛选"""
        cur_page = int(request.GET.get('currentPage', 1))
        per_page = int(request.GET.get('perPage', 10))
        user_attributes = request.data['user_attributes']
        keywords_fullmatch = request.data['keywords_fullmatch']
        keywords = request.data['keywords']

        with connections['default'].cursor() as cursor:
            with temporary_user_table(cursor) as temp_table:
                attr_count = 0
                for attr_name in user_attributes:
                    _sql = get_filter_sql(attr_name)
                    if _sql:
                        attr_count += 1
                        cursor.execute(f"""
                            INSERT INTO { temp_table } (user_id, code) { _sql };
                        """, (attr_name, ))
                users = User.objects.all()
                if attr_count > 0:
                    users = users.filter(
                        id__in=models.expressions.RawSQL(f"""
                            SELECT user_id
                            FROM { temp_table }
                            GROUP BY user_id having count(*) = %s
                        """, (attr_count, ))
                    )
                if keywords:
                    filter_qsl = models.Q()
                    for k in keywords:
                        filter_qsl |= models.Q(action=k['action_type'], keyword_id=k['keyword_id'])

                    if keywords_fullmatch:
                        keywords_having_count = len(keywords)
                    else:
                        keywords_having_count = 1
                    query = PortraitKeywordUserHistory.objects.filter(filter_qsl).values('user', 'action', 'keyword').distinct().query
                    users = users.filter(
                        id__in=models.expressions.RawSQL(f"""
                            SELECT user_id
                            FROM ({ query }) t
                            GROUP BY user_id having count(*) >= %s
                        """, (keywords_having_count, ))
                    )
                users = users.order_by('-point_stat__experience')
                total_num = users.count()
                users = users[(cur_page - 1) * per_page:cur_page * per_page]
                user_ids = list(users.values_list('id', flat=True))

                profile_users = UserProfileCollections.objects.filter(
                    is_primary=True,
                    user_id__in=user_ids
                ).values('user_id', 'name', 'company', 'phone', 'email')
                user_profile_map = {p_u['user_id']: p_u for p_u in profile_users}

                data = list()
                for u in users:
                    data.append({
                        'id': u.id,
                        'openid': u.openid,
                        'username': u.username,
                        'phone': user_profile_map.get(u.id, {'phone': u.phone})['phone'],
                        'email': user_profile_map.get(u.id, {'email': u.email})['email'],
                        'name': user_profile_map.get(u.id, {'name': u.name})['name'],
                        'company_name': user_profile_map.get(u.id, {'company': u.company_name})['company'],
                        'signup_channel': u.signup_channel,
                        'experience': u.point_stat.experience,
                    })
                
        return Response({
            'detail': _('success'),
            'data': data,
            'total_num': total_num
        })


@contextmanager
def temporary_user_table(cursor):
    table_name = f"temp_portrait_filter_{ str(uuid.uuid1()).replace('-', '_') }"
    cursor.execute(f"""
        CREATE TEMPORARY TABLE `{ table_name }` (
            id int PRIMARY KEY AUTO_INCREMENT,
            user_id int,
            code varchar(30)
        );
    """)
    yield table_name
    cursor.execute(f"""
        DROP TEMPORARY TABLE `{ table_name }`;
    """)


def get_filter_sql(code_name):
    return {
        'has_company_name': """
            SELECT distinct user_id, %s
            FROM pingcap_user_profile_collections
            WHERE is_primary = 1 and company is not null and company != '' and company != '无'
        """,
        'has_verified_email': """
            SELECT id, %s
            FROM auth_user
            WHERE email is not null and email_verified = 1
        """,
        'has_name': """
            SELECT distinct user_id, %s
            FROM pingcap_user_profile_collections
            WHERE is_primary = 1 and name is not null and name != ''
        """,
        'has_email': """
            SELECT distinct user_id, %s
            FROM pingcap_user_profile_collections
            WHERE is_primary = 1 and email is not null and email != ''
        """,
        'has_phone': """
            SELECT distinct user_id, %s
            FROM pingcap_user_profile_collections
            WHERE is_primary = 1 and phone is not null and phone != ''
        """,
        'bind_github': """
            SELECT user_id, %s
            FROM social_auth_usersocialauth
            WHERE provider = 'github'
        """,
        'company_verified': """
            SELECT id, %s
            FROM auth_user
            WHERE company_verified_status = 2
        """,
        'is_tug_member': """
            SELECT user_id, %s
            FROM pingcap_tug_memebers
        """,
        'is_contributor': """
            SELECT id, %s
            from auth_user
            where is_contributor = 1
        """,
        'passed_pcta': """
            select id, %s
            from auth_user
            where pass_pcta = 1
        """,
        'passed_pctp': """
            select id, %s
            from auth_user
            where pass_pctp = 1
        """,
        'passed_pcsd': """
            select id, %s
            from auth_user
            where pass_pcsd = 1
        """,
        'passed_praczh': """
            select id, %s
            from auth_user
            where pass_praczh = 1
        """,
        'active_last_30_days': """
            select id, %s
            from auth_user
            where openid in (
                select distinct openid from track_events where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= event_time
            )
        """,
        'active_last_7_days': """
            select id, %s
            from auth_user
            where openid in (
                select distinct openid from track_events where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= event_time
            )
        """,
        'has_tech_blogs': f"""
            select id, %s
            from auth_user
            where username in (
                select du.username
                from asktug_backup.topics dt left join asktug_backup.users du on du.id = dt.user_id
                where
                    dt.archetype = 'regular' and dt.deleted_at is null and
                    dt.visible = 1 and dt.user_id > 0 and
                    dt.category_id in ({ ','.join(str(s) for s in BLOG_CATEGORIES) })
            )
        """,
        'has_topics': f"""
            select id, %s
            from auth_user
            where username in (
                select du.username
                from asktug_backup.topics dt left join asktug_backup.users du on du.id = dt.user_id
                where
                    dt.archetype = 'regular' and dt.deleted_at is null and
                    dt.visible = 1 and dt.user_id > 0 and
                    dt.category_id not in ({ ','.join(str(s) for s in BLOG_CATEGORIES) })
            )
        """,
        'has_posts': """
            select id, %s
            from auth_user
            where username in (
                select du.username
                from asktug_backup.posts dp
                    left join asktug_backup.users du on du.id = dp.user_id
                    left join asktug_backup.topics dt on dt.id = dp.topic_id
                where
                    dt.archetype = 'regular' and dt.deleted_at is null and
                    dt.visible = 1 and dt.user_id > 0 and
                    dp.post_type = 1 and dp.deleted_at is null and dp.hidden = 0 and
                    dp.user_id != dt.user_id
            )
        """,
        'has_solved_topics': """
            select id, %s
            from auth_user
            where username in (
                select du.username
                from asktug_backup.user_actions dua left join asktug_backup.users du on du.id = dua.user_id
                where dua.action_type = 15
            )
        """,
        'click_tidb_cloud_url': """
            select id, %s
            from auth_user
            where username in (
                select du.username
                from asktug_backup.topic_link_clicks tlc left join asktug_backup.users du on du.id = tlc.user_id
                    left join asktug_backup.topic_links tl on tlc.topic_link_id = tl.id
                where tl.url like '%%tidbcloud%%' and tlc.user_id is not null
            ) or openid in (
                select distinct user_id
                from ga_events
                where
                    user_id is not null and
                    event_params like '%%https://tidbcloud.com%%'
            )
        """,
        'use_tidb_v4': """
            select distinct user_id, %s
            from pingcap_user_tidb_usage
            where version like '4.%%'
        """,
        'use_tidb_v5': """
            select distinct user_id, %s
            from pingcap_user_tidb_usage
            where version like '5.%%'
        """,
        'use_tidb_v6': """
            select distinct user_id, %s
            from pingcap_user_tidb_usage
            where version like '6.%%'
        """,
        'use_tidb_v7': """
            select distinct user_id, %s
            from pingcap_user_tidb_usage
            where version like '7.%%'
        """,
        'use_tidb_v8': """
            select distinct user_id, %s
            from pingcap_user_tidb_usage
            where version like '8.%%'
        """,
        'contact_us_official': """
            select distinct user_id, %s
            from pingcap_official_contact_us
        """,
        'visit_aliyun_landing_page': """
            select id, %s
            from auth_user
            where openid in (
                select distinct openid
                from track_events
                where
                    openid is not null and
                    event_type = 'site.pingcap#com.pageview' and
                    pure_url like 'https://pingcap.com/zh/events/tidb-on-aliyun%%'
            )
        """,
        'outbound_click_to_aliyun': """
            select id, %s
            from auth_user
            where openid in (
                select distinct user_id
                from ga_events
                where
                    user_id is not null and
                    event_params like '%%https://market.aliyun.com/products/56024006/cmgj00054143.html%%'
            )
        """,
        'apac_user': """
            select user_id, %s
            from pingcap_user_info_misc
            where JSON_EXTRACT(all_ip_top3_region, '$[0].country') in ('中国台湾', '中国澳门', '中国香港', 'India', 'Viet Nam', 'Singapore', 'Thailand', 'Malaysia', 'Indonesia', 'Philippines', 'Australia', "Korea (Democratic People's Repub", 'Cambodia')
        """
    }.get(code_name)