import random
import string
import requests
import mysql.connector
import functools
import time
import json
from datetime import datetime, timedelta

# Simple in-memory cache
_cache = {}
_cache_ttl = {}

def cache_result(ttl_seconds=300):
    """
    Function ko return values lai cache garne decorator.
    
    Args:
        ttl_seconds: Cache ma result rakhne seconds
    """
    def decorator(func):
        @functools.wraps(func)
        def wrapper(*args, **kwargs):
            # Function name ra arguments bata cache key create garne
            key = f"{func.__name__}:{str(args)}:{str(sorted(kwargs.items()))}"
            
            # Result cache ma xa ra expire bhayeko xaina check garne
            current_time = time.time()
            if key in _cache and _cache_ttl.get(key, 0) > current_time:
                return _cache[key]
            
            # Function call garera result cache garne
            result = func(*args, **kwargs)
            _cache[key] = result
            _cache_ttl[key] = current_time + ttl_seconds
            
            return result
        return wrapper
    return decorator

def clear_cache():
    """Purai cache clear garne"""
    global _cache, _cache_ttl
    _cache = {}
    _cache_ttl = {}

def generate_username(full_name):
    """Full name bata space hatayera ra random digits add garera unique username generate garne."""
    full_name = full_name.replace(" ", "")
    random_numbers = ''.join(random.choices(string.digits, k=4))
    return full_name.lower() + random_numbers


def update_id_weblogincode(user_id, conn, token):
    try:
        cursor = conn.cursor()
        sql_update = "UPDATE `user` SET `web_login_device` = %s, `logged_in` = %s WHERE `id` = %s"
        cursor.execute(sql_update, (token,"1", user_id))
        conn.commit()
        return cursor.rowcount == 1  # Update successful bhayo bhane True return garne
    except Exception as e:
        print(f"Error updating web login code: {e}")
        return False
    finally:
        cursor.close()
    
# Common processing sanga data fetch garne base function
def _fetch_data_base(conn, query, params, process_images=True):
    """Consistent processing sanga data fetch garne base function"""
    cursor = conn.cursor(dictionary=True)
    try:
        cursor.execute(query, params)
        rows = cursor.fetchall()
        
        if process_images:
            for row in rows:
                row['imageUrls'] = []
                for img_field in ['imageUrl', 'image2', 'image3']:
                    if row.get(img_field):
                        row['imageUrls'].append(row[img_field])
        
        return rows[::-1]  # Reverse order ma return garne
    except Exception as e:
        print(f"Database query error: {e}")
        return []
    finally:
        cursor.close()

@cache_result(ttl_seconds=300)
def fetch_data(course, conn, table_name):
    query = f"SELECT * FROM {table_name} WHERE for_which=%s"
    return _fetch_data_base(conn, query, (course,))

@cache_result(ttl_seconds=300)
def fetch_datawcid(course, conn, table_name, cid):
    query = f"SELECT * FROM {table_name} WHERE for_which=%s AND course_id = %s"
    return _fetch_data_base(conn, query, (course, cid))

@cache_result(ttl_seconds=300)
def fetch_assets(course, conn, video_id):
    query = f"SELECT * FROM assets WHERE for_which=%s AND video_id = %s"
    return _fetch_data_base(conn, query, (course, video_id), process_images=False)

@cache_result(ttl_seconds=600)
def fetch_achievements(conn, uid):
    query = f"SELECT * FROM achievements WHERE uid = %s"
    return _fetch_data_base(conn, query, (uid,), process_images=False)

@cache_result(ttl_seconds=3600)  # Payment details lai dherai time samma cache garne
def get_apay_card_details(conn, table_name, user_id):
    query = f"SELECT * FROM {table_name} WHERE user_id = %s"
    return _fetch_data_base(conn, query, (user_id,), process_images=False)

@cache_result(ttl_seconds=180)  # Discussions chado change hunxa, so short cache time
def fetch_video_discussion(course, conn, video_id):
    query = f"SELECT * FROM video_discussion WHERE for_which=%s AND video_id = %s"
    return _fetch_data_base(conn, query, (course, video_id), process_images=False)

@cache_result(ttl_seconds=600)
def fetch_data_course(course, conn, table_name):
    query = f"SELECT * FROM {table_name} WHERE for_which=%s AND type='course '"
    return _fetch_data_base(conn, query, (course,))

@cache_result(ttl_seconds=600)
def fetch_data_course_paid(course, conn, table_name):
    query = f"SELECT * FROM {table_name} WHERE for_which=%s AND type='course AND price > 0'"
    return _fetch_data_base(conn, query, (course,))

@cache_result(ttl_seconds=600)
def fetch_data_course_free(course, conn, table_name):
    query = f"SELECT * FROM {table_name} WHERE for_which=%s AND type='course' AND price = 0"
    return _fetch_data_base(conn, query, (course,))

@cache_result(ttl_seconds=300)
def fetch_data_course_search(course, conn, table_name):
    query = f"SELECT * FROM {table_name} WHERE for_which=%s AND price > 0"
    return _fetch_data_base(conn, query, (course,))

@cache_result(ttl_seconds=600)
def fetch_data_course_id(course, conn, table_name, course_id):
    query = f"SELECT * FROM {table_name} WHERE for_which=%s AND type='course' and id = %s"
    return _fetch_data_base(conn, query, (course, course_id))

@cache_result(ttl_seconds=600)
def fetch_data_mocktest(course, conn, table_name, test_type):
    query = f"SELECT * FROM {table_name} WHERE for_which=%s AND type=%s"
    return _fetch_data_base(conn, query, (course, test_type), process_images=False)

@cache_result(ttl_seconds=600)
def fetch_data_products(course, conn, table_name):
    query = f"SELECT * FROM {table_name} WHERE for_which = %s"
    return _fetch_data_base(conn, query, (course,), process_images=False)

# Video data fetch garne
def fetch_data_videos(course, conn, table_name, video_types):
    cursor = conn.cursor(dictionary=True)
    
    # Type filter nabhaeko base query
    base_query = f"""
    SELECT 
        id, 
        title, 
        video_url, 
        type, 
        credit, 
        short_description,
        for_which, 
        level, 
        image_url, 
        video_duration as videoDuration, 
        count_exercises as countExercises, 
        count_students as countStudents, 
        count_plays as countPlays, 
        count_views as countViews, 
        count_likes as countLikes, 
        course_id,
        count_comments as countComments,
        is_live,
        chat_id,
        free
    FROM {table_name} 
    """
    
    # Video_types list khali xa ki xaina check garne
    if not video_types:
        # Kun video types specify gareko xaina bhane, course ko sabai videos liyaune
        query = base_query + "WHERE for_which = %s AND free = 1"
        params = [course]
    else:
        # IN clause use garera list ma bhayeko kun pani type match garne SQL query banaunea
        placeholders = ', '.join(['%s'] * len(video_types))  # IN clause ko lagi placeholders create garne
        query = base_query + f"WHERE for_which = %s AND type IN ({placeholders}) AND free = 1"
        # Query execute garna course ra video_types combine garne
        params = [course] + video_types
    
    cursor.execute(query, params)
    rows = cursor.fetchall()
    
    return rows[::-1]  # Reverse order ma rows haru return garne

def fetch_data_videos_wcourse_id(course, conn, table_name,course_id):
    cursor = conn.cursor(dictionary=True)
    
    # Modify the query to select all columns except 'description'
    query = f"""
    SELECT 
        id, 
        title, 
        video_url, 
        type, 
        credit, 
        short_description,
        for_which, 
        level, 
        image_url, 
        video_duration as videoDuration, 
        count_exercises as countExercises, 
        count_students as countStudents, 
        count_plays as countPlays, 
        count_views as countViews, 
        count_likes as countLikes, 
        course_id,
        count_comments as countComments,
        is_live,
        chat_id
    FROM {table_name} 
    WHERE for_which = %s and course_id = %s
    """
    
    # Execute the query
    cursor.execute(query, (course,course_id))  
    rows = cursor.fetchall()
    
    print(query)
    return rows
    # return rows[::-1]  # Return the rows in reverse order

def fetch_data_videos_by_product_ids(product_ids, conn, table_name):
    cursor = conn.cursor(dictionary=True)
    
    # Check if product_ids is not empty
    if product_ids:
        placeholders = ', '.join(['%s'] * len(product_ids))
        query = f"""
        SELECT 
            id, 
            title, 
            video_url, 
            type, 
            credit, 
            short_description,
            for_which, 
            level, 
            imageUrl, 
            videoDuration, 
            countExercises, 
            countStudents, 
            countPlays, 
            countViews, 
            countLikes, 
            course_id,
            countComments,
            is_live,
            chat_id
        FROM {table_name} 
        WHERE is_live = 1 AND course_id IN ({placeholders})
        """
        cursor.execute(query, product_ids)  # Pass the product_ids as parameters
    else:
        # Handle case when product_ids is empty (no products)
        query = f"""
        SELECT 
            id, 
            title, 
            video_url, 
            type, 
            credit, 
            short_description,
            for_which, 
            level, 
            imageUrl, 
            videoDuration, 
            countExercises, 
            countStudents, 
            countPlays, 
            countViews, 
            countLikes, 
            course_id,
            countComments,
            is_live,
            chat_id
        FROM {table_name} 
        WHERE 1 = 0  -- This ensures no rows are returned
        """
        cursor.execute(query)  # No need for parameters here
    
    rows = cursor.fetchall()
    return rows[::-1]  # Return the rows in reverse order

def fetch_temporary_transaction(conn, table_name, user_id):
    cursor = conn.cursor(dictionary=True)
    
    # Modify the query to select all columns
    query = f"""
    SELECT *
    FROM {table_name} 
    WHERE user_id = %s  AND success = 1
    """
    
    # Execute the query
    cursor.execute(query, (user_id, ))  # Add a comma to make it a tuple
    rows = cursor.fetchall()
    
    print(query)  # Optional: Print the query for debugging
    return rows[::-1]  # Return the rows in reverse order

def fetch_videos_description(course, conn, id):
    cursor = conn.cursor(dictionary=True)
    
    # Modify the query to select all columns except 'description'
    query = """
    SELECT 
        description
    FROM videos 
    WHERE for_which = %s AND id = %s
    """
    
    # Execute the query
    cursor.execute(query, (course, id))  # Use %s for both placeholders
    rows = cursor.fetchall()
    
    # print(query)
    return rows[::-1]  # Return the rows in reverse order

# Fetch data from an external API
# Fetch data from an external API
def fetch_data_from_api(api_endpoint, api_key):
    url = f"{api_endpoint}?api_key={api_key}"
    response = requests.get(url)
    if response.status_code != 200:
        return {"error": response.text}
    return response.json()

def send_email_via_api(api_endpoint, api_key, subject, from_email, to_emails, message):
 
    # Prepare the payload for the POST request
    payload = {
        'api_key': api_key,
        'subject': subject,
        'msg': message,
        'from': from_email,
        'to': to_emails
    }

    try:
        # Make the POST request
        response = requests.post(api_endpoint, data=payload)

        # Check if the response is successful
        if response.status_code != 200:
            return {"error": f"Request failed with status {response.status_code}: {response.text}"}

        # Return JSON response
        return response.json()

    except requests.exceptions.RequestException as e:
        return {"error": f"An error occurred: {str(e)}"}
        
def fetch_data_from_api(api_endpoint, api_key):
    url = f"{api_endpoint}?api_key={api_key}"
    response = requests.get(url)
    if response.status_code != 200:
        return {"error": response.text}
    return response.json()

def fetch_data_from_api_search(api_endpoint, api_key):
    url = f"{api_endpoint}&api_key={api_key}"
    response = requests.get(url)
    if response.status_code != 200:
        return {"error": response.text}
    return response.json()
# Fetch questions from the database
def get_questions_from_db(course, conn):
    import json
    cursor = conn.cursor(dictionary=True)
    
    # Updated query to work with new questions table structure
    query = """
        SELECT q.*, t.title as topic_title, t.parent_topic_id, t.level, t.full_path
        FROM questions q
        JOIN topics t ON q.topic_id = t.id
        JOIN courses c ON t.course_id = c.id
        WHERE c.id = %s
        ORDER BY q.id DESC
    """
    cursor.execute(query, (course,))
    questions = cursor.fetchall()

    # Format questions to match expected frontend structure
    formatted_questions = []
    for question in questions:
        try:
            # Parse options from JSON
            if isinstance(question['options'], str):
                options_list = json.loads(question['options'])
            else:
                options_list = question['options']
        except (json.JSONDecodeError, TypeError):
            # Fallback if JSON parsing fails
            options_list = ['Option 1', 'Option 2', 'Option 3', 'Option 4']
        
        # Convert to expected format
        formatted_options = []
        correct_index = int(question['correct_answer_index']) if question['correct_answer_index'] is not None else 0
        
        for i, option_text in enumerate(options_list):
            formatted_options.append({
                'option_id': str(i),
                'option_text': str(option_text).strip(),
                'is_correct': i == correct_index
            })
        
        formatted_question = {
            'question_id': str(question['id']),
            'question_text': question['question_text'],
            'topic_id': question['topic_id'],
            'topic_title': question['topic_title'],
            'parent_topic_id': question.get('parent_topic_id'),
            'topic_level': question.get('level', 0),
            'topic_full_path': question.get('full_path'),
            'options': formatted_options,
            'difficulty': question.get('difficulty', 'medium'),
            'explanation': question.get('explanation', ''),
            'created_at': question.get('created_at'),
            'updated_at': question.get('updated_at')
        }
        formatted_questions.append(formatted_question)
    
    return formatted_questions