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 CloudOrgsBaseEndpoint(AdminEndpoint):
    """Read-only access to the cloud operations analytics database（Org 层）。"""

    permission_classes = (IsAdminUser,)
    database_alias = 'cloud_ops_analytics'

    ORG_COLUMNS = """
        org_id, org_name, org_status, org_type, org_country, account_manager, org_tag,
        user_count, project_count,
        cluster_count_total, active_cluster_count, ru_active_cluster_count,
        ru_used_total_7d, ru_used_total_30d, last_ru_used_at,
        cost_used_total_7d, cost_used_total_30d, current_month_discount, last_cost_at,
        storage_used_total_7d, storage_used_total_30d, last_storage_used_at,
        is_public_cloud_bound, is_billable, payment_types, current_plan, plan_status,
        current_month_cost, has_generated_cost, last_invoice_status,
        active_user_count_7d, active_user_count_30d,
        product_modes, cluster_regions, cluster_cloud_providers,
        third_party_account_provider, active_package_count, invoice_payable_package_count,
        package_expired_at, remaining_credits, active_discount_count,
        poc_application_count, last_poc_at, commercial_segment, commercial_reasons
    """

    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_org_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 OrgListEndpoint(CloudOrgsBaseEndpoint):

    sortable_fields = {
        'org_id': 'org_id',
        'user_count': 'user_count',
        'cluster_count_total': 'cluster_count_total',
        'active_cluster_count': 'active_cluster_count',
        'ru_used_total_30d': 'ru_used_total_30d',
        'current_month_cost': 'current_month_cost',
    }

    def get(self, request, *args, **kwargs):
        """audit_log: 查看了云上 Org 分析列表"""
        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', ''), 'ru_used_total_30d')
        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 org 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_org_ops_daily WHERE {}'.format(where_sql),
                    params)
                total_num = cursor.fetchone()[0]

                cursor.execute('''
                    SELECT {}
                    FROM dws_org_ops_daily
                    WHERE {}
                    ORDER BY {}
                    LIMIT %s OFFSET %s
                '''.format(self.ORG_COLUMNS, 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 orgs.')
            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('org_name LIKE %s')
            params.append(pattern)

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

        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))

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

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

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

        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')
        return where, params


class OrgDetailEndpoint(CloudOrgsBaseEndpoint):

    def get(self, request, org_id, *args, **kwargs):
        """audit_log: 查看了云上 Org 详情"""
        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 org snapshot is available.'}, status=404)
            with connection.cursor() as cursor:
                cursor.execute('''
                    SELECT {}
                    FROM dws_org_ops_daily
                    WHERE snapshot_date = %s AND org_id = %s
                    LIMIT 1
                '''.format(self.ORG_COLUMNS), [snapshot_date, org_id])
                rows = self.rows_as_dicts(cursor)
        except DatabaseError:
            logger.exception('Could not load cloud org detail.')
            return Response({'detail': 'Cloud operations analytics is unavailable.'}, status=503)

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


class OrgClustersEndpoint(CloudOrgsBaseEndpoint):

    def get(self, request, org_id, *args, **kwargs):
        """audit_log: 查看了云上 Org 的集群维度用量"""
        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 org snapshot is available.'}, status=404)
            with connection.cursor() as cursor:
                cursor.execute('''
                    SELECT
                        cluster_id,
                        service_plan,
                        COALESCE(SUM(CASE WHEN usage_date > DATE_SUB(snapshot_date, INTERVAL 7 DAY)
                                        THEN ru_used END), 0) AS ru_used_7d,
                        COALESCE(SUM(CASE WHEN usage_date > DATE_SUB(snapshot_date, INTERVAL 30 DAY)
                                        THEN ru_used END), 0) AS ru_used_30d,
                        COUNT(DISTINCT usage_date) AS active_days,
                        MAX(usage_date) AS last_usage_at
                    FROM ods_cluster_ru_usage_di
                    WHERE snapshot_date = %s
                      AND tenant_id = %s
                    GROUP BY cluster_id, service_plan
                    ORDER BY ru_used_30d DESC
                    LIMIT 300
                ''', [snapshot_date, org_id])
                rows = self.rows_as_dicts(cursor)
        except DatabaseError:
            logger.exception('Could not load cloud org clusters.')
            return Response({'detail': 'Cloud operations analytics is unavailable.'}, status=503)

        return Response({'detail': 'success', 'data': rows, 'snapshot_date': snapshot_date})


class OrgCostTrendsEndpoint(CloudOrgsBaseEndpoint):

    def get(self, request, org_id, *args, **kwargs):
        """audit_log: 查看了云上 Org 的成本趋势"""
        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 org snapshot is available.'}, status=404)
            with connection.cursor() as cursor:
                cursor.execute('''
                    SELECT cost_date, ROUND(SUM(cost_price), 4) AS cost_price
                    FROM ods_org_cost_daily_di
                    WHERE snapshot_date = %s
                      AND org_id = %s
                      AND cost_date > DATE_SUB(snapshot_date, INTERVAL 30 DAY)
                    GROUP BY cost_date
                    ORDER BY cost_date
                ''', [snapshot_date, org_id])
                rows = self.rows_as_dicts(cursor)
        except DatabaseError:
            logger.exception('Could not load cloud org cost trends.')
            return Response({'detail': 'Cloud operations analytics is unavailable.'}, status=503)

        return Response({'detail': 'success', 'data': rows, 'snapshot_date': snapshot_date})