"""
Analytics API Routes
Handles all data aggregation for dashboards and financial reporting
"""
from flask import Blueprint, request, jsonify
from datetime import datetime, timedelta
from config import db
from utils.decorators import token_required

analytics_bp = Blueprint('analytics', __name__, url_prefix='/api')


def _safe_float(val):
    """Safely convert a DB result to float."""
    try:
        return float(val) if val else 0.0
    except:
        return 0.0


def _calc_growth_pct(current, previous):
    """Calculate percentage growth between two values."""
    if not previous or previous == 0:
        return 100.0 if current and current > 0 else 0.0
    return round(((current - previous) / previous) * 100, 1)


# ==========================================
# DASHBOARD STATS
# ==========================================
@analytics_bp.route('/dashboard/stats', methods=['GET', 'POST'])
@token_required
def get_dashboard_stats(current_user, user_role, institute_id=None, instructor_id=None):
    """Get high-level stats for the dashboard"""
    try:
        institute_filter = f"AND institute_id = {institute_id}" if institute_id else ""
        now = datetime.utcnow()
        current_month = now.month
        current_year = now.year

        # Previous month calc
        prev_date = now - timedelta(days=30)
        prev_month = prev_date.month
        prev_year = prev_date.year

        # Total counts
        students_query = f"SELECT COUNT(*) FROM user WHERE user_type = 'student' {institute_filter}"
        total_students = db.session.execute(db.text(students_query)).scalar() or 0

        instructors_query = f"SELECT COUNT(*) FROM user WHERE user_type = 'instructor' {institute_filter}"
        total_instructors = db.session.execute(db.text(instructors_query)).scalar() or 0

        courses_query = f"SELECT COUNT(*) FROM courses WHERE is_published = 1 {institute_filter}"
        active_courses = db.session.execute(db.text(courses_query)).scalar() or 0

        revenue_query = f"SELECT COALESCE(SUM(amount), 0) FROM enrollments WHERE payment_status IN ('paid', 'completed') {institute_filter}"
        total_revenue = db.session.execute(db.text(revenue_query)).scalar() or 0

        # Monthly revenue (current month)
        monthly_rev_query = f"""SELECT COALESCE(SUM(amount), 0) FROM enrollments 
            WHERE payment_status IN ('paid', 'completed')
            AND MONTH(enrolled_at) = {current_month} AND YEAR(enrolled_at) = {current_year} {institute_filter}"""
        monthly_revenue = db.session.execute(db.text(monthly_rev_query)).scalar() or 0

        # Real growth: students this month vs last month
        students_this_month = db.session.execute(db.text(
            f"SELECT COUNT(*) FROM user WHERE user_type = 'student' AND MONTH(created_at) = {current_month} AND YEAR(created_at) = {current_year} {institute_filter}"
        )).scalar() or 0
        students_last_month = db.session.execute(db.text(
            f"SELECT COUNT(*) FROM user WHERE user_type = 'student' AND MONTH(created_at) = {prev_month} AND YEAR(created_at) = {prev_year} {institute_filter}"
        )).scalar() or 0
        student_growth = _calc_growth_pct(students_this_month, students_last_month)

        # Real growth: courses this month vs last month
        courses_this_month = db.session.execute(db.text(
            f"SELECT COUNT(*) FROM courses WHERE is_published = 1 AND MONTH(created_at) = {current_month} AND YEAR(created_at) = {current_year} {institute_filter}"
        )).scalar() or 0
        courses_last_month = db.session.execute(db.text(
            f"SELECT COUNT(*) FROM courses WHERE is_published = 1 AND MONTH(created_at) = {prev_month} AND YEAR(created_at) = {prev_year} {institute_filter}"
        )).scalar() or 0
        course_growth = _calc_growth_pct(courses_this_month, courses_last_month)

        # Real growth: instructors this month vs last month
        instr_this_month = db.session.execute(db.text(
            f"SELECT COUNT(*) FROM user WHERE user_type = 'instructor' AND MONTH(created_at) = {current_month} AND YEAR(created_at) = {current_year} {institute_filter}"
        )).scalar() or 0
        instr_last_month = db.session.execute(db.text(
            f"SELECT COUNT(*) FROM user WHERE user_type = 'instructor' AND MONTH(created_at) = {prev_month} AND YEAR(created_at) = {prev_year} {institute_filter}"
        )).scalar() or 0
        instructor_growth = _calc_growth_pct(instr_this_month, instr_last_month)

        # Real growth: revenue this month vs last month
        prev_rev = db.session.execute(db.text(
            f"""SELECT COALESCE(SUM(amount), 0) FROM enrollments 
                WHERE payment_status IN ('paid', 'completed')
                AND MONTH(enrolled_at) = {prev_month} AND YEAR(enrolled_at) = {prev_year} {institute_filter}"""
        )).scalar() or 0
        revenue_growth = _calc_growth_pct(monthly_revenue, prev_rev)

        stats = {
            'totalStudents': total_students,
            'totalInstructors': total_instructors,
            'activeCourses': active_courses,
            'monthlyRevenue': _safe_float(monthly_revenue),
            'studentGrowth': student_growth,
            'courseGrowth': course_growth,
            'instructorGrowth': instructor_growth,
            'revenueGrowth': revenue_growth
        }
        return jsonify({'stats': stats}), 200
    except Exception as e:
        return jsonify({'error': str(e)}), 500


# ==========================================
# DASHBOARD DETAILS (Recent Activities, Needs Attention, Revenue Chart)
# ==========================================
@analytics_bp.route('/dashboard/details', methods=['GET', 'POST'])
@token_required
def get_dashboard_details(current_user, user_role, institute_id=None, instructor_id=None):
    """Get detailed dashboard widgets: recent activities, needs attention counts, revenue chart data"""
    try:
        institute_filter = f"AND institute_id = {institute_id}" if institute_id else ""
        now = datetime.utcnow()

        # ---- Recent Activities (real data from enrollments, reviews, live classes) ----
        recent_activities = []

        # Recent enrollments
        try:
            enr_query = f"""
                SELECT u.fullname as user_name, c.title as course_title, e.enrolled_at
                FROM enrollments e
                LEFT JOIN user u ON e.user_id = u.id
                LEFT JOIN courses c ON e.course_id = c.id
                WHERE 1=1 {institute_filter.replace('institute_id', 'e.institute_id')}
                ORDER BY e.enrolled_at DESC LIMIT 3
            """
            enr_rows = db.session.execute(db.text(enr_query))
            for row in enr_rows.mappings():
                time_ago = ''
                if row['enrolled_at']:
                    diff = now - row['enrolled_at']
                    if diff.days > 0:
                        time_ago = f"{diff.days}d ago"
                    elif diff.seconds > 3600:
                        time_ago = f"{diff.seconds // 3600}h ago"
                    else:
                        time_ago = f"{max(1, diff.seconds // 60)}m ago"
                recent_activities.append({
                    'type': 'enrollment',
                    'title': f"New enrollment",
                    'description': f'"{row["user_name"] or "A student"}" enrolled in {row["course_title"] or "a course"}',
                    'time': time_ago
                })
        except:
            pass

        # Recent reviews
        try:
            rev_query = f"""
                SELECT ir.comment, ir.rating, c.title as course_title, ir.created_at
                FROM institute_reviews ir
                LEFT JOIN courses c ON ir.course_id = c.id
                WHERE 1=1 {institute_filter.replace('institute_id', 'ir.institute_id')}
                ORDER BY ir.created_at DESC LIMIT 2
            """
            rev_rows = db.session.execute(db.text(rev_query))
            for row in rev_rows.mappings():
                time_ago = ''
                if row['created_at']:
                    diff = now - row['created_at']
                    if diff.days > 0:
                        time_ago = f"{diff.days}d ago"
                    elif diff.seconds > 3600:
                        time_ago = f"{diff.seconds // 3600}h ago"
                    else:
                        time_ago = f"{max(1, diff.seconds // 60)}m ago"
                comment_preview = (row['comment'] or '')[:60]
                recent_activities.append({
                    'type': 'review',
                    'title': 'New review received',
                    'description': f'"{comment_preview}..." for {row["course_title"] or "a course"}',
                    'time': time_ago
                })
        except:
            pass

        # Sort by most recent (approximate by time string)
        # Activities are already ordered by recency from each query

        # ---- Needs Attention (real counts) ----
        needs_attention = []

        # Pending payments
        try:
            pending_payments = db.session.execute(db.text(
                f"SELECT COUNT(*) FROM payment_submissions WHERE status = 'pending' {institute_filter}"
            )).scalar() or 0
        except:
            pending_payments = 0

        if pending_payments > 0:
            needs_attention.append({
                'title': 'Manual Payment Approval',
                'subtitle': f'{pending_payments} pending verification{"s" if pending_payments != 1 else ""}',
                'link': '/admin/institute/payments',
                'action': 'Review'
            })

        # Unanswered doubts
        try:
            unanswered_doubts = db.session.execute(db.text(
                f"SELECT COUNT(*) FROM qa_questions WHERE status = 'pending' {institute_filter}"
            )).scalar() or 0
        except:
            unanswered_doubts = 0

        if unanswered_doubts > 0:
            needs_attention.append({
                'title': 'Student Doubts',
                'subtitle': f'{unanswered_doubts} unanswered question{"s" if unanswered_doubts != 1 else ""}',
                'link': '/admin/doubts',
                'action': 'Answer'
            })

        # Pending reviews
        try:
            pending_reviews = db.session.execute(db.text(
                f"SELECT COUNT(*) FROM institute_reviews WHERE status = 'pending' {institute_filter}"
            )).scalar() or 0
        except:
            pending_reviews = 0

        if pending_reviews > 0:
            needs_attention.append({
                'title': 'Course Reviews',
                'subtitle': f'{pending_reviews} pending moderation',
                'link': '/admin/institute/reviews',
                'action': 'Moderate'
            })

        total_tasks = len(needs_attention)

        # ---- Revenue Chart (last 6 months) ----
        revenue_chart = []
        for i in range(5, -1, -1):
            month_date = now - timedelta(days=30 * i)
            month_name = month_date.strftime('%b')
            try:
                rev = db.session.execute(db.text(
                    f"""SELECT COALESCE(SUM(amount), 0) FROM enrollments 
                        WHERE payment_status IN ('paid', 'completed')
                        AND MONTH(enrolled_at) = {month_date.month} AND YEAR(enrolled_at) = {month_date.year} {institute_filter}"""
                )).scalar() or 0
            except:
                rev = 0
            revenue_chart.append({'name': month_name, 'revenue': _safe_float(rev)})

        return jsonify({
            'recentActivities': recent_activities,
            'needsAttention': needs_attention,
            'totalTasks': total_tasks,
            'revenueChart': revenue_chart
        }), 200
    except Exception as e:
        return jsonify({'error': str(e)}), 500


# ==========================================
# REVENUE REPORTS STATS
# ==========================================
@analytics_bp.route('/analytics/revenue', methods=['POST'])
@token_required
def get_revenue_analytics(current_user, user_role, institute_id=None, instructor_id=None):
    """Get financial stats and recent transactions"""
    try:
        institute_filter = f"AND institute_id = {institute_id}" if institute_id else ""
        now = datetime.utcnow()
        current_month = now.month
        current_year = now.year
        prev_date = now - timedelta(days=30)
        prev_month = prev_date.month
        prev_year = prev_date.year

        revenue_query = f"SELECT COALESCE(SUM(amount), 0) FROM enrollments WHERE payment_status IN ('paid', 'completed') {institute_filter}"
        total_revenue = db.session.execute(db.text(revenue_query)).scalar() or 0

        monthly_revenue_query = f"""SELECT COALESCE(SUM(amount), 0) FROM enrollments 
            WHERE payment_status IN ('paid', 'completed') 
            AND MONTH(enrolled_at) = {current_month} AND YEAR(enrolled_at) = {current_year} {institute_filter}"""
        monthly_revenue = db.session.execute(db.text(monthly_revenue_query)).scalar() or 0

        prev_monthly_revenue = db.session.execute(db.text(
            f"""SELECT COALESCE(SUM(amount), 0) FROM enrollments 
                WHERE payment_status IN ('paid', 'completed')
                AND MONTH(enrolled_at) = {prev_month} AND YEAR(enrolled_at) = {prev_year} {institute_filter}"""
        )).scalar() or 0

        try:
            subs_query = f"SELECT COUNT(*) FROM user_subscriptions WHERE status = 'active' {institute_filter}"
            active_subs = db.session.execute(db.text(subs_query)).scalar() or 0
        except:
            active_subs = 0

        # Previous month subscriptions for growth
        try:
            prev_subs = db.session.execute(db.text(
                f"""SELECT COUNT(*) FROM user_subscriptions WHERE status = 'active' 
                    AND MONTH(created_at) <= {prev_month} AND YEAR(created_at) <= {prev_year} {institute_filter}"""
            )).scalar() or 0
        except:
            prev_subs = 0

        total_enrollments_query = f"SELECT COUNT(*) FROM enrollments WHERE 1=1 {institute_filter}"
        total_enrollments = db.session.execute(db.text(total_enrollments_query)).scalar() or 0

        avg_order = (float(total_revenue) / total_enrollments) if total_enrollments > 0 else 0

        # Growth calculations
        revenue_growth_pct = _calc_growth_pct(monthly_revenue, prev_monthly_revenue)
        monthly_growth_pct = _calc_growth_pct(monthly_revenue, prev_monthly_revenue)
        sub_growth_net = active_subs - prev_subs

        stats = {
            'totalRevenue': _safe_float(total_revenue),
            'monthlyRevenue': _safe_float(monthly_revenue),
            'activeSubscriptions': active_subs,
            'totalEnrollments': total_enrollments,
            'averageOrderValue': round(_safe_float(avg_order), 2),
            'revenueGrowthPct': revenue_growth_pct,
            'monthlyGrowthPct': monthly_growth_pct,
            'subGrowthNet': sub_growth_net
        }

        # Revenue Over Time chart data (last 6 months)
        revenue_over_time = []
        for i in range(5, -1, -1):
            month_date = now - timedelta(days=30 * i)
            month_name = month_date.strftime('%b')
            try:
                rev = db.session.execute(db.text(
                    f"""SELECT COALESCE(SUM(amount), 0) FROM enrollments 
                        WHERE payment_status IN ('paid', 'completed')
                        AND MONTH(enrolled_at) = {month_date.month} AND YEAR(enrolled_at) = {month_date.year} {institute_filter}"""
                )).scalar() or 0
            except:
                rev = 0
            revenue_over_time.append({'name': month_name, 'revenue': _safe_float(rev)})

        # Revenue by Source
        course_revenue = _safe_float(total_revenue)
        try:
            sub_revenue = db.session.execute(db.text(
                f"SELECT COALESCE(SUM(amount), 0) FROM user_subscriptions WHERE status = 'active' {institute_filter}"
            )).scalar() or 0
            sub_revenue = _safe_float(sub_revenue)
        except:
            sub_revenue = 0

        try:
            token_revenue = db.session.execute(db.text(
                "SELECT COALESCE(SUM(amount), 0) FROM ai_token_transactions WHERE transaction_type = 'purchase' AND status = 'completed'"
            )).scalar() or 0
            token_revenue = _safe_float(token_revenue)
        except:
            token_revenue = 0

        revenue_by_source = [
            {'name': 'Courses', 'value': course_revenue, 'color': '#10b981'},
            {'name': 'Subscriptions', 'value': sub_revenue, 'color': '#8b5cf6'},
            {'name': 'AI Tokens', 'value': token_revenue, 'color': '#f59e0b'}
        ]
        # Filter out zero sources
        revenue_by_source = [s for s in revenue_by_source if s['value'] > 0]
        if not revenue_by_source:
            revenue_by_source = [{'name': 'No Revenue', 'value': 1, 'color': '#6b7280'}]

        # Recent transactions
        transactions = []
        try:
            tx_query = f"""
                SELECT e.id, u.fullname as user, e.amount, 'Course Purchase' as type, e.payment_status as status, e.enrolled_at as date
                FROM enrollments e
                LEFT JOIN user u ON e.user_id = u.id
                WHERE 1=1 {institute_filter.replace('institute_id', 'e.institute_id')}
                ORDER BY e.enrolled_at DESC
                LIMIT 10
            """
            tx_results = db.session.execute(db.text(tx_query))

            for row in tx_results.mappings():
                transactions.append({
                    'id': row['id'],
                    'user': row['user'] or 'Unknown',
                    'amount': _safe_float(row['amount']),
                    'type': row['type'],
                    'status': 'Completed' if row['status'] in ('paid', 'completed') else 'Pending',
                    'date': row['date'].strftime('%Y-%m-%d %I:%M %p') if row['date'] else ''
                })
        except:
            pass

        return jsonify({
            'stats': stats,
            'recentTransactions': transactions,
            'revenueOverTime': revenue_over_time,
            'revenueBySource': revenue_by_source
        }), 200
    except Exception as e:
        return jsonify({'error': str(e)}), 500


# ==========================================
# INSTITUTE ENROLLMENTS ANALYTICS
# ==========================================
@analytics_bp.route('/institute/analytics', methods=['POST'])
@token_required
def get_institute_full_analytics(current_user, user_role, institute_id=None, instructor_id=None):
    """Get rich analytics for the institute dashboard"""
    try:
        institute_filter = f"AND institute_id = {institute_id}" if institute_id else ""

        total_enrollments = db.session.execute(db.text(f"SELECT COUNT(*) FROM enrollments WHERE 1=1 {institute_filter}")).scalar() or 0
        total_revenue = db.session.execute(db.text(f"SELECT COALESCE(SUM(amount), 0) FROM enrollments WHERE payment_status IN ('paid', 'completed') {institute_filter}")).scalar() or 0
        active_students = db.session.execute(db.text(f"SELECT COUNT(DISTINCT user_id) FROM enrollments WHERE 1=1 {institute_filter}")).scalar() or 0

        # Real completed courses count
        try:
            completed_courses = db.session.execute(db.text(
                f"SELECT COUNT(DISTINCT course_id) FROM enrollments WHERE payment_status IN ('paid', 'completed') {institute_filter}"
            )).scalar() or 0
        except:
            completed_courses = 0

        # Real average rating from institute_reviews
        try:
            avg_rating = db.session.execute(db.text(
                f"SELECT AVG(rating) FROM institute_reviews WHERE status = 'approved' {institute_filter}"
            )).scalar()
            avg_rating = round(float(avg_rating), 1) if avg_rating else 0.0
        except:
            avg_rating = 0.0

        months = []
        now = datetime.now()
        for i in range(5, -1, -1):
            month_date = now - timedelta(days=30*i)
            month_name = month_date.strftime('%b')
            month_count = db.session.execute(db.text(
                f"SELECT COUNT(*) FROM enrollments WHERE MONTH(enrolled_at) = {month_date.month} AND YEAR(enrolled_at) = {month_date.year} {institute_filter}"
            )).scalar() or 0
            months.append({'month': month_name, 'count': month_count})

        top_courses_query = f"""
            SELECT c.title, COUNT(e.id) as enrollments, COALESCE(SUM(e.amount), 0) as revenue
            FROM courses c
            LEFT JOIN enrollments e ON c.id = e.course_id
            WHERE 1=1 {institute_filter.replace('institute_id', 'c.institute_id')}
            GROUP BY c.id, c.title
            ORDER BY enrollments DESC
            LIMIT 5
        """
        top_courses_results = db.session.execute(db.text(top_courses_query))
        top_courses = []
        for row in top_courses_results.mappings():
            top_courses.append({
                'title': row['title'],
                'enrollments': row['enrollments'] or 0,
                'revenue': _safe_float(row['revenue'])
            })

        # Real recent activity from enrollments
        recent_activity = []
        try:
            act_query = f"""
                SELECT 'enrollment' as type, CONCAT(u.fullname, ' enrolled in ', c.title) as message, e.enrolled_at as time
                FROM enrollments e
                LEFT JOIN user u ON e.user_id = u.id
                LEFT JOIN courses c ON e.course_id = c.id
                WHERE 1=1 {institute_filter.replace('institute_id', 'e.institute_id')}
                ORDER BY e.enrolled_at DESC LIMIT 5
            """
            act_rows = db.session.execute(db.text(act_query))
            for row in act_rows.mappings():
                time_str = row['time'].strftime('%b %d, %I:%M %p') if row['time'] else ''
                recent_activity.append({
                    'type': row['type'],
                    'message': row['message'] or 'Recent activity',
                    'time': time_str
                })
        except:
            pass

        analytics = {
            'totalEnrollments': total_enrollments,
            'totalRevenue': _safe_float(total_revenue),
            'activeStudents': active_students,
            'completedCourses': completed_courses,
            'averageRating': avg_rating,
            'monthlyEnrollments': months,
            'topCourses': top_courses,
            'recentActivity': recent_activity
        }
        return jsonify({'analytics': analytics}), 200
    except Exception as e:
        return jsonify({'error': str(e)}), 500


# ==========================================
# SUPERAPP AI STATS (Real data from ai_token_transactions)
# ==========================================
@analytics_bp.route('/live-ai/stats', methods=['GET', 'POST'])
@token_required
def get_superapp_ai_stats(current_user, user_role, institute_id=None, instructor_id=None):
    """Get AI metrics for the Superapp dashboard — real data from ai_token_transactions"""
    try:
        # Total AI consumption sessions
        try:
            total_sessions = db.session.execute(db.text(
                "SELECT COUNT(*) FROM ai_token_transactions WHERE transaction_type = 'consumption'"
            )).scalar() or 0
        except:
            total_sessions = 0

        # Voice sessions — count those with 'voice' or 'audio' in description/endpoint
        try:
            voice_sessions = db.session.execute(db.text(
                "SELECT COUNT(*) FROM ai_token_transactions WHERE transaction_type = 'consumption' AND (endpoint LIKE '%voice%' OR endpoint LIKE '%audio%' OR description LIKE '%voice%')"
            )).scalar() or 0
        except:
            voice_sessions = 0

        # Total tokens consumed
        try:
            total_tokens = db.session.execute(db.text(
                "SELECT COALESCE(SUM(amount), 0) FROM ai_token_transactions WHERE transaction_type = 'consumption'"
            )).scalar() or 0
            total_tokens = _safe_float(total_tokens)
        except:
            total_tokens = 0

        # Avg tokens per session as a proxy for duration
        avg_tokens_per_session = (total_tokens / total_sessions) if total_sessions > 0 else 0
        # Rough estimate: 100 tokens ~= 1 minute of interaction
        avg_duration_estimate = round(avg_tokens_per_session / 100, 1) if avg_tokens_per_session > 0 else 0

        stats = {
            'total_sessions': total_sessions,
            'voice_sessions': voice_sessions,
            'avg_duration_minutes': avg_duration_estimate,
            'total_tokens_used': total_tokens
        }

        # Recent real sessions from ai_token_transactions
        recent_sessions = []
        try:
            rs_query = """
                SELECT att.description as title, att.created_at, att.user_id,
                       CAST(att.amount AS SIGNED) as interaction_count
                FROM ai_token_transactions att
                WHERE att.transaction_type = 'consumption'
                ORDER BY att.created_at DESC
                LIMIT 5
            """
            rs_rows = db.session.execute(db.text(rs_query))
            for row in rs_rows.mappings():
                recent_sessions.append({
                    'title': row['title'] or 'AI Session',
                    'created_at': row['created_at'].isoformat() if row['created_at'] else '',
                    'interaction_count': int(row['interaction_count']) if row['interaction_count'] else 0,
                    'user_id': row['user_id']
                })
        except:
            pass

        return jsonify({'stats': stats, 'recent_sessions': recent_sessions}), 200
    except Exception as e:
        return jsonify({'error': str(e)}), 500
