leader_board.py

from flask import Flask, jsonify, request
import mysql.connector
import logging
from models import get_db_connection
import ast
import re

def leaderboard_overall():
    connection = None
    cursor = None
    
    connection = get_db_connection()
    if connection:
        try:
            cursor = connection.cursor()
            
            
            cursor.execute("""
                SELECT username, total_score 
                FROM users 
                ORDER BY total_score DESC 
                LIMIT 5
            """)
            
            top_users = cursor.fetchall()
            
        
            logging.info("Top 5 users retrieved for overall leaderboard from users table.")
            
        
            return jsonify({
                'status': 'success',
                'leaderboard': top_users
            }), 200

        except mysql.connector.Error as err:
            logging.error("Error retrieving leaderboard: %s", err)
            return jsonify({
                'status': 'error',
                'message': 'An error occurred while fetching the leaderboard.'
            }), 500
        
        finally:
            if cursor:
                cursor.close()
            if connection:
                connection.close()
                logging.info("Database connection closed after retrieving overall leaderboard.")
    else:
        return jsonify({
            'status': 'error',
            'message': 'Database connection failed.'
        }), 500

def leaderboard_weekly():
    connection = None
    cursor = None
    connection = get_db_connection()  
    if connection:
        try:
            cursor = connection.cursor()

           
            cursor.execute("""
                SELECT u.username, SUM(t.score) AS weekly_score
                FROM quiz_response t
                JOIN users u ON t.user_id_attempt = u.id
                WHERE DATE(t.submitted_on) >= CURDATE() - INTERVAL 6 DAY  -- Last 7 days including today
                GROUP BY u.username
                ORDER BY weekly_score DESC
                LIMIT 5;
            """)

            top_users = cursor.fetchall()
            
           
            logging.info("Top 5 users retrieved for daily leaderboard.")
            
            
            return jsonify({
                'status': 'success',
                'leaderboard': top_users
            }), 200
        
        except mysql.connector.Error as err:
            logging.error("Error retrieving daily leaderboard: %s", err)
            return jsonify({
                'status': 'error',
                'message': 'An error occurred while fetching the daily leaderboard.'
            }), 500
        
        finally:
            if cursor:
                cursor.close()
            if connection:
                connection.close()
                logging.info("Database connection closed after retrieving daily leaderboard.")
    else:
        return jsonify({
            'status': 'error',
            'message': 'Database connection failed.'
        }), 500
    

def leaderboard_daily():
    connection = None
    cursor = None
    connection = get_db_connection()  
    if connection:
        try:
            cursor = connection.cursor()

           
            cursor.execute("""
                SELECT u.username, SUM(t.score) AS daily_score
                FROM quiz_response t
                JOIN users u ON t.user_id_attempt = u.id
                WHERE DATE(t.submitted_on) = CURDATE()  -- Only today's quizzes
                GROUP BY u.username
                ORDER BY daily_score DESC
                LIMIT 5
            """)

            top_users = cursor.fetchall()
            
           
            logging.info("Top 5 users retrieved for daily leaderboard.")
            
            
            return jsonify({
                'status': 'success',
                'leaderboard': top_users
            }), 200
        
        except mysql.connector.Error as err:
            logging.error("Error retrieving daily leaderboard: %s", err)
            return jsonify({
                'status': 'error',
                'message': 'An error occurred while fetching the daily leaderboard.'
            }), 500
        
        finally:
            if cursor:
                cursor.close()
            if connection:
                connection.close()
                logging.info("Database connection closed after retrieving daily leaderboard.")
    else:
        return jsonify({
            'status': 'error',
            'message': 'Database connection failed.'
        }), 500






def leaderboard_theme():
    connection = None
    cursor = None
    connection = get_db_connection()  
    
    if connection:
        try:
            cursor = connection.cursor()
            theme = request.form.get('theme') 
            theme = theme.lower()

            
            if not theme:
                return jsonify({
                    'status': 'error',
                    'message': 'Please provide a theme.'
                }), 400
            
            # Fetch top 5 users for the given theme
            cursor.execute("""
                SELECT u.username, SUM(qr.score) AS total_score
                FROM quiz_response qr
                JOIN users u ON qr.user_id_attempt = u.id
                WHERE qr.theme = %s
                GROUP BY u.username
                ORDER BY total_score DESC
                LIMIT 5
            """, (theme,))
            
            top_users = cursor.fetchall()
            
            return jsonify({
                'status': 'success',
                'leaderboard': top_users
            }), 200
            
        except mysql.connector.Error as err:
            logging.error(f"Error retrieving theme leaderboard for '{theme}': {err}")
            return jsonify({
                'status': 'error',
                'message': f'An error occurred while fetching the leaderboard for theme: {theme}.'
            }), 500
        
        finally:
            if cursor:
                cursor.close()
            if connection:
                connection.close()
                logging.info(f"Database connection closed after retrieving theme leaderboard for {theme}.")
    else:
        return jsonify({
            'status': 'error',
            'message': 'Database connection failed.'
        }), 500