import logging
from datetime import datetime

from django.conf import settings
from django.db import connections
from django.db.utils import DatabaseError
from rest_framework import status
from rest_framework.permissions import IsAdminUser
from rest_framework.response import Response

from pingcap_sso.api.base import AdminEndpoint


logger = logging.getLogger(__name__)


class CloudUsersBaseEndpoint(AdminEndpoint):
    """Read-only access to the isolated cloud operations analytics database."""

    permission_classes = (IsAdminUser,)
    database_alias = 'cloud_ops_analytics'
    org_business_scope_sql = '''
        COALESCE(commercial_segment, '') <> 'internal'
        AND LOWER(COALESCE(current_plan, '')) <> 'poc'
    '''
    user_business_scope_sql = '''
        NOT EXISTS (
            SELECT 1
            FROM dws_org_ops_daily scope_org
            WHERE scope_org.snapshot_date = dws_user_ops_daily.snapshot_date
              AND scope_org.org_id = dws_user_ops_daily.org_id
              AND (
                  COALESCE(scope_org.commercial_segment, '') = 'internal'
                  OR LOWER(COALESCE(scope_org.current_plan, '')) = 'poc'
              )
        )
    '''

    def get_connection(self):
        if self.database_alias not in settings.DATABASES:
            return None
        return connections[self.database_alias]

    def resolve_snapshot_date(self, request, connection):
        requested_date = request.GET.get('snapshot_date', '').strip()
        if requested_date:
            try:
                datetime.strptime(requested_date, '%Y-%m-%d')
            except ValueError:
                return None
            return requested_date

        with connection.cursor() as cursor:
            cursor.execute('SELECT MAX(snapshot_date) FROM dws_user_ops_daily')
            result = cursor.fetchone()[0]
        return result.isoformat() if result else None

    @staticmethod
    def rows_as_dicts(cursor):
        columns = [column[0] for column in cursor.description]
        return [dict(zip(columns, row)) for row in cursor.fetchall()]

    def analytics_unavailable_response(self):
        return Response({
            'detail': 'Cloud operations analytics database is not configured.'
        }, status=status.HTTP_503_SERVICE_UNAVAILABLE)


class SummaryEndpoint(CloudUsersBaseEndpoint):

    def get(self, request, *args, **kwargs):
        """audit_log: 查看了云上用户运营概览"""
        connection = self.get_connection()
        if not connection:
            return self.analytics_unavailable_response()

        try:
            snapshot_date = self.resolve_snapshot_date(request, connection)
            if not snapshot_date:
                return Response({'detail': 'No cloud user snapshot is available.'}, status=404)

            with connection.cursor() as cursor:
                cursor.execute('''
                    SELECT
                        COUNT(*) AS org_count,
                        COALESCE(SUM(user_count), 0) AS user_count,
                        COALESCE(SUM(CASE WHEN {business_scope} THEN cluster_count_total ELSE 0 END), 0) AS cluster_count,
                        COALESCE(SUM(CASE WHEN {business_scope} THEN active_cluster_count ELSE 0 END), 0) AS active_cluster_count,
                        COALESCE(SUM(CASE WHEN {business_scope} THEN deleted_cluster_count ELSE 0 END), 0) AS deleted_cluster_count,
                        COALESCE(SUM(CASE WHEN {business_scope} THEN ru_active_cluster_count ELSE 0 END), 0) AS ru_active_cluster_count,
                        COALESCE(SUM(is_public_cloud_bound), 0) AS public_cloud_bound_org_count,
                        COALESCE(SUM(is_billable), 0) AS billable_org_count,
                        COALESCE(SUM(has_generated_cost), 0) AS costed_org_count,
                        COALESCE(SUM(commercial_segment = 'high_usage_uncommercialized'), 0) AS high_usage_uncommercialized_org_count,
                        COALESCE(SUM(commercial_segment = 'commercial_active'), 0) AS commercial_active_org_count,
                        COALESCE(SUM(active_package_count > 0), 0) AS package_org_count
                    FROM dws_org_ops_daily
                    WHERE snapshot_date = %s
                '''.format(business_scope=self.org_business_scope_sql), [snapshot_date])
                summary = self.rows_as_dicts(cursor)[0]
                cursor.execute('''
                    SELECT
                        COUNT(*) AS user_count,
                        COALESCE(SUM({business_scope}), 0) AS business_scope_user_count,
                        COALESCE(SUM({business_scope} AND active_cluster_count > 0), 0) AS users_with_active_clusters,
                        COALESCE(SUM({business_scope} AND cluster_count_total > 0 AND active_cluster_count = 0), 0) AS users_with_clusters_idle,
                        COALESCE(SUM({business_scope} AND cluster_count_total = 0), 0) AS users_without_clusters,
                        COALESCE(SUM(is_public_cloud_bound), 0) AS public_cloud_bound_users,
                        COALESCE(SUM(is_billable), 0) AS billable_users,
                        COALESCE(SUM({business_scope} AND active_cluster_count > 0 AND is_billable = 0), 0) AS active_without_billable_users,
                        COALESCE(SUM({business_scope} AND active_cluster_count = 0), 0) AS users_without_active_clusters,
                        COALESCE(SUM({business_scope} AND product_modes LIKE '%%Starter%%'), 0) AS starter_user_count,
                        COALESCE(SUM({business_scope} AND product_modes LIKE '%%Essential%%'), 0) AS essential_user_count,
                        COALESCE(SUM({business_scope} AND create_cluster_action_count > 0), 0) AS cluster_creator_user_count,
                        COALESCE(SUM(survey_completed), 0) AS surveyed_user_count,
                        COALESCE(SUM(sql_execute_count_30d > 0), 0) AS sql_active_user_count_30d,
                        COALESCE(SUM(activation_stage = 'product_active'), 0) AS product_active_user_count
                    FROM dws_user_ops_daily
                    WHERE snapshot_date = %s
                '''.format(business_scope=self.user_business_scope_sql), [snapshot_date])
                summary.update(self.rows_as_dicts(cursor)[0])
        except DatabaseError:
            logger.exception('Could not load cloud user summary.')
            return Response({'detail': 'Cloud operations analytics is unavailable.'}, status=503)

        summary['snapshot_date'] = snapshot_date
        return Response({'detail': 'success', 'data': summary})


class ListEndpoint(CloudUsersBaseEndpoint):

    sortable_fields = {
        'registered_at': 'registered_at',
        'cluster_count_total': 'cluster_count_total',
        'active_cluster_count': 'active_cluster_count',
        'ru_active_cluster_count': 'ru_active_cluster_count',
        'ru_used_total_30d': 'ru_used_total_30d',
        'storage_used_total_30d': 'storage_used_total_30d',
        'last_personal_activity_at': 'last_personal_activity_at',
        'create_cluster_action_count': 'create_cluster_action_count',
        'sql_execute_count_30d': 'sql_execute_count_30d',
        'current_month_cost': 'current_month_cost',
    }

    def get(self, request, *args, **kwargs):
        """audit_log: 查看了云上用户列表"""
        connection = self.get_connection()
        if not connection:
            return self.analytics_unavailable_response()

        try:
            current_page = max(int(request.GET.get('currentPage', 1)), 1)
            max_per_page = 10000 if request.GET.get('export') == '1' else 100
            per_page = min(max(int(request.GET.get('perPage', 20)), 1), max_per_page)
        except (TypeError, ValueError):
            return Response({'detail': 'wrong params'}, status=400)

        sort_by = self.sortable_fields.get(
            request.GET.get('sortBy', ''), 'registered_at')
        sort_desc = request.GET.get('sortDesc', 'true') != 'false'
        order_by = '{} {}'.format(sort_by, 'DESC' if sort_desc else 'ASC')

        try:
            snapshot_date = self.resolve_snapshot_date(request, connection)
            if not snapshot_date:
                return Response({'detail': 'No cloud user snapshot is available.'}, status=404)

            where, params = self.build_filters(request, snapshot_date)
            where_sql = ' AND '.join(where)
            with connection.cursor() as cursor:
                cursor.execute(
                    'SELECT COUNT(*) FROM dws_user_ops_daily WHERE {}'.format(where_sql),
                    params)
                total_num = cursor.fetchone()[0]

                cursor.execute('''
                    SELECT *
                    FROM dws_user_ops_daily
                    WHERE {}
                    ORDER BY {}
                    LIMIT %s OFFSET %s
                '''.format(where_sql, order_by), params + [per_page, (current_page - 1) * per_page])
                rows = self.rows_as_dicts(cursor)
        except DatabaseError:
            logger.exception('Could not load cloud users.')
            return Response({'detail': 'Cloud operations analytics is unavailable.'}, status=503)

        return Response({
            'detail': 'success',
            'data': rows,
            'total_num': total_num,
            'per_page': per_page,
            'cur_page': current_page,
            'snapshot_date': snapshot_date,
        })

    @staticmethod
    def build_filters(request, snapshot_date):
        where = ['snapshot_date = %s']
        params = [snapshot_date]
        keyword = request.GET.get('keyword', '').strip()
        if keyword:
            pattern = '%{}%'.format(keyword)
            where.append('''(
                email LIKE %s OR first_name LIKE %s OR last_name LIKE %s
                OR phone_number LIKE %s OR CAST(org_id AS CHAR) LIKE %s
            )''')
            params.extend([pattern, pattern, pattern, pattern, pattern])

        provider = request.GET.get('provider', '').strip()
        if provider:
            where.append('cluster_cloud_providers LIKE %s')
            params.append('%{}%'.format(provider))

        service_plan = request.GET.get('service_plan', '').strip()
        if service_plan:
            where.append('product_modes LIKE %s')
            params.append('%{}%'.format(service_plan))

        for query_name, column_name in (
            ('is_bound', 'is_public_cloud_bound'),
            ('is_billable', 'is_billable'),
        ):
            value = request.GET.get(query_name, '')
            if value in ('0', '1'):
                where.append('{} = %s'.format(column_name))
                params.append(int(value))

        has_cluster = request.GET.get('has_cluster', '')
        if has_cluster == '1':
            where.append('active_cluster_count > 0')
        elif has_cluster == '0':
            where.append('active_cluster_count = 0')

        org_id = request.GET.get('org_id', '').strip()
        if org_id:
            where.append('org_id = %s')
            params.append(int(org_id))

        signup_source = request.GET.get('signup_source', '').strip()
        if signup_source:
            where.append('signup_source = %s')
            params.append(signup_source)

        login_active = request.GET.get('login_active', '')
        if login_active in ('0', '1'):
            where.append('is_active_30d = %s')
            params.append(int(login_active))

        if request.GET.get('business_scope', '') == '1':
            where.append(CloudUsersBaseEndpoint.user_business_scope_sql)
        return where, params


class DetailEndpoint(CloudUsersBaseEndpoint):

    def get(self, request, user_id, *args, **kwargs):
        """audit_log: 查看了云上用户详情"""
        connection = self.get_connection()
        if not connection:
            return self.analytics_unavailable_response()

        try:
            snapshot_date = self.resolve_snapshot_date(request, connection)
            if not snapshot_date:
                return Response({'detail': 'No cloud user snapshot is available.'}, status=404)
            with connection.cursor() as cursor:
                cursor.execute('''
                    SELECT *
                    FROM dws_user_ops_daily
                    WHERE snapshot_date = %s AND user_id = %s
                    LIMIT 1
                ''', [snapshot_date, user_id])
                rows = self.rows_as_dicts(cursor)
        except DatabaseError:
            logger.exception('Could not load cloud user detail.')
            return Response({'detail': 'Cloud operations analytics is unavailable.'}, status=503)

        if not rows:
            return Response({'detail': 'Cloud user not found.'}, status=404)
        data = rows[0]
        data['snapshot_date'] = snapshot_date
        return Response({'detail': 'success', 'data': data})