from sqlalchemy.orm import Session, joinedload
from sqlalchemy import desc, asc
from typing import List, Optional, Dict, Any
from datetime import datetime

from ..models.academy import Course, Section, Lesson, Enrollment, Progress
from ..models.user import User


# ============= HELPER FUNCTIONS =============

def course_to_dict(course: Course) -> Dict[str, Any]:
    """Convert Course ORM object to dictionary for template compatibility"""
    if not course:
        return None
    
    return {
        "id": course.id,
        "title": course.title,
        "description": course.description,
        "thumbnail": course.thumbnail,
        "status": course.status,
        "created_by": course.created_by,
        "created_at": course.created_at,
        "updated_at": course.updated_at,
        "order_index": course.order_index,
        "created_by_name": None  # Will be filled by query if needed
    }

def lesson_to_dict(lesson: Lesson) -> Dict[str, Any]:
    """Convert Lesson ORM object to dictionary"""
    if not lesson:
        return None
        
    return {
        "id": lesson.id,
        "course_id": lesson.course_id,
        "section_id": lesson.section_id,
        "title": lesson.title,
        "description": lesson.description,
        "content_type": lesson.content_type,
        "content_data": lesson.content_data,
        "duration_minutes": lesson.duration_minutes,
        "order_index": lesson.order_index,
        "status": lesson.status,
        "created_at": lesson.created_at,
        "updated_at": lesson.updated_at,
        "attachment_url": lesson.attachment_url,
        "attachment_name": lesson.attachment_name
    }


# ============= COURSES =============

def get_all_courses(db: Session, status: Optional[str] = None) -> List[Course]:
    """Get all courses, optionally filtered by status"""
    query = db.query(Course).outerjoin(User, Course.created_by == User.id)
    
    if status:
        query = query.filter(Course.status == status)
    
    return query.order_by(Course.order_index.asc(), Course.created_at.desc()).all()


def get_course_by_id(db: Session, course_id: int) -> Optional[Course]:
    """Get a single course by ID"""
    return db.query(Course).filter(Course.id == course_id).first()


def create_course(db: Session, title: str, created_by: int, description: Optional[str] = None,
                 thumbnail: Optional[str] = None, status: str = 'draft',
                 order_index: int = 0) -> int:
    """Create a new course"""
    course = Course(
        title=title,
        description=description,
        thumbnail=thumbnail,
        status=status,
        created_by=created_by,
        order_index=order_index
    )
    db.add(course)
    db.commit()
    db.refresh(course)
    return course.id


def update_course(db: Session, course_id: int, **kwargs) -> bool:
    """Update a course"""
    allowed_fields = ['title', 'description', 'thumbnail', 'status', 'order_index']
    updates = {k: v for k, v in kwargs.items() if k in allowed_fields and v is not None}
    
    if not updates:
        return False
    
    updates['updated_at'] = datetime.now().strftime('%Y-%m-%dT%H:%M:%S')
    
    db.query(Course).filter(Course.id == course_id).update(updates)
    db.commit()
    return True


def delete_course(db: Session, course_id: int) -> bool:
    """Delete a course and its lessons (cascade)"""
    course = db.query(Course).filter(Course.id == course_id).first()
    if course:
        db.delete(course)
        db.commit()
        return True
    return False


# ============= SECTIONS =============

def get_sections_by_course(db: Session, course_id: int) -> List[Section]:
    """Get all sections for a course"""
    return db.query(Section).filter(Section.course_id == course_id).order_by(
        Section.order_index.asc(),
        Section.created_at.asc()
    ).all()


def create_section(db: Session, course_id: int, title: str, description: Optional[str] = None,
                   order_index: int = 0) -> int:
    """Create a new section"""
    section = Section(
        course_id=course_id,
        title=title,
        description=description,
        order_index=order_index
    )
    db.add(section)
    db.commit()
    db.refresh(section)
    return section.id


def update_section(db: Session, section_id: int, **kwargs) -> bool:
    """Update a section"""
    allowed_fields = ['title', 'description', 'order_index']
    updates = {k: v for k, v in kwargs.items() if k in allowed_fields and v is not None}
    
    if not updates:
        return False
    
    updates['updated_at'] = datetime.now().strftime('%Y-%m-%dT%H:%M:%S')
    
    db.query(Section).filter(Section.id == section_id).update(updates)
    db.commit()
    return True


def delete_section(db: Session, section_id: int) -> bool:
    """Delete a section"""
    section = db.query(Section).filter(Section.id == section_id).first()
    if section:
        db.delete(section)
        db.commit()
        return True
    return False


# ============= LESSONS =============

def get_lessons_by_course(db: Session, course_id: int, status: Optional[str] = None) -> List[Lesson]:
    """Get all lessons for a course"""
    query = db.query(Lesson).filter(Lesson.course_id == course_id)
    if status:
        query = query.filter(Lesson.status == status)
    
    return query.order_by(
        Lesson.order_index.asc(),
        Lesson.created_at.asc()
    ).all()


def get_lessons_by_section(db: Session, section_id: int) -> List[Lesson]:
    """Get all lessons for a section"""
    return db.query(Lesson).filter(Lesson.section_id == section_id).order_by(
        Lesson.order_index.asc(),
        Lesson.created_at.asc()
    ).all()


def get_lesson_by_id(db: Session, lesson_id: int) -> Optional[Lesson]:
    """Get a single lesson by ID"""
    return db.query(Lesson).filter(Lesson.id == lesson_id).first()


def create_lesson(db: Session, course_id: int, title: str, content_type: str,
                 section_id: Optional[int] = None, description: Optional[str] = None,
                 content_data: Optional[str] = None, duration_minutes: Optional[int] = None,
                 order_index: int = 0, status: str = 'published',
                 attachment_url: Optional[str] = None, attachment_name: Optional[str] = None) -> int:
    """Create a new lesson"""
    lesson = Lesson(
        course_id=course_id,
        section_id=section_id,
        title=title,
        description=description,
        content_type=content_type,
        content_data=content_data,
        duration_minutes=duration_minutes,
        order_index=order_index,
        status=status,
        attachment_url=attachment_url,
        attachment_name=attachment_name
    )
    db.add(lesson)
    db.commit()
    db.refresh(lesson)
    return lesson.id


def update_lesson(db: Session, lesson_id: int, **kwargs) -> bool:
    """Update a lesson"""
    allowed_fields = ['title', 'description', 'content_type', 'content_data',
                     'duration_minutes', 'order_index', 'status', 'section_id',
                     'attachment_url', 'attachment_name']
    updates = {k: v for k, v in kwargs.items() if k in allowed_fields and v is not None}
    
    if not updates:
        return False
    
    updates['updated_at'] = datetime.now().strftime('%Y-%m-%dT%H:%M:%S')
    
    db.query(Lesson).filter(Lesson.id == lesson_id).update(updates)
    db.commit()
    return True


def delete_lesson(db: Session, lesson_id: int) -> bool:
    """Delete a lesson"""
    lesson = db.query(Lesson).filter(Lesson.id == lesson_id).first()
    if lesson:
        db.delete(lesson)
        db.commit()
        return True
    return False


# ============= ENROLLMENTS =============

def get_user_enrollments(db: Session, user_id: int) -> List[Enrollment]:
    """Get all enrollments for a user"""
    return db.query(Enrollment).filter(Enrollment.user_id == user_id).options(
        joinedload(Enrollment.course)
    ).all()


def get_course_enrollments(db: Session, course_id: int) -> List[Enrollment]:
    """Get all enrollments for a course"""
    return db.query(Enrollment).filter(Enrollment.course_id == course_id).all()


def is_user_enrolled(db: Session, user_id: int, course_id: int) -> bool:
    """Check if user is enrolled in a course"""
    enrollment = db.query(Enrollment).filter(
        Enrollment.user_id == user_id,
        Enrollment.course_id == course_id
    ).first()
    return enrollment is not None


def enroll_user(db: Session, user_id: int, course_id: int) -> int:
    """Enroll a user in a course"""
    # Check if already enrolled
    existing = db.query(Enrollment).filter(
        Enrollment.user_id == user_id,
        Enrollment.course_id == course_id
    ).first()
    
    if existing:
        return existing.id
    
    enrollment = Enrollment(
        user_id=user_id,
        course_id=course_id
    )
    db.add(enrollment)
    db.commit()
    db.refresh(enrollment)
    return enrollment.id


def unenroll_user(db: Session, user_id: int, course_id: int) -> bool:
    """Unenroll a user from a course"""
    enrollment = db.query(Enrollment).filter(
        Enrollment.user_id == user_id,
        Enrollment.course_id == course_id
    ).first()
    
    if enrollment:
        db.delete(enrollment)
        db.commit()
        return True
    return False


# ============= PROGRESS =============

def get_user_progress_for_course(db: Session, user_id: int, course_id: int) -> List[Progress]:
    """Get all progress records for a user in a specific course"""
    lesson_ids = db.query(Lesson.id).filter(Lesson.course_id == course_id).all()
    lesson_ids = [lid[0] for lid in lesson_ids]
    
    if not lesson_ids:
        return []

    return db.query(Progress).filter(
        Progress.user_id == user_id,
        Progress.lesson_id.in_(lesson_ids)
    ).all()

def get_user_progress(db: Session, user_id: int, course_id: Optional[int] = None) -> List[Progress]:
    """Get user's progress, optionally filtered by course"""
    if course_id:
        return get_user_progress_for_course(db, user_id, course_id)
    
    # Return all progress if no course specified
    # Note: This might be heavy if user has done many lessons, but matches old signature
    return db.query(Progress).filter(Progress.user_id == user_id).all()


def get_user_lesson_progress(db: Session, user_id: int, lesson_id: int) -> Optional[Progress]:
    """Get progress record for a specific user and lesson"""
    return db.query(Progress).filter(
        Progress.user_id == user_id,
        Progress.lesson_id == lesson_id
    ).first()


def mark_lesson_completed(db: Session, user_id: int, lesson_id: int) -> int:
    """Mark a lesson as completed for a user"""
    progress = db.query(Progress).filter(
        Progress.user_id == user_id,
        Progress.lesson_id == lesson_id
    ).first()
    
    now = datetime.now().strftime('%Y-%m-%dT%H:%M:%S')
    
    if progress:
        progress.completed = 1
        progress.completed_at = now
        progress.last_accessed_at = now
        db.commit()
        return progress.id
    else:
        new_progress = Progress(
            user_id=user_id,
            lesson_id=lesson_id,
            completed=1,
            completed_at=now,
            last_accessed_at=now
        )
        db.add(new_progress)
        db.commit()
        db.refresh(new_progress)
        return new_progress.id



def update_progress(db: Session, user_id: int, lesson_id: int, completed: bool) -> int:
    """Update lesson progress (completed status)"""
    progress = db.query(Progress).filter(
        Progress.user_id == user_id,
        Progress.lesson_id == lesson_id
    ).first()

    now = datetime.now().strftime('%Y-%m-%dT%H:%M:%S')

    if progress:
        progress.completed = 1 if completed else 0
        if completed:
            progress.completed_at = now
        else:
            progress.completed_at = None
        progress.last_accessed_at = now
        db.commit()
        return progress.id
    else:
        new_progress = Progress(
            user_id=user_id,
            lesson_id=lesson_id,
            completed=1 if completed else 0,
            completed_at=now if completed else None,
            last_accessed_at=now
        )
        db.add(new_progress)
        db.commit()
        db.refresh(new_progress)
        return new_progress.id


def update_lesson_access(db: Session, user_id: int, lesson_id: int) -> int:
    """Update last accessed time for a lesson"""
    progress = db.query(Progress).filter(
        Progress.user_id == user_id,
        Progress.lesson_id == lesson_id
    ).first()
    
    now = datetime.now().strftime('%Y-%m-%dT%H:%M:%S')
    
    if progress:
        progress.last_accessed_at = now
        db.commit()
        return progress.id
    else:
        new_progress = Progress(
            user_id=user_id,
            lesson_id=lesson_id,
            completed=0,
            last_accessed_at=now
        )
        db.add(new_progress)
        db.commit()
        db.refresh(new_progress)
        return new_progress.id


def get_course_progress_stats(db: Session, user_id: int, course_id: int) -> Dict[str, Any]:
    """Get progress stats for a course"""
    # Total published lessons
    total_count = db.query(Lesson).filter(
        Lesson.course_id == course_id,
        Lesson.status == 'published'
    ).count()
    
    if total_count == 0:
        return {
            'total': 0,
            'completed': 0,
            'percentage': 0
        }

    # Completed lessons identifying
    # We need lessons that are published AND completed by user
    # Join Progress and Lesson
    completed_count = db.query(Progress).join(Lesson).filter(
        Progress.user_id == user_id,
        Progress.completed == 1,
        Lesson.course_id == course_id,
        Lesson.status == 'published'
    ).count()

    percentage = (completed_count / total_count * 100)
    
    return {
        'total': total_count,
        'completed': completed_count,
        'percentage': round(percentage, 1)
    }