import os
import uuid
import re
import json
import shutil
import requests
import logging
from typing import List
from fastapi import APIRouter, Depends, Request, HTTPException, status
from fastapi.security import HTTPBearer, HTTPAuthorizationCredentials
from fastapi.encoders import jsonable_encoder
from fastapi.responses import JSONResponse, FileResponse
from sqlalchemy.orm import Session, joinedload
from sqlalchemy import text
from dotenv import load_dotenv
from bs4 import BeautifulSoup
import markdown2
from datetime import datetime, timezone
import db_config.database as dbase
from db_config.models import Courses,CourseModules, User, UserCourseLog, AccessToken, LMSPlatform, LMSCourseLog
import db_config.schemas as schemas
import db_config.auth as auth
from .user import *
from dependencies.helper import *
from dependencies.content_weaver import *
from dependencies.CourseCreator import CourseCreator
from dependencies.awsutils import S3Service
from syllabuild_celery_worker import process_course_celery
from celery.result import AsyncResult

router = APIRouter()
security = HTTPBearer()
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)
load_dotenv()
EMAIL_REGEX = r'^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$'
OUTPUT_FOLDER = os.getenv("OUTPUT_FOLDER", "./result")
S3_DIR = os.getenv("S3_DIR", "Syllabuild_Courses")

@router.get("/list", response_model=schemas.CoursesResponse)
async def get_courses(
    email: str,
    user_id: str = None,
    course_status: str = 'Ready',
    search: str = None,
    limit: int = 10,
    offset: int = 0,
    db: Session = Depends(dbase.get_db)
):
    """
    Get list of all courses with pagination (limit & offset)
    """
    try:
        token = await validate_token(user_id=user_id, email=email, db=db)
        logger.info("Token: %s", token)
        if token['access_token'] is None:
            return JSONResponse(
                status_code=status.HTTP_200_OK,
                content={
                    "status": status.HTTP_200_OK,
                    "message": "Invalid or expired token.",
                    "redirect_url": f"{token['redirect_url']}"
                }
            )
        
        # print("Token validated:", token['access_token'])
        # logger.info("Token validated: %s", token['access_token'])
        courses_query = db.query(Courses).filter(Courses.is_deleted == "no").order_by(Courses.created_at.desc())
        if email:
            courses_query = courses_query.filter(Courses.email == email)
        if course_status:
            courses_query = courses_query.filter(Courses.status == course_status)
        if search:
            search_pattern = f"%{search}%"
            courses_query = courses_query.filter(
                (Courses.topic.ilike(search_pattern)) |
                (Courses.skills.ilike(search_pattern)) |
                (Courses.degree.ilike(search_pattern)) |
                (Courses.mode_of_delivery.ilike(search_pattern)) |
                (Courses.tone.ilike(search_pattern)) |
                (Courses.language.ilike(search_pattern))
            )
        
        # if user_id:
        #     courses_query = courses_query.filter(Courses.user_id == user_id)
        total = courses_query.count()
        courses = courses_query.offset(offset).limit(limit).all()
        
        if not courses:
            return successResponse(
                "No courses found.",
                []
            )
        
        course_data = [schemas.CoursesResponse.from_orm(course) for course in courses]
        encoded_data = jsonable_encoder(course_data)
        
        return JSONResponse(
            status_code=status.HTTP_200_OK,
            content={
                "status": status.HTTP_200_OK,
                "message": 'Data retrieved successfully',
                "data": encoded_data,
                "total": total,
                "limit": limit,
                "offset": offset
            },
        )
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Something went wrong: {str(e)}")

@router.post("/create", response_model=schemas.CoursesResponse)
async def create_course(
    course: schemas.CoursesCreate,
    db: Session = Depends(dbase.get_db)
):
    """
    Create a new course
    """
    try:
        course_id =  f"{os.getenv('COURSE_PREFIX')}-{uuid.uuid4()}"
        course_data = course.dict()
        # print(course_data)
        course_data['course_id'] = course_id
        course_data['status'] = 'Pending'
        course_data['course_step'] = 1
        # Ensure all required fields are present
        required_fields = [
            'topic', 'email','skills', 'degree',
            'allocated_time', 'mode_of_delivery', 'tone', 'language',
            'taxonomy', 'outline', 'learning_approach', 'no_of_modules'
        ]

        if not re.match(EMAIL_REGEX, course_data['email']):
            return JSONResponse(
                status_code=status.HTTP_400_BAD_REQUEST,
                content={
                    "status": status.HTTP_400_BAD_REQUEST,
                    "message": "Email: Invalid value."
                }
            )

        for field in required_fields:
            if field not in course_data or not course_data[field]:
                filed_name = field.replace('_', ' ').capitalize()
                return JSONResponse(
                    status_code=status.HTTP_400_BAD_REQUEST,
                    content={
                        "status": status.HTTP_400_BAD_REQUEST,
                        "message": f"{filed_name} is required."
                    }
                )
        if course_data['topic']:
            if len(course_data['topic']) > 100:
                return errorResponse(
                    f"Topic is too long. Maximum length is 100 characters."
                )
            if len(course_data['topic']) < 5:
                return errorResponse(
                    f"Topic is too short. Minimum length is 5 characters."
                )
            if not course_data['topic'].isascii():
                return errorResponse(
                    f"Topic contains non-ASCII characters."
                )
        user = db.query(User).filter(User.email == course_data['email']).first()
        if user:
            course_data['user_id'] = user.id
        else:
            course_data['user_id'] = None

        if fetch_total_course_credits(
            user_id=course_data['user_id'],
            db=db
        )['available_credits'] < 1:
            return errorResponse(
                "Insufficient course credits. Please purchase more credits to create a new course."
            )
        new_course = Courses(**course_data)
        db.add(new_course)
        db.commit()
        db.refresh(new_course)
        course = db.query(Courses).filter(Courses.course_id == course_id).first()
        
        # if os.getenv("CELERY_ENABLED", "False").lower() == "true":
        #     # If Celery is enabled, process the course in the background
        #     print("Celery is enabled, processing course in the background.")
        #     task = process_course_celery.delay(course_data['topic'], course_id)
        #     print(f"Task ID: {task.id}")
            
        #     course.course_imscc_url = f"{os.getenv('APP_URL')}/download-imscc?course_id={course_id}"
        #     course.status = "Processing"
        #     course.task_id = task.id
        #     db.commit()
        #     db.refresh(course)
            
        # else:
        #     print("Celery is not enabled, processing course synchronously.")
            
        print(f"Course created with ID: {course_id}")
        encoded_data = jsonable_encoder(schemas.CoursesResponse.from_orm(course).dict())

        return successResponse(
            "Course created successfully",
            encoded_data
        )
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Something went wrong: {str(e)}")

@router.get("/regenerate/{course_id}", response_model=schemas.CoursesResponse)
async def regenerate_course(
    course_id: str,
    db: Session = Depends(dbase.get_db)
):
    """
    Regenerate course content by course_id
    """
    try:
        course = db.query(Courses).filter(Courses.course_id == course_id).first()
        if not course:
            return errorResponse(
                "Course not found."
            )
        if not course.topic:
            return errorResponse(
                "Course topic is required to regenerate content."
            )
        ### Regenerate the course content manual process
        # course_creator = CourseCreator(
        #     course=course
        # )
        # course_creator.generate_course_materials()
        # course_creator.get_module_titles()
        
        # Regenerate the course content
        task = process_course_celery.delay(course.topic, course_id)
        print(f"Regeneration Task ID: {task.id}")

        course.task_id = task.id
        course.status = "Processing"
        db.commit()
        db.refresh(course)

        encoded_data = jsonable_encoder(schemas.CoursesResponse.from_orm(course).dict())
        return successResponse(
            "Course regeneration initiated successfully",
            encoded_data
        )
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Something went wrong: {str(e)}")

@router.get("/get/{course_id}", response_model=schemas.CoursesResponse)
async def get_course(
    course_id: str,
    db: Session = Depends(dbase.get_db)
):
    """
    Get course details by course_id
    """
    try:
        course = db.query(Courses).filter(Courses.course_id == course_id).first()
        if not course:
            return errorResponse(
                "Course not found."
            )
        course_data = schemas.CoursesResponse.from_orm(course).dict()
        # course_content = get_course_content(course_data['topic'])
        if course.markdown_url:
            response = requests.get(course.markdown_url)
            if response.status_code == 200:
                markdown_content = response.text
                course_data['markdown_content'] = markdown_content

        course_data['course_content'] = course_data['markdown_content']
        encoded_data = jsonable_encoder(course_data)
        return successResponse(
            "Course retrieved successfully",
            encoded_data
        )
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Something went wrong: {str(e)}")

@router.post("/generate-ai", response_model=schemas.CoursesResponse)
async def generate_ai_course(
    course_id: str,
    form_data: schemas.CourseAISchema,
    db: Session = Depends(dbase.get_db)
):
    """
    Generate AI content for a course field
    """
    try:
        course = db.query(Courses).filter(Courses.course_id == course_id).first()
        if not course:
            return errorResponse(
                "Course not found."
            )
        if not course.topic:
            return errorResponse(
                "Course topic is required to generate AI content."
            )
        form = form_data.dict()
        field_type = form['field_type']
        taxonomy = form['taxonomy']
        
        # Validate field_type
        valid_fields = [
            "content_description", "content_outcomes", "learning_approach"
        ]
        if field_type not in valid_fields:
            return errorResponse(
                f"Invalid field type. Valid options are: {', '.join(valid_fields)}"
            )

        if taxonomy and field_type == "content_outcomes":
            # If taxonomy is provided, use it to generate content outcomes
            course.taxonomy = taxonomy
            db.commit()
            db.refresh(course)
        
        course_creator = CourseCreator(
            course=course
        )

        if field_type == "content_description":
            content_type = "course description"
        else:
            content_type = field_type
        # Generate AI content based on the field_type
        ai_content = course_creator.generate_ai_content(content_type)
        
        # Update the course with the generated content
        setattr(course, field_type, ai_content)
        db.commit()
        db.refresh(course)
        
        encoded_data = jsonable_encoder(schemas.CoursesResponse.from_orm(course).dict())
        return successResponse(
            f"{field_type.replace('_', ' ').capitalize()} generated successfully",
            encoded_data
        )
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Something went wrong: {str(e)}")

@router.put("/update/{course_id}", response_model=schemas.CoursesResponse)
async def update_course(
    course_id: str,
    course: schemas.CoursesUpdate,
    step: str = "1",
    db: Session = Depends(dbase.get_db)
):
    """
    Update an existing course by course_id
    """
    try:
        existing_course = db.query(Courses).filter(Courses.course_id == course_id).first()
        if not existing_course:
            return errorResponse(
                "Course not found."
            )
        # Ensure all required fields are present
        if step == "1":
            required_fields = [
                'topic', 'email', 'skills', 'degree',
                'allocated_time', 'mode_of_delivery', 'tone', 'language'
            ]
        elif step == "2":
            required_fields = [
                'taxonomy', 'content_description','content_outcomes'
            ]
        elif step == "3":
            required_fields = [
                'level_of_difficulty', 'type_of_assessment', 'no_of_questions', 'no_of_modules', 'outline', 'learning_approach'
            ]
        elif step == "4":
            required_fields = [
                'module_title'
            ]
        else:
            return errorResponse(
                "Invalid step. Valid steps are: 1, 2, or 3."
            )
        
        course.course_step = int(step)

        course_data = course.dict()
        
        for field in required_fields:
            if field not in course_data or not course_data[field]:
                filed_name = field.replace('_', ' ').capitalize()
                return JSONResponse(
                    status_code=status.HTTP_400_BAD_REQUEST,
                    content={
                        "status": status.HTTP_400_BAD_REQUEST,
                        "message": f"{filed_name} is required."
                    }
                )
        
        for key, value in course.dict().items():
            if value:
                setattr(existing_course, key, value)
        
        db.commit()
        db.refresh(existing_course)
        regenerate_course = db.query(Courses).filter(Courses.course_id == course_id).first()
        if os.getenv("CELERY_ENABLED", "False").lower() == "true" and step == "3":
            # If Celery is enabled, process the course in the background
            print("Celery is enabled, processing course in the background.")

            # self.save_course_module(
            #     course_id=course_id,
            #     module_data=schemas.CourseModuleCreate(
            #         course_id=course_id,
            #         module_title=course_data.get('module_title', ''),
            #         module_subtopic=course_data.get('module_subtopic', ''),
            #         estimated_time=course_data.get('estimated_time', ''),
            #         assessment=course_data.get('assessment', ''),
            #         recommended_resourses=course_data.get('recommended_resourses', ''),
            #         skills=course_data.get('skills', '')
            #     ),
            #     db=db
            # )

            # Regenerate the course content
            task = process_course_celery.delay(regenerate_course.topic, course_id)
            print(f"Regeneration Task ID: {task.id}")

            regenerate_course.task_id = task.id
            regenerate_course.status = "Processing"
            db.commit()
            db.refresh(regenerate_course)
            user = db.query(User).filter(User.id == regenerate_course.user_id).first()
            # Deduct one course credit from the user
            UserCourseLogEntry = UserCourseLog(
                user_id=regenerate_course.user_id,
                platform_id=user.platform_id,
                number_of_course=1,
                type="Debit"
            )
            db.add(UserCourseLogEntry)
            db.commit()
        else:
            print("Celery is not enabled, processing course synchronously.")

        course_data = schemas.CoursesResponse.from_orm(regenerate_course).dict()
        encoded_data = jsonable_encoder(course_data)
        return successResponse(
            "Course updated successfully",
            encoded_data
        )
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Something went wrong: {str(e)}")

def save_course_module(
    course_id: str,
    module_data: schemas.CourseModuleCreate,
    db: Session = Depends(dbase.get_db)
):
    """
    Helper function to save a course module
    """
    new_module = CourseModules(
        course_id=course_id,
        module_title=module_data.module_title,
        module_subtopic=module_data.module_subtopic,
        estimated_time=module_data.estimated_time,
        assessment=module_data.assessment,
        recommended_resourses=module_data.recommended_resourses,
        skills=module_data.skills
    )
    db.add(new_module)
    db.commit()
    db.refresh(new_module)
    return new_module


@router.delete("/delete/{course_id}", response_model=schemas.CoursesResponse)
async def delete_course(
    course_id: str,
    db: Session = Depends(dbase.get_db)
):
    """
    Delete a course by course_id
    """
    try:
        existing_course = db.query(Courses).filter(Courses.course_id == course_id).first()
        if not existing_course:
            return errorResponse(
                "Course not found."
            )
        existing_course.is_deleted = "yes"
        existing_course.deleted_at = datetime.now(timezone.utc)
        
        # db.delete(existing_course)
        db.commit()
        return successResponse(
            "Course deleted successfully",
            {}
        )
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Something went wrong: {str(e)}")  

@router.get("/{course_id}/citation/{index}", response_model=schemas.CoursesResponse)
async def get_citation(
    course_id: str,
    index: int,
    db: Session = Depends(dbase.get_db)
):
    """
    Get citation for a specific index in the course
    """
    try:
        course = db.query(Courses).filter(Courses.course_id == course_id).first()
        if not course:
            return errorResponse(
                "Course not found."
            )
        
        weblinks = json.loads(course.weblinks) if course.weblinks else []
        if index < 0 or index >= len(weblinks):
            return errorResponse(
                "Index out of range."
            )
        
        citation = weblinks[index]
        return successResponse(
            "Citation retrieved successfully",
            {"citation": citation}
        )
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Something went wrong: {str(e)}")

@router.get("/task/{task_id}")
async def get_task_status(task_id: str):
    task_result = AsyncResult(task_id)
    if task_result.state == 'PENDING':
        return {"state": task_result.state, "progress": 0}
    elif task_result.state == 'PROGRESS':
        return {
            "state": task_result.state,
            "progress": task_result.info.get('progress', 0)
        }

    elif task_result.state == 'SUCCESS':
        return {"state": "COMPLETED", "progress": 100}

    elif task_result.state == 'FAILURE':
        return {"state": "FAILED", "progress": 0, "error": str(task_result.info)}
    
    return {
        "state": task_result.state, 
        "progress": 0,
        "status": task_result.status, 
        "result": str(task_result.result),  # Get the task result
        "traceback": task_result.traceback  # Get detailed error logs
    }

@router.post('/import-course')
async def import_course(
    course_id: str,
    user_id: int,
    db: Session = Depends(dbase.get_db)
):
    """
    Import course by course_id
    """
    try:
        course = db.query(Courses).filter(Courses.course_id == course_id).first()
        if not course:
            return errorResponse(
                "Course not found."
            )
        # Logic to import the course goes here
        topic = course.topic
        article_title = truncate_filename(
            topic.replace(" ", "_").replace("/", "_")
        )
        
        imscc_course_path = await upload_imscc_on_s3(
            course_id=course_id,
            db=db
        )

        user = db.query(User).filter(User.id == user_id).first()
        plateform = db.query(LMSPlatform).filter(LMSPlatform.id == user.platform_id).first()
        accounts = json.loads(plateform.accounts) if plateform and plateform.accounts else []
        account_id = accounts[0]["id"] if accounts else None
        token = await validate_token(user_id=user_id, email=user.email, db=db)
        canvas_course_result = await create_and_migrate_course(
            course_id,
            account_id,
            course.topic,
            imscc_course_path,
            token['access_token'],
            plateform,
            db
        )

        working_dir = os.getenv("WORKING_DIR")
        article_dir = os.path.join(working_dir, article_title)

        if os.path.exists(article_dir):
            shutil.rmtree(article_dir)   # ⚠️ Deletes entire directory tree
        # For demonstration, we'll just return a success message
        return successResponse(
            "Course imported successfully",
            {
                'imscc_path': imscc_course_path
            }
        )
    except Exception as e:
        # raise HTTPException(status_code=500, detail=f"Something went wrong: {str(e)}")
        return errorResponse(
            f"Something went wrong: {str(e)}",
            500
        )

async def upload_imscc_on_s3(
    course_id: str,
    db: Session = Depends(dbase.get_db)
):
    """
    Upload IMSCC file to S3
    """
    course = db.query(Courses).filter(Courses.course_id == course_id).first()
    topic = course.topic
    article_title = truncate_filename(
        topic.replace(" ", "_").replace("/", "_")
    )
    filename = f"{article_title}.imscc"

    course_creator = CourseCreator(
        course=course
    )
    
    if course.markdown_url:
        response = requests.get(course.markdown_url)
        if response.status_code == 200:
            markdown_content = response.text
            course.markdown_content = markdown_content
    
    markdown_content = re.sub(r"^#### Subtopic:\s*\n?", "", course.markdown_content, flags=re.MULTILINE)

    imscc_path = course_creator.generate_imscc(markdown_content)
    # print(f"IMSCC Path: {imscc_path}")
    s3_service = S3Service()
    s3_result = s3_service.upload_imscc_to_s3(local_path=imscc_path, s3_directory=f"{S3_DIR}/{course_id}")
    logger.info("S3 Upload Result: %s", s3_result)
    course_path = s3_result['public_url']
    logger.info("Course IMSCC S3 Path: %s", course_path)

    course.course_imscc_url = course_path
    db.commit()
    db.refresh(course)
    return course_path

@router.get("/download-imscc")
def download_imscc(
    course_id: str,
    file_type: str = "imscc",
    db: Session = Depends(dbase.get_db)
):
    """
    Generate a course and return it as a downloadable IMSCC file.
    """
    course = db.query(Courses).filter(Courses.course_id == course_id).first()
    if not course:
        return errorResponse(
            "Course not found."
        )
    topic = course.topic
    if not topic:
        return errorResponse(
            "Course topic is required to generate IMSCC."
        )
    article_title = truncate_filename(
        topic.replace(" ", "_").replace("/", "_")
    )
    filename = f"{article_title}.imscc"

    lms_plateform_id = db.query(User).filter(User.id == course.user_id).first().platform_id if course.user_id else None
    if lms_plateform_id:
        tenant_name = db.query(LMSPlatform).filter(LMSPlatform.id == lms_plateform_id).first().tenant_name
    else:
        tenant_name = None
    course_creator = CourseCreator(
        course=course
    )

    if course.markdown_url:
        response = requests.get(course.markdown_url)
        if response.status_code == 200:
            markdown_content = response.text
            course.markdown_content = markdown_content
    # print(course.markdown_content)        
    markdown_content = re.sub(r"^#### Subtopic:\s*\n?", "", course.markdown_content, flags=re.MULTILINE)
    if file_type == "imscc":
        # Generate the IMSCC file
        imscc_path = course_creator.generate_imscc(markdown_content)
        media_type = "application/zip"
    elif file_type == "pdf":
        # Generate the PDF file
        file_response = course_creator.export_to_html_and_pdf(markdown_content, tenant_name)
        imscc_path = file_response['pdf_file']
        filename = f"{article_title}.pdf"
        media_type = "application/pdf"
    elif file_type == "html":
        # Generate the HTML file
        file_response = course_creator.export_to_html(markdown_content)
        imscc_path = file_response['html_file']
        filename = f"{article_title}.html"
        media_type = "text/html"
    elif file_type == "md":
        # Generate the Markdown file
        imscc_path = os.path.join(OUTPUT_FOLDER, f"{article_title}.md")
        filename = f"{article_title}.md"
        media_type = "text/markdown"
    else:
        imscc_path = course_creator.generate_imscc(markdown_content)
        media_type = "application/zip"
    # 1. Generate the IMSCC file
    # imscc_path = course_creator.generate_imscc(course.markdown_content)
    working_dir = os.getenv("WORKING_DIR")
    article_dir = os.path.join(working_dir, article_title)
    
    if os.path.exists(article_dir):
        shutil.rmtree(article_dir)   # ⚠️ Deletes entire directory tree
        # print(f"{article_dir} deleted successfully")
    
    # 2. Return the IMSCC file as a downloadable response
    return FileResponse(
        imscc_path,
        media_type=media_type,
        filename=filename
    )
    
@router.get("/details/{course_id}")
async def get_course_details(
    course_id: str,
    db: Session = Depends(dbase.get_db)
):
    """
    Get course details by course_id
    """
    try:
        course = db.query(Courses).filter(Courses.course_id == course_id).first()
        if not course:
            return errorResponse(
                "Course not found."
            )
        if course.markdown_url:
            response = requests.get(course.markdown_url)
            if response.status_code == 200:
                markdown_content = response.text
                course.markdown_content = markdown_content
        # Assuming the content is stored in markdown_content
        if not course.markdown_content:
            return errorResponse(
                "Course content is not available."
            )
        # output_base = os.path.join('./result', 'LLM_Prompt_Engineering_For_Developers')
        # with open(f"{output_base}.md", "r", encoding="utf-8") as f:
        #     markdown = f.read()
        # sections = parse_markdown_nested(markdown)
        # sections = parse_markdown_nested(course.markdown_content)
        markdown_content = re.sub(r"^#### Subtopic:\s*\n?", "", course.markdown_content, flags=re.MULTILINE)
        extras = ["fenced-code-blocks", "code-friendly", "tables", "cuddled-lists", "break-on-newline"]
        html = markdown2.markdown(markdown_content, extras=extras)
        soup = BeautifulSoup(html, "html.parser")
        # soup = normalize_headings(soup)
        nested_toc = build_nested_toc(soup)
        return successResponse(
            "Course details retrieved successfully",
            nested_toc
        )
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Something went wrong: {str(e)}")

@router.get("/course-modules/{course_id}")
async def get_course_modules(
    course_id: str,
    db: Session = Depends(dbase.get_db)
):
    """
    Get course modules by course_id
    """
    try:
        course = db.query(Courses).filter(Courses.course_id == course_id).first()
        if not course:
            return errorResponse(
                "Course not found."
            )
        
        if course.markdown_url:
            response = requests.get(course.markdown_url)
            if response.status_code == 200:
                markdown_content = response.text
                course.markdown_content = markdown_content

        if not course.markdown_content:
            return errorResponse(
                "Course content is not available."
            )
        
        markdown_text = course.markdown_content
        modules = []
        # Create an instance of CourseCreator with the course details
        course_creator = CourseCreator(
            course=course
        )
        
        module_titles = course_creator.get_module_titles(markdown_text)
        
        for i, module in enumerate(module_titles, start=1):
            if module['subtopics']:
                subtopics = ", ".join(f"{st}" for st in module['subtopics']) if module['subtopics'] else ""
                data = {
                    "module_id": i,
                    "module": module['module_title'],
                    "subtopics": subtopics,
                    "estimated_time": module.get('estimated_time', ''),
                }
                modules.append(data)
            
        
        return successResponse(
            "Course modules retrieved successfully",
            modules
        )
        
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Something went wrong: {str(e)}")

@router.get("/course-pdf/{course_id}")
async def get_course_pdf(
    course_id: str,
    db: Session = Depends(dbase.get_db)
):
    """
    Get course PDF by course_id
    """
    try:
        course = db.query(Courses).filter(Courses.course_id == course_id).first()
        if not course:
            return errorResponse(
                "Course not found."
            )
        
        if course.markdown_url:
            response = requests.get(course.markdown_url)
            if response.status_code == 200:
                markdown_content = response.text
                course.markdown_content = markdown_content

        if not course.markdown_content:
            return errorResponse(
                "Course content is not available."
            )
        
        course_creator = CourseCreator(
            course=course
        )
        
        pdf_path = course_creator.export_to_html_and_pdf(course.markdown_content)
        
        return successResponse(
            "Course details retrieved successfully",
            []
        )
        
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Something went wrong: {str(e)}")
    
@router.get("/get-usage-report/{email}")
async def get_usage_reports(
    email: str,
    db: Session = Depends(dbase.get_db)
):
    """
    Get course reports by email
    """
    try:

        user = db.query(User).filter(User.email == email).first()
        if not user:
            return errorResponse(
                "User not found."
            )
        user_id = user.id
        if not user.platform_id:
            query = text("""
                SELECT 
                    platform_id,
                    DATE(created_at) AS date,
                    SUM(CASE WHEN type = 'Credit' THEN number_of_course ELSE 0 END) AS added,
                    SUM(CASE WHEN type = 'Debit' THEN number_of_course ELSE 0 END) AS used,
                    SUM(SUM(CASE WHEN type = 'Credit' THEN number_of_course ELSE 0 END) -
                        SUM(CASE WHEN type = 'Debit' THEN number_of_course ELSE 0 END))
                        OVER (ORDER BY DATE(created_at)) AS balance
                FROM user_course_logs
                WHERE user_id = :user_id
                GROUP BY DATE(created_at)
                ORDER BY DATE(created_at);
                """)
            result = db.execute(query, {"user_id": user_id})
        else:
            query = text("""
                SELECT 
                    platform_id,     
                    DATE(created_at) AS date,
                    SUM(CASE WHEN type = 'Credit' THEN number_of_course ELSE 0 END) AS added,
                    SUM(CASE WHEN type = 'Debit' THEN number_of_course ELSE 0 END) AS used,
                    SUM(SUM(CASE WHEN type = 'Credit' THEN number_of_course ELSE 0 END) -
                        SUM(CASE WHEN type = 'Debit' THEN number_of_course ELSE 0 END))
                        OVER (ORDER BY DATE(created_at)) AS balance
                FROM user_course_logs
                WHERE platform_id = :platform_id
                GROUP BY DATE(created_at)
                ORDER BY DATE(created_at);
                """)
            result = db.execute(query, {"platform_id": user.platform_id})

        rows = result.fetchall()
        columns = result.keys()
        reports = []
        for row in rows:
            row_dict = dict(zip(columns, row))
            reports.append({
                "date": row_dict["date"].strftime("%d-%m-%Y") if row_dict["date"] else None,
                "added": row_dict["added"],
                "used": row_dict["used"],
                "balance": row_dict["balance"],
                "platform_id": row_dict["platform_id"]
            })
        
        return {
            "status": "success",
            "message": "Usage log retrieved successfully",
            "data": reports
        }
        
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Something went wrong: {str(e)}")

@router.get("/report-details/{date}/{platform_id}")
async def get_report_details(
    date: str,
    platform_id: str,
    db: Session = Depends(dbase.get_db)
):
    """
    Get course report details by date and platform_id
    """
    try:
        query = text("""
            SELECT user_course_logs.user_id,user_course_logs.number_of_course, user_course_logs.type, users.name
            FROM user_course_logs
            JOIN users ON user_course_logs.user_id = users.id
            WHERE DATE(user_course_logs.created_at) = :date AND user_course_logs.platform_id = :platform_id;
        """)
        result = db.execute(query, {"date": date, "platform_id": platform_id})
        rows = result.fetchall()
        columns = result.keys()
        report_details = []
        for row in rows:
            row_dict = dict(zip(columns, row))
            report_details.append(row_dict)
        
        return {
            "status": "success",
            "message": "Report details retrieved successfully",
            "data": report_details
        }
        
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Something went wrong: {str(e)}")

def fetch_total_course_credits(
    user_id: int,
    db
):
    user = db.query(User).filter(User.id == user_id).first()
    """
    Helper function to get total course credits for a user
    """
    if not user.platform_id:
        credit_query = text("""
            SELECT 
                SUM(CASE WHEN type = 'Credit' THEN number_of_course ELSE 0 END) AS total_credits,
                SUM(CASE WHEN type = 'Debit' THEN number_of_course ELSE 0 END) AS used_credits,
                SUM(CASE WHEN type = 'Credit' THEN number_of_course ELSE 0 END) -
                SUM(CASE WHEN type = 'Debit' THEN number_of_course ELSE 0 END) AS available_credits
            FROM user_course_logs
            WHERE user_id = :user_id;
        """)
        result = db.execute(credit_query, {"user_id": user_id})
    else:
        credit_query = text("""
            SELECT 
                SUM(CASE WHEN type = 'Credit' THEN number_of_course ELSE 0 END) AS total_credits,
                SUM(CASE WHEN type = 'Debit' THEN number_of_course ELSE 0 END) AS used_credits,
                SUM(CASE WHEN type = 'Credit' THEN number_of_course ELSE 0 END) -
                SUM(CASE WHEN type = 'Debit' THEN number_of_course ELSE 0 END) AS available_credits
            FROM user_course_logs
            WHERE platform_id = :platform_id;
        """)
        result = db.execute(credit_query, {"platform_id": user.platform_id})

    row = result.fetchone()
    data = {}
    if row:
        # row can be a mapping (dict-like) or a tuple; handle both cases
        try:
            total = row["total_credits"]
            used = row["used_credits"]
            available = row["available_credits"]
        except Exception:
            # Try SQLAlchemy Row mapping first
            if hasattr(row, "_mapping"):
                total = row._mapping.get("total_credits")
                used = row._mapping.get("used_credits")
                available = row._mapping.get("available_credits")
            else:
                # Fallback to tuple indices
                total = row[0] if len(row) > 0 else None
                used = row[1] if len(row) > 1 else None
                available = row[2] if len(row) > 2 else None

        data = {
            "total_credits": total or 0,
            "used_credits": used or 0,
            "available_credits": available or 0
        }
    else:
        data = {
            "total_credits": 0,
            "used_credits": 0,
            "available_credits": 0
        }
    return data 

@router.get("/total-credits/{user_id}")
async def get_total_course_credits(
    user_id: int,
    db: Session = Depends(dbase.get_db)
):
    """
    Get total course credits for a user
    """
    data = fetch_total_course_credits(
        user_id=user_id,
        db=db
    )
    return {
        "status": "success",
        "message": "Usage log retrieved successfully",
        "data": data
    }